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. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
