Re: mysql replace in postgreSQL?

At 08:24 AM 10/30/2005 -0800, David Fetter wrote:

> > >http://developer.postgresql.org/docs/postgres/plpgsql-control-structure
> s.html#PLPGSQL-ERROR-TRAPPING
> >
> > Erm, doesn't it have the same race conditions?
>
>No, don't believe it does. Have you found some?

Depends on how you do things.

As I mentioned, it's only fine if you have the relevant uniqueness constraint.

For example, if instead of:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

You do:
CREATE TABLE db (a INT, b TEXT);

Then create the function as in the docs.

Then open up two psql sessions.

Session #1
begin;
SELECT merge_db(1, 'david');
select * from db;

Session #2
begin;
SELECT merge_db(1, 'dennis');
select * from db;

Then, do commit in both sessions.

You'll end up with duplicates.

What actually protected the data before was the uniqueness constraint due
to the primary key.

BUT, if you already have the uniqueness constraint, you won't have a
problem mis-inserting duplicates with any of the typical naive methods either.

Not saying the example in the docs is wrong, but it might be misleading to
people who don't fully understand it. That doesn't seem too far fetched to
me given that a fair number here have suggested the usual variations of
"select, update if exist insert if not" and assumed they will work.

Would those methods be fine in non MVCC databases?

Regards,
Link.