diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
commit | 311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch) | |
tree | 0ec307299b1dada3701e42f4ca6eda57d708261e /src/test/regress/expected/replica_identity.out | |
parent | Initial commit. (diff) | |
download | postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.tar.xz postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.zip |
Adding upstream version 15.4.upstream/15.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/test/regress/expected/replica_identity.out')
-rw-r--r-- | src/test/regress/expected/replica_identity.out | 270 |
1 files changed, 270 insertions, 0 deletions
diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out new file mode 100644 index 0000000..7d798ef --- /dev/null +++ b/src/test/regress/expected/replica_identity.out @@ -0,0 +1,270 @@ +CREATE TABLE test_replica_identity ( + id serial primary key, + keya text not null, + keyb text not null, + nonkey text, + CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE, + CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb) +) ; +CREATE TABLE test_replica_identity_othertable (id serial primary key); +CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb); +CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb); +CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey); +CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); +CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3)); +CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3'; +-- default is 'd'/DEFAULT for user created tables +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + d +(1 row) + +-- but 'none' for system tables +SELECT relreplident FROM pg_class WHERE oid = 'pg_class'::regclass; + relreplident +-------------- + n +(1 row) + +SELECT relreplident FROM pg_class WHERE oid = 'pg_constraint'::regclass; + relreplident +-------------- + n +(1 row) + +---- +-- Make sure we detect ineligible indexes +---- +-- fail, not unique +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab; +ERROR: cannot use non-unique index "test_replica_identity_keyab" as replica identity +-- fail, not a candidate key, nullable column +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_nonkey; +ERROR: index "test_replica_identity_nonkey" cannot be used as replica identity because column "nonkey" is nullable +-- fail, hash indexes cannot do uniqueness +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_hash; +ERROR: cannot use non-unique index "test_replica_identity_hash" as replica identity +-- fail, expression index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_expr; +ERROR: cannot use expression index "test_replica_identity_expr" as replica identity +-- fail, partial index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_partial; +ERROR: cannot use partial index "test_replica_identity_partial" as replica identity +-- fail, not our index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_othertable_pkey; +ERROR: "test_replica_identity_othertable_pkey" is not an index for table "test_replica_identity" +-- fail, deferrable +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_defer; +ERROR: cannot use non-immediate index "test_replica_identity_unique_defer" as replica identity +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + d +(1 row) + +---- +-- Make sure index cases succeed +---- +-- succeed, primary key +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_pkey; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + i +(1 row) + +\d test_replica_identity + Table "public.test_replica_identity" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------------------------------------------------- + id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) + keya | text | | not null | + keyb | text | | not null | + nonkey | text | | | +Indexes: + "test_replica_identity_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY + "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) + "test_replica_identity_hash" hash (nonkey) + "test_replica_identity_keyab" btree (keya, keyb) + "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) + "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) + "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text + "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE + "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) + +-- succeed, nondeferrable unique constraint over nonnullable cols +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; +-- succeed unique index over nonnullable cols +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + i +(1 row) + +\d test_replica_identity + Table "public.test_replica_identity" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------------------------------------------------- + id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) + keya | text | | not null | + keyb | text | | not null | + nonkey | text | | | +Indexes: + "test_replica_identity_pkey" PRIMARY KEY, btree (id) + "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) + "test_replica_identity_hash" hash (nonkey) + "test_replica_identity_keyab" btree (keya, keyb) + "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) REPLICA IDENTITY + "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) + "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text + "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE + "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) + +SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; + count +------- + 1 +(1 row) + +---- +-- Make sure non index cases work +---- +ALTER TABLE test_replica_identity REPLICA IDENTITY DEFAULT; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + d +(1 row) + +SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; + count +------- + 0 +(1 row) + +ALTER TABLE test_replica_identity REPLICA IDENTITY FULL; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + f +(1 row) + +\d+ test_replica_identity + Table "public.test_replica_identity" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------------------------------------------------+----------+--------------+------------- + id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | plain | | + keya | text | | not null | | extended | | + keyb | text | | not null | | extended | | + nonkey | text | | | | extended | | +Indexes: + "test_replica_identity_pkey" PRIMARY KEY, btree (id) + "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) + "test_replica_identity_hash" hash (nonkey) + "test_replica_identity_keyab" btree (keya, keyb) + "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) + "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) + "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text + "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE + "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) +Replica Identity: FULL + +ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + n +(1 row) + +--- +-- Test that ALTER TABLE rewrite preserves nondefault replica identity +--- +-- constraint variant +CREATE TABLE test_replica_identity2 (id int UNIQUE NOT NULL); +ALTER TABLE test_replica_identity2 REPLICA IDENTITY USING INDEX test_replica_identity2_id_key; +\d test_replica_identity2 + Table "public.test_replica_identity2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | not null | +Indexes: + "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY + +ALTER TABLE test_replica_identity2 ALTER COLUMN id TYPE bigint; +\d test_replica_identity2 + Table "public.test_replica_identity2" + Column | Type | Collation | Nullable | Default +--------+--------+-----------+----------+--------- + id | bigint | | not null | +Indexes: + "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY + +-- straight index variant +CREATE TABLE test_replica_identity3 (id int NOT NULL); +CREATE UNIQUE INDEX test_replica_identity3_id_key ON test_replica_identity3 (id); +ALTER TABLE test_replica_identity3 REPLICA IDENTITY USING INDEX test_replica_identity3_id_key; +\d test_replica_identity3 + Table "public.test_replica_identity3" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | not null | +Indexes: + "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY + +ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint; +\d test_replica_identity3 + Table "public.test_replica_identity3" + Column | Type | Collation | Nullable | Default +--------+--------+-----------+----------+--------- + id | bigint | | not null | +Indexes: + "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY + +-- ALTER TABLE DROP NOT NULL is not allowed for columns part of an index +-- used as replica identity. +ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL; +ERROR: column "id" is in index used as replica identity +-- +-- Test that replica identity can be set on an index that's not yet valid. +-- (This matches the way pg_dump will try to dump a partitioned table.) +-- +CREATE TABLE test_replica_identity4(id integer NOT NULL) PARTITION BY LIST (id); +CREATE TABLE test_replica_identity4_1(id integer NOT NULL); +ALTER TABLE ONLY test_replica_identity4 + ATTACH PARTITION test_replica_identity4_1 FOR VALUES IN (1); +ALTER TABLE ONLY test_replica_identity4 + ADD CONSTRAINT test_replica_identity4_pkey PRIMARY KEY (id); +ALTER TABLE ONLY test_replica_identity4 + REPLICA IDENTITY USING INDEX test_replica_identity4_pkey; +ALTER TABLE ONLY test_replica_identity4_1 + ADD CONSTRAINT test_replica_identity4_1_pkey PRIMARY KEY (id); +\d+ test_replica_identity4 + Partitioned table "public.test_replica_identity4" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + id | integer | | not null | | plain | | +Partition key: LIST (id) +Indexes: + "test_replica_identity4_pkey" PRIMARY KEY, btree (id) INVALID REPLICA IDENTITY +Partitions: test_replica_identity4_1 FOR VALUES IN (1) + +ALTER INDEX test_replica_identity4_pkey + ATTACH PARTITION test_replica_identity4_1_pkey; +\d+ test_replica_identity4 + Partitioned table "public.test_replica_identity4" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + id | integer | | not null | | plain | | +Partition key: LIST (id) +Indexes: + "test_replica_identity4_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY +Partitions: test_replica_identity4_1 FOR VALUES IN (1) + +DROP TABLE test_replica_identity; +DROP TABLE test_replica_identity2; +DROP TABLE test_replica_identity3; +DROP TABLE test_replica_identity4; +DROP TABLE test_replica_identity_othertable; |