Re: Assertion failure when autovacuum drops orphan temp indexes.

On Sat, Oct 26, 2024 at 12:13 PM Stepan Neretin <sndcppg(at)gmail(dot)com> wrote:
>
> > IMHO the best way to handle this is to just unconditionally push a snapshot
> > in this code path instead of making assumptions about what callers will do.

+1

>
> Yes, I agree! I have found the same solution. I attempted to write Perl tests to verify the patch fix, but the autovacuum process is not triggered in my tests. Could you please assist me?
>
> ```
> use strict;
> use warnings;
> use threads;
> use PostgreSQL::Test::Cluster;
> use PostgreSQL::Test::RecursiveCopy;
> use PostgreSQL::Test::Utils;
> use Test::More;
> use Data::Dumper;
>
> my $node = PostgreSQL::Test::Cluster->new('main');
>
> # Create a data directory with initdb
> $node->init;
>
> $node->append_conf(
> 'postgresql.conf', qq[
> autovacuum = on
> track_counts=on
> autovacuum_naptime = 1s
> autovacuum_max_workers = 1
> autovacuum_vacuum_threshold = 1
> ]);
>
> # Start the PostgreSQL server
> $node->start;
>
> my $psql1 = $node->interactive_psql('postgres');
> $psql1->query("create temp table test (a int primary key);");
>
> $node->stop('immediate');
> $node->start();

IIUC after an immediate shutdown all pgstat entries are wiped out so
the server doesn't have any pgstat entries for databases at this
point. And since we don't run autovacuum on databases that have no
pg_stat entries, no autovacuum worker worked on the 'postgres'
database. Please try executing any query (e.g. 'select 1') on the
'postgres' database after the restart, which creates a pgstat entry
for the database.

> sleep(5);

While the test script sleeps for 5 seconds, the server restarts after
a crash. So even if the assertion failure happens, the test would
appear to be successful. I think you can set 'restart_after_crash =
off' and execute another query using safe_psql() after the sleep. That
way, the test ends up with safe_psql() failure because the database
server is not running.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com