PostgreSQLCapacitación Nivel 1

Día 4

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.

Fechas

Extracción y Casteo

Más sobre fechas

Más sobre fechas

Configuración

Configuración• 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.

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

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.

Parámtetros avanzados

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).

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

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.

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)

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).

TiposVariables:VarcharText, Bytea,etc.

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)

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)

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)

Parametros del kernelParámetros Descripción Valores Razonables

SHMMAX Maximo del segmento de mem compartida

32MB es por defecto. Se aconsejan 200 MB mínimo.

SHMMIN Mínimo del segmento de mem compartida

500 kb

SHMALL Total compartida ~SHMMAX

SHMSEG Máximo seg. Mem. Por proceso

1

SHMNI Máximo de segmentos SHMSEG+requerido por aplic.

SEMMNI Máx. De identificadores de semáforos

Al menos max_conection /16

SEMMNS Máx. De semáforos del sistema

(max_connection/16)*17+req. Por las aplic.

SEMMSL Semáforos del conjunto Min. 17

SEMMAP Entradas en un mapa de semáforos

~SEMMNS

SEMVMX Valor máx. De semáforo Al menos 32767

Modificación Parametros del Kernel

• Archivo sysctl.conf en el directorio /etc.• Delante de cada parámetro agregar

kernel.<parametro>

Recomendaciones

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.

Identificadores de objetosY columnas de sistema

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.

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.

Ejemplo Tableoid

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.

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.

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'.

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.

Hackaton Ctid

Tipos propios

Ejemplocreate 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) );

Expresiones regulares

Operadores

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

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

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+')

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

Ejemplos

Ejemplos

Lenguajes Procedurales

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-

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'.

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.

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.

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...

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

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)

Declaraciónpostgres=# \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 [, ...] ) ]

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)

Calificadores• SECURITY DEFINER• STRICT• COST cost_metric • ROWS est_num_rows

Tipos de variablesNumero INTEGER;otro VARCHAR(20);otroNumero NUMERIC(10,2);

-- RECOMENDADO, podemos utilizar nuestros datos predefinidossoyDelTipoQueSeaUnCampo mitabla.campo%TYPE;soyDelTipoRegistroDeUnaTabla mitable%ROWTYPE;...

RETURN soyDelTipoRegistroDeUnaTabla.unCampoDeLaTabla; -- utilizo un campo de la tupla en la variable

Bloques y declaraciónDECLAREvariable tipodedato:= dameUnValor;variable2 algunTipocurs1 refcursor;curs2 CURSOR FOR SELECT * FROM tablita;

variable := valor;SELECT field1, field2INTO variable,variable2FROM tablita WHERE .. LIMIT 1;

EjemploCREATE 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

Control de Flujo (FOR)FOR i IN 1 ... numtimes

LOOP statementsEND LOOP;FOR i IN REVERSE

numtimes ...1 LOOP statementsEND LOOP;

FOR var_e IN EXCUTE('someDynamicSql') LOOPstatements

RETURN NEXT var_e;

END LOOPFOR var_e IN someSql

LOOPstatements

RETURN NEXT var_e;

END LOOP;

Control condicional IFIF condition THEN

:

END IF;

IF condition THEN:

ELSE:

END IF;

IF condition THEN:ELSEIF condition THEN:ELSE:END IF;

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;

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;

Bucle (while y loop)

WHILE condition LOOP:

END LOOP;LOOP

-- codigoEXIT WHEN count > 100;CONTINUE WHEN count < 50;-- cout IN [50 .. 100]

END LOOP;

ReturnRETURN somevariable;RETURN NEXT rowvariable;RETURN QUERY; --nuevo!!RETURN algundatoRETURN SETOF algunTipoDeDato (SRF)RETURN voidRETURN refcursorRETURN trigger

Estados y constantes de error

FOUNDROW_COUNTdivision_by_zerono_data_foundtoo_many_rowsunique_violation

RAISE

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

Exepciones

RAISE EXCEPTION 'Exception notice: %', varEXCEPTION WHEN condition THEN hacer o dejar en blanco para ignorarEND;

Variables predefinidas para Triggers

Nombre Tipo DescripciónNEW %ROWTYPE Nuevos Valores (INSERT | UPDATE)OLD %ROWTYPE Valores Antiguos ( UPDATE |DELETE )TG_NAME NAME Nombre del TRIGGERTG_WHEN TEXT BEFORE | AFTERTG_LEVEL TEXT ROW | SENTENCIA (SQL)TG_OP TEXT INSERT, UPDATE | DELETETG_RELID OID Identificador de la tablaTG_RELNAME NAME Nombre de la tablaTG_NARGS INT Numero de argumentosTG_VARGS TEXT[] Argumentos

EjemploCREATE 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;

END LOOP;

RETURN trim(retval);

END;

$BODY$ LANGUAGE 'plpgsql' VOLATILE;

Ejemplo

Se inserta el valor de un campoEn una variable (cursor implícito)

Ejemplo Triggers

</Día 4></curso>

</Gracias!>