[OBSPROC-38] add a unique constraint of (proposal_id, ob_code) in the target table Created: 20/Apr/23  Updated: 20/Apr/23  Resolved: 20/Apr/23

Status: Done
Project: PFS observation processing/procedure
Component/s: ets_target_database
Affects Version/s: None
Fix Version/s: None

Type: Task Priority: Normal
Reporter: monodera Assignee: monodera
Resolution: Done Votes: 0
Labels: targetDB
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Epic Link: targetDB
Reviewers: monodera

 Description   

(proposal_id, ob_code) pair must be unique to track the progress. It will need to be implemented in the target table of targetdb.

 Comments   
Comment by monodera [ 20/Apr/23 ]

I modified code and tried to update the schema with alembic. The update script returned errors by detecting duplicates for the unique constraint. It seems some objects have been inserted twice.

 

targetdb_comm2022may=# SELECT proposal_id, ob_code, ra, dec, count FROM target GROUP BY proposal_id, ob_code, ra, dec HAVING count > 1;
proposal_id | ob_code | ra | dec | count
------------+-----------------------------------------+-------------------
S23A-EN16 | r_0_ishigaki_ga_2023feb | 204.98178239 | 28.2770517 | 2
S23A-EN16 | r_10000_ishigaki_ga_2023feb | 205.49925232 | 28.39503098 | 2
S23A-EN16 | r_10001_ishigaki_ga_2023feb | 205.49926758 | 28.36640549 | 2
S23A-EN16 | r_10002_ishigaki_ga_2023feb | 205.49926758 | 28.35559845 | 2
S23A-EN16 | r_10003_ishigaki_ga_2023feb | 205.49928284 | 28.3361454 | 2
S23A-EN16 | r_10004_ishigaki_ga_2023feb | 205.4992981 | 28.33244896 | 2
S23A-EN16 | r_10005_ishigaki_ga_2023feb | 205.4992981 | 28.33455276 | 2
S23A-EN16 | r_10006_ishigaki_ga_2023feb | 205.4992981 | 28.25308037 | 2
S23A-EN16 | r_10007_ishigaki_ga_2023feb | 205.49931335 | 28.39699745 | 2
S23A-EN16 | r_10008_ishigaki_ga_2023feb | 205.49932861 | 28.40284538 | 2
S23A-EN16 | r_10009_ishigaki_ga_2023feb | 205.49932861 | 28.39302063 | 2
S23A-EN16 | r_1000_ishigaki_ga_2023feb | 205.69470905 | 28.42560075 | 2
S23A-EN16 | r_10010_ishigaki_ga_2023feb | 205.49934387 | 28.38490105 | 2
S23A-EN16 | r_10011_ishigaki_ga_2023feb | 205.49935913 | 28.30970573 | 2
S23A-EN16 | r_10012_ishigaki_ga_2023feb | 205.49935913 | 28.3936615 | 2
S23A-EN16 | r_10013_ishigaki_ga_2023feb | 205.49937439 | 28.31223869 | 2
S23A-EN16 | r_10014_ishigaki_ga_2023feb | 205.49937439 | 28.31671143 | 2

 

Perhaps, it will be safe to delete one of duplicated rows, but wait for a bit.

Comment by monodera [ 20/Apr/23 ]

Using the following queries, I deleted one of two entries of duplicates. Then the alembic update succeeded.

targetdb_comm2022may=# SELECT ct, count() AS ct_ct FROM (SELECT proposal_id, ob_code, ra, dec, count() AS ct FROM target GROUP BY proposal_id, ob_code, ra, dec HAVING count > 1) sub GROUP BY 1 ORDER BY 1;
ct | ct_ct
---+------
2 | 97561
(1 row)

targetdb_comm2022may=# DELETE FROM target a USING target b WHERE a.target_id > b.target_id AND a.ob_code = b.ob_code;
DELETE 97561

Comment by monodera [ 20/Apr/23 ]

The database column was updated.

Generated at Sat Feb 10 16:44:38 JST 2024 using Jira 8.3.4#803005-sha1:1f96e09b3c60279a408a2ae47be3c745f571388b.