1 PostgreSQL Capacitación Nivel 1 Día 4

2 Agenda Funciones de Date/time. Recomendaciones para el manejo del servidor. Configuración Parámetros avanzados GEQO Tipos de identificación de objetos y columnas de sistema. Tipos de datos propios. Introducción a los lenguajes procedurales. –Tipos. –Variedad. –Finalidad. Expresiones regulares.

3 Fechas

4 Extracción y Casteo

5 Más sobre fechas

6

7

8 Configuración

9 SHARED_BUFFERS – El que más afecta el rendimiento. – Comenzar a partir del 10% de memoria. – Se peude setear en MB, KB (24MB, 24576KB). – Tener en cuenta el /proc/sys/kernel/shmax (Linux) –# sysctl -w kernel.shmmax=112009216 ó modificar a mano shmmax y shmall WORK_MEM – Memoria utilizada para trabajo del servidor. – Aumentar en caso de tener muchas consultas complejas. – Para empezar el 4% de la memoria total.

10 Configuración MAX_CONNECTIONS LOGGING (Varios parametros) effective_cache_size (una de las más importantes debe tener un buen espacio en memoria para serv. Dedicados) wal_sync_method wal_buffers (cantidad, método y tamaño de los buffers). Autovacuum

11 Configuración F[ree] S[pace] M[ap] max_fsm_pages, max_fsm_pages – Gracias al Visibility Map, en 8.4 ya no es necesario setear estas variables. – En versiones menores a 8.3, se debe setear en un valor determinado a la base más grande del cluster. » El Vacuum nos dirá a través de un HINT que debemos aumentar por encima de un determinado número.

12 Parámtetros avanzados

