Re: serial columns & loads misfeature?

On Fri, 28 Jun 2002 18:16:30 -0400, Gregory Seidman
<gss+pg@cs.brown.edu> wrote:
>CREATE TABLE Foo (
>    id int UNIQUE NOT NULL, -- no default yet
>    -- ... whatever else
>    primary key (id)
>);
>
>CREATE FUNCTION next_foo() RETURNS int
>AS 'SELECT COALESCE(max(id), 0)+1 FROM Foo'
>LANGUAGE SQL;
>
>ALTER TABLE Foo ALTER COLUMN id SET DEFAULT next_foo();

Greg, yes this works, but ...

it turns your application into a single user system, because
concurrent transactions could see the same max(id) and therefore try
to insert duplicate keys,

and the performance hit soon gets unacceptable, because select max()
always does a table scan.

Kevin, stick to serials.  They offer the best performance and you will
get used to them.  And you do the migration only once, don't you?

Servus
 Manfred