On Sat, 2005-12-10 at 12:07 +0000, Simon Riggs wrote:
> Following patch implements COPY ... FROM ... LOCK
Patch now updated so that it includes an additional optimization of
COPY, so that WAL will not be written in the transaction that created
the table.
This now gives two fast paths for COPY:
1) COPY LOCK
2) COPY in same transaction (e.g. reloading a pg_dump)
Patch passes make check on cvstip.
No docs yet, but let me know if this is OK and I'll work on them.
[Other copied in from the related patch thread on Single-Transaction
Utility options. With this new COPY optimization the
--single-transaction option will considerably increase performance.]
Performance tests shown on previous post for this thread.
Best Regards, Simon Riggs
Index: src/backend/access/heap/heapam.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/heapam.c,v
retrieving revision 1.205
diff -c -r1.205 heapam.c
*** src/backend/access/heap/heapam.c 26 Nov 2005 05:03:06 -0000 1.205
--- src/backend/access/heap/heapam.c 18 Dec 2005 21:10:52 -0000
***************
*** 28,33 ****
--- 28,34 ----
* heap_update - replace a tuple in a relation with another tuple
* heap_markpos - mark scan position
* heap_restrpos - restore position to marked location
+ * heap_sync - sync heap, for when no WAL has been written
*
* NOTES
* This file contains the heap_ routines which implement
***************
*** 49,54 ****
--- 50,56 ----
#include "miscadmin.h"
#include "pgstat.h"
#include "storage/procarray.h"
+ #include "storage/smgr.h"
#include "utils/inval.h"
#include "utils/relcache.h"
***************
*** 1335,1342 ****
* non-temp relation. Safe usage of this behavior requires that we arrange
* that all new tuples go into new pages not containing any tuples from other
* transactions, that the relation gets fsync'd before commit, and that the
! * transaction emits at least one WAL record to ensure RecordTransactionCommit
! * will decide to WAL-log the commit.
*
* use_fsm is passed directly to RelationGetBufferForTuple, which see for
* more info.
--- 1337,1345 ----
* non-temp relation. Safe usage of this behavior requires that we arrange
* that all new tuples go into new pages not containing any tuples from other
* transactions, that the relation gets fsync'd before commit, and that the
! * transaction emits at least one WAL record or must mark
! * MyXactMustRecordCommitIfAny=true to ensure RecordTransactionCommit will
! * decide to WAL-log the commit. (see heap_sync() comments also)
*
* use_fsm is passed directly to RelationGetBufferForTuple, which see for
* more info.
***************
*** 1413,1419 ****
if (relation->rd_istemp)
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMadeTempRelUpdate = true;
}
else if (use_wal)
{
--- 1416,1422 ----
if (relation->rd_istemp)
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMustRecordCommitIfAny = true;
}
else if (use_wal)
{
***************
*** 1731,1737 ****
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMadeTempRelUpdate = true;
}
END_CRIT_SECTION();
--- 1734,1740 ----
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMustRecordCommitIfAny = true;
}
END_CRIT_SECTION();
***************
*** 2172,2178 ****
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMadeTempRelUpdate = true;
}
END_CRIT_SECTION();
--- 2175,2181 ----
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMustRecordCommitIfAny = true;
}
END_CRIT_SECTION();
***************
*** 2674,2680 ****
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMadeTempRelUpdate = true;
}
END_CRIT_SECTION();
--- 2677,2683 ----
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMustRecordCommitIfAny = true;
}
END_CRIT_SECTION();
***************
*** 3456,3458 ****
--- 3459,3490 ----
else
strcat(buf, "UNKNOWN");
}
+
+ /* ----------------
+ * heap_sync - sync a heap, for use when no WAL has been written
+ *
+ * ----------------
+ */
+ void
+ heap_sync(Relation rel, bool needToRecordCommit)
+ {
+ /*
+ * If our transaction has avoided writing WAL up to now,
+ * we need to force the changes to be written to disk then make
+ * commit record visible in clog, so other users can view these changes
+ */
+ if (needToRecordCommit)
+ MyXactMustRecordCommitIfAny = true;
+
+ if (!rel->rd_istemp)
+ {
+ /*
+ * If we skipped using WAL, and it's not a temp relation,
+ * we must force the relation down to disk before it's
+ * safe to commit the transaction. This requires forcing
+ * out any dirty buffers and then doing a forced fsync.
+ */
+ FlushRelationBuffers(rel);
+ smgrimmedsync(rel->rd_smgr);
+ }
+ }
Index: src/backend/access/transam/xact.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.216
diff -c -r1.216 xact.c
*** src/backend/access/transam/xact.c 22 Nov 2005 18:17:07 -0000 1.216
--- src/backend/access/transam/xact.c 18 Dec 2005 21:10:55 -0000
***************
*** 669,675 ****
* all. (This test includes the effects of subtransactions, so the
* presence of committed subxacts need not alone force a write.)
*/
! if (MyXactMadeXLogEntry || MyXactMadeTempRelUpdate || nrels > 0)
{
TransactionId xid = GetCurrentTransactionId();
bool madeTCentries;
--- 669,675 ----
* all. (This test includes the effects of subtransactions, so the
* presence of committed subxacts need not alone force a write.)
*/
! if (MyXactMadeXLogEntry || MyXactMustRecordCommitIfAny || nrels > 0)
{
TransactionId xid = GetCurrentTransactionId();
bool madeTCentries;
***************
*** 782,788 ****
* crash the clog update might be lost. This is okay because no one
* else will ever care whether we committed.
*/
! if (madeTCentries || MyXactMadeTempRelUpdate)
{
TransactionIdCommit(xid);
/* to avoid race conditions, the parent must commit first */
--- 782,788 ----
* crash the clog update might be lost. This is okay because no one
* else will ever care whether we committed.
*/
! if (madeTCentries || MyXactMustRecordCommitIfAny)
{
TransactionIdCommit(xid);
/* to avoid race conditions, the parent must commit first */
***************
*** 799,805 ****
/* Break the chain of back-links in the XLOG records I output */
MyLastRecPtr.xrecoff = 0;
MyXactMadeXLogEntry = false;
! MyXactMadeTempRelUpdate = false;
/* And clean up local data */
if (rels)
--- 799,805 ----
/* Break the chain of back-links in the XLOG records I output */
MyLastRecPtr.xrecoff = 0;
MyXactMadeXLogEntry = false;
! MyXactMustRecordCommitIfAny = false;
/* And clean up local data */
if (rels)
***************
*** 933,939 ****
* dirtied these variables, then RecordTransactionCommit will have to do
* the full pushup anyway...)
*/
! if (MyLastRecPtr.xrecoff != 0 || MyXactMadeTempRelUpdate)
{
TransactionId xid = GetCurrentTransactionId();
--- 933,939 ----
* dirtied these variables, then RecordTransactionCommit will have to do
* the full pushup anyway...)
*/
! if (MyLastRecPtr.xrecoff != 0 || MyXactMustRecordCommitIfAny)
{
TransactionId xid = GetCurrentTransactionId();
***************
*** 973,979 ****
* recording the transaction abort at all. No one will ever care that it
* aborted. (These tests cover our whole transaction tree.)
*/
! if (MyLastRecPtr.xrecoff != 0 || MyXactMadeTempRelUpdate || nrels > 0)
{
TransactionId xid = GetCurrentTransactionId();
--- 973,979 ----
* recording the transaction abort at all. No one will ever care that it
* aborted. (These tests cover our whole transaction tree.)
*/
! if (MyLastRecPtr.xrecoff != 0 || MyXactMustRecordCommitIfAny || nrels > 0)
{
TransactionId xid = GetCurrentTransactionId();
***************
*** 1056,1062 ****
/* Break the chain of back-links in the XLOG records I output */
MyLastRecPtr.xrecoff = 0;
MyXactMadeXLogEntry = false;
! MyXactMadeTempRelUpdate = false;
/* And clean up local data */
if (rels)
--- 1056,1062 ----
/* Break the chain of back-links in the XLOG records I output */
MyLastRecPtr.xrecoff = 0;
MyXactMadeXLogEntry = false;
! MyXactMustRecordCommitIfAny = false;
/* And clean up local data */
if (rels)
***************
*** 1173,1179 ****
* aborted, because they didn't mark themselves as subcommitted in the
* first place; see the optimization in RecordSubTransactionCommit.
*/
! if (MyLastRecPtr.xrecoff != 0 || MyXactMadeTempRelUpdate || nrels > 0)
{
START_CRIT_SECTION();
--- 1173,1179 ----
* aborted, because they didn't mark themselves as subcommitted in the
* first place; see the optimization in RecordSubTransactionCommit.
*/
! if (MyLastRecPtr.xrecoff != 0 || MyXactMustRecordCommitIfAny || nrels > 0)
{
START_CRIT_SECTION();
***************
*** 1728,1734 ****
/* Break the chain of back-links in the XLOG records I output */
MyLastRecPtr.xrecoff = 0;
MyXactMadeXLogEntry = false;
! MyXactMadeTempRelUpdate = false;
/*
* Let others know about no transaction in progress by me. This has to be
--- 1728,1734 ----
/* Break the chain of back-links in the XLOG records I output */
MyLastRecPtr.xrecoff = 0;
MyXactMadeXLogEntry = false;
! MyXactMustRecordCommitIfAny = false;
/*
* Let others know about no transaction in progress by me. This has to be
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.223
diff -c -r1.223 xlog.c
*** src/backend/access/transam/xlog.c 22 Nov 2005 18:17:07 -0000 1.223
--- src/backend/access/transam/xlog.c 18 Dec 2005 21:11:01 -0000
***************
*** 232,245 ****
* make any XLOG record, since we don't care about recovering the state of
* the temp rel after a crash. However, we will still need to remember
* whether our transaction committed or aborted in that case. So, we must
! * set MyXactMadeTempRelUpdate true to indicate that the XID will be of
* interest later.
*/
XLogRecPtr MyLastRecPtr = {0, 0};
bool MyXactMadeXLogEntry = false;
! bool MyXactMadeTempRelUpdate = false;
/*
* ProcLastRecPtr points to the start of the last XLOG record inserted by the
--- 232,245 ----
* make any XLOG record, since we don't care about recovering the state of
* the temp rel after a crash. However, we will still need to remember
* whether our transaction committed or aborted in that case. So, we must
! * set MyXactMustRecordCommitIfAny true to indicate that the XID will be of
* interest later.
*/
XLogRecPtr MyLastRecPtr = {0, 0};
bool MyXactMadeXLogEntry = false;
! bool MyXactMustRecordCommitIfAny = false;
/*
* ProcLastRecPtr points to the start of the last XLOG record inserted by the
Index: src/backend/commands/copy.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.255
diff -c -r1.255 copy.c
*** src/backend/commands/copy.c 22 Nov 2005 18:17:08 -0000 1.255
--- src/backend/commands/copy.c 18 Dec 2005 21:11:04 -0000
***************
*** 110,115 ****
--- 110,116 ----
bool oids; /* include OIDs? */
bool csv_mode; /* Comma Separated Value format? */
bool header_line; /* CSV header line? */
+ bool useLock; /* use LOCK? */
char *null_print; /* NULL marker string (server encoding!) */
int null_print_len; /* length of same */
char *delim; /* column delimiter (must be 1 byte) */
***************
*** 654,659 ****
--- 655,661 ----
char *filename = stmt->filename;
bool is_from = stmt->is_from;
bool pipe = (stmt->filename == NULL);
+ LOCKMODE lockMode;
List *attnamelist = stmt->attlist;
List *force_quote = NIL;
List *force_notnull = NIL;
***************
*** 685,690 ****
--- 687,700 ----
errmsg("conflicting or redundant options")));
cstate->oids = intVal(defel->arg);
}
+ else if (strcmp(defel->defname, "lock") == 0)
+ {
+ if (cstate->useLock)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ cstate->useLock = intVal(defel->arg);
+ }
else if (strcmp(defel->defname, "delimiter") == 0)
{
if (cstate->delim)
***************
*** 854,861 ****
errmsg("CSV quote character must not appear in the NULL specification")));
/* Open and lock the relation, using the appropriate lock type. */
! cstate->rel = heap_openrv(relation,
! (is_from ? RowExclusiveLock : AccessShareLock));
/* check read-only transaction */
if (XactReadOnly && is_from &&
--- 864,879 ----
errmsg("CSV quote character must not appear in the NULL specification")));
/* Open and lock the relation, using the appropriate lock type. */
! if (is_from)
! {
! if (cstate->useLock)
! lockMode = ExclusiveLock;
! else
! lockMode = RowExclusiveLock;
! }
! else
! lockMode = AccessShareLock;
! cstate->rel = heap_openrv(relation, lockMode);
/* check read-only transaction */
if (XactReadOnly && is_from &&
***************
*** 1465,1470 ****
--- 1483,1489 ----
ExprContext *econtext; /* used for ExecEvalExpr for default atts */
MemoryContext oldcontext = CurrentMemoryContext;
ErrorContextCallback errcontext;
+ bool use_wal = true; /* By default, we use WAL to log db changes */
tupDesc = RelationGetDescr(cstate->rel);
attr = tupDesc->attrs;
***************
*** 1647,1652 ****
--- 1666,1692 ----
econtext->ecxt_param_exec_vals = (ParamExecData *)
palloc0(sizeof(ParamExecData));
+ /*
+ * Skip WAL-logging?
+ * Basic pre-conditions are
+ * - no indexes
+ * - archive logging must not be enabled
+ * If basic pre-conditions met, then test for either
+ * - use of LOCK command
+ * - created in same transaction
+ * As mentioned in comments in utils/rel.h, the latter test is not
+ * completely reliable, since rd_createSubId can be reset to zero in
+ * certain cases before the end of the creating transaction.
+ * We are doing this for performance only, so we only need to know:
+ * if rd_createSubid != InvalidSubTransactionId then it is always just
+ * created.
+ */
+ if (resultRelInfo->ri_NumIndices == 0 &&
+ !XLogArchivingActive() &&
+ (cstate->rel->rd_createSubid != InvalidSubTransactionId ||
+ cstate->useLock))
+ use_wal = false;
+
/* Initialize state variables */
cstate->fe_eof = false;
cstate->eol_type = EOL_UNKNOWN;
***************
*** 1909,1915 ****
ExecConstraints(resultRelInfo, slot, estate);
/* OK, store the tuple and create index entries for it */
! simple_heap_insert(cstate->rel, tuple);
if (resultRelInfo->ri_NumIndices > 0)
ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false);
--- 1949,1955 ----
ExecConstraints(resultRelInfo, slot, estate);
/* OK, store the tuple and create index entries for it */
! heap_insert(cstate->rel, tuple, GetCurrentCommandId(), use_wal, use_wal);
if (resultRelInfo->ri_NumIndices > 0)
ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false);
***************
*** 1919,1924 ****
--- 1959,1970 ----
}
}
+ /*
+ * If we used LOCK and thereby skipped writing WAL, then we need to sync
+ */
+ if (!use_wal)
+ heap_sync(cstate->rel, true);
+
/* Done, clean up */
error_context_stack = errcontext.previous;
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.319
diff -c -r1.319 vacuum.c
*** src/backend/commands/vacuum.c 22 Nov 2005 18:17:09 -0000 1.319
--- src/backend/commands/vacuum.c 18 Dec 2005 21:11:07 -0000
***************
*** 2454,2460 ****
* No XLOG record, but still need to flag that XID exists on
* disk
*/
! MyXactMadeTempRelUpdate = true;
}
END_CRIT_SECTION();
--- 2454,2460 ----
* No XLOG record, but still need to flag that XID exists on
* disk
*/
! MyXactMustRecordCommitIfAny = true;
}
END_CRIT_SECTION();
***************
*** 2610,2616 ****
/*
* No XLOG record, but still need to flag that XID exists on disk
*/
! MyXactMadeTempRelUpdate = true;
}
END_CRIT_SECTION();
--- 2610,2616 ----
/*
* No XLOG record, but still need to flag that XID exists on disk
*/
! MyXactMustRecordCommitIfAny = true;
}
END_CRIT_SECTION();
***************
*** 2714,2720 ****
/*
* No XLOG record, but still need to flag that XID exists on disk
*/
! MyXactMadeTempRelUpdate = true;
}
END_CRIT_SECTION();
--- 2714,2720 ----
/*
* No XLOG record, but still need to flag that XID exists on disk
*/
! MyXactMustRecordCommitIfAny = true;
}
END_CRIT_SECTION();
***************
*** 2914,2920 ****
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMadeTempRelUpdate = true;
}
END_CRIT_SECTION();
--- 2914,2920 ----
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMustRecordCommitIfAny = true;
}
END_CRIT_SECTION();
Index: src/backend/commands/vacuumlazy.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.63
diff -c -r1.63 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c 22 Nov 2005 18:17:09 -0000 1.63
--- src/backend/commands/vacuumlazy.c 18 Dec 2005 21:11:09 -0000
***************
*** 583,589 ****
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMadeTempRelUpdate = true;
}
END_CRIT_SECTION();
--- 583,589 ----
else
{
/* No XLOG record, but still need to flag that XID exists on disk */
! MyXactMustRecordCommitIfAny = true;
}
END_CRIT_SECTION();
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.262
diff -c -r1.262 execMain.c
*** src/backend/executor/execMain.c 2 Dec 2005 20:03:40 -0000 1.262
--- src/backend/executor/execMain.c 18 Dec 2005 21:11:11 -0000
***************
*** 996,1009 ****
* If we skipped using WAL, and it's not a temp relation, we must
* force the relation down to disk before it's safe to commit the
* transaction. This requires forcing out any dirty buffers and then
! * doing a forced fsync.
*/
! if (!estate->es_into_relation_use_wal &&
! !estate->es_into_relation_descriptor->rd_istemp)
! {
! FlushRelationBuffers(estate->es_into_relation_descriptor);
! smgrimmedsync(estate->es_into_relation_descriptor->rd_smgr);
! }
heap_close(estate->es_into_relation_descriptor, NoLock);
}
--- 996,1005 ----
* If we skipped using WAL, and it's not a temp relation, we must
* force the relation down to disk before it's safe to commit the
* transaction. This requires forcing out any dirty buffers and then
! * doing a forced fsync. We've written WAL for the CREATE TABLE
! * however, so no need to force writing the commit.
*/
! heap_sync(estate->es_into_relation_descriptor, false);
heap_close(estate->es_into_relation_descriptor, NoLock);
}
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.517
diff -c -r2.517 gram.y
*** src/backend/parser/gram.y 11 Dec 2005 10:54:27 -0000 2.517
--- src/backend/parser/gram.y 18 Dec 2005 21:11:18 -0000
***************
*** 1635,1640 ****
--- 1635,1644 ----
{
$$ = makeDefElem("force_notnull", (Node *)$4);
}
+ | LOCK_P
+ {
+ $$ = makeDefElem("lock", (Node *)makeInteger(TRUE));
+ }
;
/* The following exist for backward compatibility */
Index: src/include/access/heapam.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/heapam.h,v
retrieving revision 1.105
diff -c -r1.105 heapam.h
*** src/include/access/heapam.h 15 Oct 2005 02:49:42 -0000 1.105
--- src/include/access/heapam.h 18 Dec 2005 21:11:24 -0000
***************
*** 220,223 ****
--- 220,225 ----
extern HeapTuple heap_addheader(int natts, bool withoid,
Size structlen, void *structure);
+ extern void heap_sync(Relation relation, bool writeXLog);
+
#endif /* HEAPAM_H */
Index: src/include/access/xlog.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/xlog.h,v
retrieving revision 1.69
diff -c -r1.69 xlog.h
*** src/include/access/xlog.h 15 Oct 2005 02:49:42 -0000 1.69
--- src/include/access/xlog.h 18 Dec 2005 21:11:25 -0000
***************
*** 132,138 ****
extern bool InRecovery;
extern XLogRecPtr MyLastRecPtr;
extern bool MyXactMadeXLogEntry;
! extern bool MyXactMadeTempRelUpdate;
extern XLogRecPtr ProcLastRecEnd;
/* these variables are GUC parameters related to XLOG */
--- 132,138 ----
extern bool InRecovery;
extern XLogRecPtr MyLastRecPtr;
extern bool MyXactMadeXLogEntry;
! extern bool MyXactMustRecordCommitIfAny;
extern XLogRecPtr ProcLastRecEnd;
/* these variables are GUC parameters related to XLOG */
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
