Re: FOREIGN TABLE and IDENTITY columns

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.

--
Best Wishes,
Ashutosh Bapat