On Tue, Sep 6, 2011 at 3:45 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On Tue, Sep 6, 2011 at 1:50 PM, Lincoln Yeoh <lyeoh@xxxxxxxxxxxxx> wrote: >> At 07:02 PM 9/5/2011, J. Hondius wrote: >>> >>> I agree that there are better ways to do this. >>> But for me this works. (legacy driven situation) >>> >>> INSERT INTO tbinitialisatie (col1, col2) >>> SELECT 'x', 'y' >>> FROM tbinitialisatie >>> WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2 >>> = 'y') >>> LIMIT 1 >> >> Hi, >> >> That does not work 100%. Try it with two psql instances. >> >> Do: >> *** psql #1 >> begin; >> INSERT INTO tbinitialisatie (col1, col2) >> SELECT 'x', 'y' >> FROM tbinitialisatie >> WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2 = >> 'y') >> LIMIT 1 ; >> >> *** psql #2 >> begin; >> INSERT INTO tbinitialisatie (col1, col2) >> SELECT 'x', 'y' >> FROM tbinitialisatie >> WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2 = >> 'y') >> LIMIT 1 ; >> commit; >> >> *** psql #1 >> commit; >> >> You should find duplicate inserts. >> >> In most cases the "begin" and "commit" are very close together so you won't >> notice the problem. But one day you might get unlucky. >> >> Your options are: >> a) put a unique constraint and handle the insert errors when they occur >> b) lock the entire table first (affects performance: blocks all selects on >> that table) >> c) use a lock elsewhere (but this requires all applications using the >> database to cooperate and use the lock). >> d) wait for SQL MERGE to be implemented ( but from what I see the current >> proposal seems to require a) or b) anyway: >> http://wiki.postgresql.org/wiki/SQL_MERGE ) > > b) doesn't block reads if you lock in EXCLUSIVE mode. a) is the best > way to go if you prefer to handle errors on the client and/or > concurrency is important...c) otherwise. whoops! meant to say b) otherwise! As far as c) goes, that is essentially an advisory lock for the purpose -- using advisory locks in place of mvcc locks is pretty weak sauce -- they should be used when what you are locking doesn't follow mvcc rules. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
