Postgres ideas: 2017

четверг, 28 декабря 2017 г.

Мониторинг использования, статистика

Взято с pgtune

select * from pg_stat_user_tables q order by q.seq_scan desc

select * from pg_statio_user_tables t order by t.heap_blks_read desc

Расширение CREATE EXTENSION pg_buffercache;

использование буферов объектами (таблицами, индексами, прочим):

SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;

relname |buffers |
--------------------|--------|
sl_user_access_role |35119 |
sl_user_access |18547 |
t_users |17817 |
msg_content |14132 |
sl_report_data |13379 |

объекты (таблицы и индексы) в кэше:

 SELECT c.relname, count(*) AS buffers,usagecount
 FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname,usagecount
ORDER BY c.relname,usagecount;

relname |buffers |usagecount |
---------------------------------|--------|-----------|
sl_user_access_role |35119 |5 |
sl_user_access |18546 |5 |
t_users |17817 |5 |
msg_content |14147 |5 |
sl_report_data |13396 |5 |
pk_sl_f_cons_rep |10942 |5 |
pk_sl_f_form_rep |10520 |5 |

Это запрос показывает какой процент общего буфера используют обьекты (таблицы и индексы) и на сколько процентов объекты находятся в самом кэше (буфере):

SELECT
 c.relname,
 pg_size_pretty(count(*) * 8192) as buffered,
 round(100.0 * count(*) /
 (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1)
 AS buffers_percent,
 round(100.0 * count(*) * 8192 / pg_table_size(c.oid),1)
 AS percent_of_relation
FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 20;

relname |buffered |buffers_percent |percent_of_relation |
---------------------------|---------|----------------|--------------------|
sl_user_access_role |274 MB |6.7 |100.0 |
sl_user_access |145 MB |3.5 |100.0 |
t_users |139 MB |3.4 |100.0 |
msg_content |111 MB |2.7 |100.0 |
sl_report_data |105 MB |2.6 |99.9 |
pk_sl_f_cons_rep |85 MB |2.1 |98.3 |
pk_sl_f_form_rep |82 MB |2.0 |98.5 |
mv_budgets_last_sum |82 MB |2.0 |100.0 |
sl_f_form_rep |40 MB |1.0 |99.9 |
sl_report_requisites |36 MB |0.9 |20.2 |

sl_f_cons_rep |39 MB |0.9 |99.9 |

Далее берем топ таблицу и смотрим в каких процедурах она используется и как, все ли индексы на месте.

select proname,prosrc from pg_proc where proname= your_function_name;

четверг, 30 ноября 2017 г.

Интервал выполнения и sleep

do $$
DECLARE
t NUMERIC;
BEGIN
select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

perform pg_sleep(2);

select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

end $$

-----------------

00000: t=1512065827.6849

00000: t=1512065829.6977

00000: t=1512065829.69793

среда, 11 октября 2017 г.

BAT file for backup postgres schema

@echo off
set PGPASSWORD=prm_salary
set PGUSER=prm_salary
set PGDATABASE=db_ris_mkrpk
set PGBIN="C:\Program Files\PostgreSQL\9.6\bin\"
set t=%date%_%time%
set d=%t:~10,4%%t:~7,2%%t:~4,2%_%t:~15,2%%t:~18,2%%t:~21,2%
set BACKUP_FILE="C:\dumps\prm_salary_%d%.dmp"
set FILELOG="C:\dumps\prm_salary_%d%.log"
(
echo Backup start %date% %time% file %BACKUP_FILE%
%PGBIN%"pg_dump" --schema=prm_salary --format=c --file=%BACKUP_FILE%
echo End of backup %date% %time%
)>> %FILELOG% 2>&1
forfiles /p "C:\dumps" /s /m *.log /D -5 /C "cmd /c del @path"
forfiles /p "C:\dumps" /s /m *.dmp /D -5 /C "cmd /c del @path"

среда, 20 сентября 2017 г.

Блокировки

-- запросы онлайн
SELECT * FROM pg_stat_activity order by client_addr;

-- блокировки по пидам
SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'sandbox' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid()

--3
SELECT locktype, relation::regclass,mode, transactionid AS tid,
virtualtransaction AS vtid,pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid=l.database WHERE (db.datname='cabinet' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();

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 a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted

select pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0

SELECT
COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
blockeda.query as blocked_query, blockedl.mode as blocked_mode,
blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
( (blockingl.transactionid=blockedl.transactionid) OR
(blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc;

SELECT blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
blockinga.pid AS blocking_pid, blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.transactionid=blockedl.transactionid
AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted AND blockinga.datname='cabinet';

понедельник, 4 сентября 2017 г.

Размер таблиц в postgres

см. прочие поля в таблице.

SELECT oid,
table_schema,
table_name,
total_bytes,
pg_size_pretty(total_bytes) AS humna_size
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a
-- where round(total_bytes/1024/1024) > 100 -- только если размер больше 100Мб
order by total_bytes desc

среда, 16 августа 2017 г.

Логирование удаления объектов в postgres

select inet_client_addr()

create table oz.important_table(id numeric);
insert into oz.important_table values(1);

select * from oz.important_table

create view oz.v_test as select * from oz.important_table;

drop view oz.v_test;

select * from oz.v_test

CREATE OR REPLACE FUNCTION public.trg_log_drop()
RETURNS event_trigger
LANGUAGE plpgsql
AS $function$
DECLARE
obj record;
BEGIN
FOR obj in SELECT * FROM pg_event_trigger_dropped_objects() LOOP
IF obj.object_type in ('table','view') THEN
RAISE NOTICE 'TABLE % DROPPED by transaction % (pre-commit)',
obj.object_identity, txid_current();
INSERT INTO public.droplog (tablename, dropxid,user_ip)
VALUES (obj.object_identity, txid_current(),inet_client_addr());
END IF;
END LOOP;
END;
$function$

CREATE EVENT TRIGGER table_drop_logger
ON sql_drop
WHEN TAG IN ('DROP TABLE','DROP VIEW')
EXECUTE PROCEDURE public.trg_log_drop()

CREATE TABLE public.droplog (
ts timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
tablename text NOT NULL,
dropxid bigint,
user_ip text
);

select * from public.droplog

drop table oz.important_table;

среда, 2 августа 2017 г.

Извлечение данных из 2-х курсоров, и возврат курсора

CREATE TABLE pkg_get_cabinet_element.tmp_get_button_tbl (
id numeric NULL,
"name" varchar(500) NULL,
is_edit int4 NULL,
param varchar NULL,
"action" varchar NULL,
dependences_list json NULL,
ico varchar(20) NULL,
action2 varchar(2000) NULL,
button_param varchar(4000) NULL,
identifier varchar(100) NULL
);

CREATE OR REPLACE FUNCTION pkg_get_cabinet_element.get_buttons_for_grades(in_user numeric, in_comment numeric, in_org numeric DEFAULT NULL::numeric)
RETURNS SETOF pkg_get_cabinet_element.tmp_get_button_tbl
LANGUAGE plpgsql
STABLE SECURITY DEFINER
AS $function$
declare
_cur_name1 varchar(64):= md5(clock_timestamp()::varchar);
_cur_name2 varchar(64):= md5(clock_timestamp()::varchar);
_rec pkg_get_cabinet_element.tmp_get_button_tbl%rowtype;
_rcur refcursor;
begin
SELECT _cur_name1
INTO _rcur
FROM pkg_get_cabinet_element.get_button(refcur => _cur_name1::REFCURSOR, in_user => in_user, in_tab => 963, in_sys => 450, in_org => in_org, in_comment => in_comment);
LOOP
FETCH _rcur INTO _rec;
EXIT WHEN NOT FOUND;
RETURN NEXT _rec;
raise notice '%','-1-';
END LOOP;
SELECT _cur_name2
INTO _rcur
FROM pkg_get_cabinet_element.get_button(refcur => _cur_name2::REFCURSOR, in_user => in_user, in_tab => 1563, in_sys => 490, in_org => in_org, in_comment => in_comment);
LOOP
FETCH _rcur INTO _rec;
EXIT WHEN NOT FOUND;
RETURN NEXT _rec;
raise notice '%','-2-';
END LOOP;
end;
$function$

-----------

select * from pkg_get_cabinet_element.get_buttons_for_grades(in_user=> 71389407,in_org=> 1001,in_comment=> 3869)

id |name |is_edit |param |action |dependences_list |ico |action2 |button_param |identifier |
-----|---------------------|--------|------|-------|-----------------------------------------------------------------------------------------------------|-------------|--------|-------------|-----------|
2306 |Изменить специалиста |1 | |save |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"10", "val":""}} } |icon_ui_redo |edit | |5.02.2 |
2305 |Принять в работу |1 | |save |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"3", "val":""}} } | | | |5.02.1 |

