Глоссарий
Установка PostgreSQL
Запуск и остановка сервера
Конфигурирование
Транзакции
- Многоверсионность
Подключения
Прочее
Пример зависимостей объектов
- Планирование новой базы данных
Проверка состояния сервера
Разное
Упр.
- 1
- 2
Представления view
Индексы

Глоссарий

Сервер - сервер БД и его процессы
Сервис - обертка операционной системы с помощью которой вызывается сервер
Страница (блок данных) -
Кортеж (строка) -
Пул соединений - это набор сессий, которые уже подключены. Его можно использовать для уменьшения нагрузки.(Пример pgbouncer)

Установка PostgreSQL

Docker Run


docker run --rm -d --net=host --name postgres -v $(pwd)/pg_data:/var/lib/postgresql/data postgres:11-alpine

Установка из исходников


$ wget https://ftp.postgresql.org/pub/source/v9.6.16/postgresql-9.6.16.tar.gz
$ tar xzf postgresql-9.6.16.tar.gz
$ ls -l
total 23912
drwxrwxr-x 6 vagrant vagrant     4096 Nov 11 22:23 postgresql-9.6.16
-rw-rw-r-- 1 vagrant vagrant 24474740 Nov 11 22:23 postgresql-9.6.16.tar.gz


$ sudo apt-get install make gcc libreadline-dev zlib1g-dev

Создание конфигурации


$ cd postgresql-9.6.16 && ./configure
...

В команде configure можно указать различные параметры конфигурации. Например:

--prefix - каталог установки, по умолчанию /usr/local/pgsql;
--enable-debug - для включения отладочной информации.

Сборка PostgreSQL

Возможные варианты:

make - сборка только сервера
make world - сборка сервера, всех расширений и документации


$ make
make[2]: Leaving directory '/home/vagrant/postgresql-9.6.16/src/test/regress'
make -C test/perl all
make[2]: Entering directory '/home/vagrant/postgresql-9.6.16/src/test/perl'
make[2]: Nothing to be done for 'all'.
make[2]: Leaving directory '/home/vagrant/postgresql-9.6.16/src/test/perl'
make[1]: Leaving directory '/home/vagrant/postgresql-9.6.16/src'
make -C config all
make[1]: Entering directory '/home/vagrant/postgresql-9.6.16/config'
make[1]: Nothing to be done for 'all'.
make[1]: Leaving directory '/home/vagrant/postgresql-9.6.16/config'
All of PostgreSQL successfully made. Ready to install.

Установка


$ sudo make install

Настройка пользователя `postgres`


$ sudo adduser postgres -- создаем пользователя
...
$ sudo -iu postgres -- проверяем вход
$ sudo mkdir /usr/local/pgsql/data/ -- создаем и присваиваем пользователя где будет храниться БД
$ sudo chown postgres /usr/local/pgsql/data/
$ tail -n 2 /home/postgres/.bashrc 
export PGDATA=/usr/local/pgsql/data; -- уст. путь до данных
PATH="/usr/local/pgsql/bin${PATH:+:${PATH}}"; export PATH; -- добавляем /usr/local/pgsql/bin для доступа к утилитам

Запуск кластера

Ключ -k включает подсчет контрольной суммы страниц, что позволяет своевременно обнаруживать повреждение данных.


$ initdb -k
...
Success. You can now start the database server using:
    pg_ctl -D /usr/local/pgsql/data -l logfile start


pg_ctl -D /usr/local/pgsql/data -l logfile start

-D - по умолчанию $PGDATA

Проверяем


$ psql -c 'select now();'
              now              
-------------------------------
 2019-12-05 20:18:56.048817+00
(1 row)

Установка (сборка) расширений

Список доступных расширений


$ psql -c 'select * from pg_available_extensions;'
  name   | default_version | installed_version |           comment            
---------+-----------------+-------------------+------------------------------
 plpgsql | 1.0             | 1.0               | PL/pgSQL procedural language
(1 row)

Список модулей для 9.6


$ cd postgresql-9.6.16/contrib/pgcrypto/
$ make
...
$ sudo make install
...
$ psql -c 'select * from pg_available_extensions;'
   name   | default_version | installed_version |           comment            
----------+-----------------+-------------------+------------------------------
 plpgsql  | 1.0             | 1.0               | PL/pgSQL procedural language
 pgcrypto | 1.3             |                   | cryptographic functions
(2 rows)

