Рекурсивные запросы в PostgreSQL (WITH RECURSIVE) / СоХабр

Все команды запускаются под пользователем postgres (postgresql-суперпользователь)

$ su — postgres

— список баз данных.

— подключение к БД dbname.

— выполнение команд из файла file.sql.

— выполнение команды в базе dbname.

— вывод результата запроса в html-файл.

Просмотр списка и путей к конфигурационным файлам

psql > SELECT name, setting FROM pg_settings WHERE category = ‘File Locations’; # или список всех конфигурационных параметров psql > show all;

Список активных соединений с информацией о: pid процесса, выполняющегося запроса, пользователя, базы данных.

psql > SELECT * FROM pg_stat_activity;

Создание индексов

# primary key psql > ALTER TABLE tableName ADD PRIMARY KEY (id); # unique index psql > CREATE UNIQUE INDEX indexName ON tableName (columnNames);

Команды psql

— подсоединение к БД dbname.

— список баз данных.

— список всех таблиц.

— структура таблицы table.

— список всех пользователей и их привилегий.

— список всех таблиц с описанием.

— список всех таблиц, содержащих s в имени.

— выполнить команды из файла FILE.

— сохранить результат запроса в файл FILE.

— переключение между режимами вывода: с/без выравнивания.

Бекап и восстановление таблиц

В PostgreSQL есть две утилиты для бекапа и .

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

Достопримечательности Антананариву

Создание бекапа базы mydb, в сжатом виде

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb

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

pg_dump -a -t table_name -f file_name database_name

Создание резервной копии с сжатием в gz

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Список наиболее часто используемых опций:

— хост, если не указан то используется localhost или значение из переменной окружения PGHOST.

— порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.

— пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.

— дамп только данных, по-умолчанию сохраняются данные и схема.

— включать в дамп большие объекты (blog’и).

— дамп только схемы.

— добавляет команду для создания БД.

— добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

— не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).

— выходной формат дампа, custom, tar, или plain text.

— указываем определенную таблицу для дампа.

— вывод подробной информации.

— дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду .

pg_dumpall > all.sql # проверка бекапа grep «^[\]connect» all.sql \connect db1 \connect db2

В PostgreSQL есть две утилиты для восстановления базы из бекапа.

  • psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
  • pg_restore — восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

psql -h localhost -U someuser -d dbname -f mydb.sql

Восстановление всего бекапа с остановкой на первой ошибке

psql -h localhost -U someuser —set ON_ERROR_STOP=on -f mydb.sql

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью (если при создании бекапа не была указана опция ) и восстановить

pg_restore —dbname=mydb —jobs=4 —verbose mydb.backup

Восстановление резервной копии БД, сжатой gz

gunzip mydb.gz psql -U postgres -d mydb -f mydb

Начиная с версии 9.2 можно восстановить только структуру таблиц с помощью опции

# создаем БД CREATE DATABASE mydb2; # восстанавливаем pg_restore —dbname=mydb2 —section=pre-data —jobs=4 mydb.backup

Обслуживание таблицы

VACUUM ANALYZE table; REINDEX DATABASE dbName; REINDEX TABLE tabName;

Перенос директории с данным (data directory)

Узнать текущий путь

# способ 1 $ su — postgres $ psql psql > SHOW data_directory; # способ 2 $ ps ax | grep ‘postgres -D’

Создадим новую директорию, назначим пользователя и инициализируем

mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su — postgres initdb -D /pathto/postgresql/data

Теперь надо подправить файл с сервисом, который стартует postgresql

# под arch linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Environment=PGROOT=/pathto/postgresql/ PIDFile=/pathto/postgresql/data/postmaster.pid

Очищение таблицы

Очищение таблицы tablename и обнуление счетчика с ID.

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;

CASCADE нужен на случай если tablename связана с другой таблицей.

Удаление NULL у поля

ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;

Утилиты

pgcli утилита командной строки с авто-дополнениям и подсветкой синтаксиса.

Установка

pip install pgcli

Запуск

pgcli -U postgres -W dbname

Дополнительное чтиво

blog comments powered by

Иерархические запросы в Oracle обеспечиваются фразой CONNECT BY в операторе SELECT. Эта фраза употребляется в запросе после фразы WHERE и имеет синтаксис, показанный на рис.3.22.

Рисунок 3.22 – Синтаксис фразы CONNECT BY Oracle

