Mercurial > p > roundup > code
comparison roundup/backends/back_postgresql.py @ 6610:db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
checkpoint_data and restore_connection_on_error used to commit() and
rollback() the db connection. This causes additional I/O and load.
Changed them to use 'SAVEPOINT name' and 'ROLLBACK TO name' to get a
faster method for handling errors within a tranaction.
One thing to note is that postgresql (unlike SQL std) doesn't
overwrite an older savepoint with he same name. It keeps all
savepoints but only rolls back to the newest one with a given name.
This could be a resource issue. I left a commented out release
statement in case somebody runs into an issue due to too many
savepoints. I expect it to slow down the import but....
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Sat, 29 Jan 2022 11:29:36 -0500 |
| parents | 0d99ae7c8de6 |
| children | bdd28b244839 |
comparison
equal
deleted
inserted
replaced
| 6609:b6452b669720 | 6610:db3f0ba75b4a |
|---|---|
| 201 # the necessary tables (in a parallel connection!) | 201 # the necessary tables (in a parallel connection!) |
| 202 self.commit() | 202 self.commit() |
| 203 self._add_fts_table() | 203 self._add_fts_table() |
| 204 self.commit() | 204 self.commit() |
| 205 | 205 |
| 206 def checkpoint_data(self): | 206 def checkpoint_data(self, savepoint="importing"): |
| 207 """Commit the state of the database. Allows recovery/retry | 207 """Create a subtransaction savepoint. Allows recovery/retry |
| 208 of operation in exception handler because postgres | 208 of operation in exception handler because |
| 209 requires a rollback in case of error generating exception | 209 postgres requires a rollback in case of error |
| 210 generating exception. Used with | |
| 211 restore_connecion_on_error to handle uniqueness | |
| 212 conflict in import_table(). | |
| 210 """ | 213 """ |
| 211 self.commit() | 214 # Savepoints take resources. Postgres keeps all |
| 212 | 215 # savepoints (rather than overwriting) until a |
| 213 def restore_connection_on_error(self): | 216 # commit(). If an import fails because of a resource |
| 214 """Postgres leaves a cursor in an unusable state after | 217 # issue with savepoints, uncomment this line. I |
| 215 an error. Rollback the transaction to recover and | 218 # expect it will slow down the import but it should |
| 216 permit a retry of the failed statement. Used with | 219 # eliminate any issue with stored savepoints and |
| 217 checkpoint_data to handle uniqueness conflict in | 220 # resource use. |
| 218 import_table() | 221 # |
| 222 # self.sql('RELEASE SAVEPOINT %s' % savepoint) | |
| 223 self.sql('SAVEPOINT %s' % savepoint) | |
| 224 | |
| 225 def restore_connection_on_error(self, savepoint="importing"): | |
| 226 """Postgres leaves a connection/cursor in an unusable state | |
| 227 after an error. Rollback the transaction to a | |
| 228 previous savepoint and permit a retry of the | |
| 229 failed statement. Used with checkpoint_data to | |
| 230 handle uniqueness conflict in import_table(). | |
| 219 """ | 231 """ |
| 220 self.rollback() | 232 self.sql('ROLLBACK TO %s' % savepoint) |
| 221 | 233 |
| 222 def create_version_2_tables(self): | 234 def create_version_2_tables(self): |
| 223 # OTK store | 235 # OTK store |
| 224 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), | 236 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), |
| 225 otk_value TEXT, otk_time REAL)''') | 237 otk_value TEXT, otk_time REAL)''') |