postgres_fdw foreign keys with default sequence

Started by Tim Kanealmost 11 years ago3 messages
#1Tim Kane
tim.kane@gmail.com

Hi all,

Not sure if this has been reported already, it seems to be a variation on
this thread:

/messages/by-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

#2Tim Kane
tim.kane@gmail.com
In reply to: Tim Kane (#1)
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.kane@gmail.com> wrote:

Show quoted text

Hi all,

Not sure if this has been reported already, it seems to be a variation on
this thread:

/messages/by-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

#3Kevin Grittner
kgrittn@ymail.com
In reply to: Tim Kane (#2)
Re: postgres_fdw foreign keys with default sequence

Tim Kane <tim.kane@gmail.com> wrote:

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

In the future, please show code that you have actually run. In
this case it's pretty easy to know how to answer, but in others it
may really draw out the process of helping you.

At this time materialized views do not support constraints, and may
not be referenced in foreign key definitions.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers