Uncopied parameters on CREATE TABLE LIKE

CREATE TABLE LIKE is useful to create a new partition from a template
table. We can use 3 options (INCLUDING DEFAULTS, CONSTRAINTS and INDEXES)
to copy more parameters from the template, but there are still some
uncopied parameters:

1. column storage parameters (toast options)
2. reloptions on table (on index is copied already)
3. comments

I'd like to modify CREATE TABLE LIKE to copy those parameters. Especially
1 and 2 are important when we use CREATE TABLE LIKE on partitioning because
all of the child tables should have same physical properties.

I'm thinking to modify 1 and 2 to be copied as default from a tempalte to
a new table because reloptions of indexes are automatically copied already.
I'm not sure for 3, but as of now I have a plan to add INCLUDING COMMENTS
option to CREATE TABLE LIKE.

Comments welcome.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

On Thu, 2008-07-24 at 19:09 +0900, ITAGAKI Takahiro wrote:
> CREATE TABLE LIKE is useful to create a new partition from a template
> table. We can use 3 options (INCLUDING DEFAULTS, CONSTRAINTS and INDEXES)
> to copy more parameters from the template, but there are still some
> uncopied parameters:
>
> 1. column storage parameters (toast options)
> 2. reloptions on table (on index is copied already)

Sounds good

> 3. comments

Not sure about that. If comments are just user text, then probably not.
I'm guessing you might want that for something else?

I would prefer it if you had a plan to introduce user definable
parameters, similar to custom_variable_classes. Perhaps call this
"custom_table_options". So when we load a table and it has an option we
don't recognise we ignore it if it is one of the customer_table_options.

custom_table_options will help us define special behaviours for
datatypes, indexes, replication etc that relate to the specific role and
purpose of individual tables.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> I would prefer it if you had a plan to introduce user definable
> parameters, similar to custom_variable_classes. Perhaps call this
> "custom_table_options". So when we load a table and it has an option we
> don't recognise we ignore it if it is one of the customer_table_options.

> custom_table_options will help us define special behaviours for
> datatypes, indexes, replication etc that relate to the specific role and
> purpose of individual tables.

GUC parameters that silently alter the semantics of SQL statements
should be introduced only with great trepidation, not just because
someone thought them up one day. What is the real use-case for
this bit of complication? Given the very short list of supported
reloptions right now, why would you imagine that there will ever
be such a thing as installation-local reloptions?

regards, tom lane

On Thu, 2008-07-24 at 10:30 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > I would prefer it if you had a plan to introduce user definable
> > parameters, similar to custom_variable_classes. Perhaps call this
> > "custom_table_options". So when we load a table and it has an option we
> > don't recognise we ignore it if it is one of the customer_table_options.
>
> > custom_table_options will help us define special behaviours for
> > datatypes, indexes, replication etc that relate to the specific role and
> > purpose of individual tables.
>
> GUC parameters that silently alter the semantics of SQL statements
> should be introduced only with great trepidation, not just because
> someone thought them up one day.

I agree. I don't really want to alter semantics.

> What is the real use-case for
> this bit of complication?

Reloptions are additional performance options.

> Given the very short list of supported
> reloptions right now, why would you imagine that there will ever
> be such a thing as installation-local reloptions?

There's a ton of ways to introduce installation-local code, and we
support custom_variable_classes to support that. We just need some
additional flexibility at object level also.

It's already possible via comments, so why not make it official?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On Thu, 2008-07-24 at 10:30 -0400, Tom Lane wrote:
>> Given the very short list of supported
>> reloptions right now, why would you imagine that there will ever
>> be such a thing as installation-local reloptions?

> There's a ton of ways to introduce installation-local code, and we
> support custom_variable_classes to support that. We just need some
> additional flexibility at object level also.

Anyone who's capable of introducing a new reloption is also capable of
modifying reloptions.c to accept it. There is a very specific technical
reason for the existence of custom_variable_classes, which is that the
postmaster will flat out refuse to boot if you have a "bogus" variable
in postgresql.conf, and the code that might want to accept such a
variable might not have been loaded yet. That problem doesn't apply to
reloptions. It's already the case that we ignore "bogus" values in an
already-stored reloption, and I see no reason to accept a value during
CREATE or ALTER TABLE that we don't currently believe is OK.

