Hi all,
I've had good experiences with PostgreSQL and D7. No more repair tables stuff if something gliched ;-)
But now I run into a PostgreSQL specific issue: I want to share tables for users (including sessions and profile data) between different sites (all in one multi-site setup if that matters). I've done this before with D6 and MySQL but D7 gives me the following error:
PDOException: SQLSTATE[0A000]: Feature not supported: 7 ERROR: cross-database references are not implemented: "profiles.public.sessions" LINE 3: profiles.public.sessions sessions ^: SELECT 1 AS expression FROM {sessions} sessions WHERE ( (sid = :db_condition_placeholder_0) AND (ssid = :db_condition_placeholder_1) ) FOR UPDATE; Array ( [:db_condition_placeholder_0] => 7bDoS4Azj1nQCsms_9j4vAb0VV_taJxqKuQkEVTaoz4 [:db_condition_placeholder_1] => ) in _drupal_session_write() (line 209 of /Library/WebServer/Documents/drupal/includes/session.inc).
The relevant portion of my settings.php is:
$databases['default']['default'] = array (
'database' => 'sandbox',
'username' => 'sandbox',
'password' => 'sandbox',
'host' => 'localhost',
'port' => '',
'driver' => 'pgsql',
'prefix' => array(
'default' => 'sandbox.public.',
'users' => 'profiles.public.',
'sessions' => 'profiles.public.',
'role' => 'profiles.public.',
'authmap' => 'profiles.public.',
)
);
$databases['profiles']['default'] = array (
'database' => 'profiles',
'username' => 'profiles',
'password' => 'profiles',
'host' => 'localhost',
'port' => '',
'driver' => 'pgsql',
'prefix' => array(
'default' => 'sandbox.public.',
'users' => 'profiles.public.',
'sessions' => 'profiles.public.',
'role' => 'profiles.public.',
'authmap' => 'profiles.public.',
)
);I have installed the dblink extension in both sandbox and profiles databases and truncated all cache tables.
Does anybody know why this is happening?
Kind regards,
eric
Comments
Try using separate schemas, not separate databases
Hi--
dblink provides a set of functions that require special programming to use. It does not provide seamless/transparent cross-database access.
I would take a look at creating individual schemas in the same database that can hold different sites. With Drupal, the different schemas can be configured by setting the '$db_prefix' parameter for each site. I see that you are using this configuration for some other functionality, but that's ultimately going to be the cleanest, simplest way. There are some other options that could be available depending on the version of postgresql, but I think even most (if not all) of the other options will suffer the same sort of issue as dblink.
Steven C. Buttgereit
Managing Director
Muse Systems
www.musesystems.com
Thanks!
Yes, this works.
The only problem was the installation: it fails when installing in another schema for an already existing database (minimal install, in English):
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "." LINE 7: CONSTRAINT sandbox2.date_formats_formats_key UNIQUE (format... ^Only the first 10 tables are installed, and the variables table.
I copied the tables from the other database in a new schema and now it works.
Thanks for your swift and adequate reply.
Eric