Re: postgres_fdw foreign tables and serial columns

Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> Tom Lane wrote:
>> "Nicholson, Brad (Toronto, ON, CA)" <bnicholson(at)hp(dot)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