Создание таблицы postgresql: PostgreSQL | Создание и удаление таблиц

Postgres Pro Standard : Документация: 11: CREATE TABLE : Компания Postgres Professional

Необязательное предложение INHERITS определяет список таблиц, от которых новая таблица будет автоматически наследовать все столбцы. Родительские таблицы могут быть обычными или сторонними таблицами.

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

Когда в нескольких родительских таблицах оказываются столбцы с одним именем, происходит ошибка, за исключением случая, когда типы данных всех этих столбцов в таблицах совпадают. В этом случае одноимённые столбцы объединяются и формируют один столбец в новой таблице. Если имя столбца новой таблицы совпадает с именем одного из унаследованных столбцов, их типы так же должны совпадать, и в этом случае определения столбцов тоже сливаются в одну. Если в новой таблице явно указывается значение по умолчанию для нового столбца, это значение переопределяет любые значения по умолчанию, унаследованные этим столбцом. В противном случае, если значения по умолчанию определяются в разных родительских таблицах, эти определения должны совпадать, иначе произойдёт ошибка.

Ограничения CHECK объединяются вместе по сути так же, как и столбцы: если несколько родительских таблиц и/или определение новой таблицы содержат одноимённые ограничения CHECK, этим ограничениям должны соответствовать одинаковые выражения проверки, в противном случае произойдёт ошибка. В случае совпадения выражения, эти ограничения с данным выражением объединяются в одно. При этом ограничения со свойством NO INHERIT в родительской таблице исключаются из рассмотрения. Заметьте, что безымянное ограничение CHECK в новой таблице никогда не сливается с другими, так как для него всегда выбирается уникальное имя.

Параметры STORAGE для столбца так же копируются из родительских таблиц.

Если столбец в родительской таблице является столбцом идентификации, это свойство не наследуется. Если требуется, в дочерней таблице этот столбец можно объявить столбцом идентификации.

Создаёт таблицу в виде секции указанной родительской таблицы. Таблицу можно создать либо как секцию для определённых значений (используя FOR VALUES), либо как секцию по умолчанию (используя DEFAULT). Это указание неприемлемо для таблиц, секционируемых по хешу. В создаваемую секцию копируются все индексы, ограничения и пользовательские триггеры уровня строк.

Здесь указание_границ_секции должно соответствовать методу и ключу секционирования родительской таблицы и не должно конфликтовать с другой существующей секцией того же родителя. Вариант указания с IN используется для секционирования по спискам, тогда как вариант с FROM и TO для секционирования по диапазонам, а с

WITH — для секционирования по хешу.

В качестве значений, задаваемых в указании_границ_секции, принимается буквальное значение, NULL и MINVALUE или MAXVALUE. Буквальным значением должна быть либо числовая константа, приводимая к типу соответствующего столбца ключа разбиения, либо строковая константа, представляющая допустимое входное значение для этого типа.

При создании секции с разбиением по спискам возможно указать, что столбец ключа разбиения может содержать NULL, включив в список секции NULL. Однако у отдельно взятой родительской таблицы может быть не больше одной такой секции. Для диапазонных секций NULL задать нельзя.

При создании диапазонной секции нижняя граница, задаваемая во FROM, включается в диапазон, а верхняя граница, задаваемая в

TO — исключается. То есть, значения, задаваемые в списке FROM, являются допустимыми значениями соответствующих столбцов ключа разбиения для этой секции, а значения в списке TO — нет. Заметьте, что это утверждение должно восприниматься с учётом правил сравнения строк таблицы (см. Подраздел 9.23.5). Например, с разбиением PARTITION BY RANGE (x,y), секция с границами FROM (1, 2) TO (3, 4) примет x=1 с любым значением y>=2, x=2 с любым y, отличным от NULL, и x=3 с любым y<4.

Специальные значения MINVALUE и MAXVALUE могут использоваться при создании диапазонной секции для указания, что нижняя или верхняя граница для значений столбца отсутствует. Например, секция, определённая с указанием FROM (MINVALUE) TO (10)

, будет принимать любые значения меньше 10, а секция, определённая с указанием FROM (10) TO (MAXVALUE), — любые значения, которые больше или равны 10.

При создании диапазонной секции с более чем одним столбцом может также иметь смысл использовать MAXVALUE в определении нижней границы, а MINVALUE — верхней. Например, секция, определённая с указанием FROM (0, MAXVALUE) TO (10, MAXVALUE), будет принимать любые строки, в которых первый столбец ключа разбиения больше 0 и меньше или равен 10. Подобным образом, секция, определённая с указанием FROM ('a', MINVALUE) TO ('b', MINVALUE), будет принимать строки, в которых первый столбец ключа разбиения начинается с «a».

