Zen: pgsql tutorial

Использование PostgreSQL

Предполагается знание основ реляционных баз данных и наличие навыков работы с ними. Лекция рассчитана на 4 занятия по 45 минут.

Интерфейсы PostgreSQL

Основные клиентские интерфейсы
- libpq - работа с базой данных из языка 'C'
- libpqxx,libpq++ - C++
- DBD::Pg - perl интерфейс
- psqlODBC - ODBC драйвер
- pgjdbc - JDBC драйвер
- Npgsql - .Net интерфейс
- pgtcl,pgtclng - tcl
- PyGreSQL, pygres - python
- dbd_pg - ruby
- psql - коммандная строка
- pgbash - доступ из командной оболочки
- php
Программирование на стороне сервера - процедурные языки
- встроенные интерфейсы - sql,C
- pl/pgsql - sql
- pl/perl - perl
- pl/tcl - tcl
- pl/python - python
- plr - R
Пример приложения на языке perl
Особенности использования кавычек в pl/pgsql

Примеры использования

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

Использование schema
- Как псевдо-БД (аналогия с файловой системой, где схема - это директория)
- Для программистов это возможность тестирования своих приложений, для этого надо подумать о схемах как о юниксовом PATH, когда сво девелоперская директория ~bin ставится перед системной /usr/bin
- Если несколько приложений используют одни и те же объекты в одной БД, то логично выделить их в отдельную схему.
Создание таблиц, индексов, первичные и внешние ключи, ограничения
- with/without OID Умолчание задается в postgresql.conf ( #default_with_oids = true ). OID дают возможность найти последнюю вставленную строчку

test=# create table with_oid (a integer) with oids;
CREATE TABLE
test=# insert into with_oid values(1);
INSERT 6350719 1
test=# insert into with_oid values(2);
INSERT 6350720 1
test=# select a from with_oid where oid=(select max(oid) from with_oid);
 a 
---
 2
(1 row)

"name" != "Name" Двойные кавычки используются для названий таблиц, колонок.

test=# select count(temp."X") from temp;
ERROR:  column temp.X does not exist
test=# select count(temp.X) from temp;
 count 
-------
     9

Одинарные кавчычки используются только для строк !

test=# select count(temp.'X') from temp;
ERROR:  parse error at or near "'X'" at character 19
LINE 1: select count(temp.'X') from temp;
                          ^

create table "reserved word" - используйте двойные кавычки

  test=# create table "limit" (a int);
  CREATE TABLE

COMMENT ON {TABLE,FUNCTION,,,,} tblname IS '….'

   test=# COMMENT ON TABLE temp is 'тестовая таблица';
   test=# \dt+ temp
                 List of relations
  Schema | Name | Type  | Owner  |   Description    
  --------+------+-------+--------+------------------
   public | temp | table | megera | тестовая таблица
  test=# COMMENT ON COLUMN temp.x is 'целочисленный атрибут';
  COMMENT
  test=# \d+ temp
                   Table "public.temp"
   Column |  Type   | Modifiers |      Description      
  --------+---------+-----------+-----------------------
   x      | integer |           | целочисленный атрибут
   y      | integer |           |

использование NULL
- Anything <operation> NULL returns NULL

test=# select 1||NULL, 1>NULL, 1+NULL,1~NULL;
 ?column? | ?column? | ?column? | ?column? 
----------+----------+----------+----------
          |          |          |

select (null=null) is null;
см.transform_null_equals in postgresql.conf (false)

test=# set transform_null_equals to on;
SET
test=# select NULL=NULL;
 ?column? 
----------
 t
(1 row)

test=# set transform_null_equals to off;
SET
test=# select NULL=NULL;
 ?column? 
----------
 
(1 row)

coalesce(ARG1,VAL1,VAL2,…) - задание значений для NULL-ed аргументов

test=# select coalesce(NULL,'значение по умолчанию');
       coalesce        
-----------------------
 значение по умолчанию

nullif, IS NOT NULL, IS NULL,
count(*) vs count(some_field_which_could_ne_NULL))
NULL in subselects (надо следить, чтобы результат subselect не содержал NULL, пример:

WHERE co_id NOT IN (SELECT dy_company FROM diary)
WHERE co_id NOT IN (1, 2, null, 3...)
WHERE NOT (co_id=1 OR co_id=2 OR co_id=null OR co_id=3...)
WHERE NOT (null)
WHERE null

сделать NULL при сортировке впереди

select coll from mytable order by coll IS NULL DESC, coll;

NULLs в SELECT DISTINCT рассматриваются как одинаковые ;

Виртуальные таблицы (представления) Пример: Показ I/O статистики (stats_* в postgresql.conf):

create or replace view iostat as select blk.relname
as relname,blk.heap_blks_read || ':' || blk.heap_blks_hit as heap_blk,
        blk.idx_blks_read || ':' || blk.idx_blks_hit as idx_blk,
        tpl.seq_scan || ':' || tpl.seq_tup_read as seq_tup,
        tpl.idx_scan || ':' || tpl.idx_tup_fetch as idx_tup     
from  pg_statio_user_tables blk JOIN  pg_stat_user_tables tpl
USING (relname);

discovery-test=# select * from iostat where relname='message_parts';
    relname    | heap_blk | idx_blk | seq_tup | idx_tup 
---------------+----------+---------+---------+---------
 message_parts | 0:2256   | 0:6834  | 0:0     | 2:3640

Кстати, для очистки статистики, можно использовать

 select pg_stat_reset();

индексы создавать нельзя, используются индексы ориг. таблиц
Разделение прав
постоянство запроса при изменении таблиц
Обновление, rule system
materialized views

Триггеры и правила.

  Правила для переписывания запроса, триггер для реакции на изменение
  данных (per row),  Правила срабатывают *до* выполнения запроса (per statement) 
  и в результате могут измениться много записей.
  Правильное использование правил - для обновления views.

Наследование

Наследование таблиц позволяет создавать базовый объект и дочерние, которые помимо своих атрибутов будут наследовать атрибуты базовых объектов. При этом наследуются DEFAULTS и CONSTRAINTS, включая SERIAL.

наследование+index и partitioning

Очень заманчиво использовать наследование таблиц для эффективной работы с очень большей таблицей поделив ее на несколько частей.

psql test -c "create table a ( i int primary key);"
psql test -c "create table a1(check (i >=0 and i<=2000)) inherits(a);"
psql test -c "create table a2(check (i >=2001 and i<=4000)) inherits(a);"
psql test -c "create table a3(check (i >=4001 and i<=6000)) inherits(a);"

#create index a_idx on a(i);
psql test -c "create index a1_idx on a1(i);"
psql test -c "create index a2_idx on a2(i);"
psql test -c "create index a3_idx on a3(i);"

for ((i=0;i<2000;i++)) do echo $i; done| psql test -c "copy a1 from stdin;"
for ((i=2001;i<4000;i++)) do echo $i; done| psql test -c "copy a2 from stdin;"
for ((i=4001;i<6000;i++)) do echo $i; done| psql test -c "copy a3 from stdin;"

1. use inheritance, faster

explain analyze select 1 from a where i <10;

2.use union

explain analyze select 1 from only a where i <10 
union select 1 from a1 where i <10 
union select 1 from a2 where i <10 
union select 1 from a3 where i <10;

Транзакции, точки спасения - возможность отката внутри транзакции

  BEGIN;
    UPDATE very_big_table set .......;
    SAVEPOINT update_ok;
    -- do some bad things
    ROLLBACK TO SAVEPOINT update_ok;
    -- destroy SAVEPOINT
    -- RELEASE SAVEPOINT update_ok;
  COMMIT;

Табличные пространства - создание хранилища для таблиц

 CREATE TABLESPACE big_storage OWNER robot LOCATION '/db1/big_storage';
 CREATE TABLE very_big_table (.......) TABLESPACE big_storage;

Использование индексов:
- многоатрибутные индексы
- функциональные индексы
- частичные индексы
Использование LIMIT
- first N results

  SELECT * FROM companies ORDER BY co_id LIMIT 5;

last N results

   -- неправильный (обратный) порядок
   SELECT * FROM companies ORDER BY co_id DESC LIMIT 5;
   -- поправим дело
  SELECT * from ( select * FROM companies ORDER BY co_id DESC LIMIT 5) as foo order by co_id;)

