On 05/29/2018 05:18 PM, David G. Johnston wrote:
On Tue, May 29, 2018 at 4:01 PM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx <mailto:alvherre@xxxxxxxxxxxxxxx>>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.
