Thread: General Bug Report: TEMP TABLES becomes permanent CATALOG TABLES

General Bug Report: TEMP TABLES becomes permanent CATALOG TABLES

From

Unprivileged user

Date:

01 July 1999, 21:20:04

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Ricardo Coelho
Your email address      : rcoelho@px.com.br

Category                : runtime: back-end
Severity                : serious

Summary: TEMP TABLES becomes permanent CATALOG TABLES

System Configuration
--------------------
  Operating System   : RedHat Linux 5.2 Intel

  PostgreSQL version : 6.5

  Compiler used      : gcc

Hardware:
---------
Pentium II 350MHz 128M RAM

Versions of other tools:
------------------------


--------------------------------------------------------------------------

Problem Description:
--------------------
When you execute a wrong SQL command after create a TEMP TABLE, postgres backend doesn't drop this table after
connectionends. We can't drop pg_temp.PID.N with "drop table" command because it is a system catalog table. So, we had
todumpall 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.

--------------------------------------------------------------------------

Solution:
---------


--------------------------------------------------------------------------

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

From

Bruce Momjian

Date:

02 July 1999, 00:34:07

>
> ============================================================================
>                         POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
>
>
> Your name               : Ricardo Coelho
> Your email address      : rcoelho@px.com.br
>
> Category                : runtime: back-end
> Severity                : serious
>
> Summary: TEMP TABLES becomes permanent CATALOG TABLES
>
> System Configuration
> --------------------
>   Operating System   : RedHat Linux 5.2 Intel
>
>   PostgreSQL version : 6.5
>
>   Compiler used      : gcc
>
> Hardware:
> ---------
> Pentium II 350MHz 128M RAM
>
> Versions of other tools:
> ------------------------
>
>
> --------------------------------------------------------------------------
>
> Problem Description:
> --------------------

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

I can confirm that this a bug.  I am looking at it now.  As a
workaround, you can delete the temp tables by starting a postgres
backend with the -O flag to allow system table modifications, and
droping the table.

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

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

From

Bruce Momjian

Date:

02 July 1999, 03:03:42

> 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

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

From

Bruce Momjian

Date:

02 July 1999, 03:31:48

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

I think I may know the cause.  The at_exit removal of the temp tables is
taking place in a failed transaction.  Let me test that idea tomorrow.

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

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

From

Bruce Momjian

Date:

02 July 1999, 17:06:57

> > 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.
>
> I can confirm that this a bug.  I am looking at it now.  As a
> workaround, you can delete the temp tables by starting a postgres
> backend with the -O flag to allow system table modifications, and
> droping the table.

OK, I have fixed the problem, and the patch is attached.  The fix will
appear in 6.5.1, due out in mid-July.

The problem is that the temp tables are removed on exit, but they were
not given their own transaction, and were executed in the last
transaction of the session.  If that last session was aborted, the
entries were not being removed from pg_class.

If anyone thinks doing a transaction on exit is a bad idea, please let
me know.

--
  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
? src/Makefile.custom
? src/config.log
? src/log
? src/config.cache
? src/config.status
? src/GNUmakefile
? src/Makefile.global
? src/backend/fmgr.h
? src/backend/parse.h
? src/backend/postgres
? src/backend/global1.bki.source
? src/backend/local1_template1.bki.source
? src/backend/global1.description
? src/backend/local1_template1.description
? src/backend/bootstrap/bootparse.c
? src/backend/bootstrap/bootstrap_tokens.h
? src/backend/bootstrap/bootscanner.c
? src/backend/catalog/genbki.sh
? src/backend/catalog/global1.bki.source
? src/backend/catalog/global1.description
? src/backend/catalog/local1_template1.bki.source
? src/backend/catalog/local1_template1.description
? src/backend/port/Makefile
? src/backend/utils/Gen_fmgrtab.sh
? src/backend/utils/fmgr.h
? src/backend/utils/fmgrtab.c
? src/bin/cleardbdir/cleardbdir
? src/bin/createdb/createdb
? src/bin/createlang/createlang
? src/bin/createuser/createuser
? src/bin/destroydb/destroydb
? src/bin/destroylang/destroylang
? src/bin/destroyuser/destroyuser
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_dump/Makefile
? src/bin/pg_dump/pg_dump
? src/bin/pg_id/pg_id
? src/bin/pg_passwd/pg_passwd
? src/bin/pg_version/Makefile
? src/bin/pg_version/pg_version
? src/bin/pgtclsh/mkMakefile.tcldefs.sh
? src/bin/pgtclsh/mkMakefile.tkdefs.sh
? src/bin/pgtclsh/Makefile.tkdefs
? src/bin/pgtclsh/Makefile.tcldefs
? src/bin/pgtclsh/pgtclsh
? src/bin/pgtclsh/pgtksh
? src/bin/psql/Makefile
? src/bin/psql/psql
? src/include/version.h
? src/include/config.h
? src/interfaces/ecpg/lib/Makefile
? src/interfaces/ecpg/lib/libecpg.so.3.0.0
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpgtcl/Makefile
? src/interfaces/libpgtcl/libpgtcl.so.2.0
? src/interfaces/libpq/Makefile
? src/interfaces/libpq/libpq.so.2.0
? src/interfaces/libpq++/Makefile
? src/interfaces/libpq++/libpq++.so.3.0
? src/interfaces/odbc/GNUmakefile
? src/interfaces/odbc/Makefile.global
? src/lextest/lex.yy.c
? src/lextest/lextest
? src/pl/plpgsql/src/Makefile
? src/pl/plpgsql/src/mklang.sql
? src/pl/plpgsql/src/pl_gram.c
? src/pl/plpgsql/src/pl.tab.h
? src/pl/plpgsql/src/pl_scan.c
? src/pl/plpgsql/src/libplpgsql.so.1.0
? src/pl/tcl/mkMakefile.tcldefs.sh
? src/pl/tcl/Makefile.tcldefs
? src/template/linux_m68k
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.106
diff -c -r1.106 postmaster.c
*** src/backend/postmaster/postmaster.c    1999/06/04 21:14:46    1.106
--- src/backend/postmaster/postmaster.c    1999/07/02 18:03:17
***************
*** 1483,1490 ****
       * Let's clean up ourselves as the postmaster child
       */

