Postgres 13

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 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


-- setup 
create table test(tid  integer generated always as identity
                               primary key
                 , order_view  integer  not null
                               constraint  test_order_view_uk
                               unique
                 , name        text
                 ); 

insert into test ( order_view, name) 
    select n, 'Initial-' || to_char(n,'00') 
      from generate_series ( 1, 7) gs(n); 

select * from test;
7 rows affected



tid
order_view
name


1
1
Initial- 01

2
2
Initial- 02

3
3
Initial- 03

4
4
Initial- 04

5
5
Initial- 05

6
6
Initial- 06

7
7
Initial- 07


alter table test
  drop constraint if exists test_order_view_uk;
alter table test
  add constraint test_order_view_uk 
        unique(order_view) 
        deferrable initially deferred;
-- insert a new order_view 4
-- hard code demo values as fiddle in not interactive 
with updt as
     ( update test 
          set order_view = order_view+1 
        where order_view >= 4
     ) 
insert into test (order_view, name) 
   values (4, 'insert new 4');
   
select * from test;   
1 rows affected



tid
order_view
name


1
1
Initial- 01

2
2
Initial- 02

3
3
Initial- 03

8
4
insert new 4

4
5
Initial- 04

5
6
Initial- 05

6
7
Initial- 06

7
8
Initial- 07


-- build Procedure to handle
create or replace 
procedure insert_reorder_test(
          name_in       test.name%type 
        , order_view_in test.order_view%type default null
        )
  language sql 
as $$
    with new_order_view(nov) as 
         ( select coalesce( order_view_in 
                          , (select 1+coalesce(max(order_view),0)  
                               from test
                            )
                          )
         )
       , updt as
         ( update test 
              set order_view = order_view+1 
            where order_view >= (select nov from new_order_view)
         ) 
    insert into test ( name, order_view) 
      select name_in
           , nov 
        from new_order_view;
$$; 
-- use function
do $$
begin
   call insert_reorder_test('Create new #3',3);
   call insert_reorder_test('Add to end of list',null);
end;    
$$;

select * from test; 


tid
order_view
name


1
1
Initial- 01

2
2
Initial- 02

9
3
Create new #3

3
4
Initial- 03

8
5
insert new 4

4
6
Initial- 04

5
7
Initial- 05

6
8
Initial- 06

7
9
Initial- 07

10
10
Add to end of list

tid	order_view	name
1	1	Initial- 01
2	2	Initial- 02
3	3	Initial- 03
4	4	Initial- 04
5	5	Initial- 05
6	6	Initial- 06
7	7	Initial- 07

tid	order_view	name
1	1	Initial- 01
2	2	Initial- 02
9	3	Create new #3
3	4	Initial- 03
8	5	insert new 4
4	6	Initial- 04
5	7	Initial- 05
6	8	Initial- 06
7	9	Initial- 07
10	10	Add to end of list