Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8de72b6

Browse files
COPY FREEZE and mark committed on fresh tables.
When a relfilenode is created in this subtransaction ora committed child transaction and it cannot otherwisebe seen by our own process, mark tuples committed aheadof transaction commit for all COPY commands in sametransaction. If FREEZE specified on COPYand pre-conditions met then rows will also be frozen.Both options designed to avoid revisiting rows after commit,increasing performance of subsequent commands afterdata load and upgrade. pg_restore changes later.Simon Riggs, review comments from Heikki Linnakangas, Noah Misch and designinput from Tom Lane, Robert Haas and Kevin Grittner
1 parent44c03ef commit8de72b6

File tree

11 files changed

+292
-1
lines changed

11 files changed

+292
-1
lines changed

‎doc/src/sgml/ref/copy.sgml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
3434

3535
FORMAT <replaceable class="parameter">format_name</replaceable>
3636
OIDS [ <replaceable class="parameter">boolean</replaceable> ]
37+
FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
3738
DELIMITER '<replaceable class="parameter">delimiter_character</replaceable>'
3839
NULL '<replaceable class="parameter">null_string</replaceable>'
3940
HEADER [ <replaceable class="parameter">boolean</replaceable> ]
@@ -181,6 +182,28 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
181182
</listitem>
182183
</varlistentry>
183184

185+
<varlistentry>
186+
<term><literal>FREEZE</literal></term>
187+
<listitem>
188+
<para>
189+
Specifies copying the data with rows already frozen, just as they
190+
would be after running the <command>VACUUM FREEZE</> command.
191+
This is intended as a performance option for initial data loading.
192+
Rows will be frozen only if the table being loaded has been created
193+
in the current subtransaction, there are no cursors open and there
194+
are no older snapshots held by this transaction. If those conditions
195+
are not met the command will continue without error though will not
196+
freeze rows.
197+
</para>
198+
<para>
199+
Note that all sessions will immediately be able to see the data
200+
once it has been successfully loaded. This violates the normal rules
201+
of MVCC visibility and by specifying this option the user acknowledges
202+
explicitly that this is understood.
203+
</para>
204+
</listitem>
205+
</varlistentry>
206+
184207
<varlistentry>
185208
<term><literal>DELIMITER</literal></term>
186209
<listitem>

‎src/backend/access/heap/heapam.c

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1875,6 +1875,14 @@ FreeBulkInsertState(BulkInsertState bistate)
18751875
* The HEAP_INSERT_SKIP_FSM option is passed directly to
18761876
* RelationGetBufferForTuple, which see for more info.
18771877
*
1878+
* HEAP_INSERT_COMMITTED should only be specified for inserts into
1879+
* relfilenodes created during the current subtransaction and when
1880+
* there are no prior snapshots or pre-existing portals open.
1881+
*
1882+
* HEAP_INSERT_FROZEN only has meaning when HEAP_INSERT_COMMITTED is
1883+
* also set. This causes rows to be frozen, which is an MVCC violation
1884+
* and requires explicit options chosen by user.
1885+
*
18781886
* Note that these options will be applied when inserting into the heap's
18791887
* TOAST table, too, if the tuple requires any out-of-line data.
18801888
*
@@ -2078,7 +2086,14 @@ heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid,
20782086
tup->t_data->t_infomask &= ~(HEAP_XACT_MASK);
20792087
tup->t_data->t_infomask2 &= ~(HEAP2_XACT_MASK);
20802088
tup->t_data->t_infomask |=HEAP_XMAX_INVALID;
2081-
HeapTupleHeaderSetXmin(tup->t_data,xid);
2089+
if (options&HEAP_INSERT_COMMITTED)
2090+
{
2091+
tup->t_data->t_infomask |=HEAP_XMIN_COMMITTED;
2092+
if (options&HEAP_INSERT_FROZEN)
2093+
HeapTupleHeaderSetXmin(tup->t_data,FrozenTransactionId);
2094+
}
2095+
else
2096+
HeapTupleHeaderSetXmin(tup->t_data,xid);
20822097
HeapTupleHeaderSetCmin(tup->t_data,cid);
20832098
HeapTupleHeaderSetXmax(tup->t_data,0);/* for cleanliness */
20842099
tup->t_tableOid=RelationGetRelid(relation);

