Search Postgresql Archives

Re: Immediate Constraints

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux