Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


create type order_status_t as enum 
       ( 'pending' 
       , 'in progress' 
       , 'complete'
       , 'no products'
       );
--- define tables orders, order_products, cdc_order_status	
create table orders 
       ( order_id    integer generated always as identity primary key 
	   , cust_id     integer not null  -- references customers(cust_id) 
	   , status      order_status_t    not null default 'no products'
       );
      
create table order_products  
       ( ord_prod_id integer   generated always as identity primary key
	   , order_id    integer   not null  references orders(order_id) 
	   , prod_id     integer   not null
	   , status      order_status_t not null default 'pending'
       );      
      
create table cdc_order_status 
       ( cdc_order_id     integer generated always as identity primary key
       , operation        text 
       , cdc_tstz         timestamptz   
       , order_id         integer        
       , old_order_status order_status_t
       , new_order_status order_status_t
       ) ; 
-- function to determine order status from order products status
create or replace function order_status_from_order_product_status(order_id_in orders.order_id%type)
  returns order_status_t 
  language sql
as $$
   select status 
     from (
            select o.order_id, coalesce(min(op.status), 'no products') status  
              from orders o 
              left join order_products op 
                     on (op.order_id = o.order_id) 
             where o.order_id = order_id_in
             group by o.order_id
          ) sq
$$; 
-- trigger functions  
create or replace function cdc_order_status_adiur()
  returns trigger 
  language plpgsql 
as $$ 
begin 
	if old.status is distinct from new.status then 
	    insert into cdc_order_status 
	              ( operation
                  , cdc_tstz
                  , order_id 
                  , old_order_status  
                  , new_order_status 
                  ) 
           values ( tg_op
                  , clock_timestamp()
                  , coalesce(old.order_id, new.order_id) 
                  , old.status 
                  , new.status
                  ) ;
    end if; 
    return null; 
end;
$$; 

create or replace function gen_order_status_from_order_product_status_adiur() 
   returns trigger 
   language plpgsql
as $$
  
begin 
	case tg_op 
	     when 'INSERT' then 
	          update orders 
	             set status = order_status_from_order_product_status(new.order_id) 
	          where order_id = new.order_id; 
	     when 'DELETE' then 
	          update orders  
	          	 set status = order_status_from_order_product_status(old.order_id) 
	          where order_id = old.order_id; 
         when 'UPDATE' then 
	          update orders  
	          	 set status = order_status_from_order_product_status(old.order_id) 
	          where order_id = old.order_id; 
              if old.order_id is distinct from new.order_id then 
 	             update orders 
	                set status = order_status_from_order_product_status(new.order_id) 
	              where order_id = new.order_id; 
              end if; 
        else 
             raise exception 'Cannot handle order_products status change for % operation',tg_op;
    end case; 

    return null; 
end;   
$$; 
-- triggers  
create trigger cdc_order_status_adiur
  after delete or insert or update 
     on orders 
    for each row execute function  cdc_order_status_adiur();  
       
create trigger order_products_status_adiur
 after delete or insert or update 
    on order_products 
   for each row execute function gen_order_status_from_order_product_status_adiur();
 -- test
  
insert into orders( cust_id )
     select 1+(random()*50)::integer;
    
select * from orders;
select * from cdc_order_status;

insert into orders( cust_id )
     select 1+(random()*50)::integer
       from generate_series(2,10);
 
select * from orders; 
select * from cdc_order_status; 
1 rows affected



order_id
cust_id
status


1
25
no products


 select order_status_from_order_product_status(1);  -- should return no products


order_status_from_order_product_status


no products


insert into order_products (order_id, prod_id, status) 
  values (1,20,'complete') 
       , (1,50,'in progress')
       , (1,75,'pending')     
       , (2,20,'in progress') 
       , (2,57,'in progress')
       , (2,48,'pending') 
       , (3,25,'complete') 
       , (3,18,'in progress')
       , (3,6, 'complete')   
       , (4,22,'complete') 
       , (4,18,'complete')
       , (4,64,'complete')       
       , (5,20,'complete') 
       , (6,82,'in progress')
       , (7,13,'complete') 
       , (8,103,'complete') 
       , (8,63,'pending') 
       , (8,12,'complete') 
       , (9,12,'pending');       
       
      