‎src/backend/commands/copy.c

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,7 @@
4444
#include"utils/builtins.h"
4545
#include"utils/lsyscache.h"
4646
#include"utils/memutils.h"
47+
#include"utils/portal.h"
4748
#include"utils/rel.h"
4849
#include"utils/snapmgr.h"
4950

@@ -109,6 +110,7 @@ typedef struct CopyStateData
109110
char*filename;/* filename, or NULL for STDIN/STDOUT */
110111
boolbinary;/* binary format? */
111112
booloids;/* include OIDs? */
113+
boolfreeze;/* freeze rows on loading? */
112114
boolcsv_mode;/* Comma Separated Value format? */
113115
boolheader_line;/* CSV header line? */
114116
char*null_print;/* NULL marker string (server encoding!) */
@@ -895,6 +897,14 @@ ProcessCopyOptions(CopyState cstate,
895897
errmsg("conflicting or redundant options")));
896898
cstate->oids=defGetBoolean(defel);
897899
}
900+
elseif (strcmp(defel->defname,"freeze")==0)
901+
{
902+
if (cstate->freeze)
903+
ereport(ERROR,
904+
(errcode(ERRCODE_SYNTAX_ERROR),
905+
errmsg("conflicting or redundant options")));
906+
cstate->freeze=defGetBoolean(defel);
907+
}
898908
elseif (strcmp(defel->defname,"delimiter")==0)
899909
{
900910
if (cstate->delim)
@@ -1974,8 +1984,31 @@ CopyFrom(CopyState cstate)
19741984
hi_options |=HEAP_INSERT_SKIP_FSM;
19751985
if (!XLogIsNeeded())
19761986
hi_options |=HEAP_INSERT_SKIP_WAL;
1987+
1988+
/*
1989+
* Optimize if new relfilenode was created in this subxact or
1990+
* one of its committed children and we won't see those rows later
1991+
* as part of an earlier scan or command. This ensures that if this
1992+
* subtransaction aborts then the frozen rows won't be visible
1993+
* after xact cleanup. Note that the stronger test of exactly
1994+
* which subtransaction created it is crucial for correctness
1995+
* of this optimisation.
1996+
*/
1997+
if (ThereAreNoPriorRegisteredSnapshots()&&
1998+
ThereAreNoReadyPortals()&&
1999+
cstate->rel->rd_newRelfilenodeSubid==GetCurrentSubTransactionId())
2000+
{
2001+
hi_options |=HEAP_INSERT_COMMITTED;
2002+
if (cstate->freeze)
2003+
hi_options |=HEAP_INSERT_FROZEN;
2004+
}
19772005
}
19782006

2007+
if (cstate->freeze&& (hi_options&HEAP_INSERT_FROZEN)==0)
2008+
ereport(NOTICE,
2009+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2010+
errmsg("FREEZE option specified but pre-conditions not met")));
2011+
19792012
/*
19802013
* We need a ResultRelInfo so we can use the regular executor's
19812014
* index-entry-making machinery. (There used to be a huge amount of code

‎src/backend/parser/gram.y

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2383,6 +2383,10 @@ copy_opt_item:
23832383
{
23842384
$$ = makeDefElem("oids", (Node *)makeInteger(TRUE));
23852385
}
2386+
|FREEZE
2387+
{
2388+
$$ = makeDefElem("freeze", (Node *)makeInteger(TRUE));
2389+
}
23862390
|DELIMITERopt_asSconst
23872391
{
23882392
$$ = makeDefElem("delimiter", (Node *)makeString($3));

‎src/backend/utils/mmgr/portalmem.c

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1055,3 +1055,22 @@ pg_cursor(PG_FUNCTION_ARGS)
10551055

10561056
return (Datum)0;
10571057
}
1058+
1059+
bool
1060+
ThereAreNoReadyPortals(void)
1061+
{
1062+
HASH_SEQ_STATUSstatus;
1063+
PortalHashEnt*hentry;
1064+
1065+
hash_seq_init(&status,PortalHashTable);
1066+
1067+
while ((hentry= (PortalHashEnt*)hash_seq_search(&status))!=NULL)
1068+
{
1069+
Portalportal=hentry->portal;
1070+
1071+
if (portal->status==PORTAL_READY)
1072+
return false;
1073+
}
1074+
1075+
return true;
1076+
}

‎src/backend/utils/time/snapmgr.c

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1184,3 +1184,12 @@ DeleteAllExportedSnapshotFiles(void)
11841184

11851185
FreeDir(s_dir);
11861186
}
1187+
1188+
bool
1189+
ThereAreNoPriorRegisteredSnapshots(void)
1190+
{
1191+
if (RegisteredSnapshots <=1)
1192+
return true;
1193+
1194+
return false;
1195+
}

‎src/include/access/heapam.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,8 @@
2626
/* "options" flag bits for heap_insert */
2727
#defineHEAP_INSERT_SKIP_WAL0x0001
2828
#defineHEAP_INSERT_SKIP_FSM0x0002
29+
#defineHEAP_INSERT_COMMITTED0x0004
30+
#defineHEAP_INSERT_FROZEN0x0008
2931