DO $$
declare
cur refcursor := 'qwe';
begin
open cur for
select * from pkg_get_cabinet_element.get_buttons_for_grades(in_user=> 71389407,in_org=> 1001,in_comment=> 3869);
END
$$

fetch all in qwe;

id |name |is_edit |param |action |dependences_list |ico |action2 |button_param |identifier |
-----|---------------------|--------|------|-------|-----------------------------------------------------------------------------------------------------|-------------|--------|-------------|-----------|
2306 |Изменить специалиста |1 | |save |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"10", "val":""}} } |icon_ui_redo |edit | |5.02.2 |
2305 |Принять в работу |1 | |save |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"3", "val":""}} } | | | |5.02.1 |

четверг, 20 июля 2017 г.

Массовое изменение свойств функций на VOLATILE

DO $$
declare
_rec record;
_args text;
s text;
begin
for _rec in (
--113
SELECT
n.nspname AS schema
,proname AS fname
--,proargnames AS args
--,t.typname AS return_type
--,d.description
--,pg_get_functiondef(p.oid) as definition
,sum(1) over(partition by n.nspname||'.'||proname) as rn
FROM pg_proc p
JOIN pg_type t
ON p.prorettype = t.oid
LEFT OUTER
JOIN pg_description d
ON p.oid = d.objoid
LEFT OUTER
JOIN pg_namespace n
ON n.oid = p.pronamespace
WHERE n.nspname not in ('pg_catalog','pkg_process','pkg_user_management') and
provolatile='s'
and proname not in ('getphotoids','fnc_get_dict_element','f_increment_num')
) loop
SELECT pg_get_function_identity_arguments((_rec.schema||'.'||_rec.fname)::regproc) into _args;
s:='alter function '||_rec.schema||'.'||_rec.fname||'('||_args||') VOLATILE;';
raise notice '%',s;
execute s;
end loop;
END
$$

