# Copyright (c) 2026, PostgreSQL Global Development Group
#
# Cross-database portability tests.  The same schema and data are created in
# two databases.  Type, relation and index OIDs differ, but logical checksums
# must match; physical checksums must not.

use strict;
use warnings FATAL => 'all';

use PostgreSQL::Test::Utils;
use Test::More;
use PostgreSQL::Test::Cluster;

my $node = PostgreSQL::Test::Cluster->new('checksum_crossdb');
$node->init;
$node->start;

$node->safe_psql('postgres', 'CREATE DATABASE cross_a TEMPLATE template0');
$node->safe_psql('postgres', 'CREATE DATABASE cross_b TEMPLATE template0');

foreach my $db ('cross_a', 'cross_b')
{
    $node->safe_psql($db, 'CREATE EXTENSION pg_integrity_checksums;');
}

my $setup = q{
CREATE TYPE cross_mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE cross_pair AS (a integer, b text);
CREATE DOMAIN cross_positive AS integer CHECK (VALUE > 0);

CREATE TABLE cross_t (
    id integer,
    n numeric,
    f float8,
    txt text,
    js jsonb,
    b bytea,
    arr integer[],
    pair cross_pair,
    mood cross_mood,
    dom cross_positive,
    rng int4range,
    u uuid,
    net inet,
    bits bit(8)
);

INSERT INTO cross_t VALUES
    (1, 1.0, 'NaN', 'one', '{"a": 1}', '\x0102',
     ARRAY[1, 2, NULL], ROW(1, 'x')::cross_pair, 'ok', 5, '[1,3)',
     '11111111-1111-1111-1111-111111111111', '192.168.0.1', B'10101010'),
    (2, 1.00, '-0'::float8, 'two', '{"b": [true, null]}', '\x00',
     ARRAY[[1,2],[3,4]], ROW(2, 'y')::cross_pair, 'happy', 7, '(1,4]',
     '22222222-2222-2222-2222-222222222222', '2001:db8::1', B'00000001'),
    (3, 'NaN'::numeric, 1.5, 'three', '{}', NULL,
     ARRAY[]::integer[], NULL, 'sad', 9, 'empty',
     '33333333-3333-3333-3333-333333333333', '10.0.0.0/8', B'11111111');

CREATE TABLE cross_nopk (id integer, v text);
INSERT INTO cross_nopk VALUES (1, 'duplicate'), (1, 'duplicate');

CREATE TABLE cross_empty (id integer);

CREATE TABLE cross_toast (id integer, v text);
INSERT INTO cross_toast
SELECT gs, repeat(md5(gs::text), 100)
FROM generate_series(1, 30) gs;

CREATE TABLE cross_part (id integer, region text) PARTITION BY LIST (region);
CREATE TABLE cross_part_a PARTITION OF cross_part FOR VALUES IN ('a');
CREATE TABLE cross_part_b PARTITION OF cross_part FOR VALUES IN ('b');
INSERT INTO cross_part VALUES (1, 'a'), (2, 'b'), (3, 'a');
CREATE INDEX cross_part_idx ON cross_part (id);

CREATE MATERIALIZED VIEW cross_mv AS SELECT id, n FROM cross_t;

CREATE INDEX cross_btree ON cross_t (txt);
CREATE INDEX cross_hash ON cross_t USING hash (txt);
CREATE INDEX cross_gin ON cross_t USING gin (js);
CREATE INDEX cross_gist ON cross_t USING gist (rng);
CREATE INDEX cross_spgist ON cross_t USING spgist (txt);
CREATE INDEX cross_brin ON cross_t USING brin (id);
CREATE INDEX cross_expr ON cross_t ((id + 1));
CREATE INDEX cross_partial ON cross_t (txt) WHERE id > 1;
CREATE INDEX cross_include ON cross_t (id) INCLUDE (txt);
};

$node->safe_psql('cross_a', $setup);
$node->safe_psql('cross_b', $setup);

my $server_version = $node->safe_psql('postgres', 'SHOW server_version_num');
if ($server_version >= 140000)
{
    foreach my $db ('cross_a', 'cross_b')
    {
        $node->safe_psql($db, 'ALTER TABLE cross_t ADD COLUMN mr int4multirange');
        $node->safe_psql($db,
            "UPDATE cross_t SET mr = int4range(id, id + 2)::int4multirange");
    }
}

sub logical_pair
{
    my ($sql) = @_;
    return ($node->safe_psql('cross_a', $sql),
            $node->safe_psql('cross_b', $sql));
}

# Table logical checksums are independent of relation OIDs.
foreach my $table ('cross_t', 'cross_nopk', 'cross_empty', 'cross_toast',
                   'cross_part', 'cross_mv')
{
    my ($a, $b) = logical_pair(
        "SELECT pg_table_logical_checksum('$table'::regclass)");
    is($a, $b, "table logical checksum matches across databases: $table");
}

