14 有目的地选择约束

select account_id as acct from projects_flat
where account_plan <> 'team'
group by account_id having count(*) >= 2
order by account_id limit 1 \gset
update projects_flat set account_plan = 'team'
where id = (select min(id) from projects_flat where account_id = :acct);
select lab.prove('updating one project row leaves its account on two plans at once',
  (select count(distinct account_plan) from projects_flat where account_id = :acct) = 2);

begin;
select lab.prove('without a constraint, an event can be moved to another account',
  lab.try($$ update events set account_id = account_id + 1 where id = 1 $$) = 'ok');
rollback;
alter table projects add constraint projects_id_account_uq unique (id, account_id);
alter table events add constraint events_project_account_fk
  foreign key (project_id, account_id) references projects (id, account_id) not valid;
select lab.prove('with the composite foreign key, the same update is rejected',
  lab.try($$ update events set account_id = account_id + 1 where id = 1 $$) like '23503:%');

begin;
select seq as s0, tup as t0 from events_scans() \gset
select fk_ms($$ delete from projects where name like 'scratch %' $$) as fifty_ms \gset
select lab.prove('deleting 50 projects scans events 50 times: 100,000,000 rows read',
  (select seq - :s0 = 50 and tup - :t0 = 100000000 from events_scans()));
rollback;
create index events_project_id_idx on events (project_id);
begin;
select seq as s0, tup as t0, idx as i0 from events_scans() \gset
select fk_ms($$ delete from projects where name like 'scratch %' $$) as fifty_idx_ms \gset
select lab.prove('with the index, the 50 checks scan events by index: no sequential scans',
  (select seq - :s0 = 0 and idx - :i0 >= 50 from events_scans()));
rollback;
select lab.prove('and the trigger time for 50 deletes drops by more than 100 times',
  :fifty_ms > 100 * greatest(:fifty_idx_ms, 0.001));

select lab.prove('the query lists five unindexed foreign keys on projects and events',
  (select count(*) from unindexed_fks) = 5
  and (select count(*) from unindexed_fks where table_name::text in ('projects', 'events')) = 5);
select lab.prove('users.account_id is not listed: unique (account_id, email) leads with it',
  not exists (select 1 from unindexed_fks where table_name = 'users'::regclass));

\set twocol 'select * from events e join projects p on p.id = e.project_id and p.account_id = e.account_id'
select lab.est_rows(:'twocol') as est_with_fk \gset
alter table events drop constraint events_project_account_fk;
select lab.est_rows(:'twocol') as est_without_fk \gset
select lab.prove('with the composite foreign key, the planner expects about 2,000,000 joined rows',
  :est_with_fk between 1900000 and 2100000);
select lab.prove('without it, the planner multiplies two selectivities and expects about 2,000',
  :est_without_fk between 1000 and 4000);
select lab.prove('an unused left join is removed by the unique index alone; no foreign key needed',
  lab.est_nodes($$ select e.id from ev_nofk e left join projects p on p.id = e.project_id $$)
    = array['Seq Scan']);

select lab.prove('ten open inserts for one project leave ten FOR KEY SHARE members on its row',
  (select count(*) = 10 and bool_and(mode = 'keysh')
   from pg_get_multixact_members((select xmax from projects where id = 7))));
select lab.prove('getting there took nine multixacts, one per new locker, storing 54 members in all',
  (select count(distinct mx::text) from hot_seen where n >= 2) = 9
  and (select sum((select count(*) from pg_get_multixact_members(mx)))
       from hot_seen where n >= 2) = 54);
select lab.prove('a delete of the project waits for every open child insert',
  lab.try($$ set local lock_timeout = '200ms'; delete from projects where id = 7 $$) like '55P03:%');

begin;
alter table ev_nofk add constraint ev_nofk_project_fk
  foreign key (project_id) references projects (id) not valid;
select lab.prove('adding a foreign key, even not valid, takes ShareRowExclusive on the child and the parent',
  (select array_agg(c.relname::text order by c.relname) from pg_locks l
   join pg_class c on c.oid = l.relation
   where l.pid = pg_backend_pid() and l.mode = 'ShareRowExclusiveLock')
  = array['ev_nofk', 'projects']);
select lab.prove('so a new project waits until the migration commits',
  lab.try($$ select dblink_exec('w1', 'set lock_timeout = ''200ms''; '
             'insert into projects (account_id, owner_id, name) values (1, 1, ''new'')') $$)
    like '%lock timeout%');
rollback;
select lab.prove('a foreign key cannot reference a table in another database',
  lab.try($$ create table t_remote (project_id bigint references remote_projects (id)) $$)
    = '42809: referenced relation "remote_projects" is not a table');

select lab.prove('a not enforced foreign key (PostgreSQL 18) creates no triggers',
  (select count(*) from pg_trigger where tgrelid = 'ev_doc'::regclass) = 0
  and (select not conenforced from pg_constraint where conrelid = 'ev_doc'::regclass and contype = 'f'));
select lab.prove('and accepts an orphan',
  lab.try($$ insert into ev_doc values (1, 999999) $$) = 'ok');
select lab.prove('while a not valid one still checks every new row',
  lab.try($$ insert into ev_nv values (1, 999999) $$) like '23503:%');
select lab.prove('a not enforced foreign key gives the planner nothing: back to about 2,000',
  :est_not_enforced = :est_without_fk);

select lab.prove('the orphan check finds exactly the two planted orphans',
  (select count(*) from ev_nofk e
   left join accounts a on a.id = e.account_id
   left join projects p on p.id = e.project_id
   left join users    u on u.id = e.user_id
   where e.id > 0
     and (a.id is null or p.id is null or (e.user_id is not null and u.id is null))) = 2);
select lab.prove('checking all 2,000,000 events reads the table once: about 36,000 pages',
  :full_pages between 35000 and 38000);
select lab.prove('checking only rows past the last high-water mark reads under half of that',
  :recent_pages < :full_pages / 2);

select lab.prove('an overlapping range for the same project is rejected',
  lab.try($$ insert into deploy_locks values (42, tstzrange('2026-10-01 10:30+00', '2026-10-01 10:45+00')) $$)
    like '23P01: conflicting key value violates exclusion constraint "deploy_locks_project_id_held_during_excl"');
select lab.prove('without overlaps rejects a second plan in effect at the same time',
  lab.try($$ insert into project_plans values (42, 'team', tstzrange('2026-05-01+00', null)) $$)
    like '23P01: conflicting key value violates exclusion constraint "project_plans_pkey"');

select lab.prove('deferrable initially immediate checks at end of statement, so the swap works',
  lab.try($$ update steps set position = case position when 1 then 2 else 1 end $$) = 'ok');
select lab.prove('a deferrable unique constraint cannot be referenced by a foreign key',
  lab.try($$ create table steps_ref (pipeline_id bigint, position int,
             foreign key (pipeline_id, position) references steps (pipeline_id, position)) $$)
    = '55000: cannot use a deferrable unique constraint for referenced table "steps"');

select lab.prove('a range from a moment to itself is empty',
  tstzrange(now(), now()) = 'empty'::tstzrange);
-- after two earlier single-update transactions left two history rows:
begin;
set local app.user_id = '7';
update projects set name = 'Checkout v3' where id = 43;
update projects set name = 'Checkout v4' where id = 43;
commit;
select lab.prove('two updates in one transaction add one history row, stamped with the user',
  (select count(*) from projects_history where project_id = 43) = 3
  and (select changed_by from projects_history where project_id = 43
       order by lower(valid_during) desc limit 1) = 7);