Log Message:
-----------
Clean up generation of default names for constraints, indexes, and serial
sequences, as per recent discussion.  All these names are now of the
form table_column_type, with digits added if needed to make them unique.
Default constraint names are chosen to be unique across their whole schema,
not just within the parent object, so as to be more SQL-spec-compatible
and make the information schema views more useful.

Modified Files:
--------------
    pgsql-server/src/backend/catalog:
        heap.c (r1.269 -> r1.270)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/catalog/heap.c.diff?r1=1.269&r2=1.270)
        pg_constraint.c (r1.19 -> r1.20)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/catalog/pg_constraint.c.diff?r1=1.19&r2=1.20)
    pgsql-server/src/backend/commands:
        indexcmds.c (r1.120 -> r1.121)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/indexcmds.c.diff?r1=1.120&r2=1.121)
        tablecmds.c (r1.112 -> r1.113)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/tablecmds.c.diff?r1=1.112&r2=1.113)
        typecmds.c (r1.58 -> r1.59)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/typecmds.c.diff?r1=1.58&r2=1.59)
    pgsql-server/src/backend/parser:
        analyze.c (r1.304 -> r1.305)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/parser/analyze.c.diff?r1=1.304&r2=1.305)
    pgsql-server/src/include/catalog:
        pg_constraint.h (r1.10 -> r1.11)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/include/catalog/pg_constraint.h.diff?r1=1.10&r2=1.11)
    pgsql-server/src/include/commands:
        defrem.h (r1.56 -> r1.57)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/include/commands/defrem.h.diff?r1=1.56&r2=1.57)
    pgsql-server/src/include/parser:
        analyze.h (r1.26 -> r1.27)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/include/parser/analyze.h.diff?r1=1.26&r2=1.27)
    pgsql-server/src/test/regress/expected:
        alter_table.out (r1.83 -> r1.84)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/alter_table.out.diff?r1=1.83&r2=1.84)
        cluster.out (r1.15 -> r1.16)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/cluster.out.diff?r1=1.15&r2=1.16)
        copy2.out (r1.18 -> r1.19)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/copy2.out.diff?r1=1.18&r2=1.19)
        domain.out (r1.30 -> r1.31)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/domain.out.diff?r1=1.30&r2=1.31)
        foreign_key.out (r1.34 -> r1.35)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/foreign_key.out.diff?r1=1.34&r2=1.35)
        namespace.out (r1.1 -> r1.2)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/namespace.out.diff?r1=1.1&r2=1.2)
        rules.out (r1.88 -> r1.89)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/rules.out.diff?r1=1.88&r2=1.89)
        sequence.out (r1.5 -> r1.6)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/sequence.out.diff?r1=1.5&r2=1.6)
        truncate.out (r1.8 -> r1.9)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/truncate.out.diff?r1=1.8&r2=1.9)
    pgsql-server/src/test/regress/output:
        constraints.source (r1.39 -> r1.40)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/output/constraints.source.diff?r1=1.39&r2=1.40)
    pgsql-server/src/test/regress/sql:
        copy2.sql (r1.9 -> r1.10)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/sql/copy2.sql.diff?r1=1.9&r2=1.10)

Thu, 10 Jun 2004 14:56:03 -0300 (ADT)
tgl@svr1.postgresql.org (Tom Lane)
pgsql-server: Clean up generation of default names for constraints, 
> Clean up generation of default names for constraints, indexes, and serial
> sequences, as per recent discussion.  All these names are now of the
> form table_column_type, with digits added if needed to make them unique.
> Default constraint names are chosen to be unique across their whole schema,
> not just within the parent object, so as to be more SQL-spec-compatible
> and make the information schema views more useful.

Do you do any sort of 'locking of potential names' to ensure that 
another process adding a sequence at the same time or something won't 
get the same name first, causing yours to still fail or break uniqueness?

Or do we just not care since it's unlikely to happen and our transation 
would likely have some exclusive lock anyway?

Chris


Fri, 11 Jun 2004 09:14:37 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pgsql-server: Clean up generation of default names
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> Clean up generation of default names for constraints, indexes, and serial
>> sequences, as per recent discussion.

> Do you do any sort of 'locking of potential names' to ensure that 
> another process adding a sequence at the same time or something won't 
> get the same name first, causing yours to still fail or break uniqueness?