Быстрое удаление всех записей из таблицы

Обычно для этого используют

  DELETE from table_name;

однако, при этом сканируются все записи, что для очень больших таблиц очень накладно, поэтому рекомендуется команда TRUNCATE (not in SQL standard)

 TRUNCATE very_big_table;

Есть несколько ограничений по ее использованию в contraints и триггерах, см. мануал

Создание композитного типа

create type val_cid as ( value float, cid integer);
create table t1 (gid integer, z val_cid);
insert into t1 values(1,(23.65,4));
insert into t1 values(2,(20.8,2));
test=# select * from t1 where (z).cid=2;
 gid |    z     
-----+----------
   1 | (20.8,2)
(1 row)

Если определить операции сравнения, то можно и индекс построить.

joining array

Часто бывает, что нужно заменить каждый элемент массива его связкой из другой таблицы. PostgreSQL позволяет это сделать элегантно.

Пример: таблица survey(id integer, name text)

sn=# select name from survey where id = any( '{1,2,3}');
 name 
------
 AB
 AM
 AR
(3 rows)

Воспользуемся функцией array

sn=# select array( select name from survey where id = any( '{1,2,3}')) as survey;
   survey   
------------
 {AB,AM,AR}
(1 row)

Последний штрих - превратим массив в строку

sn=# select array_to_string (array( select name from survey where id = any( '{1,2,3}')), ',') as survey;
  survey  
----------
 AB,AM,AR
(1 row)

Использование команды explain для оптимизации запросов
Работа с иерархическими данными (ltree)
Организация полнотекстового поиска (tsearch2)
Работа с массивами (intarray)
Поиск с ошибками (pg_trgm)

pgsql tutorial

Projects

Links

Использование PostgreSQL

Интерфейсы PostgreSQL

Примеры использования