Re: postgres_fdw foreign keys with default sequence - Mailing list pgsql-hackers

<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>