Re: Sequences in foreign tables

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