Postgres 15

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 variable (id int, name varchar(10))

CREATE TABLE

INSERT INTO variable VALUES(1,'test'),(2,'test2')

INSERT 0 2

CREATE tABLe variable_variable (variable_id_from int,variable_id_to int)

CREATE TABLE

INSERT INTO variable_variable VALUEs (3,1)

INSERT 0 1

CREATE OR REPLACE FUNCTION DELETE_ONLY_()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
declare
  l_count integer;
begin 
  select count(*)
    into l_count
  from variable_variable WHERE variable_id_to = OLd.id;

  if l_count > 0 then 
    raise exception 'There are still records';
  end if; 
  return old;
end; 

$$

CREATE FUNCTION

CREATE TRIGGER trigger_name 
    BEFORE DELETE
   ON variable
   FOR EACH ROW
       EXECUTE PROCEDURE DELETE_ONLY_()

CREATE TRIGGER

DELETE FROM variable WHERE id = 1

ERROR:  There are still records
CONTEXT:  PL/pgSQL function delete_only_() line 10 at RAISE

DELETE FROM variable WHERE id = 2

DELETE 1

SELECT * FROM variable

id
name

1
test

SELECT 1