search

jackc / tern

The SQL Fan's Migrator
MIT License
850 stars 66 forks source link

Another proposal for selectively disabling transactional migrations #54

Closed jackc closed 2 years ago

jackc commented 2 years ago

See #36 and https://github.com/jackc/tern/pull/17#issuecomment-560457327 for more of the origin of this request.

Some commands cannot be run in a migration. e.g. create index concurrently. Even if DisableTx is used only one statement can be given per migration because the entire migration is sent as a single simple protocol query which is implicitly transactional. See https://github.com/jackc/tern/issues/43 for a failed idea of removing explicit transactions.

There are two major problems.

  1. DisableTx is awkward to use and is a a blunt instrument. i.e. it is not controllable per migration. I propose replacing that with a magic comment per migration. Something like ---- disable-tx ---- at the top.
  2. The only way I can see to have multiple statements in a single migration that is not implicitly transactional is to parse the migration and send each SQL statement as its own simple protocol query message. I'm not super eager to require a SQL parser, but in this case all it needs to do is split each statement. It doesn't need to actually understand the statement. I've already written a parser for pgx to sanitize SQL parameters which detects comments and quoted strings. It might be possible to adapt that to split on any ; that is not in a comment or quote without too much effort.
jackc commented 2 years ago

This approach is implemented on v2-dev and seems to work.