viernes, marzo 03, 2006

PostgreSQL: crear tablas temporales a partir de una consulta SQL

Siguiendo con mi trabajo de DBA Part Time, tuve que resolver el siguiente problema: en nuestra base de datos existe información de una "compra de mercadería" de muchos ítems que podía ser utilizada como plantilla para otro caso muy similar. Esto permitiría ahorrarles tiempo y trabajo a los usuarios que no tendrían que volver a ingresar toda la información manualmente, solo modificar los datos que cambian.

Esta información se encuentra desperdigada en varias tablas. Mi estrategia es crearme una o varias tablas temporales, obtener los datos requeridos, modificarlos a gusto, y finalmente, integrarlos a la tabla original. Este procedimiento me permite trabajar de forma segura, limpia, ordenada, sin arriesgarme a cometer errores en la tabla original.

Descarto que este tipo de situaciones se deben hacer primero en una base de datos de prueba, documentarlo y armar scripts automatizados (que no dependen de un dato que pueda ingresar de forma errónea), y cuando todo está correctamente verificado, se aplica en la base de producción.

No sería la primera vez que he estado obligado (por las circunstancias ;-) a trabajar directamente en la base de datos de producción, y tampoco la última vez que cometeré errores con pérdidas de datos (quién no escribió alguna vez un UPDATE sin WHERE ;-).

1) Leyendo el manual de Postgres me encuentro que puedo crear una copia de la estructura de una tabla con los datos que yo quiera, de la siguiente forma:


CREATE TABLE compras_tmp AS
SELECT * FROM compras WHERE id = '12345';


A partir de la tabla "compras" creo una tabla temporal llamada "compras_tmp" con los datos de la consulta que se extrajo de la tabla original.

2) Luego, modifico en la tabla temporal el valor clave y todos los datos que se requieran.


UPDATE compras_tmp SET id = '999999' where id = '12345';


3) Luego de que los datos fueron modificados, hay que incorporar la tupla a la tabla de producción, ejecutando el siguiente comando:


INSERT INTO compras SELECT * FROM compras_tmp;


Insertando en la tabla "compras" todos los datos que se encuentran en la tabla temporal.

4) Y lo que hacemos siempre los programadores "con aires de DBA", es olvidarnos de borrar las tablas temporales que luego nunca más se usarán:


DROP TABLE compras_tmp;


Espero que les pueda ser útil si alguna vez tiene que hacer algo similar en PostgreSQL.

14 comentarios:

Rodrigo dijo...

Con PostgreSQL y MySQL.. lo acabo de probar y anda impecable :)

enrique_place dijo...

Gracias por confirmar que es compatible con MySQL ;-)

Desde que migramos a PostgreSQL no he hecho muchos trabajos de administración con bases MySQL.

¿Será que todas las empresas con grandes bases de datos ya se habrán cambiado a PostgreSQL? ;-)

Rodrigo dijo...

Probablemente si...
La verdad no tengo experiencia con PostgreSQL.
Incluso, estoy pensando en migrar todo, ya que manejamos una cantidad enorme de registros.
Una de las bdd actualmente ocupa 11 Gb :S un abuso...

enrique_place dijo...

¿Tanta información? ¿Estás guardando datos binarios dentro de la base de datos?

Haceme acordar que tengo que escribir un artículo sobre el proceso de migración de MySQL a PostgreSQL ;-)

Tuve que pasar por ese proceso hace más de un año, y en esa época me dió más trabajo del esperado. Por ejemplo, a pesar que estamos hablando de dos bases "software libres" y que por premisa deberían respetar los estándares ... el "dump" (la exportación de las extruturas de las tablas y los datos como sentencias SQL en un archivo de texto) no eran compatibles entre ambas.

Buscando en Internet me encontré con varias personas que pasaron por ese mismo problema y terminaron haciendo un scripts que modifica el "dump" de MySQL y lo hace "compatible" con PostgreSQL.

Calculo que hoy debe ser más transparente, además existen más herramientas para hacer la migración.

La gran ventaja del cambio es obtener todo el poder que te da usar todas las características de la base. Si no es lo que buscas, y no tienes un buen DBA para sacarle provecho, ni lo intentes.

Por defecto, Postgres necesita el doble de tiempo en procesar las mismas consultas que en MySQL (no lo tengo confirmado esto con la versión 5).

El tema es que la base de datos PostgreSQL es mucho más compleja y tiene muchos más procesos y controles corriendo que MySQL, generando una "latencia" a la hora de usarla.

Bueno, cuenta un poco más las características de esa base de datos, que has despertado mi curiosidad ;-)

CyberCanibal dijo...

Buenas tardes Enrique, he leido una que otra respuesta de este topico y me gustaria saber si ya tienes listo el How To para migrar de MySQL a Postgres. De verdad apreciaria mucho tu ayuda. Necesito migrar una base de datos con una gran cantidad de registros y se me esta complicando la existencia.

He probado con:
$ mysqldump -u root -p --compatible=postgresql --skip-add-drop-table basededatos > archivo.sql

Pero cuando restauro con psql aparecen muchos errores.

Gracias!

Julio Cesar Sánchez González dijo...

Hola, hace algun tiempo migre una base de datos de mysql a postgres, como 2 millones de registros, la forma mas facil la hice generando un script de perl con dbi, extraia, metia. A mi me funciono y francamente fue muy sencillo.

Otra cosa, con respecto a las tablas temporales, en realidad las que creaste eran tablas comunes y corrientes, para crear una verdadera tabla temporal necesitas crearla con el operador temporary, por ejemplo: create temporary table clientes_tmp as selet * from clientes;

