doc: Make logical replication examples executable in bulk and legal sgml.
Started by David G. Johnston8 months ago6 messages
While responding to a "our documentation is buggy" complaint I got annoyed
in my attempt to reproduce the behavior by having to surgically copy
line-by-line the DDL and DML code involved. Let's strive for a more
copy-paste friendly example setup. No prompts and no interspersed command
tags (they are ok if the script is one block and the output is another).
In passing, the examples use < 5 and >= 5 in expressions - which in theory
are supposed to require the use of < and > entities...or enclosure in
a CDATA construct. The latter seems preferable.
David J.
Attachments:
v1-0001-doc-Make-logical-replication-examples-executable-in-.patchtext/x-patch; charset=US-ASCII; name=v1-0001-doc-Make-logical-replication-examples-executable-in-.patchDownload
From 6a21091d5ce058cb68ac23f49641e1db5069d5de Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Sat, 3 May 2025 08:41:47 -0700
Subject: [PATCH] doc: Make logical replication examples executable in bulk and
legal sgml.
The output command tags are not useful information for these examples
and just get in the way of doing a bulk copy-paste for a series of DML/DDL.
The prompt usually is unhelpful as well and likewise messes with
copy-paste. Usually would just get rid of it but given the multi-server
dynamic of this particular script converting them into comments suffices.
Apparently the docbook conversion tooling doesn't care that we weren't
entity-substituting < and > symbols in the examples. However, to be
safe, just wrap examples in a CDATA construction if they contain
problematic characters.
---
doc/src/sgml/logical-replication.sgml | 449 ++++++++++----------------
1 file changed, 177 insertions(+), 272 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index f288c049a5..686dd441d0 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -363,34 +363,25 @@
<para>
Create some test tables on the publisher.
<programlisting>
-test_pub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
-CREATE TABLE
-test_pub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
-CREATE TABLE
-test_pub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
-CREATE TABLE
+/* pub # */ CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
+/* pub # */ CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
+/* pub # */ CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
</programlisting></para>
<para>
Create the same tables on the subscriber.
<programlisting>
-test_sub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
-CREATE TABLE
-test_sub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
-CREATE TABLE
-test_sub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
-CREATE TABLE
+/* sub # */ CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
+/* sub # */ CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
+/* sub # */ CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
</programlisting></para>
<para>
Insert data to the tables at the publisher side.
<programlisting>
-test_pub=# INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
-INSERT 0 3
-test_pub=# INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C');
-INSERT 0 3
-test_pub=# INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii');
-INSERT 0 3
+/* pub # */ INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
+/* pub # */ INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C');
+/* pub # */ INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii');
</programlisting></para>
<para>
@@ -399,41 +390,34 @@ INSERT 0 3
<link linkend="sql-createpublication-params-with-publish"><literal>publish</literal></link>
operations. The publication <literal>pub3b</literal> has a row filter (see
<xref linkend="logical-replication-row-filter"/>).
-<programlisting>
-test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;
-CREATE PUBLICATION
-test_pub=# CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate');
-CREATE PUBLICATION
-test_pub=# CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate');
-CREATE PUBLICATION
-test_pub=# CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5);
-CREATE PUBLICATION
-</programlisting></para>
+<programlisting><![CDATA[
+/* pub # */ CREATE PUBLICATION pub1 FOR TABLE t1;
+/* pub # */ CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate');
+/* pub # */ CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate');
+/* pub # */ CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5);
+]]></programlisting></para>
<para>
Create subscriptions for the publications. The subscription
<literal>sub3</literal> subscribes to both <literal>pub3a</literal> and
<literal>pub3b</literal>. All subscriptions will copy initial data by default.
<programlisting>
-test_sub=# CREATE SUBSCRIPTION sub1
-test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
-test_sub-# PUBLICATION pub1;
-CREATE SUBSCRIPTION
-test_sub=# CREATE SUBSCRIPTION sub2
-test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub2'
-test_sub-# PUBLICATION pub2;
-CREATE SUBSCRIPTION
-test_sub=# CREATE SUBSCRIPTION sub3
-test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub3'
-test_sub-# PUBLICATION pub3a, pub3b;
-CREATE SUBSCRIPTION
+/* sub # */ CREATE SUBSCRIPTION sub1
+/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
+/* sub - */ PUBLICATION pub1;
+/* sub # */ CREATE SUBSCRIPTION sub2
+/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub2'
+/* sub - */ PUBLICATION pub2;
+/* sub # */ CREATE SUBSCRIPTION sub3
+/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub3'
+/* sub - */ PUBLICATION pub3a, pub3b;
</programlisting></para>
<para>
Observe that initial table data is copied, regardless of the
<literal>publish</literal> operation of the publication.
<programlisting>
-test_sub=# SELECT * FROM t1;
+/* sub # */ SELECT * FROM t1;
a | b
---+-------
1 | one
@@ -441,7 +425,7 @@ test_sub=# SELECT * FROM t1;
3 | three
(3 rows)
-test_sub=# SELECT * FROM t2;
+/* sub # */ SELECT * FROM t2;
c | d
---+---
1 | A
@@ -456,7 +440,7 @@ test_sub=# SELECT * FROM t2;
it means the copied table <literal>t3</literal> contains all rows even when
they do not match the row filter of publication <literal>pub3b</literal>.
<programlisting>
-test_sub=# SELECT * FROM t3;
+/* sub # */ SELECT * FROM t3;
e | f
---+-----
1 | i
@@ -468,18 +452,15 @@ test_sub=# SELECT * FROM t3;
<para>
Insert more data to the tables at the publisher side.
<programlisting>
-test_pub=# INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six');
-INSERT 0 3
-test_pub=# INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F');
-INSERT 0 3
-test_pub=# INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi');
-INSERT 0 3
+/* pub # */ INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six');
+/* pub # */ INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F');
+/* pub # */ INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi');
</programlisting></para>
<para>
Now the publisher side data looks like:
<programlisting>
-test_pub=# SELECT * FROM t1;
+/* pub # */ SELECT * FROM t1;
a | b
---+-------
1 | one
@@ -490,7 +471,7 @@ test_pub=# SELECT * FROM t1;
6 | six
(6 rows)
-test_pub=# SELECT * FROM t2;
+/* pub # */ SELECT * FROM t2;
c | d
---+---
1 | A
@@ -501,7 +482,7 @@ test_pub=# SELECT * FROM t2;
6 | F
(6 rows)
-test_pub=# SELECT * FROM t3;
+/* pub # */ SELECT * FROM t3;
e | f
---+-----
1 | i
@@ -521,7 +502,7 @@ test_pub=# SELECT * FROM t3;
only replicate data that matches the row filter of <literal>pub3b</literal>.
Now the subscriber side data looks like:
<programlisting>
-test_sub=# SELECT * FROM t1;
+/* sub # */ SELECT * FROM t1;
a | b
---+-------
1 | one
@@ -532,7 +513,7 @@ test_sub=# SELECT * FROM t1;
6 | six
(6 rows)
-test_sub=# SELECT * FROM t2;
+/* sub # */ SELECT * FROM t2;
c | d
---+---
1 | A
@@ -540,7 +521,7 @@ test_sub=# SELECT * FROM t2;
3 | C
(3 rows)
-test_sub=# SELECT * FROM t3;
+/* sub # */ SELECT * FROM t3;
e | f
---+-----
1 | i
@@ -567,8 +548,7 @@ test_sub=# SELECT * FROM t3;
<para>
First, create a publication for the examples to use.
<programlisting>
-test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;
-CREATE PUBLICATION
+/* pub # */ CREATE PUBLICATION pub1 FOR ALL TABLES;
</programlisting></para>
<para>
Example 1: Where the subscription says <literal>connect = false</literal>
@@ -579,13 +559,12 @@ CREATE PUBLICATION
<para>
Create the subscription.
<programlisting>
-test_sub=# CREATE SUBSCRIPTION sub1
-test_sub-# CONNECTION 'host=localhost dbname=test_pub'
-test_sub-# PUBLICATION pub1
-test_sub-# WITH (connect=false);
+/* sub # */ CREATE SUBSCRIPTION sub1
+/* sub - */ CONNECTION 'host=localhost dbname=test_pub'
+/* sub - */ PUBLICATION pub1
+/* sub - */ WITH (connect=false);
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
-CREATE SUBSCRIPTION
</programlisting></para>
</listitem>
<listitem>
@@ -594,7 +573,7 @@ CREATE SUBSCRIPTION
specified during <literal>CREATE SUBSCRIPTION</literal>, the name of the
slot to create is same as the subscription name, e.g. "sub1".
<programlisting>
-test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
+/* pub # */ SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
slot_name | lsn
-----------+-----------
sub1 | 0/19404D0
@@ -606,10 +585,8 @@ test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
On the subscriber, complete the activation of the subscription. After
this the tables of <literal>pub1</literal> will start replicating.
<programlisting>
-test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
-ALTER SUBSCRIPTION
-test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
-ALTER SUBSCRIPTION
+/* sub # */ ALTER SUBSCRIPTION sub1 ENABLE;
+/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
</programlisting></para>
</listitem>
</itemizedlist>
@@ -625,13 +602,12 @@ ALTER SUBSCRIPTION
<para>
Create the subscription.
<programlisting>
-test_sub=# CREATE SUBSCRIPTION sub1
-test_sub-# CONNECTION 'host=localhost dbname=test_pub'
-test_sub-# PUBLICATION pub1
-test_sub-# WITH (connect=false, slot_name='myslot');
+/* sub # */ CREATE SUBSCRIPTION sub1
+/* sub - */ CONNECTION 'host=localhost dbname=test_pub'
+/* sub - */ PUBLICATION pub1
+/* sub - */ WITH (connect=false, slot_name='myslot');
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
-CREATE SUBSCRIPTION
</programlisting></para>
</listitem>
<listitem>
@@ -639,7 +615,7 @@ CREATE SUBSCRIPTION
On the publisher, manually create a slot using the same name that was
specified during <literal>CREATE SUBSCRIPTION</literal>, e.g. "myslot".
<programlisting>
-test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+/* pub # */ SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
slot_name | lsn
-----------+-----------
myslot | 0/19059A0
@@ -651,10 +627,8 @@ test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput'
On the subscriber, the remaining subscription activation steps are the
same as before.
<programlisting>
-test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
-ALTER SUBSCRIPTION
-test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
-ALTER SUBSCRIPTION
+/* sub # */ ALTER SUBSCRIPTION sub1 ENABLE;
+/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
</programlisting></para>
</listitem>
</itemizedlist>
@@ -669,18 +643,17 @@ ALTER SUBSCRIPTION
<literal>enabled = false</literal>, and
<literal>create_slot = false</literal> are also needed.
<programlisting>
-test_sub=# CREATE SUBSCRIPTION sub1
-test_sub-# CONNECTION 'host=localhost dbname=test_pub'
-test_sub-# PUBLICATION pub1
-test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);
-CREATE SUBSCRIPTION
+/* sub # */ CREATE SUBSCRIPTION sub1
+/* sub - */ CONNECTION 'host=localhost dbname=test_pub'
+/* sub - */ PUBLICATION pub1
+/* sub - */ WITH (slot_name=NONE, enabled=false, create_slot=false);
</programlisting></para>
</listitem>
<listitem>
<para>
On the publisher, manually create a slot using any name, e.g. "myslot".
<programlisting>
-test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+/* pub # */ SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
slot_name | lsn
-----------+-----------
myslot | 0/1905930
@@ -692,18 +665,15 @@ test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput'
On the subscriber, associate the subscription with the slot name just
created.
<programlisting>
-test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
-ALTER SUBSCRIPTION
+/* sub # */ ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
</programlisting></para>
</listitem>
<listitem>
<para>
The remaining subscription activation steps are same as before.
<programlisting>
-test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
-ALTER SUBSCRIPTION
-test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
-ALTER SUBSCRIPTION
+/* sub # */ ALTER SUBSCRIPTION sub1 ENABLE;
+/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
</programlisting></para>
</listitem>
</itemizedlist>
@@ -752,7 +722,7 @@ ALTER SUBSCRIPTION
will return the relevant replication slots associated with the
failover-enabled subscriptions.
<programlisting>
-test_sub=# SELECT
+/* sub # */ SELECT
array_agg(quote_literal(s.subslotname)) AS slots
FROM pg_subscription s
WHERE s.subfailover AND
@@ -775,7 +745,7 @@ test_sub=# SELECT
as they will either be dropped or re-created on the new primary server in those
cases.
<programlisting>
-test_sub=# SELECT
+/* sub # */ SELECT
array_agg(quote_literal(slot_name)) AS slots
FROM
(
@@ -794,7 +764,7 @@ test_sub=# SELECT
Check that the logical replication slots identified above exist on
the standby server and are ready for failover.
<programlisting>
-test_standby=# SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS failover_ready
+/* standby # */ SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS failover_ready
FROM pg_replication_slots
WHERE slot_name IN
('sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164');
@@ -1024,12 +994,9 @@ test_standby=# SELECT slot_name, (synced AND NOT temporary AND NOT conflicting)
<para>
Create some tables to be used in the following examples.
<programlisting>
-test_pub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
-CREATE TABLE
-test_pub=# CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));
-CREATE TABLE
-test_pub=# CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));
-CREATE TABLE
+/* pub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
+/* pub # */ CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));
+/* pub # */ CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));
</programlisting></para>
<para>
@@ -1038,43 +1005,40 @@ CREATE TABLE
<literal>p2</literal> has two tables. Table <literal>t1</literal> has no row
filter, and table <literal>t2</literal> has a row filter. Publication
<literal>p3</literal> has two tables, and both of them have a row filter.
-<programlisting>
-test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
-CREATE PUBLICATION
-test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
-CREATE PUBLICATION
-test_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
-CREATE PUBLICATION
-</programlisting></para>
+<programlisting><![CDATA[
+/* pub # */ CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
+/* pub # */ CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+/* pub # */ CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
+]]></programlisting></para>
<para>
<command>psql</command> can be used to show the row filter expressions (if
defined) for each publication.
-<programlisting>
-test_pub=# \dRp+
- Publication p1
+<programlisting><![CDATA[
+/* pub # */ \dRp+
+ Publication p1
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
postgres | f | t | t | t | t | f
Tables:
- "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))
+ "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))
- Publication p2
+ Publication p2
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
postgres | f | t | t | t | t | f
Tables:
- "public.t1"
- "public.t2" WHERE (e = 99)
+ "public.t1"
+ "public.t2" WHERE (e = 99)
- Publication p3
+ Publication p3
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
postgres | f | t | t | t | t | f
Tables:
- "public.t2" WHERE (d = 10)
- "public.t3" WHERE (g = 10)
-</programlisting></para>
+ "public.t2" WHERE (d = 10)
+ "public.t3" WHERE (g = 10)
+]]></programlisting></para>
<para>
<command>psql</command> can be used to show the row filter expressions (if
@@ -1082,8 +1046,8 @@ Tables:
of two publications, but has a row filter only in <literal>p1</literal>.
See that table <literal>t2</literal> is a member of two publications, and
has a different row filter in each of them.
-<programlisting>
-test_pub=# \d t1
+<programlisting><![CDATA[
+/* pub # */ \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
@@ -1096,7 +1060,7 @@ Publications:
"p1" WHERE ((a > 5) AND (c = 'NSW'::text))
"p2"
-test_pub=# \d t2
+/* pub # */ \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
@@ -1109,7 +1073,7 @@ Publications:
"p2" WHERE (e = 99)
"p3" WHERE (d = 10)
-test_pub=# \d t3
+/* pub # */ \d t3
Table "public.t3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
@@ -1120,43 +1084,33 @@ Indexes:
"t3_pkey" PRIMARY KEY, btree (g)
Publications:
"p3" WHERE (g = 10)
-</programlisting></para>
+]]></programlisting></para>
<para>
On the subscriber node, create a table <literal>t1</literal> with the same
definition as the one on the publisher, and also create the subscription
<literal>s1</literal> that subscribes to the publication <literal>p1</literal>.
<programlisting>
-test_sub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
-CREATE TABLE
-test_sub=# CREATE SUBSCRIPTION s1
-test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'
-test_sub-# PUBLICATION p1;
-CREATE SUBSCRIPTION
+/* sub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
+/* sub # */ CREATE SUBSCRIPTION s1
+/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s1'
+/* sub - */ PUBLICATION p1;
</programlisting></para>
<para>
Insert some rows. Only the rows satisfying the <literal>t1 WHERE</literal>
clause of publication <literal>p1</literal> are replicated.
<programlisting>
-test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
-INSERT 0 1
-test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
-INSERT 0 1
-test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
-INSERT 0 1
-test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
-INSERT 0 1
-test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
-INSERT 0 1
-test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT');
-INSERT 0 1
-test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
-INSERT 0 1
-test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
-INSERT 0 1
-
-test_pub=# SELECT * FROM t1;
+/* pub # */ INSERT INTO t1 VALUES (2, 102, 'NSW');
+/* pub # */ INSERT INTO t1 VALUES (3, 103, 'QLD');
+/* pub # */ INSERT INTO t1 VALUES (4, 104, '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
@@ -1170,7 +1124,7 @@ test_pub=# SELECT * FROM t1;
(8 rows)
</programlisting>
<programlisting>
-test_sub=# SELECT * FROM t1;
+/* sub # */ SELECT * FROM t1;
a | b | c
---+-----+-----
6 | 106 | NSW
@@ -1184,10 +1138,9 @@ test_sub=# SELECT * FROM t1;
<literal>p1</literal>. The <command>UPDATE</command> replicates
the change as normal.
<programlisting>
-test_pub=# UPDATE t1 SET b = 999 WHERE a = 6;
-UPDATE 1
+/* pub # */ UPDATE t1 SET b = 999 WHERE a = 6;
-test_pub=# SELECT * FROM t1;
+/* pub # */ SELECT * FROM t1;
a | b | c
---+-----+-----
2 | 102 | NSW
@@ -1201,7 +1154,7 @@ test_pub=# SELECT * FROM t1;
(8 rows)
</programlisting>
<programlisting>
-test_sub=# SELECT * FROM t1;
+/* sub # */ SELECT * FROM t1;
a | b | c
---+-----+-----
9 | 109 | NSW
@@ -1216,10 +1169,9 @@ test_sub=# SELECT * FROM t1;
transformed into an <command>INSERT</command> and the change is replicated.
See the new row on the subscriber.
<programlisting>
-test_pub=# UPDATE t1 SET a = 555 WHERE a = 2;
-UPDATE 1
+/* pub # */ UPDATE t1 SET a = 555 WHERE a = 2;
-test_pub=# SELECT * FROM t1;
+/* pub # */ SELECT * FROM t1;
a | b | c
-----+-----+-----
3 | 103 | QLD
@@ -1233,7 +1185,7 @@ test_pub=# SELECT * FROM t1;
(8 rows)
</programlisting>
<programlisting>
-test_sub=# SELECT * FROM t1;
+/* sub # */ SELECT * FROM t1;
a | b | c
-----+-----+-----
9 | 109 | NSW
@@ -1249,10 +1201,9 @@ test_sub=# SELECT * FROM t1;
transformed into a <command>DELETE</command> and the change is replicated.
See that the row is removed from the subscriber.
<programlisting>
-test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
-UPDATE 1
+/* pub # */ UPDATE t1 SET c = 'VIC' WHERE a = 9;
-test_pub=# SELECT * FROM t1;
+/* pub # */ SELECT * FROM t1;
a | b | c
-----+-----+-----
3 | 103 | QLD
@@ -1266,7 +1217,7 @@ test_pub=# SELECT * FROM t1;
(8 rows)
</programlisting>
<programlisting>
-test_sub=# SELECT * FROM t1;
+/* sub # */ SELECT * FROM t1;
a | b | c
-----+-----+-----
6 | 999 | NSW
@@ -1284,17 +1235,13 @@ test_sub=# SELECT * FROM t1;
<para>
Create a partitioned table on the publisher.
<programlisting>
-test_pub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
-CREATE TABLE
-test_pub=# CREATE TABLE child PARTITION OF parent DEFAULT;
-CREATE TABLE
+/* 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>
-test_sub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
-CREATE TABLE
-test_sub=# CREATE TABLE child PARTITION OF parent DEFAULT;
-CREATE TABLE
+/* sub # */ CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
+/* sub # */ CREATE TABLE child PARTITION OF parent DEFAULT;
</programlisting></para>
<para>
@@ -1302,16 +1249,14 @@ CREATE TABLE
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>
-test_pub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a < 5), child WHERE (a >= 5)
-test_pub-# WITH (publish_via_partition_root=true);
-CREATE PUBLICATION
-</programlisting>
-<programlisting>
-test_sub=# CREATE SUBSCRIPTION s4
-test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4'
-test_sub-# PUBLICATION p4;
-CREATE SUBSCRIPTION
+<programlisting><![CDATA[
+/* pub # */ CREATE PUBLICATION p4 FOR TABLE parent WHERE (a < 5), child WHERE (a >= 5)
+/* pub - */ WITH (publish_via_partition_root=true);
+]]></programlisting>
+<programlisting>
+/* sub # */ CREATE SUBSCRIPTION s4
+/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s4'
+/* sub - */ PUBLICATION p4;
</programlisting></para>
<para>
@@ -1320,12 +1265,10 @@ CREATE SUBSCRIPTION
<literal>parent</literal> (because <literal>publish_via_partition_root</literal>
is true).
<programlisting>
-test_pub=# INSERT INTO parent VALUES (2), (4), (6);
-INSERT 0 3
-test_pub=# INSERT INTO child VALUES (3), (5), (7);
-INSERT 0 3
+/* pub # */ INSERT INTO parent VALUES (2), (4), (6);
+/* pub # */ INSERT INTO child VALUES (3), (5), (7);
-test_pub=# SELECT * FROM parent ORDER BY a;
+/* pub # */ SELECT * FROM parent ORDER BY a;
a
---
2
@@ -1337,7 +1280,7 @@ test_pub=# SELECT * FROM parent ORDER BY a;
(6 rows)
</programlisting>
<programlisting>
-test_sub=# SELECT * FROM parent ORDER BY a;
+/* sub # */ SELECT * FROM parent ORDER BY a;
a
---
2
@@ -1350,16 +1293,13 @@ test_sub=# SELECT * FROM parent ORDER BY a;
Repeat the same test, but with a different value for <literal>publish_via_partition_root</literal>.
The publication parameter <literal>publish_via_partition_root</literal> is
set as false. A row filter is defined on the partition (<literal>child</literal>).
+<programlisting><![CDATA[
+/* pub # */ DROP PUBLICATION p4;
+/* pub # */ CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
+/* pub - */ WITH (publish_via_partition_root=false);
+]]></programlisting>
<programlisting>
-test_pub=# DROP PUBLICATION p4;
-DROP PUBLICATION
-test_pub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
-test_pub-# WITH (publish_via_partition_root=false);
-CREATE PUBLICATION
-</programlisting>
-<programlisting>
-test_sub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
-ALTER SUBSCRIPTION
+/* sub # */ ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
</programlisting></para>
<para>
@@ -1367,14 +1307,11 @@ ALTER SUBSCRIPTION
row filter of <literal>child</literal> (because
<literal>publish_via_partition_root</literal> is false).
<programlisting>
-test_pub=# TRUNCATE parent;
-TRUNCATE TABLE
-test_pub=# INSERT INTO parent VALUES (2), (4), (6);
-INSERT 0 3
-test_pub=# INSERT INTO child VALUES (3), (5), (7);
-INSERT 0 3
+/* pub # */ TRUNCATE parent;
+/* pub # */ INSERT INTO parent VALUES (2), (4), (6);
+/* pub # */ INSERT INTO child VALUES (3), (5), (7);
-test_pub=# SELECT * FROM parent ORDER BY a;
+/* pub # */ SELECT * FROM parent ORDER BY a;
a
---
2
@@ -1386,7 +1323,7 @@ test_pub=# SELECT * FROM parent ORDER BY a;
(6 rows)
</programlisting>
<programlisting>
-test_sub=# SELECT * FROM child ORDER BY a;
+/* sub # */ SELECT * FROM child ORDER BY a;
a
---
5
@@ -1505,8 +1442,7 @@ test_sub=# SELECT * FROM child ORDER BY a;
<para>
Create a table <literal>t1</literal> to be used in the following example.
<programlisting>
-test_pub=# CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
-CREATE TABLE
+/* pub # */ CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
</programlisting></para>
<para>
@@ -1515,15 +1451,14 @@ CREATE TABLE
replicated. Notice that the order of column names in the column list does
not matter.
<programlisting>
-test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d);
-CREATE PUBLICATION
+/* pub # */ CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d);
</programlisting></para>
<para>
<literal>psql</literal> can be used to show the column lists (if defined)
for each publication.
<programlisting>
-test_pub=# \dRp+
+/* pub # */ \dRp+
Publication p1
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
@@ -1536,7 +1471,7 @@ Tables:
<literal>psql</literal> can be used to show the column lists (if defined)
for each table.
<programlisting>
-test_pub=# \d t1
+/* pub # */ \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
@@ -1559,24 +1494,19 @@ Publications:
<literal>s1</literal> that subscribes to the publication
<literal>p1</literal>.
<programlisting>
-test_sub=# CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id));
-CREATE TABLE
-test_sub=# CREATE SUBSCRIPTION s1
-test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'
-test_sub-# PUBLICATION p1;
-CREATE SUBSCRIPTION
+/* sub # */ CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id));
+/* sub # */ CREATE SUBSCRIPTION s1
+/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s1'
+/* sub - */ PUBLICATION p1;
</programlisting></para>
<para>
On the publisher node, insert some rows to table <literal>t1</literal>.
<programlisting>
-test_pub=# INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
-INSERT 0 1
-test_pub=# INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');
-INSERT 0 1
-test_pub=# INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3');
-INSERT 0 1
-test_pub=# SELECT * FROM t1 ORDER BY id;
+/* pub # */ INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
+/* pub # */ INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');
+/* pub # */ INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3');
+/* pub # */ SELECT * FROM t1 ORDER BY id;
id | a | b | c | d | e
----+-----+-----+-----+-----+-----
1 | a-1 | b-1 | c-1 | d-1 | e-1
@@ -1589,7 +1519,7 @@ test_pub=# SELECT * FROM t1 ORDER BY id;
Only data from the column list of publication <literal>p1</literal> is
replicated.
<programlisting>
-test_sub=# SELECT * FROM t1 ORDER BY id;
+/* sub # */ SELECT * FROM t1 ORDER BY id;
id | b | a | d
----+-----+-----+-----
1 | b-1 | a-1 | d-1
@@ -1617,13 +1547,10 @@ test_sub=# SELECT * FROM t1 ORDER BY id;
For example, note below that subscriber table generated column value comes from the
subscriber column's calculation.
<programlisting>
-test_pub=# CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
-CREATE TABLE
-test_pub=# INSERT INTO tab_gen_to_gen VALUES (1),(2),(3);
-INSERT 0 3
-test_pub=# CREATE PUBLICATION pub1 FOR TABLE tab_gen_to_gen;
-CREATE PUBLICATION
-test_pub=# SELECT * FROM tab_gen_to_gen;
+/* pub # */ CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
+/* pub # */ INSERT INTO tab_gen_to_gen VALUES (1),(2),(3);
+/* pub # */ CREATE PUBLICATION pub1 FOR TABLE tab_gen_to_gen;
+/* pub # */ SELECT * FROM tab_gen_to_gen;
a | b
---+---
1 | 2
@@ -1631,11 +1558,9 @@ test_pub=# SELECT * FROM tab_gen_to_gen;
3 | 4
(3 rows)
-test_sub=# CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 100) STORED);
-CREATE TABLE
-test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
-CREATE SUBSCRIPTION
-test_sub=# SELECT * from tab_gen_to_gen;
+/* sub # */ CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 100) STORED);
+/* sub # */ CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
+/* sub # */ SELECT * from tab_gen_to_gen;
a | b
---+----
1 | 100
@@ -2690,8 +2615,7 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
<link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
e.g.:
<programlisting>
-node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
-ALTER SUBSCRIPTION
+/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
</programlisting>
</para>
</step>
@@ -2780,8 +2704,7 @@ pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
<xref linkend="two-node-cluster-disable-subscriptions-node2"/>
and now, e.g.:
<programlisting>
-node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
-CREATE TABLE
+/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</programlisting>
</para>
</step>
@@ -2793,8 +2716,7 @@ CREATE TABLE
<link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
e.g.:
<programlisting>
-node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
-ALTER SUBSCRIPTION
+/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
</programlisting>
</para>
</step>
@@ -2805,8 +2727,7 @@ ALTER SUBSCRIPTION
<link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
e.g.:
<programlisting>
-node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
-ALTER SUBSCRIPTION
+/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
</programlisting>
</para>
</step>
@@ -2844,8 +2765,7 @@ ALTER SUBSCRIPTION
<link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
e.g.:
<programlisting>
-node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
-ALTER SUBSCRIPTION
+/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
</programlisting>
</para>
</step>
@@ -2896,8 +2816,7 @@ pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
<link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
e.g.:
<programlisting>
-node3=# ALTER SUBSCRIPTION sub1_node2_node3 DISABLE;
-ALTER SUBSCRIPTION
+/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 DISABLE;
</programlisting>
</para>
</step>
@@ -2948,8 +2867,7 @@ pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
<xref linkend="cascaded-cluster-disable-sub-node1-node2"/>
and now, e.g.:
<programlisting>
-node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
-CREATE TABLE
+/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</programlisting>
</para>
</step>
@@ -2961,8 +2879,7 @@ CREATE TABLE
<link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
e.g.:
<programlisting>
-node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
-ALTER SUBSCRIPTION
+/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
</programlisting>
</para>
</step>
@@ -2973,8 +2890,7 @@ ALTER SUBSCRIPTION
<link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
e.g.:
<programlisting>
-node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
-ALTER SUBSCRIPTION
+/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
</programlisting>
</para>
</step>
@@ -3025,8 +2941,7 @@ pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile
<xref linkend="cascaded-cluster-disable-sub-node2-node3"/> and now,
e.g.:
<programlisting>
-node3=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
-CREATE TABLE
+/* node3 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</programlisting>
</para>
</step>
@@ -3038,8 +2953,7 @@ CREATE TABLE
<link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
e.g.:
<programlisting>
-node3=# ALTER SUBSCRIPTION sub1_node2_node3 ENABLE;
-ALTER SUBSCRIPTION
+/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 ENABLE;
</programlisting>
</para>
</step>
@@ -3050,8 +2964,7 @@ ALTER SUBSCRIPTION
<link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
e.g.:
<programlisting>
-node3=# ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION;
-ALTER SUBSCRIPTION
+/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION;
</programlisting>
</para>
</step>
@@ -3082,8 +2995,7 @@ ALTER SUBSCRIPTION
<link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
e.g.:
<programlisting>
-node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
-ALTER SUBSCRIPTION
+/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
</programlisting>
</para>
</step>
@@ -3134,8 +3046,7 @@ pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
<link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
e.g.:
<programlisting>
-node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
-ALTER SUBSCRIPTION
+/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
</programlisting>
</para>
</step>
@@ -3146,8 +3057,7 @@ ALTER SUBSCRIPTION
<literal>node2</literal> between <xref linkend="circular-cluster-disable-sub-node2"/>
and now, e.g.:
<programlisting>
-node1=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
-CREATE TABLE
+/* node1 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</programlisting>
</para>
</step>
@@ -3160,8 +3070,7 @@ CREATE TABLE
<link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
e.g.:
<programlisting>
-node1=# ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION;
-ALTER SUBSCRIPTION
+/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION;
</programlisting>
</para>
</step>
@@ -3173,8 +3082,7 @@ ALTER SUBSCRIPTION
<link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
e.g.:
<programlisting>
-node1=# ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
-ALTER SUBSCRIPTION
+/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
</programlisting>
</para>
</step>
@@ -3225,8 +3133,7 @@ pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
<link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
e.g.:
<programlisting>
-node1=# ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
-ALTER SUBSCRIPTION
+/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
</programlisting>
</para>
</step>
@@ -3237,8 +3144,7 @@ ALTER SUBSCRIPTION
the upgraded <literal>node1</literal> between <xref linkend="circular-cluster-disable-sub-node1"/>
and now, e.g.:
<programlisting>
-node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
-CREATE TABLE
+/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</programlisting>
</para>
</step>
@@ -3250,8 +3156,7 @@ CREATE TABLE
<link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
e.g.:
<programlisting>
-node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
-ALTER SUBSCRIPTION
+/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
</programlisting>
</para>
</step>
--
2.34.1
Re: doc: Make logical replication examples executable in bulk and legal sgml.
On Sun, May 4, 2025 at 2:03 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
While responding to a "our documentation is buggy" complaint I got annoyed in my attempt to reproduce the behavior by having to surgically copy line-by-line the DDL and DML code involved. Let's strive for a more copy-paste friendly example setup. No prompts and no interspersed command tags (they are ok if the script is one block and the output is another).
Hi David,
I think I was the author of most of these examples, so I feel obliged
to post my 2c.
For the logical replication examples it is really important to be
aware if the psql is executing at the publisher or subscriber side;
that is the main reason why I had kept prompts in the examples (e.g.
it avoided having to say "On the publisher:" or "On the subscriber:"
for every example).
OTOH, I understand your issue that those prompts made it difficult to
simply cut/paste slabs of psql directly from the examples -- I've
heard this complaint a few times before from other people.
So, your patch which still keeps my prompts albeit as "comments", but
that now allows the execution of the bulk copy/paste, seems like a
reasonable compromise to me.
~~
BTW, I can understand the commented prompt for the bulk fragments like:
/* sub # */ CREATE SUBSCRIPTION sub1
/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
/* sub - */ PUBLICATION pub1;
/* sub # */ CREATE SUBSCRIPTION sub2
/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub2'
/* sub - */ PUBLICATION pub2;
/* sub # */ CREATE SUBSCRIPTION sub3
/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub3'
/* sub - */ PUBLICATION pub3a, pub3b;
~
But, for the SELECT statements, commenting the prompt like:
/* sub # */ SELECT * FROM t1;
a | b
---+-------
1 | one
2 | two
3 | three
(3 rows)
doesn't help bulk execution at all because the result gets in the way
so you'll still end up surgically copying each SELECT. Since it fixes
nothing I assumed you just commented the SELECT prompts for
consistency with the other ones, right?
======
Kind Regards,
Peter Smith.
Fujitsu Australia
Re: doc: Make logical replication examples executable in bulk and legal sgml.
On Thu, May 15, 2025, 19:28 Peter Smith <smithpb2250@gmail.com> wrote:
doesn't help bulk execution at all because the result gets in the way
so you'll still end up surgically copying each SELECT. Since it fixes
nothing I assumed you just commented the SELECT prompts for
consistency with the other ones, right?
Only mostly for consistency. Being able to select entire lines and not
have to precision place the cursor at column 7 to avoid the prompt means
the comment for select has a usability aspect.
David J.
Re: doc: Make logical replication examples executable in bulk and legal sgml.
On Sat, May 3, 2025 at 9:33 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
While responding to a "our documentation is buggy" complaint I got annoyed in my attempt to reproduce the behavior by having to surgically copy line-by-line the DDL and DML code involved. Let's strive for a more copy-paste friendly example setup. No prompts and no interspersed command tags (they are ok if the script is one block and the output is another).
That's a valid point. Since this is not a correctness issue, I am less
inclined to backpatch. What do you or others think?
--
With Regards,
Amit Kapila.
On Thursday, May 22, 2025, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Sat, May 3, 2025 at 9:33 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:While responding to a "our documentation is buggy" complaint I got
annoyed in my attempt to reproduce the behavior by having to surgically
copy line-by-line the DDL and DML code involved. Let's strive for a more
copy-paste friendly example setup. No prompts and no interspersed command
tags (they are ok if the script is one block and the output is another).That's a valid point. Since this is not a correctness issue, I am less
inclined to backpatch. What do you or others think?
Agreed, this would not be back-patched.
David J.
Re: doc: Make logical replication examples executable in bulk and legal sgml.
On Fri, May 23, 2025 at 10:52 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
On Thursday, May 22, 2025, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Sat, May 3, 2025 at 9:33 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:While responding to a "our documentation is buggy" complaint I got annoyed in my attempt to reproduce the behavior by having to surgically copy line-by-line the DDL and DML code involved. Let's strive for a more copy-paste friendly example setup. No prompts and no interspersed command tags (they are ok if the script is one block and the output is another).
That's a valid point. Since this is not a correctness issue, I am less
inclined to backpatch. What do you or others think?Agreed, this would not be back-patched.
Yesterday, I pushed the patch. Thanks for working on this.
--
With Regards,
Amit Kapila.