Заметьте, что если для одного столбца в границе секции задаётся MINVALUE или MAXVALUE, то же значение должно применяться и для всех последующих столбцов. Например, граница

(10, MINVALUE, 0) будет некорректной; допустимая граница: (10, MINVALUE, MINVALUE).

Также заметьте, что для некоторых типов элементов, таких как timestamp, наряду с другими значениями допускается значение «infinity» (бесконечность). Оно отличается от вариантов MINVALUE и MAXVALUE, которые на самом деле не обозначают значения, которые можно сохранить, а просто говорят о том, это значение не ограничено. MAXVALUE можно воспринимать как значение, которое больше любого другого, включая «бесконечность», а MINVALUE меньше любого другого значения, включая «минус бесконечность». Таким образом, диапазон FROM ('infinity') TO (MAXVALUE) не будет пустым, а будет принимать ровно одно значение — «infinity».

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

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

При создании секции с разбиением по хешу должен задаваться модуль и остаток. Модулем должно быть положительное число, а остатком неотрицательное число, меньшее модуля. Обычно при начальной настройке таблицы с секционированием по хешу нужно выбрать модуль, равный количеству секций, и назначить каждой секции этот модуль и разные остатки (см. примеры ниже). Однако секциям можно назначить и разные модули, с условием, что модули, назначенные секциям таблицы, разбиваемой по хешу, являются делителями следующих больших модулей. Это позволяет постепенно увеличивать число секций, не производя полное перемещение всех данных. Например, предположим, что у вас есть таблица, разбиваемая по хешу на 8 секций, для каждой из которых назначен модуль 8, и возникла необходимость увеличить число секций до 16. Вы можете отсоединить одну из секций по модулю 8, создать две новые секции по модулю 16, покрывающих ту же часть пространства ключа (одну с остатком, равным остатку отсоединённой секции, а вторую с остатком, равным тому же остатку плюс 8), и вновь наполнить их данными. Затем вы можете повторять эту операцию (возможно, позже) для следующих секций по модулю 8, пока таковых не останется. Хотя и при таком подходе может потребоваться перемещать большие объёмы данных на каждом этапе, это всё же лучше, чем создавать абсолютно новую таблицу и перемещать все данные сразу.

В секции должны содержаться столбцы с теми же именами и типами, что и в секционированной таблице, к которой она относится. Если родитель имеет характеристику WITH OIDS, все секции должны иметь OID; родительский столбец OID будет наследоваться всеми секциями, как и любой другой столбец. Изменение имён и типов столбцов в секционируемой таблице, а также добавление или удаление столбца OID будет автоматически распространяться во все секции. Ограничения CHECK будут наследоваться автоматически всеми секциями, но для отдельных секций могут быть заданы дополнительные ограничения CHECK; дополнительные ограничения с теми же именами и условиями, как в родительской таблице, будут объединены с родительским ограничением. Также независимо для каждой секции могут быть заданы значения по умолчанию. Но заметьте, что значение по умолчанию, заданное на уровне секции, не будет действовать при добавлении строк через секционированную таблицу.

Строки, добавляемые в секционированную таблицу, будут автоматически перенаправляться в соответствующую секцию. Если подходящей секции не найдётся, произойдёт ошибка.

Такие операции, как TRUNCATE, обычно затрагивают и саму таблицу, и каскадно распространяются на все дочерние секции, но могут также выполняться в отдельных секциях. Заметьте, что для удаления секции с помощью DROP TABLE требуется установить блокировку ACCESS EXCLUSIVE в родительской таблице.

PostgreSQL : Документация: 9.6: psql : Компания Postgres Professional

-a

--echo-all

Отправляет на стандартный вывод все непустые входные строки по мере их чтения. (Это не относится к строкам, считанным в интерактивном режиме.) Эквивалентно установке переменной ECHO в значение all.

-A
--no-align

Переключение на невыровненный режим вывода. (По умолчанию, наоборот, используется выровненный режим вывода.)

-b
--echo-errors

Посылает все команды SQL с ошибками на стандартный вывод. Равнозначно присвоению переменной ECHO значения errors.

-c команда
--command=команда

Передаёт psql команду для выполнения. Этот ключ можно повторять и комбинировать в любом порядке с ключом -f. Когда указывается

-c или -f, psql не читает команды со стандартного ввода; вместо этого она завершается сразу после обработки всех ключей -c и -f по порядку.

Заданная команда должна быть либо командной строкой, которая полностью интерпретируется сервером (т. е. не использует специфические функции psql), либо одиночной командой с обратной косой чертой. Таким образом, используя -c, нельзя смешивать метакоманды SQL и psql. Но это можно сделать, передав несколько ключей -c или передав строку в psql через канал:

psql -c '\x' -c 'SELECT * FROM foo;'

или

echo '\x \\ SELECT * FROM foo;' | psql

(\\ — разделитель метакоманд.)

Каждая строка SQL-команд, заданная ключом -c, передаётся на сервер как один запрос. Поэтому сервер выполняет её в одной транзакции, даже когда эта строка содержит несколько команд SQL, если только в ней не содержатся явные команды

BEGIN/COMMIT, разделяющие её на несколько транзакций. Кроме того, psql печатает результат только последней SQL-команды в строке. Это отличается от поведения, когда та же строка считывается из файла или подаётся на стандартный ввод psql, так как в последнем случае psql передаёт каждую команду SQL отдельно.

Из-за такого поведения указание нескольких команд в одной строке -c часто приводит к неожиданным результатам. Поэтому лучше использовать несколько ключей -c или подавать команды на стандартный ввод psql, применяя либо echo, как показано выше, либо создаваемый прямо в оболочке документ, например:

psql <<EOF
\x
SELECT * FROM foo;
EOF
-d имя_бд
--dbname=имя_бд

Указывает имя базы данных для подключения. Равносильно указанию имя_бд в первом аргументе командной строки, отличном от ключа.

Если этот параметр содержит знак = или начинается с допустимого префикса URI (postgresql:// или postgres://), он воспринимается как строка conninfo. За дополнительными сведениями обратитесь к Подразделу 32.1.1.

-e
--echo-queries

Посылает все команды SQL, отправленные на сервер, ещё и на стандартный вывод. Эквивалентно установке переменной ECHO в значение queries.

-E
--echo-hidden

Отображает фактические запросы, генерируемые \d и другими командами, начинающимися с \. Это можно использовать для изучения внутренних операций в psql. Эквивалентно установке переменной ECHO_HIDDEN значения on.

-f имя_файла
--file=имя_файла

Читает команды из файла имя_файла, а не из стандартного ввода. Этот ключ можно повторять и комбинировать в любом порядке с ключом -c. Если указан ключ -c или -f, программа psql не читает команды со стандартного ввода; вместо этого она завершается после обработки всех ключей -c и -f по очереди. Не считая этого, данный ключ по большому счёту равнозначен метакоманде \i.

Если имя_файла задано символом - (минус), считывается стандартный ввод до признака конца файла или до метакоманды \q. Это позволяет перемежать интерактивный ввод с вводом из файлов. Однако заметьте, что Readline в этом случае не применяется (так же, как и с ключом -n).

Использование этого параметра немного отличается от psql < имя_файла. В основном, оба варианта будут делать то, что вы ожидаете, но с -f доступны некоторые полезные свойства, такие как сообщения об ошибках с номерами строк. Также есть небольшая вероятность, что запуск в таком режиме будет быстрее. С другой стороны, вариант с перенаправлением ввода из командного интерпретатора (в теории) гарантирует получение точно такого же вывода, какой вы получили бы, если бы ввели всё вручную.

-F разделитель
--field-separator=разделитель

Использование разделитель в качестве разделителя полей при невыровненном режиме вывода. Эквивалентно \pset fieldsep или \f.

-h компьютер
--host=компьютер

Указывает имя компьютера, на котором работает сервер. Если значение начинается с косой черты, оно определяет каталог Unix-сокета.

-H
--html

Включает табличный вывод в формате HTML. Эквивалентно \pset format html или команде \H.

-l
--list

Выводит список всех доступных баз данных и завершает работу. Другие параметры, не связанные с соединением, игнорируются. Это похоже на метакоманду \list.

-L имя_файла
--log-file=имя_файла

В дополнение к обычному выводу, записывает вывод результатов всех запросов в файл имя_файла.

-n
--no-readline

Отключает использование Readline для редактирования командной строки и использования истории команд. Может быть полезно для выключения расширенных действий клавиши табуляции при вырезании и вставке.

-o имя_файла
--output=имя_файла

Записывает вывод результатов всех запросов в файл имя_файла. Эквивалентно команде \o.

-p порт
--port=порт

Указывает TCP-порт или расширение файла локального Unix-сокета, через который сервер принимает подключения. Значение по умолчанию определяется переменной среды PGPORT, если она установлена, либо числом, заданным при компиляции, обычно 5432.

-P присвоение
--pset=присвоение

Задаёт параметры печати, в стиле команды \pset. Обратите внимание, что имя параметра и значение разделяются знаком равенства, а не пробела. Например, чтобы установить формат вывода в LaTeX, нужно написать -P format=latex.

-q
--quiet

Указывает, что psql должен работать без вывода дополнительных сообщений. По умолчанию, выводятся приветствия и различные информационные сообщения. Этого не произойдёт с использованием данного параметра. Полезно вместе с параметром -c. Этот же эффект можно получить, установив для переменной QUIET значение on.

-R разделитель
--record-separator=разделитель

Использует разделитель как разделитель записей при невыровненном режиме вывода. Эквивалентно команде \pset recordsep.

-s
--single-step

Запуск в пошаговом режиме. Это означает, что пользователь будет подтверждать выполнение каждой команды, отправляемой на сервер, с возможностью отменить выполнение. Используется для отладки скриптов.

-S
--single-line

Запуск в однострочном режиме, при котором символ новой строки завершает SQL-команды, так же как это делает точка с запятой.

Примечание

Этот режим реализован для тех, кому он нужен, но это не обязательно означает, что и вам нужно его использовать. В частности, если смешивать в одной строке команды SQL и метакоманды, порядок их выполнения может быть не всегда понятен для неопытного пользователя.

-t
--tuples-only

Отключает вывод имён столбцов и результирующей строки с количеством выбранных записей. Эквивалентно команде \t.

-T параметры_таблицы
--table-attr=параметры_таблицы

Задаёт атрибуты, которые будут вставлены в тег HTML table. За подробностями обратитесь к описанию \pset.

-U имя_пользователя
--username=имя_пользователя

Использовать для подключения к базе данных имя_пользователя вместо подразумеваемого по умолчанию. (Разумеется, это потребует соответствующего разрешения.)

-v присвоение
--set=присвоение
--variable=присвоение

Выполняет присвоение значения переменной, как метакоманда \set. Обратите внимание на то, что необходимо разделить имя переменной и значение (при наличии) знаком равенства в командной строке. Чтобы сбросить переменную, опустите знак равенства. Чтобы установить пустое значение, поставьте знак равенства, но опустите значение. Присваивания выполняются на очень ранней стадии запуска, поэтому если переменные зарезервированы для внутренних целей, то позже они могут быть перезаписаны.

-V
--version

Выводит версию psql и завершает работу.

-w
--no-password

Не выдавать запрос на ввод пароля. Если сервер требует аутентификацию по паролю и пароль не доступен с помощью других средств, таких как файл .pgpass, попытка соединения не удастся. Этот параметр может быть полезен в пакетных заданиях и скриптах, где нет пользователя, который вводит пароль.

Обратите внимание, что этот параметр действует на протяжении всей сессии и, таким образом, влияет на метакоманду \connect, так же как и на первую попытку соединения.

-W
--password

Принудительно запрашивать пароль перед подключением к базе данных.

Это несущественный параметр, так как psql запрашивает пароль автоматически, если сервер проверяет подлинность по паролю. Однако, чтобы понять это, psql лишний раз подключается к серверу. Поэтому иногда имеет смысл ввести -W, чтобы исключить эту ненужную попытку подключения.

Обратите внимание, что этот параметр действует на протяжении всей сессии и, таким образом, влияет на метакоманду \connect, так же как и на первую попытку соединения.

-x
--expanded

Включает режим развёрнутого вывода таблицы. Эквивалентно команде \x.

-X,
--no-psqlrc

Не читать стартовые файлы (ни общесистемный файл psqlrc, ни пользовательский файл ~/.psqlrc).

-z
--field-separator-zero

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

-0
--record-separator-zero

Установить нулевой байт в качестве разделителя записей для невыровненного режима вывода. Это полезно при взаимодействии с другими программами, например, с xargs -0.

-1
--single-transaction

Этот параметр может применяться только в сочетании с одним или несколькими параметрами -c и/или -f. С ним psql выполняет команду BEGIN перед обработкой первого такого параметра и COMMIT после последнего, заворачивая таким образом все команды в одну транзакцию. Это гарантирует, что либо все команды завершатся успешно, либо никакие изменения не сохранятся.

Если в самих этих командах содержатся операторы BEGIN, COMMIT или ROLLBACK, этот параметр не даст желаемого эффекта. Кроме того, если какая-либо отдельная команда не может выполняться внутри блока транзакции, с этим параметром вся транзакция прервётся с ошибкой.

-?
--help[=тема]

Показать справку по psql и завершиться. Необязательный параметр тема (по умолчанию options) выбирает описание интересующей части psql: commands описывает команды psql с обратной косой чертой; options описывает параметры командной строки, которые можно передать psql; а variables выдаёт справку по переменным конфигурации psql.

PostgreSQL : Документация: 12: CREATE INDEX : Компания Postgres Professional

Параметры хранения индекса

Необязательное предложение WITH определяет параметры хранения для индекса. У каждого метода индекса есть свой набор допустимых параметров хранения. Следующий параметр принимают методы B-дерево, хеш, GiST и SP-GiST:

fillfactor

Фактор заполнения для индекса определяет в процентном отношении, насколько плотно метод индекса будет заполнять страницы индекса. Для B-деревьев концевые страницы заполняются до этого процента при начальном построении индекса и позже, при расширении индекса вправо (добавлении новых наибольших значений ключа). Если страницы впоследствии оказываются заполненными полностью, они будут разделены, что приводит к постепенному снижению эффективности индекса. Для B-деревьев по умолчанию используется фактор заполнения 90, но его можно поменять на любое целое значение от 10 до 100. Фактор заполнения, равный 100, полезен для статических таблиц и помогает уменьшить физический размер таблицы, но для интенсивно изменяемых таблиц лучше использовать меньшее значение, чтобы разделять страницы приходилось реже. С другими методами индекса фактор заполнения действует по-другому, но примерно в том же ключе; значение фактора заполнения по умолчанию для разных методов разное.

Индексы B-дерево дополнительно принимают этот параметр:

Индексы GiST дополнительно принимают этот параметр:

buffering

Определяет, будет ли при построении индекса использоваться буферизация, описанная в Подразделе 64.4.1. Со значением OFF она отключена, с ON — включена, а с AUTO — отключена вначале, но может затем включиться на ходу, как только размер индекса достигнет значения effective_cache_size. По умолчанию подразумевается AUTO.

Индексы GIN принимают другие параметры:

fastupdate

Этот параметр управляет механизмом быстрого обновления, описанным в Подразделе 66.4.1. Он имеет логическое значение: ON включает быстрое обновление, OFF отключает его. (Другие возможные написания ON и OFF перечислены в Разделе 19.1.) Значение по умолчанию — ON.

Примечание

Выключение fastupdate в ALTER INDEX предотвращает помещение добавляемых в дальнейшем строк в список записей, ожидающих индексации, но записи, добавленные в этот список ранее, в нём остаются. Чтобы очистить очередь операций, надо затем выполнить VACUUM для этой таблицы или вызвать функцию gin_clean_pending_list.

gin_pending_list_limit

Пользовательский параметр gin_pending_list_limit. Его значение задаётся в килобайтах.

Индексы BRIN принимают другие параметры:

pages_per_range

Определяет, сколько блоков таблицы образуют зону блоков для каждой записи в индексе BRIN (за подробностями обратитесь к Разделу 67.1). Значение по умолчанию — 128.

autosummarize

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

Неблокирующее построение индексов

Создание индекса может мешать обычной работе с базой данных. Обычно PostgreSQL блокирует запись в индексируемую таблицу и выполняет всю операцию построения индекса за одно сканирование таблицы. Другие транзакции могут продолжать читать таблицу, но при попытке вставить, изменить или удалить строки в таблице они будут заблокированы до завершения построения индекса. Это может оказать нежелательное влияние на работу производственной базы данных. Индексация очень больших таблиц может занимать много часов, и даже для маленьких таблиц построение индекса может заблокировать записывающие процессы на время, неприемлемое для производственной системы.

PostgreSQL поддерживает построение индексов без блокировки записи. Этот метод выбирается указанием CONCURRENTLY команды CREATE INDEX. Когда он используется, PostgreSQL должен выполнить два сканирования таблицы, а кроме того, должен дождаться завершения всех существующих транзакций, которые потенциально могут модифицировать и использовать этот индекс. Таким образом, эта процедура требует проделать в сумме больше действий и выполняется значительно дольше, чем обычное построение индекса. Однако благодаря тому, что этот метод позволяет продолжать обычную работу с базой во время построения индекса, он оказывается полезным в производственной среде. Хотя разумеется, дополнительная нагрузка на процессор и подсистему ввода/вывода, создаваемая при построении индекса, может привести к замедлению других операций.

При неблокирующем построении индекса он попадает в системный каталог в одной транзакции, затем ещё два сканирования таблицы выполняются в двух других транзакциях. Перед каждым сканированием таблицы процедура построения индекса должна ждать завершения текущих транзакций, модифицировавших эту таблицу. После второго сканирования также необходимо дожидаться завершения всех транзакций, получивших снимок (см. Главу 13) перед вторым сканированием. Наконец индекс может быть помечен как готовый к использованию, после чего команда CREATE INDEX завершается. Однако даже тогда индекс может быть не готов немедленно к применению в запросах: в худшем случае он не будет использоваться, пока существуют транзакции, начатые до начала построения индекса.

Если при сканировании таблицы возникает проблема, например взаимоблокировка или нарушение уникальности в уникальном индексе, команда CREATE INDEX завершится ошибкой, но оставит после себя «нерабочий» индекс. Этот индекс будет игнорироваться при чтении данных, так как он может быть неполным; однако с ним могут быть связаны дополнительные операции при изменениях. В psql встроенная команда \d помечает такой индекс как INVALID:

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID

Рекомендуемый в таких случаях способ исправления ситуации — удалить индекс и затем попытаться снова выполнить CREATE INDEX CONCURRENTLY. (Кроме того, можно перестроить его с помощью команды REINDEX INDEX CONCURRENTLY.)

Ещё одна сложность, с которой можно столкнуться при неблокирующем построении уникального индекса, заключается в том, что ограничение уникальности уже влияет на другие транзакции, когда второе сканирование таблицы только начинается. Это значит, что нарушения ограничения могут проявляться в других запросах до того, как индекс становится доступным для использования и даже тогда, когда создать индекс в итоге не удаётся. Кроме того, если при втором сканировании происходит ошибка, «нерабочий» индекс оставляет в силе своё ограничение уникальности и дальше.

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

Обычное построение индекса допускает одновременное построение других индексов для таблицы обычным методом, но неблокирующее построение для конкретной таблицы в один момент времени допускается только одно. Однако в любом случае никакие другие изменения схемы таблицы в это время не разрешаются. Другое отличие состоит в том, что в блоке транзакции может быть выполнена обычная команда CREATE INDEX, но не CREATE INDEX CONCURRENTLY.

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

PostgreSQL : Документация: 11: CREATE INDEX : Компания Postgres Professional

Параметры хранения индекса

Необязательное предложение WITH определяет параметры хранения для индекса. У каждого метода индекса есть свой набор допустимых параметров хранения. Следующий параметр принимают методы B-дерево, хеш, GiST и SP-GiST:

fillfactor

Фактор заполнения для индекса определяет в процентном отношении, насколько плотно метод индекса будет заполнять страницы индекса. Для B-деревьев концевые страницы заполняются до этого процента при начальном построении индекса и позже, при расширении индекса вправо (добавлении новых наибольших значений ключа). Если страницы впоследствии оказываются заполненными полностью, они будут разделены, что приводит к постепенному снижению эффективности индекса. Для B-деревьев по умолчанию используется фактор заполнения 90, но его можно поменять на любое целое значение от 10 до 100. Фактор заполнения, равный 100, полезен для статических таблиц и помогает уменьшить физический размер таблицы, но для интенсивно изменяемых таблиц лучше использовать меньшее значение, чтобы разделять страницы приходилось реже. С другими методами индекса фактор заполнения действует по-другому, но примерно в том же ключе; значение фактора заполнения по умолчанию для разных методов разное.

Индексы B-дерево дополнительно принимают этот параметр:

Индексы GiST дополнительно принимают этот параметр:

buffering

Определяет, будет ли при построении индекса использоваться буферизация, описанная в Подразделе 64.4.1. Со значением OFF она отключена, с ON — включена, а с AUTO — отключена вначале, но может затем включиться на ходу, как только размер индекса достигнет значения effective_cache_size. По умолчанию подразумевается AUTO.

Индексы GIN принимают другие параметры:

fastupdate

Этот параметр управляет механизмом быстрого обновления, описанным в Подразделе 66.4.1. Он имеет логическое значение: ON включает быстрое обновление, OFF отключает его. (Другие возможные написания ON и OFF перечислены в Разделе 19.1.) Значение по умолчанию — ON.

Примечание

Выключение fastupdate в ALTER INDEX предотвращает помещение добавляемых в дальнейшем строк в список записей, ожидающих индексации, но записи, добавленные в этот список ранее, в нём остаются. Чтобы очистить очередь операций, надо затем выполнить VACUUM для этой таблицы или вызвать функцию gin_clean_pending_list.

gin_pending_list_limit

Пользовательский параметр gin_pending_list_limit. Его значение задаётся в килобайтах.

Индексы BRIN принимают другие параметры:

pages_per_range

Определяет, сколько блоков таблицы образуют зону блоков для каждой записи в индексе BRIN (за подробностями обратитесь к Разделу 67.1). Значение по умолчанию — 128.

autosummarize

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

Неблокирующее построение индексов

Создание индекса может мешать обычной работе с базой данных. Обычно PostgreSQL блокирует запись в индексируемую таблицу и выполняет всю операцию построения индекса за одно сканирование таблицы. Другие транзакции могут продолжать читать таблицу, но при попытке вставить, изменить или удалить строки в таблице они будут заблокированы до завершения построения индекса. Это может оказать нежелательное влияние на работу производственной базы данных. Индексация очень больших таблиц может занимать много часов, и даже для маленьких таблиц построение индекса может заблокировать записывающие процессы на время, неприемлемое для производственной системы.

PostgreSQL поддерживает построение индексов без блокировки записи. Этот метод выбирается указанием CONCURRENTLY команды CREATE INDEX. Когда он используется, PostgreSQL должен выполнить два сканирования таблицы, а кроме того, должен дождаться завершения всех существующих транзакций, которые потенциально могут модифицировать и использовать этот индекс. Таким образом, эта процедура требует проделать в сумме больше действий и выполняется значительно дольше, чем обычное построение индекса. Однако благодаря тому, что этот метод позволяет продолжать обычную работу с базой во время построения индекса, он оказывается полезным в производственной среде. Хотя разумеется, дополнительная нагрузка на процессор и подсистему ввода/вывода, создаваемая при построении индекса, может привести к замедлению других операций.

При неблокирующем построении индекса он попадает в системный каталог в одной транзакции, затем ещё два сканирования таблицы выполняются в двух других транзакциях. Перед каждым сканированием таблицы процедура построения индекса должна ждать завершения текущих транзакций, модифицировавших эту таблицу. После второго сканирования также необходимо дожидаться завершения всех транзакций, получивших снимок (см. Главу 13) перед вторым сканированием. Наконец индекс может быть помечен как готовый к использованию, после чего команда CREATE INDEX завершается. Однако даже тогда индекс может быть не готов немедленно к применению в запросах: в худшем случае он не будет использоваться, пока существуют транзакции, начатые до начала построения индекса.

Если при сканировании таблицы возникает проблема, например взаимоблокировка или нарушение уникальности в уникальном индексе, команда CREATE INDEX завершится ошибкой, но оставит после себя «нерабочий» индекс. Этот индекс будет игнорироваться при чтении данных, так как он может быть неполным; однако с ним могут быть связаны дополнительные операции при изменениях. В psql встроенная команда \d помечает такой индекс как INVALID:

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID

Рекомендуемый в таких случаях способ исправления ситуации — удалить индекс и затем попытаться снова выполнить CREATE INDEX CONCURRENTLY. (Кроме того, можно перестроить его с помощью команды REINDEX. Но так как REINDEX не поддерживает неблокирующий режим, вряд ли этот вариант будет желательным.)

Ещё одна сложность, с которой можно столкнуться при неблокирующем построении уникального индекса, заключается в том, что ограничение уникальности уже влияет на другие транзакции, когда второе сканирование таблицы только начинается. Это значит, что нарушения ограничения могут проявляться в других запросах до того, как индекс становится доступным для использования и даже тогда, когда создать индекс в итоге не удаётся. Кроме того, если при втором сканировании происходит ошибка, «нерабочий» индекс оставляет в силе своё ограничение уникальности и дальше.

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

Обычное построение индекса допускает одновременное построение других индексов для таблицы обычным методом, но неблокирующее построение для конкретной таблицы в один момент времени допускается только одно. Однако в любом случае никакие другие изменения схемы таблицы в это время не разрешаются. Другое отличие состоит в том, что в блоке транзакции может быть выполнена обычная команда CREATE INDEX, но не CREATE INDEX CONCURRENTLY.

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

Как создать и удалить таблицу в PostgreSQL [Пример]

  • На главную
  • Тестирование

      • Назад
      • Agile Testing
      • BugZilla
      • Cucumber
      • A Database Testing
      • Тестирование базы данных
      • Назад
      • JUnit
      • LoadRunner
      • Ручное тестирование
      • Мобильное тестирование
      • Mantis
      • Почтальон
      • QTP
      • Назад
      • SAP
      • 00030003 Центр контроля качества
      • SoapUI
      • Управление тестированием
      • TestLink
  • SAP

      • Назад
      • ABAP
      • APO
      • Начинающий
      • Basis
      • BODS
      • BI
      • BPC
      • CO
      • Назад
      • CRM
      • Crystal Reports
      • Crystal Reports
      • FICO
      • Заработная плата
      • Назад
      • PI / PO
      • PP
      • SD
      • SAPUI5
      • Безопасность
      • Менеджер решений
      • Successfactors
      • SAP Tutorials
      4
    • Web
    • Apache
    • AngularJS
    • ASP.Net
    • C
    • C #
    • C ++
    • CodeIgniter
    • СУБД
    • JavaScript
    • Назад
    • Java
    • JSP
    • Kotlin
    • Linux
    • Linux
    • Kotlin
    • Linux
    • js
    • Perl
    • Назад
    • PHP
    • PL / SQL
    • PostgreSQL
    • Python
    • ReactJS
    • Ruby & Rails
    • Scala
    • SQL
    • 000
    • SQL
    • 000 0003 SQL 000 0003 SQL 000
    • UML
    • VB.Net
    • VBScript
    • Веб-службы
    • WPF
  • Обязательно учите!

      • Назад
      • Бухгалтерский учет
      • Алгоритмы
      • Android
      • Блокчейн
      • Business Analyst
      • Создание веб-сайта
      • CCNA
      • Облачные вычисления
      • 00030003 COBOL 9000 Compiler
          9000 Встроенные системы
        • 00030002 9000 Compiler 9000
        • Ethical Hacking
        • Учебники по Excel
        • Программирование на Go
        • IoT
        • ITIL
        • Jenkins
        • MIS
        • Сеть
        • Операционная система
        • Назад
        • Управление проектами Обзоры
        • Salesforce
        • SEO
        • Разработка программного обеспечения
        • VB A
    • Big Data

        • Назад
        • AWS
        • BigData
        • Cassandra
        • Cognos
        • Хранилище данных
        • 0003
        • HBOps
        • 0003
        • HBOps
        • 0003
        • MicroStrategy
    .

    PostgreSQL: СОЗДАТЬ ТАБЛИЦУ КАК Заявление


    В этом руководстве по PostgreSQL объясняется, как использовать оператор PostgreSQL CREATE TABLE AS с синтаксисом и примерами.

    Описание

    Оператор PostgreSQL CREATE TABLE AS используется для создания таблицы из существующей таблицы путем копирования столбцов существующей таблицы.

    Важно отметить, что при создании таблицы таким способом новая таблица будет заполнена записями из существующей таблицы (на основе оператора SELECT).

    Синтаксис

    Синтаксис оператора CREATE TABLE AS в PostgreSQL:

     СОЗДАТЬ ТАБЛИЦУ new_table AS
      ВЫБРАТЬ выражения
      ИЗ существующих_таблиц
      [ГДЕ условия]; 

    Параметры или аргументы

    новая_таблица
    Имя таблицы, которую вы хотите создать.
    выражения
    Столбцы из existing_tables , которые вы хотите создать в new_table .Определения столбцов из перечисленных столбцов будут перенесены в создаваемую вами new_table .
    существующие_таблицы
    Существующие таблицы, из которых можно скопировать определения столбцов и связанные записи (согласно предложению WHERE).
    ГДЕ условия
    Необязательно. Условия, которые должны быть выполнены для копирования записей в new_table .

    Примечание

    • Определения столбцов из existing_tables будут скопированы в new_table .
    • new_table будет заполнена записями в соответствии с условиями в предложении WHERE.

    Пример

    Давайте посмотрим на пример PostgreSQL CREATE TABLE AS, который показывает, как создать таблицу путем копирования всех столбцов из другой таблицы.

     СОЗДАТЬ ТАБЛИЦУ current_inventory AS
      ВЫБРАТЬ *
      ИЗ продуктов
      ГДЕ количество> 0; 

    В этом примере будет создана новая таблица с именем current_inventory , включающая все столбцы из таблицы products .

    Если бы были записи в таблице products , то новая таблица current_inventory была бы заполнена записями, возвращаемыми оператором SELECT. Другими словами, все записи из таблицы продуктов с количеством больше 0 будут вставлены в таблицу current_inventory при ее создании.

    Затем давайте рассмотрим пример CREATE TABLE AS, который показывает, как создать таблицу путем копирования выбранных столбцов из нескольких таблиц.

    Например:

     СОЗДАТЬ ТАБЛИЦУ current_inventory AS
      ВЫБЕРИТЕ products.product_id, products.product_name, category.category_name
      ИЗ продуктов
      Категории INNER JOIN
      НА products.category_id = category.category_id
      ГДЕ products.quantity> 0; 

    В этом примере будет создана новая таблица с именем current_inventory на основе определений столбцов из таблиц продуктов и категорий .

    Опять же, в новую таблицу current_inventory будут помещены только записи, удовлетворяющие условиям оператора SELECT.

    .

    postgresql — Создание временных таблиц в SQL

    Переполнение стека
    1. Около
    2. Продукты
    3. Для команд
    1. Переполнение стека Общественные вопросы и ответы
    2. Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
    3. Вакансии Программирование и связанные с ним технические возможности карьерного роста
    4. Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
    5. Реклама Обратитесь к разработчикам и технологам со всего мира
    6. О компании

    Загрузка…

    .

    sql — PostgreSQL создает таблицу, если не существует

    Переполнение стека
    1. Около
    2. Продукты
    3. Для команд
    1. Переполнение стека Общественные вопросы и ответы
    2. Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
    3. Вакансии Программирование и связанные с ним технические возможности карьерного роста
    4. Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
    5. Реклама Обратитесь к разработчикам и технологам со всего мира
    6. О компании
    .
  • Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *