PostgreSQL General Bits estará de vacaciones durante las dos semanas siguientes. Por favor deléitese con artículos anteriores desde los Archivos.

PostgreSQL General Bits volverá con nuevos artículos y una nueva edición el 30 de noviembre.

Editora: elein at varlena.com

Este es el clásico problema donde se tiene una lista de detalles por cada maestro, pero además uno de ellos, y sólo uno, debe ser designado especial. Por ejemplo, en una lista de correo simple se desea almacenar la dirección de correo electrónico de varias personas, y designar una y sólo una como la dirección del moderador.

Hay varios detalles a tener en cuenta con este valor verdadero exclusivo. Estudiaremos un par de aspectos: primero la manera general de exigir que se compla esta condición, y luego un par de ejemplos prácticos de su aplicación.

La manera simple de restringir una fila de cada grupo a que sea verdadero es agregar un índice único condicional. A continuación se presenta una tabla de ejemplo que ilustra esta metodología.

	create table maillist (
		listname	text, --  REFERENCES lists (listname),
		email	text,
		moderator boolean,
		PRIMARY KEY (listname, email)
	);

	create unique index moderator on maillist (listname) where moderator;

	insert into maillist values ('bugs', 'sam@iam.com','f');
	insert into maillist values ('bugs', 'elle@what.com','f');
	insert into maillist values ('bugs', 'xandy@where.com','t');
	insert into maillist values ('support', 'help@bigbus.com','f');
	insert into maillist values ('support', 'target@zero.com','t');
	insert into maillist values ('support', 'frank@subtle.com','f');
	insert into maillist values ('dev', 'hqr@zoneout.com','t');
	insert into maillist values ('dev', 'lphant@zoo.com','f');
	insert into maillist values ('dev', 'zardoz@who.com','f');

Con este índice único condicional, no se puede insertar o actualizar una segunda fila con un valor verdadero para la columna moderator.

	db=# insert into maillist values 
		('bugs', 'spider@web.com', 't');
	ERROR:  duplicate key violates unique constraint "moderator"
	db=# update maillist set moderator='t' 
		where email='help@bigbus.com' and listname='support';
	ERROR:  duplicate key violates unique constraint "moderator"

	
	db=# delete from maillist 
		where email='xandy@where.com' and listname='bugs';
	DELETE 1
	db=# update maillist set moderator='f' 
		where email='target@zero.com' and listname='support';
	UPDATE 1

Los cambios de moderador de una lista se realizan ejecutando dos sentencias. Si se desea cambiar el moderador, se cambia el valor a falso en el moderador actual y se cambia a verdadero en el nuevo moderador. Si se desea que una dirección recién ingresada sea el nuevo moderador, cambie el antiguo a falso y agregue el nuevo con el valor verdadero.

	update maillist set moderator='f' where email='hqr@zoneout.com' and listname = 'dev';
	update maillist set moderator='t' where email='lphant@zoo.com' and listname = 'dev';

Si quisiera efectuar un cambio de moderador en una sentencia, podría tener un disparador (trigger) que, cuando la columna moderator esté en verdadero en la fila NEW, primero cambie el valor del antiguo moderador a falso. Los disparadores pueden ser excesivos si puede manejar la lógica de dos pasos en la aplicación. La desición de agregar o no la posibilidad de efectuar el cambio automáticamente depende de si desea que los cambios de moderadores sean invisibles o si prefiere manejar la condición de error. Ambas maneras son válidas y tienen sus méritos y limitaciones. A continuación se presenta la función para el disparador para INSERT o UPDATE.

	CREATE OR REPLACE FUNCTION insupd_maillist( )
	RETURNS TRIGGER as 
	'
	BEGIN
	IF NEW.moderator=''t'' THEN
	   UPDATE  maillist SET moderator=''f'' 
	   WHERE moderator=''t'' AND listname=NEW.listname;
	END IF;
	RETURN NEW;
	END;
	' language 'plpgsql';
	
	CREATE TRIGGER insmaillist BEFORE INSERT ON maillist
	FOR EACH ROW EXECUTE PROCEDURE insert_maillist();

El último asunto a tener en cuenta es si el moderador es requerido. Si no es aceptable no tener moderador, el índice no es suficiente. Además tendrá que capturar las eliminaciones y las actualizaciones donde el campo de moderador se cambie de verdadero a falso, y lanzar un error. Los detalles de esto se dejan de ejercicio para el lector.

Contribuyeron: Ron St-Pierre rstpierre at syscor.com Tom Lane tgl at sss.pgh.pa.us scott.marlowe scott.marlowe at ihs.com Martin Marques martin at bugs.unl.edu.ar Greg Stark gsstark at mit.edu Csaba Nagy nagy at ecircle-ag.com Stephan Szabo sszabo at megazone.bigpanda.com Alvaro Herrera alvherre at dcc.uchile.cl Jan Wieck JanWieck at Yahoo.com

