Re: Trouble with postgres_fdw & dblink extensions

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(dot)klaver(at)aklaver(dot)com