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
