giving a

The server reports the following message when attempting to create the database: SQLSTATE[42809]: Wrong object type: 7 ERROR: invalid locale name: "en_US_POSIX.utf8".

So Locale::getDefault() sometimes seems to return a locale that isn't supported at all on the machine. A lot of locale errors with PostgreSQL seem to stem from the locale not being installed on the machine. But en_US_POSIX which is a special case, that definitely can't work.

Didn't find exhaustive documentation about it apart from ICU Locale and PostgreSQL Character Set Support, so there might be even more character that might generally not be supported.

We could try to create the database and catch an exception if there is any, and if that's the case, then try again with 'en_US'. We should also change the locale detection and perhaps even try to determine whether the database will be local or remote.

Issue fork drupal-2010368

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Pancho’s picture

Status: Needs work » Needs review
FileSize
1.35 KB

Capitalization of the 'utf8' encoding seems to be more standard. Rolled it in but if in doubt it doesn't really matter.
Also minor documentation fix: we're not setting the database active unlike MySQLs 'USE' statement, this is not necessary in PostgreSQL.

With this patch, database creation on PostgreSQL works now, and seems to be fine.

Damien Tournoud’s picture

Well, this is bogus because Locale::getDefault() returns the ICU locale, which is not the same thing as the POSIX locale.

Here we are talking about collation, not character set, which is always UTF08 in our case.

There are no perfectly safe way of detecting the proper locale. We *could* use setlocale(LC_COLLATE, 0) to get the current value of LC_COLLATE, and use that if it is actually a locale using UTF-8 encoding. If that fails, we have no other good choice then to assume that en_US.utf8 is available.

Damien Tournoud’s picture

Status: Active » Needs work
Pancho’s picture

Thanks for your suggestion which definitely makes more sense.
I hit the different locale definitions but wasn't aware of setlocale() being more correct here. The patch writers and reviewers in #203955: Create database at installation time obviously weren't either. So if anything is bogus, it's the original implementation.
Also, noone said we were talking about character sets. We're talking about both character classification (LC_CTYPE) and collation order (LC_COLLATE).
To be correct, we should probably determine LC_CTYPE and LC_COLLATE separately.

Status: Needs review » Needs work
Issue tags: -PostgreSQL, -character sets, -intl

The last submitted patch, create_postgresql_db-2010368-4.patch, failed testing.

Pancho’s picture

Status: Needs work » Needs review
Issue tags: +PostgreSQL, +character sets, +intl

Previous result: FAILED: [[SimpleTest]]: [MySQL] 55,992 pass(es), 3 fail(s), and 3 exception(s).

How can this patch fail? We're not changing anything at all for MySQL.

#4: create_postgresql_db-2010368-4.patch queued for re-testing.

fvideon’s picture

Both of the setlocale calls return 'C' on my system, but this still fails.

CREATE DATABASE foo WITH 
  TEMPLATE template0 
  ENCODING='UTF8' 
  LC_CTYPE='C.UTF-8' 
  LC_COLLATE='C.UTF-8'

ERROR: invalid locale name: "C.UTF-8"

My postgresql installation is the 9.2.4 windows binary from EnterpriseDB with minimal configuration.

It works if I leave out the '.UTF8' for both LC_TYPE and LC_COLLATE, leaving just 'C'.

Also 'en_US.UTF-8' fails with the same error. It wants instead 'English_United States'.

steinmb’s picture

PHP 5.4.13 (cli) (built: Apr 16 2013 11:46:33)
PostgreSQL 9.2.4
Both installed on OSX 10.8x with homebrew

CREATE DATABASE foo WITH
  TEMPLATE template0
  ENCODING='UTF8'
  LC_CTYPE='C.UTF-8'
  LC_COLLATE='C.UTF-8';

Fails same way as in #7
ERROR: invalid locale name: "C.UTF-8"

Pancho’s picture

Status: Needs review » Needs work

I know, did some more research, but will be coming back to this only next week.

steinmb’s picture

FileSize
2.38 KB

Thanx :)
Ref. #7 - Attaching a list over available locale (get it by doing 'locale -a') on a typical OS X 10.8.x.

steinmb’s picture

#2001350: [meta] Drupal cannot be installed on PostgreSQL now have a working patch. Any progress on this?

Pancho’s picture

Not API-relevant, so I'll be coming back to this one only after API freeze next week, sorry...

Damien Tournoud’s picture

I recommend removing support for automatically creating a PostgreSQL database.

