Thread: Proposal to CREATE FOREIGN TABLE LIKE

On 2025-Feb-01, Zhang Mingli wrote:

> For example, we use kafka_fdw to produce and consume data from a Kafka
> server.  In our scenario, we sometimes need to write records from a
> local table into Kafka. Here’s a brief outline of our process:
> 
> 1. We already have a wide table, local_wide_table in Postgres.
> 2. We need to create a foreign table, foreign_table, with the same
> definition as local_wide_table.
> 3. Insert records into foreign_table by selecting
> from local_wide_table with the some quals.
> 
> In step 2, we currently have to manually create the foreign table
> using CREATE FOREIGN TABLE and copy the column definitions one by one.

Eh yeah, I guess for this use case it makes sense to allow a LIKE clause
on CREATE FOREIGN TABLE.  Were you going to submit a patch?

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)

On 2025-Feb-06, Zhang Mingli wrote:

> Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
> foreign tables based on the column definitions, constraints of
> existing source tables.
> This feature mirrors the behavior of CREATE TABLE LIKE, but ignores
> inapplicable options such as INCLUDING INDEXES and INCLUDING
> COMPRESSION for foreign tables.

I think it'd be better to throw errors if they are given -- but
INCLUDING ALL should be made to work in a different way than today so
that it doesn't raise errors uselessly.  Right now it works by setting
all the bits in the value, um.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

On 2025-Feb-06, Álvaro Herrera wrote:

> On 2025-Feb-06, Zhang Mingli wrote:
> 
> > Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
> > foreign tables based on the column definitions, constraints of
> > existing source tables.
> > This feature mirrors the behavior of CREATE TABLE LIKE, but ignores
> > inapplicable options such as INCLUDING INDEXES and INCLUDING
> > COMPRESSION for foreign tables.
> 
> I think it'd be better to throw errors if they are given -- but
> INCLUDING ALL should be made to work in a different way than today so
> that it doesn't raise errors uselessly.  Right now it works by setting
> all the bits in the value, um.

Ah, but our fine manual already says

  The LIKE clause can also be used to copy column definitions from views,
  foreign tables, or composite types. Inapplicable options (e.g.,
  INCLUDING INDEXES from a view) are ignored.

so what you implemented seems to be okay from that POV.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)

> Patch V2 addressed the comments.

Overall this LGTM.

I still see a "no real storage" in v2 that should be removed
from the documentation.

+ Foreign tables have no real storage in PostgreSQL.
+ Inapplicable options: <literal>INCLUDING INDEXES</literal>,
<literal>INCLUDING STORAGE</literal>,

I think the test coverage to check for the negative conditions only is
enough.

Regards,

Sami

On Wed, Feb 19, 2025 at 03:40:56PM +0800, Zhang Mingli wrote:
> Thanks for the correction.

No problem.  If you see anything else that you think is misleading,
please feel free to ping me on this thread.
--
Michael