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