Thread: 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,<br />you can do the identification of customer by ip in many ways. IMHO, first of all, you have to put the allowed IPsinto your table. <br />The simpler way is to write all IPs allowed, of course. <br />The simpler way to do range checkis to have 2 columns in table, IP-range-starts and IP-range-ends, so the SQL could be easy using every data types.<br/> I prefer to use string data type and not compound ip4r type because can check using reg.exp. in SQL select, writingIPs range like 1.2.3.100-1.2.3.150 or just single 2.3.4.5. <br />When I did something like that, I wrote IP in thisform : 001002003100 and I use the two columns of range-starts and range-ends, so it became very easy (and I think veryperformant) doing check even with large table.<br /><br />The worse way is to remand check to PHP, because you have toload entire table during select and manually select target row there.<br /><br />I hope these suggests help<br /><br /><divclass="gmail_quote">2011/8/22 Herouth Maoz <span dir="ltr"><<a href="mailto:herouth@unicell.co.il">herouth@unicell.co.il</a>></span><br/><blockquote class="gmail_quote" style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><br /> On 22/08/2011, at 01:19, Harald Fuchs wrote:<br/><br /> > In article <<a href="mailto:CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il">CAF36091-203E-4C10-AA53-7D9087114D35@unicell.co.il</a>>,<br />> Herouth Maoz <<a href="mailto:herouth@unicell.co.il">herouth@unicell.co.il</a>> writes:<br /> ><br /> >>Hi,<br /> >> 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 fallswithin the range, it is identified as a particular customer.<br /> ><br /> >> Naturally, I'd like to have constraintson 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?<br /> ><br /> > This "new type" already exists: ip4r, whichcan be found in pgfoundry.<br /> > With it you can do<br /> ><br /> > CREATE TABLE mytbl (<br /> > iprangeip4r NOT NULL,<br /> > ...,<br /> > CONSTRAINT range_check CHECK ((NOT overlap(iprange)))<br /> > );<br/><br /><br /> Thank you.<br /><br /> I assume you can't use a CHECK constraint for between-rows constraints. Wouldn'tthis be<br /><br /> CONSTRAINT EXCLUDE ( iprange WITH && )<br /><br /> ?<br /><br /> 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 willhave to send and what it will receive when querying a compound type. I use PHP/ZF. I have just now posted a questionon the pgsql-php list about this. I suspect I'll be getting a string which I'll have to parse, which would make theapplication more complicated to read and understand.<br /><br /> Herouth<br /><font color="#888888">--<br /> Sent viapgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changesto your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /><br clear="all" /><br/>-- <br />Simone<br />
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,
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>
Thank you.
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)))
> );
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
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.
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
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.
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