alter function pkg_get_cabinet.get_zas_mrg_guest_list(INOUT refcur refcursor, in_comment_id integer) VOLATILE;

четверг, 6 июля 2017 г.

postgres стек ошибки с указанием строки

do language plpgsql $$
declare
l_message_text text;
l_excp_context text;
begin

.... код

exception
when others then
GET STACKED DIAGNOSTICS
l_message_text = MESSAGE_TEXT,
l_excp_context := PG_EXCEPTION_CONTEXT;
raise notice 'message text: >> % <<', l_message_text;
raise notice 'exception stack: >> % <<', l_excp_context;
end;
$$

вторник, 20 июня 2017 г.

Пример создания джоба в postgres

1) установка pgagent

yum search pgagent
yum install pgagent_96

2) В каталоге /etc/init.d/ создаем файл и прописываем его в автозагрузку

#!/bin/bash
#
# /etc/rc.d/init.d/pgagent
#
# Manages the pgagent daemon
#
# chkconfig: - 65 35
# description: PgAgent PostgreSQL Job Service
# processname: pgagent
. /etc/init.d/functions
RETVAL=0
prog="PgAgent"
start() {
echo -n $"Starting $prog: "
daemon "/usr/bin/pgagent_95 -s /var/log/pgagent_95.log hostaddr=your_ip dbname=cuser=postgres password=YYYYYYYY"
RETVAL=$?
echo
}
stop() {
echo -n $"Stopping $prog: "
killproc /usr/bin/pgagent_95
RETVAL=$?
echo
}
case "$1" in
start)
start
;;
stop)
stop
;;
reload|restart)
stop
start
RETVAL=$?
;;
status)
status /usr/bin/pgagent_95
RETVAL=$?
;;
*)
echo $"Usage: $0 {start|stop|restart|reload|status}"
exit 1
esac
exit $RETVAL

3) Запуск /etc/init.d/pgagent_95 start

4) Создание расписания/запуск функции каждую минуту (расписание аналогично crontab)

DO $$
DECLARE
jid integer;
scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
1::integer, 'check events emails'::text, ''::text, ''::text, true
) RETURNING jobid INTO jid;

-- Steps
-- Inserting a step (jobid: NULL)
INSERT INTO pgagent.pga_jobstep (
jstjobid, jstname, jstenabled, jstkind,
jstconnstr, jstdbname, jstonerror,
jstcode, jstdesc
) VALUES (
jid, 'check events emails'::text, true, 's'::character(1),
''::text, 'cabinet'::name, 'f'::character(1),
'begin;
select msg.fnc_job_check_events_letters();'::text, ''::text
) ;

-- Schedules
-- Inserting a schedule
INSERT INTO pgagent.pga_schedule(
jscjobid, jscname, jscdesc, jscenabled,
jscstart, jscend, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
) VALUES (
jid, 'del'::text, ''::text, true,
'2017-04-07 16:44:15+05'::timestamp with time zone, '2057-04-07 16:30:30+05'::timestamp with time zone,
-- Minutes
ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
-- Hours
ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
-- Week days
ARRAY[true, true, true, true, true, true, true]::boolean[],
-- Month days
ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
-- Months
ARRAY[true, true, true, true, true, true, true, true, true, true, true, true]::boolean[]
) RETURNING jscid INTO scid;
END
$$;

------------------------
5) 2 функции и тестовая таблица.

CREATE OR REPLACE FUNCTION msg.fnc_job_check_events_letters()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
declare
v_conn_str text;
v_conn_name varchar(64) := md5(clock_timestamp()::varchar);
v_query text;
v_res numeric;
begin

select conn_str
into v_conn_str
from public.connect_data;

v_query := 'SELECT msg.fnc_exec_check_events_letters()';

PERFORM public.dblink_connect(v_conn_name,v_conn_str);
PERFORM public.dblink_exec(v_conn_name, 'SET datestyle = ISO, DMY;');

