Обсуждение: Re: AutoVacuum Behaviour Question

Bruce, please make sure to keep the list copied on replies.  I think
there is an important bug here and I don't want it to get lost just
because I lose track of it.  I'm also crossposting to pgsql-hackers.

Bruce McAlister wrote:

> okidoki, I tried this:
>
> blueface-crm=# select relname, nspname from pg_class join pg_namespace
> on (relnamespace = pg_namespace.oid) where
> pg_is_other_temp_schema(relnamespace);
>  relname  |  nspname
> ----------+------------
>  temp4295 | pg_temp_63
> (1 row)
>
> blueface-crm=# select pg_stat_get_backend_pid(63);
>  pg_stat_get_backend_pid
> -------------------------
>                     6661
> (1 row)
>
> blueface-crm=# select datname, client_addr, client_port from
> pg_stat_activity where procpid = '6661';
>     datname     | client_addr | client_port
> ----------------+-------------+-------------
>  whitelabel-ibb | 10.6.0.181  |        1587
> (1 row)
>
> Is that correct? If it is then I'm really confused, how can a connection
> to the whitelabel-ibb database create temporary tables in the
> blueface-crm database?

Well, it certainly seems like this shouldn't be happening.  Maybe the
table belonged to a session that crashed, but the pg_class entry has not
been cleaned up -- possibly because that backend has not connected to
that particular database.

Maybe autovacuum itself could do something about cleaning up this kind
of stuff on sight (--> dropping temp tables belonging to sessions that
crash).  I'm not sure though.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Well, it certainly seems like this shouldn't be happening.  Maybe the
> table belonged to a session that crashed, but the pg_class entry has not
> been cleaned up -- possibly because that backend has not connected to
> that particular database.

Hm --- a crash would mean that the temp table would remain until some
other session (a) connected to the same database (b) using the same
BackendId (sinval slot number), and (c) decided to create some temp
tables of its own.  So indeed it's not implausible that the table could
hang around for a long time, especially if you were unlucky enough that
the original creator had been using a very high BackendId slot.  (Which
pg_temp schema is this table attached to, anyway?)

> Maybe autovacuum itself could do something about cleaning up this kind
> of stuff on sight (--> dropping temp tables belonging to sessions that
> crash).  I'm not sure though.

Yeah, we had better investigate some way to clean them up.  It was never
obvious before that it mattered to get rid of orphan temp tables, but I
guess it does.

Another possibility is just to ignore temp tables while computing
datvacuumxid.  A temp table that survives for > 2G transactions is going
to be trouble, but I'm not sure there's anything we can usefully do
about it anyway --- certainly autovacuum has no power to fix it.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Well, it certainly seems like this shouldn't be happening.  Maybe the
> > table belonged to a session that crashed, but the pg_class entry has not
> > been cleaned up -- possibly because that backend has not connected to
> > that particular database.
>
> Hm --- a crash would mean that the temp table would remain until some
> other session (a) connected to the same database (b) using the same
> BackendId (sinval slot number), and (c) decided to create some temp
> tables of its own.  So indeed it's not implausible that the table could
> hang around for a long time, especially if you were unlucky enough that
> the original creator had been using a very high BackendId slot.  (Which
> pg_temp schema is this table attached to, anyway?)

