postgres_fdw foreign tables and serial columns

"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 via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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

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

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

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

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

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

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

* 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

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

* 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