pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcan this be done with a check expression?
7+ messages / 4 participants
[nested] [flat]
* can this be done with a check expression?
@ 2012-08-02 23:10 Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-02 23:23 ` Re: can this be done with a check expression? Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:50 ` Re: can this be done with a check expression? Jonathan S. Katz <jonathan.katz@excoventures.com>
0 siblings, 2 replies; 7+ messages in thread
From: Wayne Cuddy @ 2012-08-02 23:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can this be done with a check expression?
2012-08-02 23:10 can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
@ 2012-08-02 23:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 05:40 ` Re: can this be done with a check expression? Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2012-08-02 23:23 UTC (permalink / raw)
To: Wayne Cuddy <lists-pgsql@useunix.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can this be done with a check expression?
2012-08-02 23:10 can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-02 23:23 ` Re: can this be done with a check expression? Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-08-03 05:40 ` Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 7+ messages in thread
From: Andreas Kretschmer @ 2012-08-03 05:40 UTC (permalink / raw)
To: pgsql-sql
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°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can this be done with a check expression?
2012-08-02 23:10 can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
@ 2012-08-03 15:50 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
2012-08-03 19:50 ` Re: can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
1 sibling, 1 reply; 7+ messages in thread
From: Jonathan S. Katz @ 2012-08-03 15:50 UTC (permalink / raw)
To: Wayne Cuddy <lists-pgsql@useunix.net>; +Cc: pgsql-sql
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:
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=
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can this be done with a check expression?
2012-08-02 23:10 can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-03 15:50 ` Re: can this be done with a check expression? Jonathan S. Katz <jonathan.katz@excoventures.com>
@ 2012-08-03 19:50 ` Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-08 15:41 ` Re: can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
0 siblings, 1 reply; 7+ messages in thread
From: Wayne Cuddy @ 2012-08-03 19:50 UTC (permalink / raw)
To: pgsql-sql
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 (though the 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can this be done with a check expression?
2012-08-02 23:10 can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-03 15:50 ` Re: can this be done with a check expression? Jonathan S. Katz <jonathan.katz@excoventures.com>
2012-08-03 19:50 ` Re: can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
@ 2012-08-08 15:41 ` Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-08 15:43 ` Re: can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
0 siblings, 1 reply; 7+ messages in thread
From: Wayne Cuddy @ 2012-08-08 15:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can this be done with a check expression?
2012-08-02 23:10 can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-03 15:50 ` Re: can this be done with a check expression? Jonathan S. Katz <jonathan.katz@excoventures.com>
2012-08-03 19:50 ` Re: can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-08 15:41 ` Re: can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
@ 2012-08-08 15:43 ` Wayne Cuddy <lists-pgsql@useunix.net>
0 siblings, 0 replies; 7+ messages in thread
From: Wayne Cuddy @ 2012-08-08 15:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-08-08 15:43 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-02 23:10 can this be done with a check expression? Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-02 23:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 05:40 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-08-03 15:50 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
2012-08-03 19:50 ` Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-08 15:41 ` Wayne Cuddy <lists-pgsql@useunix.net>
2012-08-08 15:43 ` Wayne Cuddy <lists-pgsql@useunix.net>
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