pgsql tips

Короткие и полезные заметки по работе с PostgreSQL

другой супер-юзер (не postgres)
Иногда требуется поменять пользователя 'postgres', например, для работы с отладочной версией постгреса на той же машине, где работает рабочая версия. При этом минимизируется вероятность что-нибудь напортить. Скрипт, которые я использую для установки отладочного окружения:
#!/bin/sh
# set environment for CVS postgresql
PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin
PGROOT=/usr/local/pgsql-dev
export PATH=$PGROOT/bin:$PATH
export PGDATA=$PGROOT/data
export PGLIB=$PGROOT/bin/lib
export MANPATH=$PGROOT/man:$MANPATH
export PGPORT=5434
export PS1='pg-dev@\h:\w\$ '

Здесь новая версия установлена в /usr/local/pgsql-dev, в postgresql.conf задан порт 5433. Логично направить лог в другой файл.

Если вы устанавливаете базу с нуля, то для работы из под пользователя 'pg' надо использовать опцию '-U':

initdb -U pg

Если вы решили поменять пользователя на ходу, то все усложняется. Я отработал следующую процедуру:

update pg_shadow set usename='pg' where usename='postgres';

Сообщения по-русски

Глобальные переменные в pl/perl процедурах
-- local to a session so you don't have to worry about the counters
-- interfering. If you need two counters in a session, just execute
-- reset_counter().

CREATE OR REPLACE FUNCTION reset_counter() RETURNS INT AS $$
$_SHARED{counter} = 0;
return 0;
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION counter() RETURNS INT AS $$
return $_SHARED{counter}++;
$$ LANGUAGE plperl;


unix timestamp to Pg timestamp
test=# SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 1109796233 * INTERVAL '1 second';
      ?column?       
---------------------
 2005-03-02 20:43:53
(1 row)

test=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1109796233 * INTERVAL '1 second';
        ?column?        
------------------------
 2005-03-02 23:43:53+03
(1 row)

CREATE OR REPLACE FUNCTION ts2int(timestamp without time zone) RETURNS int AS
$$
select extract('epoch' from $1)::integer;
$$ LANGUAGE SQL STRICT STABLE;

CREATE OR REPLACE FUNCTION int2ts(integer) RETURNS timestamp AS
$$
SELECT ( TIMESTAMP WITH TIME ZONE 'epoch' + $1 * INTERVAL '1second')::timestamp without time zone;
$$ LANGUAGE SQL STRICT STABLE;

STRICT STABLE because they depend on the local timezone setting. have you considered allowing the numeric values to be float8 instead of integer? There doesn't seem any reason to disallow sub-second precision. (Tom)

