diff options
| -rw-r--r-- | src/backend/access/common/toast_internals.c | 16 | ||||
| -rw-r--r-- | src/test/isolation/expected/reindex-concurrently-toast.out | 775 | ||||
| -rw-r--r-- | src/test/isolation/isolation_schedule | 1 | ||||
| -rw-r--r-- | src/test/isolation/specs/reindex-concurrently-toast.spec | 119 |
4 files changed, 905 insertions, 6 deletions
diff --git a/src/backend/access/common/toast_internals.c b/src/backend/access/common/toast_internals.c index f3abd7eaf6f..3d5ae381197 100644 --- a/src/backend/access/common/toast_internals.c +++ b/src/backend/access/common/toast_internals.c @@ -344,10 +344,12 @@ toast_save_datum(Relation rel, Datum value, } /* - * Done - close toast relation and its indexes + * Done - close toast relation and its indexes but keep the lock until + * commit, so as a concurrent reindex done directly on the toast relation + * would be able to wait for this transaction. */ - toast_close_indexes(toastidxs, num_indexes, RowExclusiveLock); - table_close(toastrel, RowExclusiveLock); + toast_close_indexes(toastidxs, num_indexes, NoLock); + table_close(toastrel, NoLock); /* * Create the TOAST pointer value that we'll return @@ -424,11 +426,13 @@ toast_delete_datum(Relation rel, Datum value, bool is_speculative) } /* - * End scan and close relations + * End scan and close relations but keep the lock until commit, so as a + * concurrent reindex done directly on the toast relation would be able to + * wait for this transaction. */ systable_endscan_ordered(toastscan); - toast_close_indexes(toastidxs, num_indexes, RowExclusiveLock); - table_close(toastrel, RowExclusiveLock); + toast_close_indexes(toastidxs, num_indexes, NoLock); + table_close(toastrel, NoLock); } /* ---------- diff --git a/src/test/isolation/expected/reindex-concurrently-toast.out b/src/test/isolation/expected/reindex-concurrently-toast.out new file mode 100644 index 00000000000..a6f1edc0edd --- /dev/null +++ b/src/test/isolation/expected/reindex-concurrently-toast.out @@ -0,0 +1,775 @@ +Parsed test spec with 2 sessions + +starting permutation: lrex1 ins1 retab2 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 + 3|3333333333 +(3 rows) + + +starting permutation: lrex1 ins1 reind2 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 + 3|3333333333 +(3 rows) + + +starting permutation: lrex1 upd1 retab2 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|4444444444 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 upd1 reind2 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|4444444444 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 del1 retab2 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 +(1 row) + + +starting permutation: lrex1 del1 reind2 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 +(1 row) + + +starting permutation: lrex1 dro1 retab2 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step dro1: DROP TABLE reind_con_wide; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +ERROR: relation "pg_toast.reind_con_toast" does not exist +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lrex1 dro1 reind2 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step dro1: DROP TABLE reind_con_wide; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +ERROR: relation "pg_toast.reind_con_toast_idx" does not exist +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lrex1 retab2 dro1 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; +step dro1: DROP TABLE reind_con_wide; +step end1: COMMIT; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lrex1 reind2 dro1 end1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; +step dro1: DROP TABLE reind_con_wide; +step end1: COMMIT; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lsha1 ins1 retab2 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 + 3|3333333333 +(3 rows) + + +starting permutation: lsha1 ins1 reind2 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 + 3|3333333333 +(3 rows) + + +starting permutation: lsha1 upd1 retab2 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|4444444444 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 upd1 reind2 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|4444444444 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 del1 retab2 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 +(1 row) + + +starting permutation: lsha1 del1 reind2 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 +(1 row) + + +starting permutation: lsha1 dro1 retab2 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step dro1: DROP TABLE reind_con_wide; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +ERROR: relation "pg_toast.reind_con_toast" does not exist +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lsha1 dro1 reind2 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step dro1: DROP TABLE reind_con_wide; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +ERROR: relation "pg_toast.reind_con_toast_idx" does not exist +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lsha1 retab2 dro1 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; +step dro1: DROP TABLE reind_con_wide; +step end1: COMMIT; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lsha1 reind2 dro1 end1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; +step dro1: DROP TABLE reind_con_wide; +step end1: COMMIT; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lexc1 ins1 retab2 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 + 3|3333333333 +(3 rows) + + +starting permutation: lexc1 ins1 reind2 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 + 3|3333333333 +(3 rows) + + +starting permutation: lexc1 upd1 retab2 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|4444444444 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 upd1 reind2 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|4444444444 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 del1 retab2 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 +(1 row) + + +starting permutation: lexc1 del1 reind2 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 +(1 row) + + +starting permutation: lexc1 dro1 retab2 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step dro1: DROP TABLE reind_con_wide; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step end1: COMMIT; +step retab2: <... completed> +ERROR: relation "pg_toast.reind_con_toast" does not exist +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lexc1 dro1 reind2 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step dro1: DROP TABLE reind_con_wide; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step end1: COMMIT; +step reind2: <... completed> +ERROR: relation "pg_toast.reind_con_toast_idx" does not exist +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lexc1 retab2 dro1 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; +step dro1: DROP TABLE reind_con_wide; +step end1: COMMIT; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lexc1 reind2 dro1 end1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; +step dro1: DROP TABLE reind_con_wide; +step end1: COMMIT; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +ERROR: relation "reind_con_wide" does not exist + +starting permutation: lrex1 ins1 retab2 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 ins1 reind2 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 upd1 retab2 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 upd1 reind2 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 del1 retab2 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 del1 reind2 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 dro1 retab2 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step dro1: DROP TABLE reind_con_wide; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 dro1 reind2 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step dro1: DROP TABLE reind_con_wide; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 retab2 dro1 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; +step dro1: DROP TABLE reind_con_wide; +step rol1: ROLLBACK; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lrex1 reind2 dro1 rol1 sel2 +step lrex1: lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; +step dro1: DROP TABLE reind_con_wide; +step rol1: ROLLBACK; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 ins1 retab2 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 ins1 reind2 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 upd1 retab2 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 upd1 reind2 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 del1 retab2 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 del1 reind2 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 dro1 retab2 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step dro1: DROP TABLE reind_con_wide; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 dro1 reind2 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step dro1: DROP TABLE reind_con_wide; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 retab2 dro1 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; +step dro1: DROP TABLE reind_con_wide; +step rol1: ROLLBACK; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lsha1 reind2 dro1 rol1 sel2 +step lsha1: lock TABLE reind_con_wide in SHARE MODE; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; +step dro1: DROP TABLE reind_con_wide; +step rol1: ROLLBACK; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 ins1 retab2 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 ins1 reind2 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step ins1: INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 upd1 retab2 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 upd1 reind2 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step upd1: UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 del1 retab2 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 del1 reind2 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step del1: DELETE FROM reind_con_wide WHERE id = 2; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 dro1 retab2 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step dro1: DROP TABLE reind_con_wide; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; <waiting ...> +step rol1: ROLLBACK; +step retab2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 dro1 reind2 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step dro1: DROP TABLE reind_con_wide; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; <waiting ...> +step rol1: ROLLBACK; +step reind2: <... completed> +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 retab2 dro1 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step retab2: REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; +step dro1: DROP TABLE reind_con_wide; +step rol1: ROLLBACK; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + + +starting permutation: lexc1 reind2 dro1 rol1 sel2 +step lexc1: lock TABLE reind_con_wide in EXCLUSIVE MODE; +step reind2: REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; +step dro1: DROP TABLE reind_con_wide; +step rol1: ROLLBACK; +step sel2: SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; +id| substr +--+---------- + 1|1111111111 + 2|2222222222 +(2 rows) + diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 9ec39730ebc..ccb8f5339e4 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -49,6 +49,7 @@ test: lock-committed-update test: lock-committed-keyupdate test: update-locked-tuple test: reindex-concurrently +test: reindex-concurrently-toast test: propagate-lock-delete test: tuplelock-conflict test: tuplelock-update diff --git a/src/test/isolation/specs/reindex-concurrently-toast.spec b/src/test/isolation/specs/reindex-concurrently-toast.spec new file mode 100644 index 00000000000..81887922d29 --- /dev/null +++ b/src/test/isolation/specs/reindex-concurrently-toast.spec @@ -0,0 +1,119 @@ +# REINDEX CONCURRENTLY with toast relations +# +# Ensure that concurrent operations work correctly when a REINDEX is performed +# concurrently on toast relations. Toast relation names are not deterministic, +# so this abuses of allow_system_table_mods to change the names of toast +# tables and its indexes so as they can be executed with REINDEX CONCURRENTLY, +# which cannot be launched in a transaction context. + +# Create a table, with deterministic names for its toast relation and indexes. +# Fortunately ALTER TABLE is transactional, making the renaming of toast +# relations possible with allow_system_table_mods. +setup +{ + CREATE TABLE reind_con_wide(id int primary key, data text); + INSERT INTO reind_con_wide + SELECT 1, repeat('1', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); + INSERT INTO reind_con_wide + SELECT 2, repeat('2', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); + SET allow_system_table_mods TO true; + DO $$DECLARE r record; + BEGIN + SELECT INTO r reltoastrelid::regclass::text AS table_name FROM pg_class + WHERE oid = 'reind_con_wide'::regclass; + EXECUTE 'ALTER TABLE ' || r.table_name || ' RENAME TO reind_con_toast;'; + SELECT INTO r indexrelid::regclass::text AS index_name FROM pg_index + WHERE indrelid = (SELECT oid FROM pg_class where relname = 'reind_con_toast'); + EXECUTE 'ALTER INDEX ' || r.index_name || ' RENAME TO reind_con_toast_idx;'; + END$$; +} + +teardown +{ + DROP TABLE IF EXISTS reind_con_wide; +} + +session s1 +setup { BEGIN; } +step lrex1 { lock TABLE reind_con_wide in ROW EXCLUSIVE MODE; } +step lsha1 { lock TABLE reind_con_wide in SHARE MODE; } +step lexc1 { lock TABLE reind_con_wide in EXCLUSIVE MODE; } +step ins1 { INSERT INTO reind_con_wide SELECT 3, repeat('3', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i); } +step upd1 { UPDATE reind_con_wide SET data = (SELECT repeat('4', 11) || string_agg(g.i::text || random()::text, '') FROM generate_series(1, 500) g(i)) WHERE id = 1; } +step del1 { DELETE FROM reind_con_wide WHERE id = 2; } +step dro1 { DROP TABLE reind_con_wide; } +step end1 { COMMIT; } +step rol1 { ROLLBACK; } + +session s2 +step retab2 { REINDEX TABLE CONCURRENTLY pg_toast.reind_con_toast; } +step reind2 { REINDEX INDEX CONCURRENTLY pg_toast.reind_con_toast_idx; } +step sel2 { SELECT id, substr(data, 1, 10) FROM reind_con_wide ORDER BY id; } + +# Transaction commit with ROW EXCLUSIVE MODE +permutation lrex1 ins1 retab2 end1 sel2 +permutation lrex1 ins1 reind2 end1 sel2 +permutation lrex1 upd1 retab2 end1 sel2 +permutation lrex1 upd1 reind2 end1 sel2 +permutation lrex1 del1 retab2 end1 sel2 +permutation lrex1 del1 reind2 end1 sel2 +permutation lrex1 dro1 retab2 end1 sel2 +permutation lrex1 dro1 reind2 end1 sel2 +permutation lrex1 retab2 dro1 end1 sel2 +permutation lrex1 reind2 dro1 end1 sel2 +# Transaction commit with SHARE MODE +permutation lsha1 ins1 retab2 end1 sel2 +permutation lsha1 ins1 reind2 end1 sel2 +permutation lsha1 upd1 retab2 end1 sel2 +permutation lsha1 upd1 reind2 end1 sel2 +permutation lsha1 del1 retab2 end1 sel2 +permutation lsha1 del1 reind2 end1 sel2 +permutation lsha1 dro1 retab2 end1 sel2 +permutation lsha1 dro1 reind2 end1 sel2 +permutation lsha1 retab2 dro1 end1 sel2 +permutation lsha1 reind2 dro1 end1 sel2 +# Transaction commit with EXCLUSIVE MODE +permutation lexc1 ins1 retab2 end1 sel2 +permutation lexc1 ins1 reind2 end1 sel2 +permutation lexc1 upd1 retab2 end1 sel2 +permutation lexc1 upd1 reind2 end1 sel2 +permutation lexc1 del1 retab2 end1 sel2 +permutation lexc1 del1 reind2 end1 sel2 +permutation lexc1 dro1 retab2 end1 sel2 +permutation lexc1 dro1 reind2 end1 sel2 +permutation lexc1 retab2 dro1 end1 sel2 +permutation lexc1 reind2 dro1 end1 sel2 + +# Transaction rollback with ROW EXCLUSIVE MODE +permutation lrex1 ins1 retab2 rol1 sel2 +permutation lrex1 ins1 reind2 rol1 sel2 +permutation lrex1 upd1 retab2 rol1 sel2 +permutation lrex1 upd1 reind2 rol1 sel2 +permutation lrex1 del1 retab2 rol1 sel2 +permutation lrex1 del1 reind2 rol1 sel2 +permutation lrex1 dro1 retab2 rol1 sel2 +permutation lrex1 dro1 reind2 rol1 sel2 +permutation lrex1 retab2 dro1 rol1 sel2 +permutation lrex1 reind2 dro1 rol1 sel2 +# Transaction rollback with SHARE MODE +permutation lsha1 ins1 retab2 rol1 sel2 +permutation lsha1 ins1 reind2 rol1 sel2 +permutation lsha1 upd1 retab2 rol1 sel2 +permutation lsha1 upd1 reind2 rol1 sel2 +permutation lsha1 del1 retab2 rol1 sel2 +permutation lsha1 del1 reind2 rol1 sel2 +permutation lsha1 dro1 retab2 rol1 sel2 +permutation lsha1 dro1 reind2 rol1 sel2 +permutation lsha1 retab2 dro1 rol1 sel2 +permutation lsha1 reind2 dro1 rol1 sel2 +# Transaction rollback with EXCLUSIVE MODE +permutation lexc1 ins1 retab2 rol1 sel2 +permutation lexc1 ins1 reind2 rol1 sel2 +permutation lexc1 upd1 retab2 rol1 sel2 +permutation lexc1 upd1 reind2 rol1 sel2 +permutation lexc1 del1 retab2 rol1 sel2 +permutation lexc1 del1 reind2 rol1 sel2 +permutation lexc1 dro1 retab2 rol1 sel2 +permutation lexc1 dro1 reind2 rol1 sel2 +permutation lexc1 retab2 dro1 rol1 sel2 +permutation lexc1 reind2 dro1 rol1 sel2 |