Обсуждение: Port Bug Report: transaction control interacts strangely with drop table

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


Your name               : Christopher Oliver
Your email address      : oliver@traverse.net

Category                : runtime: back-end
Severity                : serious

Summary: transaction control interacts strangely with drop table

System Configuration
--------------------
  Operating System   : Linux 2.0.33 ELF

  PostgreSQL version : 6.5

  Compiler used      : 2.7.3.2

Hardware:
---------
Pentium 133 / 64M RAM

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


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

Problem Description:
--------------------
Transactions do not protect against outside visibility of
drop table.  The interaction leaves an empty file which
held the table which prevents a similarly named table
from being created.  Further aborting does not undo
effects of drop table.  This does not seem right.



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

Test Case:
----------
Scenario:

  1) open two psql sessions (A and B) on the same database.

  2) in session 1, create a talbe an populate with entries.

  3) in session 2, select * from table to see that values
  are present;

  4) in session 1, begin a transaction and drop the
  newly created table.

  5) in session 2, select * from the table.  Observe
  that the entries are gone though the transaction is
  not commited.

  6) In session 1, commit the transaction.

  7) In session 2, observe select * still shows an
  empty table where none should exist.

  8) In session 2, observe that while the table doesn't
  exist, you may not create another with the same name.
  (The file representing the table still exists, and it
  is empty.)

Note also that an abort of the transaction will not cause
the data to return.


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

Solution:
---------


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

We have played with this problem for quite some time.  I thought it had
been fixed.  Obviously not.  You give a good example of the problem.



>   PostgreSQL version : 6.5
>
>   Compiler used      : 2.7.3.2
>
> Hardware:
> ---------
> Pentium 133 / 64M RAM
>
> Versions of other tools:
> ------------------------
>
>
> --------------------------------------------------------------------------
>
> Problem Description:
> --------------------
> Transactions do not protect against outside visibility of
> drop table.  The interaction leaves an empty file which
> held the table which prevents a similarly named table
> from being created.  Further aborting does not undo
> effects of drop table.  This does not seem right.
>
>
>
> --------------------------------------------------------------------------
>
> Test Case:
> ----------
> Scenario:
>
>   1) open two psql sessions (A and B) on the same database.
>
>   2) in session 1, create a talbe an populate with entries.
>
>   3) in session 2, select * from table to see that values
>   are present;
>
>   4) in session 1, begin a transaction and drop the
>   newly created table.
>
>   5) in session 2, select * from the table.  Observe
>   that the entries are gone though the transaction is
>   not commited.
>
>   6) In session 1, commit the transaction.
>
>   7) In session 2, observe select * still shows an
>   empty table where none should exist.
>
>   8) In session 2, observe that while the table doesn't
>   exist, you may not create another with the same name.
>   (The file representing the table still exists, and it
>   is empty.)
>
> Note also that an abort of the transaction will not cause
> the data to return.
>
>
> --------------------------------------------------------------------------
>
> Solution:
> ---------
>
>
> --------------------------------------------------------------------------
>
>
>


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

We are working on cleaning this up for 6.6.  We will issue notices to
the user so they know they can't rollback a transaction that deletes a
table.



>
> ============================================================================
>                         POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
>
>
> Your name               : Christopher Oliver
> Your email address      : oliver@traverse.net
>
> Category                : runtime: back-end
> Severity                : serious
>
> Summary: transaction control interacts strangely with drop table
>
> System Configuration
> --------------------
>   Operating System   : Linux 2.0.33 ELF
>
>   PostgreSQL version : 6.5
>
>   Compiler used      : 2.7.3.2
>
> Hardware:
> ---------
> Pentium 133 / 64M RAM
>
> Versions of other tools:
> ------------------------
>
>
> --------------------------------------------------------------------------
>
> Problem Description:
> --------------------
> Transactions do not protect against outside visibility of
> drop table.  The interaction leaves an empty file which
> held the table which prevents a similarly named table
> from being created.  Further aborting does not undo
> effects of drop table.  This does not seem right.
>
>
>
> --------------------------------------------------------------------------
>
> Test Case:
> ----------
> Scenario:
>
>   1) open two psql sessions (A and B) on the same database.
>
>   2) in session 1, create a talbe an populate with entries.
>
>   3) in session 2, select * from table to see that values
>   are present;
>
>   4) in session 1, begin a transaction and drop the
>   newly created table.
>
>   5) in session 2, select * from the table.  Observe
>   that the entries are gone though the transaction is
>   not commited.
>
>   6) In session 1, commit the transaction.
>
>   7) In session 2, observe select * still shows an
>   empty table where none should exist.
>
>   8) In session 2, observe that while the table doesn't
>   exist, you may not create another with the same name.
>   (The file representing the table still exists, and it
>   is empty.)
>
> Note also that an abort of the transaction will not cause
> the data to return.
>
>
> --------------------------------------------------------------------------
>
> Solution:
> ---------
>
>
> --------------------------------------------------------------------------
>
>
>


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