PostgreSQL присваивает пятисимвольные коды ошибок, которые соответствуют «SQLSTATE». Приложения обычно проверяют код ошибки и только потом обращаются к текстовому сообщению об ошибке. Коды ошибок и текстовое сообщение не меняются в новых выпусках. Обратите внимание, что не все коды ошибок, создаваемые PostgreSQL, определены в стандарте SQL. Некоторые дополнительные коды ошибок были добавлены самостоятельно или заимствованы из других баз данных.
Первые два символа кода ошибки представляют класс ошибок, а последние три символа — конкретное условие в этом классе. Таким образом, если вы не знаете значение конкретного кода ошибки, вы можете определить причину по классу ошибки.
Все коды ошибок и классы ошибок перечислены в статье. Для каждого класса ошибок существует свой «стандарт», код ошибки с тремя последними нулями. Этот код указывается только для условий ошибки, которые принадлежат к определенному классу, но не имеют конкретного кода. Читайте так же: Расшифровка кодов ошибок MySQL.
Класс 00 — Успешное завершение
00000
successful_completion
УСПЕШНОЕ ЗАВЕРШЕНИЕ
Класс 01 — Предупреждение
01000
warning
ПРЕДУПРЕЖДЕНИЕ
0100C
dynamic_result_sets_returned
ВОЗВРАЩЕНЫ ДИНАМИЧЕСКИЕ НАБОРЫ РЕЗУЛЬТАТОВ
01008
implicit_zero_bit_padding
НЕЯВНОЕ ЗАПОЛНЕНИЕ НУЛЕВОГО БИТА
01003
null_value_eliminated_in_set_function
НУЛЕВОЕ ЗНАЧЕНИЕ ИСКЛЮЧЕНО В ФУНКЦИИ SET
01007
privilege_not_granted
ПРИВИЛЕГИЯ НЕ ПРЕДОСТАВЛЕНА
1006
privilege_not_revoked
ПРИВИЛЕГИЯ НЕ ОТОЗВАНА
01004
string_data_right_truncation
УСЕЧЕНИЕ ПРАВОЙ ЧАСТИ СТРОКОВЫХ ДАННЫХ
01P01
deprecated_feature
УСТАРЕВШАЯ ФУНКЦИЯ
Класс 02 — Нет данных (это также класс предупреждения в соответствии со стандартом SQL)
02000
no_data
НЕТ ДАННЫХ
02001
no_additional_dynamic_result_sets_returned
ДОПОЛНИТЕЛЬНЫЕ ДИНАМИЧЕСКИЕ НАБОРЫ РЕЗУЛЬТАТОВ НЕ ВОЗВРАЩАЮТСЯ
Класс 03 — SQL-запрос еще не завершен
03000
sql_statement_not_yet_complete
SQL-ЗАПРОС ЕЩЕ НЕ ЗАВЕРШЕН
Класс 08 — Исключение подключения
08000
connection_exception
ИСКЛЮЧЕНИЕ СОЕДИНЕНИЯ
08003
connection_does_not_exist
СОЕДИНЕНИЕ НЕ СУЩЕСТВУЕТ
08006
connection_failure
СБОЙ СОЕДИНЕНИЯ
08001
sqlclient_unable_to_establish_sqlconnection
SQLCLIENT НЕ МОЖЕТ УСТАНОВИТЬ SQL-СОЕДИНЕНИЕ
008004
sqlserver_rejected_establishment_of_sqlconnection
SQLSERVER ОТКЛОНИЛ СОЗДАНИЕ SQL-СОЕДИНЕНИЯ
08007
transaction_resolution_unknown
РАЗРЕШЕНИЕ ТРАНЗАКЦИИ НЕИЗВЕСТНО
08P01
protocol_violation
Нарушение ПРОТОКОЛА
Класс 09 — Спровоцированное исключение действия
09000
triggered_action_exception
ИСКЛЮЧЕНИЕ СРАБОТАВШЕГО ДЕЙСТВИЯ
Класс 0A — Функция не поддерживается
0A000
feature_not_supported
ФАТУРА НЕ ПОДДЕРЖИВАЕТСЯ Класс 0B — Неправильная инициация транзакции
0B000
invalid_transaction_initiation
НЕКОРРЕКТНОЕ ИНИЦИИРОВАНИЕ ТРАНЗАКЦИИ
Класс 0F — Исключение локатора
0F000
locator_exception
ИСКЛЮЧЕНИЕ ЛОКАТОРА
0F001
invalid_locator_specification
НЕДЕЙСТВИТЕЛЬНАЯ СПЕЦИФИКАЦИЯ ЛОКАТОРА
Класс 0L — Неверный грантодатель
0L000
invalid_grantor
НЕДЕЙСТВИТЕЛЬНЫЙ ПРАВОДАТЕЛЬ
0LP01
invalid_grant_operation
НЕДОПУСТИМАЯ ОПЕРАЦИЯ ГРАНТА
Класс 0P — Неверная спецификация роли
0P000
invalid_role_specification
НЕДОПУСТИМАЯ СПЕЦИФИКАЦИЯ РОЛИ
Класс 21 — Нарушение кардинальности
21000
cardinality_violation
НАРУШЕНИЕ КАРДИНАЛЬНОСТИ
Класс 22 — Исключение данных
22000
data_exception
ИСКЛЮЧЕНИЕ ДАННЫХ
2202E
array_subscript_error
ОШИБКА ПОДСКРИПТА МАССИВА
22021
character_not_in_repertoire
СИМВОЛ ОТСУТСТВУЕТ В РЕПЕРТУАРЕ
22008
datetime_field_overflow
ПЕРЕПОЛНЕНИЕ ПОЛЯ DATETIME
22012
division_by_zero
ДЕЛЕНИЕ НА НОЛЬ
22005
error_in_assignment
ОШИБКА В ПРИСВОЕНИИ
2200B
escape_character_conflict
КОНФЛИКТ УПРАВЛЯЮЩИХ СИМВОЛОВ
22022
indicator_overflow
ПЕРЕПОЛНЕНИЕ ИНДИКАТОРА
22015
interval_field_overflow
ПЕРЕПОЛНЕНИЕ ПОЛЯ ИНТЕРВАЛА
2201E
invalid_argument_for_logarithm
НЕДОПУСТИМЫЙ АРГУМЕНТ ДЛЯ ЛОГАРИФМА
2201F
invalid_argument_for_power_function
НЕДОПУСТИМЫЙ АРГУМЕНТ ДЛЯ ФУНКЦИИ ПИТАНИЯ
2201G
invalid_argument_for_width_bucket_function
НЕДОПУСТИМЫЙ АРГУМЕНТ ДЛЯ ФУНКЦИИ ШИРИНЫ ВЕДРА
22018
invalid_character_value_for_cast
НЕДОПУСТИМОЕ ЗНАЧЕНИЕ СИМВОЛА ДЛЯ БРОСКА
22007
invalid_datetime_format
НЕВЕРНЫЙ ФОРМАТ ВРЕМЕНИ ДАТЫ
22019
invalid_escape_character
НЕДОПУСТИМЫЙ УПРАВЛЯЮЩИЙ СИМВОЛ
2200D
invalid_escape_octet
НЕДОПУСТИМЫЙ УПРАВЛЯЮЩИЙ ОКТЕТ
22025
invalid_escape_sequence
НЕВЕРНАЯ ПОСЛЕДОВАТЕЛЬНОСТЬ СИМВОЛОВ
22P06
nonstandard_use_of_escape_character
НЕСТАНДАРТНОЕ ИСПОЛЬЗОВАНИЕ УПРАВЛЯЮЩЕГО СИМВОЛА
22010
invalid_indicator_parameter_value
НЕДОПУСТИМОЕ ЗНАЧЕНИЕ ПАРАМЕТРА ИНДИКАТОРА
22020
invalid_limit_value
НЕДОПУСТИМОЕ ПРЕДЕЛЬНОЕ ЗНАЧЕНИЕ
22023
invalid_parameter_value
НЕДОПУСТИМОЕ ЗНАЧЕНИЕ ПАРАМЕТРА
2201B
invalid_regular_expression
НЕВЕРНОЕ РЕГУЛЯРНОЕ ВЫРАЖЕНИЕ
22009
invalid_time_zone_displacement_value
НЕДОПУСТИМОЕ ЗНАЧЕНИЕ СМЕЩЕНИЯ ЧАСОВОГО ПОЯСА
2200C
invalid_use_of_escape_character
НЕКОРРЕКТНОЕ ИСПОЛЬЗОВАНИЕ УПРАВЛЯЮЩЕГО СИМВОЛА
2200G
most_specific_type_mismatch
НАИБОЛЕЕ СПЕЦИФИЧЕСКОЕ НЕСООТВЕТСТВИЕ ТИПОВ
22004
null_value_not_allowed
НУЛЕВОЕ ЗНАЧЕНИЕ НЕДОПУСТИМО
22002
null_value_no_indicator_parameter
НУЛЕВОЕ ЗНАЧЕНИЕ НЕТ ПАРАМЕТРА ИНДИКАТОРА
22003
numeric_value_out_of_range
ЧИСЛОВОЕ ЗНАЧЕНИЕ ВНЕ ДИАПАЗОНА
22026
string_data_length_mismatch
НЕСООТВЕТСТВИЕ ДЛИНЫ СТРОКОВЫХ ДАННЫХ
22001
string_data_right_truncation
УСЕЧЕНИЕ СТРОКОВЫХ ДАННЫХ СПРАВА
22011
substring_error
ОШИБКА ПОДСТРОКИ
22027
trim_error
ОШИБКА ТРИМ
22024
unterminated_c_string
НЕЗАВЕРШЕННАЯ СТРОКА C
2200F
zero_length_character_string
СТРОКА СИМВОЛОВ НУЛЕВОЙ ДЛИНЫ
22P01
floating_point_exception
ИСКЛЮЧЕНИЕ ПЛАВАЮЩЕЙ ТОЧКИ
22P02
invalid_text_representation
НЕКОРРЕКТНОЕ ПРЕДСТАВЛЕНИЕ ТЕКСТА
22P03
invalid_binary_representation
НЕКОРРЕКТНОЕ ДВОИЧНОЕ ПРЕДСТАВЛЕНИЕ
22P04
bad_copy_file_format
НЕПРАВИЛЬНЫЙ ФОРМАТ ФАЙЛА КОПИИ
22P05
untranslatable_character
НЕПЕРЕВОДИМЫЙ СИМВОЛ
Класс 23 — Нарушение ограничений целостности
23000
integrity_constraint_violation
НАРУШЕНИЕ ОГРАНИЧЕНИЯ ЦЕЛОСТНОСТИ
23001
restrict_violation
НАРУШЕНИЕ ОГРАНИЧЕНИЙ
23502
not_null_violation
НАРУШЕНИЕ НЕ НУЛЯ
23503
foreign_key_violation
НАРУШЕНИЕ ВНЕШНЕГО КЛЮЧА
23505
unique_violation
УНИКАЛЬНОЕ НАРУШЕНИЕ
23514
check_violation
НАРУШЕНИЕ ПРОВЕРКИ
Класс 24 — Неверное состояние курсора
24000
invalid_cursor_state
НЕДОПУСТИМОЕ СОСТОЯНИЕ КУРСОРА
Класс 25 — Неверное состояние транзакции
25000
invalid_transaction_state
НЕДЕЙСТВИТЕЛЬНОЕ СОСТОЯНИЕ ТРАНЗАКЦИИ
25001
active_sql_transaction
АКТИВНАЯ SQL ТРАНЗАКЦИЯ
25002
branch_transaction_already_active
ТРАНЗАКЦИЯ ОТДЕЛЕНИЯ УЖЕ АКТИВНА
25008
held_cursor_requires_same_isolation_level
УДЕРЖИВАЕМЫЙ КУРСОР ТРЕБУЕТ ОДИНАКОВОГО УРОВНЯ ИЗОЛЯЦИИ
25003
inappropriate_access_mode_for_branch_transaction
НЕСООТВЕТСТВУЮЩИЙ РЕЖИМ ДОСТУПА ДЛЯ ТРАНЗАКЦИИ ВЕТВЛЕНИЯ
25004
inappropriate_isolation_level_for_branch_transaction
НЕСООТВЕТСТВУЮЩИЙ УРОВЕНЬ ИЗОЛЯЦИИ ДЛЯ ТРАНЗАКЦИИ ФИЛИАЛА
25005
no_active_sql_transaction_for_branch_transaction
НЕТ АКТИВНОЙ SQL-ТРАНЗАКЦИИ ДЛЯ ТРАНЗАКЦИИ ФИЛИАЛА
25006
read_only_sql_transaction
SQL-ТРАНЗАКЦИЯ ТОЛЬКО ДЛЯ ЧТЕНИЯ
25007
schema_and_data_statement_mixing_not_supported
СМЕШИВАНИЕ СХЕМЫ И ОПЕРАТОРА ДАННЫХ НЕ ПОДДЕРЖИВАЕТСЯ
25P01
no_active_sql_transaction
НЕТ АКТИВНОЙ SQL ТРАНЗАКЦИИ
25P02
in_failed_sql_transaction
В НЕУДАЧНОЙ ТРАНЗАКЦИИ SQL
Класс 26 — Неверное имя SQL-запроса
26000
invalid_sql_statement_name
НЕДОПУСТИМОЕ ИМЯ SQL-ОПЕРАТОРА
Класс 27 — Нарушение триггерного изменения данных
27000
triggered_data_change_violation
НАРУШЕНИЕ ПРАВИЛ ИЗМЕНЕНИЯ ДАННЫХ
Класс 28 — Неверная спецификация авторизации
28000
invalid_authorization_specification
НЕДОПУСТИМАЯ СПЕЦИФИКАЦИЯ АВТОРИЗАЦИИ
Класс 2B — Зависимые дескрипторы привилегий все еще существуют
2B000
dependent_privilege_descriptors_still_exist
ЗАВИСИМЫЕ ДЕСКРИПТОРЫ ПРИВИЛЕГИЙ ВСЕ ЕЩЕ СУЩЕСТВУЮТ
2BP01
dependent_objects_still_exist
ЗАВИСИМЫЕ ОБЪЕКТЫ ВСЕ ЕЩЕ СУЩЕСТВУЮТ
Класс 2D — Некорректное завершение транзакции
2D000
invalid_transaction_termination
НЕКОРРЕКТНОЕ ЗАВЕРШЕНИЕ ТРАНЗАКЦИИ
Класс 2F — Исключение SQL-маршрута
2F000
sql_routine_exception
ИСКЛЮЧЕНИЕ SQL-ПРОЦЕДУРЫ
2F005
function_executed_no_return_statement
ФУНКЦИЯ ВЫПОЛНЕНА НЕТ ЗАЯВЛЕНИЯ О ВОЗВРАТЕ
2F002
modifying_sql_data_not_permitted
МОДИФИКАЦИЯ ДАННЫХ SQL ЗАПРЕЩЕНА
2F003
prohibited_sql_statement_attempted
ПОПЫТКА ЗАПРЕЩЕННОГО SQL-ОПЕРАТОРА
2F004
reading_sql_data_not_permitted
ЧТЕНИЕ SQL-ДАННЫХ ЗАПРЕЩЕНО
Класс 34 — Неверное имя курсора
34000
invalid_cursor_name
НЕДОПУСТИМОЕ ИМЯ КУРСОРА
Класс 38 — Исключение внешней программы
38000
external_routine_exception
ИСКЛЮЧЕНИЕ ВНЕШНЕЙ ПРОЦЕДУРЫ
38001
containing_sql_not_permitted
СОДЕРЖАНИЕ SQL НЕ РАЗРЕШЕНО
38002
modifying_sql_data_not_permitted
МОДИФИКАЦИЯ ДАННЫХ SQL НЕ РАЗРЕШЕНА
38003
prohibited_sql_statement_attempted
ПРЕДПРИНЯТА ПОПЫТКА ВЫПОЛНЕНИЯ ЗАПРЕЩЕННОГО SQL-ЗАПРОСА
38004
reading_sql_data_not_permitted
ЧТЕНИЕ SQL-ДАННЫХ НЕ РАЗРЕШЕНО
Класс 39 — Исключение при вызове внешнего маршрута
39000
external_routine_invocation_exception
ИСКЛЮЧЕНИЕ ВЫЗОВА ВНЕШНЕЙ ПРОЦЕДУРЫ
39001
invalid_sqlstate_returned
ВОЗВРАЩЕНО НЕВЕРНОЕ СОСТОЯНИЕ SQLSTATE
39004
null_value_not_allowed
НУЛЕВОЕ ЗНАЧЕНИЕ НЕДОПУСТИМО
39P01
trigger_protocol_violated
НАРУШЕН ПРОТОКОЛ ТРИГГЕРА
39P02
srf_protocol_violated
НАРУШЕН ПРОТОКОЛ SRF
Класс 3B — Исключение точки сохранения
3B000
savepoint_exception
ИСКЛЮЧЕНИЕ ТОЧКИ СОХРАНЕНИЯ
3B001
invalid_savepoint_specification
НЕВЕРНАЯ СПЕЦИФИКАЦИЯ ТОЧКИ СОХРАНЕНИЯ
Класс 3D — Неверное имя каталога
3D000
invalid_catalog_name
НЕДОПУСТИМОЕ ИМЯ КАТАЛОГА
Класс 3F — Неверное имя схемы
3F000
invalid_schema_name
НЕКОРРЕКТНОЕ ИМЯ СХЕМЫ
Класс 40 — Откат транзакции
40000
transaction_rollback
ОТКАТ ТРАНЗАКЦИИ
40002
transaction_integrity_constraint_violation
НАРУШЕНИЕ ОГРАНИЧЕНИЙ ЦЕЛОСТНОСТИ ТРАНЗАКЦИИ
40001
serialization_failure
СБОЙ СЕРИАЛИЗАЦИИ
40003
statement_completion_unknown
ЗАВЕРШЕНИЕ ОПЕРАЦИИ НЕИЗВЕСТНО
40P01
deadlock_detected
ОБНАРУЖЕН ТУПИК
Класс 42 — Ошибка синтаксиса или нарушение правила доступа
42000
syntax_error_or_access_rule_violation
СИНТАКСИЧЕСКАЯ ОШИБКА ИЛИ НАРУШЕНИЕ ПРАВИЛА ДОСТУПА
42601
syntax_error
ОШИБКА СИНТАКСИСА
42501
insufficient_privilege
НЕДОСТАТОЧНАЯ ПРИВИЛЕГИЯ
42846
cannot_coerce
НЕ МОЖЕТ СОХРАНИТЬСЯ
42803
grouping_error
ОШИБКА ГРУППИРОВКИ
42830
invalid_foreign_key
НЕДОПУСТИМЫЙ ВНЕШНИЙ КЛЮЧ
42602
invalid_name
НЕПРАВИЛЬНОЕ ИМЯ
42622
name_too_long
СЛИШКОМ ДЛИННОЕ ИМЯ
42939
reserved_name
ЗАРЕГИСТРИРОВАННОЕ ИМЯ
42804
datatype_mismatch
НЕСООТВЕТСТВИЕ ТИПОВ ДАННЫХ
42P18
indeterminate_datatype
НЕОПРЕДЕЛЕННЫЙ ТИП ДАННЫХ
42809
wrong_object_type
НЕПРАВИЛЬНЫЙ ТИП ОБЪЕКТА
42703
undefined_column
НЕОПРЕДЕЛЁННЫЙ СТОЛБЕЦ
42883
undefined_function
НЕОПРЕДЕЛЁННАЯ ФУНКЦИЯ
42P01
undefined_table
НЕОПРЕДЕЛЁННАЯ ТАБЛИЦА
42P02
undefined_parameter
НЕОПРЕДЕЛЁННЫЙ ПАРАМЕТР
42704
undefined_object
НЕОПРЕДЕЛЕННЫЙ ОБЪЕКТ
42701
duplicate_column
ДУБЛИРУЮЩИЙ СТОЛБЕЦ
42P03
duplicate_cursor
ДУБЛИРУЮЩИЙ КУРСОР
42P04
duplicate_database
ДУБЛИРОВАНИЕ БАЗЫ ДАННЫХ
42723
duplicate_function
ДУБЛИРУЮЩАЯ ФУНКЦИЯ
42P05
duplicate_prepared_statement
ДУБЛИРОВАНИЕ ПОДГОТОВЛЕННОГО ОПЕРАТОРА
42P06
duplicate_schema
ДУБЛИРОВАНИЕ СХЕМЫ
42P07
duplicate_table
ДУБЛИРУЮЩАЯ ТАБЛИЦА
42712
duplicate_alias
ДУБЛИКАТ ПСЕВДОНИМА
42710
duplicate_object
ДУБЛИРУЮЩИЙ ОБЪЕКТ
42702
ambiguous_column
НЕОДНОЗНАЧНЫЙ СТОЛБЕЦ
42725
ambiguous_function
НЕОДНОЗНАЧНАЯ ФУНКЦИЯ
42P08
ambiguous_parameter
НЕОДНОЗНАЧНЫЙ ПАРАМЕТР
42P09
ambiguous_alias
НЕОДНОЗНАЧНЫЙ ПСЕВДОНИМ
42P10
invalid_column_reference
НЕДОПУСТИМАЯ ССЫЛКА НА СТОЛБЕЦ
42611
invalid_column_definition
НЕКОРРЕКТНОЕ ОПРЕДЕЛЕНИЕ СТОЛБЦА
42P11
invalid_cursor_definition
НЕКОРРЕКТНОЕ ОПРЕДЕЛЕНИЕ КУРСОРА
42P12
invalid_database_definition
НЕДОПУСТИМОЕ ОПРЕДЕЛЕНИЕ БАЗЫ ДАННЫХ
42P13
invalid_function_definition
НЕВЕРНОЕ ОПРЕДЕЛЕНИЕ ФУНКЦИИ
42P14
invalid_prepared_statement_definition
НЕДОПУСТИМОЕ ОПРЕДЕЛЕНИЕ ПОДГОТОВЛЕННОГО ОПЕРАТОРА
42P15
invalid_schema_definition
НЕВЕРНОЕ ОПРЕДЕЛЕНИЕ СХЕМЫ
42P16
invalid_table_definition
НЕВЕРНОЕ ОПРЕДЕЛЕНИЕ ТАБЛИЦЫ
42P17
invalid_object_definition
НЕВЕРНОЕ ОПРЕДЕЛЕНИЕ ОБЪЕКТА
Класс 44 — Нарушение проверки
44000
with_check_option_violation
НАРУШЕНИЕ ОПЦИИ ПРОВЕРКИ
Класс 53 — Недостаточно ресурсов
53000
insufficient_resources
НЕДОСТАТОЧНОЕ КОЛИЧЕСТВО РЕСУРСОВ
53100
disk_full
ДИСК ПЕРЕПОЛНЕН
53200
out_of_memory
МАЛО ПАМЯТИ
53300
too_many_connections
СЛИШКОМ МНОГО СОЕДИНЕНИЙ
Класс 54 — Превышен лимит программы
54000
program_limit_exceeded
ПРЕВЫШЕН ЛИМИТ ПРОГРАММЫ
54001
statement_too_complex
СЛИШКОМ СЛОЖНОЕ УТВЕРЖДЕНИЕ
54011
too_many_columns
СЛИШКОМ МНОГО СТОЛБЦОВ
54023
too_many_arguments
СЛИШКОМ МНОГО АРГУМЕНТОВ
Класс 55 — Объект не находится в состоянии предпосылки
55000
object_not_in_prerequisite_state
ОБЪЕКТ НЕ НАХОДИТСЯ В СОСТОЯНИИ ПРЕДПОСЫЛКИ
55006
object_in_use
ОБЪЕКТ В ИСПОЛЬЗОВАНИИ
55P02
cant_change_runtime_param
НЕВОЗМОЖНО ИЗМЕНИТЬ ПАРАМЕТР ВРЕМЕНИ ВЫПОЛНЕНИЯ
55P03
lock_not_available
БЛОКИРОВКА НЕДОСТУПНА
Класс 57 — Вмешательство оператора
57000
operator_intervention
ВМЕШАТЕЛЬСТВО ОПЕРАТОРА
57014
query_canceled
ЗАПРОС ОТМЕНЕН
57P01
admin_shutdown
ОТКЛЮЧЕНИЕ АДМИНИСТРАТОРА
57P02
crash_shutdown
АВАРИЙНОЕ ОТКЛЮЧЕНИЕ
57P03
cannot_connect_now
НЕВОЗМОЖНО ПОДКЛЮЧИТЬСЯ
Класс 58 — Системная ошибка (ошибки, внешние по отношению к самому PostgreSQL)
58030
io_error
ОШИБКА ВВОДА-ВЫВОДА
58P01
undefined_file
НЕОПРЕДЕЛЕННЫЙ ФАЙЛ
58P02
duplicate_file
ДУБЛИКАТ ФАЙЛА
Класс F0 — Ошибка файла конфигурации
F0000
config_file_error
ОШИБКА ФАЙЛА КОНФИГУРАЦИИ
F0001
lock_file_exists
СУЩЕСТВУЕТ ФАЙЛ БЛОКИРОВКИ
Класс P0 — Ошибка PL/pgSQL
P0000
plpgsql_error
PLPGSQL ERROR
P0001
raise_exception
ПОДНЯТЬ ИСКЛЮЧЕНИЕ
P0002
no_data_found
НЕ НАЙДЕНЫ ДАННЫЕ
P0003
too_many_rows
СЛИШКОМ МНОГО СТРОК
Класс XX — Внутренняя ошибка
XX000
internal_error
ВНУТРЕННЯЯ ОШИБКА
XX001
data_corrupted
ДАННЫЕ ИСПОРЧЕНЫ
XX002
index_corrupted
ПОВРЕЖДЁННЫЙ ИНДЕКС
Comments
Original author: Martin.A…@gmail.com (May 11, 2012 10:11:31)
We are using PostgreSQL 9.1 and are using the uuid-ossp extension. Clean tries to drop one of its functions. The extension has been installed manually as it required adding binaries to the DBMS.
Any way to work around this?
FlywayException: Error executing statement at line 64: DROP FUNCTION IF EXISTS «public».»uuid_nil»() CASCADE
Occured in com.googlecode.flyway.core.migration.sql.SqlStatement in method execute, line number 78
Caused by org.postgresql.util.PSQLException: ERROR: cannot drop function uuid_nil() because extension uuid-ossp requires it
Hint: You can drop extension uuid-ossp instead.
Occured in org.postgresql.core.v3.QueryExecutorImpl in method receiveErrorResponse, line number 2102
Original issue: http://code.google.com/p/flyway/issues/detail?id=257
Copy link
Collaborator
Author
From axel.fontaine.business@gmail.com on May 22, 2012 00:08:11
Hi Martin,
thanks for your report. Sorry for the late reply. I’ve thought about this a bit.
Do you believe the following solution would work?
- Add support to Flyway to clean extension (using DROP EXTENSION)
- Add a CREATE EXTENSION line to your initial migration
Looking forward to your feedback
Axel
P.S.: There are 2 things that still would need to be researched: how to do this with 9.0 and how to enumerate the installed extensions.
Copy link
Collaborator
Author
From Martin.A…@gmail.com on May 22, 2012 20:15:00
Looks like a feasible solution. Let me explain what actions we took when establishing our migration using the extension.
1.) The uuid-ossp extension requires a binary/os-specific addon. On the windows platform that was already installed. For linux we had to get the package first and install it
2.) Since the extension required some additional software to be installed we decided not to have the CREATE EXTENSION statement as part of the migration script
Still the cleanup tries to delete individual functions from the extension. So either during cleanup functions from extensions are left alone or only a drop extension command is executed.
Copy link
Collaborator
Author
From Martin.A…@gmail.com on May 29, 2012 20:15:21
As we are currently looking for a workaround for the failing clean process, we have a question regarding the reason why clean is actually performing a ‘object by object’ delete process?
How much would it differ from a simple DROP SCHEMA, CREATE SCHEMA sequence?
Copy link
Collaborator
Author
Copy link
Collaborator
Author
From kevin…@gmail.com on February 11, 2013 16:44:49
Doing DROP/CREATE SCHEMA may be useful for some situations, but there are many extensions which require superuser privileges to install/remove (e.g. isn), and others which do not require superuser may still be managed by a database administrator team which would prevent application developers (or the application user) from modifying them.
A local modification that I am using is to only drop objects when running as superuser or when the object is owned by the user (since the drop would fail otherwise and the object is not likely to be part of the migration).
The solution is not perfect. It may surprise users if some objects are left installed. This could, perhaps, be more transparent if a warning was issued or if it required a configuration option (dropObjects/dropOwnObjects/dropAllObjects or similar).
Copy link
Collaborator
Author
From kevin…@gmail.com on February 11, 2013 16:45:40
Sorry, in my last comment I meant DROP/CREATE EXTENSION rather than DROP/CREATE SCHEMA.
Copy link
Collaborator
Author
From laser.ta…@googlemail.com on March 08, 2013 21:59:35
Hi there, is there any solution yet?
I have the same problem with the extension postgis, which creates a table.
So when running clean the table cannot be dropped because postgis extension is still enabled…
Copy link
Collaborator
Author
From axel.fontaine.business@gmail.com on March 12, 2013 09:57:28
No solution yet.
Somehow I feel that the best solution/workaround involves putting your objects in your own schema, so extensions don’t interfere and Flyway doesn’t have to deal with them.
Comments/contributions/pull requests welcome.
+1 for cleaning the schema of all its objects
I have submitted pull request #629, which would allow us to extend any of the DbSupport classes. This makes it trivial to override doClean(), as demonstrated by the ‘CustomCleanTest’ in the core module.
Just ran into this when adding Postgres support. Surprised that there is still no solution.
@axelfontaine I think @bjhartin might have dropped out and I agree with @sfitts that this issue is very annoying while doing Postgres development.
Any chance you could take another look at it?
@axelfontaine
I used callbacks to work around this issue (thank you!)
Just a head’s up… Even once you fix this problem (dropping extensions), you’ll also need to ensure you drop views before attempting to drop «basic types». Otherwise you’ll get another error.
For those of you wanting to implement your own workaround, please see #629 (comment)
I am having this problem. I have a migration that adds an extension for pg_trgm. Then I’m dead in the water for unit testing when I call flyway.clean(). What is the current workaround for this?
Put your own objects in a different schema than public
Am 01.12.2014 21:20 schrieb «Robert DiFalco» notifications@github.com:
I am having this problem. I have a migration that adds an extension for
pg_trgm. Then I’m dead in the water for unit testing when I call
flyway.clean(). What is the current workaround for this?—
Reply to this email directly or view it on GitHub
#100 (comment).
Sorry Axel, I don’t know what that means. Do you mean take «CREATE EXTENSION pg_trgm» out of my flyway migration file and do it manually?
@rdifalco See #100 (comment) for a workaround. You register a FlywayCallback
with Flyway, then implement beforeClean()
to drop the function before Flyway tries to drop it itself.
Is there a way to implement a callback with an SQL file rather than in Java?
Just incase someone comes looking here and doesn’t fancy jumping between the issues to figure out a work around:
Create a beforeClean.sql
file in your migrations folder and add the required DROP...
command into it.
For example, if like me, when doing flyway clean -X
you get: ERROR: cannot drop function uuid_nil() because extension uuid-ossp requires it
you would add the following into the beforeClean.sql
file:
DROP EXTENSION IF EXISTS "uuid-ossp" CASCADE;
1 similar comment
+1
Just attempted to use Flyway with a PostGIS-enabled database in an integration test. Cleaning fails with:
org.flywaydb.core.internal.dbsupport.FlywaySqlException:
Unable to drop «public».»spatial_ref_sys»
SQL State : 2BP01
Error Code : 0
Message : ERROR: cannot drop table spatial_ref_sys because extension postgis requires it
Hint: You can drop extension postgis instead.
+1, same as above — clean doesn’t work with Postgres databases with PostGIS extension installed.
The reason we’re encountering this is because we put our tables inside «public». There’s a workaround to this which I’ve «uncovered» on Stackoverflow and the like. I’ll try to re-iterate the solution:
- Create new schema. E.g. «myschema»
- To enable PostGIS extension on «myschema», execute:
ALTER DATABASE <YOUR_DATABASE> SET search_path = myschema, public;
ALTER USER <YOUR_USER> SET search_path = myschema, public; - Ensure all your migrations create tables in «myschema».
- Finally, and I’m not sure why right now, Flyway does not seem to honor the schema setting in your jdbc-url. E.g. jdbc:postgresql://locahost:5432/mydb?currentSchema=myschema
I came over this script (pretty sure it was on Stackoverflow):
Put in ‘beforeEachMigrate.sql’ on your migration path:
DO $$
DECLARE
default_search_path TEXT := NULL;
BEGIN
SELECT setting_value INTO default_search_path
FROM (
SELECT
substring(setting from 1 for eq_position — 1) AS setting_name,
substring(setting from eq_position + 1) AS setting_value
FROM (
SELECT position(‘=’ in setting) AS eq_position, setting
FROM (
SELECT UNNEST(setconfig) AS setting FROM pg_db_role_setting s JOIN pg_database d ON s.setdatabase = d.oid WHERE d.datname = current_database()
) raw_db_role_settings
) presplit_db_role_settings
) split_db_role_settings
WHERE setting_name = ‘search_path’;
IF default_search_path IS NOT NULL THEN
EXECUTE ‘SET search_path = ‘||default_search_path;
END IF;
END;
$$
Took me a while to figure this out but this solution seems to have no side effects.
I had the same issue with hstore extension (required superuser). Just create it in PG_CATALOG schema while setup test database as superuser.
CREATE EXTENSION hstore SCHEMA pg_catalog
In that case no need to set up search_paht.
This is now working as expected. As long as you create your extensions with CREATE EXTENSION IF NOT EXISTS
you should now be able to clean your schema and rebuild it just fine.
@axelfontaine unfortunately this is still not working for me. My setup:
flyway version: tried both 5.0.7 (recent) and 3.2.1 (embedded in spring boot)
V1__init.sql:
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
location geometry NOT NULL
);
Then running against clean db:
$ ./flyway migrate
Flyway Community Edition 5.0.7 by Boxfuse
Database: jdbc:postgresql://localhost:5432/airlydb (PostgreSQL 9.5)
Successfully validated 1 migration (execution time 00:00.009s)
Creating Schema History table: "public"."flyway_schema_history"
Current version of schema "public": << Empty Schema >>
Migrating schema "public" to version 1 - init
Successfully applied 1 migration to schema "public" (execution time 00:00.900s)
And then:
$ ./flyway clean -X
Flyway Community Edition 5.0.7 by Boxfuse
DEBUG: Java 1.8.0_152 (Oracle Corporation)
DEBUG: Windows 10 10.0 amd64
DEBUG: Loading config file: C:devappsflyway-5.0.7confflyway.conf
DEBUG: Unable to load config file: C:Usersm.woronieckiflyway.conf
DEBUG: Unable to load config file: C:devappsflyway-5.0.7flyway.conf
DEBUG: Using configuration:
DEBUG: flyway.jarDirs -> C:devappsflyway-5.0.7jars
DEBUG: flyway.password -> *****
DEBUG: flyway.url -> jdbc:postgresql://localhost:5432/airlydb
DEBUG: flyway.user -> postgres
DEBUG: flyway.locations -> filesystem:C:devappsflyway-5.0.7sql
DEBUG: Adding location to classpath: C:devappsflyway-5.0.7driversderby-10.12.1.1.jar
DEBUG: Adding location to classpath: C:devappsflyway-5.0.7driversderbyclient-10.12.1.1.jar
DEBUG: Adding location to classpath: C:devappsflyway-5.0.7driversh2-1.4.196.jar
DEBUG: Adding location to classpath: C:devappsflyway-5.0.7drivershsqldb-2.4.0.jar
DEBUG: Adding location to classpath: C:devappsflyway-5.0.7driversjtds-1.3.1.jar
DEBUG: Adding location to classpath: C:devappsflyway-5.0.7driversmariadb-java-client-2.2.0.jar
DEBUG: Adding location to classpath: C:devappsflyway-5.0.7driversmssql-jdbc-6.2.2.jre7.jar
DEBUG: Adding location to classpath: C:devappsflyway-5.0.7driverspostgresql-42.1.4.jre6.jar
DEBUG: Adding location to classpath: C:devappsflyway-5.0.7driverssqlite-jdbc-3.20.1.jar
Database: jdbc:postgresql://localhost:5432/airlydb (PostgreSQL 9.5)
DEBUG: DDL Transactions Supported: true
DEBUG: Schema: public
DEBUG: Spring Jdbc available: false
DEBUG: Scanning for SQL callbacks ...
DEBUG: Scanning for filesystem resources at 'C:/dev/apps/flyway-5.0.7/sql' (Prefix: '', Suffixes: '.sql')
DEBUG: Scanning for resources in path: C:devappsflyway-5.0.7sql (C:/dev/apps/flyway-5.0.7/sql)
DEBUG: Filtering out resource: C:devappsflyway-5.0.7sqlput-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
DEBUG: Found filesystem resource: C:devappsflyway-5.0.7sqlV1__init.sql
DEBUG: Cleaning schema "public" ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
ERROR: Unexpected error
org.flywaydb.core.internal.exception.FlywaySqlException:
Unable to drop "public"."spatial_ref_sys"
-----------------------------------------
SQL State : 2BP01
Error Code : 0
Message : ERROR: cannot drop table spatial_ref_sys because extension postgis requires it
Wskaz▒wka: You can drop extension postgis instead.
at org.flywaydb.core.internal.database.SchemaObject.drop(SchemaObject.java:83)
at org.flywaydb.core.internal.database.postgresql.PostgreSQLSchema.doClean(PostgreSQLSchema.java:86)
at org.flywaydb.core.internal.database.Schema.clean(Schema.java:149)
at org.flywaydb.core.internal.command.DbClean$4.call(DbClean.java:179)
at org.flywaydb.core.internal.command.DbClean$4.call(DbClean.java:176)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:176)
at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:124)
at org.flywaydb.core.Flyway$3.execute(Flyway.java:1306)
at org.flywaydb.core.Flyway$3.execute(Flyway.java:1299)
at org.flywaydb.core.Flyway.execute(Flyway.java:1655)
at org.flywaydb.core.Flyway.clean(Flyway.java:1299)
at org.flywaydb.commandline.Main.executeOperation(Main.java:157)
at org.flywaydb.commandline.Main.main(Main.java:108)
Caused by: org.postgresql.util.PSQLException: ERROR: cannot drop table spatial_ref_sys because extension postgis requires it
Wskaz▒wka: You can drop extension postgis instead.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)
at org.flywaydb.core.internal.util.jdbc.JdbcTemplate.execute(JdbcTemplate.java:246)
at org.flywaydb.core.internal.database.postgresql.PostgreSQLTable.doDrop(PostgreSQLTable.java:43)
at org.flywaydb.core.internal.database.SchemaObject.drop(SchemaObject.java:81)
... 13 more
You said
This is now working as expected. As long as you create your extensions with CREATE EXTENSION IF NOT EXISTS you should now be able to clean your schema and rebuild it just fine.
Could you provide a working example please?
@maraswrona Check the milestone set for this issue. The fix is in the latest sources.
ah…. so I guess the fix will be in the next release? Thanks for the clarification.
Does the fix work with users without superuser privilege?
Sorry if I could just build from source myself and find out, but I’m not familiar with java, have no idea how to build 5.1.0 from github source.
This is not working for me either.
I am using
<dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency> <dependency> <groupId>org.flywaydb.flyway-test-extensions</groupId> <artifactId>flyway-spring-test</artifactId> <version>5.0.0</version> <scope>test</scope> </dependency>
or
[INFO] +- org.flywaydb:flyway-core:jar:5.0.7:compile
[INFO] +- org.flywaydb.flyway-test-extensions:flyway-spring-test:jar:5.0.0:test
in a Spring Boot (2.0.1.RELEASE) application. However, running my unit tests will throw:
org.flywaydb.core.internal.exception.FlywaySqlException:
Unable to clean schema "public"
-------------------------------
SQL State : 2BP01
Error Code : 0
Message : ERROR: cannot drop type cube because extension cube requires it
Hint: You can drop extension cube instead.
at org.flywaydb.core.internal.database.Schema.clean(Schema.java:151)
at org.flywaydb.core.internal.command.DbClean$4.call(DbClean.java:179)
at org.flywaydb.core.internal.command.DbClean$4.call(DbClean.java:176)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:176)
at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:124)
at org.flywaydb.core.Flyway$3.execute(Flyway.java:1306)
at org.flywaydb.core.Flyway$3.execute(Flyway.java:1299)
at org.flywaydb.core.Flyway.execute(Flyway.java:1655)
at org.flywaydb.core.Flyway.clean(Flyway.java:1299)
at org.flywaydb.test.FlywayTestExecutionListener.dbResetWithAnnotation(FlywayTestExecutionListener.java:378)
at org.flywaydb.test.FlywayTestExecutionListener.handleFlywayTestAnnotationForClass(FlywayTestExecutionListener.java:178)
at org.flywaydb.test.FlywayTestExecutionListener.beforeTestClass(FlywayTestExecutionListener.java:157)
at org.springframework.test.context.TestContextManager.beforeTestClass(TestContextManager.java:215)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:60)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.postgresql.util.PSQLException: ERROR: cannot drop type cube because extension cube requires it
Hint: You can drop extension cube instead.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.flywaydb.core.internal.util.jdbc.JdbcTemplate.execute(JdbcTemplate.java:246)
at org.flywaydb.core.internal.database.postgresql.PostgreSQLSchema.doClean(PostgreSQLSchema.java:90)
at org.flywaydb.core.internal.database.Schema.clean(Schema.java:149)
... 22 more
@axelfontaine Oh I only just saw your comment: Is it not supposed to work with 5.0.7
?
Okay, I’ll repeat what @foal said because it’s easily overlooked.
If you can, just install your extensions with pg_catalog
. In my case I put
CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA pg_catalog; CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA pg_catalog;
in my V1__init.sql
script.
Now my tests are running without problems.
ghost
mentioned this issue
May 11, 2018
@axelfontaine perhaps I’m doing something wrong here but with flyway-core-5.2.1 and «create extension if not exists» on public (not pg_catalog) and I’m still getting the «ERROR: cannot drop table spatial_ref_sys because extension postgis …» error on clean.
@svpace Please open a new issue with exact steps how to reproduce.
flyway
locked as resolved and limited conversation to collaborators
Dec 3, 2018
00000
successful_completion
01000
warning
0100C
dynamic_result_sets_returned
01008
implicit_zero_bit_padding
01003
null_value_eliminated_in_set_function
01007
privilege_not_granted
01006
privilege_not_revoked
01004
string_data_right_truncation
01P01
deprecated_feature
02000
no_data
02001
no_additional_dynamic_result_sets_returned
03000
sql_statement_not_yet_complete
08000
connection_exception
08003
connection_does_not_exist
08006
connection_failure
08001
sqlclient_unable_to_establish_sqlconnection
08004
sqlserver_rejected_establishment_of_sqlconnection
08007
transaction_resolution_unknown
08P01
protocol_violation
09000
triggered_action_exception
0A000
feature_not_supported
0B000
invalid_transaction_initiation
0F000
locator_exception
0F001
invalid_locator_specification
0L000
invalid_grantor
0LP01
invalid_grant_operation
0P000
invalid_role_specification
0Z000
diagnostics_exception
0Z002
stacked_diagnostics_accessed_without_active_handler
20000
case_not_found
21000
cardinality_violation
22000
data_exception
2202E
array_subscript_error
22021
character_not_in_repertoire
22008
datetime_field_overflow
22012
division_by_zero
22005
error_in_assignment
2200B
escape_character_conflict
22022
indicator_overflow
22015
interval_field_overflow
2201E
invalid_argument_for_logarithm
22014
invalid_argument_for_ntile_function
22016
invalid_argument_for_nth_value_function
2201F
invalid_argument_for_power_function
2201G
invalid_argument_for_width_bucket_function
22018
invalid_character_value_for_cast
22007
invalid_datetime_format
22019
invalid_escape_character
2200D
invalid_escape_octet
22025
invalid_escape_sequence
22P06
nonstandard_use_of_escape_character
22010
invalid_indicator_parameter_value
22023
invalid_parameter_value
22013
invalid_preceding_or_following_size
2201B
invalid_regular_expression
2201W
invalid_row_count_in_limit_clause
2201X
invalid_row_count_in_result_offset_clause
2202H
invalid_tablesample_argument
2202G
invalid_tablesample_repeat
22009
invalid_time_zone_displacement_value
2200C
invalid_use_of_escape_character
2200G
most_specific_type_mismatch
22004
null_value_not_allowed
22002
null_value_no_indicator_parameter
22003
numeric_value_out_of_range
2200H
sequence_generator_limit_exceeded
22026
string_data_length_mismatch
22001
string_data_right_truncation
22011
substring_error
22027
trim_error
22024
unterminated_c_string
2200F
zero_length_character_string
22P01
floating_point_exception
22P02
invalid_text_representation
22P03
invalid_binary_representation
22P04
bad_copy_file_format
22P05
untranslatable_character
2200L
not_an_xml_document
2200M
invalid_xml_document
2200N
invalid_xml_content
2200S
invalid_xml_comment
2200T
invalid_xml_processing_instruction
22030
duplicate_json_object_key_value
22031
invalid_argument_for_sql_json_datetime_function
22032
invalid_json_text
22033
invalid_sql_json_subscript
22034
more_than_one_sql_json_item
22035
no_sql_json_item
22036
non_numeric_sql_json_item
22037
non_unique_keys_in_a_json_object
22038
singleton_sql_json_item_required
22039
sql_json_array_not_found
2203A
sql_json_member_not_found
2203B
sql_json_number_not_found
2203C
sql_json_object_not_found
2203D
too_many_json_array_elements
2203E
too_many_json_object_members
2203F
sql_json_scalar_required
2203G
sql_json_item_cannot_be_cast_to_target_type
23000
integrity_constraint_violation
23001
restrict_violation
23502
not_null_violation
23503
foreign_key_violation
23505
unique_violation
23514
check_violation
23P01
exclusion_violation
24000
invalid_cursor_state
25000
invalid_transaction_state
25001
active_sql_transaction
25002
branch_transaction_already_active
25008
held_cursor_requires_same_isolation_level
25003
inappropriate_access_mode_for_branch_transaction
25004
inappropriate_isolation_level_for_branch_transaction
25005
no_active_sql_transaction_for_branch_transaction
25006
read_only_sql_transaction
25007
schema_and_data_statement_mixing_not_supported
25P01
no_active_sql_transaction
25P02
in_failed_sql_transaction
25P03
idle_in_transaction_session_timeout
26000
invalid_sql_statement_name
27000
triggered_data_change_violation
28000
invalid_authorization_specification
28P01
invalid_password
2B000
dependent_privilege_descriptors_still_exist
2BP01
dependent_objects_still_exist
2D000
invalid_transaction_termination
2F000
sql_routine_exception
2F005
function_executed_no_return_statement
2F002
modifying_sql_data_not_permitted
2F003
prohibited_sql_statement_attempted
2F004
reading_sql_data_not_permitted
34000
invalid_cursor_name
38000
external_routine_exception
38001
containing_sql_not_permitted
38002
modifying_sql_data_not_permitted
38003
prohibited_sql_statement_attempted
38004
reading_sql_data_not_permitted
39000
external_routine_invocation_exception
39001
invalid_sqlstate_returned
39004
null_value_not_allowed
39P01
trigger_protocol_violated
39P02
srf_protocol_violated
39P03
event_trigger_protocol_violated
3B000
savepoint_exception
3B001
invalid_savepoint_specification
3D000
invalid_catalog_name
3F000
invalid_schema_name
40000
transaction_rollback
40002
transaction_integrity_constraint_violation
40001
serialization_failure
40003
statement_completion_unknown
40P01
deadlock_detected
42000
syntax_error_or_access_rule_violation
42601
syntax_error
42501
insufficient_privilege
42846
cannot_coerce
42803
grouping_error
42P20
windowing_error
42P19
invalid_recursion
42830
invalid_foreign_key
42602
invalid_name
42622
name_too_long
42939
reserved_name
42804
datatype_mismatch
42P18
indeterminate_datatype
42P21
collation_mismatch
42P22
indeterminate_collation
42809
wrong_object_type
428C9
generated_always
42703
undefined_column
42883
undefined_function
42P01
undefined_table
42P02
undefined_parameter
42704
undefined_object
42701
duplicate_column
42P03
duplicate_cursor
42P04
duplicate_database
42723
duplicate_function
42P05
duplicate_prepared_statement
42P06
duplicate_schema
42P07
duplicate_table
42712
duplicate_alias
42710
duplicate_object
42702
ambiguous_column
42725
ambiguous_function
42P08
ambiguous_parameter
42P09
ambiguous_alias
42P10
invalid_column_reference
42611
invalid_column_definition
42P11
invalid_cursor_definition
42P12
invalid_database_definition
42P13
invalid_function_definition
42P14
invalid_prepared_statement_definition
42P15
invalid_schema_definition
42P16
invalid_table_definition
42P17
invalid_object_definition
44000
with_check_option_violation
53000
insufficient_resources
53100
disk_full
53200
out_of_memory
53300
too_many_connections
53400
configuration_limit_exceeded
54000
program_limit_exceeded
54001
statement_too_complex
54011
too_many_columns
54023
too_many_arguments
55000
object_not_in_prerequisite_state
55006
object_in_use
55P02
cant_change_runtime_param
55P03
lock_not_available
55P04
unsafe_new_enum_value_usage
57000
operator_intervention
57014
query_canceled
57P01
admin_shutdown
57P02
crash_shutdown
57P03
cannot_connect_now
57P04
database_dropped
57P05
idle_session_timeout
58000
system_error
58030
io_error
58P01
undefined_file
58P02
duplicate_file
72000
snapshot_too_old
F0000
config_file_error
F0001
lock_file_exists
HV000
fdw_error
HV005
fdw_column_name_not_found
HV002
fdw_dynamic_parameter_value_needed
HV010
fdw_function_sequence_error
HV021
fdw_inconsistent_descriptor_information
HV024
fdw_invalid_attribute_value
HV007
fdw_invalid_column_name
HV008
fdw_invalid_column_number
HV004
fdw_invalid_data_type
HV006
fdw_invalid_data_type_descriptors
HV091
fdw_invalid_descriptor_field_identifier
HV00B
fdw_invalid_handle
HV00C
fdw_invalid_option_index
HV00D
fdw_invalid_option_name
HV090
fdw_invalid_string_length_or_buffer_length
HV00A
fdw_invalid_string_format
HV009
fdw_invalid_use_of_null_pointer
HV014
fdw_too_many_handles
HV001
fdw_out_of_memory
HV00P
fdw_no_schemas
HV00J
fdw_option_name_not_found
HV00K
fdw_reply_handle
HV00Q
fdw_schema_not_found
HV00R
fdw_table_not_found
HV00L
fdw_unable_to_create_execution
HV00M
fdw_unable_to_create_reply
HV00N
fdw_unable_to_establish_connection
P0000
plpgsql_error
P0001
raise_exception
P0002
no_data_found
P0003
too_many_rows
P0004
assert_failure
XX000
internal_error
XX001
data_corrupted
XX002
index_corrupted
Error Code | Condition Name |
---|---|
Class 00 — Successful Completion | |
00000 |
successful_completion |
Class 01 — Warning | |
01000 |
warning |
0100C |
dynamic_result_sets_returned |
01008 |
implicit_zero_bit_padding |
01003 |
null_value_eliminated_in_set_function |
01007 |
privilege_not_granted |
01006 |
privilege_not_revoked |
01004 |
string_data_right_truncation |
01P01 |
deprecated_feature |
Class 02 — No Data (this is also a warning class per the SQL standard) | |
02000 |
no_data |
02001 |
no_additional_dynamic_result_sets_returned |
Class 03 — SQL Statement Not Yet Complete | |
03000 |
sql_statement_not_yet_complete |
Class 08 — Connection Exception | |
08000 |
connection_exception |
08003 |
connection_does_not_exist |
08006 |
connection_failure |
08001 |
sqlclient_unable_to_establish_sqlconnection |
08004 |
sqlserver_rejected_establishment_of_sqlconnection |
08007 |
transaction_resolution_unknown |
08P01 |
protocol_violation |
Class 09 — Triggered Action Exception | |
09000 |
triggered_action_exception |
Class 0A — Feature Not Supported | |
0A000 |
feature_not_supported |
Class 0B — Invalid Transaction Initiation | |
0B000 |
invalid_transaction_initiation |
Class 0F — Locator Exception | |
0F000 |
locator_exception |
0F001 |
invalid_locator_specification |
Class 0L — Invalid Grantor | |
0L000 |
invalid_grantor |
0LP01 |
invalid_grant_operation |
Class 0P — Invalid Role Specification | |
0P000 |
invalid_role_specification |
Class 0Z — Diagnostics Exception | |
0Z000 |
diagnostics_exception |
0Z002 |
stacked_diagnostics_accessed_without_active_handler |
Class 20 — Case Not Found | |
20000 |
case_not_found |
Class 21 — Cardinality Violation | |
21000 |
cardinality_violation |
Class 22 — Data Exception | |
22000 |
data_exception |
2202E |
array_subscript_error |
22021 |
character_not_in_repertoire |
22008 |
datetime_field_overflow |
22012 |
division_by_zero |
22005 |
error_in_assignment |
2200B |
escape_character_conflict |
22022 |
indicator_overflow |
22015 |
interval_field_overflow |
2201E |
invalid_argument_for_logarithm |
22014 |
invalid_argument_for_ntile_function |
22016 |
invalid_argument_for_nth_value_function |
2201F |
invalid_argument_for_power_function |
2201G |
invalid_argument_for_width_bucket_function |
22018 |
invalid_character_value_for_cast |
22007 |
invalid_datetime_format |
22019 |
invalid_escape_character |
2200D |
invalid_escape_octet |
22025 |
invalid_escape_sequence |
22P06 |
nonstandard_use_of_escape_character |
22010 |
invalid_indicator_parameter_value |
22023 |
invalid_parameter_value |
22013 |
invalid_preceding_or_following_size |
2201B |
invalid_regular_expression |
2201W |
invalid_row_count_in_limit_clause |
2201X |
invalid_row_count_in_result_offset_clause |
2202H |
invalid_tablesample_argument |
2202G |
invalid_tablesample_repeat |
22009 |
invalid_time_zone_displacement_value |
2200C |
invalid_use_of_escape_character |
2200G |
most_specific_type_mismatch |
22004 |
null_value_not_allowed |
22002 |
null_value_no_indicator_parameter |
22003 |
numeric_value_out_of_range |
2200H |
sequence_generator_limit_exceeded |
22026 |
string_data_length_mismatch |
22001 |
string_data_right_truncation |
22011 |
substring_error |
22027 |
trim_error |
22024 |
unterminated_c_string |
2200F |
zero_length_character_string |
22P01 |
floating_point_exception |
22P02 |
invalid_text_representation |
22P03 |
invalid_binary_representation |
22P04 |
bad_copy_file_format |
22P05 |
untranslatable_character |
2200L |
not_an_xml_document |
2200M |
invalid_xml_document |
2200N |
invalid_xml_content |
2200S |
invalid_xml_comment |
2200T |
invalid_xml_processing_instruction |
22030 |
duplicate_json_object_key_value |
22031 |
invalid_argument_for_sql_json_datetime_function |
22032 |
invalid_json_text |
22033 |
invalid_sql_json_subscript |
22034 |
more_than_one_sql_json_item |
22035 |
no_sql_json_item |
22036 |
non_numeric_sql_json_item |
22037 |
non_unique_keys_in_a_json_object |
22038 |
singleton_sql_json_item_required |
22039 |
sql_json_array_not_found |
2203A |
sql_json_member_not_found |
2203B |
sql_json_number_not_found |
2203C |
sql_json_object_not_found |
2203D |
too_many_json_array_elements |
2203E |
too_many_json_object_members |
2203F |
sql_json_scalar_required |
Class 23 — Integrity Constraint Violation | |
23000 |
integrity_constraint_violation |
23001 |
restrict_violation |
23502 |
not_null_violation |
23503 |
foreign_key_violation |
23505 |
unique_violation |
23514 |
check_violation |
23P01 |
exclusion_violation |
Class 24 — Invalid Cursor State | |
24000 |
invalid_cursor_state |
Class 25 — Invalid Transaction State | |
25000 |
invalid_transaction_state |
25001 |
active_sql_transaction |
25002 |
branch_transaction_already_active |
25008 |
held_cursor_requires_same_isolation_level |
25003 |
inappropriate_access_mode_for_branch_transaction |
25004 |
inappropriate_isolation_level_for_branch_transaction |
25005 |
no_active_sql_transaction_for_branch_transaction |
25006 |
read_only_sql_transaction |
25007 |
schema_and_data_statement_mixing_not_supported |
25P01 |
no_active_sql_transaction |
25P02 |
in_failed_sql_transaction |
25P03 |
idle_in_transaction_session_timeout |
Class 26 — Invalid SQL Statement Name | |
26000 |
invalid_sql_statement_name |
Class 27 — Triggered Data Change Violation | |
27000 |
triggered_data_change_violation |
Class 28 — Invalid Authorization Specification | |
28000 |
invalid_authorization_specification |
28P01 |
invalid_password |
Class 2B — Dependent Privilege Descriptors Still Exist | |
2B000 |
dependent_privilege_descriptors_still_exist |
2BP01 |
dependent_objects_still_exist |
Class 2D — Invalid Transaction Termination | |
2D000 |
invalid_transaction_termination |
Class 2F — SQL Routine Exception | |
2F000 |
sql_routine_exception |
2F005 |
function_executed_no_return_statement |
2F002 |
modifying_sql_data_not_permitted |
2F003 |
prohibited_sql_statement_attempted |
2F004 |
reading_sql_data_not_permitted |
Class 34 — Invalid Cursor Name | |
34000 |
invalid_cursor_name |
Class 38 — External Routine Exception | |
38000 |
external_routine_exception |
38001 |
containing_sql_not_permitted |
38002 |
modifying_sql_data_not_permitted |
38003 |
prohibited_sql_statement_attempted |
38004 |
reading_sql_data_not_permitted |
Class 39 — External Routine Invocation Exception | |
39000 |
external_routine_invocation_exception |
39001 |
invalid_sqlstate_returned |
39004 |
null_value_not_allowed |
39P01 |
trigger_protocol_violated |
39P02 |
srf_protocol_violated |
39P03 |
event_trigger_protocol_violated |
Class 3B — Savepoint Exception | |
3B000 |
savepoint_exception |
3B001 |
invalid_savepoint_specification |
Class 3D — Invalid Catalog Name | |
3D000 |
invalid_catalog_name |
Class 3F — Invalid Schema Name | |
3F000 |
invalid_schema_name |
Class 40 — Transaction Rollback | |
40000 |
transaction_rollback |
40002 |
transaction_integrity_constraint_violation |
40001 |
serialization_failure |
40003 |
statement_completion_unknown |
40P01 |
deadlock_detected |
Class 42 — Syntax Error or Access Rule Violation | |
42000 |
syntax_error_or_access_rule_violation |
42601 |
syntax_error |
42501 |
insufficient_privilege |
42846 |
cannot_coerce |
42803 |
grouping_error |
42P20 |
windowing_error |
42P19 |
invalid_recursion |
42830 |
invalid_foreign_key |
42602 |
invalid_name |
42622 |
name_too_long |
42939 |
reserved_name |
42804 |
datatype_mismatch |
42P18 |
indeterminate_datatype |
42P21 |
collation_mismatch |
42P22 |
indeterminate_collation |
42809 |
wrong_object_type |
428C9 |
generated_always |
42703 |
undefined_column |
42883 |
undefined_function |
42P01 |
undefined_table |
42P02 |
undefined_parameter |
42704 |
undefined_object |
42701 |
duplicate_column |
42P03 |
duplicate_cursor |
42P04 |
duplicate_database |
42723 |
duplicate_function |
42P05 |
duplicate_prepared_statement |
42P06 |
duplicate_schema |
42P07 |
duplicate_table |
42712 |
duplicate_alias |
42710 |
duplicate_object |
42702 |
ambiguous_column |
42725 |
ambiguous_function |
42P08 |
ambiguous_parameter |
42P09 |
ambiguous_alias |
42P10 |
invalid_column_reference |
42611 |
invalid_column_definition |
42P11 |
invalid_cursor_definition |
42P12 |
invalid_database_definition |
42P13 |
invalid_function_definition |
42P14 |
invalid_prepared_statement_definition |
42P15 |
invalid_schema_definition |
42P16 |
invalid_table_definition |
42P17 |
invalid_object_definition |
Class 44 — WITH CHECK OPTION Violation | |
44000 |
with_check_option_violation |
Class 53 — Insufficient Resources | |
53000 |
insufficient_resources |
53100 |
disk_full |
53200 |
out_of_memory |
53300 |
too_many_connections |
53400 |
configuration_limit_exceeded |
Class 54 — Program Limit Exceeded | |
54000 |
program_limit_exceeded |
54001 |
statement_too_complex |
54011 |
too_many_columns |
54023 |
too_many_arguments |
Class 55 — Object Not In Prerequisite State | |
55000 |
object_not_in_prerequisite_state |
55006 |
object_in_use |
55P02 |
cant_change_runtime_param |
55P03 |
lock_not_available |
55P04 |
unsafe_new_enum_value_usage |
Class 57 — Operator Intervention | |
57000 |
operator_intervention |
57014 |
query_canceled |
57P01 |
admin_shutdown |
57P02 |
crash_shutdown |
57P03 |
cannot_connect_now |
57P04 |
database_dropped |
57P05 |
idle_session_timeout |
Class 58 — System Error (errors external to PostgreSQL itself) | |
58000 |
system_error |
58030 |
io_error |
58P01 |
undefined_file |
58P02 |
duplicate_file |
Class 72 — Snapshot Failure | |
72000 |
snapshot_too_old |
Class F0 — Configuration File Error | |
F0000 |
config_file_error |
F0001 |
lock_file_exists |
Class HV — Foreign Data Wrapper Error (SQL/MED) | |
HV000 |
fdw_error |
HV005 |
fdw_column_name_not_found |
HV002 |
fdw_dynamic_parameter_value_needed |
HV010 |
fdw_function_sequence_error |
HV021 |
fdw_inconsistent_descriptor_information |
HV024 |
fdw_invalid_attribute_value |
HV007 |
fdw_invalid_column_name |
HV008 |
fdw_invalid_column_number |
HV004 |
fdw_invalid_data_type |
HV006 |
fdw_invalid_data_type_descriptors |
HV091 |
fdw_invalid_descriptor_field_identifier |
HV00B |
fdw_invalid_handle |
HV00C |
fdw_invalid_option_index |
HV00D |
fdw_invalid_option_name |
HV090 |
fdw_invalid_string_length_or_buffer_length |
HV00A |
fdw_invalid_string_format |
HV009 |
fdw_invalid_use_of_null_pointer |
HV014 |
fdw_too_many_handles |
HV001 |
fdw_out_of_memory |
HV00P |
fdw_no_schemas |
HV00J |
fdw_option_name_not_found |
HV00K |
fdw_reply_handle |
HV00Q |
fdw_schema_not_found |
HV00R |
fdw_table_not_found |
HV00L |
fdw_unable_to_create_execution |
HV00M |
fdw_unable_to_create_reply |
HV00N |
fdw_unable_to_establish_connection |
Class P0 — PL/pgSQL Error | |
P0000 |
plpgsql_error |
P0001 |
raise_exception |
P0002 |
no_data_found |
P0003 |
too_many_rows |
P0004 |
assert_failure |
Class XX — Internal Error | |
XX000 |
internal_error |
XX001 |
data_corrupted |
XX002 |
index_corrupted |
При использовании базы данных используется вид, материал используется.
В зависимости от таблицы, просмотра и материализованных представлений.
Например
create table t(id int);
create view v1 as select * from t;
create view v2 as select * from v1;
create view v3 as select v1.id from v1,v2 where v1.id=v2.id;
create view v4 as SELECT v1.id +
FROM v1, +
v2, +
pg_class, +
pg_authid;
。。。。
Относительные отношения, вызванные ошибкой
Если вы хотите изменить T, или удалить таблицы T. Что это будет?
postgres=# drop table t;
ERROR: 2BP01: cannot drop table t because other objects depend on it
DETAIL: view v1 depends on table t
view v2 depends on view v1
view v3 depends on view v1
view v4 depends on view v1
materialized view v5 depends on view v4
materialized view v6 depends on view v4
view vv1v depends on table t
view vv1v1 depends on view vv1v
HINT: Use DROP ... CASCADE to drop the dependent objects too.
LOCATION: reportDependentObjects, dependency.c:986
Используйте Cascade Drop Table T Cascade для автоматического удаления зависимостей.
Если это поле изменений, извините, вам нужно сначала удалить объект зависимости и восстановить зависимый объект.
postgres=# alter table t alter column id type int8;
ERROR: 0A000: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view vv1v depends on column "id"
LOCATION: ATExecAlterColumnType, tablecmds.c:8225
При падении вы будете распечатывать представление T стол с помощью функции ReportDePandentObjects.
Кодовое шоу, как показано ниже
src/backend/catalog/objectaddress.c
/*
* reportDependentObjects - report about dependencies, and fail if RESTRICT
*
* Tell the user about dependent objects that we are going to delete
* (or would need to delete, but are prevented by RESTRICT mode);
* then error out if there are any and it's not CASCADE mode.
*
* targetObjects: list of objects that are scheduled to be deleted
* behavior: RESTRICT or CASCADE
* msglevel: elog level for non-error report messages
* origObject: base object of deletion, or NULL if not available
* (the latter case occurs in DROP OWNED)
*/
static void
reportDependentObjects(const ObjectAddresses *targetObjects,
DropBehavior behavior,
int msglevel,
const ObjectAddress *origObject)
{
...
Где информация?
Но как мы узнаем зависимости?
Вид и материализованные виды на самом деле находятся в pg_rewrite, и они могут быть определены путем запроса ev_action.
postgres=# select ev_action from pg_rewrite where ev_class='v1'::regclass;
({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowS
ecurity false :cteList <> :rtable ({RTE :alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("id")} :rtekind 0 :relid 13090504 :relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPer
ms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :securityQuals <>} {RTE :alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("id")} :rtekind 0 :relid 13090504 :relkind v :tablesample
<> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :securityQuals <>} {RTE :alias <> :eref {ALIAS :aliasname t :colnames ("id")} :rtekind 0 :relid 13090484 :r
elkind r :tablesample <> :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9) :insertedCols (b) :updatedCols (b) :securityQuals <>}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <
>} :targetList ({TARGETENTRY :expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 25} :resno 1 :resname id :ressortgroupref 0 :resorigtbl 13090484 :resorigcol 1 :resjunk
false}) :onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :rowMarks <> :setOperations <> :constraintDeps <>})
(1 row)
Как верно идентифицировать зависимость
Разрешая это правило, может быть получено зависимый объект OID.
Создать функцию анализа, чтобы получить зависимый OID
create or replace function get_dep_oids(oid) returns oid[] as
$$
declare
res oid[];
begin
select array_agg(unnest::oid) into res from
(
select unnest(regexp_matches(ev_action::text,':relid (d+)', 'g')) from pg_rewrite where ev_class = $1
union
select unnest(regexp_matches(ev_action::text,':resorigtbl (d+)','g')) from pg_rewrite where ev_class = $1
EXCEPT
select oid::text from pg_class where oid=$1
) t;
return res;
end;
$$
language plpgsql strict;
пример
Запрос V1 Зависимость объекта OID
postgres=# select * from get_dep_oids('v1'::regclass);
get_dep_oids
--------------
{13090484}
(1 row)
Создать функцию, которая рекурсивно опирается на объект.
create or replace function recursive_get_deps(IN tbl oid, OUT oid oid, OUT relkind "char", OUT nspname name, OUT relname name, OUT deps oid[], OUT ori_oid oid, OUT ori_relkind "char", OUT ori_nspname name, OUT ori_relname name ) returns setof record as
$$
declare
begin
return query
with recursive a as (
select * from (
select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps,(select t1.oid from pg_class t1,pg_namespace t2 where t1.relnamespace=t2.oid and t1.oid=tbl) as ori_oid from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and t1.relkind in ('m','v')
) t where t.ori_oid = any(t.deps)
union
select * from (
select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps, a.oid as ori_oid from pg_class t1,pg_namespace t2,a where t1.relnamespace=t2.oid and t1.relkind in ('m','v')
) t where t.ori_oid = any(t.deps)
)
select a.oid,a.relkind,a.nspname,a.relname,a.deps,a.ori_oid,b.relkind ori_relkind, c.nspname ori_nspname,b.relname ori_relname from a,pg_class b,pg_namespace c where a.ori_oid=b.oid and b.relnamespace=c.oid order by a.nspname,a.relkind,a.relname;
end;
$$
language plpgsql strict;
пример :
Запросить все объекты прямо или косвенно зависимы от T стол
postgres=# select * from recursive_get_deps('t'::regclass);
oid | relkind | nspname | relname | deps | ori_oid | ori_relkind | ori_nspname | ori_relname
----------+---------+---------+---------+-----------------------------------------+----------+-------------+-------------+-------------
13090804 | m | public | v5 | {13090794} | 13090794 | v | public | v4
13090808 | m | public | v6 | {13090804,13090794,0} | 13090804 | m | public | v5
13090808 | m | public | v6 | {13090804,13090794,0} | 13090794 | v | public | v4
13090504 | v | public | v1 | {13090484} | 13090484 | r | public | t
13090508 | v | public | v2 | {13090504} | 13090504 | v | public | v1
13090790 | v | public | v3 | {13090508,13090504} | 13090504 | v | public | v1
13090790 | v | public | v3 | {13090508,13090504} | 13090508 | v | public | v2
13090794 | v | public | v4 | {13090508,1259,1260,13090504} | 13090504 | v | public | v1
13090794 | v | public | v4 | {13090508,1259,1260,13090504} | 13090508 | v | public | v2
13090815 | v | public | vv1v | {13090484} | 13090484 | r | public | t
13090819 | v | public | vv1v1 | {13090508,13090790,13090504,13090815,0} | 13090790 | v | public | v3
13090819 | v | public | vv1v1 | {13090508,13090790,13090504,13090815,0} | 13090508 | v | public | v2
13090819 | v | public | vv1v1 | {13090508,13090790,13090504,13090815,0} | 13090815 | v | public | vv1v
13090819 | v | public | vv1v1 | {13090508,13090790,13090504,13090815,0} | 13090504 | v | public | v1
(14 rows)
Объекты прямых и косвенно зависимых T стол T имеют V5, V6, V1, V2, V3, V4, VV1V, VV1V1 и предыдущее содержание ошибок в таблице Drop T.
Это очень красиво?
Получить просмотр определений
После получения зависимостей мы также можем получить определение просмотра через pg_get_viewdefef.
postgres=# select * from pg_get_viewdef('v4',false);
pg_get_viewdef
----------------
SELECT v1.id +
FROM v1, +
v2, +
pg_class, +
pg_authid;
(1 row)
Рекубровочная грамматика, пожалуйста, обратитесь к моей предыдущей статье.
https://yq.aliyun.com/articles/54657
резюме
В основном использованные навыки
.1. Выражение правила совпадает с regexp_matches
https://www.postgresql.org/docs/9.6/static/functions-matching.html
.2. Рекурсивный запрос с рекурсивным запросом
https://yq.aliyun.com/articles/54657