Concerto won't load because of problem with permission.grp_ancestors on PostgreSQL 9.5

Bug #1568046 reported by Ben Shum on 2016-04-08
This bug affects 1 person
Affects Status Importance Assigned to Milestone

Bug Description

Evergreen master, PostgreSQL 9.5

While I was working on testing Ubuntu 16.04 (bug 1551084), I found that it ships with PostgreSQL 9.5. Along the way, I found an issue at the end of trying to load the concerto dataset. It bombs out on transactions.sql with an error like:

psql:transactions.sql:139: ERROR: invalid return type
DETAIL: SQL key field type text does not match return key field type integer.
CONTEXT: SQL statement "SELECT 2 IN (SELECT id FROM permission.grp_ancestors(grp))"
PL/pgSQL function inline_code_block line 15 at IF

Running just the piece for permission.grp_ancestors (int), I found that is the problem area.

The following snippet comes from PG 9.1 vs. 9.5 and testing out that function:

With PG 9.1.20:

evergreen=# SELECT permission.grp_ancestors (5) ORDER BY 1;
 (1,Users,,f,"3 years",,group_application.user,0)
 (3,Staff,1,f,"3 years",,group_application.user.staff,0)
 (5,Circulators,3,t,"3 years",,group_application.user.staff.circ,0)
(3 rows)

With PG 9.5.2:

evergreen=# SELECT permission.grp_ancestors (5) ORDER BY 1;
ERROR: invalid return type
DETAIL: SQL key field type text does not match return key field type integer.
CONTEXT: SQL function "grp_ancestors" statement 1

Looking inside that function permission.grp_ancestors, it seems that we use this connectby() that draws from the Tablefunc contrib. So, it would appear that something has changed how this operates with PG 9.5+

Will investigate further, but this was my first attempts at trying out PG 9.5 and Evergreen.

Mike Rylander (mrylander) wrote :

I'd call this one a bitesize bug, and suggest taking all the similar ancestor and descendant functions for org units from Open-ILS/src/sql/Pg/020.schema.functions.sql and simply translating them for use with the group tree. Whoever does this can just ignore any functions that have "_at_depth" in their name, as that uses org unit type information which doesn't have an analog in groups.

tags: added: bitesize
Jason Stephenson (jstephenson) wrote :

While it may be a bitesize bug in difficulty, I don't think it is a bitesize bug in intent.

That tag is mainly used for people who are new and looking for something simple to cut their teeth on.

If we're going to have xenial officially supported by its release during the conference, then this bug needs to be fixed sooner than most bitesize bugs get fixed.

For supporting Ubuntu 16.04, this is a show stopper.

Galen Charlton (gmc) on 2016-04-08
tags: removed: bitesize
Galen Charlton (gmc) wrote :

I agree that this isn't a bitesize bug, given the need for unit tests and the potential for subtle circ policy breakage if a revised permission.grp_ancestors() is wrong.

Changed in evergreen:
assignee: nobody → Mike Rylander (mrylander)
Mike Rylander (mrylander) wrote :

That's fair. Here's a branch that implements the change, and adds utility functions that parallel the org unit versions:;a=shortlog;h=refs/heads/user/miker/lp_1568046_kill-connectby

Changed in evergreen:
assignee: Mike Rylander (mrylander) → nobody
Jason Stephenson (jstephenson) wrote :

Thanks, Mike!

Loaded the above real quick in my development system on 9.3 and found a few errors.

I've corrected them and pushed to working/collab/dyrcona/lp_1568046_kill-connectby.

They seem to work on a 9.3 system with production data. More testing is warranted as are some PgTap tests.

If I have time, I'll add the latter over the weekend as well as test this with fresh concerto installs on Pg 9.3 and 9.5.

Mike Rylander (mrylander) wrote :

Awesome, thanks!

Ben Shum (bshum) wrote :

Fix pushed to master for next major series. Not backporting at the moment, but will check with release maintainers to decide whether this is needed earlier or not.

Changed in evergreen:
milestone: none →
status: New → Fix Committed
Bill Ott (bott) wrote :

Also playing with Pg9.5 along with EG 2.10.5.

I found a "invalid return type", again seemingly related to connectby, within the permission.usr_has_perm_at_all_nd function.

This manifests itself with the inability to get to Register Patron, within the browser staff client.

Ben Shum (bshum) wrote :

Hi Bill, can you tell us some more about your installation, since the fix for PG 9.5 for this bug only went to master, and not 2.10 series.

I just tested opening up the register patron and then successfully added a new patron via the registration pages in my Xenial VM running Evergreen master from a few weeks back, so I have been unable to replicate the issue you're noting. I'll try again later on a fresh rebuilt VM to be sure that nothing new has crept in that would be causing the problem.

Jason Stephenson (jstephenson) wrote :

Register Patron will not load for me in the browser staff client with a Pg 9.5 database. Checking on the two functions that still use connectby, it looks like permission.usr_has_perm_at_all_nd is the culprit:

select permission.usr_has_perm_at_all_nd(223, 'ABORT_TRANSIT');
ERROR: invalid return type
DETAIL: SQL key field type text does not match return key field type integer.
CONTEXT: PL/pgSQL function permission.usr_has_perm_at_all_nd(integer,text) line 28 at FOR over SELECT rows

Concerto loads, but calling the function gives the above result.

I think we should get rid the remaining connectby calls, and then we can also drop a dependency.

Jason Stephenson (jstephenson) wrote :

I've pushed a branch with database changes and release notes to working/user/dyrcona/lp1568046-kill-connectby-with-fire to eliminate connectby in the last two places where it is used. The branch also removes the tablefunc database extension from the database creation scripts. These last two uses of connectby are the only thing requiring tablefunc in stock master. There are no tests, but some could be added.;a=shortlog;h=refs/heads/user/dyrcona/lp1568046-kill-connectby-with-fire

Jason Stephenson (jstephenson) wrote :

Rather than make a new bug for Bill Ott's issue, we'll leave it on this one.

I've reopened this bug as "confirmed" because I can confirm Bill's report in comment #8. It happens if you login as a non-superuser user.

Changed in evergreen:
status: Fix Committed → Confirmed
Ben Shum (bshum) wrote :

Okay, tested the error, and yes, it happens when you use a non-admin-superuser account to try loading up register patron.

Applied Jason's fix to remove connectby for those functions and voila, happy registering new patrons.

Tested the upgrade script and clean DB rebuild for both Trusty with PG 9.3 and Xenial with PG 9.5.

Looks good, signed off and pushed to master.

Changed in evergreen:
status: Confirmed → Fix Committed
Changed in evergreen:
milestone: → 2.11-alpha
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers