okbob / plpgsql_check Goto Github PK

Hi,
we are working on getting plpgsql_check into Debian. Unfortunately the latest 1.11.0 release crashes:

13:29:33 9.5 regress 5432 online postgres /tmp/pg_virtualenv.UeAhNP/data/9.5/regress /tmp/pg_virtualenv.UeAhNP/log/postgresql-9.5-regress.log
13:29:34 ============== running regression test queries        ==============
13:29:34 test plpgsql_check_passive    ... ok
13:29:34 test plpgsql_check_active     ... FAILED (test process exited with exit code 2)
13:29:34 test plpgsql_check_active-9.5 ... FAILED (test process exited with exit code 2)
13:29:34 test plpgsql_check_passive-9.5 ... ok

13:29:34 2020-07-17 11:29:34.354 UTC [6978] postgres@contrib_regression ERROR:  could not identify column "a" in record data type at character 8
13:29:34 2020-07-17 11:29:34.354 UTC [6978] postgres@contrib_regression QUERY:  select $1.a + $1.b
13:29:34 2020-07-17 11:29:34.354 UTC [6978] postgres@contrib_regression CONTEXT:  PL/pgSQL function dyn_sql_2() line 8 at EXECUTE
13:29:34 2020-07-17 11:29:34.354 UTC [6978] postgres@contrib_regression STATEMENT:  select dyn_sql_2();
13:29:34 2020-07-17 11:29:34.360 UTC [6928] LOG:  server process (PID 6978) was terminated by signal 11: Segmentation fault
13:29:34 2020-07-17 11:29:34.360 UTC [6928] DETAIL:  Failed process was running: select * from plpgsql_check_function('dyn_sql_3');
13:29:34 2020-07-17 11:29:34.360 UTC [6928] LOG:  terminating any other active server processes

