Обсуждение: postgres_fdw foreign tables and serial columns
Hi, I'm kicking the tires on the 9.3 postgres_fdw stuff - I'm not sure if this is an issue or intended behavior, but it was prettyconfusing based on the error message that was output. If you try creating a foreign table with a reference to a serialdata type, it comes back with a "referenced relation is not a table" error. If you change the data type in the referencedtable to integer - then it works. Completely understand why this is needed - but it tripped me up for a while. At the very least, can I suggest adding something in the documentation about serial columns (if it is not an issue)? test=# create table foo (id serial); CREATE TABLE test=# create foreign table local_foo (id serial) server test_server options (table_name 'foo'); ERROR: referenced relation "local_foo" is not a table test=# create foreign table local_foo (id integer) server test_server options (table_name 'foo'); CREATE FOREIGN TABLE Brad
"Nicholson, Brad (Toronto, ON, CA)" <bnicholson@hp.com> writes: > [ this error message sucks: ] > test=# create foreign table local_foo (id serial) server test_server options (table_name 'foo'); > ERROR: referenced relation "local_foo" is not a table Yeah, I'd noticed that myself. We could probably tweak the code to issue a different error message and/or add a HINT if the serial's parent relation is a foreign table. I'm not exactly sure what it should say though. Thoughts? regards, tom lane
Sent from my iPad On 15-May-2013, at 18:56, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Nicholson, Brad (Toronto, ON, CA)" <bnicholson@hp.com> writes: >> [ this error message sucks: ] >> test=# create foreign table local_foo (id serial) server test_server options (table_name 'foo'); >> ERROR: referenced relation "local_foo" is not a table > > Yeah, I'd noticed that myself. We could probably tweak the code to > issue a different error message and/or add a HINT if the serial's > parent relation is a foreign table. I'm not exactly sure what it > should say though. Thoughts? > > > > > +1 for the HINT message addition. Regards, Atri
Tom Lane wrote: > "Nicholson, Brad (Toronto, ON, CA)" <bnicholson@hp.com> writes: >> [ this error message sucks: ] >> test=# create foreign table local_foo (id serial) server test_server options (table_name 'foo'); >> ERROR: referenced relation "local_foo" is not a table > > Yeah, I'd noticed that myself. We could probably tweak the code to > issue a different error message and/or add a HINT if the serial's > parent relation is a foreign table. I'm not exactly sure what it > should say though. Thoughts? HINT: Serial columns can only be defined for local tables. Use "integer" or "bigint" instead. Yours, Laurenz Albe
Albe Laurenz <laurenz.albe@wien.gv.at> writes: > Tom Lane wrote: >> "Nicholson, Brad (Toronto, ON, CA)" <bnicholson@hp.com> writes: >>> [ this error message sucks: ] >>> test=# create foreign table local_foo (id serial) server test_server options (table_name 'foo'); >>> ERROR: referenced relation "local_foo" is not a table >> Yeah, I'd noticed that myself. We could probably tweak the code to >> issue a different error message and/or add a HINT if the serial's >> parent relation is a foreign table. I'm not exactly sure what it >> should say though. Thoughts? > HINT: Serial columns can only be defined for local tables. Use "integer" or "bigint" instead. Actually ... wait a minute. Why *don't* we allow SERIAL columns in foreign tables? That made sense before, but now that we support column defaults for them, I don't see any good reason for this prohibition. The behavior, if we just remove this error check, would be that we'd create a local sequence and it would become the source of default values for insertions into the foreign table. Since we've already committed to the decision that column defaults are evaluated locally, this is perfectly consistent. You can certainly argue that it might be silly to have a local sequence generating the default value for insertions into a remote table; but on the other hand, it might not be silly, depending on usage scenario. I don't think the database should be enforcing a policy choice like that. regards, tom lane
On 05/15/2013 10:27 AM, Tom Lane wrote: > You can certainly argue that it might be silly to have a local sequence > generating the default value for insertions into a remote table; but > on the other hand, it might not be silly, depending on usage scenario. > I don't think the database should be enforcing a policy choice like > that. Presumably if it's not appropriate they won't define it as a serial column. If Postgres is the primary source of the data rather than the foreign handler then it makes plenty of sense to have a serial column, I should think. So +1 for allowing it. cheers andrew
* Andrew Dunstan (andrew@dunslane.net) wrote: > On 05/15/2013 10:27 AM, Tom Lane wrote: > >You can certainly argue that it might be silly to have a local sequence > >generating the default value for insertions into a remote table; but > >on the other hand, it might not be silly, depending on usage scenario. > >I don't think the database should be enforcing a policy choice like > >that. > > Presumably if it's not appropriate they won't define it as a serial > column. If Postgres is the primary source of the data rather than > the foreign handler then it makes plenty of sense to have a serial > column, I should think. > > So +1 for allowing it. I'm alright with allowing it and making it the 'default', but I can certainly see use-cases for having a sequence on the remote side which is used for new values for that table and I'd suggest that we figure out a way to support that. At first blush, with 'simple' writable views, perhaps that can just be a view definition on the remote side which doesn't include that column and therefore that column won't be sent to the remote side explicitly but, but the view, running on the remote, would turn around and pick up the default value for any fields which aren't in the view definition when inserting into the table underneath. I'm not suggesting that as something we do for the user, but perhaps we could include a note along these lines in the docs for users who need the default evaluated on the remote? And come up with a better way to handle it in the future (perhaps an option in the foreign table definition?). This, of course, presumes that the solution I've described actually works, iow, not tested. :) Thanks, Stephen
Stephen Frost <sfrost@snowman.net> writes: > I'm alright with allowing it and making it the 'default', but I can > certainly see use-cases for having a sequence on the remote side which > is used for new values for that table and I'd suggest that we figure out > a way to support that. The generic issue there is whether we can allow column defaults to be evaluated on the remote end. The original postgres_fdw submission tried to support that, but it had enough bugs and logical inconsistencies that I ended up ripping that out before commit. There's a good deal of discussion about that in the archives (in January or February IIRC). However, when and if we do allow that to work, I'd still say that it's reasonable for "SERIAL" to mean local creation of the default value. If you want a remotely-supplied default to work, you'd not put a DEFAULT clause into the local definition; and SERIAL is essentially a shorthand for a DEFAULT clause. > At first blush, with 'simple' writable views, perhaps that can just be a > view definition on the remote side which doesn't include that column and > therefore that column won't be sent to the remote side explicitly but, > but the view, running on the remote, would turn around and pick up the > default value for any fields which aren't in the view definition when > inserting into the table underneath. Yeah, I think the possibility of such a workaround was one of the reasons we decided it was okay to support only locally-computed defaults for now. regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote: > The generic issue there is whether we can allow column defaults to be > evaluated on the remote end. The original postgres_fdw submission tried > to support that, but it had enough bugs and logical inconsistencies that > I ended up ripping that out before commit. There's a good deal of > discussion about that in the archives (in January or February IIRC). Yeah, I watched much of that go by- just couldn't follow it entirely at the time. Still.. > However, when and if we do allow that to work, I'd still say that it's > reasonable for "SERIAL" to mean local creation of the default value. I agree with this; all I was trying to get at is that we shouldn't close off any doors to eventually providing a way for defaults to be pushed to the remote. > If you want a remotely-supplied default to work, you'd not put a DEFAULT > clause into the local definition; and SERIAL is essentially a shorthand > for a DEFAULT clause. Agreed. > Yeah, I think the possibility of such a workaround was one of the > reasons we decided it was okay to support only locally-computed > defaults for now. Right, and, of course, a simple trigger on the remote table would probably work just fine too. Thanks, Stephen