Re: Problem with trigger makes Detail record be invalid

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(dot)klaver(at)aklaver(dot)com