By DANG Fan


2015-10-19 03:29:31 8 Comments

I'm confused about usage of SP / UDF. Typically, it is also possible to write code in programs outside of database. So is there any general advice to decide when to use them?

1 comments

@Erwin Brandstetter 2015-10-19 04:29:18

Update

Postgres 11 finally introduces SQL procedures ("stored procedures") that can also start and end transactions:

However, commands that are not allowed in a transaction block can not (yet) be included. Important examples CREATE DATABASE or CREATE INDEX CONCURRENTLY or VACUUM.

And procedures can only return a single result row, yet, when created with INOUT parameters.

True stored procedures would not impose these restrictions. Further improvements may come with later releases.


Original answer

Before Postgres 11 there were no "stored procedures", strictly speaking. Just functions, doing almost but not quite the same. Most importantly, functions always run inside a transaction, which matters for lock management, trapping errors, or commands that cannot be run in a transaction context (see above). Related:

Processing data in SQL can have major benefits over retrieving all raw data into your application and doing calculations there:

The same principle can apply to bigger operations that can't be done in a single statement, not even when using "chained" commands in a (data-modifying) CTE:

Depending on the use case, a function can be superior in performance, and often more convenient - especially when multiple apps access the same database. You have to chose a server-side language, most commonly SQL or PL/pgSQL:

In the presence of competing concurrent transactions (multi-user environments) be brief. Only put as much into the same function as needs to be atomic. If possible, avoid long transactions with many locks. Locks are only released at the end of a transaction. Long transactions may stall concurrent access, even lead to deadlocks (which are detected and resolved by Postgres automatically, by raising an error for one or more competing transaction, which are then rolled back) ...

If you avoid those anti-patterns, server-side functions can be a great tool. For some purposes you must use functions anyway, like trigger functions.

@Evan Carroll 2018-06-25 03:52:46

You should merge this into your answer here, dba.stackexchange.com/questions/194684/…

@Erwin Brandstetter 2018-06-26 21:51:51

@Evan: I added some clarifications and links between the two questions. That should help.

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] User defined function performance disadvantages

1 Answered Questions

3 Answered Questions

[SOLVED] How do you find where a stored procedure is used (in other stored procedures)

2 Answered Questions

1 Answered Questions

3 Answered Questions

[SOLVED] Azure Data warehouse - User defined function issues

1 Answered Questions

Sponsored Content