search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
8.99k stars 1.07k forks source link

sqlancer: Hitting issue when creating temporary table with PK and ON COMMIT DELETE in parallel workload #10140

Open qvad opened 3 years ago

qvad commented 3 years ago

Jira Link: DB-749 There is couple of thread failures in SQLancer with same error message

This is different logs, from different runs, but they have common SQL calls like this are temporary tables creation with PK and ON COMMIT DELETE. IT's not reproducible if user tries to evaluate it in single thread via JDBC client.

Caused by: org.postgresql.util.PSQLException: ERROR: could not find tuple for relation

Here is examples:

--java.lang.AssertionError: CREATE TEMPORARY TABLE t0(c0 FLOAT  DEFAULT (0.1910669), c1 money  UNIQUE DEFAULT (CAST(0.7390993 AS MONEY)) PRIMARY KEY, UNIQUE(c0)) ON COMMIT DELETE ROWS ;
--  at sqlancer.common.query.SQLQueryAdapter.checkException(SQLQueryAdapter.java:100)
--  at sqlancer.common.query.SQLQueryAdapter.execute(SQLQueryAdapter.java:93)
--  at sqlancer.Main$QueryManager.execute(Main.java:239)
--  at sqlancer.GlobalState.executeStatement(GlobalState.java:108)
--  at sqlancer.yugabyte.YugabyteProvider.createTables(YugabyteProvider.java:265)
--  at sqlancer.yugabyte.YugabyteProvider.generateDatabase(YugabyteProvider.java:145)
--  at sqlancer.yugabyte.YugabyteProvider.generateDatabase(YugabyteProvider.java:1)
--  at sqlancer.ProviderAdapter.generateAndTestDatabase(ProviderAdapter.java:40)
--  at sqlancer.Main$DBMSExecutor.run(Main.java:327)
--  at sqlancer.Main$2.run(Main.java:511)
--  at sqlancer.Main$2.runThread(Main.java:489)
--  at sqlancer.Main$2.run(Main.java:479)
--  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
--  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
--  at java.base/java.lang.Thread.run(Thread.java:834)
--Caused by: org.postgresql.util.PSQLException: ERROR: could not find tuple for relation 16745
--  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
--  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
--  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
--  at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
--  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
--  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
--  at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
--  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
--  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
--  at sqlancer.common.query.SQLQueryAdapter.execute(SQLQueryAdapter.java:87)
--  ... 13 more
---- Time: 2021/09/29 10:55:14
-- Database: database35
-- Database version: 11.2-YB-2.9.1.0-b0
-- seed value: 2057
\c yugabyte;
DROP DATABASE IF EXISTS database35;
CREATE DATABASE database35  TEMPLATE template0;
\c database35;
CREATE TEMPORARY TABLE t0(c0 FLOAT  DEFAULT (0.1910669), c1 money  UNIQUE DEFAULT (CAST(0.7390993 AS MONEY)) PRIMARY KEY, UNIQUE(c0)) ON COMMIT DELETE ROWS ;
--java.lang.AssertionError: CREATE TEMP TABLE IF NOT EXISTS t2(c0 FLOAT  PRIMARY KEY) ON COMMIT DELETE ROWS ;
--  at sqlancer.common.query.SQLQueryAdapter.checkException(SQLQueryAdapter.java:100)
--  at sqlancer.common.query.SQLQueryAdapter.execute(SQLQueryAdapter.java:93)
--  at sqlancer.Main$QueryManager.execute(Main.java:239)
--  at sqlancer.GlobalState.executeStatement(GlobalState.java:108)
--  at sqlancer.yugabyte.YugabyteProvider.createTables(YugabyteProvider.java:265)
--  at sqlancer.yugabyte.YugabyteProvider.generateDatabase(YugabyteProvider.java:145)
--  at sqlancer.yugabyte.YugabyteProvider.generateDatabase(YugabyteProvider.java:1)
--  at sqlancer.ProviderAdapter.generateAndTestDatabase(ProviderAdapter.java:40)
--  at sqlancer.Main$DBMSExecutor.run(Main.java:327)
--  at sqlancer.Main$2.run(Main.java:511)
--  at sqlancer.Main$2.runThread(Main.java:489)
--  at sqlancer.Main$2.run(Main.java:479)
--  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
--  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
--  at java.base/java.lang.Thread.run(Thread.java:834)
--Caused by: org.postgresql.util.PSQLException: ERROR: could not find tuple for relation 18489
--  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
--  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
--  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
--  at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
--  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
--  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
--  at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
--  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
--  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
--  at sqlancer.common.query.SQLQueryAdapter.execute(SQLQueryAdapter.java:87)
--  ... 13 more
---- Time: 2021/09/29 11:08:15
-- Database: database57
-- Database version: 11.2-YB-2.9.1.0-b0
-- seed value: 2079
\c yugabyte;
DROP DATABASE IF EXISTS database57;
CREATE DATABASE database57 WITH ENCODING 'utf8'  TEMPLATE template0;
\c database57;
...
CREATE TEMP TABLE IF NOT EXISTS t2(c0 FLOAT  PRIMARY KEY) ON COMMIT DELETE ROWS ;
tedyu commented 3 years ago

As of commit 1d34d30a181d77b6d01ff62c25c60c74d5c64d32, I don't see error for the temp table creation:

yugabyte=# CREATE TEMPORARY TABLE t0(c0 FLOAT  DEFAULT (0.1910669), c1 money  UNIQUE DEFAULT (CAST(0.7390993 AS MONEY)) PRIMARY KEY, UNIQUE(c0)) ON COMMIT DELETE ROWS ;
CREATE TABLE
yugabyte=# CREATE TEMP TABLE IF NOT EXISTS t2(c0 FLOAT  PRIMARY KEY) ON COMMIT DELETE ROWS ;
CREATE TABLE
yugabyte=# ^D\q
yugabyte=# create TABLE t0(c0 FLOAT  DEFAULT (0.1910669), c1 money  UNIQUE DEFAULT (CAST(0.7390993 AS MONEY)) PRIMARY KEY, UNIQUE(c0)) ;
CREATE TABLE
yugabyte=# create TABLE IF NOT EXISTS t2(c0 FLOAT  PRIMARY KEY) ;
CREATE TABLE
qvad commented 3 years ago

@tedyu It's not reproducible in simple tests. For now it requires SQLancer tests to run. It's definitely related to parallel workload. I will try to create reproducer in Java