!     on_exit_reset();            /* we don't want the postmaster's
!                                  * proc_exit() handlers */

      /* ----------------
       *    register signal handlers.
--- 1483,1490 ----
       * Let's clean up ourselves as the postmaster child
       */

!     /* We don't want the postmaster's proc_exit() handlers */
!     on_exit_reset();

      /* ----------------
       *    register signal handlers.
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.118
diff -c -r1.118 postgres.c
*** src/backend/tcop/postgres.c    1999/05/29 10:25:30    1.118
--- src/backend/tcop/postgres.c    1999/07/02 18:03:20
***************
*** 1483,1499 ****
          puts("\treset_client_encoding() done.");
  #endif

-     /* ----------------
-      * if stable main memory is assumed (-S(old) flag is set), it is necessary
-      * to flush all dirty shared buffers before exit
-      * plai 8/7/90
-      * this used to be done further down, causing an additional entry in
-      * the shmem exit list for every error :-( ... tgl 10/1/98
-      * ----------------
-      */
-     if (!TransactionFlushEnabled())
-         on_shmem_exit(FlushBufferPool, NULL);
-
      on_shmem_exit(remove_all_temp_relations, NULL);

      /* ----------------
--- 1483,1488 ----
Index: src/backend/utils/cache/temprel.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/utils/cache/temprel.c,v
retrieving revision 1.4
diff -c -r1.4 temprel.c
*** src/backend/utils/cache/temprel.c    1999/05/25 22:42:16    1.4
--- src/backend/utils/cache/temprel.c    1999/07/02 18:03:21
***************
*** 30,35 ****
--- 30,36 ----
  #include "nodes/pg_list.h"
  #include "utils/mcxt.h"
  #include "utils/temprel.h"
+ #include "access/xact.h"
  #include "access/htup.h"
  #include "access/heapam.h"
  #include "catalog/heap.h"
***************
*** 79,84 ****
--- 80,87 ----
      List       *l,
                 *next;

+     StartTransactionCommand();
+
      l = temp_rels;
      while (l != NIL)
      {
***************
*** 102,107 ****
--- 105,111 ----

          l = next;
      }
+     CommitTransactionCommand();
  }

  /* we don't have the relname for indexes, so we just pass the oid */

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

From

Tom Lane

Date:

03 July 1999, 14:31:09

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> I think I may know the cause.  The at_exit removal of the temp tables is
> taking place in a failed transaction.  Let me test that idea tomorrow.

Take a look at the code for cleaning up the listen/notify table
(Async_UnlistenOnExit() in backend/commands/async.c).  It used to
have problems with cleaning up when the last transaction executed
by the backend had failed.  I fixed that by aborting any old transaction
and starting/committing a new one.  I believe that any at_exit,
on_shmem_exit, etc routine that tries to perform database changes will
need to be coded similarly.

            regards, tom lane

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

From

Bruce Momjian

Date:

03 July 1999, 14:46:09

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > I think I may know the cause.  The at_exit removal of the temp tables is
> > taking place in a failed transaction.  Let me test that idea tomorrow.
>
> Take a look at the code for cleaning up the listen/notify table
> (Async_UnlistenOnExit() in backend/commands/async.c).  It used to
> have problems with cleaning up when the last transaction executed
> by the backend had failed.  I fixed that by aborting any old transaction
> and starting/committing a new one.  I believe that any at_exit,
> on_shmem_exit, etc routine that tries to perform database changes will
> need to be coded similarly.

Thanks.  I have added AbortOutOfAnyTransaction() to the fix.  I did not
have that function call.

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

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

From

Bruce Momjian

Date:

08 July 1999, 02:07:48

> Problem Description:  -------------------- 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.

This will be fixed in 6.5.1, due out next week.

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