Fix Version/s: None
For spatial indexing of obscore table we need pg_sphere extension enabled. Standard postgres deployment usually comes without that extension, so it has to be installed separately. Details for that process depend on how postgres is deployed. Some Linux distributions may have that extension in their repos, and also Postgres has it in its repos as well.
I think we need that for USDF and also for cloud instance, likely very different approaches needed for those two cases.
pg_sphere extension was installed yesterday by DBAs on usdf-butler instance and added to lsstdb1 database. Today I loaded ci_hsc data into a separate schema in that database and ran obscore migration scripts on to test that pg_sphere is in usable shape. Everything works perfectly, queries run successfully and spatial indexing works as well.
I'll wait for feedback on
DM-36701 to finalize configuration for OGA repository and will add obscore to that repo. This ticket is finished, no code to review.
Yee Li, thanks a lot for coordinating this installation!
Andy Salnikov, what is the origin of the pgsphere extension that you've actually been installing? Are we getting an RPM from outside, or building it ourselves from source, or...?
Gregory Dubois-Felsmann, I believe it was built from source, our DBAs made a special image that includes pg_sphere. Yee Li can probably point you to specifics.
yep - from source. see https://github.com/slaclab/rubin-usdf-butler-deploy/blob/main/Dockerfile#L17
Yee Li, I saw few instances of this exception:
All seem to be related to a long-running transaction.