proposal: plpgsql pragma statement

classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|

proposal: plpgsql pragma statement

Pavel Stehule
Hi


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.


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
Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Pavel Stehule
Hi

st 5. 12. 2018 v 12:42 odesílatel Alexey Bashtanov <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

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
Reply | Threaded
Open this post in threaded view
|

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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Pavel Stehule
In reply to this post by bashtanov


st 5. 12. 2018 v 14:42 odesílatel Alexey Bashtanov <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Pavel Stehule
In reply to this post by Pavel Luzanov


st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Pavel Stehule
In reply to this post by Pavel Luzanov


st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Jonah H. Harris-2
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 <[hidden email]> wrote:


st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Pavel Stehule
Hi

st 5. 12. 2018 v 18:28 odesílatel Jonah H. Harris <[hidden email]> 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 <[hidden email]> wrote:


st 5. 12. 2018 v 15:03 odesílatel Pavel Luzanov <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Dmitry Igrishin
In reply to this post by Pavel Stehule
вт, 4 дек. 2018 г. в 20:13, Pavel Stehule <[hidden email]>:

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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Pavel Stehule


st 5. 12. 2018 v 19:12 odesílatel Dmitry Igrishin <[hidden email]> napsal:
вт, 4 дек. 2018 г. в 20:13, Pavel Stehule <[hidden email]>:
>
> 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 ...
Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Robert Haas
In reply to this post by Pavel Stehule
On Tue, Dec 4, 2018 at 12:13 PM Pavel Stehule <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Jonah H. Harris-2
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 <[hidden email]> wrote:
On Tue, Dec 4, 2018 at 12:13 PM Pavel Stehule <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Pavel Stehule
In reply to this post by Robert Haas


čt 6. 12. 2018 v 17:27 odesílatel Robert Haas <[hidden email]> napsal:
On Tue, Dec 4, 2018 at 12:13 PM Pavel Stehule <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Robert Haas
In reply to this post by Jonah H. Harris-2
On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Jonah H. Harris-2
Ahh. Gotcha. Makes sense.

On Thu, Dec 6, 2018 at 11:57 AM Robert Haas <[hidden email]> wrote:
On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Pavel Stehule
In reply to this post by Robert Haas


čt 6. 12. 2018 v 17:57 odesílatel Robert Haas <[hidden email]> napsal:
On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Pavel Stehule


čt 6. 12. 2018 v 18:05 odesílatel Pavel Stehule <[hidden email]> napsal:


čt 6. 12. 2018 v 17:57 odesílatel Robert Haas <[hidden email]> napsal:
On Thu, Dec 6, 2018 at 11:39 AM Jonah H. Harris <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: proposal: plpgsql pragma statement

Robert Haas
On Thu, Dec 6, 2018 at 12:13 PM Pavel Stehule <[hidden email]> 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

12