select * from orders;  
select * from order_products; 
select * from cdc_order_status; 
19 rows affected



order_id
cust_id
status


10
41
no products

1
25
pending

2
30
pending

3
25
in progress

4
47
complete

5
40
complete

6
1
in progress

7
32
complete

8
10
pending

9
49
pending




ord_prod_id
order_id
prod_id
status


1
1
20
complete

2
1
50
in progress

3
1
75
pending

4
2
20
in progress

5
2
57
in progress

6
2
48
pending

7
3
25
complete

8
3
18
in progress

9
3
6
complete

10
4
22
complete

11
4
18
complete

12
4
64
complete

13
5
20
complete

14
6
82
in progress

15
7
13
complete

16
8
103
complete

17
8
63
pending

18
8
12
complete

19
9
12
pending




cdc_order_id
operation
cdc_tstz
order_id
old_order_status
new_order_status


1
INSERT
2022-01-31 01:04:10.922488+00
1
null
no products

2
INSERT
2022-01-31 01:04:10.923407+00
2
null
no products

3
INSERT
2022-01-31 01:04:10.923459+00
3
null
no products

4
INSERT
2022-01-31 01:04:10.923509+00
4
null
no products

5
INSERT
2022-01-31 01:04:10.923546+00
5
null
no products

6
INSERT
2022-01-31 01:04:10.923604+00
6
null
no products

7
INSERT
2022-01-31 01:04:10.923629+00
7
null
no products

8
INSERT
2022-01-31 01:04:10.923647+00
8
null
no products

9
INSERT
2022-01-31 01:04:10.923663+00
9
null
no products

10
INSERT
2022-01-31 01:04:10.92368+00
10
null
no products

11
UPDATE
2022-01-31 01:04:10.928958+00
1
no products
pending

12
UPDATE
2022-01-31 01:04:10.930203+00
2
no products
pending

13
UPDATE
2022-01-31 01:04:10.931317+00
3
no products
in progress

14
UPDATE
2022-01-31 01:04:10.932198+00
4
no products
complete

15
UPDATE
2022-01-31 01:04:10.933079+00
5
no products
complete

16
UPDATE
2022-01-31 01:04:10.933382+00
6
no products
in progress

17
UPDATE
2022-01-31 01:04:10.933703+00
7
no products
complete

18
UPDATE
2022-01-31 01:04:10.934008+00
8
no products
pending

19
UPDATE
2022-01-31 01:04:10.934912+00
9
no products
pending


 -- order 3:  in progress -> complete
update order_products 
   set status = 'complete' 
 where (order_id, prod_id) = (3, 18); 
select * from orders where order_id = 3; 

-- insert a new line for order 4
-- order 4: status complete -> pending
insert into order_products(order_id, prod_id) 
  values (4,109);
select * from orders where order_id = 4; 
 
-- delete prod_id 63 from order_id 8
-- order 8: status pending -> complete
delete 
  from order_products
where (order_id, prod_id) = (8, 63);
select * from orders where order_id = 8;


-- delete order_id 9
-- order 9: status pending -> no products
delete 
  from order_products
where order_id=9;  
select * from orders where order_id = 9;
1 rows affected



order_id
cust_id
status


3
25
complete


1 rows affected



order_id
cust_id
status


4
47
pending


1 rows affected



order_id
cust_id
status


8
10
complete


1 rows affected



order_id
cust_id
status


9
49
no products


-- Update prod_id 109 from order_id 4 to order_id 5
-- order 4: status pending -> complete
-- order 5: status complete -> pending
update order_products
   set order_id = 5 
where (order_id, prod_id) = (4,109);
select * from orders where order_id in (4,5);
1 rows affected



order_id
cust_id
status


4
47
complete

5
40
pending


-- show CDC for order header status
select * 
  from cdc_order_status
 order by order_id, cdc_tstz; 


