Re: CREATE FOREIGN TABLE ( ... LIKE ... )

Folks,

Please find attached a patch implementing and documenting, to some
extent, $subject. I did this in aid of being able to import SQL
standard catalogs and other entities where a known example could
provide a template for a foreign table.

Should there be errhint()s, too? Should we pile up all such errors
and mention them at the end rather than simply bailing on the first
one?

TBD: regression tests.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Mon, Oct 07, 2013 at 11:16:56PM -0700, David Fetter wrote:
> Folks,
>
> Please find attached a patch implementing and documenting, to some
> extent, $subject. I did this in aid of being able to import SQL
> standard catalogs and other entities where a known example could
> provide a template for a foreign table.
>
> Should there be errhint()s, too? Should we pile up all such errors
> and mention them at the end rather than simply bailing on the first
> one?
>
> TBD: regression tests.

Now included: regression tests for disallowed LIKE options.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 10/15/2013 07:50 AM, David Fetter wrote:
> On Mon, Oct 07, 2013 at 11:16:56PM -0700, David Fetter wrote:
>> Folks,
>>
>> Please find attached a patch implementing and documenting, to some
>> extent, $subject. I did this in aid of being able to import SQL
>> standard catalogs and other entities where a known example could
>> provide a template for a foreign table.
>>
>> Should there be errhint()s, too? Should we pile up all such errors
>> and mention them at the end rather than simply bailing on the first
>> one?
>>
>> TBD: regression tests.
> Now included: regression tests for disallowed LIKE options.

I like this patch, but I don't like its implementation at all.

First of all, the documentation doesn't compile:

openjade:ref/create_foreign_table.sgml:124:17:E: end tag for "LISTITEM"
omitted, but OMITTAG NO was specified
openjade:ref/create_foreign_table.sgml:119:4: start tag was here

I fixed that, and then noticed that like_option is not explained like it
is in CREATE TABLE.

Then I got down to the description of the LIKE clause in both pages, and
I noticed the last line of CREATE TABLE, which is "Inapplicable options
(e.g., INCLUDING INDEXES from a view) are ignored.". This is
inconsistent with the behavior of this patch to throw errors for
inapplicable options.

Attached is a patch which corrects and completes the documentation
issues noted above, and also silently ignores inapplicable options. In
addition to reducing patch size, this also allows the use of INCLUDING
ALL. Because these options no longer produce errors, and that's all the
regression tests were looking for, I have removed those tests
(unfortunately leaving none).

Aside from this difference in behavior, I see no fault in this patch.

I am marking this patch as 'returned with feedback' in the commitfest app.

--
Vik

On 11/24/2013 02:03 AM, Vik Fearing wrote:
> On 10/15/2013 07:50 AM, David Fetter wrote:
>> On Mon, Oct 07, 2013 at 11:16:56PM -0700, David Fetter wrote:
>>> Folks,
>>>
>>> Please find attached a patch implementing and documenting, to some
>>> extent, $subject. I did this in aid of being able to import SQL
>>> standard catalogs and other entities where a known example could
>>> provide a template for a foreign table.
>>>
>>> Should there be errhint()s, too? Should we pile up all such errors
>>> and mention them at the end rather than simply bailing on the first
>>> one?
>>>
>>> TBD: regression tests.
>> Now included: regression tests for disallowed LIKE options.
> I like this patch, but I don't like its implementation at all.
>
> First of all, the documentation doesn't compile:
>
> openjade:ref/create_foreign_table.sgml:124:17:E: end tag for "LISTITEM"
> omitted, but OMITTAG NO was specified
> openjade:ref/create_foreign_table.sgml:119:4: start tag was here
>
>
> I fixed that, and then noticed that like_option is not explained like it
> is in CREATE TABLE.
>
> Then I got down to the description of the LIKE clause in both pages, and
> I noticed the last line of CREATE TABLE, which is "Inapplicable options
> (e.g., INCLUDING INDEXES from a view) are ignored.". This is
> inconsistent with the behavior of this patch to throw errors for
> inapplicable options.
>
> Attached is a patch which corrects and completes the documentation
> issues noted above, and also silently ignores inapplicable options. In
> addition to reducing patch size, this also allows the use of INCLUDING
> ALL. Because these options no longer produce errors, and that's all the
> regression tests were looking for, I have removed those tests
> (unfortunately leaving none).
>
> Aside from this difference in behavior, I see no fault in this patch.
>
> I am marking this patch as 'returned with feedback' in the commitfest app.
>

