Bug: Unreferenced temp tables disables vacuum to update xid

Joshua D. Drake wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

I spent the better part of this evening tracking down a problem with a
high velocity database. The database had entered the point of no return
by invoking xidStopLimit.

8.2.4

Joshua D. Drake

Joshua D. Drake wrote:

Ping?

Joshua D. Drake wrote:

SELECT oid::regclass FROM pg_class WHERE
reltoastrelid='pg_toast.pg_toast_49013869'::regclass;

oid | pg_temp_24.tmp_isp_blk_chk

The hack to get this cleaned up was to connect about 2 dozen times (to
get to slot 24) with psql via different sessions and create temp
tables. Once we hit slot 24, the probably instantly went away and the
database returned to normal state.

Ah -- interesting. This is a known issue, but we haven't found a
solution yet.

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, 7 Jan 2008 09:18:24 -0300
Alvaro Herrera <alvherre@commandprompt.com> wrote:

Joshua D. Drake wrote:

SELECT oid::regclass FROM pg_class WHERE
reltoastrelid='pg_toast.pg_toast_49013869'::regclass;

oid | pg_temp_24.tmp_isp_blk_chk

The hack to get this cleaned up was to connect about 2 dozen times
(to get to slot 24) with psql via different sessions and create
temp tables. Once we hit slot 24, the probably instantly went away
and the database returned to normal state.

Ah -- interesting. This is a known issue, but we haven't found a
solution yet.

Is there bug number?

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHglltATb/zqfZUUQRAk19AJ9GywJ7ohqGZa4jrRYtufgbwCacowCgrgml
00egslWmlrI0MK2sJjyc63I=
=Y4Ok
-----END PGP SIGNATURE-----

Joshua D. Drake wrote:

SELECT oid::regclass FROM pg_class WHERE
reltoastrelid='pg_toast.pg_toast_49013869'::regclass;

oid | pg_temp_24.tmp_isp_blk_chk

The hack to get this cleaned up was to connect about 2 dozen times
(to get to slot 24) with psql via different sessions and create
temp tables. Once we hit slot 24, the probably instantly went away
and the database returned to normal state.

Ah -- interesting. This is a known issue, but we haven't found a
solution yet.

Is there bug number?

I assume it is this TODO item:

o Prevent long-lived temporary tables from causing frozen-xid
advancement starvation

The problem is that autovacuum cannot vacuum them to set frozen xids;
only the session that created them can do that.
http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php

but am confused how the fix worked. Have all of these backends been
active for 1 billion transactions?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, 7 Jan 2008 11:58:29 -0500 (EST)
Bruce Momjian <bruce@momjian.us> wrote:

Ah -- interesting. This is a known issue, but we haven't found a
solution yet.

Is there bug number?

I assume it is this TODO item:

o Prevent long-lived temporary tables from causing frozen-xid
advancement starvation

The problem is that autovacuum cannot vacuum them to set
frozen xids; only the session that created them can do that.
http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php

but am confused how the fix worked. Have all of these backends been
active for 1 billion transactions?

Well it certainly appears that the TODO item is related. However there
are a couple of differences.

1. I had to manually vacuum because we had already hid xidStoplimit.

2. Postgres has been restarted multiple times and it made zero
difference.

E.g; PostgreSQL isn't cleaning up after itself and it isn't apparent
when it happens.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHgl0CATb/zqfZUUQRAtcnAKChLV9E4p7klYXRnVoEWKGsM+xv2QCgjmKB
JrBjOrL9i/4RcwXKMNk+z5I=
=6Gdf
-----END PGP SIGNATURE-----

"Joshua D. Drake" <jd@commandprompt.com> writes:

On Mon, 7 Jan 2008 11:58:29 -0500 (EST)
Bruce Momjian <bruce@momjian.us> wrote:

I assume it is this TODO item:

o Prevent long-lived temporary tables from causing frozen-xid
advancement starvation

The problem is that autovacuum cannot vacuum them to set
frozen xids; only the session that created them can do that.
http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php

but am confused how the fix worked. Have all of these backends been
active for 1 billion transactions?

