FOREIGN TABLE and IDENTITY columns

Started by Julien Rouhaudover 1 year ago6 messages

rjuju123@gmail.com

over 1 year ago

1 attachment(s)

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.

I'm attaching an initial patch for the missing parts that I could spot after a
quick look.

Attachments:

cft_doc.difftext/plain; charset=us-asciiDownload

diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml
index 3cb6f08fcf..1672f548ef 100644
--- a/doc/src/sgml/ref/alter_foreign_table.sgml
+++ b/doc/src/sgml/ref/alter_foreign_table.sgml
@@ -38,6 +38,11 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceab
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } NOT NULL
+    ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET EXPRESSION AS ( <replaceable class="parameter">expression</replaceable> )
+    ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP EXPRESSION [ IF EXISTS ]
+    ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]
+    ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...]
+    ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP IDENTITY [ IF EXISTS ]
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STATISTICS <replaceable class="parameter">integer</replaceable>
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] )
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index dc4b907599..9c6851f47d 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -48,6 +48,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
   CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
   DEFAULT <replaceable>default_expr</replaceable> |
   GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED }
+  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
 
 <phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
 
@@ -309,6 +310,52 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]</literal></term>
+    <listitem>
+     <para>
+      This clause creates the column as an <firstterm>identity
+      column</firstterm>.  It will have an implicit sequence attached to it
+      and the column in new rows will automatically have values from the
+      sequence assigned to it.  (The computed value will be presented
+      to the foreign-data wrapper for storage and must be returned on
+      reading.)
+      Such a column is implicitly <literal>NOT NULL</literal>.
+     </para>
+
+     <para>
+      The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal>
+      determine how explicitly user-specified values are handled in
+      <command>INSERT</command> and <command>UPDATE</command> commands.
+     </para>
+
+     <para>
+      In an <command>INSERT</command> command, if <literal>ALWAYS</literal> is
+      selected, a user-specified value is only accepted if the
+      <command>INSERT</command> statement specifies <literal>OVERRIDING SYSTEM
+      VALUE</literal>.  If <literal>BY DEFAULT</literal> is selected, then the
+      user-specified value takes precedence.  See <xref linkend="sql-insert"/>
+      for details.  (In the <command>COPY</command> command, user-specified
+      values are always used regardless of this setting.)
+     </para>
+
+     <para>
+      In an <command>UPDATE</command> command, if <literal>ALWAYS</literal> is
+      selected, any update of the column to any value other than
+      <literal>DEFAULT</literal> will be rejected.  If <literal>BY
+      DEFAULT</literal> is selected, the column can be updated normally.
+      (There is no <literal>OVERRIDING</literal> clause for the
+      <command>UPDATE</command> command.)
+     </para>
+
+     <para>
+      The optional <replaceable>sequence_options</replaceable> clause can be
+      used to override the options of the sequence.
+      See <xref linkend="sql-createsequence"/> for details.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ...] )</literal></term>
     <listitem>

Ashutosh Bapat

ashutosh.bapat.oss@gmail.com

over 1 year ago

In reply to: Julien Rouhaud (#1)

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

Julien Rouhaud

rjuju123@gmail.com

over 1 year ago

In reply to: Ashutosh Bapat (#2)

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?

Ashutosh Bapat

ashutosh.bapat.oss@gmail.com

over 1 year ago

In reply to: Julien Rouhaud (#3)

Re: FOREIGN TABLE and IDENTITY columns

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

Julien Rouhaud

rjuju123@gmail.com

over 1 year ago

In reply to: Ashutosh Bapat (#4)

Re: FOREIGN TABLE and IDENTITY columns

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).

Show quoted text

Peter Eisentraut

peter@eisentraut.org

about 1 year ago

In reply to: Ashutosh Bapat (#2)

Re: FOREIGN TABLE and IDENTITY columns

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.