Re: [GENERAL] conditional insert
At 03:51 AM 9/8/2011, Merlin Moncure wrote: yeah -- but you only need to block selects if you are selecting in the inserting transaction (this is not a full upsert). if both writers are doing: begin; lock table foo exclusive; insert into foo select ... where ...; commit; is good enough. btw even if you are doing upsert pattern (lock...select for update...insert/update), you'd be fine with straight exclusive locks because the 'for update' lock takes a higher lock that is blocked by exclusive. A basic rule of thumb is to try and not fully block readers unless absolutely necessary...basically maintenance operations. Yeah it works if all the inserters do the lock table (or select for update), and provides better performance. But if you're paranoid and lazy - a full lock will ensure that your code won't get dupe errors even if someone else's code or manual control doesn't do the lock table (they might get the dupe errors[1], but that's their problem ;) ). So your code can safely assume that any DB errors that occur are those that deserve a full rollback of everything (which is what Postgresql likes by default). This means fewer scenarios to handle so you don't need to write as much code, nor document and support as much code ;). Link. [1] I'm assuming a unique constraint is present- the locking is to simplify things. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
At 03:51 AM 9/8/2011, Merlin Moncure wrote: Don't you have to block SELECTs so that the SELECTs get serialized? Otherwise concurrent SELECTs can occur at the same time, find no existing rows, then all the inserts proceed and you get errors (or dupes). That's how Postgresql still works right? I haven't really been keeping up. yeah -- but you only need to block selects if you are selecting in the inserting transaction (this is not a full upsert). if both writers are doing: begin; lock table foo exclusive; insert into foo select ... where ...; commit; is good enough. btw even if you are doing upsert pattern (lock...select for update...insert/update), you'd be fine with straight exclusive locks because the 'for update' lock takes a higher lock that is blocked by exclusive. A basic rule of thumb is to try and not fully block readers unless absolutely necessary...basically maintenance operations. Oh wait, now I think I get it. lock table foo exclusive will block the inserts too, so I wouldn't get dupe errors even if other transactions blindly insert dupes at the same time. The other transactions might get the dupe errors, but mine won't as long as it selects first and only inserts if there are no rows at that point. Is that correct? Link. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On Thu, Sep 8, 2011 at 9:14 AM, Lincoln Yeoh ly...@pop.jaring.my wrote: At 03:51 AM 9/8/2011, Merlin Moncure wrote: Don't you have to block SELECTs so that the SELECTs get serialized? Otherwise concurrent SELECTs can occur at the same time, find no existing rows, then all the inserts proceed and you get errors (or dupes). That's how Postgresql still works right? I haven't really been keeping up. yeah -- but you only need to block selects if you are selecting in the inserting transaction (this is not a full upsert). if both writers are doing: begin; lock table foo exclusive; insert into foo select ... where ...; commit; is good enough. btw even if you are doing upsert pattern (lock...select for update...insert/update), you'd be fine with straight exclusive locks because the 'for update' lock takes a higher lock that is blocked by exclusive. A basic rule of thumb is to try and not fully block readers unless absolutely necessary...basically maintenance operations. Oh wait, now I think I get it. lock table foo exclusive will block the inserts too, so I wouldn't get dupe errors even if other transactions blindly insert dupes at the same time. The other transactions might get the dupe errors, but mine won't as long as it selects first and only inserts if there are no rows at that point. Is that correct? correct -- your transactions never get dup errors and external transactions only get them if they, say, select without update before the upsert (which is a bug any way you slice it). fully blocking readers on a high traffic table is a good way to crash your application. bring this issue up to any 'sql server admin' and they'll start to develop a nervous tic... merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
At 04:04 AM 9/8/2011, Andrew Sullivan wrote: On Wed, Sep 07, 2011 at 02:51:32PM -0500, Merlin Moncure wrote: @andrew s: going SERIALIZABLE doesn't help if you trying to eliminate cases that would push you into retrying the transaction. Well, no, of course. But why not catch the failure and retry? I guess I just don't get the problem, since I hear people say this all the time. (I mean, I've also seen places where 'upsert' would be cool, but it doesn't seem trivial to do in a general way and you can do this with catch-serialization-error-and-retry, I think?) Doesn't catching the failure and retrying mean writing more code? Need extra code: 1) to handle the savepoint. 2) to correctly distinguish between retryable errors and nonretryable errors. 3) to retry transactions. More code to debug, test, document and support :). In contrast, the lock table, insert if row does not exist, update if it exists and rollback everything if stuff happens seems simpler to do correctly. I personally prefer to leave as much code writing to others (e.g. those writing Postgresql, programming languages and libraries), because I assume they are much better at writing code than I am. Plus I usually don't have to document and support their code ;). Regards, Link. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On Thu, Sep 08, 2011 at 10:31:39PM +0800, Lincoln Yeoh wrote: Doesn't catching the failure and retrying mean writing more code? Well, yes. OTOH, if you want to use upsert and you have to use other database systems too, then you'll need that other code also, since it's not standard. There isn't an easy answer here. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
At 05:23 AM 9/7/2011, Merlin Moncure wrote: On Tue, Sep 6, 2011 at 3:45 PM, Merlin Moncure mmonc...@gmail.com wrote: 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 Don't you have to block SELECTs so that the SELECTs get serialized? Otherwise concurrent SELECTs can occur at the same time, find no existing rows, then all the inserts proceed and you get errors (or dupes). That's how Postgresql still works right? I haven't really been keeping up. From what I see this (UPSERT/MERGE) has been a common problem/query over the years but it's not in a Postgresql FAQ and many people seem to be using methods that don't actually work. Google shows that many are even recommending those methods to others. Postgresql might still get blamed for the resulting problems. Regards, Link. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On Wed, Sep 07, 2011 at 11:45:11PM +0800, Lincoln Yeoh wrote: Don't you have to block SELECTs so that the SELECTs get serialized? If you want to do that, why wouldn't you just use serializable mode? A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On Wed, Sep 7, 2011 at 10:45 AM, Lincoln Yeoh ly...@pop.jaring.my wrote: At 05:23 AM 9/7/2011, Merlin Moncure wrote: On Tue, Sep 6, 2011 at 3:45 PM, Merlin Moncure mmonc...@gmail.com wrote: 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 Don't you have to block SELECTs so that the SELECTs get serialized? Otherwise concurrent SELECTs can occur at the same time, find no existing rows, then all the inserts proceed and you get errors (or dupes). That's how Postgresql still works right? I haven't really been keeping up. yeah -- but you only need to block selects if you are selecting in the inserting transaction (this is not a full upsert). if both writers are doing: begin; lock table foo exclusive; insert into foo select ... where ...; commit; is good enough. btw even if you are doing upsert pattern (lock...select for update...insert/update), you'd be fine with straight exclusive locks because the 'for update' lock takes a higher lock that is blocked by exclusive. A basic rule of thumb is to try and not fully block readers unless absolutely necessary...basically maintenance operations. From what I see this (UPSERT/MERGE) has been a common problem/query over the years but it's not in a Postgresql FAQ and many people seem to be using methods that don't actually work. Google shows that many are even recommending those methods to others. Postgresql might still get blamed for the resulting problems. yeah -- there are two basic ways to do upsert -- a) table lock b) row lock with loop/retry (either in app or server side via procedure). I greatly prefer a) for simplicity's sake unless you are shooting for maximum possible concurrency. @andrew s: going SERIALIZABLE doesn't help if you trying to eliminate cases that would push you into retrying the transaction. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On Wed, Sep 07, 2011 at 02:51:32PM -0500, Merlin Moncure wrote: @andrew s: going SERIALIZABLE doesn't help if you trying to eliminate cases that would push you into retrying the transaction. Well, no, of course. But why not catch the failure and retry? I guess I just don't get the problem, since I hear people say this all the time. (I mean, I've also seen places where 'upsert' would be cool, but it doesn't seem trivial to do in a general way and you can do this with catch-serialization-error-and-retry, I think?) A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On Wed, Sep 7, 2011 at 3:04 PM, Andrew Sullivan a...@crankycanuck.ca wrote: On Wed, Sep 07, 2011 at 02:51:32PM -0500, Merlin Moncure wrote: @andrew s: going SERIALIZABLE doesn't help if you trying to eliminate cases that would push you into retrying the transaction. Well, no, of course. But why not catch the failure and retry? I guess I just don't get the problem, since I hear people say this all the time. (I mean, I've also seen places where 'upsert' would be cool, but it doesn't seem trivial to do in a general way and you can do this with catch-serialization-error-and-retry, I think?) good points, but consider that savepoints have a certain amount of performance overhead, and there may be some dependent client side processing that is non-trivial to roll back. Also, if you have a lot of contention, things can get nasty very quickly -- a full lock is reliable, simple, and fast, and can be done in one round trip. Any solution that doesn't have loops is inherently more robust than one that does. I'll rest my case on that point -- consider very carefully that the upsert loop example presented in the docs for years was vulnerable to an infinite loop condition that was caught by one of our users in production. That completely turned me off towards that general method of dealing with these types of problems unless there is really no other reasonable way to do it. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
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 ) You could do both a) and b) too. Or both a) and c) (if you don't want insert errors in the cooperating apps and want to allow other selects during the transaction). Regards, Link. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On Tue, Sep 6, 2011 at 1:50 PM, Lincoln Yeoh ly...@pop.jaring.my 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On Tue, Sep 6, 2011 at 3:45 PM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Sep 6, 2011 at 1:50 PM, Lincoln Yeoh ly...@pop.jaring.my 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] conditional insert
Hi follk i trying to performe a conditional insert into a table, indeed, what i'm trying to do is not insert a record into the table if that record exist googleling i found something like insert into XX values (1,2,3) where not exist (select ); but i'm having and error near where... anyone knows how do i can perfome this insert? thanks p
Re: [GENERAL] conditional insert
Στις Monday 05 September 2011 12:38:34 ο/η Pau Marc Muñoz Torres έγραψε: Hi follk i trying to performe a conditional insert into a table, indeed, what i'm trying to do is not insert a record into the table if that record exist thats why primary/unique keys are for. isolate the columns which you consider to be a correct unique key and create a unique key on them. thereis no notion of conditional insert that i know of. googleling i found something like insert into XX values (1,2,3) where not exist (select ); but i'm having and error near where... anyone knows how do i can perfome this insert? thanks p -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On 05/09/2011 10:38, Pau Marc Muñoz Torres wrote: Hi follk i trying to performe a conditional insert into a table, indeed, what i'm trying to do is not insert a record into the table if that record exist googleling i found something like insert into XX values (1,2,3) where not exist (select ); but i'm having and error near where... Shouldn't it be EXISTS, not EXIST? Anyway, what is the error you're getting? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
Pau Marc Muñoz Torres, 05.09.2011 11:38: Hi follk i trying to performe a conditional insert into a table, indeed, what i'm trying to do is not insert a record into the table if that record exist googleling i found something like insert into XX values (1,2,3) where not exist (select ); but i'm having and error near where... anyone knows how do i can perfome this insert? INSERT INTO xxx SELECT 1,2,3 WHERE NOT EXISTS (SELECT ...) Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] conditional insert
On 09/05/2011 12:38 PM, Pau Marc Muoz Torres wrote: Hi follk i trying to performe a conditional insert into a table, indeed, what i'm trying to do is not insert a record into the table if that record exist googleling i found something like insert into XX values (1,2,3) where not exist (select ); but i'm having and error near where... anyone knows how do i can perfome this insert? thanks p You can either do an Insert(...) select... from...where... or you can add a rule to the table that checks if the key exists and if so, do an update or nothing instead.
Re: [GENERAL] conditional insert
i don't see it clear, let me put an example
i got the following table
molec varchar(30)
seq varchar(100)
where I insert my values
lets image that i have a record introduced as ('ubq', 'aadgylpittrs')
how i can prevent to insert another record where molec='ubq' ?
thanks
2011/9/5 Thomas Kellerer spam_ea...@gmx.net
Pau Marc Muñoz Torres, 05.09.2011 11:38:
Hi follk
i trying to performe a conditional insert into a table, indeed, what
i'm trying to do is not insert a record into the table if that record exist
googleling i found something like
insert into XX values (1,2,3) where not exist (select );
but i'm having and error near where...
anyone knows how do i can perfome this insert?
INSERT INTO xxx
SELECT 1,2,3
WHERE NOT EXISTS (SELECT ...)
Regards
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
--
*Pau Marc Muñoz Torres*
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: (+34)935 86 89 39*
Email : paumarc.mu...@bioinf.uab.cat*
Re: [GENERAL] conditional insert
On 09/05/2011 01:37 PM, Pau Marc Muoz Torres wrote:
i don't see it clear, let me put an example
i got the following table
molec varchar(30)
seq varchar(100)
where I insert my values
lets image that i have a record introduced as ('ubq',
'aadgylpittrs')
how i can prevent to insert another record where molec='ubq' ?
thanks
Either put a unique constraint on molec or do
insert into tbl(molec,seq)
select 'ubq', 'aadgylpittrs' where not exists(select molec from tbl
where molec='ubq')
Re: [GENERAL] conditional insert
Ok , thanks Sim, now i see it
P
2011/9/5 Sim Zacks s...@compulab.co.il
**
On 09/05/2011 01:37 PM, Pau Marc Muñoz Torres wrote:
i don't see it clear, let me put an example
i got the following table
molec varchar(30)
seq varchar(100)
where I insert my values
lets image that i have a record introduced as ('ubq', 'aadgylpittrs')
how i can prevent to insert another record where molec='ubq' ?
thanks
Either put a unique constraint on molec or do
insert into tbl(molec,seq)
select 'ubq', 'aadgylpittrs' where not exists(select molec from tbl where
molec='ubq')
--
*Pau Marc Muñoz Torres*
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: (+34)935 86 89 39*
Email : paumarc.mu...@bioinf.uab.cat*
Re: [GENERAL] conditional insert
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
Pau Marc Muoz Torres schreef:
Ok , thanks Sim, now i see it
P
2011/9/5 Sim Zacks s...@compulab.co.il
On 09/05/2011 01:37 PM, Pau Marc Muoz Torres
wrote:
i don't see it clear, let me put an example
i got the following table
molec varchar(30)
seq varchar(100)
where I insert my values
lets image that i have a record introduced as ('ubq', 'aadgylpittrs')
how i can prevent to insert another record where molec='ubq' ?
thanks
Either put a unique constraint on molec or do
insert into tbl(molec,seq)
select 'ubq', 'aadgylpittrs' where not exists(select molec from tbl
where molec='ubq')
--
Pau Marc Muoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telfon: (+34)935 86 89 39
Email : paumarc.mu...@bioinf.uab.cat
