Thread: Stale temp tables
Friends,
I've got a whole bunch (8,000 or so) of old stale temp tables that were the
result of a hung backend. I can see them using pgAdmin II, but not through
psql. Is there any way to dump these guys, should I dump them, are they
likely to be causing problems? What should I do about this?
Thanks,
Peter D.
Peter Darley wrote: > Friends, > I've got a whole bunch (8,000 or so) of old stale temp tables that were the > result of a hung backend. I can see them using pgAdmin II, but not through > psql. Is there any way to dump these guys, should I dump them, are they > likely to be causing problems? What should I do about this? Wow, that is a good question. You can stop the postmaster and start the 'postgres' binary with the -O option and drop them. -O is required because they are system tables. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Mon, Mar 11, 2002 at 03:49:22PM -0500, Bruce Momjian wrote: > Peter Darley wrote: > > Friends, > > I've got a whole bunch (8,000 or so) of old stale temp tables that were the > > result of a hung backend. I can see them using pgAdmin II, but not through > > psql. Is there any way to dump these guys, should I dump them, are they > > likely to be causing problems? What should I do about this? > > Wow, that is a good question. You can stop the postmaster and start the > 'postgres' binary with the -O option and drop them. -O is required > because they are system tables. Isn't this something VACUUM should be checking for? Assuming that it could tell if a temporary table was still in use by another backend, it would be possible for the system to go through the data directory and delete any temporary tables not in use. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > If the company that invents a cure for AIDS is expected to make their > money back in 17 years, why can't we ask the same of the company that > markets big-titted lip-syncing chicks and goddamn cartoon mice?
Martijn van Oosterhout wrote:
> On Mon, Mar 11, 2002 at 03:49:22PM -0500, Bruce Momjian wrote:
> > Peter Darley wrote:
> > > Friends,
> > > I've got a whole bunch (8,000 or so) of old stale temp tables that were the
> > > result of a hung backend. I can see them using pgAdmin II, but not through
> > > psql. Is there any way to dump these guys, should I dump them, are they
> > > likely to be causing problems? What should I do about this?
> >
> > Wow, that is a good question. You can stop the postmaster and start the
> > 'postgres' binary with the -O option and drop them. -O is required
> > because they are system tables.
>
> Isn't this something VACUUM should be checking for? Assuming that it could
> tell if a temporary table was still in use by another backend, it would be
> possible for the system to go through the data directory and delete any
> temporary tables not in use.
Agreed. Should be very possible. I actually wrote such a function to
be added to 7.1 but Tom didn't like it because he was concerned about
the use of oid's to determine if any other backend was using the temp
table.
It is at:
ftp://candle.pha.pa.us/pub/postgresql/mypatches/clean.patch
I am looking for other ideas to get this functionality into the
database.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Mon, Mar 11, 2002 at 05:57:28PM -0500, Bruce Momjian wrote: > Martijn van Oosterhout wrote: > > Isn't this something VACUUM should be checking for? Assuming that it could > > tell if a temporary table was still in use by another backend, it would be > > possible for the system to go through the data directory and delete any > > temporary tables not in use. > > Agreed. Should be very possible. I actually wrote such a function to > be added to 7.1 but Tom didn't like it because he was concerned about > the use of oid's to determine if any other backend was using the temp > table. Using OIDs does seem a bit flimsy. It depends entirely on how temp tables are handled. If, for example, they were stored in normal pg_class and the rows are automatically deleted when the backend terminates, then a simple scan of pg_class will tell you which files are in use. If not deleted you could mark them with a backend identifier so you know when the backend has died. IIRC though, temporary tables only exist in the image of the postmaster that created them, so there is no way to find out which ones are supposed to exists. Maybe you need a new system table pg_temp_tables to track them... Is there some way you can use the schema code to do this? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > If the company that invents a cure for AIDS is expected to make their > money back in 17 years, why can't we ask the same of the company that > markets big-titted lip-syncing chicks and goddamn cartoon mice?
Bruce (or someone else),
Once I start PostgreSQL with the -O option, is there any way to drop tables
according to a wild card operation? If not, will I be safe with a perl
script like:
my $dbhPG = DBI->connect(Connect String, etc);
my $sthTables = $dbhPG->prepare("select relname from pg_class where relname
like 'pg_temp.8227.%'); $sthTables->execute();
while (my $TableName = $sthTables->fetchrow())
{
$dbhPG->do("DROP TABLE $TableName");
}
$sthTables->finish();
$dbhPG->disconnect();
8227 is the ID of the backend that died or something? All the temp tables
are all named pg_temp.8227.something
I realy don't want to damage my production DB, so please let me know if
this will work without hurting anything.
Thanks,
Peter Darley
-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Monday, March 11, 2002 12:49 PM
To: Peter Darley
Cc: Pgsql-General
Subject: Re: [GENERAL] Stale temp tables
Peter Darley wrote:
> Friends,
> I've got a whole bunch (8,000 or so) of old stale temp tables that were
the
> result of a hung backend. I can see them using pgAdmin II, but not
through
> psql. Is there any way to dump these guys, should I dump them, are they
> likely to be causing problems? What should I do about this?
Wow, that is a good question. You can stop the postmaster and start the
'postgres' binary with the -O option and drop them. -O is required
because they are system tables.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Martijn van Oosterhout <kleptog@svana.org> writes:
> IIRC though, temporary tables only exist in the image of the postmaster that
> created them, so there is no way to find out which ones are supposed to
> exists. Maybe you need a new system table pg_temp_tables to track them...
> Is there some way you can use the schema code to do this?
What I'm intending in 7.3 is that temp tables will live in
special-purpose schemas, so it should be relatively easy to identify
them correctly.
I was toying with an approach like this:
* Identify the temp schemas by names along the lines of pg_temp_N, where
N ranges from 1 to max_connections. A particular backend gets to use
the temp schema associated with its BackendId (PROC array slot).
* For speed, do nothing with the temp schema until/unless a temp table
creation is requested in the current session. At that point, delete
anything we find in the temp schema (it must be from a crashed backend),
add the temp schema to the search path, and proceed with table creation.
As is, this scheme would not ensure prompt cleanout of dead temp tables;
particularly for the higher-numbered PROC slots, it might be a long time
before someone else wants to use temp tables in that slot. You could do
the cleanup manually if you had to reclaim space sooner. We could
theoretically offer some automated tool for this, but I'm not sure that
it's worth the trouble. I would definitely not support expecting the
postmaster to do it automatically at startup. (The postmaster isn't a
backend; it would have to launch 1 backend per database to perform this
function.)
regards, tom lane