Linkedin Instagram

Sintaxe no PostgreSQL: endereço de rede

Bom, chega um ponto em que estou falando mais sobre tipos de dados do que sobre sintaxe propriamente dito. O PostgreSQL é um SGDB que nasceu com uma proposta ousada, não apenas substituir o Ingres, mas ter uma arquitetura orientada a objetos e ser muito extensível. Um dos impactos disso é a capacidade de se criar vários tipos de dados diferentes. É realmente simples de fazer isso. Quando vimos o ENUM, vimos que você tem o CREATE TYPE, mas é só a ponta do iceberg: você tem o CREATE OPERATOR, CREATE AGGREGATE, CREATE CAST, CREATE ACCESS METHOD, CREATE COLLATION e por aí vai. Você nem precisa conhecer a linguagem C para fazer muita coisa interessante.

Uma das coisas interessantes que o PostgreSQL faz é criar tipos de dados novos. É quase um vício. Quase todo ano aparece um tipo de dados novo. O capítulo “Data Types” da documentação está dividido em 20 sessões na versão 10 do PostgreSQL. Na versão 7.1 eram apenas 8. Cada sessão apresenta um conjunto de tipos de dados específico. É claro que isso começa a parecer jabuticaba (uma daquelas coisas que só tem no Brasil…). Na verdade é mesmo. São extensões ao padrão SQL. Ninguém mais tem. Mas se considerarmos que a linguagem SQL é fortemente tipada, acho que os demais SGDBs deveriam ser um pouco mais desta forma também. Todos os SGDBs correram para criar uma implementação de XML e JSON nos seus bancos de dados. Mas existem muitas outras situações em que criar um tipo de dados específico para uma situação ajuda muito.

Vamos ver o caso específico de redes aqui. Este é um caso emblemático, pois é bem antigo no PostgreSQL e exemplifica bem como um tipo que poderia ser apenas um VARCHAR, pode ser muito mais que isso. O Postgres possui atualmente 4 tipos que armazenam endereços de rede:

  • inet: Armazena endereços IPv4 ou IPv6 e opcionalmente a subrede ou máscara (ok, é mais comum ouvir em inglês netmask).
  • cidr: Armazena a rede ou netmask da rede;
  • macaddr: Armazena  endereços MAC ou MAC addresses;
  • macaddr8: Semelhandte ao macaddr, mas utilizando o formato EUI-64 com 6 ou 8  bytes.

inet

A sintaxe para fazer a coerção para o tipo correto é a mesma que demonstramos no primeiro episódio da nossa saga. Vejamos o uso do inet:

SQL
teste=# SELECT inet '192.168.0.1/24' AS ip;
       ip
----------------
 192.168.0.1/24
(1 row)

teste=# SELECT  '192.168.0.1/24'::inet AS ip;
       ip
----------------
 192.168.0.1/24
(1 row)

teste=# SELECT  CAST('192.168.0.1/24' AS inet) AS ip;
       ip
----------------
 192.168.0.1/24
(1 row)

Cada um dos tipos possui uma sintaxe específica que é aceita e convertida internamente antes de armazenar. A primeira vantagem óbvia é a validação. Se você tentar inserir um valor inválido, o Postgres vai acusar um erro imediatamente, o que não aconteceria se você utilizar um simples VARCHAR:

SQL
teste=# SELECT inet '192.168.0.0.0/24' AS ip;
ERROR:  invalid input syntax for type inet: "192.168.0.0.0/24"
LINE 1: SELECT inet '192.168.0.0.0/24' AS ip;
                    ^
teste=# SELECT inet '192.168.0.257/24' AS ip;
ERROR:  invalid input syntax for type inet: "192.168.0.257/24"
LINE 1: SELECT inet '192.168.0.257/24' AS ip;

Claro que você pode criar uma função que faz esse tipo de validação na sua aplicação. Mas aqui você já tem isso pronto. Veja que o formato inet admite vários formatos diferentes de entrada, considerando sempre a máscara também. Vejamos alguns exemplos:

SQL
teste=# SELECT inet '192.168.0.1/32' AS ip;
     ip
-------------
 192.168.0.1
(1 row)

teste=# SELECT inet '192.168.0.1/24' AS ip;
       ip
----------------
 192.168.0.1/24
(1 row)

teste=# SELECT inet '192.168.0/32' AS ip;
ERROR:  invalid input syntax for type inet: "192.168.0/32"
LINE 1: SELECT inet '192.168.0/32' AS ip;
                    ^
teste=# SELECT inet '192.168.0/24' AS ip;
       ip
----------------
 192.168.0.0/24
(1 row)

Veja que um com máscara 32 o IP é exibido sem uma máscara uma vez que /32 é a mesma coisa não utilizar uma subrede. Já quando omitimos um dos últimos dígitos e utilizamos uma Classe C por exemplo, o PostgreSQL subentende que o último dígito é zero.

Podemos trabalhar com IPv6 também:

SQL
teste=# SELECT inet '2001:0DB8:0000:0000:130F:0000:0000:140B' AS "IPv6";
          IPv6
-------------------------
 2001:db8::130f:0:0:140b
(1 row)

teste=# SELECT inet '2001:DB8:0:0:130F::140B' AS "IPv6";
          IPv6
-------------------------
 2001:db8::130f:0:0:140b
(1 row)

teste=# SELECT inet '2001:DB8::130F:0:0:140B' AS "IPv6";
          IPv6
-------------------------
 2001:db8::130f:0:0:140b
(1 row)

cidr

Aqui temos algo semelhante, mas pensando apenas na máscara:

SQL
teste=# SELECT cidr '192/16' AS mask;
 mask
--------------
 192.0.0.0/16
(1 row)
teste=# SELECT cidr '192.168/16' AS mask;
      mask
----------------
 192.168.0.0/16
(1 row)

teste=# SELECT cidr '192.168.0/16' AS mask;
      mask
----------------
 192.168.0.0/16
(1 row)

teste=# SELECT cidr '192.168.0.0/16' AS mask;
      mask
----------------
 192.168.0.0/16
(1 row)

teste=# SELECT cidr '192.168.0.1/16' AS mask;
ERROR:  invalid cidr value: "192.168.0.1/16"
LINE 1: SELECT cidr '192.168.0.1/16' AS mask;
                    ^
DETAIL:  Value has bits set to right of mask.

Se você omitir o tamanho da máscara, o PostgreSQL vai deduzir o tamanho pela faixa de IPs que você fornecer:

SQL
teste=# SELECT cidr '10' AS mask;
    mask
------------
 10.0.0.0/8
(1 row)

teste=# SELECT cidr '128' AS mask;
     mask
--------------
 128.0.0.0/16
(1 row)

teste=# SELECT cidr '192' AS mask;
     mask
--------------
 192.0.0.0/24
(1 row)

É claro que a quantidade de dígitos que você fornecer também afeta a interpretação:

SQL
teste=# SELECT cidr '10' AS mask;
    mask
------------
 10.0.0.0/8
(1 row)

teste=# SELECT cidr '10.1' AS mask;
    mask
-------------
 10.1.0.0/16
(1 row)

teste=# SELECT cidr '10.1.2' AS mask;
    mask
-------------
 10.1.2.0/24
(1 row)

teste=# SELECT cidr '10.1.2.3' AS mask;
    mask
-------------
 10.1.2.3/32
(1 row)

macaddr

A sintaxe para o macaddr admite o uso dos separadores ‘.’, ‘:’, ‘-‘ ou nenhum separador, dependendo da forma como você agrupar ou não os dígitos:

SQL
teste=# SELECT macaddr '01:02:03:04:05:06' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '01-02-03-04-05-06' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '01.02.03.04.05.06' AS mac;
ERROR:  invalid input syntax for type macaddr: "01.02.03.04.05.06"
LINE 1: SELECT macaddr '01.02.03.04.05.06' AS mac;
                       ^
