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

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