It's not 100% bulletproof, but I'd say that in practice I don't expect
any problems.  For instance, since all the generated names start with
the base relation name, in the absence of truncation there can't be any
such conflict --- ALTER TABLE will be holding an exclusive lock on the
base relation, and in the CREATE TABLE case you're going to die on the
base relation name conflict anyway.

If you use sufficiently long table/field names then different tables
could truncate to the same generated names, and in that case there's
some risk of concurrently choosing the same "unique" name.  But I don't
recall anyone having complained of that since we started using this
technique for choosing index names, so I'm not very worried.  Basically
what this commit did was propagate the index naming technique to
constraints and sequences.

			regards, tom lane

Thu, 10 Jun 2004 21:34:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pgsql-server: Clean up generation of default names 
(moved to -hackers)

> If you use sufficiently long table/field names then different tables
> could truncate to the same generated names, and in that case there's
> some risk of concurrently choosing the same "unique" name.  But I don't
> recall anyone having complained of that since we started using this
> technique for choosing index names, so I'm not very worried.  Basically
> what this commit did was propagate the index naming technique to
> constraints and sequences.

Is it conceivable that different SERIAL sequence names could now be 
generated?

ie.  If I upgrade from 7.4 with a dump that looks like this:

CREATE TABLE blah (
	id SERIAL
);

COPY ...

SELECT SETVAL('blah_id_seq', 10);

Then if the name given to the id sequence is now different, these dumps 
will not restore.  (In this case it will be the same, I'm just 
illustrating the general problem of hard-coding those sequence names in 
the dump - I've never liked it :) )

Chris


Fri, 11 Jun 2004 09:49:25 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: [COMMITTERS] pgsql-server: Clean up generation of default names
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Then if the name given to the id sequence is now different, these dumps 
> will not restore.  (In this case it will be the same, I'm just 
> illustrating the general problem of hard-coding those sequence names in 
> the dump - I've never liked it :) )

Yeah, I know ... we ought to find some way around that, but I dunno
what yet ...

			regards, tom lane

Thu, 10 Jun 2004 23:15:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [COMMITTERS] pgsql-server: Clean up generation of default names 
> Yeah, I know ... we ought to find some way around that, but I dunno
> what yet ...

My idea, which I tried hacking, but gave up was to do the following:

1. Extend this command:

ALTER SEQUENCE seqname RESTART WITH 17;

to allow:

ALTER SEQUENCE ON table(col) RESTART WITH 17...
or
ALTER SEQUENCE ON table.col RESTART WITH 17...

2. Overload nextval, curval and setval:

SELECT SETVAL('"schema.table"', 'col', 17, false);

3. Or even create a pg_get_sequence() function:

SELECT SETVAL(pg_get_sequence(schema.table, col), 17);

etc.

Chris





Fri, 11 Jun 2004 11:41:22 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: [COMMITTERS] pgsql-server: Clean up generation of default names
> 3. Or even create a pg_get_sequence() function:
> 
> SELECT SETVAL(pg_get_sequence(schema.table, col), 17);

Actually, this is the best solution :)

Chris


Fri, 11 Jun 2004 12:02:21 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: [COMMITTERS] pgsql-server: Clean up generation of default

On Jun 11, 2004, at 1:02 PM, Christopher Kings-Lynne wrote:

>> 3. Or even create a pg_get_sequence() function:
>> SELECT SETVAL(pg_get_sequence(schema.table, col), 17);
>
> Actually, this is the best solution :)

John Hansen and I worked this up. It works, though it's not 
schema-aware, afaict.

create or replace function last_val(
     text     -- tablename
     , text   -- colname
     ) returns bigint
     language 'sql' as '
     select currval(
         (select
             split_part(adsrc,\'\'\'\',2) as seq
         from pg_class
         join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
         join pg_attrdef
             on (pg_attrdef.adnum = pg_attribute.attnum
             and pg_attrdef.adrelid = pg_attribute.attrelid)
         where pg_class.relname = $1
             and pg_attribute.attname = $2)
     );
     ';

Might be a starting point.

Michael Glaesemann
grzm myrealbox com


Fri, 11 Jun 2004 15:16:04 +0900
Michael Glaesemann <grzm@myrealbox.com>
Re: [COMMITTERS] pgsql-server: Clean up generation of default
>>> 3. Or even create a pg_get_sequence() function:
>>> SELECT SETVAL(pg_get_sequence(schema.table, col), 17);
>>
>>
>> Actually, this is the best solution :)