teste=# SELECT macaddr '0102.0304.0506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '0102-0304-0506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '0102:0304:0506' AS mac;
ERROR:  invalid input syntax for type macaddr: "0102:0304:0506"
LINE 1: SELECT macaddr '0102:0304:0506' AS mac;
                       ^
teste=# SELECT macaddr '010203:040506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '010203-040506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

teste=# SELECT macaddr '010203.040506' AS mac;
ERROR:  invalid input syntax for type macaddr: "010203.040506"
LINE 1: SELECT macaddr '010203.040506' AS mac;
                       ^
teste=# SELECT macaddr '010203040506' AS mac;
        mac
-------------------
 01:02:03:04:05:06
(1 row)

Apesar de aceitar vários formatos, o padrão IEE especifica o uso do segundo exemplo como correto.

macaddr8

Da mesma forma, o MAC com 8 bytes ao invés de 6 pode ser utilizado em diferentes formatos:

SQL
teste=# SELECT macaddr8 '01:02:03:04:05:06' AS mac;
           mac
-------------------------
 01:02:03:ff:fe:04:05:06
(1 row)

teste=# SELECT macaddr8 '01:02:03:04:05:06:07:08' AS mac;
           mac
-------------------------
 01:02:03:04:05:06:07:08
(1 row)

teste=# SELECT macaddr8 '01-02-03-04-05-06-07-08' AS mac;
           mac
-------------------------
 01:02:03:04:05:06:07:08
(1 row)

teste=# SELECT macaddr8 '0102.0304.0506.0708' AS mac;
           mac
-------------------------
 01:02:03:04:05:06:07:08
(1 row)

teste=# SELECT macaddr8 '0102030405060708' AS mac;
           mac
-------------------------
 01:02:03:04:05:06:07:08
(1 row)

Note que se você inserir um MAC de 6 bytes num macaddr8 ele vai preencher 2 bytes com ff:fe no lugar dos bytes faltantes.

Armazenamento

É claro que os tipos de rede são mais eficientes em termos de armazenamento que utilizar simplesmente um VARCHAR. Cada dígito aqui é um dígito hexadecimal de 0 a F e não um byte completo de 00 a FF. Portanto, há uma economia de 50% do espaço ocupado. Em termos de espaço temos:

  • inet: 7 bytes com IPv4 e 19 bytes com IPv6
  • cdir: 7 bytes com IPv4 e 19 bytes com IPv6
  • macaddr: 6 bytes
  • macaddr8: 8 bytes

Operadores

A brincadeira começa a ficar interessante aqui. Se você olhar o capítulo “Funções e Operadores” na documentação, verá uma sessão específica só para tipos de rede. Antes de mais nada, vou criar uma tabela com alguns registros aleatórios para poder brincar aqui:

SQL
CREATE TABLE net (ip inet);

INSERT INTO net SELECT inet (
    '10.' || 
    round(random()*255) || '.' ||
    round(random()*255) || '.' ||
    round(random()*255) || '/' || 
    '8')
FROM generate_series(1,100);

INSERT INTO net SELECT inet (
    '128.191.' || 
    round(random()*255) || '.' ||
    round(random()*255) || '/' || 
    '16')
FROM generate_series(1,100);

INSERT INTO net SELECT inet (
    '192.168.1.' || 
    round(random()*255) || '/' || 
    '24')
FROM generate_series(1,100);

INSERT INTO net SELECT 
    inet (round(random()*255) || '.' || 
          round(random()*255) || '.' ||
          round(random()*255) || '.' ||
          round(random()*255) || '/' || 
          round(random()*4)*8) AS ip 
FROM generate_series(1,100);

Alguns operadores não são novidade como <, <=, =, >=, > e <>. Como esperado temos:






SQL

teste=# SELECT ip FROM net WHERE ip > inet '192.168.0.0';
        ip
-------------------
 221.208.133.56/16
 248.244.222.86/16
 254.187.237.159/8
