Re: Add CREATE SCHEMA ... LIKE support

Thank you for all the comments, they were very helpful!

I'll address all previous comments on this email.

> In a scenario where a parent table and the partitioned tables live in
> different schemas, creating a schema based on the schema that contains
> only the partitions arguably generates useless tables.
> ...
> I'm not saying it's wrong, but perhaps you should consider ERROR/WARN if
> trying to copy a schema with "orphan" partitions
>

I've fixed this by adding a WARNING message and skipping the table
partition.

On this new version of the patch I've also added support for FK's and it
has the same behaviour, if a FK reference a table outside from the
source schema the FK will not be created.

I think that any object that reference an object outside of the source
schema should be skipped. It fells more safe to me to avoid sharing the
same object by multiple schemas.

> The same applies for creating schema that contains only the parent table
> ...
> Even if parent and children live in the same schema, they become
> detached in the new copy -- I'd argue that this one is a bug.
>

I've added support for partitioned tables on this new version. Although
this works for the test cases that I've added on create_schema.sql (make
check is also happy) I'm not sure if it's the best way to do it. On
getPartitionBoundSpec() I get PartitionBoundSpec from a given partitiond
oid as a string and then call stringToNode to actually generate a
PartitionBoundSpec. The problem IIUC is that fields like lowerdatums,
upperdatums and listdatums are already transformed so when
transformPartitionBoundValue() call transformExpr() the Node* will
already be transformed (e.g PartitionRangeDatum vs T_A_Const) and it
will fail on switch (nodeTag(Node))

I fixed this by adding a is_transformed field on PartitionBoundSpec and
set it to true on getPartitionBoundSpec(). Hash partition bounds only
have int values (modulus and remainder) and it shows to be required to
still do the transformation for this case.

When I was writing this email I've noticed that PartitionSpec need the
same workaround for partition keys that are expressions. I want to study
more the code to understand how we could properly fix this. Any idea is
welcome.

> Comments are also being ignored, but I guess it was already mentioned
> upthread:
>

It should work... I'll investigate this.

> I also just noticed that UNLOGGED tables are cloned as normal tables:
> ...
> Adding this to collectSchemaTablesLike in schemacmds.c could do the trick:
> newRelation->relpersistence = classForm->relpersistence;
>

I've also fixed this.

> > I think the approach will fail at some point if you keep using CREATE
> > TABLE LIKE, because so many things will be incomplete or at least
> > strange, that you'll have to redo many of them.
> > Sequencevalues will be shared with the old table.
> > Constraints/Indexes names will have to be renamed.
> > Partitioned tables will have to be attached.
> > Foreign Keys are not created either.
>
> Quite right. Either we draw a clear line here, or it will be a tough nut
> to crack.
>

The idea is not to use CREATE TABLE LIKE for all scenarios. Sorry if I
was not clear about to mention this.

As CreateSchemaCommand() use ProcessUtility() to process the
schema_element's of CREATE SCHEMA the overall idea of adding support for
LIKE was to generate a bunch of parsenodes that re-create the elements
of the source schema into the new schema and append it on this list of
schema_elements.

I think that using CREATE TABLE LIKE is the simple way to do this for
normal tables without complex data types but it certanately will not
work for all kind of objects that a schema can have. For example, on FK
I've generated a AlterTableStmt to include a FK on a table after it is
created. For domains for another case I think it would need to generate
a CreateDomainStmt.

I also want to mention that I don't think that we would be able to
properly re-created 100% all objects from the source schema into the new
schema. Some objects will be hard to copy and can still generate bougy
objects like functions for example as David mention on [1] (we can
support some kind of functions but some others will be hard).

Another issue is to handle complex relations like the following:
- Function A returns a boolean type
- Custom DOMAIN type call function A on CHECK constraint
- Function B return/use a type of custom DOMAIN

What we should create first? It can have functions that depends on
domains, so domains should be created first, but it can also have
domains that depends on functions, so functions would need to be created
first. This would be trick to fix.

But I still think that we could have support for some kind of objects. I
think that the following would be a good start point:
- tables
- partitioned tables
- indexes
- fks
- sequences
- types

For more complex scenarios we could document the limitation and perhaps
try to including WARNING's messages to mention these limitations when
re-creating a schema.

Overall, on this new patch version I've fixed/added the following:
- Index names are preserved on the new schema
- FK's are created on the new schema
- Support for partitioned tables
- More documentation (all documentation is on 0004)

Know issues remaining:
- Comment's are not being created
- Sequence of SERIAL columns are shared
- Workaround with is_transformed field
- Certanately a bunch of other things...

Thgouths?

[1] https://www.postgresql.org/message-id/CAKFQuwZFAkGMoV0fLtf%2BHJL4_cwivz0Vqfk1r5SemUZvNNfxkg%40mail.gmail.com

--
Matheus Alcantara
EDB: https://www.enterprisedb.com