Now, if you're suggesting we need a plugin hook somewhere in or around
default_reloptions, that's possibly reasonable; but a GUC like you're
suggesting seems quite pointless.

regards, tom lane

On Thu, 2008-07-24 at 11:41 -0400, Tom Lane wrote:

> Now, if you're suggesting we need a plugin hook somewhere in or around
> default_reloptions, that's possibly reasonable; but a GUC like you're
> suggesting seems quite pointless.

OK, I'll have a look, or perhaps Itagaki?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Thu, 2008-07-24 at 11:41 -0400, Tom Lane wrote:
>
> > Now, if you're suggesting we need a plugin hook somewhere in or around
> > default_reloptions, that's possibly reasonable; but a GUC like you're
> > suggesting seems quite pointless.
>
> OK, I'll have a look, or perhaps Itagaki?

Yes, I agree, too. But my proposal is independent from such hooks :-)
I just suggested to copy reloptions as-is on CREATE TABLE LIKE.

I guess the first applicaitons using the extended reloptions are
user defined access methods for GiST and GIN. If those access methods
can receive reloptions, they can support their own parameters.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Is there a TODO here?

---------------------------------------------------------------------------

ITAGAKI Takahiro wrote:
>
> Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> > On Thu, 2008-07-24 at 11:41 -0400, Tom Lane wrote:
> >
> > > Now, if you're suggesting we need a plugin hook somewhere in or around
> > > default_reloptions, that's possibly reasonable; but a GUC like you're
> > > suggesting seems quite pointless.
> >
> > OK, I'll have a look, or perhaps Itagaki?
>
> Yes, I agree, too. But my proposal is independent from such hooks :-)
> I just suggested to copy reloptions as-is on CREATE TABLE LIKE.
>
> I guess the first applicaitons using the extended reloptions are
> user defined access methods for GiST and GIN. If those access methods
> can receive reloptions, they can support their own parameters.
>
> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Tue, 2008-08-12 at 13:34 -0400, Bruce Momjian wrote:
> Is there a TODO here?

Yes, three:

* create plugin API to allow extensible reloptions

* allow reloptions to be copied when using INCLUDING DEFAULTS
(if I got that right..)

* allow new option to copy comments INCLUDING COMMENTS

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Tue, 2008-08-12 at 13:34 -0400, Bruce Momjian wrote:
> > Is there a TODO here?
>
> Yes, three:

> * create plugin API to allow extensible reloptions

Yes, and some index access methods (gist and gin) should have
abilities to handle reloptions in user defined functions, too.

> * allow reloptions to be copied when using INCLUDING DEFAULTS
> (if I got that right..)

The part "INCLUDING DEFAULTS" is not correct. Reloptions are attribute
of table, not of columns. So they should not be copied on LIKE and INHERITS.
Of course, we'd better to have convenient way to copy reltoptions from
another table, but concrete syntax was not suggested in the discussion.
(and I don't have.)

> * allow new option to copy comments INCLUDING COMMENTS

Sure.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Added to TODO:

> o Add CREATE TABLE LIKE ... INCLUDING COMMENTS

> * Allow table and index WITH options to be specified via hooks, for use
> with plugins like GiST index methods

---------------------------------------------------------------------------

ITAGAKI Takahiro wrote:
>
> Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> > On Tue, 2008-08-12 at 13:34 -0400, Bruce Momjian wrote:
> > > Is there a TODO here?
> >
> > Yes, three:
>
> > * create plugin API to allow extensible reloptions
>
> Yes, and some index access methods (gist and gin) should have
> abilities to handle reloptions in user defined functions, too.
>
> > * allow reloptions to be copied when using INCLUDING DEFAULTS
> > (if I got that right..)
>
> The part "INCLUDING DEFAULTS" is not correct. Reloptions are attribute
> of table, not of columns. So they should not be copied on LIKE and INHERITS.
> Of course, we'd better to have convenient way to copy reltoptions from
> another table, but concrete syntax was not suggested in the discussion.
> (and I don't have.)
>
> > * allow new option to copy comments INCLUDING COMMENTS
>
> Sure.
>
>
> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +