dbExistsTable and current SCHEMA

[GoogleCodeExporter]

If there is no schema given in the table name, dbExistsTable('tabelName') 
returns true if there is a table with name 'tabelName' in any Schema.

This is a problem as the usage of Schemas and rpostgresql is a bit tricky, as 
postgresqlQuoteId() puts the quotes around the the whole table name, so that 
e.g. dbWriteTable( con, "mySchema.myTable", ...) creates a table 
'mySchema.myTable' in the current schema and not the table 'myTable' in the 
schema 'myTable'. If you use "SET search_path TO mySchema" then  dbWriteTable( 
con, "myTable", ...) will fail if ther is a table named '
myTable' in any other schema.

I propose to use, so that it is possible to create tables in a schema with 
dbWriteTable():

setMethod("dbExistsTable",
          signature(conn="PostgreSQLConnection", name="character"),
          def = function(conn, name, ...){
              ## Edd 09 Oct 2009: Fusion of patches by Joe Conway and Prasenjit Kapat
              names <- strsplit(name, ".", fixed=TRUE)[[1]]
              if (length(names) == 2) {     # format was "public.sometable"
                  res <- dbGetQuery(conn,
                                    paste("select schemaname,tablename from pg_tables where ",
                                          "schemaname !='information_schema' ",
                                          "and schemaname !='pg_catalog' and schemaname='",
                                          names[1], "' and tablename='", postgresqlEscapeStrings(conn, names[2]), "'", sep=""))
              } else {
                  cShemas <- dbGetQuery( conn, "SHOW search_path;")
                  ccShemas <- gsub(" ", "", strsplit(cShemas[[1]], ",", fixed=TRUE)[[1]])
                  res <- dbGetQuery(conn,
                                    paste("select tablename from pg_tables where ",
                                          "schemaname !='information_schema' and schemaname !='pg_catalog' ",
                                          "and schemaname='", ccShemas[1], "' ",
                                          "and tablename='", postgresqlEscapeStrings(conn, names[1]), "'", sep=""))
              }
              return(as.logical(dim(res)[1]))
          },
          valueClass = "logical"
          )

Original issue reported on code.google.com by dominik....@gmail.com on 11 Nov 2010 at 2:39

[GoogleCodeExporter]

In r174 (just committed) dbExistsTable will ignore the "." in the table name 
and quote as a whole, but it is aware of current schema.  So, you should be 
writable if a table with the same name is absent in the current schema.

Regarding the way to specify schema in dbExistsTable, dbWriteTable, 
dbReadTable, and dbRemoveTable family, I think the right way is to pass vector 
instead of a single string to solve this, I am waiting for comments in R-sig-db.

For the detail of the change:
It's perhaps better to use current_schema rather than "SHOW search_path" plus 
complex operation to extract one.  Note that the default return value of "SHOW 
search_path"
would be '"$user",public', and extracting the public is difficult.

So, the code fragment should look like:

currentschema <- dbGetQuery(conn, "SELECT current_schema()")
res <- dbGetQuery(conn,
    paste("select tablename from pg_tables where ",
    "schemaname !='information_schema' and schemaname !='pg_catalog' ",
    "and schemaname='", postgresqlEscapeStrings(conn, currentschema[[1]]), "' ",
    "and tablename='", postgresqlEscapeStrings(conn, names[1]), "'", sep=""))

But, there is another fundamental problem in this section of code that
dbWriteTable will write table containing "." and dbExistsTable regard it
as a separator.  This also needed to be fixed. So, drop the strsplit part.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 13 Nov 2010 at 9:00

• Changed state: Started
• Added labels: OpSys-All

[GoogleCodeExporter]

r175,r176 tries to deal with "schema"."table" reference when
schema is explicitly given as c("schema", "table).

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 6 Mar 2011 at 8:03

[GoogleCodeExporter]

There is a very important pitfall when schema support is not handled properly 
in PostgreSQL, or stated otherwise: it MUST be fully supported for the 
following reason.

When retrieving table information (fields, fieldtypes), a query is performed on 
the database catalog, which is a single catalog, not linked to the search path! 
So, when retrieving details for a given table, and omitting the namespace name 
(schema), you'll get all the fields of every table with the requested name, 
which is definitely not the desired result.
An extra catalog table and WHERE contraint have to be added to the query 
currently used to retrieve the table information. See the following example:

SELECT nspname as schemaname
, relname AS tablename
, attname AS attribname
, typname AS attribtype
FROM pg_class tbl
JOIN pg_namespace as nsp
  ON nsp.oid = tbl.relnamespace
JOIN pg_attribute as  att
  ON att.attrelid = tbl.oid
JOIN pg_type as typ
  ON typ.oid = att.atttypid
WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND nsp. nspname LIKE 'myschema'
AND tbl.relname LIKE 'mytable';

It might be worth considering using the information_schema, as the ANSI schema 
is stable across PostgreSQL versions, and the pg_catalog schema could change.

Original comment by wouter.b...@rivm.nl on 2 May 2011 at 11:23

[GoogleCodeExporter]

r185 dbListFields returns only the fields of the table in current_schema() or 
specified schema.  Because nspname is explicitly specified, NOT IN condition is 
not used.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 25 Sep 2011 at 7:53

[GoogleCodeExporter]

The spec is changed in 0.2 to access dbWriteTable( con, 
c("mySchema","myTable"), ...).  
Although I tried best, there might still some shortcomings. 
In such a case, please report as a new issue.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 10 Oct 2011 at 7:53

• Changed state: Fixed