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