SELECT *
into v_res
FROM public.dblink(v_conn_name, v_query) AS p(res numeric);

end;
$function$

CREATE OR REPLACE FUNCTION msg.fnc_exec_check_events_letters()
RETURNS numeric
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
declare
begin
insert into msg.test_jobs(id) values(1);
return 1;
end;
$function$

create table msg.test_jobs(
date_inserted timestamp NOT NULL DEFAULT 'now'::text::timestamp without time zone,
id numeric);

четверг, 1 июня 2017 г.

Примеры работы с комплексными типами (коллекции) пополнение/циклы

CREATE TYPE msg.entity_object AS (
field_id numeric,
field_value text
);

DO $$
declare
entity_Before_arr msg.entity_object[];
_rec msg.entity_object%rowtype;
_record record;
begin

/*
--1
entity_Before_arr := (SELECT ARRAY(SELECT (cf.field_id,cf.field_value)
FROM er.ref_comment_field cf
WHERE cf.comment_id = 986)
);
*/
--2
SELECT INTO entity_Before_arr
ARRAY(SELECT (cf.field_id,cf.field_value)
FROM er.ref_comment_field cf
WHERE cf.comment_id = 986);
/*
--1
FOREACH _rec IN ARRAY entity_Before_arr
LOOP
RAISE NOTICE 'field_id: % - %', _rec.field_id,_rec.field_value;
END LOOP;
*/

for _record in select unnest(entity_Before_arr) as v loop
RAISE NOTICE '%-%', (_record.v).field_id, (_record.v).field_value ;
end loop;

END
$$

-----------------------

Формируем массив пар полей типа msg.entity_object, для передачи в функции и обработки с помощью SQL.

SELECT ARRAY(
SELECT cast((cf.field_id,cf.field_value) as msg.entity_object) FROM er.ref_comment_field cf WHERE cf.comment_id = 986
) as v

Разворачивание полей:

select (d.val).field_id,
(d.val).field_value
from(
select unnest(entB.v) as val
from (
SELECT ARRAY(
SELECT cast((cf.field_id,cf.field_value) as msg.entity_object) FROM er.ref_comment_field cf WHERE cf.comment_id = 986
) as v
) entB
) d

Или компактнее

select (entB.v).field_id,
(entB.v).field_value
from (
SELECT unnest(ARRAY(
SELECT cast((cf.field_id,cf.field_value) as msg.entity_object) FROM er.ref_comment_field cf WHERE cf.comment_id = 986
)) as v
) entB

===========================================================

Ещё один вид/вариант обработки, собрали массив, передали дальше, проверили что можно его обработать и через SQL и через plPgSQL

DO $$
declare
entity_Before_arr msg.entity_object[];
_rec msg.entity_object%rowtype;
_record record;
begin

--2
SELECT INTO entity_Before_arr
ARRAY(
SELECT cast((cf.field_id,cf.field_value) as msg.entity_object)
FROM er.ref_comment_field cf
WHERE cf.comment_id = 986
);

for _record in select (entB.v).field_id,
(entB.v).field_value
from (
SELECT unnest(entity_Before_arr) as v
) entB
loop
RAISE NOTICE '%-%', _record.field_id, _record.field_value ;
end loop;

END
$$

среда, 17 мая 2017 г.

Поменять тип полей, с очисткой

DO $$
DECLARE
_rec record;
_tag_name varchar(32);
begin
_tag_name := 'tag_678';
for _rec in
-----------------------------
SELECT attrelid::regclass as tablename,
attname,
(select typname from pg_type where oid=atttypid) as col_type
FROM pg_attribute
WHERE --attrelid = 'er.editor_comment_tmp'::regclass AND
attnum > 0
and attname = _tag_name
AND NOT attisdropped
and (select typname from pg_type where oid=atttypid)='varchar'
-----------------------------
loop
begin
execute 'update '||_rec.tablename||' set '||_tag_name||'=null';
execute 'alter table '||_rec.tablename||' alter column '||_tag_name||' type numeric using '||_tag_name||'::numeric';
raise notice 'OK: table - %',_rec.tablename;
exception
when others then raise notice 'ERROR: table - %',_rec.tablename;
end;
end loop;
END
$$

воскресенье, 14 мая 2017 г.

Обвязка функцией процедуры, возвращающей курсор. С выводом данных.

Есть процедура, возвращающая курсор в виде inout параметра.

BEGIN;
SELECT * FROM pkg_get_comm.get_comm_with_fields(refcur => 'qwe', in_user=> 111111111,in_tab=> 423,in_sys=> 330,in_comment=> 556);

FETCH ALL IN qwe;

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

1)
Руками явно достаем запрос из процедуры, выполняем его создав пустую таблицу методом ctas. Эта таблица не содержит данные и используется только для описания типа. Можно было бы создавать не таблицу, а явно тип в бд.

2) Создаем функцию