OK, attached is a pg_get_serial_sequence(schema, table, column) function 
.  I have tested it with crazy names and it seems to be good.  It works 
like this:

SELECT setval(pg_get_serial_sequence('public', 'mytable', 'mycol'), 1, 
false);

If someone approves it, i'll work on making it a built-in backend 
function, and make pg_dump use it.

This will also be great for our app, since we would no longer have to 
have hard-coded sequence names in our code.  (For getting last sequence 
val on oid-less tables)

Chris
Fri, 11 Jun 2004 20:51:39 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: [COMMITTERS] pgsql-server: Clean up generation of default
On June 11, 2004 05:51 am, Christopher Kings-Lynne wrote:
> >>> 3. Or even create a pg_get_sequence() function:
> >>> SELECT SETVAL(pg_get_sequence(schema.table, col), 17);
> >>
> >> Actually, this is the best solution :)
>
> OK, attached is a pg_get_serial_sequence(schema, table, column) function
> .  I have tested it with crazy names and it seems to be good.  It works
> like this:
>
> SELECT setval(pg_get_serial_sequence('public', 'mytable', 'mycol'), 1,
> false);

I'd be inclined to make it only take 2 args, table, col  where table can be 
namespace qualified.  This allows people who arn't namespace aware to just do 
SELECT pg_get_serial_sequence('mytable','mycol') and have it return the 
correct item following searchpath..  I would think this would then become 
consistant with the standard behavior.  Not to mention it would also allow 
for easier moving schema form one namespace to another.. 



>
> If someone approves it, i'll work on making it a built-in backend
> function, and make pg_dump use it.
>
> This will also be great for our app, since we would no longer have to
> have hard-coded sequence names in our code.  (For getting last sequence
> val on oid-less tables)
>
> Chris

-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com

Fri, 11 Jun 2004 14:48:23 -0700
Darcy Buskermolen <darcy@wavefire.com>
Re: [COMMITTERS] pgsql-server: Clean up generation of default
> I'd be inclined to make it only take 2 args, table, col  where table can be 
> namespace qualified.  This allows people who arn't namespace aware to just do 
> SELECT pg_get_serial_sequence('mytable','mycol') and have it return the 
> correct item following searchpath..  I would think this would then become 
> consistant with the standard behavior.  Not to mention it would also allow 
> for easier moving schema form one namespace to another.. 

OK, attached is a file with the original function, and an overloaded one 
that just takes table and column.  It searches your current search_path 
to find the first matching table.

Tom, do you have any opinion on whether the former or latter function 
would be a good solution to the pg_dump issue?

Chris
Sat, 12 Jun 2004 18:12:37 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: [COMMITTERS] pgsql-server: Clean up generation of default
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> OK, attached is a file with the original function, and an overloaded one 
> that just takes table and column.  It searches your current search_path 
> to find the first matching table.

The right way to do this at the C level would be to use the same
infrastructure as nextval() does to accept arguments like 'foo' and
'"Foo"."Bar"'.  There's no reason to restrict the two-argument form
to the current search_path.

Given that you do that, I'm not sure that a three-argument form is even
needed.  AFAIR no one has asked for a two-argument form of nextval...

			regards, tom lane

Sat, 12 Jun 2004 13:44:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [COMMITTERS] pgsql-server: Clean up generation of default 
> The right way to do this at the C level would be to use the same
> infrastructure as nextval() does to accept arguments like 'foo' and
> '"Foo"."Bar"'.  There's no reason to restrict the two-argument form
> to the current search_path.

Is it possible to do that in SQL?  eg. is there anything you can do to go:

select '"Foo"."Bar"'::regclassoid;

Or something?

I'm trying to avoid doing it in C as it seems like it would be a pita.

Chris



Sun, 13 Jun 2004 15:32:10 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: [COMMITTERS] pgsql-server: Clean up generation of
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I'm trying to avoid doing it in C as it seems like it would be a pita.

I think it would be simpler in C than this mess in SQL is ;-).  You
would not of course implement it in any way that would look like the
SQL query ... but there are existing utility subroutines for most of
the bits that are being done with joins here.

			regards, tom lane

Sun, 13 Jun 2004 13:24:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [COMMITTERS] pgsql-server: Clean up generation of 