It looks like this patch got left behind in the previous commitfest.
What is the policy for moving it? Is it too late and will have to wait
until the next commitfest?

https://commitfest.postgresql.org/action/patch_view?id=1254

--
Vik

On Thu, Jan 16, 2014 at 01:07:50AM +0100, Vik Fearing wrote:
> On 11/24/2013 02:03 AM, Vik Fearing wrote:
> > On 10/15/2013 07:50 AM, David Fetter wrote:
> >> On Mon, Oct 07, 2013 at 11:16:56PM -0700, David Fetter wrote:
> >>> Folks,
> >>>
> >>> Please find attached a patch implementing and documenting, to some
> >>> extent, $subject. I did this in aid of being able to import SQL
> >>> standard catalogs and other entities where a known example could
> >>> provide a template for a foreign table.
> >>>
> >>> Should there be errhint()s, too? Should we pile up all such errors
> >>> and mention them at the end rather than simply bailing on the first
> >>> one?
> >>>
> >>> TBD: regression tests.
> >> Now included: regression tests for disallowed LIKE options.
> > I like this patch, but I don't like its implementation at all.
> >
> > First of all, the documentation doesn't compile:
> >
> > openjade:ref/create_foreign_table.sgml:124:17:E: end tag for "LISTITEM"
> > omitted, but OMITTAG NO was specified
> > openjade:ref/create_foreign_table.sgml:119:4: start tag was here
> >
> >
> > I fixed that, and then noticed that like_option is not explained like it
> > is in CREATE TABLE.
> >
> > Then I got down to the description of the LIKE clause in both pages, and
> > I noticed the last line of CREATE TABLE, which is "Inapplicable options
> > (e.g., INCLUDING INDEXES from a view) are ignored.". This is
> > inconsistent with the behavior of this patch to throw errors for
> > inapplicable options.
> >
> > Attached is a patch which corrects and completes the documentation
> > issues noted above, and also silently ignores inapplicable options. In
> > addition to reducing patch size, this also allows the use of INCLUDING
> > ALL. Because these options no longer produce errors, and that's all the
> > regression tests were looking for, I have removed those tests
> > (unfortunately leaving none).
> >
> > Aside from this difference in behavior, I see no fault in this patch.
> >
> > I am marking this patch as 'returned with feedback' in the commitfest app.
> >
>
> It looks like this patch got left behind in the previous commitfest.
> What is the policy for moving it? Is it too late and will have to wait
> until the next commitfest?
>
> https://commitfest.postgresql.org/action/patch_view?id=1254

I think we should still be OK putting it in the current one.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> writes:
>> I am marking this patch as 'returned with feedback' in the commitfest app.

> It looks like this patch got left behind in the previous commitfest.
> What is the policy for moving it? Is it too late and will have to wait
> until the next commitfest?

> https://commitfest.postgresql.org/action/patch_view?id=1254

I think you were in error to mark it "returned with feedback", as that
caused everyone to stop paying attention to it in that commitfest.
(And David dropped the ball too, as he should have done something to
bring it back from that state, if it was committable or nearly so.)

I see no reason why you shouldn't move it to the new fest; perhaps
mark it as waiting on author, since really it's his responsibility
to take the next step, ie comment on your version of the patch.

regards, tom lane

On 01/16/2014 01:21 AM, Tom Lane wrote:
> Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> writes:
>>> I am marking this patch as 'returned with feedback' in the commitfest app.
>> It looks like this patch got left behind in the previous commitfest.
>> What is the policy for moving it? Is it too late and will have to wait
>> until the next commitfest?
>> https://commitfest.postgresql.org/action/patch_view?id=1254
> I think you were in error to mark it "returned with feedback", as that
> caused everyone to stop paying attention to it in that commitfest.
> (And David dropped the ball too, as he should have done something to
> bring it back from that state, if it was committable or nearly so.)

