Bruce Momjian wrote:
Tom Lane wrote:
Heikki Linnakangas [EMAIL PROTECTED] writes:
We wouldn't clean up tuples that are visible to a transaction, but if
you have one long-running transaction like pg_dump in a database with
otherwise short transaction, you'll have a lot of tuples that
Bruce Momjian [EMAIL PROTECTED] writes:
In researching, I found we already do this by updating PGPROC.xid for
every command in non-serialzable transactions:
Dunno how you arrived at that conclusion, but it's quite wrong.
regards, tom lane
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
In researching, I found we already do this by updating PGPROC.xid for
every command in non-serialzable transactions:
Dunno how you arrived at that conclusion, but it's quite wrong.
Looking in the function I now see you are right:
Bruce Momjian [EMAIL PROTECTED] writes:
So, can't this be improved to allow more aggressive vacuuming?
Not at that level. We do not keep track of the oldest still-used
snapshot in a transaction. I'm dubious that it'd be worth the
bookkeeping trouble to try --- often as not, the problem with a
Tom Lane wrote:
Heikki Linnakangas [EMAIL PROTECTED] writes:
We wouldn't clean up tuples that are visible to a transaction, but if
you have one long-running transaction like pg_dump in a database with
otherwise short transaction, you'll have a lot of tuples that are not
vacuumable
Heikki Linnakangas wrote:
One potential issue I'm seeing is that if we rely on the unbroken chain
starting from OldestXmin, and that tuple isn't there because of a bug,
for example, the later version of the tuple is skipped and the row is lost.
After further thought, I don't feel comfortable
Heikki Linnakangas [EMAIL PROTECTED] writes:
I'm thinking of keeping an in-memory mapping of old and new tids of
updated tuples while clustering, instead. That means that cluster
requires a little bit of memory for each RECENTLY_DEAD updated tuple. In
the worst case that means that you run
Tom Lane wrote:
Heikki Linnakangas [EMAIL PROTECTED] writes:
I'm thinking of keeping an in-memory mapping of old and new tids of
updated tuples while clustering, instead. That means that cluster
requires a little bit of memory for each RECENTLY_DEAD updated tuple. In
the worst case that means
Tom Lane wrote:
Heikki Linnakangas [EMAIL PROTECTED] writes:
In each chain, there must be at least one non-dead tuple with xmin
Oldestxmin.
Huh? Typically *all* the tuples but the last are dead, for varying
values of dead. Please be more specific what you mean.
I meant dead as in
Heikki Linnakangas wrote:
Tom Lane wrote:
The reason it's not trivial is that you also have to preserve the t_ctid
links of update chains. If you look into VACUUM FULL, a very large part
of its complexity is that it moves update chains as a unit to make that
possible. (BTW, I believe the
Heikki Linnakangas [EMAIL PROTECTED] writes:
In each chain, there must be at least one non-dead tuple with xmin
Oldestxmin.
Huh? Typically *all* the tuples but the last are dead, for varying
values of dead. Please be more specific what you mean.
regards, tom lane
Is there a particular reason why CLUSTER isn't MVCC-safe? It seems to me
that it would be trivial to fix, by using SnapshotAny instead of
SnapshotNow, and not overwriting the xmin/xmax with the xid of the
cluster command.
I feel that I must missing something, or someone would've already fixed
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
Ühel kenal päeval, R, 2007-03-09 kell 11:29, kirjutas Heikki
Linnakangas:
Is there a particular reason why CLUSTER isn't MVCC-safe? It seems to me
that it would be trivial to fix, by using SnapshotAny instead of
SnapshotNow, and not overwriting the xmin/xmax with the xid of the
cluster
Csaba Nagy [EMAIL PROTECTED] writes:
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 ?
Doing it in CLUSTER would be weird. However perhaps it would be useful to have
some sort of
On Fri, 2007-03-09 at 13:42, Gregory Stark wrote:
Csaba Nagy [EMAIL PROTECTED] writes:
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 ?
Doing it in CLUSTER would be weird.
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?
Gregory Stark wrote:
Csaba Nagy [EMAIL PROTECTED] writes:
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 ?
Doing it in CLUSTER would be weird. However perhaps it would be useful
Is there a particular reason why CLUSTER isn't MVCC-safe? It seems
to
me that it would be trivial to fix, by using SnapshotAny instead of
SnapshotNow, and not overwriting the xmin/xmax with the xid of the
cluster command.
It's trivial to fix now in this way, but it would break HOT,
On Fri, 2007-03-09 at 14:00, Alvaro Herrera wrote:
But I'm not really seeing the problem here. Why isn't Csaba's problem
fixed by the fact that HOT reduces the number of dead tuples in the
first place? If it does, then he no longer needs the CLUSTER
workaround, or at least, he needs it to a
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
Csaba Nagy wrote:
On Fri, 2007-03-09 at 14:00, Alvaro Herrera wrote:
But I'm not really seeing the problem here. Why isn't Csaba's problem
fixed by the fact that HOT reduces the number of dead tuples in the
first place? If it does, then he no longer needs the CLUSTER
workaround, or at least,
Heikki Linnakangas wrote:
Csaba Nagy wrote:
On Fri, 2007-03-09 at 14:00, Alvaro Herrera wrote:
But I'm not really seeing the problem here. Why isn't Csaba's problem
fixed by the fact that HOT reduces the number of dead tuples in the
first place? If it does, then he no longer needs the
Csaba Nagy wrote:
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,
Zeugswetter Andreas ADI SD wrote:
Is there a particular reason why CLUSTER isn't MVCC-safe? It seems
to
me that it would be trivial to fix, by using SnapshotAny instead of
SnapshotNow, and not overwriting the xmin/xmax with the xid of the
cluster command.
It's trivial to fix
Florian G. Pflug wrote:
Couldn't HOT in principle deal with this? Let's say you have two
long-running
transactions, which see row versions A and D. While those transactions
are running, the row is constantly updated, leading to row versions B, C
(before
the second long-running transaction
On 3/9/07, Florian G. Pflug [EMAIL PROTECTED] wrote:
Heikki Linnakangas wrote:
Csaba Nagy wrote:
On Fri, 2007-03-09 at 14:00, Alvaro Herrera wrote:
But I'm not really seeing the problem here. Why isn't Csaba's problem
fixed by the fact that HOT reduces the number of dead tuples in the
Mike Rylander wrote:
On 3/9/07, Florian G. Pflug [EMAIL PROTECTED] wrote:
Couldn't HOT in principle deal with this? Let's say you have two
long-running
transactions, which see row versions A and D. While those transactions
are running, the row is constantly updated, leading to row versions B,
Heikki Linnakangas [EMAIL PROTECTED] writes:
Is there a particular reason why CLUSTER isn't MVCC-safe? It seems to me
that it would be trivial to fix, by using SnapshotAny instead of
SnapshotNow, and not overwriting the xmin/xmax with the xid of the
cluster command.
The reason it's not
On Fri, 2007-03-09 at 12:48 -0300, Alvaro Herrera wrote:
Zeugswetter Andreas ADI SD wrote:
Is there a particular reason why CLUSTER isn't MVCC-safe? It seems
to
me that it would be trivial to fix, by using SnapshotAny instead of
SnapshotNow, and not overwriting the xmin/xmax
Tom Lane wrote:
Heikki Linnakangas [EMAIL PROTECTED] writes:
Is there a particular reason why CLUSTER isn't MVCC-safe? It seems to me
that it would be trivial to fix, by using SnapshotAny instead of
SnapshotNow, and not overwriting the xmin/xmax with the xid of the
cluster command.
The
Heikki Linnakangas [EMAIL PROTECTED] writes:
We wouldn't clean up tuples that are visible to a transaction, but if
you have one long-running transaction like pg_dump in a database with
otherwise short transaction, you'll have a lot of tuples that are not
vacuumable because of the
Tom Lane wrote:
Heikki Linnakangas [EMAIL PROTECTED] writes:
We wouldn't clean up tuples that are visible to a transaction, but if
you have one long-running transaction like pg_dump in a database with
otherwise short transaction, you'll have a lot of tuples that are not
vacuumable because of
Heikki Linnakangas wrote:
The implementation problem is that we don't have a global view of all
snapshots in the system. If we solve that, we can be more aggressive
with vacuuming in presence of long-running transactions. It's not an
easy problem, we don't want to add a lot of accounting
Tom Lane wrote:
Heikki Linnakangas [EMAIL PROTECTED] writes:
We wouldn't clean up tuples that are visible to a transaction, but if
you have one long-running transaction like pg_dump in a database with
otherwise short transaction, you'll have a lot of tuples that are not
vacuumable because of
35 matches
Mail list logo