Re: existence of a savepoint?

On 05/29/2018 05:18 PM, David G. Johnston wrote:
> On Tue, May 29, 2018 at 4:01 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com <mailto:alvherre(at)2ndquadrant(dot)com>>wrote:
>
> On 2018-May-29, Stuart McGraw wrote:
>
> > Alternatively if there were a setting to tell Postgresql to
> > follow the SQL standard behavior of overwriting rather stacking
> > savepoints, that too would also solve my current problem I think.
> > Perhaps it is just my limited experience but the former behavior
> > has always seemed more useful in practice than the latter.
>
> I think if what we're doing breaks the semantics of the SQL spec, we're
> definitely open to changing our behavior.  But that wouldn't solve your
> problem today.  What I think could solve your problem today is a
> C-language extension that uses xact.c callbacks in order to expose a
> list that you can query from user space.
>
> ​Stuart:​
>
> That said, have you measured this "leaking" and can show that it is non-trivial (given the large size of the overall transaction)?

No I haven't and am not sure how I would. Are you saying I shouldn't worry about it and just not bother releasing any of the savepoints? I would feel a little uneasy about that the same way I would feel about a program that never freed allocated memory or closed open files. If I know there are relatively small limits on how much data will be processed or how long the program will run, sure. But in my case I don't control the size of the input data and I don't understand the internals of savepoints so I think caution is prudent.

Also I'm not sure the warnings against premature optimization when talking about code performance tweaks apply to resource leaks. The former attempt to make a program run faster but don't (in theory) affect its correctness. Resource problems often show up unexpectedly and catastrophically. So being more preemptively concerned about the latter I think is justified.

> Beyond that bulk ETL leveraging SAVEPOINT is not something I've encountered or contemplated.  Expecting and reacting to errors is expensive and itself error-prone.  I'd much rather try to design something that where failure is simply bad - usually by bulk loading with fewer constraints and then ensuring that future queries don't attempt to do something illegal like insert duplicates.

Funny you should say that :-) I am looking at rewriting these import programs (there are several) to do just that. But it is not a trivial job and in the meantime I need to keep what already exists, working.