Re: Updating default PostgreSQL server encoding from SQL_ASCII to UTF8

[Luis Falcon <falcon%gnuhealth.org@localhost>]

Hi, Greg
El Tue, 08 Sep 2026 13:49:48 -0400
Greg Troxel <gdt%lexort.com@localhost> escribió:
> Luis Falcon <falcon%gnuhealth.org@localhost> writes:
> 
> Looking at pkgsrc now, I see why there is a double -p.
> 
>   @MKDIR@ -p ${pgsql_home
> 
> which is obviously right, except @MKDIR@ expands to "mkdir -p" it
> seems. I have cc'd MAINTAINER who probably doesn't read netbsd-users;
> I'll remove the extra -p as a bug in a week, if it hasn't happened
> and I don't get an explanation why really it's right and I'm confused.
>

Thank you :)
 
> 
> As for "what is postgresql's grand plan for encoding", my guess is the
> answer is "the DBA should understand the issues and choose", and thus
> pkgsrc's plan of doing a stealth init is contrary to their ideas.
> 
> Reading upstream:
> 
>   https://www.postgresql.org/docs/current/app-initdb.html
> 
>   By default, the template database encoding is derived from the
>   locale. If --no-locale is specified (or equivalently, if the locale
> is C or POSIX), then the default is UTF8 for the ICU provider and
>   SQL_ASCII for the libc provider.
> 
> 
> Our pgsql pakcages do not use icu (which makes sense; icu is an ABI
> compat nightmare so IMHO best avoided when reasonable).  

I agree. If PostgreSQL can provide collation support through the OS,
without ICU libraries, it will be simpler and better.

I just tried both C.UTF-8, en_US.UTF-8 and es_ES.ISO8859-1. In all
cases PG refuses to init. unsetting them (or using POSIX) will allow
creating the cluster ( defaulting to SQL_ASCII)

	tolstoy# export LC_ALL="C.UTF-8"
	tolstoy# /etc/rc.d/pgsql oneinitdb
	Initializing PostgreSQL databases.
	initdb: error: invalid locale settings; check LANG and LC_*
	environment variables 
        pg_ctl: database system initialization failed
        tolstoy# unset LC_ALL
	tolstoy# /etc/rc.d/pgsql oneinitdb
	Initializing PostgreSQL databases.
        #

> Upstream --
> for reasons that I don't understand -- thinks that a different default
> is appropriate depending on the use of icu vs [something less than
> icu]. I am not comfortable changing the default for others.
> 

That makes sense. It might be good to place a warning / hint
at the initialization process, pasting the statement for postgres on
SQL_ASCII, so it does not go unseen. Something like:

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the
option -A, or --auth-local and --auth-host, the next time you run
initdb.
initdb: warning:
	The SQL_ASCII setting behaves considerably differently from the
	other settings. When the server character set is SQL_ASCII, the
	server interprets byte values 0–127 according to the ASCII
	standard, while byte values 128–255 are taken as uninterpreted
	characters. No encoding conversion will be done when the
	setting is SQL_ASCII. Thus, this setting is not so much a
	declaration that a specific encoding is in use, as a
	declaration of ignorance about the encoding. In most cases, if
	you are working with any non-ASCII data, it is unwise to use
	the SQL_ASCII setting because PostgreSQL will be unable to help
	you by converting or validating non-ASCII character


Just some thoughts. What do you think?

PS: I'll be doing some tests importing demo GNU Health databases (all
unicode) I have from other systems and report if any issues on this
matter arise.

Thank you!
Luis