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@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