Re: "stored procedures" - use cases?

On mån, 2011-04-25 at 14:35 -0500, Kevin Grittner wrote:
> (1) All the \d commands in psql should be implemented in SPs so
> that they are available from any client, through calling one SP
> equivalent to one \d command.

You don't need stored procedures with special transaction behavior for
this. In fact, you probably shouldn't use them even if you had them,
because you surely want a consistent view of, say, a table.

> (2) In certain types of loads -- in particular converting data from
> old systems into the database for a new system -- you need to load
> several tables in parallel, with queries among the tables which are
> being loaded. The ability to batch many DML statements into one
> transaction is important, to avoid excessive COMMIT overhead and
> related disk output; however, the ability to ANALYZE tables
> periodically is equally important, to prevent each access to an
> initially-empty table from being done as a table scan after it has
> millions of rows. VACUUM might become equally important if there
> are counts or totals being accumulated in some tables, or status
> columns are being updated, as rows are added to other tables.

I'm not sure I really follow this. If your aim is to batch DML
statements and avoid COMMIT overhead, why would you want to use stored
procedures that possibly span multiple transactions?