Hacker News Reader

Comment by pwaivers | Hacker News Reader

> "PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure."

Did PostgreSQL not have stored procs before?

View on HN

Stored functions, not procedures. The difference is explained here: https://www.postgresql.org/docs/11/static/xproc.html Basically, procedures do not return values, and can have nested transactions. (Today, functions are used to accomplish much of what seems more appropriate for a procedure.)

They do return values:

https://www.postgresql.org/docs/devel/static/sql-call.html

    CALL executes a procedure.
    
    If the procedure has output arguments, then a result row will be returned.

and from https://www.postgresql.org/docs/devel/static/sql-createproce...

    argmode

        The mode of an argument: IN, INOUT, or VARIADIC. If omitted, the default is IN. (OUT arguments are currently not supported for procedures. Use INOUT instead.)

Not in the sense of a function; you can't call a procedure from within an expression. Interesting that they can produce output rows though.

Yes, that's true.

I think the new part is "allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure."

Stored procedures are technically new with 11, but I think the only functional (rather than syntactic) difference between the new support for stored procs and stored functions which happen to not return anything is the transaction control for stored procs. (And stored functions returning nothing are often referred to informally as stored procedures in postgres discussions.)

Stored procedures can return values. See https://www.postgresql.org/docs/devel/static/sql-call.html

That's a rather nice feature that Transact-SQL (both Sybase and Microsoft) has had for a long time. It should make it easier for some folks to convert over.

Just as a point of clarity on that, SQL Server only allows one encapsulating transaction per session. You can have begins and so on within a stored procedure, but really that is little more than a "just in case" defense (e.g. just in case the caller didn't already do this), as you can only commit or rollback the outer transaction.

If you want to have atomic transactions within a transaction you have to do hacks like make a distributed call back to yourself, in that new session creating another transaction.

It's an element of SQL Server that confuses people to this day.

I was more on the Sybase side and dealt with an environment where the calling program didn't initiate a transaction so it worked quite well for me. We were kind of touchy about having a transaction open longer than it needed to be.

Absolutely! I come from a historically Microsoft shop, and the lack of this was by far the biggest barrier when initially learning PostgresSQL since I was still in the mindset of designing stuff as I would in Sql Server.

I'm probably wrong, but I think Stored Procedures are being introduced in 11.

From the announcement: "SQL Stored Procedures

PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure. Procedures can be created using the CREATE PROCEDURE command and executed using the CALL command."

It doesn't make sense to teach people how to create procedures if they already have them before. The docs[0] also don't have a section for CREATE PROCEDURE before 11[1].

It is a great tool anyway, better late than never.

[0] https://www.postgresql.org/docs/10/static/sql-commands.html

[1] https://www.postgresql.org/docs/11/static/sql-commands.html

You're correct, "CREATE PROCEDURE" didn't exist until PG 11. But "CREATE FUNCTION" has existed for ages, and it covers most of the common cases for server-side logic.

Yes it did. The "embedded transactions" is the new feature.

No, it didn't.

It had functions, not procedure. You can emulate (most of) procedure using function that return void.

Yes, that's true, although slightly pedantic. I'm sure there are procedural cases that PG functions can't emulate, although I don't think I've ever encountered one in practice.

Having said that, the addition of transaction support is a great step forward.

Only one: the ability to BEGIN/ROLLBACK/COMMIT in the body of the function.

And, this is just adding new syntactic support for SQL standards compliance, right? I don't think it's really a new control-flow if you ignore syntactic differences.

I think you can get the equivalent behavior out of BEGIN/EXCEPT blocks in plpgsql functions. I think this behaves as if you used SAVEPOINT and ROLLBACK TO SAVEPOINT which have been directly supported in postgresql but not in functions.

> most of

Curious, what are those procedures which can't be emulated as functions. Maybe you have a chance to describe some example?

There is only one thing that procedures can do that functions can't: nest transactions inside the transaction that calls it.

Reply on news.ycombinator.com