Copy package job reporting OOPS due transaction being aborted

[Cristian Gonzalez]

We have received a couple of reports that indicate that the copy package job is getting OOPS related to their transaction being aborted.

First report (May 11th):
https://chat.canonical.com/canonical/pl/uncpb9wbxt89dye9pwjwd97kee
https://oops.canonical.com/?oopsid=OOPS-7ed77fecea038a1ab5e3e6825ad70cbb

Second report (May 17th):
https://chat.canonical.com/canonical/pl/mcn473twp7ytzdgttxdbqjxkhe
https://oops.canonical.com/?oopsid=OOPS-82e664d43bac23271dba9c1441e99841

[Steve Beattie (sbeattie)]

FYI, I just hit this again today publishing caribou to groovy-security resulting in OOPS-ab9002599ae8917c9c063108d1ae4014 (lp notified me via email at 17 May 2021 20:14:21 -0000).

[Steve Beattie (sbeattie)]

The publication at the same time to focal-security succeeded.

[Colin Watson (cjwatson)]

The problem here is that unlocking the advisory lock fails if the current transaction is aborted. We could manage the transaction state more carefully to avoid this, but I think a better solution is probably to use `pg_try_advisory_xact_lock` to acquire a transaction-level advisory lock instead, as that way we won't need to unlock it explicitly.