XWiki Platform
  1. XWiki Platform
  2. XWIKI-1960

Add virtual mode support for postgresql using schemas

    Details

    • Type: Bug Bug
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2 RC1
    • Fix Version/s: 4.5-milestone-1
    • Component/s: Storage
    • Labels:
    • Documentation:
      N/A
    • Similar issues:
      XWIKI-8753Add full virtual mode support for PostGreSQL using the database mode
      XWIKI-9006The schema does not seems to be deleted when deleting a wiki using PostgreSQL in schema mode
      XWIKI-1740Add full xwiki virtual mode support for HSQLDB
      XWIKI-3353Support schemas instead of different databases on DB2
      XWIKI-7644Add support for PostgreSQL as a known DatabaseProduct
      XWIKI-9004PostgreSQL wrongly tries to switch to the schema with database name when in database mode
      XWIKI-2381Support for ldap posixGroup schema
      XWIKI-8244Add full xwiki virtual mode support for Derby
      XWIKI-7232Encoding trouble in page versions (on Postgresql)
      XWIKI-6742Add virtual extensions support

      Description

      Here is what is till missing to make it official support:

      Option 1, db per wiki (mysql style):

      • create the wiki database from XWiki. The workaround is to create the database by hand first and then create the wiki.
      • Proxy the db connection so that when we change the db, the actual TCP connection to the db changes without Hibernate knowing it. (Postgres requires a new TCP connection for a db change.)

      Option 2, schema per wiki (Oracle style):

      • Investigate why some queries query over all schemas rather than only the one which has been selected.

        Issue Links

          Activity

          Hide
          Thomas Mortagne added a comment -

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

          Show
          Thomas Mortagne added a comment - Probably, nobody worked on it since a long time a far as I know.
          Hide
          Tomas added a comment -

          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

          Show
          Tomas added a comment - 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
          Hide
          Vincent Massol added a comment -

          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.

          Show
          Vincent Massol added a comment - 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.
          Hide
          Vincent Massol added a comment - - edited

          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...

          Show
          Vincent Massol added a comment - - edited 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...
          Hide
          Vincent Massol added a comment -

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

          Show
          Vincent Massol added a comment - Basic testing and only tested on PostGreSQL 8.4.12. Needs more testing

            People

            • Assignee:
              Vincent Massol
              Reporter:
              Thomas Mortagne
            • Votes:
              4 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Date of First Response: