Re: serial columns & loads misfeature?

On Fri, 28 Jun 2002 18:16:30 -0400, Gregory Seidman
<gss+pg(at)cs(dot)brown(dot)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