Well it certainly appears that the TODO item is related. However there
are a couple of differences.

Yeah, it seems there are two bugs here. 1) temporary tables prevent frozen-xid
advancement and 2) if a process dies at the wrong moment it's possible to
temporary tables. Either one alone is pretty minor but I guess the combination
is lethal.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

On Monday 07 January 2008 09:10:24 Joshua D. Drake wrote:

On Mon, 7 Jan 2008 11:58:29 -0500 (EST)

Bruce Momjian <bruce@momjian.us> wrote:

Ah -- interesting. This is a known issue, but we haven't found a
solution yet.

Is there bug number?

I assume it is this TODO item:

o Prevent long-lived temporary tables from causing frozen-xid
advancement starvation

The problem is that autovacuum cannot vacuum them to set
frozen xids; only the session that created them can do that.

http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php

but am confused how the fix worked. Have all of these backends been
active for 1 billion transactions?

Well it certainly appears that the TODO item is related. However there
are a couple of differences.

1. I had to manually vacuum because we had already hid xidStoplimit.

2. Postgres has been restarted multiple times and it made zero
difference.

E.g; PostgreSQL isn't cleaning up after itself and it isn't apparent
when it happens.

After a fresh start of postgres, there should be no temp tables, so would a
work around to this at least be at postmaster start to (for a lack of a
better pseudo code ) DROP SCHEMA pg_temp* CASCADE; before coming up in
interactive mode? Doing this would at least have allowedthe manual vacuum to
do what it needed and not have caused confusion on the part of the user?
Also it would have greatly reduced the total time to resolution, and not
requiring hacking the backend to get there.

Sincerely,

Joshua D. Drake

--
Darcy Buskermolen
Command Prompt, Inc.
+1.503.667.4564 X 102
http://www.commandprompt.com/
PostgreSQL solutions since 1997

"Gregory Stark" <stark@enterprisedb.com> writes:

"Joshua D. Drake" <jd@commandprompt.com> writes:

On Mon, 7 Jan 2008 11:58:29 -0500 (EST)
Bruce Momjian <bruce@momjian.us> wrote:

I assume it is this TODO item:

o Prevent long-lived temporary tables from causing frozen-xid
advancement starvation

The problem is that autovacuum cannot vacuum them to set
frozen xids; only the session that created them can do that.
http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php

but am confused how the fix worked. Have all of these backends been
active for 1 billion transactions?

Well it certainly appears that the TODO item is related. However there
are a couple of differences.

Yeah, it seems there are two bugs here. 1) temporary tables prevent frozen-xid
advancement and 2) if a process dies at the wrong moment it's possible to
temporary tables. Either one alone is pretty minor but I guess the combination
is lethal.

oops, "2) if a process dies at the wrong moment it's possible to *leak*
temporary tables"

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, 7 Jan 2008 10:37:18 -0800
Darcy Buskermolen <darcyb@commandprompt.com> wrote:

1. I had to manually vacuum because we had already hid xidStoplimit.

2. Postgres has been restarted multiple times and it made zero
difference.

E.g; PostgreSQL isn't cleaning up after itself and it isn't apparent
when it happens.

After a fresh start of postgres, there should be no temp tables, so
would a work around to this at least be at postmaster start to (for a
lack of a better pseudo code ) DROP SCHEMA pg_temp* CASCADE; before
coming up in interactive mode? Doing this would at least have
allowedthe manual vacuum to do what it needed and not have caused
confusion on the part of the user? Also it would have greatly reduced
the total time to resolution, and not requiring hacking the backend
to get there.

+1

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHgnQaATb/zqfZUUQRAt1qAJ4hzeNG8fzA4l5y/luNrg3eGOz5QQCfcvtZ
xMuLPQSEbvG+AYfTRkEyLD0=
=+Lkk
-----END PGP SIGNATURE-----

Darcy Buskermolen <darcyb@commandprompt.com> writes:

After a fresh start of postgres, there should be no temp tables, so would a
work around to this at least be at postmaster start to (for a lack of a
better pseudo code ) DROP SCHEMA pg_temp* CASCADE; before coming up in
interactive mode?

