Content-Type: multipart/mixed; boundary="------------4KD3a00f8BF7gUQgELKa90SG"
Message-ID: <bc2bd0cc-4a19-b311-043f-f9a40e71dfe8@gmail.com>
Date: Mon, 5 Jun 2023 12:00:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: BUG #17959: amcheck fails to find a matching index tuple for an
 invisible heap tuple
Content-Language: en-US
To: pgsql-bugs@lists.postgresql.org
References: <17959-0d691c29037366ab@postgresql.org>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <17959-0d691c29037366ab@postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/bc2bd0cc-4a19-b311-043f-f9a40e71dfe8%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------4KD3a00f8BF7gUQgELKa90SG
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

03.06.2023 22:00, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference:      17959
> Logged by:          Alexander Lakhin
> Email address:      exclusion@gmail.com
> PostgreSQL version: 16beta1
> Operating system:   Ubuntu 22.04
> Description:
>
> After some DDL/transactional operations (a reproducer to follow)
> pg_amcheck detects an anomaly:
> btree index "regress001.pg_catalog.pg_depend_reference_index":
>      ERROR:  heap tuple (13,35) from table "pg_depend" lacks matching index
> tuple within index "pg_depend_reference_index"

The operations that trigger that anomaly are as follows:
my $bsession1 = $node->background_psql('regress001');
$bsession1->query_safe("create temp table t1(a int)");
my $bsession2 = $node->background_psql('regress002');
$bsession2->query_safe("begin transaction");
$bsession2->query_safe("prepare transaction 'pt1'");
$bsession1->quit;
my $bsession3 = $node->background_psql('regress001');
$bsession3->query_safe("create temp table t1(a int)");
$bsession3->query_safe("vacuum t1");
$bsession2->quit;
$bsession3->quit;

A complete TAP test to reproduce the issue is attached.
I put it in src/bin/pg_amcheck/t/, run (on master, e6a254c0d)
PROVE_TESTS=t/099_pdri_error.pl make -s check -C src/bin/pg_amcheck/
and get:

#   Failed test 'pg_amcheck after manipulations stdout /(?^:^$)/'
#   at t/099_pdri_error.pl line 60.
#                   'btree index "regress001.pg_catalog.pg_depend_reference_index":
#     ERROR:  heap tuple (13,35) from table "pg_depend" lacks matching index tuple within index "pg_depend_reference_index"
# '
#     doesn't match '(?^:^$)'

Best regards,
Alexander
--------------4KD3a00f8BF7gUQgELKa90SG
Content-Type: application/x-perl; name="099_pdri_error.pl"
Content-Disposition: attachment; filename="099_pdri_error.pl"
Content-Transfer-Encoding: 7bit

use strict;
use warnings;

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

use Test::More;

my ($node, $result);

$node = PostgreSQL::Test::Cluster->new('test');
$node->init;
$node->append_conf('postgresql.conf', "
log_connections = on
log_disconnections = on
autovacuum = off
fsync = off
max_prepared_transactions = 10
");
$node->start;
$node->safe_psql('postgres', 'CREATE DATABASE regress001');
$node->safe_psql('postgres', 'CREATE DATABASE regress002');
$node->safe_psql('regress001', 'CREATE EXTENSION pageinspect');
$node->command_checks_all(['pg_amcheck', '-d', 'regress001', '--install-missing', '--heapallindexed', '--rootdescend' ],
    0, [qr/^$/], [qr/^$/], 'pg_amcheck before manipulations');


my $bsession1 = $node->background_psql('regress001');
$bsession1->query_safe("create temp table t1(a int)");
my $bsession2 = $node->background_psql('regress002');
$bsession2->query_safe("begin transaction");
$bsession2->query_safe("prepare transaction 'pt1'");
$bsession1->quit;
my $bsession3 = $node->background_psql('regress001');
$bsession3->query_safe("create temp table t1(a int)");
$bsession3->query_safe("vacuum t1");
$bsession2->quit;
$bsession3->quit;

my $pd_pages = $node->safe_psql('regress001', "SELECT pg_relation_size('pg_depend')/8192");
print("pd_pages: $pd_pages\n");

my $pd_page_13 = $node->safe_psql('regress001', "\\pset format aligned
\\pset tuples_only off
SELECT * FROM heap_page_item_attrs(get_raw_page('pg_depend', 13), 'pg_depend'::regclass), LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2);");
print("pg_depend page 13:\n$pd_page_13");

my $pdri_pages = $node->safe_psql('regress001', "SELECT pg_relation_size('pg_catalog.pg_depend_reference_index')/8192");
print("pdri_pages: $pdri_pages\n");
for (my $i = 1; $i < $pdri_pages; $i++) {
  my $pdri_page = $node->safe_psql('regress001', "\\pset format aligned
\\pset tuples_only off
SELECT * FROM bt_page_items('pg_depend_reference_index', $i)");
  print("pg_depend_reference_index page $i:\n$pdri_page");
}

print($node->safe_psql('regress001', "
SELECT ctid, * FROM pg_depend WHERE refclassid = 0x0a37 AND refobjid = 0x4036 AND refobjsubid = 0"));

$node->command_checks_all(['pg_amcheck', '-d', 'regress001', '--heapallindexed', '--rootdescend' ],
    0, [qr/^$/], [qr/^$/], 'pg_amcheck after manipulations');

ok(1);
done_testing();

--------------4KD3a00f8BF7gUQgELKa90SG--