Re: Stale temp tables

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026