search

cockroachdb / docs

CockroachDB user documentation
https://cockroachlabs.com/docs
Creative Commons Attribution 4.0 International
187 stars 455 forks source link

Update psycopg2 docs to reference `autocommit` mode #7073

Open irfansharif opened 4 years ago

irfansharif commented 4 years ago

Irfan Sharif (irfansharif) commented:

Re: Build a Python App with CockroachDB and psycopg2

Issue Description

We elide any mention of psycopg2's autocommit mode, which is necessary to document for statements executed against CRDB that are decidedly not allowed to be as part of transactions. One example is RESTORE, using the code snippet linked above but only swapping in the DDL with a RESTORE statement would fail with psycopg2.InternalError: RESTORE cannot be used inside a transaction.

Suggested Resolution

I believe the thing we're looking for is https://www.psycopg.org/docs/usage.html#transactions-control. Since RESTORE cannot be used as part of the transaction, to address this, we're going to need to set autocommit. From the psycopg2 doc:

A few commands (e.g. CREATE DATABASE, VACUUM…) require to be run outside any transaction: in order to be able to run these commands from Psycopg, the connection must be in autocommit mode: you can use the autocommit property.

I believe this may be more broadly applicable to other DDL commands that aren't entirely safe to be wrapped in txns (though that's something that's currently being worked on on the CRDB side of things)

Jira Issue: DOC-488

rmloveland commented 4 years ago

@irfansharif thanks for filing. Unassigned myself since I am not working on developer docs for the 20.2 cycle (got assigned to geo stuff instead).

@ericharmeling I think this is still your world?

jseldess commented 3 years ago

@ericharmeling, @awoods187, this should go into your backlog.