[asterisk-bugs] [JIRA] (ASTERISK-26146) cdr_pgsql: Schema change happened

[Early Bird (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-26146?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=231234#comment-231234 ] 

Early Bird commented on ASTERISK-26146:
---------------------------------------

I also suggest that the configuration for timezone=UTC backend parameters receive more commenting in the sample config files to indicate that any value different from UTC is not supported (set timezone=JST with localtime in JST and the timezone logged in CDR is in JST-JST=UTC).

The name of the key is misleading and suggests that the timezone is actually configurable; it should be a binary option instead. Naming it useutc=yes|no seems much more sensible.

> cdr_pgsql: Schema change happened
> ---------------------------------
>
>                 Key: ASTERISK-26146
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-26146
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: CDR/cdr_pgsql
>    Affects Versions: 13.1.0, 13.9.1
>         Environment: Ubuntu 16.04 Xenial, Asterisk 13.1 (latest release available in the 16.04 repos), Postgres 9.5.
> {code}
> dpkg -l | grep asterisk:
> ii  asterisk                         1:13.1.0~dfsg-1.1ubuntu4                 amd64        Open Source Private Branch Exchange (PBX)
> ii  asterisk-config                  1:13.1.0~dfsg-1.1ubuntu4                 all          Configuration files for Asterisk
> ii  asterisk-core-sounds-en          1.4.22-1                                 all          asterisk PBX sound files - US English
> ii  asterisk-core-sounds-en-gsm      1.4.22-1                                 all          asterisk PBX sound files - en-us/gsm
> ii  asterisk-dahdi                   1:13.1.0~dfsg-1.1ubuntu4                 amd64        DAHDI devices support for the Asterisk PBX
> ii  asterisk-modules                 1:13.1.0~dfsg-1.1ubuntu4                 amd64        loadable modules for the Asterisk PBX
> ii  asterisk-moh-opsound-gsm         2.03-1                                   all          asterisk extra sound files - English/gsm
> ii  asterisk-mp3                     1:13.1.0~dfsg-1.1ubuntu4                 amd64        MP3 playback support for the Asterisk PBX
> ii  asterisk-voicemail               1:13.1.0~dfsg-1.1ubuntu4                 amd64        simple voicemail support for the Asterisk PBX
> dpkg -l | grep postgres:
> ii  postgresql                       9.5+173                                  all          object-relational SQL database (supported version)
> ii  postgresql-9.5                   9.5.3-0ubuntu0.16.04                     amd64        object-relational SQL database, version 9.5 server
> ii  postgresql-client-9.5            9.5.3-0ubuntu0.16.04                     amd64        front-end programs for PostgreSQL 9.5
> ii  postgresql-client-common         173                                      all          manager for multiple PostgreSQL client versions
> ii  postgresql-common                173                                      all          PostgreSQL database-cluster manager
> ii  postgresql-contrib-9.5           9.5.3-0ubuntu0.16.04                     amd64        additional facilities for PostgreSQL
> {code}
>            Reporter: Early Bird
>            Assignee: Unassigned
>            Severity: Minor
>
> While porting our configuration from 1.8 to 13.1, I noticed the following behaviour change in cdr_pgsql which I believe is a regression:
> We were using the following CDR table format with 1.8. Whenever a call was entered with the 'NO ANSWER' disposition, the answer column got set to a timestamp equivalent to 0 epoch (1970-yadayada):
> {code}
> CREATE TABLE cdr ( 
>         id serial NOT NULL,
>         start timestamp with time zone NOT NULL ,
>         answer timestamp with time zone NOT NULL , 
>         "end" timestamp with time zone NOT NULL , 
>         clid varchar (80) NOT NULL , 
>         src varchar (80) NOT NULL , 
>         dst varchar (80) NOT NULL , 
>         dcontext varchar (80) NOT NULL , 
>         channel varchar (80) NOT NULL , 
>         dstchannel varchar (80) NOT NULL , 
>         lastapp varchar (80) NOT NULL , 
>         lastdata varchar (80) NOT NULL , 
>         duration int NOT NULL , 
>         billsec int NOT NULL , 
>         disposition varchar (45) NOT NULL , 
>         amaflags int NOT NULL , 
>         accountcode varchar (20) NOT NULL , 
>         uniqueid varchar (150) NOT NULL , 
>         userfield varchar (255) NOT NULL ,
>         peeraccount varchar(20) NOT NULL ,
>         linkedid varchar(150) NOT NULL ,
>         sequence int NOT NULL
> );
> {code}
> When attempting to use the same data format on 13.1, inserting calls with the NO ANSWER dispostion fails hard to the effect that the CDR gets dropped, the reason being that instead of a valid timestamp, cdr_pgsql tries to pass an empty string:
> {code}
> [Jun 23 03:14:30] ERROR[27597] cdr_pgsql.c: Failed to insert call detail record into database!
>  985 [Jun 23 03:14:30] ERROR[27597] cdr_pgsql.c: Reason: ERROR:  invalid input syntax for type timestamp with time zone: ""
>  986 LINE 1: ...nkedid","sequence") VALUES ('2016-06-23 03:14:06','','2016-0...
> {code}
> Everything else continues to work properly. I have for now worked around this issue by dropping the answer column and deriving that timestamp using start + (duration-billsec) in our reporting queries.
> I'm reporting this since the passing of the empty string is not compliant with postgres's strict type requirements.
> I should also note that this occurred when using chan_sip.



--
This message was sent by Atlassian JIRA
(v6.2#6252)