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

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.

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.

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.

That's fair. Here's a branch that implements the change, and adds utility functions that parallel the org unit versions:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/miker/lp_1568046_kill-connectby

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.

Awesome, thanks!

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.

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.

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.

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.

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.

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/dyrcona/lp1568046-kill-connectby-with-fire

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.

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.