On 12/4/24 03:15, Peter Eisentraut wrote:
>> 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.
These five patches all look good to me.
Note that my tests already include a section for REPLICA IDENTITY FULL, which passed. But the
subscriber was using a SeqScan to look up tuples to update.
Here are the steps (mostly just because it was confusing for me at first): First in
FindUsableIndexForReplicaIdentityFull, we would call IsIndexUsableForReplicaIdentityFull, get back
false, and decide there was no index to use. Then in FindReplTupleInLocalRel, localidxoid was 0, so
we woudln't call IsIndexUsableForReplicaIdentityFull at all.
After applying the five patches, I can see that we choose the index and call
IsIndexUsableForReplicaIdentityFull from both sites. This should make applying changes a lot faster.
Here are those patches again, but incoporating Vignesh's feedback:
On 12/5/24 01:39, vignesh C wrote:
> 1) wait_for_catchup and data validation can be done after insertion
> itself, update and delete error validation can happen later:
> +($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');
I'd like to keep the order of tests the same for all scenarios, and sometimes update+delete succeed
and sometimes they fail. The data validation includes changes caused by the update+delete. Even when
they fail, putting the data validation at the end shows that they had no effect.
> 2) Copyright need not mention "2021-"
> 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
> +#
Okay.
> 3) This statement seems very long in a single line, could we split it
> into multiple lines:
> @@ -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;
> + }
Reformatted.
> 4) The commit message had a small typo, should "fulfull" be "fulfill":
> IsIndexUsableForReplicaIdentityFull() described a number of conditions
> that a suitable index has to fulfull. But not all of these were
Fixed.
> 5) temporal_no_key table is not dropped:
> +$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
Fixed as part of #7 below.
> 6) Since this is common to first and last test we can have it in a
> subroutine and use it for both:
> +# 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))"
> +);
> +
Done.
> 7) Similarly the drop tables can be moved to a subroutine and used as
> it is being used in multiple tests:
> +# 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");
Done.
Thanks!
Rebased to 3220ceaf77.
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.com
From bd957b368f711788e953072e461857c224a9c1e0 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.3 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,
--
2.42.0
From 7bee24f1bbf210e26eae0b6b799176b7923c795f 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.3 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 | 19 ++++++++++++++-----
src/include/executor/executor.h | 2 +-
3 files changed, 18 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..c3799a6185e 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,17 @@ 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.42.0
From c957288f978aec59e8bd4ebc6b35039520f818b2 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.3 3/5] Make the conditions in
IsIndexUsableForReplicaIdentityFull() more explicit
IsIndexUsableForReplicaIdentityFull() described a number of conditions
that a suitable index has to fulfill. 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 c3799a6185e..dd8a3809096 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.
@@ -853,6 +839,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))
@@ -867,15 +868,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.42.0
From 2a302f511a9b013aef290b1a641ec6ccc4075080 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.3 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.42.0
From 47fd028f5950af76e878c33cb9d3da4a8adf6da7 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.3 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 | 623 ++++++++++++++++++++++++
2 files changed, 624 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..88332581a9d
--- /dev/null
+++ b/src/test/subscription/t/034_temporal.pl
@@ -0,0 +1,623 @@
+
+# Copyright (c) 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);
+
+sub create_tables() {
+ # 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, valid_at daterange, 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, valid_at daterange, a text,
UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+ );
+}
+
+sub drop_everything() {
+ $node_publisher->safe_psql('postgres', "DROP TABLE IF EXISTS
temporal_no_key");
+ $node_publisher->safe_psql('postgres', "DROP TABLE IF EXISTS temporal_pk");
+ $node_publisher->safe_psql('postgres', "DROP TABLE IF EXISTS
temporal_unique");
+ $node_publisher->safe_psql('postgres', "DROP PUBLICATION pub1");
+ $node_subscriber->safe_psql('postgres', "DROP TABLE IF EXISTS
temporal_no_key");
+ $node_subscriber->safe_psql('postgres', "DROP TABLE IF EXISTS temporal_pk");
+ $node_subscriber->safe_psql('postgres', "DROP TABLE IF EXISTS
temporal_unique");
+ $node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1");
+}
+
+# #################################
+# Test with REPLICA IDENTITY DEFAULT:
+# #################################
+
+create_tables();
+
+# 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
+
+drop_everything();
+
+
+# #################################
+# Test with REPLICA IDENTITY FULL:
+# #################################
+
+create_tables();
+
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_no_key REPLICA IDENTITY FULL");
+
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY FULL");
+
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_unique REPLICA IDENTITY FULL");
+
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_no_key REPLICA IDENTITY FULL");
+
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY FULL");
+
+$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
+
+drop_everything();
+
+
+# #################################
+# 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
+
+drop_everything();
+
+
+# #################################
+# Test with REPLICA IDENTITY NOTHING
+# #################################
+
+create_tables();
+
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_no_key REPLICA IDENTITY NOTHING");
+
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY NOTHING");
+
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE temporal_unique REPLICA IDENTITY NOTHING");
+
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_no_key REPLICA IDENTITY NOTHING");
+
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE temporal_pk REPLICA IDENTITY NOTHING");
+
+$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
+
+drop_everything();
+
+done_testing();
--
2.42.0
