Copy storage parameters on CREATE TABLE LIKE/INHERITS

Here is a patch to copy column storage parameters and reloptions on
CREATE TABLE LIKE, which I proposed at:
[HACKERS] Uncopied parameters on CREATE TABLE LIKE
http://archives.postgresql.org/message-id/20080724145954.E572.52131E4D@oss.ntt.co.jp

Column storage parameters (by ALTER COLUMN SET STORAGE) and table
storage parameters (by ALTER TABLE SET (...) ) are copied from template
tables on LIKE or parent tables on INHERITS (1. and 2. at above e-mail).
The patch does not include copying of comments (3.) for now.
It also includes new regression tests and rough documentation.

When template or parent tables have non-default settings,
they are copied into a new table automatically on CREATE TABLE.
If CREATE TABLE statement has own storage parameter definition,
they overwrites inherited settings.

Basically, this patch does nothing if the base tables have only
default settings, but there is an exception -- WITH OIDS. In 8.3,
we inherits WITH OIDS configuration on INHERITS, but not on LIKE.
I'd like to modify the behavior to inherit OIDS configuration
because inheritance would be expected in various contexts.
I did so in the patch, in that a new table will have OIDS if
at least one of its template tables in LIKE has OIDS.

Comments welcome.

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

On Wed, Jul 30, 2008 at 04:45:47PM +0900, ITAGAKI Takahiro wrote:
> Here is a patch to copy column storage parameters and reloptions on
> CREATE TABLE LIKE, which I proposed at:
> [HACKERS] Uncopied parameters on CREATE TABLE LIKE
> http://archives.postgresql.org/message-id/20080724145954.E572.52131E4D@oss.ntt.co.jp
>
> Column storage parameters (by ALTER COLUMN SET STORAGE) and table
> storage parameters (by ALTER TABLE SET (...) ) are copied from template
> tables on LIKE or parent tables on INHERITS (1. and 2. at above e-mail).
> The patch does not include copying of comments (3.) for now.
> It also includes new regression tests and rough documentation.
>
> When template or parent tables have non-default settings,
> they are copied into a new table automatically on CREATE TABLE.
> If CREATE TABLE statement has own storage parameter definition,
> they overwrites inherited settings.

I'd like to have the ability to copy these parameters, but not to have it
be automatic. Some of my clients applications commonly use CREATE TABLE
LIKE to get empty work tables to populate and update before adding the
data to the main table (which may have non-default settings). The automatic
behaviour may be undesirable for this use.

-dg

--
David Gould daveg(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.

daveg <daveg(at)sonic(dot)net> writes:
> On Wed, Jul 30, 2008 at 04:45:47PM +0900, ITAGAKI Takahiro wrote:
>> Here is a patch to copy column storage parameters and reloptions on
>> CREATE TABLE LIKE, which I proposed at:

> I'd like to have the ability to copy these parameters, but not to have it
> be automatic.

There are a couple of other things that need to be thought about:

* LIKE is intended to copy a table as a *portion* of another table;
for example it adds the source table's columns to whatever was specified
directly in CREATE TABLE, plus possibly other LIKE invocations. For
example

regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create table t2 (f2 text);
CREATE TABLE
regression=# create table t3 (f3 float, like t1, like t2);
CREATE TABLE
regression=# \d t3
Table "public.t3"
Column | Type | Modifiers
--------+------------------+-----------
f3 | double precision |
f1 | integer |
f2 | text |

I think therefore that having LIKE copy anything "global" to a table,
such as tablespace or reloptions, is fundamentally wrongheaded. What
will you do about conflicts? The same is true for inheritance cases,
since a table can inherit from multiple parents.

* LIKE INCLUDING INDEXES, on the other hand, copies indexes as units,
so it's sensible that it should (and already does) copy the tablespace
and reloptions of those indexes. This leads to a disconnect between
the table-level and index-level behavior, which I believe is what is
bothering Itagaki-san. I'm not sure what to do about it but making
them behave the same is not necessarily a good answer.

* It does make sense to copy per-column storage parameters, if you
consider those as part of the column definitions. However, I'm not
clear on how we'd "merge" storage parameters when the same column
appears from multiple parents.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> There are a couple of other things that need to be thought about:
> * LIKE is intended to copy a table as a *portion* of another table;

You're absolutely right. I just thought it's a *better default* behavior
because LIKE and INHERITS are often used in single inheritance, but
in definition they declare columns, not whole tables.
In other words, we don't have an ability of copying tables as units...

However, I think we'd better to have an ability to copy reloptions easily.
There were requests about adding a configuration parameter to modify
default fillfactor. I think reloption-cloning would be a solution about it.

How about adding a new WITH-LIKE syntax?

CREATE TABLE newtbl ( ... ) WITH (LIKE "template-table")

It is expanded to an option array as below:

SELECT 'WITH (' || array_to_string(
array_append(reloptions, 'OIDS=' || relhasoids), ',') || ')'
FROM pg_class
WHERE oid = "template-table";

> I think therefore that having LIKE copy anything "global" to a table,
> such as tablespace or reloptions, is fundamentally wrongheaded. What
> will you do about conflicts? The same is true for inheritance cases,
> since a table can inherit from multiple parents.

Currently I uses the first setting found in multiple tables
and directly specified options have the highest priority.
For example, setting are used in order of [A] -> [B] -> [C].
Conflicted parameters are ignored silently for now.

CREATE TABLE newtbl (LIKE [B], LIKE [C]) WITH ([A])

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