Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, Jun 5, 2025 at 11:07 AM Dominique Devienne wrote: > So... It is possible to have the SAME command on Windows and Linux, > which yields the SAME datcollate and datctype values??? > So far, such a command eludes me, I'm afraid. --DD So I tried to be explicit about lc_collate and lc_ctype too. OK on Linux, KO on Windows... Windows: ddevienne=> create database "dd_v168b" encoding 'UTF8' builtin_locale 'C.UTF-8' lc_collate 'C.UTF-8' lc_ctype 'C.UTF-8' ddevienne-> locale_provider 'builtin' template template0; ERROR: invalid LC_COLLATE locale name: "C.UTF-8" HINT: If the locale name is specific to ICU, use ICU_LOCALE. Linux: ddevienne=> create database "dd_v168c" encoding 'UTF8' builtin_locale 'C.UTF-8' lc_collate 'C.UTF-8' lc_ctype 'C.UTF-8' ddevienne-> locale_provider 'builtin' template template0; CREATE DATABASE ddevienne=> select datlocprovider, datlocale, datcollate, datctype from pg_database where datname = 'dd_v168c'; datlocprovider | datlocale | datcollate | datctype +---++-- b | C.UTF-8 | C.UTF-8| C.UTF-8 (1 row) AFAIK, C and C.UTF-8 are NOT the same thing, for collations. It is indeed super confusing Jeff. I'm lost. How can I get a datlocprovider | datlocale | datcollate | datctype +---++-- b | C.UTF-8 | C.UTF-8| C.UTF-8 database on Windows *AND* Linux? If not possible using the same SQL (but why...), using what SQL?
Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, 2025-06-05 at 10:53 +0200, Dominique Devienne wrote: > Thanks Laurenz. Indeed, Using LOCALE vs BUILTIN_LOCALE matters. > > On Linux, no error unlike on Windows (still inconsistent there IMHO), > but the result is slightly different for datcollate and datctype (C vs en_US), > while the same for datlocprovider and datlocale, what I looked at. > > Thus I kinda persist that there *is* a portability issue here. Perhaps, if omitting BUILTIN_LOCALE actually fails on Windows (I cannot test it, no Windows nearby). > Also, note what the doc says: > > If locale_provider is builtin, then locale or builtin_locale must be > specified and set to either C or C.UTF-8. > > It clearly says "locale or builtin_locale", emphasis on the OR. You are right, and that's how it works on Linux. BUILTIN_LOCALE is not required. > So two issues here. > 1) the doc is wrong or misleading on this. Perhaps the problem is in the implementation, not the documentation. > 2) the same command works on Linux, but not Windows. Unfortunately I am not in a position to get to the bottom of that. In principle, it is acceptable for commands to fail on Windows and work elsewhere, if operating system things like collations are involved. But I agree that the "builtin" locale provider should work the same everywhere. Yours, Laurenz Albe
Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, Jun 5, 2025 at 2:40 PM Daniel Verite wrote: > Dominique Devienne wrote: > > On Linux, no error unlike on Windows (still inconsistent there IMHO), > > but the result is slightly different for datcollate and datctype (C vs > > en_US), > > while the same for datlocprovider and datlocale, what I looked at. > > > > Thus I kinda persist that there *is* a portability issue here. > > "datcollate" and "datctype" refer to operating system locale names. > > locale 'C.UTF-8' or lc_collate 'C.UTF-8' lc_ctype 'C.UTF-8' > cannot work on Windows because Windows does not have a locale > named C.UTF-8, whereas a Linux system does (well at least recent > Linuxes. Some old Linuxes don't). But isn't the point of the new-in-v17 builtin provider is to be system independent??? > What you are seeing is the effect of OS locales not being portable > across systems. That's confusing but not a Postgres bug. Thus builtin SHOULD be portable IMHO. --DD
Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, Jun 5, 2025 at 5:01 PM Daniel Verite wrote: > Dominique Devienne wrote: > > > locale 'C.UTF-8' or lc_collate 'C.UTF-8' lc_ctype 'C.UTF-8' > > > cannot work on Windows because Windows does not have a locale > > > named C.UTF-8, whereas a Linux system does (well at least recent > > > Linuxes. Some old Linuxes don't). > > > > But isn't the point of the new-in-v17 builtin provider is to be system > > independent??? > > Yes, definitely. > > But suppose your database has an extension that calls local-dependent > code, such as strxfrm() [1] for instance. > > The linked MSVC doc says: > > "The transformation is made using the locale's LC_COLLATE category > setting. For more information on LC_COLLATE, see setlocale. strxfrm > uses the current locale for its locale-dependent behavior" > > But what will be the value in LC_COLLATE when this extension code > is running in a database using the builtin provider? > It's the value found in pg_database.datcollate that was specified > when creating the database with the lc_collate or locale option. > > The builtin provider routines are used for code inside Postgres > core, but code outside its perimeter can still call libc functions > that depend on lc_collate and lc_ctype. So you're saying datcollate and datctype from pg_database are irrelevant to PostgreSQL itself, and only extensions might be affects? Which implies that I shouldn't worry about those differences? Am I reading you right? --DD
Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, 2025-06-05 at 10:53 +0200, Dominique Devienne wrote:
> If locale_provider is builtin, then locale or builtin_locale must be
> specified and set to either C or C.UTF-8.
>
> It clearly says "locale or builtin_locale", emphasis on the OR.
>
> So two issues here.
> 1) the doc is wrong or misleading on this.
The code in dbcommands.c:createdb():
if (localeEl && localeEl->arg)
{
dbcollate = defGetString(localeEl);
dbctype = defGetString(localeEl);
dblocale = defGetString(localeEl);
}
if (builtinlocaleEl && builtinlocaleEl->arg)
dblocale = defGetString(builtinlocaleEl);
if (collateEl && collateEl->arg)
dbcollate = defGetString(collateEl);
if (ctypeEl && ctypeEl->arg)
dbctype = defGetString(ctypeEl);
if (iculocaleEl && iculocaleEl->arg)
dblocale = defGetString(iculocaleEl);
So LC_COLLATE, LC_CTYPE, and BUILTIN_LOCALE all fall back to LOCALE if
they aren't set. On windows, it fails because LC_COLLATE and LC_CTYPE
fall back to LOCALE, which is "C.UTF-8", which doesn't exist.
(I know the CREATE DATABASE command is confusing, but it grew
historically and we needed to support previously-working commands.)
If you have specific doc suggestions to clarify this, please let me
know.
> 2) the same command works on Linux, but not Windows.
As long as we accept the libc provider, or allow the user to set
LC_COLLATE/LC_CTYPE, then there will be some commands that succeed on
some platforms and fail on others. Even with ICU, there may be versions
that accept a locale and versions that don't.
I'd like to make libc less "special" so that users who don't want to
use it aren't confronted with errors about things that don't matter to
them. I welcome suggestions that can move us closer to that goal
without breaking previously-working commands.
Regards,
Jeff Davis
Re: LOCALE C.UTF-8 on EDB Windows v17 server
Dominique Devienne wrote: > > locale 'C.UTF-8' or lc_collate 'C.UTF-8' lc_ctype 'C.UTF-8' > > cannot work on Windows because Windows does not have a locale > > named C.UTF-8, whereas a Linux system does (well at least recent > > Linuxes. Some old Linuxes don't). > > But isn't the point of the new-in-v17 builtin provider is to be system > independent??? Yes, definitely. But suppose your database has an extension that calls local-dependent code, such as strxfrm() [1] for instance. The linked MSVC doc says: "The transformation is made using the locale's LC_COLLATE category setting. For more information on LC_COLLATE, see setlocale. strxfrm uses the current locale for its locale-dependent behavior" But what will be the value in LC_COLLATE when this extension code is running in a database using the builtin provider? It's the value found in pg_database.datcollate that was specified when creating the database with the lc_collate or locale option. The builtin provider routines are used for code inside Postgres core, but code outside its perimeter can still call libc functions that depend on lc_collate and lc_ctype. [1] https://learn.microsoft.com/en-us/cpp/c-runtime-library/reference/strxfrm-wcsxfrm-strxfrm-l-wcsxfrm-l?view=msvc-170 Best regards, -- Daniel Vérité https://postgresql.verite.pro/
Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, 2025-06-05 at 11:34 +0200, Dominique Devienne wrote: > On Thu, Jun 5, 2025 at 11:07 AM Dominique Devienne > wrote: > > So... It is possible to have the SAME command on Windows and Linux, > > which yields the SAME datcollate and datctype values??? > > So far, such a command eludes me, I'm afraid. --DD > > So I tried to be explicit about lc_collate and lc_ctype too. > OK on Linux, KO on Windows... LC_COLLATE and LC_CTYPE (datcollate and datctype) are platform- dependent (handled by libc) so it won't be possible for those to have the same meaning across different systems. The only locale that's guaranteed to be available, AFAIK, is "C". Even some other unix-like operating systems don't support "C.UTF-8". The good news is that LC_COLLATE and LC_CTYPE don't have much effect if you are using a different provider like "builtin" or ICU. They affect the server's LC_COLLATE and LC_CTYPE environment, which does have a few effects, but in general we are trying to avoid cases where those matter a lot. To maintain consistency across platforms, use LC_COLLATE=C and LC_CTYPE=C when creating a database; along with the builtin provider and UTF-8 as you are already doing. Regards, Jeff Davis
Re: LOCALE C.UTF-8 on EDB Windows v17 server
Dominique Devienne wrote: > So you're saying datcollate and datctype from pg_database are > irrelevant to PostgreSQL itself, and only extensions might be affects? Almost. An exception that still exists in v18, as far as I can see [1], is the default full text search parser still using libc functions like iswdigit(), iswpunct(), iswspace()... that depend on LC_CTYPE. So you could see differences between OSes in tsvector contents in a database with the builtin provider. Unless using LC_CTYPE=C. But then the parsing is suboptimal, since the parser does not recognize Unicode fancy punctuation signs or spaces as such. Personally I would still care to set LC_CTYPE to a reasonable UTF-8 locale with v17 or v18. [1] https://doxygen.postgresql.org/wparser__def_8c.html#a420ea398a8a11db92412a2af7bf45e40 Best regards, -- Daniel Vérité https://postgresql.verite.pro/
Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, 2025-06-05 at 15:07 +0200, Dominique Devienne wrote: > But isn't the point of the new-in-v17 builtin provider is to be > system > independent??? Yes, a major part of the builtin provider is complete consistency across platforms for the entire collation system -- anything affected by the database default collation or a COLLATE clause, including comparisons, casing behavior, pattern matching, etc. New major versions of Postgres may update Unicode, but those updates will never affect comparisons in the builtin C.UTF-8 locale; and will only affect other behaviors (like casing) subject to the (rather strict) Unicode stability policy[1]. Regarding datcollate and datctype: those affect the LC_COLLATE and LC_CTYPE environment variables, and Postgres does a setlocale() upon a new database connection. That only affects libc functions like strcoll(), so it won't affect the builtin provider or ICU which don't use strcoll(). You're right to ask why those matter at all, then. It's hard for me to guarantee that datcollate/datctype won't affect some other part of the system or an extension (I see that Daniel offered some more details). I'd like to force LC_COLLATE=C and LC_CTYPE=C, and then there'd be no question, but I won't promise when that will happen. I'd suggest just forcing those to "C" in your database. Regards, Jeff Davis [1] https://www.unicode.org/policies/stability_policy.html
Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, Jun 5, 2025 at 3:01 AM Laurenz Albe wrote: > On Wed, 2025-06-04 at 14:23 +0200, Dominique Devienne wrote: > > The command I'm using (from a libpq trace) is: > > > > create database "dd_v168" encoding 'UTF8' locale 'C.UTF-8' > > locale_provider 'builtin' template template0 > > > > On Windows, I'm getting > > > > 2025-06-04 14:07:41.227419 B 155 ErrorResponse S "ERROR" V "ERROR" C > > "42809" M "invalid LC_COLLATE locale name: "C.UTF-8"" H "If the locale > > name is specific to ICU, use ICU_LOCALE." F "dbcommands.c" L "1057" R > > "createdb" \x00 > > Pilot error. If you use "LOCALE_PROVIDER builtin", you have to specify > BUILTIN LOCALE too: > > CREATE DATABASE b >TEMPLATE template0 >LOCALE_PROVIDER builtin >BUILTIN_LOCALE 'C.UTF-8' >/* used for aspects other than collation and character type */ >LOCALE 'C'; Thanks Laurenz. Indeed, Using LOCALE vs BUILTIN_LOCALE matters. On Linux, no error unlike on Windows (still inconsistent there IMHO), but the result is slightly different for datcollate and datctype (C vs en_US), while the same for datlocprovider and datlocale, what I looked at. Thus I kinda persist that there *is* a portability issue here. Also, note what the doc says: If locale_provider is builtin, then locale or builtin_locale must be specified and set to either C or C.UTF-8. It clearly says "locale or builtin_locale", emphasis on the OR. So two issues here. 1) the doc is wrong or misleading on this. 2) the same command works on Linux, but not Windows. FWIW. --DD C:\Users\ddevienne>psql service=pau17 psql (17.4, server 17.5) ddevienne=> select version(); version - PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit (1 row) ddevienne=> create database "dd_v168" encoding 'UTF8' locale 'C.UTF-8' ddevienne-> locale_provider 'builtin' template template0; CREATE DATABASE ddevienne=> select datlocprovider, datlocale, datcollate, datctype from pg_database where datname = 'dd_v168'; datlocprovider | datlocale | datcollate | datctype +---++-- b | C.UTF-8 | C.UTF-8| C.UTF-8 (1 row) ddevienne=> create database "dd_v168b" encoding 'UTF8' builtin_locale 'C.UTF-8' ddevienne-> locale_provider 'builtin' template template0; CREATE DATABASE ddevienne=> select datlocprovider, datlocale, datcollate, datctype from pg_database where datname = 'dd_v168b'; datlocprovider | datlocale | datcollate | datctype +---+-+- b | C.UTF-8 | en_US.UTF-8 | en_US.UTF-8 (1 row)
Re: Feature request: Settings to disable comments and multiple statements in a connection
On Thu, 5 Jun 2025 at 01:06, Tom Lane wrote: > ... An injection attack is normally > trying to break out of a quoted string, not a comment. I think the comments he refers to are more used to do "bobby tables" like stuff, as helpers in correct statement forming, not to inject per se. ( I do not think the feature request is worth doing either, just commenting ). Francisco Olarte.
Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, Jun 5, 2025 at 4:52 AM Jeff Davis wrote: > > On Windows, I'm getting > I tried on windows and linux and got the same result. Thanks Jeff, for your informative email. The command work, just like it does on Linux. YET... The results are NOT the same! C:\Users\ddevienne>psql service=my17 psql (17.4, server 17.5) ddevienne=> select version(); version - PostgreSQL 17.5 on x86_64-windows, compiled by msvc-19.43.34808, 64-bit (1 row) ddevienne=> create database "dd_v168" encoding 'UTF8' locale 'C.UTF-8' ddevienne-> locale_provider 'builtin' template template0; ERROR: invalid LC_COLLATE locale name: "C.UTF-8" HINT: If the locale name is specific to ICU, use ICU_LOCALE. ddevienne=> create database "dd_v168" encoding 'UTF8' builtin_locale 'C.UTF-8' ddevienne-> locale_provider 'builtin' template template0; CREATE DATABASE ddevienne=> select datlocprovider, datlocale, datcollate, datctype from pg_database where datname = 'dd_v168'; datlocprovider | datlocale | datcollate | datctype +---++-- b | C.UTF-8 | C | C (1 row) Contrast that with the results on Linux, I just sent in response to Laurenz's message, and that I'll repeat below: ddevienne=> select version(); version - PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit (1 row) ddevienne=> create database "dd_v168b" encoding 'UTF8' builtin_locale 'C.UTF-8' ddevienne-> locale_provider 'builtin' template template0; CREATE DATABASE ddevienne=> select datlocprovider, datlocale, datcollate, datctype from pg_database where datname = 'dd_v168b'; datlocprovider | datlocale | datcollate | datctype +---+-+- b | C.UTF-8 | en_US.UTF-8 | en_US.UTF-8 (1 row) Which means the same commands sometimes work, sometimes not, across platforms. But when they work, they don't even yield the SAME results. Which means it's not portable IMHO... So... It is possible to have the SAME command on Windows and Linux, which yields the SAME datcollate and datctype values??? So far, such a command eludes me, I'm afraid. --DD
Re: Combining scalar and row types in RETURNING
> In my case, m_new_data is actually a table row type rather than plain > "record". The row is passed on to another function which calculates the > altered columns and logs the changes. In my experience, to specify composite-typed receiver as one element of INTO list, it need to be column of composite variable. CREATE TABLE t (a int, b int, c int); CREATE TYPE wrap AS (v t); DO $DO$ DECLARE s text; w wrap; BEGIN INSERT INTO t VALUES (1, 2, 3) RETURNING 'text', t INTO s, w.v; RAISE '%, %', s, w.v; END; $DO$; https://dbfiddle.uk/e5Q4Fj6l
Re: LOCALE C.UTF-8 on EDB Windows v17 server
On Thu, Jun 5, 2025 at 1:40 PM Laurenz Albe wrote: > On Thu, 2025-06-05 at 10:53 +0200, Dominique Devienne wrote: > > It clearly says "locale or builtin_locale", emphasis on the OR. > > You are right, and that's how it works on Linux. > BUILTIN_LOCALE is not required. Still, required or not, they yield different results (for datcollate and datctype), which is hardly expected.
Re: LOCALE C.UTF-8 on EDB Windows v17 server
Dominique Devienne wrote: > On Linux, no error unlike on Windows (still inconsistent there IMHO), > but the result is slightly different for datcollate and datctype (C vs > en_US), > while the same for datlocprovider and datlocale, what I looked at. > > Thus I kinda persist that there *is* a portability issue here. "datcollate" and "datctype" refer to operating system locale names. locale 'C.UTF-8' or lc_collate 'C.UTF-8' lc_ctype 'C.UTF-8' cannot work on Windows because Windows does not have a locale named C.UTF-8, whereas a Linux system does (well at least recent Linuxes. Some old Linuxes don't). What you are seeing is the effect of OS locales not being portable across systems. That's confusing but not a Postgres bug. Best regards, -- Daniel Vérité https://postgresql.verite.pro/
Re: Feature request: Settings to disable comments and multiple statements in a connection
Am Wed, Jun 04, 2025 at 10:41:15PM + schrieb Glen K: > In my experience developing backends for APIs, I have > never come across a case where comments were needed or > desired within SQL statements generated for API requests, Being able to garnish with comments the SQL being sent to a backend greatly aids in debugging queries (such as which query originated from which part of remote code [or even when]). Not that it didn't take me quite a few years to chance upon that idea ... Best regards, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
