Re: Any risks in using FUNCTIONs (stored procedures) instead of raw sql queries?

Phoenix Kiula wrote:

> I have googled but it looks like there's a whole variety of
> information from 2003 (when PG must have been quite different) until
> now--some people find stored functions slow for web based apps, others
> find it is worth the maintenance.

If your web servers are very close in network terms to your database
server, issue mostly non-trivial queries, and are on a low latency link,
it probably doesn't matter *that* much.

If your web servers have non-trivial latency to the database servers or
if they do vast numbers of tiny individual queries, it might be
extremely worthwhile wrapping them up in appropriate stored procedures
(set-returning where appropriate), especially if that also helps the
logical organisation of the code.

> 1. Create a connection
> 2. Take the submitted form info and check if it already exists in db (SQL1)
> 3. If exists, then update db with submitted info and return new values (SQL 2)
> 4. If not exists, then insert new record (SQL 2.1)

2, 3 and 4 are perfect candidates for being bundled into a PL/PgSQL
stored procedure.

You can probably get away with ditching step 2 entirely. What you really
want is "Ensure that the form info is in the database and up to date",
ie an UPSERT / REPLACE. There's a fairly convenient way to do that:



-- If the form is already there, update it.
-- If it's not there, this is a no-op.
UPDATE table SET val1 = blah, val2 = blah, etc
WHERE form_identifier = whatever;

-- Otherwise, insert it. If it's already there, this
-- only costs us an index lookup.
INSERT INTO table (form_identifier, val1, val2, etc)
SELECT whatever, blah, blah2
WHERE NOT EXISTS (SELECT 1 FROM table WHERE form_identifer = whatever)



You can of course conveniently bundle this into a PL/PgSQL stored
procedure. If you like you can also use GET DIAGNOSTICS to see whether
the UPDATE did anything and skip the INSERT if it did (allowing you to
structure the INSERT the usual way instead of INSERT ... SELECT ... WHERE).

> 5. If insert/update went well, we get the new values otherwise an
> "ERROR" string depending on what the error was

This should probably be handled by letting any error emitted by the
INSERT or UPDATE propagate out of the PL/PgSQL stored procedure and be
caught by the application.

> But a DBA told me that it will be much better to do all of these
> things in a stored procedure as it may bring some performance
> benefits.

I would tend to agree. It'll probably also be cleaner, and as a bonus
once PostgreSQL supports the UPSERT / REPLACE operation you can probably
just switch to using that instead of your stored procedure.

> He's an oracle DBA so I am not sure if the same applies to
> PG? Will a "function" that takes input values with 15 column data
> fields including two TEXT fields and then outputs perhaps an array of
> values to a PHP program be faster than 2-3 separate SQL queries issues
> from PHP?

Quite possibly. There's a cost to running a PL/PgSQL stored procedure,
but it's not huge. The best way to find out is to test it, since it
sounds like your code is well enough structured to make that fairly fuss
free.

--
Craig Ringer