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)