Re: How can I get and handle the status of sql statements that run in plpgsql ?
Hi st 3. 10. 2018 v 18:26 odesílatel David Gauthier 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 IF NOT FOUND THEN RAISE EXCEPTION 'no rows updates'; END IF; or DECLARE rc int; BEGIN UPDATE 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 > 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 >> 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 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.kla...@aklaver.com >>> >>
Re: How can I get and handle the status of sql statements that run in plpgsql ?
On Wed, Oct 3, 2018 at 9:25 AM, David Gauthier 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.
Re: How can I get and handle the status of sql statements that run in plpgsql ?
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 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 > 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 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.kla...@aklaver.com >> >
Re: How can I get and handle the status of sql statements that run in plpgsql ?
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 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 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.kla...@aklaver.com >
Re: How can I get and handle the status of sql statements that run in plpgsql ?
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 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.kla...@aklaver.com
Re: 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 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?"
How can I get and handle the status of sql statements that run in plpgsql ?
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 = 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 = 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 = 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 = 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 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 !
