Thread: postgres_fdw foreign keys with default sequence

<div dir="ltr"><div class="gmail_default" style="font-family:times new roman,serif;font-size:small">Hi all,<br /><br
/>Notsure if this has been reported already, it seems to be a variation on this thread:<br />   <a
href="http://www.postgresql.org/message-id/20130515151059.GO4361@tamriel.snowman.net">http://www.postgresql.org/message-id/20130515151059.GO4361@tamriel.snowman.net</a><br
/><br/><br /></div><div class="gmail_default" style="font-family:times new roman,serif;font-size:small">One minor
differenceis, in my scenario - my source table field is defined as BIGINT (not serial) - though it does have a default
nextvalon a sequence, so ultimately - the same dependence.<br /><br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small">The primary difference (IMHO), is that I am actually foreign
keyingon a local materialised view of the fdw'ed foreign table.<br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small"><br /><br /><br />On the foreign host:<br /><span
style="font-family:monospace,monospace">                             Table "live.devices"<br />   Column   |  Type 
|                        Modifiers<br
/>------------+--------+-----------------------------------------------------------<br/> device_id  | bigint | not null
defaultnextval('devices_id_sequence'::regclass)</span><br /><br /><br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small">On the local host:<br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small"><br /><br /><span
style="font-family:monospace,monospace">CREATEFOREIGN TABLE IF NOT EXISTS live.devices (<br />         device_id 
bigintNOT NULL<br /> );<br /><br /></span></div><div class="gmail_default" style="font-family:times new
roman,serif;font-size:small"><spanstyle="font-family:monospace,monospace">CREATE MATERIALISED VIEW local.devices;<br
/><br/>CREATE test_table (device_id bigint FOREIGN KEY (device_id) REFERENCES clone.devices (device_id) );</span><br
/></div><divclass="gmail_default" style="font-family:times new roman,serif;font-size:small"><br /><span
style="font-family:monospace,monospace">ERROR: referenced relation "devices" is not a table</span><br /><br /><br /><br
/></div><divclass="gmail_default" style="font-family:times new roman,serif;font-size:small">Though this is a similar
scenarioto the previous thread, I would have expected foreign keying from a materialised view to behave independently
ofthe FDW, as if from a regular local table.<br /><br /></div><div class="gmail_default" style="font-family:times new
roman,serif;font-size:small">FYI,I'm running postgresql 9.3.4<br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small"><br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small">Cheers,<br /><br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small">Tim<br /><br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small"><br /></div></div> 

<div dir="ltr"><div class="gmail_default" style="font-family:times new roman,serif;font-size:small">Slight typo on my
localhost example there.  s/clone/local/<br />More like the below:<br /><br /><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small"><br /><span style="font-family:monospace,monospace">CREATE
FOREIGNTABLE IF NOT EXISTS live.devices (<br />         device_id  bigint NOT NULL<br /> );<br /><br
/></span></div><divclass="gmail_default" style="font-family:times new roman,serif;font-size:small"><span
style="font-family:monospace,monospace">CREATEMATERIALISED VIEW local.devices;<br /><br />CREATE test_table (device_id
bigintFOREIGN KEY (device_id) REFERENCES <b>local</b>.devices (device_id) );</span><br /></div><br /><span
style="font-family:monospace,monospace">ERROR: referenced relation "devices" is not a table</span><br
/></div></div><divclass="gmail_extra"><br /><div class="gmail_quote">On Tue, Feb 17, 2015 at 1:08 PM, Tim Kane <span
dir="ltr"><<ahref="mailto:tim.kane@gmail.com" target="_blank">tim.kane@gmail.com</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div
dir="ltr"><divclass="gmail_default" style="font-family:times new roman,serif;font-size:small">Hi all,<br /><br />Not
sureif this has been reported already, it seems to be a variation on this thread:<br />   <a
href="http://www.postgresql.org/message-id/20130515151059.GO4361@tamriel.snowman.net"
target="_blank">http://www.postgresql.org/message-id/20130515151059.GO4361@tamriel.snowman.net</a><br/><br /><br
/></div><divclass="gmail_default" style="font-family:times new roman,serif;font-size:small">One minor difference is, in
myscenario - 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.<br /><br /></div><div class="gmail_default" style="font-family:times new
roman,serif;font-size:small">Theprimary difference (IMHO), is that I am actually foreign keying on a local materialised
viewof the fdw'ed foreign table.<br /></div><div class="gmail_default" style="font-family:times new
roman,serif;font-size:small"><br/><br /><br />On the foreign host:<br /><span
style="font-family:monospace,monospace">                             Table "live.devices"<br />   Column   |  Type 
|                        Modifiers<br
/>------------+--------+-----------------------------------------------------------<br/> device_id  | bigint | not null
defaultnextval('devices_id_sequence'::regclass)</span><br /><br /><br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small">On the local host:<br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small"><br /><br /><span
style="font-family:monospace,monospace">CREATEFOREIGN TABLE IF NOT EXISTS live.devices (<br />         device_id 
bigintNOT NULL<br /> );<br /><br /></span></div><div class="gmail_default" style="font-family:times new
roman,serif;font-size:small"><spanstyle="font-family:monospace,monospace">CREATE MATERIALISED VIEW local.devices;<br
/><br/>CREATE test_table (device_id bigint FOREIGN KEY (device_id) REFERENCES clone.devices (device_id) );</span><br
/></div><divclass="gmail_default" style="font-family:times new roman,serif;font-size:small"><br /><span
style="font-family:monospace,monospace">ERROR: referenced relation "devices" is not a table</span><br /><br /><br /><br
/></div><divclass="gmail_default" style="font-family:times new roman,serif;font-size:small">Though this is a similar
scenarioto the previous thread, I would have expected foreign keying from a materialised view to behave independently
ofthe FDW, as if from a regular local table.<br /><br /></div><div class="gmail_default" style="font-family:times new
roman,serif;font-size:small">FYI,I'm running postgresql 9.3.4<br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small"><br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small">Cheers,<br /><br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small">Tim<br /><br /></div><div class="gmail_default"
style="font-family:timesnew roman,serif;font-size:small"><br /></div></div></blockquote></div><br /></div> 

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