Reusing deleted monograph parts

Bug #1569884 reported by Steve Callender on 2016-04-13
18
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Evergreen
Medium
Unassigned
2.10
Medium
Unassigned
2.9
Medium
Unassigned

Bug Description

EG 2.9+

Recently monograph parts were changed to have a deleted flag rather than being permanently deleted from the biblio.monograph_part tables. This helped fix issues with holds that relied on that monograph part ID existing.

A side problem with this, is that once a part is deleted, you cannot re-create that same part label due to the unique constraints on the table.

 "record_label_unique" UNIQUE CONSTRAINT, btree (record, label)

When trying to re-add a monograph part that already exists in deleted form, EG does not error nicely. Instead it just freezes and waits for a response from the database that never comes due to an insert error on the back end.

It looks to me like the constraint should be changed to take into account deleted items. Maybe something like this,

biblio.monograph_part
"record_label_unique" UNIQUE CONSTRAINT, btree (record, label) WHERE deleted = false OR deleted IS FALSE

Mike Rylander (mrylander) wrote :

Steve,

That is close, but will stop one from deleting two parts with the same label. You're proposal in the description is actually closer, but can't be done with a CONSTRAINT directly. Instead, we need a partial unique index. Something like:

ALTER TABLE biblio.monograph_part DROP CONSTRAINT "record_label_unique";
CREATE INDEX CONCURRENTLY record_label_unique_idx ON biblio.monograph_part (record, label) WHERE deleted = FALSE;

Mike Rylander (mrylander) wrote :

Dan Wells spotted a mistake of mine. We'll want a unique index ... so:

ALTER TABLE biblio.monograph_part DROP CONSTRAINT "record_label_unique";
CREATE UNIQUE INDEX CONCURRENTLY record_label_unique_idx ON biblio.monograph_part (record, label) WHERE deleted = FALSE;

Thanks, Dan!

New patch incoming. Thanks guys!

Steve

Based on Mike and Dans suggestion, here's the new patch,

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/collab/Callender/monograph_parts_new_constraints_index

I've tested it by repeatedly adding and deleting an identical monograph part in the biblio.monograph_part table and it will only allow one at a time per label, but there can be an unlimited amount of deleted ones using the same label.

Steve

Changed in evergreen:
status: New → Confirmed
Jason Stephenson (jstephenson) wrote :

I have applied this patch to testing and production because it resolves the issue for us.

I was going to make the base schema change and add my sign off, but I see that Steve has not signed off his latest branch.

Steve, could you add your signoff, please?

Jason Stephenson (jstephenson) wrote :

Thanks, Steve!

As I mentioned, we've used the upgrade script in production already and it works.

I tested a concerto build with the base schema changes, found a couple of errors, and corrected them.

I have pushed a rebased, signed-off branch to collab/dyrcona/lp1569884_monograph_parts_new_constraints_index_with_signoff

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/collab/dyrcona/lp1569884_monograph_parts_new_constraints_index_with_signoff

Anyone testing/pushing will want the top three commits on the branch.

There are no tests or release notes, so we need at least 1 more signoff.

Changed in evergreen:
milestone: none → 2.next
Galen Charlton (gmc) wrote :

I've tested and pushed to master, rel_2_10, and rel_2_9, along with a pgTAP regression test and a fix for the schema update script. Thanks, Steve and Jason!

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

Duplicates of this bug

Other bug subscribers