Re: Immediate Constraints

From a logical standpoint, its like this.

The purpose of constraints is to have the DBMS enforce your concept of
consistency, wherein a database is consistent if any questions you ask it result
in a valid answer insofar as the database could possibly know.

Immediate constraints ensure that the database is consistent between statement
boundaries, while deferred constraints only ensure that the database is
consistent between transaction boundaries.

Logically speaking, the purpose of immediate constraints is to ensure that the
database can't give you possibly wrong/invalid/illogical answers to a query you
make following a database change but before you commit.

Ideally, from a logical standpoint, all constraints would be immediate, but a
primary reason we have deferred constraints at all is to compensate for
deficiencies in the SQL language such that we can't perform arbitrarily complex
database changes in a single statement, such as inserting a record into each of
2 separate tables as a single operation, and so we may defer any constraint that
requires both records to be present.

Bottom line, the more of your constraints are immediate, the more the database
helps you avoid program bugs or corruption due to decisions made based on
incomplete or wrong database changes you make.

-- Darren Duncan

On 2013.08.15 9:14 AM, Perry Smith wrote:
> The direct question is: what is the advantage of an immediate constraint?
>
> My habit is to add constraints to my databases and my first lesson was to make them "deferrable". But a recent fight with pg_restore taught me that to do a pg_restore that is complex, you need to defer the constraints. I cobbled a way to do that as I do the pg_restore.
>
> But that raised a question of why not just make the constraints all "deferred" and simplify my pg_restore process.
>
> Are immediate constraints more efficient? Does this relate to transaction isolation in that the data would be consistent after each statement and therefor give better stability when multiple transactions are running at the same time?
>
> My brain is asking this question because so far in my experience, the issues with constraints are solved by making them deferred. If I made them immediate, would I just bump into a different set of issues whose solution would be to make the constraints immediate?
>
> Thank you,
> Perry
>