Postgres Pro
Downloads
Home   >   mailing lists

Thread: proposal: plpgsql, new check for extra_errors - strict_expr_check

proposal: plpgsql, new check for extra_errors - strict_expr_check

From
Pavel Stehule
Date:
Hi,

assigned patch try to solve issue reported by Mor Lehr (Missing semicolon in anonymous plpgsql block does not raise syntax error).


by introducing a new extra error check. With this check only a_expr exprs are allowed as plpgsql expressions. This is a small step to behaviour described in SQL/PSM standard (although the language is different, the expression syntax and features are almost similar. With this check the undocumented (but supported syntax)

var := column FROM tab

is disallowed. Only ANSI syntax for embedded queries (inside assignment statement) is allowed

var := (SELECT column FROM tab);

With this check, the reported issue (by Mor Lehr) is detected

default setting

CREATE TABLE foo3(id serial PRIMARY key, txt text);
INSERT INTO foo3 (txt) VALUES ('aaa'),('bbb');

DO $$
DECLARE
    l_cnt int;
BEGIN
    l_cnt := 1
    DELETE FROM foo3 WHERE id=1;
END; $$

-- without reaction - just don't work

(2024-06-16 16:05:55) postgres=# set plpgsql.extra_errors to 'strict_expr_check';
SET
(2024-06-16 16:06:43) postgres=# DO $$                                          
DECLARE
    l_cnt int;
BEGIN
    l_cnt := 1
    DELETE FROM foo3 WHERE id=1;
END; $$;
ERROR:  syntax error at or near "DELETE"
LINE 11:     DELETE FROM foo3 WHERE id=1;
             ^

This patch has three parts

1. Introduction strict_expr_check
2. set strict_expr_check as default, and impact on regress tests
3. revert @2

I don't propose to be strict_expr_check active  by default.

Comments, notes?

Regards

Pavel


Attachment

Re: proposal: plpgsql, new check for extra_errors - strict_expr_check

From
Marcos Pegoraro
Date:
Can you remove or just ignore double ; too ?

postgres=# do $$ 
declare var_x integer; 
begin 
  var_x = 99;; 
  delete from x where x = var_x; 
end; $$;
ERROR:  syntax error at or near ";"
LINE 1: do $$ declare var_x integer; begin var_x = 99;; delete from ...

Atenciosamente, 




Em dom., 16 de jun. de 2024 às 11:12, Pavel Stehule <pavel.stehule@gmail.com> escreveu:
Hi,

assigned patch try to solve issue reported by Mor Lehr (Missing semicolon in anonymous plpgsql block does not raise syntax error).


by introducing a new extra error check. With this check only a_expr exprs are allowed as plpgsql expressions. This is a small step to behaviour described in SQL/PSM standard (although the language is different, the expression syntax and features are almost similar. With this check the undocumented (but supported syntax)

var := column FROM tab

is disallowed. Only ANSI syntax for embedded queries (inside assignment statement) is allowed

var := (SELECT column FROM tab);

With this check, the reported issue (by Mor Lehr) is detected

default setting

CREATE TABLE foo3(id serial PRIMARY key, txt text);
INSERT INTO foo3 (txt) VALUES ('aaa'),('bbb');

DO $$
DECLARE
    l_cnt int;
BEGIN
    l_cnt := 1
    DELETE FROM foo3 WHERE id=1;
END; $$

-- without reaction - just don't work

(2024-06-16 16:05:55) postgres=# set plpgsql.extra_errors to 'strict_expr_check';
SET
(2024-06-16 16:06:43) postgres=# DO $$                                          
DECLARE
    l_cnt int;
BEGIN
    l_cnt := 1
    DELETE FROM foo3 WHERE id=1;
END; $$;
ERROR:  syntax error at or near "DELETE"
LINE 11:     DELETE FROM foo3 WHERE id=1;
             ^

This patch has three parts

1. Introduction strict_expr_check
2. set strict_expr_check as default, and impact on regress tests
3. revert @2

I don't propose to be strict_expr_check active  by default.

Comments, notes?

Regards

Pavel


Re: proposal: plpgsql, new check for extra_errors - strict_expr_check

From
Pavel Stehule
Date:
Hi

ne 16. 6. 2024 v 16:22 odesílatel Marcos Pegoraro <marcos@f10.com.br> napsal:
Can you remove or just ignore double ; too ?

I don't know - it is a different issue.

PLpgSQL allows zero statements inside block, so you can write BEGIN END or IF 1 THEN END IF but it doesn't allow empty statement

like ;;

probably it just needs one more rule in gram.y - but in this case, I am not sure if we should support it. 

What is the expected benefit? Generally PL/pgSQL has very strict syntax - and using double semicolons makes no sense.


 

postgres=# do $$ 
declare var_x integer; 
begin 
  var_x = 99;; 
  delete from x where x = var_x; 
end; $$;
ERROR:  syntax error at or near ";"
LINE 1: do $$ declare var_x integer; begin var_x = 99;; delete from ...

Atenciosamente, 




Em dom., 16 de jun. de 2024 às 11:12, Pavel Stehule <pavel.stehule@gmail.com> escreveu:
Hi,

assigned patch try to solve issue reported by Mor Lehr (Missing semicolon in anonymous plpgsql block does not raise syntax error).


by introducing a new extra error check. With this check only a_expr exprs are allowed as plpgsql expressions. This is a small step to behaviour described in SQL/PSM standard (although the language is different, the expression syntax and features are almost similar. With this check the undocumented (but supported syntax)

var := column FROM tab

is disallowed. Only ANSI syntax for embedded queries (inside assignment statement) is allowed

var := (SELECT column FROM tab);

With this check, the reported issue (by Mor Lehr) is detected

default setting

CREATE TABLE foo3(id serial PRIMARY key, txt text);
INSERT INTO foo3 (txt) VALUES ('aaa'),('bbb');

DO $$
DECLARE
    l_cnt int;
BEGIN
    l_cnt := 1
    DELETE FROM foo3 WHERE id=1;
END; $$

-- without reaction - just don't work

(2024-06-16 16:05:55) postgres=# set plpgsql.extra_errors to 'strict_expr_check';
SET
(2024-06-16 16:06:43) postgres=# DO $$                                          
DECLARE
    l_cnt int;
BEGIN
    l_cnt := 1
    DELETE FROM foo3 WHERE id=1;
END; $$;
ERROR:  syntax error at or near "DELETE"
LINE 11:     DELETE FROM foo3 WHERE id=1;
             ^

This patch has three parts

1. Introduction strict_expr_check
2. set strict_expr_check as default, and impact on regress tests
3. revert @2

I don't propose to be strict_expr_check active  by default.

Comments, notes?

Regards

Pavel


Re: proposal: plpgsql, new check for extra_errors - strict_expr_check

From
Marcos Pegoraro
Date:
Em dom., 16 de jun. de 2024 às 11:37, Pavel Stehule <pavel.stehule@gmail.com> escreveu:

What is the expected benefit? Generally PL/pgSQL has very strict syntax - and using double semicolons makes no sense.

exactly, makes no sense. That is because it should be ignored, right ?
But ok, if this is a different issue, that´s fine.

regards
Marcos

Re: proposal: plpgsql, new check for extra_errors - strict_expr_check

From
Pavel Stehule
Date:


ne 16. 6. 2024 v 16:43 odesílatel Marcos Pegoraro <marcos@f10.com.br> napsal:
Em dom., 16 de jun. de 2024 às 11:37, Pavel Stehule <pavel.stehule@gmail.com> escreveu:

What is the expected benefit? Generally PL/pgSQL has very strict syntax - and using double semicolons makes no sense.

exactly, makes no sense. That is because it should be ignored, right ?
But ok, if this is a different issue, that´s fine.

I don't follow this idea - when it does not make sense, then why do you use it?  It can be a signal of some issue in your code.

The source code should not contain a code that should be ignored.

But I am not a authority - can be interesting if this is allowed in PL/SQL or Ada

Regards

Pavel






regards
Marcos

Re: proposal: plpgsql, new check for extra_errors - strict_expr_check

From
Marcos Pegoraro
Date:
Em dom., 16 de jun. de 2024 às 12:11, Pavel Stehule <pavel.stehule@gmail.com> escreveu:
I don't follow this idea - when it does not make sense, then why do you use it?  It can be a signal of some issue in your code.

I don't use it, but sometimes it occurs, and there are lots of languages which ignore it, so it would be cool if plpgsql does it too.
 
If you do this, works
set search_path to public;;;

but if you do the same inside a block, it does not.

regards
Marcos

Re: proposal: plpgsql, new check for extra_errors - strict_expr_check

From
Pavel Stehule
Date:


ne 16. 6. 2024 v 19:36 odesílatel Marcos Pegoraro <marcos@f10.com.br> napsal:
Em dom., 16 de jun. de 2024 às 12:11, Pavel Stehule <pavel.stehule@gmail.com> escreveu:
I don't follow this idea - when it does not make sense, then why do you use it?  It can be a signal of some issue in your code.

I don't use it, but sometimes it occurs, and there are lots of languages which ignore it, so it would be cool if plpgsql does it too.
 
If you do this, works
set search_path to public;;;

psql allows it, but it is a shell - not a programming language.
 

but if you do the same inside a block, it does not.

It is a different language. I have not too strong an opinion about it - it is hard to say what is the correct design when you should work with a mix of languages like SQL and Ada (PL/pgSQL), and when related standard SQL/PSM is not widely used. Personally, I don't see any nice features that allow it to accept dirty code. I have negative experiences when a language is tolerant.

Regards

Pavel


regards
Marcos