From: Alena Rybakina <lena.ribackina@yandex.ru>
To: Peter Geoghegan <pg@bowt.ie>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Noah Misch <noah@leadboat.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Alexander Lakhin <exclusion@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()
Date: Thu, 2 May 2024 21:01:14 +0300
Message-ID: <d1ca3a1d-7ead-41a7-bfd0-5b66ad97b1cd@yandex.ru> (raw)
In-Reply-To: <CAH2-WzmYAMYBRp==fSCskLmgPDmx13vPe07E4gOzLLSbDFO8-g@mail.gmail.com>
References: <17257-1e46de26bec11433@postgresql.org>
<d5d5af5d-ba46-aff3-9f91-776c70246cc3@gmail.com>
<CAH2-WzknORjSVufsDQKr7VVnXspRhdsjKBh8brwHEkrUpqVXjg@mail.gmail.com>
<20231225024402.77@rfd.leadboat.com>
<CAH2-Wz=X88igoxQvAGW2cFrTsEXK+VJJHap=90-z=juD5DqYdw@mail.gmail.com>
<20240106202413.e5@rfd.leadboat.com>
<20240415173913.4zyyrwaftujxthf2@awork3.anarazel.de>
<CAAKRu_ai8PMW5cqCFhu-U46CWLmgP2d_FnpLOqCSvMxY-UQ9xw@mail.gmail.com>
<CAH2-Wz=_FWEs2YSa=ggKuXhZtdCPiORKhk2JWu1bcVNb+hJdxw@mail.gmail.com>
<CAAKRu_Yj0rLuirkktkH62cAmPoTn+RH-WF6Y63nxwV6CHHmCuA@mail.gmail.com>
<CAH2-Wzn2tggg9X6n08jiyW2VU=+wuDjh-ESo6QWnckNMDq8gDg@mail.gmail.com>
<CAAKRu_adrsViY6pNQcGLF555dbp9C6f5OO7nwZavTfECSM5kow@mail.gmail.com>
<CAH2-WzmYAMYBRp==fSCskLmgPDmx13vPe07E4gOzLLSbDFO8-g@mail.gmail.com>
On 02.05.2024 19:52, Peter Geoghegan wrote:
> On Sat, Apr 27, 2024 at 10:38 AM Melanie Plageman
> <melanieplageman@gmail.com> wrote:
>> In 17, we don't ever get a new HTSV_Result, so if the tuple is not
>> removed, it would be because HeapTupleSatisfiesVacuumHorizon()
>> returned HEAPTUPLE_RECENTLY_DEAD and, if GlobalVisTestIsRemovableXid()
>> was called, dead_after did not precede GlobalVisState->maybe_needed.
>> This tuple, during this vacuum of the relation, would never be
>> determined to be HEAPTUPLE_DEAD or it would have been removed.
> That makes sense.
>
>>>> It will always be HEAPTUPLE_RECENTLY_DEAD in 17 and in <= 16, if
>>>> HeapTupleSatisfiesVacuum() returns HEAPTUPLE_DEAD, we wouldn't call
>>>> heap_prepare_freeze_tuple() because of the retry loop.
>>> The retry loop exists precisely because heap_prepare_freeze_tuple()
>>> isn't prepared to deal with HEAPTUPLE_DEAD tuples. So I agree that
>>> that won't be allowed to happen on versions that have the retry loop
>>> (14 - 16).
>> So, it can't happen in back branches. Let's just address 17. Help me
>> understand how this can happen in 17.
> Just to be clear, I never said that it was possible in 17. If I
> somehow implied it, then I didn't mean to.
>
Hi! I also investigated this issue and reproduced it using this test
added to the isolated tests, where I added 2 tuples, deleted them and
ran vacuum and printed the tuple_deleted and dead_tuples statistics (I
attached test c to this email as a patch). Within400iterationsormore,
Igotthe results:
n_dead_tup|n_live_tup|n_tup_del
----------------+------------+------------- 0| 0| 0 (1 row)
After 400 or more running cycles, I felt the differences, as shown earlier:
n_dead_tup|n_live_tup|n_tup_del
----------+----------+---------
- 0| 0| 0
+ 2| 0| 0
(1 row)
I debugged and found that the test produces results with 0 dead tuples
if GlobalVisTempRels.maybe_needed is less than the x_max of the tuple.
In the code, this condition works in heap_prune_satisfies_vacuum:
else if (GlobalVisTestIsRemovableXid(prstate->vistest, dead_after))
{
res = HEAPTUPLE_DEAD;
}
But when GlobalVisTempRels.maybe_needed is equal to the x_max xid of the
tuple, vacuum does not touch this tuple, because the
heap_prune_satisfies_vacuum function returns the status of the
RECENTLY_DEAD tuple.
Unfortunately, I have not found any explanation why
GlobalVisTempRels.maybe_needed does not change after 400 iterations or
more. I'm still studying it. Perhaps this information will help you.
I reproduced the problem on REL_16_STABLE.
--
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
Attachments:0001-vacuum_test.patch (text/x-patch, 5.6K) downloaddiff --git a/src/test/isolation/expected/vacuum_test.out b/src/test/isolation/expected/vacuum_test.outnew file mode 100644index 00000000000..38ef1e68b5d--- /dev/null+++ b/src/test/isolation/expected/vacuum_test.out@@ -0,0 +1,23 @@+unused step name: s1_activity_print+unused step name: s1_delete_half_table+unused step name: s1_set_agressive_vacuum+unused step name: s1_update+unused step name: s1_vacuum+unused step name: s1_vacuum_full+Parsed test spec with 1 sessions++starting permutation: s1_insert s1_delete_full_table s1_vacuum_parallel s1_print_vacuum_stats_tables+step s1_insert: INSERT INTO vestat(x) SELECT id FROM generate_series(1,2) As id;+step s1_delete_full_table: DELETE FROM vestat;+step s1_vacuum_parallel: VACUUM (PARALLEL 2, INDEX_CLEANUP ON) vestat;+step s1_print_vacuum_stats_tables: + SELECT n_dead_tup, n_live_tup, n_tup_del+ FROM pg_stat_all_tables vt, pg_class c+ WHERE vt.relname = 'vestat' AND+ vt.relid = c.oid;++n_dead_tup|n_live_tup|n_tup_del+----------+----------+---------+ 0| 0| 0+(1 row)+diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_scheduleindex 0342eb39e40..edb751c2ff4 100644--- a/src/test/isolation/isolation_schedule+++ b/src/test/isolation/isolation_schedule@@ -1,116 +1 @@-test: read-only-anomaly-test: read-only-anomaly-2-test: read-only-anomaly-3-test: read-write-unique-test: read-write-unique-2-test: read-write-unique-3-test: read-write-unique-4-test: simple-write-skew-test: receipt-report-test: temporal-range-integrity-test: project-manager-test: classroom-scheduling-test: total-cash-test: referential-integrity-test: ri-trigger-test: partial-index-test: two-ids-test: multiple-row-versions-test: index-only-scan-test: predicate-lock-hot-tuple-test: update-conflict-out-test: deadlock-simple-test: deadlock-hard-test: deadlock-soft-test: deadlock-soft-2-test: deadlock-parallel-test: detach-partition-concurrently-1-test: detach-partition-concurrently-2-test: detach-partition-concurrently-3-test: detach-partition-concurrently-4-test: fk-contention-test: fk-deadlock-test: fk-deadlock2-test: fk-partitioned-1-test: fk-partitioned-2-test: fk-snapshot-test: subxid-overflow-test: eval-plan-qual-test: eval-plan-qual-trigger-test: lock-update-delete-test: lock-update-traversal-test: inherit-temp-test: temp-schema-cleanup-test: insert-conflict-do-nothing-test: insert-conflict-do-nothing-2-test: insert-conflict-do-update-test: insert-conflict-do-update-2-test: insert-conflict-do-update-3-test: insert-conflict-specconflict-test: merge-insert-update-test: merge-delete-test: merge-update-test: merge-match-recheck-test: merge-join-test: delete-abort-savept-test: delete-abort-savept-2-test: aborted-keyrevoke-test: multixact-no-deadlock-test: multixact-no-forget-test: lock-committed-update-test: lock-committed-keyupdate-test: update-locked-tuple-test: reindex-concurrently-test: reindex-concurrently-toast-test: reindex-schema-test: propagate-lock-delete-test: tuplelock-conflict-test: tuplelock-update-test: tuplelock-upgrade-no-deadlock-test: tuplelock-partition-test: freeze-the-dead-test: nowait-test: nowait-2-test: nowait-3-test: nowait-4-test: nowait-5-test: skip-locked-test: skip-locked-2-test: skip-locked-3-test: skip-locked-4-test: drop-index-concurrently-1-test: multiple-cic-test: alter-table-1-test: alter-table-2-test: alter-table-3-test: alter-table-4-test: create-trigger-test: sequence-ddl-test: async-notify-test: vacuum-no-cleanup-lock-test: timeouts-test: vacuum-concurrent-drop-test: vacuum-conflict-test: vacuum-skip-locked-test: stats-test: horizons-test: predicate-hash-test: predicate-gist-test: predicate-gin-test: partition-concurrent-attach-test: partition-drop-index-locking-test: partition-key-update-1-test: partition-key-update-2-test: partition-key-update-3-test: partition-key-update-4-test: partition-merge-test: partition-split-test: plpgsql-toast-test: cluster-conflict-test: cluster-conflict-partition-test: truncate-conflict-test: serializable-parallel-test: serializable-parallel-2-test: serializable-parallel-3-test: matview-write-skew-test: lock-nowait+test: vacuum_test
\ No newline at end of file
diff --git a/src/test/isolation/specs/vacuum_test.spec b/src/test/isolation/specs/vacuum_test.specnew file mode 100644index 00000000000..1fb4d67cc34--- /dev/null+++ b/src/test/isolation/specs/vacuum_test.spec@@ -0,0 +1,34 @@+setup+{+ CREATE TABLE vestat (x int primary key) WITH (autovacuum_enabled = off);++}++teardown+{+ DROP TABLE vestat CASCADE;+}++session s1+step s1_set_agressive_vacuum { SET vacuum_freeze_min_age = 0; }+step s1_insert { INSERT INTO vestat(x) SELECT id FROM generate_series(1,2) As id; }+step s1_update { UPDATE vestat SET x = x+1; }+step s1_delete_half_table { DELETE FROM vestat WHERE x % 2 = 0; }+step s1_delete_full_table { DELETE FROM vestat; }+step s1_vacuum { VACUUM vestat; }+step s1_vacuum_full { VACUUM FULL vestat; }+step s1_vacuum_parallel { VACUUM (PARALLEL 2, INDEX_CLEANUP ON) vestat; }+step s1_activity_print { select application_name, backend_type from pg_stat_activity order by (application_name, backend_type); }+step s1_print_vacuum_stats_tables+{+ SELECT n_dead_tup, n_live_tup, n_tup_del+ FROM pg_stat_all_tables vt, pg_class c+ WHERE vt.relname = 'vestat' AND+ vt.relid = c.oid;+}++permutation+ s1_insert+ s1_delete_full_table+ s1_vacuum_parallel+ s1_print_vacuum_stats_tables
\ No newline at end of file
--
2.34.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: lena.ribackina@yandex.ru, pg@bowt.ie, melanieplageman@gmail.com, andres@anarazel.de, noah@leadboat.com, robertmhaas@gmail.com, boekewurm+postgres@gmail.com, exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()
In-Reply-To: <d1ca3a1d-7ead-41a7-bfd0-5b66ad97b1cd@yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox