Re: Any difference between commit/rollback when only temp tables and \copy are used

On Mon, May 16, 2016 at 3:56 PM, Alan Hodgson <ahodgson(at)lists(dot)simkin(dot)ca>
wrote:

> On Monday, May 16, 2016 03:41:23 PM David G. Johnston wrote:
> > I have a psql script that obtains data via the \copy command and loads it
> > into a temporary table. Additional work is performed possibly generating
> > additional temporary tables but never any "real" tables. Then the script
> > outputs, either to stdout or via \copy, the results.
> >
> > Does it matter whether I issue a ROLLBACK or a COMMIT at the of the
> > transaction? More basically: does it matter whether I issue a BEGIN?
> >
> > The script runs on Ubuntu inside a bash shell's heredoc.
> >
>
> Some things will complete faster if you use BEGIN to start, as PostgreSQL
> will
> otherwise issue an implicit BEGIN and COMMIT before and after every
> statement.
>
>
​Non-zero but minimal compared to the I/O hit.​ The slightly more
meaningful savings would be in lesser consumption of transaction id
values. Though both would probably only matter at scale.

> If you don't need anything saved at the end it probably doesn't matter if
> you
> use ROLLBACK or COMMIT.
>

​Yeah, without the front end of this being optimized for once you get to
the end you've already consumed the relevant resources and I suspect that
as far at the catalogs go those temp tables are just as good as rolledback
on session end.

David J.