Re: [OPEN-ILS-GENERAL] Change in DB action regarding SQL marc update
On Mon, Apr 23, 2018 at 3:48 PM, Jim Taylor 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
Re: [OPEN-ILS-GENERAL] Change in DB action regarding SQL marc update
Jim, I wonder if this is a scope/search path issue? "oils_xpath" looks like it has no schema specified there. When I run "show search_path" I get show search_path; search_path --- evergreen, public, pg_catalog Josh Stompro - LARL IT Director From: Open-ils-general [mailto:open-ils-general-boun...@list.georgialibraries.org] On Behalf Of Jim Taylor Sent: Monday, April 23, 2018 2:48 PM To: 'Evergreen Discussion Group' Subject: [OPEN-ILS-GENERAL] Change in DB action regarding SQL marc update 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. Jim ERROR: function oils_xpath(text, text, text[]) does not exist LINE 6: (oils_xpath( ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT COALESCE( naco_normalize( ARRAY_TO_STRING( oils_xpath( '//text()', (oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ))[1] ), '' ) ), '' ) CONTEXT: PL/pgSQL function biblio.extract_fingerprint(text) line 34 at assignment PL/pgSQL function biblio.fingerprint_trigger() line 10 at assignment ** Error ** ERROR: function oils_xpath(text, text, text[]) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Context: PL/pgSQL function biblio.extract_fingerprint(text) line 34 at assignment PL/pgSQL function biblio.fingerprint_trigger() line 10 at assignment
