[R-sig-DB] Error in postgresqlExecStatement...RS-DBI driver: (could not Retrieve the result...)

[Tomoaki NISHIYAMA]

Hi,

Some part of this error seems to be made by
the confusion of "public.tmptable" and public.tmptable.

The meaning of the tablename handled by dbWriteTable, dbRemoveTable,  
etc.
changed relatively recently,
Now "public.tmptable" means literaly "public.tmptable" in the current  
schema,
while public.tmptable in the sql means tmptable in public schema.
This may relate to possible cause of not being to be able to create  
table, if
the current schema is changed in some other place.  Also, what access  
privilege
the connecting postgresql user have on each database/schema  
combination should be checked.

>> The environment where I ran the program is Red Hat Enterprise  
>> Linux 6, and PostgreSQL 8.4.7. R version is  2.12.1.

and what version for RPostgreSQL?

>> I ran into this error when I was trying to run an R program  
>> written by someone else.


Do you have any estimate when it was written?
-- 
Tomoaki NISHIYAMA

Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi,
Kanazawa, 920-0934, Japan


On 2011/02/09, at 13:00, Rex Hu wrote:

> Sorry, my bad. Forgot the attachment...
>
> --Rex
>
> <loop.R>
>
>
>
> On Feb 8, 2011, at 10:55 PM, Rex Hu wrote:
>
>> Hi all!
>>
>> I am a total newb in database programming, but unfortunately, I am  
>> working on a project that requires database programming using R. I  
>> hope that some one could help me about this. Thank you all in  
>> advance!
>>
>> I ran into this error when I was trying to run an R program  
>> written by someone else. It occurs when executing this 'big  
>> loop' (the R code of this loop is in the attachment). I have  
>> absolutely no clue what is going on.
>>
>> The environment where I ran the program is Red Hat Enterprise  
>> Linux 6, and PostgreSQL 8.4.7. R version is  2.12.1.
>>
>> The following are error messages.
>>
>> 64490 :class(mydata):  data.frame
>>   cleaning.out.cols: geofencename, geofencereason
>>   prod(u.gfn == ""): 0
>>   prod(u.gfr == ""): 0
>>   dim(mydata): 185 7
>>   Creating hourflag, odoflag, stateflag...done.
>>   Appending them to the data.frame...done.
>>   Sum:: hourflag: 10 , odoflag: 10 , stateflag: 12 , hrOdoflag: 10
>>   Starting hrodoflag.index loop...
>>   hrodoflag.index loop finished.
>> Error in postgresqlExecStatement(conn, statement, ...) :
>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>> "public.tmptable" already exists
>> )
>> In addition: Warning messages:
>> 1: In dir.create(datadumpdir, showWarnings = TRUE, recursive =  
>> TRUE,  :
>> '/scratch/kan/datadump' already exists
>> 2: In dir.create(misclwritedir, showWarnings = TRUE, recursive =  
>> TRUE,  :
>> '/scratch/kan/work/bordercrossing/miscl/AU08_100301' already exists
>> 3: In dir.create(datawritedir, showWarnings = TRUE, recursive =  
>> TRUE,  :
>> '/scratch/kan/work/bordercrossing/data/cevaAU08_100301' already  
>> exists
>> Error in postgresqlExecStatement(conn, statement, ...) :
>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>> "public.tmptable" does not exist
>> )
>> In addition: Warning message:
>> In postgresqlQuickSQL(conn, statement, ...) :
>> Could not create executeCREATE TABLE "public.tmptable"
>> ( "id_tb100301trp" bigint,
>>   "bridge_new" text,
>>   "tripscore" float8,
>>   "stflag" float8,
>>   "hrflag" float8,
>>   "odflag" float8
>> )
>> Error in postgresqlExecStatement(conn, statement, ...) :
>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>> "public.tmptable" does not exist
>> )
>> In addition: Warning message:
>> In postgresqlQuickSQL(conn, statement, ...) :
>> Could not create executeCOPY public.tmptable FROM '/tmp/ 
>> rpsql.G19577' WITH CSV HEADER QUOTE AS '"'
>> Error in postgresqlExecStatement(conn, statement, ...) :
>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>> "public.tmptable" does not exist
>> )
>> In addition: Warning message:
>> In postgresqlQuickSQL(conn, statement, ...) :
>> Could not create executeUPDATE  au08_100301.tb100301trp  SET   
>> bridge_new  =  public.tmptable.bridge_new FROM  public.tmptable
>> WHERE  au08_100301.tb100301trp.id_tb100301trp  =   
>> public.tmptable.id_tb100301trp ;
>> Error in postgresqlExecStatement(conn, statement, ...) :
>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>> "public.tmptable" does not exist
>> )
>> In addition: Warning message:
>> In postgresqlQuickSQL(conn, statement, ...) :
>> Could not create executeUPDATE  au08_100301.tb100301trp  SET   
>> tripscore  =  public.tmptable.tripscore FROM public.tmptable
>> WHERE  au08_100301.tb100301trp.id_tb100301trp  =   
>> public.tmptable.id_tb100301trp ;
>> Error in postgresqlExecStatement(conn, statement, ...) :
>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>> "public.tmptable" does not exist
>> )
>> In addition: Warning message:
>> In postgresqlQuickSQL(conn, statement, ...) :
>> Could not create executeUPDATE  au08_100301.tb100301trp  SET   
>> stflag  =  public.tmptable.stflag FROM public.tmptable
>> WHERE  au08_100301.tb100301trp.id_tb100301trp  =   
>> public.tmptable.id_tb100301trp ;
>> Error in postgresqlExecStatement(conn, statement, ...) :
>> RS-DBI driver: (could not Retrieve the result : ERROR:  relation  
>> "public.tmptable" does not exist
>> )
>> In addition: Warning message:
>> In postgresqlQuickSQL(conn, statement, ...) :
>> Could not create executeUPDATE  au08_100301.tb100301trp  SET   
>> hrflag  =  public.tmptable.hrflag FROM public.tmptable
>> WHERE  au08_100301.tb100301trp.id_tb100301trp  =   
>> public.tmptable.id_tb100301trp ;
>>
>>
>>
>> ***********************************************
>> Hu, Zhengyu (Rex)
>> Department of Statistics
>> The Ohio State University
>> Columbus, OH 43210
>>
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db