Re: Problem with trigger makes Detail record be invalid - Mailing list pgsql-general

On 04/20/2018 01:30 PM, PegoraroF10 wrote:
> Well, talking about responsabilities, I think one of responsabilities of a
> mature database is that it can only accept data it was configured for. If
> you try to store a timestamp in a integer field or a huge numeric value in a

Actually there have been examples on this list where folks have stored a 
timestamp as seconds from an epoch in an integer field. Of course then 
someone has to know what that field really represents. This is not nit 
picking on my part so much as an example of end user inventiveness. To 
that end Postgres has many ways of coming to a solution for a problem. 
Unfortunately, there are paths to a solution can trip you up. This means 
there is often no simple answer to a problem. Basically, more choices 
means more pre-thinking, testing, re-thinking, repeat as needed.

> smallint field, Postgres will block you because that operation is not
> acceptable.
> So, it's not acceptable to break referential integrity, is it ?

That is a maybe:

https://www.postgresql.org/docs/10/static/sql-altertable.html

"
DISABLE TRIGGER [ trigger_name | ALL | USER ]

ALL

     Disable or enable all triggers belonging to the table. (This 
requires superuser privilege if any of the triggers are internally 
generated constraint triggers such as those that are used to implement 
foreign key constraints or deferrable uniqueness and exclusion constraints.)


ADD table_constraint [ NOT VALID ]

     This form adds a new constraint to a table using the same syntax as 
CREATE TABLE, plus the option NOT VALID, which is currently only allowed 
for foreign key and CHECK constraints. If the constraint is marked NOT 
VALID, the potentially-lengthy initial check to verify that all rows in 
the table satisfy the constraint is skipped. The constraint will still 
be enforced against subsequent inserts or updates (that is, they'll fail 
unless there is a matching row in the referenced table, in the case of 
foreign keys; and they'll fail unless the new row matches the specified 
check constraints). But the database will not assume that the constraint 
holds for all rows in the table, until it is validated by using the 
VALIDATE CONSTRAINT option.
"

https://www.postgresql.org/docs/10/static/sql-createtrigger.html

"SQL specifies that BEFORE DELETE triggers on cascaded deletes fire 
after the cascaded DELETE completes. The PostgreSQL behavior is for 
BEFORE DELETE to always fire before the delete action, even a cascading 
one. This is considered more consistent. There is also nonstandard 
behavior if BEFORE triggers modify rows or prevent updates during an 
update that is caused by a referential action. This can lead to 
constraint violations or stored data that does not honor the referential 
constraint."

https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

"Row-level triggers fired BEFORE can return null to signal the trigger 
manager to skip the rest of the operation for this row (i.e., subsequent 
triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for 
this row). "

A certain amount of this came about because folks are dealing with messy 
data and want to get it into the database first, do the clean up there 
and then apply the RI constraints. Other folks have different ways of 
doing it. It comes done to personal choice. That means though you have 
to know that dangerous paths exist and how to avoid them. A lot of this 
is ingrained in the code and in use in the wild, so I would not expect 
there would be major changes in how things work. Instead as has already 
been indicated there maybe better documentation on the way detailing all 
the above.

> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com