I see. Sorry about that.

> I see no reason why you shouldn't move it to the new fest; perhaps
> mark it as waiting on author, since really it's his responsibility
> to take the next step, ie comment on your version of the patch.

Done.

--
Vik

On Sun, Nov 24, 2013 at 02:03:18AM +0100, Vik Fearing wrote:
> On 10/15/2013 07:50 AM, David Fetter wrote:
> > On Mon, Oct 07, 2013 at 11:16:56PM -0700, David Fetter wrote:
> >> Folks,
> >>
> >> Please find attached a patch implementing and documenting, to some
> >> extent, $subject. I did this in aid of being able to import SQL
> >> standard catalogs and other entities where a known example could
> >> provide a template for a foreign table.
> >>
> >> Should there be errhint()s, too? Should we pile up all such errors
> >> and mention them at the end rather than simply bailing on the first
> >> one?
> >>
> >> TBD: regression tests.
> > Now included: regression tests for disallowed LIKE options.
>
> I like this patch, but I don't like its implementation at all.
>
> First of all, the documentation doesn't compile:
>
> openjade:ref/create_foreign_table.sgml:124:17:E: end tag for "LISTITEM"
> omitted, but OMITTAG NO was specified
> openjade:ref/create_foreign_table.sgml:119:4: start tag was here

Fixed.

> I fixed that, and then noticed that like_option is not explained like it
> is in CREATE TABLE.

Also fixed.

> Then I got down to the description of the LIKE clause in both pages, and
> I noticed the last line of CREATE TABLE, which is "Inapplicable options
> (e.g., INCLUDING INDEXES from a view) are ignored.". This is
> inconsistent with the behavior of this patch to throw errors for
> inapplicable options.

Fixed.

Please find attached the next rev :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 01/25/2014 06:25 AM, David Fetter wrote:
>> I like this patch, but I don't like its implementation at all.
>> >
>> > First of all, the documentation doesn't compile:
>> >
>> > openjade:ref/create_foreign_table.sgml:124:17:E: end tag for "LISTITEM"
>> > omitted, but OMITTAG NO was specified
>> > openjade:ref/create_foreign_table.sgml:119:4: start tag was here
> Fixed.
>
>> > I fixed that, and then noticed that like_option is not explained like it
>> > is in CREATE TABLE.
> Also fixed.
>
>> > Then I got down to the description of the LIKE clause in both pages, and
>> > I noticed the last line of CREATE TABLE, which is "Inapplicable options
>> > (e.g., INCLUDING INDEXES from a view) are ignored.". This is
>> > inconsistent with the behavior of this patch to throw errors for
>> > inapplicable options.
> Fixed.
>
> Please find attached the next rev :)

This version looks committable to me, so I am marking it as such.

--
Vik

On 2014-01-31 18:16:18 +0100, Vik Fearing wrote:
> On 01/25/2014 06:25 AM, David Fetter wrote:
> > Please find attached the next rev :)
>
> This version looks committable to me, so I am marking it as such.

This doesn't contain a single regression test, I don't see how that's
ok. Marking as waiting on author.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sat, Feb 15, 2014 at 03:14:03PM +0100, Andres Freund wrote:
> On 2014-01-31 18:16:18 +0100, Vik Fearing wrote:
> > On 01/25/2014 06:25 AM, David Fetter wrote:
> > > Please find attached the next rev :)
> >
> > This version looks committable to me, so I am marking it as such.
>
> This doesn't contain a single regression test, I don't see how that's
> ok. Marking as waiting on author.

It now contains regression tests. Re-marking.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 2014-02-16 20:27:09 -0800, David Fetter wrote:
> On Sat, Feb 15, 2014 at 03:14:03PM +0100, Andres Freund wrote:
> > On 2014-01-31 18:16:18 +0100, Vik Fearing wrote:
> > > On 01/25/2014 06:25 AM, David Fetter wrote:
> > > > Please find attached the next rev :)
> > >
> > > This version looks committable to me, so I am marking it as such.
> >
> > This doesn't contain a single regression test, I don't see how that's
> > ok. Marking as waiting on author.
>
> It now contains regression tests. Re-marking.

