On 26.11.24 13:18, Peter Eisentraut wrote:
I think this is the right idea, but after digging around a bit more, I
think more could/should be done.
After these changes, the difference between
get_equal_strategy_number_for_am() and get_equal_strategy_number() is
kind of pointless. We should really just use
get_equal_strategy_number() for all purposes.
But then you have the problem that IsIndexUsableForReplicaIdentityFull()
doesn't have the opclass IDs available in the IndexInfo structure. You
appear to have worked around that by writing
+ if (get_equal_strategy_number_for_am(indexInfo->ii_Am, InvalidOid)
== InvalidStrategy)
which I suppose will have the same ultimate result as before that patch,
but it seems kind of incomplete.
I figure this could all be simpler if
IsIndexUsableForReplicaIdentityFull() used the index relcache entry
directly instead of going the detour through IndexInfo. Then we have
all the information available, and this should ultimately all work
properly for suitable GiST indexes as well.
I have attached three patches that show how that could be done. (This
would work in conjunction with your new tests. (Although now we could
also test GiST with replica identity full?))
The comment block for IsIndexUsableForReplicaIdentityFull() makes a
bunch of claims that are not all explicitly supported by the code. The
code doesn't actually check the AM, this is all only done indirectly via
other checks. The second point (about tuples_equal()) appears to be
slightly wrong, because while you need an equals operator from the type
cache, that shouldn't prevent you from also using a different index AM
than btree or hash for the replica identity index. And the stuff about
amgettuple, if that is important, why is it only checked for assert builds?
I did some more work on this approach, with the attached patches
resulting. This is essentially what I'm describing above, which in turn
is a variation of your patch
v45-0001-Fix-logical-replication-for-temporal-tables.patch, with your
tests added at the end.
I also did some more work on IsIndexUsableForReplicaIdentityFull() to
make the various claims in the comments reflected by actual code. With
all of this, it can now also use gist indexes on the subscriber side in
cases of REPLICA IDENTITY FULL. This isn't immediately visible in the
tests, but you can see that the tests are using it internally by adding
debugging elogs or something like that.
Altogether, I think this fixes the original problem of temporal keys not
being handled properly in logical replication subscribers, and it makes
things less hardcoded around btree and hash in general.
Please review.
From 02a390446816da66204b696ac5ad189d5ce82835 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 4 Dec 2024 12:06:24 +0100
Subject: [PATCH v45.2 1/5] Improve internal logical replication error for
missing equality strategy
This "shouldn't happen", except right now it can with a temporal gist
index (to be fixed soon), because of missing gist support in
get_equal_strategy_number(). But right now, the error is not caught
right away, but instead you get the subsequent error about a "missing
operator 0". This makes the error more accurate.
Discussion:
https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mdhcy4_qq0+noc...@mail.gmail.com
---
src/backend/executor/execReplication.c | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/backend/executor/execReplication.c
b/src/backend/executor/execReplication.c
index cfdf2eedf4d..2ca8c5c50be 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -134,6 +134,8 @@ build_replindex_scan_key(ScanKey skey, Relation rel,
Relation idxrel,
optype = get_opclass_input_type(opclass->values[index_attoff]);
opfamily = get_opclass_family(opclass->values[index_attoff]);
eq_strategy =
get_equal_strategy_number(opclass->values[index_attoff]);
+ if (!eq_strategy)
+ elog(ERROR, "missing equal strategy for opclass %u",
opclass->values[index_attoff]);
operator = get_opfamily_member(opfamily, optype,
optype,
base-commit: 7727049e8f663344d4d0457e1d9ec048d626f3d9
--
2.47.1
From 649698b9f03a79a3d62d60e48b674c77dbbde269 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 4 Dec 2024 12:06:24 +0100
Subject: [PATCH v45.2 2/5] Replace get_equal_strategy_number_for_am() by
get_equal_strategy_number()
get_equal_strategy_number_for_am() gets the equal strategy number for
an AM. This currently only supports btree and hash. In the more
general case, this also depends on the operator class (see for example
GistTranslateStratnum()). To support that, replace this function with
get_equal_strategy_number() that takes an opclass and derives it from
there. (This function already existed before as a static function, so
the signature is kept for simplicity.)
This patch is only a refactoring, it doesn't add support for other
index AMs such as gist. This will be done separately.
Discussion:
https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mdhcy4_qq0+noc...@mail.gmail.com
---
src/backend/executor/execReplication.c | 17 +++--------------
src/backend/replication/logical/relation.c | 17 ++++++++++++-----
src/include/executor/executor.h | 2 +-
3 files changed, 16 insertions(+), 20 deletions(-)
diff --git a/src/backend/executor/execReplication.c
b/src/backend/executor/execReplication.c
index 2ca8c5c50be..2b42c0ae524 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -39,7 +39,7 @@ static bool tuples_equal(TupleTableSlot *slot1,
TupleTableSlot *slot2,
/*
* Returns the fixed strategy number, if any, of the equality operator for the
- * given index access method, otherwise, InvalidStrategy.
+ * given operator class, otherwise, InvalidStrategy.
*
* Currently, only Btree and Hash indexes are supported. The other index access
* methods don't have a fixed strategy for equality operation - instead, the
@@ -47,8 +47,9 @@ static bool tuples_equal(TupleTableSlot *slot1,
TupleTableSlot *slot2,
* according to the operator class's definition.
*/
StrategyNumber
-get_equal_strategy_number_for_am(Oid am)
+get_equal_strategy_number(Oid opclass)
{
+ Oid am = get_opclass_method(opclass);
int ret;
switch (am)
@@ -68,18 +69,6 @@ get_equal_strategy_number_for_am(Oid am)
return ret;
}
-/*
- * Return the appropriate strategy number which corresponds to the equality
- * operator.
- */
-static StrategyNumber
-get_equal_strategy_number(Oid opclass)
-{
- Oid am = get_opclass_method(opclass);
-
- return get_equal_strategy_number_for_am(am);
-}
-
/*
* Setup a ScanKey for a search in the relation 'rel' for a tuple 'key' that
* is setup to match 'rel' (*NOT* idxrel!).
diff --git a/src/backend/replication/logical/relation.c
b/src/backend/replication/logical/relation.c
index 71a1b7e01eb..f2fac57412e 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -29,6 +29,7 @@
#include "replication/logicalrelation.h"
#include "replication/worker_internal.h"
#include "utils/inval.h"
+#include "utils/syscache.h"
static MemoryContext LogicalRepRelMapContext = NULL;
@@ -815,7 +816,7 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel,
AttrMap *attrmap)
* The reasons why only Btree and Hash indexes can be considered as usable are:
*
* 1) Other index access methods don't have a fixed strategy for equality
- * operation. Refer get_equal_strategy_number_for_am().
+ * operation. Refer get_equal_strategy_number().
*
* 2) For indexes other than PK and REPLICA IDENTITY, we need to match the
* local and remote tuples. The equality routine tuples_equal() cannot accept
@@ -833,10 +834,7 @@ bool
IsIndexUsableForReplicaIdentityFull(Relation idxrel, AttrMap *attrmap)
{
AttrNumber keycol;
-
- /* Ensure that the index access method has a valid equal strategy */
- if (get_equal_strategy_number_for_am(idxrel->rd_rel->relam) ==
InvalidStrategy)
- return false;
+ oidvector *indclass;
/* The index must not be a partial index */
if (!heap_attisnull(idxrel->rd_indextuple, Anum_pg_index_indpred, NULL))
@@ -844,6 +842,15 @@ IsIndexUsableForReplicaIdentityFull(Relation idxrel,
AttrMap *attrmap)
Assert(idxrel->rd_index->indnatts >= 1);
+ indclass = (oidvector *)
DatumGetPointer(SysCacheGetAttrNotNull(INDEXRELID, idxrel->rd_indextuple,
Anum_pg_index_indclass));
+
+ /* Ensure that the index has a valid equal strategy for each key column
*/
+ for (int i = 0; i < idxrel->rd_index->indnkeyatts; i++)
+ {
+ if (get_equal_strategy_number(indclass->values[i]) ==
InvalidStrategy)
+ return false;
+ }
+
/* The leftmost index field must not be an expression */
keycol = idxrel->rd_index->indkey.values[0];
if (!AttributeNumberIsValid(keycol))
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 69c3ebff00a..e949cce7d98 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -658,7 +658,7 @@ extern void check_exclusion_constraint(Relation heap,
Relation index,
/*
* prototypes from functions in execReplication.c
*/
-extern StrategyNumber get_equal_strategy_number_for_am(Oid am);
+extern StrategyNumber get_equal_strategy_number(Oid opclass);
extern bool RelationFindReplTupleByIndex(Relation rel, Oid idxoid,
LockTupleMode lockmode,
TupleTableSlot *searchslot,
--
2.47.1
From a0fbbe282649865e67f71152481a0c44e6e79f39 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 4 Dec 2024 12:06:24 +0100
Subject: [PATCH v45.2 3/5] Make the conditions in
IsIndexUsableForReplicaIdentityFull() more explicit
IsIndexUsableForReplicaIdentityFull() described a number of conditions
that a suitable index has to fulfull. But not all of these were
actually checked in the code. Instead, it appeared to rely on
get_equal_strategy_number() to filter out any indexes that are not
btree or hash. As we look to generalize index AM capabilities, this
would possibly break if we added additional support in
get_equal_strategy_number(). Instead, write out code to check for the
required capabilities explicitly. This shouldn't change any behaviors
at the moment.
Discussion:
https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mdhcy4_qq0+noc...@mail.gmail.com
---
src/backend/replication/logical/relation.c | 52 +++++++++++-----------
1 file changed, 25 insertions(+), 27 deletions(-)
diff --git a/src/backend/replication/logical/relation.c
b/src/backend/replication/logical/relation.c
index f2fac57412e..b49b0af2f62 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -17,9 +17,7 @@
#include "postgres.h"
-#ifdef USE_ASSERT_CHECKING
#include "access/amapi.h"
-#endif
#include "access/genam.h"
#include "access/table.h"
#include "catalog/namespace.h"
@@ -798,9 +796,10 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel,
AttrMap *attrmap)
/*
* Returns true if the index is usable for replica identity full.
*
- * The index must be btree or hash, non-partial, and the leftmost field must be
- * a column (not an expression) that references the remote relation column.
These
- * limitations help to keep the index scan similar to PK/RI index scans.
+ * The index must have an equal strategy for each key column, be non-partial,
+ * and the leftmost field must be a column (not an expression) that references
+ * the remote relation column. These limitations help to keep the index scan
+ * similar to PK/RI index scans.
*
* attrmap is a map of local attributes to remote ones. We can consult this
* map to check whether the local index attribute has a corresponding remote
@@ -813,19 +812,6 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel,
AttrMap *attrmap)
* compare the tuples for non-PK/RI index scans. See
* RelationFindReplTupleByIndex().
*
- * The reasons why only Btree and Hash indexes can be considered as usable are:
- *
- * 1) Other index access methods don't have a fixed strategy for equality
- * operation. Refer get_equal_strategy_number().
- *
- * 2) For indexes other than PK and REPLICA IDENTITY, we need to match the
- * local and remote tuples. The equality routine tuples_equal() cannot accept
- * a datatype (e.g. point or box) that does not have a default operator class
- * for Btree or Hash.
- *
- * XXX: Note that BRIN and GIN indexes do not implement "amgettuple" which
- * will be used later to fetch the tuples. See RelationFindReplTupleByIndex().
- *
* XXX: To support partial indexes, the required changes are likely to be
larger.
* If none of the tuples satisfy the expression for the index scan, we
fall-back
* to sequential execution, which might not be a good idea in some cases.
@@ -851,6 +837,21 @@ IsIndexUsableForReplicaIdentityFull(Relation idxrel,
AttrMap *attrmap)
return false;
}
+ /*
+ * For indexes other than PK and REPLICA IDENTITY, we need to match the
+ * local and remote tuples. The equality routine tuples_equal() cannot
+ * accept a data type where the type cache cannot provide an equality
+ * operator.
+ */
+ for (int i = 0; i < idxrel->rd_att->natts; i++)
+ {
+ TypeCacheEntry *typentry;
+
+ typentry = lookup_type_cache(TupleDescAttr(idxrel->rd_att,
i)->atttypid, TYPECACHE_EQ_OPR_FINFO);
+ if (!OidIsValid(typentry->eq_opr_finfo.fn_oid))
+ return false;
+ }
+
/* The leftmost index field must not be an expression */
keycol = idxrel->rd_index->indkey.values[0];
if (!AttributeNumberIsValid(keycol))
@@ -865,15 +866,12 @@ IsIndexUsableForReplicaIdentityFull(Relation idxrel,
AttrMap *attrmap)
attrmap->attnums[AttrNumberGetAttrOffset(keycol)] < 0)
return false;
-#ifdef USE_ASSERT_CHECKING
- {
- IndexAmRoutine *amroutine;
-
- /* The given index access method must implement amgettuple. */
- amroutine = GetIndexAmRoutineByAmId(idxrel->rd_rel->relam,
false);
- Assert(amroutine->amgettuple != NULL);
- }
-#endif
+ /*
+ * The given index access method must implement "amgettuple", which will
+ * be used later to fetch the tuples. See
RelationFindReplTupleByIndex().
+ */
+ if (GetIndexAmRoutineByAmId(idxrel->rd_rel->relam, false)->amgettuple
== NULL)
+ return false;
return true;
}
--
2.47.1
From 8218b5cc61e7df14bc6ec24d7618a3a1bbca96df Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 4 Dec 2024 12:06:24 +0100
Subject: [PATCH v45.2 4/5] Support for GiST in get_equal_strategy_number()
A WITHOUT OVERLAPS primary key or unique constraint is accepted as a
REPLICA IDENTITY, since it guarantees uniqueness. But subscribers
applying logical decoding messages would fail because there was not
support for looking up the equals operator for a gist index. This
fixes that: For GiST indexes we can use the stratnum GiST support
function.
Discussion:
https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mdhcy4_qq0+noc...@mail.gmail.com
---
src/backend/executor/execReplication.c | 10 ++++------
1 file changed, 4 insertions(+), 6 deletions(-)
diff --git a/src/backend/executor/execReplication.c
b/src/backend/executor/execReplication.c
index 2b42c0ae524..fddbfaba7ab 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -15,6 +15,7 @@
#include "postgres.h"
#include "access/genam.h"
+#include "access/gist.h"
#include "access/relscan.h"
#include "access/tableam.h"
#include "access/transam.h"
@@ -40,11 +41,6 @@ static bool tuples_equal(TupleTableSlot *slot1,
TupleTableSlot *slot2,
/*
* Returns the fixed strategy number, if any, of the equality operator for the
* given operator class, otherwise, InvalidStrategy.
- *
- * Currently, only Btree and Hash indexes are supported. The other index access
- * methods don't have a fixed strategy for equality operation - instead, the
- * support routines of each operator class interpret the strategy numbers
- * according to the operator class's definition.
*/
StrategyNumber
get_equal_strategy_number(Oid opclass)
@@ -60,8 +56,10 @@ get_equal_strategy_number(Oid opclass)
case HASH_AM_OID:
ret = HTEqualStrategyNumber;
break;
+ case GIST_AM_OID:
+ ret = GistTranslateStratnum(opclass,
RTEqualStrategyNumber);
+ break;
default:
- /* XXX: Only Btree and Hash indexes are supported */
ret = InvalidStrategy;
break;
}
--
2.47.1
From 036b076d4559c023e81ee5228116da752bbe66e4 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 4 Dec 2024 12:06:24 +0100
Subject: [PATCH v45.2 5/5] Tests for logical replication with temporal keys
Author: Paul A. Jungwirth <p...@illuminatedcomputing.com>
Discussion:
https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mdhcy4_qq0+noc...@mail.gmail.com
---
src/test/subscription/meson.build | 1 +
src/test/subscription/t/034_temporal.pl | 673 ++++++++++++++++++++++++
2 files changed, 674 insertions(+)
create mode 100644 src/test/subscription/t/034_temporal.pl
diff --git a/src/test/subscription/meson.build
b/src/test/subscription/meson.build
index c591cd7d619..b2395e7b57e 100644
--- a/src/test/subscription/meson.build
+++ b/src/test/subscription/meson.build
@@ -40,6 +40,7 @@ tests += {
't/031_column_list.pl',
't/032_subscribe_use_index.pl',
't/033_run_as_table_owner.pl',
+ 't/034_temporal.pl',
't/100_bugs.pl',
],
},
diff --git a/src/test/subscription/t/034_temporal.pl
b/src/test/subscription/t/034_temporal.pl
new file mode 100644
index 00000000000..0f501f1cee8
--- /dev/null
+++ b/src/test/subscription/t/034_temporal.pl
@@ -0,0 +1,673 @@
+
+# Copyright (c) 2021-2024, PostgreSQL Global Development Group
+
+# Logical replication tests for temporal tables
+#
+# A table can use a temporal PRIMARY KEY or UNIQUE index as its REPLICA
IDENTITY.
+# This is a GiST index (not B-tree) and its last element uses WITHOUT OVERLAPS.
+# That element restricts other rows with overlaps semantics instead of
equality,
+# but it is always at least as restrictive as a normal non-null unique index.
+# Therefore we can still apply logical decoding messages to the subscriber.
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# setup
+
+my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
+$node_subscriber->init;
+$node_subscriber->start;
+
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+
+my ($result, $stdout, $stderr);
+
+# #################################
+# Test with REPLICA IDENTITY DEFAULT:
+# #################################
+
+# create tables on publisher
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a
text)"
+);
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at
daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+
+# create tables on subscriber
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a
text)"
+);
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at
daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+
+# sync initial data:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_no_key (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_pk (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_unique (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION pub1 FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION
pub1"
+);
+$node_subscriber->wait_for_subscription_sync;
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result,
+ qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_no_key DEFAULT');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is($result, qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_pk DEFAULT');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result,
+ qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_unique DEFAULT');
+
+# replicate with no key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_no_key (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "UPDATE temporal_no_key SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot update table "temporal_no_key"
because it does not have a replica identity and publishes updates
+HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't UPDATE temporal_no_key DEFAULT");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_no_key WHERE id = '[3,4)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_no_key"
because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER
TABLE.),
+ "can't DELETE temporal_no_key DEFAULT");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_no_key DEFAULT');
+
+# replicate with a primary key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_pk (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_pk WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk DEFAULT');
+
+# replicate with a unique key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_unique (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot update table "temporal_unique"
because it does not have a replica identity and publishes updates
+HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't UPDATE temporal_unique DEFAULT");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_unique WHERE id = '[3,4)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_unique"
because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER
TABLE.),
+ "can't DELETE temporal_unique DEFAULT");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique DEFAULT');
+
+# cleanup
+
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_no_key");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub1");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_no_key");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1");
+
+
+# #################################
+# Test with REPLICA IDENTITY FULL:
+# #################################
+
+# create tables on publisher
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a
text)"
+);
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_no_key REPLICA IDENTITY FULL");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY FULL");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at
daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_unique REPLICA IDENTITY FULL");
+
+# create tables on subscriber
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a
text)"
+);
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_no_key REPLICA IDENTITY FULL");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY FULL");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at
daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_unique REPLICA IDENTITY FULL");
+
+# sync initial data:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_no_key (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_pk (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_unique (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION pub1 FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION
pub1"
+);
+$node_subscriber->wait_for_subscription_sync;
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result,
+ qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_no_key FULL');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is($result, qq{[1,2)|[2000-01-01,2010-01-01)|a}, 'synced temporal_pk FULL');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result,
+ qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_unique FULL');
+
+# replicate with no key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_no_key (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_no_key SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_no_key WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_no_key FULL');
+
+# replicate with a primary key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_pk (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_pk WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk FULL');
+
+# replicate with a unique key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_unique (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_unique WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique FULL');
+
+# cleanup
+
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_no_key");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub1");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_no_key");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1");
+
+
+# #################################
+# Test with REPLICA IDENTITY USING INDEX
+# #################################
+
+# create tables on publisher
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY USING INDEX
temporal_pk_pkey");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at
daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_unique REPLICA IDENTITY USING INDEX
temporal_unique_id_valid_at_key"
+);
+
+# create tables on subscriber
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY USING INDEX
temporal_pk_pkey");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at
daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_unique REPLICA IDENTITY USING INDEX
temporal_unique_id_valid_at_key"
+);
+
+# sync initial data:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_pk (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_unique (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION pub1 FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION
pub1"
+);
+$node_subscriber->wait_for_subscription_sync;
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result,
+ qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_pk USING INDEX');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result,
+ qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_unique USING INDEX');
+
+# replicate with a primary key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_pk (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_pk WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk USING INDEX');
+
+# replicate with a unique key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_unique (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM temporal_unique WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique USING INDEX');
+
+# cleanup
+
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub1");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1");
+
+
+# #################################
+# Test with REPLICA IDENTITY NOTHING
+# #################################
+
+# create tables on publisher
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a
text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_no_key REPLICA IDENTITY NOTHING");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY NOTHING");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE temporal_unique (id int4range, valid_at daterange, a
text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_unique REPLICA IDENTITY NOTHING");
+
+# create tables on subscriber
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a
text)"
+);
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_no_key REPLICA IDENTITY NOTHING");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY NOTHING");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE temporal_unique (id int4range, valid_at daterange, a
text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_unique REPLICA IDENTITY NOTHING");
+
+# sync initial data:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_no_key (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_pk (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_unique (id, valid_at, a)
+ VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION pub1 FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION
pub1"
+);
+$node_subscriber->wait_for_subscription_sync;
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result,
+ qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_no_key NOTHING');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is($result, qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_pk NOTHING');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result,
+ qq{[1,2)|[2000-01-01,2010-01-01)|a},
+ 'synced temporal_unique NOTHING');
+
+# replicate with no key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_no_key (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "UPDATE temporal_no_key SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot update table "temporal_no_key"
because it does not have a replica identity and publishes updates
+HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't UPDATE temporal_no_key NOTHING");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_no_key WHERE id = '[3,4)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_no_key"
because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER
TABLE.),
+ "can't DELETE temporal_no_key NOTHING");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_no_key NOTHING');
+
+# replicate with a primary key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_pk (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot update table "temporal_pk" because it
does not have a replica identity and publishes updates
+HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't UPDATE temporal_pk NOTHING");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_pk WHERE id = '[3,4)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_pk"
because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER
TABLE.),
+ "can't DELETE temporal_pk NOTHING");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk NOTHING');
+
+# replicate with a unique key:
+
+$node_publisher->safe_psql(
+ 'postgres',
+ "INSERT INTO temporal_unique (id, valid_at, a)
+ VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+ ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot update table "temporal_unique"
because it does not have a replica identity and publishes updates
+HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+ "can't UPDATE temporal_unique NOTHING");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+ "DELETE FROM temporal_unique WHERE id = '[3,4)'");
+is( $stderr,
+ qq(psql:<stdin>:1: ERROR: cannot delete from table "temporal_unique"
because it does not have a replica identity and publishes deletes
+HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER
TABLE.),
+ "can't DELETE temporal_unique NOTHING");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique NOTHING');
+
+# cleanup
+
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub1");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1");
+
+
+done_testing();
--
2.47.1
