Re: [BUGS] General Bug Report: TEMP TABLES becomes permanent CATALOG TABLES

> When you execute a wrong SQL command after create a TEMP TABLE,
> postgres backend doesn't drop this table after connection ends. We can't
> drop pg_temp.PID.N with "drop table" command because it is a system
> catalog table. So, we had to dumpall the database, edit db.out file to
> erase lines of pg_temp??? create table and reload it again.

>
> --------------------------------------------------------------------------
>
> Test Case:
> ----------
> psql mydb
> mydb=> select * into temp table TMP from anytable;
> mydb=> drop table invalidTable;
> mydb=> \q
> psql mydb
> mydb=> \dS    --> You will see a new permanent system table.

OK, I have looked at the problem, and found a few things.  First, if you
look in data/base/dbname, you will see the actual temp files are not
there.  The only place they exist after psql exit is in pg_class.

Second, I found that if I do:

    select * into temp xx from yy;
    drop table badname;
    select * into temp xxe from yy;

it does not leave around the temp tables, but it if I add a second drop,
it fails again, so it appears that I have to exit on a bad command to
have the entries left around.  Just issuing the select without the bad
command cleans up properly, so I am left to believe that the failed
command is doing something strange.  My guess is that somehow the dirty
marks on buffers is getting cleared by the bad command, and on exit, the
new pg_class blocks are not getting put on disk.

Tom Lane, you did the code that does special things when there is a
failed command, right?  It was because creating a table inside a failed
transaction was leaving around the old cache entries.  Could this be
causing this problem?

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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