pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedexclusion constraint for ranges of IP
11+ messages / 6 participants
[nested] [flat]
* exclusion constraint for ranges of IP
@ 2011-08-21 15:08 Herouth Maoz <herouth@unicell.co.il>
0 siblings, 3 replies; 11+ messages in thread
From: Herouth Maoz @ 2011-08-21 15:08 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm designing a new database. One of the table contains allowed IP ranges for a customer (Fields: customer_id, from_ip, to_ip) which is intended to check - if an incoming connection's originating IP number falls within the range, it is identified as a particular customer.
Naturally, I'd like to have constraints on the table that prevent entering of ip ranges that overlap. Is there a way to do that with exclusion constraints? Or do I have to define a new type for this?
Herouth
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-21 22:19 Harald Fuchs <hari.fuchs@gmail.com>
parent: Herouth Maoz <herouth@unicell.co.il>
2 siblings, 1 reply; 11+ messages in thread
From: Harald Fuchs @ 2011-08-21 22:19 UTC (permalink / raw)
To: pgsql-sql
In article <CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il>,
Herouth Maoz <herouth@unicell.co.il> writes:
> Hi,
> I'm designing a new database. One of the table contains allowed IP ranges for a customer (Fields: customer_id, from_ip, to_ip) which is intended to check - if an incoming connection's originating IP number falls within the range, it is identified as a particular customer.
> Naturally, I'd like to have constraints on the table that prevent entering of ip ranges that overlap. Is there a way to do that with exclusion constraints? Or do I have to define a new type for this?
This "new type" already exists: ip4r, which can be found in pgfoundry.
With it you can do
CREATE TABLE mytbl (
iprange ip4r NOT NULL,
...,
CONSTRAINT range_check CHECK ((NOT overlap(iprange)))
);
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-22 08:31 Herouth Maoz <herouth@unicell.co.il>
parent: Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: Herouth Maoz @ 2011-08-22 08:31 UTC (permalink / raw)
To: Harald Fuchs <hari.fuchs@gmail.com>; +Cc: pgsql-sql
On 22/08/2011, at 01:19, Harald Fuchs wrote:
> In article <CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il>,
> Herouth Maoz <herouth@unicell.co.il> writes:
>
>> Hi,
>> I'm designing a new database. One of the table contains allowed IP ranges for a customer (Fields: customer_id, from_ip, to_ip) which is intended to check - if an incoming connection's originating IP number falls within the range, it is identified as a particular customer.
>
>> Naturally, I'd like to have constraints on the table that prevent entering of ip ranges that overlap. Is there a way to do that with exclusion constraints? Or do I have to define a new type for this?
>
> This "new type" already exists: ip4r, which can be found in pgfoundry.
> With it you can do
>
> CREATE TABLE mytbl (
> iprange ip4r NOT NULL,
> ...,
> CONSTRAINT range_check CHECK ((NOT overlap(iprange)))
> );
Thank you.
I assume you can't use a CHECK constraint for between-rows constraints. Wouldn't this be
CONSTRAINT EXCLUDE ( iprange WITH && )
?
Basically, though, I'm not too happy about using compound types - that's why I asked if I have to. I'm not sure what my application will have to send and what it will receive when querying a compound type. I use PHP/ZF. I have just now posted a question on the pgsql-php list about this. I suspect I'll be getting a string which I'll have to parse, which would make the application more complicated to read and understand.
Herouth
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-22 09:27 Simone Sanfratello <simone.sanfra@gmail.com>
parent: Herouth Maoz <herouth@unicell.co.il>
2 siblings, 0 replies; 11+ messages in thread
From: Simone Sanfratello @ 2011-08-22 09:27 UTC (permalink / raw)
To: Herouth Maoz <herouth@unicell.co.il>; +Cc: Harald Fuchs <hari.fuchs@gmail.com>; pgsql-sql
Hi,
you can do the identification of customer by ip in many ways. IMHO, first of
all, you have to put the allowed IPs into your table.
The simpler way is to write all IPs allowed, of course.
The simpler way to do range check is to have 2 columns in table,
IP-range-starts and IP-range-ends, so the SQL could be easy using every data
types.
I prefer to use string data type and not compound ip4r type because can
check using reg.exp. in SQL select, writing IPs range like
1.2.3.100-1.2.3.150 or just single 2.3.4.5.
When I did something like that, I wrote IP in this form : 001002003100 and I
use the two columns of range-starts and range-ends, so it became very easy
(and I think very performant) doing check even with large table.
The worse way is to remand check to PHP, because you have to load entire
table during select and manually select target row there.
I hope these suggests help
2011/8/22 Herouth Maoz <herouth@unicell.co.il>
>
> On 22/08/2011, at 01:19, Harald Fuchs wrote:
>
> > In article <CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il>,
> > Herouth Maoz <herouth@unicell.co.il> writes:
> >
> >> Hi,
> >> I'm designing a new database. One of the table contains allowed IP
> ranges for a customer (Fields: customer_id, from_ip, to_ip) which is
> intended to check - if an incoming connection's originating IP number falls
> within the range, it is identified as a particular customer.
> >
> >> Naturally, I'd like to have constraints on the table that prevent
> entering of ip ranges that overlap. Is there a way to do that with exclusion
> constraints? Or do I have to define a new type for this?
> >
> > This "new type" already exists: ip4r, which can be found in pgfoundry.
> > With it you can do
> >
> > CREATE TABLE mytbl (
> > iprange ip4r NOT NULL,
> > ...,
> > CONSTRAINT range_check CHECK ((NOT overlap(iprange)))
> > );
>
>
> Thank you.
>
> I assume you can't use a CHECK constraint for between-rows constraints.
> Wouldn't this be
>
> CONSTRAINT EXCLUDE ( iprange WITH && )
>
> ?
>
> Basically, though, I'm not too happy about using compound types - that's
> why I asked if I have to. I'm not sure what my application will have to send
> and what it will receive when querying a compound type. I use PHP/ZF. I have
> just now posted a question on the pgsql-php list about this. I suspect I'll
> be getting a string which I'll have to parse, which would make the
> application more complicated to read and understand.
>
> Herouth
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Simone
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-22 11:37 Jasen Betts <jasen@xnet.co.nz>
parent: Herouth Maoz <herouth@unicell.co.il>
2 siblings, 0 replies; 11+ messages in thread
From: Jasen Betts @ 2011-08-22 11:37 UTC (permalink / raw)
To: pgsql-sql
On 2011-08-21, Herouth Maoz <herouth@unicell.co.il> wrote:
> Hi,
>
> I'm designing a new database. One of the table contains allowed IP ranges for a customer (Fields: customer_id, from_ip, to_ip) which is intended to check - if an incoming connection's originating IP number falls within the range, it is identified as a particular customer.
>
> Naturally, I'd like to have constraints on the table that prevent entering of ip ranges that overlap. Is there a way to do that with exclusion constraints? Or do I have to define a new type for this?
>
> Herouth
if you can use CIDR instead of ranges it should be relatively simple
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-22 13:07 Misa Simic <misa.simic@gmail.com>
parent: Herouth Maoz <herouth@unicell.co.il>
2 siblings, 0 replies; 11+ messages in thread
From: Misa Simic @ 2011-08-22 13:07 UTC (permalink / raw)
To: Herouth Maoz <herouth@unicell.co.il>; +Cc: Harald Fuchs <hari.fuchs@gmail.com>; pgsql-sql
Hi Herouth,
I think you are right about exclusion...
If you are getting 'string' I think then command would be:
INSERT INTO customer_ip_range(cutomer_id, ip4r) VALUES('customeridstring',
ip4r('iprangestring'))
Kind Regards,
Misa
2011/8/22 Herouth Maoz <herouth@unicell.co.il>
>
> On 22/08/2011, at 01:19, Harald Fuchs wrote:
>
> > In article <CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il>,
> > Herouth Maoz <herouth@unicell.co.il> writes:
> >
> >> Hi,
> >> I'm designing a new database. One of the table contains allowed IP
> ranges for a customer (Fields: customer_id, from_ip, to_ip) which is
> intended to check - if an incoming connection's originating IP number falls
> within the range, it is identified as a particular customer.
> >
> >> Naturally, I'd like to have constraints on the table that prevent
> entering of ip ranges that overlap. Is there a way to do that with exclusion
> constraints? Or do I have to define a new type for this?
> >
> > This "new type" already exists: ip4r, which can be found in pgfoundry.
> > With it you can do
> >
> > CREATE TABLE mytbl (
> > iprange ip4r NOT NULL,
> > ...,
> > CONSTRAINT range_check CHECK ((NOT overlap(iprange)))
> > );
>
>
> Thank you.
>
> I assume you can't use a CHECK constraint for between-rows constraints.
> Wouldn't this be
>
> CONSTRAINT EXCLUDE ( iprange WITH && )
>
> ?
>
> Basically, though, I'm not too happy about using compound types - that's
> why I asked if I have to. I'm not sure what my application will have to send
> and what it will receive when querying a compound type. I use PHP/ZF. I have
> just now posted a question on the pgsql-php list about this. I suspect I'll
> be getting a string which I'll have to parse, which would make the
> application more complicated to read and understand.
>
> Herouth
> --
> 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] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-22 15:20 Harald Fuchs <hari.fuchs@gmail.com>
parent: Herouth Maoz <herouth@unicell.co.il>
2 siblings, 0 replies; 11+ messages in thread
From: Harald Fuchs @ 2011-08-22 15:20 UTC (permalink / raw)
To: pgsql-sql
In article <1343D11C-6F58-4653-8EA8-837C01E6184F@unicell.co.il>,
Herouth Maoz <herouth@unicell.co.il> writes:
> On 22/08/2011, at 01:19, Harald Fuchs wrote:
>> In article <CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il>,
>> Herouth Maoz <herouth@unicell.co.il> writes:
>>
>>> Hi,
>>> I'm designing a new database. One of the table contains allowed IP ranges for a customer (Fields: customer_id, from_ip, to_ip) which is intended to check - if an incoming connection's originating IP number falls within the range, it is identified as a particular customer.
>>
>>> Naturally, I'd like to have constraints on the table that prevent entering of ip ranges that overlap. Is there a way to do that with exclusion constraints? Or do I have to define a new type for this?
>>
>> This "new type" already exists: ip4r, which can be found in pgfoundry.
>> With it you can do
>>
>> CREATE TABLE mytbl (
>> iprange ip4r NOT NULL,
>> ...,
>> CONSTRAINT range_check CHECK ((NOT overlap(iprange)))
>> );
> Thank you.
> I assume you can't use a CHECK constraint for between-rows constraints. Wouldn't this be
> CONSTRAINT EXCLUDE ( iprange WITH && )
> ?
You're right. In the old PostgreSQL version I had to use I defined a
helper function
CREATE FUNCTION overlap(ip4r) RETURNS boolean
LANGUAGE sql
AS $_$
SELECT count(*) > 0
FROM mytbl
WHERE iprange != $1 AND iprange && $1
$_$;
for the CHECK CONSTRAINT, but in more recent PostgreSQL versions you can
just say
EXCLUDE (iprange WITH &&)
(without CONSTRAINT).
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-23 08:27 Herouth Maoz <herouth@unicell.co.il>
parent: Herouth Maoz <herouth@unicell.co.il>
2 siblings, 2 replies; 11+ messages in thread
From: Herouth Maoz @ 2011-08-23 08:27 UTC (permalink / raw)
To: pgsql-sql
My thanks to everyone who replied.
I have decided not to implement that constraint at this time. Using a compound type will make the system more complicated and less readable, plus requires installing the package which is beyond vanilla PostgreSQL.
Now I have another exclusion constraint I'm thinking about in another and I want to verify that this will do what I mean it to do:
CREATE TABLE invoice_definitions
(
id SERIAL PRIMARY KEY NOT NULL,
customer_id INTEGER NOT NULL REFERENCES customers(id),
is_default BOOLEAN NOT NULL DEFAULT FALSE,
bill_description VARCHAR(100) NOT NULL,
itemized_description VARCHAR(100) NOT NULL,
EXCLUDE USING GIST ( customer_id WITH =, is_default WITH AND )
)
;
Basically, each customer can have several rows in this table, but only one per customer is allowed to have is_default = true. Is this exclude constraint correct?
TIA,
Herouth=
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-23 10:15 Samuel Gendler <sgendler@ideasculptor.com>
parent: Herouth Maoz <herouth@unicell.co.il>
1 sibling, 0 replies; 11+ messages in thread
From: Samuel Gendler @ 2011-08-23 10:15 UTC (permalink / raw)
To: Herouth Maoz <herouth@unicell.co.il>; +Cc: pgsql-sql
On Tue, Aug 23, 2011 at 1:27 AM, Herouth Maoz <herouth@unicell.co.il> wrote:
> My thanks to everyone who replied.
>
> I have decided not to implement that constraint at this time. Using a
> compound type will make the system more complicated and less readable, plus
> requires installing the package which is beyond vanilla PostgreSQL.
>
> Now I have another exclusion constraint I'm thinking about in another and I
> want to verify that this will do what I mean it to do:
>
> CREATE TABLE invoice_definitions
> (
> id SERIAL PRIMARY KEY NOT NULL,
> customer_id INTEGER NOT NULL REFERENCES customers(id),
> is_default BOOLEAN NOT NULL DEFAULT FALSE,
> bill_description VARCHAR(100) NOT NULL,
> itemized_description VARCHAR(100) NOT NULL,
> EXCLUDE USING GIST ( customer_id WITH =, is_default WITH AND )
> )
> ;
>
> Basically, each customer can have several rows in this table, but only one
> per customer is allowed to have is_default = true. Is this exclude
> constraint correct?
>
You can validate this yourself with 3 insert statements into the table
declared in your email.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-23 10:31 Jasen Betts <jasen@xnet.co.nz>
parent: Herouth Maoz <herouth@unicell.co.il>
1 sibling, 1 reply; 11+ messages in thread
From: Jasen Betts @ 2011-08-23 10:31 UTC (permalink / raw)
To: pgsql-sql
On 2011-08-23, Herouth Maoz <herouth@unicell.co.il> wrote:
> EXCLUDE USING GIST ( customer_id WITH =, is_default WITH AND )
> Basically, each customer can have several rows in this table, but only =
> one per customer is allowed to have is_default =3D true. Is this exclude =
> constraint correct?
I don't really understand exclude, but instead of EXCLUDE... I would do
CREATE UNIQUE INDEX "invoice_definitions-unique-default"
ON invoice_definitions(customer_id) WHERE is_default;
Which would create a smaller (and probably faster) BTREE index
containing only the rows with is_default true.
There seems to be no way to create this in the create-table
command. (using 8.4 here)
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: exclusion constraint for ranges of IP
@ 2011-08-23 11:39 Herouth Maoz <herouth@unicell.co.il>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 11+ messages in thread
From: Herouth Maoz @ 2011-08-23 11:39 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
On 23/08/2011, at 13:31, Jasen Betts wrote:
> On 2011-08-23, Herouth Maoz <herouth@unicell.co.il> wrote:
>
>> EXCLUDE USING GIST ( customer_id WITH =, is_default WITH AND )
>
>
>> Basically, each customer can have several rows in this table, but only =
>> one per customer is allowed to have is_default =3D true. Is this exclude =
>> constraint correct?
>
> I don't really understand exclude, but instead of EXCLUDE... I would do
>
> CREATE UNIQUE INDEX "invoice_definitions-unique-default"
> ON invoice_definitions(customer_id) WHERE is_default;
>
> Which would create a smaller (and probably faster) BTREE index
> containing only the rows with is_default true.
This is an interesting concept. It's a different angle on the same condition.
>
> There seems to be no way to create this in the create-table
> command. (using 8.4 here)
Yes, it's curious that exclude constraints are the only ones which are allowed to be partial in a table definition.
Thank you.
Herouth
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-08-23 11:39 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-21 15:08 exclusion constraint for ranges of IP Herouth Maoz <herouth@unicell.co.il>
2011-08-21 22:19 ` Harald Fuchs <hari.fuchs@gmail.com>
2011-08-22 08:31 ` Herouth Maoz <herouth@unicell.co.il>
2011-08-22 09:27 ` Simone Sanfratello <simone.sanfra@gmail.com>
2011-08-22 13:07 ` Misa Simic <misa.simic@gmail.com>
2011-08-22 15:20 ` Harald Fuchs <hari.fuchs@gmail.com>
2011-08-22 11:37 ` Jasen Betts <jasen@xnet.co.nz>
2011-08-23 08:27 ` Herouth Maoz <herouth@unicell.co.il>
2011-08-23 10:15 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-08-23 10:31 ` Jasen Betts <jasen@xnet.co.nz>
2011-08-23 11:39 ` Herouth Maoz <herouth@unicell.co.il>
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