Re: Sequences in foreign tables

On Tue, Sep 16, 2014 at 10:17 AM, Daniele Varrazzo
<daniele(dot)varrazzo(at)gmail(dot)com> wrote:
> On Tue, Sep 16, 2014 at 6:04 PM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
>> On Tue, Sep 16, 2014 at 8:05 AM, Daniele Varrazzo
>> <daniele(dot)varrazzo(at)gmail(dot)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