Re: how do i avoid multiple sessions from inserting the

You might have duplicate john doeos with diffent ID's

2/24/2003 9:51:40 AM, Kolus Maximiliano <Kolus.maximiliano@bcr.com.ar> wrote:

You might have duplicate john doeos with diffent ID's

I'm working on an emtpy table... besides, it's a syntax error.

Kolus Maximiliano <Kolus.maximiliano@bcr.com.ar> writes:

I tried, but it's giving me an error, as if INSERT wouldn't like the WHERE:
INSERT INTO users (email) VALUES ('john@doe.com')
WHERE NOT EXISTS
(SELECT id FROM users WHERE email='john@doe.com');
ERROR: parser: parse error at or near "WHERE"

This is not correct syntax: INSERT...VALUES doesn't take WHERE.
But INSERT...SELECT does:

INSERT INTO users (email)
SELECT 'john@doe.com'
WHERE NOT EXISTS
(SELECT id FROM users WHERE email='john@doe.com');

However, as a method of avoiding duplicate-key errors this is useless :-(

In my opinion the easiest approach is to just go ahead and roll back
the transaction when you get a dup-key error, and try again from the
top. So:

try UPDATE; if succeed then done
else try INSERT; if succeed then done
else ROLLBACK, start again

In practice rollbacks are going to be pretty infrequent, so this is
not inefficient --- certainly no worse than any other solution.

regards, tom lane

INSERT INTO users (email) VALUES ('john@doe.com')
WHERE NOT EXISTS
(SELECT id FROM users WHERE email='john@doe.com');

ERROR: parser: parse error at or near "WHERE"

(Btw, i didnt know that INSERT would accept a WHERE clause)

Exactly. It will not.

You might do something like

insert into users (email)
select 'john@doe.com' where not exists (select 1 from users where email = 'john@doe.com');

This should work (syntactically), but, as I mentioned earlier, I doubt it will solve your problem, because it is still possible that somebody
else is inserting the same row right this moment, in which case your subquery will not see it until the other quy commits anyway, and you will have
the same problem as before...

You have to either lock the table before checking if the row exists, or be able to handle the error you get after insert
(in which case, you do not really need to check if it exists first - go straight to insert, and, if it fails, ignore the error)

Dima

In my opinion the easiest approach is to just go ahead and roll back
the transaction when you get a dup-key error, and try again from the
top. So:

I tried something slightly different:

* LOCK the table in SHARE ROW EXCLUSIVE mode, this would allow me to use the
data in the users table when i'm trying to update it (there are parts where
users are just queried, not queried & added). Since this lock collides with
itself, two processes trying to update the users table wont be able to do so
at the same time.
* SELECT (search for the user)
* IF NOT FOUND ... INSERT

This logic is inside a function -user_select_or_create
(varchar(256)-, but i had some problems:

* I couldn't use BEGIN TRANSACTION nor COMMIT/ROLLBACK. (I'm not a pg guru,
but, could it be because the function itself is considered a transaction?).

* If i use LOCK TABLE users IN SHARE ROW EXCLUSIVE MODE in two psql session
it works (one session gets the lock, the other waits). But inside the
function it seems to be ignored (i had the dup key problem).

* I thought it could be the auto-commit thing, so i tried this: "SET
TRANSACTION ISOLATION LEVEL SERIALIZABLE;" inside the function, when i do so
i get this: "ERROR: SET TRANSACTION ISOLATION LEVEL must be called before
any query". There isnt anything between the "BEGIN" and the "SET TRANS...",
i dont know which query is it talking about (i call this function by doing
SELECT user_select_or_create('john@doe.com'), could it be *this* query?).

try UPDATE; if succeed then done
else try INSERT; if succeed then done
else ROLLBACK, start again
In practice rollbacks are going to be pretty infrequent, so this is
not inefficient --- certainly no worse than any other solution.

Can errors on INSERT commands be ignored?. I mean, would the
transaction be aborted in that case?.

Kolus Maximiliano <Kolus.maximiliano@bcr.com.ar> writes:

I tried something slightly different:
* LOCK the table in SHARE ROW EXCLUSIVE mode,

Well, sure, if you don't mind giving up concurrency of writers completely.
How likely is a conflict, anyway? If it's very probable then you're not
losing much, but if it's not then this isn't a good approach.

* If i use LOCK TABLE users IN SHARE ROW EXCLUSIVE MODE in two psql session
it works (one session gets the lock, the other waits). But inside the
function it seems to be ignored (i had the dup key problem).

Within a function the snapshot doesn't get updated, so you cannot see
results of other transactions that commit after the function starts.
This means you pretty much have to issue the LOCK as a separate
interactive command.

regards, tom lane

I tried something slightly different:
* LOCK the table in SHARE ROW EXCLUSIVE mode,

Well, sure, if you don't mind giving up concurrency of writers completely.
How likely is a conflict, anyway? If it's very probable then you're not
losing much, but if it's not then this isn't a good approach.

There are only two tables that would have such conflicts: users and
hosts (as i said before, it's a system like ordb.org, but for vulnerable
proxies). This has been working for a while and i'm new migrating the whole
thing to this new schema (yes, the current one has this concurrency problem
as well).

So far i have received over 540,000 emails (there are a lot of
automated spam detection systems that mail this address) and this number
grows in the order of hundreds per day. All i have to do for testing
concurrency problems is carbon-copy those emails to the "testing" address
and watch the flames in syslog :) (it takes only ten minutes to see one).

Within a function the snapshot doesn't get updated, so you cannot see
results of other transactions that commit after the function starts.
This means you pretty much have to issue the LOCK as a separate
interactive command.

I did this, i issued a BEGIN TRANSACTION & CO before calling this
function from the C++ program that uses the database (the email parser) and
so far it seems to work (haven't seen any dup key error yet).

Thanks for the tip.