| From: | wolters(dot)k(at)web(dot)de |
|---|---|
| To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
| Subject: | Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints |
| Date: | 2024-05-22 22:38:41 |
| Message-ID: | trinity-df545866-e250-4a4d-a113-c9f6e3cd3fca-1716417521384@msvc-mesg-web003 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
<html style="background-color:transparent"><head><meta charset="utf-8" name="viewport" content="width=device-width">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body contenteditable="false" style="margin-left:10px;">
<div id="8787634986986720895ty" contenteditable="false">
<div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Hi,</div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">getting trigger and functions over several tables smooth at work is sometimes difficult, as there are many points for unecpexted stuff. Maybe some of the following hints are helpful? </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">maybe you can check the unique constraint at first with a simple insert of several rows in your table <span style="font-family: Aptos, serif; font-size: 10.666667px;">bx.test_part_details_all_mcm_</span><b style="font-family: Aptos, serif; font-size: 10.666667px;">mid</b> if everything works as expected. </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Does your constraint <span style="font-family: Aptos, serif; font-size: 10.666667px;">cons_unique_for_concatenated_view</span> include all columns of the table (the comparision works for the whole table or a few columns)? </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Are there any further update- /insert-, etc. trigger or functions that operate at the some table ( <span style="font-family: Aptos, serif; font-size: 10.666667px;">bx.test_part_details_all_mcm_</span><b style="font-family: Aptos, serif; font-size: 10.666667px;">mid</b>) - in worst case at the same time? </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Are the duplicated rows containg NULL- values in a column? </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">„<span style="caret-color: rgb(243, 245, 249); color: rgb(243, 245, 249); font-family: "Open Sans", sans-serif; font-size: 14.4px; -webkit-tap-highlight-color: rgba(0, 0, 0, 0); -webkit-text-size-adjust: 100%; background-color: rgb(33, 33, 33);"> By default, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns“ (</span><a href="https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS">https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS</a>) </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Then you could solve the Problem by adding <span style="caret-color: rgb(243, 245, 249); color: rgb(243, 245, 249); font-family: monospace, monospace; font-size: 14.4px; -webkit-tap-highlight-color: rgba(0, 0, 0, 0); -webkit-text-size-adjust: 100%; background-color: rgb(74, 74, 74);">NULLS NOT DISTINCT</span> (compare further down same link)</div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Good luck! </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">kind regards,</div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Katrin</div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><span style="caret-color: rgb(243, 245, 249); color: rgb(243, 245, 249); font-family: monospace, monospace; font-size: 14.4px; -webkit-tap-highlight-color: rgba(0, 0, 0, 0); -webkit-text-size-adjust: 100%; background-color: rgb(74, 74, 74);"></span></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br>Am 22.05.24 um 23:15 schrieb M Sarwar
<br><br>
</div>
<div style="border-left:2px solid gray;padding-left:10px;width:100%%">
<div style="font-family: Verdana;font-size:17px; color:black;">
Von: "M Sarwar" <sarwarmd02(at)outlook(dot)com><br>Datum: 22. Mai 2024<br>An: "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org><br>Cc: <br>Betreff: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
</div>
<div class="elementToProof" style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, serif; font-size: 8pt; color: rgb(0, 0, 0);">
Hello All,</div>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">This is a trigger related question.</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> </span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">Table, test_part_details_all_mcm_init has a trigger, tr_test_part_details_all_mcm_init.</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> </span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">CREATE TRIGGER tr_test_part_details_all_mcm_init</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> AFTER INSERT</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> ON bx.test_part_details_all_mcm_<b>init</b></span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> FOR EACH ROW</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> EXECUTE FUNCTION bx.tr_fn_test_part_details_all_mcm_init();</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"> </p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">Function, bx.tr_fn_test_part_details_all_mcm_init() INSERTs a row into another table, bx.test_part_details_all_mcm_<b>mid</b></span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"> </p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">Table, bx.test_part_details_all_mcm_<b>mid
</b>has a constraint,</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">CONSTRAINT cons_unique_for_concatenated_view UNIQUE (start_time_numeric, stop_time_numeric, test_action, part_type, fixture_id, run_id, auxid1_build_id, auxid2_asic_id,
serial_number, part_pf)</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> </span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">When I INSERT a data into the table Table, test_part_details_all_mcm_init , trigger is not checking CONSTRAINT cons_unique_for_concatenated_view while INSERTing
the data in bx.test_part_details_all_mcm_<b>mid. </b>I am expecting that anything violating the constraint cons_unique_for_concatenated_view will throw an error. Instead of that it is allowing to INSERT the data.</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"> </p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">What is that I am missing here?</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;">Thanks,</p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;">Sarwar</p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"> </p>
</div>
</div>
</body></html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 9.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | M Sarwar | 2024-05-23 00:29:16 | Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints |
| Previous Message | M Sarwar | 2024-05-22 21:14:46 | AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints |