Thread

1. Some problem with inet type on PostgreSQL-7.0

   Vadim I. Passynkov <pvi@axxent.ca> — 2000-06-14T15:02:38Z

   Hi All,
   
   I have some problem with inet type on PostgreSQL-7.0 (FreeBSD
   3.4-STABLE)
   
                    Table "ipaddresses"
     Attribute   |  Type   |          Modifier          
   --------------+---------+----------------------------
    sysname      | text    | not null
    index        | integer | not null
    ip_addr      | inet    | not null
   
   Indices: ipaddresses_ip_addr,
            ipaddresses_pkey
   
   (sysname, ip_addr) - PRIMARY KEY
   
         View "ipaddresses_view"
     Attribute   |  Type   | Modifier 
   --------------+---------+----------
    sysname      | text    | 
    index        | integer | 
    ip_addr      | inet    | 
    ip_netmask   | inet    | 
   
   View definition: SELECT ipaddresses.sysname, ipaddresses."index",
   ipv4_host(ipaddresses.ip_addr) AS ip_addr,
   ipv4_netmask(ipaddresses.ip_addr) AS ip_netmask FROM ipaddresses;
   
   
   ipv4_host and ipv4_netmask like original host and netmask but return
   inet type ( need for ORDER )
   
   CREATE FUNCTION ipv4_host(inet) RETURNS inet AS '
   BEGIN
   RETURN host($1);
   END;
   ' LANGUAGE 'plpgsql';
   
   CREATE FUNCTION ipv4_netmask(inet) RETURNS inet AS '
   BEGIN
   RETURN netmask($1);
   END;
   ' LANGUAGE 'plpgsql';
   
   
   **************************** Problem ************************
   
   select * from ipaddresses where sysname = 'switch01.tor';
      sysname    | index |     ip_addr       
   --------------+-------+------------------
    switch01.tor |     1 | 127.0/8  
    switch01.tor |     2 | 127.0/8                     
    switch01.tor |     3 | 209.250.155.8/27           
   (2 rows)
   
   but (sysname, ip_addr) - PRIMARY KEY
   
   127.0/8 - it's not correct output ( real 127.0.0.2/8 and 127.0.0.3/8) 
   
   select * from ipaddresses_view where sysname = 'switch01.tor';
      sysname    | index |    ip_addr    |   ip_netmask    
   --------------+-------+---------------+-----------------
    switch01.tor |     1 | 127.0.0.2     | 255.0.0.0
    switch01.tor |     2 | 127.0.0.3     | 255.0.0.0           
    switch01.tor |     3 | 209.250.155.8 | 255.255.255.224              
   (2 rows)
   
   
   127.0.0.2 | 255.0.0.0  and 127.0.0.3 | 255.0.0.0 - it's correct output
   
   And of course after pg_dump and restore correct value 127.0.0.2/8 and
   127.0.0.3/8 will lose
   and will have problem with PRIMARY KEY - (sysname, ip_addr).
   
   -- 
   
    Passynkov Vadim, Axxent Inc.
   
   

2. Re: Some problem with inet type on PostgreSQL-7.0

   Peter Eisentraut <peter_e@gmx.net> — 2000-06-17T13:02:29Z

   I can confirm your problem but there's no known fix. The truth is that the
   inet/cidr types have quite a number of bogosities but no one understands
   them well enough to undertake fixing them.
   
   
   Vadim Passynkov writes:
   
   > Hi All,
   > 
   > I have some problem with inet type on PostgreSQL-7.0 (FreeBSD
   > 3.4-STABLE)
   > 
   >                  Table "ipaddresses"
   >   Attribute   |  Type   |          Modifier          
   > --------------+---------+----------------------------
   >  sysname      | text    | not null
   >  index        | integer | not null
   >  ip_addr      | inet    | not null
   > 
   > Indices: ipaddresses_ip_addr,
   >          ipaddresses_pkey
   > 
   > (sysname, ip_addr) - PRIMARY KEY
   > 
   >       View "ipaddresses_view"
   >   Attribute   |  Type   | Modifier 
   > --------------+---------+----------
   >  sysname      | text    | 
   >  index        | integer | 
   >  ip_addr      | inet    | 
   >  ip_netmask   | inet    | 
   > 
   > View definition: SELECT ipaddresses.sysname, ipaddresses."index",
   > ipv4_host(ipaddresses.ip_addr) AS ip_addr,
   > ipv4_netmask(ipaddresses.ip_addr) AS ip_netmask FROM ipaddresses;
   > 
   > 
   > ipv4_host and ipv4_netmask like original host and netmask but return
   > inet type ( need for ORDER )
   > 
   > CREATE FUNCTION ipv4_host(inet) RETURNS inet AS '
   > BEGIN
   > RETURN host($1);
   > END;
   > ' LANGUAGE 'plpgsql';
   > 
   > CREATE FUNCTION ipv4_netmask(inet) RETURNS inet AS '
   > BEGIN
   > RETURN netmask($1);
   > END;
   > ' LANGUAGE 'plpgsql';
   > 
   > 
   > **************************** Problem ************************
   > 
   > select * from ipaddresses where sysname = 'switch01.tor';
   >    sysname    | index |     ip_addr       
   > --------------+-------+------------------
   >  switch01.tor |     1 | 127.0/8  
   >  switch01.tor |     2 | 127.0/8                     
   >  switch01.tor |     3 | 209.250.155.8/27           
   > (2 rows)
   > 
   > but (sysname, ip_addr) - PRIMARY KEY
   > 
   > 127.0/8 - it's not correct output ( real 127.0.0.2/8 and 127.0.0.3/8) 
   > 
   > select * from ipaddresses_view where sysname = 'switch01.tor';
   >    sysname    | index |    ip_addr    |   ip_netmask    
   > --------------+-------+---------------+-----------------
   >  switch01.tor |     1 | 127.0.0.2     | 255.0.0.0
   >  switch01.tor |     2 | 127.0.0.3     | 255.0.0.0           
   >  switch01.tor |     3 | 209.250.155.8 | 255.255.255.224              
   > (2 rows)
   > 
   > 
   > 127.0.0.2 | 255.0.0.0  and 127.0.0.3 | 255.0.0.0 - it's correct output
   > 
   > And of course after pg_dump and restore correct value 127.0.0.2/8 and
   > 127.0.0.3/8 will lose
   > and will have problem with PRIMARY KEY - (sysname, ip_addr).
   > 
   > 
   
   -- 
   Peter Eisentraut                  Sernanders väg 10:115
   peter_e@gmx.net                   75262 Uppsala
   http://yi.org/peter-e/            Sweden