Forum OpenACS Q&A: PG 7.1 to 7.1.2 RPM upgrade breaks OpenACS: mobile plpgsql.so

In PG 7.1, the RPMs put plpgsql.so under /usr/lib. This was an accident, apparently, but it happened. In PG 7.1.2, it has moved back to /usr/lib/pgsql.

If you upgrade your RPM-based PG installation from 7.1 to 7.1.2, this breaks OpenACS 3.2.5 (and all other PG databases that use plpgsql.so too, as far as I can see!).

The quick fix is:

  cp -p /usr/lib/pgsql/plpgsql.so /usr/lib
  /sbin/ldconfig

The better fix is probably to do something like

  drop language 'plpgsql';
  drop function plpgsql_call_handler();

in psql connected to the ACS database, and then use createlang to add the language back in again... but then a zillion OID references in pg_proc.prolang will need fixing up! So maybe the 'quick fix' is adequate??

Does anyone have any better ideas on dealing with this?

Yes, thanks, symlink rather than copy is a useful minor refinement, and one which should work fine.

But IMO this is still a kludge, a workaround: the user still has a PG database or databases containing pointers to a library /usr/lib/plpgsql.so which officially 'should' no longer be there.

So my opinion is that this sort of 'fix' is sort of OK for an individual user to do to 'get by'; but asking every OpenACS RPM installer to do it, or automating it and doing it for them, seems to be dirtying people's machines, rather than doing a clean installation.

I was hoping someone could come up with some SQL to 'fix up' those OIDs -- that is the really long term fix, it means the database is now fully consistent with the current PG installation regarding this issue.  In an ideal world the PH 7.1.2 RPMs would detect this issue and fix it up neatly during the RPM upgrade... but they don't seem to do so 😟

I don't normally dig into the innards of PG like this myself, but I suspect there are folks in this community who have been there and done that sort of thing before!