Postgres 16

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 metric_events_1(event_at,id,code,is_test) as values
 (clock_timestamp()-'3h'::interval,1,'code1',false)
,(clock_timestamp()-'2h'::interval,2,'code2',false);

SELECT 2


CREATE OR REPLACE FUNCTION my_query(part_id integer)
RETURNS TABLE (
  event_at timestamp,
  id integer,
  code varchar,
  is_test boolean
)
 LANGUAGE plpgsql IMMUTABLE 
AS $function$
DECLARE
    query varchar := format("SELECT date_trunc('hour', event_at),
               id,
               code,
               is_test
        FROM metric_events_%s
         event_at < date_trunc('hour', NOW() - interval '1 hour')
         != 0", part_id);
    
BEGIN
    RAISE NOTICE 'Value: %', query;
    RETURN QUERY EXECUTE query;
END $function$;

select * from my_query(1);

CREATE FUNCTION



ERROR:  column "SELECT date_trunc('hour', event_at),
               id,
       " does not exist
LINE 1: format("SELECT date_trunc('hour', event_at),
               ^
QUERY:  format("SELECT date_trunc('hour', event_at),
               id,
               code,
               is_test
        FROM metric_events_%s
         event_at < date_trunc('hour', NOW() - interval '1 hour')
         != 0", part_id)
CONTEXT:  PL/pgSQL function my_query(integer) line 3 during statement block local variable initialization


CREATE OR REPLACE FUNCTION my_query(part_id integer)
RETURNS TABLE (
  event_at timestamptz, --timestamptz is preferable
  id integer,
  code text, --text is a better choice than varchar
  is_test boolean
)
 LANGUAGE plpgsql IMMUTABLE 
AS $function$
DECLARE
    query varchar := format(
        $dynsql$ --double quotes replaced with named dollar quotes
        SELECT date_trunc('hour', event_at),
               id,
               code,
               is_test
        FROM metric_events_%s --added missing `WHERE clause`
        WHERE event_at < date_trunc('hour', NOW() - interval '1 hour') 
        --unnecessary `!=0` removed
        $dynsql$ , part_id);
    
BEGIN
    RAISE NOTICE 'Value: %', query;
    RETURN QUERY EXECUTE query;
END $function$;

select * from my_query(1);

CREATE FUNCTION




event_at
id
code
is_test


2024-04-08 06:00:00+00
1
code1
f

2024-04-08 07:00:00+00
2
code2
f



SELECT 2


create table metric_events (
  event_at timestamptz,
  id integer,
  code text,
  is_test boolean) partition by range(event_at);

alter table metric_events attach partition metric_events_1
    for values from (now()+'-4h') to (now()+'-1h');

select tableoid::regclass,* from metric_events where event_at>now()+'-5h';

CREATE TABLE



ALTER TABLE




tableoid
event_at
id
code
is_test


metric_events_1
2024-04-08 06:54:11.769316+00
1
code1
f

metric_events_1
2024-04-08 07:54:11.769422+00
2
code2
f



SELECT 2


select now()+'-5h';
select now()-'5h';


?column?


2024-04-08 04:54:11.78998+00



SELECT 1



ERROR:  invalid input syntax for type timestamp with time zone: "5h"
LINE 2: select now()-'5h';
                     ^


--plus `+` is unambiguous
--minus `-` means the right operand could be either an `interval` or a `timestamp`
--which means `-` requires an explicit cast of the constant
SELECT --n.nspname as "Schema",
  o.oprname AS "Name",
  CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS "Left arg type",
  CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS "Right arg type",
  pg_catalog.format_type(o.oprresult, NULL) AS "Result type"
  --,coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),
           --pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS "Description"
FROM pg_catalog.pg_operator o
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace
     LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprleft
     LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace
     LEFT JOIN pg_catalog.pg_type t1 ON t1.oid = o.oprright
     LEFT JOIN pg_catalog.pg_namespace nt1 ON nt1.oid = t1.typnamespace
WHERE (o.oprname OPERATOR(pg_catalog.~) '^(-)$' COLLATE pg_catalog.default
   OR o.oprname OPERATOR(pg_catalog.~)  E'^(\\+)$' COLLATE pg_catalog.default)
  AND pg_catalog.pg_operator_is_visible(o.oid)
  AND (t0.typname OPERATOR(pg_catalog.~) '^(timestamp)$' COLLATE pg_catalog.default
        OR pg_catalog.format_type(t0.oid, NULL) OPERATOR(pg_catalog.~) '^(timestamp)$' COLLATE pg_catalog.default)
  AND pg_catalog.pg_type_is_visible(t0.oid)
  AND pg_catalog.pg_type_is_visible(t1.oid)
ORDER BY 1, 2, 3, 4;


Name
Left arg type
Right arg type
Result type


+
timestamp without time zone
interval
timestamp without time zone

-
timestamp without time zone
interval
timestamp without time zone

-
timestamp without time zone
timestamp without time zone
interval



SELECT 3

tableoid	event_at	id	code	is_test
metric_events_1	2024-04-08 06:54:11.769316+00	1	code1	f
metric_events_1	2024-04-08 07:54:11.769422+00	2	code2	f

Name	Left arg type	Right arg type	Result type
+	timestamp without time zone	interval	timestamp without time zone
-	timestamp without time zone	interval	timestamp without time zone
-	timestamp without time zone	timestamp without time zone	interval