Add virtual mode support for postgresql using schemas

• In XWikiHibernateStore.createWiki(String wikiName, XWikiContext context), find a way to make a "create database" query not in a "transaction". Postgre return a error : "CREATE DATABASE: may not be called in a transaction block".

I was looking a few days ago at the session/transaction relationship in XWiki. For the moment it is impossible to create a session without a transaction, although the method accepts a boolean withTransaction parameter (which is ignored). This should be fixed, but there is a lot of code that must be checked, as changing the current behavior could break something.

Plus I "fixed" the withTransaction parameter (adding an if around transaction init at end of method) use just for a test and even with that I had the same error

This patch add virtual mode support for PostgreSQL database in a similar way that the issue XWIKI-1740 add support for Derby an HSQLDB.

It only works with hibernate 3.1.3 or later because of the bug HHH-1325:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-1325

we've now upgraded to hibernate 3.1.3 so this patch could be applied. I'll let you work on it Thomas.

This works almost totally. There is just some errors when creating new wiki then import xar or copy wiki template into it just after like Wiki Manager do. I think what you did is good but there is another problem with schemas like for derby and hsqldb in XWIKI-1740.

There is another problem with the modification in XWikiHibernateBaseStore.getSchemaUpdateScript with this patch : it make updateSchema to generate lot of exceptions.

As Michal Bielicki said in the mailing list, the JDBC driver for postgresql 8.3.3 support hibernate catalog (see http://markmail.org/message/hfw56keweoc2dckl). We should ttry it.

Hibernate doesn't provide direct access to the jdbc connection, but rather a proxy which makes sure everything is done inside of a transaction.
"No communication with the database can occur outside of a database transaction"
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/transactions.html#transactions-demarcation
There is however a hole which makes closing the transaction possible.

stmt.execute("ROLLBACK; CREATE DATABASE " + schema + " ENCODING = 'UNICODE' TABLESPACE = pg_default; START TRANSACTION;");

What is important is that hibernate doesn't loose track of the current transaction since it is being stopped and started manually.

In my search for a supported way to run sql outside of a transaction, I found this:
http://biotext.org.uk/execute-sql-outside-of-a-transaction-in-hibernate/
He seems to have settled on the same method which I found.

Here it gives an example of running code outside of a transaction (for jdbc environments) but I was unable to reproduce the results.
https://www.hibernate.org/452.html

After testing and looking at the jdbc source code, I can say the setCatalog function is still not supported.

The two possible ways to do postgres multiwiki are by having separate databases (aka mysql style) or by using separate schemas (oracle)
Method #1 problem is that connecting to seperate databases requires seperate connections (new jdbc connection to new postgres process) Hibernate doesn't take kindly to this idea although if we had a connection provider of our own this would be a possibility.
Method #2 problem is that (for some reason) when the activity stream saves an event in the subwiki activity stream then tries to save the same event in the main wiki activity stream, it gets a duplicate key exception.

Is this issue still in the 4.x releases ?

Probably, nobody worked on it since a long time a far as I know.

Hello did someone look whether the JDBC for PG got fixed?

After testing and looking at the jdbc source code, I can say the setCatalog function is still not supported.

It is a post from 2010..

I would really really like Postgres

Note that one issue I had when implementing virtual support for HSQLDB was that DBCP was caching prepared statement and a prepared statement on HSQLDB was containing the schema:

From hibernate.cfg.xml:

<!-- Setting dbcp.ps.maxActive configuration tells DBCP whether to cache Prepared Statement or not.
Note that when using HSQLDB for example, it's important to NOT cache prepared statements because HSQLDB
Prepared Statements (PS) contain the schema on which they were initially created and thus when switching
schema if the same PS is reused it'll execute on the wrong schema! Since HSQLDB does internally cache
prepared statement there's no performance loss. see http://jira.xwiki.org/browse/XWIKI-1740.
Thus we recommend not turning on this configuration for HSQLDB unless you know what you're doing -->

Maybe there's a similar issue with PostGreSQL.

Useful: http://stackoverflow.com/questions/8349267/hibernate-and-multi-tenant-database-using-schemas-in-postgresql

Actually this is what the patch is doing. So this combined with asking dbcp to not cache ps might be a winner...

Basic testing and only tested on PostGreSQL 8.4.12. Needs more testing