Hay una serie de índices aparte de los convencionales, que son poco conocidos, pero no por ello dejan de ser muy útiles.
GIN - índices optimizados para la búsqueda de subelementos, dentro de una columna, por ejemplo columnas de tipo Array, columnas que almacenen JSON o campos de texto para hacer búsquedas FTS (Full Text Search).
GIST - índices para búsquedas de datos geolocalizados.
BRIN - índices que ahorra espacio de almacenamiento para aquellos datos que se encuentran ordenados de forma natural. Por ejemplo las entradas de un fichero log, siempre se generan de forma secuencial en base al campo de fecha de la entrada.
HASH - indice optimizado en velocidad para la búsqueda de elementos por igualdad. Por ejemplo la dirección de email de usuarios. Usando este índice tendríamos máxima velocidad de acceso para buscar a un usuario por su email.
Mostrando entradas con la etiqueta postgres. Mostrar todas las entradas
Mostrando entradas con la etiqueta postgres. Mostrar todas las entradas
lunes, 28 de mayo de 2018
jueves, 19 de febrero de 2015
analizando consultas en Postgresql. Parte I
Tenemos una potente herramienta para analizar las consultas en PostgreSQL: explain analyze
explain analyze select * from usuario; Seq Scan on usuario (cost=0.00..4743.71 rows=44471 width=243) (actual time=0.026..59.431 rows=44474 loops=1) Total runtime: 63.645 ms (2 filas)
Seq Scan, significa que la consulta se hará fila a fila secuencialmente sin acceso por índice.
El valor numérico cost=0,00, es el tiempo que tiene que esperar la consulta en ser ejecutada. En este caso el tiempo es 0.00 porque puede ejecutarse inmediatamente.
El segundo valor 4743,71, es el numero de fetches necesarios para recuperar todas las filas de la consulta, el número de fetches lo podemos minimizar por ejemplo aplicando LIMIT.
Rows=44.471 es el número de filas que devolverá la consulta y width el tamaño medio en bytes de cada fila.
Truco: si vas a realizar análisis de operaciones de escritura, encierralas en un bloque de transacción, para poder ejecutarlas las veces que necesites y que no se produzcan cambios en la base de datos:
begin; explain analyze insert ...; rollback;
lunes, 15 de diciembre de 2014
Usuario de solo lectura
A veces puedes necesitar dar acceso a tu base de datos a una herramienta de terceros, de la cual no te fias o solo quieres que haga consultas pero no toque nada. Una forma rápida es crear un usuario de solo lectura:
create user 'readonly' password 'laquesea';
alter user readonly set default_transaction_read_only = on;
Luego puedes darle permiso sobre todas las tablas de los esquemas que necesites:
select 'grant select on ' || a.schemaname || '.' || a.tablename || ' to readonly;' from pg_tables a where schemaname in ('esquema1');
select 'grant select on ' || a.schemaname || '.' || a.tablename || ' to readonly;' from pg_tables a where schemaname in ('esquema2');
Y por último dale permisos de acceso a los esquemas:
grant usage on schema cromos to readonly;
grant usage on schema coleccion to readonly;
create user 'readonly' password 'laquesea';
alter user readonly set default_transaction_read_only = on;
Luego puedes darle permiso sobre todas las tablas de los esquemas que necesites:
select 'grant select on ' || a.schemaname || '.' || a.tablename || ' to readonly;' from pg_tables a where schemaname in ('esquema1');
select 'grant select on ' || a.schemaname || '.' || a.tablename || ' to readonly;' from pg_tables a where schemaname in ('esquema2');
Y por último dale permisos de acceso a los esquemas:
grant usage on schema cromos to readonly;
grant usage on schema coleccion to readonly;
lunes, 6 de octubre de 2014
Bloque anónimo
A veces necesitas hacer uso de las estructuras de plpgsql, por ejemplo, en scripts .sql que llamas desde tareas del cron, etc. Para ello tenemos los bloques DO.
A continuación un bloque sencillo, que comprueba si es primeros de mes y añade un mes de antigüedad a los usuarios registrados. Como puedes ver podemos utilizar variables y ejecutar comandos de los cuales no necesito conocer el resultado (perform).
do $$
declare
dia integer;
begin
select extract ('day' from current_date) into dia from editorial;
if dia = 1 then
perform 'update usuario set months=months+1';
else
raise notice 'Hoy no es día 1';
end if;
end$$;
A continuación un bloque sencillo, que comprueba si es primeros de mes y añade un mes de antigüedad a los usuarios registrados. Como puedes ver podemos utilizar variables y ejecutar comandos de los cuales no necesito conocer el resultado (perform).
do $$
declare
dia integer;
begin
select extract ('day' from current_date) into dia from editorial;
if dia = 1 then
perform 'update usuario set months=months+1';
else
raise notice 'Hoy no es día 1';
end if;
end$$;
martes, 12 de agosto de 2014
Cambiar atributos default o not null a una columna existente
Si quieres quitar la propiedad default o not null a una columna existente;
alter table alter column drop not null;
alter table alter column drop default;
Por el contrario, si quieres añadir el atributo not null o la propiedad default:
alter table alter column set not null;
alter table alter column set default current_timestamp;
alter table
alter table
Por el contrario, si quieres añadir el atributo not null o la propiedad default:
alter table
alter table
Ultimo id insertado
Cuando queremos recuperar el último id insertado en una tabla, para por ejemplo asignarlo en los registros de sus hijas. Lo habitual es consultar el último valor de la secuencia asociada al campo clave. Por ejemplo:
select currval('cambio_id_seq');
En este caso tenemos que conocer el nombre de la secuencia, si lo desconociésemos podríamos utilizar la función pg_get_serial_sequence que nos proporcionaría el nombre de la secuencia asociado a un campo determinado de una tabla:
select currval(pg_get_serial_squence('cambio','id'));
Esta opción tiene un problema. Solo nos funcionará si nadie más utiliza dicha secuencia en otra sesión. Es decir en entorno de multiples conexiones no es recomendable.
Por el contrario LASTVAL(), está aislado a nivel de sesión, por lo que no tiene dichos efectos indeseados
Pero si la tabla tuviese un trigger que insertará nuevos registros o modificara la secuencia por algún motivo, LASTVAL() no funcionaría correctamente.
Por todo lo anterior, el mejor método posible es utilizar la clausula RETURNING después de la inserción. Por ejemplo:
insert into cambio (id,usuario1,usuario2)
values (DEFAULT,usuario1_id,usuario2_id)
RETURNING id into cambio_id;
En la variable cambio_id tendremos almacenado seguro el valor de la inserción que estamos haciendo en nuestra sesión, sin efecto colateral alguno.
select currval('cambio_id_seq');
En este caso tenemos que conocer el nombre de la secuencia, si lo desconociésemos podríamos utilizar la función pg_get_serial_sequence que nos proporcionaría el nombre de la secuencia asociado a un campo determinado de una tabla:
select currval(pg_get_serial_squence('cambio','id'));
Esta opción tiene un problema. Solo nos funcionará si nadie más utiliza dicha secuencia en otra sesión. Es decir en entorno de multiples conexiones no es recomendable.
Por el contrario LASTVAL(), está aislado a nivel de sesión, por lo que no tiene dichos efectos indeseados
Pero si la tabla tuviese un trigger que insertará nuevos registros o modificara la secuencia por algún motivo, LASTVAL() no funcionaría correctamente.
Por todo lo anterior, el mejor método posible es utilizar la clausula RETURNING después de la inserción. Por ejemplo:
insert into cambio (id,usuario1,usuario2)
values (DEFAULT,usuario1_id,usuario2_id)
RETURNING id into cambio_id;
En la variable cambio_id tendremos almacenado seguro el valor de la inserción que estamos haciendo en nuestra sesión, sin efecto colateral alguno.
lunes, 17 de marzo de 2014
Busqueda de Esquemas en Postgres por defecto
Si teneis una base de datos con varios esquemas, llega un momento en que es complicado encontrar una tabla y es farragoso tener que escribir el nombre del esquema delante de cada tabla para acceder al contenido.
Una solución es modificar el search_path del usuario con el que te conectas a la base de datos, por ejemplo del usuario user_connect, conectado como superusuario:
alter user "user_connect" set search_path to "$user",public,master,soccer;
Pero es mucho mejor indicar el camino de acceso a nivel de base de datos, en lugar de hacerlo a nivel de usuario.
alter database "league" set search_path to "$user",public,master,soccer;
De esta forma todos los usuarios que se conecten a esta base de datos, podrán encontrar las tablas de forma cómoda, en los esquemas public, master, soccer y en un esquema con su propio nombre de usuario con el que se ha conectado.
Una solución es modificar el search_path del usuario con el que te conectas a la base de datos, por ejemplo del usuario user_connect, conectado como superusuario:
alter user "user_connect" set search_path to "$user",public,master,soccer;
Pero es mucho mejor indicar el camino de acceso a nivel de base de datos, en lugar de hacerlo a nivel de usuario.
alter database "league" set search_path to "$user",public,master,soccer;
De esta forma todos los usuarios que se conecten a esta base de datos, podrán encontrar las tablas de forma cómoda, en los esquemas public, master, soccer y en un esquema con su propio nombre de usuario con el que se ha conectado.
jueves, 26 de septiembre de 2013
Indices no utilizados en postgresql
Consultar los índices de tu bbdd que no están siendo utilizados, para ahorrar espacio o replantearte sus campos:
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;
viernes, 10 de mayo de 2013
Consulta de Bloqueos en PostgresSql
Si tienes problemas de registros bloqueados en la base de datos ejecuta la siguiente sentencia para visualizar que sentencia/s lo están provocando:
SELECT bl.pid AS blocked_pid, a.usename AS blocked_user,
kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.pid
JOIN pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.pid
ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
WHERE NOT bl.granted;
SELECT bl.pid AS blocked_pid, a.usename AS blocked_user,
kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.pid
JOIN pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.pid
ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
WHERE NOT bl.granted;
martes, 19 de febrero de 2013
Error JPA no sigue secuencias de Postgres
Si os encontrais en JPA que vuestros objetos no siguen las secuencias definidas en Postgres, para el objeto en cuestión, y recibes errores del tipo:
[ERROR]: javax.persistence.PersistenceException: org.hibernate.NonUniqueObjectException: a different object with the same identifier value was already associated with the session: [
@Id
@SequenceGenerator(name="tabla_id_generator", sequenceName="esquema.tabla_id_seq")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="tabla_id_generator")
@Basic(optional=false)
private Integer id;
Hay un truco que funciona, que es añadir "allocationSize=1":
@Id
@SequenceGenerator(name="tabla_id_generator", sequenceName="esquema.tabla_id_seq",allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="tabla_id_generator")
@Basic(optional=false)
private Integer id;
Por si sirve a alguien.
[ERROR]: javax.persistence.PersistenceException: org.hibernate.NonUniqueObjectException: a different object with the same identifier value was already associated with the session: [
@Id
@SequenceGenerator(name="tabla_id_generator", sequenceName="esquema.tabla_id_seq")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="tabla_id_generator")
@Basic(optional=false)
private Integer id;
Hay un truco que funciona, que es añadir "allocationSize=1":
@Id
@SequenceGenerator(name="tabla_id_generator", sequenceName="esquema.tabla_id_seq",allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="tabla_id_generator")
@Basic(optional=false)
private Integer id;
Por si sirve a alguien.
miércoles, 19 de septiembre de 2012
Componer fechas
Como componer en postgres una fecha con partes de otras fechas:
select date (
date_part('year' ,current_timestamp) || '-' ||
date_part('month',current_timestamp) || '-' ||
date_part('day' ,current_timestamp));
select date (
date_part('year' ,current_timestamp) || '-' ||
date_part('month',current_timestamp) || '-' ||
date_part('day' ,current_timestamp));
lunes, 3 de septiembre de 2012
Estadísticas de acierto de la cache
Es muy interesante saber en nuestra base de datos cuantas veces encontramos en la caché el resultado de las consultas. Si tenemos valores altos, seguro que no accedemos al disco a buscar los resultados y por lo tanto nuestra base de datos está funcionando bien.
Vamos a ver el porcentaje de acierto a nivel de base de datos:
SELECT d.datname, pg_database_size(d.datname), SUM(pg_stat_get_db_blocks_hit(d.oid)) / SUM(pg_stat_get_db_blocks_fetched(d.oid)) AS hit_rate
FROM pg_database d
GROUP BY d.datname
HAVING SUM(pg_stat_get_db_blocks_fetched(d.oid)) > 0
Pero aún más interesante es ver el resultado a nivel de tabla:
select t.schemaname, t.relname, heap_blks_hit*100/(heap_blks_hit+heap_blks_read) as BCHR
from pg_statio_user_tables t
where t.heap_blks_read > 0
Si tus tablas más accedidas tienen valores superiores a 95% todo va bien.
Sino revisa tus valores de "shared_buffers" y "effective_cache_size", si son correctos quizá tengas que revisar tus sentencias o aumentar tu memoria.
Referencias:
http://blog.kimiensoftware.com/2011/05/postgresql-vs-oracle-differences-4-shared-memory-usage-257
Vamos a ver el porcentaje de acierto a nivel de base de datos:
SELECT d.datname, pg_database_size(d.datname), SUM(pg_stat_get_db_blocks_hit(d.oid)) / SUM(pg_stat_get_db_blocks_fetched(d.oid)) AS hit_rate
FROM pg_database d
GROUP BY d.datname
HAVING SUM(pg_stat_get_db_blocks_fetched(d.oid)) > 0
Pero aún más interesante es ver el resultado a nivel de tabla:
select t.schemaname, t.relname, heap_blks_hit*100/(heap_blks_hit+heap_blks_read) as BCHR
from pg_statio_user_tables t
where t.heap_blks_read > 0
Si tus tablas más accedidas tienen valores superiores a 95% todo va bien.
Sino revisa tus valores de "shared_buffers" y "effective_cache_size", si son correctos quizá tengas que revisar tus sentencias o aumentar tu memoria.
Referencias:
http://blog.kimiensoftware.com/2011/05/postgresql-vs-oracle-differences-4-shared-memory-usage-257
jueves, 30 de agosto de 2012
Indices OnlyScan
A partir de Postgres 9.2 tenemos índices que si contienen los campos necesarios de una consulta, no acuden a la tabla a recuperar la información, sino que la recuperan directamente del índice. Para disfrutar de esta mejora tenemos que activar la consulta de este modo: SET enable_indexonlyscan TO true;Podeis ver ejemplos de uso en: http://michael.otacoo.com/postgresql-2/postgresql-9-2-highlight-index-only-scans/ Tened cuidado al utilizarlo para no crear índices sobre campos que se actualicen muy a menudo, ya que perderemos rendimiento durante las actualizaciones e inserciones y no compensará la ganancia sobre las consultas.
miércoles, 22 de agosto de 2012
Creación de índices
Cuando en producción nos encontramos que tenemos que optimizar las consultas, nos encontraremos a veces que no podemos parar los servidores.
La optimización muchas veces pasa por crear un índice para alguna tabla, pero en Postgres esta operación bloquea la tabla.
Esto puede hacer caer el servidor de aplicaciones en el caso de aplicaciones web dinámicas, si el índice es muy grande.
Por fortuna Postgres nos facilita hacer esta operación de forma concurrente sin necesidad de bloquear la tabla, la operación será más lenta pero podremos hacerla en caliente:
CREATE INDEX CONCURRENTLY idx_salary ON employees(last_name, salary);
La optimización muchas veces pasa por crear un índice para alguna tabla, pero en Postgres esta operación bloquea la tabla.
Esto puede hacer caer el servidor de aplicaciones en el caso de aplicaciones web dinámicas, si el índice es muy grande.
Por fortuna Postgres nos facilita hacer esta operación de forma concurrente sin necesidad de bloquear la tabla, la operación será más lenta pero podremos hacerla en caliente:
CREATE INDEX CONCURRENTLY idx_salary ON employees(last_name, salary);
martes, 12 de junio de 2012
Corregir nicks de usuarios con caracteres no válidos
Aquí podeis observar como arreglar la típica tabla de usuarios en la que se se han introducidos nicks con caracteres extraños que luego dan problemas al hacer login.
La tabla principal que almacena los usuarios es USUARIO
La tabla aux_usuario almacena los usuarios con caracteres no admitidos
La tabla aux_usuario_bueno almacena los usuarios que realizada la traducción
de un caracter incorrecto a otro, no supone nick duplicado en la tabla principal
-- Borramos la tabla de apoyo si existe
drop table if exists aux_usuario;
-- Rellenamos la tabla con los usuarios problemáticos
create table aux_usuario as select * from usuario where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________');
-- Actualizamos el nick substituyendo los caracteres problematicos
update aux_usuario set nick=translate(nick,'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________') where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________') ;
-- Creamos una tabla excluyendo los duplicados
drop table if exists aux_usuario_bueno;
create table aux_usuario_bueno as select * from aux_usuario aux where aux.nick not in ( select nick from usuario) and aux.nick not in ( select nick from (
-- Actualizamos la tabla principal
update usuario set nick=aux_usuario_bueno.nick from aux_usuario_bueno where aux_usuario_bueno.id=usuario.id ; ===================================================================== --- PARA LOS DUPLICADOS
--- Los usuarios que no al ser traducido el nick colisiona con otro
--- existente le añadimos el identificador para hacerlo único.
--- En el caso de nombres mayores de 10 caracteres añadimos
--- el identificador por segunda vez. ===================================================================== -- Borramos la tabla de apoyo si existe
drop table if exists aux_usuario2;
-- Rellenamos la tabla con los usuarios problemáticos create table aux_usuario2 as select * from usuario where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________');
update aux_usuario2 set nick=translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________') where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________');
update aux_usuario2 set nick=id::varchar where length(nick) >=10; update aux_usuario2 set nick=nick||id::varchar where length(nick) <10;
update usuario set nick=aux_usuario2.nick from aux_usuario2 where aux_usuario2.id=usuario.id ;
-- Borramos la tabla de apoyo si existe
drop table if exists aux_usuario;
-- Rellenamos la tabla con los usuarios problemáticos
create table aux_usuario as select * from usuario where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________');
-- Actualizamos el nick substituyendo los caracteres problematicos
update aux_usuario set nick=translate(nick,'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________') where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________') ;
-- Creamos una tabla excluyendo los duplicados
drop table if exists aux_usuario_bueno;
create table aux_usuario_bueno as select * from aux_usuario aux where aux.nick not in ( select nick from usuario) and aux.nick not in ( select nick from (
-- Actualizamos la tabla principal
update usuario set nick=aux_usuario_bueno.nick from aux_usuario_bueno where aux_usuario_bueno.id=usuario.id ; ===================================================================== --- PARA LOS DUPLICADOS
--- Los usuarios que no al ser traducido el nick colisiona con otro
--- existente le añadimos el identificador para hacerlo único.
--- En el caso de nombres mayores de 10 caracteres añadimos
--- el identificador por segunda vez. ===================================================================== -- Borramos la tabla de apoyo si existe
drop table if exists aux_usuario2;
-- Rellenamos la tabla con los usuarios problemáticos create table aux_usuario2 as select * from usuario where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________');
update aux_usuario2 set nick=translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________') where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________');
update aux_usuario2 set nick=id::varchar where length(nick) >=10; update aux_usuario2 set nick=nick||id::varchar where length(nick) <10;
update usuario set nick=aux_usuario2.nick from aux_usuario2 where aux_usuario2.id=usuario.id ;
martes, 7 de febrero de 2012
Vacuum de tablas con muchas actualizaciones
Si revisando los logs de tu Base de datos postgres, te encuentras a menudo con la siguiente entrada:
2012-02-06 18:44:20 CETLOG: vacuum automático de la tabla «esquema.tabla»: recorridos de índice: 1Esto se debe a que esa tabla tiene muchas inserciones o updates y constantemente tiene que aprovisionar nuevas páginas, el proceso de vacuum autoático se lanza para liberar las páginas inservibles, etc. Para solucionar este problema lo mejor es indicar un Factor de relleno (Fillfactor) para la tabla. Este factor representa cuanto espacio se deja en una página para el alojamiento de nuevas inserciones o actualizaciones de tuplas de esa página. Por defecto este valor es 100, por lo que todas la actualizaciones/inserciones irán a páginas nuevas y no se modificará la página actual. Para empezar un buen valor es 80, pero si la tabla tiene un ínidice muy alto de actualización tendrás que probar con valores por debajo de 50. Despues de hacer la modificación, no tendrá efecto hasta que hagas un Vacuum full (Ojo esta operación bloqueará la tabla, por lo que es mejor que no haya conexiones a la base de datos):
ALTER TABLE rankings.ranking SET ( fillfactor = 80 ); VACUUM FULL rankings.ranking;Este factor también afecta a los índices que sean modificados con mucha frecuencia, la sintaxis para modificarlos es:
ALTER INDEX "tabla_idx" ON "esquema"."tabla" USING btree ("campo_a_indexar"); DROP INDEX.tabla_idx; CREATE INDEX "tabla_idx" ON "esquema"."tabla" USING btree ("campo_a_indexar") WITH (fillfactor = 80);Referencias: http://www.postgresql.org/docs/9.0/static/sql-createtable.html
lunes, 6 de febrero de 2012
Update basado en select
Si un día necesitas guardar algunos campos de una tabla antes de que la tabla sea machacada por un proceso, puedes guardar los datos en una tabla auxiliar:
drop table if exists aux_coleccion; create table aux_coleccion as select * from coleccion;Despues de que hayamos guardado una copia de la tabla podemos machacar su contenido. Ahora podemos recuperar el contenido original de los campos que queramos:
update coleccion set precio=a.precio,nivel=a.nivel,puntos=a.puntos,estado=a.estado, nombre=a.nombre,descripcion=a.descripcion, from aux_coleccion a where a.id=coleccion.id;
Extraer permisos de un usuario
Si tienes una base de datos con un usuario y quieres copiar sus permisos en otro lugar, puedes utilizar la siguiente consulta, el resultado puedes ejecutarlo en otro lugar:
select 'grant ' || privilege_type || ' on table ' || table_schema || '.' || table_name || ' to ' || grantee || ';' from information_schema.role_table_grants where grantee='';
viernes, 26 de febrero de 2010
Convertir UTF8 a ASCII
En el siglo XXI, todos los sistemas operativos modernos soportan caracteres multibyte (UTF8, UTF16...), las bases de datos es buena idea crearlas también con un juego de caracteres que posteriormente pueda albergar textos en cualquier idioma.
En el caso de Postgres lo ideal es crear la base de datos así:
CREATE DATABASE mi_bbdd
WITH OWNER = yo_mismo
ENCODING = 'UTF8'
LC_COLLATE = 'es_ES.UTF-8'
LC_CTYPE = 'es_ES.UTF-8';
Una vez guardados los datos podemos recuperarlos con distintos juegos de caracteres, cambiando la variable de entorno client_encoding. Por ejemplo dentro de psql si quieres saber que client_encoding tienes actualmente teclea:
mi_bbdd=> show client_encoding;
client_encoding
-----------------
UTF8
(1 fila)
Si queremos forzar la codificación a otro juego de caracteres, como por ejemplo "latin1" usariamos set client_encoding:
mi_bbdd=>set client_encoding='latin1';
SET
mi_bbdd=>show client_encoding;
client_encoding
-----------------
latin1
(1 fila)
Todo esto está muy bien, porque a veces tienes que extraer datos de la base de datos para incorporarlos a otras bases de datos o aplicaciones.
Imaginaos que tenéis que enviar datos a un sistema muy antiguo, que no soporta la codificación utf8, yo intenté infructuosamente extraerlos a través de una consulta programada en un script bash, usando client_encoding.
Por si os encontráis en la misma situación, os recomiendo que no hagais client_encoding en las consultas y que las hagais en UTF8 de forma normal. Cuando tengais los ficheros generados, entonces aplicar la conversión de juegos de caracteres usando iconv, una función de Linux, que funciona del siguiente modo:
iconv -f UTF-8 -t ISO_8859-1 fichero_entrada -o fichero_salida
Espero que os haya resultado de ayuda.
Suscribirse a:
Entradas (Atom)