Re: Serializable transaction restart/re-execute

Thank you very much for such detailed response.

Indeed I'm thinking too much "GTM" instead of actually changing the
mindset, but the problem with LOCKs (which are also available in GTM) is
that the developer does have to remind to lock what they want to use for
update and if they don't, integrity/consistency issues might come up (or
even data loss which would be worse..).

Serializable isolation would make sure developers don't need to remember
that while keeping that consistency (though losing some performance) and
also they wouldn't have to worry about deadlocks (as serializable use soft
locks).

But I guess strong code review and code re-design (where needed) should be
able have an even better solution, that's true.

From what I have read so far, I can't find a way to cause this "restarts"
(besides patching pgsql itself which I wouldn't know where to start).

Thanks once again.

On Mon, Apr 6, 2015 at 12:42 PM, Bill Moran <wmoran(at)potentialtech(dot)com>
wrote:

> On Mon, 6 Apr 2015 10:41:25 +0100
> Filipe Pina <fopina(at)impactzero(dot)pt> 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
>