On Mon, 6 Apr 2015 10:41:25 +0100 Filipe Pina <fopina@xxxxxxxxxxxxx> wrote: > Hi Bill, thanks for the quick reply. > > I had read about SAVEPOINTs but I also read I cannot use them in PLPGSQL and > should use BEGIN/END blocks and EXCEPTIONs. > > Did you check the URL I mentioned? Yes, I did: http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure ... > But it doesn't work.. Every iteration fails with serialization_failure > probably because the outer transaction is not rolled back and I'm not sure > how to write this in a way I can roll it back and still have control of the > LOOP.. Probably one of your issues is that there is no such thing as an "outer" transaction. There's just a transaction. There is no nesting of transactions, so the belief that there is an outer transaction that can somehow be manipulated indepently of some other transaction is leading you to try things that will never work. I wasn't aware that SAVEPOINTs didn't work in pl/pgsql, thanks for educating me on that point. > I find it hard to believe that PGSQL has this amazing "serializable" > isolation method but not a standard way to take advantage of it to > automatically "restart" the failed transactions... I've been over this ground before. You're thinking in such a micro case that you haven't realized the inherent difficulty of restarting large transactions with lots of data modification. An RDBMS may have many tables updated within a transaction, and transactions may do data processing completely outside of the database, which means the only way to ensure consistency is to notify the controlling process of the problem so it can decide how best to respond. So ... I dug into your problem a little more, and I think the problem is that you're trying too hard to replicate GTM design paradigms instead of learning the way that PostgreSQL is designed to work. If I were creating the functions you describe, I would ditch the second one and simply have this: CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$ BEGIN update account set balance = balance+10 where id=1 RETURNING balance; END $$ LANGUAGE SQL; of course, it's unlikely that you'll ever want to wrap such a simple query in a function, so I'm supposing that you'd want to do something else with the old value of balance before updating it, in which case: CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$ DECLARE cc integer; BEGIN SELECT INTO cc balance FROM account WHERE id = 1 FOR UPDATE; RAISE NOTICE 'Balance: %', cc; perform pg_sleep(3); update account set balance = cc+10 where id=1 RETURNING balance INTO cc; return cc; END $$ LANGUAGE plpgsql; The FOR UPDATE ensures that no other process can modify the row while this one is sleeping. Now, I understand that you want to don't want to do row locking, but this is (again) an insistance on your part of trying to force PostgreSQL to do things the way GTM did instead of understanding the RDBMS way of doing things. Unlearn. Keep in mind that mytest() might be called as part of a much larger transaction that does many other things, and you can't simply roll that back and restart it within mytest() since mytest() doesn't know everything else that happened. In you're case, you're trying to look at mytest() as something that will always be used in a specific way where the aforementioned problem won't be encountered, but you can not guarantee that, and it doesn't hold true for all functions. In general, it's inappropriate for a function to be able to manipulate a transaction beyond aborting it. And the abort has to bubble up so that other statements involved in the transaction are also notified. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