Установка из пакетов

В книге нет инструкции к установке, поэтому использовал статью - Как установить и начать использовать PostgreSQL в Ubuntu 16.04 Сама установка свелась к


sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Базовая установка postgres на ubuntu server 16.04.4


sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Install 9.6


sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

Вход в БД

Зайти под пользователем postgres, в базу postgres


sudo -iu postgres
...
postgres@Postgres:~$ psql 
psql (9.5.12)
Type "help" for help.

postgres=#

Запуск и остановка сервера


$ pg_ctl stop -m fast|smart|immediate

Shutdown modes are:

fast принудительно завершает сеансы и записывает на диск изменения из оперативной памяти;
smart ожидает завершения всех сеансов и записывает на диск изменения из оперативной памяти;
immediate принудительно завершает сеансы, при запуске потребуется восстановление.

Конфигурирование

Основной файл конфигурации - postgresql.conf. Находиться в


$ psql 
postgres@postgres=# show config_file 
postgres-# ;
              config_file              
---------------------------------------
 /usr/local/pgsql/data/postgresql.conf
(1 row)

При изменении без перезагрузки можно


$ pg_ctl reload
server signaled


# select pg_reload_conf();
LOG:  received SIGHUP, reloading configuration files
 pg_reload_conf 
----------------
 t
(1 row)


$ kill -s HUP $( pidof postgres )

Изменение параметров

Для просмотра текущих параметров


# select * from pg_settings where name like 'work_mem';
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------
name            | work_mem <-- Название
setting         | 4096 <-- текущее значение
unit            | kB
category        | Resource Usage / Memory 
short_desc      | Sets the maximum memory to be used for query workspaces.
extra_desc      | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.
context         | user <-- контекст который может изменить параметр
vartype         | integer
source          | default <-- источник. 
min_val         | 64
max_val         | 2147483647
enumvals        | 
boot_val        | 4096 <-- Значение по уполчанию
reset_val       | 4096 <-- уст. значение
sourcefile      | 
sourceline      | 
pending_restart | f

Контексты

internal - нельзя изменить, никак…
postmaster - требуется перезапуск сервера
sighup - достаточно перечитать конфиг
superuser - может менять на ходу, супер пользователь, во время своего сеанса
user - любой может изменить для своего сеанса

Пример

При дублировании параметров в конфиге, примениться последний


# select sourceline, name, setting, applied from pg_file_settings where name like 'work_mem';
 sourceline |   name   | setting | applied 
------------+----------+---------+---------
        647 | work_mem | 12MB    | f
        648 | work_mem | 8MB     | t
(2 rows)

Флаг applied говорит какой из параметров примениться


# select pg_reload_conf();
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "work_mem" changed to "8MB"
 pg_reload_conf 
----------------
 t
(1 row)


# select * from pg_settings where name like 'work_mem';
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------
name            | work_mem
setting         | 8192
unit            | kB
category        | Resource Usage / Memory
short_desc      | Sets the maximum memory to be used for query workspaces.
extra_desc      | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.
context         | user
vartype         | integer
source          | configuration file
min_val         | 64
max_val         | 2147483647
enumvals        | 
boot_val        | 4096
reset_val       | 8192
sourcefile      | /usr/local/pgsql/data/postgresql.conf
sourceline      | 648
pending_restart | f

Добавление/удаление из `postgresql.auto.conf`

Изменение postgresql.auto.conf


# alter system set work_mem to '16MB';
ALTER SYSTEM


# select sourcefile, sourceline, name, setting, applied from pg_file_settings where name like 'work_mem';
                 sourcefile                 | sourceline |   name   | setting | appl
--------------------------------------------+------------+----------+---------+-----
 /usr/local/pgsql/data/postgresql.conf      |        647 | work_mem | 12MB    | f
 /usr/local/pgsql/data/postgresql.conf      |        648 | work_mem | 8MB     | f
 /usr/local/pgsql/data/postgresql.auto.conf |          3 | work_mem | 16MB    | t
(3 rows)

Для применения параметра нужно перезагрузить конфиг


# show work_mem;
 work_mem 
----------
 8MB
(1 row)


# select pg_reload_conf();
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "work_mem" changed to "16MB"
 pg_reload_conf 
----------------
 t
(1 row)

Time: 1.770 ms
postgres@postgres=# show work_mem;
 work_mem 
----------
 16MB
(1 row)

Time: 1.496 ms

