Skip to content

Commit 8796abd

Browse files
pallavisontakkepallavisontakke
committed
Add primary key update tests
For Testing Issue: 2ndQuadrant/pglogical_output#14
1 parent 3e277ad commit 8796abd

File tree

3 files changed

+456
-1
lines changed

3 files changed

+456
-1
lines changed

‎Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ SCRIPTS_built = pglogical_create_subscriber
1818
PG_CPPFLAGS = -I$(libpq_srcdir)
1919
SHLIB_LINK = $(libpq)
2020

21-
REGRESS = preseed init_fail init preseed_check basic extended toasted replication_set add_table matview bidirectional foreign_key functions drop
21+
REGRESS = preseed init_fail init preseed_check basic extended toasted replication_set add_table matview bidirectional primary_key foreign_key functions drop
2222

2323
ifdef USE_PGXS
2424

‎expected/primary_key.out

Lines changed: 314 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,314 @@
1+
--PRIMARY KEY
2+
\c regression
3+
-- testing update of primary key
4+
-- create table with primary key and 3 other tables referencing it
5+
SELECT pglogical.replicate_ddl_command($$
6+
CREATE TABLE public.pk_users (
7+
id integer PRIMARY KEY,
8+
another_id integer unique not null,
9+
a_id integer,
10+
name text,
11+
address text
12+
);
13+
14+
--pass
15+
$$);
16+
replicate_ddl_command
17+
-----------------------
18+
t
19+
(1 row)
20+
21+
SELECT * FROM pglogical.replication_set_add_table('default', 'pk_users');
22+
replication_set_add_table
23+
---------------------------
24+
t
25+
(1 row)
26+
27+
INSERT INTO pk_users VALUES(1,11,1,'User1', 'Address1');
28+
INSERT INTO pk_users VALUES(2,12,1,'User2', 'Address2');
29+
INSERT INTO pk_users VALUES(3,13,2,'User3', 'Address3');
30+
INSERT INTO pk_users VALUES(4,14,2,'User4', 'Address4');
31+
SELECT * FROM pk_users;
32+
id | another_id | a_id | name | address
33+
----+------------+------+-------+----------
34+
1 | 11 | 1 | User1 | Address1
35+
2 | 12 | 1 | User2 | Address2
36+
3 | 13 | 2 | User3 | Address3
37+
4 | 14 | 2 | User4 | Address4
38+
(4 rows)
39+
40+
\d+ pk_users;
41+
Table "public.pk_users"
42+
Column | Type | Modifiers | Storage | Stats target | Description
43+
------------+---------+-----------+----------+--------------+-------------
44+
id | integer | not null | plain | |
45+
another_id | integer | not null | plain | |
46+
a_id | integer | | plain | |
47+
name | text | | extended | |
48+
address | text | | extended | |
49+
Indexes:
50+
"pk_users_pkey" PRIMARY KEY, btree (id)
51+
"pk_users_another_id_key" UNIQUE CONSTRAINT, btree (another_id)
52+
53+
\c postgres
54+
SELECT * FROM pk_users;
55+
id | another_id | a_id | name | address
56+
----+------------+------+-------+----------
57+
1 | 11 | 1 | User1 | Address1
58+
2 | 12 | 1 | User2 | Address2
59+
3 | 13 | 2 | User3 | Address3
60+
4 | 14 | 2 | User4 | Address4
61+
(4 rows)
62+
63+
\c regression
64+
UPDATE pk_users SET address='UpdatedAddress1' WHERE id=1;
65+
SELECT pg_xlog_wait_remote_apply(pg_current_xlog_location(), pid) FROM pg_stat_replication;
66+
pg_xlog_wait_remote_apply
67+
---------------------------
68+
69+
(1 row)
70+
71+
\c postgres
72+
SELECT * FROM pk_users;
73+
id | another_id | a_id | name | address
74+
----+------------+------+-------+-----------------
75+
2 | 12 | 1 | User2 | Address2
76+
3 | 13 | 2 | User3 | Address3
77+
4 | 14 | 2 | User4 | Address4
78+
1 | 11 | 1 | User1 | UpdatedAddress1
79+
(4 rows)
80+
81+
\c regression
82+
SELECT pglogical.replicate_ddl_command($$
83+
CREATE UNIQUE INDEX another_id_temp_idx ON public.pk_users (another_id);
84+
ALTER TABLE public.pk_users DROP CONSTRAINT pk_users_pkey,
85+
ADD CONSTRAINT pk_users_pkey PRIMARY KEY USING INDEX another_id_temp_idx;
86+
87+
ALTER TABLE public.pk_users DROP CONSTRAINT pk_users_another_id_key;
88+
$$);
89+
NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "another_id_temp_idx" to "pk_users_pkey"
90+
CONTEXT: during execution of queued SQL statement:
91+
CREATE UNIQUE INDEX another_id_temp_idx ON public.pk_users (another_id);
92+
ALTER TABLE public.pk_users DROP CONSTRAINT pk_users_pkey,
93+
ADD CONSTRAINT pk_users_pkey PRIMARY KEY USING INDEX another_id_temp_idx;
94+
95+
ALTER TABLE public.pk_users DROP CONSTRAINT pk_users_another_id_key;
96+
97+
replicate_ddl_command
98+
-----------------------
99+
t
100+
(1 row)
101+
102+
\d+ pk_users;
103+
Table "public.pk_users"
104+
Column | Type | Modifiers | Storage | Stats target | Description
105+
------------+---------+-----------+----------+--------------+-------------
106+
id | integer | not null | plain | |
107+
another_id | integer | not null | plain | |
108+
a_id | integer | | plain | |
109+
name | text | | extended | |
110+
address | text | | extended | |
111+
Indexes:
112+
"pk_users_pkey" PRIMARY KEY, btree (another_id)
113+
114+
SELECT pg_xlog_wait_remote_apply(pg_current_xlog_location(), pid) FROM pg_stat_replication;
115+
pg_xlog_wait_remote_apply
116+
---------------------------
117+
118+
(1 row)
119+
120+
UPDATE pk_users SET address='UpdatedAddress2' WHERE id=2;
121+
SELECT pg_xlog_wait_remote_apply(pg_current_xlog_location(), pid) FROM pg_stat_replication;
122+
pg_xlog_wait_remote_apply
123+
---------------------------
124+
125+
(1 row)
126+
127+
\c postgres
128+
\d+ pk_users;
129+
Table "public.pk_users"
130+
Column | Type | Modifiers | Storage | Stats target | Description
131+
------------+---------+-----------+----------+--------------+-------------
132+
id | integer | not null | plain | |
133+
another_id | integer | not null | plain | |
134+
a_id | integer | | plain | |
135+
name | text | | extended | |
136+
address | text | | extended | |
137+
Indexes:
138+
"pk_users_pkey" PRIMARY KEY, btree (another_id)
139+
140+
SELECT * FROM pk_users;
141+
id | another_id | a_id | name | address
142+
----+------------+------+-------+-----------------
143+
3 | 13 | 2 | User3 | Address3
144+
4 | 14 | 2 | User4 | Address4
145+
1 | 11 | 1 | User1 | UpdatedAddress1
146+
2 | 12 | 1 | User2 | UpdatedAddress2
147+
(4 rows)
148+
149+
\c regression
150+
UPDATE pk_users SET address='UpdatedAddress3' WHERE another_id=12;
151+
SELECT pg_xlog_wait_remote_apply(pg_current_xlog_location(), pid) FROM pg_stat_replication;
152+
pg_xlog_wait_remote_apply
153+
---------------------------
154+
155+
(1 row)
156+
157+
\c postgres
158+
SELECT * FROM pk_users;
159+
id | another_id | a_id | name | address
160+
----+------------+------+-------+-----------------
161+
3 | 13 | 2 | User3 | Address3
162+
4 | 14 | 2 | User4 | Address4
163+
1 | 11 | 1 | User1 | UpdatedAddress1
164+
2 | 12 | 1 | User2 | UpdatedAddress3
165+
(4 rows)
166+
167+
\c regression
168+
UPDATE pk_users SET address='UpdatedAddress4' WHERE a_id=2;
169+
SELECT pg_xlog_wait_remote_apply(pg_current_xlog_location(), pid) FROM pg_stat_replication;
170+
pg_xlog_wait_remote_apply
171+
---------------------------
172+
173+
(1 row)
174+
175+
\c postgres
176+
INSERT INTO pk_users VALUES(4,15,2,'User5', 'Address5');
177+
-- subscriber now has duplicated value in id field while provider does not
178+
SELECT * FROM pk_users;
179+
id | another_id | a_id | name | address
180+
----+------------+------+-------+-----------------
181+
1 | 11 | 1 | User1 | UpdatedAddress1
182+
2 | 12 | 1 | User2 | UpdatedAddress3
183+
3 | 13 | 2 | User3 | UpdatedAddress4
184+
4 | 14 | 2 | User4 | UpdatedAddress4
185+
4 | 15 | 2 | User5 | Address5
186+
(5 rows)
187+
188+
\c regression
189+
SELECT pglogical.replicate_ddl_command($$
190+
CREATE UNIQUE INDEX id_temp_idx ON public.pk_users (id);
191+
ALTER TABLE public.pk_users DROP CONSTRAINT pk_users_pkey,
192+
ADD CONSTRAINT pk_users_pkey PRIMARY KEY USING INDEX id_temp_idx;
193+
$$);
194+
NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "id_temp_idx" to "pk_users_pkey"
195+
CONTEXT: during execution of queued SQL statement:
196+
CREATE UNIQUE INDEX id_temp_idx ON public.pk_users (id);
197+
ALTER TABLE public.pk_users DROP CONSTRAINT pk_users_pkey,
198+
ADD CONSTRAINT pk_users_pkey PRIMARY KEY USING INDEX id_temp_idx;
199+
200+
replicate_ddl_command
201+
-----------------------
202+
t
203+
(1 row)
204+
205+
\d+ pk_users;
206+
Table "public.pk_users"
207+
Column | Type | Modifiers | Storage | Stats target | Description
208+
------------+---------+-----------+----------+--------------+-------------
209+
id | integer | not null | plain | |
210+
another_id | integer | not null | plain | |
211+
a_id | integer | | plain | |
212+
name | text | | extended | |
213+
address | text | | extended | |
214+
Indexes:
215+
"pk_users_pkey" PRIMARY KEY, btree (id)
216+
217+
SELECT pg_xlog_wait_remote_apply(pg_current_xlog_location(), pid) FROM pg_stat_replication;
218+
pg_xlog_wait_remote_apply
219+
---------------------------
220+
(0 rows)
221+
222+
\c postgres
223+
\d+ pk_users;
224+
Table "public.pk_users"
225+
Column | Type | Modifiers | Storage | Stats target | Description
226+
------------+---------+-----------+----------+--------------+-------------
227+
id | integer | not null | plain | |
228+
another_id | integer | not null | plain | |
229+
a_id | integer | | plain | |
230+
name | text | | extended | |
231+
address | text | | extended | |
232+
Indexes:
233+
"pk_users_pkey" PRIMARY KEY, btree (another_id)
234+
235+
SELECT pglogical.alter_subscription_disable('test_subscription', true);
236+
alter_subscription_disable
237+
----------------------------
238+
t
239+
(1 row)
240+
241+
\c regression
242+
DO $$
243+
BEGIN
244+
FOR i IN 1..100 LOOP
245+
IF (SELECT count(1) FROM pg_replication_slots WHERE active = false) THEN
246+
RETURN;
247+
END IF;
248+
PERFORM pg_sleep(0.1);
249+
END LOOP;
250+
END;
251+
$$;
252+
SELECT data::json->'action' as action, CASE WHEN data::json->>'action' IN ('I', 'D', 'U') THEN json_extract_path(data::json, 'relation') END as data FROM pg_logical_slot_get_changes((SELECT slot_name FROM pg_replication_slots), NULL, 1, 'min_proto_version', '1', 'max_proto_version', '1', 'startup_params_format', '1', 'proto_format', 'json');
253+
action | data
254+
--------+-----------------------
255+
"S" |
256+
"B" |
257+
"I" | ["pglogical","queue"]
258+
"C" |
259+
(4 rows)
260+
261+
SELECT data::json->'action' as action, CASE WHEN data::json->>'action' IN ('I', 'D', 'U') THEN data END as data FROM pg_logical_slot_get_changes((SELECT slot_name FROM pg_replication_slots), NULL, 1, 'min_proto_version', '1', 'max_proto_version', '1', 'startup_params_format', '1', 'proto_format', 'json');
262+
action | data
263+
--------+------
264+
(0 rows)
265+
266+
\c postgres
267+
SELECT pglogical.alter_subscription_enable('test_subscription', true);
268+
alter_subscription_enable
269+
---------------------------
270+
t
271+
(1 row)
272+
273+
DELETE FROM pk_users WHERE id = 4;-- remove the offending entries.
274+
\c regression
275+
DO $$
276+
BEGIN
277+
FOR i IN 1..100 LOOP
278+
IF (SELECT count(1) FROM pg_replication_slots WHERE active = true) THEN
279+
RETURN;
280+
END IF;
281+
PERFORM pg_sleep(0.1);
282+
END LOOP;
283+
END;
284+
$$;
285+
UPDATE pk_users SET address='UpdatedAddress2' WHERE id=2;
286+
SELECT pg_xlog_wait_remote_apply(pg_current_xlog_location(), pid) FROM pg_stat_replication;
287+
pg_xlog_wait_remote_apply
288+
---------------------------
289+
290+
(1 row)
291+
292+
\c postgres
293+
SELECT * FROM pk_users;
294+
id | another_id | a_id | name | address
295+
----+------------+------+-------+-----------------
296+
1 | 11 | 1 | User1 | UpdatedAddress1
297+
3 | 13 | 2 | User3 | UpdatedAddress4
298+
2 | 12 | 1 | User2 | UpdatedAddress2
299+
(3 rows)
300+
301+
\c regression
302+
SELECT pglogical.replicate_ddl_command($$
303+
DROP TABLE public.pk_users CASCADE;
304+
$$);
305+
NOTICE: drop cascades to 1 other object
306+
DETAIL: table public.pk_users in replication set default
307+
CONTEXT: during execution of queued SQL statement:
308+
DROP TABLE public.pk_users CASCADE;
309+
310+
replicate_ddl_command
311+
-----------------------
312+
t
313+
(1 row)
314+

0 commit comments

Comments
 (0)