Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-21 Thread Gregory Stark
Jim C. Nasby [EMAIL PROTECTED] writes: On Fri, Jul 20, 2007 at 12:40:46PM +0100, Gregory Stark wrote: One of the main use cases I envision is wanting to create new partitions suitable for being added to a partitioned table. Except that's normally done with CREATE TABLE INHERITS, which

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-20 Thread Gregory Stark
Tatsuo Ishii [EMAIL PROTECTED] writes: Can someone enligten me what the usecase for CREATE TABLE LIKE at this moment is? One of the main use cases I envision is wanting to create new partitions suitable for being added to a partitioned table. -- Gregory Stark EnterpriseDB

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-20 Thread Jim C. Nasby
On Fri, Jul 20, 2007 at 12:40:46PM +0100, Gregory Stark wrote: Tatsuo Ishii [EMAIL PROTECTED] writes: Can someone enligten me what the usecase for CREATE TABLE LIKE at this moment is? One of the main use cases I envision is wanting to create new partitions suitable for being added to a

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-19 Thread Tatsuo Ishii
Can someone enligten me what the usecase for CREATE TABLE LIKE at this moment is? I read the doc and followed the discssion in this thread in pgsql-patches list but it was hard for me to figure out. -- Tatsuo Ishii SRA OSS, Inc. Japan I've applied the latest version of the patch to HEAD. Thanks

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-16 Thread Neil Conway
On Tue, 2007-10-07 at 12:53 +0530, NikhilS wrote: Yes, in the CREATE..LIKE case, options will be NULL and in the normal CREATE..INDEX case inhreloptions will be NULL. Note that options is a List of DefElem entries, whereas inhreloptions is a char pointer. Hmm, right -- ugly. I'll just stick

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-16 Thread NikhilS
Hi, Attached is a revised patch that does that. Barring any objections, I'll apply this to HEAD tomorrow. A minor correction below in your patch: --- src/include/commands/defrem.h 16 Jul 2007 05:19:43 - *** extern void DefineIndex(RangeVar *heapRe *** 26,31 ---

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-16 Thread Neil Conway
On Mon, 2007-16-07 at 14:28 +0530, NikhilS wrote: Guess you want to use src_options here to be uniform with usages elsewhere that you have replaced. Thanks, good catch. You suppose src_options is much more readable than inhreloptions or inh_idxoptions? Your call :). Yeah, I'm not

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-16 Thread NikhilS
Hi, You suppose src_options is much more readable than inhreloptions or inh_idxoptions? Your call :). Yeah, I'm not necessarily sure that it is. inhreloptions made me think of table inheritance, whereas LIKE in general is more of a source = target copying operation. But I'm not convinced

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-16 Thread Neil Conway
I've applied the latest version of the patch to HEAD. Thanks for the patches, Nikhil and Trevor -- we can take a look at improving INCLUDING CONSTRAINTS for 8.4. On Mon, 2007-16-07 at 15:50 +0530, NikhilS wrote: I agree, since its a LIKE operation and not inheritance as such, how about

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-10 Thread NikhilS
Hi, This version updates the patch to CVS HEAD and has various fixes and refactoring, including proper docs. I refactored get_opclass_name() into lsyscache.c, but then realized that this means that lsyscache.c will depend on commands/indexcmds.c (for GetDefaultOpClass()), which is arguably

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-10 Thread Bruce Momjian
Neil Conway wrote: On Tue, 2007-05-06 at 14:50 +0530, NikhilS wrote: PFA, a patch which provides the CREATE .. INCLUDING INDEXES functionality. This patch uses the same functionality introduced by the earlier patches in this thread albeit for the INCLUDING INDEXES case. Attached is a

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-10 Thread Jaime Casanova
On 7/10/07, Bruce Momjian [EMAIL PROTECTED] wrote: Neil Conway wrote: Attached is a revised version of this patch. Sorry for taking so long to make progress on this: my new job been keeping my busy, and I've recently been ill. Illness only counts as an excuse if you _don't_ recover. If you

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-07-09 Thread Neil Conway
On Tue, 2007-05-06 at 14:50 +0530, NikhilS wrote: PFA, a patch which provides the CREATE .. INCLUDING INDEXES functionality. This patch uses the same functionality introduced by the earlier patches in this thread albeit for the INCLUDING INDEXES case. Attached is a revised version of this

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-06-05 Thread NikhilS
Hi, Anyways, this patch and the functionality introduced herein will be useful in the CREATE .. INCLUDING INDEXES case too. No doubt. But those are different features and we shouldn't confuse 'em; in particular not put behavior into the INCLUDING CONSTRAINTS case that shouldn't be there.

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-06-03 Thread NikhilS
Hi, On 6/3/07, Neil Conway [EMAIL PROTECTED] wrote: On Mon, 2007-21-05 at 12:23 +0530, NikhilS wrote: I had spent some time on this earlier so decided to complete and send the patch to you for review. This patch supports copying of expressions, predicates, opclass, amorder, reloptions etc.

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-06-03 Thread NikhilS
Hi, On 6/3/07, Tom Lane [EMAIL PROTECTED] wrote: Neil Conway [EMAIL PROTECTED] writes: Attached is a revised version of this patch. This still seems to fundamentally misunderstand the difference between an index and a constraint. IMHO it should not be examining pg_index (or specifically,

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-06-03 Thread Tom Lane
NikhilS [EMAIL PROTECTED] writes: On 6/3/07, Tom Lane [EMAIL PROTECTED] wrote: This still seems to fundamentally misunderstand the difference between an index and a constraint. IMHO it should not be examining pg_index (or specifically, the index Relations) at all. Anyways, this patch and

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-06-02 Thread Neil Conway
On Mon, 2007-21-05 at 12:23 +0530, NikhilS wrote: I had spent some time on this earlier so decided to complete and send the patch to you for review. This patch supports copying of expressions, predicates, opclass, amorder, reloptions etc. The test case also contains some more additions with

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-06-02 Thread Tom Lane
Neil Conway [EMAIL PROTECTED] writes: Attached is a revised version of this patch. This still seems to fundamentally misunderstand the difference between an index and a constraint. IMHO it should not be examining pg_index (or specifically, the index Relations) at all.

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-28 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes: OK, so the patch is ready to be applied? Neil's still reviewing it, last I heard. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives?

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-23 Thread NikhilS
Hi, I noticed that this patch uses names for some things (for example it gets the name of the access method), and then builds a IndexStmt which contains the name. I don't think this is a good idea. I think what should happen here is that the function to build indexes should be split in two:

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-23 Thread NikhilS
Hi, The index creation happens after the new table (which is LIKE the parent) has been created, by appending the cxt.alist information to extras_after. The entry point for the index creation is thus via ProcessUtility which expects an IndexStmt structure. That is why the current patch does all

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-23 Thread NikhilS
Hi, On 5/23/07, NikhilS [EMAIL PROTECTED] wrote: Hi, The index creation happens after the new table (which is LIKE the parent) has been created, by appending the cxt.alist information to extras_after. The entry point for the index creation is thus via ProcessUtility which expects an

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-23 Thread Alvaro Herrera
NikhilS escribió: Sorry for the barrage of emails. But as I looked closely at the current patch there are only 2 fields (accessMethod and tableSpace) in IndexStmt structure that we populate by doing the conversion from OIDs to name. For the other fields, the current transformations will

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-23 Thread Tom Lane
NikhilS [EMAIL PROTECTED] writes: If so, I think we can introduce 2 Oid fields in the IndexStmt structure and store the Oids there. In DefineIndex we can use these Oids if they are not invalid. I think this is just make-work that causes the patch to complicate parts of the system it didn't

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-23 Thread NikhilS
Hi, On 5/23/07, Tom Lane [EMAIL PROTECTED] wrote: NikhilS [EMAIL PROTECTED] writes: If so, I think we can introduce 2 Oid fields in the IndexStmt structure and store the Oids there. In DefineIndex we can use these Oids if they are not invalid. I think this is just make-work that causes the

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-23 Thread Alvaro Herrera
NikhilS escribió: On 5/23/07, Tom Lane [EMAIL PROTECTED] wrote: NikhilS [EMAIL PROTECTED] writes: If so, I think we can introduce 2 Oid fields in the IndexStmt structure and store the Oids there. In DefineIndex we can use these Oids if they are not invalid. I think this is just

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-23 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes: Not sure. Is it possible that the schema is renamed while the operation is being executed? If it's not then this not a problem at all so the existing patch is fine. There are hazards of that type in CREATE TABLE right now; it's hardly fair to hold

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-23 Thread NikhilS
Hi, I agree this will unnecessary add arguments to the DefineIndex API. If we stick to the patch's earlier way of converting the Oid to names for just these 2 arguments, we can avoid this IMO. Considering that we will be generating this information from existing valid index information, I

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-22 Thread Bruce Momjian
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. ---

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-22 Thread Alvaro Herrera
Bruce Momjian escribió: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. I noticed that this patch uses names for some

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-21 Thread NikhilS
Hi Neil, On 5/18/07, Neil Conway [EMAIL PROTECTED] wrote: On Mon, 2007-14-05 at 22:58 -0400, Neil Conway wrote: Has a revised version of this patch been submitted? In the absence of a revised patch, I can finish the feature myself, but I won't get the free cycles until after PGCon. I can

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-20 Thread NikhilS
Hi, Since this patch is going to consider creating unique/primary indexes assuming them to be constraints, If it does that it will be rejected. There is a difference here and that difference has to be maintained. The correct way to think about this is that a pg_constraint entry of type

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-18 Thread NikhilS
Hi, [ remembering previous discussions more clearly... ] Actually there is a concrete problem here: unique constraints are supposed to be represented in the information_schema views, and there is no spec-compliant way to do that for a constraint on something other than a column. We'd have to

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-18 Thread Tom Lane
NikhilS [EMAIL PROTECTED] writes: [ remembering previous discussions more clearly... ] Actually there is a concrete problem here: unique constraints are supposed to be represented in the information_schema views, and there is no spec-compliant way to do that for a constraint on something

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-17 Thread NikhilS
Hi, Nope: neilc=# create table t1 (a int, b int); CREATE TABLE neilc=# create unique index t1_a_idx on t1 ((a + b)) where (a 5); CREATE INDEX I just now realized that even though we allow the above. We do not allow: pg=# create table t1 (a int, b int, unique(a+b)); nor the where clause

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-17 Thread Tom Lane
NikhilS [EMAIL PROTECTED] writes: I just now realized that even though we allow the above. We do not allow: pg=# create table t1 (a int, b int, unique(a+b)); Any specific reason for this behaviour? It'd be contrary to SQL spec. The UNIQUE constraint takes a list of column names, full stop.

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-17 Thread Gregory Stark
Tom Lane [EMAIL PROTECTED] writes: NikhilS [EMAIL PROTECTED] writes: I just now realized that even though we allow the above. We do not allow: pg=# create table t1 (a int, b int, unique(a+b)); Any specific reason for this behaviour? It'd be contrary to SQL spec. The UNIQUE constraint

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-17 Thread Tom Lane
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: It'd be contrary to SQL spec. The UNIQUE constraint takes a list of column names, full stop. Does the SQL spec actually specify what happens if you provide an non-compliant table definition like this? It does not.

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-17 Thread Tom Lane
I wrote: Gregory Stark [EMAIL PROTECTED] writes: Does the SQL spec actually specify what happens if you provide an non-compliant table definition like this? It does not. We could accept expressions there, and pray that the SQL committee never extends the spec syntax in a direction

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-17 Thread Neil Conway
On Mon, 2007-14-05 at 22:58 -0400, Neil Conway wrote: Has a revised version of this patch been submitted? In the absence of a revised patch, I can finish the feature myself, but I won't get the free cycles until after PGCon. I can commit to getting it done before the end of May, or else we can

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-17 Thread Bruce Momjian
Neil Conway wrote: On Mon, 2007-14-05 at 22:58 -0400, Neil Conway wrote: Has a revised version of this patch been submitted? In the absence of a revised patch, I can finish the feature myself, but I won't get the free cycles until after PGCon. I can commit to getting it done before the end

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-14 Thread Neil Conway
On Fri, 2007-27-04 at 18:59 -0400, Neil Conway wrote: This patch needs more work. Has a revised version of this patch been submitted? -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-03 Thread NikhilS
Hi, On 5/3/07, Tom Lane [EMAIL PROTECTED] wrote: NikhilS [EMAIL PROTECTED] writes: Hi Neil, * the patch is broken for expressional indexes, and silently omits copying the predicate that may be associated with an index. Since this patch is only supposed to copy unique/primary indexes, I

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-03 Thread Tom Lane
NikhilS [EMAIL PROTECTED] writes: But this patch is not for the INCLUDING INDEXES case. Pardon me for being misled by the thread title :-( regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-02 Thread NikhilS
Hi Neil, * the patch is broken for expressional indexes, and silently omits copying the predicate that may be associated with an index. It also doesn't copy the index's amoptions (WITH clause), or the NULLS FIRST/etc. options that may be associated with any of the index's columns. Since

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-02 Thread Tom Lane
NikhilS [EMAIL PROTECTED] writes: Hi Neil, * the patch is broken for expressional indexes, and silently omits copying the predicate that may be associated with an index. Since this patch is only supposed to copy unique/primary indexes, I dont think we will ever have predicates associated to

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-05-02 Thread Neil Conway
On Wed, 2007-02-05 at 17:09 +0530, NikhilS wrote: Since this patch is only supposed to copy unique/primary indexes, I dont think we will ever have predicates associated to such indexes? Nope: neilc=# create table t1 (a int, b int); CREATE TABLE neilc=# create unique index t1_a_idx on t1 ((a +

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-27 Thread Neil Conway
This patch needs more work. How carefully did you test it? * the patch failed to copy index constraints if there was not also at least one CHECK constraint defined on the table * the patch is broken for expressional indexes, and silently omits copying the predicate that may be associated with an

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-26 Thread Bruce Momjian
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. ---

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-13 Thread Trevor Hardcastle
NikhilS wrote: Hi Trevor, + + parent_index_info = BuildIndexInfo(parent_index); The above is not used anywhere else in the code and seems redundant. Yep, pulled that out. + + ereport(NOTICE,

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-12 Thread Trevor Hardcastle
Bruce Momjian wrote: NikhilS wrote: Hi, On 4/10/07, Bruce Momjian [EMAIL PROTECTED] wrote: Added to TODO: o Have WITH CONSTRAINTS also create constraint indexes http://archives.postgresql.org/pgsql-patches/2007-04/msg00149.php Trevor's patch does add unique/primary

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-12 Thread NikhilS
Hi Trevor, + + parent_index_info = BuildIndexInfo(parent_index); The above is not used anywhere else in the code and seems redundant. + + ereport(NOTICE, +

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-11 Thread Bruce Momjian
NikhilS wrote: Hi, On 4/10/07, Bruce Momjian [EMAIL PROTECTED] wrote: Added to TODO: o Have WITH CONSTRAINTS also create constraint indexes http://archives.postgresql.org/pgsql-patches/2007-04/msg00149.php Trevor's patch does add unique/primary indexes. This would

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-10 Thread NikhilS
Hi, On 4/10/07, Bruce Momjian [EMAIL PROTECTED] wrote: Added to TODO: o Have WITH CONSTRAINTS also create constraint indexes http://archives.postgresql.org/pgsql-patches/2007-04/msg00149.php Trevor's patch does add unique/primary indexes. This would mean that we have to remove

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-09 Thread Bruce Momjian
Added to TODO: o Have WITH CONSTRAINTS also create constraint indexes http://archives.postgresql.org/pgsql-patches/2007-04/msg00149.php --- Trevor Hardcastle wrote: Greetings all, I wrote this patch

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-05 Thread Bruce Momjian
Gregory Stark wrote: Bruce Momjian [EMAIL PROTECTED] writes: Uh, shouldn't CREATE TABLE LIKE INCLUDING CONSTRAINTS already be including any indexes in the parent table? You could argue it should for unique indexes since our unique indexes are how we implement unique constraints. But I

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-04 Thread Bruce Momjian
Uh, shouldn't CREATE TABLE LIKE INCLUDING CONSTRAINTS already be including any indexes in the parent table? --- Trevor Hardcastle wrote: Greetings all, I wrote this patch about a week ago to introduce myself to coding

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-04 Thread Gregory Stark
Bruce Momjian [EMAIL PROTECTED] writes: Uh, shouldn't CREATE TABLE LIKE INCLUDING CONSTRAINTS already be including any indexes in the parent table? You could argue it should for unique indexes since our unique indexes are how we implement unique constraints. But I see no particular reason to

Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support

2007-04-02 Thread Bruce Momjian
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. ---