About the CREATE TABLE LIKE indexes vs constraints issue

In connection with the operator-exclusion patch,
Brendan Jurd <direvus(at)gmail(dot)com> wrote:
> * What to do about INCLUDING INDEXES EXCLUDING CONSTRAINTS --
> Postgres gets this wrong for unique indexes currently. Should we
> persist with the existing behaviour or fix it as part of this patch?
> My personal feeling was +1 for fixing it in this patch.

I'm not sure whether we came to a conclusion about this point.
The current code behavior is that INCLUDING INDEXES copies both
plain indexes and index-based constraints, while INCLUDING/EXCLUDING
CONSTRAINTS controls whether CHECK constraints are copied. Brendan
argues that this is wrong, and I agree that it's not what the syntax
would appear to mean. I think the most natural reading of the syntax
would be "INCLUDING INDEXES means to include everything you made
with CREATE INDEX syntax, while INCLUDING CONSTRAINTS means to
include everything you made with CONSTRAINT syntax". However, it's
unclear whether that's so much better or more useful as to justify
a compatibility break. Arguably, lumping all indexes together is
the most useful behavior in practice.

Another spanner in the works is that foreign-key constraints aren't
copied. Not to mention that simple NOT NULL constraints are always
copied independently of these options. So the whole thing fails to
satisfy the POLA by pretty much any standard.

Do we want to try to clean this up, or should we leave it alone on
backwards-compatibility grounds?

regards, tom lane

On Wed, 2009-12-23 at 14:55 -0500, Tom Lane wrote:
> I think the most natural reading of the syntax
> would be "INCLUDING INDEXES means to include everything you made
> with CREATE INDEX syntax, while INCLUDING CONSTRAINTS means to
> include everything you made with CONSTRAINT syntax".

Agreed.

> However, it's
> unclear whether that's so much better or more useful as to justify
> a compatibility break. Arguably, lumping all indexes together is
> the most useful behavior in practice.

Probably so. LIKE is shorthand anyway, and I think the most useful thing
in practice would be to get everything (indexes and constraints).

> Another spanner in the works is that foreign-key constraints aren't
> copied.

I see why FKs aren't always copied:

Let's say you have two tables, one with a FK referencing the other. If
you want to create two parallel tables that are like the two originals,
you might want one new table to reference the other new table, rather
than the original.

But if you say "including constraints", the POLA would probably require
copying the FKs, as well.

> Do we want to try to clean this up, or should we leave it alone on
> backwards-compatibility grounds?

I don't have a strong opinion.

It's DDL time only, so the only breakage I can imagine is someone's
version-controlled DDL. I would think that most of the people who use
LIKE are probably doing it as a one-off. So it wouldn't be the end of
the world to break compatibility here.

Honestly, I've never used LIKE in a table definition aside from one-off
design experiments. For that kind of thing, what I want is to just get
everything (except perhaps FKs if the above situation applies), and I
adjust it from there. Are there people out there who use LIKE in their
production schema files?

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> Honestly, I've never used LIKE in a table definition aside from one-off
> design experiments. For that kind of thing, what I want is to just get
> everything (except perhaps FKs if the above situation applies), and I
> adjust it from there. Are there people out there who use LIKE in their
> production schema files?

The only concrete application I've heard of for LIKE is to create
something that is going to be part of a partitioned table later.
That is, instead of

create table partition23 () inherits (partition_parent);

you do

create table partition23 (like partition_parent);
...
alter table partition23 inherit partition_parent;

The advantage of the latter is you can tweak the new partition
(eg, load data into it) before you make it a live part of the
partition set.

So in this context it's entirely likely that people would be using
LIKE in scripted procedures. However, it's not immediately obvious
to me whether the current definitions of the LIKE options are well
suited to this application. The lack of any support for copying
foreign keys seems a bit questionable for instance. Now if you plan
a bulk load before taking the partition live, maybe you'd not want
to enable foreign key checks till after --- but the same would hold
for indexes, so why is there an option to copy one but not the other?

regards, tom lane

Jeff Davis wrote:

...
> Honestly, I've never used LIKE in a table definition aside from one-off

> design experiments. For that kind of thing, what I want is to just get
> everything (except perhaps FKs if the above situation applies), and I
> adjust it from there. Are there people out there who use LIKE in their
> production schema files?

I for one never have either. If I needed such a thing I'd go through the steps
of using pg_dump to get the structure, edit the result to get my new table's
name and structure, and then use that SQL to create the table and add
it to the CVS so I would have a record of it if I ever needed to refer to
it later (when did I do this?) or if I needed to recreate the scheme because
of a crash or whatever. Too easy to make new table with LIKE and not
have the proper record of how to recreate the changes, IMHO. (Of course
you could capture the SQL w/ history and use that.)

Greg W.

Hi, sorry for posting style,

--
dim

Le 23 déc. 2009 à 23:58, Jeff Davis <pgsql(at)j-davis(dot)com> a écrit :
> Honestly, I've never used LIKE in a table definition aside from one-
> off
> design experiments. For that kind of thing, what I want is to just get
> everything (except perhaps FKs if the above situation applies), and I
> adjust it from there. Are there people out there who use LIKE in their
> production schema files?

I do use LIKE in scripts for adding providers of federated data. In
some cases you want to INHERIT, in some other you want to move
incoming data to another set of tables.

Regards,