Re: [HACKERS] DROP TABLE and autovacuum
Alvaro Herrera [EMAIL PROTECTED] wrote:
Something worth considering, though unrelated to the topic at hand: what
happens with the table stats after CLUSTER? Should we cause an ANALYZE
afterwards? We could end up running with outdated statistics.
We don't invalidate the value statistics in pg_stats by ANALYZE presently.
Also, the runtime statistics are not invalidated -- it cound be a bug.
pgstat_drop_relation() is expecting relid (pg_class.oid) as the argument,
but we pass it relfilenode.
[storage/smgr/smgr.c]
static void
smgr_internal_unlink(RelFileNode rnode, int which, bool isTemp, bool isRedo)
{
...
/*
* Tell the stats collector to forget it immediately, too. Skip this in
* recovery mode, since the stats collector likely isn't running (and if
* it is, pgstat.c will get confused because we aren't a real backend
* process).
*/
if (!InRecovery)
pgstat_drop_relation(rnode.relNode);
...
}
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] DROP TABLE and autovacuum
Alvaro Herrera [EMAIL PROTECTED] 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'. 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. 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. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DROP TABLE and autovacuum
ITAGAKI Takahiro wrote: Alvaro Herrera [EMAIL PROTECTED] 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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] DROP TABLE and autovacuum
Alvaro Herrera wrote: ITAGAKI Takahiro wrote: 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. Something worth considering, though unrelated to the topic at hand: what happens with the table stats after CLUSTER? Should we cause an ANALYZE afterwards? We could end up running with outdated statistics. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] DROP TABLE and autovacuum
If we tries to drop the table on which autovacuum is running, we have to wait finish of the vacuum. However, the vacuuming effort goes to waste for the table being dropped or rewritten. Meanwhile, we've already had the autovacuum killer triggered in CREATE/DROP/RENAME DATABASE commands. Can we extend the feature to several TABLE commands? One simple solution is that every time a non-autovacuum backend tries to access a table with a lock equal or stronger than SHARE UPDATE EXCLUSIVE, the backend checks whether some autovacuum workers are vacuuming the table and send SIGINT to them. Is this worth doing? Or are there any dangerous situation in it? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] DROP TABLE and autovacuum
ITAGAKI Takahiro wrote: If we tries to drop the table on which autovacuum is running, we have to wait finish of the vacuum. However, the vacuuming effort goes to waste for the table being dropped or rewritten. Meanwhile, we've already had the autovacuum killer triggered in CREATE/DROP/RENAME DATABASE commands. Can we extend the feature to several TABLE commands? One simple solution is that every time a non-autovacuum backend tries to access a table with a lock equal or stronger than SHARE UPDATE EXCLUSIVE, the backend checks whether some autovacuum workers are vacuuming the table and send SIGINT to them. Is this worth doing? Or are there any dangerous situation in it? 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. 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. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] DROP TABLE and autovacuum
ITAGAKI Takahiro [EMAIL PROTECTED] writes: If we tries to drop the table on which autovacuum is running, we have to wait finish of the vacuum. However, the vacuuming effort goes to waste for the table being dropped or rewritten. Meanwhile, we've already had the autovacuum killer triggered in CREATE/DROP/RENAME DATABASE commands. Can we extend the feature to several TABLE commands? One simple solution is that every time a non-autovacuum backend tries to access a table with a lock equal or stronger than SHARE UPDATE EXCLUSIVE, the backend checks whether some autovacuum workers are vacuuming the table and send SIGINT to them. I don't think this is a good idea at all. You're proposing putting a dangerous sledgehammer into a core part of the system in order to fix a fairly minor annoyance. For the specific case of DROP TABLE, a SIGINT might be a good idea but I don't agree with it for any weaker action. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
