1-- complex datatype handling 2SELECT * FROM pglogical_regress_variables() 3\gset 4 5\c :provider_dsn 6SELECT pglogical.replicate_ddl_command($$ 7 CREATE TABLE public.tst_one_array ( 8 a INTEGER PRIMARY KEY, 9 b INTEGER[] 10 ); 11 CREATE TABLE public.tst_arrays ( 12 a INTEGER[] PRIMARY KEY, 13 b TEXT[], 14 c FLOAT[], 15 d INTERVAL[] 16 ); 17 18 CREATE TYPE public.tst_enum_t AS ENUM ('a', 'b', 'c', 'd', 'e'); 19 CREATE TABLE public.tst_one_enum ( 20 a INTEGER PRIMARY KEY, 21 b public.tst_enum_t 22 ); 23 CREATE TABLE public.tst_enums ( 24 a public.tst_enum_t PRIMARY KEY, 25 b public.tst_enum_t[] 26 ); 27 28 CREATE TYPE public.tst_comp_basic_t AS (a FLOAT, b TEXT, c INTEGER); 29 CREATE TYPE public.tst_comp_enum_t AS (a FLOAT, b public.tst_enum_t, c INTEGER); 30 CREATE TYPE public.tst_comp_enum_array_t AS (a FLOAT, b public.tst_enum_t[], c INTEGER); 31 CREATE TABLE public.tst_one_comp ( 32 a INTEGER PRIMARY KEY, 33 b public.tst_comp_basic_t 34 ); 35 CREATE TABLE public.tst_comps ( 36 a public.tst_comp_basic_t PRIMARY KEY, 37 b public.tst_comp_basic_t[] 38 ); 39 CREATE TABLE public.tst_comp_enum ( 40 a INTEGER PRIMARY KEY, 41 b public.tst_comp_enum_t 42 ); 43 CREATE TABLE public.tst_comp_enum_array ( 44 a public.tst_comp_enum_t PRIMARY KEY, 45 b public.tst_comp_enum_t[] 46 ); 47 CREATE TABLE public.tst_comp_one_enum_array ( 48 a INTEGER PRIMARY KEY, 49 b public.tst_comp_enum_array_t 50 ); 51 CREATE TABLE public.tst_comp_enum_what ( 52 a public.tst_comp_enum_array_t PRIMARY KEY, 53 b public.tst_comp_enum_array_t[] 54 ); 55 56 CREATE TYPE public.tst_comp_mix_t AS ( 57 a public.tst_comp_basic_t, 58 b public.tst_comp_basic_t[], 59 c public.tst_enum_t, 60 d public.tst_enum_t[] 61 ); 62 CREATE TABLE public.tst_comp_mix_array ( 63 a public.tst_comp_mix_t PRIMARY KEY, 64 b public.tst_comp_mix_t[] 65 ); 66 CREATE TABLE public.tst_range ( 67 a INTEGER PRIMARY KEY, 68 b int4range 69 ); 70 CREATE TABLE public.tst_range_array ( 71 a INTEGER PRIMARY KEY, 72 b TSTZRANGE, 73 c int8range[] 74 ); 75$$); 76 77SELECT * FROM pglogical.replication_set_add_all_tables('default', '{public}'); 78 79SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 80 81-- test_tbl_one_array_col 82INSERT INTO tst_one_array (a, b) VALUES 83 (1, '{1, 2, 3}'), 84 (2, '{2, 3, 1}'), 85 (3, '{3, 2, 1}'), 86 (4, '{4, 3, 2}'), 87 (5, '{5, NULL, 3}'); 88 89-- test_tbl_arrays 90INSERT INTO tst_arrays (a, b, c, d) VALUES 91 ('{1, 2, 3}', '{"a", "b", "c"}', '{1.1, 2.2, 3.3}', '{"1 day", "2 days", "3 days"}'), 92 ('{2, 3, 1}', '{"b", "c", "a"}', '{2.2, 3.3, 1.1}', '{"2 minutes", "3 minutes", "1 minute"}'), 93 ('{3, 1, 2}', '{"c", "a", "b"}', '{3.3, 1.1, 2.2}', '{"3 years", "1 year", "2 years"}'), 94 ('{4, 1, 2}', '{"d", "a", "b"}', '{4.4, 1.1, 2.2}', '{"4 years", "1 year", "2 years"}'), 95 ('{5, NULL, NULL}', '{"e", NULL, "b"}', '{5.5, 1.1, NULL}', '{"5 years", NULL, NULL}'); 96 97-- test_tbl_single_enum 98INSERT INTO tst_one_enum (a, b) VALUES 99 (1, 'a'), 100 (2, 'b'), 101 (3, 'c'), 102 (4, 'd'), 103 (5, NULL); 104 105-- test_tbl_enums 106INSERT INTO tst_enums (a, b) VALUES 107 ('a', '{b, c}'), 108 ('b', '{c, a}'), 109 ('c', '{b, a}'), 110 ('d', '{c, b}'), 111 ('e', '{d, NULL}'); 112 113-- test_tbl_single_composites 114INSERT INTO tst_one_comp (a, b) VALUES 115 (1, ROW(1.0, 'a', 1)), 116 (2, ROW(2.0, 'b', 2)), 117 (3, ROW(3.0, 'c', 3)), 118 (4, ROW(4.0, 'd', 4)), 119 (5, ROW(NULL, NULL, 5)); 120 121-- test_tbl_composites 122INSERT INTO tst_comps (a, b) VALUES 123 (ROW(1.0, 'a', 1), ARRAY[ROW(1, 'a', 1)::tst_comp_basic_t]), 124 (ROW(2.0, 'b', 2), ARRAY[ROW(2, 'b', 2)::tst_comp_basic_t]), 125 (ROW(3.0, 'c', 3), ARRAY[ROW(3, 'c', 3)::tst_comp_basic_t]), 126 (ROW(4.0, 'd', 4), ARRAY[ROW(4, 'd', 3)::tst_comp_basic_t]), 127 (ROW(5.0, 'e', NULL), ARRAY[NULL, ROW(5, NULL, 5)::tst_comp_basic_t]); 128 129-- test_tbl_composite_with_enums 130INSERT INTO tst_comp_enum (a, b) VALUES 131 (1, ROW(1.0, 'a', 1)), 132 (2, ROW(2.0, 'b', 2)), 133 (3, ROW(3.0, 'c', 3)), 134 (4, ROW(4.0, 'd', 4)), 135 (5, ROW(NULL, 'e', NULL)); 136 137-- test_tbl_composite_with_enums_array 138INSERT INTO tst_comp_enum_array (a, b) VALUES 139 (ROW(1.0, 'a', 1), ARRAY[ROW(1, 'a', 1)::tst_comp_enum_t]), 140 (ROW(2.0, 'b', 2), ARRAY[ROW(2, 'b', 2)::tst_comp_enum_t]), 141 (ROW(3.0, 'c', 3), ARRAY[ROW(3, 'c', 3)::tst_comp_enum_t]), 142 (ROW(4.0, 'd', 3), ARRAY[ROW(3, 'd', 3)::tst_comp_enum_t]), 143 (ROW(5.0, 'e', 3), ARRAY[ROW(3, 'e', 3)::tst_comp_enum_t, NULL]); 144 145-- test_tbl_composite_with_single_enums_array_in_composite 146INSERT INTO tst_comp_one_enum_array (a, b) VALUES 147 (1, ROW(1.0, '{a, b, c}', 1)), 148 (2, ROW(2.0, '{a, b, c}', 2)), 149 (3, ROW(3.0, '{a, b, c}', 3)), 150 (4, ROW(4.0, '{c, b, d}', 4)), 151 (5, ROW(5.0, '{NULL, e, NULL}', 5)); 152 153-- test_tbl_composite_with_enums_array_in_composite 154INSERT INTO tst_comp_enum_what (a, b) VALUES 155 (ROW(1.0, '{a, b, c}', 1), ARRAY[ROW(1, '{a, b, c}', 1)::tst_comp_enum_array_t]), 156 (ROW(2.0, '{b, c, a}', 2), ARRAY[ROW(2, '{b, c, a}', 1)::tst_comp_enum_array_t]), 157 (ROW(3.0, '{c, a, b}', 1), ARRAY[ROW(3, '{c, a, b}', 1)::tst_comp_enum_array_t]), 158 (ROW(4.0, '{c, b, d}', 4), ARRAY[ROW(4, '{c, b, d}', 4)::tst_comp_enum_array_t]), 159 (ROW(5.0, '{c, NULL, b}', NULL), ARRAY[ROW(5, '{c, e, b}', 1)::tst_comp_enum_array_t]); 160 161-- test_tbl_mixed_composites 162INSERT INTO tst_comp_mix_array (a, b) VALUES 163 (ROW( 164 ROW(1,'a',1), 165 ARRAY[ROW(1,'a',1)::tst_comp_basic_t, ROW(2,'b',2)::tst_comp_basic_t], 166 'a', 167 '{a,b,NULL,c}'), 168 ARRAY[ 169 ROW( 170 ROW(1,'a',1), 171 ARRAY[ 172 ROW(1,'a',1)::tst_comp_basic_t, 173 ROW(2,'b',2)::tst_comp_basic_t, 174 NULL 175 ], 176 'a', 177 '{a,b,c}' 178 )::tst_comp_mix_t 179 ] 180 ); 181 182-- test_tbl_range 183INSERT INTO tst_range (a, b) VALUES 184 (1, '[1, 10]'), 185 (2, '[2, 20]'), 186 (3, '[3, 30]'), 187 (4, '[4, 40]'), 188 (5, '[5, 50]'); 189 190-- test_tbl_range_array 191INSERT INTO tst_range_array (a, b, c) VALUES 192 (1, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz, 'infinity'), '{"[1,2]", "[10,20]"}'), 193 (2, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '2 days', 'Mon Aug 04 00:00:00 2014 CEST'::timestamptz), '{"[2,3]", "[20,30]"}'), 194 (3, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '3 days', 'Mon Aug 04 00:00:00 2014 CEST'::timestamptz), '{"[3,4]"}'), 195 (4, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '4 days', 'Mon Aug 04 00:00:00 2014 CEST'::timestamptz), '{"[4,5]", NULL, "[40,50]"}'), 196 (5, NULL, NULL); 197 198SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 199\c :subscriber_dsn 200SELECT a, b FROM tst_one_array ORDER BY a; 201SELECT a, b, c, d FROM tst_arrays ORDER BY a; 202SELECT a, b FROM tst_one_enum ORDER BY a; 203SELECT a, b FROM tst_enums ORDER BY a; 204SELECT a, b FROM tst_one_comp ORDER BY a; 205SELECT a, b FROM tst_comps ORDER BY a; 206SELECT a, b FROM tst_comp_enum ORDER BY a; 207SELECT a, b FROM tst_comp_enum_array ORDER BY a; 208SELECT a, b FROM tst_comp_one_enum_array ORDER BY a; 209SELECT a, b FROM tst_comp_enum_what ORDER BY a; 210SELECT a, b FROM tst_comp_mix_array ORDER BY a; 211SELECT a, b FROM tst_range ORDER BY a; 212SELECT a, b, c FROM tst_range_array ORDER BY a; 213 214-- test_tbl_one_array_col 215\c :provider_dsn 216UPDATE tst_one_array SET b = '{4, 5, 6}' WHERE a = 1; 217SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 218\c :subscriber_dsn 219SELECT a, b FROM tst_one_array ORDER BY a; 220\c :provider_dsn 221UPDATE tst_one_array SET b = '{4, 5, 6, 1}' WHERE a > 3; 222SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 223\c :subscriber_dsn 224SELECT a, b FROM tst_one_array ORDER BY a; 225 226\c :provider_dsn 227DELETE FROM tst_one_array WHERE a = 1; 228SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 229\c :subscriber_dsn 230SELECT a, b FROM tst_one_array ORDER BY a; 231\c :provider_dsn 232DELETE FROM tst_one_array WHERE b = '{2, 3, 1}'; 233SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 234\c :subscriber_dsn 235SELECT a, b FROM tst_one_array ORDER BY a; 236\c :provider_dsn 237DELETE FROM tst_one_array WHERE 1 = ANY(b); 238SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 239\c :subscriber_dsn 240SELECT a, b FROM tst_one_array ORDER BY a; 241 242-- test_tbl_arrays 243\c :provider_dsn 244UPDATE tst_arrays SET b = '{"1a", "2b", "3c"}', c = '{1.0, 2.0, 3.0}', d = '{"1 day 1 second", "2 days 2 seconds", "3 days 3 second"}' WHERE a = '{1, 2, 3}'; 245SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 246\c :subscriber_dsn 247SELECT a, b, c, d FROM tst_arrays ORDER BY a; 248\c :provider_dsn 249UPDATE tst_arrays SET b = '{"c", "d", "e"}', c = '{3.0, 4.0, 5.0}', d = '{"3 day 1 second", "4 days 2 seconds", "5 days 3 second"}' WHERE a[1] > 3; 250SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 251\c :subscriber_dsn 252SELECT a, b, c, d FROM tst_arrays ORDER BY a; 253 254\c :provider_dsn 255DELETE FROM tst_arrays WHERE a = '{1, 2, 3}'; 256SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 257\c :subscriber_dsn 258SELECT a, b, c, d FROM tst_arrays ORDER BY a; 259\c :provider_dsn 260DELETE FROM tst_arrays WHERE a[1] = 2; 261SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 262\c :subscriber_dsn 263SELECT a, b, c, d FROM tst_arrays ORDER BY a; 264\c :provider_dsn 265DELETE FROM tst_arrays WHERE b[1] = 'c'; 266SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 267\c :subscriber_dsn 268SELECT a, b, c, d FROM tst_arrays ORDER BY a; 269 270-- test_tbl_single_enum 271\c :provider_dsn 272UPDATE tst_one_enum SET b = 'c' WHERE a = 1; 273SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 274\c :subscriber_dsn 275SELECT a, b FROM tst_one_enum ORDER BY a; 276\c :provider_dsn 277UPDATE tst_one_enum SET b = NULL WHERE a > 3; 278SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 279\c :subscriber_dsn 280SELECT a, b FROM tst_one_enum ORDER BY a; 281 282\c :provider_dsn 283DELETE FROM tst_one_enum WHERE a = 1; 284SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 285\c :subscriber_dsn 286SELECT a, b FROM tst_one_enum ORDER BY a; 287\c :provider_dsn 288DELETE FROM tst_one_enum WHERE b = 'b'; 289SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 290\c :subscriber_dsn 291SELECT a, b FROM tst_one_enum ORDER BY a; 292 293-- test_tbl_enums 294\c :provider_dsn 295UPDATE tst_enums SET b = '{e, NULL}' WHERE a = 'a'; 296SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 297\c :subscriber_dsn 298SELECT a, b FROM tst_enums; 299\c :provider_dsn 300UPDATE tst_enums SET b = '{e, d}' WHERE a > 'c'; 301SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 302\c :subscriber_dsn 303SELECT a, b FROM tst_enums; 304 305\c :provider_dsn 306DELETE FROM tst_enums WHERE a = 'a'; 307SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 308\c :subscriber_dsn 309SELECT a, b FROM tst_enums; 310\c :provider_dsn 311DELETE FROM tst_enums WHERE 'c' = ANY(b); 312SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 313\c :subscriber_dsn 314SELECT a, b FROM tst_enums; 315\c :provider_dsn 316DELETE FROM tst_enums WHERE b[1] = 'b'; 317SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 318\c :subscriber_dsn 319SELECT a, b FROM tst_enums; 320 321-- test_tbl_single_composites 322\c :provider_dsn 323UPDATE tst_one_comp SET b = ROW(1.0, 'A', 1) WHERE a = 1; 324SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 325\c :subscriber_dsn 326SELECT a, b from tst_one_comp ORDER BY a; 327\c :provider_dsn 328UPDATE tst_one_comp SET b = ROW(NULL, 'x', -1) WHERE a > 3; 329SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 330\c :subscriber_dsn 331SELECT a, b from tst_one_comp ORDER BY a; 332 333\c :provider_dsn 334DELETE FROM tst_one_comp WHERE a = 1; 335SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 336\c :subscriber_dsn 337SELECT a, b from tst_one_comp ORDER BY a; 338\c :provider_dsn 339DELETE FROM tst_one_comp WHERE (b).a = 2.0; 340SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 341\c :subscriber_dsn 342SELECT a, b from tst_one_comp ORDER BY a; 343 344-- test_tbl_composites 345\c :provider_dsn 346UPDATE tst_comps SET b = ARRAY[ROW(9, 'x', -1)::tst_comp_basic_t] WHERE (a).a = 1.0; 347SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 348\c :subscriber_dsn 349SELECT a, b from tst_comps ORDER BY a; 350\c :provider_dsn 351UPDATE tst_comps SET b = ARRAY[NULL, ROW(9, 'x', NULL)::tst_comp_basic_t] WHERE (a).a > 3.9; 352SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 353\c :subscriber_dsn 354SELECT a, b from tst_comps ORDER BY a; 355 356\c :provider_dsn 357DELETE FROM tst_comps WHERE (a).b = 'a'; 358SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 359\c :subscriber_dsn 360SELECT a, b FROM tst_comps ORDER BY a; 361\c :provider_dsn 362DELETE FROM tst_comps WHERE (b[1]).a = 2.0; 363SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 364\c :subscriber_dsn 365SELECT a, b FROM tst_comps ORDER BY a; 366\c :provider_dsn 367DELETE FROM tst_comps WHERE ROW(3, 'c', 3)::tst_comp_basic_t = ANY(b); 368SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 369\c :subscriber_dsn 370SELECT a, b FROM tst_comps ORDER BY a; 371 372-- test_tbl_composite_with_enums 373\c :provider_dsn 374UPDATE tst_comp_enum SET b = ROW(1.0, NULL, NULL) WHERE a = 1; 375SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 376\c :subscriber_dsn 377SELECT a, b from tst_comp_enum ORDER BY a; 378\c :provider_dsn 379UPDATE tst_comp_enum SET b = ROW(4.0, 'd', 44) WHERE a > 3; 380SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 381\c :subscriber_dsn 382SELECT a, b from tst_comp_enum ORDER BY a; 383 384\c :provider_dsn 385DELETE FROM tst_comp_enum WHERE a = 1; 386SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 387\c :subscriber_dsn 388SELECT a, b FROM tst_comp_enum ORDER BY a; 389\c :provider_dsn 390DELETE FROM tst_comp_enum WHERE (b).a = 2.0; 391SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 392\c :subscriber_dsn 393SELECT a, b FROM tst_comp_enum ORDER BY a; 394 395-- test_tbl_composite_with_enums_array 396\c :provider_dsn 397UPDATE tst_comp_enum_array SET b = ARRAY[NULL, ROW(3, 'd', 3)::tst_comp_enum_t] WHERE a = ROW(1.0, 'a', 1)::tst_comp_enum_t; 398SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 399\c :subscriber_dsn 400SELECT a, b from tst_comp_enum_array ORDER BY a; 401\c :provider_dsn 402UPDATE tst_comp_enum_array SET b = ARRAY[ROW(1, 'a', 1)::tst_comp_enum_t, ROW(2, 'b', 2)::tst_comp_enum_t] WHERE (a).a > 3; 403SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 404\c :subscriber_dsn 405SELECT a, b from tst_comp_enum_array ORDER BY a; 406 407\c :provider_dsn 408DELETE FROM tst_comp_enum_array WHERE a = ROW(1.0, 'a', 1)::tst_comp_enum_t; 409SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 410\c :subscriber_dsn 411SELECT a, b FROM tst_comp_enum_array ORDER BY a; 412\c :provider_dsn 413DELETE FROM tst_comp_enum_array WHERE (b[1]).b = 'b'; 414SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 415\c :subscriber_dsn 416SELECT a, b FROM tst_comp_enum_array ORDER BY a; 417\c :provider_dsn 418DELETE FROM tst_comp_enum_array WHERE ROW(3, 'c', 3)::tst_comp_enum_t = ANY(b); 419SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 420\c :subscriber_dsn 421SELECT a, b FROM tst_comp_enum_array ORDER BY a; 422 423-- test_tbl_composite_with_single_enums_array_in_composite 424\c :provider_dsn 425UPDATE tst_comp_one_enum_array SET b = ROW(1.0, '{a, e, c}', NULL) WHERE a = 1; 426SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 427\c :subscriber_dsn 428SELECT a, b from tst_comp_one_enum_array ORDER BY a; 429\c :provider_dsn 430UPDATE tst_comp_one_enum_array SET b = ROW(4.0, '{c, b, d}', 4) WHERE a > 3; 431SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 432\c :subscriber_dsn 433SELECT a, b from tst_comp_one_enum_array ORDER BY a; 434 435\c :provider_dsn 436DELETE FROM tst_comp_one_enum_array WHERE a = 1; 437SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 438\c :subscriber_dsn 439SELECT a, b FROM tst_comp_one_enum_array ORDER BY a; 440\c :provider_dsn 441DELETE FROM tst_comp_one_enum_array WHERE (b).c = 2; 442SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 443\c :subscriber_dsn 444SELECT a, b FROM tst_comp_one_enum_array ORDER BY a; 445\c :provider_dsn 446DELETE FROM tst_comp_one_enum_array WHERE 'a' = ANY((b).b); 447SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 448\c :subscriber_dsn 449SELECT a, b FROM tst_comp_one_enum_array ORDER BY a; 450 451-- test_tbl_composite_with_enums_array_in_composite 452\c :provider_dsn 453UPDATE tst_comp_enum_what SET b = ARRAY[NULL, ROW(1, '{a, b, c}', 1)::tst_comp_enum_array_t, ROW(NULL, '{a, e, c}', 2)::tst_comp_enum_array_t] WHERE (a).a = 1; 454SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 455\c :subscriber_dsn 456SELECT a, b from tst_comp_enum_what ORDER BY a; 457\c :provider_dsn 458UPDATE tst_comp_enum_what SET b = ARRAY[ROW(5, '{a, b, c}', 5)::tst_comp_enum_array_t] WHERE (a).a > 3; 459SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 460\c :subscriber_dsn 461SELECT a, b from tst_comp_enum_what ORDER BY a; 462 463\c :provider_dsn 464DELETE FROM tst_comp_enum_what WHERE (a).a = 1; 465SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 466\c :subscriber_dsn 467SELECT a, b FROM tst_comp_enum_what ORDER BY a; 468\c :provider_dsn 469DELETE FROM tst_comp_enum_what WHERE (b[1]).a = 2; 470SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 471\c :subscriber_dsn 472SELECT a, b FROM tst_comp_enum_what ORDER BY a; 473\c :provider_dsn 474DELETE FROM tst_comp_enum_what WHERE (b[1]).b = '{c, a, b}'; 475SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 476\c :subscriber_dsn 477SELECT a, b FROM tst_comp_enum_what ORDER BY a; 478 479-- test_tbl_mixed_composites 480\c :provider_dsn 481UPDATE tst_comp_mix_array SET b[2] = NULL WHERE ((a).a).a = 1; 482SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 483\c :subscriber_dsn 484SELECT a, b FROM tst_comp_mix_array ORDER BY a; 485 486\c :provider_dsn 487DELETE FROM tst_comp_mix_array WHERE ((a).a).a = 1; 488SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 489\c :subscriber_dsn 490SELECT a, b FROM tst_comp_mix_array ORDER BY a; 491 492-- test_tbl_range 493\c :provider_dsn 494UPDATE tst_range SET b = '[100, 1000]' WHERE a = 1; 495SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 496\c :subscriber_dsn 497SELECT a, b FROM tst_range ORDER BY a; 498\c :provider_dsn 499UPDATE tst_range SET b = '(1, 90)' WHERE a > 3; 500SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 501\c :subscriber_dsn 502SELECT a, b FROM tst_range ORDER BY a; 503 504\c :provider_dsn 505DELETE FROM tst_range WHERE a = 1; 506SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 507\c :subscriber_dsn 508SELECT a, b FROM tst_range ORDER BY a; 509\c :provider_dsn 510DELETE FROM tst_range WHERE b = '[2, 20]'; 511SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 512\c :subscriber_dsn 513SELECT a, b FROM tst_range ORDER BY a; 514\c :provider_dsn 515DELETE FROM tst_range WHERE '[10,20]' && b; 516SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 517\c :subscriber_dsn 518SELECT a, b FROM tst_range ORDER BY a; 519 520-- test_tbl_range_array 521\c :provider_dsn 522UPDATE tst_range_array SET c = '{"[100, 1000]"}' WHERE a = 1; 523SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 524\c :subscriber_dsn 525SELECT a, b, c FROM tst_range_array ORDER BY a; 526\c :provider_dsn 527UPDATE tst_range_array SET b = tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz, 'infinity'), c = '{NULL, "[11,9999999]"}' WHERE a > 3; 528SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 529\c :subscriber_dsn 530SELECT a, b, c FROM tst_range_array ORDER BY a; 531 532\c :provider_dsn 533DELETE FROM tst_range_array WHERE a = 1; 534SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 535\c :subscriber_dsn 536SELECT a, b, c FROM tst_range_array ORDER BY a; 537\c :provider_dsn 538DELETE FROM tst_range_array WHERE b = tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '2 days', 'Mon Aug 04 00:00:00 2014 CEST'::timestamptz); 539SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 540\c :subscriber_dsn 541SELECT a, b, c FROM tst_range_array ORDER BY a; 542\c :provider_dsn 543DELETE FROM tst_range_array WHERE tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz, 'Mon Aug 05 00:00:00 2014 CEST'::timestamptz) && b; 544SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 545\c :subscriber_dsn 546SELECT a, b, c FROM tst_range_array ORDER BY a; 547 548\c :provider_dsn 549-- Verify that swap_relation_files(...) breaks replication 550-- as invoked by CLUSTER, VACUUM FULL, or REFRESH MATERIALIZED VIEW 551VACUUM FULL tst_one_array; 552 553SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 554 555 556\c :provider_dsn 557\set VERBOSITY terse 558SELECT pglogical.replicate_ddl_command($$ 559 DROP TABLE public.tst_one_array CASCADE; 560 DROP TABLE public.tst_arrays CASCADE; 561 DROP TABLE public.tst_one_enum CASCADE; 562 DROP TABLE public.tst_enums CASCADE; 563 DROP TABLE public.tst_one_comp CASCADE; 564 DROP TABLE public.tst_comps CASCADE; 565 DROP TABLE public.tst_comp_enum CASCADE; 566 DROP TABLE public.tst_comp_enum_array CASCADE; 567 DROP TABLE public.tst_comp_one_enum_array CASCADE; 568 DROP TABLE public.tst_comp_enum_what CASCADE; 569 DROP TABLE public.tst_comp_mix_array CASCADE; 570 DROP TABLE public.tst_range CASCADE; 571 DROP TABLE public.tst_range_array CASCADE; 572 573 DROP TYPE public.tst_comp_mix_t; 574 DROP TYPE public.tst_comp_enum_array_t; 575 DROP TYPE public.tst_comp_enum_t; 576 DROP TYPE public.tst_comp_basic_t; 577 DROP TYPE public.tst_enum_t; 578$$); 579