Thread: Re: Autoincrement

Re: Autoincrement

From
Federico Passaro
Date:
Herouth Maoz wrote:

> (redirected to the SQL list because it really has nothing to do with
> interfaces):
>
> At 14:07 +0300 on 17/7/98, Federico Passaro wrote:
>
> >   You are right, but it's better to put the autoincrementing field as
> >   the last one like in:
> >
> > CREATE TABLE cliente (
> >         name    varchar(100) UNIQUE NOT NULL,
> >         username        varchar(8) NOT NULL ,
> >         key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
> >         );
> >
> > This way you can use the sintax
> >
> > insert into cliente values ('JACK', 'postgres');
> >
> > in place of
> >
> > insert into cliente (name, username) values ('JACK', 'postgres');
>
> No! The syntax may look attractive to you, because you have to write less,
> but you will pay for it in performance! I definitely would not put that
> field last. This is because in the current version of Postgres, any fields
> following the first VARCHAR have a performance penalty. So, in order to
> avoid this, you should place all fixed-sized fields at the beginning, and
> then all the variable-length fields.

When I have to pay this penalty: on insert / update / query ?

>
>
> In any case it is always recommeded to explicitly specify the names of all
> the fields in an insert operation, rather than rely on your memory of the
> correct order.

>
>
> > A more robust solution is to use a trigger. Look at the files
> > <PostGreSQL source dir>/contrib/spi/autoinc.*
>
> I am not sure a solution which depends on writing code in C and having
> postgres superuser privileges can be considered "more robust". I'd
> recommend the use of sequences in any case.

Could you explain your assertion please: the C code I mentioned is ready and
well tested ....

>
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma

federico



Re: [SQL] Re: Autoincrement

From
Herouth Maoz
Date:
At 11:06 +0300 on 20/7/98, Federico Passaro wrote:


> When I have to pay this penalty: on insert / update / query ?

Query, I think.

> > > A more robust solution is to use a trigger. Look at the files
> > > <PostGreSQL source dir>/contrib/spi/autoinc.*
> >
> > I am not sure a solution which depends on writing code in C and having
> > postgres superuser privileges can be considered "more robust". I'd
> > recommend the use of sequences in any case.
>
> Could you explain your assertion please: the C code I mentioned is ready and
> well tested ....

As far as I understand, the autoinc trigger increments the value of the
field on both insert and update. If you want to do it only for insert
(which is what you have to do if the field is to be used as a key), you
have to change the code. That opens a can of bugs.

Besides, the warning is general. Things that appear in the contrib
directory are not actually guaranteed to be up to date - are they? The
contributors may not submit updated versions if there are any future
versions of Postgres. Thus, you may remain without a function.

The funny thing is that the autoinc trigger actually relies on the builtin
sequences, thus it is almost the same, but you rely on external code for
your, and do a lot of work you didn't want to do (create function, create
trigger).


Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma