How can I get and handle the status of sql statements that run in plpgsql ?

On Tue, 2 Oct 2018 at 16:48, David Gauthier <davegauthierpg@gmail.com> wrote:

Hi:
psql (9.6.7, server 9.5.2) on linux

How does one get the status of an sql statement executed in plpgsql? If that status is cryptic, how can that be translated to something which someone could understand? Finally, how can I effectively do a start transaction and either rollback or commit based on the results of the sql statements run?

For the situations you describe, I suggest that it is simple enough to
embrace the fact that stored functions run inside a pre-existing
transaction context.

You do not need to rollback or to return errors; you merely need to
raise the exceptions.

If *any* piece of the logic encountered an exception, then the
transaction has fallen into an exception state, and will automatically
be rolled back.

You can "lean on this"; either:
a) All of the logic passed with flying colours, and the transaction
may happily proceed, or
b) If any problem comes up along the way, the transaction is cancelled.

In PostgreSQL 11 (not yet a production release), there now exist
stored procedures that allow having BEGIN/COMMIT logic within a
procedure:
https://www.postgresql.org/docs/11/static/sql-createprocedure.html

I would think it likely that you'd want to use a mixture of stored
functions, that do some work on the assumption that it will all either
succeed or fail, and then use a stored procedure to do transactional
control on top of that.

But as things stand today, the transaction control will need to take
place in whatever layer you are using to control things. So, if
you're using an app written in Python to control things, you'd submit
the BEGIN/COMMIT on the Python side, and the stored functions run
within transaction context. And next year, when PG11 is available,
perhaps the BEGIN/COMMIT could reside in a stored procedure, so that
the Python code gets a bit simpler. (Or s/Python/Java/g, or
s/Python/PHP/g as needed...)
--

When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On 10/2/18 1:47 PM, David Gauthier wrote:

Hi:
psql (9.6.7, server 9.5.2) on linux

How does one get the status of an sql statement executed in plpgsql?  If
that status is cryptic, how can that be translated to something which
someone could understand?   Finally, how can I effectively do a start
transaction and either rollback or commit based on the results of the
sql statements run?

Of course I don't know what the <capture the status> and
"something_went_wrong" pieces look like, or they even make sense with
how this sort of thing shold be properly handled in plpgsql.  Also, in

The below(read to bottom of the page) might help:

https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

my trials, it appears that plpgsql doesn't like "start transaction".  So
how is that piece done ?

Thanks in Advance for any help !

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks Adrian and Christopher !

So the transaction will be rolled back automatically if there's a problem.
Got it !

Question: How do I detect when it is appropriate to raise notice so as to
be able to see the error message? Or will that automatically be sent to
stdout if an error occurs?

Question: Also, how can I detect how many records were operated on by, say,
an update statement? In the example I gave, I would want to make sure that
the transfer amount was deducted from the savings AND that the amount was
added to the checking. Both need to be 1, else I want to...
1) send a message (raise notice)
2) rollback somehow.

On Tue, Oct 2, 2018 at 5:27 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

I found "no_data" here...
https://www.postgresql.org/docs/10/static/errcodes-appendix.html

update blah, blah...
if(no_data) then
raise exception "update failed to update anything";
end if

Is that how you detect if nothing was updated and how to make sure the
thing returns and rolls back ?

On Wed, Oct 3, 2018 at 11:46 AM David Gauthier <davegauthierpg@gmail.com>
wrote:

On Wed, Oct 3, 2018 at 9:25 AM, David Gauthier <davegauthierpg@gmail.com>
wrote:

Is that how you detect if nothing was updated

https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

Given your concerns and examples you might also want to look into
serializable isolation; it is much more robust.

https://www.postgresql.org/docs/10/static/transaction-iso.html#XACT-SERIALIZABLE

If your concern isn't related to concurrency though you should just setup
tests to ensure the coding logic is correct and then let it run without all
the double-checking in production.

David J.

Hi

st 3. 10. 2018 v 18:26 odesílatel David Gauthier <davegauthierpg@gmail.com>
napsal:

I found "no_data" here...
https://www.postgresql.org/docs/10/static/errcodes-appendix.html

update blah, blah...
if(no_data) then
raise exception "update failed to update anything";
end if

UPDATE xxxx
IF NOT FOUND THEN
RAISE EXCEPTION 'no rows updates';
END IF;

or

DECLARE rc int;
BEGIN
UPDATE xxxx
GET DIAGNOSTICS rc = ROW_COUNT;
IF rc = 0 THEN
...

Regards

Pavel