Problem/Motivation

The pgsql driver connects and then runs SET NAMES 'UTF8' as a separate statement in Connection::__construct(). This adds one network round trip to every connection. Drupal opens a new database connection on every request, so every request pays this cost.

libpq can set session settings in the connection startup packet through the options DSN parameter. SET NAMES 'UTF8' is equivalent to SET client_encoding TO 'UTF8'. The encoding can be set with options='-c client_encoding=UTF8' in the DSN, with no extra round trip.

Steps to reproduce

Enable statement logging on PostgreSQL. Load any page. The log shows SET NAMES 'UTF8' after each connection start.

Proposed resolution

Append options='-c client_encoding=UTF8' to the DSN in Connection::open(). Remove the exec() call from the constructor.

Remaining tasks

Create a merge request. Review.

API changes

None.

Data model changes

None.

For the committer

The changes to the .gitlab-ci.yml file need to be removed before merging!

Issue fork drupal-3615187

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:

About issue forks

Comments

daffie created an issue. See original summary.

daffie opened merge request !16579

daffie’s picture

daffie commented
Status: Active » Needs review

Added the fix and testing.

daffie’s picture

daffie commented

Disclosure: I have used AI on the PR and the IS.

daffie’s picture

daffie commented
Issue summary: View changes
smustgrave’s picture

smustgrave commented
Status: Needs review » Needs work

For 1 comment on the MR.

daffie’s picture

daffie commented
Status: Needs work » Needs review
longwave’s picture

longwave
he/him
Primary language English
Location UK
commented
Status: Needs review » Needs work

Added a suggestion to the MR.

daffie’s picture

daffie commented
Status: Needs work » Needs review

Rebased the PR and set the updated comment back to the original comment.

smustgrave’s picture

smustgrave commented
Status: Needs review » Reviewed & tested by the community

Not a postgresql expert but believe all feedback on this one has been addressed.

  • longwave committed f7b68ec1 on 11.x 
    perf: #3615187 Set names to UTF8 takes an extra round trip to the...

  • longwave committed 9b33c68d on main 
    perf: #3615187 Set names to UTF8 takes an extra round trip to the...
longwave’s picture

longwave
he/him
Primary language English
Location UK
commented
Version: main » 11.x-dev
Status: Reviewed & tested by the community » Fixed

Committed and pushed 9b33c68d4b3 to main and f7b68ec152a to 11.x. Thanks!

Debated about backporting it but not sure it's worth it or a change we should be making in a patch release, it's technically not a bug fix.

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.