Cleanup (7.6) bombs with error message

[Wally Grotophorst]

I saw the earlier posting on a problem with cleanup that stopped running with a message like this one:

 Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on 
> table "bitstream" violates foreign key constraint 
> "bundle_primary_bitstream_id_fkey" on table "bundle" 
> Detail: Key (uuid)=(02caead4-de93-4011-8105-0e6921f286d8) is still 
> referenced from table "bundle". 

I have the same issue with 7.6 and the suggestions Mark Wood included in response on June 12 has been working for me.  Well, sort of.  I fixed 9 or 10 using Mark's method and then cleanup died with this error message:

Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "bitstream" violates foreign key constraint "requestitem_bitstream_id_fkey" on table "requestitem"

  Detail: Key (uuid)=(ca1a4a1e-5fd0-418e-96f6-6911475a8b2f) is still referenced from table "requestitem".

Mark's suggestion does not work for this table but I wonder if someone else has encountered this issue and has the SQL that might fix it?  Thanks.

- Wally Grotophorst

George Mason University

 

[Technologiczny Informator]

Hi,

Mark was replying to my post then. While cleaning I had the same one case as yours. Since you got to know Mark's method, I'm throwing you my "procedure" without comment:

ERROR: update or delete on table "bitstream" violates foreign key constraint "requestitem_bitstream_id_fkey" on table "requestitem"

  Szczegóły: Key (uuid)=(e2f6374b-f2ff-435c-9940-f37adc6250aa) is still referenced from table "requestitem".

\set a e2f6374b-f2ff-435c-9940-f37adc6250aa

SELECT item_id AS b FROM requestitem WHERE bitstream_id = :'a';

b

--------------------------------------

db43aa8e-855d-42b9-b82c-57c3d469e4f7

db43aa8e-855d-42b9-b82c-57c3d469e4f7

(2 rows)

SELECT deleted FROM bitstream WHERE uuid = :'a';

deleted

---------

t

(1 row)

\set b db43aa8e-855d-42b9-b82c-57c3d469e4f7

UPDATE requestitem SET bitstream_id = null WHERE item_id = :'b';

UPDATE 2

\q

Regards,

Mariusz

[Wally Grotophorst]

Thanks so much.  Just what I needed.  -- Wally

[Andrew K]

Hi!
I have the same error on cleanup. Trying to use this method to fix it.
But I receive 2 empty rows here instead of some uuids

\set a 15b3ebc4-ae05-461e-ab9d-3a39bdb1bb50
dspace=# SELECT item_id AS b FROM requestitem WHERE bitstream_id = :'a';
b
- - -


(2 rows)

This looks OK though

dspace=# SELECT deleted FROM bitstream WHERE uuid = :'a';

deleted
---------
t
(1 row)

What do I do next?

Thanks,

Andrew

середу, 5 липня 2023 р. о 09:29:31 UTC+3 Technologiczny Informator пише:

[Andrew K]

For clarity, the error is

Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "bitstream" violates foreign key constraint "requestitem_bitstream_id_fkey" on table "requestitem"

  Detail: Key (uuid)=(15b3ebc4-ae05-461e-ab9d-3a39bdb1bb50) is still referenced from table "requestitem".

неділю, 17 березня 2024 р. о 16:07:38 UTC+2 Andrew K пише:

[Amy Ball Wicklund]

You could try substituting requestitem_id for item_id in those same queries. It's the Primary Key in the requestitem table, and thus cannot be empty, so you should get 2 unique values that you can use to update the records. You will just have to run the update query for each value returned.

[Andrew K]

Hi Amy!

Not that I completely understand it, but I run

dspace=# \set a 15b3ebc4-ae05-461e-ab9d-3a39bdb1bb50

dspace=# SELECT requestitem_id AS b FROM requestitem WHERE bitstream_id = :'a';
 b
---
 2
 3
(2 rows)

and then

dspace=# UPDATE requestitem SET bitstream_id = null WHERE requestitem_id = 2;
UPDATE 1
dspace=# UPDATE requestitem SET bitstream_id = null WHERE requestitem_id = 3;
UPDATE 1

Problem solved! Thanks a lot!

Cleanup successfully finished now.

WBR,

Andrew

четвер, 21 березня 2024 р. о 22:43:11 UTC+2 Amy Ball Wicklund пише: