Skip to content

RPostgreSQL - dbExistsTable() is FALSE with schema names?

6 messages · Dirk Eddelbuettel, Sean Davis, Prasenjit Kapat

Original
Prasenjit Kapat
# 
Hi,

dbExistsTable (conn, name="myschema.mytable") is FALSE, whereas
dbExistsTable (conn, name="mytable") is TRUE ! 'mytable' is part of
the 'myschema'.

Is this as expected?

Regards,
--
Prasenjit
Dirk Eddelbuettel
#
On 6 April 2009 at 19:18, Prasenjit Kapat wrote:
| Hi,
| 
| dbExistsTable (conn, name="myschema.mytable") is FALSE, whereas
| dbExistsTable (conn, name="mytable") is TRUE ! 'mytable' is part of
| the 'myschema'.
| 
| Is this as expected?

Not sure. Check the Pg API doc of what is expected.

And in general, patches and debugging are welcome.  Sameer, who wrote
RPostgreSQL as part of last year's Google Summer of Code seems to have lost
touch with the code.

Cheers, Dirk
Sean Davis
#
On Mon, Apr 6, 2009 at 7:42 PM, Dirk Eddelbuettel <edd at debian.org> wrote:

            

      
      
      
    

        
Looks like dbTableExists() method calls dbListTables() method, and that uses
the SQL query:

select tablename from pg_tables
where schemaname !='information_schema'
and schemaname !='pg_catalog'

This returns the table name, but no schema name.  R code is then used to
check for equality of strings.  This will lead to the incorrect behavior as
noted.  I can provide a patch, probably, but it won't be immediate, so if
someone else gets there first, just let us know.

Sean

  
  


  


      

    

    

      
      

        


  

        

      Prasenjit Kapat
    

    

      
      #
    

  


  

      
On Mon, Apr 6, 2009 at 8:01 PM, Sean Davis <sdavis2 at mail.nih.gov> wrote:

            

      
      
      
    

        
Hmmm, I am not too familiar with postgres myself. I am trying to cover
as much as possible with RPostgreSQL.

            

      
      
      
    

        
It looks like anything involving schema is buggy, probably the code
was not tested in such scenarios. For example:

dbRemoveTable (con, name="myschema.mytable") is again FALSE and
dbRemoveTable (con, name="mytable") gives (which is expected as
'public.mytable' is nonexistent):

Error in postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (could not Retrieve the result : ERROR:  table
"mytable" does not exist
)
[1] FALSE

I'll try to see if any proper solution exists.

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Sean Davis
    

    

      
      #
    

  


  

      
On Mon, Apr 6, 2009 at 9:23 PM, Prasenjit Kapat <kapatp at gmail.com> wrote:

        

            

      
      
      
    

        
I was looking through the DBI docs and I didn't see a mention of schema,
specifically.  I'm not very familiar with how other DB drivers deal with the
issue of schema.  Is there a description of how DBI methods are meant to
deal with schema, or is it up to the implementation to deal with the issue
via the ... arguments?

Sean

  
  


  


      

    

    

      
      

        


  

        

      Prasenjit Kapat
    

    

      
      #
    

  


  

      
On Mon, Apr 6, 2009 at 10:41 PM, Sean Davis <sdavis2 at mail.nih.gov> wrote:

            

      
      
      
    

        
Hopefully someone who knows the details can help on this.

Back here, the following is a quick-n-dirty solution (not a patch of course):

dbExistsTable <- function (con, name, ...)
{
  as.logical (
   dim (
    dbGetQuery (con,
     paste ("select schemaname,tablename from pg_tables where schemaname='",
      rev(strsplit(name, ".", fixed=TRUE)[[1]])[2],
      "' and tablename='",
      rev(strsplit(name, ".", fixed=TRUE)[[1]])[1], "'", sep="")
  )) [1])
}

dbRemoveTable <- function (con, name, ..., cascade=FALSE)
{
  if (dbExistsTable (con, name)) {
    dbGetQuery (con, paste ("drop table ", name, ifelse (cascade, "
cascade", ""), ";", sep=""))
  }
}