CREATE OR REPLACE FUNCTION pkg_get_comm.get_comm_with_fields_part(in_user numeric, in_tab numeric, in_sys numeric, in_comment numeric, in_is_debug numeric DEFAULT 0, in_theme numeric DEFAULT NULL::numeric)
RETURNS SETOF pkg_get_comm.t_type_gcwf
LANGUAGE plpgsql
STABLE SECURITY DEFINER
AS $function$
declare
_cur_name varchar(64):= md5(clock_timestamp()::varchar);
_rec pkg_get_comm.t_type_gcwf%rowtype;
_rcur refcursor;
begin
SELECT _cur_name
INTO _rcur
FROM pkg_get_comm.get_comm_with_fields(_cur_name::REFCURSOR,
in_user => in_user,
in_tab => in_tab,
in_sys => in_sys,
in_comment => in_comment);
LOOP
FETCH _rcur INTO _rec;
EXIT WHEN NOT FOUND;
RETURN NEXT _rec;
END LOOP;
end;
$function$

3) Пример использования. можно фильтровать по полям, запрашивать только нужные поля.

select * from pkg_get_comm.get_comm_with_fields_part(in_user=> 111111111,in_tab=> 423,in_sys=> 330,in_comment=> 556)

пятница, 7 апреля 2017 г.

Работа с курсорами. возвращаемыми функциями, autocommit, явное начало транзакции

Пусть есть процедура возвращающая курсор через INOUT параметр:

drop function p_return_cursor(inout refcur refcursor);

create or replace function p_return_cursor(inout refcur refcursor)
returns refcursor -- тут именно refcursor, т.к. он есть в параметрах с типом OUT
LANGUAGE plpgsql
as $$
begin
open refcur for select generate_series(1,10,1) as ID;
end
$$

Как в postgres в SQL вызвать её и получить набор данных.
1)
select p_return_cursor('refcur');

результат - '-- refcursor' ничего!

Произошёл вызов процедуры, был открыт курсор, далее курсор был вернут вызывающей среде (ссылка на курсор - указатель на первую запись) и тут же выполнен autocommit - при этом курсор сразу закрылся и более не существует. Поэтому в вызывающей среде мы ни чего не имеем.

Комментарий:
Important Note: The cursor remains open until the end of transaction, and since PostgreSQL works in auto-commit mode by default, the cursor is closed immediately after the procedure call, so it is not available to the caller.

To work with cursors you have to start a transaction (turn auto-commit off).

Для явного старта транзакции можно использовать BEGIN;

BEGIN initiates a transaction block, that is, all statements after a BEGIN command will be executed in a single transaction until an explicit COMMIT or ROLLBACK is given.

By default (without BEGIN), PostgreSQL executes transactions in "autocommit" mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

Поэтому:
1)
begin;
select p_return_cursor('refcur');

и дальше мы находимся в запущенной транзакции, Postgres ждёт от нас explicit COMMIT or ROLLBACK. :) Тут можно посидеть, подумать, а нужны ли нам данные и если до, то можно извлечь их из открытого курсора.

fetch all in refcur;

1
2
3
4
5
6
7
8
9

10

Тут же выполняем повторно и уже ни чего не получаем, но не генерируется и ошибка, т.к. просто указать курсора находится в конце.

Можно передвинуть его на первую запись и опять выполнить извлечение.

MOVE first from refcur;
fetch all in refcur;

2
3
4
5
6
7
8
9

10

Почему с 2, а не с 1 пока для меня загадка!

Так же явно начать транзакцию можно так:

START TRANSACTION ISOLATION LEVEL READ COMMITTED READ only;

select p_return_cursor('refcur');

fetch all in refcur;

Пример функции возвращающей набор данных, параметризованное представление

create or replace function f_get_data()
RETURNS TABLE(id integer)
LANGUAGE plpgsql
as $$
declare
begin
return query
select generate_series(1,10,1) as ID;
end
$$

Изначально известен возвращаемый тип данных TABLE(id integer)
по всей видимости в момент выполнения запускается запрос и возвращается результат.

Примеры вызова: можно использовать как таблицу в простом SQL.

select f_get_data()

select * from f_get_data() t where t.id>=5

Если запрос в функции динамический, то можно использовать:

return query execute 'select generate_series(1,10,1) as ID';

в том числе начиная с 8.4 можно использовать using

RETURN QUERY EXECUTE sql USING var1, var2;

четверг, 6 апреля 2017 г.

Загрузка из csv в таблицу

Создаем таблицу, копируем файл в ОС

create table cabinet.buff_kpgz(
id integer,
date_time_create character varying(128),
date_time_chnage character varying(128),
code_kpgz character varying(128),
name character varying(1000),
prnt_kpgz character varying(1000),
s_desc character varying(4000),
okpd character varying(128),
okpd2 character varying(128),
code_mer character varying(128),
is_need_be_ref character varying(128),
is_stand_position character varying(128),
is_gov_expert character varying(128),
to_repair character varying(128),
obj_type character varying(128),
project character varying(128)
);

