Subject: Re: PQexecPrepared() question

From: Igor Korot <[email-masked]>
Date: Thu, 18 Dec 2025 07:20:12 -0800

Hi,

On Tue, Dec 16, 2025 at 11:32 PM Laurenz Albe <[email-masked]>
wrote:

> On Tue, 2025-12-16 at 21:53 -0800, Igor Korot wrote:
> > Is there some default value for client_encoding?
>
> The default value for a client connection is whatever the parameter
> "client_encoding" was set to in the PostgreSQL server configuration.

Imagine following scenario:

I have 2 machines. One is running PG server on *nix. Second is my app on
Windows.

An application starts for the first time.

What is “clientencoding in this case?

Thank you.

>
> But that value can be overridden in several ways:
>
> - explicitly by setting "client_encoding" in the connect string
>
> - with the SQL statement SET by the client
>
> - if the client has "client_encoding=auto", by the encoding set in
>   the client's environment
>
> - by setting the PGCLIENTENCODING environment variable
>
> Yours,
> Laurenz Albe
>

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 18 Dec 2025 08:40:31 -0700

> What is “clientencoding in this case?

This day in age, probably UTF-8; which is what most servers are
initialized using.  If you aren't having issues with encoding I suggest you
just take for granted that the defaults work in 99% of the cases.  If you
are having issues, share the details.

David J.

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 19 Dec 2025 08:43:06 +0100

On Thu, 2025-12-18 at 07:20 -0800, Igor Korot wrote:
> I have 2 machines. One is running PG server on *nix. Second is my app on Windows.
> 

If I read the code correctly:

- if PGCLIENTENCODING is set in the environment of the client executable, that

- otherwise, if "client_encoding" is set on the server, that

- otherwise, SQL_ASCII

Yours,
Laurenz Albe

---

From: Igor Korot <[email-masked]>
Date: Fri, 19 Dec 2025 10:49:26 -0800

---------- Forwarded message ---------
From: Igor Korot <[email-masked]>
Date: Fri, Dec 19, 2025 at 9:17 AM
Subject: Re: PQexecPrepared() question
To: Laurenz Albe <[email-masked]>

Hi, Lauren’s,

> - if PGCLIENTENCODING is set in the environment of the client executable,

No it is not.

> - otherwise, if "client_encoding" is set on the server, that

I guess I is.
But what is the purpose of setting client encoding on the server? It is for
the client

Thank you.

---

From: Tom Lane <[email-masked]>
Date: Fri, 19 Dec 2025 13:52:14 -0500

> But what is the purpose of setting client encoding on the server? It is for

No, it's to tell the server what encoding to transmit to the client
(as well as what encoding strings coming from the client are in).

			regards, tom lane

---

From: Ron Johnson <[email-masked]>
Date: Fri, 19 Dec 2025 14:12:37 -0500

> (as well as what encoding strings coming from the client are in).

If there's an encoding mismatch between the server and client, does
"something" (like a PQ driver) convert text in the data stream from the
source encoding to the target encoding?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 19 Dec 2025 13:21:11 -0800

On 12/19/25 11:12, Ron Johnson wrote:
> On Fri, Dec 19, 2025 at 1:52 PM Tom Lane <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     Igor Korot <[email-masked] <mailto:[email-masked]>> writes:

It is explained here:

https://www.postgresql.org/docs/current/multibyte.html#MULTIBYTE-AUTOMATIC-CONVERSION

-- 
Adrian Klaver
[email-masked]

---

From: Igor Korot <[email-masked]>
Date: Fri, 19 Dec 2025 20:10:12 -0800

Hi, ALL,

>> - otherwise, if "client_encoding" is set on the server, that
I just checked the postgres.conf.

This file does not have any client_encoding.

> I guess I is.
> But what is the purpose of setting client encoding on the server? It is
> for the client
>
> Thank you.
>
>
>>
>> - otherwise, SQL_ASCII
>
>
Which means that this is an encoding that will be used.

But then I don’t understand anything.

The code I posted above worked fine on SELECT, but INSERT failed.

If the SQL_ASCII is the encoding used both operations should fail. Or both
succeeds.

Could someone explain what happened?

Thank you.

---

From: Laurenz Albe <[email-masked]>
Date: Sat, 20 Dec 2025 07:24:47 +0100

> Which means that this is an encoding that will be used.

You can verify that with the SQL statement "SHOW client_encoding"
in your sample program.

