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 (AT) fast (DOT) 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 ordinary user 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 this email 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 email unsubscribe (AT) fast (DOT) fujitsu.com.au



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

--IS0zKkzwUGydFO0o
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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

Hi,

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)

--IS0zKkzwUGydFO0o
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="smgr.patch"

Index: src/backend/access/transam/xact.c
================================================== =================
RCS file: /home/alvherre/cvs/pgsql-server/src/backend/access/transam/xact.c,v
retrieving revision 1.185
diff -c -r1.185 xact.c
*** src/backend/access/transam/xact.c 30 Aug 2004 19:00:03 -0000 1.185
--- src/backend/access/transam/xact.c 6 Sep 2004 12:57:25 -0000
***************
*** 1481,1494 ****
* 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,
--- 1481,1494 ----
* ordering.
*/


CallXactCallbacks(XACT_EVENT_ABORT, InvalidTransactionId);

ResourceOwnerRelease(TopTransactionResourceOwner,
RESOURCE_RELEASE_BEFORE_LOCKS,
false, true);
+ smgrDoPendingDeletes(false);
+ smgrabort();
AtEOXact_Inval(false);
ResourceOwnerRelease(TopTransactionResourceOwner,
RESOURCE_RELEASE_LOCKS,
Index: src/test/regress/expected/transactions.out
================================================== =================
RCS file: /home/alvherre/cvs/pgsql-server/src/test/regress/expected/transactions.out,v
retrieving revision 1.8
diff -c -r1.8 transactions.out
*** src/test/regress/expected/transactions.out 12 Aug 2004 19:12:21 -0000 1.8
--- src/test/regress/expected/transactions.out 6 Sep 2004 13:08:27 -0000
***************
*** 374,379 ****
--- 374,387 ----
FETCH 10 FROM c;
ERROR: portal "c" cannot be run
COMMIT;
+ -- make sure dropping an index that's being scanned works
+ BEGIN;
+ 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;
Index: src/test/regress/sql/transactions.sql
================================================== =================
RCS file: /home/alvherre/cvs/pgsql-server/src/test/regress/sql/transactions.sql,v
retrieving revision 1.8
diff -c -r1.8 transactions.sql
*** src/test/regress/sql/transactions.sql 12 Aug 2004 19:12:21 -0000 1.8
--- src/test/regress/sql/transactions.sql 6 Sep 2004 13:05:30 -0000
***************
*** 231,236 ****
--- 231,243 ----
FETCH 10 FROM c;
COMMIT;

+ -- make sure dropping an index that's being scanned works
+ BEGIN;
+ 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;

--IS0zKkzwUGydFO0o
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

--IS0zKkzwUGydFO0o--

Alvaro Herrera <alvherre (AT) dcc (DOT) uchile.cl> writes:
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;

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org