# Index logical checksums are independent of index OIDs.
foreach my $index ('cross_btree', 'cross_hash', 'cross_gin', 'cross_gist',
                   'cross_spgist', 'cross_brin', 'cross_expr',
                   'cross_partial', 'cross_include', 'cross_part_idx')
{
    my ($a, $b) = logical_pair(
        "SELECT pg_index_logical_checksum('$index'::regclass)");
    is($a, $b, "index logical checksum matches across databases: $index");
}

# Database logical checksums include user tables and, optionally, indexes.
foreach my $args ('false, false', 'false, true')
{
    my ($a, $b) = logical_pair(
        "SELECT pg_database_logical_checksum($args)");
    is($a, $b, "database logical checksum matches across databases ($args)");
}

# Tuple and cell logical checksums are independent of physical location.
my ($tuple_a, $tuple_b) = logical_pair(
    "SELECT pg_tuple_logical_checksum('cross_t'::regclass, ctid)
       FROM cross_t WHERE id = 1");
is($tuple_a, $tuple_b, 'tuple logical checksum matches across databases');

my ($cell_a, $cell_b) = logical_pair(
    "SELECT pg_cell_checksum('cross_t'::regclass, ctid, 4)
       FROM cross_t WHERE id = 1");
is($cell_a, $cell_b, 'cell logical checksum matches across databases');

# Bucket assignment and key hashes do not depend on OIDs either: the key
# columns below have an enum, a composite and a domain type, whose OIDs
# differ between the two databases.
foreach my $args ("'cross_t', 4, '{mood,pair,dom}'", "'cross_t', 4, '{}'",
                  "'cross_nopk', 2", "'cross_part', 3", "'cross_toast', 8")
{
    my ($a, $b) = logical_pair(
        "SELECT string_agg(bucket || ':' || row_count || ':' ||
                           encode(checksum, 'hex'), ',' ORDER BY bucket)
           FROM pg_table_bucket_checksums($args)");
    is($a, $b, "bucket checksums match across databases ($args)");
}

my ($rows_a, $rows_b) = logical_pair(
    "SELECT string_agg(encode(key_hash, 'hex') || ':' ||
                       encode(row_checksum, 'hex'), ',' ORDER BY key_hash)
       FROM pg_table_row_checksums('cross_t', 4, NULL, '{mood,pair,dom}')");
is($rows_a, $rows_b, 'row checksums and key hashes match across databases');

# A changed row is found through its bucket.
$node->safe_psql('cross_b', "UPDATE cross_toast SET v = 'changed' WHERE id = 17");
my $bucket_sql = "SELECT bucket || ':' || encode(checksum, 'hex')
                    FROM pg_table_bucket_checksums('cross_toast', 8, '{id}')
                   ORDER BY bucket";
my %buckets_a = map { split /:/ } split /\n/, $node->safe_psql('cross_a', $bucket_sql);
my %buckets_b = map { split /:/ } split /\n/, $node->safe_psql('cross_b', $bucket_sql);
my @differing = sort { $a <=> $b }
  grep { $buckets_a{$_} ne $buckets_b{$_} } keys %buckets_a;
is(scalar(@differing), 1, 'one bucket differs after changing one row');

my $rows_sql = "SELECT key || ':' || encode(row_checksum, 'hex')
                  FROM pg_table_row_checksums('cross_toast', 8,
                                              '{$differing[0]}', '{id}')";
my %rowmap_a = map { split /:/ } split /\n/, $node->safe_psql('cross_a', $rows_sql);
my %rowmap_b = map { split /:/ } split /\n/, $node->safe_psql('cross_b', $rows_sql);
my @changed = grep { $rowmap_a{$_} ne $rowmap_b{$_} } keys %rowmap_a;
is_deeply(\@changed, ['(17)'], 'the changed row is found in that bucket');

# Physical checksums include relation/storage identity and must differ.
my $phys_table_a = $node->safe_psql('cross_a',
    "SELECT pg_table_physical_checksum('cross_t'::regclass, false)");
my $phys_table_b = $node->safe_psql('cross_b',
    "SELECT pg_table_physical_checksum('cross_t'::regclass, false)");
isnt($phys_table_a, $phys_table_b,
     'table physical checksum differs across databases');

my $phys_db_a = $node->safe_psql('cross_a',
    'SELECT pg_database_physical_checksum(false, false)');
my $phys_db_b = $node->safe_psql('cross_b',
    'SELECT pg_database_physical_checksum(false, false)');
isnt($phys_db_a, $phys_db_b,
     'database physical checksum differs across databases');

# System-catalog logical checksums must also be available.  They are not
# compared across databases because catalog rows contain database-local OIDs.
foreach my $db ('cross_a', 'cross_b')
{
    my $catalog = $node->safe_psql($db,
        "SELECT pg_table_logical_checksum('pg_statistic'::regclass) IS NOT NULL");
    is($catalog, 't', "system catalog logical checksum works in $db");

    my $with_system = $node->safe_psql($db,
        'SELECT pg_database_logical_checksum(true, true) IS NOT NULL');
    is($with_system, 't', "database logical checksum with system catalogs works in $db");
}

$node->safe_psql('postgres', 'DROP DATABASE cross_a');
$node->safe_psql('postgres', 'DROP DATABASE cross_b');
$node->stop;

done_testing();
