Re: 答复: autovacuum can not remove dead tuples

From: Sergei Kornilov <sk(at)zsrv(dot)org>
To: 石勇虎 <shiyonghu651(at)pingan(dot)com(dot)cn>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: 答复: autovacuum can not remove dead tuples
Date: 2018-04-04 06:45:21
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

Yes, any session in transaction will prevent vacuum some rows. Regardless DISABLE_PAGE_SKIPPING or any other option, this is not bug. Vacuum should not remove any deleted row which can be accessible by any running transaction.
You can use idle_in_transaction_session_timeout to prevent long idle in transaction state. But i use simple crontab like this:
*/10 * * * * psql -d postgres -tx -c "SELECT pg_terminate_backend(pid),now(),now()-xact_start as duration,* from pg_stat_activity where (now() - pg_stat_activity.xact_start) > '1 hour'::interval and usename NOT IN ('postgres') and state<>'idle'"
to terminate any long running transaction. Time interval changed by database workload, for web requests can be 5min.

regards, Sergei

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Paquier 2018-04-04 07:26:25 Re: BUG #14999: pg_rewind corrupts control file global/pg_control
Previous Message 石勇虎 2018-04-04 00:56:22 答复: autovacuum can not remove dead tuples