Re: FOREIGN TABLE and IDENTITY columns

On Wed, Oct 9, 2024 at 4:22 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> On Wed, Oct 9, 2024 at 12:40 AM Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> >
> > On Tue, Oct 8, 2024 at 7:57 PM Julien Rouhaud <rjuju123(at)gmail(dot)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