Для удаления alter system используется alter system reset


# alter system reset work_mem;
ALTER SYSTEM
Time: 10.622 ms
postgres@postgres=# show work_mem;
 work_mem 
----------
 16MB
(1 row)

Time: 0.307 ms
postgres@postgres=# select pg_reload_conf();
LOG:  received SIGHUP, reloading configuration files
 pg_reload_conf 
----------------
 t
(1 row)

Time: 0.509 ms
postgres@postgres=# LOG:  parameter "work_mem" changed to "8MB"

Открытие доступа к базе из вне

Установить адреса с которого постгрес будет ожидать соединения,

для всех адресов listen_addresses='*' в /etc/postgresql/9.5/main/postgresql.conf
Чтобы установить всем пользователям доступ ко всем базам по паролю, нужно добавить/изменить в /etc/postgresql/9.5/main/pg_hba.conf
```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5
```

Удаленное подключение

Перед подключением необходимо задать пароль пользователя к базе. Сделать это можно так:


avis=# alter user avis with password 'test12';
ALTER ROLE

~~Что не очень безопасно, т.к. именно в таком виде будет храниться в базе~~ или нет...


postgres=# SELECT *
FROM pg_user
WHERE usename = 'avis';

-[ RECORD 1 ]+---------
usename      | avis
usesysid     | 16388
usecreatedb  | t
usesuper     | t
userepl      | f
usebypassrls | f
passwd       | ********
valuntil     | 
useconfig    |

Поэтому более безопасно задать пароль после подключения. см \password

Создание новой роли


sudo -iu postgres
postgres@ubuntu:~createuser --interactive
Enter name of role to add: avis
Shall the new role be a superuser? (y/n) y

Сменить пароль пользователя под которым вошли \password


avis@ubuntu:~psql -d postgres
postgres=# \password
Enter new password: 
Enter it again:

Подключение к базе

psql -h host -p port -d dbname -U user

host - узел
port - порт
dbname - название базы данных
user - пользователь
пароль


psql -h 192.168.16.106 -p 5432 -d avis -U avis

Пароль test12

Где я? кто я?

При подключении к базе, например через pgsql следующие команды помогут узнать где вы находитесь

Текущая база


avis=# select current_database();
...
 current_database 
------------------
 avis
(1 row)

Текущий пользователь


avis=# select current_user;
...
 current_user 
--------------
 avis
(1 row)

Текущая версия postgres


avis=# select version();
...
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.12 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 row)

Транзакции

Многоверсионность

Номер транзакции - отметка времени

xmin - номер транзакции которая создала версию строки
xmax - номер транзакции которая удалила версию строки


create table t(
  s text
);

insert into t values ('Первая версия'); <-- xmin=600, xmax=0
# select *, xmin, xmax from t;
       s       | xmin | xmax 
---------------+------+------
 Первая версия |  600 |    0
(1 row)

Подключения

psql


psql -U avis -h 192.168.16.102 -p 5432 avis

sql manager

pgAdmin

Прочее

Сколько времени работает сервер?


SELECT date_trunc('second', CURRENT_TIMESTAMP - pg_postmaster_start_time());

 date_trunc 
------------
 00:56:57
(1 row)

pg_postmaster_start_time - возвращает время когда сервер стартовал

Где лежат логи сервера?

Он может быть в:

Директории с данными
В директории операционной системы - /spool/log, и т.п.
Перенаправлен в syslog
Может вовсе отсутсвовать

По умолчанию, в ubuntu - /var/log/postgresql/postgresql-9.5-main.log

Список баз данных на сервере


psql -h 192.168.16.106 -l
                               List of databases
                               ...


avis=# \l
                               List of databases
                               ...

```
SELECT datname FROM pg_database;
```

datname
———– template1 template0 postgres test avis (5 rows) </code></pre>

Пример рабочей базы - тут

Установка тут

Сколько таблиц в БД?


psql -h 192.168.1.36 -p 5432 -d dvdrental -U avis
dvdrental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)


SELECT count(*)
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog');

 count 
-------
    22
(1 row)

В фонмиксе, на сегодняшний день - 14-04-2018, 26 схемы, 114 таблиц.

Сколько весит база данных?


SELECT pg_size_pretty(pg_database_size(current_database()));
 pg_size_pretty 
----------------
 14 MB
(1 row)

