I have a VVX 150 hardphone that registers successfully with asterisk 22.6, but after a while i get this in the log and it becomes unreachable.
[Jan 9 12:38:43] VERBOSE[107119] res_pjsip/pjsip_options.c: Contact 1111/sip:1111@192.168.51.83:37616;transport=TLS is now Reachable. RTT: 82.357 msec
........
[Jan 9 13:37:41] WARNING[107119] res_config_odbc.c: Key field 'id' does not exist in table 'ps_contacts@Postgresql-connection'. Update will fail
[Jan 9 13:37:41] WARNING[107119] res_odbc.c: SQL Execute returned an error: 42601: ERROR: syntax error at or near "WHERE";
Error while preparing parameters
[Jan 9 13:37:41] WARNING[107119] res_odbc.c: SQL Execute error -1!
[Jan 9 13:37:41] ERROR[107119] res_pjsip_registrar.c: Failed to update contact 'sip:1111@192.168.51.83:37616;transport=TLS' expiration time to 3600 seconds.
[Jan 9 13:37:41] VERBOSE[107119] res_pjsip_registrar.c: Removed contact 'sip:1111@192.168.51.83:37616;transport=TLS' from AOR '1111' due to registration failure
Postgres log shows this:
2026-01-09 13:37:41.633 PST [110780] asterisk@asterisk ERROR: syntax error at or near "WHERE" at character 25
2026-01-09 13:37:41.633 PST [110780] asterisk@asterisk STATEMENT: UPDATE ps_contacts SET WHERE id=$1
Here is my table that was created using the postgresql_config.sql that comes with asterisk
CREATE TABLE public.ps_contacts (
id varchar(255) NOT NULL,
uri varchar(511) NULL,
expiration_time int8 NULL,
qualify_frequency int4 NULL,
outbound_proxy varchar(255) NULL,
"path" text NULL,
user_agent varchar(255) NULL,
qualify_timeout float8 NULL,
reg_server varchar(255) NULL,
authenticate_qualify public."ast_bool_values" NULL,
via_addr varchar(40) NULL,
via_port int4 NULL,
call_id varchar(255) NULL,
endpoint varchar(255) NULL,
prune_on_boot public."ast_bool_values" NULL,
qualify_2xx_only public."ast_bool_values" NULL,
CONSTRAINT ps_contacts_id_key UNIQUE (id),
CONSTRAINT ps_contacts_uq UNIQUE (id, reg_server)
);
CREATE INDEX ps_contacts_id ON public.ps_contacts USING btree (id);
CREATE INDEX ps_contacts_qualifyfreq_exp ON public.ps_contacts USING btree (qualify_frequency, expiration_time);
I see that 22.7 is out, i checked the changelog but didnāt see anything about thisā¦
The syntax error is because no variable are being set, so the parser is finding āWHEREā when it is expecting a field name. Iām not sure about the id field diagnostic.
Thanks for the reply @david551. Iām going to turn on more verbose PostgreSQL logging to see if i can get more information. Also going to try reverting to version 20, I donāt think this happens there, but Iāll confirm.
res_config_odbc uses a table cache provided by res_odbc to find tables and columns. Did Asterisk start/restart after the id column was added to the table? If it did not, then the cache may not have picked it up.
If you enable debug logging (at least level 3) you should see messages on Asterisk startup that say something like Found <foo> column with type <blah>ā¦
I did a grep for ps_contacts but only found these lines at startup related to the loading of the pjsip real time tables.
[Jan 12 07:45:32] VERBOSE[48202] config.c: Binding ps_aors to odbc/Postgresql-connection/ps_aors
[Jan 12 07:45:32] VERBOSE[48202] config.c: Binding ps_domain_aliases to odbc/Postgresql-connection/ps_domain_aliases
[Jan 12 07:45:32] VERBOSE[48202] config.c: Binding ps_endpoint_id_ips to odbc/Postgresql-connection/ps_endpoint_id_ips
[Jan 12 07:45:32] VERBOSE[48202] config.c: Binding ps_contacts to odbc/Postgresql-connection/ps_contacts
[Jan 12 07:45:32] VERBOSE[48202] config.c: Binding ps_outbound_publishes to odbc/Postgresql-connection/ps_outbound_publishes
[Jan 12 07:45:32] VERBOSE[48202] config.c: Binding ps_inbound_publications to odbc/Postgresql-connection/ps_inbound_publication
I see what your describing for the cdr table.
[Jan 12 15:45:20] VERBOSE[47819] cdr_adaptive_odbc.c: Found accountcode column with type 12 with len 80, octetlen 255, and numlen (0,0)
[Jan 12 15:45:20] VERBOSE[47819] cdr_adaptive_odbc.c: Found src column with type 12 with len 80, octetlen 255, and numlen (0,0)
[Jan 12 15:45:20] VERBOSE[47819] cdr_adaptive_odbc.c: Found dst column with type 12 with len 80, octetlen 255, and numlen (0,0)
[Jan 12 15:45:20] VERBOSE[47819] cdr_adaptive_odbc.c: Found dcontext column with type 12 with len 80, octetlen 255, and numlen (0,0)
Also my logger.conf appears to be setup correctly for verbose logging..
messages.log => notice,warning,error,verbose
Itās just so strange that asterisk appears to use key field āidā when the contact is initially added, but asterisk doesnāt seem to know the key id when it goes to update it.
Turned on postgresql statement logging so i could see the statements that asterisk is running.
From messages.logā¦
[Jan 12 10:33:56] VERBOSE[70265] res_pjsip_registrar.c: Added contact 'sip:1111@192.168.51.83:48229;transport=TLS' to AOR '1111' with expiration of 3600 seconds
....
[Jan 12 11:32:54] WARNING[70265] res_config_odbc.c: Key field 'id' does not exist in table 'ps_contacts@Postgresql-connection'. Update will fail
[Jan 12 11:32:54] WARNING[70265] res_odbc.c: SQL Execute returned an error: 42601: ERROR: syntax error at or near "WHERE";
Error while preparing parameters
[Jan 12 11:32:54] WARNING[70265] res_odbc.c: SQL Execute error -1!
[Jan 12 11:32:54] ERROR[70265] res_pjsip_registrar.c: Failed to update contact 'sip:1111@192.168.51.83:48229;transport=TLS' expiration time to 3600 seconds.
[Jan 12 11:32:54] VERBOSE[70265] res_pjsip_registrar.c: Removed contact 'sip:1111@192.168.51.83:48229;transport=TLS' from AOR '1111' due to registration failure
[Jan 12 11:32:54] VERBOSE[81861] res_pjsip/pjsip_options.c: Contact 1111/sip:1111@192.168.51.83:48229;transport=TLS has been deleted
From postgresql logā¦
2026-01-12 11:32:54.289 PST [70250] asterisk@asterisk LOG: execute _PLAN0x7187500bdc50: SELECT * FROM ps_aors WHERE id = $1
2026-01-12 11:32:54.289 PST [70250] asterisk@asterisk DETAIL: Parameters: $1 = '1111'
2026-01-12 11:32:54.289 PST [70250] asterisk@asterisk LOG: statement: DEALLOCATE "_PLAN0x7187500bdc50"
2026-01-12 11:32:54.290 PST [70250] asterisk@asterisk LOG: execute _PLAN0x7187501427f0: SELECT * FROM ps_contacts WHERE id LIKE $1 ORDER BY id
2026-01-12 11:32:54.290 PST [70250] asterisk@asterisk DETAIL: Parameters: $1 = '1111^3B@%'
2026-01-12 11:32:54.291 PST [70250] asterisk@asterisk LOG: statement: DEALLOCATE "_PLAN0x7187501427f0"
2026-01-12 11:32:54.291 PST [70250] asterisk@asterisk LOG: statement: select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod, 0, attidentity, c.relhassubclass from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like 'ps_contacts' and n.nspname like 'dm') inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum operator(pg_catalog.>) 0 and a.attname like '%' and a.attrelid operator(pg_catalog.=) c.oid) inner join pg_catalog.pg_type t on t.oid operator(pg_catalog.=) a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid operator(pg_catalog.=) a.attrelid and d.adnum operator(pg_catalog.=) a.attnum order by n.nspname, c.relname, attnum
2026-01-12 11:32:54.292 PST [70250] asterisk@asterisk ERROR: syntax error at or near "WHERE" at character 25
2026-01-12 11:32:54.292 PST [70250] asterisk@asterisk STATEMENT: UPDATE ps_contacts SET WHERE id=$1
2026-01-12 11:32:54.293 PST [70250] asterisk@asterisk LOG: execute _PLAN0x71875008ded0: DELETE FROM ps_contacts WHERE id=$1
2026-01-12 11:32:54.293 PST [70250] asterisk@asterisk DETAIL: Parameters: $1 = '1111^3B@19e897db7c9280a0ca2710b89aa6e176'
2026-01-12 11:32:54.353 PST [70250] asterisk@asterisk LOG: statement: DEALLOCATE "_PLAN0x71875008ded0"
2026-01-12 11:32:54.356 PST [70250] asterisk@asterisk LOG: execute _PLAN0x71874426cdc0: SELECT * FROM ps_endpoints WHERE id = $1
2026-01-12 11:32:54.356 PST [70250] asterisk@asterisk DETAIL: Parameters: $1 = '1111'
The problem seems to be the update statement is missing the set clause.
āSTATEMENT: UPDATE ps_contacts SET WHERE id=$1ā
You should grep your logs again for res_odbc.c: - there have to be debug messages about columns being loaded from ps_contacts(the message wonāt include the table name though).
Can you grep for everything from res_odbc.c and attach it here?
asterisk queried catalogs withdm(schema) but later ran UPDATE ps_contacts ... without schema-qualifying which are two different mechanisms inside the stack.
I have one database named asterisk with two schemaās one for asterisk config (public), the other (dm) for log tables such as cdr, queue_logs, and some other custom logs I hit with func_odbc.
I thought I could get away with āALTER DATABASE asterisk SET search_path TO dm, public;ā. It works most of the time, but not in every case as i have found out.
Iām just going to put them in the same schema. My fault for trying to separate configs and data like this.
I could get around it by creating another DSN, but Iāve lost the sense of adventure after troubleshooting this off and on for a week. The deadlines are calling.