Thread: How can I get and handle the status of sql statements that run inplpgsql ?
How can I get and handle the status of sql statements that run inplpgsql ?
From
David Gauthier
Date:
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?
create or replace function xfer_savings_to_checking(acct_no text, howmuch float)
returns text as $$
declare
savings_balance float;
checking_balance float;
begin
select balance into savings_balance from savings_acct_info where acct = acct_no;
sql_status = <capture the status>
if(something_went_wrong) then
raise exception 'select savings statement was bad "%"',sql_status;
return 'error';
end if;
select balance into checking_balance from checking_acct_info where acct = acct_no;
sql_status = <capture the status>
if(something_went_wrong) then
raise exception 'select checking statement was bad "%"',sql_status;
return 'error';
end if;
if(howmuch > saving_balance) then
raise notice 'Hey, you dont have that much to xfer ! You only have %',savings_balance;
return 'error';
end if;
start transaction;
update savings_acct_info set balance = balance - howmuch where acct = acct_no;
sql_status = <capture the status>
if(something_went_wrong) then
raise exception 'updating savings acct "%"',sql_status;
rollback;
return 'error';
end if;
update checking_acct_info set balance = balance + howmuch where acct = acct_no;
sql_status = <capture the status>
if(something_went_wrong) then
raise exception 'updating checking acct "%"',sql_status;
rollback;
return 'error';
end if;
commit;
end;
$$ language plpgsql;
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 my trials, it appears that plpgsql doesn't like "start transaction". So how is that piece done ?
Thanks in Advance for any help !
Re: How can I get and handle the status of sql statements that run inplpgsql ?
From
Christopher Browne
Date:
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 translatedto something which someone could understand? Finally, how can I effectively do a start transaction and eitherrollback 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?"
Re: How can I get and handle the status of sql statements that run inplpgsql ?
From
Adrian Klaver
Date:
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
Re: How can I get and handle the status of sql statements that run inplpgsql ?
From
David Gauthier
Date:
Thanks Adrian and Christopher !
So the transaction will be rolled back automatically if there's a problem. Got it !
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:
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
Re: How can I get and handle the status of sql statements that run inplpgsql ?
From
David Gauthier
Date:
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:
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: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
Re: How can I get and handle the status of sql statements that run inplpgsql ?
From
"David G. Johnston"
Date:
Is that how you detect if nothing was updated
Given your concerns and examples you might also want to look into serializable isolation; it is much more robust.
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.
Re: How can I get and handle the status of sql statements that run inplpgsql ?
From
Pavel Stehule
Date:
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.htmlupdate blah, blah...if(no_data) thenraise 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
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: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: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