Dyrcona

Key Error for Key that exists

Oct 20th, 2021
969
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. BEGIN;
  2.  
  3. -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
  4.  
  5. CREATE TABLE asset.copy_inventory (
  6.     id                          SERIAL                      PRIMARY KEY,
  7.     inventory_workstation       INTEGER                     REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
  8.     inventory_date              TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
  9.     copy                        BIGINT                      REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED
  10. );
  11. CREATE INDEX copy_inventory_copy_idx ON asset.copy_inventory (copy);
  12. CREATE UNIQUE INDEX asset_copy_inventory_date_once_per_copy ON asset.copy_inventory (inventory_date, copy);
  13.  
  14. INSERT INTO asset.copy_inventory
  15. (inventory_workstation, inventory_date, copy)
  16. SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy
  17. FROM asset.latest_inventory
  18. JOIN asset.copy acp ON acp.id = latest_inventory.copy
  19. JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation
  20. WHERE acp.circ_lib = workstation.owning_lib
  21. UNION
  22. SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy
  23. FROM asset.latest_inventory
  24. JOIN asset.copy acp ON acp.id = latest_inventory.copy
  25. JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation
  26. WHERE acp.circ_lib <> workstation.owning_lib
  27. AND acp.floating IS NOT NULL;
  28.  
  29. DROP TABLE asset.latest_inventory;
  30.  
  31. CREATE VIEW asset.latest_inventory (id, inventory_workstation, inventory_date, copy) AS
  32. SELECT DISTINCT ON (copy) id, inventory_workstation, inventory_date, copy
  33. FROM asset.copy_inventory
  34. ORDER BY copy, inventory_date DESC;
  35.  
  36. DROP FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey();
  37.  
  38. COMMIT;
  39.  
  40. -- Produces this output:
  41. BEGIN
  42. CREATE TABLE
  43. CREATE INDEX
  44. CREATE INDEX
  45. INSERT 0 483281
  46. DROP TABLE
  47. CREATE VIEW
  48. DROP FUNCTION
  49. psql:Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor.copy_inventory.sql:38: ERROR:  insert or update on table "copy_inventory" violates foreign key constraint "copy_inventory_copy_fkey"
  50. DETAIL:  Key (copy)=(19793384) is not present in table "copy".
  51.  
  52. select * from asset.copy where id = 19793384;
  53.  
  54.  
  55.     id    | circ_lib | creator | call_number | editor  |      create_date       |           edit_date           | copy_number | status | location | loan_duration | fine_level | age_protect | circulate | deposit | ref | holdable | deposit_amount | price |    barcode     | circ_modifier | circ_as_type | dummy_title | dummy_author | alert_message | opac_visible | deleted | floating | dummy_isbn |  status_changed_time   |      active_date       | mint_condition | cost
  56. ----------+----------+---------+-------------+---------+------------------------+-------------------------------+-------------+--------+----------+---------------+------------+-------------+-----------+---------+-----+----------+----------------+-------+----------------+---------------+--------------+-------------+--------------+---------------+--------------+---------+----------+------------+------------------------+------------------------+----------------+------
  57.  19793384 |      260 |  931922 |    17657780 | 1605803 | 2020-06-10 14:42:37-04 | 2021-07-14 09:26:12.489283-04 |             |      0 |     2514 |             2 |          2 |             | t         | f       | f   | t        |           0.00 | 10.00 | 38125002152305 | Periodical    |              |             |              |               | t            | t       |          |            | 2020-06-23 13:38:04-04 | 2020-06-10 14:42:37-04 | t              |    
  58. (1 row)
  59.  
  60.  
RAW Paste Data