postgres_fdw foreign keys with default sequence
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
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
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