pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about check constraints
6+ messages / 3 participants
[nested] [flat]
* Question about check constraints
@ 2006-01-27 01:01 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 0 replies; 6+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-01-27 01:01 UTC (permalink / raw)
To: pgsql-sql-owner@postgresql.org
Hi all,
I have a table where two columns have two different check constraints
associated with them. When I update one column, the check constraint on
the other column is also executed. Is there a way to avoid this? I want
to check only for the condition defined for the column being updated.
Thanks,
Kashmira
^ permalink raw reply [nested|flat] 6+ messages in thread
* Question about check constraints
@ 2006-01-27 23:06 Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-01-28 00:40 ` Re: Question about check constraints Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 6+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-01-27 23:06 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I have a table where two columns have two different check constraints
associated with them. When I update one column, the check constraint on
the other column is also executed. Is there a way to avoid this? I want
to check only for the condition defined for the column being updated.
Thanks,
Kashmira
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about check constraints
2006-01-27 23:06 Question about check constraints Kashmira Patel (kupatel) <kupatel@cisco.com>
@ 2006-01-28 00:40 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2006-01-28 00:40 UTC (permalink / raw)
To: Kashmira Patel (kupatel) <kupatel@cisco.com>; +Cc: pgsql-sql
On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel) wrote:
> I have a table where two columns have two different check constraints
> associated with them. When I update one column, the check constraint on
> the other column is also executed. Is there a way to avoid this? I want
> to check only for the condition defined for the column being updated.
I don't think you can change this behavior: each CHECK constraint
is evaluated for the new row regardless of whether a particular
column changed or not. However, you could enforce the constraints
with a trigger and skip checks where NEW.column is the same as
OLD.column.
Why the concern? Are the checks expensive? Do they have side
effects? What do they do?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about check constraints
@ 2006-01-28 01:46 Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-01-28 05:24 ` Re: Question about check constraints Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-01-28 01:46 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
Both concerns.
1) There are actually more than two columns with such checks, and each
one calls a few functions which execute some more queries. So I would
like to invoke these checks only when necessary.
2) The bigger concern is the side effect: Here's my schema:
CREATE TABLE vm_device
(
device_id INTEGER UNIQUE NOT NULL
REFERENCES device_table(device_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
preference VARCHAR(1) NOT NULL DEFAULT 'U'
CHECK (CASE WHEN preference = 'U'
THEN true
ELSE validate_preference()
END),
enabled BOOLEAN NOT NULL DEFAULT false
CHECK (CASE WHEN enabled = false
THEN true
ELSE
validate_system_enabled() AND
validate_enabled(device_id)
END),
attach_vm BOOLEAN NOT NULL DEFAULT false
CHECK (CASE WHEN attach_vm = false
THEN true
ELSE validate_attach_vm()
END),
PRIMARY KEY (device_id)
) WITHOUT OIDS;
This table contains some information about a device in my system.
The issue is with the enabled column. It basically enables/disables the
device.
The device can be enabled only when the two check conditions pass. But
once it is
enabled, the conditions of the system might change such that if executed
again, these
conditions might not pass. We want to allow such situations. The problem
arises when
we want to change the value of some other column, say attach_vm.
Although the check
constraints for the attach_vm column pass, those for enabled column
fail, and I cannot
complete my updates.
Any suggestions on the best way to overcome this?
Thanks,
kashmira
-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Friday, January 27, 2006 4:40 PM
To: Kashmira Patel (kupatel)
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Question about check constraints
On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel)
wrote:
> I have a table where two columns have two different check
> constraints associated with them. When I update one column, the check
> constraint on the other column is also executed. Is there a way to
> avoid this? I want to check only for the condition defined for the
column being updated.
I don't think you can change this behavior: each CHECK constraint is
evaluated for the new row regardless of whether a particular column
changed or not. However, you could enforce the constraints with a
trigger and skip checks where NEW.column is the same as OLD.column.
Why the concern? Are the checks expensive? Do they have side effects?
What do they do?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about check constraints
2006-01-28 01:46 Re: Question about check constraints Kashmira Patel (kupatel) <kupatel@cisco.com>
@ 2006-01-28 05:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2006-01-28 05:24 UTC (permalink / raw)
To: Kashmira Patel (kupatel) <kupatel@cisco.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
On Fri, 27 Jan 2006, Kashmira Patel (kupatel) wrote:
> Both concerns.
> 1) There are actually more than two columns with such checks, and each
> one calls a few functions which execute some more queries. So I would
> like to invoke these checks only when necessary.
> 2) The bigger concern is the side effect: Here's my schema:
>
> CREATE TABLE vm_device
> (
> device_id INTEGER UNIQUE NOT NULL
> REFERENCES device_table(device_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> preference VARCHAR(1) NOT NULL DEFAULT 'U'
> CHECK (CASE WHEN preference = 'U'
> THEN true
> ELSE validate_preference()
> END),
> enabled BOOLEAN NOT NULL DEFAULT false
> CHECK (CASE WHEN enabled = false
> THEN true
> ELSE
> validate_system_enabled() AND
>
> validate_enabled(device_id)
> END),
> attach_vm BOOLEAN NOT NULL DEFAULT false
> CHECK (CASE WHEN attach_vm = false
> THEN true
> ELSE validate_attach_vm()
> END),
>
> PRIMARY KEY (device_id)
> ) WITHOUT OIDS;
>
> This table contains some information about a device in my system. The
> issue is with the enabled column. It basically enables/disables the
> device. The device can be enabled only when the two check conditions
> pass. But once it is enabled, the conditions of the system might change
> such that if executed again, these conditions might not pass. We want to
> allow such situations. The problem arises when we want to change the
> value of some other column, say attach_vm. Although the check
> constraints for the attach_vm column pass, those for enabled column
> fail, and I cannot complete my updates.
In that case check constraints are probably not the correct tool. IIRC
the theoretical model from the spec implies that all check constraints in
the system are supposed to be satisfied at their check time (statement end
in most cases) and that it doesn't matter what the change was, and as
such, the conditions of the system shouldn't have been allowed to change
such that a row with enabled=true existed when its constraint would be
violated at this moment. I don't think that's practically reasonable to
enforce in general, but we do the best we can which is fail the later
update.
> Any suggestions on the best way to overcome this?
I'd second Michael's suggestion of a trigger.
> -----Original Message-----
> From: Michael Fuhr [mailto:mike@fuhr.org]
> Sent: Friday, January 27, 2006 4:40 PM
> To: Kashmira Patel (kupatel)
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Question about check constraints
>
> On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel)
> wrote:
> > I have a table where two columns have two different check
> > constraints associated with them. When I update one column, the check
> > constraint on the other column is also executed. Is there a way to
> > avoid this? I want to check only for the condition defined for the
> column being updated.
>
> I don't think you can change this behavior: each CHECK constraint is
> evaluated for the new row regardless of whether a particular column
> changed or not. However, you could enforce the constraints with a
> trigger and skip checks where NEW.column is the same as OLD.column.
>
> Why the concern? Are the checks expensive? Do they have side effects?
> What do they do?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about check constraints
@ 2006-01-28 06:48 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 0 replies; 6+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-01-28 06:48 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
Thanks, Michael and Stephan, for the replies.
I think I will change my schema and remove the check constraint on the
enable column,
since, as Stephan pointed out, it was bad/wrong design to begin with.
Thanks again.
Kashmira
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Friday, January 27, 2006 9:25 PM
To: Kashmira Patel (kupatel)
Cc: Michael Fuhr; pgsql-sql@postgresql.org
Subject: Re: [SQL] Question about check constraints
On Fri, 27 Jan 2006, Kashmira Patel (kupatel) wrote:
> Both concerns.
> 1) There are actually more than two columns with such checks, and each
> one calls a few functions which execute some more queries. So I would
> like to invoke these checks only when necessary.
> 2) The bigger concern is the side effect: Here's my schema:
>
> CREATE TABLE vm_device
> (
> device_id INTEGER UNIQUE NOT NULL
> REFERENCES device_table(device_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> preference VARCHAR(1) NOT NULL DEFAULT 'U'
> CHECK (CASE WHEN preference = 'U'
> THEN true
> ELSE validate_preference()
> END),
> enabled BOOLEAN NOT NULL DEFAULT false
> CHECK (CASE WHEN enabled = false
> THEN true
> ELSE
> validate_system_enabled() AND
>
> validate_enabled(device_id)
> END),
> attach_vm BOOLEAN NOT NULL DEFAULT false
> CHECK (CASE WHEN attach_vm = false
> THEN true
> ELSE validate_attach_vm()
> END),
>
> PRIMARY KEY (device_id)
> ) WITHOUT OIDS;
>
> This table contains some information about a device in my system. The
> issue is with the enabled column. It basically enables/disables the
> device. The device can be enabled only when the two check conditions
> pass. But once it is enabled, the conditions of the system might
> change such that if executed again, these conditions might not pass.
> We want to allow such situations. The problem arises when we want to
> change the value of some other column, say attach_vm. Although the
> check constraints for the attach_vm column pass, those for enabled
> column fail, and I cannot complete my updates.
In that case check constraints are probably not the correct tool. IIRC
the theoretical model from the spec implies that all check constraints
in the system are supposed to be satisfied at their check time
(statement end in most cases) and that it doesn't matter what the change
was, and as such, the conditions of the system shouldn't have been
allowed to change such that a row with enabled=true existed when its
constraint would be violated at this moment. I don't think that's
practically reasonable to enforce in general, but we do the best we can
which is fail the later update.
> Any suggestions on the best way to overcome this?
I'd second Michael's suggestion of a trigger.
> -----Original Message-----
> From: Michael Fuhr [mailto:mike@fuhr.org]
> Sent: Friday, January 27, 2006 4:40 PM
> To: Kashmira Patel (kupatel)
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Question about check constraints
>
> On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel)
> wrote:
> > I have a table where two columns have two different check
> > constraints associated with them. When I update one column, the
> > check constraint on the other column is also executed. Is there a
> > way to avoid this? I want to check only for the condition defined
> > for the
> column being updated.
>
> I don't think you can change this behavior: each CHECK constraint is
> evaluated for the new row regardless of whether a particular column
> changed or not. However, you could enforce the constraints with a
> trigger and skip checks where NEW.column is the same as OLD.column.
>
> Why the concern? Are the checks expensive? Do they have side
effects?
> What do they do?
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-01-28 06:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-27 01:01 Question about check constraints Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-01-27 23:06 Question about check constraints Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-01-28 00:40 ` Michael Fuhr <mike@fuhr.org>
2006-01-28 01:46 Re: Question about check constraints Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-01-28 05:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-01-28 06:48 Re: Question about check constraints Kashmira Patel (kupatel) <kupatel@cisco.com>
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