Thread: Why can't I have a "language sql" anonymous block?

My question is this:

Was there a deliberate decision not to allow a “language sql” anonymous block? Or is it just that nobody thought that it would be useful?

create procedure p_sql()
  security definer
  language sql
as $body$
  drop table if exists t cascade;
  create table t(k serial primary key, v text not null);

Bryn Llewellyn <bryn@yugabyte.com> writes:
> Was there a deliberate decision not to allow a “language sql” anonymous block? Or is it just that nobody thought that
itwould be useful? 

I think nobody thought it'd be useful.  What's the difference from
just executing the contained SQL statements?

(If DO blocks had parameters, the conclusion might be different,
but they don't so far.)

            regards, tom lane

Well… that’s the answer: “nobody thought it’d be useful”. Thanks, Tom.

The difference between using a “language sql” anonymous block and just executing the contained SQL statements? is
partlya clear declaration of the intent of your code and a guarantee that all the statements are sent from client to
serverin one go. (But see what I say about “prepare” below.) 

Here’s how I’d reason the case.

There must be a reason to prefer a “language sql” procedure over a “language plpgsql” procedure—otherwise the former
wouldn’tbe supported. 

A “language sql” procedure has restricted functionality compared with a “language plpgsql” procedure. So I can only
guessthat it’s preferred when it lets you program what you need ‘cos simpler means quicker. 

In general, an anonymous block is preferred over a stored unit when you don’t want to clutter the schema with an object
that’sused only occasionally. (In some cases, you don’t even have the privileges to create a stored unit but can
executean anonymous block. So putting these two ideas together makes the case for a “language sql” anonymous block. 

Another reason to support “language sql” anonymous blocks is to improve symmetry—and therefore usability: one fewer
arbitraryrule to remember. 

B.t.w., you mentioned the restriction that DO blocks can’t have parameters. The reason for allowing them is what I just
referredto: don’t want to, or simply cannot, create a procedure or function. Oracle Database allows binding to
placeholdersin anonymous blocks (their vocabulary for “ parameters”)—and, as I recall, has since their very first
appearanceas a feature. 

Might your “they don't so far” give me hope that they presently will be? Presumably, the notion would have to include
theability to prepare-once and execute-many using an anonymous block. (This is another counter intuitive restriction
that,today, has to be learned.) 

tgl@sss.pgh.pa.us wrote:

Bryn Llewellyn <bryn@yugabyte.com> writes:
> Was there a deliberate decision not to allow a “language sql” anonymous block? Or is it just that nobody thought that
itwould be useful? 

I think nobody thought it'd be useful.  What's the difference from just executing the contained SQL statements?

(If DO blocks had parameters, the conclusion might be different, but they don't so far.)

            regards, tom lane

On 12/13/21 13:15, Bryn Llewellyn wrote:
> Well… that’s the answer: “nobody thought it’d be useful”. Thanks, Tom.
> 
> The difference between using a “language sql” anonymous block and just executing the contained SQL statements? is
partlya clear declaration of the intent of your code and a guarantee that all the statements are sent from client to
serverin one go. (But see what I say about “prepare” below.)
 

As in?:

begin;
drop table if exists t cascade;
create table t(k serial primary key, v text not null);
commit;

> 
> Here’s how I’d reason the case.
> 
> There must be a reason to prefer a “language sql” procedure over a “language plpgsql” procedure—otherwise the former
wouldn’tbe supported.
 

The 'Law of Minimums', use the minimum needed functionality to get the 
job done. Less chance of wandering into areas where there be dragons.

> 
> A “language sql” procedure has restricted functionality compared with a “language plpgsql” procedure. So I can only
guessthat it’s preferred when it lets you program what you need ‘cos simpler means quicker.
 

Also inlining:

https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions

https://stackoverflow.com/questions/53040170/postgresql-inline-function-behavior

> 
> In general, an anonymous block is preferred over a stored unit when you don’t want to clutter the schema with an
objectthat’s used only occasionally. (In some cases, you don’t even have the privileges to create a stored unit but can
executean anonymous block. So putting these two ideas together makes the case for a “language sql” anonymous block.
 
> 
> Another reason to support “language sql” anonymous blocks is to improve symmetry—and therefore usability: one fewer
arbitraryrule to remember.
 
> 
> B.t.w., you mentioned the restriction that DO blocks can’t have parameters. The reason for allowing them is what I
justreferred to: don’t want to, or simply cannot, create a procedure or function. Oracle Database allows binding to
placeholdersin anonymous blocks (their vocabulary for “ parameters”)—and, as I recall, has since their very first
appearanceas a feature.
 
> 
> Might your “they don't so far” give me hope that they presently will be? Presumably, the notion would have to include
theability to prepare-once and execute-many using an anonymous block. (This is another counter intuitive restriction
that,today, has to be learned.)
 
> 
> tgl@sss.pgh.pa.us wrote:
> 
> Bryn Llewellyn <bryn@yugabyte.com> writes:
>> Was there a deliberate decision not to allow a “language sql” anonymous block? Or is it just that nobody thought
thatit would be useful?
 
> 
> I think nobody thought it'd be useful.  What's the difference from just executing the contained SQL statements?
> 
> (If DO blocks had parameters, the conclusion might be different, but they don't so far.)
> 
>             regards, tom lane
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver wrote:

On 12/14/21 11:30, Bryn Llewellyn wrote:
> /Adrian Klaver wrote:/
> 
> /Bryn wrote:/

> 
> Thanks for the links to the articles on the inlining of “language sql” 
> functions into SQL statements that use them. (I noted “the exact 
> conditions which apply to inlining are somewhat complex and not well 
> documented outside the source code” in the PG Wiki.) This optimization 
> is interesting. But its discussion is orthogonal to the question that I 
> asked.

You asked:

"There must be a reason to prefer a “language sql” procedure over a 
“language plpgsql” procedure—otherwise the former wouldn’t be supported."

I provided two reasons, or did I misread that?


> It would seem, on its face, that the DO block is preferable because it 
> uses a single server call from the client rather than four. (Maybe it’s 
> two server calls if the implementation of autocommit is done client-side 
> by sending a follow-up “commit”.) I do know that at least some client 
> languages that have a PG driver allow many SQL statements to be sent in 
> a single call. I’ve heard that psql will do this if all the statements 
> are on one line. But I can’t find anything in the PG docs about this. Is 
> it true? And if so, where is it documented? However, this just feels far 
> less like a clear way to ask for what you want than a DO block. And it 
> would lead to unreadable code with only a small number of to-be-batched 
> SQL statements.

Seems to work for the tests:


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/timestamp.sql;h=e011e779ea2da20393f624505ad6dea7f9582438;hb=HEAD

> 
> All this aside, as long as DO blocks don’t allow their contained 
> statements to be parameterized, you anyway have to use a procedure to 
> get the functionality that you need. This makes my question largely 
> moot—as Tom implied. So I’ll simply hope that, one day, the PostgreSQL 
> guardians will concede that implementing this missing DO functionality 
> would be useful—just as the Oracle Database guardians decided three 
> decades ago—and bring that functionality in a future PG release.

My experience is when I get to the point of needing parameters I'm 
pretty much going to need the other plpgsql features. I could see having 
it, but I do not remember seeing any/many previous posts to this list 
requesting it. That pushes it down to the bottom of the must haves.



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Monday, December 13, 2021, Bryn Llewellyn <bryn@yugabyte.com> wrote:

There must be a reason to prefer a “language sql” procedure over a “language plpgsql” procedure—otherwise the former wouldn’t be supported.

david.g.johnston@gmail.com wrote:

On Monday, December 13, 2021, Bryn Llewellyn <bryn@yugabyte.com> wrote:

There must be a reason to prefer a “language sql” procedure over a “language plpgsql” procedure—otherwise the former wouldn’t be supported.

I would say that is true for functions.  I wouldn’t assume that for procedures—it’s probable that because sql already worked for functions we got that feature for free when implementing procedures.

mmoncure@gmail.com wrote:

Bryn wrote:

david.g.johnston@gmail.com wrote:

Bryn wrote:

There must be a reason to prefer a “language sql” procedure over a “language plpgsql” procedure—otherwise the former wouldn’t be supported.

I would say that is true for functions.  I wouldn’t assume that for procedures—it’s probable that because sql already worked for functions we got that feature for free when implementing procedures.

Interesting. That’s exactly the kind of historical insight I was after. Thanks.

SQL language functions have one clear advantage in that they can be inlined in narrow contexts; this can give dramatic performance advantages when it occurs. They have a lot of disadvantages:

(1) Tables can’t be created then used without turning off function body evaluation.

(2) Queries must be parsed and planned upon each evocation (this can be construed as advantage in scenarios where you want to float a function over schemas).

(3) Generally constrained to basic SQL statements (no variables, logic etc).

…simplifies down to, “use SQL functions [only] when inlining”.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
About your point #1…

I used a procedure to test this because functions shouldn’t do DDL. I started with a working “language plpgsql” example:

drop table if exists t cascade;
drop procedure if exists p() cascade;
create procedure p()
  language plpgsql
as $body$
begin
  drop table if exists t cascade;
  create table t(k int primary key, v text not null);
  insert into t(k, v) values (1, 'dog'), (2, 'cat'), (3, 'frog');
end;
$body$;

call p();
select k, v from t order by k;

This runs without error and produces the expected content in the newly-created table. This informs my understanding of the “compilation” that’s done at “create” time. It’s only a syntax check. If the check fails, then the “create” is turned into a no-op; else the source code is stored. Everything else happens at run time.

Then I changed it to a “language sql” test:

drop table if exists t cascade;
drop procedure if exists p() cascade;
create procedure p()
  language sql
as $body$
  drop table if exists t cascade;
  create table t(k int primary key, v text not null);

  -- Causes compilation error: 42P01: relation "t" does not exist
  -- insert into t(k, v) values (1, 'dog'), (2, 'cat'), (3, 'frog');
$body$;

With the “insert” in place, it fails the syntax check with the error that I mentioned. When it’s commented out, it passes the syntax check and executes without error and has the expected effect.

This implies a different “create” model for a “language sql” unit than for a “language plsqsql” unit. This difference is described under “sql_body” in the PG doc section for “CREATE FUNCTION”—except that “sql_body” denotes an “unquoted” body and my example uses a “quoted” body. I tried the “unquoted” syntax thus:

create procedure p()
begin atomic
  drop table if exists t cascade;
  create table t(k int primary key, v text not null);
end;

But this caused the error “DROP TABLE is not yet supported in unquoted SQL function body”

The upshot of this is that I can’t design a test to demonstrate the effect that I thought you meant. Could you show me a code example, please?

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
About your point #2…

I’m surprised by this.Consider this code example:

do $body$
begin
  -- All because there's no "deallocate if exists".
  deallocate v_from_t ;
exception
  when invalid_sql_statement_name then null;
end;
$body$;

drop table if exists t cascade;
drop function if exists f_plpgsql(int) cascade;
drop function if exists f_quoted_sql(int) cascade;
drop function if exists f_unquoted_sql(int) cascade;

create table t(k int primary key, v text not null);
insert into t(k, v) values (1, 'dog'), (2, 'cat'), (3, 'frog');

prepare v_from_t(int) as
select t.v from t where t.k = $1;

create function f_plpgsql(k in int)
  returns text
  language plpgsql
as $body$
begin
  return (select t.v from t where t.k = f_plpgsql.k);
end;
$body$;

create function f_quoted_sql(k in int)
  returns text
  language sql
as $body$
  select t.v from t where t.k = f_quoted_sql.k;
$body$;

create function f_unquoted_sql(k in int) returns text
return (select t.v from t where t.k = f_unquoted_sql.k);


\set k 2
execute v_from_t(:k);
select f_plpgsql(:k);
select f_quoted_sql(:k);
select f_unquoted_sql(:k);

The “execute” and each “select” all give the same result.

I’ve come to assume that, at runtime, “execute v_from_t(:k)” has the same performance as “select f_plpgsql(:k)”—discounting the very first execution of the latter which implies the work of “prepare” too.

The prepare paradigm has the huge disadvantage that it must be done afresh in each newly-started session. But you can’t implement this in a trigger ‘cos (unlike Oracle Database) a PG developer can’t write an event trigger that fires when a session starts. In contrast, the PL/pgSQL function paradigm requires a single “create function” at install time; and thereafter, the “prepare” is done implicitly on first use in a new session.

Is my analysis here sound?

Moving on to “select f_quoted_sql(:k)”, the surprise is that this does NOT imply a “prepare” and that, rather, the “select… from t…” is compiled and executed from scratch of every use. Is this what you meant? If so, where is this documented. Having said this, if the body of “f_sql()” is simply inlined into any SQL that uses it, and if that invoking SQL is then prepared (either explicitly or because it’s used in the body of a “language plpgsql” unit, then all this boils down to nothing of concern.

Is the story different for “f_unquoted_sql()”?

Please clarify your point.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 

I understand your point #3.

On 12/15/21 13:05, Bryn Llewellyn wrote:
>> mmoncure@gmail.com <mailto:mmoncure@gmail.com> wrote:
>>
>>> Bryn wrote:
>>>
>>>> david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> wrote:
>>>>
>>>>> Bryn wrote:
>>>>>
>>>>> There must be a reason to prefer a “language sql” procedure over a 
>>>>> “language plpgsql” procedure—otherwise the former wouldn’t be 
>>>>> supported.
>>>>
>>>> I would say that is true for functions.  I wouldn’t assume that for 
>>>> procedures—it’s probable that because sql already 
>>>> worked for functions we got that feature for free when implementing 
>>>> procedures.
>>>
>>> Interesting. That’s exactly the kind of historical insight I was 
>>> after. Thanks.
>>
>> SQL language functions have one clear advantage in that they can 
>> be inlined in narrow contexts; this can give 
>> dramatic performance advantages when it occurs. They have a lot of 
>> disadvantages:
>>
>> (1) Tables can’t be created then used without turning off function 
>> body evaluation.
>>
>> (2) Queries must be parsed and planned upon each evocation (this can 
>> be construed as advantage in scenarios where you want to float a 
>> function over schemas).
>>
>> (3) Generally constrained to basic SQL statements (no variables, logic 
>> etc).
>>
>> …simplifies down to, “use SQL functions [only] when inlining”.
> 
> — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
> About your point #1…
> 
> I used a procedure to test this because functions shouldn’t do DDL. I 
> started with a working “language plpgsql” example:

Since procedures are relatively new to Postgres you are going to find 
more functions doing DDL then procedures. Not sure I follow why one is 
preferred over the other anyway?


> Then I changed it to a “language sql” test:
> 
> *drop table if exists t cascade;
> drop procedure if exists p() cascade;
> create procedure p()
>    language sql
> as $body$
>    drop table if exists t cascade;
>    create table t(k int primary key, v text not null);
> 
>    -- Causes compilation error: 42P01: relation "t" does not exist
>    -- insert into t(k, v) values (1, 'dog'), (2, 'cat'), (3, 'frog');
> $body$;
> *
> With the “insert” in place, it fails the syntax check with the error 
> that I mentioned. When it’s commented out, it passes the syntax check 
> and executes without error and has the expected effect.

Which is documented:

https://www.postgresql.org/docs/current/xfunc-sql.html
"
Note

The entire body of an SQL function is parsed before any of it is 
executed. While an SQL function can contain commands that alter the 
system catalogs (e.g., CREATE TABLE), the effects of such commands will 
not be visible during parse analysis of later commands in the function. 
Thus, for example, CREATE TABLE foo (...); INSERT INTO foo VALUES(...); 
will not work as desired if packaged up into a single SQL function, 
since foo won't exist yet when the INSERT command is parsed. It's 
recommended to use PL/pgSQL instead of an SQL function in this type of 
situation.
"






> The upshot of this is that I can’t design a test to demonstrate the 
> effect that I thought you meant. Could you show me a code example, please?

To turn off function body evaluation:

https://www.postgresql.org/docs/current/runtime-config-client.html

"check_function_bodies (boolean)

     This parameter is normally on. When set to off, it disables 
validation of the routine body string during CREATE FUNCTION and CREATE 
PROCEDURE. Disabling validation avoids side effects of the validation 
process, in particular preventing false positives due to problems such 
as forward references. Set this parameter to off before loading 
functions on behalf of other users; pg_dump does so automatically.
"



-- 
Adrian Klaver
adrian.klaver@aklaver.com

david.g.johnston@gmail.com wrote:

adrian.klaver@aklaver.com wrote:

Bryn wrote:

I used a procedure to test this because functions shouldn’t do DDL. I started with a working “language plpgsql” example…

Since procedures are relatively new to Postgres you are going to find more functions doing DDL then procedures. Not sure I follow why one is preferred over the other anyway?

(1) Unless you are doing transaction control our implementation doesn’t really give a preference. But from a theory perspective functions are ideally side-effect free while procedures are not. DDL, and even DML, cause side-effects and so are better done within a procedure. Having side-effects in a SELECT query is likewise not desirable so the inability to actually execute a procedure in the middle of a SELECT command doesn't pose a conceptual problem.

(2) As for the main question of allowing anonymous procedures to be written in SQL, I too don't see much benefit.  The pl/pgsql implementation is basically a superset, aside from adding BEGIN/END; you can simply pretend you are writing plain SQL in the DO body and it should work.  Now, would we reject a well-written patch that made it work?  Probably not.  But given the fact that DO is not a standard proscribed feature, and pl/pgsql works, I see little motivation for anyone to simply complete the symmetry.  If anything, the fact that these procedures would mostly be used for "side-effect causing actions" means that added overhead of the language tends to 0% of the overall execution time as the procedures become more complex and thus benefit more from being wrapped.

adrian.klaver@aklaver.com wrote:

Bryn wrote:

mmoncure@gmail.com wrote:

SQL language functions have one clear advantage in that they can be inlined in narrow contexts; this can give dramatic performance advantages when it occurs. They have a lot of disadvantages:

(1) Tables can’t be created then used without turning off function body evaluation.

(2) Queries must be parsed and planned upon each evocation (this can be construed as advantage in scenarios where you want to float a function over schemas).

(3) Generally constrained to basic SQL statements (no variables, logic etc).

…simplifies down to, “use SQL functions [only] when inlining”.

About your point #1, I used a procedure to test this… I started with a working “language plpgsql” example… Then I changed it to a “language sql” test:

drop table if exists t cascade;
drop procedure if exists p() cascade;
create procedure p()
  language sql
as $body$
  drop table if exists t cascade;
  create table t(k int primary key, v text not null);
  -- Causes compilation error: 42P01: relation "t" does not exist
  -- insert into t(k, v) values (1, 'dog'), (2, 'cat'), (3, 'frog');
$body$;

With the “insert” in place, it fails the syntax check with the error that I mentioned. When it’s commented out, it passes the syntax check and executes without error and has the expected effect.

Which is documented:

https://www.postgresql.org/docs/current/xfunc-sql.html

«
Note

The entire body of an SQL function is parsed before any of it is executed. While an SQL function can contain commands that alter the system catalogs (e.g., CREATE TABLE), the effects of such commands will not be visible during parse analysis of later commands in the function. Thus, for example, CREATE TABLE foo (...); INSERT INTO foo VALUES(...); will not work as desired if packaged up into a single SQL function, since foo won't exist yet when the INSERT command is parsed. It's recommended to use PL/pgSQL instead of an SQL function in this type of situation.
»

Bryn continued:

The upshot of this is that I can’t design a test to demonstrate the effect that I thought you meant. Could you show me a code example, please?

To turn off function body evaluation:

https://www.postgresql.org/docs/current/runtime-config-client.html

«

check_function_bodies (boolean)

This parameter is normally on. When set to off, it disables validation of the routine body string during CREATE FUNCTION and CREATE PROCEDURE. Disabling validation avoids side effects of the validation process, in particular preventing false positives due to problems such as forward references. Set this parameter to off before loading functions on behalf of other users; pg_dump does so automatically.
»

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

Re your paragraph #2, I already made the case for anonymous procedures. And I said that, to deserve the name, they must allow parameterization. They bring their value in a certain kind of scripting where you want to do stuff but leave no secondary traces. Plus the point about whether you even have the privilege to create objects. However, nobody here was convinced by this thinking.

I’d suggest if you want to debate the merits of having DO accept input arguments that you start a new thread for that; as it is only loosely related to the subject line for this thread. I’m not seeing any positions being given that such a capability would be undesirable.

All anyone is saying is that our best, untested, guesses are that the benefit/cost ratio of simply making “DO/LANGUAGE SQL” work, without any scope creep, is very low—namely because the benefit seems small regardless of the cost. I'm doubtful anyone has bothered to try and measure the cost. As for the benefit, I’m doubting the anonymous aspect of this makes much difference so an interested party can fairly easily use CREATE PROCEDURE to generate some actual performance data and at least plausibly argue that the results would carry over to “DO”. 
 

bryn continued:

I do think that it’s risky to dismiss as valueless some feature that, for example, Oracle Database has (and has had since the dawn of time), and that PG lacks, unless the feature is intertwined with specific aspects of the other environment that have no counterpart in PG. The extreme example of this thinking is to dismiss the notion of PL/pgSQL packages and inner procedures as valueless except in that they might ease migrations from Oracle Database to PG.

[If I understand correctly] at least one of the larger contributors to PostgreSQL, and some individuals, are deeply involved with the service of transitioning clients from Oracle to PostgreSQL.  I personally don't worry about weighting my interpretations based upon that external factor but simply evaluate it within the scope and reality I observe within the PostgreSQL hacker community.