I don't think this really has gone above Needs Review yet.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Feb 17, 2014 at 6:28 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> I don't think this really has gone above Needs Review yet.
I am not sure that this remark makes the review of this patch much
progressing :(

By the way, I spent some time looking at it and here are some comments:
- Regression tests added are too sensitive with the other tests. For
example by not dropping tables or creating new tables on another tests
run before foreign_data you would need to update the output of this
test as well, something rather unfriendly.
- Regression coverage is limited (there is nothing done for comments
and default expressions)
- regression tests are added in postgres_fdw. This should be perhaps
the target of another patch so I removed them for now as this is only
a core feature (if I am wrong here don't hesitate). Same remark about
information_schema though, those tests are too fragile as they are.
- Documentation had some issues IMO:
-- A bracket was missing before "<replaceable class="PARAMETER">column_name"...
-- like_option should be clear about what it supports or not, more
precisely that it supports only default expressions and comments
-- some typos and formatting inconsistencies found
- In the case of CREATE TABLE, like_option is bypassed based on the
nature of the object linked, and not based on the nature of the object
created, so for CREATE FOREIGN TABLE, using this argument, I do not
think that we should simply ignore the options not directly supported
but return an error or a warning at least to user (attached patch
returns an ERROR). Documentation needs to reflect that precisely to
let the user know what can be and cannot be done.

After testing the patch, well it does what it is aimed for and it
works. It is somewhat unfortunate that we cannot enforce the name of
columns hidden behind LIKE directly with CREATE, but this would result
in some kludging in the code. It can as well be done simply with ALTER
FOREIGN TABLE.

All those comments result in the patch attached, which I think is in a
state close to committable, so I am marking it as "ready for
committer" (feel free to scream at me if you do not think so). Note
that the patch attached is not using context diffs but git diffs
(really I tried!) because of filterdiff that skipped a block of code
in parse_utilcmd.c.
Regards,
--
Michael

On 2014-02-17 23:07:45 +0900, Michael Paquier wrote:
> On Mon, Feb 17, 2014 at 6:28 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > I don't think this really has gone above Needs Review yet.
> I am not sure that this remark makes the review of this patch much
> progressing :(

Uh. What should I then say if a patch is marked as ready for committer
by the author, after it previously had been marked such when it clearly
wasn't? Your review just seems to confirm that it wasn't ready?
If the patch is isn't marked "needs review" in the CF it's less likely
to get timely review. And when a committer looks at the patch it'll just
be determined at not being ready again, making it less likely to get
committed.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2014-02-17 23:07:45 +0900, Michael Paquier wrote:
> On Mon, Feb 17, 2014 at 6:28 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > I don't think this really has gone above Needs Review yet.
> I am not sure that this remark makes the review of this patch much
> progressing :(
>
> By the way, I spent some time looking at it and here are some
> comments:

David just pinged me and tricked me into having a quick look :)

Unless I miss something this possibly allows column definition to slip
by that shouldn't because normally all fdw column definitions are passed
through transformColumnDefinition() which does some checks, but the
copied ones aren't.
I haven't looked long enough to see whether that's currently
problematic, but even if not, it's sure a trap waiting to spring.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, Feb 18, 2014 at 7:22 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-02-17 23:07:45 +0900, Michael Paquier wrote:
>> On Mon, Feb 17, 2014 at 6:28 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> > I don't think this really has gone above Needs Review yet.
>> I am not sure that this remark makes the review of this patch much
>> progressing :(
>>
>> By the way, I spent some time looking at it and here are some
>> comments:
>
> David just pinged me and tricked me into having a quick look :)
>
> Unless I miss something this possibly allows column definition to slip
> by that shouldn't because normally all fdw column definitions are passed
> through transformColumnDefinition() which does some checks, but the
> copied ones aren't.
> I haven't looked long enough to see whether that's currently
> problematic, but even if not, it's sure a trap waiting to spring.
transformColumnDefinition contains checks about serial and constraints
mainly. The only thing that could be problematic IMO is the process
done exclusively for foreign tables which is the creation of some
ALTER FOREIGN TABLE ALTER COLUMN commands when per-column options are
detected, something that is not passed to a like'd table with this
patch. This may meritate a comment in the code.
Actually after more thinking I think that it would make sense to have
another INCLUDING/EXCLUDING option for foreign tables: OPTIONS to pass
the column options when link is done from another foreign table. This
should be another patch though.
Regards,
--
Michael

On 2014-02-18 08:35:35 +0900, Michael Paquier wrote:
> On Tue, Feb 18, 2014 at 7:22 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On 2014-02-17 23:07:45 +0900, Michael Paquier wrote:
> >> On Mon, Feb 17, 2014 at 6:28 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> >> > I don't think this really has gone above Needs Review yet.
> >> I am not sure that this remark makes the review of this patch much
> >> progressing :(
> >>
> >> By the way, I spent some time looking at it and here are some
> >> comments:
> >
> > David just pinged me and tricked me into having a quick look :)
> >
> > Unless I miss something this possibly allows column definition to slip
> > by that shouldn't because normally all fdw column definitions are passed
> > through transformColumnDefinition() which does some checks, but the
> > copied ones aren't.
> > I haven't looked long enough to see whether that's currently
> > problematic, but even if not, it's sure a trap waiting to spring.

> transformColumnDefinition contains checks about serial and constraints
> mainly. The only thing that could be problematic IMO is the process
> done exclusively for foreign tables which is the creation of some
> ALTER FOREIGN TABLE ALTER COLUMN commands when per-column options are
> detected, something that is not passed to a like'd table with this
> patch. This may meritate a comment in the code.

As I said, I am not all that concerned that it's a big problem today,
but imo it's an accident waiting to happen.

I rather wonder if the code shouln't just ensure it's running
transformTableLikeClause() before transformColumnDefinition() by doing
it in a separate loop.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> On Tue, Feb 18, 2014 at 7:22 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> Unless I miss something this possibly allows column definition to slip
>> by that shouldn't because normally all fdw column definitions are passed
>> through transformColumnDefinition() which does some checks, but the
>> copied ones aren't.
>> I haven't looked long enough to see whether that's currently
>> problematic, but even if not, it's sure a trap waiting to spring.

> transformColumnDefinition contains checks about serial and constraints
> mainly. The only thing that could be problematic IMO is the process
> done exclusively for foreign tables which is the creation of some
> ALTER FOREIGN TABLE ALTER COLUMN commands when per-column options are
> detected, something that is not passed to a like'd table with this
> patch. This may meritate a comment in the code.
> Actually after more thinking I think that it would make sense to have
> another INCLUDING/EXCLUDING option for foreign tables: OPTIONS to pass
> the column options when link is done from another foreign table. This
> should be another patch though.

ISTM this is because the proposed feature is wrongheaded. The basic
concept of CREATE TABLE LIKE is that you're copying properties from
another object of the same type. You might or might not want every
property, but there's no question of whether you *could* copy every
property. In contrast, what this is proposing to do is copy properties
from (what might be) a plain table to a foreign table, and those things
aren't even remotely the same kind of object.

It would make sense to me to restrict LIKE to copy from another foreign
table, and then there would be a different set of INCLUDING/EXCLUDING
options that would be relevant (options yes, indexes no, for example).

In any case, I agree with Andres' concern: whether or not it's a bug
currently that this bypasses some of the normal processing, it's a hazard
that can be expected to bite us someday.

regards, tom lane

On 2014-04-05 11:46:16 -0400, Tom Lane wrote:
> ISTM this is because the proposed feature is wrongheaded. The basic
> concept of CREATE TABLE LIKE is that you're copying properties from
> another object of the same type. You might or might not want every
> property, but there's no question of whether you *could* copy every
> property. In contrast, what this is proposing to do is copy properties
> from (what might be) a plain table to a foreign table, and those things
> aren't even remotely the same kind of object.
>
> It would make sense to me to restrict LIKE to copy from another foreign
> table, and then there would be a different set of INCLUDING/EXCLUDING
> options that would be relevant (options yes, indexes no, for example).

I actually think it's quite useful to create a foreign table that's the
same shape as a local table. And the patches approach of refusing to
copy thinks that aren't supported sounds sane to me.
Consider e.g. moving off older partitioned data off to an archiving
server. New local partitions are often created using CREATE TABLE LIKE,
but that's not possible for the foreign ones.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, Apr 8, 2014 at 5:24 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-04-05 11:46:16 -0400, Tom Lane wrote:
>> ISTM this is because the proposed feature is wrongheaded. The basic
>> concept of CREATE TABLE LIKE is that you're copying properties from
>> another object of the same type. You might or might not want every
>> property, but there's no question of whether you *could* copy every
>> property. In contrast, what this is proposing to do is copy properties
>> from (what might be) a plain table to a foreign table, and those things
>> aren't even remotely the same kind of object.
>>
>> It would make sense to me to restrict LIKE to copy from another foreign
>> table, and then there would be a different set of INCLUDING/EXCLUDING
>> options that would be relevant (options yes, indexes no, for example).
>
> I actually think it's quite useful to create a foreign table that's the
> same shape as a local table. And the patches approach of refusing to
> copy thinks that aren't supported sounds sane to me.
This could be improved as well: it would be useful to be able to copy
the column options of another foreign table.

> Consider e.g. moving off older partitioned data off to an archiving
> server. New local partitions are often created using CREATE TABLE LIKE,
> but that's not possible for the foreign ones.
Definitely a use case.
--
Michael

On Mon, Apr 7, 2014 at 4:24 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-04-05 11:46:16 -0400, Tom Lane wrote:
>> ISTM this is because the proposed feature is wrongheaded. The basic
>> concept of CREATE TABLE LIKE is that you're copying properties from
>> another object of the same type. You might or might not want every
>> property, but there's no question of whether you *could* copy every
>> property. In contrast, what this is proposing to do is copy properties
>> from (what might be) a plain table to a foreign table, and those things
>> aren't even remotely the same kind of object.
>>
>> It would make sense to me to restrict LIKE to copy from another foreign
>> table, and then there would be a different set of INCLUDING/EXCLUDING
>> options that would be relevant (options yes, indexes no, for example).
>
> I actually think it's quite useful to create a foreign table that's the
> same shape as a local table. And the patches approach of refusing to
> copy thinks that aren't supported sounds sane to me.
> Consider e.g. moving off older partitioned data off to an archiving
> server. New local partitions are often created using CREATE TABLE LIKE,
> but that's not possible for the foreign ones.

+1.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

(2014/04/08 9:26), Michael Paquier wrote:
> On Tue, Apr 8, 2014 at 5:24 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> On 2014-04-05 11:46:16 -0400, Tom Lane wrote:
>>> ISTM this is because the proposed feature is wrongheaded. The basic
>>> concept of CREATE TABLE LIKE is that you're copying properties from
>>> another object of the same type. You might or might not want every
>>> property, but there's no question of whether you *could* copy every
>>> property. In contrast, what this is proposing to do is copy properties
>>> from (what might be) a plain table to a foreign table, and those things
>>> aren't even remotely the same kind of object.
>>>
>>> It would make sense to me to restrict LIKE to copy from another foreign
>>> table, and then there would be a different set of INCLUDING/EXCLUDING
>>> options that would be relevant (options yes, indexes no, for example).
>>
>> I actually think it's quite useful to create a foreign table that's the
>> same shape as a local table. And the patches approach of refusing to
>> copy thinks that aren't supported sounds sane to me.
> This could be improved as well: it would be useful to be able to copy
> the column options of another foreign table.

Yes, I think so, too. But to think of validating generic column/table
options, I think we would probably need to restrict LIKE to copy from
another foreign table maybe using the same FDW. So, I'd like to vote
for Tom's idea.

Thanks,

Best regards,
Etsuro Fujita