[OPEN-ILS-GENERAL] Change in DB action regarding SQL marc update

[Jeff Godin]

On Mon, Apr 23, 2018 at 3:48 PM, Jim Taylor <jtaylor at jtdata.com> wrote:

> I haven’t done this for a while but just tried a straightforward SQL
> update of the “marc” field in the biblio.record_entry table and get the
> error below.   Have done this 10’s of thousands of times within the last
> year or so with no problem.  Currently running version 2.12.6.   Did
> something change which would purposely cause such an action to fail?
> Suggestions?   Thanks.
>

[began this yesterday, in the meantime Josh has replied along the same
lines -jeff]

Jim-

This is a PostgreSQL search_path related issue.

At least one function in your database is expecting to be run with a search
path that contains the "evergreen" schema.

The default PostgreSQL search_path is:
"$user", public

Common ways that the "evergreen" schema ends up in your PostgreSQL
search_path:

1. connecting to the database as a PostgreSQL user named "evergreen" --
which means that the special value "$user" translates to the "evergreen"
schema.

2. setting the database-level search_path setting with ALTER DATABASE

3. setting search_path manually from the psql shell using a command like:
SET search_path = evergreen,public,pg_catalog;

The last option (item 3) only lasts for as long as the life of your session.

>From a psql shell, if you run a SHOW search_path, you'll probably find your
search path is set to the default, and I'd also guess that you're connected
to PostgreSQL as a user named something other than "evergreen" -- perhaps
as the "postgres" user.

There are a number of reasons why things worked before and aren't working
for you now. One common gotcha is that database settings (shown with \drds
in the psql shell) are not preserved by pg_dump -- they are stored at the
cluster level, so you'll only get them with pg_dumpall. (This isn't a
recommendation to change your use of pd_dump / pg_dumpall.)

You can manually set the search_path in your psql session as in item 3
above and you'll probably find your query works as expected.

Your other connections to PostgreSQL (from OpenSRF services) may already be
receiving the correct search_path if they connect as the database user
"evergreen".

Be aware that in some rare (bordering on theoretical) cases, setting /
correcting a search path in a persistent way (using methods 1 or 2 above)
can expose latent issues, including issues that may not be immediately
apparent.

Armed with that knowledge / warning, you'll probably want to investigate
adjusting the database setting for search_path (which will not take effect
for clients until they reconnect).

Good luck!

-jeff
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20180424/de12f526/attachment.html>