(3 rows)

teste=# SELECT ip FROM net WHERE ip < inet '192.168.0.0';
        ip
------------------
 110.46.113.5/24
 173.21.188.12
 13.117.195.196/8
 143.51.137.1/8
 47.222.9.180/0
 164.98.44.97/16
 38.91.99.5/24
(7 rows)

teste=# SELECT ip FROM net WHERE ip =  inet '164.98.44.97';
 ip
----
(0 rows)

teste=# SELECT ip FROM net WHERE ip =  inet '164.98.44.97/16';
       ip
-----------------
 164.98.44.97/16
(1 row)





Mas o PostgreSQL traz outros operadores mais interessantes:





    

  • << está contido
    • 




  • <<= está contido ou igual
    • 




  • >> contém
    • 




  • >> contém ou igual
    • 




  • && contém ou está contido
    • 




  • NOT bit a bit
    • 




  • AND bit a bit
    • 




  • OR bit a bit
    • 




  • +
    • 




  • –
    • 






Vejamos como ficam alguns exemplos:





SQL
teste=# SELECT ip FROM net WHERE ip <<  inet '192.168.1.0/16';
        ip
------------------
 192.168.1.32/24
 192.168.1.115/24
 192.168.1.168/24

teste=# SELECT ip FROM net WHERE ip >>  inet '192.168.1.0/32';
        ip
------------------
 192.168.1.32/24
 192.168.1.115/24
 192.168.1.168/24
 192.168.1.233/24
 192.168.1.213/24

teste=# SELECT
teste-#     ip,
teste-#     ~ip AS "NOT",
teste-#     ip & inet'10.0.0.1' AS "AND",
teste-#     ip | inet'10.0.0.1' AS "OR"
teste-# FROM net LIMIT 5;
       ip        |        NOT        |   AND    |      OR
-----------------+-------------------+----------+---------------
 10.38.170.142/8 | 245.217.85.113/8  | 10.0.0.0 | 10.38.170.143
 10.134.7.73/8   | 245.121.248.182/8 | 10.0.0.1 | 10.134.7.73
 10.70.82.112/8  | 245.185.173.143/8 | 10.0.0.0 | 10.70.82.113
 10.224.98.126/8 | 245.31.157.129/8  | 10.0.0.0 | 10.224.98.127
 10.75.233.180/8 | 245.180.22.75/8   | 10.0.0.0 | 10.75.233.181
(5 rows)

teste=# SELECT
    ip,
    ip + 64 AS soma,
    ip - 64 AS subtracao,
    ip - inet'10.0.0.1' subtracao_ip
FROM net LIMIT 5;
        ip        |       soma       |    subtracao     | subtracao_ip
------------------+------------------+------------------+--------------
 10.38.170.142/8  | 10.38.170.206/8  | 10.38.170.78/8   |      2534029
 10.134.7.73/8    | 10.134.7.137/8   | 10.134.7.9/8     |      8783688
 10.199.2.34/8    | 10.199.2.98/8    | 10.199.1.226/8   |     13042209
 10.78.195.52/8   | 10.78.195.116/8  | 10.78.194.244/8  |      5161779
 10.153.148.224/8 | 10.153.149.32/8  | 10.153.148.160/8 |     10065119
(5 rows)





Agora a cereja do bolo. Primeiro vamos aumentar o volume de dados na tabela:





SQL
INSERT INTO net SELECT inet (
    round(random()*255) || '.' ||
    round(random()*255) || '.' ||
    round(random()*255) || '.' ||
    round(random()*255) || '/' || 
    '8')
FROM generate_series(1,100000);





Agora vejamos o plano de execução de uma de nossas consultas:





SQL
teste=# EXPLAIN ANALYZE SELECT ip FROM net WHERE ip <<  inet '192.168.1.0/32';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on net  (cost=0.00..1697.75 rows=1 width=7) (actual time=18.627..18.627 rows=0 loops=1)
   Filter: (ip << '192.168.1.0'::inet)
   Rows Removed by Filter: 100300
 Planning time: 0.082 ms
 Execution time: 18.660 ms