current_database() - возвращает название текущей БД.
pg_database_size([название базы]) - возвращает размер базы в байтах
pg_size_pretty([байты]) - возвращает размер в человекочитаемом виде

Сколько весит таблица и индексы?


SELECT pg_size_pretty(pg_table_size('public.store')) AS table_size,
       pg_size_pretty(pg_indexes_size('public.store')) AS index_size;

 table_size | index_size 
------------+------------
 8192 bytes | 32 kB
(1 row)

Размер всей таблицы + ее индексы и т.п.


SELECT pg_size_pretty(pg_total_relation_size('store'));

 pg_size_pretty 
----------------
 40 kB
(1 row)

Топ 10 самых больших таблиц


SELECT TABLE_NAME,
       pg_size_pretty(pg_relation_size(TABLE_NAME)) AS size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

         table_name         |    size    
----------------------------+------------
 payment                    | 864 kB
 language                   | 8192 bytes
 staff                      | 8192 bytes
 store                      | 8192 bytes
 category                   | 8192 bytes
 country                    | 8192 bytes
 customer                   | 72 kB
 address                    | 64 kB
 film_category              | 48 kB
 film                       | 432 kB
(10 rows)

Сколько строк в таблице?


SELECT count(*) FROM payment;

 count 
-------
 14596
(1 row)

Пример зависимостей объектов


CREATE TABLE orders (
    order_id integer PRIMARY KEY
);
CREATE TABLE order_lines (
    order_id integer, line_id smallint,
    PRIMARY KEY (order_id, line_id)
);

Добавляем ссылочную целостность


ALTER TABLE order_lines ADD FOREIGN KEY (order_id) REFERENCES orders (order_id);
ALTER TABLE

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


DROP TABLE orders;
ERROR:  cannot drop table orders because other objects depend on it
DETAIL:  constraint order_lines_order_id_fkey on table order_lines depends on table orders
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Для получения полной информации о зависимостях таблицы


\d+ orders
                         Table "public.orders"
  Column  |  Type   | Modifiers | Storage | Stats target | Description 
----------+---------+-----------+---------+--------------+-------------
 order_id | integer | not null  | plain   |              | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
Referenced by:
    TABLE "order_lines" CONSTRAINT "order_lines_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(order_id)

Планирование новой базы данных

Пример тут

Проверка состояния сервера

Состояние сервера


sudo pg_ctlcluster 9.5 main status
pg_ctl: server is running (PID: 1869)
/usr/lib/postgresql/9.5/bin/postgres "-D" "/var/lib/postgresql/9.5/main" "-c" "config_file=/etc/postgresql/9.5/main/postgresql.conf"
avis@ubuntu:~

Запуск сервера вручную

Ubuntu


sudo pg_ctlcluster 9.5 main start
Redirecting start request to systemctl

Быстрая и безопасная остановка сервера


sudo pg_ctlcluster 9.5 main stop --force
Redirecting stop request to systemctl

ключ --force указывает что нужно выполнить быстрое отключение, а если не получается выполняется мнгновенное отключение

Перезагрузка конфига


sudo pg_ctlcluster 9.5 main reload

Принудительное отключение пользователя

client


dvdrental=# select count(*) from public.payment;
 count 
-------
 14596
(1 row)

admin


postgres=# SELECT * FROM pg_stat_activity WHERE pid = 1989;
-[ RECORD 1 ]----+-------------------------------------
datid            | 16389
datname          | dvdrental
pid              | 1989
usesysid         | 16388
usename          | avis
application_name | psql
client_addr      | 192.168.1.46
client_hostname  | 
client_port      | 34886
backend_start    | 2019-01-03 06:09:46.482286+03
xact_start       | 
query_start      | 2019-01-03 06:10:08.897423+03
state_change     | 2019-01-03 06:10:08.902233+03
waiting          | f
state            | idle
backend_xid      | 
backend_xmin     | 
query            | select count(*) from public.payment;


postgres=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 1989;
-[ RECORD 1 ]--------+--
pg_terminate_backend | t

client


dvdrental=# SELECT 1;
FATAL:  terminating connection due to administrator command
SSL connection has been closed unexpectedly
The connection to the server was lost. Attempting reset: Succeeded.

Выделение для пользователя собственной БД

Создание пользователя и БД


postgres=# CREATE USER foo;
CREATE ROLE
postgres=# CREATE DATABASE foo OWNER = foo;
CREATE DATABASE

Указываем права доступа


