From: | Luis Carril <luis(dot)carril(at)swarm64(dot)com> |
---|---|
To: | vignesh C <vignesh21(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Daniel Gustafsson <daniel(at)yesql(dot)se>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Option to dump foreign data in pg_dump |
Date: | 2020-01-20 15:04:56 |
Message-ID: | LEXPR01MB0255758629A9EA4A63C1793BE7320@LEXPR01MB0255.DEUPRD01.PROD.OUTLOOK.DE |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Jan 14, 2020 at 5:22 PM Luis Carril <luis(dot)carril(at)swarm64(dot)com<mailto:luis(dot)carril(at)swarm64(dot)com>> wrote:
Can you have a look at dump with parallel option. Parallel option will
take a lock on table while invoking lockTableForWorker. May be this is
not required for foreign tables.
Thoughts?
I tried with -j and found no issue. I guess that the foreign table needs locking anyway to prevent anyone to modify it while is being dumped.
I'm able to get the problem with the following steps:
Bring up a postgres setup with servers running in 5432 & 5433 port.
Execute the following commands in Server1 configured on 5432 port:
* CREATE EXTENSION postgres_fdw;
* CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5433', dbname 'postgres');
* create user user1 password '123';
* alter user user1 with superuser;
* CREATE USER MAPPING FOR user1 SERVER foreign_server OPTIONS (user 'user1', password '123');
Execute the following commands in Server2 configured on 5433 port:
* create user user1 password '123';
* alter user user1 with superuser;
Execute the following commands in Server2 configured on 5433 port as user1 user:
* create schema test;
* create table test.test1(id int);
* insert into test.test1 values(10);
Execute the following commands in Server1 configured on 5432 port as user1 user:
* CREATE FOREIGN TABLE foreign_table1 (id integer NOT NULL) SERVER foreign_server OPTIONS (schema_name 'test', table_name 'test1');
Without parallel option, the operation is successful:
* ./pg_dump -d postgres -f dumpdir -U user1 -F d --include-foreign-data foreign_server
With parallel option it fails:
* ./pg_dump -d postgres -f dumpdir1 -U user1 -F d -j 5 --include-foreign-data foreign_server
pg_dump: error: could not obtain lock on relation "public.foreign_table1"
This usually means that someone requested an ACCESS EXCLUSIVE lock on the table after the pg_dump parent process had gotten the initial ACCESS SHARE lock on the table.
pg_dump: error: a worker process died unexpectedly
There may be simpler steps than this to reproduce the issue, i have not try to optimize it.
Regards,
Vignesh
EnterpriseDB: http://www.enterprisedb.com
Hi Vignesh,
yes you are right I could reproduce it also with 'file_fdw'. The issue is that LOCK is not supported on foreign tables, so I guess that the safest solution is to make the --include-foreign-data incompatible with --jobs, because skipping the locking for foreign tables maybe can lead to a deadlock anyway. Suggestions?
Cheers
Luis M Carril
________________________________
From: vignesh C <vignesh21(at)gmail(dot)com>
Sent: Thursday, January 16, 2020 10:01 AM
To: Luis Carril <luis(dot)carril(at)swarm64(dot)com>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>; Daniel Gustafsson <daniel(at)yesql(dot)se>; Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Option to dump foreign data in pg_dump
On Tue, Jan 14, 2020 at 5:22 PM Luis Carril <luis(dot)carril(at)swarm64(dot)com<mailto:luis(dot)carril(at)swarm64(dot)com>> wrote:
Can you have a look at dump with parallel option. Parallel option will
take a lock on table while invoking lockTableForWorker. May be this is
not required for foreign tables.
Thoughts?
I tried with -j and found no issue. I guess that the foreign table needs locking anyway to prevent anyone to modify it while is being dumped.
I'm able to get the problem with the following steps:
Bring up a postgres setup with servers running in 5432 & 5433 port.
Execute the following commands in Server1 configured on 5432 port:
* CREATE EXTENSION postgres_fdw;
* CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5433', dbname 'postgres');
* create user user1 password '123';
* alter user user1 with superuser;
* CREATE USER MAPPING FOR user1 SERVER foreign_server OPTIONS (user 'user1', password '123');
Execute the following commands in Server2 configured on 5433 port:
* create user user1 password '123';
* alter user user1 with superuser;
Execute the following commands in Server2 configured on 5433 port as user1 user:
* create schema test;
* create table test.test1(id int);
* insert into test.test1 values(10);
Execute the following commands in Server1 configured on 5432 port as user1 user:
* CREATE FOREIGN TABLE foreign_table1 (id integer NOT NULL) SERVER foreign_server OPTIONS (schema_name 'test', table_name 'test1');
Without parallel option, the operation is successful:
* ./pg_dump -d postgres -f dumpdir -U user1 -F d --include-foreign-data foreign_server
With parallel option it fails:
* ./pg_dump -d postgres -f dumpdir1 -U user1 -F d -j 5 --include-foreign-data foreign_server
pg_dump: error: could not obtain lock on relation "public.foreign_table1"
This usually means that someone requested an ACCESS EXCLUSIVE lock on the table after the pg_dump parent process had gotten the initial ACCESS SHARE lock on the table.
pg_dump: error: a worker process died unexpectedly
There may be simpler steps than this to reproduce the issue, i have not try to optimize it.
Regards,
Vignesh
EnterpriseDB: http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-01-20 15:43:32 | Re: Add support for automatically updating Unicode derived files |
Previous Message | a.kondratov | 2020-01-20 14:50:06 | Re: Physical replication slot advance is not persistent |