? ? Pending

User tests: Successful: Unsuccessful:

avatar mbabker
mbabker
13 May 2018

Pull Request for Issue #20272

Summary of Changes

  • Adds a database driver for PDO PostgreSQL support
  • Deprecates the database driver supporting PHP's ext/pgsql
  • Updates relevant 4.0 compatibility checks

Testing Instructions

In an environment with PostgreSQL database support, make sure PHP has PDO support enabled with the PostgreSQL engine available (if you can already run native ext/pgsql then you should only need to enable the PDO support if it's not there already). Validate Joomla functions on-par when running PDO PostgreSQL as it relates to the native PostgreSQL driver.

Documentation Changes Required

Update 4.0 compatibility references.

avatar mbabker mbabker - open - 13 May 2018
avatar mbabker mbabker - change - 13 May 2018
Status New Pending
avatar joomla-cms-bot joomla-cms-bot - change - 13 May 2018
Category Administration com_admin com_config Language & Strings Installation Libraries Postgresql
avatar mbabker mbabker - change - 13 May 2018
Labels Added: ? ?
avatar SniperSister SniperSister - test_item - 14 May 2018 - Tested unsuccessfully
avatar SniperSister
SniperSister - comment - 14 May 2018

I have tested this item ? unsuccessfully on 6de3bf5

* Creating a category throws a "There is no active transaction" exception, apparently being related because the "unlock" call (commiting a transaction) in TableNested is being made after the unlock has already being called in Table

  • Smart Search Batch Index Button breaks, because com_finder adds serialized PHP objects to the DB which include null bytes. It "worked" in postgresql because that silently truncated null bytes, under PDO it breaks. Proposed solution: run base64 on it

Besides that: works great!


This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/20386.

avatar mbabker
mbabker - comment - 15 May 2018

Smart Search Batch Index Button breaks, because com_finder adds serialized PHP objects to the DB which include null bytes. It "worked" in postgresql because that silently truncated null bytes, under PDO it breaks. Proposed solution: run base64 on it

And that becomes problematic without introducing driver specific behavior since these serializations are stored to the database. Maybe for 4.0 we can have a hard "changed serialization/encoding format" break in the data structure and force sites to have their search index to be rebuilt, but I don't think that's a fair requirement for a 3.x release. Be it implementing Serializable and cleaning up how the FinderIndexerResult object is actually serialized or adding a base64 wrapper, we should do something.

It actually also complicates moving from native PostgreSQL to PDO because you'd get bitten by this driver specific switch anyway.

Can't say I'm a fan of trying to code a mitigation layer in, to me there isn't a "good" option here that doesn't expose some kind of B/C or migration issue.

avatar SniperSister
SniperSister - comment - 16 May 2018

force sites to have their search index to be rebuilt, but I don't think that's a fair requirement for a 3.x release.

Do we actually need to do this? A transparent "does a plain unserialize work? No, then try a base64 decode first" should do the job withouth rebuilt, right?

It actually also complicates moving from native PostgreSQL to PDO because you'd get bitten by this driver specific switch anyway.

I disagree on that part: smart search is broken in both Postgres drivers right now, just nobody noticed.

avatar csthomas
csthomas - comment - 16 May 2018

Joomla does not support the escape function for binary data. It may be time to add it.

Example for postgresql (including PDO):

	/**
	 * Quotes a binary string to database requirements for use in database queries.
	 *
	 * @param   mixed  $text  A binary string to quote.
	 *
	 * @return  string  The binary quoted input string.
	 *
	 * @since   __DEPLOY_VERSION__
	 */
	public function quoteBinary($text)
	{
		return 'E\'\\\\x' . bin2hex($text) . '\'';
	}

More examples for other DB you can find on csthomas/joomla-cms@501b92a

avatar alikon
alikon - comment - 30 Jul 2018

a lot of changes have been happend in the meantime especially in the com_finder
so can we reconsider this one for 4.x?

avatar mbabker
mbabker - comment - 30 Jul 2018

PDO PostgreSQL is already in 4.0, and native PostgreSQL is already out.

The intent here was to make the PDO variant available for 3.x to simplify the migration path (being able to switch platforms pre-upgrade versus being unable to do an in-place upgrade because you won't have the same database driver options available on both ends of it).

This can slip to 3.10 if need be, but really we need to make sure that either the PDO option is in 3.x otherwise the 17 PostgreSQL users are going to come at us with pitchforks and chains.

avatar alikon
alikon - comment - 30 Jul 2018

understood, and sorry to be 1 of 17, but not in my plan to came back to you

with pitchforks and chains

my mostly feeling is to give up, right now

avatar alikon
alikon - comment - 30 Jul 2018

we deserve a mysql world only ?

avatar alikon
alikon - comment - 31 Jul 2018

testing with a fresh 3.9-dev + this pr unable to install
screenshot from 2018-07-31 10-39-07

avatar alikon
alikon - comment - 31 Jul 2018

starting with a fresh 3.9-dev with postgresql, and after install, applying this pr and then switch to PDO Postgresql via Global Configuration->Server
screenshot from 2018-07-31 11-28-32
....... still testing, but till now not able to found big issues
so the question is :
it's all about this one 1d8a064#diff-7acfc2b93c1bae5b5114fc39c2c715ff ?

avatar alikon
alikon - comment - 31 Jul 2018

com_finder, trying to index.....
screenshot from 2018-07-31 11-51-32

avatar wilsonge
wilsonge - comment - 31 Jul 2018

OK I think com_finder is broken in both postgres versions. if @mbabker can solve the installation issue you found let's get this merged and work through issues as we come across them :)

avatar alikon
alikon - comment - 31 Jul 2018

i think com_finder is not perfect even on mysql, so yes, i agree with you let's fix the installation for now ;)

avatar mbabker
mbabker - comment - 31 Jul 2018

Rebased the PR, tested installing again, works fine here. This is on a PHP 7.1 and PostgreSQL 10.0 build.

avatar alikon
alikon - comment - 1 Aug 2018

tested installing and works now, with PHP 7.2.7 and Postgresql 9.6.8

avatar alikon alikon - test_item - 1 Aug 2018 - Tested successfully
avatar alikon
alikon - comment - 1 Aug 2018

installing a multilanguage site fail with : There is no active transaction

avatar alikon
alikon - comment - 1 Aug 2018

the same message There is no active transaction when you try to create a new category

avatar alikon
alikon - comment - 2 Aug 2018

i've made this pr mbabker#27 against your branch mbabker:pdo-pgsql

should fix the nested transaction issue (create/delete a category)

btw a multilanguage site even if it is installable now, still have multiple issues

for example in Control Panel
screenshot from 2018-08-02 11-22-36

avatar mbabker
mbabker - comment - 6 Aug 2018

Transaction fix merged, anyone aside from the two of us who can test and validate this thing?

avatar SniperSister
SniperSister - comment - 7 Aug 2018

Tested, works fine

avatar franz-wohlkoenig franz-wohlkoenig - alter_testresult - 7 Aug 2018 - SniperSister: Tested successfully
avatar franz-wohlkoenig
franz-wohlkoenig - comment - 7 Aug 2018

Altered successfully Test by @SniperSister at Issue Tracker.

avatar wilsonge wilsonge - change - 7 Aug 2018
Status Pending Fixed in Code Base
Closed_Date 0000-00-00 00:00:00 2018-08-07 09:05:12
Closed_By wilsonge
avatar wilsonge wilsonge - close - 7 Aug 2018
avatar wilsonge wilsonge - merge - 7 Aug 2018
avatar wilsonge
wilsonge - comment - 7 Aug 2018

Given the number of Postgres users we have is so low, with @SniperSister and @alikon 's tests I'm merging this

Add a Comment

Login with GitHub to post a comment