![[Home]](/~megera/oddmuse/zen3.gif)
#!/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';
-- 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;
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)
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 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".
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';
> 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.
=# 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%'
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 слов.
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';
и это означает, что эти переменные нельзя использовать в теле функции, которую, например, генерит скрипт, так как тело функции заключено в скобках.
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/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
