Does anyone object to extending the grammar to allow this?

ALTER TABLE test ADD PRIMARY KEY (a) TABLESPACE foo;

ALTER TABLE test ADD UNIQUE (a) TABLESPACE foo;

CREATE TABLE test (a INTEGER PRIMARY KEY TABLESPACE foo);

CREATE TABLE test (a INTEGER UNIQUE TABLESPACE foo);

This is needed since we can move indexes between tablespaces now.  It 
makes pg_dump support for it possible and prevents huge performance cost 
associated with creating the primary key and then having to move it.

If there are no objects, I've already mostly finished the patch and I'll 
send it in.

Chris


Thu, 22 Jul 2004 14:54:06 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Fixing PKs and Uniques in tablespaces
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Does anyone object to extending the grammar to allow this?

Yes.  This is horribly ugly, and I suspect that you cannot do it
without making TABLESPACE a fully-reserved word.

			regards, tom lane

Thu, 22 Jul 2004 03:09:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Fixing PKs and Uniques in tablespaces 
> Yes.  This is horribly ugly, and I suspect that you cannot do it
> without making TABLESPACE a fully-reserved word.

No, I've already done it and it works just fine.  What is your 
suggestion then?  Just assume the name of the index it will get?

Also, I realised that the pg_get_serial_sequence() doesn't solve the 
whole sequence autonaming problem  :(  Specifically, dump entries like this:

COMMENT ON SEQUENCE blah_seq IS 'asdfsdfa';

Chris

Thu, 22 Jul 2004 15:30:01 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Fixing PKs and Uniques in tablespaces
>>Does anyone object to extending the grammar to allow this?
> 
> Yes.  This is horribly ugly, and I suspect that you cannot do it
> without making TABLESPACE a fully-reserved word.

I note that this seems to be the Oracle syntax:

CONSTRAINT PK_Stock PRIMARY KEY (Company) USING INDEX TABLESPACE 
Appl_Indexes

http://www.siue.edu/~dbock/cmis564/otext4.htm

Since we stole tablespaces from Oracle, maybe we should make them work 
the same?

Chris


Thu, 22 Jul 2004 15:32:24 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Fixing PKs and Uniques in tablespaces
>>Does anyone object to extending the grammar to allow this?
> 
> Yes.  This is horribly ugly, and I suspect that you cannot do it
> without making TABLESPACE a fully-reserved word.

This suggests a slightly different oracle syntax.  I guess the word 
'index' is optional.

http://www.jlcomp.demon.co.uk/ch_18.html

Chris


Thu, 22 Jul 2004 15:37:20 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Fixing PKs and Uniques in tablespaces
On Thu, 22 Jul 2004, Christopher Kings-Lynne wrote:

> >>Does anyone object to extending the grammar to allow this?
> >
> > Yes.  This is horribly ugly, and I suspect that you cannot do it
> > without making TABLESPACE a fully-reserved word.
>
> I note that this seems to be the Oracle syntax:
>
> CONSTRAINT PK_Stock PRIMARY KEY (Company) USING INDEX TABLESPACE
> Appl_Indexes
>
> http://www.siue.edu/~dbock/cmis564/otext4.htm
>
> Since we stole tablespaces from Oracle, maybe we should make them work
> the same?

I never really considered oracle's implementation of tablespaces when I
worked on tablespaces. The database default tablespace seems similar to
Oracle's SYSTEM tablespace. I'm not sure if they use a global tablespace
like we do.

I also cannot find any information about Oracle placing object's in their
parent's table space if a tablespace isn't specified.

Tablespaces in Oracle also do raw block device stuff, which we obviously
don't.

I'd dare say that tablespaces in Oracle don't use symlinks either :-)

Gavin

Thu, 22 Jul 2004 17:47:26 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Fixing PKs and Uniques in tablespaces
>>Since we stole tablespaces from Oracle, maybe we should make them work
>>the same?
> 
> I never really considered oracle's implementation of tablespaces when I
> worked on tablespaces. The database default tablespace seems similar to
> Oracle's SYSTEM tablespace. I'm not sure if they use a global tablespace
> like we do.

My point was that Oracle has added a tablespace clause to the constraint 
clause, so we can too!

Also, since I checked and it seems that our syntax for putting tables an 
d indexes in tablespaces at creation time is identical to oracle's, 
perhaps we should copy them on constraints as well.

Chris


Thu, 22 Jul 2004 15:54:46 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Fixing PKs and Uniques in tablespaces
>> I never really considered oracle's implementation of tablespaces when I
>> worked on tablespaces. The database default tablespace seems similar to
>> Oracle's SYSTEM tablespace. I'm not sure if they use a global tablespace
>> like we do.
> 
> 
> My point was that Oracle has added a tablespace clause to the constraint 
> clause, so we can too!
> 
> Also, since I checked and it seems that our syntax for putting tables an 
> d indexes in tablespaces at creation time is identical to oracle's, 
> perhaps we should copy them on constraints as well.

Since we're getting close to beta, can we have consensus on what I'm to 
do about this?

Chris


Fri, 23 Jul 2004 11:24:16 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Fixing PKs and Uniques in tablespaces
Christopher Kings-Lynne wrote:
>>> I never really considered oracle's implementation of tablespaces when I
>>> worked on tablespaces. The database default tablespace seems similar to
>>> Oracle's SYSTEM tablespace. I'm not sure if they use a global tablespace
>>> like we do.
>>
>>
>>
>> My point was that Oracle has added a tablespace clause to the 
>> constraint clause, so we can too!
>>
>> Also, since I checked and it seems that our syntax for putting tables 
>> an d indexes in tablespaces at creation time is identical to oracle's, 
>> perhaps we should copy them on constraints as well.
> 
> 
> Since we're getting close to beta, can we have consensus on what I'm to 
> do about this?

We are already in a features freeze period, or not ?


Regards
Gaetano Mendola





Fri, 23 Jul 2004 09:39:46 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Fixing PKs and Uniques in tablespaces
> We are already in a features freeze period, or not ?

This isn't a feature, it's a bug...

Chris


Fri, 23 Jul 2004 16:02:57 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Fixing PKs and Uniques in tablespaces
Christopher Kings-Lynne wrote:
> > Also, since I checked and it seems that our syntax for putting
> > tables an d indexes in tablespaces at creation time is identical to
> > oracle's, perhaps we should copy them on constraints as well.
>
> Since we're getting close to beta, can we have consensus on what I'm
> to do about this?

I think it's OK if  you can do the Oracle syntax (or some other 
established syntax) without reserving the key word TABLESPACE.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Sat, 24 Jul 2004 00:49:54 +0200
Peter Eisentraut <peter_e@gmx.net>
Re: Fixing PKs and Uniques in tablespaces