61 reputation
24
bio website
location
age
visits member for 2 years, 2 months
seen Jun 27 '13 at 20:25

Jun
20
awarded  Notable Question
Apr
14
awarded  Notable Question
Mar
14
awarded  Nice Question
Aug
22
awarded  Popular Question
Jul
16
awarded  Popular Question
Oct
31
comment Postgres requires commit or rollback after exception
Hi Steve, Thanks for the info! I realize I was kind of mixing things here...I added the "start transaction" bit to indicate that it was not an auto-commit tx. We started down the road of using SavePoints to achieve consistent behavior across the databases we use. Might have to go down that road after all.
Oct
31
comment Postgres requires commit or rollback after exception
This is a Java app, so we're using the PG 9.0 JDBC driver. I have tested the above through a query tool using the same driver to take our application out of the picture. The second insert statement above yields: ERROR: current transaction is aborted, commands ignored until end of transaction block [SQL State: 25P02]
Oct
31
asked Postgres requires commit or rollback after exception
Oct
29
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
Retries it is. Thanks much for the valuable insight!
Oct
29
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
(continuing...) I wonder if a really cheap (cheesy?) way to make this work is to issue two DELETES followed by the INSERT. In my limited (2 threads) testing, it worked OK, but need to test more to see if that would hold for many threads.
Oct
29
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
Yep, that helps a lot. Looks like we have a couple of options then for PG support:
Oct
26
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
Thanks Mat. While that does seem to be what's happening, there seems to be a flaw in that logic. It seems to me that, in a READ_COMMITTED iso level, then these two statements must succeed inside a tx: DELETE FROM test WHERE ID=1 INSERT INTO test VALUES (1) I mean, if I delete the row and then insert the row, then that insert should succeed. SQLServer gets this right. As it is, I'm having a very hard time dealing with this situation in a product that has to work with both databases.
Oct
26
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
Well, but this doesn't mesh, exactly.
Oct
26
comment Locking issue with concurrent DELETE / INSERT in PostgreSQL
thanks for the tip, but using DEFERRABLE made no difference at all. The doc reads like it should have, but does not.
Oct
26
awarded  Student
Oct
26
asked Locking issue with concurrent DELETE / INSERT in PostgreSQL