Skip site navigation (1) Skip section navigation (2)

Re: Mysql -> Postgresql pitfalls

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: "Chad N(dot) Tindel" <chad(at)tindel(dot)net>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, <pgsql-docs(at)postgresql(dot)org>
Subject: Re: Mysql -> Postgresql pitfalls
Date: 2003-08-04 19:06:28
Message-ID: Pine.LNX.4.33.0308041256030.10758-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-docs
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.


In response to

Responses

pgsql-docs by date

Next:From: scott.marloweDate: 2003-08-04 19:26:50
Subject: Re: Mysql -> Postgresql pitfalls
Previous:From: Chad N. TindelDate: 2003-08-04 17:54:15
Subject: Re: Mysql -> Postgresql pitfalls

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group