Allow raw SQL operations to run in migrations with other operations

[andrew-farries]

Currently sql operations have to be run in isolation; they can't be run in the same migration as other operations. This means that migrations like the following example from #936 don't work:

operations:
  - sql:
      up: |-
        CREATE OR REPLACE PROCEDURE setfakename(
          IN p_id integer)
        LANGUAGE 'plpgsql'
        AS $$
        begin
          update users set firstname = 'fake firstname', lastname = 'fake lastname' where id = p_id;
        end;
        $$;
  - add_column:
      table: users
      column:
        name: firstname
        type: VARCHAR(255)
        nullable: false
        unique: false
        pk: false
      up: name
  - add_column:
      table: users
      column:
        name: lastname
        type: VARCHAR(255)
        nullable: true
        unique: false
        pk: false
      up: null
  - drop_column:
      table: users
      column: name
      down: firstname

In this migration we want to update a stored procedure to work with the new columns added by the other operations in the migration. This isn't possible; the sql operation needs to be run as a separate operation.

sql operations need to be run in isolation because the sql operation requires re-reading the schema after it executes in order for pgroll to know what it did (in contrast to other operations, where pgroll can modify the in-memory schema because it knows what the operation did). If we were to re-read the schema partway through a series of other operations, we'd lose the indirections they created in the in-memory schema, meaning pgroll couldn't create the views.

It might be possible to allow sql migrations to occur in migrations with other operations so long as they precede any other operation type.

[xfischer]

I realized there might be a catch in the case above : sql operation cannot succeed if it is run before the other operations, as it uses column names the operations are creating.

[xfischer]

Had an idea around that: pg_query could be used to parse the raw_sql statements, such that we can detect whether schema has been changed by the operations or not (No change in this case as it's just a procedure doing an UPDATE). Then if we have a restriction like "raw_sql is allowed with other operations only if there are no schema changes", that could work, isn't it?

[xfischer]

FYI: there's a nice workaround.

1. create the table schema migration

# file table_migration.yaml
operations:
  - add_column:
      table: users
      column:
        name: firstname
        type: VARCHAR(255)
        nullable: false
        unique: false
        pk: false
      up: name
  - add_column:
      table: users
      column:
        name: lastname
        type: VARCHAR(255)
        nullable: true
        unique: false
        pk: false
      up: null
  - drop_column:
      table: users
      column: name
      down: firstname

run with pgroll start table_migration.yaml. A new public_table_migration schema will be created by pgroll. Important do not complete at this point

2. create the code migration

# file code_migration.yaml
operations:
  - sql:
      up: |-
        CREATE OR REPLACE PROCEDURE setfakename(
          IN p_id integer)
        LANGUAGE 'plpgsql'
        AS $$
        begin
          update users set firstname = 'fake firstname', lastname = 'fake lastname' where id = p_id;
        end;
        $$;

Run this using --use-version-schema and --schema flags, such that this migration should be performed in situ, on the same schema. pgroll start code_migration.yaml --use-version-schema=false --schema=public_table_migration

Then voilà! The code has been added to the same schema!

A little caveat though: AFAIK doing pgroll migrate will not run this last file with the flags. (maybe some ninja yaml comments could instruct pgroll to run with those flags?)