Temporary table initially treated as non-existing

[GoogleCodeExporter]

The following script shows the problem (PSql 8.4.1, R 2.9.2, RPostgresql
0.1-6, DBI 0.2-4):

require(RPostgreSQL)

# connect to some database
i.db <- dbConnect('PostgreSQL', host="localhost", dbname="xxx", user="yyy",
password='zzz')

# create temporary table (as a copy of any existing one)
dbSendQuery( i.db, "CREATE TEMP TABLE tmptest AS
                    SELECT * FROM pg_database")

# query temp table
dbGetQuery( i.db, "SELECT * FROM tmptest" )
# expected: contents of tmptest
# outcome: RS-DBI driver: (could not Retrieve the result : ERROR:
# relation "tmptest" does not exist

# now, a work-around:
# try to create tmptest once more
dbSendQuery( i.db, "CREATE TEMP TABLE tmptest AS
                    SELECT * FROM pg_database")
# Error in postgresqlExecStatement(conn, statement, ...) : 
#  RS-DBI driver: (could not Retrieve the result : ERROR:  relation
# "tmptest" already exists)

# this time "select * from tmptest" should work
dbGetQuery( i.db, "SELECT * FROM tmptest" )

Original issue reported on code.google.com by astuka...@gmail.com on 28 Oct 2009 at 12:53

[GoogleCodeExporter]

TEMP TABLE is invisible from a different connection, while RPostgreSQL tries to 
open new connection when some results is remaining on current connection.  In 
the new connection, the temp table would be invisible.

While the example by astukalov does not work, inserting a dbClearResult() call
will make it work without creating another tmptest like:

    c <- dbSendQuery(con, "CREATE TEMP TABLE tmptest AS SELECT * FROM foo")
    dbClearResult(c)
    d <- dbGetQuery(con, "SELECT * FROM tmptest")

I am not sure if the implicit clone connection feature is better removed or 
actually required for some purpose.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 24 Sep 2010 at 12:47

[GoogleCodeExporter]

r230, r231 avoid connection cloning and call dbClearResult() instead.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 30 Mar 2012 at 4:08

• Changed state: Fixed