Re: FOREIGN TABLE and IDENTITY columns

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?