Thread: transaction block: server closed the connection unexpectedly

Hi

While testing 8.0's transaction mechanism, I encountered this error:
(I can reproduce this error with the latest cvs codes)

========================================================================
Welcome to psql 8.0.0beta2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# BEGIN;
BEGIN
template1=# CREATE TABLE FOO ( a int unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "foo_a_key" for
table
"foo"
CREATE TABLE
template1=# INSERT INTO FOO VALUES ( 0 );
INSERT 17232 1
template1=# INSERT INTO FOO VALUES ( 0 );
ERROR:  duplicate key violates unique constraint "foo_a_key"
FATAL:  block 1 of 1663/1/17230 is still referenced (private 1, global 1)
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
template1=#
========================================================================

This error cann't be reproduced If the CREATE TABLE is called outside the
transaction block.


Regards

koju

----------------------------------------------------------------------------
---
Koju Iijima

Software Engineer
Fujitsu Australia Software Technology
Address: 14 Rodborough Road, Frenchs Forest NSW 2086
Tel: +61 2 9452 9076
Fax: +61 2 9975 2899
Email: koju@fast.fujitsu.com.au
Web site: www.fastware.com
----------------------------------------------------------------------------
---

This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the
ordinaryuser of the email address to which it was addressed and may contain copyright and/or legally privileged
information.No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive
thisemail in error, please return to sender. Thank you. 

If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please
emailunsubscribe@fast.fujitsu.com.au 

On Mon, Sep 06, 2004 at 04:41:32PM +1000, Koju Iijima wrote:

Hi,

> template1=# BEGIN;
> BEGIN
> template1=# CREATE TABLE FOO ( a int unique);
> NOTICE:  CREATE TABLE / UNIQUE will create implicit index "foo_a_key" for table "foo"
> CREATE TABLE
> template1=# INSERT INTO FOO VALUES ( 0 );
> INSERT 17232 1
> template1=# INSERT INTO FOO VALUES ( 0 );
> ERROR:  duplicate key violates unique constraint "foo_a_key"
> FATAL:  block 1 of 1663/1/17230 is still referenced (private 1, global 1)

The problem is that there's a pin on an index page when the smgr tries
to drop its buffers.  This patch corrects this problem, by having
resowner cleanup before smgr.

It also passes regression tests.  Not sure if it's the correct solution
though, but it seems the natural thing to me.

Thanks for the report.  I wonder how we managed to miss this.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> The problem is that there's a pin on an index page when the smgr tries
> to drop its buffers.  This patch corrects this problem, by having
> resowner cleanup before smgr.

Good diagnosis, not very good fix.  You didn't do anything about
adjusting the comments to match the code, and you missed the identical
bug occurring during subtransaction abort.

One of the most powerful programming techniques I know is, once you've
identified a bug, to ask yourself "where else might I (or others) have
made this same error?".  That would have led you to the subtransaction
case, even if we hadn't already agreed that the order of operations
during xact/subxact commit/abort should be kept the same as much as
possible.

Patch as-applied is attached.

            regards, tom lane


*** src/backend/access/transam/xact.c.orig    Mon Aug 30 15:00:03 2004
--- src/backend/access/transam/xact.c    Mon Sep  6 13:52:42 2004
***************
*** 1333,1341 ****
       * backend-wide state.
       */