select * from cabinet.buff_kpgz

в Dbeaver
COPY cabinet.buff_kpgz FROM '/home/kpgz.csv' DELIMITER ';' CSV HEADER encoding 'windows-1251';

среда, 5 апреля 2017 г.

привязка сиквенса к полю таблицы

CREATE SEQUENCE cabinet.s_ref_theme_process
INCREMENT BY 2
MINVALUE 2
NO MAXVALUE
START WITH 2;

alter table cabinet.ref_theme_process alter column id set default nextval('cabinet.s_ref_theme_process');

вторник, 4 апреля 2017 г.

Блокировки/Висящие сессии, отключение сессии, активные сессии

Блокировки
select
lock.locktype,
lock.relation::regclass,
lock.mode,
lock.transactionid as tid,
lock.virtualtransaction as vtid,
lock.pid,
lock.granted--, pg_terminate_backend(lock.pid)
from pg_catalog.pg_locks lock
left join pg_catalog.pg_database db
on db.oid = lock.database
where (db.datname = 'sandbox' or db.datname is null)
and not lock.pid = pg_backend_pid()
order by lock.pid;

отключение сессии
select pg_terminate_backend(24736) сюда pid

активные сессии
select * from pg_stat_activity

----------------------------------------------

Проход в цикле

DO $$
DECLARE
_rec record;
_res boolean;
BEGIN
for _rec in
select distinct
lock.pid
from pg_catalog.pg_locks lock
left join pg_catalog.pg_database db
on db.oid = lock.database
where (db.datname = 'sandbox' or db.datname is null)
and not lock.pid = pg_backend_pid()
order by lock.pid loop
_res := pg_terminate_backend(_rec.pid);
end loop;
END
$$;

№2
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'cabinet';

пятница, 31 марта 2017 г.

Пример передачи в функцию 2-х коллекций и возврат 2-х курсоров

CREATE OR REPLACE FUNCTION study.test_collection_param(
INOUT refcur1 refcursor,
INOUT refcur2 refcursor,
in_coll_n int[],
in_coll_s varchar(50)[] default '{}'::varchar(50)[]
)
RETURNS record
LANGUAGE plpgsql
STABLE SECURITY DEFINER
AS $function$
begin
open refcur1 for
select d.id_dict,
sum(1) as cnt,
sum(val) as val
from study.dat d
where d.id_dict = ANY(in_coll_n)
group by d.id_dict;
open refcur2 for
select unnest(in_coll_s) as COL_NAME;
end;
$function$

commit;

begin;
select study.test_collection_param('rc1','rc2',ARRAY[1,2,3],ARRAY['x1','y2',' ','z3']);

FETCH ALL IN rc1;
1 17 916
2 17 921
3 17 905

FETCH ALL IN rc2;
COL_NAME
x1
y2

z3

Передача массива (коллекции чисел) на вход функции, использование в SQL

create table dat(
id_dict integer not null constraint fk_dat_pok references d_dict(id),
id_year integer not null constraint fk_dat_year references d_years(id),
val numeric,
constraint uk_data unique (id_dict,id_year)
);

CREATE OR REPLACE FUNCTION study.test_collection_param(
INOUT refcur refcursor,
in_coll_n int[]
)
RETURNS refcursor
LANGUAGE plpgsql
STABLE SECURITY DEFINER
AS $function$
begin
open refcur for
select d.id_dict,
sum(1) as cnt,
sum(val) as val
from study.dat d
where d.id_dict = ANY(in_coll_n)
group by d.id_dict;
end;
$function$

Пример вызова

begin;
select study.test_collection_param('rc',ARRAY[1,2,3]);

FETCH ALL IN rc;

Сравнение планов, при использовании коллекции в подзапросе:

Вариант 1.

explain analyze
select d.id_dict,d.val
from study.dat d
where d.id_dict = ANY(ARRAY[1,2,3])

План.

Bitmap Heap Scan on dat d (cost=13.23..23.93 rows=51 width=8) (actual time=0.018..0.033 rows=51 loops=1)
Recheck Cond: (id_dict = ANY ('{1,2,3}'::integer[]))
Heap Blocks: exact=9
-> Bitmap Index Scan on uk_data (cost=0.00..13.21 rows=51 width=0) (actual time=0.014..0.014 rows=51 loops=1)
Index Cond: (id_dict = ANY ('{1,2,3}'::integer[]))
Planning time: 0.050 ms
Execution time: 0.052 ms

Execution time: 0.088 ms
Execution time: 0.061 ms
Execution time: 0.056 ms

Вариант 2.

explain analyze
select d.id_dict,d.val
from study.dat d
where d.id_dict in (select unnest(array[1,2,3]))

План.

