granicus.if.org Git - postgresql/blob - contrib/test_decoding/sql/ddl.sql

   1 -- predictability
   2 SET synchronous_commit = on;
   3
   4 SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
   5 -- fail because of an already existing slot
   6 SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
   7 -- fail because of an invalid name
   8 SELECT 'init' FROM pg_create_logical_replication_slot('Invalid Name', 'test_decoding');
   9
  10 -- fail twice because of an invalid parameter values
  11 SELECT 'init' FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', 'frakbar');
  12 SELECT 'init' FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'nonexistant-option', 'frakbar');
  13 SELECT 'init' FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', 'frakbar');
  14
  15 -- succeed once
  16 SELECT pg_drop_replication_slot('regression_slot');
  17 -- fail
  18 SELECT pg_drop_replication_slot('regression_slot');
  19
  20 -- check that we're detecting a streaming rep slot used for logical decoding
  21 SELECT 'init' FROM pg_create_physical_replication_slot('repl');
  22 SELECT data FROM pg_logical_slot_get_changes('repl', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
  23 SELECT pg_drop_replication_slot('repl');
  24
  25
  26 SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
  27
  28 /* check whether status function reports us, only reproduceable columns */
  29 SELECT slot_name, plugin, slot_type, active,
  30     NOT catalog_xmin IS NULL AS catalog_xmin_set,
  31     xmin IS NULl  AS data_xmin_not_set,
  32     pg_xlog_location_diff(restart_lsn, '0/01000000') > 0 AS some_wal
  33 FROM pg_replication_slots;
  34
  35 /*
  36  * Check that changes are handled correctly when interleaved with ddl
  37  */
  38 CREATE TABLE replication_example(id SERIAL PRIMARY KEY, somedata int, text varchar(120));
  39 BEGIN;
  40 INSERT INTO replication_example(somedata, text) VALUES (1, 1);
  41 INSERT INTO replication_example(somedata, text) VALUES (1, 2);
  42 COMMIT;
  43
  44 ALTER TABLE replication_example ADD COLUMN bar int;
  45
  46 INSERT INTO replication_example(somedata, text, bar) VALUES (2, 1, 4);
  47
  48 BEGIN;
  49 INSERT INTO replication_example(somedata, text, bar) VALUES (2, 2, 4);
  50 INSERT INTO replication_example(somedata, text, bar) VALUES (2, 3, 4);
  51 INSERT INTO replication_example(somedata, text, bar) VALUES (2, 4, NULL);
  52 COMMIT;
  53
  54 ALTER TABLE replication_example DROP COLUMN bar;
  55 INSERT INTO replication_example(somedata, text) VALUES (3, 1);
  56
  57 BEGIN;
  58 INSERT INTO replication_example(somedata, text) VALUES (3, 2);
  59 INSERT INTO replication_example(somedata, text) VALUES (3, 3);
  60 COMMIT;
  61
  62 ALTER TABLE replication_example RENAME COLUMN text TO somenum;
  63
  64 INSERT INTO replication_example(somedata, somenum) VALUES (4, 1);
  65
  66 -- collect all changes
  67 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
  68
  69 ALTER TABLE replication_example ALTER COLUMN somenum TYPE int4 USING (somenum::int4);
  70 -- throw away changes, they contain oids
  71 SELECT count(data) FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
  72
  73 INSERT INTO replication_example(somedata, somenum) VALUES (5, 1);
  74
  75 BEGIN;
  76 INSERT INTO replication_example(somedata, somenum) VALUES (6, 1);
  77 ALTER TABLE replication_example ADD COLUMN zaphod1 int;
  78 INSERT INTO replication_example(somedata, somenum, zaphod1) VALUES (6, 2, 1);
  79 ALTER TABLE replication_example ADD COLUMN zaphod2 int;
  80 INSERT INTO replication_example(somedata, somenum, zaphod2) VALUES (6, 3, 1);
  81 INSERT INTO replication_example(somedata, somenum, zaphod1) VALUES (6, 4, 2);
  82 COMMIT;
  83
  84 -- show changes
  85 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
  86
  87 -- ON CONFLICT DO UPDATE support
  88 BEGIN;
  89 INSERT INTO replication_example(id, somedata, somenum) SELECT i, i, i FROM generate_series(-15, 15) i
  90   ON CONFLICT (id) DO UPDATE SET somenum = excluded.somenum + 1;
  91 COMMIT;
  92
  93 /* display results */
  94 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
  95
  96 -- hide changes bc of oid visible in full table rewrites
  97 CREATE TABLE tr_unique(id2 serial unique NOT NULL, data int);
  98 INSERT INTO tr_unique(data) VALUES(10);
  99 ALTER TABLE tr_unique RENAME TO tr_pkey;
 100 ALTER TABLE tr_pkey ADD COLUMN id serial primary key;
 101 SELECT count(data) FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 102
 103 INSERT INTO tr_pkey(data) VALUES(1);
 104 --show deletion with primary key
 105 DELETE FROM tr_pkey;
 106
 107 /* display results */
 108 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 109
 110 /*
 111  * check that disk spooling works
 112  */
 113 BEGIN;
 114 CREATE TABLE tr_etoomuch (id serial primary key, data int);
 115 INSERT INTO tr_etoomuch(data) SELECT g.i FROM generate_series(1, 10234) g(i);
 116 DELETE FROM tr_etoomuch WHERE id < 5000;
 117 UPDATE tr_etoomuch SET data = - data WHERE id > 5000;
 118 COMMIT;
 119
 120 /* display results, but hide most of the output */
 121 SELECT count(*), min(data), max(data)
 122 FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1')
 123 GROUP BY substring(data, 1, 24)
 124 ORDER BY 1,2;
 125
 126 -- check that a large, spooled, upsert works
 127 INSERT INTO tr_etoomuch (id, data)
 128 SELECT g.i, -g.i FROM generate_series(8000, 12000) g(i)
 129 ON CONFLICT(id) DO UPDATE SET data = EXCLUDED.data;
 130
 131 SELECT substring(data, 1, 29), count(*)
 132 FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1')
 133 GROUP BY 1
 134 ORDER BY min(location - '0/0');
 135
 136 /*
 137  * check whether we decode subtransactions correctly in relation with each
 138  * other
 139  */
 140 CREATE TABLE tr_sub (id serial primary key, path text);
 141
 142 -- toplevel, subtxn, toplevel, subtxn, subtxn
 143 BEGIN;
 144 INSERT INTO tr_sub(path) VALUES ('1-top-#1');
 145
 146 SAVEPOINT a;
 147 INSERT INTO tr_sub(path) VALUES ('1-top-1-#1');
 148 INSERT INTO tr_sub(path) VALUES ('1-top-1-#2');
 149 RELEASE SAVEPOINT a;
 150
 151 SAVEPOINT b;
 152 SAVEPOINT c;
 153 INSERT INTO tr_sub(path) VALUES ('1-top-2-1-#1');
 154 INSERT INTO tr_sub(path) VALUES ('1-top-2-1-#2');
 155 RELEASE SAVEPOINT c;
 156 INSERT INTO tr_sub(path) VALUES ('1-top-2-#1');
 157 RELEASE SAVEPOINT b;
 158 COMMIT;
 159
 160 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 161
 162 -- check that we handle xlog assignments correctly
 163 BEGIN;
 164 -- nest 80 subtxns
 165 SAVEPOINT subtop;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 166 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 167 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 168 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 169 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 170 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 171 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 172 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 173 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 174 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 175 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 176 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 177 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 178 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 179 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 180 SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;SAVEPOINT a;
 181 -- assign xid by inserting
 182 INSERT INTO tr_sub(path) VALUES ('2-top-1...--#1');
 183 INSERT INTO tr_sub(path) VALUES ('2-top-1...--#2');
 184 INSERT INTO tr_sub(path) VALUES ('2-top-1...--#3');
 185 RELEASE SAVEPOINT subtop;
 186 INSERT INTO tr_sub(path) VALUES ('2-top-#1');
 187 COMMIT;
 188
 189 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 190
 191 -- make sure rollbacked subtransactions aren't decoded
 192 BEGIN;
 193 INSERT INTO tr_sub(path) VALUES ('3-top-2-#1');
 194 SAVEPOINT a;
 195 INSERT INTO tr_sub(path) VALUES ('3-top-2-1-#1');
 196 SAVEPOINT b;
 197 INSERT INTO tr_sub(path) VALUES ('3-top-2-2-#1');
 198 ROLLBACK TO SAVEPOINT b;
 199 INSERT INTO tr_sub(path) VALUES ('3-top-2-#2');
 200 COMMIT;
 201
 202 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 203
 204 -- test whether a known, but not yet logged toplevel xact, followed by a
 205 -- subxact commit is handled correctly
 206 BEGIN;
 207 SELECT txid_current() != 0; -- so no fixed xid apears in the outfile
 208 SAVEPOINT a;
 209 INSERT INTO tr_sub(path) VALUES ('4-top-1-#1');
 210 RELEASE SAVEPOINT a;
 211 COMMIT;
 212
 213 -- test whether a change in a subtransaction, in an unknown toplevel
 214 -- xact is handled correctly.
 215 BEGIN;
 216 SAVEPOINT a;
 217 INSERT INTO tr_sub(path) VALUES ('5-top-1-#1');
 218 COMMIT;
 219
 220
 221 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 222
 223
 224 /*
 225  * Check whether treating a table as a catalog table works somewhat
 226  */
 227 CREATE TABLE replication_metadata (
 228     id serial primary key,
 229     relation name NOT NULL,
 230     options text[]
 231 )
 232 WITH (user_catalog_table = true)
 233 ;
 234 \d+ replication_metadata
 235
 236 INSERT INTO replication_metadata(relation, options)
 237 VALUES ('foo', ARRAY['a', 'b']);
 238
 239 ALTER TABLE replication_metadata RESET (user_catalog_table);
 240 \d+ replication_metadata
 241
 242 INSERT INTO replication_metadata(relation, options)
 243 VALUES ('bar', ARRAY['a', 'b']);
 244
 245 ALTER TABLE replication_metadata SET (user_catalog_table = true);
 246 \d+ replication_metadata
 247
 248 INSERT INTO replication_metadata(relation, options)
 249 VALUES ('blub', NULL);
 250
 251 -- make sure rewrites don't work
 252 ALTER TABLE replication_metadata ADD COLUMN rewritemeornot int;
 253 ALTER TABLE replication_metadata ALTER COLUMN rewritemeornot TYPE text;
 254
 255 ALTER TABLE replication_metadata SET (user_catalog_table = false);
 256 \d+ replication_metadata
 257
 258 INSERT INTO replication_metadata(relation, options)
 259 VALUES ('zaphod', NULL);
 260
 261 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 262
 263 /*
 264  * check whether we handle updates/deletes correct with & without a pkey
 265  */
 266
 267 /* we should handle the case without a key at all more gracefully */
 268 CREATE TABLE table_without_key(id serial, data int);
 269 INSERT INTO table_without_key(data) VALUES(1),(2);
 270 DELETE FROM table_without_key WHERE data = 1;
 271 -- won't log old keys
 272 UPDATE table_without_key SET data = 3 WHERE data = 2;
 273 UPDATE table_without_key SET id = -id;
 274 UPDATE table_without_key SET id = -id;
 275 -- should log the full old row now
 276 ALTER TABLE table_without_key REPLICA IDENTITY FULL;
 277 UPDATE table_without_key SET data = 3 WHERE data = 2;
 278 UPDATE table_without_key SET id = -id;
 279 UPDATE table_without_key SET id = -id;
 280 -- ensure that FULL correctly deals with new columns
 281 ALTER TABLE table_without_key ADD COLUMN new_column text;
 282 UPDATE table_without_key SET id = -id;
 283 UPDATE table_without_key SET id = -id, new_column = 'someval';
 284 DELETE FROM table_without_key WHERE data = 3;
 285
 286 CREATE TABLE table_with_pkey(id serial primary key, data int);
 287 INSERT INTO table_with_pkey(data) VALUES(1), (2);
 288 DELETE FROM table_with_pkey WHERE data = 1;
 289 -- should log the old pkey
 290 UPDATE table_with_pkey SET data = 3 WHERE data = 2;
 291 UPDATE table_with_pkey SET id = -id;
 292 UPDATE table_with_pkey SET id = -id;
 293 -- check that we log nothing despite having a pkey
 294 ALTER TABLE table_without_key REPLICA IDENTITY NOTHING;
 295 UPDATE table_with_pkey SET id = -id;
 296 -- check that we log everything despite having a pkey
 297 ALTER TABLE table_without_key REPLICA IDENTITY FULL;
 298 UPDATE table_with_pkey SET id = -id;
 299 DELETE FROM table_with_pkey WHERE data = 3;
 300
 301 CREATE TABLE table_with_unique_not_null(id serial unique, data int);
 302 ALTER TABLE table_with_unique_not_null ALTER COLUMN id SET NOT NULL; --already set
 303 -- won't log anything, replica identity not setup
 304 INSERT INTO table_with_unique_not_null(data) VALUES(1), (2);
 305 DELETE FROM table_with_unique_not_null WHERE data = 1;
 306 UPDATE table_with_unique_not_null SET data = 3 WHERE data = 2;
 307 UPDATE table_with_unique_not_null SET id = -id;
 308 UPDATE table_with_unique_not_null SET id = -id;
 309 DELETE FROM table_with_unique_not_null WHERE data = 3;
 310 -- should log old key
 311 ALTER TABLE table_with_unique_not_null REPLICA IDENTITY USING INDEX table_with_unique_not_null_id_key;
 312 INSERT INTO table_with_unique_not_null(data) VALUES(1), (2);
 313 DELETE FROM table_with_unique_not_null WHERE data = 1;
 314 UPDATE table_with_unique_not_null SET data = 3 WHERE data = 2;
 315 UPDATE table_with_unique_not_null SET id = -id;
 316 UPDATE table_with_unique_not_null SET id = -id;
 317 DELETE FROM table_with_unique_not_null WHERE data = 3;
 318
 319 -- check toast support
 320 BEGIN;
 321 CREATE SEQUENCE toasttable_rand_seq START 79 INCREMENT 1499; -- portable "random"
 322 CREATE TABLE toasttable(
 323        id serial primary key,
 324        toasted_col1 text,
 325        rand1 float8 DEFAULT nextval('toasttable_rand_seq'),
 326        toasted_col2 text,
 327        rand2 float8 DEFAULT nextval('toasttable_rand_seq')
 328        );
 329 COMMIT;
 330 -- uncompressed external toast data
 331 INSERT INTO toasttable(toasted_col1) SELECT string_agg(g.i::text, '') FROM generate_series(1, 2000) g(i);
 332
 333 -- compressed external toast data
 334 INSERT INTO toasttable(toasted_col2) SELECT repeat(string_agg(to_char(g.i, 'FM0000'), ''), 50) FROM generate_series(1, 500) g(i);
 335
 336 -- update of existing column
 337 UPDATE toasttable
 338     SET toasted_col1 = (SELECT string_agg(g.i::text, '') FROM generate_series(1, 2000) g(i))
 339 WHERE id = 1;
 340
 341 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 342
 343 INSERT INTO toasttable(toasted_col1) SELECT string_agg(g.i::text, '') FROM generate_series(1, 2000) g(i);
 344
 345 -- update of second column, first column unchanged
 346 UPDATE toasttable
 347     SET toasted_col2 = (SELECT string_agg(g.i::text, '') FROM generate_series(1, 2000) g(i))
 348 WHERE id = 1;
 349
 350 -- make sure we decode correctly even if the toast table is gone
 351 DROP TABLE toasttable;
 352
 353 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 354
 355 -- done, free logical replication slot
 356 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
 357
 358 SELECT pg_drop_replication_slot('regression_slot');
 359
 360 /* check that the slot is gone */
 361 SELECT * FROM pg_replication_slots;