Обсуждение: 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