Hash Semi Join (cost=2.76..53.13 rows=1700 width=8) (actual time=0.018..0.244 rows=51 loops=1)
Hash Cond: (d.id_dict = (unnest('{1,2,3}'::integer[])))
-> Seq Scan on dat d (cost=0.00..27.00 rows=1700 width=8) (actual time=0.006..0.103 rows=1700 loops=1)
-> Hash (cost=1.51..1.51 rows=100 width=4) (actual time=0.007..0.007 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.004..0.005 rows=3 loops=1)
Planning time: 0.096 ms
Execution time: 0.261 ms

Execution time: 0.296 ms
Execution time: 0.266 ms
Execution time: 0.270 ms

среда, 29 марта 2017 г.

Рекурсивные запросы

Задача ставится следующим образом:

Есть дерево определяемое через ID,PID и поле значения VAL.

Необходимо просуммировать всё вверх от листовых узлов до корней. Причем промежуточные узлы изначально могут сами иметь значения отличные от суммы дочерних.

Создадим простое дерево.

drop table testh;

CREATE TABLE testh(
id integer constraint pk_testh primary key,
pid integer constraint fk_testh_self references testh(id),
val real,
lvl integer
);

И заполним его деревом (3 уровня, 5 узлов, 1 корень).

insert into testh
select unnest(array[1, 4,5, 10,11]) as ID,
unnest(array[NULL, 1,1, 4,4]) as PID,
unnest(array[NULL, 10,1, 2,4]) as VAL,
unnest(array[null,null,null,null,null]::int[]) as LVL;

commit;

select ID,PID,VAL from testh

Выведем дерево через рекурсивный запрос, с расчетом уровня LVL.

-- [1] Вывод простого дерева, Иерархия

WITH RECURSIVE temp1(ID,PID,VAL,PATH,LEVEL) AS
(
SELECT T1.ID,T1.PID, T1.VAL, CAST (T1.ID AS VARCHAR (50)) as PATH, 1
FROM testh T1
WHERE T1.PID IS NULL
union
SELECT T2.ID, T2.PID, T2.VAL, CAST ( temp1.PATH ||'->'|| T2.ID AS VARCHAR(50)) ,LEVEL + 1
FROM testh T2 INNER JOIN temp1 on (temp1.ID = T2.PID)
)
select *
from temp1

Запрос выполняется в 4 шага:

1) Во "временную таблицу" temp1 попадает одна корневая запись, ID=1, LEVEL=1
(Это выполнение верхнего из 2-х UNION-ов).

2) Выполнение нижнего из UNION-ов возвращает 2 строки с ID=4,5. Эти 2 узла достаются из таблицы testh и помещаются во временную таблицу temp1. На данный момент она содержит 3 записи, для этих вновь добавленных 2-х записей так же выполнилось LEVEL+1 и их проставился 2-й уровень.

3) Далее запускается цикл по вновь добавленным записям с ID=4,5 . Первый шаг, в нижнем UNION достаем детей 4-го, это 2 записи (10,11), им выставился LEVEL=2+1=3.

4) Второй шаг, в нижнем UNION достаем детей 5-го, их нет, поэтому выходим из рекурсии.

В итоге таблица temp1 содержит 5 записей

Для того чтобы далее применить рекурсивный запрос для суммирования заполним поле LVL в таблице testh.

-- Заполнение поля LVL
UPDATE testh
SET lvl = res.level
FROM
(
-- -----------------------------------------------------------------------
WITH RECURSIVE temp1(ID,PID,VAL,PATH,LEVEL) AS
(
SELECT T1.ID,T1.PID, T1.VAL, CAST (T1.ID AS VARCHAR (50)) as PATH, 1
FROM testh T1
WHERE T1.PID IS NULL
union
SELECT T2.ID, T2.PID, T2.VAL, CAST ( temp1.PATH ||'->'|| T2.ID AS VARCHAR(50)) ,LEVEL + 1
FROM testh T2 INNER JOIN temp1 on (temp1.ID = T2.PID)
)
select *
from temp1
-- -----------------------------------------------------------------------
) AS res
WHERE
testh.id = res.id

commit;

select * from testh

Сам запрос для суммирования данных с листьев до корней имеет вид.

-- Суммировнаие узлов со своими детьми вверх к корням.
with recursive summ_values(id, pid, lvl, iteration_lvl, val)
as (
select id, pid, lvl, max(lvl) over() - 1 as iteration_lvl, val
from testh t1
union all /* =============================== */
select id, pid, lvl, iteration_lvl - 1 as iteration_lvl,
case
when iteration_lvl != lvl
then val
else sum(val) over(partition by case when iteration_lvl = lvl then id else pid end)
end as val
from summ_values t2
where t2.iteration_lvl > 0
)
select *
from summ_values
where iteration_lvl = 0

Запрос выполняется в 3 шага (связано с количеством уровней):

1) На первом во временную таблицу summ_values попадает результат верхнего union.