It's pg_temp_63.  Backend 63 is running in another database.  It seems
perfectly possible that a backend connects to database A, creates a temp
table, crashes, then connects to database B after restart and then keeps
running there forever :-(

> > Maybe autovacuum itself could do something about cleaning up this kind
> > of stuff on sight (--> dropping temp tables belonging to sessions that
> > crash).  I'm not sure though.
>
> Yeah, we had better investigate some way to clean them up.  It was never
> obvious before that it mattered to get rid of orphan temp tables, but I
> guess it does.

Would it be enough to delete the tuple from pg_class?  I guess that will
leave behind the tuples in pg_attribute etc, but I don't see another way
to drop it ...  Maybe UPDATE to move it to the local temp schema and
then DROP it?

Or maybe it works to do DROP TABLE pg_temp_63.temp2394 as superuser ...?
I haven't tried.

> Another possibility is just to ignore temp tables while computing
> datvacuumxid.  A temp table that survives for > 2G transactions is going
> to be trouble, but I'm not sure there's anything we can usefully do
> about it anyway --- certainly autovacuum has no power to fix it.

Yes, I was going to suggest that, though it doesn't seem right.

--
Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Tiene valor aquel que admite que es un cobarde" (Fernandel)

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> Yeah, we had better investigate some way to clean them up.  It was never
>> obvious before that it mattered to get rid of orphan temp tables, but I
>> guess it does.

> Would it be enough to delete the tuple from pg_class?

No, you need a full DROP.  I don't see that that's harder than removing
only the pg_class tuple --- the problem in either case is to be sure
it's OK.  In particular, how to avoid a race condition against an
incoming backend that adopts that BackendId?  Worst-case, you could be
deleting a temp table he just made.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Tom Lane wrote:
> >> Yeah, we had better investigate some way to clean them up.  It was never
> >> obvious before that it mattered to get rid of orphan temp tables, but I
> >> guess it does.
>
> > Would it be enough to delete the tuple from pg_class?
>
> No, you need a full DROP.  I don't see that that's harder than removing
> only the pg_class tuple --- the problem in either case is to be sure
> it's OK.  In particular, how to avoid a race condition against an
> incoming backend that adopts that BackendId?  Worst-case, you could be
> deleting a temp table he just made.

Oh, I was just thinking in way for Bruce to get out of his current
situation.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Oh, I was just thinking in way for Bruce to get out of his current
> situation.

Oh, for that a manual "drop table" as superuser should work fine.

            regards, tom lane

Is this item closed?

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> writes:
> > > Tom Lane wrote:
> > >> Yeah, we had better investigate some way to clean them up.  It was never
> > >> obvious before that it mattered to get rid of orphan temp tables, but I
> > >> guess it does.
> >
> > > Would it be enough to delete the tuple from pg_class?
> >
> > No, you need a full DROP.  I don't see that that's harder than removing
> > only the pg_class tuple --- the problem in either case is to be sure
> > it's OK.  In particular, how to avoid a race condition against an
> > incoming backend that adopts that BackendId?  Worst-case, you could be
> > deleting a temp table he just made.
>
> Oh, I was just thinking in way for Bruce to get out of his current
> situation.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

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

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

Bruce Momjian wrote:
>
> Is this item closed?

No, it isn't.  Please add a TODO item about it:
 * Prevent long-lived temp tables from causing frozen-Xid advancement
   starvation


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

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> >
> > Is this item closed?
>
> No, it isn't.  Please add a TODO item about it:
>  * Prevent long-lived temp tables from causing frozen-Xid advancement
>    starvation

Sorry, I don't understand this.  Can you give me more text?  Thanks.

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

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

Andrew Hammond wrote:
> On 9/13/07, Bruce Momjian <bruce@momjian.us> wrote:
> >
> > Alvaro Herrera wrote:
> > > Bruce Momjian wrote:
> > > >
> > > > Is this item closed?
> > >
> > > No, it isn't.  Please add a TODO item about it:
> > >  * Prevent long-lived temp tables from causing frozen-Xid advancement
> > >    starvation
> >
> > Sorry, I don't understand this.  Can you give me more text?  Thanks.
> >
>
> s/long-lived/orphaned/ ? And possibly this means better orphan detection and
> removal.

Added:

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

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

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

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

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> >
> > Is this item closed?
>
> No, it isn't.  Please add a TODO item about it:
>  * Prevent long-lived temp tables from causing frozen-Xid advancement
>    starvation

Thanks.  Added to TODO.

--
  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. +

No, it isn't.  Please add a TODO item about it:* Prevent long-lived temp tables from causing frozen-Xid advancement  starvation   

Jeff Amiel wrote:
>
> Bruce Momjian wrote:
>>>
>>> No, it isn't.  Please add a TODO item about it:
>>>  * Prevent long-lived temp tables from causing frozen-Xid advancement
>>>    starvation
>
> Can somebody explain this one to me?  because of our auditing technique, we
> have many LONG lived temp tables.....(one per pooled connection)...so as
> long as the pool isn't disturbed, these temp tables can exist for a long
> time (weeks....months?)

Hmm.  The problem is that the system can't advance the frozen Xid for a
database when there are temp tables that live for long periods of time.
Autovacuum can't vacuum those tables; if the app vacuums them itself
then there's no problem, but you can only vacuum them in the same
session that creates it.

The problem with a frozen Xid (datfrozenxid) that doesn't advance is of
Xid-wraparound nature.  The system eventually shuts itself down to
prevent data loss, so if those temp tables live a really long life, you
could be subject to that.  (The immediate symptom is that pg_clog
segments do not get recycled, which is not serious because it's just
wasted disk space, and it's not a lot).

> (previous thread about our use of temp tables and autovacuum/xid issues)
> http://archives.postgresql.org/pgsql-general/2007-01/msg00690.php
> http://archives.postgresql.org/pgsql-general/2007-01/msg00691.php

Sorry, I'm offline ATM and can't check those.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Cuando miro a alguien, más me atrae cómo cambia que quién es" (J. Binoche)

Alvaro Herrera <alvherre@commandprompt.com> writes:
>>>> No, it isn't.  Please add a TODO item about it:
>>>> * Prevent long-lived temp tables from causing frozen-Xid advancement
>>>> starvation
>>
> Jeff Amiel wrote:
>> Can somebody explain this one to me?  because of our auditing technique, we
>> have many LONG lived temp tables.....(one per pooled connection)...so as
>> long as the pool isn't disturbed, these temp tables can exist for a long
>> time (weeks....months?)

> Hmm.  The problem is that the system can't advance the frozen Xid for a
> database when there are temp tables that live for long periods of time.
> Autovacuum can't vacuum those tables; if the app vacuums them itself
> then there's no problem, but you can only vacuum them in the same
> session that creates it.

I'm not convinced there's a huge problem here.  Surely Jeff's app is
going to either vacuum or truncate those temp tables occasionally;
otherwise they'll bloat to the point of uselessness.  Either action
will fix the problem.

The real issue is that the app has to remember to do that.  Perhaps
a better TODO item would be
    * Find a way to autovacuum temp tables
though I admit I have no clue how to do that without giving up most
of the performance advantages of temp tables.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> >>>> No, it isn't.  Please add a TODO item about it:
> >>>> * Prevent long-lived temp tables from causing frozen-Xid advancement
> >>>> starvation
> >>
> > Jeff Amiel wrote:
> >> Can somebody explain this one to me?  because of our auditing technique, we
> >> have many LONG lived temp tables.....(one per pooled connection)...so as
> >> long as the pool isn't disturbed, these temp tables can exist for a long
> >> time (weeks....months?)
>
> > Hmm.  The problem is that the system can't advance the frozen Xid for a
> > database when there are temp tables that live for long periods of time.
> > Autovacuum can't vacuum those tables; if the app vacuums them itself
> > then there's no problem, but you can only vacuum them in the same
> > session that creates it.
>
> I'm not convinced there's a huge problem here.  Surely Jeff's app is
> going to either vacuum or truncate those temp tables occasionally;
> otherwise they'll bloat to the point of uselessness.  Either action
> will fix the problem.
>
> The real issue is that the app has to remember to do that.  Perhaps
> a better TODO item would be
>     * Find a way to autovacuum temp tables
> though I admit I have no clue how to do that without giving up most
> of the performance advantages of temp tables.

TODO updated:

* 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.

--
  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. +