Pero como referencia que mejor que el mismo manual: http://www.postgresql.org/docs/8.1/static/sql-createtable.html

Una tabla temporal solo es visible por el usuario que la crea y por el tiempo que dura la conexion, no es necesario borrarla el RDBMS lo hace por nosotros mismos.

Saludos a todos.

Julio Cesar Sánchez González dijo...

Hola, hace algun tiempo migre una base de datos de mysql a postgres, como 2 millones de registros, la forma mas facil la hice generando un script de perl con dbi, extraia, metia. A mi me funciono y francamente fue muy sencillo.

Otra cosa, con respecto a las tablas temporales, en realidad las que creaste eran tablas comunes y corrientes, para crear una verdadera tabla temporal necesitas crearla con el operador temporary, por ejemplo: create temporary table clientes_tmp as selet * from clientes;

Pero como referencia que mejor que el mismo manual: http://www.postgresql.org/docs/8.1/static/sql-createtable.html

Una tabla temporal solo es visible por el usuario que la crea y por el tiempo que dura la conexion, no es necesario borrarla el RDBMS lo hace por nosotros mismos.

Saludos a todos.

Julio Cesar Sánchez González dijo...

Sobre el performance de postgresql, actualmente en la version 8.2.4 es en realidad muy bueno y yo no diria que es el doble o mas lento que mysql, pero para fines practicos lo que importa es la integridad de datos no la velocidad. Otra cosa, postgresql es facil de usar y administrar, no tanto mas que mysql. Conoci la historia de un argentino que tenia un sistema con slony y manejaba varios teras de informacion en tiempo record, eso si que es obtimizacion. Compañeros Postgresql no muerde, en realidad es lo mejor que les podria pasar en la vida a todos aquello seguidores de MySQL, pruebenlo y me dicen que les parecio.

Max dijo...

Hola, yo tengo un problema un tanto raro
necesito hacer una funcion que cree una tabla temporal, pero me sale un error:
ERROR: cannot open SELECT INTO query as cursor
CONTEXT: PL/pgSQL function "compmarca" line 23 at open
La consulta esta bien, porque cuando la hago a parte, si me crea la tabla y la llena, algo tiene que ver con la llamada al cursor. si pueden ayudarme les agradecería muchisimo.

CREATE OR REPLACE FUNCTION "reportes"."compmarca" (varchar, varchar, varchar, varchar) RETURNS SETOF boolean AS
$body$
declare
categoria alias for $1;
campana alias for $2;
fechaini alias for $3;
fechafin alias for $4;
cursor1 refcursor;
consulta varchar;

begin
consulta:='CREATE TEMP TABLE compxmarca as
select comp.* from (
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''tv'','''||fechaini||''','''||fechafin||' '')
union
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''rd'','''||fechaini||''','''||fechafin||' '')
union
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''pr'','''||fechaini||''','''||fechafin||' '')
union
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''rv'','''||fechaini||''','''||fechafin||' '')
union
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''su'','''||fechaini||''','''||fechafin||' '')
) as comp;';

open cursor1 for execute consulta;
close cursor1;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Max dijo...

Tengo otro problema, cambie la funcion y esta si me resulta pero cuando quiero hacerle un select a la tabla o incluso un drop me sale que no existe la tabla, pero si esta creada, es como si la funcion creara una sesion para la creacion de la tabla y la tabla perteneciera a esa sesiosn y por eso en el editor no puedo verla ser?

esta es la nueva funcion


CREATE OR REPLACE FUNCTION "reportes"."compmarca" (varchar, varchar, varchar, varchar) RETURNS SETOF boolean AS
$body$
declare
categoria alias for $1;
campana alias for $2;
fechaini alias for $3;
fechafin alias for $4;
cursor1 refcursor;
consulta varchar;
respuesta varchar;
begin


EXECUTE 'CREATE GLOBAL TEMP TABLE compxmarcatb (
medio varchar(2),
categoria varchar(6),
marca varchar(6),
inversion numeric
)';
INSERT INTO compxmarcatb (select comp.* from (
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','tv',''||fechaini||'',''||fechafin||'')
union
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','rd',''||fechaini||'',''||fechafin||'')
union
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','pr',''||fechaini||'',''||fechafin||'')
union
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','rv',''||fechaini||'',''||fechafin||'')
union
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','su',''||fechaini||'',''||fechafin||'')
) as comp);


end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

lianjudo dijo...

Hola enrique, que pasa si no modifico la clave primaria en la tmp para hacer el insert en la tabla original; es decir, no necesito hacer un insert sino un update

Jose Miguel dijo...

Hermano una pregunta, eso me sirve para crear carteles en caso de matrimonios? me explico, los novios van al registro civil, dan sus datos y los de los testigos, y el sistema (en una tabla temporal) emite un reporte temporal, que dice que deben ir en dos meses a contraer el matrimonio, un dia antes del matrimonio ellos regresan a confirmar que no estan arrepentidos y es en ese momento donde paso los datos de la temporal a la original. Puede hacerse asi durante tanto tiempo? o solo es mientras ejecutamos un query?

jonny dijo...

Estoy realizando un script en pl pgsql(postgres) y me sirvio para poder borrar unos registros duplicados sin qu me dañaran los originales...ya les contaré como me quedo el código sirve mucho creanme. Gracias Enrique

leafarpaur dijo...

un excelente trabajo, me ayudo demasiado, estamos implementando búsquedas en un nuevo sistema y ha sido de gran ayuda lo que describes en tu publicación saludos!!!!