答复: autovacuum can not remove dead tuples

From: 石勇虎 <SHIYONGHU651(at)pingan(dot)com(dot)cn>
To: pgsql-bugs(at)postgresql(dot)org
Subject: 答复: autovacuum can not remove dead tuples
Date: 2018-04-04 00:56:22
Message-ID: 020adf37a8d54e79b0b797590252a24b@pingan.com.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I think we have foud the cause,after we killed the session that state is idle in transation ,dead tuples can be removed.I know that in PG9.6 we can use DISABLE_PAGE_SKIPPING,but 9.5 can`t .
But we still don’t know how this idle in transaction session comes.maybe it is produced by DBeaver,we need to have a test.

DBID |dbname| username| program | ip address | begi_time |state |Query
17482 | ela | ela | | 2018-04-03 01:00:00 | active | select collect_customer_job();
17482 | ela | ela | DBeaver 3.7.7 - Main | xxx.xxx.xx.xxx | 2018-04-03 08:51:48 | idle in transaction | SELECT current_database(), current_schema(),session_user
17482 | ela | dbm DBeaver 3.7.7 - Main |xxx.xxx.xx.xxx | 2018-04-03 16:20:02.| idle in transaction | SELECT current_database(), current_schema(),session_user

-----邮件原件-----
发件人: Sergei Kornilov [mailto:sk(at)zsrv(dot)org]
发送时间: 2018年4月3日 22:19
收件人: 石勇虎 <SHIYONGHU651(at)pingan(dot)com(dot)cn>; pgsql-bugs(at)postgresql(dot)org
主题: Re: autovacuum can not remove dead tuples

Hello
And an additional question:

> [postgres:5543(at)ela] [04-03.17:27:09]=# select
> pid,state,substr(query,0,100) from pg_stat_activity where query ~
> 'ela_elifeassist_product' ;
Have you another long transactions related not to this table?
Any long transaction will prevent vacuum. Vacuum can only delete rows older than the oldest transaction regardless relations. For example, transaction begin; select * from tablename1;
-- sleep 10 min
select * from another_tablename;
commit;
We must leave some dead rows in another_tablename for this 10 minutes because we do not know which rows are required in an open transaction later.

regards Sergei.

********************************************************************************************************************************
The information in this email is confidential and may be legally privileged. If you have received this email in error or are not the intended recipient, please immediately notify the sender and delete this message from your computer. Any use, distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages sent to and from us may be monitored to ensure compliance with internal policies and to protect our business.
Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks.

收发邮件者请注意:
本邮件含涉密信息,请保守秘密,若误收本邮件,请务必通知发送人并直接删去,不得使用、传播或复制本邮件。
进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
********************************************************************************************************************************

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sergei Kornilov 2018-04-04 06:45:21 Re: 答复: autovacuum can not remove dead tuples
Previous Message 石勇虎 2018-04-04 00:39:00 can not kill client session