[asterisk-users] Error When Using Postgresql Schema With Realtime Sip

[stephen.hindmarch at bt.com]

I am using asterisk 1.6.1.6 and have been setting up a system to use a
Postgresql database as the realtime DB via the ODBC route. I have got
extensions and voicemail working but am having trouble with SIP

The problem seems to be with using a schema. If I put the table "sip" in
the schema "foo" then I add this entry to extconfig.conf

sippeers => odbc,psqldb,foo.sip

Restart everything and then try to register a client. The registration
fails and I get this set of messages in the log

[2009-09-23 11:10:57.3q] DEBUG[10431] chan_sip.c: -REALTIME- loading
peer from database to memory. Name: stone. Peer objects: 8
[2009-09-23 11:10:57.3q] VERBOSE[10431] chan_sip.c:     -- Registered
SIP 'stone' at 10.215.42.138 port 5060
[2009-09-23 11:10:57.3q] VERBOSE[10431] chan_sip.c:        > Saved
useragent "ipDialog SipTone 1.2.0 rc Z_21 UA" for peer stone
[2009-09-23 11:10:57.3q] WARNING[10431] res_config_odbc.c: Key field
'ipaddr' does not exist in table 'foo.sip at asterisk'.  Update will fail
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skip: 62; SQL:
UPDATE public.sip SET ipaddr=? WHERE name=?
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Parameter 1
('ipaddr') = '10.215.42.138'
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'port'='5060' (2/76)
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'regseconds'='1253704257' (4/76)
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'useragent'='ipDialog SipTone 1.2.0 rc Z_21 UA' (10/76)
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'lastms'='0' (20/76)
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'defaultuser'='stone' (40/76)

I now drop the table and recreate it in the public schema. I change
extconfig to 

sippeers => odbc,psqldb,public.sip

Restart and repeat with the same result.

The public schema does not need to be explicitly named so now I edit
extconfig to say

sippeers => odbc,mydb,sip

Restart and repeat, but this time the client is able to register and I
am able the set up calls to it.

So the only thing that has changed is the pointer in extconfig to the
database name. The fact that it works in the last instance proves that
my database structure is correct and the correct grants are used. The
fact that it failed for "public.sip" but worked for "sip" shows it is
nothing about the permissions of the schema itself. I can double check
this by running queries through the ODBC driver myself by using the isql
application. Selects on "sip", "public.sip" and "foo.sip" all ran
correctly and returned the same results.

So it seems to be something to do with having the schema name in the
table name. But as I say I have already got extensions and voicemail
working, and they both uses schemas, so it seems to be peculiar to SIP.

Does anybody have any ideas about what it might be?

Steve Hindmarch
BT Design