On Sat, Feb 25, 2012 at 6:58 PM, Simon Riggs <si...@2ndquadrant.com> wrote:
> On Sat, Feb 25, 2012 at 6:24 PM, Kevin Grittner
> <kevin.gritt...@wicourts.gov> wrote:
>> Simon Riggs <si...@2ndquadrant.com> wrote:
>>
>>> This patch extends that and actually sets the tuple header flag as
>>> HEAP_XMIN_COMMITTED during the load.
>>
>> Fantastic!
> I think we could add that as an option on COPY, since "breaking MVCC"
> in that way is only a bad thing if it happens accidentally without the
> user's permission and knowledge - which they may wish to give in many
> cases, such as reloading a database from a dump.
I've coded up COPY FREEZE to do just that.
This version doesn't require any changes to transaction machinery.
But it is very effective at avoiding 4 out of the 5 writes you mention.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index a73b022..4912449 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -34,6 +34,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
FORMAT <replaceable class="parameter">format_name</replaceable>
OIDS [ <replaceable class="parameter">boolean</replaceable> ]
+ FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
DELIMITER '<replaceable class="parameter">delimiter_character</replaceable>'
NULL '<replaceable class="parameter">null_string</replaceable>'
HEADER [ <replaceable class="parameter">boolean</replaceable> ]
@@ -181,6 +182,23 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
</varlistentry>
<varlistentry>
+ <term><literal>FREEZE</literal></term>
+ <listitem>
+ <para>
+ Specifies copying the data with rows already frozen, just as they
+ would be after running the <command>VACUUM FREEZE</> command.
+ This only occurs if the table being loaded has been created in this
+ subtransaction, there are no cursors open and there are no older
+ snapshots held by this transaction.
+ Note that all sessions will immediately be able to see the data
+ if it has been successfully loaded, which specifically operates
+ outside of the normal definitions of MVCC. This is a performance
+ option intended for use only during initial data loads.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>DELIMITER</literal></term>
<listitem>
<para>
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index c910863..68534bf 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -2050,7 +2050,13 @@ heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid,
tup->t_data->t_infomask &= ~(HEAP_XACT_MASK);
tup->t_data->t_infomask2 &= ~(HEAP2_XACT_MASK);
tup->t_data->t_infomask |= HEAP_XMAX_INVALID;
- HeapTupleHeaderSetXmin(tup->t_data, xid);
+ if (options & HEAP_INSERT_FREEZE)
+ {
+ HeapTupleHeaderSetXmin(tup->t_data, FrozenTransactionId);
+ tup->t_data->t_infomask |= HEAP_XMIN_COMMITTED;
+ }
+ else
+ HeapTupleHeaderSetXmin(tup->t_data, xid);
HeapTupleHeaderSetCmin(tup->t_data, cid);
HeapTupleHeaderSetXmax(tup->t_data, 0); /* for cleanliness */
tup->t_tableOid = RelationGetRelid(relation);
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 110480f..ec0bed8 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -43,6 +43,7 @@
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/portal.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
@@ -108,6 +109,7 @@ typedef struct CopyStateData
char *filename; /* filename, or NULL for STDIN/STDOUT */
bool binary; /* binary format? */
bool oids; /* include OIDs? */
+ bool freeze; /* freeze rows on loading? */
bool csv_mode; /* Comma Separated Value format? */
bool header_line; /* CSV header line? */
char *null_print; /* NULL marker string (server encoding!) */
@@ -889,6 +891,14 @@ ProcessCopyOptions(CopyState cstate,
errmsg("conflicting or redundant options")));
cstate->oids = defGetBoolean(defel);
}
+ else if (strcmp(defel->defname, "freeze") == 0)
+ {
+ if (cstate->freeze)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ cstate->freeze = defGetBoolean(defel);
+ }
else if (strcmp(defel->defname, "delimiter") == 0)
{
if (cstate->delim)
@@ -1922,6 +1932,11 @@ CopyFrom(CopyState cstate)
hi_options |= HEAP_INSERT_SKIP_FSM;
if (!XLogIsNeeded())
hi_options |= HEAP_INSERT_SKIP_WAL;
+
+ if (cstate->freeze &&
+ ThereAreNoPriorRegisteredSnapshots() &&
+ ThereAreNoReadyPortals())
+ hi_options |= HEAP_INSERT_FREEZE;
}
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d1ce2ab..cd2b243 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2315,6 +2315,10 @@ copy_opt_item:
{
$$ = makeDefElem("oids", (Node *)makeInteger(TRUE));
}
+ | FREEZE
+ {
+ $$ = makeDefElem("freeze", (Node *)makeInteger(TRUE));
+ }
| DELIMITER opt_as Sconst
{
$$ = makeDefElem("delimiter", (Node *)makeString($3));
diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index cfb73c1..24075db 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -1055,3 +1055,22 @@ pg_cursor(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+
+bool
+ThereAreNoReadyPortals(void)
+{
+ HASH_SEQ_STATUS status;
+ PortalHashEnt *hentry;
+
+ hash_seq_init(&status, PortalHashTable);
+
+ while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL)
+ {
+ Portal portal = hentry->portal;
+
+ if (portal->status == PORTAL_READY)
+ return false;
+ }
+
+ return true;
+}
diff --git a/src/backend/utils/time/snapmgr.c b/src/backend/utils/time/snapmgr.c
index 5aebbd1..5d9e3bf 100644
--- a/src/backend/utils/time/snapmgr.c
+++ b/src/backend/utils/time/snapmgr.c
@@ -1183,3 +1183,12 @@ DeleteAllExportedSnapshotFiles(void)
FreeDir(s_dir);
}
+
+bool
+ThereAreNoPriorRegisteredSnapshots(void)
+{
+ if (RegisteredSnapshots <= 1)
+ return true;
+
+ return false;
+}
diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h
index fa38803..61472f2 100644
--- a/src/include/access/heapam.h
+++ b/src/include/access/heapam.h
@@ -26,6 +26,7 @@
/* "options" flag bits for heap_insert */
#define HEAP_INSERT_SKIP_WAL 0x0001
#define HEAP_INSERT_SKIP_FSM 0x0002
+#define HEAP_INSERT_FREEZE 0x0004
typedef struct BulkInsertStateData *BulkInsertState;
diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h
index 4833942..bd2b133 100644
--- a/src/include/utils/portal.h
+++ b/src/include/utils/portal.h
@@ -219,5 +219,6 @@ extern void PortalDefineQuery(Portal portal,
extern Node *PortalListGetPrimaryStmt(List *stmts);
extern void PortalCreateHoldStore(Portal portal);
extern void PortalHashTableDeleteAll(void);
+extern bool ThereAreNoReadyPortals(void);
#endif /* PORTAL_H */
diff --git a/src/include/utils/snapmgr.h b/src/include/utils/snapmgr.h
index f195981..789b72e 100644
--- a/src/include/utils/snapmgr.h
+++ b/src/include/utils/snapmgr.h
@@ -46,5 +46,6 @@ extern Datum pg_export_snapshot(PG_FUNCTION_ARGS);
extern void ImportSnapshot(const char *idstr);
extern bool XactHasExportedSnapshots(void);
extern void DeleteAllExportedSnapshotFiles(void);
+extern bool ThereAreNoPriorRegisteredSnapshots(void);
#endif /* SNAPMGR_H */
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 8e2bc0c..ad2c24c 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -239,6 +239,53 @@ a\.
\.b
c\.d
"\."
+CREATE TABLE vistest (LIKE testeoc);
+BEGIN;
+TRUNCATE vistest;
+COPY vistest FROM stdin CSV;
+SELECT * FROM vistest;
+ a
+---
+ a
+ b
+ c
+(3 rows)
+
+SAVEPOINT s1;
+TRUNCATE vistest;
+COPY vistest FROM stdin CSV;
+SELECT * FROM vistest;
+ a
+---
+ d
+ e
+ f
+(3 rows)
+
+COMMIT;
+BEGIN;
+TRUNCATE vistest;
+COPY vistest FROM stdin CSV FREEZE;
+SELECT * FROM vistest;
+ a
+---
+ a
+ b
+ c
+(3 rows)
+
+SAVEPOINT s1;
+TRUNCATE vistest;
+COPY vistest FROM stdin CSV FREEZE;
+SELECT * FROM vistest;
+ a
+---
+ d
+ e
+ f
+(3 rows)
+
+COMMIT;
DROP TABLE x, y;
DROP FUNCTION fn_x_before();
DROP FUNCTION fn_x_after();
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 6322c8f..4da6452 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -164,6 +164,43 @@ c\.d
COPY testeoc TO stdout CSV;
+CREATE TABLE vistest (LIKE testeoc);
+BEGIN;
+TRUNCATE vistest;
+COPY vistest FROM stdin CSV;
+a
+b
+c
+\.
+SELECT * FROM vistest;
+SAVEPOINT s1;
+TRUNCATE vistest;
+COPY vistest FROM stdin CSV;
+d
+e
+f
+\.
+SELECT * FROM vistest;
+COMMIT;
+
+BEGIN;
+TRUNCATE vistest;
+COPY vistest FROM stdin CSV FREEZE;
+a
+b
+c
+\.
+SELECT * FROM vistest;
+SAVEPOINT s1;
+TRUNCATE vistest;
+COPY vistest FROM stdin CSV FREEZE;
+d
+e
+f
+\.
+SELECT * FROM vistest;
+COMMIT;
+
DROP TABLE x, y;
DROP FUNCTION fn_x_before();
DROP FUNCTION fn_x_after();
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
