Thread: Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0

Jeff Amiel <becauseimjeff@yahoo.com> writes:
> Jan 13 08:27:26 prod-app-1 postgres[92257]: [30259-1] jboss 92257 ERROR:  could not access status of transaction
2107200825
> Jan 13 08:27:26 prod-app-1 postgres[92257]: [30259-2] jboss 92257 DETAIL:  could not open file "pg_clog/07D9": No
suchfile or directory 
> Jan 13 08:27:26 prod-app-1 postgres[92257]: [30259-3] jboss 92257 CONTEXT:  SQL statement "DELETE FROM audit_metadata
WHEREuser_id <> -1" 

> pg_clog dir looks like this:
> -rw-------  1 pgsql  wheel  262144 Jan 13 05:41 07DA
> -rw-------  1 pgsql  wheel  262144 Jan 13 08:06 07DB
> -rw-------  1 pgsql  wheel   90112 Jan 13 08:51 07DC

> Now that table, audit_metadata, is a temporary table (when accessed by jboss as it is here).  There is a 'rea'l table
withthe same name, but only used by batch processes that connect to postgres. 

Really?  Wow, *that's* an interesting thought.  Is it likely that that
temp table could contain many-hour-old data?

This seems unrelated to your issue with autovacuum (which should never
touch a temp table, and certainly isn't going to find one in template0)
... but I suddenly fear that we've missed a fundamental point about
pg_clog truncation.  And WAL wraparound for that matter.  To wit, a
sufficiently long-lived temp table could contain old XIDs, and there's
no way for anyone except the owning backend to clean them out, or even
guarantee that they're marked committed.

Thoughts?

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Really? Wow, *that's* an interesting thought. Is it likely that that
temp table could contain many-hour-old data?

Certainly...our connection pool used by jboss can have connections to postgres persisting for multiple days.  (We're still looking for a way to tell it to recycle these occasionally).  As each 'user' of our web based app performs some action, they acquire one of the connection pool connections and set their user_id in the temporary table used by that connection (we use that for our audit triggers)  Once they are 'done' with the connection, the connection is just released back to the pool but not actually closed...so the temp table still contains the  data from a previous iteration.

---

TV dinner still cooling?
Check out "Tonight's Picks" on Yahoo! TV.

I wrote:
> ... but I suddenly fear that we've missed a fundamental point about
> pg_clog truncation.  And WAL wraparound for that matter.  To wit, a
> sufficiently long-lived temp table could contain old XIDs, and there's
> no way for anyone except the owning backend to clean them out, or even
> guarantee that they're marked committed.

After further thought I believe this is OK as of 8.2, because a temp
table's relfrozenxid is tracked independently of any other's.  (This
problem puts a stake through the heart of the recently-discussed idea
that a temp table might be able to get along without a globally visible
pg_class entry, however.)

But it seems that we need a band-aid for 8.1 and earlier.  The simplest
fix I can think of is for vacuum not to attempt to advance the
datvacuumxid/datfrozenxid fields if it skipped over any temp tables of
other backends.  That's a bit nasty, since in a database making heavy
use of temp tables, you might do a whole lot of vacuums without ever
meeting that condition.  Anyone have a better idea?

            regards, tom lane

Tom Lane wrote:
> I wrote:
> > ... but I suddenly fear that we've missed a fundamental point about
> > pg_clog truncation.  And WAL wraparound for that matter.  To wit, a
> > sufficiently long-lived temp table could contain old XIDs, and there's
> > no way for anyone except the owning backend to clean them out, or even
> > guarantee that they're marked committed.
>
> After further thought I believe this is OK as of 8.2, because a temp
> table's relfrozenxid is tracked independently of any other's.  (This
> problem puts a stake through the heart of the recently-discussed idea
> that a temp table might be able to get along without a globally visible
> pg_class entry, however.)
>
> But it seems that we need a band-aid for 8.1 and earlier.  The simplest
> fix I can think of is for vacuum not to attempt to advance the
> datvacuumxid/datfrozenxid fields if it skipped over any temp tables of
> other backends.  That's a bit nasty, since in a database making heavy
> use of temp tables, you might do a whole lot of vacuums without ever
> meeting that condition.  Anyone have a better idea?

That seems nasty.  Can we examine the xmin of the pg_class entry for
temp tables instead?

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> But it seems that we need a band-aid for 8.1 and earlier.  The simplest
>> fix I can think of is for vacuum not to attempt to advance the
>> datvacuumxid/datfrozenxid fields if it skipped over any temp tables of
>> other backends.  That's a bit nasty, since in a database making heavy
>> use of temp tables, you might do a whole lot of vacuums without ever
>> meeting that condition.  Anyone have a better idea?

> That seems nasty.  Can we examine the xmin of the pg_class entry for
> temp tables instead?

No, because any sort of schema update on the temp table would rewrite
its pg_class row with a newer version.  You couldn't assume that the
pg_class row is older than what's in the table.  Consider this perfectly
reasonable scenario:

    CREATE TEMP TABLE foo ...
    COPY foo FROM ...
    CREATE INDEX ...        <- must set relhasindex


            regards, tom lane