Re: proposal: plpgsql pragma statement

[Pavel Stehule]

Hi

út 9. 4. 2019 v 15:03 odesílatel David Steele  napsal:

> Hi Pavel,
>
> On 3/25/19 3:50 AM, Pavel Stehule wrote:
> > po 25. 3. 2019 v 8:38 odesílatel David Steele  > > napsal:
> >
> > On 3/10/19 8:39 PM, Pavel Stehule wrote:
> > > Here is pragma patch with demo
> > We're still not getting real review for this patch and Andrew seems
> as
> > skeptical as anyone that this is the right way to go.
> >
> > I'm planning to mark it as rejected at the end of this CF.
> >
> >
> > I hope so Andrew will have some time to review this patch
>
> As discussed above, I have marked this patch as rejected.  It appears
> that a more general purpose approach is required to get a consensus on
> this feature.
>

I have to accepted this decision. I cannot to write this patch more general
- it's absolute generic (or I didn't understand objections).

I agree so now, there are higher priorities, and there are not customers
for this patch. But from syntax, semantic perspective, this patch is
correct. Now, it is interesting for plpgsql_check users, and their voice
was not here, unfortunately. I cannot do more in this area.

Regards

Pavel



>
> Regards,
> --
> -David
> da...@pgmasters.net
>

Re: proposal: plpgsql pragma statement

[David Steele]

Hi Pavel,

On 3/25/19 3:50 AM, Pavel Stehule wrote:
> po 25. 3. 2019 v 8:38 odesílatel David Steele  > napsal:
> 
> On 3/10/19 8:39 PM, Pavel Stehule wrote:
> >     Here is pragma patch with demo
> We're still not getting real review for this patch and Andrew seems as
> skeptical as anyone that this is the right way to go.
> 
> I'm planning to mark it as rejected at the end of this CF.
> 
> 
> I hope so Andrew will have some time to review this patch

As discussed above, I have marked this patch as rejected.  It appears
that a more general purpose approach is required to get a consensus on
this feature.

Regards,
-- 
-David
da...@pgmasters.net

Re: Re: proposal: plpgsql pragma statement

[Pavel Stehule]

po 25. 3. 2019 v 8:38 odesílatel David Steele  napsal:

> Hi Pavel,
>
> On 3/10/19 8:39 PM, Pavel Stehule wrote:
> > Here is pragma patch with demo
> We're still not getting real review for this patch and Andrew seems as
> skeptical as anyone that this is the right way to go.
>
> I'm planning to mark it as rejected at the end of this CF.
>

I hope so Andrew will have some time to review this patch

Regards

Pavel


> Regards,
> --
> -David
> da...@pgmasters.net
>

Re: Re: proposal: plpgsql pragma statement

[David Steele]

Hi Pavel,

On 3/10/19 8:39 PM, Pavel Stehule wrote:

Here is pragma patch with demo
We're still not getting real review for this patch and Andrew seems as 
skeptical as anyone that this is the right way to go.


I'm planning to mark it as rejected at the end of this CF.

Regards,
--
-David
da...@pgmasters.net

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

so 9. 3. 2019 v 22:17 odesílatel Pavel Stehule 
napsal:

>
>
> čt 7. 3. 2019 v 18:45 odesílatel Andrew Dunstan <
> andrew.duns...@2ndquadrant.com> napsal:
>
>>
>> On 3/7/19 12:41 PM, Pavel Stehule wrote:
>> >
>> >
>> > čt 7. 3. 2019 v 18:35 odesílatel Andrew Dunstan
>> > > > > napsal:
>> >
>> >
>> >
>> >
>> > The other thing that bugs me a bit about the patch is that the only
>> > testing it does it to make sure that pragmas are ignored by the core
>> > plpgsql processor. Maybe that's enough, but mostly we tend to like
>> to
>> > have one actual use of a feature.
>> >
>> >
>> > Unfortunately plpgsql_check is not part of upstream
>> >
>> > https://github.com/okbob/plpgsql_check
>> >
>> > I can to write some simple extension - some print tracing, that can
>> > use this feature?
>> >
>> >
>>
>> Works for me. Another idea I had was some sort of crypto signature pragma.
>>
>
> Here is pragma patch with demo
>

fixed check-world

Regards

Pavel


> Regards
>
> Pavel
>
>>
>>
>> I still think making it block level only is unwarranted, though.
>>
>>
>> cheers
>>
>>
>> andrew
>>
>>
>> --
>> Andrew Dunstanhttps://www.2ndQuadrant.com
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>>
diff --git a/contrib/Makefile b/contrib/Makefile
index 92184ed487..a5edd12e35 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -39,6 +39,7 @@ SUBDIRS = \
 		pgrowlocks	\
 		pgstattuple	\
 		pg_visibility	\
+		plpgsql_tracer	\
 		postgres_fdw	\
 		seg		\
 		spi		\
