Postgres 12

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?.

create table foo (
    id serial primary key,
    a int,
    b int,
    c int,
    a_tstz timestamptz not null default now(),
    b_tstz timestamptz not null default now(),
    c_tstz timestamptz not null default now()
);
insert into foo (a,b,c)
values
(1,2,3),
(4,5,6);

CREATE TABLE



INSERT 0 2


select pg_sleep(10);


pg_sleep






SELECT 1


create procedure bar (payload jsonb)
language sql
as $$
    with j as (
       select payload as p
    ), cte as (
        select 
            (p->>'id')::int as id,
            case when p ? 'a' then (p->>'a')::int else f.a end as a,
            case when p ? 'b' then (p->>'b')::int else f.b end as b,
            case when p ? 'c' then (p->>'c')::int else f.c end as c,
            case when p ? 'a' then now()::timestamptz else f.a_tstz end as a_tstz,
            case when p ? 'b' then now()::timestamptz else f.b_tstz end as b_tstz,
            case when p ? 'c' then now()::timestamptz else f.c_tstz end as c_tstz
        from foo f
        join j on (j.p->>'id')::int = f.id 
    )
    update foo as f set
        a = c.a,
        b = c.b,
        c = c.c,
        a_tstz = c.a_tstz,
        b_tstz = c.b_tstz,
        c_tstz = c.c_tstz
    from cte as c
    where c.id = f.id;
$$

CREATE PROCEDURE


call bar('{"id":1,"a":1,"c":null}'::jsonb);

CALL


select * 
from foo;


id
a
b
c
a_tstz
b_tstz
c_tstz


2
4
5
6
2022-11-07 12:43:45.521336+00
2022-11-07 12:43:45.521336+00
2022-11-07 12:43:45.521336+00

1
1
2
null
2022-11-07 12:43:55.569869+00
2022-11-07 12:43:45.521336+00
2022-11-07 12:43:55.569869+00



SELECT 2

id	a	b	c	a_tstz	b_tstz	c_tstz
2	4	5	6	2022-11-07 12:43:45.521336+00	2022-11-07 12:43:45.521336+00	2022-11-07 12:43:45.521336+00
1	1	2	null	2022-11-07 12:43:55.569869+00	2022-11-07 12:43:45.521336+00	2022-11-07 12:43:55.569869+00