Thread: Any difference between commit/rollback when only temp tables and \copy are used
Any difference between commit/rollback when only temp tables and \copy are used
From
"David G. Johnston"
Date:
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.
Thanks!
David J.
Re: Any difference between commit/rollback when only temp tables and \copy are used
From
Alan Hodgson
Date:
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. If you don't need anything saved at the end it probably doesn't matter if you use ROLLBACK or COMMIT.
Re: Any difference between commit/rollback when only temp tables and \copy are used
From
"David G. Johnston"
Date:
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.
Re: Any difference between commit/rollback when only temp tables and \copy are used
From
Adrian Klaver
Date:
On 05/16/2016 12:41 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? I would say that depends on how you are creating the temp table: http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html " TEMPORARY or TEMP If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well. The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands. For example, if a temporary table is going to be used in complex queries, it is wise to run ANALYZE on the temporary table after it is populated. Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes no difference in PostgreSQL and is deprecated; see Compatibility. " "ON COMMIT The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are: PRESERVE ROWS No special action is taken at the ends of transactions. This is the default behavior. DELETE ROWS All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit. DROP The temporary table will be dropped at the end of the current transaction block. " > > The script runs on Ubuntu inside a bash shell's heredoc. > > Thanks! > > David J. -- Adrian Klaver adrian.klaver@aklaver.com
Re: Any difference between commit/rollback when only temp tables and \copy are used
From
"David G. Johnston"
Date:
On 05/16/2016 12:41 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?
I would say that depends on how you are creating the temp table:
I'm presently using them without an "ON COMMIT" clause - they live until the end of the script/session/transaction (which here are all the same).
So within-transaction storage usage would be controllable making the use of BEGIN at least potentially meaningful - as long rollbacks or commits are used and more than one transaction is used to isolate the different parts.
The lack of auto-analyze is something to keep in mind (but haven't been bit by it yet so getting it to stick in memory is more difficult) in either case...though present usage involves sequential scans anyway.
Thanks!
David J.