Could someone please give me a quick tip as to where in the source code the
bit that auto-generates sequence names is?

I plan to patch it to stop it generating conflicting names...

Cheers,

Chris


Tue, 13 Nov 2001 18:00:59 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Quick question
On 13 Nov 2001 at 18:00 (+0800), Christopher Kings-Lynne wrote:
| Could someone please give me a quick tip as to where in the source code the
| bit that auto-generates sequence names is?

I used cscope (http://cscope.sourceforge.net/) and did a text search
for 'implicit sequence'.  A bit of backtracking from there led me to
src/backend/parser/anaylyz.c:783
   sname = makeObjectName(cxt->relname, column->colname, "seq");

That function is in the same file, and has some comments related to
name collision above it.  I believe this is where you'll want to work.

cheers. 
  brent

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman

Tue, 13 Nov 2001 07:51:34 -0500
Brent Verner <brent@rcfile.org>
Re: Quick question
> On 13 Nov 2001 at 18:00 (+0800), Christopher Kings-Lynne wrote:
> | Could someone please give me a quick tip as to where in the source code the
> | bit that auto-generates sequence names is?
> 
> I used cscope (http://cscope.sourceforge.net/) and did a text search
> for 'implicit sequence'.  A bit of backtracking from there led me to
> src/backend/parser/anaylyz.c:783
>    sname = makeObjectName(cxt->relname, column->colname, "seq");
> 
> That function is in the same file, and has some comments related to
> name collision above it.  I believe this is where you'll want to work.

I think we handled this.  Have you tried 7.2 beta2?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 13 Nov 2001 12:33:15 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Quick question
On 13 Nov 2001 at 12:33 (-0500), Bruce Momjian wrote:
| > On 13 Nov 2001 at 18:00 (+0800), Christopher Kings-Lynne wrote:
| > | Could someone please give me a quick tip as to where in the source code the
| > | bit that auto-generates sequence names is?
| > 
| > I used cscope (http://cscope.sourceforge.net/) and did a text search
| > for 'implicit sequence'.  A bit of backtracking from there led me to
| > src/backend/parser/anaylyz.c:783
| >    sname = makeObjectName(cxt->relname, column->colname, "seq");
| > 
| > That function is in the same file, and has some comments related to
| > name collision above it.  I believe this is where you'll want to work.
| 
| I think we handled this.  Have you tried 7.2 beta2?

I believe the following demonstrates the problem Christopher would
like to solve.

brent=# select version();
                            version                            
---------------------------------------------------------------
 PostgreSQL 7.2b2 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

brent=# create table test (id serial);
NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'test_id_key' for table 'test'
ERROR:  Relation 'test_id_seq' already exists


  ISTM, that these sequences created by way of a SERIAL type should 
be named "pg_serial_test_id_HASH" or similar, since they are system
(bookkeeping) rels.  Also, I /personally/ would like it if the sequence
was dropped along with the table using it, provided that no other atts 
in the system are using it.  I'm not sure right now if this behavior
is even feasible.

  That said, there is certainly immediate benefit in making sure the 
CREATE TABLE with a SERIAL will succeed if the (initial choice for) 
sequence name already exists.

cheers.
  brent

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman

Tue, 13 Nov 2001 12:59:04 -0500
Brent Verner <brent@rcfile.org>
Re: Quick question
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Could someone please give me a quick tip as to where in the source code the
> bit that auto-generates sequence names is?
> I plan to patch it to stop it generating conflicting names...

Before you start hacking, you might want to discuss your proposed
change in behavior with the rest of us.  The code is simple; figuring
out what it really Ought To Do is not so simple (and has been discussed
before, BTW; read the archives).

			regards, tom lane

Tue, 13 Nov 2001 13:15:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Quick question 
Brent Verner <brent@rcfile.org> writes:
>   ISTM, that these sequences created by way of a SERIAL type should 
> be named "pg_serial_test_id_HASH" or similar, since they are system
> (bookkeeping) rels.  Also, I /personally/ would like it if the sequence
> was dropped along with the table using it, provided that no other atts 
> in the system are using it.

I think there are two completely different issues here: one is what
name to use for the auto-generated sequence, and the other is whether
(when) to drop the sequence if the table is dropped.  Fixing the
latter issue would reduce but not entirely eliminate the issue of
name collisions.

IIRC, the major objection to the notion of adding random hash characters
to the auto-generated names was that people wanted to be able to predict
the names.  There was a long discussion about this a couple years back
when we settled on the present algorithm.  Please search the archives
a bit if you want to re-open that issue.

			regards, tom lane

Tue, 13 Nov 2001 13:37:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Quick question 
> > That function is in the same file, and has some comments related to
> > name collision above it.  I believe this is where you'll want to work.
>
> I think we handled this.  Have you tried 7.2 beta2?

Oh, ok.  Hmmm - haven't really checked to tell the truth.  All I see in the
HISTORY is: "Truncate extra-long sequence names to a reasonable value (Tom)"
That doesn't seem to be it.  I'll try it when I get home.

Chris


Wed, 14 Nov 2001 09:32:26 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Quick question
> I think there are two completely different issues here: one is what
> name to use for the auto-generated sequence, and the other is whether
> (when) to drop the sequence if the table is dropped.  Fixing the
> latter issue would reduce but not entirely eliminate the issue of
> name collisions.

Hmmm?  No way - see below.

> IIRC, the major objection to the notion of adding random hash characters
> to the auto-generated names was that people wanted to be able to predict
> the names.  There was a long discussion about this a couple years back
> when we settled on the present algorithm.  Please search the archives
> a bit if you want to re-open that issue.

I will search the archives, but I'll explain my thoughts here a well.

Well, what's the problem with appending a number - that's how index names
get generated.

This is my horrible schema that forced me to abandon using SERIAL in favour
of explicit CREATE SEQUENCE statements:

BEGIN;

-- Categories of foods
CREATE TABLE medidiets_categories_foods (
	category_id SERIAL,
	description varchar(255) NOT NULL,
	PRIMARY KEY(category_id)
);

-- Categories of recipes
CREATE TABLE medidiets_categories_rec (
	category_id SERIAL,
	description varchar(255) NOT NULL,
	PRIMARY KEY(category_id)
);

COMMIT;

Both of these SERIALs are given the same name - it's a real pain.

Chris


Wed, 14 Nov 2001 09:51:08 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Quick question 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Both of these SERIALs are given the same name - it's a real pain.

A large part of the problem would go away if we just doubled
NAMEDATALEN, which is on the to-do-soon list anyway...

			regards, tom lane

Tue, 13 Nov 2001 21:12:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Quick question 
> > I think there are two completely different issues here: one is what
> > name to use for the auto-generated sequence, and the other is whether
> > (when) to drop the sequence if the table is dropped.  Fixing the
> > latter issue would reduce but not entirely eliminate the issue of
> > name collisions.
> 
> Hmmm?  No way - see below.
> 
> > IIRC, the major objection to the notion of adding random hash characters
> > to the auto-generated names was that people wanted to be able to predict
> > the names.  There was a long discussion about this a couple years back
> > when we settled on the present algorithm.  Please search the archives
> > a bit if you want to re-open that issue.
> 
> I will search the archives, but I'll explain my thoughts here a well.
> 
> Well, what's the problem with appending a number - that's how index names
> get generated.
> 
> This is my horrible schema that forced me to abandon using SERIAL in favour
> of explicit CREATE SEQUENCE statements:

Added to TODO:

  * Have SERIAL generate non-colliding sequence names when we have
    auto-destruction

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 21 Nov 2001 20:34:11 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Quick question