diff --git a/contrib/plpgsql_tracer/.gitignore b/contrib/plpgsql_tracer/.gitignore
new file mode 100644
index 00..5dcb3ff972
--- /dev/null
+++ b/contrib/plpgsql_tracer/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/plpgsql_tracer/Makefile b/contrib/plpgsql_tracer/Makefile
new file mode 100644
index 00..aedbd4f20f
--- /dev/null
+++ b/contrib/plpgsql_tracer/Makefile
@@ -0,0 +1,25 @@
+# contrib/plpgsql_tracer/Makefile
+
+MODULES = plpgsql_tracer
+
+EXTENSION = plpgsql_tracer
+PGFILEDESC = "plpgsql tracer - example of PRAGMA based extension"
+
+REGRESS = plpgsql_tracer
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/plpgsql_tracer
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
+ifeq ($(PORTNAME), darwin)
+override CFLAGS += -undefined dynamic_lookup
+endif
+
+override CFLAGS += -I$(top_builddir)/src/pl/plpgsql/src -Wall
diff --git a/contrib/plpgsql_tracer/expected/plpgsql_tracer.out b/contrib/plpgsql_tracer/expected/plpgsql_tracer.out
new file mode 100644
index 00..d183fcebef
--- /dev/null
+++ b/contrib/plpgsql_tracer/expected/plpgsql_tracer.out
@@ -0,0 +1,38 @@
+--
+--  Test plpgsql_tracer extension
+--
+load 'plpgsql';
+load 'plpgsql_tracer';
+create or replace function fx()
+returns integer
+language plpgsql
+AS $function$
+declare
+  x int = 0;
+  pragma plpgsql_tracer(on);
+  pragma ignore_me;
+begin
+  x := x + 1;
+  x := x + 1;
+  declare
+pragma plpgsql_tracer(off);
+  begin
+-- hidden statemt
+declare pragma plpgsql_tracer; -- ignored, just warning
+begin
+  x := x + 1;
+end;
+  end;
+  return x;
+end;
+$function$;
+select fx();
+WARNING:  missing argument of PRAGMA plpgsql_tracer
+NOTICE:  plpgsql_tracer:7: assignment
+NOTICE:  plpgsql_tracer:8: assignment
+NOTICE:  plpgsql_tracer:   18: RETURN
+ fx 
+
+  3
+(1 row)
+
diff --git a/contrib/plpgsql_tracer/plpgsql_tracer.c b/contrib/plpgsql_tracer/plpgsql_tracer.c
new file mode 100644
index 00..03d4ed8a62
--- /dev/null
+++ b/contrib/plpgsql_tracer/plpgsql_tracer.c
@@ -0,0 +1,248 @@
+/*
+ * contrib/plpgsql_tracer/plpgsql_tracer.c
+ */
+#include "postgres.h"
+#include "plpgsql.h"
+#include "fmgr.h"
+
+#include "nodes/bitmapset.h"
+
+PG_MODULE_MAGIC;
+
+
+#define PLPGSQL_TRACER_MAGIC		73071522
+
+typedef struct plpgsql_tracer_data
+{
+	intmagic;
+	Bitmapset	   *stmtids;
+} plpgsql_tracer_data;
+
+
+static void collect_stmtid(PLpgSQL_stmt *stmt, plpgsql_tracer_data *data, bool trace);
+
+static void plpgsql_tracer_func_init(PLpgSQL_execstate *estate, PLpgSQL_function *func);
+static void plpgsql_tracer_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt);
+
+static PLpgSQL_plugin plugin_funcs = { plpgsql_tracer_func_init,
+	   NULL,
+	   NULL,
+	   plpgsql_tracer_stmt_beg,
+	   NULL,
+	   NULL,
+	   NULL};
+
+/*
+ * Collect traced statement id from list of statements.
+ */
+static void
+collect_stmtid_list(List *stmts,
+	plpgsql_tracer_data *data,
+	bool trace)
+{
+	ListCell	   *lc;
+
+	foreach(lc, stmts)
+	{
+		collect_stmtid((PLpgSQL_stmt *) lfirst(lc), data, trace);
+	}
+}
+
+
+/*
+ * It is iterate over all plpgsql statements and collect stmtid of statements
+ * inside blocks marked by 

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

čt 7. 3. 2019 v 18:45 odesílatel Andrew Dunstan <
andrew.duns...@2ndquadrant.com> napsal:

>
> On 3/7/19 12:41 PM, Pavel Stehule wrote:
> >
> >
> > čt 7. 3. 2019 v 18:35 odesílatel Andrew Dunstan
> >  > > napsal:
> >
> >
> >
> >
> > The other thing that bugs me a bit about the patch is that the only
> > testing it does it to make sure that pragmas are ignored by the core
> > plpgsql processor. Maybe that's enough, but mostly we tend to like to
> > have one actual use of a feature.
> >
> >
> > Unfortunately plpgsql_check is not part of upstream
> >
> > https://github.com/okbob/plpgsql_check
> >
> > I can to write some simple extension - some print tracing, that can
> > use this feature?
> >
> >
>
> Works for me. Another idea I had was some sort of crypto signature pragma.
>

Here is pragma patch with demo

Regards

Pavel

>
>
> I still think making it block level only is unwarranted, though.
>
>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstanhttps://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
diff --git a/contrib/Makefile b/contrib/Makefile
index 92184ed487..a5edd12e35 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -39,6 +39,7 @@ SUBDIRS = \
 		pgrowlocks	\
 		pgstattuple	\
 		pg_visibility	\
+		plpgsql_tracer	\
 		postgres_fdw	\
 		seg		\
 		spi		\
diff --git a/contrib/plpgsql_tracer/.gitignore b/contrib/plpgsql_tracer/.gitignore
new file mode 100644
index 00..5dcb3ff972
--- /dev/null
+++ b/contrib/plpgsql_tracer/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/plpgsql_tracer/Makefile b/contrib/plpgsql_tracer/Makefile
new file mode 100644
index 00..7900471ed6
--- /dev/null
+++ b/contrib/plpgsql_tracer/Makefile
@@ -0,0 +1,25 @@
+# contrib/plpgsql_tracer/Makefile
+
+MODULES = plpgsql_tracer
+
+EXTENSION = plpgsql_tracer
+PGFILEDESC = "plpgsql tracer - example of PRAGMA based extension"
+
+REGRESS = plpgsql_tracer
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/citext
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
+ifeq ($(PORTNAME), darwin)
+override CFLAGS += -undefined dynamic_lookup
+endif
+
+override CFLAGS += -I$(top_builddir)/src/pl/plpgsql/src -Wall
diff --git a/contrib/plpgsql_tracer/expected/plpgsql_tracer.out b/contrib/plpgsql_tracer/expected/plpgsql_tracer.out
new file mode 100644
index 00..d183fcebef
--- /dev/null
+++ b/contrib/plpgsql_tracer/expected/plpgsql_tracer.out
@@ -0,0 +1,38 @@
+--
+--  Test plpgsql_tracer extension
+--
+load 'plpgsql';
+load 'plpgsql_tracer';
+create or replace function fx()
+returns integer
+language plpgsql
+AS $function$
+declare
+  x int = 0;
+  pragma plpgsql_tracer(on);
+  pragma ignore_me;
+begin
+  x := x + 1;
+  x := x + 1;
+  declare
+pragma plpgsql_tracer(off);
+  begin
+-- hidden statemt
+declare pragma plpgsql_tracer; -- ignored, just warning
+begin
+  x := x + 1;
+end;
+  end;
+  return x;
+end;
+$function$;
+select fx();
+WARNING:  missing argument of PRAGMA plpgsql_tracer
+NOTICE:  plpgsql_tracer:7: assignment
+NOTICE:  plpgsql_tracer:8: assignment
+NOTICE:  plpgsql_tracer:   18: RETURN
+ fx 
+
+  3
+(1 row)
+
diff --git a/contrib/plpgsql_tracer/plpgsql_tracer.c b/contrib/plpgsql_tracer/plpgsql_tracer.c
new file mode 100644
index 00..03d4ed8a62
--- /dev/null
+++ b/contrib/plpgsql_tracer/plpgsql_tracer.c
@@ -0,0 +1,248 @@
+/*
+ * contrib/plpgsql_tracer/plpgsql_tracer.c
+ */
+#include "postgres.h"
+#include "plpgsql.h"
+#include "fmgr.h"
+
+#include "nodes/bitmapset.h"
+
+PG_MODULE_MAGIC;
+
+
+#define PLPGSQL_TRACER_MAGIC		73071522
+
+typedef struct plpgsql_tracer_data
+{
+	intmagic;
+	Bitmapset	   *stmtids;
+} plpgsql_tracer_data;
+
+
+static void collect_stmtid(PLpgSQL_stmt *stmt, plpgsql_tracer_data *data, bool trace);
+
+static void plpgsql_tracer_func_init(PLpgSQL_execstate *estate, PLpgSQL_function *func);
+static void plpgsql_tracer_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt);
+
+static PLpgSQL_plugin plugin_funcs = { plpgsql_tracer_func_init,
+	   NULL,
+	   NULL,
+	   plpgsql_tracer_stmt_beg,
+	   NULL,
+	   NULL,
+	   NULL};
+
+/*
+ * Collect traced statement id from list of statements.
+ */
+static void
+collect_stmtid_list(List *stmts,
+	plpgsql_tracer_data *data,
+	bool trace)
+{
+	ListCell	   *lc;
+
+	foreach(lc, stmts)
+	{
+		collect_stmtid((PLpgSQL_stmt *) lfirst(lc), data, trace);
+	}
+}
+
+
+/*
+ * It is iterate over all plpgsql statements and collect stmtid of statements
+ * inside blocks marked by PRAGMA trace.
+ */
+static void
+collect_stmtid(PLpgSQL_stmt *stmt,
+			   plpgsql_tracer_data *data,
+			   bool trace)
+{
+	switch (stmt->cmd_type)
+	{
+		case 

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

čt 7. 3. 2019 v 18:45 odesílatel Andrew Dunstan <
andrew.duns...@2ndquadrant.com> napsal:

>
> On 3/7/19 12:41 PM, Pavel Stehule wrote:
> >
> >
> > čt 7. 3. 2019 v 18:35 odesílatel Andrew Dunstan
> >  > > napsal:
> >
> >
> >
> >
> > The other thing that bugs me a bit about the patch is that the only
> > testing it does it to make sure that pragmas are ignored by the core
> > plpgsql processor. Maybe that's enough, but mostly we tend to like to
> > have one actual use of a feature.
> >
> >
> > Unfortunately plpgsql_check is not part of upstream
> >
> > https://github.com/okbob/plpgsql_check
> >
> > I can to write some simple extension - some print tracing, that can
> > use this feature?
> >
> >
>
> Works for me. Another idea I had was some sort of crypto signature pragma.
>
>
> I still think making it block level only is unwarranted, though.
>

for me it is better than nothing, and it is good - it is not bad
compromise. It can be enhanced in future, when there will be good case. I
didn't propose any syntax, feature, that block future full implementation
compatible with Ada and PL/SQL.

Pavel



>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstanhttps://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>

Re: proposal: plpgsql pragma statement

[Andrew Dunstan]

On 3/7/19 12:41 PM, Pavel Stehule wrote:
>
>
> čt 7. 3. 2019 v 18:35 odesílatel Andrew Dunstan
>  > napsal:
>
>
>
>
> The other thing that bugs me a bit about the patch is that the only
> testing it does it to make sure that pragmas are ignored by the core
> plpgsql processor. Maybe that's enough, but mostly we tend to like to
> have one actual use of a feature.
>
>
> Unfortunately plpgsql_check is not part of upstream
>
> https://github.com/okbob/plpgsql_check
>
> I can to write some simple extension - some print tracing, that can
> use this feature?
>
>

Works for me. Another idea I had was some sort of crypto signature pragma.


I still think making it block level only is unwarranted, though.


cheers


andrew


-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

čt 7. 3. 2019 v 18:35 odesílatel Andrew Dunstan <
andrew.duns...@2ndquadrant.com> napsal:

>
> On 3/7/19 11:45 AM, Pavel Stehule wrote:
> >
> >
> >
> >
> > I have looked at the latest patch, but it seems inadequate unless I'm
> > misreading it. I think a general pragma mechanism should allow a
> > pragma
> > just about anywhere. If the processor doesn't recognize the pragma it
> > ignores it, if it does it processes it in the appropriate context.
> > That
> > could be for the next statement, the current block, or whatever.
> > If it
> > appears in the wrong context the processor should object.
> >
> >
> > For example, one of Ada's predefined pragmas is PAGE, which causes a
> > processor producing a program listing to emit a page feed. (Yes, I
> > know,
> > probably silly, but you get the idea why this pragma can accur just
> > about anywhere.) There is also 'pragma Assert' which is more or less
> > like our Assert in C.
> >
> >
> > a PRAGMA can be used on three levels (related to Postgres): function,
> > block, line (and on package on Ada and PLpgSQL). Any PRAGMA has a
> > scope related to usage.
> >
> > My first patch implemented all levels, but If I understand it well,
> > Tom has strong disagreement with line's level.
> >
> > So I reduced it just for block level - it is good enough for
> > plpgsql_check, and it is good enough for future autonomous
> > transactions. Other levels can be very simply supported, but I don't
> > need it, and in this moment, I had not a case for it.
> >
> > For plpgsql_check I need to read some non code informations like:
> > disable, enable some checks, assign some type to record variable,
> > assign some informations about dynamic SQL, ... I think so PRAGMA is
> > very natural tool for this purpose.
> >
> > My implementation of PRAGMA in this moment just push some tag and some
> > information to AST. It ensure correct syntax. It doesn't do more. But
> > it is good enough for plpgsql_check.
> >
> >
>
> That seems pretty brain-dead, TBH.
>
> The other thing that bugs me a bit about the patch is that the only
> testing it does it to make sure that pragmas are ignored by the core
> plpgsql processor. Maybe that's enough, but mostly we tend to like to
> have one actual use of a feature.
>

Unfortunately plpgsql_check is not part of upstream

https://github.com/okbob/plpgsql_check

I can to write some simple extension - some print tracing, that can use
this feature?

Pavel



>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstanhttps://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>

Re: proposal: plpgsql pragma statement

[Andrew Dunstan]

On 3/7/19 11:45 AM, Pavel Stehule wrote:
>
>
>
>
> I have looked at the latest patch, but it seems inadequate unless I'm
> misreading it. I think a general pragma mechanism should allow a
> pragma
> just about anywhere. If the processor doesn't recognize the pragma it
> ignores it, if it does it processes it in the appropriate context.
> That
> could be for the next statement, the current block, or whatever. 
> If it
> appears in the wrong context the processor should object.
>
>
> For example, one of Ada's predefined pragmas is PAGE, which causes a
> processor producing a program listing to emit a page feed. (Yes, I
> know,
> probably silly, but you get the idea why this pragma can accur just
> about anywhere.) There is also 'pragma Assert' which is more or less
> like our Assert in C.
>
>
> a PRAGMA can be used on three levels (related to Postgres): function,
> block, line (and on package on Ada and PLpgSQL). Any PRAGMA has a
> scope related to usage.
>
> My first patch implemented all levels, but If I understand it well,
> Tom has strong disagreement with line's level.
>
> So I reduced it just for block level - it is good enough for
> plpgsql_check, and it is good enough for future autonomous
> transactions. Other levels can be very simply supported, but I don't
> need it, and in this moment, I had not a case for it.
>
> For plpgsql_check I need to read some non code informations like:
> disable, enable some checks, assign some type to record variable,
> assign some informations about dynamic SQL, ... I think so PRAGMA is
> very natural tool for this purpose.
>
> My implementation of PRAGMA in this moment just push some tag and some
> information to AST. It ensure correct syntax. It doesn't do more. But
> it is good enough for plpgsql_check.
>
>

That seems pretty brain-dead, TBH.

The other thing that bugs me a bit about the patch is that the only
testing it does it to make sure that pragmas are ignored by the core
plpgsql processor. Maybe that's enough, but mostly we tend to like to
have one actual use of a feature.


cheers


andrew


-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

čt 7. 3. 2019 v 14:40 odesílatel Andrew Dunstan <
andrew.duns...@2ndquadrant.com> napsal:

>
> On 3/7/19 3:19 AM, Pavel Stehule wrote:
> > Hi
> >
> > čt 7. 3. 2019 v 8:03 odesílatel David Steele  > > napsal:
> >
> > On 2/4/19 8:12 PM, Pavel Stehule wrote:
> > >
> > >   attached rebased patch
> >
> > This patch has gone through a few iterations but I don't think
> > there's
> > any agreement on what it should look like.  There's been no code
> > review
> > that I can see.
> >
> > I think this should be pushed to PG13 at the least, perhaps returned
> > with comment or rejected.
> >
> >
> > This patch is in this moment in this form interesting just for
> > plpgsql_check's users.
> >
> > I cannot to move forward without this functionality .. I have a
> > promise of review by some people from big company that uses
> > plpgsql_check, but if there is not interest from commiter's side, then
> > it is not time for pushing this patch today.
> >
> > On second hand, the proposed syntax is same like for autonomous
> > transactions, so I believe, so this patch will come in few years.
> >
> > Please, wait few week, and then it can be rejected.
> >
> >
>
>
> I have looked at the latest patch, but it seems inadequate unless I'm
> misreading it. I think a general pragma mechanism should allow a pragma
> just about anywhere. If the processor doesn't recognize the pragma it
> ignores it, if it does it processes it in the appropriate context. That
> could be for the next statement, the current block, or whatever.  If it
> appears in the wrong context the processor should object.
>
>
> For example, one of Ada's predefined pragmas is PAGE, which causes a
> processor producing a program listing to emit a page feed. (Yes, I know,
> probably silly, but you get the idea why this pragma can accur just
> about anywhere.) There is also 'pragma Assert' which is more or less
> like our Assert in C.
>
>
a PRAGMA can be used on three levels (related to Postgres): function,
block, line (and on package on Ada and PLpgSQL). Any PRAGMA has a scope
related to usage.

My first patch implemented all levels, but If I understand it well, Tom has
strong disagreement with line's level.

So I reduced it just for block level - it is good enough for plpgsql_check,
and it is good enough for future autonomous transactions. Other levels can
be very simply supported, but I don't need it, and in this moment, I had
not a case for it.

For plpgsql_check I need to read some non code informations like: disable,
enable some checks, assign some type to record variable, assign some
informations about dynamic SQL, ... I think so PRAGMA is very natural tool
for this purpose.

My implementation of PRAGMA in this moment just push some tag and some
information to AST. It ensure correct syntax. It doesn't do more. But it is
good enough for plpgsql_check.

Regards

Pavel






>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstanhttps://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>

Re: proposal: plpgsql pragma statement

[Andrew Dunstan]

On 3/7/19 3:19 AM, Pavel Stehule wrote:
> Hi
>
> čt 7. 3. 2019 v 8:03 odesílatel David Steele  > napsal:
>
> On 2/4/19 8:12 PM, Pavel Stehule wrote:
> >
> >   attached rebased patch
>
> This patch has gone through a few iterations but I don't think
> there's
> any agreement on what it should look like.  There's been no code
> review
> that I can see.
>
> I think this should be pushed to PG13 at the least, perhaps returned
> with comment or rejected.
>
>
> This patch is in this moment in this form interesting just for
> plpgsql_check's users.
>
> I cannot to move forward without this functionality .. I have a
> promise of review by some people from big company that uses
> plpgsql_check, but if there is not interest from commiter's side, then
> it is not time for pushing this patch today.
>
> On second hand, the proposed syntax is same like for autonomous
> transactions, so I believe, so this patch will come in few years.
>
> Please, wait few week, and then it can be rejected.
>
>


I have looked at the latest patch, but it seems inadequate unless I'm
misreading it. I think a general pragma mechanism should allow a pragma
just about anywhere. If the processor doesn't recognize the pragma it
ignores it, if it does it processes it in the appropriate context. That
could be for the next statement, the current block, or whatever.  If it
appears in the wrong context the processor should object.


For example, one of Ada's predefined pragmas is PAGE, which causes a
processor producing a program listing to emit a page feed. (Yes, I know,
probably silly, but you get the idea why this pragma can accur just
about anywhere.) There is also 'pragma Assert' which is more or less
like our Assert in C.


cheers


andrew


-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Re: proposal: plpgsql pragma statement

[Pavel Stehule]

Hi

čt 7. 3. 2019 v 8:03 odesílatel David Steele  napsal:

> On 2/4/19 8:12 PM, Pavel Stehule wrote:
> >
> >   attached rebased patch
>
> This patch has gone through a few iterations but I don't think there's
> any agreement on what it should look like.  There's been no code review
> that I can see.
>
> I think this should be pushed to PG13 at the least, perhaps returned
> with comment or rejected.
>

This patch is in this moment in this form interesting just for
plpgsql_check's users.

I cannot to move forward without this functionality .. I have a promise of
review by some people from big company that uses plpgsql_check, but if
there is not interest from commiter's side, then it is not time for pushing
this patch today.

On second hand, the proposed syntax is same like for autonomous
transactions, so I believe, so this patch will come in few years.

Please, wait few week, and then it can be rejected.

Regards

Pavel




>
> Regards,
> --
> -David
> da...@pgmasters.net
>

Re: Re: proposal: plpgsql pragma statement

[David Steele]

On 2/4/19 8:12 PM, Pavel Stehule wrote:


  attached rebased patch


This patch has gone through a few iterations but I don't think there's 
any agreement on what it should look like.  There's been no code review 
that I can see.


I think this should be pushed to PG13 at the least, perhaps returned 
with comment or rejected.


Regards,
--
-David
da...@pgmasters.net

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

Hi,

po 4. 2. 2019 v 6:10 odesílatel Michael Paquier 
napsal:

> On Fri, Jan 04, 2019 at 02:17:49PM +0100, Pavel Stehule wrote:
> > It means to write own lexer and preparse source code before I start
> > checking.
> >
> > I think so block level PRAGMA is significantly better solution
>
> Please note that the latest patch is failing to apply, so I have moved
> the patch to next CF, waiting on author.
>

 attached rebased patch

thank you for checking.

Regards

Pavel

--
> Michael
>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index f8c6435c50..54bfb3f137 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -814,6 +814,49 @@ $$ LANGUAGE plpgsql;
 happen in a plain SQL command.

   
+
+  
+   Block level PRAGMA
+
+   
+A PL/pgSQL function supports pragma on block
+level. Pragma is a compiler directive, that can be used by
+PL/pgSQL compiler, or by any extensions that
+can work with PL/pgSQL code.
+   
+
+
+PRAGMA name;
+PRAGMA name (  argument_name =  value );
+
+
+   
+The pragma can be used for plpgsql_check
+enabling/disabling code checking or for storing additional information:
+
+
+DECLARE
+  PRAGMA plpgsql_check(off);
+BEGIN
+  -- code inside block will not be checked by plpgsql_check
+  ...
+
+
+DECLARE
+  -- force routine volatility detection
+  PRAGMA plpgsql_check(volatility => volatile);
+  PRAGMA plpgsql_check(temporary_table => 'tmp_tab', '(a int, b int, c int)');
+BEGIN
+  ...
+
+
+More details are described in related extension's description.
+   
+
+   
+Unknown pragma is ignored.
+   
+  
   
 
   
diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile
index cc1c2613d3..2aded819f9 100644
--- a/src/pl/plpgsql/src/Makefile
+++ b/src/pl/plpgsql/src/Makefile
@@ -27,7 +27,8 @@ DATA = plpgsql.control plpgsql--1.0.sql plpgsql--unpackaged--1.0.sql
 REGRESS_OPTS = --dbname=$(PL_TESTDB)
 
 REGRESS = plpgsql_call plpgsql_control plpgsql_domain plpgsql_record \
-	plpgsql_cache plpgsql_transaction plpgsql_trigger plpgsql_varprops
+	plpgsql_cache plpgsql_transaction plpgsql_trigger plpgsql_varprops \
+	plpgsql_pragma
 
 # where to find gen_keywordlist.pl and subsidiary files
 TOOLSDIR = $(top_srcdir)/src/tools
diff --git a/src/pl/plpgsql/src/expected/plpgsql_pragma.out b/src/pl/plpgsql/src/expected/plpgsql_pragma.out
new file mode 100644
index 00..ffe5c7664a
--- /dev/null
+++ b/src/pl/plpgsql/src/expected/plpgsql_pragma.out
@@ -0,0 +1,11 @@
+do $$
+DECLARE
+  var int;
+  PRAGMA xxx;
+  PRAGMA do;
+  PRAGMA var; -- name can be any identifier
+  PRAGMA xxx(10, 10.1, '', "a"., off, on); -- supported types
+  PRAGMA xxx(label => value);
+BEGIN
+END;
+$$;
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 03f7cdce8c..33e6929af9 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -111,6 +111,11 @@ static	PLpgSQL_expr	*read_cursor_args(PLpgSQL_var *cursor,
 static	List			*read_raise_options(void);
 static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 
+/*
+ * local variable for collection pragmas inside one declare block
+ */
+static List		   *pragmas;
+
 %}
 
 %expect 0
@@ -146,6 +151,7 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 			char *label;
 			int  n_initvars;
 			int  *initvarnos;
+			List *pragmas;
 		}		declhdr;
 		struct
 		{
@@ -166,6 +172,8 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 		PLpgSQL_diag_item		*diagitem;
 		PLpgSQL_stmt_fetch		*fetch;
 		PLpgSQL_case_when		*casewhen;
+		PLpgSQL_pragma			*pragma;
+		PLpgSQL_pragma_arg		*pragma_arg;
 }
 
 %type  decl_sect
@@ -221,6 +229,9 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 
 %type 	unreserved_keyword
 
+%type 	pragma_args
+%type  pragma_arg
+%type  pragma_val
 
 /*
  * Basic non-keyword token types.  These are hard-wired into the core lexer.
@@ -321,6 +332,7 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %token 	K_PG_EXCEPTION_CONTEXT
 %token 	K_PG_EXCEPTION_DETAIL
 %token 	K_PG_EXCEPTION_HINT
+%token 	K_PRAGMA
 %token 	K_PRINT_STRICT_PARAMS
 %token 	K_PRIOR
 %token 	K_QUERY
@@ -418,6 +430,7 @@ pl_block		: decl_sect K_BEGIN proc_sect exception_sect K_END opt_label
 		new->label		= $1.label;
 		new->n_initvars = $1.n_initvars;
 		new->initvarnos = $1.initvarnos;
+		new->pragmas	= $1.pragmas;
 		new->body		= $3;
 		new->exceptions	= $4;
 
@@ -436,6 +449,7 @@ decl_sect		: opt_block_label
 		$$.label	  = $1;
 		$$.n_initvars = 0;
 		$$.initvarnos = NULL;
+		$$.pragmas	  = NIL;
 	}
 | opt_block_label decl_start
 	{
@@ -443,6 +457,7 @@ decl_sect		: opt_block_label
 		$$.label	  = $1;
 		$$.n_initvars = 0;
 		$$.initvarnos = NULL;
+		$$.pragmas	  = NIL;
 	}
 | opt_block_label decl_start decl_stmts
 	{
@@ -450,6 +465,9 @@ decl_sect		: opt_block_label
 		$$.label	  = $1;
 		/* Remember 

Re: proposal: plpgsql pragma statement

[Michael Paquier]

On Fri, Jan 04, 2019 at 02:17:49PM +0100, Pavel Stehule wrote:
> It means to write own lexer and preparse source code before I start
> checking.
> 
> I think so block level PRAGMA is significantly better solution

Please note that the latest patch is failing to apply, so I have moved
the patch to next CF, waiting on author.
--
Michael


signature.asc
Description: PGP signature

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

pá 4. 1. 2019 v 14:07 odesílatel Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> napsal:

> On 06/12/2018 18:27, Pavel Stehule wrote:
> > For my purpose I can imagine PRAGMA on function level with same syntax
> > like PL/SQL - I need to push somewhere some information that I can use
> > for plpgsql_check to protect users against false alarms. The locality in
> > this moment is not too important for me. But I prefer solution that
> > doesn't looks too strange, and is possible just with change plpgsql
> parser.
>
> When you are about to warn about a particular statement, you have the
> statement's line number, so you can look up the source code and check
> for any disable-this-warning comments.
>

It means to write own lexer and preparse source code before I start
checking.

I think so block level PRAGMA is significantly better solution



> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: proposal: plpgsql pragma statement

[Peter Eisentraut]

On 06/12/2018 18:27, Pavel Stehule wrote:
> For my purpose I can imagine PRAGMA on function level with same syntax
> like PL/SQL - I need to push somewhere some information that I can use
> for plpgsql_check to protect users against false alarms. The locality in
> this moment is not too important for me. But I prefer solution that
> doesn't looks too strange, and is possible just with change plpgsql parser.

When you are about to warn about a particular statement, you have the
statement's line number, so you can look up the source code and check
for any disable-this-warning comments.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

Hi

st 12. 12. 2018 v 9:03 odesílatel Pavel Stehule 
napsal:

>
>
> čt 6. 12. 2018 v 18:27 odesílatel Pavel Stehule 
> napsal:
>
>>
>>
>> čt 6. 12. 2018 v 18:17 odesílatel Robert Haas 
>> napsal:
>>
>>> On Thu, Dec 6, 2018 at 12:13 PM Pavel Stehule 
>>> wrote:
>>> > My idea about plpgsql PRAGMA is very close to PL/SQL or Ada PRAGMA.
>>> This is not runtime statement - the information from this command will be
>>> assigned to related object - function, block, command at parser time.
>>>
>>> That's sensible, but the syntax you were proposing didn't look like it
>>> was related to a specific statement.  I was objecting to the idea that
>>> PRAGMA whatever; should be construed as an annotation of,
>>> specifically, the following statement.
>>>
>>
>> please, can you propose, some what you like?
>>
>> For my purpose I can imagine PRAGMA on function level with same syntax
>> like PL/SQL - I need to push somewhere some information that I can use for
>> plpgsql_check to protect users against false alarms. The locality in this
>> moment is not too important for me. But I prefer solution that doesn't
>> looks too strange, and is possible just with change plpgsql parser.
>>
>
> I looked to some documentation - and for example - the PL/SQL PRAGMA
> inline looks very similar to what I propose.
>
> For me good enough is block level pragma used in DECLARE section
>
> DECLARE
>   pragma plpgsql_check(OFF);
> BEGIN
>   .. this part will not be checked ..
> END;
>
> The syntax will be prepared for future PL/SQL pragmas like
> AUTONOMOUS_TRANSACTION, ..
>

here is block only level PRAGMA - available only from DECLARE part.

The informations are attached to PLpgSQL_stmt_block as List's field pragmas;

Note, if somebody will write support for autonomous transactions, then then
the PL/SQL syntax will be prepared. But my motivation is primary for some
possibility to push some parameters to plpgsql extensions with user
friendly persistent natural readable way.

Regards

Pavel


>
> Regards
>
> Pavel
>
>>
>> Regards
>>
>> Pavel
>>
>>
>>> --
>>> Robert Haas
>>> EnterpriseDB: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 1f2abbb5d1..fc95d3e950 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -814,6 +814,49 @@ $$ LANGUAGE plpgsql;
 happen in a plain SQL command.

   
+
+  
+   Block level PRAGMA
+
+   
+A PL/pgSQL function supports pragma on block
+level. Pragma is a compiler directive, that can be used by
+PL/pgSQL compiler, or by any extensions that
+can work with PL/pgSQL code.
+   
+
+
+PRAGMA name;
+PRAGMA name (  argument_name =  value );
+
+
+   
+The pragma can be used for plpgsql_check
+enabling/disabling code checking or for storing additional information:
+
+
+DECLARE
+  PRAGMA plpgsql_check(off);
+BEGIN
+  -- code inside block will not be checked by plpgsql_check
+  ...
+
+
+DECLARE
+  -- force routine volatility detection
+  PRAGMA plpgsql_check(volatility => volatile);
+  PRAGMA plpgsql_check(temporary_table => 'tmp_tab', '(a int, b int, c int)');
+BEGIN
+  ...
+
+
+More details are described in related extension's description.
+   
+
+   
+Unknown pragma is ignored.
+   
+  
   
 
   
diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile
index 25a5a9d448..0c97ddbb12 100644
--- a/src/pl/plpgsql/src/Makefile
+++ b/src/pl/plpgsql/src/Makefile
@@ -27,7 +27,7 @@ DATA = plpgsql.control plpgsql--1.0.sql plpgsql--unpackaged--1.0.sql
 REGRESS_OPTS = --dbname=$(PL_TESTDB)
 
 REGRESS = plpgsql_call plpgsql_control plpgsql_domain plpgsql_record \
-	plpgsql_cache plpgsql_transaction plpgsql_varprops
+	plpgsql_cache plpgsql_transaction plpgsql_varprops plpgsql_pragma
 
 all: all-lib
 
diff --git a/src/pl/plpgsql/src/expected/plpgsql_pragma.out b/src/pl/plpgsql/src/expected/plpgsql_pragma.out
new file mode 100644
index 00..ffe5c7664a
--- /dev/null
+++ b/src/pl/plpgsql/src/expected/plpgsql_pragma.out
@@ -0,0 +1,11 @@
+do $$
+DECLARE
+  var int;
+  PRAGMA xxx;
+  PRAGMA do;
+  PRAGMA var; -- name can be any identifier
+  PRAGMA xxx(10, 10.1, '', "a"., off, on); -- supported types
+  PRAGMA xxx(label => value);
+BEGIN
+END;
+$$;
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index a979a5109d..53e707bdd5 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -111,6 +111,11 @@ static	PLpgSQL_expr	*read_cursor_args(PLpgSQL_var *cursor,
 static	List			*read_raise_options(void);
 static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 
+/*
+ * local variable for collection pragmas inside one declare block
+ */
+static List		   *pragmas;
+
 %}
 
 %expect 0
@@ -146,6 +151,7 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 			char *label;
 			int  n_initvars;
 			int  *initvarnos;
+			List *pragmas;
 		}		declhdr;
 		struct
 		{
@@ -166,6 +172,8 @@ static	void			

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

čt 6. 12. 2018 v 18:27 odesílatel Pavel Stehule 
napsal:

>
>
> čt 6. 12. 2018 v 18:17 odesílatel Robert Haas 
> napsal:
>
>> On Thu, Dec 6, 2018 at 12:13 PM Pavel Stehule 
>> wrote:
>> > My idea about plpgsql PRAGMA is very close to PL/SQL or Ada PRAGMA.
>> This is not runtime statement - the information from this command will be
>> assigned to related object - function, block, command at parser time.
>>
>> That's sensible, but the syntax you were proposing didn't look like it
>> was related to a specific statement.  I was objecting to the idea that
>> PRAGMA whatever; should be construed as an annotation of,
>> specifically, the following statement.
>>
>
> please, can you propose, some what you like?
>
> For my purpose I can imagine PRAGMA on function level with same syntax
> like PL/SQL - I need to push somewhere some information that I can use for
> plpgsql_check to protect users against false alarms. The locality in this
> moment is not too important for me. But I prefer solution that doesn't
> looks too strange, and is possible just with change plpgsql parser.
>

I looked to some documentation - and for example - the PL/SQL PRAGMA inline
looks very similar to what I propose.

For me good enough is block level pragma used in DECLARE section

DECLARE
  pragma plpgsql_check(OFF);
BEGIN
  .. this part will not be checked ..
END;

The syntax will be prepared for future PL/SQL pragmas like
AUTONOMOUS_TRANSACTION, ..

Regards

Pavel

>
> Regards
>
> Pavel
>
>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

pá 7. 12. 2018 v 21:28 odesílatel Robert Haas 
napsal:

> On Thu, Dec 6, 2018 at 12:28 PM Pavel Stehule 
> wrote:
> > please, can you propose, some what you like?
> >
> > For my purpose I can imagine PRAGMA on function level with same syntax
> like PL/SQL - I need to push somewhere some information that I can use for
> plpgsql_check to protect users against false alarms. The locality in this
> moment is not too important for me. But I prefer solution that doesn't
> looks too strange, and is possible just with change plpgsql parser.
>
> Well, I haven't really studied this, but I would assume a
> statement-level pragma would look like an annotation of some kind on
> that particular statement, e.g.
>
> PRAGMA plpgsql_check (magic pavel stuff goes here) SELECT ...
>
> Rather than a separate statement:
>
> PRAGMA plpgsql_check (magic pavel stuff goes here);
> SELECT ...
>
> This might be the wrong idea, I'm not an expert on this or anything.
>

it looks strange - if we use a Ada like keyword, we should to use Ada like
syntax. and it can looks pretty strange if we will think about multiple
PRAGMAs.

For my purpose, the function level or block level pragma can be enough -
and there maybe will not be a problem, because you, me would to use PL/SQL
near syntax.

Regards

Pavel




>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: proposal: plpgsql pragma statement

[Robert Haas]

On Thu, Dec 6, 2018 at 12:28 PM Pavel Stehule  wrote:
> please, can you propose, some what you like?
>
> For my purpose I can imagine PRAGMA on function level with same syntax like 
> PL/SQL - I need to push somewhere some information that I can use for 
> plpgsql_check to protect users against false alarms. The locality in this 
> moment is not too important for me. But I prefer solution that doesn't looks 
> too strange, and is possible just with change plpgsql parser.

Well, I haven't really studied this, but I would assume a
statement-level pragma would look like an annotation of some kind on
that particular statement, e.g.

PRAGMA plpgsql_check (magic pavel stuff goes here) SELECT ...

Rather than a separate statement:

PRAGMA plpgsql_check (magic pavel stuff goes here);
SELECT ...

This might be the wrong idea, I'm not an expert on this or anything.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

čt 6. 12. 2018 v 18:17 odesílatel Robert Haas 
napsal:

> On Thu, Dec 6, 2018 at 12:13 PM Pavel Stehule 
> wrote:
> > My idea about plpgsql PRAGMA is very close to PL/SQL or Ada PRAGMA. This
> is not runtime statement - the information from this command will be
> assigned to related object - function, block, command at parser time.
>
> That's sensible, but the syntax you were proposing didn't look like it
> was related to a specific statement.  I was objecting to the idea that
> PRAGMA whatever; should be construed as an annotation of,
> specifically, the following statement.
>

please, can you propose, some what you like?

For my purpose I can imagine PRAGMA on function level with same syntax like
PL/SQL - I need to push somewhere some information that I can use for
plpgsql_check to protect users against false alarms. The locality in this
moment is not too important for me. But I prefer solution that doesn't
looks too strange, and is possible just with change plpgsql parser.

Regards

Pavel


> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: proposal: plpgsql pragma statement

[Robert Haas]

On Thu, Dec 6, 2018 at 12:13 PM Pavel Stehule  wrote:
> My idea about plpgsql PRAGMA is very close to PL/SQL or Ada PRAGMA. This is 
> not runtime statement - the information from this command will be assigned to 
> related object - function, block, command at parser time.

That's sensible, but the syntax you were proposing didn't look like it
was related to a specific statement.  I was objecting to the idea that
PRAGMA whatever; should be construed as an annotation of,
specifically, the following statement.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

čt 6. 12. 2018 v 18:05 odesílatel Pavel Stehule 
napsal:

>
>
> čt 6. 12. 2018 v 17:57 odesílatel Robert Haas 
> napsal:
>
>> On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris 
>> wrote:
>> > IIRC, PRAGMA in Ada was compile-time only. How would you foresee it
>> affecting runtime?
>>
>> Well, I don't know what Ada does with PRAGMA exactly, but look at
>> these examples from Oracle:
>>
>> http://psoug.org/definition/pragma.htm
>>
>> You wouldn't *execute* those at runtime, but at least for some of
>> them, the runtime behavior would depend on whether or not they were
>> specified.  It certainly seems possible that we might want to have
>> similar things.
>>
>
> My proposal doesn't block it.
>
> The pragma in Ada has three levels - function, block, statement. I propose
> (in this moment) just statement level syntax, but I am sure, so other
> levels are possible.
>

My idea about plpgsql PRAGMA is very close to PL/SQL or Ada PRAGMA. This is
not runtime statement - the information from this command will be assigned
to related object - function, block, command at parser time.


> I would to have a autonomous functions or autonomous blocks too, and Ada
> syntax (same with PL/SQL) is good.
>
> Regards
>
> Pavel
>
>
>
>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

čt 6. 12. 2018 v 17:57 odesílatel Robert Haas 
napsal:

> On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris 
> wrote:
> > IIRC, PRAGMA in Ada was compile-time only. How would you foresee it
> affecting runtime?
>
> Well, I don't know what Ada does with PRAGMA exactly, but look at
> these examples from Oracle:
>
> http://psoug.org/definition/pragma.htm
>
> You wouldn't *execute* those at runtime, but at least for some of
> them, the runtime behavior would depend on whether or not they were
> specified.  It certainly seems possible that we might want to have
> similar things.
>

My proposal doesn't block it.

The pragma in Ada has three levels - function, block, statement. I propose
(in this moment) just statement level syntax, but I am sure, so other
levels are possible.

I would to have a autonomous functions or autonomous blocks too, and Ada
syntax (same with PL/SQL) is good.

Regards

Pavel




> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: proposal: plpgsql pragma statement

[Jonah H. Harris]

Ahh. Gotcha. Makes sense.

On Thu, Dec 6, 2018 at 11:57 AM Robert Haas  wrote:

> On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris 
> wrote:
> > IIRC, PRAGMA in Ada was compile-time only. How would you foresee it
> affecting runtime?
>
> Well, I don't know what Ada does with PRAGMA exactly, but look at
> these examples from Oracle:
>
> http://psoug.org/definition/pragma.htm
>
> You wouldn't *execute* those at runtime, but at least for some of
> them, the runtime behavior would depend on whether or not they were
> specified.  It certainly seems possible that we might want to have
> similar things.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
-- 
Jonah H. Harris

Re: proposal: plpgsql pragma statement

[Robert Haas]

On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris  wrote:
> IIRC, PRAGMA in Ada was compile-time only. How would you foresee it affecting 
> runtime?

Well, I don't know what Ada does with PRAGMA exactly, but look at
these examples from Oracle:

http://psoug.org/definition/pragma.htm

You wouldn't *execute* those at runtime, but at least for some of
them, the runtime behavior would depend on whether or not they were
specified.  It certainly seems possible that we might want to have
similar things.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

čt 6. 12. 2018 v 17:27 odesílatel Robert Haas 
napsal:

> On Tue, Dec 4, 2018 at 12:13 PM Pavel Stehule 
> wrote:
> > I wrote plpgsql_check https://github.com/okbob/plpgsql_check.
> >
> > It is working well, but because it does static analyse only, sometimes
> it can produces false alarms or it should to stop a analyse, because there
> are not necessary data.
> >
> > https://github.com/okbob/plpgsql_check/issues/36
> >
> > I see one possible solution in introduction of pragma statement with
> syntax:
> >
> >   PRAGMA keyword [content to semicolon];
> >
> > The pragma has a relation to following statement. So the issue 36 can be
> solved by pragma
> >
> > PRAGMA cmdtype CREATE;
> > EXECUTE format('CREATE TABLE xxx ...
> >
> > The PRAGMA statement does nothing in runtime. It works only in compile
> time, and add a pair of key, value to next non pragma statement. This
> information can be used by some plpgsql extensions.
> >
> > What do you think about this proposal?
>
> I think it's commandeering PRAGMA for a fairly narrow purpose.  It's
> hardly unimaginable that someone in future might want a PRAGMA that
> does change runtime behavior, or that affects something other than the
> statement which immediately follows.
>
> I don't see a big problem allowing some kind of annotation that
> plpgsql_check can easily access, and I don't even mind it being called
> PRAGMA.  But I don't think it should foreclose unrelated uses of
> PRAGMA which somebody might want to introduce in the future.
>

The most simple (and probably one possible) implementation is plpgsql
statement - because I would not to modify SQL lexer.

I am thinking so PRAGMA statement is natural due semantic and usage in Ada,
but I am opened any proposals.

Regards

Pavel


> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: proposal: plpgsql pragma statement

[Jonah H. Harris]

IIRC, PRAGMA in Ada was compile-time only. How would you foresee it
affecting runtime?

On Thu, Dec 6, 2018 at 11:27 AM Robert Haas  wrote:

> On Tue, Dec 4, 2018 at 12:13 PM Pavel Stehule 
> wrote:
> > I wrote plpgsql_check https://github.com/okbob/plpgsql_check.
> >
> > It is working well, but because it does static analyse only, sometimes
> it can produces false alarms or it should to stop a analyse, because there
> are not necessary data.
> >
> > https://github.com/okbob/plpgsql_check/issues/36
> >
> > I see one possible solution in introduction of pragma statement with
> syntax:
> >
> >   PRAGMA keyword [content to semicolon];
> >
> > The pragma has a relation to following statement. So the issue 36 can be
> solved by pragma
> >
> > PRAGMA cmdtype CREATE;
> > EXECUTE format('CREATE TABLE xxx ...
> >
> > The PRAGMA statement does nothing in runtime. It works only in compile
> time, and add a pair of key, value to next non pragma statement. This
> information can be used by some plpgsql extensions.
> >
> > What do you think about this proposal?
>
> I think it's commandeering PRAGMA for a fairly narrow purpose.  It's
> hardly unimaginable that someone in future might want a PRAGMA that
> does change runtime behavior, or that affects something other than the
> statement which immediately follows.
>
> I don't see a big problem allowing some kind of annotation that
> plpgsql_check can easily access, and I don't even mind it being called
> PRAGMA.  But I don't think it should foreclose unrelated uses of
> PRAGMA which somebody might want to introduce in the future.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
Jonah H. Harris

Re: proposal: plpgsql pragma statement

[Robert Haas]

On Tue, Dec 4, 2018 at 12:13 PM Pavel Stehule  wrote:
> I wrote plpgsql_check https://github.com/okbob/plpgsql_check.
>
> It is working well, but because it does static analyse only, sometimes it can 
> produces false alarms or it should to stop a analyse, because there are not 
> necessary data.
>
> https://github.com/okbob/plpgsql_check/issues/36
>
> I see one possible solution in introduction of pragma statement with syntax:
>
>   PRAGMA keyword [content to semicolon];
>
> The pragma has a relation to following statement. So the issue 36 can be 
> solved by pragma
>
> PRAGMA cmdtype CREATE;
> EXECUTE format('CREATE TABLE xxx ...
>
> The PRAGMA statement does nothing in runtime. It works only in compile time, 
> and add a pair of key, value to next non pragma statement. This information 
> can be used by some plpgsql extensions.
>
> What do you think about this proposal?

I think it's commandeering PRAGMA for a fairly narrow purpose.  It's
hardly unimaginable that someone in future might want a PRAGMA that
does change runtime behavior, or that affects something other than the
statement which immediately follows.

I don't see a big problem allowing some kind of annotation that
plpgsql_check can easily access, and I don't even mind it being called
PRAGMA.  But I don't think it should foreclose unrelated uses of
PRAGMA which somebody might want to introduce in the future.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

st 5. 12. 2018 v 19:12 odesílatel Dmitry Igrishin 
napsal:

> вт, 4 дек. 2018 г. в 20:13, Pavel Stehule :
> >
> > Hi
> >
> > I wrote plpgsql_check https://github.com/okbob/plpgsql_check.
> >
> > It is working well, but because it does static analyse only, sometimes
> it can produces false alarms or it should to stop a analyse, because there
> are not necessary data.
> >
> > https://github.com/okbob/plpgsql_check/issues/36
> >
> > I see one possible solution in introduction of pragma statement with
> syntax:
> >
> >   PRAGMA keyword [content to semicolon];
> >
> > The pragma has a relation to following statement. So the issue 36 can be
> solved by pragma
> >
> > PRAGMA cmdtype CREATE;
> > EXECUTE format('CREATE TABLE xxx ...
> >
> > The PRAGMA statement does nothing in runtime. It works only in compile
> time, and add a pair of key, value to next non pragma statement. This
> information can be used by some plpgsql extensions.
> >
> > What do you think about this proposal?
> I think it's a good idea in common. But how about multiple PRAGMAs?
> Consider
>
>   PRAGMA cmdtype CREATE;
>   PRAGMA objtype TABLE;
>   EXECUTE format('CREATE TABLE');
>

yes, it is possible. They are assigned to next non pragma statement.

some like

PRAGMA cmdtype SELECT
PRAGMA resulttype (a int, b int, c int)
EXECUTE format('SELECT ... FROM %I ...

PRAGMA tmp_table_query
PRAGMA resulttype (a int, b int, c int)
FOR r IN SELECT * FROM some_temporary_table ...

Re: proposal: plpgsql pragma statement

[Dmitry Igrishin]

вт, 4 дек. 2018 г. в 20:13, Pavel Stehule :
>
> Hi
>
> I wrote plpgsql_check https://github.com/okbob/plpgsql_check.
>
> It is working well, but because it does static analyse only, sometimes it can 
> produces false alarms or it should to stop a analyse, because there are not 
> necessary data.
>
> https://github.com/okbob/plpgsql_check/issues/36
>
> I see one possible solution in introduction of pragma statement with syntax:
>
>   PRAGMA keyword [content to semicolon];
>
> The pragma has a relation to following statement. So the issue 36 can be 
> solved by pragma
>
> PRAGMA cmdtype CREATE;
> EXECUTE format('CREATE TABLE xxx ...
>
> The PRAGMA statement does nothing in runtime. It works only in compile time, 
> and add a pair of key, value to next non pragma statement. This information 
> can be used by some plpgsql extensions.
>
> What do you think about this proposal?
I think it's a good idea in common. But how about multiple PRAGMAs? Consider

  PRAGMA cmdtype CREATE;
  PRAGMA objtype TABLE;
  EXECUTE format('CREATE TABLE');

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

Hi

st 5. 12. 2018 v 18:28 odesílatel Jonah H. Harris 
napsal:

> You can alter the lexer and create a comment node, right? That’s how we
> did hints in EnterpriseDB.
>

I don't think so it is adequate solution - sure, it is, if I would to
implement Oracle's hints. But it is not my target. I afraid about
performance impacts a) for SQL queries, b) for plpgsql.

I had a possibility to see a functions, procedures (ported from Oracle)
with thousands lines of comments. It is not a Oracle problem, due
compilation to persistent byte code. But I don't think so this cost is
accepted for pragma implementation.

The native plpgsql statement PRAGMA has zero performance impact on existing
code. More, the implementation is just local inside plpgsql, what is
important for me.

Regards

Pavel




>
> On Wed, Dec 5, 2018 at 11:41 AM Pavel Stehule 
> wrote:
>
>>
>>
>> st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov 
>> napsal:
>>
>>>
>>> But maybe your extension could read the PERFORM statement preceding it
>>> and treat it as an annotation hint for the following statement.
>>>
>>>
>>> In this case, comment line in some format will be better than real
>>> PERFORM statement. Like this:
>>>
>>> /*+PRAGMA cmdtype CREATE; */
>>> EXECUTE format('CREATE TABLE xxx ...
>>>
>>
>> I looked there and It is not possible to implement it - plpgsql uses SQL
>> lexer, and the content of comments are just ignored. So I cannot to read
>> comments. There is not any possibility to read it simply from plpgsql.
>> Unfortunately, but it is expected, there is nothing like query string for
>> plpgsql statement.
>>
>> Regards
>>
>> Pavel
>>
>>> -
>>> Pavel Luzanov
>>> Postgres Professional: http://www.postgrespro.com
>>> The Russian Postgres Company
>>>
>>>
>>> --
> Jonah H. Harris
>
>

Re: proposal: plpgsql pragma statement

[Jonah H. Harris]

You can alter the lexer and create a comment node, right? That’s how we did
hints in EnterpriseDB.

On Wed, Dec 5, 2018 at 11:41 AM Pavel Stehule 
wrote:

>
>
> st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov 
> napsal:
>
>>
>> But maybe your extension could read the PERFORM statement preceding it
>> and treat it as an annotation hint for the following statement.
>>
>>
>> In this case, comment line in some format will be better than real
>> PERFORM statement. Like this:
>>
>> /*+PRAGMA cmdtype CREATE; */
>> EXECUTE format('CREATE TABLE xxx ...
>>
>
> I looked there and It is not possible to implement it - plpgsql uses SQL
> lexer, and the content of comments are just ignored. So I cannot to read
> comments. There is not any possibility to read it simply from plpgsql.
> Unfortunately, but it is expected, there is nothing like query string for
> plpgsql statement.
>
> Regards
>
> Pavel
>
>> -
>> Pavel Luzanov
>> Postgres Professional: http://www.postgrespro.com
>> The Russian Postgres Company
>>
>>
>> --
Jonah H. Harris

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov 
napsal:

>
> But maybe your extension could read the PERFORM statement preceding it and
> treat it as an annotation hint for the following statement.
>
>
> In this case, comment line in some format will be better than real PERFORM
> statement. Like this:
>
> /*+PRAGMA cmdtype CREATE; */
> EXECUTE format('CREATE TABLE xxx ...
>

I looked there and It is not possible to implement it - plpgsql uses SQL
lexer, and the content of comments are just ignored. So I cannot to read
comments. There is not any possibility to read it simply from plpgsql.
Unfortunately, but it is expected, there is nothing like query string for
plpgsql statement.

Regards

Pavel

> -
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov 
napsal:

>
> But maybe your extension could read the PERFORM statement preceding it and
> treat it as an annotation hint for the following statement.
>
>
> In this case, comment line in some format will be better than real PERFORM
> statement. Like this:
>
> /*+PRAGMA cmdtype CREATE; */
> EXECUTE format('CREATE TABLE xxx ...
>

I though about it, but it is significantly harder for implementation. Now,
the content of comments are just ignored. If we push PRAGMA into comments,
then we should to do some work with comments.

PRAGMA as statement is much more easy for implementation. But Using PRAGMA
inside comments is very good alternative.

Regards

Pavel



> -
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

st 5. 12. 2018 v 14:42 odesílatel Alexey Bashtanov 
napsal:

>
>
>> You can use PERFORM as a workaround:
>>
>> PERFORM 'PRAGMA', 'cmdtype', 'CREATE';
>>
>> There's some overhead when executing, but probably not too much.
>>
>
> Thank you for tip, but I have not any idea, how it can work?
>
>
> Well, I thought you were for a comment-like thing that remains there when
> compiled and can act as a hint for your static analysis extension.
> Surely this PERFORM won't impose any hints on the following statement
> itself.
> But maybe your extension could read the PERFORM statement preceding it and
> treat it as an annotation hint for the following statement.
>

PERFORM is +/- SELECT, so if I use PERFORM, I have to modify this statement.



> Best,
>   Alex
>

Re: proposal: plpgsql pragma statement

[Pavel Luzanov]

But maybe your extension could read the PERFORM statement preceding it 
and treat it as an annotation hint for the following statement.


In this case, comment line in some format will be better than real 
PERFORM statement. Like this:


/*+PRAGMA cmdtype CREATE; */
EXECUTE format('CREATE TABLE xxx ...

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: proposal: plpgsql pragma statement

[Alexey Bashtanov]

You can use PERFORM as a workaround:

PERFORM 'PRAGMA', 'cmdtype', 'CREATE';

There's some overhead when executing, but probably not too much.


Thank you for tip, but I have not any idea, how it can work?



Well, I thought you were for a comment-like thing that remains there 
when compiled and can act as a hint for your static analysis extension.
Surely this PERFORM won't impose any hints on the following statement 
itself.
But maybe your extension could read the PERFORM statement preceding it 
and treat it as an annotation hint for the following statement.


Best,
  Alex

Re: proposal: plpgsql pragma statement

[Pavel Stehule]

Hi

st 5. 12. 2018 v 12:42 odesílatel Alexey Bashtanov 
napsal:

> Hello Pavel,
>
> >
> > The PRAGMA statement does nothing in runtime. It works only in compile
> > time, and add a pair of key, value to next non pragma statement. This
> > information can be used by some plpgsql extensions.
> >
> > What do you think about this proposal?
> >
>
> You can use PERFORM as a workaround:
>
> PERFORM 'PRAGMA', 'cmdtype', 'CREATE';
>
> There's some overhead when executing, but probably not too much.
>

Thank you for tip, but I have not any idea, how it can work?

Regards

Pavel


>
> Best regards,
>Alexey
>

Re: proposal: plpgsql pragma statement

[Alexey Bashtanov]

Hello Pavel,



The PRAGMA statement does nothing in runtime. It works only in compile 
time, and add a pair of key, value to next non pragma statement. This 
information can be used by some plpgsql extensions.


What do you think about this proposal?



You can use PERFORM as a workaround:

PERFORM 'PRAGMA', 'cmdtype', 'CREATE';

There's some overhead when executing, but probably not too much.

Best regards,
  Alexey

proposal: plpgsql pragma statement

[Pavel Stehule]

Hi

I wrote plpgsql_check https://github.com/okbob/plpgsql_check.

It is working well, but because it does static analyse only, sometimes it
can produces false alarms or it should to stop a analyse, because there are
not necessary data.

https://github.com/okbob/plpgsql_check/issues/36

I see one possible solution in introduction of pragma statement with syntax:

  PRAGMA keyword [content to semicolon];

The pragma has a relation to following statement. So the issue 36 can be
solved by pragma

PRAGMA cmdtype CREATE;
EXECUTE format('CREATE TABLE xxx ...

The PRAGMA statement does nothing in runtime. It works only in compile
time, and add a pair of key, value to next non pragma statement. This
information can be used by some plpgsql extensions.

What do you think about this proposal?

Regards

Pavel