El uso de un disparador para contar filas fue discutido en el Artículo #49-1. Posteriormente apareció otra pregunta acerca del efecto de múltiples disparadores en la lógica del contador. La pregunta fue si era posible que un disparador incrementara el contador, y que un disparador posterior abortara la sentencia original.

Cuando se ejecuta una sentencia, uno o más disparadores también se ejecutan para implementar la verificación de llaves foráneas, funcionalidad adicional como el contador de filas y también cláusulas CHECK. Todos los triggers se ejecutan en el contexto de la transacción de la sentencia original. El orden de ejecución, arbitrariamente, es alfabético. Si cualquiera de los disparadores hace que la transacción se aborte, las acciones de todos los disparadores también serán abortadas. Todo sucede en la misma transacción.

Si el disparador para contar tiene éxito y un disparador subsiguiente aborta la transacción, los efectos del disparador del contador también son abortados.

Contribuyeron: Steven Halerman halermans at yahoo.com elein at varlena.com

Para aquellos que no se han enterado, hay una nueva lista de correo "general" en alemán en postgresql.org

Für alle, die es noch nicht gehört haben, es gibt jetzt eine deutsche Mailingliste für "allgemeine" Themen unter postgresql.org. Zum Eintragen, schreibt eine E-Mail mit "subscribe" im Text an pgsql-de-allgemein-request@postgresql.org.

Contribuyó: Peter Eisentraut peter_e at gmx.net

Antes de mirar los detalles de cualquier EXPLAIN, recuerde que debe aplicar VACUUM ANALYZE para que el optimizador de consultas tome las decisiones correctas. Sin VACUUM ANALYZE, la optimización de consultas es, cuando mucho, improvisación. Lo segundo a tener en cuenta es que EXPLAIN ANALYZE entrega mejor información que simplemente EXPLAIN: entrega la estimación del optimizador, y además el costo real de ejecución de la consulta. Así como un promedio es información útil, es más útil contar con la mediana y la desviación estándar para calificar el promedio. De la misma forma, EXPLAIN ANALYZE da un mayor contexto para juzgar la consulta.

Para comenzar a entender EXPLAIN, es útil mirar casos pequeños y comprenderlos antes de mirar consultas más complejas. Para esta tabla ejemplo y dos consultas, la pregunta es por qué una usa un recorrido secuencial y la otra usa un índice.

	              Table "public.abcs"
	  Column   |   Type       |      Modifiers
	-----------+--------------+-----------------------------------
	 abcid     | integer      | not null default nextval('abcid_seq'::text)
	 type      | varchar(255) |
	 versionof | integer      |
	Indexes: abcs_pkey primary key btree (abcid),
	         abcs_versionof btree (versionof)
	
	planb=# explain select type from abcs where abcid = 6339;
	                                 QUERY PLAN
	-----------------------------------------------------------------------
	 Index Scan using abcs_pkey on abcs  (cost=0.00..6.01 rows=1 width=145)
	   Index Cond: (abcid = 6339)
	(2 rows)
	
	planb=# explain select type from abcs where versionof = 6339;
	                           QUERY PLAN
	----------------------------------------------------------------
	 Seq Scan on abcs  (cost=0.00..59182.10 rows=16137 width=145)
	   Filter: (versionof = 6339)
	(2 rows)

La primera consulta ve el índice de llave primaria en la columna abcid y comprende que retornará una y sólo una fila. En este caso, lo mejor es usar el índice si es posible.

En el segundo caso, el optimizador determina que el resultado tendrá 16000 filas. En casos particulares, un recorrido secuencial es más rápido que un recorrido de índice. Esos casos principalmente se presentan cuando hay que seleccionar un porcentaje grande de filas de la tabla.

Contribuyeron: Mark Harrison Rod Taylor Adam Ruth

Algunos clientes que no soportan PostgreSQL explícitamente (¡y algunos de los que sí lo hacen!) pueden tener problemas con funciones que retornan conjuntos (SRF, por las siglas en inglés de Set Returning Functions). En MS Query, la siguiente sentencia usando una SRF en la cláusula FROM retorna un error indicando que no hay una tabla llamada getStudents(). Obviamente.

	SELECT * FROM getStudents();

Los clientes utilizan los catálogos de sistema para determinar qué tablas existen y cuáles son sus especificaciones. Cuando se usa una SRF, se declara un tipo para indicar las especificaciones de las filas que se retornarán, pero esta información no es comprendida por MS Query.

Para resolver este problema, cree una tabla que tenga el tipo que retorne la SRF. Luego, cree una regla en la tabla que sustituya un SELECT en la tabla por un SELECT de la función.

Para obtener más información sobre las funciones que retornan conjuntos, vea la Edición #26 de PostgreSQL General Bits.

Contribuyeron: Tomas Larsson Shridhar Daithankar