Excerpts from Robert Haas's message of vie jun 03 09:34:03 -0400 2011:
> > Just a note that since Alvaro created a patch to provide similar
> > functionality for constraints, I identified an issue with database
> > dumps, which apparently affects invalid foreign keys too:
> > http://archives.postg
On Thu, Jun 2, 2011 at 5:34 PM, Thom Brown wrote:
> On 8 February 2011 03:50, Robert Haas wrote:
>> On Thu, Feb 3, 2011 at 11:00 PM, Robert Haas wrote:
>>> On Fri, Jan 14, 2011 at 6:15 AM, Simon Riggs wrote:
Patch to implement the proposed feature attached, for CFJan2011.
2 sub-c
On 8 February 2011 03:50, Robert Haas wrote:
> On Thu, Feb 3, 2011 at 11:00 PM, Robert Haas wrote:
>> On Fri, Jan 14, 2011 at 6:15 AM, Simon Riggs wrote:
>>> Patch to implement the proposed feature attached, for CFJan2011.
>>>
>>> 2 sub-command changes:
>>>
>>> ALTER TABLE foo ADD FOREIGN KEY fk
On Thu, Feb 3, 2011 at 11:00 PM, Robert Haas wrote:
> On Fri, Jan 14, 2011 at 6:15 AM, Simon Riggs wrote:
>> Patch to implement the proposed feature attached, for CFJan2011.
>>
>> 2 sub-command changes:
>>
>> ALTER TABLE foo ADD FOREIGN KEY fkoo ... NOT VALID;
>>
>> ALTER TABLE foo VALIDATE CONST
On Fri, Jan 14, 2011 at 6:15 AM, Simon Riggs wrote:
> Patch to implement the proposed feature attached, for CFJan2011.
>
> 2 sub-command changes:
>
> ALTER TABLE foo ADD FOREIGN KEY fkoo ... NOT VALID;
>
> ALTER TABLE foo VALIDATE CONSTRAINT fkoo;
This patch, which seems to be the latest version,
si...@2ndquadrant.com (Simon Riggs) writes:
> I just wanted to point out that the patch submitted here does not allow
> what is requested here for FKs (nor indexes).
That's fine; I was trying to support the thought that there was
something useful about this idea. Being able to expressly deactivat
Tom Lane wrote:
> I seem to recall pointing out upthread that the FK check triggers
> are designed on the assumption that the constraint does hold
> currently. Has any analysis been done on exactly how badly they'll
> fail when it doesn't hold? I remain unconvinced that this behavior
> is desir
On Sun, 2011-01-23 at 16:13 -0500, Tom Lane wrote:
> Simon Riggs writes:
> > You can add an FK without an initial check, but the FK is enforced for
> > all further DML changes.
>
> I seem to recall pointing out upthread that the FK check triggers are
> designed on the assumption that the constrai
Simon Riggs writes:
> You can add an FK without an initial check, but the FK is enforced for
> all further DML changes.
I seem to recall pointing out upthread that the FK check triggers are
designed on the assumption that the constraint does hold currently.
Has any analysis been done on exactly h
On Tue, 2010-12-14 at 11:24 -0500, Chris Browne wrote:
> t...@sss.pgh.pa.us (Tom Lane) writes:
> > Robert Haas writes:
> >> ... On the
> >> other hand, there's clearly also a use case for this behavior. If a
> >> bulk load of prevalidated data forces an expensive revalidation of
> >> constraints
On Tue, 2011-01-18 at 14:26 -0600, Jim Nasby wrote:
> >
> > 2 sub-command changes:
> >
> > ALTER TABLE foo ADD FOREIGN KEY fkoo ... NOT VALID;
> >
> > ALTER TABLE foo VALIDATE CONSTRAINT fkoo;
>
> Sorry for the late reply; I just saw this...
>
> Is there any way to be able to get the bad recor
On Jan 14, 2011, at 5:15 AM, Simon Riggs wrote:
> On Mon, 2010-12-13 at 17:15 +, Peter Geoghegan wrote:
>> On 13 December 2010 16:08, Robert Haas wrote:
>>> On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs wrote:
2. pg_validate_foreign_key('constraint name');
Returns immediately if FK
On Mon, 2010-12-13 at 17:15 +, Peter Geoghegan wrote:
> On 13 December 2010 16:08, Robert Haas wrote:
> > On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs wrote:
> >> 2. pg_validate_foreign_key('constraint name');
> >> Returns immediately if FK is valid
> >> Returns SETOF rows that violate the c
On Wed, 2011-01-05 at 22:05 -0500, Bruce Momjian wrote:
> Robert Haas wrote:
> > On Mon, Dec 13, 2010 at 12:59 AM, Rob Wultsch wrote:
> > > On Sun, Dec 12, 2010 at 7:24 PM, Andrew Dunstan
> > > wrote:
> > >> In fact it's possible now to disable FK enforcement, by disabling the
> > >> triggers. I
Robert Haas wrote:
> On Mon, Dec 13, 2010 at 12:59 AM, Rob Wultsch wrote:
> > On Sun, Dec 12, 2010 at 7:24 PM, Andrew Dunstan wrote:
> >> In fact it's possible now to disable FK enforcement, by disabling the
> >> triggers. It's definitely a footgun though. Just the other day I was asked
> >> how
t...@sss.pgh.pa.us (Tom Lane) writes:
> Robert Haas writes:
>> ... On the
>> other hand, there's clearly also a use case for this behavior. If a
>> bulk load of prevalidated data forces an expensive revalidation of
>> constraints that are already known to hold, there's a real chance the
>> DBA w
On Mon, 2010-12-13 at 17:15 +, Peter Geoghegan wrote:
> On 13 December 2010 16:08, Robert Haas wrote:
> > On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs wrote:
> >> 2. pg_validate_foreign_key('constraint name');
> >> Returns immediately if FK is valid
> >> Returns SETOF rows that violate the c
On Mon, Dec 13, 2010 at 05:15:29PM +, Peter Geoghegan wrote:
> On 13 December 2010 16:08, Robert Haas wrote:
> > On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs wrote:
> >> 2. pg_validate_foreign_key('constraint name');
> >> Returns immediately if FK is valid
> >> Returns SETOF rows that violat
On 13 December 2010 16:08, Robert Haas wrote:
> On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs wrote:
>> 2. pg_validate_foreign_key('constraint name');
>> Returns immediately if FK is valid
>> Returns SETOF rows that violate the constraint, or if no rows are
>> returned it updates constraint to sh
On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs wrote:
> (a) ALTER TABLE ... ADD FOREIGN KEY ... NOT VALIDATED INITIALLY;
> will add a FK but NOT run the check - we mark it as "check pending".
> Lock held: ShareRowExclusiveLock
Seems about right. Not sure whether the lock strength is correct.
> (
On Mon, 2010-12-13 at 11:54 +, Peter Geoghegan wrote:
> On 13 December 2010 10:30, Dimitri Fontaine wrote:
> > Seriously, real-world use cases such as Kevin's one seems to warrant
> > that we are able to create a table withouth enforcing the FK. That's
> > horrid, yes, that's needed, too. Mayb
On Mon, Dec 13, 2010 at 12:59 AM, Rob Wultsch wrote:
> On Sun, Dec 12, 2010 at 7:24 PM, Andrew Dunstan wrote:
>> In fact it's possible now to disable FK enforcement, by disabling the
>> triggers. It's definitely a footgun though. Just the other day I was asked
>> how data violating the constraint
On 13 December 2010 10:30, Dimitri Fontaine wrote:
> Seriously, real-world use cases such as Kevin's one seems to warrant
> that we are able to create a table withouth enforcing the FK. That's
> horrid, yes, that's needed, too. Maybe some operations would have to be
> instructed that the constrain
2010/12/13 Tom Lane :
> But allow me to harbor doubts that they really
> intend to allow someone to force a constraint to be considered valid
> without any verification.
"Table constraints are either enforced or not enforced. Domain
constraints and assertions are always enforced.", 4.17.2
I don'
Tom Lane writes:
> The incremental FK checks are designed on the assumption that the
> constraint condition held before; they aren't likely to behave very
> sanely if the data is bad. I'd want to see a whole lot more analysis of
> the resulting behavior before even considering an idea like this
On Sun, 2010-12-12 at 19:07 -0500, Tom Lane wrote:
> Robert Haas writes:
> > ... On the
> > other hand, there's clearly also a use case for this behavior. If a
> > bulk load of prevalidated data forces an expensive revalidation of
> > constraints that are already known to hold, there's a real ch
On Sun, Dec 12, 2010 at 7:24 PM, Andrew Dunstan wrote:
> In fact it's possible now to disable FK enforcement, by disabling the
> triggers. It's definitely a footgun though. Just the other day I was asked
> how data violating the constraint could have got into the table, and caused
> some surprise
Peter Geoghegan wrote:
> If we followed this behaviour, we wouldn't "let people just
> arbitrarily claim" that a referential condition exists - rather,
> we'd let them assert that it /ought/ to exist, and that it will be
> maintained going forward, and give them the option of verifying
> that as
I wouldn't like to comment on whether or not Simon has correctly
interpreted the words of the SQL standards committee, because
standards committees sometimes word things in an intentionally
ambiguous way to placate different interests, and because it seems
fairly inconsequential in this case. IMHO
On 12/12/2010 08:27 PM, Rob Wultsch wrote:
MySQL does in fact have this feature and it is used by mysqldump. This
feature is very useful.
The trouble is that FK's have more than one use. In particular, they
have a documentary use that's used by tools that analyze databases, as
well as by
On Sun, Dec 12, 2010 at 4:49 PM, Robert Haas wrote:
> On Sun, Dec 12, 2010 at 6:20 PM, Tom Lane wrote:
>> Simon Riggs writes:
>>> On Sun, 2010-12-12 at 17:57 -0500, Tom Lane wrote:
Huh? It allows you to postpone the check until commit. That's far from
not enforcing it.
>>
>>> This cl
On Sun, Dec 12, 2010 at 7:07 PM, Tom Lane wrote:
> Robert Haas writes:
>> ... On the
>> other hand, there's clearly also a use case for this behavior. If a
>> bulk load of prevalidated data forces an expensive revalidation of
>> constraints that are already known to hold, there's a real chance
Robert Haas writes:
> ... On the
> other hand, there's clearly also a use case for this behavior. If a
> bulk load of prevalidated data forces an expensive revalidation of
> constraints that are already known to hold, there's a real chance the
> DBA will be backed into a corner where he simply h
On Sun, Dec 12, 2010 at 6:20 PM, Tom Lane wrote:
> Simon Riggs writes:
>> On Sun, 2010-12-12 at 17:57 -0500, Tom Lane wrote:
>>> Huh? It allows you to postpone the check until commit. That's far from
>>> not enforcing it.
>
>> This clearly implies that un-enforced constraints are not checked at
Simon Riggs writes:
> On Sun, 2010-12-12 at 17:57 -0500, Tom Lane wrote:
>> Huh? It allows you to postpone the check until commit. That's far from
>> not enforcing it.
> This clearly implies that un-enforced constraints are not checked at
> commit.
[ shrug... ] I can't argue with you about wh
On Sun, 2010-12-12 at 17:57 -0500, Tom Lane wrote:
> Simon Riggs writes:
> > The new SQL Standard (SQL:2011) contains this:
> > "Table constraints are either enforced or not enforced. Domain
> > constraints and assertions are always enforced.", 4.17.2
>
> > The SQL Standard allows you to turn the
Simon Riggs writes:
> The new SQL Standard (SQL:2011) contains this:
> "Table constraints are either enforced or not enforced. Domain
> constraints and assertions are always enforced.", 4.17.2
> The SQL Standard allows you to turn the checking on and off for CHECK
> constraints, UNIQUE constraint
The new SQL Standard (SQL:2011) contains this:
"Table constraints are either enforced or not enforced. Domain
constraints and assertions are always enforced.", 4.17.2
The SQL Standard allows you to turn the checking on and off for CHECK
constraints, UNIQUE constraints and FOREIGN KEYS.
Which of
38 matches
Mail list logo
