Encoding bug, still not out of the woods ...

[blogh]

We had some encoding issues there : #149.
Well we're not out of the woods yet ... there are still some edge cases (at least one) .

In psql :

pcsql -c "SELECT '\It`s gona crash', pg_sleep(10)"

Quickly :

(pgactivity) [benoit@benoit-dalibo pg_activity]$ pg_activity 
Traceback (most recent call last):
  File "/home/benoit/tmp/pgactivity/bin/pg_activity", line 33, in <module>
    sys.exit(load_entry_point('pg-activity', 'console_scripts', 'pg_activity')())
  File "/home/benoit/git/dalibo/pg_activity/pgactivity/cli.py", line 312, in main
    ui.main(term, dataobj, host, options, dsn)
  File "/home/benoit/git/dalibo/pg_activity/pgactivity/ui.py", line 200, in main
    pg_procs.set_items(data.pg_get_activities(ui.duration_mode))
  File "/home/benoit/git/dalibo/pg_activity/pgactivity/data.py", line 278, in pg_get_activities
    cur.execute(
  File "/home/benoit/tmp/pgactivity/lib64/python3.9/site-packages/psycopg2/extras.py", line 146, in execute
    return super(DictCursor, self).execute(query, vars)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type bytea

It's because of this part of the queries pg_get_activity*.sql:

convert_from(a.query::bytea, coalesce(pg_catalog.pg_encoding_to_char(b.encoding), 'UTF8')) AS query,

In order for the cast to work, the '' should be escaped:

[local]:5435 postgres@postgres=# SELECT convert_from('\It`s gona crash', 'UTF8');
ERROR:  invalid input syntax for type bytea
LINE 1: SELECT convert_from('\It`s gona crash', 'UTF8');
                            ^
[local]:5435 postgres@postgres=# SELECT convert_from(E'\It`s gona crash', 'UTF8');
  convert_from   
-----------------
 It`s gona crash
(1 row)
[local]:5435 postgres@postgres=# SELECT convert_from('\\It`s gona crash', 'UTF8');
   convert_from   
------------------
 \It`s gona crash
(1 row)

[ssharunas]

This bug crashes pg_activity if there are a lot of queries with regexp or newlines.
Example:

select '123' ~ '\d+', pg_sleep(3);
select 'Hello world!\n', pg_sleep(3)

Not sure if it is the best solution, but manually escaping '\' to '\\' in all queries with a cast to ::bytea works.

-- fails:
convert_from(a.query::bytea, coalesce(pg_catalog.pg_encoding_to_char(b.encoding), 'UTF8')) AS query
-- fixes the problem:
convert_from(replace(a.query, '\', '\\')::bytea, coalesce(pg_catalog.pg_encoding_to_char(b.encoding), 'UTF8')) AS query

[blogh]

Thanks for the contribution.
I commit-ed your change.