postgres=# BEGIN;
BEGIN
postgres=# REVOKE CONNECT ON DATABASE foo FROM PUBLIC;
REVOKE
postgres=# GRANT CONNECT ON DATABASE foo TO foo;
GRANT
postgres=# COMMIT;
COMMIT

Создаем другого пользователя чтобы проверить работает ли доступ, и пробуем подключиться


postgres=# CREATE USER bar;
CREATE ROLE
postgres=# ALTER USER bar WITH password 'bar';
ALTER ROLE

avis@avis-PC[06:32:03]:~psql -h 192.168.1.36 -p 5432 -d foo -U bar
Password for user bar: 
psql: FATAL:  permission denied for database "foo"
DETAIL:  User does not have CONNECT privilege.

Создание нескольких серверов на одной системе


sudo pg_createcluster 9.5 db2
Creating new cluster 9.5/db2 ...
  config /etc/postgresql/9.5/db2
  data   /var/lib/postgresql/9.5/db2
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433

Проделываем все операции что и с новоым сервером БД см. открытие доступов и т.п. После этого получаем доступ к новому серверу БД


sudo -iu postgres
postgres@ubuntu:~psql -p 5433
psql (9.5.12)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=#

Разное

Чтобы по 100 раз не вводить пароль, можно сохранить параметры подключения с машины с которой подкючаетесь в ~/.pgpass
Формат: host:port:dbname:user:password


cat ~/.pgpass 
...
192.168.16.106:5432:*:avis:test12

