Evergreen

Can't delete acq.fund_debit linked to acq.invoice_item

Bug #2009066 reported by Jason Stephenson
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Evergreen
New
Undecided
Unassigned
Evergreen 3.next
3.10
Won't Fix
Undecided
Unassigned
3.11
New
Undecided
Unassigned
Evergreen 3.11.6
3.12
New
Undecided
Unassigned
Evergreen 3.12.4
3.9
Won't Fix
Undecided
Unassigned

Bug Description

Evergreen version 3.7.3 and 3.10.0
OpenSRF version: N/A
PostgreSQL version: 10.23, 15.2

The foreign key relationship between acq.fund_debit and acq.invoice_item prevents the deletion of acq.fund_debit rows if they are used by acq.invoice_item rows. The constraint is:

TABLE "acq.invoice_item" CONSTRAINT "invoice_item_fund_debit_fkey" FOREIGN KEY (fund_debit) REFERENCES acq.fund_debit(id)

Based on the constraints with acq.fund_debit and acq.invoice_entry or acq.po_item, the above constraint ought to either be deferrable (so it will fire at the end of a transaction) or include "on delete set null."

My preference is for the latter to match the acq.invoice_entry constraint.

Jason Stephenson (jstephenson)
summary: - Can't delete acq.fund-debit linked to acq.invoice_item
+ Can't delete acq.fund_debit linked to acq.invoice_item
Tiffany Little (tslittle)
tags: added: acq
Jason Stephenson (jstephenson)
Changed in evergreen:
assignee: nobody → Jason Stephenson (jstephenson)
Revision history for this message
Jason Stephenson (jstephenson) wrote :

Upon having a more than cursory look, the situation is not as simple as it first appeared. Some code diving is required to figure out whether making the invoice_item_fund_debit_fkey deferred or using on delete set null is the better option. It seems to me that po_item_fund_debit_fkey ought to be the same whichever choice is made.

Revision history for this message
Tiffany Little (tslittle) wrote :

Hi Jason, is there a workflow where you would want to delete the fund debit on a still-existing invoice item?

Revision history for this message
Jason Stephenson (jstephenson) wrote :

Tiffany, I can't really think of such a situation, so here's a branch that makes the foreign key relationship deferrable, like that between acq.fund_debit and acq.po_item.

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/dyrcona/lp2009066-acq-invoice_item_fund_debit_fkey-deferred

I have also targeted this at rel_3_9 and rel_3_10 because I consider it a bug in the schema.

Changed in evergreen:
milestone: none → 3.11-beta
assignee: Jason Stephenson (jstephenson) → nobody
Michele Morgan (mmorgan)
tags: added: pullrequest
Evergreen Bug Maintenance (bugmaster)
Changed in evergreen:
milestone: 3.11-beta → 3.10.2
no longer affects: evergreen/3.10
Evergreen Bug Maintenance (bugmaster)
Changed in evergreen:
milestone: 3.10.2 → 3.10.3
Jason Stephenson (jstephenson)
Changed in evergreen:
milestone: 3.10.3 → 3.12-beta
Evergreen Bug Maintenance (bugmaster)
Changed in evergreen:
milestone: 3.12-beta → 3.12-rc
Evergreen Bug Maintenance (bugmaster)
Changed in evergreen:
milestone: 3.12-rc → 3.next
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.