pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedALTER TABLE lock level
4+ messages / 2 participants
[nested] [flat]
* ALTER TABLE lock level
@ 2013-05-27 15:23 Marko Tiikkaja <marko@joh.to>
0 siblings, 1 reply; 4+ messages in thread
From: Marko Tiikkaja @ 2013-05-27 15:23 UTC (permalink / raw)
To: pgsql-docs
Hi,
Today I got (unfortunately) reminded that this statement, in
sql-altertable.html under VALIDATE CONSTRAINT, is not true anymore:
The value of separating validation from initial creation of the
constraint is that validation requires a lesser lock on the table than
constraint creation does.
Seems to affect all versions >= 9.1.
Regards,
Marko Tiikkaja
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ALTER TABLE lock level
@ 2013-05-31 12:46 Marko Tiikkaja <marko@joh.to>
parent: Marko Tiikkaja <marko@joh.to>
0 siblings, 1 reply; 4+ messages in thread
From: Marko Tiikkaja @ 2013-05-31 12:46 UTC (permalink / raw)
To: pgsql-docs
On 5/27/13 5:23 PM, I wrote:
> Today I got (unfortunately) reminded that this statement, in
> sql-altertable.html under VALIDATE CONSTRAINT, is not true anymore:
>
> The value of separating validation from initial creation of the
> constraint is that validation requires a lesser lock on the table than
> constraint creation does.
Since nobody seems to want to pick this up, I'm going to suggest the
following wording:
The value of separating validation from initial creation of the
constraint is that you can immediately start enforcing the
constraint on all future rows, but delay the validation of
pre-existing data until your database is not under heavy load.
But I'm not going to object to simply removing the entire sentence.
Regards,
Marko Tiikkaja
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ALTER TABLE lock level
@ 2013-06-02 11:47 Kevin Grittner <kgrittn@ymail.com>
parent: Marko Tiikkaja <marko@joh.to>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2013-06-02 11:47 UTC (permalink / raw)
To: Marko Tiikkaja <marko@joh.to>; pgsql-docs
Marko Tiikkaja <marko@joh.to> wrote:
> On 5/27/13 5:23 PM, I wrote:
>> Today I got (unfortunately) reminded that this statement, in
>> sql-altertable.html under VALIDATE CONSTRAINT, is not true anymore:
>>
>> The value of separating validation from initial creation of the
>> constraint is that validation requires a lesser lock on the table than
>> constraint creation does.
>
> Since nobody seems to want to pick this up, I'm going to suggest the
> following wording:
>
> The value of separating validation from initial creation of the
> constraint is that you can immediately start enforcing the
> constraint on all future rows, but delay the validation of
> pre-existing data until your database is not under heavy load.
That's good as far as it goes, but there's another major reason for
wanting the capability. There are cases where, due to dirty
converted data or late recognition of the need for a constraint
there is existing data which violates the constraint. Being able
to add a constraint to protect against creating additional bad data
before all the existing problems are cleaned up can be valuable.
I know of cases where the old rows which violate the constraint may
persist for decades. In a court setting they may care a lot that
data on a new and active court case is forced to be correct when
entered, but may not feel that it is worth the cost (or in some
cases even possible) to determine correct values for cases which
are 10 or 20 (or 120) years old. These cases can't be deleted due
to records retention laws or relevant court rules, and for a DBA to
update them with bogus values to make them comply with the
constraint would be not only potentially more misleading than
leaving them alone, but would be a felony.
> But I'm not going to object to simply removing the entire sentence.
I don't know -- it seems like the reasons for the feature are
non-obvious enough that such a note is useful.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ALTER TABLE lock level
@ 2013-06-04 22:29 Marko Tiikkaja <marko@joh.to>
parent: Kevin Grittner <kgrittn@ymail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Marko Tiikkaja @ 2013-06-04 22:29 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: pgsql-docs
On 02/06/2013 13:47, Kevin Grittner wrote:
> That's good as far as it goes, but there's another major reason for
> wanting the capability. There are cases where, due to dirty
> converted data or late recognition of the need for a constraint
> there is existing data which violates the constraint. Being able
> to add a constraint to protect against creating additional bad data
> before all the existing problems are cleaned up can be valuable.
Yeah, that's a valid use case.
Anyway, you seem to know more about this than I do and your English is
better, so I'll leave it up to you. I just hope we can get this change
into the next minor release so I'd be the last person to suffer from the
misinformation that's currently in the documentation.
Regards,
Marko Tiikkaja
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-06-04 22:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-27 15:23 ALTER TABLE lock level Marko Tiikkaja <marko@joh.to>
2013-05-31 12:46 ` Marko Tiikkaja <marko@joh.to>
2013-06-02 11:47 ` Kevin Grittner <kgrittn@ymail.com>
2013-06-04 22:29 ` Marko Tiikkaja <marko@joh.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox