Skipping the pg_try_advisory_lock function call

[rotten]

I'm attempting to run sqitch against a postgresql compatible database that does not yet have the pg_try_advisory_lock function. Is there a way to run sqitch so that this function is not used?

[rotten]

I should add that I'm using sqitch v1.2.1 from Ubuntu apt repository.

[rotten]

I'll probably try (2) next to see if I can hack something to get it to run since I am much more comfortable with PL/PGSQL than I am with Perl.

[theory]

Oh, I see that Cockroach provides the advisory lock functions, but they're no-ops. That explains why it still calls them.

If RisingWave has PL/pgSQL or just PL/SQL it should be pretty easy, something like:

CREATE OR REPLACE FUNCTION pg_try_advisory_lock(
    bigint
) RETURNS boolean LANGUAGE SQL AS $
   SELECT TRUE;
$$;

CREATE OR REPLACE FUNCTION pg_try_advisory_lock(
    int, int
) RETURNS boolean LANGUAGE SQL AS $
   SELECT TRUE;
$$;

[rotten]

Studying their docs it looks like they do not support sql functions. :-( I'll bring this up in their slack forum. Maybe someone there will have an idea.

[rotten]

Follow up from overnight. RisingWave says they'll support sql functions in their next release (due out sometime in the next few weeks). Additionally they've agreed to do some testing with sqitch (presumably against the next release). While I await the next RisingWave release, I'll just hack the perl code in my local environment to return true. Hopefully I won't have to hack the perl in the production release branches because the new release will have landed by then. Thank you for your help and attention to this matter!

[theory]

Oh cool, let me know if you run into issues. It wouldn't surprise me if RisingWave has significant divergence from Postgres that will require other measures to address. Nice to know there's a Docker image.

[rotten]

I spent some time trying to make a risingwave engine and unfortunately have to give up because perl skills are not strong enough to do it well. I have no problem refactoring all of the relevant queries but once I get into the perl weeds, I'm quickly lost.

With version 1.7.0 of RisingWave - coming out in a couple of weeks I think - I can write SQL functions, so my original advisory lock question is easily resolved by dropping in a couple of functions. The other thing 1.7.0 lets me do is switch from using etcd as the RisingWave "meta" database to using PostgreSQL. This means that when I deploy RisingWave, I also deploy a PostgreSQL database. RisingWave uses this database to keep track of its configuration and manage its cluster.

If I could specify a different database for the sqitch tables than the one that the deploy/revert/verify sql runs against, then I could put the sqitch configuration tables in the RisingWave Meta database. Then run the custom sql I need in the RisingWave database. This approach seems more flexible for any time you don't want to keep the sqitch change management details in the same data store as your data. I could use this to manage Redshift too (or any other pgwire compatible data store) without needing another engine.

• I would create role sqitch and create database risingwave_sqitch owner sqitch in the meta database server for that purpose.

Thoughts?

[rotten]

I'm thinking that the "registry" definition would be able to support an entire URI to a different database resource.

You'd probably have to add target columns to the sqitch tables so that you'd know which target had which migration.

It feels like sqitch can almost support this.

I like the idea of keeping the migration tracking in the same database it is tracking. If you restore backups you know exactly where you are. If you separate the registry and target database, when you restore the target database, it is harder to know for sure that all of the migrations have still been applied. In other words they could get out of sync.

I think for special use cases the risk of being out of sync can be managed. I also think that sqitch is powerful enough, especially with the verify command, to be able to figure out which migrations are missing in a newly restored target, and then be able to re-apply them.

[theory]

Sorry to be late replying to this, been super busy. I think it wouldn't be too big a lift to support a complete URI for the registry database, rather than just as schema name. You're right about the backups and such: that's why it's all in one database.

And yes, if one was managing multiple databases, one would need an extra registry column to identify each and avoid conflicts. That would get tricky, though, because what should be there? Can't just be database name, because different hosts can have the same database name (and I expect they would in a multi-tenant sort of situation). Host plus database? But then what happens when multiple servers are on the same host? Include the port I guess. But also what if the host name changes, like for an upgrade?

It could get tricky to get right. Perhaps it could be a target name, and then one has to keep the target names correct. That solves the problem, but offloads the need for careful naming and planning to the end user.

At any rate, would you like to create a feature request issue for this? Do you know someone who could pick up that work?

[rotten]

I don't know if I can convince any of my perl coding former colleagues to pick this up, but I'll ask.