PostgreSQL Constraints – How to Add a Check Constraint Without Locking the Table

Apparently, you want to see constraint exclusion at doing its work. As in similar setups the parent table has no constraint (and, just to mention it, usually no rows), it will be always visited by queries on the parent table. Using your example schema, see the output:

test=# EXPLAIN SELECT count(1) FROM p;
                            QUERY PLAN                             
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=576.00..576.01 rows=1 width=0)
   ->  Append  (cost=0.00..501.00 rows=30001 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
         ->  Seq Scan on a  (cost=0.00..164.00 rows=10000 width=0)
         ->  Seq Scan on b  (cost=0.00..164.00 rows=10000 width=0)
         ->  Seq Scan on c  (cost=0.00..173.00 rows=10000 width=0)

test=# EXPLAIN SELECT count(1) FROM p WHERE type_id = 1;
                            QUERY PLAN                             
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=412.00..412.01 rows=1 width=0)
   ->  Append  (cost=0.00..387.00 rows=10002 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on a  (cost=0.00..189.00 rows=10000 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on c  (cost=0.00..198.00 rows=1 width=0)
               Filter: (type_id = 1)

In the second case, table b is not visited, because the planner knows for sure that there cannot be any rows matching the condition. At the same time, p is present in both cases. The same applies to c, too: it has no easy way (the check) to exclude the table from the plan. In this case, it has to visit all tables that have the possibility to contain the actual rows.

Let's add some check there, too:

ALTER TABLE c ADD CHECK (type_id = 3);

test=# EXPLAIN SELECT count(1) FROM p WHERE type_id = 1;
                            QUERY PLAN                             
───────────────────────────────────────────────────────────────────
 Aggregate  (cost=214.00..214.01 rows=1 width=0)
   ->  Append  (cost=0.00..189.00 rows=10001 width=0)
         ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=0)
               Filter: (type_id = 1)
         ->  Seq Scan on a  (cost=0.00..189.00 rows=10000 width=0)
               Filter: (type_id = 1)

Much better now.

Now let's have a look on the locks that are taken.

BEGIN; -- to be able to see the locks

SELECT count(1) FROM p WHERE type_id = 1;

SELECT relname, locktype, mode
  FROM pg_locks
  JOIN pg_class c ON relation = c.oid 
  JOIN pg_namespace n ON c.relnamespace = n.oid
 WHERE nspname = 'dba' 
   AND relkind = 'r';

 relname │ locktype │      mode       
─────────┼──────────┼─────────────────
 c       │ relation │ AccessShareLock
 b       │ relation │ AccessShareLock
 a       │ relation │ AccessShareLock
 p       │ relation │ AccessShareLock

This is not so nice... Apparently (and somewhat surprisingly), going to the parent table locks all children, too.

But:

ROLLBACK;
BEGIN; -- to be able to see the locks

SELECT count(1) FROM a WHERE type_id = 1; -- touching only "a"

-- the locks taken:
 a       │ relation │ AccessShareLock

This means that if you can constraint your query to touch only a subset of the tables, it will save you the locks on the other children. Playing around a bit it shows that this applies to other statements (for example, an UPDATE), too, just the lock modes are different.

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select * 
from (
   values 
      (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
              from a 
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.