search

yugabyte / yugabyte-db

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

[YSQL] Enable all isolation regress tests present in Pg #12637

Open pkj415 opened 2 years ago

pkj415 commented 2 years ago

Jira Link: DB-519

Description

We aim to ensure that YSQL passes all tests in PostgreSQL's isolation regress test suite.

This issue tracks the progress of this goal. The following tests haven't yet been enabled due to the reason mentioned along with each test - 

alter-table-1 - Needs some work on DDLs
alter-table-2 - Needs some work on DDLs
alter-table-3 - Needs some work on DDLs
alter-table-4 - Needs some work on DDLs
async-notify - #1872 LISTEN is not supported yet in YSQL
classroom-scheduling - YSQL has a different algorithm for SERIALIZABLE, not SSI
create-trigger - requires table-level locking
deadlock-hard - requires table-level locking
deadlock-simple - requires table-level locking
deadlock-soft-2 - requires table-level locking
deadlock-soft - requires table-level locking
drop-index-concurrently-1 - Needs work on DDL
fk-contention - #12325
freeze-the-dead - freezing doesn't apply to YSQL
index-only-scan - YSQL has a different algorithm for SERIALIZABLE, not SSI
inherit-temp - #1129 we don't support INHERITS yet
insert-conflict-do-nothing-2 - requires pessimistic locking behaviour
two-ids - YSQL has a different algorithm for SERIALIZABLE, not SSI
prepared-transactions-cic - #11102 we don't support prepared txns
sequence-ddl - Needs some work on DDLs
ri-trigger - YSQL has a different algorithm for SERIALIZABLE, not SSI
insert-conflict-toast - advisory locks are not supported in YSQL
lock-committed-keyupdate - advisory locks are not supported in YSQL
lock-committed-update - advisory locks are not supported in YSQL
lock-update-delete - advisory locks are not supported in YSQL
lock-update-traversal - TBD
multiple-cic - advisory locks are not supported in YSQL
multiple-row-versions - depends on SSI
multixact-no-forget - TBD
nowait-4 - YSQL doesn't support advisory locks
nowait-5 - YSQL doesn't support advisory locks
partial-index - TBD
partition-key-update-1 - TBD
partition-key-update-2 - TBD
partition-key-update-3 - TBD
partition-key-update-4 - TBD
plpgsql-toast - TBD
predicate-gin - TBD
predicate-gist - TBD
predicate-hash - TBD
prepared-transactions - TBD
project-manager - TBD
propagate-lock-delete - fails due to 2 issues: (1) in setup, mixing DDLs with DMLs lead to aborting the INSERT statement with errors such as `I1214 14:25:48.580806 110927872 tablet_service.cc:887] Aborting transactions that started prior to <max> for tablet id 647b433c0501429897f8cda6d23646c2 excluding transaction with id 217bb533-dcee-4305-9671-3c9ed1acf226` (2) `UPDATE parent SET i = i;` in the spec doesn't block in Pg but blocks in YSQL.
read-only-anomaly-2 - depends on SSI
read-only-anomaly-3 - depends on SSI
read-write-unique-2 - depends on SSI
read-write-unique-3 - depends on SSI
read-write-unique-4 - depends on SSI
read-write-unique - depends on SSI
receipt-report - depends on SSI
referential-integrity - depends on SSI
simple-write-skew - depends on SSI
temporal-range-integrity - depends on SSI
timeouts - table level locks not supported yet
total-cash - depends on SSI
tuplelock-conflict - TBD
tuplelock-update - depends on advisory locks
vacuum-concurrent-drop - TBD
vacuum-reltuples - TBD
skip-locked-4 - depends on advisory locks
insert-conflict-do-update-3 - @pkj415 to post the cause of this shortly
robertsami commented 1 year ago

@pkj415 create-trigger seems to require table-level locking as well. see this case:

starting permutation: s1a s1b s2a s2b s2c s1c s2d
step s1a: BEGIN;
step s1b: CREATE TRIGGER t AFTER UPDATE ON a EXECUTE PROCEDURE f();
step s2a: BEGIN;
step s2b: SELECT * FROM a WHERE i = 1 FOR UPDATE;
i              

1              
step s2c: UPDATE a SET i = 4 WHERE i = 3; <waiting ...>
step s1c: COMMIT;
step s2c: <... completed>
step s2d: COMMIT;
robertsami commented 1 year ago

@pkj415 note that with wait-based locking we still get some discrepancies on insert-conflict-do-nothing-2

*** /Users/rob/code/branches/repo5/src/postgres/src/test/isolation/expected/insert-conflict-do-nothing-2.out    2021-07-05 11:51:18.000000000 -0700
--- /Users/rob/code/branches/repo5/./pgregress_output/yb_pg_isolation_schedule/results/insert-conflict-do-nothing-2.out 2022-12-12 10:59:25.000000000 -0700
***************
*** 31,37 ****
  step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING; <waiting ...>
  step c1: COMMIT;
  step donothing2: <... completed>
- error in steps c1 donothing2: ERROR:  could not serialize access due to concurrent update
  step c2: COMMIT;
  step show: SELECT * FROM ints;
  key            val            
--- 31,36 ----
***************
*** 45,51 ****
  step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING; <waiting ...>
  step c2: COMMIT;
  step donothing1: <... completed>
- error in steps c2 donothing1: ERROR:  could not serialize access due to concurrent update
  step c1: COMMIT;
  step show: SELECT * FROM ints;
  key            val            
--- 44,49 ----
***************
*** 80,103 ****
  step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
  step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
  step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
! step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING; <waiting ...>
  step c1: COMMIT;
! step donothing2: <... completed>
! error in steps c1 donothing2: ERROR:  could not serialize access due to concurrent update
  step c2: COMMIT;
  step show: SELECT * FROM ints;
  key            val            

! 1              donothing1     

  starting permutation: begins1 begins2 donothing2 donothing1 c2 c1 show
  step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
  step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
  step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING;
! step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING; <waiting ...>
  step c2: COMMIT;
- step donothing1: <... completed>
- error in steps c2 donothing1: ERROR:  could not serialize access due to concurrent update
  step c1: COMMIT;
  step show: SELECT * FROM ints;
  key            val            
--- 78,99 ----
  step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
  step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
  step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
! step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING;
  step c1: COMMIT;
! ERROR:  Operation expired: Transaction [71, 53, 155, 234, 145, 145, 76, 33, 138, 37, 223, 226, 191, 114, 207, 107] expired or aborted by a conflict: 40001
  step c2: COMMIT;
  step show: SELECT * FROM ints;
  key            val            

! 1              donothing2     

  starting permutation: begins1 begins2 donothing2 donothing1 c2 c1 show
  step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
  step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
  step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING;
! step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
! ERROR:  All transparent retries exhausted. Operation failed. Try again: 7d2d2b05-e4ea-4a45-83a9-ed77786cbeb0 Conflicts with higher priority transaction: dc883489-c41a-48b0-b717-6cd09c271e21
  step c2: COMMIT;
  step c1: COMMIT;
  step show: SELECT * FROM ints;
  key            val            

======================================================================