-     smgrDoPendingDeletes(true);
-     /* smgrcommit already done */
-
      CallXactCallbacks(XACT_EVENT_COMMIT, InvalidTransactionId);

      ResourceOwnerRelease(TopTransactionResourceOwner,
--- 1333,1338 ----
***************
*** 1352,1357 ****
--- 1349,1362 ----
       */
      AtEOXact_Inval(true);

+     /*
+      * Likewise, dropping of files deleted during the transaction is best done
+      * after releasing relcache and buffer pins.  (This is not strictly
+      * necessary during commit, since such pins should have been released
+      * already, but this ordering is definitely critical during abort.)
+      */
+     smgrDoPendingDeletes(true);
+
      ResourceOwnerRelease(TopTransactionResourceOwner,
                           RESOURCE_RELEASE_LOCKS,
                           true, true);
***************
*** 1363,1368 ****
--- 1368,1374 ----
      AtEOXact_SPI(true);
      AtEOXact_on_commit_actions(true, s->transactionIdData);
      AtEOXact_Namespace(true);
+     /* smgrcommit already done */
      AtEOXact_Files();
      pgstat_count_xact_commit();

***************
*** 1481,1495 ****
       * ordering.
       */

-     smgrDoPendingDeletes(false);
-     smgrabort();
-
      CallXactCallbacks(XACT_EVENT_ABORT, InvalidTransactionId);

      ResourceOwnerRelease(TopTransactionResourceOwner,
                           RESOURCE_RELEASE_BEFORE_LOCKS,
                           false, true);
      AtEOXact_Inval(false);
      ResourceOwnerRelease(TopTransactionResourceOwner,
                           RESOURCE_RELEASE_LOCKS,
                           false, true);
--- 1487,1499 ----
       * ordering.
       */

      CallXactCallbacks(XACT_EVENT_ABORT, InvalidTransactionId);

      ResourceOwnerRelease(TopTransactionResourceOwner,
                           RESOURCE_RELEASE_BEFORE_LOCKS,
                           false, true);
      AtEOXact_Inval(false);
+     smgrDoPendingDeletes(false);
      ResourceOwnerRelease(TopTransactionResourceOwner,
                           RESOURCE_RELEASE_LOCKS,
                           false, true);
***************
*** 1501,1506 ****
--- 1505,1511 ----
      AtEOXact_SPI(false);
      AtEOXact_on_commit_actions(false, s->transactionIdData);
      AtEOXact_Namespace(false);
+     smgrabort();
      AtEOXact_Files();
      pgstat_count_xact_rollback();

***************
*** 3014,3020 ****
      AtSubCommit_Notify();
      AtEOSubXact_UpdatePasswordFile(true, s->transactionIdData,
                                     s->parent->transactionIdData);
-     AtSubCommit_smgr();

      CallXactCallbacks(XACT_EVENT_COMMIT_SUB, s->parent->transactionIdData);

--- 3019,3024 ----
***************
*** 3024,3029 ****
--- 3028,3034 ----
      AtEOSubXact_RelationCache(true, s->transactionIdData,
                                s->parent->transactionIdData);
      AtEOSubXact_Inval(true);
+     AtSubCommit_smgr();
      ResourceOwnerRelease(s->curTransactionOwner,
                           RESOURCE_RELEASE_LOCKS,
                           true, false);
***************
*** 3109,3116 ****
      RecordSubTransactionAbort();

      /* Post-abort cleanup */
-     AtSubAbort_smgr();
-
      CallXactCallbacks(XACT_EVENT_ABORT_SUB, s->parent->transactionIdData);

      ResourceOwnerRelease(s->curTransactionOwner,
--- 3114,3119 ----
***************
*** 3119,3124 ****
--- 3122,3128 ----
      AtEOSubXact_RelationCache(false, s->transactionIdData,
                                s->parent->transactionIdData);
      AtEOSubXact_Inval(false);
+     AtSubAbort_smgr();
      ResourceOwnerRelease(s->curTransactionOwner,
                           RESOURCE_RELEASE_LOCKS,
                           false, false);
*** src/backend/storage/smgr/smgr.c.orig    Sun Aug 29 22:57:38 2004
--- src/backend/storage/smgr/smgr.c    Mon Sep  6 13:39:42 2004
***************
*** 784,790 ****
  }

  /*
!  *    smgrabort() -- Abort changes made during the current transaction.
   */
  void
  smgrabort(void)
--- 784,790 ----
  }

  /*
!  *    smgrabort() -- Clean up after transaction abort.
   */
  void
  smgrabort(void)
*** src/test/regress/expected/transactions.out.orig    Thu Aug 12 14:57:49 2004
--- src/test/regress/expected/transactions.out    Mon Sep  6 13:46:34 2004
***************
*** 374,379 ****
--- 374,394 ----
      FETCH 10 FROM c;
  ERROR:  portal "c" cannot be run
  COMMIT;
+ -- test case for problems with dropping an open relation during abort
+ BEGIN;
+     savepoint x;
+         CREATE TABLE koju (a INT UNIQUE);
+ NOTICE:  CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju"
+         INSERT INTO koju VALUES (1);
+         INSERT INTO koju VALUES (1);
+ ERROR:  duplicate key violates unique constraint "koju_a_key"
+     rollback to x;
+     CREATE TABLE koju (a INT UNIQUE);
+ NOTICE:  CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju"
+     INSERT INTO koju VALUES (1);
+     INSERT INTO koju VALUES (1);
+ ERROR:  duplicate key violates unique constraint "koju_a_key"
+ ROLLBACK;
  DROP TABLE foo;
  DROP TABLE baz;
  DROP TABLE barbaz;
*** src/test/regress/sql/transactions.sql.orig    Thu Aug 12 14:25:44 2004
--- src/test/regress/sql/transactions.sql    Mon Sep  6 13:44:08 2004
***************
*** 231,236 ****
--- 231,249 ----
      FETCH 10 FROM c;
  COMMIT;

+ -- test case for problems with dropping an open relation during abort
+ BEGIN;
+     savepoint x;
+         CREATE TABLE koju (a INT UNIQUE);
+         INSERT INTO koju VALUES (1);
+         INSERT INTO koju VALUES (1);
+     rollback to x;
+
+     CREATE TABLE koju (a INT UNIQUE);
+     INSERT INTO koju VALUES (1);
+     INSERT INTO koju VALUES (1);
+ ROLLBACK;
+
  DROP TABLE foo;
  DROP TABLE baz;
  DROP TABLE barbaz;