Re: Autoincremental value

"Brendan Jurd" <blakjak@blakjak.sytes.net> wrote:

>
> gnari wrote:
>
> >From: <adburne@asocmedrosario.com.ar>:
> >
> >
> >>[mysql implementation of autoincrement as second field in primary key]
> >> ...
> >>and then select * from table1, you get:
> >>field1| field2
> >>------+-------
> >>  1   |  1
> >>  1   |  2
> >>  2   |  1
> >>------+-------
> >
> >[trigger implementation using max]
> > ...

>
> Rather than using an aggregate function ( max() ) on the table, which
> could be expensive over a very great number of rows, why not use a
> sequence?  If it's good enough for a true serial, then it should be good
> enough for this value-dependant one.  You'd still use the trigger, but
> simplify it.  Like so:
>
> CREATE SEQUENCE table1_field2_seq;
>
> CREATE OR REPLACE FUNCTION fill_field2() RETURNS trigger AS '
> BEGIN
>    IF new.field2 IS NULL THEN
>      SELECT nextval( ''table1_field2_seq'' ) INTO new.field2
>    END IF;
>    RETURN new;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER insert_table1 BEFORE INSERT on table1 FOR EACH ROW
>  EXECUTE PROCEDURE fill_field2();
>

how is this any different than a regular serial ?

>
> This gives the same result, without the added burden of running MAX for
> every insert, and because it's a sequence, the results will work even if
> multiple inserts are trying to run at very similar times.

I agree that the use of MAX is weak, but the point was that the OP
wanted the mysql behaviour.

gnari