Would switching to postgres be a solution? It isn't clear that the problem is gen2/gen3, and both can run against sqlite or postgres. As a reference point, we're switching from sqlite to postgres (within gen3) on Cerro Pachón to avoid similar problems.

A minimum code to produce a deadlock is here.

deadlock.py

bash-3.2$ python ./deadlock.py
Process Process-1:
Traceback (most recent call last):
File "/Users/michitaro/opt/anaconda3/lib/python3.8/multiprocessing/process.py", line 315, in _bootstrap
self.run()
File "/Users/michitaro/opt/anaconda3/lib/python3.8/multiprocessing/process.py", line 108, in run
self._target(*self._args, **self._kwargs)
File "/Users/michitaro/Desktop/deadlock.py", line 26, in run_transaction
db.execute('insert into t values (0)')
sqlite3.OperationalError: database is locked

I don't know how a sqlite-based registry can possibly work if there are "many" notebooks holding locks, but I'm not sure a simple connection is the same thing as a lock.

michitaro's demonstration seems to suggest that the problem is too many concurrent writes, perhaps because the ingest process is taking longer than the time between exposures? You say that there are pairs of exposures; does this produce pairs of ingest operations, which would have the same effect as the deadlock demonstration? If any of this is the problem, then I think the calling pattern needs to change from a direct trigger to regular polling so that there's only ever one ingest process in flight at any time.

One ingest task is launched per visit, not one per cam. I'm pretty sure that is the only writer.

What is involved in switching to postgresql?

Are there any protections against having more than one ingest process running at once? How easy would that be to implement?

Switching to Postgresql involves creating a new database, setting up a registry.pgsql YAML file in the repo (with entries host, port, database, user and optional password), then re-ingesting everything.

We're doing this switch on Cerro Pachón.  I think that KT has a script to do the migration

If the mechanism is already developed and tested, I propose that we try using it. Basically, investigating sqlite3 locking issues feels pretty open-ended to me, and we know we have issues with NFS in any case.

Discussed with rhl and cloomis. We believe that the Gen2 registry works with PostgresQL (we understand the HSC summit processing uses this), so we just need to transfer the contents from SQLite to a new PostgresQL database (we suspect KTL's script used for LSST@Cerro Pachon is particular to the Gen3 middleware).

I have verified migration of the sqlite registry to postgresql using the following command:

sqlite3 registry.sqlite3 .dump | sed -e 's|^PRAGMA.*$||' -e 's|integer primary key autoincrement|serial primary key|g' -e 's|double|double precision|g' -e 's|^.*sqlite.*$||' -e 's|pfsDesignId int|pfsDesignId bigint|g' | tee registry.sql | psql -h localhost -U pfs pfs_gen2

I still need to verify operation of the pipeline using the postgresql registry. I am awaiting provision of postgresql databases on tiger and the summit.

I’ve got the postgresql registry working at Hilo. In order to use it, you need to have a ~/.pgpass entry. The alternative is to put the database password in plaintext in the configuration file in the repo, which I think is a bad idea since the database user has admin privs.
Then there’s a new script that needs to be used for ingesting images. That means that we’d need to update the stack (after we merge and cut a new release).

+many on using .pgpass vs. repo: I think we all already do.

And good to know about the ingest – same calling conventions, but just a new version/tag? Is it on this ticket for now?

The ingest updates are on the ticket branch of obs_pfs. You'll need to call ingestPfsImagesPgsql.py instead of ingestPfsImages.py.

Asking for review of the code changes. These will need to be put into a release that's deployed and used for the ingest. Everyone who uses it will need to have the database details in their ~/.pgpass.

The code changes look fine. A rebase is needed, but that is already been understood.

Merged to master.

Leaving this ticket open until we can do the transition at Hilo.

The migration is complete from my side. /work/drp on pfsa-usr01-gb is configured to use the postgresql registry. All future ingests into that repo should use the ingestPfsImagesPgsql.py script, or it won’t be registered correctly.

All users of /work/drp will need to ensure that they have the correct entry in their ~/.pgpass file. The password is available from me or Kiyoto Yabe.

To roll back to the old system, rename the registry.pgsql file to _registry.pgsql, and rename the registry.sqlite3.OLD file to registry.sqlite3.