`column "{refnr,beruf,titel,..." not found in data type stellenangebote_active pg(plpgsql_check)`

[diraneyya]

Bug report

I switched to using the vscode extension that relies on this server from UniqueVision's unmaintained extension and despite initial hiccups, it is working right now and I am very grateful for it.

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Most of my extension code is interpreted correctly and good feedback is given by the server and the syntax checker except for one instance: (an auditing trigger function)

create or replace function stellenangebote_update_audit_handler ()
    returns trigger language plpgsql as $audit$
declare
    column_names text[] := array[
        'refnr',
        'beruf',
        'titel',
        'ort',
        'plz',
        'region',
        'land',
        'lat',
        'lon',
        'arbeitgeber',
        'veroeffentlichungsdatum',
        'modifikationstimestamp',
        'eintrittsdatum',
        'kundennummerhash',
        'externeurl'
    ];
    t text := null;
    is_equal boolean;
begin 
    foreach t in array column_names loop
        execute format('select ($1).%1$I is not distinct from ($2).%1$I', t)
            using old, new into is_equal;
        if not is_equal then 
            execute format('insert into stellenangebote_update_audit '
                '(job_refnr, column_name, old_value, new_value) values '
                '($1, ''%1$s'', ($2).%1$I::text, ($3).%1$I::text)', t)
                using old.refnr, old, new;
        end if;
    end loop;
    return new;
end $audit$;

Note that the stellenangebote and the audit tables are defined as follows:

create table if not exists stellenangebote_update_audit (
    created timestamp primary key default current_timestamp,
    job_refnr text not null,
    column_name text not null,
    old_value text,
    new_value text
);

create table if not exists stellenangebote (
    refnr varchar(32) not null,
    beruf text null,
    titel text null,
    ort text null,
    plz ValidPLZ null,
    region text null, 
    land text null,
    lat numeric null,
    lon numeric null,
    arbeitgeber text null,
    veroeffentlichungsdatum date null,
    modifikationsTimestamp timestamp null,
    eintrittsdatum date null,
    kundennummerHash text null,
    externeUrl text null,
    created timestamp not null default current_timestamp,
    updated timestamp not null default current_timestamp,
    deleted timestamp null,
    archive_partition smallint not null default 0 check (archive_partition >= 0)
) partition by range(archive_partition);

To Reproduce

NA

Expected behavior

The code works great so there should not be an error. It seems as if the syntax checker things that the array of column names is a column name which is not the case. This may have also to do with the fact I am using a partitioned table, not entirely sure.

Screenshots

System information

• OS: Linux

Additional context

NA

[psteinroe]

hey, thanks for reporting! could you share the postgres version of your dev database, as well as the version of plpgsql_check that is installed? I tried to repro it, but without success.

[diraneyya]

Hey @psteinroe, can I say thank you for being so incredible and prompt in responding? Something I certainly miss and love to see in open-source projects.

In this case, I have not been as prompt as you have been. The reason being: very busy building! Today as I was troubleshooting other issues I saw the following notice in my PostgreSQL logs (thanks to https://github.com/tstack/lnav, another great project)

NOTICE:  identifier "{refnr,beruf,titel,ort,plz,region,land,lat,lon,arbeitgeber,veroeffentlichungsdatum,eintrittsdatum,kundennummerhash,externeurl}" will be truncated to "{refnr,beruf,titel,ort,plz,region,land,lat,lon,arbeitgeber,vero"

This log line explains what we see in the error message:

column "{refnr,beruf,titel,ort,plz,region,land,lat,lon,arbeitgeber,vero" not found in data type stellenangebote_activepg(plpgsql_check)

I am using version 2.8 of plpgsql_check and my PostgreSQL database is version 17.4 (Ubuntu 17.4-1.pgdg22.04+2)