select id, pid, lvl, max(lvl) over() - 1 as iteration_lvl, val
from testh t1

2) На втором шаге к данным во временной таблице summ_values (шаг 1.) добавляется результат второго UNION.

select id, pid, lvl, iteration_lvl - 1 as iteration_lvl,
case
when iteration_lvl != lvl
then val
else sum(val) over(partition by case when iteration_lvl = lvl then id else pid end)
end as val
from summ_values t2
where t2.iteration_lvl > 0

Для наглядности и понимания заменим summ_values на подзапрос с шага 1.

select id, pid, lvl, iteration_lvl - 1 as iteration_lvl,
case
when iteration_lvl != lvl
then val
else sum(val) over(partition by case when iteration_lvl = lvl then id else pid end)
end as val
from (
---- query from step 1 ----
select id, pid, lvl, max(lvl) over() - 1 as iteration_lvl, val
from testh t1
---------------------------
) t2
where t2.iteration_lvl > 0

получаем

Этот результат во-первых добавляется к уже имеющемуся в summ_values, во-вторых идёт в следуюущую рекурсию.

Опять для наглядности поменяем заменим summ_values на подзапрос из п.2.

-- step 3 -------
select id, pid, lvl, iteration_lvl - 1 as iteration_lvl,
case
when iteration_lvl != lvl
then val
else sum(val) over(partition by case when iteration_lvl = lvl then id else pid end)
end as val
from (
-- query from step 2 --------------------------
select id, pid, lvl, iteration_lvl - 1 as iteration_lvl,
case
when iteration_lvl != lvl
then val
else sum(val) over(partition by case when iteration_lvl = lvl then id else pid end)
end as val
from (
---- query from step 1 ----
select id, pid, lvl, max(lvl) over() - 1 as iteration_lvl, val
from testh t1
---------------------------
) t2
where t2.iteration_lvl > 0
-----------------------------------------------
) t2
where t2.iteration_lvl > 0

Дальше рекурсия не идёт, т.к. в нижнем UNION стоит условие where t2.iteration_lvl > 0

==================================================================

Итоговый запрос дереве с 3-мя корнями и результат суммирования:

drop table testh;

CREATE TABLE testh(
id integer constraint pk_testh primary key,
pid integer constraint fk_testh_self references testh(id),
val real,
lvl integer

);

insert into testh
select unnest(array[1,2,3, 4,5, 6,7, 8,9, 10,11, 12,13, 14,15]) as ID,
unnest(array[NULL,NULL,null, 1,1, 2,2, 3,3, 4,4, 6,6, 8,8]) as PID,
unnest(array[NULL,NULL,null, 10,1, 5,6, 2,4, 2,4, 6,1, 2,3]) as VAL,
unnest(array[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]::int[]) as LVL;

select ID,PID,VAL from testh

-- [1] Вывод простого дерева, Иерархия
WITH RECURSIVE temp1(ID,PID,VAL,PATH,LEVEL) AS
(
--1
SELECT T1.ID,T1.PID, T1.VAL, CAST (T1.ID AS VARCHAR (50)) as PATH, 1
FROM testh T1
WHERE T1.PID IS NULL
union
-- 2,3
SELECT T2.ID, T2.PID, T2.VAL, CAST ( temp1.PATH ||'->'|| T2.ID AS VARCHAR(50)) ,temp1.LEVEL + 1
FROM testh T2 INNER JOIN temp1 on (temp1.ID = T2.PID)
)
select *
from temp1
order by path

+ -- Заполнение поля LVL
UPDATE testh ..... (см. запрос выше)

-- Суммировнаие узлов со своими детьми вверх к корням.
with recursive summ_values(id, pid, lvl, iteration_lvl, val)
as (
select id, pid, lvl, max(lvl) over() - 1 as iteration_lvl, val
from testh t1
union all /* =============================== */
select id, pid, lvl, iteration_lvl - 1 as iteration_lvl,
case
when iteration_lvl != lvl
then val
else sum(val) over(partition by case when iteration_lvl = lvl then id else pid end)
end as val
from summ_values t2
where t2.iteration_lvl > 0
)
select *
from summ_values
where iteration_lvl = 0

четверг, 28 декабря 2017 г.

четверг, 30 ноября 2017 г.

среда, 11 октября 2017 г.

среда, 20 сентября 2017 г.

понедельник, 4 сентября 2017 г.

среда, 16 августа 2017 г.

среда, 2 августа 2017 г.

четверг, 20 июля 2017 г.

четверг, 6 июля 2017 г.

вторник, 20 июня 2017 г.

четверг, 1 июня 2017 г.

среда, 17 мая 2017 г.

воскресенье, 14 мая 2017 г.

пятница, 7 апреля 2017 г.

четверг, 6 апреля 2017 г.

среда, 5 апреля 2017 г.

вторник, 4 апреля 2017 г.

пятница, 31 марта 2017 г.

среда, 29 марта 2017 г.