Quelle 034_temporal.pl

Sprache: Shell

# Copyright (c) 2024-2025, 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();

Messung V0.5 in Prozent

¤ Dauer der Verarbeitung: 0.13 Sekunden (vorverarbeitet am 2026-08-10) ¤

Wurzel

Suchen

PVS Prover

Isabelle Prover

NIST Cobol Testsuite

Cephes Mathematical Library

Vienna Development Method

Haftungshinweis

Die Informationen auf dieser Webseite wurden nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit, noch Qualität der bereit gestellten Informationen zugesichert.

Bemerkung:

Die farbliche Syntaxdarstellung und die Messung sind noch experimentell.