Thread: Sequences in foreign tables
Hello,
I'm learning now something about foreign tables in PG 9.3. I wonder if
there is a clean way to use a sequence on the remote side, so that an
"insert into remote_table values ([data not including id]) returning
id" would ask the remote server to generate a new value for id.
As it stands now defaults are evaluated client-side and a column with
no default is treated as defaulting to null (as per docs): I see from
the logs that explicit nulls are sent to the server even if not
requested by the insert, or if "default" is specified.
I've worked around that using a trigger on the remote table to
reinstate the defaults, something along the line of:
create or replace function ... returning trigger
begin
if new.id is null then new.id = nextval('seqname'::regclass); end if;
if new.cr_date is null then new.cr_date = now(); end if;
return new;
end
create trigger ... before insert for each row...
but I wonder if there is a more idiomatic way to do that. Using a
sequence on the remote side instead of the local seems a basic use
case and local sequences are not an option if more than one database
have a foreign table on the same physical table.
Thank you very much
-- Daniele
On Tue, Sep 16, 2014 at 8:05 AM, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> I'm learning now something about foreign tables in PG 9.3. I wonder if
> there is a clean way to use a sequence on the remote side, so that an
> "insert into remote_table values ([data not including id]) returning
> id" would ask the remote server to generate a new value for id.
>
> As it stands now defaults are evaluated client-side and a column with
> no default is treated as defaulting to null (as per docs): I see from
> the logs that explicit nulls are sent to the server even if not
> requested by the insert, or if "default" is specified.
>
> I've worked around that using a trigger on the remote table to
> reinstate the defaults, something along the line of:
>
> create or replace function ... returning trigger
> begin
> if new.id is null then new.id = nextval('seqname'::regclass); end if;
> if new.cr_date is null then new.cr_date = now(); end if;
> return new;
> end
>
> create trigger ... before insert for each row...
>
> but I wonder if there is a more idiomatic way to do that. Using a
> sequence on the remote side instead of the local seems a basic use
> case and local sequences are not an option if more than one database
> have a foreign table on the same physical table.
You could always define foreign table on local node without the
columns having default values you want to enforce on remote side, and
you may even be able to do well with such a definition on local side
as it does not seem you want to make the default remotes visible on
local side (always possible to use an extra foreign table definition
btw). So for example:
On remote node:
=# create table aa (a serial, b int, c int);
CREATE TABLE
On local node with postgres_fdw, the following insertion...
=# CREATE FOREIGN TABLE aa_foreign (b int, c int) SERVER
postgres_server OPTIONS (table_name 'aa');
CREATE FOREIGN TABLE
=# explain verbose insert into aa_foreign values (1,2);
QUERY PLAN
---------------------------------------------------------------
Insert on public.aa_foreign (cost=0.00..0.01 rows=1 width=0)
Remote SQL: INSERT INTO public.aa(b, c) VALUES ($1, $2)
-> Result (cost=0.00..0.01 rows=1 width=0)
Output: 1, 2
(4 rows)
=# insert into aa_foreign values (1,2);
INSERT 0 1
... Will generate the following data on remote node:
=# select * from aa;
a | b | c
---+---+---
1 | 1 | 2
(1 row)
Regards,
--
Michael
On Tue, Sep 16, 2014 at 6:04 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Tue, Sep 16, 2014 at 8:05 AM, Daniele Varrazzo > <daniele.varrazzo@gmail.com> wrote: >> I'm learning now something about foreign tables in PG 9.3. I wonder if >> there is a clean way to use a sequence on the remote side, so that an >> "insert into remote_table values ([data not including id]) returning >> id" would ask the remote server to generate a new value for id. > You could always define foreign table on local node without the > columns having default values you want to enforce on remote side, and > you may even be able to do well with such a definition on local side > as it does not seem you want to make the default remotes visible on > local side Well, actually I do: see the query in question. The "returning id" requires the field id to exist on the foreign table :) > (always possible to use an extra foreign table definition > btw). So for example: Yes, of course, but I'm experimenting into how transparent would be to replace the table with a foreign table and leave some working code unchanged (it works fine with the trigger workaround). By the way even if I had a table without id where to write to and one with the id to read from doesn't help in detecting what unique id was generated remotely. -- Daniele
On Tue, Sep 16, 2014 at 10:17 AM, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote: > On Tue, Sep 16, 2014 at 6:04 PM, Michael Paquier > <michael.paquier@gmail.com> wrote: >> On Tue, Sep 16, 2014 at 8:05 AM, Daniele Varrazzo >> <daniele.varrazzo@gmail.com> wrote: > >>> I'm learning now something about foreign tables in PG 9.3. I wonder if >>> there is a clean way to use a sequence on the remote side, so that an >>> "insert into remote_table values ([data not including id]) returning >>> id" would ask the remote server to generate a new value for id. > >> You could always define foreign table on local node without the >> columns having default values you want to enforce on remote side, and >> you may even be able to do well with such a definition on local side >> as it does not seem you want to make the default remotes visible on >> local side > > Well, actually I do: see the query in question. The "returning id" > requires the field id to exist on the foreign table :) > >> (always possible to use an extra foreign table definition >> btw). So for example: > > Yes, of course, but I'm experimenting into how transparent would be to > replace the table with a foreign table and leave some working code > unchanged (it works fine with the trigger workaround). By the way even > if I had a table without id where to write to and one with the id to > read from doesn't help in detecting what unique id was generated > remotely. Sorry I missed your point. Yes, I would go as well with a remote row-based before trigger, perhaps even coupled with a NOT NULL check on the column where default value is enforced on remote table definition to have some error control. -- Michael