Re: Bug: Unreferenced temp tables disables vacuum to update xid

Joshua D. Drake wrote:

Ping?

> -----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.
>
> This by itself isn't a problem because you just vacuum right? Well we
> vacuumed... and the problem didn't resolve itself. It continued to
> throw the warning:
>
> FATAL: database is not accepting commands to avoid wraparound
> data loss in database "foo"
> HINT: Stop the postmaster and use a standalone backend to vacuum
> database "foo".
>
> And when in --single with postgres we would get:
>
> 2008-01-06 02:04:45 EST WARNING: database "foo" must be vacuumed
> within 993712 transactions
> 2008-01-06 02:04:45 EST HINT: To avoid a database shutdown,
> execute a full-database VACUUM in "foo".
>
> We performed all the requisite queries to determine where the problem
> was:
>
> SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
>
> Everything returned ~ 50 mil
>
> But:
>
> SELECT datname, age(datfrozenxid) FROM pg_database;
>
> Always returned ~ 2bil.
>
> Even after two vacuums (one a vacuum and the other a vacuum analyze).
>
> Anyway.. we tried a lot of different things, including adjusting
> xidStopLimit so we could get back into interactive mode and have a
> reasonable interface to work with...
>
> The end result was that by chance we checked relkind = 't' instead of
> 'r' (Shout out to AndrewSN). And sure enough:
>
> pg_toast_49013869 | 2146491285
>
> And yes:
>
> 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.
>
> May I humbly suggest that a:
>
> * We need to check clean up unreferenced temp relations on startup and
> remove them
>
> * We need to change the docs for the following query:
>
> SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
>
> To:
>
> SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' OR
> relkind = 't';
>
> I apologize if this doesn't quite make sense. I am very tired but I
> wanted to make sure to get this out on the list.
>
> 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)
>
> iD8DBQFHgJQaATb/zqfZUUQRAnKAAJ0fs0OahvGjlJq6fWrFZ67h1tY6qwCfcHmR
> K0xOKL+JMAcPTQGbqR3qy1M=
> =te9S
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>