Issues caused by asynchronous migrations

[olepbr]

Hi!

The newly added asynchronous migration feature (#693) brought with it two issues for those who use the CLI to generate SQL plans:

1. Since the generated plans now puts some migrations in pgboss.bam, the database user one runs pg-boss as now needs CREATE privileges (in this case CREATE INDEX). This is unfortunate, as not granting said privileges to the pg-boss user is part of the motivation from handling migrations separately (cf. https://timgit.github.io/pg-boss/#/install )
2. The generated rollback plan from version 27 to 26 fails unless pgboss.start() has been run in the meantime, since it unconditionally tries to drop the job_common_i7 index, which doesn't exist unless the migration in pgboss.bam has been run.

The second issue is easy to fix; just add IF EXISTS to the DROP INDEX command in the rollback script.

The first issue is more thorny. I think the documentation should at least give a warning to monitor the SQL plans for invocations of job_table_run_async(). I also feel like this perhaps warrants a major version bump, given that it could break things for anyone running pg-boss without CREATE permissions. At the same time, I understand that this is part of the package when one doesn't opt-in to pg-boss' automatic migrations :-)

[timgit]

The async migrations are bypassed if migrate=false in the configuration, which I'm assuming you already have in place if you don't want automatic migrations.

Having said that, you bring up a good point on how the migration scripts should be exported from the CLI, since I agree they likely should bypass using bam entirely.

[olepbr]

The async migrations are bypassed if migrate=false in the configuration, which I'm assuming you already have in place if you don't want automatic migrations.

I randomly spotted this when reading the code for start() just now and was about to edit my issue, apologies for the noise on that part :-)