Hacker News Reader

Comment by jrs95 | Hacker News Reader

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.

Reply on news.ycombinator.com