Oracle формирует иерархическую выборку, выполняя следующие шаги:

  1. Oracle выбирает корневую строку (строки) иерархии – ту строку, которая удовлетворяет условию в выражении START WITH.
  2. Затем выбираются дочерние строки для каждой корневой строки. Каждая дочерняя строка должна удовлетворять условию в фразе CONNECT BY по отношению к одной из корневых строк.
  3. Выбираются следующие поколения дочерних строк. Сначала выбираются потомки строк, выбранных на шаге 2, потом – их потомки и т.д.
  4. Oracle всегда выбирает потомков, вычисляя условия CONNECT BY относительно текущей родительской строки.
    Если запрос содержит фразу WHERE, исключаются все строки, которые не удовлетворяют условию в фразе WHERE. Oracle вычисляет эти условия для каждой строки, а не просто удаляет всех потомков строки, которая не удовлетворяет условию.

Оператор SELECT, выполняющий иерархический запрос, не может содержать соединение.

Выражение START WITH – задает строку/строки, лежащие в корне иерархии. Это выражение определяет условие, которому должны соответствовать корневые строки. Условие может содержать вложенные запросы. Если эта фраза не задана, то все строки таблицы являются корневыми.

CONNECT BY – задает отношение между родительскими и дочерними строками в иерархии.

Дешёвые авиабилеты Антананариву — Умео находятся здесь

Отношение задается «P-условием», это может быть любое сравнение, но какая-то его часть должна содержать ключевое слово PRIOR, относящееся к родительской строке.

Чтобы найти дочерние строки, Oracle вычисляет PRIOR-выражение для родительской строки, а другое выражение – для каждой строки таблицы. Строки, для которых это выражение дает истину, являются дочерними. CONNECT BY может содержать и другие условия-фильтры. CONNECT BY не может содержать вложенных запросов.

Если CONNECT BY приводит к петле, Oracle возвращает ошибку.

3.5.1.2 Запрос: выбрать фамилии всех прямых начальников сотрудника по фамилии ADAMS.

Обработка иерархии обеспечивается выражением CONNECT BY, которое выполняет рекурсивную выборку строк: условие, по которому выбирается следующая строка, определяется значениями, выбранными в составе текущей строки. В нашем случае следующей выбирается строка, в которой значение manager_id равно значению employee_id в только что выбранной строке. Выражение START WITH определяет условие выборки первой строки.

3.5.1.3 Запрос: вывести структуру подчиненности в фирме.

В решении используется условие CONNECT BY, инвертированное по сравнению с предыдущей задачей. В этом случае следующей выбирается строка, в которой значение employee_id равно значению manager_id в только что выбранной строке, что обеспечивает движение от корня дерева вниз. Начальной строкой является та, которая содержит код должности, соответствующий функции PRESIDENT. Выборки Oracle, использующие иерархические свойства запросов, могут использовать псевдостолбец level. Этот псевдостолбец имеет значение 1 для узла дерева, находящегося в корне, 2 – для узлов, являющихся непосредственными потомками корневого, и т.д.

Источник: http://khpi-iip.mipk.kharkiv.edu/library/extent/dbms/sql/35.html
Оффдоки: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm

Запись опубликована 02.01.2011 в 3:21 пп и размещена в рубрике Oracle PL/SQL. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.

SELECT employee_id, last_name, manager_id FROM employee CONNECT BY PRIOR manager_id=employee_id START WITH last_name=’ADAMS’
SELECT level, employee_id, last_name, manager_id FROM employee CONNECT BY PRIOR employee_id=manager_id START WITH last_name=(SELECT last_name FROM employee, job WHERE employee.job_id=job.job_id ANDFUNCTION=’PRESIDENT’)

Antananarivo Фото со стоков и изображения

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

Если запрошенный адрес имени FQDN не найден в кэше преобразователя или в файле Hosts, клиент выполнит рекурсивный запрос к своему первичному серверу DNS.

Рекурсивный запрос – это запрос на выполнение полного преобразования имени FQDN в адрес IP. Если сервер DNS имеет информацию о записи, он ответит клиенту, завершив запрос ответом. Если сервер DNS не знает ответа, он может выполнить несколько итеративных запросов к корневым серверам системы DNS.

Итеративный запрос является запросом на преобразование только части имени FQDN. Например, если система запрашивает сервер DNS для получения адреса IP имени windata.microsoft.com и сервер DNS не имеет информации об адресе этого узла, сначала он выполнит итеративный запрос к серверу домена com., чтобы узнать адрес сервера DNS для домена microsoft.com.

После этого будет выполнен итеративный запрос к серверу microsoft.com для получения адреса IP узла, который называется windata.

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

10.08. Раздел Локальная сеть

Добавить комментарий

Закрыть меню