Thread: can this be done with a check expression?
I have a table with 3 columns: name text start_id integer end_id integer start_id and end_id are ranges which must not overlap but can have gaps between them. Is it possible to formulate a table check constraint that can verify that either id does not fall within an existing range at insert time? IE prevent overlaps during insert? Thanks, Wayne
Wayne Cuddy <lists-pgsql@useunix.net> writes:
> I have a table with 3 columns:
> name text
> start_id integer
> end_id integer
> start_id and end_id are ranges which must not overlap but can have gaps
> between them. Is it possible to formulate a table check constraint that
> can verify that either id does not fall within an existing range at
> insert time? IE prevent overlaps during insert?
You can't do it reliably with a check constraint, at least not short of
taking table-wide locks to serialize all modifications of the table.
(If you were willing to do that, a check constraint calling a function
that does an EXISTS probe would work; although personally I'd use a
trigger instead. Either way, performance is likely to suck.)
A less bogus way of doing things is to use an EXCLUDE constraint,
although that will restrict you to be running PG 9.0 or newer. You
also need some way of representing the ranges as indexable objects.
In 9.0 or 9.1, probably the best way is to use contrib/seg/ to
represent the ranges as line segments. 9.2 will have a cleaner
solution, ie range types.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Wayne Cuddy <lists-pgsql@useunix.net> writes:
> A less bogus way of doing things is to use an EXCLUDE constraint,
> although that will restrict you to be running PG 9.0 or newer. You
> also need some way of representing the ranges as indexable objects.
> In 9.0 or 9.1, probably the best way is to use contrib/seg/ to
> represent the ranges as line segments. 9.2 will have a cleaner
> solution, ie range types.
Simple example for 9.2:
test=# create table foo (name text, id_range int4range, exclude using gist(name with =, id_range with &&));
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "foo_name_id_range_excl" for table "foo"
CREATE TABLE
Time: 40,273 ms
test=*# insert into foo values ('name1', '[1,9)');
INSERT 0 1
Time: 0,660 ms
test=*# insert into foo values ('name1', '[10,19)');
INSERT 0 1
Time: 0,313 ms
test=*# insert into foo values ('name1', '[5,15)');
ERROR: conflicting key value violates exclusion constraint "foo_name_id_range_excl"
DETAIL: Key (name, id_range)=(name1, [5,15)) conflicts with existing key (name, id_range)=(name1, [1,9)).
test=!#
Great feature! Thx to all developers behing PG!
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Aug 2, 2012, at 7:10 PM, Wayne Cuddy wrote:
I have a table with 3 columns:
name text
start_id integer
end_id integer
start_id and end_id are ranges which must not overlap but can have gaps
between them. Is it possible to formulate a table check constraint that
can verify that either id does not fall within an existing range at
insert time? IE prevent overlaps during insert?
Thanks,
Wayne
So this answer will not help you for the here-and-now, but Postgres 9.2 is going to be released in the near future (though the beta is available) and contains "range types" which have check constraints on them:
You could formulate a check constraint right now to do the equivalent, albeit it will involve a lot of conditions.
Jonathan
Thanks for the input. I don't insert into this table that often so I'll just prevent overlaps at the application level since I'm running 9.0.X and not really in a position to upgrade right now. Thanks again, Wayne On Fri, Aug 03, 2012 at 11:50:13AM -0400, Jonathan S. Katz wrote: > On Aug 2, 2012, at 7:10 PM, Wayne Cuddy wrote: > > > I have a table with 3 columns: > > > > name text > > start_id integer > > end_id integer > > > > start_id and end_id are ranges which must not overlap but can have gaps > > between them. Is it possible to formulate a table check constraint that > > can verify that either id does not fall within an existing range at > > insert time? IE prevent overlaps during insert? > > > > Thanks, > > Wayne > > So this answer will not help you for the here-and-now, but Postgres 9.2 is going to be released in the near future (thoughthe beta is available) and contains "range types" which have check constraints on them: > > http://www.postgresql.org/docs/9.2/static/rangetypes.html#RANGETYPES-CONSTRAINT > > You could formulate a check constraint right now to do the equivalent, albeit it will involve a lot of conditions. > > Jonathan
I'm seeing this in my PGSQL logs, is this something to be concerned about? I know the file system it sits on is reliable and the DB appears to run with fine, additionally the log indicates it's a debug message. I did some google searches and didn't find much. When I examine the file system file by these names to not exist. Thanks, Wayne ---------------------------------------------------------------------- 2012-07-19 21:05:42.840 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45365979_fsm" but retrying: No such file or directory 2012-07-20 06:25:43.899 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45413298_fsm" but retrying: No such file or directory 2012-07-20 11:20:44.755 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45438221_fsm" but retrying: No such file or directory 2012-07-20 15:40:45.974 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45460199_fsm" but retrying: No such file or directory 2012-07-22 12:35:43.882 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/45687933_fsm" but retrying: No such file or directory 2012-07-25 03:35:42.933 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46007387_fsm" but retrying: No such file or directory 2012-07-25 06:30:45.354 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46022167_fsm" but retrying: No such file or directory 2012-07-26 06:48:13.595 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46145719_fsm" but retrying: No such file or directory 2012-07-28 17:58:40.774 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46445945_fsm" but retrying: No such file or directory 2012-08-01 09:38:42.234 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/46890581_fsm" but retrying: No such file or directory 2012-08-05 01:07:43.659 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/47334522_fsm" but retrying: No such file or directory 2012-08-07 01:27:43.605 GMT 8966 4fd07ba5.2306 @: DEBUG: could not fsync file "base/16711/47579627_fsm" but retrying: No such file or directory
This was posted under the wrong subject.. Please ignore, will repost. On Wed, Aug 08, 2012 at 11:41:01AM -0400, Wayne Cuddy wrote: > I'm seeing this in my PGSQL logs, is this something to be concerned > about? I know the file system it sits on is reliable and the DB appears > to run with fine, additionally the log indicates it's a debug message. I > did some google searches and didn't find much. > > When I examine the file system file by these names to not exist. > > Thanks, > Wayne > > ---------------------------------------------------------------------- > 2012-07-19 21:05:42.840 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45365979_fsm" but retrying: No such file or directory > 2012-07-20 06:25:43.899 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45413298_fsm" but retrying: No such file or directory > 2012-07-20 11:20:44.755 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45438221_fsm" but retrying: No such file or directory > 2012-07-20 15:40:45.974 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45460199_fsm" but retrying: No such file or directory > 2012-07-22 12:35:43.882 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/45687933_fsm" but retrying: No such file or directory > 2012-07-25 03:35:42.933 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46007387_fsm" but retrying: No such file or directory > 2012-07-25 06:30:45.354 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46022167_fsm" but retrying: No such file or directory > 2012-07-26 06:48:13.595 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46145719_fsm" but retrying: No such file or directory > 2012-07-28 17:58:40.774 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46445945_fsm" but retrying: No such file or directory > 2012-08-01 09:38:42.234 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/46890581_fsm" but retrying: No such file or directory > 2012-08-05 01:07:43.659 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/47334522_fsm" but retrying: No such file or directory > 2012-08-07 01:27:43.605 GMT 8966 4fd07ba5.2306 @: DEBUG: could not > fsync > file "base/16711/47579627_fsm" but retrying: No such file or directory > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql