proposal: plpgsql pragma statement

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

Hi

st 5. 12. 2018 v 12:42 odesílatel Alexey Bashtanov <bashtanov@imap.cc>
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

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

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

st 5. 12. 2018 v 14:42 odesílatel Alexey Bashtanov <bashtanov@imap.cc>
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.

st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov <p.luzanov@postgrespro.ru>
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

st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov <p.luzanov@postgrespro.ru>
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

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 <pavel.stehule@gmail.com>
wrote:

st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov <p.luzanov@postgrespro.ru>
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

Hi

st 5. 12. 2018 v 18:28 odesílatel Jonah H. Harris <jonah.harris@gmail.com>
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

вт, 4 дек. 2018 г. в 20:13, Pavel Stehule <pavel.stehule@gmail.com>:

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');

st 5. 12. 2018 v 19:12 odesílatel Dmitry Igrishin <dmitigr@gmail.com>
napsal:

вт, 4 дек. 2018 г. в 20:13, Pavel Stehule <pavel.stehule@gmail.com>:

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 ...

On Tue, Dec 4, 2018 at 12:13 PM Pavel Stehule <pavel.stehule@gmail.com> 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

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 <robertmhaas@gmail.com> wrote:

On Tue, Dec 4, 2018 at 12:13 PM Pavel Stehule <pavel.stehule@gmail.com>
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

čt 6. 12. 2018 v 17:27 odesílatel Robert Haas <robertmhaas@gmail.com>
napsal:

On Tue, Dec 4, 2018 at 12:13 PM Pavel Stehule <pavel.stehule@gmail.com>
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

On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris <jonah.harris@gmail.com> 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

Ahh. Gotcha. Makes sense.

On Thu, Dec 6, 2018 at 11:57 AM Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris <jonah.harris@gmail.com>
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

čt 6. 12. 2018 v 17:57 odesílatel Robert Haas <robertmhaas@gmail.com>
napsal:

On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris <jonah.harris@gmail.com>
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

čt 6. 12. 2018 v 18:05 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

čt 6. 12. 2018 v 17:57 odesílatel Robert Haas <robertmhaas@gmail.com>
napsal:

On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris <jonah.harris@gmail.com>
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.

On Thu, Dec 6, 2018 at 12:13 PM Pavel Stehule <pavel.stehule@gmail.com> 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