[sr-dev] PostgreSQL problems with Kamailio_3.0.2

[Henning Westerholt]

On Sunday 20 June 2010, Klaus Feichtinger wrote:
> 1)      The default settings of 4 PGSQL tables after initializing the
>  database with “kamdbctl init” are not useful; the tables “PRESENTITY”,
>  “PUA”, “ACC” and “MISSED_CALLS” have wrong settings for “Not NULL”
>  characteristics of some columns. In detail following columns had to be
>  adapted manually in the database:

Hello Klaus,

thanks for the report.
 
> “acc” and “missed_calls” table : column “id” must allow “NULL” (remove “Not
>  Null” setting)

This two tables are related to the acc module. Do you get some errors here as 
well by using this module?

> “presentity” table: the column “sender” must allow “NULL” (remove “Not
>  Null” setting)
> 
> “pua” table: the columns “extra_headers”, “version”, “remote_contact”,
>  “contact” and “desired_expires” must allow “NULL” (remove “Not Null”
>  setting)

We can fix this in the data definition (the SQL is derived from some XML 
source). Can you maybe quote a bit more context to the error messages you 
provided, that i can take a look to the module in question how its inserted?

> 
> 2)      I do not know if this has a direct influence on the problems I have
>  with presence, but the column “sender” in the table “presentity” seems to
>  be used only “half”. When the pua_usrloc module is inserting an entry into
>  the table it does NOT insert a value for the column “sender”. However,
>  when a query is sent for selecting information from this table, the column
>  “sender” is explicitly requested……

This looks like a bug in the module to me.

> [..]
> What does the column “sender” represent? In the presence description on the
>  Kamailio homepage (version 1.5) this column still is not included.

In sr repository the docs are also not that meaningful:
<description>Sender contact</description>

If this was added recently, maybe the author can comment on the purpose of 
them?

> 3)      The next problem I have is, that the PIDF-body, which is stored in
>  the PGSQL database, seems to cause an error in the presence_xml module and
>  therefore no body is attached to the NOTIFY message. The NOTIFY message
>  contains a SIP header “Content-Type: application/pidf+xml”, but no
>  PIDF-body is sent in this message. As result of this SIP request the SIP
>  user agent (= subscriber) is a little bit confused….. I think that problem
>  in general has something to do with the “error” described in the new task
>  from Friday June 18th
>  (http://lists.sip-router.org/pipermail/sr-dev/2010-June/007865.html).

This is something related to the BLOB handling as well, maybe its related.

> 4)      I don’t know if the parser might be influenced by a WARNING that is
>  generated by the postgresql daemon whenever an entry into the presentity
>  table is done (including XML body). From Kamailio log output I saw that
>  the special characters “#011” and “#012” are included in the XML body. I
>  guess that is the octal notation of \t (horizontal tab) and \n (newline).
> 
> However, postgresql generates an error message that looks like following:
> WARNING:  nonstandard use of \\ in a string literal at character 162
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> 
> Maybe this has some influence on the parser problem, too. Because in this
>  version of Postgresql the parameter “standard_conforming_strings” is
>  implicitly on – just for previous versions it could be set to off. That
>  means, that any backslash symbol (\) is interpreted as standard character
>  (no escape). Therefore the queried result of the database does no longer
>  include \n and \t.

Sounds indeed possible that this caused the problem.
 
> [..]
> Please give me some comments to these problems ;-) I know, PostgreSQL is
>  only “second quality” for Kamailio, but it has some advantages against
>  MySQL, too.

I'll comment on the mails later on as well. Yes, postgres is indeed not that 
used that much, means that more bugs will be present especially in module that 
are as well not that much used like e.g. usrloc. If there are problems in the 
driver module it should be of course fixed.

Henning