This is on 9.5. (I don't know if other versions are affected, our testsuite tries 9.5 first and stops there.)

Full build log: https://pgdgbuild.dus.dg-i.net/job/plpgsql-check-binaries/2/architecture=amd64,distribution=sid/consoleFull

Hello,
function with move statement:

create or replace function f1(in p_cursor refcursor) returns void as
$body$
declare
	z_offset integer;
begin
	z_offset := 10;
	move absolute z_offset from p_cursor;
end;
$body$ language 'plpgsql' stable;

plpgsql_check raises warning

select * from plpgsql_check_function_tb('f1(refcursor)');
 functionid | lineno | statement | sqlstate |            message             | detail | hint |  level  | position | query | context
------------+--------+-----------+----------+--------------------------------+--------+------+---------+----------+-------+---------
 f1         |      3 | DECLARE   | 00000    | never read variable "z_offset" |        |      | warning |          |       |

Hi!

From the latest commit from master it doesn't compile. It gives the following error. With v1.4.2 it works fine.

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fPIC -pie -fno-omit-frame-pointer -fPIC -I/usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -Wall -I. -I./ -I/usr/include/postgresql/9.6/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/mit-krb5  -c -o src/catalog.o src/catalog.c
src/catalog.c:20:27: fatal error: utils/regproc.h: No such file or directory
compilation terminated.
<builtin>: recipe for target 'src/catalog.o' failed
make: *** [src/catalog.o] Error 1

Thanks for your work!

in plpgsql

The following function:

create function out1(OUT f1 int, OUT f2 int)
returns setof record as
$$
begin
    for f1, f2 in
        execute $q$ select 1, 2 $q$
    loop
        return next;
    end loop;
    end
$$ language plpgsql;

is being complained about as follows:

=# select * from plpgsql_check_function('out1()');
                plpgsql_check_function
------------------------------------------------------
 warning:00000:1:DECLARE:unused variable "*internal*"
 warning extra:00000:unmodified OUT variable "$1"
 warning extra:00000:unmodified OUT variable "$2"
(3 rows)

create function f1(out cr refcursor) as $$
begin
	open cr for select 1;
end;
$$ language plpgsql;

select * from plpgsql_check_function('f1()');
-- returns: "warning extra:00000:unmodified OUT variable "$1""

We have about 2000 functions with refcursors, so this kind of diagnostic became useless thanks to this issue.

Anyway, thanks for the project!

Trying to build against PostgreSQL 11_STABLE:

src/stmtwalk.c: In function 'plpgsql_check_stmt':
src/stmtwalk.c:563:26: error: too few arguments to function 'plpgsql_build_datatype'
  563 |        t_var->datatype = plpgsql_build_datatype(result_oid,
      |                          ^~~~~~~~~~~~~~~~~~~~~~
In file included from src/plpgsql_check.h:3,
                 from src/stmtwalk.c:12:
C:/Users/pasha/Code/POSTGR~1/msys64/usr/local/POSTGR~1/include/server/plpgsql.h:1199:22: note: declared here
 1199 | extern PLpgSQL_type *plpgsql_build_datatype(Oid typeOid, int32 typmod,
      |                      ^~~~~~~~~~~~~~~~~~~~~~
make: *** [<builtin>: src/stmtwalk.o] Error 1

Is it a bug to receive message about exception that might not be raised? Below is example:

create or replace function test0() returns text 
language plpgsql
STABLE
as
$$
declare
begin
  if (extract(second from clock_timestamp())::integer % 2) = 1 then
    return 'OK';
  end if;
  raise exception 'TEST-EXCEPTION';
end;
$$;

create or replace function test() returns void as
$$
declare
  vstr text;
begin
  select 'test'
    into vstr
    from pg_catalog.pg_class
   where relname = test0();
end;
$$ language plpgsql;

Then if we execute
select plpgsql_check_function('test()');

we may (not always) receive message like:

postgres=# select plpgsql_check_function('test()');
               plpgsql_check_function               
----------------------------------------------------
 error:P0001:5:SQL statement:TEST-EXCEPTION
 Context: PL/pgSQL function test0() line 7 at RAISE
(2 rows)

Which I suppose is not correct. Note that in test0() function "STABLE" keyword does the trick. Without it no error message received

heyho,

i was playing around with your extension and noticed that this function crashes pg10.5 ( but so far not pg12b4)

rollback;
begin;

CREATE EXTENSION IF NOT EXISTS plpgsql_check;

CREATE OR REPLACE FUNCTION t()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
 BEGIN

  CREATE OR REPLACE VIEW art_lang AS SELECT * FROM art;

  CREATE OR REPLACE VIEW art_small AS SELECT ak_nr, ak_bez, ak_znr, ak_mat, ak_dim FROM art;

  --folgendes muß in DO weil sonst das Erstellunggscript knallt: DEVART parst mit den Klammern hier etwas falsch!
  EXECUTE '
    CREATE OR REPLACE VIEW tartikel.art_multilang AS
        SELECT art.*, ac_i,
                (SELECT akbl_txt FROM artblang WHERE akbl_ak_nr=ak_nr AND akbl_spr_key=''D'') AS ak_bez_de,
                (SELECT akbl_txt FROM artblang WHERE akbl_ak_nr=ak_nr AND akbl_spr_key=''EN'') AS ak_bez_en
        FROM art JOIN artcod ON ak_ac=ac_n; --JOIN wegen Suche nach IC

    CREATE OR REPLACE RULE art_update_lang AS ON UPDATE TO tartikel.art_multilang DO INSTEAD
        (SELECT tartikel.art_multilang_updatelang(new.ak_nr, ''D'', new.ak_bez_de);
         SELECT tartikel.art_multilang_updatelang(new.ak_nr, ''EN'', new.ak_bez_en);
        );';
 END $function$;

 select plpgsql_check_function('t()');

i get

psql result:

 🐘 09:57:50 » docker@localhost:6666/docker *#  select plpgsql_check_function('t()');
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Succeeded.
Time: 875.794 ms

Log:

2019-09-19 07:57:48.958 UTC [1] LOG:  database system is ready to accept connections
2019-09-19 07:57:52.532 UTC [1] LOG:  server process (PID 31) was terminated by signal 11: Segmentation fault
2019-09-19 07:57:52.532 UTC [1] DETAIL:  Failed process was running: select plpgsql_check_function('t()');
2019-09-19 07:57:52.532 UTC [1] LOG:  terminating any other active server processes
2019-09-19 07:57:52.532 UTC [32] docker@docker WARNING:  terminating connection because of crash of another server process
2019-09-19 07:57:52.532 UTC [32] docker@docker DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2019-09-19 07:57:52.532 UTC [32] docker@docker HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2019-09-19 07:57:52.533 UTC [28] WARNING:  terminating connection because of crash of another server process
2019-09-19 07:57:52.533 UTC [28] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared
memory.

against version 12beta4 i get the following results:

 🐘 09:55:55 » docker@localhost:6666/docker *#  select plpgsql_check_function('t()');
┌─────────────────────────────────────────────────────────┐
│                 plpgsql_check_function                  │
╞═════════════════════════════════════════════════════════╡
│ error:XX000:9:EXECUTE:plan is not single execution plan │
└─────────────────────────────────────────────────────────┘
(1 row)

Time: 0.810 ms

Hello,
while working on Debian / Ubuntu packaging for your extensions the maintainer ran the regression tests against all active PostgreSQL versions and noticed a problem with the plpgsql_check_passive_2.out file.

Problem is that pg9.4 is showing less context on errors causing it to not match the expected output given in the _2.out file.

Probably a pg9.4 specific out file is required here to have the test pass cleanly.

Difference can be seen here:
https://pgdgbuild.dus.dg-i.net/job/plpgsql-check-binaries/1/architecture=amd64,distribution=sid/consoleFull
when searching for "plpgsql_check_passive_2.out"

Note:
pg9.4 is getting close to its end of live having only 2 month left.
https://www.postgresql.org/support/versioning/

this function:

 CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$
    DECLARE
    original alias for $1;
          reverse_str text;
          i int4;
    BEGIN
        IF original IS NULL THEN
            RETURN null;
        END IF;
        reverse_str := '';
        FOR i IN REVERSE LENGTH(original)..1 LOOP
          reverse_str := reverse_str || substr(original,i,1);
        END LOOP;
    RETURN reverse_str;
    END;$$
    LANGUAGE plpgsql IMMUTABLE;

returns unused variable "i" for me.

ubuntu 18.04, pg 11, latest plpgsql_check from git
set local search_path to public, test; -> error:XX000:3:SET:unrecognized cmd_type:27
case my_var .. end case; -> warning:00000:3:DECLARE:unused variable "my_var"

Noticed plpgsql_check suggested STABLE for a FUNCTION that EXECUTEs CREATE TABLE.

plpgsql_check 1.4,
postgresql 10.6

Here's a simple reproduce:

postgres=# CREATE OR REPLACE FUNCTION foo()
RETURNS VOID AS $$
BEGIN
EXECUTE format('CREATE TABLE bar (colummmm int)');
END
$$ LANGUAGE plpgsql VOLATILE;

CREATE FUNCTION

postgres=# SELECT * FROM plpgsql_check_function('foo()', performance_warnings => true);
plpgsql_check_function

performance:00000:routine is marked as VOLATILE, should be STABLE
(1 row)

postgres=# CREATE OR REPLACE FUNCTION foo()
RETURNS VOID AS $$
BEGIN
EXECUTE format('CREATE TABLE bar (colummmm int)');
END
$$ LANGUAGE plpgsql STABLE;

CREATE FUNCTION

postgres=# SELECT foo();

ERROR: CREATE TABLE is not allowed in a non-volatile function
CONTEXT: SQL statement "CREATE TABLE bar (colummmm int)"
PL/pgSQL function foo() line 3 at EXECUTE

create table t1(a int);
create or replace PROCEDURE test1() as
$$
begin
insert into t1 values(1);
commit;
insert into t1 values(2);
rollback;
end;
$$language plpgsql;
call test1();
select * from plpgsql_check_function('test1()');
plpgsql_check_function
error:2D000:1:COMMIT:invalid transaction termination
(1 row)

Have you considered releasing this on PGXN?

Any chance to get windows binaries for PG10 beta4?

In any case can we get files compatible with PSQL 12.2 & Windows 10 ?
plpgsql_check.dll
plpgsql_check.control
plpgsql_check--0.8.sql

Tried with available version(plpgsql_check-1.9.2). But not working

This is a very obscure one. It occurs when a function calls an immutable function which raises an exception on the first branch (when buried inside an if-statement it doesn't trigger).
I think it happens because immutable functions get in-lined.

create or replace function a() RETURNS VOID AS $$
BEGIN
   IF FALSE THEN
      PERFORM b('a');
   END IF;
END;
$$ LANGUAGE PLPGSQL;


create or replace function b(TEXT) RETURNS VOID AS $$
BEGIN
   RAISE EXCEPTION USING MESSAGE = format('function %s (called fail)', $1) ;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

load 'plpgsql';
select plpgsql_check_function('a()');
-- "error:P0001:4:PERFORM:function a (called fail)"

Changing function b() here to VOLATILE removes the false positive.

It seems that plpgsql_check_function gives a warning where no casting issues are there.

CREATE TYPE t AS (t TEXT);

CREATE FUNCTION f(OUT ta t[]) AS $$
DECLARE
    t t;
BEGIN
    t.t := 'abc';
    ta[1] := t;
END;
$$ LANGUAGE plpgsql;

SELECT f(); -- works ok
SELECT plpgsql_check_function('f()');
/*
"warning:42804:6:assignment:target variable has different type than expression result"
"Detail: assign "text" value to "t" variable"
"Hint: The input expression type does not have an assignment cast to the target type."
*/

Assigning untyped 'null' to a composite target makes plpgsql_check detect an error where there is none.

CREATE TABLE t(i int);

CREATE FUNCTION test_t(OUT t) RETURNS t AS $$
BEGIN
    $1 := null; -- null::t would prevent error reporting
END;
$$ LANGUAGE plpgsql;

LOAD 'plpgsql';
SELECT plpgsql_check_function_tb('public.test_t()');

-- "(test_t,3,assignment,42804,"cannot assign scalar variable to composite target",,,error,,,)"

Consider adding dependency on to build the extension. At least for Ububtu/Xeniall I had to install libicu-dev.
Something like sudo apt install libicu-dev might be added into readme.

We're starting to have triggers declared with REFERENCING NEW TABLE AS for which plpgsql_check returns relation "xxxx" does not exist errors.

Maybe these names could be passed to the check fuctions, as they are available from pg_trigger ?

Regards

Hello,
i was trying the new 1.2.2 release against our openbravo codebase and it segfaults trying to check one of our pl-functions.

Problem is that that the 'refname' if some variable declaration is null and so the is_internal function defaults comparing it to "internal".

Attached patch works around the segfault by adding an explicit null check. Note i just did return true for is_internal to work around the crash. I did not investigate how that variable definition should be classified.

Backtrace from gdb:
#0 __strcmp_sse2_unaligned () at ../sysdeps/x86_64/multiarch/strcmp-sse2-unaligned.S:31
#1 0x00007f6f55489c46 in is_internal (refname=0x0, lineno=2) at plpgsql_check.c:2897
#2 0x00007f6f55489d14 in datum_is_explicit (cstate=0x7ffd0e884a60, dno=3) at plpgsql_check.c:2923
#3 0x00007f6f5548a022 in report_unused_variables (cstate=0x7ffd0e884a60) at plpgsql_check.c:3033
#4 0x00007f6f55485fd5 in function_check (func=0x561c23b8c460, fcinfo=0x7ffd0e884c40, estate=0x7ffd0e884ad0,
cstate=0x7ffd0e884a60) at plpgsql_check.c:1247
#5 0x00007f6f55485c01 in check_plpgsql_function (procTuple=0x7f6f558d0248, relid=0, trigtype=PLPGSQL_NOT_TRIGGER,
tupdesc=0x561c23b82840, tupstore=0x561c23b82950, format=1, fatal_errors=1 '\001', other_warnings=1 '\001',
performance_warnings=0 '\000', extra_warnings=1 '\001') at plpgsql_check.c:1134
#6 0x00007f6f55484d7b in plpgsql_check_function (fcinfo=0x7ffd0e8851a0) at plpgsql_check.c:651

(gdb) up
#1 0x00007f6f55489c46 in is_internal (refname=0x0, lineno=2) at plpgsql_check.c:2897
warning: Source file is more recent than executable.
2897 if (strcmp(refname, "internal") == 0)

Also attached is the pl-function which is triggered the problem (cut down to the minimum to trigger the crash).
The trigger seems to be having that "declare cursor" line. Removing that make the problem not happen.

Test was done against postgresql-9.3.

Stefan
issue-30.diff.txt
issue-30-reproducer.txt

Hi,

Postgresql treats "character varying/varchar" as "text" as same type for index searches.
I made several test and same index are always used no matter which of this 2 types you use

where field=value:text
where field=value:character varying

In my functions I usually mix this 2 types because character varying let me specify a size for formatting grids in function that return tables and my table data types are usually text.

I think that reporting this hidden cast as a performance issue is wrong.

Is there any way to disable only this performance warning?

I noticed that when i try to run plpgsql_check on a trigger function writting in plpgsql that it fails with the following error:

select plpgsql_check_function(87001);

ERROR: missing trigger relation
HINT: Trigger relation oid must be valid

Is that to be expected as not supported ?
As from the error message it is not directly clear to me..

For example, consider the following function:

CREATE FUNCTION a() RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
 foo int;
BEGIN
  foo := 2;
  RETURN 1;
END
$$;

In the above function, the variable foo is assigned a value but it is never read from. I think this would always indicate a mistake when writing the function, since the variable could just be omitted. Would it be possible to add a check for this condition?

Given the following SQL code:

CREATE TABLE my_table(r1 int, r2 int);
CREATE FUNCTION f() RETURNS INT
LANGUAGE PLPGSQL
AS $_$
DECLARE
record my_table;
BEGIN
  SELECT * INTO record FROM my_table;
  RETURN record.r1;
END
$_$;

ALTER table my_table drop column r2;
SELECT * FROM plpgsql_check_function('f()');

The following output is produced:

                         plpgsql_check_function                         
------------------------------------------------------------------------
 warning:00000:5:SQL statement:too few attributies for target variables
 Detail: There are more target variables than output columns in query.
 Hint: Check target variables in SELECT INTO statement.

I may be wrong about this, but I believe this warning is unnecessary?
It seems to me like the cause of the problem is the ALTER TABLE command.

Additionally, attributies should become attributes

declare
m_TargetRef refcursor := null;
m_TargetRec record;
begin
m_TargetRef := my_func (/* cursor returning function*/);
fetch m_TargetRef into m_TargetRec; -- plpgsql_check force error at this point

With the version 1.2.3 I have no errors in my database code. With the current version (master) I have a lot of errors like the following (which I took as an example, if necessary I can provide the tables' structures but I don't think is necessary):

functionid | cw8icollectstats()
lineno     | 38
statement  | FOR over cursor
sqlstate   | 42601
message    | query "SELECT s.SCHEMA, '0' ;" returned 2 columns
detail     | [null]
hint       | [null]
level      | error
position   | [null]
query      | [null]
context    | [null]
schema     | coswin

A lot

CREATE OR REPLACE FUNCTION coswin.cw8icollectstats()
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$ 
DECLARE
    cAuthTicket CURSOR(vSchema varchar, vType varchar) IS
        SELECT
               COUNT( 1 )::integer TICKETS
          FROM
               COSWIN_TYPES.AUTH_TICKET
         WHERE
               SUBSTR(CWAT_USER_NAME, 1, LENGTH(CWAT_USER_NAME) - LENGTH(CWAT_LOGIN_USER) - 1) = vSchema AND
               SUBSTR( CWAT_TICKET, 1, 1 ) = vType;
    iStandardTickets    integer := 0;
    iLightTickets       integer := 0;
    iWsTickets          integer := 0;
    iNomadTickets       integer := 0;
    s RECORD; -- implicit record must be explicit for pg
BEGIN
    -- null session global cursors names to avoid 'cursor already open' problems
    cAuthTicket := null;

    <<loop_schemas>>
    FOR s IN (
        SELECT
               upper(current_database())AS SCHEMA
          FROM
               information_schema.tables
         WHERE
               table_schema = 'coswin' and
               TABLE_NAME = lower('PARAMETERS') AND
               current_database() NOT IN ( lower('TWINBASE') )
    )
    LOOP
        iStandardTickets := 0;
        iLightTickets := 0;
        iWsTickets := 0;
        iNomadTickets := 0;
        <>
        FOR t IN cAuthTicket( s.SCHEMA, '0' )
            LOOP
            iStandardTickets := t.TICKETS;
            EXIT count_standard_tickets;
        END LOOP count_standard_tickets;
        <<count_light_tickets>>
        FOR t IN cAuthTicket( s.SCHEMA, '1' )
            LOOP
            iLightTickets := t.TICKETS;
            EXIT count_light_tickets;
        END LOOP count_light_tickets;
        <<count_ws_tickets>>
        FOR t IN cAuthTicket( s.SCHEMA, '2' )
            LOOP
            iWsTickets := t.TICKETS;
            EXIT count_ws_tickets;
        END LOOP count_ws_tickets;
        <<count_nomad_tickets>>
        FOR t IN cAuthTicket( s.SCHEMA, '3' )
            LOOP
            iNomadTickets := t.TICKETS;
            EXIT count_nomad_tickets;
        END LOOP count_nomad_tickets;
        INSERT INTO COSWIN_TYPES.CONNECTION_STATISTICS
               ( CWOS_SCHEMA,
               CWOS_STANDARD_CONNECTIONS,
               CWOS_LIGHT_CONNECTIONS,
               CWOS_WS_CONNECTIONS,
               CWOS_NOMAD_CONNECTIONS
                   )
             VALUES
               ( s.SCHEMA,
               iStandardTickets,
               iLightTickets,
               iWsTickets,
               iNomadTickets
        );
        -- COMMIT;
    END LOOP loop_schemas;
END; -- cw8iCollectStats
$function$
;

I know that today was release day, but code I had running this morning doesn't seem to be working now. So perhaps a smell?

Anyway, ripping straight from the README, the following code is no longer working for me:

 SELECT p.oid, p.proname, plpgsql_check_function(p.oid)
   FROM pg_catalog.pg_namespace n
   JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
   JOIN pg_catalog.pg_language l ON p.prolang = l.oid
  WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279;

Error message is:

SQL Error [XX000]: ERROR: unexpected number of parameters, you should to update extension
  Where: PL/pgSQL function plpgsql_check_function(regprocedure,regclass,text,boolean,boolean,boolean,boolean) line 3 at RETURN QUERY

I am on PG 11.2 I think, OS is Ubuntu 18

I am brand new to this project (literally installed for the first time this morning), so I don't think I can be of any real help in making a patch. But do let me know if you need any further output to help aid in debugging.

Having a table that allows to implement a tree of categories

CREATE TABLE t_category ( mdct_code character varying(20) COLLATE pg_catalog."default" NOT NULL, mdct_parent_category character varying(20) COLLATE pg_catalog."default", mdct_system_category character varying(20) COLLATE pg_catalog."default" NOT NULL, mdct_level integer NOT NULL DEFAULT 1, CONSTRAINT un_mdct_code UNIQUE (mdct_code), CONSTRAINT fk_mdct_parent_category FOREIGN KEY (mdct_parent_category) REFERENCES t_category (mdct_code) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_mdct_system_category FOREIGN KEY (mdct_system_category) REFERENCES t_category (mdct_code) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT ck_mdct_level CHECK (mdct_level >= 0), CONSTRAINT ck_mdct_parent_category_level CHECK (mdct_parent_category IS NULL AND mdct_level = 1 OR mdct_parent_category IS NOT NULL AND mdct_level > 1), CONSTRAINT ck_mdct_parent_category_code CHECK (mdct_code::text <> mdct_parent_category::text), CONSTRAINT ck_mdct_system_category_code CHECK (mdct_level = 1 OR mdct_code::text <> mdct_system_category::text) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default;
and a function to get the children of a given category
CREATE OR REPLACE FUNCTION pack_category.children( startwith character varying, withlevel smallint DEFAULT 0) RETURNS SETOF record LANGUAGE 'plpgsql' AS $BODY$ BEGIN return query WITH RECURSIVE category_children_tree AS ( SELECT mdct_code, mdct_parent_category, CASE withlevel WHEN 0 THEN null WHEN 1 THEN 1 WHEN 2 THEN mdct_level ELSE null END as level, mdct_system_category FROM t_category WHERE mdct_code = startwith UNION SELECT c.mdct_code, c.mdct_parent_category, CASE withlevel WHEN 0 THEN null WHEN 1 THEN t.level + 1 WHEN 2 THEN t.level + 1 ELSE null END, c.mdct_system_category FROM t_category c INNER JOIN category_children_tree t ON t.mdct_code = c.mdct_parent_category ) SELECT * FROM category_children_tree; END; $BODY$;

I get the following false positive

functionid | children(character varying,smallint)
lineno | 9
statement | RETURN QUERY
sqlstate | 42804
message | structure of query does not match function result type
detail | Returned type character varying(20) does not match expected type record in column 1.
hint | [null]
level | error
position | [null]
query | [null]
context | [null]

Hi!

I have function

create or replace function import$test() returns void as $body$
begin

    CREATE TEMPORARY TABLE tmp(
      list varchar(50)
    ) ON COMMIT DROP;

    insert into tmp(list) select 'ABC' ;
   
   --use data in tmp

end;
$body$
LANGUAGE PLPGSQL;

I plpgsql_check prints error:
import$test: error:42P01:8:SQL statement:relation "tmp" does not exist

How i can override this behavior ?

This parameter is documented as other_warnings, but is declared as others_warnings in the function definition (which looks like a typo)

test=# create or replace function demo_pb (a int) returns table (foo int, bar int) language plpgsql
as
$$
begin
return query select a,1;
end;
$$
;
CREATE FUNCTION
test=# select * from plpgsql_check_function_tb('public.demo_pb(int)'::regprocedure);
functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context
------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+---------
(0 rows)
test=# create or replace function demo_pb (a int) returns table (foo int, bar int) language plpgsql
as
$$
begin
return query execute 'select ' || a || ',1';
end;
$$
;
CREATE FUNCTION
test=# select * from plpgsql_check_function_tb('public.demo_pb(int)'::regprocedure);
functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context
------------+--------+-----------+----------+-------------------------------+--------+------+---------------+----------+-------+---------
demo_pb | Ø | Ø | 00000 | unmodified OUT variable "foo" | Ø | Ø | warning extra | Ø | Ø | Ø
demo_pb | Ø | Ø | 00000 | unmodified OUT variable "bar" | Ø | Ø | warning extra | Ø | Ø | Ø
(2 rows)

Hi when running the checker against our application i get quite of few of those erros above and am not sure what it means.

1 Example:
error:XX000:263:SQL statement:query returns no result

260 BEGIN
261 v_ResultStr:='ReserveInventory';
262 -- Set reserved quantity to 0
263 UPDATE C_ORDERLINE
264 SET QtyReserved = 0,
265 Updated=TO_DATE(NOW()),
266 UpdatedBy=v_User
267 WHERE c_orderline_id IN (select c_orderline_id
268 from c_orderline
269 where c_order_id = v_Record_id);
270 END;
271 UPDATE C_ORDER
272 SET DocStatus='CJ',
273 DocAction='--',
274 Processed='Y',
275 Updated=TO_DATE(NOW()),
276 UpdatedBy=v_User
277 WHERE C_Order_ID=v_Record_ID;

Why does the error trigger for the update statement in line 263 but not for the one in 271 ?

Only difference i see at the moment in the in-clause subselect in the first one (which should be simplified by removing it).

Any help is appreciated understanding this error message.

I am seeing this warning:
unused variable "cur_orderline" in one of our pl functions in which we have the following:

DECLARE Cur_OrderLine CURSOR (Order_ID VARCHAR)  FOR
  SELECT * .... ;

ptr_ol RECORD; --OBTG:Cur_OrderLine--

then later:

OPEN Cur_OrderLine(Cur_Order.C_Order_ID) ;
Cur_OrderLine_ISOPEN:=true;
FETCH Cur_OrderLine INTO ptr_ol;

Reporting here as a false positive as not clear to me if that is one of the known limits or a real issue.

Full function can be seen here:
https://gist.github.com/shuehner/46c1b8aaadbd687cdcd8

Interesting parts are:
https://gist.github.com/shuehner/46c1b8aaadbd687cdcd8#file-c_invoice_create-sql-L102
https://gist.github.com/shuehner/46c1b8aaadbd687cdcd8#file-c_invoice_create-sql-L387

I found problem with foreach array iteration:
create or replace function foreach_array_loop()
returns void as
$body$
declare
arr text[];
el text;
begin
arr := array['1111','2222','3333'];
foreach el in array arr loop
null;
end loop;
end;
$body$
language 'plpgsql' stable;

select * from plpgsql_check_function_tb('foreach_array_loop()');

 functionid     | lineno |     statement      | sqlstate |                        message                        | detail | hint | level | position | query | context 

--------------------+--------+--------------------+----------+-------------------------------------------------------+--------+------+-------+----------+-------+---------
foreach_array_loop | 7 | FOREACH over array | XX000 | cannot to identify real type for record type variable | | | error | 0 | |
(1 row)

There is simple workaround:
select fn.* from plpgsql_check_function_tb('foreach_array_loop()') fn where fn.sqlstate != 'XX000';
But I'm not sure if there will be omitted other errors.

Regards
Adam

Hello,
to scan all pl functions ignoring triggers in public schema in active mode i use the following query.

Maybe it could be interesting for others as an example in the readme.

SELECT p.oid, p.proname, plpgsql_check_function(p.oid)
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
JOIN pg_catalog.pg_language l on p.prolang = l.oid
WHERE nspname = 'public' and l.lanname = 'plpgsql' and p.prorettype <> 2279;

Note: the prorettype not 2279 filter is ignore trigger functions, see issue#10.
Not sure if that oid is stable globally though.

From an attempt to build on Mac OS X 10.9.1 “Mavericks”:

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv  -I/usr/local/pgsql/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -I. -I. -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal -D_XOPEN_SOURCE -I/usr/local/include/libxml2  -I/usr/local/include  -c -o plpgsql_check.o plpgsql_check.c
plpgsql_check.c:2573:9: warning: unused variable 'use_sql_lineno' [-Wunused-variable]
        bool                    use_sql_lineno = false;
                                ^
1 warning generated.
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv  -I/usr/local/pgsql/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -bundle -multiply_defined suppress -o plpgsql_check.so plpgsql_check.o -L/usr/local/pgsql/lib -L/usr/local/lib  -L/usr/local/lib -Wl,-dead_strip_dylibs   -bundle_loader /usr/local/pgsql/bin/postgres
Undefined symbols for architecture x86_64:
  "_exec_get_datum_type", referenced from:
      _check_target in plpgsql_check.o
  "_plpgsql_build_datatype", referenced from:
      _check_stmt in plpgsql_check.o
  "_plpgsql_compile", referenced from:
      _check_plpgsql_function in plpgsql_check.o
  "_plpgsql_parser_setup", referenced from:
      _prepare_expr in plpgsql_check.o
  "_plpgsql_stmt_typename", referenced from:
      _put_error in plpgsql_check.o
ld: symbol(s) not found for architecture x86_64
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make: *** [plpgsql_check.so] Error 1

Hello,
just an idea for a new performance related check.
Not sure if that is possible at all in full or in part here or easy.

Case:
trigger defined on a table as:
aprm_fin_finacc_tran_check_trg BEFORE INSERT OR DELETE OR UPDATE ON fin_finacc_transaction FOR EACH ROW EXECUTE PROCEDURE aprm_fin_finacc_tran_check_trg()

and then inside the trigger pl function:
IF (TG_OP = 'UPDATE') THEN
do something
END IF;
IF(TG_OP = 'DELETE') THEN
do something else
END IF;

So essentially for case 'INSERT' no reachable code.

So marking it as 'on insert' also is not useful as it contains no reachable code for this.

Is that something which could be added as special cases for validating trigger functions?

In a lot code reviews I often catch wrong variable type declarations, which leads to severe performance degradation.
Some synthetic example:

DECLARE
  vID NUMERIC(19,0) := 0;

<snip>

SELECT * FROM some_large_table WHERE id=vID;

In some_large_table the id is declared as BIGINT, but vID is NUMERIC(19,0), thus PostgreSQL does implicit cast and slows down in time.
So can we detect such situations?

Hi,

I see that you released 1.0.5 yesterday, but that was released on July. That will cause issues with existing packages (as I already pushed 1.0.5 to repos last month or so). Can you please fix this? I think it would be 1.0.6.

Regards, Devrim

Hi!
plpgsql_check_function does not work correctly with polymorphic type "anyarray".

tmpdb=# create or replace function distinct_array(arr anyarray) returns anyarray as $$
begin
  return array(select distinct e from unnest(arr) as e);
end;
$$ language plpgsql immutable;
CREATE FUNCTION

tmpdb=# -- function works correctly:
tmpdb=# select distinct_array(array[1,1,2]);
 distinct_array
----------------
 {1,2}
(1 row)

tmpdb=# -- but plpgsql_check_function finds error:
tmpdb=# select plpgsql_check_function('distinct_array(anyarray)');
                                     plpgsql_check_function
------------------------------------------------------------------------------------------------
 error:0A000:could not determine actual argument type for polymorphic function "distinct_array"
(1 row)

I've found a 100% reproducible bug that seems to be an "off-by-one" error where the code seems to be checking the type of the column after the actual column referenced and generating a false warning.

Here's a reproducible test case using PostgreSQL 9.3.5:

CREATE EXTENSION plpgsql_check;

CREATE TABLE test (
a text,
b integer,
c uuid
);

CREATE FUNCTION before_insert_test() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
SELECT a INTO NEW.a FROM test WHERE b = 1;
RETURN NEW;
END
$$;

CREATE TRIGGER test_trigger AFTER INSERT ON test
FOR EACH ROW EXECUTE PROCEDURE before_insert_test();

SELECT plpgsql_check_function(p.oid, t.tgrelid)
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_trigger t ON t.tgfoid = p.oid
WHERE p.proname = 'before_insert_test';

<Function oid="17489">
<Issue>
<Level>warning</level>
<Sqlstate>42804</Sqlstate>
<Message>target variable has different type than expression result</Message>
<Stmt lineno="3">SQL statement</Stmt>
<Hint>The input expression type does not have an assignment cast to the target type.</Hint>
<Detail>assign "text" value to "integer" variable</Detail>
</Issue>
</Function>

This is clearly not true, and I have numerous similar examples in my real code that all report the variable type as the type of the column immediately after the actual column referenced. All these seem to be the target expression of SELECT INTO queries, and only on trigger functions.

Spending a few minutes working backwards from where the detail text comes from, I suspect the 'dno' index is maybe one-based but being used in a zero-base C array?

Do you think this tools could be enhance for style linting as well? e.g:

1. Keywords are written uppercase, names are written in lowercase.
2. 4 space indention.
3. One command per line.
4. Keywords LOOP, ELSE, ELSIF, END IF, WHEN on a new line.
5. Commas in front/end of separated elements.
6. Call parameters aligned, operators aligned, values aligned.
7. SQL keywords are either indent, or right aligned within a SQL command.
8. Within a function only single line comments -- are used.
9. Brackets are used when needed or when helpful to clarify a construct.
10. common variable naming conventions

Hi,

I just discover this extension, and I want to install it on demand.
So I followed the procedure, and I want to automate it
When I do the make && make install, all ok
When I load extension into a database and create it, all ok
But when I try to shared_preload_libraries it...
PG-XX000 FATAL: could not load library "/tech/postgres/9.6.3/server/lib/plpgsql_check.so": /tech/postgres/9.6.3/server/lib/plpgsql_check.so: undefined symbol: plpgsql_parser_setup

This extension cannot be preloaded?

Hi,

I tried to compile plpqsql_check plugin in postgres docker container but showing below error, I tried on versions 11.8 and 12.3.

root@af98e78a8c04:/plpgsql_check# make install
gcc -Wextra -g -fPIC -I/usr/lib/postgresql/11/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -Wall -I. -I./ -I/usr/include/postgresql/11/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/mit-krb5 -c -o src/plpgsql_check.o src/plpgsql_check.c
In file included from src/plpgsql_check.c:26:0:
src/plpgsql_check.h:1:22: fatal error: postgres.h: No such file or directory
#include "postgres.h"
^
compilation terminated.
: recipe for target 'src/plpgsql_check.o' failed
make: *** [src/plpgsql_check.o] Error 1

Please, anyone, help me to integrate plpgsql_check extension on Postgres docker container.

Hello,
while working on packaging your extension for Debian/Ubuntu i noticed some inconsistency for the written copyright/license.

In concrete:
LICENSE file contained in the repo is kind of short
README.md file (License section) is more extensive with copyright.

I assume the README.md section would be the more appropriate text when to talk about license/copyright of your extension?

For the moment i used this:
https://salsa.debian.org/postgresql/plpgsql-check/blob/master/debian/copyright

Could you please check is that is appropriate and maybe update the LICENSE file (if needed)?

Thanks,
Stefan

Is there any reason to show a "routine is marked as VOLATILE, should be STABLE." when the function RETURNS void?