[R-sig-DB] RPostgreSQL - dbExistsTable() is FALSE with schema names?

[Sean Davis]

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

> On Mon, Apr 6, 2009 at 8:01 PM, Sean Davis <sdavis2 using mail.nih.gov> wrote:
> >
> >
> > On Mon, Apr 6, 2009 at 7:42 PM, Dirk Eddelbuettel <edd using debian.org>
> wrote:
> >>
> >> 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.
>
> Hmmm, I am not too familiar with postgres myself. I am trying to cover
> as much as possible with RPostgreSQL.
>
> >> 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.
> >
> > 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.
>
> 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.
>

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

	[[alternative HTML version deleted]]