Re: postgres_fdw foreign keys with default sequence

Slight typo on my local host example there. s/clone/local/
More like the below:

CREATE FOREIGN TABLE IF NOT EXISTS live.devices (
device_id bigint NOT NULL
);

CREATE MATERIALISED VIEW local.devices;

CREATE test_table (device_id bigint FOREIGN KEY (device_id) REFERENCES
*local*.devices (device_id) );

ERROR: referenced relation "devices" is not a table

On Tue, Feb 17, 2015 at 1:08 PM, Tim Kane <tim(dot)kane(at)gmail(dot)com> wrote:

> Hi all,
>
> Not sure if this has been reported already, it seems to be a variation on
> this thread:
>
> http://www.postgresql.org/message-id/20130515151059.GO4361@tamriel.snowman.net
>
>
> One minor difference is, in my scenario - my source table field is defined
> as BIGINT (not serial) - though it does have a default nextval on a
> sequence, so ultimately - the same dependence.
>
> The primary difference (IMHO), is that I am actually foreign keying on a
> local materialised view of the fdw'ed foreign table.
>
>
>
> On the foreign host:
> Table "live.devices"
> Column | Type | Modifiers
>
> ------------+--------+-----------------------------------------------------------
> device_id | bigint | not null default
> nextval('devices_id_sequence'::regclass)
>
>
> On the local host:
>
>
> CREATE FOREIGN TABLE IF NOT EXISTS live.devices (
> device_id bigint NOT NULL
> );
>
> CREATE MATERIALISED VIEW local.devices;
>
> CREATE test_table (device_id bigint FOREIGN KEY (device_id) REFERENCES
> clone.devices (device_id) );
>
> ERROR: referenced relation "devices" is not a table
>
>
>
> Though this is a similar scenario to the previous thread, I would have
> expected foreign keying from a materialised view to behave independently of
> the FDW, as if from a regular local table.
>
> FYI, I'm running postgresql 9.3.4
>
> Cheers,
>
> Tim
>
>
>