Csaba Nagy wrote:
On Fri, 2007-03-09 at 12:29, Heikki Linnakangas wrote:
Csaba, you mentioned recently (http://archives.postgresql.org/pgsql-hackers/2007-03/msg00027.php) that you're actually using the MVCC-violation to clean up tables during a backup. Can you tell us a bit more about that? Would you be upset if we shut that backdoor?


My use case: a queue-like table (in fact a 'task' table) which is very
frequently inserted/updated/deleted. This table tends to be bloated in
the presence of any long running transaction... the only transactional
behavior we need from this table is to make sure that when we insert
something in this table in a transaction (possibly together with other
actions) and then commit/rollback, it commits/rolls back the insert.
CLUSTER's violation of MVCC does not affect this, as CLUSTER will not be
able to lock the table if another transaction inserted something in it
(the inserting transaction will have a lock on the table). Selections on
this table are not critical for us, it just doesn't matter which job
processor is getting which task and in what order... (actually it does
matter, but CLUSTER won't affect that either).

Hmm. You could use something along these lines instead:

0. LOCK TABLE queue_table
1. SELECT * INTO queue_table_new FROM queue_table
2. DROP TABLE queue_table
3. ALTER TABLE queue_table_new RENAME queue_table

After all, it's not that you care about the clustering of the table, you just want to remove old tuples.

As a long term solution, it would be nice if we had more fine-grained bookkeeping of snapshots that are in use in the system. In your case, there's a lot of tuples that are not visible to pg_dump because xmin is too new, and also not visible to any other transaction because xmax is too old. If we had a way to recognize situations like that, and vacuum those tuples, much of the problem with long-running transactions would go away.

Wouldn't be possible to do it like Simon (IIRC) suggested, and add a
parameter to enable/disable the current behavior, and use the MVCC
behavior as default ?

I guess we could, but I don't see why should encourage using CLUSTER for that. A more aggressive, MVCC-breaking version of VACUUM would make more sense to me, but I don't like the idea of adding "break-MVCC" flags to any commands.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

---------------------------(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

Reply via email to