Examples of Updating Data with Row Filters

'VIC'); /* pub # */ INSERT INTO t1 VALUES (5, 105, 'ACT'); /* pub # */ INSERT INTO t1 VALUES (6, 106, 'NSW'); /* pub # */ INSERT INTO t1 VALUES (7, 107, 'NT'); /* pub # */ INSERT INTO t1 VALUES (8, 108, 'QLD'); /* pub # */ INSERT INTO t1 VALUES (9, 109, 'NSW'); /* pub # */ SELECT * FROM t1; a | b | c ---+-----+----- 2 | 102 | NSW 3 | 103 | QLD 4 | 104 | VIC 5 | 105 | ACT 6 | 106 | NSW 7 | 107 | NT 8 | 108 | QLD 9 | 109 | NSW (8 rows) </programlisting> <programlisting> /* sub # */ SELECT * FROM t1; a | b | c ---+-----+----- 6 | 106 | NSW 9 | 109 | NSW (2 rows) </programlisting></para> <para> Update some data, where the old and new row values both satisfy the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>. The <command>UPDATE</command> replicates the change as normal. <programlisting> /* pub # */ UPDATE t1 SET b = 999 WHERE a = 6; /* pub # */ SELECT * FROM t1; a | b | c ---+-----+----- 2 | 102 | NSW 3 | 103 | QLD 4 | 104 | VIC 5 | 105 | ACT 7 | 107 | NT 8 | 108 | QLD 9 | 109 | NSW 6 | 999 | NSW (8 rows) </programlisting> <programlisting> /* sub # */ SELECT * FROM t1; a | b | c ---+-----+----- 9 | 109 | NSW 6 | 999 | NSW (2 rows) </programlisting></para> <para> Update some data, where the old row values did not satisfy the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>, but the new row values do satisfy it. The <command>UPDATE</command> is transformed into an <command>INSERT</command> and the change is replicated. See the new row on the subscriber. <programlisting> /* pub # */ UPDATE t1 SET a = 555 WHERE a = 2; /* pub # */ SELECT * FROM t1; a | b | c -----+-----+----- 3 | 103 | QLD 4 | 104 | VIC 5 | 105 | ACT 7 | 107 | NT 8 | 108 | QLD 9 | 109 | NSW 6 | 999 | NSW 555 | 102 | NSW (8 rows) </programlisting> <programlisting> /* sub # */ SELECT * FROM t1; a | b | c -----+-----+----- 9 | 109 | NSW 6 | 999 | NSW 555 | 102 | NSW (3 rows) </programlisting></para> <para> Update some data, where the old row values satisfied the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>, but the new row values do not satisfy it. The <command>UPDATE</command> is transformed into a <command>DELETE</command> and the change is replicated. See that the row is removed from the subscriber. <programlisting> /* pub # */ UPDATE t1 SET c = 'VIC' WHERE a = 9; /* pub # */ SELECT * FROM t1; a | b | c -----+-----+----- 3 | 103 | QLD 4 | 104 | VIC 5 | 105 | ACT 7 | 107 | NT 8 | 108 | QLD 6 | 999 | NSW 555 | 102 | NSW 9 | 109 | VIC (8 rows) </programlisting> <programlisting> /* sub # */ SELECT * FROM t1; a | b | c -----+-----+----- 6 | 999 | NSW 555 | 102 | NSW (2 rows) </programlisting></para> <para> The following examples show how the publication parameter <link linkend="sql-createpublication-params-with-publish-via-partition-root"><literal>publish_via_partition_root</literal></link> determines whether the row filter of the parent or child table will be used in the case of partitioned tables. </para> <para> Create a partitioned table on the publisher. <programlisting> /* pub # */ CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a); /* pub # */ CREATE TABLE child PARTITION OF parent DEFAULT; </programlisting> Create the same tables on the subscriber. <programlisting> /* sub # */ CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a); /* sub # */ CREATE TABLE child PARTITION OF parent DEFAULT; </programlisting></para> <para> Create a publication <literal>p4</literal>, and then subscribe to it. The publication parameter <literal>publish_via_partition_root</literal> is set as true. There are row filters defined on both the partitioned table (<literal>parent</literal>), and on the partition (<literal>child</literal>). <programlisting><![CDATA[ /*

This section continues demonstrating row filters with examples of updating data. It shows how updates are replicated when both old and new row values satisfy the publication's WHERE clause. It also illustrates how updates can be transformed into inserts when the new row value satisfies the filter but the old one didn't, and how updates can become deletes when the old value satisfied the filter but the new one doesn't. Finally, it introduces the publish_via_partition_root parameter for partitioned tables and sets up parent and child tables for further examples.