Jim Garrison <jim.garrison@nwea.org> writes:
> Given (pseudocode)
>
> CREATE TABLE kvstore (
> k varchar primary key,
> v varchar);
>
> CREATE OR REPLACE FUNCTION store_key_value_pair(k varchar, v varchar) returns boolean as $$
> BEGIN
> INSERT INTO kvstore (k, v)
> SELECT :k, :v
> WHERE NOT EXISTS (select 1 from kvstore where k = :k);
> RETURN FOUND;
> END;
> $$ LANGUAGE plpgsql;
>
> I have a few questions:
>
> 1) Does INSERT statement set FOUND based on whether or not the row was inserted?
Yes unless triggers/rules are in volved..
> 2) If this is invoked without a transaction in progress, is there any
> guarantee of atomicity between checking the EXISTS and attempting to
> insert the row? If this is being executed in two (or more) sessions,
> can the SELECT succeed but then have the INSERT fail with a
> duplicate-key exception?
You will either be at risk of a race condition or more likely your
insert will try the dupe insert and block waiting for another session
that's already inserted same row to commit/abort. And if other guy does
commit then you will raise a dupe key exception.
Your code is wise trying to avoid a dupe insert if the row is already
existing and visible but you will still need to trap the dupe key
exception when you get one which will happen eventually if there will
be other sessions trying this same insert.
> 3) Will the behavior be different if the invoking processes have a
> transaction in progress?
Every statement you run is a transaction of its own but you are far more
at risk of testing negative for an existing row, proceeding to try the
insert but then hanging because there is the same insert already
pending... if there are longer running complex transactions involved.
Suppose...
session A
begin;
insert into your table key=1
... do more work here...
meanwhile...
session B
test for row and I don't see it
try insert and hang here till commit/abort of session A
session A
commit
session B
Doh!! dupe key error
HTH
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
