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.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit



SELECT 1


create table events ( event_id   integer generated always as identity 
                    , name       text    not null
                    , descripton text
                    , location   text
                    , status     text not null default 'scheduled' 
                                      check ( status in ('scheduled', 'canceled', 'in progress', 'complete') )
                    , start_ts   timestamp   not null 
                    , end_ts     timestamp  
                    --- other event attributes
                    , constraint events_pk primary key (event_id)
                    , constraint event_name_bk unique (name) 
                    ); 
                  
 create table event_audits( ea_id      integer generated always as identity 
                          , event_id   integer not null 
                          , dml_action text    not null
                          , audit_tstz timestamptz not null default clock_timestamp() 
                          , old_status text  
                          -- other audit attributes
                          , constraint event_audits_pk primary key (ea_id) 
                          , constraint event_audits_2_events_fk 
                                       foreign key (event_id) 
                                       references events(event_id)          
                          ); 
                                                
create table event_last_audits( 
             event_id integer 
           , ea_id    integer
           , constraint last_event_audits_pk primary key (event_id) 
           , constraint last_event_audits_2fk   
                        foreign key (ea_id)
                        references event_audits(ea_id)
           );

CREATE TABLE



CREATE TABLE



CREATE TABLE


create or replace function  audit_events()
  returns trigger 
  language plpgsql
as $$
begin 
	with audit_rec( event_id, ea_id)  as 
	     ( insert into event_audits(event_id, dml_action,old_status) 
	           select new.event_id
	                , tg_op
	                , case when tg_op='INSERT'
	                       then null 
	                        else old.status 
	                  end
	         returning event_id, ea_id
	     ) 
	insert into event_last_audits(event_id, ea_id) 
	     select event_id, ea_id
	       from audit_rec
	         on conflict (event_id) 
	         do update 
	                set ea_id = excluded.ea_id; 
	 return new; 
end; $$; 
 

create trigger audit_events_aiur
       after insert or update on events 
       for each row 
       execute function audit_events(); 

CREATE FUNCTION



CREATE TRIGGER


insert into events( name , descripton, location, start_ts, end_ts) 
     values ( 'Downtown Geo. Lecture Series' 
            , 'Each Wednesday, Public presentation of recent (last 5m years) geology'
            , '6th Street auditorium, Downtown'
            , '2021-09-08 19:00'::timestamp
            , '2021-09-08 20:30'::timestamp   
            ) 
          , ( 'Council Special Meeting #4186' 
            , 'Proposed new sports areana'
            , 'City Hall, rm 212' 
            , '2021-10-08 19:00'::timestamp
            , '2021-10-08 21:30'::timestamp
            ) ;

INSERT 0 2


select * from events; 
select * from event_audits; 
select * from event_last_audits; 


event_id
name
descripton
location
status
start_ts
end_ts


1
Downtown Geo. Lecture Series
Each Wednesday, Public presentation of recent (last 5m years) geology
6th Street auditorium, Downtown
scheduled
2021-09-08 19:00:00
2021-09-08 20:30:00

2
Council Special Meeting #4186
Proposed new sports areana
City Hall, rm 212
scheduled
2021-10-08 19:00:00
2021-10-08 21:30:00



SELECT 2




ea_id
event_id
dml_action
audit_tstz
old_status


1
1
INSERT
2022-11-20 10:46:12.090909+00
null

2
2
INSERT
2022-11-20 10:46:12.093157+00
null



SELECT 2




event_id
ea_id


1
1

2
2



SELECT 2


update events 
   set status = 'in progress'
  where name = 'Downtown Geo. Lecture Series' ; 
select * from event_last_audits order by event_id; 

UPDATE 1




event_id
ea_id


1
3

2
2



SELECT 2


update events 
   set status = 'canceled'
  where name = 'Council Special Meeting #4186' ; 
select * from event_last_audits order by event_id;

UPDATE 1




event_id
ea_id


1
3

2
4



SELECT 2


update events 
   set status = 'complete'
  where name = 'Downtown Geo. Lecture Series';
select * from event_last_audits order by event_id;  

UPDATE 1




event_id
ea_id


1
5

2
4



SELECT 2


update events 
   set status = 'scheduled' 
     , start_ts = start_ts + interval '1 week'
     , end_ts = end_ts + interval '1 week'     
  where name = 'Downtown Geo. Lecture Series' ;

UPDATE 1


select * from events; 
select * from event_audits;
select * from event_last_audits order by event_id;


event_id
name
descripton
location
status
start_ts
end_ts


2
Council Special Meeting #4186
Proposed new sports areana
City Hall, rm 212
canceled
2021-10-08 19:00:00
2021-10-08 21:30:00

1
Downtown Geo. Lecture Series
Each Wednesday, Public presentation of recent (last 5m years) geology
6th Street auditorium, Downtown
scheduled
2021-09-15 19:00:00
2021-09-15 20:30:00



SELECT 2




ea_id
event_id
dml_action
audit_tstz
old_status


1
1
INSERT
2022-11-20 10:46:12.090909+00
null

2
2
INSERT
2022-11-20 10:46:12.093157+00
null

3
1
UPDATE
2022-11-20 10:46:12.09624+00
scheduled

4
2
UPDATE
2022-11-20 10:46:12.098014+00
scheduled

5
1
UPDATE
2022-11-20 10:46:12.09952+00
in progress

6
1
UPDATE
2022-11-20 10:46:12.102235+00
complete



SELECT 6




event_id
ea_id


1
6

2
4



SELECT 2


 -- get last event action 
select e.name, e.status, ea.audit_tstz "Last Changes" 
  from event_last_audits ela 
  join event_audits      ea    on (ea.ea_id = ela.ea_id) 
  join events            e     on (e.event_id = ea.event_id)  
 order by e.name;


name
status
Last Changes


Council Special Meeting #4186
canceled
2022-11-20 10:46:12.098014+00

Downtown Geo. Lecture Series
scheduled
2022-11-20 10:46:12.102235+00



SELECT 2


-- get last event action view 
create view last_event as 
 select e.name, e.status, ea.audit_tstz "Last Changes"
   from event_last_audits lea 
   join event_audits      ea    on (ea.ea_id = lea.ea_id) 
   join events            e     on (e.event_id = ea.event_id)  
  order by e.name;

CREATE VIEW


select * from last_event;


name
status
Last Changes


Council Special Meeting #4186
canceled
2022-11-20 10:46:12.098014+00

Downtown Geo. Lecture Series
scheduled
2022-11-20 10:46:12.102235+00



SELECT 2

event_id	name	descripton	location	status	start_ts	end_ts
1	Downtown Geo. Lecture Series	Each Wednesday, Public presentation of recent (last 5m years) geology	6th Street auditorium, Downtown	scheduled	2021-09-08 19:00:00	2021-09-08 20:30:00
2	Council Special Meeting #4186	Proposed new sports areana	City Hall, rm 212	scheduled	2021-10-08 19:00:00	2021-10-08 21:30:00

ea_id	event_id	dml_action	audit_tstz	old_status
1	1	INSERT	2022-11-20 10:46:12.090909+00	null
2	2	INSERT	2022-11-20 10:46:12.093157+00	null

name	status	Last Changes
Council Special Meeting #4186	canceled	2022-11-20 10:46:12.098014+00
Downtown Geo. Lecture Series	scheduled	2022-11-20 10:46:12.102235+00