CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3temp-tablessql-function
aarona
aarona

Reputation: 37354

Postgres: Temporary table in function is persistent. Why?

I have the following function in my postgresql database:

CREATE OR REPLACE FUNCTION get_unused_part_ids()
  RETURNS integer[] AS
$BODY$
DECLARE
  part_ids integer ARRAY;
BEGIN
  create temporary table tmp_parts
      as
  select vendor_id, part_number, max(price) as max_price
    from refinery_akouo_parts
   where retired = false
   group by vendor_id, part_number
  having min(price) < max(price);

  -- do some work etc etc

  -- simulate ids being returned
  part_ids = '{1,2,3,4}';
  return part_ids;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_unused_part_ids()
  OWNER TO postgres;

This compiles but when I run:

select get_unused_part_ids();

the temporary table, tmp_parts, still exists. I can do a select on it after. Forgive me as I'm used to a particular functionality with t-sql/MSSQL. This wouldn't be the case with MSSQL. What am I doing wrong?

Upvotes: 1

Views: 6802

Answers (3)

Gabriel&#39;s Messanger
Gabriel&#39;s Messanger

Reputation: 3318

After manual

Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below)

Session ends after disconnection. Not after transaction commit. So default behavior is to preserve temp table till your connection is still open. You must add ON COMMIT DROP; to achive your desired behaviour:

create temporary table tmp_parts on commit drop
      as
  select vendor_id, part_number, max(price) as max_price
    from refinery_akouo_parts
   where retired = false
   group by vendor_id, part_number
  having min(price) < max(price)
on commit drop;

Upvotes: 3

JonathanG
JonathanG

Reputation: 292

The table will only be deleted at the end of the session. You need to specify the ON COMMIT option to drop, and it will drop the table at the end of the transaction.

  create temporary table tmp_parts 
  on commit drop
      as
  select vendor_id, part_number, max(price) as max_price
    from refinery_akouo_parts
   where retired = false
   group by vendor_id, part_number
  having min(price) < max(price);

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Temporary tables are treated differently in the two databases. In SQL Server, they will be removed at the end of a stored procedure where they are created, automatically.

In Postgres, temporary tables are assigned to a session (or a transaction), as explained in the documentation:

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 concept is sort of between a regular temporary table and a global temporary table in SQL Server (global temporary tables start with ##).

Upvotes: 2

Related Questions