> If the SQL_ASCII is the encoding used both operations should fail. Or both succeeds.
> 

SQL_ASCII as client encoding means that no conversion will take place.

Still, the database encoding (I suspect UTF8) will govern what can be stored
in the database.  Anything that is not valid UTF-8 will be rejected.

A SELECT will never cause an error - the client will just receive data
in UTF-8.

Yours,
Laurenz Albe

---

From: Igor Korot <[email-masked]>
Date: Fri, 19 Dec 2025 23:04:03 -0800

Hi, Lauren’z,

> You can verify that with the SQL statement "SHOW client_encoding"

Thx, will check.

> in the database.  Anything that is not valid UTF-8 will be rejected.

Rejected how?

> A SELECT will never cause an error - the client will just receive data

And then what?

I’ll check the encoding and report back..

Thank you.

---

From: Laurenz Albe <[email-masked]>
Date: Sat, 20 Dec 2025 18:02:13 +0100

> > in the database.  Anything that is not valid UTF-8 will be rejected.

As you experienced: by throwing an error.

> > A SELECT will never cause an error - the client will just receive data

The *client application* will receive UTF-8 data.

Yours,
Laurenz Albe

---

From: Igor Korot <[email-masked]>
Date: Sat, 20 Dec 2025 21:33:23 -0800

Hi, Laurenz,

> You can verify that with the SQL statement "SHOW client_encoding"

I added the following code in my app:

    res = PQexec( m_db, "SHOW client_encoding" );
    auto value = PQgetvalue( res, 0, 0 );
    PQclear( res );

and the value of the "value" variable is "UTF8".
> A SELECT will never cause an error - the client will just receive data

The exact error message is:

ERROR: invalid byte sequence for encoding UTF8: 0xdf
CONTEXT: unnamed portal parameter $1

on the INSERT.

Thank you.

---

From: Tom Lane <[email-masked]>
Date: Sun, 21 Dec 2025 00:39:23 -0500

> and the value of the "value" variable is "UTF8".

Okay ...

> CONTEXT: unnamed portal parameter $1

client_encoding governs both the encoding that the server will
send, and the encoding that it expects to receive.  You are
sending a parameter string that is not valid UTF8.

			regards, tom lane

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sun, 21 Dec 2025 22:19:25 +0100

> sending a parameter string that is not valid UTF8.

As I see it, Igor has two options:

1) Send UTF8. wcstombs is the wrong tool for this, since it uses the
   current locale which is obviously not using UTF-8. I don't program in
   C++, but I'm pretty sure there is a portable way to encode a string
   in UTF-8.
2) Figure out which encoding his locale is using and then set
   client_encoding appropriately. The first step to do that is to find
   the current locale.
   Igor, add
        #include <ctype.h>
        ...
        char *current_locale = setlocale(LC_CTYPE, "");
   to your code. What's the value of current_locale?

I think 1) is the better strategy, but them I've been a UTF-8 fan-boy
for over 30 years ;-).

        hjp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Igor Korot <[email-masked]>
Date: Sun, 21 Dec 2025 17:30:38 -0800

Hi,

> I think 1) is the better strategy, but them I've been a UTF-8 fan-boy

Maybe all I need is something like this:

convert(char *dest, const std::wstring src)
{
    const wchar_t *temp = src.c_str();
    while( *dest )
    {
        dest++;
    }
    while( *temp )
    {
        *dest = *temp;
        dest++;
        temp++;
    }
    *dest++ = '\0';
    *dest = '\0';
}

?

Thank you.

---

From: "Peter J. Holzer" <[email-masked]>
Date: Mon, 22 Dec 2025 10:55:16 +0100

> >    C++, but I'm pretty sure there is a portable way to encode a string
[...]
>     const wchar_t *temp = src.c_str();

I hope your compiler complains loudly about that code. You can't just
assign a wchar_t to a char and expect it to do anything sensible.

A short search points to std::wstring_convert, but that has been
deprecated in C++17, and it's not obvious to me what the replacement is.

There is always ICU, of course, but that feels like cracking a nut with
a sledgehammer. 

Personally, I might roll my own if an hour of googling doesn't turn up
anything promising (UTF-8 encoding is quite simple), but that shouldn't
be necessary.

        hjp

---

From: Igor Korot <[email-masked]>
Date: Mon, 22 Dec 2025 15:07:52 -0800

Hi, Peter,

