begriffs open source - pg_scribe/blob - wal2sql/expected/basic.out

   1 --
   2 -- Basic tests
   3 --
   4 -- Create a replication slot
   5 SELECT slot_name FROM pg_create_logical_replication_slot('custom_slot', 'wal2sql');
   6   slot_name
   7 -------------
   8  custom_slot
   9 (1 row)
  10
  11 -- Install the wal2sql extension to enable automatic DDL capture
  12 CREATE EXTENSION wal2sql;
  13 -- DEFAULT case with PRIMARY KEY
  14 CREATE TABLE aa (a int primary key, b text NOT NULL);
  15 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
  16 -- Update of Non-selective column
  17 UPDATE aa SET b = 'cc' WHERE a = 1;
  18 -- Update of only selective column
  19 UPDATE aa SET a = 3 WHERE a = 1;
  20 -- Update of both columns
  21 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
  22 DELETE FROM aa WHERE a = 4;
  23 -- Have a look at changes with different modes.
  24 -- In the second call changes are consumed to not impact the next cases.
  25 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
  26                          data
  27 -------------------------------------------------------
  28  CREATE TABLE aa (a int primary key, b text NOT NULL);
  29  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
  30  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
  31  UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1;
  32  UPDATE public.aa SET a = 3, b = 'cc' WHERE a = 1;
  33  UPDATE public.aa SET a = 4, b = 'dd' WHERE a = 2;
  34  DELETE FROM public.aa WHERE a = 4;
  35 (7 rows)
  36
  37 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
  38                          data
  39 -------------------------------------------------------
  40  BEGIN;
  41  COMMIT;
  42  BEGIN;
  43  CREATE TABLE aa (a int primary key, b text NOT NULL);
  44  COMMIT;
  45  BEGIN;
  46  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
  47  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
  48  COMMIT;
  49  BEGIN;
  50  UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1;
  51  COMMIT;
  52  BEGIN;
  53  UPDATE public.aa SET a = 3, b = 'cc' WHERE a = 1;
  54  COMMIT;
  55  BEGIN;
  56  UPDATE public.aa SET a = 4, b = 'dd' WHERE a = 2;
  57  COMMIT;
  58  BEGIN;
  59  DELETE FROM public.aa WHERE a = 4;
  60  COMMIT;
  61 (21 rows)
  62
  63 DROP TABLE aa;
  64 -- DEFAULT case without PRIMARY KEY
  65 CREATE TABLE aa (a int, b text NOT NULL);
  66 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
  67 -- Update of Non-selective column
  68 UPDATE aa SET b = 'cc' WHERE a = 1;
  69 -- Update of only selective column
  70 UPDATE aa SET a = 3 WHERE a = 1;
  71 -- Update of both columns
  72 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
  73 DELETE FROM aa WHERE a = 4;
  74 -- Have a look at changes with different modes.
  75 -- In the second call changes are consumed to not impact the next cases.
  76 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
  77                       data
  78 ------------------------------------------------
  79  DROP TABLE aa;
  80  CREATE TABLE aa (a int, b text NOT NULL);
  81  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
  82  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
  83 (4 rows)
  84
  85 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
  86                       data
  87 ------------------------------------------------
  88  BEGIN;
  89  DROP TABLE aa;
  90  COMMIT;
  91  BEGIN;
  92  CREATE TABLE aa (a int, b text NOT NULL);
  93  COMMIT;
  94  BEGIN;
  95  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
  96  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
  97  COMMIT;
  98  BEGIN;
  99  COMMIT;
 100  BEGIN;
 101  COMMIT;
 102  BEGIN;
 103  COMMIT;
 104  BEGIN;
 105  COMMIT;
 106 (18 rows)
 107
 108 DROP TABLE aa;
 109 -- INDEX case
 110 CREATE TABLE aa (a int NOT NULL, b text);
 111 CREATE UNIQUE INDEX aai ON aa(a);
 112 ALTER TABLE aa REPLICA IDENTITY USING INDEX aai;
 113 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
 114 -- Update of Non-selective column
 115 UPDATE aa SET b = 'cc' WHERE a = 1;
 116 -- Update of only selective column
 117 UPDATE aa SET a = 3 WHERE a = 1;
 118 -- Update of both columns
 119 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
 120 DELETE FROM aa WHERE a = 4;
 121 -- Have a look at changes with different modes
 122 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 123                        data
 124 ---------------------------------------------------
 125  DROP TABLE aa;
 126  CREATE TABLE aa (a int NOT NULL, b text);
 127  CREATE UNIQUE INDEX aai ON aa(a);
 128  ALTER TABLE aa REPLICA IDENTITY USING INDEX aai;
 129  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
 130  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
 131  UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1;
 132  UPDATE public.aa SET a = 3, b = 'cc' WHERE a = 1;
 133  UPDATE public.aa SET a = 4, b = 'dd' WHERE a = 2;
 134  DELETE FROM public.aa WHERE a = 4;
 135 (10 rows)
 136
 137 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
 138                        data
 139 ---------------------------------------------------
 140  BEGIN;
 141  DROP TABLE aa;
 142  COMMIT;
 143  BEGIN;
 144  CREATE TABLE aa (a int NOT NULL, b text);
 145  COMMIT;
 146  BEGIN;
 147  CREATE UNIQUE INDEX aai ON aa(a);
 148  COMMIT;
 149  BEGIN;
 150  ALTER TABLE aa REPLICA IDENTITY USING INDEX aai;
 151  COMMIT;
 152  BEGIN;
 153  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
 154  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
 155  COMMIT;
 156  BEGIN;
 157  UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1;
 158  COMMIT;
 159  BEGIN;
 160  UPDATE public.aa SET a = 3, b = 'cc' WHERE a = 1;
 161  COMMIT;
 162  BEGIN;
 163  UPDATE public.aa SET a = 4, b = 'dd' WHERE a = 2;
 164  COMMIT;
 165  BEGIN;
 166  DELETE FROM public.aa WHERE a = 4;
 167  COMMIT;
 168 (28 rows)
 169
 170 DROP TABLE aa;
 171 -- INDEX case using a second column
 172 CREATE TABLE aa (b text, a int NOT NULL);
 173 CREATE UNIQUE INDEX aai ON aa(a);
 174 ALTER TABLE aa REPLICA IDENTITY USING INDEX aai;
 175 INSERT INTO aa VALUES ('aa', 1), ('bb', 2);
 176 -- Update of Non-selective column
 177 UPDATE aa SET b = 'cc' WHERE a = 1;
 178 -- Update of only selective column
 179 UPDATE aa SET a = 3 WHERE a = 1;
 180 -- Update of both columns
 181 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
 182 DELETE FROM aa WHERE a = 4;
 183 -- Have a look at changes with different modes
 184 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 185                        data
 186 ---------------------------------------------------
 187  DROP TABLE aa;
 188  CREATE TABLE aa (b text, a int NOT NULL);
 189  CREATE UNIQUE INDEX aai ON aa(a);
 190  ALTER TABLE aa REPLICA IDENTITY USING INDEX aai;
 191  INSERT INTO public.aa (b, a) VALUES ('aa', 1);
 192  INSERT INTO public.aa (b, a) VALUES ('bb', 2);
 193  UPDATE public.aa SET b = 'cc', a = 1 WHERE a = 1;
 194  UPDATE public.aa SET b = 'cc', a = 3 WHERE a = 1;
 195  UPDATE public.aa SET b = 'dd', a = 4 WHERE a = 2;
 196  DELETE FROM public.aa WHERE a = 4;
 197 (10 rows)
 198
 199 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
 200                        data
 201 ---------------------------------------------------
 202  BEGIN;
 203  DROP TABLE aa;
 204  COMMIT;
 205  BEGIN;
 206  CREATE TABLE aa (b text, a int NOT NULL);
 207  COMMIT;
 208  BEGIN;
 209  CREATE UNIQUE INDEX aai ON aa(a);
 210  COMMIT;
 211  BEGIN;
 212  ALTER TABLE aa REPLICA IDENTITY USING INDEX aai;
 213  COMMIT;
 214  BEGIN;
 215  INSERT INTO public.aa (b, a) VALUES ('aa', 1);
 216  INSERT INTO public.aa (b, a) VALUES ('bb', 2);
 217  COMMIT;
 218  BEGIN;
 219  UPDATE public.aa SET b = 'cc', a = 1 WHERE a = 1;
 220  COMMIT;
 221  BEGIN;
 222  UPDATE public.aa SET b = 'cc', a = 3 WHERE a = 1;
 223  COMMIT;
 224  BEGIN;
 225  UPDATE public.aa SET b = 'dd', a = 4 WHERE a = 2;
 226  COMMIT;
 227  BEGIN;
 228  DELETE FROM public.aa WHERE a = 4;
 229  COMMIT;
 230 (28 rows)
 231
 232 DROP TABLE aa;
 233 -- FULL case
 234 CREATE TABLE aa (a int primary key, b text NOT NULL);
 235 ALTER TABLE aa REPLICA IDENTITY FULL;
 236 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
 237 -- Update of Non-selective column
 238 UPDATE aa SET b = 'cc' WHERE a = 1;
 239 -- Update of only selective column
 240 UPDATE aa SET a = 3 WHERE a = 1;
 241 -- Update of both columns
 242 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
 243 DELETE FROM aa WHERE a = 4;
 244 -- Have a look at changes with different modes
 245 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 246                               data
 247 ----------------------------------------------------------------
 248  DROP TABLE aa;
 249  CREATE TABLE aa (a int primary key, b text NOT NULL);
 250  ALTER TABLE aa REPLICA IDENTITY FULL;
 251  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
 252  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
 253  UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1 AND b = 'aa';
 254  UPDATE public.aa SET a = 3, b = 'cc' WHERE a = 1 AND b = 'cc';
 255  UPDATE public.aa SET a = 4, b = 'dd' WHERE a = 2 AND b = 'bb';
 256  DELETE FROM public.aa WHERE a = 4 AND b = 'dd';
 257 (9 rows)
 258
 259 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
 260                               data
 261 ----------------------------------------------------------------
 262  BEGIN;
 263  DROP TABLE aa;
 264  COMMIT;
 265  BEGIN;
 266  CREATE TABLE aa (a int primary key, b text NOT NULL);
 267  COMMIT;
 268  BEGIN;
 269  ALTER TABLE aa REPLICA IDENTITY FULL;
 270  COMMIT;
 271  BEGIN;
 272  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
 273  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
 274  COMMIT;
 275  BEGIN;
 276  UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1 AND b = 'aa';
 277  COMMIT;
 278  BEGIN;
 279  UPDATE public.aa SET a = 3, b = 'cc' WHERE a = 1 AND b = 'cc';
 280  COMMIT;
 281  BEGIN;
 282  UPDATE public.aa SET a = 4, b = 'dd' WHERE a = 2 AND b = 'bb';
 283  COMMIT;
 284  BEGIN;
 285  DELETE FROM public.aa WHERE a = 4 AND b = 'dd';
 286  COMMIT;
 287 (25 rows)
 288
 289 DROP TABLE aa;
 290 -- NOTHING case
 291 CREATE TABLE aa (a int primary key, b text NOT NULL);
 292 ALTER TABLE aa REPLICA IDENTITY NOTHING;
 293 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
 294 UPDATE aa SET b = 'cc' WHERE a = 1;
 295 UPDATE aa SET a = 3 WHERE a = 1;
 296 DELETE FROM aa WHERE a = 4;
 297 -- Have a look at changes with different modes
 298 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 299                          data
 300 -------------------------------------------------------
 301  DROP TABLE aa;
 302  CREATE TABLE aa (a int primary key, b text NOT NULL);
 303  ALTER TABLE aa REPLICA IDENTITY NOTHING;
 304  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
 305  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
 306 (5 rows)
 307
 308 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
 309                          data
 310 -------------------------------------------------------
 311  BEGIN;
 312  DROP TABLE aa;
 313  COMMIT;
 314  BEGIN;
 315  CREATE TABLE aa (a int primary key, b text NOT NULL);
 316  COMMIT;
 317  BEGIN;
 318  ALTER TABLE aa REPLICA IDENTITY NOTHING;
 319  COMMIT;
 320  BEGIN;
 321  INSERT INTO public.aa (a, b) VALUES (1, 'aa');
 322  INSERT INTO public.aa (a, b) VALUES (2, 'bb');
 323  COMMIT;
 324  BEGIN;
 325  COMMIT;
 326  BEGIN;
 327  COMMIT;
 328 (17 rows)
 329
 330 DROP TABLE aa;
 331 -- Special value handling for various data types
 332 -- boolean, with true and false values correctly shaped
 333 CREATE TABLE aa (a boolean);
 334 INSERT INTO aa VALUES (true);
 335 INSERT INTO aa VALUES (false);
 336 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 337                    data
 338 -------------------------------------------
 339  DROP TABLE aa;
 340  CREATE TABLE aa (a boolean);
 341  INSERT INTO public.aa (a) VALUES (true);
 342  INSERT INTO public.aa (a) VALUES (false);
 343 (4 rows)
 344
 345 DROP TABLE aa;
 346 -- numeric and flost with Nan and infinity - quotes should be correctly placed
 347 CREATE TABLE aa (a numeric, b float4, c float8);
 348 INSERT INTO aa VALUES ('Nan'::numeric, 'Nan'::float4, 'Nan'::float8);
 349 INSERT INTO aa VALUES (1.0, '+Infinity'::float4, '+Infinity'::float8);
 350 INSERT INTO aa VALUES (2.0, '-Infinity'::float4, '-Infinity'::float8);
 351 INSERT INTO aa VALUES (3.0, 4.0, 5.0);
 352 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 353                                   data
 354 -------------------------------------------------------------------------
 355  DROP TABLE aa;
 356  CREATE TABLE aa (a numeric, b float4, c float8);
 357  INSERT INTO public.aa (a, b, c) VALUES ('NaN', 'NaN', 'NaN');
 358  INSERT INTO public.aa (a, b, c) VALUES (1.0, 'Infinity', 'Infinity');
 359  INSERT INTO public.aa (a, b, c) VALUES (2.0, '-Infinity', '-Infinity');
 360  INSERT INTO public.aa (a, b, c) VALUES (3.0, 4, 5);
 361 (6 rows)
 362
 363 DROP TABLE aa;
 364 -- Unchanged toast datum
 365 CREATE TABLE tt (a int primary key, t text);
 366 ALTER TABLE tt ALTER COLUMN t SET STORAGE EXTERNAL;
 367 INSERT INTO tt VALUES (1, 'foo');
 368 INSERT INTO tt VALUES (2, repeat('x', 3000));
 369 UPDATE tt SET t=t WHERE a=1;
 370 UPDATE tt SET t=t WHERE a=2;
 371 SELECT substr(data, 1, 50), substr(data, 3000, 45)
 372   FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off')
 373   ORDER BY lsn;
 374                        substr                       |                    substr
 375 ----------------------------------------------------+-----------------------------------------------
 376  DROP TABLE aa;                                     |
 377  CREATE TABLE tt (a int primary key, t text);       |
 378  ALTER TABLE tt ALTER COLUMN t SET STORAGE EXTERNAL |
 379  INSERT INTO public.tt (a, t) VALUES (1, 'foo');    |
 380  INSERT INTO public.tt (a, t) VALUES (2, 'xxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
 381  UPDATE public.tt SET a = 1, t = 'foo' WHERE a = 1; |
 382  UPDATE public.tt SET a = 2 WHERE a = 2;            |
 383 (7 rows)
 384
 385 DROP TABLE tt;
 386 -- TRUNCATE test - single table
 387 CREATE TABLE test_truncate (id int primary key, value text);
 388 INSERT INTO test_truncate VALUES (1, 'one'), (2, 'two'), (3, 'three');
 389 TRUNCATE test_truncate;
 390 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 391                                data
 392 -------------------------------------------------------------------
 393  DROP TABLE tt;
 394  CREATE TABLE test_truncate (id int primary key, value text);
 395  INSERT INTO public.test_truncate (id, value) VALUES (1, 'one');
 396  INSERT INTO public.test_truncate (id, value) VALUES (2, 'two');
 397  INSERT INTO public.test_truncate (id, value) VALUES (3, 'three');
 398  TRUNCATE TABLE public.test_truncate;
 399 (6 rows)
 400
 401 DROP TABLE test_truncate;
 402 -- TRUNCATE test - multiple tables
 403 CREATE TABLE parent_table (id int primary key);
 404 CREATE TABLE child_table (id int primary key, parent_id int REFERENCES parent_table(id));
 405 INSERT INTO parent_table VALUES (1), (2);
 406 INSERT INTO child_table VALUES (1, 1), (2, 2);
 407 TRUNCATE parent_table, child_table;
 408 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 409                                            data
 410 -------------------------------------------------------------------------------------------
 411  DROP TABLE test_truncate;
 412  CREATE TABLE parent_table (id int primary key);
 413  CREATE TABLE child_table (id int primary key, parent_id int REFERENCES parent_table(id));
 414  INSERT INTO public.parent_table (id) VALUES (1);
 415  INSERT INTO public.parent_table (id) VALUES (2);
 416  INSERT INTO public.child_table (id, parent_id) VALUES (1, 1);
 417  INSERT INTO public.child_table (id, parent_id) VALUES (2, 2);
 418  TRUNCATE TABLE public.parent_table, public.child_table;
 419 (8 rows)
 420
 421 DROP TABLE child_table;
 422 DROP TABLE parent_table;
 423 -- Drop replication slot
 424 DROP EXTENSION wal2sql;
 425 SELECT pg_drop_replication_slot('custom_slot');
 426  pg_drop_replication_slot
 427 --------------------------
 428
 429 (1 row)
 430