The argument against this is the same as not wiping out
apparently-unreferenced regular tables: automatically destroying the
evidence after a crash is someday going to bite you. Admittedly,
this argument is a bit weaker for temp tables than it is for regular
tables, but that only goes to the question of whether the data is
valuable on its own terms, not whether it might be valuable for crash
analysis.

The real question that Josh's report brings up to me is why the heck was
there an orphaned temp table? Especially if it was only a toast table
and not the linked "regular" temp table? Something happened there that
should not have.

regards, tom lane

On 2008-01-07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The real question that Josh's report brings up to me is why the heck was
there an orphaned temp table? Especially if it was only a toast table
and not the linked "regular" temp table? Something happened there that
should not have.

The regular table was there too, but the regular table's relfrozenxid
was apparently recent, only the toast table's was old:

pg_toast_49013869 | 2146491285

[...]

SELECT oid::regclass FROM pg_class WHERE
reltoastrelid='pg_toast.pg_toast_49013869'::regclass;

oid | pg_temp_24.tmp_isp_blk_chk

The regular table had not shown up on a query of age(relfrozenxid) WHERE
relkind='r' but the toast table showed up on a similar query with WHERE
relkind='t'.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Andrew - Supernews <andrew+nonews@supernews.com> writes:

On 2008-01-07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The real question that Josh's report brings up to me is why the heck was
there an orphaned temp table? Especially if it was only a toast table
and not the linked "regular" temp table? Something happened there that
should not have.

The regular table was there too, but the regular table's relfrozenxid
was apparently recent, only the toast table's was old:

Hmm, that's even more odd, since AFAICS vacuum will always vacuum a
toast table immediately after vacuuming the parent. I wonder whether
we have a bug somewhere that allows a toast table's relfrozenxid to
get initially set to something substantially different from the
parent's.

(BTW, if the parent table *was* there then Josh hardly needed any fancy
jujitsu to clear the problem -- "drop table pg_temp_24.tmp_isp_blk_chk"
as a superuser should've worked. I wouldn't try this if the originating
backend were still around, but if it's not then there's not going to be
anything all that special about the temp table.)

regards, tom lane

Tom Lane wrote:

Andrew - Supernews <andrew+nonews@supernews.com> writes:

On 2008-01-07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The real question that Josh's report brings up to me is why the heck was
there an orphaned temp table? Especially if it was only a toast table
and not the linked "regular" temp table? Something happened there that
should not have.

The regular table was there too, but the regular table's relfrozenxid
was apparently recent, only the toast table's was old:

Hmm, that's even more odd, since AFAICS vacuum will always vacuum a
toast table immediately after vacuuming the parent. I wonder whether
we have a bug somewhere that allows a toast table's relfrozenxid to
get initially set to something substantially different from the
parent's.

Hmm ... that would be strange. Off-the-cuff idea: we introduced code to
advance relfrozenxid in CLUSTER, TRUNCATE and table-rewriting forms of
ALTER TABLE. Perhaps the problem is that we're neglecting to update it
for the toast table there. AFAIR I analyzed the cases and they were all
handled, but perhaps I forgot something.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

Tom Lane wrote:

Andrew - Supernews <andrew+nonews@supernews.com> writes:

On 2008-01-07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The real question that Josh's report brings up to me is why the heck was
there an orphaned temp table? Especially if it was only a toast table
and not the linked "regular" temp table? Something happened there that
should not have.

The regular table was there too, but the regular table's relfrozenxid
was apparently recent, only the toast table's was old:

Hmm, that's even more odd, since AFAICS vacuum will always vacuum a
toast table immediately after vacuuming the parent. I wonder whether
we have a bug somewhere that allows a toast table's relfrozenxid to
get initially set to something substantially different from the
parent's.

Hmm ... that would be strange. Off-the-cuff idea: we introduced code to
advance relfrozenxid in CLUSTER, TRUNCATE and table-rewriting forms of
ALTER TABLE. Perhaps the problem is that we're neglecting to update it
for the toast table there. AFAIR I analyzed the cases and they were all
handled, but perhaps I forgot something.

