Re: [sqlalchemy] Re: Conditional insert in one transaction

2021-02-05 Thread Mike Bayer
SQLAlchemy will wrap that psycopg2 exception underneath IntegrityError (or at 
least DBAPIError at the bottommost) so you need to catch the 
SQLAlchemy-specific expression.   of course you should also test it with an 
existing row to make sure it actually catches and works!



On Thu, Feb 4, 2021, at 8:42 PM, Vitaly Kruglikov wrote:
> Thanks Mike! I followed up on your suggestion concerning savepoints and came 
> up with the following code snippet. Does this look right to you?
> 
> ```
> try:
> with orm_session.begin_nested():
> orm_session.add(record)
> except psycopg2.errors.UniqueViolation as error: # or is it 
> `sqlalchemy.exc.IntegrityError`???
> # Matching metadata row already inserted by someone else
> # Fetch and return existing record
> 
> # We inserted it!
> return record
> ```
> 

> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/b3002fec-c8ac-4b41-8101-1283455bfe7dn%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f693c8a5-53d4-4bde-ba7d-94fe7ae875dd%40www.fastmail.com.


Re: [sqlalchemy] Re: Conditional insert in one transaction

2021-02-04 Thread Vitaly Kruglikov
Additional background: In the proposed code snippet, I placed the 
try/except around the `begin_nested()` block instead of just the 
`orm_session.add()` statement because the `add()` might not get flushed to 
database until the savepoint is released in the event the session is not 
auto-flushing.

I also came across a nice graphical illustration of the race condition 
associated with concurrent INSERT 
here: http://rachbelaid.com/handling-race-condition-insert-with-sqlalchemy/

On Thursday, February 4, 2021 at 5:42:40 PM UTC-8 Vitaly Kruglikov wrote:

> Thanks Mike! I followed up on your suggestion concerning savepoints and 
> came up with the following code snippet. Does this look right to you?
>
> ```
> try:
> with orm_session.begin_nested():
> orm_session.add(record)
> except psycopg2.errors.UniqueViolation as error: # or is it 
> `sqlalchemy.exc.IntegrityError`???
> # Matching metadata row already inserted by someone else
> # Fetch and return existing record
>
> # We inserted it!
> return record
> ```
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ded97aab-d4f3-498b-aea9-af89d2e411fan%40googlegroups.com.


Re: [sqlalchemy] Re: Conditional insert in one transaction

2021-02-04 Thread Vitaly Kruglikov
Thanks Mike! I followed up on your suggestion concerning savepoints and 
came up with the following code snippet. Does this look right to you?

```
try:
with orm_session.begin_nested():
orm_session.add(record)
except psycopg2.errors.UniqueViolation as error: # or is it 
`sqlalchemy.exc.IntegrityError`???
# Matching metadata row already inserted by someone else
# Fetch and return existing record

# We inserted it!
return record
```

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b3002fec-c8ac-4b41-8101-1283455bfe7dn%40googlegroups.com.


Re: [sqlalchemy] Re: Conditional insert in one transaction

2021-02-03 Thread Mike Bayer
for inserts, not really.   if you want to guard against duplicate, the pattern 
is use a savepoint (begin_nested()), catch integrity error, then use the new 
row if someone else inserted it.this is the more performant option as well 
as opposed to pessimistic locking.

https://docs.sqlalchemy.org/en/14/orm/session_transaction.html#session-begin-nested
  includes an example with merge().


The *other* way to do it is to use INSERT..ON CONFLICT (PostgreSQL, SQLite) or 
INSERT..ON DUPLICATE KEY (MySQL, MariaDB) statements.  These are popular 
choices as well but you need to construct them as statement objects.




On Wed, Feb 3, 2021, at 8:49 PM, Vitaly Kruglikov wrote:
> How can the query/merge or query/add be performed atomically? What would 
> happen if between the two calls another process inserted a row with the same 
> unique key? Wouldn't the `s.merge()` then trigger a 
> `psycopg2.errors.UniqueViolation` exception (in postgres case) when the new 
> row insert is flushed? Is there a way to make the execution of the ORM atomic 
> so that the `s.merge()` would be guaranteed to succeed (barring network 
> errors, etc.)? 
> 

> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d117fb88-2fd5-4006-a60f-424cc25c4bd7n%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/c5176c7b-5c25-4fcd-8891-9f91bf87f554%40www.fastmail.com.


Re: [sqlalchemy] Re: Conditional insert in one transaction

2021-02-03 Thread Vitaly Kruglikov
How can the query/merge or query/add be performed atomically? What would 
happen if between the two calls another process inserted a row with the 
same unique key? Wouldn't the `s.merge()` then trigger a 
`psycopg2.errors.UniqueViolation` exception (in postgres case) when the new 
row insert is flushed? Is there a way to make the execution of the ORM 
atomic so that the `s.merge()` would be guaranteed to succeed (barring 
network errors, etc.)?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d117fb88-2fd5-4006-a60f-424cc25c4bd7n%40googlegroups.com.


Re: [sqlalchemy] Re: Conditional insert in one transaction

2011-06-19 Thread Michael Bayer

On Jun 18, 2011, at 4:32 PM, Eric Ongerth wrote:

 Just curious and learning here  -- There are two separate issues here,
 aren't there?  (1.) Atomicity of the transaction, taken care of by the
 above discussion, and (2.) what if there was a need to have it be not
 only atomic but consume as little time as possible between the read
 and write, let's say for financial purposes?

Right, so the executing two statements at once instead of two statements 
thing in this kind of case is a fleeting optimization.   That is, its a micro 
optimization that is easily nullified by the surrounding context.   Such as, if 
the way the app works in reality is that the row already exists 95% of the 
time, the optimization saves negligible time.  Or if it's trivial to just 
pre-insert the rows in question, or a whole selection of 100 rows can be 
selected at once and just the ones that aren't present can be INSERTed in one 
multi-row statement, would provide even better performance.

Taking a SELECT then an INSERT and making the choice to turn it into a non-ORM, 
single statement, database-specific call is something you'd do once the app is 
up and running, and the separate SELECT/INSERT pair has been observed to be a 
definite bottleneck with no feasible workaround.I.e. a non-premature 
optimization.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.