****************** Proposal 3/1/2015 ******************** >>>> Fixing the PGSQL database adapter for OpenSim <<<< PostgreSQL (PGSQL) is one of the two professional open source databases supported by OpenSim in grid mode (MSSQL support has been removed as of 0.8.1) The PGSQL adapter is more recent than the MySQL adapter which is the “de facto” used for most OpenSim grids and standalones. It is therefore much less used, tested and debugged. The objective of this funding project is to bring the PGSQL adapter up to the same level as the MySQL adapter, while taking advantage of native data types such as UUID, better BLOB support, and transaction handling and integrity. It also offers performance gains on the same hardware, where in practical OpenSim operation 40–50% improvements have been seen compared ty MySQL. A number of know issues have been identified and is largely logged in the Mantis issue tracking system for OpenSim. http://opensimulator.org/mantis/view_all_bug_page.php There are 4 main areas that needs attention: 1 Table definition and consistency 2 Support for OpenSim built-in groups 3 Support for Search 4 Support for viewer settings 1. TABLE DEFINITION AND CONSISTENCY The tables defined for an OpenSim database running on PostgreSQL needs to be consistent with the table definitions of MySQL databases. There are however differences in that Postgres has build in data types like UUID that is used in a large number of tables. Also, Postgres is more picky about casting the correct type for the field content and this leads to error situations if not properly handled. While database definitions are looked at and fixed, it is also worthwhile to look at adding additional indexes and keys that could speed up database operations, and be of assistance if this project proceeds to a Phase II; Deduplication of database tables. While the built in Postgres (auto)vacuum functionality will trim the size of the tables was records are deleted, the asset table typically gets very big on a grid and is not trimmed (much) by vacuuming. The Following mantis entries exist for this category: 0007456 - Group note generating exception - Note not sent [GROUPS HANDLER] - ROBUST 0007398 - PGSQL module - typecasting issues - Viewer Preferences Panel 0007341 - Truncation error storing asset data PGSQL - Robust 0007360 - 14 tables without primary key PGSQL (also for MySQL - 0004380) 0007347 - user profile table rows created with wrong type PGSQL (may be fixed, but must be checked) 0007344 - Error loading profile data with PGSQL for UserProfilesData in Robust (this one is fixed but is still marked as open in Mantis) 0007350 - MySQL Data Base Schemas - Inconsistency in Name and Description Field Lengths - even if this is for MySQL the same inconsistencies will most likely be found for Postgres. It needs to be fixed for both databases. It is related to 0007341. 1. SUPPORT FOR OPENSIM BUILT-IN GROUPS This has been partly fixed by Bluewall and JustinCC, but there are still issues. In addition to the most pressing one listed below, it needs to be checked that all aspects of group operations correctly writes and reads group data from the tables. The Following mantis entry exist for this category: 0007336 - PGSQL exception Groups Handler - this is mostly fixed, but at the end of the comment section at entry for 2014–12–01 09:55 the exception for searching groups is described. This exception is generated for all tested viewers. 1. SUPPORT FOR SEARCH It needs to be verified that search works correctly with the Basic Search module OpenSimSearch only works with a MySQL database, so table definitions needs to be created for Postgres. The associated php files to be rewritten to also connect to a PostgreSQL database 1. SUPPORT FOR VIEWER SETTINGS Certain viewer settings are persisted to the database, and there are settings that never is written to the PostgreSQL database. The following have been identified, but there are possibly others: User Profile: Show in search user Preferences: Send IM to Email OTHER ISSUES There may be other issues with functions not described above. If there are issues know by individuals in the community, please make sure to create a Mantis entry or add to the existing ones so they can be included in this project.