Thread: Trouble with postgres_fdw & dblink extensions

=?UTF-8?B?THVrw6HFoSBTb2JvdGth?= <sobotka.luk@gmail.com> writes:
> I would be grateful for some help. I am writing you because I am confused
> about using data foreign wrappers and dblink. I attached simplified script
> describing the problem.

I think what is happening is that postgres_fdw starts a transaction on
its connection as soon as it's asked to do something, and then the CREATE
TABLE executed on dblink's separate connection isn't visible to that
already-in-progress transaction.

That theory only holds up if you are running in serializable mode (which
postgres_fdw would then also use for its remote transaction).  Which you
didn't say, but it's hard to see how it'd fail otherwise.

            regards, tom lane

On 11/15/18 3:23 PM, Lukáš Sobotka wrote:
> Hi guys,
> 
> I would be grateful for some help. I am writing you because I am 
> confused about using data foreign wrappers and dblink. I attached 
> simplified script describing the problem.
> 
> What I am trying to do?
> I have two databases and I need to copy table from local database to the 
> remote one. For copying is used function which contains a few parts:
> 
>   *
> 
>     loading setting from foreign table (this part became a problematic)
> 
>   *
> 
>     creating destination table on remote db
> 
>   *
> 
>     importing foreign table
> 
>   *
> 
>     insert data into foreign table
> 
> If query using foreign table (with setting) is performed, command for 
> importing schema does not import new created table (it looks like table 
> is not created yet). So copying ends with error. The second calling of 
> function is all right (because destination table is already created from 
> first calling).
> If function does not use foreign table, the first calling of function 
> copies all data.
> 
> Why the new created remote table can not be imported to local database 
> when I had performed query on other foreign table? What am I missing?

Should this:

dblink('test_server_link' ...)

not be:

dblink_exec('test_server_link' ...)

> 
> I am using PG 9.6 (PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by 
> gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit).
> 
> Best regards, Lukas
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com