Обсуждение: cidr & inet types
I'm trying to create a database of IP networks. I'd like to have the ability
to enforce network boundaries within the database, instead of doing it
through my application. Or lack thereof. I see that Postgres has built in
data capabilities for both inet and cidr, however, they don't act the way
I'm expecting them to.
If I create this table:
nettest=> create table cidr (net cidr NOT NULL, UNIQUE(net));
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'cidr_net_key' for table 'cidr' CREATE TABLE
which is fairly simple, but just for illustration purposes, I would expect
that the values in the field 'net' would be required to be unique.
However, the field type doesn't appear to honor the bitmask. If I insert
networks of the same length, it works as I would expect it to:
nettest=> insert into cidr values('10.1.0.0/22');
INSERT 58180 1
nettest=> insert into cidr values('10.1.1.0/22');
ERROR: invalid CIDR value '10.1.1.0/22': has bits set to right of mask
nettest=> insert into cidr values('10.1.4.0/22');
INSERT 58181 1
Which, is the way it should be, since a 22 bit netmask allows for 4 (24 bit) networks.
However, when I add a 'larger' netmask that includes those networks, it
allows that value to be entered:
nettest=> insert into cidr values('10.1.0.0/20');
INSERT 58182 1
And, the values are actually all there:
nettest=> select * from cidr;
net
-------------
10.1.0.0/22
10.1.4.0/22
10.1.0.0/20
(3 rows)
While it seems intelligent enough to know that 10.1.1.0/22 would overlap, it
doesn't know that 10.1.0.0/20 would envelop other networks that already exist
in it's dataset. Am I doing something incorrectly here?
While it's possible it's just my viewpoint, it seems that this is kind of weird
behavior. If the datatype has any notion of networks and that data being
'unique' shouldn't it also know about netmasks? I guess it's a different kind
of unique than people are used to seeing. But, you can't have two networks,
one 10.1.0.0/22 and 10.1.0.0/20 on the same wire, or connected to a router, or whatever.
Am I missing something? Doing something incorrectly?
Thanks for any guidance,
Tim
Tim Conrad <conrad@external.timconrad.org> writes:
> While it's possible it's just my viewpoint, it seems that this is kind of weird
> behavior. If the datatype has any notion of networks and that data being
> 'unique' shouldn't it also know about netmasks?
A UNIQUE constraint means there are no two entries in the column that
are *equal*. I understand what you're after, but I don't really see why
you'd expect a UNIQUE constraint to do it for you.
regards, tom lane
At 08:25 PM 7/15/2003 -0400, Tim Conrad wrote: >And, the values are actually all there: >nettest=> select * from cidr; > net >------------- > 10.1.0.0/22 > 10.1.4.0/22 > 10.1.0.0/20 >(3 rows) > >While it seems intelligent enough to know that 10.1.1.0/22 would overlap, it >doesn't know that 10.1.0.0/20 would envelop other networks that already exist >in it's dataset. Am I doing something incorrectly here? And what if 10.1.0.0/20 covers the range of other networks? Even if Big Org network totally covers the Dept A and Dept B networks, why shouldn't you be able to insert all 3? The Unique constraint should prevent people from successfully inserting Big Org Network more than once, but that's about it. You're assuming a much narrower range of usage/application for cidr stuff. >While it's possible it's just my viewpoint, it seems that this is kind of >weird >behavior. If the datatype has any notion of networks and that data being >'unique' shouldn't it also know about netmasks? I guess it's a different kind >of unique than people are used to seeing. But, you can't have two networks, >one 10.1.0.0/22 and 10.1.0.0/20 on the same wire, or connected to a >router, or whatever. You can have two networks 10.1.0.0/22 and 10.1.0.0/20 on the same router and on the same wire too. A router could have two different routes: packets to 10.1.0.0/22 will be sent via 10.1.1.1 and the rest matching 10.1.0.0/20 via 10.1.1.2. The more specific route takes precedence. But that does not exclude the existence of a more general network. You might be able to create a functional index that does what you want. I don't know how but I think it could be possible. Regards, Link.
--On Sunday, July 20, 2003 15:33:14 +0800 Lincoln Yeoh <lyeoh@pop.jaring.my> wrote: > At 08:25 PM 7/15/2003 -0400, Tim Conrad wrote: > > >> And, the values are actually all there: >> nettest=> select * from cidr; >> net >> ------------- >> 10.1.0.0/22 >> 10.1.4.0/22 >> 10.1.0.0/20 >> (3 rows) >> >> While it seems intelligent enough to know that 10.1.1.0/22 would >> overlap, it doesn't know that 10.1.0.0/20 would envelop other networks >> that already exist in it's dataset. Am I doing something incorrectly >> here? > > And what if 10.1.0.0/20 covers the range of other networks? Even if Big > Org network totally covers the Dept A and Dept B networks, why shouldn't > you be able to insert all 3? The Unique constraint should prevent people > from successfully inserting Big Org Network more than once, but that's > about it. > > You're assuming a much narrower range of usage/application for cidr stuff. > > >> While it's possible it's just my viewpoint, it seems that this is kind >> of weird >> behavior. If the datatype has any notion of networks and that data being >> 'unique' shouldn't it also know about netmasks? I guess it's a different >> kind of unique than people are used to seeing. But, you can't have two >> networks, one 10.1.0.0/22 and 10.1.0.0/20 on the same wire, or connected >> to a router, or whatever. > > You can have two networks 10.1.0.0/22 and 10.1.0.0/20 on the same router > and on the same wire too. > > A router could have two different routes: packets to 10.1.0.0/22 will be > sent via 10.1.1.1 and the rest matching 10.1.0.0/20 via 10.1.1.2. > > The more specific route takes precedence. But that does not exclude the > existence of a more general network. > > You might be able to create a functional index that does what you want. I > don't know how but I think it could be possible. > > Regards, > Link. I work for an ISP, and wrote our IP Management database using the CIDR type, and USE the ability to have the overlapping networks. If I couldn't have 10.0.0.0/8, 10.1.0.0/16, etc in my database WITH A UNIQUE constraint, my application would BREAK. My application automatically splits netblocks to make smaller ones, and a status field says it is "subdivided". Just another point of view. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749