Pancho’s picture

Why that? If we don't want to bother with it, we could remove locale detection. The rest would be fixed with patch #1.
If you want we can fix the $this->exec bug now, remove the locale detection and possibly reimplement it properly in a followup.

Damien Tournoud’s picture

Without locale detection, how are you going to pick the locale? We need a UTF-8 locale, that need to be available *on the PostgreSQL server* (which is likely different then the web server PHP is running on). There is no guarantee that en_US.UTF8 is available (and if you installed, let's say, Debian in a non-american language it will *not* be), so what do we do? We cannot really do locale detection remotely...

Pancho’s picture

The C "non-locale" is always available, so we have something to fall back to.
Still I think we should try harder getting this right, and IMHO this would also mean, giving the admin control or at least feedback on the locale to be used for collation.
IMHO, this should also be extended to MySQL and SQLite collations. One of our focusses in D8 finally is (multi)languages, and here we're clearly lagging behind our huge efforts on application level. Would need extensive research that I'm ready to do. But I believe that this all isn't API-freeze relevant, so can wait another two or three days. Am I wrong?

fvideon’s picture

+1 for keeping support for automatically creating the database, AND providing a disclaimer about cases where this should be done manually.

Damien Tournoud’s picture

The C "non-locale" is always available, so we have something to fall back to.

C is not a UTF-8 locale, as far as I know. So this is not going to fly.

Still I think we should try harder getting this right, and IMHO this would also mean, giving the admin control or at least feedback on the locale to be used for collation.
IMHO, this should also be extended to MySQL and SQLite collations. One of our focusses in D8 finally is (multi)languages, and here we're clearly lagging behind our huge efforts on application level. Would need extensive research that I'm ready to do. But I believe that this all isn't API-freeze relevant, so can wait another two or three days. Am I wrong?

That's a way broader thing to do, and it has consequences across the board (as designing a database schema to be truly multilingual is going to be really hard, and most people are going to use Search API / Solr anyway), so I'm afraid this will have to wait for 9.x.

dlu’s picture

Is it possible to detect if the PostgreSQL instance is local, so that – in that case at least – we could do locale discovery?

Seems like that would make life a bit nicer and saner in some instances.

dlu’s picture

Issue summary: View changes

.

mradcliffe’s picture

Added related task.

bzrudi71’s picture

If one decides to go with Drupal and PostgreSQL I'm pretty sure he/she knows how to create a DB from command line first. Not that I'm against it, I like the idea, but we have still tons of work todo to make PostgreSQL install and passing all D8 tests.
So I vote for postpone to 9.x as Damien Tournoud suggested to remove this from the current long 8.x list of issues.

amontero’s picture

lokapujya’s picture

Status: Needs work » Needs review
FileSize
2.21 KB

Returned C, and created the database for me. Rerolled.
Mac OS X 10.9.4
PHP 5.5.17
Postgres 9.35

jhedstrom’s picture

Version: 8.0.x-dev » 8.1.x-dev
Status: Needs review » Postponed

Folks seem a bit divided on if this is even possible. Regardless, I think it's probably too late for 8.0.

bzrudi71’s picture

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.0-beta1 was released on March 2, 2016, which means new developments and disruptive changes should now be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.0-beta1 was released on August 3, 2016, which means new developments and disruptive changes should now be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Pancho’s picture

Category: Bug report » Task
Status: Postponed » Needs work

In the end, we may move this one to the 9.x queue at some point late in D8's dev cycle. But for now there's no reason not to implement it in a future minor 8.x version, as long as a solid implementation is found. Also, the D7 backport of #203955: Create database at installation time is postponed on this one per webchick.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.0-alpha1 will be released the week of January 30, 2017, which means new developments and disruptive changes should now be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.0-alpha1 will be released the week of July 31, 2017, which means new developments and disruptive changes should now be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.0-alpha1 will be released the week of January 17, 2018, which means new developments and disruptive changes should now be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.7.x-dev

Drupal 8.6.0-alpha1 will be released the week of July 16, 2018, which means new developments and disruptive changes should now be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.7.x-dev » 8.8.x-dev

Drupal 8.7.0-alpha1 will be released the week of March 11, 2019, which means new developments and disruptive changes should now be targeted against the 8.8.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 9.1.x-dev » 9.2.x-dev

Drupal 9.1.0-alpha1 will be released the week of October 19, 2020, which means new developments and disruptive changes should now be targeted for the 9.2.x-dev branch. For more information see the Drupal 9 minor version schedule and the Allowed changes during the Drupal 9 release cycle.

Version: 9.2.x-dev » 9.3.x-dev

Drupal 9.2.0-alpha1 will be released the week of May 3, 2021, which means new developments and disruptive changes should now be targeted for the 9.3.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.0-rc1 was released on November 26, 2021, which means new developments and disruptive changes should now be targeted for the 9.4.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.0-alpha1 was released on May 6, 2022, which means new developments and disruptive changes should now be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 10.1.x-dev

Drupal 9.5.0-beta2 and Drupal 10.0.0-beta2 were released on September 29, 2022, which means new developments and disruptive changes should now be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 10.1.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch, which currently accepts only minor-version allowed changes. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

fjgarlin made their first commit to this issue’s fork.

fjgarlin’s picture

Status: Needs work » Needs review

I ran into the same issue while converting DrupalCI to GitlabCI and testing PostgreSQL.

The new MR (based on #23) for 11.x is https://git.drupalcode.org/project/drupal/-/merge_requests/4627

See the before and after for "PhpUnitCliTest":
* Before (error): https://git.drupalcode.org/project/gitlab_ci_testbed_for_drupal_core/-/j...
* After (fixed): https://git.drupalcode.org/project/gitlab_ci_testbed_for_drupal_core/-/j...

poker10’s picture

Status: Needs review » Needs work
Issue tags: +Needs issue summary update

Thanks for working on the MR @fjgarlin!

I think the issue summary needs to be updated, because some parts were fixed in other issues in the meantime (for example the broken exec call was fixed here: #2259947: Minor bug fixes in database system). Therefore this issue should only focus on detecting a locale (if possible).

The points raised in #15 and #19 are good, and if we are making changes, I think it will be the best to detect, if the database is run locally. If yes, we can use the approach proposed in the MR currently (try to detect locale and if the creation fail, then fallback to en_US). But if the database is not run locally, I think there is no point doing that "blind" attempt with locale from different system, as the separate database server could be entirely different. I propose that in case the database is not run locally, we skip the locale detection and try the luck with en_US only.

What do you think?

fjgarlin’s picture

Issue summary: View changes

Updated issue summary to reflect the part that needs fixing. Thanks for the suggestion.

Re detecting if the database runs locally, the DB server could use an alias that "looks" and still be local. And in any case, I think the approach shouldn't change (see below paragraph).

I think that the same approach would work well for both local and remote DBs. We should try to detect the web server locale and then try to set that value for the database (whether it's local or remote), and if it fails then fallback to "en_US" (whether it's local or remote). I know that if the DB is remote we'll be trying blindly, but why not try with the desired value first, and otherwise with the fallback (which will actually be a guess as well).

I think the MR can be simplified a little, so I'll iterate over it and put it again as "Need review". If after reading the above and seeing the MR you still think that we need to change the approach just put it again in "Need work" and share your thoughts.

fjgarlin’s picture

Status: Needs work » Needs review
poker10’s picture

Thanks @fjgarlin for updating the IS, MR and your explanation! I think that this is a good point:

the DB server could use an alias that "looks" and still be local.

Then I think that it could probably stay as it is, without complicating things. Let's keep it in NR to get more reviews.

catch’s picture

Component: database system » postgresql db driver
Category: Task » Bug report
Priority: Normal » Critical
Issue tags: +Needs subsystem maintainer review

This is blocking gitlab CI. Could use review from the postgres maintainers.

daffie’s picture

Status: Needs review » Reviewed & tested by the community
Issue tags: -Needs subsystem maintainer review

The MR looks good to me.
I am not totally sure that we cannot create a PostgreSQL database with ctype and collate that will not work with Drupal. That said, we do not get any complains on the issue queue about it. Therefor for me it is not a big problem. When we get complains about this we create a better fix + testing. For now, lets fix Gitlab CI. That is far more important!
The testbot is green.
For me it is RTBC.

  • catch committed 9ff9e5d0 on 10.1.x
    Issue #2010368 by fjgarlin, Pancho, poker10, daffie: Installer can't...

  • catch committed 0a8a9dcb on 11.x
    Issue #2010368 by fjgarlin, Pancho, poker10, daffie: Installer can't...
catch’s picture

Version: 11.x-dev » 10.1.x-dev
Status: Reviewed & tested by the community » Fixed

Committed/pushed to 11.x and cherry-picked to 10.1.x, thanks!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.