Обсуждение: exclusion constraint for ranges of IP

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
identifiedas 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
dothat with exclusion constraints? Or do I have to define a new type for this? 

Herouth

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,
itis 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
todo 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)))
);

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,
itis 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
todo 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
applicationwill have to send and what it will receive when querying a compound type. I use PHP/ZF. I have just now
posteda question on the pgsql-php list about this. I suspect I'll be getting a string which I'll have to parse, which
wouldmake the application more complicated to read and understand. 

Herouth

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




--
Simone

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,
itis 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
todo 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

Hi Herouth,

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,
itis 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
wayto 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).

My thanks to everyone who replied.

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

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