Re: DROP TABLE and autovacuum

ITAGAKI Takahiro wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
> > ITAGAKI Takahiro wrote:
> > > autovacuum killer triggered in CREATE/DROP/RENAME DATABASE commands.
> > > Can we extend the feature to several TABLE commands?
> >
> > Well, one problem with this is that currently SIGINT cancels the whole
> > autovacuum worker, not just the table currently being processed. I
> > think this can be fixed easily by improving the signal handling.
>
> There is no difference between SIGINT and SIGTERM against autovacuum
> workers presently. I'm thinking to split their effects -- SIGINT to
> 'skip the current table' and SIGTERM to 'cancel all tables'.

Sure, we can do that (it's even mentioned in the comments in autovacuum.c).

> BTW, if autovacuum workers are signaled by an internal server activity,
> we will see 'ERROR: canceling statement due to user request' in server log.
> Is it surprising to users? I prefer quiet shutdown to ERROR logs.

Maybe cancelling the current table processing should be just a WARNING,
not ERROR. We would abort the transaction quietly.

> > Aside from that, I don't see any problem in handling DROP TABLE like you
> > suggest. But I don't feel comfortable with doing it with just any
> > strong locker, because that would easily starve tables from being
> > vacuumed at all.
>
> Hmm, how about canceling only the cases of DROP TABLE, TRUNCATE and CLUSTER.
> We will obviously not need the table after the commands. Other commands,
> VACUUM (FULL), ANALYZE, CREATE INDEX (CONCURRENTLY), REINDEX and LOCK TABLE
> still conflict with autovacuum, but I'll leave it as-is in the meantime.

Well, all of DROP TABLE, TRUNCATE and CLUSTER seem safe -- and also,
they will advance the table's relfrozenxid. No objection there.

I think all the others you mention should be waiting on autovacuum, not
cancel it. Maybe what we could do with VACUUM and ANALYZE is let the
user know that the table is being processed by autovacuum and return
quickly.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support