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@2ndquadrant.com <mailto:alvherre@2ndquadrant.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
ofthe savepoints?  I would feel a little uneasy about that the same way I would feel about a program that never freed
allocatedmemory or closed open files.  If I know there are relatively small limits on how much data will be processed
orhow long the program will run, sure.  But in my case I don't control the size of the input data and I don't
understandthe 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
resourceleaks.  The former attempt to make a program run faster but don't (in theory) affect its correctness.  Resource
problemsoften show up unexpectedly and catastrophically.  So being more preemptively concerned about the latter I think
isjustified.
 

> Beyond that bulk ETL leveraging SAVEPOINT is not something I've encountered or contemplated.  Expecting and reacting
toerrors 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
illegallike insert duplicates.
 

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