By digoal
Assume that a table has constraints and understand when these constraints are detected. Is it at the end of the statement or during the insert tuple? Can you defer the constraint detection? Can you defer it until the end of the statement or transaction?
It will be very inconvenient to import data if you cannot defer the detection. You must first transfer the dependent data and then transfer the relevant data. If both the data types are interdependent, it will be even more difficult to import the data.
To solve the constraint- and dependency-related problem, PostgreSQL usually adopts the following methods:
1) When data is imported, constraints are usually created after all the data is written. Therefore, you can avoid dumping failures after creating constraints.
2) PostgreSQL allows the deferred detection of constraints.
- Deferred constraint judgment is allowed. You can specify this feature when creating tables or constraints and modify them later.
- Set rules for deferred judgment to determine the constraint at the end of a statement or a transaction. You can set rules by modifying the constraint definition or set directly in the transaction.
Syntax reference: https://www.postgresql.org/docs/12/sql-createtable.html
Whether deferred constraint judgment is allowed:
This controls whether you can defer the constraint. A constraint that is not deferrable will be checked immediately after every command. Checking of deferrable constraints can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. Note that you cannot use deferrable constraints as conflict arbitrators in an INSERT statement that includes an ON CONFLICT DO UPDATE clause.
Referred judgment rules: End of statement or transaction
If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement, which is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. You can alter the constraint check time with the SET CONSTRAINTS command.
Set rules for deferred constraint judgment in transactions: https://www.postgresql.org/docs/12/sql-set-constraints.html
SET CONSTRAINTS { ALL | name [, ...]} { DEFERRED | IMMEDIATE }
1) Deferred constraint judgment is not allowed.
postgres=# create table pr(id int primary key, c1 int);
CREATE TABLE
postgres=# create table fp(id int references pr(id), c1 int);
CREATE TABLE
postgres=# insert into fp values(1,1);
ERROR: insert or update on table "fp" violates foreign key constraint "fp_id_fkey"
DETAIL: Key (id)=(1) is not present in table "pr".
2) Allow the deferred constraint judgment and set rules for deferral to end the transaction.
create table fp(id int references pr(id) INITIALLY DEFERRED, c1 int);
postgres=# begin;
BEGIN
postgres=# insert into fp values(1,1);
INSERT 0 1
3) Deferred judgment is allowed. Set the transaction after starting the transaction to finish the deferred judgement.
postgres=# alter table fp alter CONSTRAINT fp_id_fkey deferrable;
ALTER TABLE
postgres=# begin;
BEGIN
postgres=# insert into fp values(1,1);
ERROR: insert or update on table "fp" violates foreign key constraint "fp_id_fkey"
DETAIL: Key (id)=(1) is not present in table "pr".
postgres=# rollback;
ROLLBACK
postgres=# begin;
BEGIN
设置事务结束延判, 所有约束生效
postgres=# set constraints all deferred;
SET CONSTRAINTS
postgres=# insert into fp values(1,1);
INSERT 0 1
postgres=# rollback;
ROLLBACK
How to Mask Sensitive Information on PostgreSQL Using Anon Plug-in
PostgreSQL Time Series Database: How TimescaleDB Supports Compression Scheduling
digoal - July 4, 2019
ApsaraDB - February 22, 2022
digoal - March 25, 2020
digoal - April 29, 2021
digoal - July 24, 2019
ApsaraDB - January 3, 2024
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreAn online MPP warehousing service based on the Greenplum Database open source program
Learn MoreAn on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreLeverage cloud-native database solutions dedicated for FinTech.
Learn MoreMore Posts by digoal