BUG #15552: Unexpected error in COPY to a foreign table in atransaction - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15552
Logged by:          Luis M Carril
Email address:      luis.carril@swarm64.com
PostgreSQL version: 11.1
Operating system:   Ubuntu 16.04
Description:

Hi,
   Postgres throws a "could not open file" error when inside a transaction
we create a foreign table and copy data into it.

Reproduction (code based on tests in postgres_fdw test suite):
-----------------------
CREATE EXTENSION postgres_fdw;

CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
        EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER
postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;

CREATE USER MAPPING FOR public SERVER testserver1
    OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;

create table loct1 (a int check (a in (1)), b text);

begin;
create foreign table remp1 (a int check (a in (1)), b text) server loopback
options (table_name 'loct1');
copy remp1 from stdin delimiter ',';
1,f
\.
-----------------------

Observed behavior:
     ERROR:  could not open file "base/16385/16460": No such file or
directory

-----------------------

For what I saw, the error is triggered when synchronizing the heap in
CopyFrom (backend/commands/copy.c:2890), see the following stacktrace (when
setting a breakpoint at errcode_for_file_access():

#0  errcode_for_file_access () at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/utils/error/elog.c:600
#1  0x00005636212d33d2 in mdopen (reln=<optimized out>,
forknum=forknum@entry=MAIN_FORKNUM, behavior=behavior@entry=1)
    at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/storage/smgr/md.c:606
#2  0x00005636212d3961 in mdopen (behavior=1, forknum=MAIN_FORKNUM,
reln=0x563622ba8a88) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/storage/smgr/md.c:922
#3  mdnblocks (reln=0x563622ba8a88, forknum=MAIN_FORKNUM) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/storage/smgr/md.c:875
#4  0x00005636212d39b9 in mdimmedsync (reln=0x563622ba8a88,
forknum=MAIN_FORKNUM) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/storage/smgr/md.c:1033
#5  0x000056362103257c in heap_sync (rel=0x7f0e3e681aa8) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/access/heap/heapam.c:9408
#6  0x0000563621115e89 in CopyFrom (cstate=cstate@entry=0x563622ba2040) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/commands/copy.c:2890
#7  0x000056362111629b in DoCopy (pstate=pstate@entry=0x563622ad08c0,
stmt=stmt@entry=0x563622a9c0c8, stmt_location=0, stmt_len=59,
processed=processed@entry=0x7fff92a85aa0)
    at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/commands/copy.c:992
#8  0x00005636212dfe95 in standard_ProcessUtility (pstmt=0x563622a9c198,
queryString=0x563622a9b460 "COPY test_table (col1, col2, col3) FROM STDIN
DELIMITER ',';", context=PROCESS_UTILITY_TOPLEVEL, params=0x0,
    queryEnv=0x0, dest=0x563622a9c518, completionTag=0x7fff92a86480 "") at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/tcop/utility.c:551
#9  0x00007f0f5b027c89 in Db::Psql::ProcessUtilityHook
(pstmt=0x563622a9c198, queryString=0x563622a9b460 "COPY test_table (col1,
col2, col3) FROM STDIN DELIMITER ',';", context=PROCESS_UTILITY_TOPLEVEL,
    paramListInfo=0x0, queryEnvironment=0x0, destReceiver=0x563622a9c518,
completionTag=0x7fff92a86480 "") at
/home/luis/main-dev/db/psql/src/utility_hook.cpp:1024
#10 0x00005636212dc9c9 in PortalRunUtility (portal=0x563622b290a0,
pstmt=0x563622a9c198, isTopLevel=<optimized out>, setHoldSnapshot=<optimized
out>, dest=0x563622a9c518, completionTag=0x7fff92a86480 "")
    at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/tcop/pquery.c:1178
#11 0x00005636212dd4f8 in PortalRunMulti
(portal=portal@entry=0x563622b290a0, isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false,
dest=dest@entry=0x563622a9c518,
    altdest=altdest@entry=0x563622a9c518,
completionTag=completionTag@entry=0x7fff92a86480 "") at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/tcop/pquery.c:1331
#12 0x00005636212de265 in PortalRun (portal=portal@entry=0x563622b290a0,
count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true,
run_once=run_once@entry=true, dest=dest@entry=0x563622a9c518,
    altdest=altdest@entry=0x563622a9c518, completionTag=0x7fff92a86480 "")
at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/tcop/pquery.c:799
#13 0x00005636212d9d21 in exec_simple_query (query_string=0x563622a9b460
"COPY test_table (col1, col2, col3) FROM STDIN DELIMITER ',';")
    at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/tcop/postgres.c:1145
#14 0x00005636212db24b in PostgresMain (argc=<optimized out>,
argv=argv@entry=0x563622add4f8, dbname=0x563622add3a8 "docker-user",
username=<optimized out>)
    at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/tcop/postgres.c:4182
#15 0x0000563620fec961 in BackendRun (port=0x563622ad17f0) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/postmaster/postmaster.c:4361
#16 BackendStartup (port=0x563622ad17f0) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/postmaster/postmaster.c:4033
#17 ServerLoop () at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/postmaster/postmaster.c:1706
#18 0x0000563621266054 in PostmasterMain (argc=5, argv=<optimized out>) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/postmaster/postmaster.c:1379
#19 0x0000563620fedd05 in main (argc=5, argv=0x563622a95f80) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/main/main.c:228
(gdb) f 6
#6  0x0000563621115e89 in CopyFrom (cstate=cstate@entry=0x563622ba2040) at
/build/postgresql-11-9gVVK7/postgresql-11-11.1/build/../src/backend/commands/copy.c:2890

If someone gives me a hint on the expected behavior here, I would gladly
submit a patch myself.

Cheers
Luis M. Carril