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)