[asterisk-bugs] [JIRA] (ASTERISK-26146) cdr_pgsql: Schema change happened
Early Bird (JIRA)
noreply at issues.asterisk.org
Wed Jun 29 00:11:56 CDT 2016
[ https://issues.asterisk.org/jira/browse/ASTERISK-26146?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=231233#comment-231233 ]
Early Bird edited comment on ASTERISK-26146 at 6/29/16 12:10 AM:
-----------------------------------------------------------------
Ah yes, I overlooked that the TZ is always +00 now. Changing the schema solves the original problem.
I see where this might be coming from.
cdr_pgsql.conf permits a timezone=UTC parameter which alters the timezone (time per se) passed to the storage backend. This functionality is in line with the usegmttime=yes being supported by other storage backends such as CSV ([csv] in cdr.conf). Similar goes for the ODBC or MySQL CDR.
What is making this a mess is that dumb flat-file storage backends like CSV are thrown into the same basket as SQL backends with strict timestamp types. Perhaps ODBC is a bit special because TIMESTAMP in the SQL standard means WITHOUT TIME ZONE and ODBC might not be able to make assumptions about the active ODBC driver, however if you can pass a timestamp with timezone to a SQL database I'd strongly suggest to do so because you obtain a CDR table with full information. This makes for a more robust storage backend. If you use timestamp without timezone, you need to get the information what timezone a timestamp is in from a 2nd source -- as it is not guaranteed to be consistent, e.g. always UTC.
You'd also be able to drop the timezone= options from at least the SQL backends. I also see that cdr_tds does not have such an option in the first place?
was (Author: earlybird):
Ah yes, I overlooked that the TZ is always +00 now. Changing the schema solves the original problem.
I see where this might be coming from.
cdr_pgsql.conf permits a timezone=UTC parameter which alters the timezone (time per se) passed to the storage backend. This functionality is in line with the usegmttime=yes being supported by other storage backends such as CSV ([csv] in cdr.conf). Similar goes for the ODBC backend or MySQL CDR.
What is making this a mess is that dumb flat-file storage backends like CSV are thrown into the same basket as SQL backends with strict timestamp types. Perhaps ODBC is a bit special because TIMESTAMP in the SQL standard means WITHOUT TIME ZONE and ODBC might not be able to make assumptions about the active ODBC driver, however if you can pass a timestamp with timezone to a SQL database I'd strongly suggest to do so because you obtain a CDR table with full information. This makes for a more robust storage backend. If you use timestamp without timezone, you need to get the information what timezone a timestamp is in from a 2nd source -- as it is not guaranteed to be consistent, e.g. always UTC.
You'd also be able to drop the timezone= options from at least the SQL backends. I also see that cdr_tds does not have such an option in the first place?
> 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)
More information about the asterisk-bugs
mailing list