FATAL: 'utf-8' codec can't decode byte 0xe7 in position 128: invalid continuation byte

[PatrickSiqueira]

Hello people!
I am currently using pg_activity-1.6.1 with PostgreSQL 12.3, after a certain run time, I get the following error:

Traceback (most recent call last):
File "./pg_activity", line 332, in main
procs, disp_procs)
File "/var/lib/pgsql/install/pg_activity-1.6.1/pgactivity/UI.py", line 1191, in poll
disp_proc)
File "/var/lib/pgsql/install/pg_activity-1.6.1/pgactivity/UI.py", line 1337, in __poll_activities
queries = self.data.pg_get_activities(self.duration_mode)
File "/var/lib/pgsql/install/pg_activity-1.6.1/pgactivity/Data.py", line 510, in pg_get_activities
ret = cur.fetchall()
File "/usr/lib64/python3.6/site-packages/psycopg2/extras.py", line 100, in fetchall
res = super(DictCursorBase, self).fetchall()
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe7 in position 128: invalid continuation byte
FATAL: 'utf-8' codec can't decode byte 0xe7 in position 128: invalid continuation byte

Previously with version 9 of PostgreSQL, I did not receive these errors, after the migration to version 12 is that I am having this. can you help me?

[blogh]

Hi,

Are you using third party extensions on postgres ?
We had a similar issue related to powa before : #130

Benoit

[PatrickSiqueira]

Hello,

Only pg_stat_statements, but I've been using it since version 9 of postgres, could this problem be related to some incompatibility of pg_activity with postgres 12.3 or CentOS 8?

[blogh]

Hi sorry for the long delay,

could this problem be related to some incompatibility of pg_activity with postgres 12.3 or CentOS 8?
I dont think so.

I'll try to reproduce this on my side.

Benoit.

[dlax]

I could reproduce on a debian system with postgres 11 (from debian) by executing the following SQL while pg_activity is running:

$ psql postgres
denis@postgres=# CREATE DATABASE latin1 ENCODING 'latin1' TEMPLATE template0 LC_COLLATE 'fr_FR.latin1' LC_CTYPE 'fr_FR.latin1';
CREATE DATABASE
denis@postgres=# \c latin1 
You are now connected to database "latin1" as user "denis".
denis@latin1=# CREATE TABLE test (data text);
CREATE TABLE
denis@latin1=# BEGIN;
BEGIN
denis@latin1=# INSERT INTO test VALUES ('é');
INSERT 0 1
denis@latin1=# 

By keeping the last transaction uncommitted, the INSERT ... makes pg_activity crashes as described above. I think this is because the é is attempted to be decoded with utf-8 whereas the database uses another encoding.

[dlax]

Actually, the problem shows up by using psycopg2 directly:

>>> conn = psycopg2.connect(database="postgres")
>>> conn.encoding
'UTF8'
>>> cur = conn.cursor()
>>> cur.execute("select query from pg_stat_activity")
>>> cur.fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 26: invalid continuation byte

so perhaps it's not really related to pg_activity, not sure what we can do about this?

@PatrickSiqueira, at the moment, you might try to use pg_activity with a database that has the correct encoding: pg_activity -d <dbname>.

[dlax]

And incidentally, psql seems smarter and just escapes characters that cannot be decoded:

$ psql postgres -t -c "select query from pg_stat_activity;"
 INSERT INTO test VALUES ('');
...
$ psql latin1 -t -c "select query from pg_stat_activity;"
 INSERT INTO test VALUES ('é');
...

[dvarrazzo]

psql is not smarter: it's dumber, so it works :) it just emits a stream of bytes to the console, and the console has a replace policy.

You can do the same by asking psycopg2 to return bytes instead of unicode strings: see https://www.psycopg.org/docs/faq.html#faq-bytes.

[dlax]

@dvarrazzo, thanks for the tip and explanation.

[dvarrazzo]

Another option would be to cast the query field to bytea. psycopg2 returns a memoryview object for it, which you can convert to bytes, or you can create your own typecaster to convert Postgres bytea to Python bytes.

The result is the same, you get in control of the decoding, but this way you can choose column-by-column what to retrieve in as unicode and what as bytes, and you can register the typecaster globally.

[blogh]

We can also get the encoding of the field from pg_database.

SELECT convert_from(query::bytea, pg_catalog.pg_encoding_to_char(b.encoding)),  
       pg_catalog.pg_encoding_to_char(b.encoding)                               
FROM pg_stat_activity a                                                         
     INNER JOIN pg_database b ON a.datid = b.oid;    

The join filters all the non client backends. But right now, we don't care and it would be easy enough to fix.

[blogh]

It took a long while. but it's fixed..
Thanks for reporting and helping !