Can not share tables across databases
| Project: | Drupal |
| Version: | 6.12 |
| Component: | postgresql database |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
While attempting to set up my multisite test server according to this guide, http://drupal.org/node/291373 I encountered the following after dropping authmap, roles, users, and sessions from my site2db and prefixing those tables with "sharddb.":
Warning: pg_query() [function.pg-query]: Query failed: ERROR: schema "drupal" does not exist in /drupal/includes/database.pgsql.inc on line 139
Warning: ERROR: schema "drupal" does not exist query: SELECT u.*, s.* FROM drupal.users u INNER JOIN drupal.sessions s ON u.uid = s.uid WHERE s.sid = 'f0c3e5bdb9e099f3d2cf66fcbf002ce4' in /drupal/includes/database.pgsql.inc on line 160
Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /drupal/includes/database.pgsql.inc:139) in /drupal/includes/bootstrap.inc on line 1031
Warning: Cannot modify header information - headers already sent by (output started at /drupal/includes/database.pgsql.inc:139) in /drupal/includes/bootstrap.inc on line 630
Warning: Cannot modify header information - headers already sent by (output started at /drupal/includes/database.pgsql.inc:139) in /drupal/includes/bootstrap.inc on line 631
Warning: Cannot modify header information - headers already sent by (output started at /drupal/includes/database.pgsql.inc:139) in /drupal/includes/bootstrap.inc on line 632
Warning: Cannot modify header information - headers already sent by (output started at /drupal/includes/database.pgsql.inc:139) in /drupal/includes/bootstrap.inc on line 633My immediate thought was that pgsql needed a different syntax to tell it that I wanted to share tables on a different db, not in a different schema. Turns out that was correct but the same documentation made it clear that you can only access data from a single database from a client connection. Here's the quote from the pgsql documentation:
A PostgreSQL database cluster contains one or more named databases. Users and groups of users are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.
Note: Users of a cluster do not necessarily have the privilege to access every database in the cluster. Sharing of user names means that there cannot be different users named, say, joe in two databases in the same cluster; but the system can be configured to allow joe access to only some of the databases.
A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.
Schemas can be used to do what I want to do but this apparently causes other issues as outlined here: http://drupal.org/node/45332
IMO this falls under the heading of Usability. There are two possible ways to deal with this that I can see. Either fix the upgrade issue that is created when schemas are used or have Drupal open 2 db connections to pgsql when using a shared database configuration.