Disclaimer: I use PostgreSQL and PostgreSQL alone for all my databasing. This is not some kind of "hate".

This is something which has bothered me for many years, but at this point, in the year 2020, it's just ridiculous.

Source: https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

The application_name can be any string of less than NAMEDATALEN characters (64 characters in a standard build). It is typically set by an application upon connection to the server. The name will be displayed in the pg_stat_activity view and included in CSV log entries. It can also be included in regular log entries via the log_line_prefix parameter. Only printable ASCII characters may be used in the application_name value. Other characters will be replaced with question marks (?).

(Emphasis mine.)

So, the self-proclaimed "World's Most Advanced Open Source Relational Database" doesn't support Unicode/UTF-8 for this specific thing, but everywhere else. It forces me (and my potential boss, if I had one) to see a bunch of "???????????" all over the control panels, such as in pgAdmin 4.

I've heard that this is supposedly motivated by "security concerns". Something about the application_name being set at a stage before they have set up the "Unicode engine" or something along those lines. I don't understand that. If that really is the case, sure, force ASCII-only for the application_name when it's set during the initial connect, but do allow Unicode/UTF-8 when setting application_name later, after the connection has already been established, in a normal query using the "SET" keyword!

I don't get it. Why am I forced to see these ugly question marks everywhere just because my language happens to contain more than A-Za-z? Even English-centric databases might have a script related to other languages or a service with a fancy name, so even if the PG project is US-centric, that still doesn't excuse this.

  • 5
    Strongly recommend that you ask this over on the Postgres development mailing list(s). Not all questions are best asked here. Jan 31 '20 at 9:48
  • 8
    And in a less aggressive tone. Jan 31 '20 at 9:53
  • @Colin 't Hart: They made it so difficult to read or post to that mailing list that nobody ever will, so that's not gonna help. Jan 31 '20 at 9:55
  • 1
    I like Laurenz answer, but to add to it, UTF-8 is still not universal, Windows still does not force UTF-8, no OS forces the use of UTF-8. Having an application support so many different OS's and each OS decides to do things differently causes a mess. I do lots of things with Asia and have constant issues with computers missing an Encoder and the data being corrupted due it. ASCII is the only universal encoding method used today, till UTF-8 becomes universal ASCII is what we get. Another issue is missing fonts to display the character, many people confuse missing fonts as corruption
    – zsheep
    Jan 31 '20 at 14:07
  • @RussellPerry: "They made it so difficult to read or post to that mailing list that nobody ever will" - what's hard about sending an email or reading an email? Sep 21 '20 at 17:20

One of the dreaded “why” questions... and I thought people outgrow that at some age...

Anyway, I can answer that one, and it is a good example of how you should approach questions like that. The point here is that with PostgreSQL, a free open source software that is developed by many people around the globe, all discussions and decisions are open for everyone to see.

First, use git blame on the source to find the commit that added the documentation line and the feature. You'll easily find 8217cfbd991856d25d73b0f7afcf43d99f90b653 from 2009-11-29.

Next, you search the mailing list archives around that time for the discussion and the submitted patches. You will find this, this and this thread, which contain an exhaustive discussion.

Relevant points made there are:

  1. By Tom Lane in this message:

    Thinking about it more, it should be sufficient if we can ensure that the value is in the database encoding; logging of statements will already cause pretty much any legal DB-encoded string to be written to the log, so if you have a problem with that then you've already got a problem to fix.

    This is no issue for an ordinary SET, but AFAIR we do not have a good story for handling non-ASCII stuff arriving within the initial connection request packet. It might be time to try to do something about that. Or we could just restrict those values to ASCII.

  2. Later, in this message, he found another, graver consideration:

    One more question: Per my reading of the discussion (which very well might be flawed), wasnt the plan to limit the availale characters in the application name to ascii?

    That was suggested, but I thought the eventual outcome was to not bother.

    I think that's really essential, not optional. The proposed patch will transfer the application name from one backend to another without any encoding conversion. If it contains non-ASCII characters that will result in injection of badly-encoded data inside the backend, which is something we have been trying hard to avoid in recent versions.

    The only other thing you could do about this would be to try to convert the data from the source backend's encoding to the target's. Which would lead to assorted failure scenarios when no conversion is possible.

    ISTM restricting the name to ASCII-only is the most reasonable tradeoff. Of course, as a speaker of English I may be a bit biased here --- but doing nothing about the issue doesn't seem acceptable.

That is actually a very good reason.

In pg_stat_statements, where application_name is displayed, you see sessions from all databases in the cluster. Now different databases can have different encodings, so if you allowed non-ASCII characters in the database encoding, a SELECT on pg_stat_activity might return a string that is invalid in the database where the SELECT is running. This is unacceptable in PostgreSQL, which is unforgiving about corrupt data.

So, in a way, that limitation is because PostgreSQL is the World's Most Advanced Open Source Relational Database, and certainly one which has high standards of data integrity.

  • +1 for the top notch sleuthing Feb 5 '20 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.