Skip to content

Resolve "Prevent transactions being left open by bug in Djangos schema editor"

The is a workaround for a bug in the Django Schema editor. This bug is:

  1. Whilst using connection.schema_editor to edit a schema it builds up deferred_sql which it runs at end
  2. If any of this deferred SQL fails to execute and causes an exception as a result the atomic block inside of the base schema editor will not close the atomic block it opened.
  3. Due to the way the django atomic framework works, this inner transaction will still be rolled back, but only if you've happened to wrap the schema editor usage in an outer atomic block.
  4. However there will still be a pending SAVEPOINT made by the innermost atomic block not properly closed by the connection.schema_editor.
  5. This pending savepoint will mean that when django goes to close the cursor at the end of a the request that had this failure, the connection will be still in a pending transaction.
  6. Pgbouncer will then kill the connection on its side as a pending transaction was still open when the connection was closed. See this for more details https://www.psycopg.org/docs/connection.html#connection.close
  7. Django does not know that pgbouncer did this and assumes the connection is still usable, however when it tries we will get loads of connection already closed errors for that particular worker where the bad transaction happened.

Reproducing this issue

See the provided tests on a way of replicating this issue. Essentially you want to:

  1. Checkout https://gitlab.com/bramw/baserow/-/merge_requests/562 so you can easily cause a index collision as that MR sets the name for the order/id index to be easy given the table id.
  2. Manually create an index on an existing table called tbl_order_id_{REPLACE_WITH_NEXT_FREE_TABLE_NAME}_idx
CREATE index tbl_order_id_{REPLACE_WITH_NEXT_FREE_TABLE_ID}_idx on database_table_{other_table.id}("id", "order")
  1. Create a new table
  2. See the error in the log about the duplicate index
  3. Try to do anything else with Baserow and see the broken state of the connection

Tests performed

  1. Followed the above steps and confirm that now the connection is left in a good state even if the schema setup fails
  2. Can still create fields,tables, delete fields, convert between fields including single select <-> multi select

Merge Request Checklist

  • changelog.md has been updated if required
  • New/updated Premium features are separated correctly in the premium folder
  • The latest Chrome and Firefox have been used to test any new frontend features
  • Documentation has been updated
  • Quality Standards are met
  • Performance: tables are still fast with 100k+ rows, 100+ field tables
  • The redoc API pages have been updated for any REST API changes
  • Our custom API docs are updated for changes to endpoints accessed via api tokens
  • The UI/UX has been updated following UI Style Guide

Closes #773 (closed)

Edited by Nigel Gott

Merge request reports