cdc_order_id
operation
cdc_tstz
order_id
old_order_status
new_order_status


1
INSERT
2022-01-31 01:04:10.922488+00
1
null
no products

11
UPDATE
2022-01-31 01:04:10.928958+00
1
no products
pending

2
INSERT
2022-01-31 01:04:10.923407+00
2
null
no products

12
UPDATE
2022-01-31 01:04:10.930203+00
2
no products
pending

3
INSERT
2022-01-31 01:04:10.923459+00
3
null
no products

13
UPDATE
2022-01-31 01:04:10.931317+00
3
no products
in progress

20
UPDATE
2022-01-31 01:04:10.93928+00
3
in progress
complete

4
INSERT
2022-01-31 01:04:10.923509+00
4
null
no products

14
UPDATE
2022-01-31 01:04:10.932198+00
4
no products
complete

21
UPDATE
2022-01-31 01:04:10.940166+00
4
complete
pending

24
UPDATE
2022-01-31 01:04:10.94443+00
4
pending
complete

5
INSERT
2022-01-31 01:04:10.923546+00
5
null
no products

15
UPDATE
2022-01-31 01:04:10.933079+00
5
no products
complete

25
UPDATE
2022-01-31 01:04:10.945152+00
5
complete
pending

6
INSERT
2022-01-31 01:04:10.923604+00
6
null
no products

16
UPDATE
2022-01-31 01:04:10.933382+00
6
no products
in progress

7
INSERT
2022-01-31 01:04:10.923629+00
7
null
no products

17
UPDATE
2022-01-31 01:04:10.933703+00
7
no products
complete

8
INSERT
2022-01-31 01:04:10.923647+00
8
null
no products

18
UPDATE
2022-01-31 01:04:10.934008+00
8
no products
pending

22
UPDATE
2022-01-31 01:04:10.941114+00
8
pending
complete

9
INSERT
2022-01-31 01:04:10.923663+00
9
null
no products

19
UPDATE
2022-01-31 01:04:10.934912+00
9
no products
pending

23
UPDATE
2022-01-31 01:04:10.942046+00
9
pending
no products

10
INSERT
2022-01-31 01:04:10.92368+00
10
null
no products

order_id	cust_id	status
10	41	no products
1	25	pending
2	30	pending
3	25	in progress
4	47	complete
5	40	complete
6	1	in progress
7	32	complete
8	10	pending
9	49	pending

cdc_order_id	operation	cdc_tstz	order_id	old_order_status	new_order_status
1	INSERT	2022-01-31 01:04:10.922488+00	1	null	no products
2	INSERT	2022-01-31 01:04:10.923407+00	2	null	no products
3	INSERT	2022-01-31 01:04:10.923459+00	3	null	no products
4	INSERT	2022-01-31 01:04:10.923509+00	4	null	no products
5	INSERT	2022-01-31 01:04:10.923546+00	5	null	no products
6	INSERT	2022-01-31 01:04:10.923604+00	6	null	no products
7	INSERT	2022-01-31 01:04:10.923629+00	7	null	no products
8	INSERT	2022-01-31 01:04:10.923647+00	8	null	no products
9	INSERT	2022-01-31 01:04:10.923663+00	9	null	no products
10	INSERT	2022-01-31 01:04:10.92368+00	10	null	no products
11	UPDATE	2022-01-31 01:04:10.928958+00	1	no products	pending
12	UPDATE	2022-01-31 01:04:10.930203+00	2	no products	pending
13	UPDATE	2022-01-31 01:04:10.931317+00	3	no products	in progress
14	UPDATE	2022-01-31 01:04:10.932198+00	4	no products	complete
15	UPDATE	2022-01-31 01:04:10.933079+00	5	no products	complete
16	UPDATE	2022-01-31 01:04:10.933382+00	6	no products	in progress
17	UPDATE	2022-01-31 01:04:10.933703+00	7	no products	complete
18	UPDATE	2022-01-31 01:04:10.934008+00	8	no products	pending
19	UPDATE	2022-01-31 01:04:10.934912+00	9	no products	pending