13 Opciones avanzadas default_statistics_target (valores por defecto de la recolección de estadísticas, con expeción de aquellas establecidas con ALTER TABLE SET STATISTICS) from_collapse_limit, join_collapse_limit (establece si el optimizador debe fusionar subconsultas en las consultas si los items del FROM o el JOIN -según corresponda- están por debajo del valor dado) constraint_exclusion (establece si el optimizador debe utilizar las restricciones para la optimización. effective_cache_size (tamaño efectivo del caché de disco disponible para rastreo de índice. Aumentarlo, aumenta la probabilidad de rastreo por índice).

14 Opc. durante la compilación block_size (por defecto 8192) » Indica el tamaño del bloque de datos. integer_datetime » Soporte o no de 64 bits. lc_collate, lc_ctype » Forma de hacer la ordenación y clasificaciones de texto. server_encoding » Encoding del servidor

15 Parametros de almacenamiento Fillfactor(integer) – Porcentaje entre 10 y 100 (empaquetamiento completo). – A menor valor, las operaciones de inserción empaquetarán la tabla solo en el porcentaje especificado. – El espacio remanente será reservado para los UPDATE de las filas de esa página. Esto le da la chance a los UPDATE de almacenar la copia de la fila en el mismo espacio que el original, lo que es mejor que almacenarla en otro lugar. – Para tablas con poco o casi nada de UPDATES, 100% es el mejor valor. Casos contrarios, disminuirlo.

16 Parametros de almacenamiento (2) autovacuum_enabled(boolean) (deshabilita o habilita el autovacuum en una tabla particular) autovacuum_vacuum_threshold(int) (numero de inserciones o actualizaciones para disparar el autovacuum)

17 TOAST (The Oversized Attributte Storage Technique) Se reconocen 4 tipos de estrategias para el almacenamiento de columnas TOASTables PLAIN previene la compresión o el almacenamiento fuera de linea. Deshabilita el uso de encabezados de un solo byte para los tipos varlena. Es la única posible estrategia para los tipos no toast-ables (Tipos Fijos : int, char, real). EXTENDED permite compresión y almacenamiento fuera de linea. Es el por defecto de la mayoría de los tipos de datos toastables. La compresión ocurrirá primero, si la fila sigue siendo grande: out-of-line. EXTERNAL permite el alamcenamiento fuera de linea pero no la compresión. Hará las operaciones con caracteres y expresiones más rápidas en textos y byteas largos ( pero consumiendo mayor espacio) debido a que esas operaciones están optimizadas para avanzar sobre solamente las partes requeridas del valor de fuera de linea cuando no están comprimidas. MAIN permite la compresión per no el almacenamiento fuera de linea (Actualmente, el almacenamiento fuera de linea será mejorado para este tipod e columnas, pero solo como último recurso, cuando no hay otro camino para hacer que la fila sea más pequeña). Tipos Variables: Varchar Text, Bytea, etc.

18 GEQO Genetic Query Optimizer Elimina aquellos planes de ejecución que posiblemente sean caros. geqo_thresold (items del FROM a partir del cual se debe utilizar geqo). Se podría recomendar bajar. Geqo (activa o desactiva)

19 Gestión de Recursos del Kernel max_locks_per_transaction » 220 bytes c/u max_connections » 400 bytes ~c/u + 220*max_locks_per_transactions max_prepared_transactions » 600 bytes ~ c/u +220*max_locks_per_transactions shared_buffers » Cada buffer es de 8kb (se recomiendan valores superiores a 5000) wal_buffers » 8kb c/u (puede recomendarse un valor superior a los 5000)

20 Getión de Recursos del Kernel (2) max_fsm_relations » 70 bytes c/u max_fsm_pages » c/u 6 bytes (max_fsm_relations*16)

21 Parametros del kernel

22 Modificación Parametros del Kernel Archivo sysctl.conf en el directorio /etc. Delante de cada parámetro agregar kernel.

23 Recomendaciones

24 Cambiando la configuración ¿Como resetear – recargar? – /etc/init.d/postgres reload – /etc/init.d/postgres restart – Service postgres reload | restart En el postgresql.conf, las variables que requieren restartear el motor, tienen un comentario que indica eso. 'Reload' utiliza mucho menos tiempo.

25 Identificadores de objetos Y columnas de sistema

26 OID Se usa internamente para tablas de sistema como PK's. No son agregadas a las tablas de usuarios a menos que se la cree con la opción WITH OIDS o se habilite la variable default_with_oids. Hay diversos tipos de alias para el OID: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, and regdictionary.

27 Tableoid El OID de la tabla es el contenido de esta fila. Es particularmente útil para consultas que selecccionan desde herencia, permitiendo observar deque tabla individualmente proviene un registro. Puede ser 'unido' contra el OID de la columna OID de pg_class, obteniendo el nombre de la tabla.

28 Ejemplo Tableoid

29 xmin Es la identidad de la transacción de inserción de la versión de la fila. Una 'versión de una fila' es un estado individual de una fila. – Cada actualización de una fila genera una nueva versión de la misma para la misma fila lógica.

30 Cmin -cmax CMIN: El identificador de comando (comenzando de 0) de la transacción de inserción. CMAX:Idem de la transacción de borrado o en 0.

31 xmax Es la identidad de la transacción de borrado o en 0 para una fila no borrada. Es posible para esta columna ser distinto de 0 en una versión de fila visible. Usualmente indica que la transacción de borrado no ha sido comprometida aún o que fue abortada con 'rollback'.

32 ctid Ubicación física de una versión de una fila de una tabla El CTID puede variar, por lo que no se aconseja para ser utilizada para buscar un determinado campo. (Esto lo hace el VACUUM). Es útil para identificar campos en conjuntos grandes. En el caso de buscar filas lógicas, se recomienda OID.

33 Hackaton Ctid

34 Tipos propios

35 Ejemplo create type groupOfNumber AS ( first integer, second float, third numeric(10,2) ); create table tabla1( numeros groupOfNumber, a serial not null, dia date default CURRENT_DATE ); CREATE DOMAIN midominio as integer default 1 NOT NULL; create table foo3 (numero midominio); alter domain midominio set default 2; NOTICE: CREATE TABLE will create implicit sequence "tabla1_a_seq" for serial column "tabla1.a" CREATE TABLE base1=# \d List of relations Schema | Name | Type | Owner --------+--------------+----------+---------- public | tabla1 | table | postgres public | tabla1_a_seq | sequence | postgres (2 rows) insert into tabla1 default values; --mirar que no hay restriccion en 'numeros' insert into tabla1 values( (1,2.0,2.1) );

36 Expresiones regulares

37 Operadores http://developer.postgresql.org/pgdocs/postgres/functions-string.html

38 Ejemplos 'abc' ~ 'abc' true 'abc' ~ '^a' true 'abc' ~ '(b|d)' true 'abc' ~ '^(b|c)' false 'abc' ~ '(b)$' false

39 Funciones regexp_matches('foobarbequebaz', '(bar)(beque)') regexp_replace('Thomas', '.[mN]a.', 'M') regexp_split_to_array('hello world', E'\\s+') regexp_split_to_table('hello world', E'\\s+')

40 Ejemplos funciones substring('foobar' from 'o.b') oob substring('foobar' from 'o(.)b') o regexp_replace('foobarbaz', 'b..', 'X') fooXbaz regexp_replace('foobarbaz', 'b..', 'X', 'g') fooXX regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g') fooXarYXazY

41 Ejemplos

42

43 Lenguajes Procedurales

44 Finalidad Extienden los limites de SQL para tareas complejas. Permiten el control de acceso a determinados objetos. Controlar la integridad de los datos. Realizar operaciones suplementarias del lado del servidor, aprovechando el poder de procesamiento-

45 Características Las funciones se ejecutan del lado servidor, lo que puede ser utilizado para aprovechar los beneficios que eso acarrea, en lugar de ejecutarlos localmente. Están 'más cerca de los datos'.

46 Lenguajes Postgresql posee varios lenguajes Procedurales (o extensiones). Entre ellos se encuentran: SQL, PL/Pgsql, C, PL/python, PL/perl, PL/tcl, PL/Proxy, PL/Scheme, PL/R, PL/java, PL/php entre varios más... Saber aprovechar las ventajas de cada uno y conocer las debilidades.

47 Intro Pl/Pgsql PL por excelencia Procedural Fácil de usar Fácil de portar desde Oracle PL/SQL Sintaxis simple y apropiada No particularmente veloz … cache de planes de ejecución Requiere ser instalado, pero no necesita modificaciones en la compilación.

48 PL/pgsql Plpgsql no esta instalado por defecto. Se lo instala: CREATE LANGUAGE 'plpgsql'; Se puede crear a traves de un handler teniendo compilada la shared library. Plperl no viene por defecto, requiere librerias no incluidas. Entre los mas conocidos PL/tcl, python, ruby,php, java... y continua la lista...

49 Crear lenguaje CREATE [ PROCEDURAL ] LANGUAGE name CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name HANDLER call_handler [ VALIDATOR valfunction ] CREATE LANGUAGE plpgsql; GRANT ALL ON LANGUAGE plpgsql TO resagado2; --all es USAGE en este caso

50 Tipos de Funciones Según lo que retornan – Funciones “normales” – SRF (set-returning function) – Agregación Especiales – trigger – language_handler Privilegios durante invocación – security definer – security invoke Por lo que modifican – Volátiles VOLATILE – Inmutable INMUTABLE – Estricta STRICT Tradicionales – Retornan un solo resultado (escalar o tupla) – Es posible retornar un cursor abierto – Pueden tener efectos secundarios( tabla temporal)

51 Declaración postgres=# \h create function... CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [,...] ] ) [ RETURNS rettype ] { LANGUAGE langname | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST execution_cost | ROWS result_rows | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' }... [ WITH ( attribute [,...] ) ]

52 Estructura Tienen estructuras de 'bloque'. Cada bloque se define usando DECLARE, BEGIN, END; Estos bloques pueden estar anidados. NO SE PUEDEN DEFINIR TRANSACCIONES EN UNA FUNCIÓN. Los tipos de datos pasados a la función se dan en paréntesis El cuerpo de la función se pasa a la base de datos como una cadena de caracteres (véase, que el cuerpo empieza y acaba con comillas simples en las versiones 7.x, a partir del 8 se recomienda el uso de $$ o $BODY$) Tras la cadena el lenguaje usado para crear la función se define usando la orden "LANGUAGE" (otros lenguajes posibles son PL/PERL, PL/python, C, etc)

53 Calificadores SECURITY DEFINER STRICT COST cost_metric ROWS est_num_rows

54 Tipos de variables Numero INTEGER; otroVARCHAR(20); otroNumero NUMERIC(10,2); -- RECOMENDADO, podemos utilizar nuestros datos predefinidos soyDelTipoQueSeaUnCampomitabla.campo%TYPE; soyDelTipoRegistroDeUnaTablamitable%ROWTYPE;... RETURN soyDelTipoRegistroDeUnaTabla.unCampoDeLaTabla; -- utilizo un campo de la tupla en la variable

55 Bloques y declaración DECLARE variable tipodedato:= dameUnValor; variable2 algunTipo curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tablita; variable := valor; SELECT field1, field2 INTO variable,variable2 FROM tablita WHERE.. LIMIT 1;

56 Ejemplo CREATE FUNCTION suma (INT,INT,INT,INT) RETURNS INT4 AS $BODY$ DECLARE SUMA INT4; soyElPrimerParametro ALIAS FOR $1; BEGIN SUMA:=soyElPrimerParametro+$2+$3; SUMA:=SUMA-$4; RETURN cast(SUMA as int4); END; $BODY$ LANGUAGE plpgsql; postgres=> SELECT SUMA (1,2,3,4); Suma ----- 2

57 Control de Flujo (FOR) FOR i IN 1... numtimes LOOP statements END LOOP; FOR i IN REVERSE numtimes...1 LOOP statements END LOOP; FOR var_e IN EXCUTE('someDynamicS ql') LOOP statements RETURN NEXT var_e; END LOOP FOR var_e IN someSql LOOP statements RETURN NEXT var_e; END LOOP;

58 Control condicional IF IF condition THEN : END IF; IF condition THEN : ELSE : END IF; IF condition THEN : ELSEIF condition THEN : ELSE : END IF;

59 8.4 CASE (x valor) CREATE OR REPLACE FUNCTION x(INT4) RETURNS TEXT as $$ BEGIN CASE $1 WHEN 1,2 THEN RETURN 'uno o dos'; WHEN 3 THEN RETURN 'tres...'; ELSE RETURN 'ni idea'; END CASE; END; $$ language plpgsql;

60 8.4 CASE (x expresión) CREATE OR REPLACE FUNCTION x(INT4) RETURNS TEXT as $$ BEGIN CASE WHEN $1 < 10 THEN RETURN 'menor a 10'; WHEN $1 = 10 THEN RETURN 'it''s 10'; ELSE RETURN 'mas de 10 seguro'; END CASE; END; $$ language plpgsql;

61 Bucle (while y loop) WHILE condition LOOP : END LOOP; LOOP -- codigo EXIT WHEN count > 100; CONTINUE WHEN count < 50; -- cout IN [50.. 100] END LOOP;

62 Return RETURN somevariable; RETURN NEXT rowvariable; RETURN QUERY; --nuevo!! RETURN algundato RETURN SETOF algunTipoDeDato (SRF) RETURN void RETURN refcursor RETURN trigger

63 Estados y constantes de error FOUND ROW_COUNT division_by_zero no_data_found too_many_rows unique_violation

64 RAISE RAISE DEBUG[1-5] RAISE EXCEPTION RAISE INFO RAISE LOG RAISE NOTICE

65 Exepciones RAISE EXCEPTION 'Exception notice: %', var EXCEPTION WHEN condition THEN hacer o dejar en blanco para ignorar END;

66 Variables predefinidas para Triggers

67 Ejemplo CREATE OR REPLACE FUNCTION capitalize(_texto text) RETURNS text AS $BODY$ DECLARE fin INTEGER; temporal RECORD; retval TEXT; BEGIN retval = ' '; FOR temporal IN SELECT string_to_array (_texto,' ') AS arreglo LOOP SELECT regexp_replace( regexp_replace( array_dims(temporal.arreglo),'[^1-9]','','g'),'.','')::integer into fin; FOR i IN 1..fin LOOP retval = retval || upper(substring(temporal.arreglo[i],1,1)) || lower(regexp_replace(temporal.arreglo[i],'(.)(.*)','\\2')) ||' '; END LOOP; RETURN trim(retval); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;

68 Ejemplo Se inserta el valor de un campo En una variable (cursor implícito)

69 Ejemplo Triggers

70