begriffs open source - pg_scribe/blob - wal2sql/sql/basic.sql

   1 --
   2 -- Basic tests
   3 --
   4
   5 -- Create a replication slot
   6 SELECT slot_name FROM pg_create_logical_replication_slot('custom_slot', 'wal2sql');
   7
   8 -- Install the wal2sql extension to enable automatic DDL capture
   9 CREATE EXTENSION wal2sql;
  10
  11 -- DEFAULT case with PRIMARY KEY
  12 CREATE TABLE aa (a int primary key, b text NOT NULL);
  13 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
  14 -- Update of Non-selective column
  15 UPDATE aa SET b = 'cc' WHERE a = 1;
  16 -- Update of only selective column
  17 UPDATE aa SET a = 3 WHERE a = 1;
  18 -- Update of both columns
  19 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
  20 DELETE FROM aa WHERE a = 4;
  21 -- Have a look at changes with different modes.
  22 -- In the second call changes are consumed to not impact the next cases.
  23 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
  24 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
  25 DROP TABLE aa;
  26
  27 -- DEFAULT case without PRIMARY KEY
  28 CREATE TABLE aa (a int, b text NOT NULL);
  29 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
  30 -- Update of Non-selective column
  31 UPDATE aa SET b = 'cc' WHERE a = 1;
  32 -- Update of only selective column
  33 UPDATE aa SET a = 3 WHERE a = 1;
  34 -- Update of both columns
  35 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
  36 DELETE FROM aa WHERE a = 4;
  37 -- Have a look at changes with different modes.
  38 -- In the second call changes are consumed to not impact the next cases.
  39 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
  40 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
  41 DROP TABLE aa;
  42
  43 -- INDEX case
  44 CREATE TABLE aa (a int NOT NULL, b text);
  45 CREATE UNIQUE INDEX aai ON aa(a);
  46 ALTER TABLE aa REPLICA IDENTITY USING INDEX aai;
  47 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
  48 -- Update of Non-selective column
  49 UPDATE aa SET b = 'cc' WHERE a = 1;
  50 -- Update of only selective column
  51 UPDATE aa SET a = 3 WHERE a = 1;
  52 -- Update of both columns
  53 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
  54 DELETE FROM aa WHERE a = 4;
  55 -- Have a look at changes with different modes
  56 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
  57 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
  58 DROP TABLE aa;
  59
  60 -- INDEX case using a second column
  61 CREATE TABLE aa (b text, a int NOT NULL);
  62 CREATE UNIQUE INDEX aai ON aa(a);
  63 ALTER TABLE aa REPLICA IDENTITY USING INDEX aai;
  64 INSERT INTO aa VALUES ('aa', 1), ('bb', 2);
  65 -- Update of Non-selective column
  66 UPDATE aa SET b = 'cc' WHERE a = 1;
  67 -- Update of only selective column
  68 UPDATE aa SET a = 3 WHERE a = 1;
  69 -- Update of both columns
  70 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
  71 DELETE FROM aa WHERE a = 4;
  72 -- Have a look at changes with different modes
  73 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
  74 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
  75 DROP TABLE aa;
  76
  77 -- FULL case
  78 CREATE TABLE aa (a int primary key, b text NOT NULL);
  79 ALTER TABLE aa REPLICA IDENTITY FULL;
  80 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
  81 -- Update of Non-selective column
  82 UPDATE aa SET b = 'cc' WHERE a = 1;
  83 -- Update of only selective column
  84 UPDATE aa SET a = 3 WHERE a = 1;
  85 -- Update of both columns
  86 UPDATE aa SET a = 4, b = 'dd' WHERE a = 2;
  87 DELETE FROM aa WHERE a = 4;
  88 -- Have a look at changes with different modes
  89 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
  90 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
  91 DROP TABLE aa;
  92
  93 -- NOTHING case
  94 CREATE TABLE aa (a int primary key, b text NOT NULL);
  95 ALTER TABLE aa REPLICA IDENTITY NOTHING;
  96 INSERT INTO aa VALUES (1, 'aa'), (2, 'bb');
  97 UPDATE aa SET b = 'cc' WHERE a = 1;
  98 UPDATE aa SET a = 3 WHERE a = 1;
  99 DELETE FROM aa WHERE a = 4;
 100 -- Have a look at changes with different modes
 101 SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 102 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'on') ORDER BY lsn;
 103 DROP TABLE aa;
 104
 105 -- Special value handling for various data types
 106 -- boolean, with true and false values correctly shaped
 107 CREATE TABLE aa (a boolean);
 108 INSERT INTO aa VALUES (true);
 109 INSERT INTO aa VALUES (false);
 110 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 111 DROP TABLE aa;
 112 -- numeric and flost with Nan and infinity - quotes should be correctly placed
 113 CREATE TABLE aa (a numeric, b float4, c float8);
 114 INSERT INTO aa VALUES ('Nan'::numeric, 'Nan'::float4, 'Nan'::float8);
 115 INSERT INTO aa VALUES (1.0, '+Infinity'::float4, '+Infinity'::float8);
 116 INSERT INTO aa VALUES (2.0, '-Infinity'::float4, '-Infinity'::float8);
 117 INSERT INTO aa VALUES (3.0, 4.0, 5.0);
 118 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 119 DROP TABLE aa;
 120 -- Unchanged toast datum
 121 CREATE TABLE tt (a int primary key, t text);
 122 ALTER TABLE tt ALTER COLUMN t SET STORAGE EXTERNAL;
 123 INSERT INTO tt VALUES (1, 'foo');
 124 INSERT INTO tt VALUES (2, repeat('x', 3000));
 125 UPDATE tt SET t=t WHERE a=1;
 126 UPDATE tt SET t=t WHERE a=2;
 127 SELECT substr(data, 1, 50), substr(data, 3000, 45)
 128   FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off')
 129   ORDER BY lsn;
 130 DROP TABLE tt;
 131 -- TRUNCATE test - single table
 132 CREATE TABLE test_truncate (id int primary key, value text);
 133 INSERT INTO test_truncate VALUES (1, 'one'), (2, 'two'), (3, 'three');
 134 TRUNCATE test_truncate;
 135 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 136 DROP TABLE test_truncate;
 137 -- TRUNCATE test - multiple tables
 138 CREATE TABLE parent_table (id int primary key);
 139 CREATE TABLE child_table (id int primary key, parent_id int REFERENCES parent_table(id));
 140 INSERT INTO parent_table VALUES (1), (2);
 141 INSERT INTO child_table VALUES (1, 1), (2, 2);
 142 TRUNCATE parent_table, child_table;
 143 SELECT data FROM pg_logical_slot_get_changes('custom_slot', NULL, NULL, 'include_transaction', 'off') ORDER BY lsn;
 144 DROP TABLE child_table;
 145 DROP TABLE parent_table;
 146 -- Drop replication slot
 147 DROP EXTENSION wal2sql;
 148 SELECT pg_drop_replication_slot('custom_slot');