diff options
Diffstat (limited to 'contrib/test_decoding/sql/ddl.sql')
| -rw-r--r-- | contrib/test_decoding/sql/ddl.sql | 31 |
1 files changed, 31 insertions, 0 deletions
diff --git a/contrib/test_decoding/sql/ddl.sql b/contrib/test_decoding/sql/ddl.sql index 2c4823e5780..1b3866d0153 100644 --- a/contrib/test_decoding/sql/ddl.sql +++ b/contrib/test_decoding/sql/ddl.sql @@ -345,6 +345,37 @@ UPDATE table_with_unique_not_null SET id = -id; UPDATE table_with_unique_not_null SET id = -id; DELETE FROM table_with_unique_not_null WHERE data = 3; +-- check tables with dropped indexes used in REPLICA IDENTITY +-- table with primary key +CREATE TABLE table_dropped_index_with_pk (a int PRIMARY KEY, b int, c int); +CREATE UNIQUE INDEX table_dropped_index_with_pk_idx + ON table_dropped_index_with_pk(a); +ALTER TABLE table_dropped_index_with_pk REPLICA IDENTITY + USING INDEX table_dropped_index_with_pk_idx; +DROP INDEX table_dropped_index_with_pk_idx; +INSERT INTO table_dropped_index_with_pk VALUES (1,1,1), (2,2,2), (3,3,3); +UPDATE table_dropped_index_with_pk SET a = 4 WHERE a = 1; +UPDATE table_dropped_index_with_pk SET b = 5 WHERE a = 2; +UPDATE table_dropped_index_with_pk SET b = 6, c = 7 WHERE a = 3; +DELETE FROM table_dropped_index_with_pk WHERE b = 1; +DELETE FROM table_dropped_index_with_pk WHERE a = 3; +DROP TABLE table_dropped_index_with_pk; + +-- table without primary key +CREATE TABLE table_dropped_index_no_pk (a int NOT NULL, b int, c int); +CREATE UNIQUE INDEX table_dropped_index_no_pk_idx + ON table_dropped_index_no_pk(a); +ALTER TABLE table_dropped_index_no_pk REPLICA IDENTITY + USING INDEX table_dropped_index_no_pk_idx; +DROP INDEX table_dropped_index_no_pk_idx; +INSERT INTO table_dropped_index_no_pk VALUES (1,1,1), (2,2,2), (3,3,3); +UPDATE table_dropped_index_no_pk SET a = 4 WHERE a = 1; +UPDATE table_dropped_index_no_pk SET b = 5 WHERE a = 2; +UPDATE table_dropped_index_no_pk SET b = 6, c = 7 WHERE a = 3; +DELETE FROM table_dropped_index_no_pk WHERE b = 1; +DELETE FROM table_dropped_index_no_pk WHERE a = 3; +DROP TABLE table_dropped_index_no_pk; + -- check toast support BEGIN; CREATE SEQUENCE toasttable_rand_seq START 79 INCREMENT 1499; -- portable "random" |