CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
four-eyes
four-eyes

Reputation: 12394

Drop Temporary Table after execution of function

I am executing a selfwritten postgresql function in a loop for several times from Python. I am using the psycopg2 framework to do this. The function I wrote hast the following structure:

CREATE OR REPLACE FUNCTION my_func()
RETURNS void AS
$$
BEGIN
    -- create a temporary table that should be deleted after  
    -- the functions finishes
    -- normally a CREATE TABLE ... would be here
    CREATE TEMPORARY TABLE temp_t
        (
          seq integer,
          ...
        ) ON COMMIT DROP;

    -- now the insert    
    INSERT INTO temp_t
        SELECT
        ...

END
$$
LANGUAGE 'plpgsql';

Thats basically the python part

import time
import psycopg2
conn = psycopg2.connect(host="localhost", user="user", password="...", dbname="some_db")
cur = conn.cursor()
for i in range(1, 11):
    print i
    print time.clock()
    cur.callproc("my_func")
    print time.clock()
cur.close()
conn.close()

The error I get when I run the python script is: 

---> relation "temp_t" already exists

Basically I want to measure how long it takes to execute the function. Doing that, the loop shall run several times. Storing the result of the SELECT in a temporary table is supposed to replace the CREATE TABLE ... part which would normally create the output table Why doesnt postgres drop the function after I executed the function from Python?

Upvotes: 1

Views: 1838

Answers (3)

klin
klin

Reputation: 121604

All the function calls in the loop are performed in a single transaction, so the temporary table is not dropped each time. Setting autocommit should change this behavior:

...
conn = psycopg2.connect(host="localhost", user="user", password="...", dbname="some_db")
conn.autocommit = True
cur = conn.cursor()
for i in range(1, 11):
    ...

Upvotes: 3

four-eyes
four-eyes

Reputation: 12394

Another quick n dirty is to connect and disconnet after each function call.

import time
import psycopg2
for i in range(1, 11):
    conn = psycopg2.connect(host="localhost", user="user", password="...",   dbname="some_db")
    cur = conn.cursor()
    print i
    print time.clock()
    cur.callproc("my_func")
    print time.clock()
    cur.close()
    conn.close()

Not nice, but does the trick.

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76551

Temporary tables are dropped when the session ends. Since your session does not end with the function call, the second function call will try to create the table again. You need to alter your store function and to check whether the temporary table already exists and create it if it doesn't. This post can help you in doing so.

Upvotes: 1

Related Questions