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();
create schema schemaone;
create schema schematwo;

CREATE FUNCTION schemaone.get_pg_stat_activity() RETURNS SETOF pg_stat_activity
    LANGUAGE sql SECURITY DEFINER
    AS $$ SELECT * FROM pg_catalog.pg_stat_activity; $$;


version


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



SELECT 1



CREATE SCHEMA



CREATE SCHEMA



CREATE FUNCTION


--version 10-12
CREATE VIEW schematwo.pg_stat_activity AS
SELECT *
--FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query, backend_type, "query (2)", "backend_type (2)");
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query, backend_type)--, "query (2)", "backend_type (2)");

ERROR:  column "query" specified more than once


--version 13
CREATE VIEW schematwo.pg_stat_activity AS
SELECT *
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query, backend_type, "backend_type (2)");

ERROR:  column "backend_type" specified more than once


--version 14-16
CREATE VIEW schematwo.pg_stat_activity AS
SELECT *
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query, backend_type, "query (2)", "backend_type (2)");

CREATE VIEW


select p.ordinal_position,
       p.column_name as source_column,
       v.column_name as your_alias,
       p.column_name=v.column_name as is_equal,
       case when v.column_name=p.column_name then 0
            when v.column_name=lead(p.column_name,1)over() then 1
            when v.column_name=lead(p.column_name,2)over() then 2
            else 0 end as name_shifted_by
from (  select p.ordinal_position,
               p.column_name
        from information_schema.columns p
        where table_schema='pg_catalog' 
        and table_name='pg_stat_activity'
        order by p.ordinal_position) p 
join (  select v.ordinal_position,
               v.column_name
        from information_schema.columns v
        where table_schema='schematwo' 
        and table_name='pg_stat_activity'
        order by v.ordinal_position) v 
on p.ordinal_position=v.ordinal_position;


ordinal_position
source_column
your_alias
is_equal
name_shifted_by


1
datid
datid
t
0

2
datname
datname
t
0

3
pid
pid
t
0

4
leader_pid
usesysid
f
1

5
usesysid
usename
f
1

6
usename
application_name
f
1

7
application_name
client_addr
f
1

8
client_addr
client_hostname
f
1

9
client_hostname
client_port
f
1

10
client_port
backend_start
f
1

11
backend_start
xact_start
f
1

12
xact_start
query_start
f
1

13
query_start
state_change
f
1

14
state_change
wait_event_type
f
1

15
wait_event_type
wait_event
f
1

16
wait_event
state
f
1

17
state
backend_xid
f
1

18
backend_xid
backend_xmin
f
1

19
backend_xmin
query
f
2

20
query_id
backend_type
f
2

21
query
query (2)
f
0

22
backend_type
backend_type (2)
f
0



SELECT 22


CREATE VIEW schematwo.pg_stat_activity2 AS
SELECT 
    get_pg_stat_activity.datid,
    get_pg_stat_activity.datname,
    get_pg_stat_activity.pid,
    get_pg_stat_activity.usesysid,
    get_pg_stat_activity.usename,
    get_pg_stat_activity.application_name,
    get_pg_stat_activity.client_addr,
    get_pg_stat_activity.client_hostname,
    get_pg_stat_activity.client_port,
    get_pg_stat_activity.backend_start,
    get_pg_stat_activity.xact_start,
    get_pg_stat_activity.query_start,
    get_pg_stat_activity.state_change,
    get_pg_stat_activity.wait_event_type,
    get_pg_stat_activity.wait_event,
    get_pg_stat_activity.state,
    get_pg_stat_activity.backend_xid,
    get_pg_stat_activity.backend_xmin,
    get_pg_stat_activity.query,
    get_pg_stat_activity.backend_type
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, leader_pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query_id, query, backend_type);

CREATE VIEW


CREATE VIEW schematwo.pg_stat_activity3 AS
SELECT 
    get_pg_stat_activity.datid,
    get_pg_stat_activity.datname,
    get_pg_stat_activity.pid,
    get_pg_stat_activity.usesysid,
    get_pg_stat_activity.usename,
    get_pg_stat_activity.application_name,
    get_pg_stat_activity.client_addr,
    get_pg_stat_activity.client_hostname,
    get_pg_stat_activity.client_port,
    get_pg_stat_activity.backend_start,
    get_pg_stat_activity.xact_start,
    get_pg_stat_activity.query_start,
    get_pg_stat_activity.state_change,
    get_pg_stat_activity.wait_event_type,
    get_pg_stat_activity.wait_event,
    get_pg_stat_activity.state,
    get_pg_stat_activity.backend_xid,
    get_pg_stat_activity.backend_xmin,
    get_pg_stat_activity.query,
    get_pg_stat_activity.backend_type
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity;

CREATE VIEW


CREATE VIEW schematwo.pg_stat_activity4 AS
SELECT * FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, leader_pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query_id, query, backend_type);

CREATE VIEW


CREATE VIEW schematwo.pg_stat_activity5 AS
SELECT * FROM schemaone.get_pg_stat_activity();

CREATE VIEW

ordinal_position	source_column	your_alias	is_equal	name_shifted_by
1	datid	datid	t	0
2	datname	datname	t	0
3	pid	pid	t	0
4	leader_pid	usesysid	f	1
5	usesysid	usename	f	1
6	usename	application_name	f	1
7	application_name	client_addr	f	1
8	client_addr	client_hostname	f	1
9	client_hostname	client_port	f	1
10	client_port	backend_start	f	1
11	backend_start	xact_start	f	1
12	xact_start	query_start	f	1
13	query_start	state_change	f	1
14	state_change	wait_event_type	f	1
15	wait_event_type	wait_event	f	1
16	wait_event	state	f	1
17	state	backend_xid	f	1
18	backend_xid	backend_xmin	f	1
19	backend_xmin	query	f	2
20	query_id	backend_type	f	2
21	query	query (2)	f	0
22	backend_type	backend_type (2)	f	0