Just to throw another variable into the mix. This machine was a PITR
slave that was pushed into production about two weeks ago.

Joshua D. Drake

Joshua D. Drake wrote:

Alvaro Herrera wrote:

Hmm ... that would be strange. Off-the-cuff idea: we introduced code to
advance relfrozenxid in CLUSTER, TRUNCATE and table-rewriting forms of
ALTER TABLE. Perhaps the problem is that we're neglecting to update it
for the toast table there. AFAIR I analyzed the cases and they were all
handled, but perhaps I forgot something.

Just to throw another variable into the mix. This machine was a PITR slave
that was pushed into production about two weeks ago.

Ah, right, I bet we have a smoking gun here.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:

Hmm ... that would be strange. Off-the-cuff idea: we introduced code to
advance relfrozenxid in CLUSTER, TRUNCATE and table-rewriting forms of
ALTER TABLE. Perhaps the problem is that we're neglecting to update it
for the toast table there. AFAIR I analyzed the cases and they were all
handled, but perhaps I forgot something.

I found a smoking gun ...

regression=# create table foo (f1 serial primary key, f2 text);
NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for serial column "foo.f1"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
regression=# insert into foo values(1,repeat('xyzzy',100000));
INSERT 0 1
regression=# insert into foo values(2,repeat('xqzzy',100000));
INSERT 0 1
regression=# select relname, relkind, relfrozenxid from pg_class order by oid desc limit 6;
relname | relkind | relfrozenxid
-----------------------+---------+--------------
foo_pkey | i | 0
pg_toast_707220_index | i | 0
pg_toast_707220 | t | 119421
foo | r | 119421
foo_f1_seq | S | 0
xmlview5 | v | 0
(6 rows)

regression=# cluster foo_pkey on foo;
CLUSTER
regression=# select relname, relkind, relfrozenxid from pg_class order by oid desc limit 6;
relname | relkind | relfrozenxid
-----------------------+---------+--------------
pg_toast_707231_index | i | 0
pg_toast_707231 | t | 119424
foo_pkey | i | 0
foo | r | 4195086720
foo_f1_seq | S | 0
xmlview5 | v | 0
(6 rows)

So something is out of whack in CLUSTER. However it only seems to be
broken in HEAD, so I'm not sure this helps to explain the original
report. (Speculation: this is related to the rewrite to make CLUSTER
MVCC-safe?)

regards, tom lane

Tom Lane wrote:

regression=# cluster foo_pkey on foo;
CLUSTER
regression=# select relname, relkind, relfrozenxid from pg_class order by oid desc limit 6;
relname | relkind | relfrozenxid
-----------------------+---------+--------------
pg_toast_707231_index | i | 0
pg_toast_707231 | t | 119424
foo_pkey | i | 0
foo | r | 4195086720
foo_f1_seq | S | 0
xmlview5 | v | 0
(6 rows)

So something is out of whack in CLUSTER. However it only seems to be
broken in HEAD, so I'm not sure this helps to explain the original
report. (Speculation: this is related to the rewrite to make CLUSTER
MVCC-safe?)

Right ... see copy_heap_data --- it sets FreezeXid as relfrozenxid.

If we were to scan each tuple as it is inserted, we could store a higher
relfrozenxid, but I doubt we want to do that.

Perhaps what we could do is take the relfrozenxid from the old relation
and copy it over, if it's later than FreezeXid?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Perhaps what we could do is take the relfrozenxid from the old relation
and copy it over, if it's later than FreezeXid?

It certainly doesn't seem to make any sense to allow the rel's
relfrozenxid to go backwards. Indeed this coding lets it end up less
than the DB's datfrozenxid, which is certainly inappropriate.

What might be the best idea is to advance FreezeXid to the old
relfrozenxid between the vacuum_set_xid_limits and begin_heap_rewrite
calls. Then we'd be quite certain we are not lying: anything older
than that did indeed get frozen.

regards, tom lane