Thread: Re: FOREIGN TABLE and IDENTITY columns
On Tue, Oct 8, 2024 at 7:57 PM Julien Rouhaud <rjuju123@gmail.com> wrote: > > Hi, > > I was looking at the CREATE FOREIGN TABLE documentation to see if IDENTITY > columns were supported, and according to the doc they're not: only GENERATED > ALWAYS AS ( expr ) STORED is supported. > > However, a quick test shows that this is supported (same as serial datatype), > and apparently behaves as expected. Looking at the grammar, CreateStmt and > CreateForeignTableStmt actually share the same rule for the column definitions > (OptTableElementList) so the behavior seems expected. The parse analysis code > is also mostly shared between the two, with only a few stuff explicitly > forbidden for foreign tables (primary keys and such). > > It looks like this is just an oversight in the documentation? If so, it seems > like the CREATE and ALTER FOREIGN TABLE pages needs to be updated. The ALTER > FOREIGN TABLE page is also at least lacking the SET / DROP EXPRESSION clauses. The rows inserted/udpated on the foreign server won't honour the local IDENTITY constraint. Maybe that's why we don't want to support identity column in foreign tables. If all it is expected to do is add a monotonically increasing value, probably a DEFAULT value of nextval() would suffice. -- Best Wishes, Ashutosh Bapat
On Wed, Oct 9, 2024 at 12:40 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote: > > On Tue, Oct 8, 2024 at 7:57 PM Julien Rouhaud <rjuju123@gmail.com> wrote: > > > > Hi, > > > > I was looking at the CREATE FOREIGN TABLE documentation to see if IDENTITY > > columns were supported, and according to the doc they're not: only GENERATED > > ALWAYS AS ( expr ) STORED is supported. > > > > However, a quick test shows that this is supported (same as serial datatype), > > and apparently behaves as expected. Looking at the grammar, CreateStmt and > > CreateForeignTableStmt actually share the same rule for the column definitions > > (OptTableElementList) so the behavior seems expected. The parse analysis code > > is also mostly shared between the two, with only a few stuff explicitly > > forbidden for foreign tables (primary keys and such). > > > > It looks like this is just an oversight in the documentation? If so, it seems > > like the CREATE and ALTER FOREIGN TABLE pages needs to be updated. The ALTER > > FOREIGN TABLE page is also at least lacking the SET / DROP EXPRESSION clauses. > > The rows inserted/udpated on the foreign server won't honour the local > IDENTITY constraint. Maybe that's why we don't want to support > identity column in foreign tables. If all it is expected to do is add > a monotonically increasing value, probably a DEFAULT value of > nextval() would suffice. What if there is no local IDENTITY constraint, is that an unsupported scenario?
On Wed, Oct 9, 2024 at 4:22 AM Julien Rouhaud <rjuju123@gmail.com> wrote: > > On Wed, Oct 9, 2024 at 12:40 AM Ashutosh Bapat > <ashutosh.bapat.oss@gmail.com> wrote: > > > > On Tue, Oct 8, 2024 at 7:57 PM Julien Rouhaud <rjuju123@gmail.com> wrote: > > > > > > Hi, > > > > > > I was looking at the CREATE FOREIGN TABLE documentation to see if IDENTITY > > > columns were supported, and according to the doc they're not: only GENERATED > > > ALWAYS AS ( expr ) STORED is supported. > > > > > > However, a quick test shows that this is supported (same as serial datatype), > > > and apparently behaves as expected. Looking at the grammar, CreateStmt and > > > CreateForeignTableStmt actually share the same rule for the column definitions > > > (OptTableElementList) so the behavior seems expected. The parse analysis code > > > is also mostly shared between the two, with only a few stuff explicitly > > > forbidden for foreign tables (primary keys and such). > > > > > > It looks like this is just an oversight in the documentation? If so, it seems > > > like the CREATE and ALTER FOREIGN TABLE pages needs to be updated. The ALTER > > > FOREIGN TABLE page is also at least lacking the SET / DROP EXPRESSION clauses. > > > > The rows inserted/udpated on the foreign server won't honour the local > > IDENTITY constraint. Maybe that's why we don't want to support > > identity column in foreign tables. If all it is expected to do is add > > a monotonically increasing value, probably a DEFAULT value of > > nextval() would suffice. > > What if there is no local IDENTITY constraint, is that an unsupported scenario? Do you mean there's no local IDENTITY constraint but there's a remote one? The documentation doesn't explicitly mention this. But it would be good to test how that works, esp if somebody tries to INSERT a row from local server with a value specified for an IDENTITY column. -- Best Wishes, Ashutosh Bapat
On Wed, 9 Oct 2024, 21:22 Ashutosh Bapat, <ashutosh.bapat.oss@gmail.com> wrote:
On Wed, Oct 9, 2024 at 4:22 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> On Wed, Oct 9, 2024 at 12:40 AM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> >
> > On Tue, Oct 8, 2024 at 7:57 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> > >
> >
> > The rows inserted/udpated on the foreign server won't honour the local
> > IDENTITY constraint. Maybe that's why we don't want to support
> > identity column in foreign tables. If all it is expected to do is add
> > a monotonically increasing value, probably a DEFAULT value of
> > nextval() would suffice.
>
> What if there is no local IDENTITY constraint, is that an unsupported scenario?
Do you mean there's no local IDENTITY constraint but there's a remote
one?
yes. after all the identity clause is supposed to be the standard way to write it, and I don't see why having a relation only written through foreign table(s) wouldn't be that unacceptable.
The documentation doesn't explicitly mention this. But it would
be good to test how that works, esp if somebody tries to INSERT a row
from local server with a value specified for an IDENTITY column.
I'm still waiting for an actual answer to whether the identity syntax is supposed to be supported or not. I don't really see the point wasting time testing that scenario and a bunch of others if someone shows up tomorrow to say it's a mistake and we should be explicitly forbidding it (especially since I won't be in front of a computer for a week or so).
On 08.10.24 18:40, Ashutosh Bapat wrote: > On Tue, Oct 8, 2024 at 7:57 PM Julien Rouhaud <rjuju123@gmail.com> wrote: >> I was looking at the CREATE FOREIGN TABLE documentation to see if IDENTITY >> columns were supported, and according to the doc they're not: only GENERATED >> ALWAYS AS ( expr ) STORED is supported. >> >> However, a quick test shows that this is supported (same as serial datatype), >> and apparently behaves as expected. Looking at the grammar, CreateStmt and >> CreateForeignTableStmt actually share the same rule for the column definitions >> (OptTableElementList) so the behavior seems expected. The parse analysis code >> is also mostly shared between the two, with only a few stuff explicitly >> forbidden for foreign tables (primary keys and such). >> >> It looks like this is just an oversight in the documentation? If so, it seems >> like the CREATE and ALTER FOREIGN TABLE pages needs to be updated. The ALTER >> FOREIGN TABLE page is also at least lacking the SET / DROP EXPRESSION clauses. > > The rows inserted/udpated on the foreign server won't honour the local > IDENTITY constraint. Maybe that's why we don't want to support > identity column in foreign tables. Stored generated columns have a similar issue: The column is computed on the local server and the remote server must store it and return it. If you go and update it manually on the remote server, you break this. I think this still has use, though, depending on how you use foreign tables. If you use foreign tables as a frontend to data that is actually managed on the remote side, then generated columns and identity columns don't make much sense. But if you plan to manage the data through the foreign table, and the remote side is just dumb storage (like for sharding), then generated columns and identity columns could be useful.