> assign a wchar_t to a char and expect it to do anything sensible.

At least MSVC 2017 did not.
However, trying didn't solve it. Same error.

> There is always ICU, of course, but that feels like cracking a nut with

I think ICU IS a replacement for wstring_comvert.
Will check it.

---

From: Igor Korot <[email-masked]>
Date: Wed, 24 Dec 2025 17:28:23 -0800

Hi, ALL,
My current code is below:

    std::wstring query1 = L"SELECT t.table_catalog AS catalog,
t.table_schema AS schema, t.table_name AS table, u.usename AS owner,
c.oid AS table_id FROM information_schema.tables t,
pg_catalog.pg_class c, pg_catalog.pg_user u WHERE t.table_name =
c.relname AND c.relowner = usesysid AND (t.table_type = 'BASE TABLE'
OR t.table_type = 'VIEW' OR t.table_type = 'LOCAL TEMPORARY') ORDER BY
table_name;";
            std::wstring query7 = L"INSERT INTO \"abcattbl\" VALUES(
0, $1, (SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
c.relnamespace AND c.relname = $2 AND nc.nspname = $3), \'\', 8, 400,
\'N\', \'N\', 0, 1, 0, \'MS Sans Serif\', 8, 400, \'N\', \'N\', 0, 1,
0, \'MS Sans Serif\', 8, 400, \'N\', \'N\', 0, 1, 0, \'MS Sans
Serif\', \'\' ) ON CONFLICT DO NOTHING;";
        for( int i = 0; i < PQntuples( res ) && !result; i++ )
         {
             std::wstring cat = m_pimpl->m_myconv.from_bytes(
PQgetvalue( res, i, 0 ) );
             std::wstring schema = m_pimpl->m_myconv.from_bytes(
PQgetvalue( res, i, 1 ) );
             std::wstring table = m_pimpl->m_myconv.from_bytes(
PQgetvalue( res, i, 2 ) );
             std::wstring  table_owner = m_pimpl->m_myconv.from_bytes(
PQgetvalue( res, i, 3 ) );
             pimpl.m_tableDefinitions[cat].push_back( TableDefinition(
cat, schema,  table ) );
             count++;
             paramValues = schema + L"." + table;
             params[0] = new char[paramValues.length() + 2];
             memset( params[0], '\0', paramValues.length() + 2 );
             auto temp = m_pimpl->m_myconv.to_bytes(
paramValues.c_str() ).c_str();
             params[0] = const_cast<char *>(
m_pimpl->m_myconv.to_bytes( paramValues.c_str() ).c_str() );
             params[1] = new char[table.length() + 2];
             memset( params[1], '\0', table.length() + 2 );
             params[1] = const_cast<char *>(
m_pimpl->m_myconv.to_bytes( table.c_str() ).c_str() );
             params[2] = new char[table_owner.length() + 2];
             memset( params[2], '\0', table_owner.length() + 2 );
             params[2] = const_cast<char *>(
m_pimpl->m_myconv.to_bytes( table_owner.c_str() ).c_str() );
             paramFormat[0] = paramFormat[1] = paramFormat[2] = 0;
             paramLength[0] = paramValues.length();
             paramLength[1] = table.length();
             paramLength[2] = table_owner.length();
             res8 = PQexecPrepared( m_db, "set_table_prop", 3, params,
paramLength, paramFormat, 0 );
             if( PQresultStatus( res8 ) != PGRES_COMMAND_OK )
             {
                 std::wstring err = m_pimpl->m_myconv.from_bytes(
PQerrorMessage( m_db ) );
                 errorMsg.push_back( L"Error executing query: " + err );
                 result = 1;
             }
             PQclear( res8 );
             delete[] params[0];
             params[0] = nullptr;
             delete[] params[1];
             params[1] = nullptr;
             delete[] params[2];
             params[2] = nullptr;
         }

This code crashes on the PQexecParam.

I am completely puzzled.

Could anyone help?

Thank you.

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 25 Dec 2025 10:32:08 +0100

On Wed, 2025-12-24 at 17:28 -0800, Igor Korot wrote:
>    res8 = PQexecPrepared( m_db, "set_table_prop", 3, params, paramLength, paramFormat, 0 );
>    [...]
> 

Your code does not contain PQexecParam.
It is also incomplete and doesn't contain where the statement is prepared,
nor does it contain the definition of the variables you are using.

I recommend using a debugger.

Yours,
Laurenz Albe