Schema size
I use (http://www.pgsql.ru/db/mw/msg.html?mid=2047341)
SELECT nspname,
sum(relpages * cast( 8192 AS bigint )) as "table size",
sum( ( select sum(relpages)
      from pg_class i, pg_index idx
      where i.oid = idx.indexrelid
      and t.oid=idx.indrelid ) ) * cast( 8192 AS bigint ) as "index size",
sum ( relpages * cast( 8192 AS bigint ) + ( select sum(relpages)
      from pg_class i, pg_index idx
      where i.oid = idx.indexrelid
      and t.oid=idx.indrelid ) * cast( 8192 AS bigint ) ) as "size"
FROM pg_class t, pg_namespace
WHERE relnamespace = pg_namespace.oid
and pg_namespace.nspname not like 'pg_%'
and pg_namespace.nspname != 'information_schema'
and relkind = 'r' group by nspname;

to sum over all tables/indices of a schema. Note that the input for the sum is relpages in pg_class and this value is only updated by VACUUM, ANALYZE und CREATE INDEX. So you should analyze all tables before you execute the statement.

Timestamp with/without time zone
See http://www.pgsql.ru/db/mw/msg.html?mid=2047498 for interesting thread
 "timestamp with time zone" and "timestamp without time zone" have _very_ different semantics.

One way to look at it is that "timestamp with time zone" designates a
specific instant in absolute time (past or future). It is therefore the
correct type to use for recording when something happened. In contrast,
"timestamp without time zone" designates a point on the calendar, which
has a different meaning according to where you are, and when. So the
latter type crops up in some cases in calendar applications, and also in
input/output conversions, but it's more often than not the _wrong_ type
to use for storage, since the meaning changes with the timezone (and data
_does_ get moved across timezones, whether due to physical relocation or
other factors).

Unix epoch times have the same semantics as "timestamp with time zone".


Временно отключить тригеры для ускорения закачки БД
The reltriggers field in the pg_class table contains the number of triggers active for each table. It can be set to 0 the disable the triggers, but will need to be reset to the proper number of triggers to have them re-enabled.
	UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname"='tablename';
	UPDATE pg_class SET reltriggers = (
	   SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid)
	   WHERE relname = 'table name';                          

Как восстановить template0
> I made a mistake, I droped the template0 database...
> How can I recreate it?

Do you still have template1?  If so, connect in single user mode, enter:
postgres template1
create database template0 with template template1
update pg_database set datallowconn = false where datname='template0';

Use CTRL-D to exit.

Alvaro's notice:

I think it'd be wise to issue a VACUUM FULL; VACUUM FREEZE in the newly
created template0 before disallowing connections to it.

Как почистить template1 ?
Иной раз template1 "мусориться" всякими разными объектами (например, разными уже устаревшими и неиспользуемыми функциями) и хочется восстановить ее в первозданном виде. При этом надо помнить, что если в template1 еще есть полезные объекты, которые используются, то надо сначала спасти их. Если вы уверены, что ничего полезного там нет из добавленного вами, то предлагается следующий путь:
  =# UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';
  =# DROP DATABASE template1;
  =# CREATE DATABASE template1 WITH template template0;
  =# UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';

Вытащить тело функции из системного каталога

Проще всего это сделать в psql: \df+ function_name.

   CREATE OR REPLACE VIEW funcsource as
   SELECT '--\012create or replace function ' ||
      n.nspname::text || '.'::text || p.proname::text  ||
      '('::text || oidvectortypes(p.proargtypes) || ')\012'::text ||
      'returns ' || t.typname || ' as \'\012' ||
      p.prosrc || '  \'\012' || 
      'language \'' || l.lanname || ' \';\012' as func_source,
      proname as function, nspname as schema, t.typname as rettype,
      oidvectortypes(p.proargtypes) as args, l.lanname as language
   FROM pg_proc p, pg_type t, pg_namespace n, pg_language l
   WHERE p.prorettype = t.oid AND p.pronamespace = n.oid AND p.prolang = l.oid
   AND l.lanname <> 'c' AND l.lanname <> 'internal'  ;

You could use this as follows to output all of the functions definitions to a file:

   psql -Atc "select func_source from funcsource;" > functions.out

Interesting additional qualifications can select out functions on return types, function name, schema, and argument types.

   AND rettype = 'bool'
   AND schema = 'public'
   AND function = 'myfunc'
   AND language = 'plpgsql'
   AND argtypes LIKE '%bool%'

Как добиться, чтобы в запросах LIKE 'что%' использовался индекс ?
Из-за сложности и многообразия locale в постгресе запрещено использовать индекс для запросов вида LIKE 'что%' для всех locale кроме 'C'. А что делать если хочется ? В 8.01 стало возможным использовать operator class [1] ! Ьы будем использовать varchar_pattern_ops, B-tree индекс в этом случае, будет строиться без использования collation правил из locale, а на основе сравнения буквы с буквой
test=#  \d ru_words 
  Table "public.ru_words"
 Column | Type | Modifiers 
--------+------+-----------
 w      | text | 
Indexes:
    "w_idx" btree (lower(w) varchar_pattern_ops)
test=# create index w_idx on ru_words (lower(w) varchar_pattern_ops);
CREATE INDEX
test=# vacuum analyze test;
test=# explain analyze select w from ru_words where lower(w) like 'что%';
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using w_idx on ru_words  (cost=0.01..5.02 rows=1 width=13) (actual time=0.058..0.117 rows=11 loops=1)
   Index Cond: ((lower(w) ~>=~ 'что'::character varying) AND (lower(w) ~<~ 'чтп'::character varying))
   Filter: (lower(w) ~~ 'что%'::text)
 Total runtime: 0.206 ms

Что нам и хотелось ! Забыл упомянуть, что всего в таблице около 300,000 слов.

sleep(real) function
Иногда требуется ждать определенное время, например, статистика в таблицах pg_stat* обновляется через 500ms после исполнения запроса. Для этого необходима функция sleep:
create or replace function sleep (float) returns time as '
        declare
                seconds alias for $1;
                later time;
                thetime time;
        begin
                thetime := timeofday()::timestamp;
                later := thetime + (seconds::text || '' seconds'')::interval;
                loop
                        if thetime >= later then
                                exit;
                        else
                                thetime := timeofday()::timestamp;
                        end if;
                end loop;
                return later;
        end;
' language plpgsql;

=# select sleep(0.5);
      sleep      
-----------------
 17:10:57.592827
discovery-test=# select pg_stat_reset();select count(*) from message_parts 
where message_parts.index_fts @@ '\'trident\'';
select sleep(0.6); 
select * from iostat where relname='message_parts';
 pg_stat_reset 
---------------
 t
(1 row)

 count 
-------
     2
(1 row)

      sleep      
-----------------
 17:12:12.661959
(1 row)

    relname    | heap_blk | idx_blk | seq_tup | idx_tup 
---------------+----------+---------+---------+---------
 message_parts | 0:794    | 0:2769  | 0:0     | 1:1390
(1 row)




Как использовать переменные в скриптах

Скрипт tt.sql выглядит так:

select * from iostat where relname=:a1;

Теперь я могу задавать значение переменной a1 при запуске psql

psql discovery-test -v a1=\'message_parts\' -f tt.sql 
    relname    | heap_blk  |  idx_blk   | seq_tup | idx_tup 
---------------+-----------+------------+---------+---------
 message_parts | 1996:2480 | 1871:11923 | 0:0     | 3:7887
(1 row)

Однако, в кавычках не происходит подстановка переменной,

\qecho
-- в двойных кавычках не работает
select * from iostat where relname=":a1";
-- и так тоже
select * from iostat where relname=':a1';

и это означает, что эти переменные нельзя использовать в теле функции, которую, например, генерит скрипт, так как тело функции заключено в скобках.

Как получить сводную таблицу ?
По-английски она называется pivot table. Суть ее проста:
test=# select * from t;
   c1   | c2 
--------+----
 oleg   |  5
 oleg   |  4
 teodor |  3
 teodor |  8

А хочется получить нечто вроде:

   c1   | concat 
--------+--------
 oleg   | 5 4
 teodor | 3 8

Задача решается с помощью агрегатной функции:

create or replace function concat(text, text) returns text
   immutable
   language sql
as '
select case when $1 = '''' then $2 else $1 || '' '' || $2 end
';

drop aggregate concat(text) cascade;
create aggregate concat
(
   basetype = text,
   stype = text,
   sfunc = concat,
   initcond = ''
);

После этого мы получаем результат:

test=# select c1, concat(c2) from t group by c1;
   c1   | concat 
--------+--------
 oleg   | 5 4
 teodor | 3 8
(2 rows)

Быстро посчитать max()

Если есть индекс по атрибуту, то посчитать max/min для этого атрибута можно быстро:

SELECT attr FROM foo ORDER BY col DESC LIMIT 1;

Замечание: Это рецепт не работает с NULL, поэтому надо писать

SELECT attr FROM foo where attr is not null ORDER BY col DESC LIMIT 1;

Замечание: В 8.1 ничего придумывать не надо !!!!

Как восстановить удаленные записи ?

Итак, это случилось, вы удалили записи и вы их хотите вернуть обратно. В этом случае я рекомендую поправить src/backend/utils/time/tqual.c - в конец HeapTupleSatisfiesSnapshot заменить

return false;

на

return true;

перекомпилить и запустить нового постмастера. В CVS версии для этого достаточно определить переменную MAKE_EXPIRED_TUPLES_VISIBLE, наприме в CFLAGS (В 8.2 этого уже нет и надо руками патчить. См. детали http://archives.postgresql.org/pgsql-patches/2005-02/msg00126.php)

Есть еще xlogviewer для просмотра WAL-логов.

– megera 2005-03-14 16:55 UTC