Você não precisa entender muito sobre planos de execução. Apenas observe que fizemos um “Seq Scan” na tabela “net”, ou seja, varremos todas as linhas da tabela em busca de quais atendem aos requisitos da nossa cláusula WHERE. E claro: levou 18,66ms para rodar o comando.





Agora vamos criar um índice e rodar o mesmo plano de execução:





SQL
teste=# CREATE INDEX ON net (ip);
CREATE INDEX
teste=# EXPLAIN ANALYZE SELECT ip FROM net WHERE ip <<  inet '192.168.1.0/32';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Only Scan using net_ip_idx on net  (cost=0.29..2.52 rows=1 width=7) (actual time=0.027..0.027 rows=0 loops=1)
   Index Cond: ((ip > '192.168.1.0'::inet) AND (ip <= '192.168.1.0'::inet))
   Filter: (ip << '192.168.1.0'::inet)
   Heap Fetches: 0
 Planning time: 0.336 ms
 Execution time: 0.063 ms
(6 rows)





Agora o plano de execução passou a utilizar o índice “net_ip_idx” que criamos. E o resultado? Um tempo de execução de 0,062ms. Este é o grande pulo do gato com os operadores. Suas operações já são indexáveis por natureza. E isso você nunca vai conseguir imitar utilizando um VARCHAR.





Funções





Além dos operadores, temos também algumas funções nativas para trabalhar com tipos de dados para redes. Estas não são indexáveis por natureza, mas você pode criar índices incluindo estas funções se for realmente importante. Você pode e deve ver a lista das funções existentes na documentação oficial. Sempre. Não pretendemos aqui substituir a documentação, apenas dar uma visão mais clara sobre alguns pontos específicos. Então vá lá e faça uma visita.





Conclusões





Tipos de dados específicos como os tipos de rede são muito poderosos, eles nos ajudam de diversas formas:





    

  • Armazenamento otimizado, ocupando muito menos espaço;
    • 




  • Validação nativa bem feita;
    • 




  • Existe um arsenal operadores e funções prontas para utilizarem estes tipos de dados;
    • 




  • Operadores nativos utilizam índices automaticamente sem que você precise fazer nada além de criar um índice comum no campo.
    • 



		

				

				

		

		

				

				

			
Compartilhe
		

				

				

				

					
    
							
  • 
											
													
										
									
    • 
								
  • 
											
													
										
									
    • 
								
  • 
											
													
										
									
    • 
						

				

				

				

				

				

			
Você pode gostar
		

				

				

				

					

				

				
			

		
				

				

			
				pg_hba.conf			
		

				

			
Introdução O arquivo pg_hba.conf (PostgreSQL Host-Based Authentication) é uma peça fundamental na configuração de segurança de qualquer instância PostgreSQL. Ele define as regras de autenticação

		

				

				

				

				
			

		
				

				

			
				Tuning de SO (no Linux)			
		

				

			
Introdução Tuning refere-se ao processo de ajustar e otimizar o desempenho de um sistema, software ou aplicação. A otimização do sistema operacional é uma etapa

		

				

				

				
				

				
			

		
				

				

			
				Tipos de cargas dos bancos de dados			
		

				

			
Introdução Cargas de dados referem-se aos diferentes tipos de operações e transações que um banco de dados deve processar. Essas cargas variam conforme o tipo

		

				

				

				

		
				

				

				

					

				

		

					

				

				

					

			

							

							

					

				

						

							

		

				

				

					

				

				

				

			

					

		

				

				

														
															
													

				

				

		

				

				

			
Fale conosco
		

				

				

				

					
				

				

				

		

				

				

			
receba novidades
		

				

				

				

					

			
			
			

							
			
			

								

												
														
											

								

					
				

			

		

				

				

				

					

				

				

		
plugins premium WordPress