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

Commitbf491a9

Browse files
committed
Disable WAL-skipping optimization for COPY on views and foreign tables
COPY can skip writing WAL when loading data on a table which has beencreated in the same transaction as the one loading the data, howeverthis cannot work on views or foreign table as this would result intrying to flush relation files which do not exist. So disable theoptimization so as commands are able to work the same way with anyconfiguration of wal_level.Tests are added to cover the different cases, which need to havewal_level set to minimal to allow the problem to show up, and that isnot the default configuration.Reported-by: Luis M. Carril, Etsuro FujitaAuthor: Amit Langote, Michael PaquierReviewed-by: Etsuro FujitaDiscussion:https://postgr.es/m/15552-c64aa14c5c22f63c@postgresql.orgBackpatch-through: 10, where support for COPY on views has been added,while v11 has added support for COPY on foreign tables.
1 parent11a60d4 commitbf491a9

File tree

5 files changed

+70
-1
lines changed

5 files changed

+70
-1
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7981,6 +7981,22 @@ drop trigger rem2_trig_row_before on rem2;
79817981
drop trigger rem2_trig_row_after on rem2;
79827982
drop trigger loc2_trig_row_before_insert on loc2;
79837983
delete from rem2;
7984+
-- test COPY FROM with foreign table created in the same transaction
7985+
create table loc3 (f1 int, f2 text);
7986+
begin;
7987+
create foreign table rem3 (f1 int, f2 text)
7988+
server loopback options(table_name 'loc3');
7989+
copy rem3 from stdin;
7990+
commit;
7991+
select * from rem3;
7992+
f1 | f2
7993+
----+-----
7994+
1 | foo
7995+
2 | bar
7996+
(2 rows)
7997+
7998+
drop foreign table rem3;
7999+
drop table loc3;
79848000
-- ===================================================================
79858001
-- test IMPORT FOREIGN SCHEMA
79868002
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2124,6 +2124,20 @@ drop trigger loc2_trig_row_before_insert on loc2;
21242124

21252125
deletefrom rem2;
21262126

2127+
-- test COPY FROM with foreign table created in the same transaction
2128+
createtableloc3 (f1int, f2text);
2129+
begin;
2130+
create foreign table rem3 (f1int, f2text)
2131+
server loopback options(table_name'loc3');
2132+
copy rem3from stdin;
2133+
1foo
2134+
2bar
2135+
\.
2136+
commit;
2137+
select*from rem3;
2138+
drop foreign table rem3;
2139+
droptable loc3;
2140+
21272141
-- ===================================================================
21282142
-- test IMPORT FOREIGN SCHEMA
21292143
-- ===================================================================

‎src/backend/commands/copy.c

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2397,10 +2397,15 @@ CopyFrom(CopyState cstate)
23972397
* possible to improve on this, but it does mean maintaining heap insert
23982398
* option flags per partition and setting them when we first open the
23992399
* partition.
2400+
*
2401+
* This optimization is not supported for relation types which do not
2402+
* have any physical storage, with foreign tables and views using
2403+
* INSTEAD OF triggers entering in this category. Partitioned tables
2404+
* are not supported as per the description above.
24002405
*----------
24012406
*/
24022407
/* createSubid is creation check, newRelfilenodeSubid is truncation check */
2403-
if (cstate->rel->rd_rel->relkind!=RELKIND_PARTITIONED_TABLE&&
2408+
if (RELKIND_CAN_HAVE_STORAGE(cstate->rel->rd_rel->relkind)&&
24042409
(cstate->rel->rd_createSubid!=InvalidSubTransactionId||
24052410
cstate->rel->rd_newRelfilenodeSubid!=InvalidSubTransactionId))
24062411
{

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -545,6 +545,23 @@ SELECT * FROM instead_of_insert_tbl;
545545
1 | test1
546546
(1 row)
547547

548+
-- Test of COPY optimization with view using INSTEAD OF INSERT
549+
-- trigger when relation is created in the same transaction as
550+
-- when COPY is executed.
551+
BEGIN;
552+
CREATE VIEW instead_of_insert_tbl_view_2 as select ''::text as str;
553+
CREATE TRIGGER trig_instead_of_insert_tbl_view_2
554+
INSTEAD OF INSERT ON instead_of_insert_tbl_view_2
555+
FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl();
556+
COPY instead_of_insert_tbl_view_2 FROM stdin;
557+
SELECT * FROM instead_of_insert_tbl;
558+
id | name
559+
----+-------
560+
1 | test1
561+
2 | test1
562+
(2 rows)
563+
564+
COMMIT;
548565
-- clean up
549566
DROP TABLE forcetest;
550567
DROP TABLE vistest;
@@ -557,4 +574,5 @@ DROP FUNCTION fn_x_before();
557574
DROP FUNCTION fn_x_after();
558575
DROP TABLE instead_of_insert_tbl;
559576
DROP VIEW instead_of_insert_tbl_view;
577+
DROP VIEW instead_of_insert_tbl_view_2;
560578
DROP FUNCTION fun_instead_of_insert_tbl();

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -398,6 +398,21 @@ test1
398398

399399
SELECT*FROM instead_of_insert_tbl;
400400

401+
-- Test of COPY optimization with view using INSTEAD OF INSERT
402+
-- trigger when relation is created in the same transaction as
403+
-- when COPY is executed.
404+
BEGIN;
405+
CREATEVIEWinstead_of_insert_tbl_view_2asselect''::textas str;
406+
CREATETRIGGERtrig_instead_of_insert_tbl_view_2
407+
INSTEAD OF INSERTON instead_of_insert_tbl_view_2
408+
FOR EACH ROW EXECUTE PROCEDURE fun_instead_of_insert_tbl();
409+
410+
COPY instead_of_insert_tbl_view_2FROM stdin;
411+
test1
412+
\.
413+
414+
SELECT*FROM instead_of_insert_tbl;
415+
COMMIT;
401416

402417
-- clean up
403418
DROPTABLE forcetest;
@@ -411,4 +426,5 @@ DROP FUNCTION fn_x_before();
411426
DROPFUNCTION fn_x_after();
412427
DROPTABLE instead_of_insert_tbl;
413428
DROPVIEW instead_of_insert_tbl_view;
429+
DROPVIEW instead_of_insert_tbl_view_2;
414430
DROPFUNCTION fun_instead_of_insert_tbl();

0 commit comments

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