3032
typedefstructBulkInsertStateData*BulkInsertState;
3133

‎src/include/utils/portal.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -220,5 +220,6 @@ extern void PortalDefineQuery(Portal portal,
220220
externNode*PortalListGetPrimaryStmt(List*stmts);
221221
externvoidPortalCreateHoldStore(Portalportal);
222222
externvoidPortalHashTableDeleteAll(void);
223+
externboolThereAreNoReadyPortals(void);
223224

224225
#endif/* PORTAL_H */

‎src/include/utils/snapmgr.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,5 +48,6 @@ extern Datum pg_export_snapshot(PG_FUNCTION_ARGS);
4848
externvoidImportSnapshot(constchar*idstr);
4949
externboolXactHasExportedSnapshots(void);
5050
externvoidDeleteAllExportedSnapshotFiles(void);
51+
externboolThereAreNoPriorRegisteredSnapshots(void);
5152

5253
#endif/* SNAPMGR_H */

‎src/test/regress/expected/copy2.out

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -254,6 +254,112 @@ SELECT * FROM testnull;
254254
|
255255
(4 rows)
256256

257+
CREATE TABLE vistest (LIKE testeoc);
258+
BEGIN;
259+
TRUNCATE vistest;
260+
COPY vistest FROM stdin CSV;
261+
SELECT * FROM vistest;
262+
a
263+
---
264+
a
265+
b
266+
(2 rows)
267+
268+
SAVEPOINT s1;
269+
TRUNCATE vistest;
270+
COPY vistest FROM stdin CSV;
271+
SELECT * FROM vistest;
272+
a
273+
---
274+
d
275+
e
276+
(2 rows)
277+
278+
COMMIT;
279+
BEGIN;
280+
TRUNCATE vistest;
281+
COPY vistest FROM stdin CSV FREEZE;
282+
SELECT * FROM vistest;
283+
a
284+
---
285+
a
286+
b
287+
(2 rows)
288+
289+
SAVEPOINT s1;
290+
TRUNCATE vistest;
291+
COPY vistest FROM stdin CSV FREEZE;
292+
SELECT * FROM vistest;
293+
a
294+
---
295+
d
296+
e
297+
(2 rows)
298+
299+
COMMIT;
300+
TRUNCATE vistest;
301+
COPY vistest FROM stdin CSV FREEZE;
302+
NOTICE: FREEZE option specified but pre-conditions not met
303+
SELECT * FROM vistest;
304+
a
305+
---
306+
a
307+
b
308+
(2 rows)
309+
310+
BEGIN;
311+
INSERT INTO vistest VALUES ('z');
312+
SAVEPOINT s1;
313+
TRUNCATE vistest;
314+
ROLLBACK TO SAVEPOINT s1;
315+
-- FREEZE should be silently ignored here
316+
COPY vistest FROM stdin CSV FREEZE;
317+
NOTICE: FREEZE option specified but pre-conditions not met
318+
SELECT * FROM vistest;
319+
a
320+
---
321+
a
322+
b
323+
z
324+
d
325+
e
326+
(5 rows)
327+
328+
COMMIT;
329+
CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS
330+
$$
331+
BEGIN
332+
SELECT * FROM nonexistent;
333+
EXCEPTION
334+
WHEN OTHERS THEN
335+
TRUNCATE vistest;
336+
END;
337+
$$ language plpgsql;
338+
BEGIN;
339+
INSERT INTO vistest VALUES ('z');
340+
SELECT truncate_in_subxact();
341+
truncate_in_subxact
342+
---------------------
343+
344+
(1 row)
345+
346+
COPY vistest FROM stdin CSV FREEZE;
347+
SELECT * FROM vistest;
348+
a
349+
---
350+
d
351+
e
352+
(2 rows)
353+
354+
COMMIT;
355+
SELECT * FROM vistest;
356+
a
357+
---
358+
d
359+
e
360+
(2 rows)
361+
362+
DROP TABLE vistest;
257363
DROP TABLE x, y;
258364
DROP FUNCTION fn_x_before();
259365
DROP FUNCTION fn_x_after();

‎src/test/regress/sql/copy2.sql

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -179,6 +179,84 @@ COPY testnull FROM stdin WITH NULL AS E'\\0';
179179
SELECT*FROM testnull;
180180

181181

182+
CREATETABLEvistest (LIKE testeoc);
183+
BEGIN;
184+
TRUNCATE vistest;
185+
COPY vistestFROM stdin CSV;
186+
a
187+
b
188+
\.
189+
SELECT*FROM vistest;
190+
SAVEPOINT s1;
191+
TRUNCATE vistest;
192+
COPY vistestFROM stdin CSV;
193+
d
194+
e
195+
\.
196+
SELECT*FROM vistest;
197+
COMMIT;
198+
199+
BEGIN;
200+
TRUNCATE vistest;
201+
COPY vistestFROM stdin CSV FREEZE;
202+
a
203+
b
204+
\.
205+
SELECT*FROM vistest;
206+
SAVEPOINT s1;
207+
TRUNCATE vistest;
208+
COPY vistestFROM stdin CSV FREEZE;
209+
d
210+
e
211+
\.
212+
SELECT*FROM vistest;
213+
COMMIT;
214+
BEGIN;
215+
TRUNCATE vistest;
216+
COPY vistestFROM stdin CSV FREEZE;
217+
x
218+
y
219+
\.
220+
SELECT*FROM vistest;
221+
COMMIT;
222+
TRUNCATE vistest;
223+
COPY vistestFROM stdin CSV FREEZE;
224+
p
225+
g
226+
\.
227+
BEGIN;
228+
INSERT INTO vistestVALUES ('z');
229+
SAVEPOINT s1;
230+
TRUNCATE vistest;
231+
ROLLBACK TO SAVEPOINT s1;
232+
-- FREEZE should be silently ignored here
233+
COPY vistestFROM stdin CSV FREEZE;
234+
d
235+
e
236+
\.
237+
SELECT*FROM vistest;
238+
COMMIT;
239+
CREATEFUNCTIONtruncate_in_subxact() RETURNS VOIDAS
240+
$$
241+
BEGIN
242+
SELECT*FROM nonexistent;
243+
EXCEPTION
244+
WHEN OTHERS THEN
245+
TRUNCATE vistest;
246+
END;
247+
$$ language plpgsql;
248+
BEGIN;
249+
INSERT INTO vistestVALUES ('z');
250+
SELECT truncate_in_subxact();
251+
COPY vistestFROM stdin CSV FREEZE;
252+
d
253+
e
254+
\.
255+
SELECT*FROM vistest;
256+
COMMIT;
257+
SELECT*FROM vistest;
258+
DROPTABLE vistest;
259+
DROPFUNCTION truncate_in_subxact();
182260
DROPTABLE x, y;
183261
DROPFUNCTION fn_x_before();
184262
DROPFUNCTION fn_x_after();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp