Thread: SQL99 CREATE TABLE ... (LIKE parent_table)
Quick patch to add the subject. Restructures all inheritance to consist of a few flags to indicate which structures we want to inherit (structure only, constraints other than NOT NULL -- check constraints only at this time, and defaults). It's slightly better than CREATE TABLE AS due to NOT NULL being carried over. Would there be any objections to adding the SQL 2k3 addition INCLUDING DEFAULTS. Okay, how about extending SQL 2k3 and adding INCLUDING CHECK CONSTRAINTS to allow inheritance of the check constraint structures? MergeAttributes does all of the work, we simply need to turn it on in the parser (gram.y). Yes, I wish to add an option to allow check constraints to be carried over despite the below note from Sect. 11.3: NOTE 234 <column constraint>s, except for NOT NULL, are not included in NCi; <column constraint definition>s are effectively transformed to <table constraint definition>s and are thereby also excluded. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Rod Taylor <rbt@rbt.ca> writes:
> Quick patch to add the subject. Restructures all inheritance to consist
> of a few flags to indicate which structures we want to inherit
I think overloading the inheritance mechanism to serve this purpose is a
bad idea. It complicates and confuses a significant amount of code
that's already pretty confusing (no, I don't believe you found it all).
It'd be better to have a localized bit of code that processes LIKE by
generating a ColumnDef schema list.
> Yes, I wish to add an option to allow check
> constraints to be carried over despite the below note from Sect. 11.3:
> NOTE 234 <column constraint>s, except for NOT NULL, are not included
> in NCi; <column constraint definition>s are effectively transformed to
> <table constraint definition>s and are thereby also excluded.
Why is it a good idea to ignore the express requirement of the spec?
(I'm not saying it's not a good idea --- that note seems a little odd
to me too --- but presumably the spec writers had some reasons for
doing it that way. I'd like some justification for not doing it their
way.)
regards, tom lane
On Mon, 2003-05-12 at 10:59, Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: > > Quick patch to add the subject. Restructures all inheritance to consist > > of a few flags to indicate which structures we want to inherit > > I think overloading the inheritance mechanism to serve this purpose is a > bad idea. It complicates and confuses a significant amount of code > that's already pretty confusing (no, I don't believe you found it all). > It'd be better to have a localized bit of code that processes LIKE by > generating a ColumnDef schema list. That can be done without much of an issue. Much of the code in MergeAttributes will be duplicated in this new routine. I'll conduct the transform of LIKE to ColumnDef list within the transformCreateStmt where the rest of the transformations take place. > > Yes, I wish to add an option to allow check > > constraints to be carried over despite the below note from Sect. 11.3: > > > NOTE 234 <column constraint>s, except for NOT NULL, are not included > > in NCi; <column constraint definition>s are effectively transformed to > > <table constraint definition>s and are thereby also excluded. > > Why is it a good idea to ignore the express requirement of the spec? > (I'm not saying it's not a good idea --- that note seems a little odd > to me too --- but presumably the spec writers had some reasons for > doing it that way. I'd like some justification for not doing it their > way.) If you consider LIKE is allowed to inherit an IDENTIFIER, which is a sequence based column with the intent that it will be a primary key, then allowing inheritance of CONSTRAINTS (via an optional flag INCLUDING CONSTRAINTS -- default is per spec) only makes sense. UNDER (IHERITS) appears to allow constraints to be inherited. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Rod Taylor <rbt@rbt.ca> writes:
> On Mon, 2003-05-12 at 10:59, Tom Lane wrote:
>> It'd be better to have a localized bit of code that processes LIKE by
>> generating a ColumnDef schema list.
> That can be done without much of an issue. Much of the code in
> MergeAttributes will be duplicated in this new routine.
Yeah. It might be worth splitting out the duplicated code into little
subroutines with purposes like "make a ColumnDef given this pg_attribute
entry".
> If you consider LIKE is allowed to inherit an IDENTIFIER, which is a
> sequence based column with the intent that it will be a primary key,
> then allowing inheritance of CONSTRAINTS (via an optional flag INCLUDING
> CONSTRAINTS -- default is per spec) only makes sense.
Hm. Exactly what will LIKE do with a serial column, and will it be
sensible? (I'd bet not, unless you put in some special cases...)
regards, tom lane
> > If you consider LIKE is allowed to inherit an IDENTIFIER, which is a > > sequence based column with the intent that it will be a primary key, > > then allowing inheritance of CONSTRAINTS (via an optional flag INCLUDING > > CONSTRAINTS -- default is per spec) only makes sense. > > Hm. Exactly what will LIKE do with a serial column, and will it be > sensible? (I'd bet not, unless you put in some special cases...) Going with the assumption that SERIAL is another way of specifying a IDENTITY column (auto-generated value by sequence as described in SQL 2k3) the default behaviour is described as copying the column name and datatype by default. If INCLUDING DEFAULTS is specified, then I would argue that the automatically created SERIAL default should *not* be copied into the new table as the spec treats GENERATORS and DEFAULTS as two independent concepts. This is shown by the 'ALWAYS GENERATED' flag which indicates the generated value may not be overridden, unlike a default. If INCLUDING IDENTITY is specified (which I don't intend to do at this time as our SERIALs are different enough from IDENTITIES to make this somewhat confusing), then it appears that the sequence should be duplicated but have the same settings as the one on the parent table. Including the same START WITH value. Not the current value of the sequence, but the value it was initially started at. That said, if the user creates a SEQUENCE and adds a default value of NEXT VALUE FOR <sequence>, then it should be copied if INCLUDING DEFAULTS is specified -- and is not an IDENTITY of the column, so would be ignored by INCLUDING IDENTITY. SQL99 has none of the above inclusions. For Serials default behaviour would be to copy the int4 or int8 datatype, the NOT NULL constraint, as well as the column name. Defaults and all other information are ignored. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Tom Lane kirjutas E, 12.05.2003 kell 17:59: > Rod Taylor <rbt@rbt.ca> writes: > > Quick patch to add the subject. Restructures all inheritance to consist > > of a few flags to indicate which structures we want to inherit > > I think overloading the inheritance mechanism to serve this purpose is a > bad idea. Are you sure that LIKE is not _supposed_ to be inheritable. I mean that changes done to the LIKE table should carry over to the table defined using LIKE: create table template( i int); create table instance(like template, t text); alter table template add column j int; and now instance should have columns i,j,t . > It complicates and confuses a significant amount of code > that's already pretty confusing (no, I don't believe you found it all). > It'd be better to have a localized bit of code that processes LIKE by > generating a ColumnDef schema list. Would this not detach the definitions . ---------- Hannu
On Tue, 2003-05-13 at 05:08, Hannu Krosing wrote: > Tom Lane kirjutas E, 12.05.2003 kell 17:59: > > Rod Taylor <rbt@rbt.ca> writes: > > > Quick patch to add the subject. Restructures all inheritance to consist > > > of a few flags to indicate which structures we want to inherit > > > > I think overloading the inheritance mechanism to serve this purpose is a > > bad idea. > > Are you sure that LIKE is not _supposed_ to be inheritable. I mean that > changes done to the LIKE table should carry over to the table defined > using LIKE: > create table template( i int); > create table instance(like template, t text); > alter table template add column j int; > > and now instance should have columns i,j,t . It is described as being replaced by the column definitions within the new table, with no mention of connection to the parent table.. 11.3, 6, d) "The <like clause> is effectively replaced by NCi, 1 (one) <= i <= cnt" -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Rod Taylor kirjutas T, 13.05.2003 kell 15:40: > On Tue, 2003-05-13 at 05:08, Hannu Krosing wrote: > > Tom Lane kirjutas E, 12.05.2003 kell 17:59: > > > Rod Taylor <rbt@rbt.ca> writes: > > > > Quick patch to add the subject. Restructures all inheritance to consist > > > > of a few flags to indicate which structures we want to inherit > > > > > > I think overloading the inheritance mechanism to serve this purpose is a > > > bad idea. > > > > Are you sure that LIKE is not _supposed_ to be inheritable. I mean that > > changes done to the LIKE table should carry over to the table defined > > using LIKE: > > > create table template( i int); > > create table instance(like template, t text); > > alter table template add column j int; > > > > and now instance should have columns i,j,t . > > It is described as being replaced by the column definitions within the > new table, with no mention of connection to the parent table.. > > 11.3, 6, d) "The <like clause> is effectively replaced by NCi, 1 (one) > <= i <= cnt" Must "11.3, 6, d)" hold only at table create time or should the relationship stay the same afterwards as well ? The reason I ask is that I was hoping that we can replace our current (multiple) INHERITS mechanism with combination of: * (single) UNDER which could inherit all constraints, including PRIMARY KEY and UNIQUE, by storing the UNDER tables in the same physical relation, and * (multiple) LIKE tables which would be able to inherit row-level level constraints (NOT NULL, CHECK, FOREIGN KEY) so that no functionality we already have would be lost but the logical problems of inheriting constraints (what to do with multiple PK's and UNIQUES) could be solved. I would have also liked to twist the specs a little so that create table t1 (a text, i int); create table t2 (b text, i int); create table t3 (like t1, like t2); would yield t3 as (a text, i int, b text) and in be simultaneously of types t1, t2 and t3 so that a function defined over t1 or t2 could be used directly on t3 as well. also i would propose that functions be resolved dynamically so that create table t1 (a text, i int) create table t2 (b text) under t1; select func(t1) from t1; would use func(t1) on rows from t1 and func(t2) on rows from t2; This would give PostgreSQL a nice minimal OO type system. ------------- Hannu
Rod Taylor kirjutas T, 13.05.2003 kell 15:40: > On Tue, 2003-05-13 at 05:08, Hannu Krosing wrote: > > Tom Lane kirjutas E, 12.05.2003 kell 17:59: > > > Rod Taylor <rbt@rbt.ca> writes: > > > > Quick patch to add the subject. Restructures all inheritance to consist > > > > of a few flags to indicate which structures we want to inherit > > > > > > I think overloading the inheritance mechanism to serve this purpose is a > > > bad idea. > > > > Are you sure that LIKE is not _supposed_ to be inheritable. I mean that > > changes done to the LIKE table should carry over to the table defined > > using LIKE: > > > create table template( i int); > > create table instance(like template, t text); > > alter table template add column j int; > > > > and now instance should have columns i,j,t . > > It is described as being replaced by the column definitions within the > new table, with no mention of connection to the parent table.. > > 11.3, 6, d) "The <like clause> is effectively replaced by NCi, 1 (one) > <= i <= cnt" Is this different from UNDER, i.e does it's definition mention connection with parent table ? -------------- Hannu
On Tue, 2003-05-13 at 10:15, Hannu Krosing wrote: > Rod Taylor kirjutas T, 13.05.2003 kell 15:40: > > On Tue, 2003-05-13 at 05:08, Hannu Krosing wrote: > > > Tom Lane kirjutas E, 12.05.2003 kell 17:59: > > > > Rod Taylor <rbt@rbt.ca> writes: > > > > > Quick patch to add the subject. Restructures all inheritance to consist > > > > > of a few flags to indicate which structures we want to inherit > > > > > > > > I think overloading the inheritance mechanism to serve this purpose is a > > > > bad idea. > > > > > > Are you sure that LIKE is not _supposed_ to be inheritable. I mean that > > > changes done to the LIKE table should carry over to the table defined > > > using LIKE: > > > > > create table template( i int); > > > create table instance(like template, t text); > > > alter table template add column j int; > > > > > > and now instance should have columns i,j,t . > > > > It is described as being replaced by the column definitions within the > > new table, with no mention of connection to the parent table.. > > > > 11.3, 6, d) "The <like clause> is effectively replaced by NCi, 1 (one) > > <= i <= cnt" > > Is this different from UNDER, i.e does it's definition mention > connection with parent table ? UNDER doesn't have a statement resembling the above and does mention the concept of a base table, which is described as having a connection with it's subtables (children). -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Where did we leave this patch? --------------------------------------------------------------------------- Rod Taylor wrote: -- Start of PGP signed section. > On Mon, 2003-05-12 at 10:59, Tom Lane wrote: > > Rod Taylor <rbt@rbt.ca> writes: > > > Quick patch to add the subject. Restructures all inheritance to consist > > > of a few flags to indicate which structures we want to inherit > > > > I think overloading the inheritance mechanism to serve this purpose is a > > bad idea. It complicates and confuses a significant amount of code > > that's already pretty confusing (no, I don't believe you found it all). > > It'd be better to have a localized bit of code that processes LIKE by > > generating a ColumnDef schema list. > > That can be done without much of an issue. Much of the code in > MergeAttributes will be duplicated in this new routine. > > I'll conduct the transform of LIKE to ColumnDef list within the > transformCreateStmt where the rest of the transformations take place. > > > > Yes, I wish to add an option to allow check > > > constraints to be carried over despite the below note from Sect. 11.3: > > > > > NOTE 234 <column constraint>s, except for NOT NULL, are not included > > > in NCi; <column constraint definition>s are effectively transformed to > > > <table constraint definition>s and are thereby also excluded. > > > > Why is it a good idea to ignore the express requirement of the spec? > > (I'm not saying it's not a good idea --- that note seems a little odd > > to me too --- but presumably the spec writers had some reasons for > > doing it that way. I'd like some justification for not doing it their > > way.) > > If you consider LIKE is allowed to inherit an IDENTIFIER, which is a > sequence based column with the intent that it will be a primary key, > then allowing inheritance of CONSTRAINTS (via an optional flag INCLUDING > CONSTRAINTS -- default is per spec) only makes sense. > > UNDER (IHERITS) appears to allow constraints to be inherited. > > -- > Rod Taylor <rbt@rbt.ca> > > PGP Key: http://www.rbt.ca/rbtpub.asc -- End of PGP section, PGP failed! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Mon, 2003-06-02 at 11:26, Bruce Momjian wrote: > Where did we leave this patch? http://archives.postgresql.org/pgsql-patches/2003-05/msg00103.php Please apply the above. > --------------------------------------------------------------------------- > > Rod Taylor wrote: > -- Start of PGP signed section. > > On Mon, 2003-05-12 at 10:59, Tom Lane wrote: > > > Rod Taylor <rbt@rbt.ca> writes: > > > > Quick patch to add the subject. Restructures all inheritance to consist > > > > of a few flags to indicate which structures we want to inherit > > > > > > I think overloading the inheritance mechanism to serve this purpose is a > > > bad idea. It complicates and confuses a significant amount of code > > > that's already pretty confusing (no, I don't believe you found it all). > > > It'd be better to have a localized bit of code that processes LIKE by > > > generating a ColumnDef schema list. > > > > That can be done without much of an issue. Much of the code in > > MergeAttributes will be duplicated in this new routine. > > > > I'll conduct the transform of LIKE to ColumnDef list within the > > transformCreateStmt where the rest of the transformations take place. > > > > > > Yes, I wish to add an option to allow check > > > > constraints to be carried over despite the below note from Sect. 11.3: > > > > > > > NOTE 234 <column constraint>s, except for NOT NULL, are not included > > > > in NCi; <column constraint definition>s are effectively transformed to > > > > <table constraint definition>s and are thereby also excluded. > > > > > > Why is it a good idea to ignore the express requirement of the spec? > > > (I'm not saying it's not a good idea --- that note seems a little odd > > > to me too --- but presumably the spec writers had some reasons for > > > doing it that way. I'd like some justification for not doing it their > > > way.) > > > > If you consider LIKE is allowed to inherit an IDENTIFIER, which is a > > sequence based column with the intent that it will be a primary key, > > then allowing inheritance of CONSTRAINTS (via an optional flag INCLUDING > > CONSTRAINTS -- default is per spec) only makes sense. > > > > UNDER (IHERITS) appears to allow constraints to be inherited. > > > > -- > > Rod Taylor <rbt@rbt.ca> > > > > PGP Key: http://www.rbt.ca/rbtpub.asc > -- End of PGP section, PGP failed! -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Rod Taylor <rbt@rbt.ca> writes: > On Mon, 2003-06-02 at 11:26, Bruce Momjian wrote: >> Where did we leave this patch? > http://archives.postgresql.org/pgsql-patches/2003-05/msg00103.php > Please apply the above. I was unconvinced that the proposed patch actually implements SQL99 semantics, as opposed to grafting SQL99-like syntax onto Postgres semantics. regards, tom lane
> > http://archives.postgresql.org/pgsql-patches/2003-05/msg00103.php > > > Please apply the above. > > I was unconvinced that the proposed patch actually implements SQL99 > semantics, as opposed to grafting SQL99-like syntax onto Postgres > semantics. Yes, but the second attempt (linked above) addressed those concerns did it not? There wasn't a response one way or the other, and a lack of a negative response is generally a good thing on -patches. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Rod Taylor <rbt@rbt.ca> writes:
> Yes, but the second attempt (linked above) addressed those concerns did
> it not? There wasn't a response one way or the other, and a lack of a
> negative response is generally a good thing on -patches.
For the last while, neither Bruce nor I have been paying much
attention to pgsql-patches; we were both busy with our own problems.
So the actual status of most recent patches is "unreviewed", not
"reviewed and not objected to".
I can't recall at the moment whether I looked at your second version of
this patch; it may be that my objection was to the first version. But
I'm not sure.
BTW, Bruce, where are you on catching up the patch backlog? I'm
hesitant to start elog() editing until that's pretty much done, since
it'll certainly cause merge problems for unapplied patches ...
regards, tom lane
Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: > > Yes, but the second attempt (linked above) addressed those concerns did > > it not? There wasn't a response one way or the other, and a lack of a > > negative response is generally a good thing on -patches. > > For the last while, neither Bruce nor I have been paying much > attention to pgsql-patches; we were both busy with our own problems. > So the actual status of most recent patches is "unreviewed", not > "reviewed and not objected to". > > I can't recall at the moment whether I looked at your second version of > this patch; it may be that my objection was to the first version. But > I'm not sure. > > BTW, Bruce, where are you on catching up the patch backlog? I'm > hesitant to start elog() editing until that's pretty much done, since > it'll certainly cause merge problems for unapplied patches ... I will be finished today, then I will call you to have you eyeball the queue. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073