Hola Lista, a ver si alguien me ilumina con este expediente X... Tengo
varias BBDD en postgres 9.1.X y las necesito a migrar a 9.5 o 9.6 pero
detecto un problema que no consigo solucionar.
Resumiendo la demostración que hay mas abajo, busco un registro en UTC de
una fecha sin problemas y al cambiar de TIMEZONE si la tabla tiene indice
en PG 9.5 o PG 9.6 NO encuentra el Registro.
La BBDD tanto en 9.1, 9.5 o 9.6 las tengo en UTC, al igual que el sistema
operativo.
En una 9.1 las siguientes sentencias FUNCIONAN con el cambio de TIMEZONE.
psql -d demo -U demo
Demostramos que estamos en UTC
demo=> *show timezone;*
TimeZone
----------
UTC
(1 row)
*create table a ( fecha timestamptz, numero numeric(10), codigo varchar(18)
);*
*alter table a add constraint a_pk primary key (fecha,numero,codigo);*
*insert into a values (current_timestamp, 2010, '004694052615675402');*
demo=> *select * from a;*
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 13:37:52.287151+00 | 2010 | 004694052615675402
(1 row)
Localiza el registro sin problemas
demo=> *select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT
TIME ZONE '2016-10-18 13:37:52.287151+00' and numero = 2010 and codigo =
'004694052615675402';*
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 13:37:52.287151+00 | 2010 | 004694052615675402
Este es el explain y usa el indice.
demo=> *explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 13:37:52.287151+00' and numero =
2010 and codigo = '004694052615675402';*
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Index Scan using a_pk on a (cost=0.00..22.97 rows=1 width=46) (actual
time=0.015..0.017 rows=1 loops=1)
Index Cond: ((numero = 2010::numeric) AND ((codigo)::text =
'004694052615675402'::text))
Filter: (timezone('UTC'::text, fecha) = '2016-10-18
13:37:52.287151'::timestamp without time zone)
Total runtime: 0.049 ms
(4 rows)
Cambiamos el TIMEZONE y la MISMA consulta encuentra el registro con *ÉXITO* ya
que forzamos el TIMEZONE ( esto en PG9.5 - 9.6 NO FUNCIONA)
demo=> *SET TIMEZONE TO 'America/Santo_Domingo';*
select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT TIME
ZONE '2016-10-18 13:37:52.287151+00' and numero = 2010 and codigo =
'004694052615675402';
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 09:37:52.287151-04 | 2010 | 004694052615675402
demo=> *explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 13:37:52.287151+00' and numero =
2010 and codigo = '004694052615675402';*
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Index Scan using a_pk on a (cost=0.00..22.97 rows=1 width=46) (actual
time=0.018..0.021 rows=1 loops=1)
Index Cond: ((numero = 2010::numeric) AND ((codigo)::text =
'004694052615675402'::text))
Filter: (timezone('UTC'::text, fecha) = '2016-10-18
13:37:52.287151'::timestamp without time zone)
Total runtime: 0.045 ms
(4 rows)
Misma prueba en postgres 9.5 o 9.6
psql -d demo -U demo
Demostramos que estamos en UTC
demo=> *show timezone;*
TimeZone
----------
UTC
(1 row)
*create table a ( fecha timestamptz, numero numeric(10), codigo varchar(18)
);*
*alter table a add constraint a_pk primary key (fecha,numero,codigo);*
*insert into a values (current_timestamp, 2010, '004694052615675402');*
demo=> *select * from a;*
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 14:32:21.433333+00 | 2010 | 004694052615675402
localiza el registro sin problemas
demo=> *select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT
TIME ZONE '2016-10-18 14:32:21.433333+00' and numero = 2010 and codigo =
'004694052615675402';*
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 14:32:21.433333+00 | 2010 | 004694052615675402
(1 row)
Este es el explain y usa el indice.
demo=> *explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 14:32:21.433333+00' and numero =
2010 and codigo = '004694052615675402';*
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using a_pk on a (cost=0.15..8.17 rows=1 width=46) (actual
time=0.022..0.023 rows=1 loops=1)
Index Cond: ((fecha OPERATOR(pg_catalog.=) '2016-10-18
14:32:21.433333'::timestamp without time zone) AND (numero =
'2010'::numeric) AND (codigo = '004694052615675402'::text))
Heap Fetches: 1
Planning time: 0.135 ms
Execution time: 0.061 ms
(5 rows)
Cambiamos el TIMEZONE y la MISMA consulta encuentra el registro con *ÉXITO* ya
que forzamos el TIMEZONE ( esto en PG9.5 - 9.6 NO FUNCIONA)
demo=> *SET TIMEZONE TO 'America/Santo_Domingo';*
Dónde esta el registro ? No lo encuentra -- LA CULPA LA TIENE EL INDICE...
demo=> select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT
TIME ZONE '2016-10-18 14:32:21.433333+00' and numero = 2010 and codigo =
'004694052615675402';
fecha | numero | codigo
-------+--------+--------
(0 rows)
Como podemos ver usa la PK para NO Localizar el registro
demo=> explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 14:32:21.433333+00' and numero =
2010 and codigo = '004694052615675402';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using a_pk on a (cost=0.15..8.17 rows=1 width=46) (actual
time=0.013..0.013 rows=0 loops=1)
Index Cond: ((fecha OPERATOR(pg_catalog.=) '2016-10-18
14:32:21.433333'::timestamp without time zone) AND (numero =
'2010'::numeric) AND (codigo = '004694052615675402'::text))
Heap Fetches: 0
Planning time: 0.120 ms
Execution time: 0.041 ms
(5 rows)
Borramos el índice
demo=> alter table a drop constraint a_pk;
ALTER TABLE
Buscamos con EXITO sin INDICE
demo=> select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT
TIME ZONE '2016-10-18 14:32:21.433333+00' and to_varchar(numero) = '2010'
and codigo = '004694052615675402';
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 10:32:21.433333-04 | 2010 | 004694052615675402
(1 row)
Demostración que ahora lo localiza con un secuencial Scan
demo=> explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 14:32:21.433333+00' and
to_varchar(numero) = '2010' and codigo = '004694052615675402';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on a (cost=10000000000.00..10000000037.50 rows=1 width=46)
(actual time=0.018..0.020 rows=1 loops=1)
Filter: (((fecha)::timestamp without time zone = '2016-10-18
14:32:21.433333'::timestamp without time zone) AND ((codigo)::text =
'004694052615675402'::text) AND (((numero)::character varying(20))::text =
'2010'::text))
Planning time: 0.141 ms
Execution time: 0.048 ms
(4 rows)
Siento el tocho pero no doy con la solución. He probado reindexar ,
analizar estadísticas y nada, cuando usa el índice no encuentro el registro.
Muchas Gracias.
Alex.
