Re: Article on MySQL vs. Postgres

Hannu Krosing wrote:
>
> Tim Perdue wrote:
> >
> > The Hermit Hacker wrote:
> > > > Further, I have had situations where postgres actually had DUPLICATE
> > > > ids in a primary key field, probably due to some abort or other nasty
> > > > situation in the middle of a commit. How did I recover from That?
> > > > Well, I had to run a count(*) next to each ID and select out the rows
> > > > where there was more than one of each "unique" id, then reinsert those
> > > > rows and drop and rebuild the indexes and reset the sequences.

There a bug report that allowed tuplicate ids in an uniqe field when
SELECT FOR UPDATE was used. Could this be your case ?

---8<-------8<-------8<-------8<-------8<-------8<-------8<-------8<----
gamer=# create table test(i int primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'test_pkey'
for table 'test'
CREATE
gamer=# insert into test values(1);
INSERT 18860 1
gamer=# begin;
BEGIN
gamer=# select * from test for update;
i
---
1
(1 row)

gamer=# insert into test values(1);
INSERT 18861 1
gamer=# commit;
COMMIT
gamer=# select * from test;
i
---
1
1
(2 rows)

gamer=# insert into test values(1);
ERROR: Cannot insert a duplicate key into unique index test_pkey
---8<-------8<-------8<-------8<-------8<-------8<-------8<-------8<----

IIRC the fix was also provided, so it could be fixed in current CVS (the
above
is from 7.0.2, worked the same in 6.5.3)

> > > Odd, were you using transactions here, or transactionless?

Ironically the above has to be using transactions as select for update
works
like this only inside transactions and is thus ineffectif if
transaction=statement;

As multi-command statements are run as a single transaction
(which can't be done from psql as it does its own splittng ;()
so a command like 'select * from test for update;insert into test
values(1);'
has the same effect

> > Does it matter? I suppose it was my programming error that somehow I got
> > duplicate primary keys in a table in the database where that should be
> > totally impossible under any circumstance? Another stupid
> > transactionless program I'm sure.

constraints and transactions are quite different (though connected)
things.

lack of some types of constraints (not null, in (1,2,3)) can be overcome
with careful programming, others like foreign keys or unique can't
unless
transactions are used)

no amount of careful programming will overcome lack of transactions
(except
implementing transactions yourself ;)

-----------
Hannu