Reusing deleted monograph parts

This is my proposed fix,

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

Steve

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;

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

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?

Absolutely. Here's is my signoff branch,

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=2737b284090109c7230e57de1adabcf3ac91f02b

And here is my signoff for the baseline schema,

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=6aed1b0562d93f3b56189fc509db64f7d9bb009d

Steve

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.

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!