Take Sample Error: invalid input syntax for type interval: "" in Version 4.6

[RomanJaeger]

Hello all,

I got the following error when I execute take_sample from Version 4.6

select * from profile.take_sample(); server	result	elapsed --------+-----------------------------------------------------------------------------+------------- local	invalid input syntax for type interval: "" +	00:00:09.12	while executing query on dblink connection named "server_connection" +		SQL statement "INSERT INTO last_stat_activity +		SELECT +		sserver_id, +		s_id, +		dbl.subsample_ts, +		dbl.datid, +		dbl.datname, +		dbl.pid, +		dbl.leader_pid, +		dbl.usesysid, +		dbl.usename, +		dbl.application_name, +		dbl.client_addr, +		dbl.client_hostname, +		dbl.client_port, +		dbl.backend_start, +		dbl.xact_start, +		dbl.query_start, +		dbl.state_change, +		dbl.state, +		dbl.backend_xid, +		dbl.backend_xmin, +		dbl.query_id, +		dbl.query, +		dbl.backend_type, +		dbl.backend_xmin_age +		FROM +		dblink('server_connection', server_query) AS dbl( +		subsample_ts timestamp with time zone, +		datid oid, +		datname name, +		pid integer, +		leader_pid integer, +		usesysid oid, +		usename name, +		application_name text, +		client_addr inet, +		client_hostname text, +		client_port integer, +		backend_start timestamp with time zone, +		xact_start timestamp with time zone, +		query_start timestamp with time zone, +		state_change timestamp with time zone, +		state text, +		backend_xid xid, +		backend_xmin xid, +		query_id bigint, +		query text, +		backend_type text, +		backend_xmin_age bigint +		)" +		PL/pgSQL function take_subsample(integer,jsonb) line 258 at SQL statement +		PL/pgSQL function take_sample(integer,boolean) line 1106 at assignment +		PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment+		SQL function "take_sample" statement 1 +

(1 row)

I downgraded to version 4.5 and it works now.

My set up is a EDB Postgres Database Version 16. User have superuser privileges. dblink version is 1.2, pg_stat_statements is 1.10 and plpgsql 1.0 .

Regards,

Roman

[zubkov-andrei]

Hi, Thank you for report. The error seems strange. The first thought was that your instance treats NULL text value as an empty string. Can you check it please? Try something like

postgres=# select 'a'||null::text||'b' is null;
 ?column? 
----------
 t

Check if it will be true or false.

[RomanJaeger]

Hi,

edb=# select 'a'||null::text||'b' is null;
 ?column?
----------
 f
(1 row)

I get a false from this command.

[zubkov-andrei]

It seems like a Oracle compatible mode in postgres. I'll try to find a way to avoid an error in such case.

[RomanJaeger]

Yes, that is correct. The database works with Oracle Objects and Procedures and is in compatible mode.

[zubkov-andrei]

I'm sorry, I haven't access to such database, so I would ask you to test something for me in your environment. Would this return a NULL as a text value?

select format('%L', NULLIF('',''));

[RomanJaeger]

Here are the output.

edb=# select format('%L', NULLIF('',''));
 format
--------
 NULL

[RomanJaeger]

Hi, I found another issue, but I could not test it on a newer version. So perhabs it soveld then. I give you a short description. Please let me know, if I should open a new thread for this.

If I set the settings like SELECT set_server_size_sampling('local','23:00+03',interval '2 hour',interval '8 hour'); I get the error:

edb=# select * from profile.take_sample(); server	result	elapsed --------+-----------------------------------------------------------------------------+------------- local	operator does not exist: timestamp without time zone + time with time zone +	00:00:00.03	PL/pgSQL function take_sample(integer,boolean) line 51 at SQL statement +		PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment+		SQL function "take_sample" statement 1 +

(1 row)

after I set the entry size_smp_wnd_start to null in the table servers. than it works again.

[zubkov-andrei]

Sorry for long delay in discussion. The first part of the issue (invalid input syntax) likely will be fixed in upcoming 4.7. The second part (like the first one) does not produce any errors on PostgreSQL, so I need a test.. Could you please check something like

select now()::date + '23:00:00+03'::time with time zone;

[RomanJaeger]

No Problem. I'm glad to hear that the problem is solved in the new version.

If I put in your command I got this error:

In tested it in a postgres version 13. there it work.

[RomanJaeger]

I spoke with my contact by EDB. He is not absolutly sure, why ::date is not working. Can be something with the compatibility to Oracle, but if you change it to pg_catalog.date then it works.

select now()::pg_catalog.date + '23:00:00+03'::time with time zone;

On a EDB EPAS 16:

On a postgres as well. Tested on a version 13:

[zubkov-andrei]

Thank you for detailed investigation, I'll include your solution in 4.7

[zubkov-andrei]

Please check the 4.7 pre-release. Does it work on your environment?

[RomanJaeger]

Hi, I installed version 4.7 and get this error now.

edb=# SELECT * FROM profile.take_sample(); server	result	elapsed --------+--------------------------------------------------------------------------------------------------------------------+------------- local	syntax error at or near ")" +	00:00:00.97	while executing query on dblink connection named "server_connection" +		PL/pgSQL function collect_pg_stat_statements_stats(jsonb,integer,integer,integer) line 838 at FOR over SELECT rows+		SQL statement "SELECT collect_pg_stat_statements_stats(server_properties, sserver_id, s_id, topn)" +		PL/pgSQL function take_sample(integer,boolean) line 624 at PERFORM +		PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment +		SQL function "take_sample" statement 1 +

(1 row)

[zubkov-andrei]

Hi, is it the same system? I.e. EDB based Postgres 16, pgss 1.10?

[zubkov-andrei]

Can you examine the server logs for exact failed statement executed over dblink? Looks strange - PG16 seems to be well-tested.

[RomanJaeger]

Hi, this is a EDB Version 16 and pgss 1.10.

Here are the output: 2024-09-25 11:37:03.794 CEST,"enterprisedb","idashval",2975268,"[local]",66f3d9be.2d6624,1,"idle in transaction",2024-09-25 11:37:02 CEST,36/3554,0,ERROR,42601,"syntax error at or near "")""",,,,,,"SELECT userid, dbid, toplevel, queryid, regexp_replace(query,$i$\s+$i$,$i$ $i$,$i$g$i$) AS query FROM public.pg_stat_statements(true) WHERE queryid IN ()",153,,"pg_profile","client backend",,0