Re: Mysql -> Postgresql pitfalls

On Mon, 4 Aug 2003, Chad N. Tindel wrote:

> > > > Yes. The documentation very clearly states using sequences instead of
> > > > auto-increment, but it doesn't make it clear that inserting the id's into
> > > > data by hand doesn't cause the sequence to be auto-matically incremented.
> > > > It'd be nice of postgres had a way to trigger an update of the sequence
> > > > value after every insert containing an id clumn.
> >
> > Um, how would this be a bennefit? If you're inserting rows 101-259, how does
> In mysql, when you insert into an auto_increment field and you specify an id,
> all future requests to insert a row without specifying the ID will still work
> properly. In postgres, if you specify the id, your next insert without and
> id will fail because the sequence won't have been updated.

Correct. And MySQL is doing it "wrong" but everyone is used to it.

In Postgresql, there are exact functions for setting the increment.
Imagine this (T1 and T2 represent two different transactions:

T1: begin;
T2: begin;
T1: select nextval('seqname'); <- returns 55
T2: select nextval('seqname'); <- returns 56
T2: insert into parent_table (id,info) values (56,'information');
T1: insert into parent_table (id,info) values (55,'somemoreinformation');
T1: insert into child_table (p_id,info) values (55,'childinfo');
T2: insert into child_table (p_id,info) values (56,'childinfo');
T1: commit;
T2: commit;

If Postgresql autoset the field to the value last inserted, then the
sequence would be reset back to 55 and be ready to reissue 56 on the next
call. Bad news.

Or, imagine I delete a parent row then reinsert it, with a lower value,
then the sequence is reset. Resetting the sequence automatically on
insert is NOT the best way to handle sequences. Setting them by hand
during imports is the preferred method because you'll assume that you're
the only one on the database making changes.

Once you go live, i.e. you've got 200 simultaneous users doing updates,
the last thing you want is some user process accidentally setting your
sequence number to some low number that's already been used just because
they inserted it by hand.

Postgresql does things differently than MySQL, and most of the time it
does, it's been better thought out in postgresql, in terms of impact on
highly paralleled database accesses.

MySQL tends to be developed thinking of convenience instead of handling
the "whoopsies" situations that can be created by programming for
convenience. A lot like Unix versus Windows. Unix isn't just hard for
the fun of it, some things in unix are hard so you'll have to understand
the underlying reasoning before jumping in with both feet. The Windows
methodology tends to be faster to learn and use, but often puts your data
at risk.

> > FWIW, in 7.5 we're likely to implement SQL3 IDENTITY columns ... basically
> > just a SERIAL column where you don't have the option of inserting your own
> > value, you have to take what it gives you.
>
> Interesting... how do you import data from a dump with such columsn?

Easy, after you import the last row, you

select setval('seqname',lastvalue);

on the sequence. Like I said above, it's mostly just a different way of
doing things in Postgresql, and often those different ways are less
obvious, and quite often, being less obvious is actually safer even if
it is a littler harder to learn up front.