search

okbob / plpgsql_check

plpgsql_check is a linter tool (does source code static analyze) for the PostgreSQL language plpgsql (the native language for PostgreSQL store procedures).
https://groups.google.com/forum/#!forum/postgresql-extensions-hacking
Other
641 stars 53 forks source link

false error with rule on table #173

Closed klementszabolcs closed 4 months ago

klementszabolcs commented 5 months ago

when check a function with query a table with insert/update rule, false error detected: "error:XX000:qq:SQL statement:there is not single query" qq-rownumber

okbob commented 5 months ago

Please, can me send an example

Dne čt 6. 6. 2024 10:12 uživatel klementszabolcs @.***> napsal:

when check a function with query a table with insert/update rule, false error detected: "error:XX000:qq:SQL statement:there is not single query" qq-rownumber

— Reply to this email directly, view it on GitHub https://github.com/okbob/plpgsql_check/issues/173, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO42QHT5ZVC5T4KILQEDZGAKVVAVCNFSM6AAAAABI4GPZVGVHI2DSMVQWIX3LMV43ASLTON2WKOZSGMZTONRSGEZTCNQ . You are receiving this because you are subscribed to this thread.Message ID: @.***>

klementszabolcs commented 5 months ago

CREATE TABLE IF NOT EXISTS xakta ( aktaid integer NOT NULL, CONSTRAINT pk_xakta PRIMARY KEY (aktaid) ); CREATE or replace RULE xrule_akta2 AS ON INSERT TO xakta DO select 1;

CREATE OR REPLACE FUNCTION public.fff(p_aa integer) RETURNS integer LANGUAGE plpgsql AS $body$ Begin insert into xakta values( 1 ); return 0; End; $body$ VOLATILE COST 100 SECURITY DEFINER;

select exts.plpgsql_check_function('fff');

error:XX000:3:SQL statement:there is not single query Detail: plpgsql_check cannot detect result type Hint: Probably there are some unsupported (by plpgsql_check) rules on related tables

okbob commented 5 months ago

It is hard to say what can be correct solution. Rules are bad, because you can modify INSERT into SELECT or do some crazy things, and it is hard to say, what plpgsql_check should to do. PLpgSQL ignore this and just takes an result of last rule, but sometimes this can be unwanted. Generally using "rules" is bad practice, and I don't know, what usage is correct and what not, what is expected and what not, and then I cannot to write support for rules to plpgsql_check. I afraid so I cannot to fix this issue - not technically, but I cannot to separate correct usage of rules from wrong usage of rules. The result is correct for plpgsql_check, because it is fatal error on input, and plpgsql_check cannot to correctly handle it. You can use pragmas to partially disable plpgsql_check.

Generally if it is possible, don't use rules - it is very old feature - designed ten years before plpgsql, and together it can do some crazy things. In this case I am internally against to follow plpgsql, that is not sensitive to this behave, because it is invisible for plpgsql.