I am encountering a character encoding issue when using the pg_background extension for PostgreSQL.
The issue occurs in a Windows/PostgreSQL 15 environment, with the raise notice
statement.
To explain the problem, a minimal example using psql will be more illustrative than a detailed explanation.
The encodings are as follows:
select current_setting('client_encoding'), current_setting('server_encoding');
current_setting | current_setting
-----------------+-----------------
WIN1252 | UTF8
(1 row)
Create the following two functions:
create or replace function test_encoding_1() returns text language plpgsql as $$ begin raise notice 'Noël'; return 'Noël'; end $$;
create or replace function test_encoding_2() returns text language plpgsql as $$ begin return 'Noël'; end $$;
A direct call to the first function does not cause any issues (nor does the second function, for that matter):
select test_encoding_1();
NOTICE: Noël
test_encoding_1
-----------------
Noël
(1 row)
A call through pg_background fails with the first function, whereas with the second function everything works fine.
select r from pg_background_result(pg_background_launch('select test_encoding_1()')) as t(r text);
ERROR: invalid byte sequence for encoding "UTF8": 0xeb 0x6c
select r from pg_background_result(pg_background_launch('select test_encoding_2()')) as t(r text);
r
------
Noël
(1 row)
If we change the client encoding, the crash no longer occurs, but the characters obtained are not in the correct set:
set client_encoding to 'SQL_ASCII';
select r from pg_background_result(pg_background_launch('select test_encoding_1()')) as t(r text);
NOTICE: Noël
r
-------
Noël
(1 row)
select r from pg_background_result(pg_background_launch('select test_encoding_2()')) as t(r text);
r
-------
Noël
(1 row)
So my question is: Is there a way to obtain a result in the correct character set (WIN1252)? Either by configuring the server or by changing the function code? Or any other solution?
This issue is resolved with pg_background 1.3.