psql -h 192.168.16.106
...
psql (9.5.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

avis=#

Упр.

1

Запустите psql и проверьте информацию о текущем подключении.


postgres=# \conninfo 
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".

Выведите строки таблицы pg_tables.


postgres=# select schemaname, tablename from pg_tables limit 4;
 schemaname |    tablename    
------------+-----------------
 pg_catalog | pg_statistic
 pg_catalog | pg_type
 pg_catalog | pg_authid
 pg_catalog | pg_user_mapping
(4 rows)

Установите команду less -XS для постраничного просмотра и еще раз выведите все строки pg_tables.


postgres=# \setenv PAGER 'less -XS'

Для постоянного включения


postgres@vagrant:~$ cat ~/.psqlrc 
\pset pager on
\setenv PAGER 'less -XS'
postgres@vagrant:~$

Настройте psql так, чтобы для каждой команды печаталось время ее выполнения. Убедитесь, что при повторном запуске эта настройка сохраняется.


$ cat ~/.psqlrc 
\timing
...
$ psql 
Pager is used for long output.
Timing is on.
psql (9.6.16)
Type "help" for help.

postgres=# select 1;
 ?column? 
----------
        1
(1 row)

Time: 0.709 ms

Приглашение по умолчанию показывает имя базы данных. Настройте приглашение так, чтобы дополнительно выводилась информация о пользователе: роль@база=#


\set PROMPT1 '%n@%/%R%# '
\set PROMPT2 '%n@%/%R%# '

2

Создайте таблицу с одной строкой.


create table t(
  s text
);
insert into t values ('Первая версия');

Начните первую транзакцию и выполните запрос к таблице.


# begin ;
BEGIN
ubuntu_vb=# select * from t;
       s       
---------------
 Первая версия
(1 row)

Во втором сеансе удалите строку и зафиксируйте изменения.


# begin ;
BEGIN
ubuntu_vb=# delete from t;
DELETE 1
ubuntu_vb=#

Сколько строк увидит первая транзакция, выполнив тот же запрос повторно? Проверьте.


# select *, xmin, xmax from t;
       s       | xmin | xmax 
---------------+------+------
 Первая версия |  607 |  608
(1 row)

Завершите первую транзакцию.


# commit ;
COMMIT
# select *, xmin, xmax from t;
 s | xmin | xmax 
---+------+------
(0 rows)

Повторите все то же самое, но пусть теперь транзакция работает на уровне изоляции repeatable read: BEGIN ISOLATION LEVEL REPEATABLE READ; Объясните отличия.

tx1


# select *, xmin, xmax from t;
 s | xmin | xmax 
---+------+------
(0 rows)

ubuntu_vb=# insert into t values ('Первая версия');
INSERT 0 1
ubuntu_vb=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
ubuntu_vb=# select *, xmin, xmax from t;
       s       | xmin | xmax 
---------------+------+------
 Первая версия |  616 |    0
(1 row)

tx2


ubuntu_vb=# begin ;
BEGIN
ubuntu_vb=# select *, xmin, xmax from t;
       s       | xmin | xmax 
---------------+------+------
 Первая версия |  616 |    0
(1 row)

ubuntu_vb=# delete from t;
DELETE 1
ubuntu_vb=# commit ;
COMMIT
ubuntu_vb=#

tx1


# select *, xmin, xmax from t;
       s       | xmin | xmax 
---------------+------+------
 Первая версия |  616 |  617
(1 row)
ubuntu_vb=# update t set s = 'e21';
ERROR:  could not serialize access due to concurrent update
ubuntu_vb=# select *, xmin, xmax from t;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ubuntu_vb=#

Представления `view`

Преимущества

Повторное использование select-ов
Настройка прав доступа

Недостатки

Нет триггеров (?)


ERROR:  cannot insert into view "test_view"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.

Таблицы используемые в представлении должны существовать

Особенности

Если данные из таблицы удаляются, то и из view тоже удаляются

Пример:


create table films (
    imdb varchar(16) primary key,
    title varchar(40) not null,
    kind varchar(10)
);


insert into films values 
('123', 'test1', 'Comedy'),
('1234', 'test2', 'Drama'),
('1235', 'test3', 'Undef');


create or replace view comedies as
    select *
    from films
    where kind = 'Comedy'


select * from comedies;  
...
 imdb | title |  kind  
------+-------+--------
 123  | test1 | Comedy
(1 row)

Если view простой (состоит только из фильтра where) то можно делать insert и update. При этом данные добавяться в основную таблицу и если они не подходят под условие where, то во view не покажуться


insert into comedies values
('12345', 'dsa', 'Comedy');


select * from comedies;
...
 imdb  | title |  kind  
-------+-------+--------
 123   | test1 | Comedy
 12345 | dsa   | Comedy
(2 rows)


select * from films;
...
 imdb  | title |  kind  
-------+-------+--------
 123   | test1 | Comedy
 1234  | test2 | Drama
 1235  | test3 | Undef
 12345 | dsa   | Comedy
(4 rows)

При обновлении, пропадает из view


update comedies set kind = 'test' where imdb = '12345';


select * from films;
...
 imdb  | title |  kind  
-------+-------+--------
 123   | test1 | Comedy
 1234  | test2 | Drama
 1235  | test3 | Undef
 12345 | dsa   | test
(4 rows)


select * from comedies;
...
 imdb | title |  kind  
------+-------+--------
 123  | test1 | Comedy
(1 row)

Индексы

Зачем?

Быстрый поиск записей по условию where
Объединение таблиц посредством join. Необходимо использовать типы сравниваемых полей
Поиск MAX и MIN значений для ключевых полей
Сортировка, группировка таблиц (ORDER BY, GROUP BY)
Извлечение данных не из таблиц с данными а из индексного файла.

Индекс по умолчанию - `btree`

Пример создания


CREATE TABLE people (
  last_name text not null,
  first_name text not null,
  dob timestamp not null,
  gender int not null
);

CREATE INDEX idx_people_name
ON people USING btree (last_name, first_name, dob);

Можно

Поиск по полному значению
Поиск по самому левому префиксу ?
Поиск по префиксу столбца
Поиск по диапазону значений
Запросы только по индексу

Нельзя

Пропускать столбцы

hash-индексы


create table testhash (
  fname text not null,
  lname text not null
);

CREATE INDEX idx_testhash
ON testhash USING hash (fname);
...
ПРЕДУПРЕЖДЕНИЕ:  хеш-индексы не записываются в журнал, использовать их не рекомендуется
CREATE INDEX
...

insert into testhash values ('TEST', 'HASH');

test=# explain 
test-# select * from testhash where fname = 'TEST';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Bitmap Heap Scan on testhash  (cost=1.13..5.33 rows=4 width=64)
   Recheck Cond: (fname = 'TEST'::text)
   ->  Bitmap Index Scan on idx_testhash  (cost=0.00..1.13 rows=4 width=0)
         Index Cond: (fname = 'TEST'::text)
(4 строки)

test=#

Нельзя использовать данные в индексе чтобы избежать чтения строк
Нельзя использовать для сортировки
Хеш-индексы не поддерживают поиск по частичному ключу
Поддерживают только сравнения на равенство

GiST индексы (для географических данных)

GIN (инвертированный) индекс