On Wed, 2008-10-22 at 21:47 +0100, Simon Riggs wrote:
> But once you reach 64 transactions, you'll need to write an extra WAL
> record for every subtransaction, which currently I've managed to avoid.
Some further notes/tests on the optimisation you discovered.
Because of the way I have changed tqual.c, we only need to write to
subtrans if a proc's subxid cache overflows. (I think we would need to
change tqual.c in a similar way in any of the ways so far discussed).
Anyway, quick test with a representative test case shows 3-5%
performance gain from skipping the subtrans updates. I only the test
case files here and the test patch. The test is a simple PL/pgSQL
function with one EXCEPTION clause, so fairly real world.
The patch isn't ready to apply standalone because we need to include the
changes to XidInMVCCSnapshot() also, which would take a little while to
extract. Let me know if that is worth producing a standalone patch for.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
SELECT new_order();
CREATE TABLE orders
(ordid SERIAL NOT NULL PRIMARY KEY);
CREATE FUNCTION new_order() RETURNS VOID LANGUAGE PLPGSQL AS
$$
DECLARE
BEGIN
INSERT INTO orders DEFAULT VALUES;
EXCEPTION
WHEN unique_violation THEN
--do nothing
END;
$$;
Index: src/backend/access/transam/varsup.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/varsup.c,v
retrieving revision 1.81
diff -c -r1.81 varsup.c
*** src/backend/access/transam/varsup.c 1 Jan 2008 19:45:48 -0000 1.81
--- src/backend/access/transam/varsup.c 23 Oct 2008 04:36:04 -0000
***************
*** 36,44 ****
* The new XID is also stored into MyProc before returning.
*/
TransactionId
! GetNewTransactionId(bool isSubXact)
{
! TransactionId xid;
/*
* During bootstrap initialization, we return the special bootstrap
--- 36,45 ----
* The new XID is also stored into MyProc before returning.
*/
TransactionId
! GetNewTransactionId(TransactionId parentXid)
{
! TransactionId xid;
! bool isSubXact = TransactionIdIsValid(parentXid);
/*
* During bootstrap initialization, we return the special bootstrap
***************
*** 176,181 ****
--- 177,191 ----
LWLockRelease(XidGenLock);
+ /*
+ * NB: we must make the subtrans entry BEFORE the Xid appears anywhere in
+ * shared storage other than PG_PROC; because if there's no room for it in
+ * PG_PROC, the subtrans entry is needed to ensure that other backends see
+ * the Xid as "running".
+ */
+ if (isSubXact && MyProc->subxids.overflowed)
+ SubTransSetParent(xid, parentXid);
+
return xid;
}
Index: src/backend/access/transam/xact.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.266
diff -c -r1.266 xact.c
*** src/backend/access/transam/xact.c 20 Oct 2008 19:18:18 -0000 1.266
--- src/backend/access/transam/xact.c 23 Oct 2008 04:38:02 -0000
***************
*** 391,396 ****
--- 391,397 ----
{
bool isSubXact = (s->parent != NULL);
ResourceOwner currentOwner;
+ TransactionId parentXid = InvalidTransactionId;
/* Assert that caller didn't screw up */
Assert(!TransactionIdIsValid(s->transactionId));
***************
*** 404,420 ****
AssignTransactionId(s->parent);
/*
! * Generate a new Xid and record it in PG_PROC and pg_subtrans.
! *
! * NB: we must make the subtrans entry BEFORE the Xid appears anywhere in
! * shared storage other than PG_PROC; because if there's no room for it in
! * PG_PROC, the subtrans entry is needed to ensure that other backends see
! * the Xid as "running". See GetNewTransactionId.
*/
- s->transactionId = GetNewTransactionId(isSubXact);
-
if (isSubXact)
! SubTransSetParent(s->transactionId, s->parent->transactionId);
/*
* Acquire lock on the transaction XID. (We assume this cannot block.) We
--- 405,415 ----
AssignTransactionId(s->parent);
/*
! * Generate a new Xid and record it in PG_PROC and pg_subtrans, if required
*/
if (isSubXact)
! parentXid = s->parent->transactionId;
! s->transactionId = GetNewTransactionId(parentXid);
/*
* Acquire lock on the transaction XID. (We assume this cannot block.) We
Index: src/include/access/transam.h
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/include/access/transam.h,v
retrieving revision 1.66
diff -c -r1.66 transam.h
*** src/include/access/transam.h 20 Oct 2008 19:18:18 -0000 1.66
--- src/include/access/transam.h 23 Oct 2008 04:37:04 -0000
***************
*** 152,158 ****
extern XLogRecPtr TransactionIdGetCommitLSN(TransactionId xid);
/* in transam/varsup.c */
! extern TransactionId GetNewTransactionId(bool isSubXact);
extern TransactionId ReadNewTransactionId(void);
extern void SetTransactionIdLimit(TransactionId oldest_datfrozenxid,
Name oldest_datname);
--- 152,158 ----
extern XLogRecPtr TransactionIdGetCommitLSN(TransactionId xid);
/* in transam/varsup.c */
! extern TransactionId GetNewTransactionId(TransactionId parentXid);
extern TransactionId ReadNewTransactionId(void);
extern void SetTransactionIdLimit(TransactionId oldest_datfrozenxid,
Name oldest_datname);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
