Debian 12
Timescaldb version 2.15.3
Postgresql version: 15.7
Zabbix version: 7.0.2

I have this error:

2024-08-20 12:18:27.866 CEST [2099100] ERROR:  function base36_decode(text) does not exist at character 6
2024-08-20 12:18:27.866 CEST [2099100] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2024-08-20 12:18:27.866 CEST [2099100] QUERY:  CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS integer)
2024-08-20 12:18:27.866 CEST [2099100] CONTEXT:  PL/pgSQL function public.cuid_timestamp(character varying) line 3 at RETURN
        automatic analyze of table "zabbix._timescaledb_internal._hyper_7_16_chunk"

Hi 

Are the any updates on this? 

Same issue on Ubuntu22.04 / Postgresql 13

psql:zabbix_dump.sql:50697338: ERROR:  function base36_decode(text) does not exist
LINE 1: SELECT CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000...
                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS integer)
CONTEXT:  PL/pgSQL function public.cuid_timestamp(character varying) line 3 at RETURN
COPY _hyper_17_242_chunk, line 1: "cm058exuk0001pbhbybtdqapr    \N      System  1724328074              1       47      \N      clhq3s42p0001hkhbvacy7kzt               cm058exur0002pbhb..."
psql:zabbix_dump.sql:50697358: ERROR:  function base36_decode(text) does not exist
LINE 1: SELECT CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000...
                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS integer)
CONTEXT:  PL/pgSQL function public.cuid_timestamp(character varying) line 3 at RETURN
COPY _hyper_17_259_chunk, line 1: "cm0ezfbmj0001n1hb28cbs4xv    \N      System  1724917638              1       47      \N      clhq3s42p0001hkhbvacy7kzt               cm0ezfbmm0002n1hb..."

same zabbix 7, pg16.

```

psql:/opt/dump.sql:4399730: ERROR:  function base36_decode(text) does not exist
LINE 1: CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS int...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS integer)
CONTEXT:  PL/pgSQL function public.cuid_timestamp(character varying) line 3 at RETURN
COPY _hyper_15_2664_chunk, line 1: "cm0yiwdjmp58vwgiy7a3dvq60   \N      System  1726099203              1       13      219608  \N      FS [/boot]: Filesystem has become rea..."
psql:/opt/dump.sql:4399730: STATEMENT:  COPY "_timescaledb_internal"."_hyper_15_2664_chunk" ("auditid", "userid", "username", "clock", "ip", "action", "resourcetype", "resourceid", "resource_cuid", "resourcename", "recordsetid", "details") FROM stdin;

```

Hello Zabbix Support Team,

I hope you can confirm the issue we are encountering and validate my findings.

I am encountering the same issue reading the logs of PostgreSQL 16.3 and TimescaleDB 2.15.3 (Zabbix 7.0.3). It appears that the problem exists even before performing a restore.

It seems the problem lies with both the function and the hypertable chunks of the auditlog table.

Specifically, the details column of the auditlog chunks is of type text, while the base36_decode function requires a character varying argument.

As a result, the auditlog chunks are not being compressed due to this mismatch.

How can this be resolved?

Should the base36_decode function be modified, or does it need an explicit cast? I used the schema.sql file from the TimescaleDB folder, but it doesn’t include this fix (Zabbix 7.0.3 version).

Thank you for your help,

Best regards.

Same issue, when i am trying upgrade PostgreSQL 13 to 16 with pg_upgradecluster, on step with pg_restore get:
pg_restore: error: COPY failed for table "_hyper_15_2262_chunk": ERROR:  function base36_decode(text) does not exist
LINE 1: CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS int...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS integer)
CONTEXT:  PL/pgSQL function public.cuid_timestamp(character varying) line 3 at RETURN

COPY _hyper_15_2262_chunk, line 1: "cm18j94luvt7vt7i5   \N      System  1726704460              0       15      175339  \N      VLAN 721 - : ifOperStatus       cm18j94nlvt...

Available in versions:

• pre-7.0.5rc1 40575135239, 5dc04b08f66
• pre-7.2.0alpha1 e04f3be383a, 2948ac990c8

For users

The pg_dump tool does not change the content of functions and procedures. It just dumps it. So schema is not being added to calls.

If you have problem with restoring database using pg_restore like:

ERROR:  function base36_decode(text) does not exist
LINE 1: CAST(base36_decode(substring(cuid FROM 2 FOR 8))/1000 AS int...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Before upgrade to Zabbix 7.0.5 or later replace the cuid_timestamp function in your Zabbix database by running:

CREATE OR REPLACE FUNCTION cuid_timestamp(cuid varchar(25)) RETURNS integer AS $$
DECLARE
	base36 varchar; 
	a char[];
	ret bigint;
	i int;
	val int;
	chars varchar;
BEGIN
	base36 := substring(cuid FROM 2 FOR 8);

	chars := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';

	FOR i IN REVERSE char_length(base36)..1 LOOP
		a := a || substring(upper(base36) FROM i FOR 1)::char;
	END LOOP;
	i := 0;
	ret := 0;
	WHILE i < (array_length(a, 1)) LOOP
		val := position(a[i + 1] IN chars) - 1;
		ret := ret + (val * (36 ^ i));
		i := i + 1;
	END LOOP;

	RETURN CAST(ret/1000 AS integer);
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
DROP FUNCTION IF EXISTS base36_decode(character varying);

Edit: order of PSQL commands.

Hi Armands,

Thanks that this will be fixed in future version.

Kind regards

Hi, Frits!

We just updated instruction in comment above

askolmeisters Thank you very much for your assistance. Should I stop PostgreSQL/TimescaleDB before applying this manual patch?

Will this fix the functions used in the audit log tables that are generating errors and won't affect other tables?

I previously posted about this issue in a closed duplicate post because I noticed the error in the PostgreSQL logs, but I don’t need to migrate the database and I think the two problems were linked in some way.

Thank you again for your hard work. I appreciate the quick resolution of the problem.

Best regards.

Out of curiosity, if possible, could you explain what the problem was?

I also noticed that the old base36_decode function was dropped, but it wasn’t recreated after patching the other function:
DROP FUNCTION IF EXISTS base36_decode(character varying);

Are the both function now merged into one?

Thank you and sorry for all of these questions

Should I stop PostgreSQL/TimescaleDB before applying this manual patch?

rockandstone, for safety, yes.

Will this fix the functions used in the audit log tables that are generating errors and won't affect other tables?

rockandstone, yes, it will fix errors described in this task and the duplicate ZBX-25307.

Out of curiosity, if possible, could you explain what the problem was?

rockandstone, the problem was that pg_dump does not change the content of functions. It won't add schema name in front of function call.

Are the both function now merged into one?

Yes.

askolmeisters ** 

When I am trying to execute this code in my DB - i get error:

After adding CASCADE as HINT say to me:

Zabbix 7.0.3, PSQL 13, TSDB 2.15.2

Mitaps, try to "CREATE OR REPLACE cuid_timestamp ..." and then "DROP FUNCTION IF EXISTS base36_decode". Do not drop cuid_timestamp.

New function created.
Old:
DROP FUNCTION IF EXISTS base36_decode(character varying);
NOTICE:  function base36_decode(pg_catalog.varchar) does not exist, skipping
DROP FUNCTION

I will try to upgrade DB and inform here about result, thank you.

askolmeisters 

Huge thank you for this fix!

In steps:
1) Created new function, after it deleted base36_encode in working DB
2) Upgrade DB with

sudo -iu postgres /usr/lib/postgresql/16/bin/pg_upgrade -o "-c config_file=/etc/postgresql/13/main/postgresql.conf" --old-datadir=/var/lib/postgresql/13/main/ -O "-c config_file=/etc/postgresql/16/main/postgresql.conf"  --new-datadir=/var/lib/postgresql/16/main/ --old-bindir=/usr/lib/postgresql/13/bin --new-bindir=/usr/lib/postgresql/16/bin 

Before it with --check key for confirm what all is OK
3) After upgrade:

cd /var/lib/postgresql

3.1) Here start

./delete_old_cluster.sh

3.2) Start new DB and vacuum it

sudo -u postgres /usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages -p 5433

3.3) Changed DB password back(idk why it cleared) for user zabbix
In zabbix log was "authentication for user zabbix failed"

ALTER USER zabbix PASSWORD '<new-password>';

3.4) Changed port to 5432 back in postgresql.conf
3.5) Added back in pg_hba.conf user zbx_monitor(PSQL monitoring user from official template)

Now it's working without issues and i can configure replicas correctly.

Thank you so much for your efforts!
After manually applying the patch as you suggested, the log errors have disappeared, and all the tables are now compressed.
I no longer see any errors in the logs as mentioned in my previous post.

Best Regards

Thank you for reporting! Documentation (7.0) updated:

• Installation > Upgrade notes for 7.0.0, 7.0.1, 7.0.2, 7.0.3, 7.0.4, 7.0.5
• Installation > Known issues