[asterisk-dev] SVN Trunk: Postgres CDR broken

[Brian Capouch]

I wish I could say when this started.  Some while back.  I can't quite 
figure out what is going on, but I solved it by trial-and-error while 
looking at the source for cdr_pgsql.c

Detailed CLI captures below, from version r110444M.  Problem has existed 
for "a long time" in the trunk version.  I posted about this once 
before, but didn't have time to dig it, as I now have done.

Synopsis: The "calldate" field can't be used anymore as a column name in 
the cdr table; it blows up when talking to the backend.  The fix (which 
is foul, because it blows up the Gudino CDR analyzer program and makes 
it useless) is simple: rename "calldate" to "start"

If I can fix this somehow I'd be much obliged.

Thx.

b.

*** Snip ***

** Here is the CDR table I *used* to be able to use:
    Column    |           Type           | Modifiers
-------------+--------------------------+-----------
  calldate    | timestamp with time zone | not null
  clid        | character varying(80)    | not null
  src         | character varying(80)    | not null
  dst         | character varying(80)    | not null
  dcontext    | character varying(80)    | not null
  channel     | character varying(80)    | not null
  dstchannel  | character varying(80)    | not null
  lastapp     | character varying(80)    | not null
  lastdata    | character varying(80)    | not null
  duration    | bigint                   | not null
  billsec     | bigint                   | not null
  disposition | character varying(45)    | not null
  amaflags    | character varying(45)    | not null
  accountcode | character varying(20)    |
  uniqueid    | character varying(32)    | not null
  userfield   | character varying(255)   |

** Here is error that occurs when Asterisk tries to write the CDR record:

        > [INSERT INTO cdr 
(clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,uniqueid) 
VALUES ('"Brian Capouch" 
<2192538181>','2192538181','6060','testcontext','SIP/spa3k-0064e3e0','IAX2/bc-x-4','Dial','IAX2/bc-x/6060',7,5,'ANSWERED','DOCUMENTATION','1210401208.0')]
[May 10 02:33:12] ERROR[5059]: cdr_pgsql.c:283 pgsql_log: cdr_pgsql: 
Failed to insert call detail record into database!
[May 10 02:33:12] ERROR[5059]: cdr_pgsql.c:284 pgsql_log: cdr_pgsql: 
Reason: ERROR:  null value in column "calldate" violates not-null constraint
[May 10 02:33:12] ERROR[5059]: cdr_pgsql.c:285 pgsql_log: cdr_pgsql: 
Connection may have been lost... attempting to reconnect.
        > doing dnsmgr_lookup for '131.93.62.7'
[May 10 02:33:13] ERROR[5059]: cdr_pgsql.c:288 pgsql_log: cdr_pgsql: 
Connection reestablished.
[May 10 02:33:13] ERROR[5059]: cdr_pgsql.c:294 pgsql_log: cdr_pgsql: 
HARD ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
[May 10 02:33:13] ERROR[5059]: cdr_pgsql.c:295 pgsql_log: cdr_pgsql: 
Reason: ERROR:  null value in column "calldate" violates not-null constraint

** Here is fixed version of cdr table, identical except for column name 
"calldate" is now called "start:"

    Column    |           Type           | Modifiers
-------------+--------------------------+-----------
  start       | timestamp with time zone | not null
  clid        | character varying(80)    | not null
  src         | character varying(80)    | not null
  dst         | character varying(80)    | not null
  dcontext    | character varying(80)    | not null
  channel     | character varying(80)    | not null
  dstchannel  | character varying(80)    | not null
  lastapp     | character varying(80)    | not null
  lastdata    | character varying(80)    | not null
  duration    | bigint                   | not null
  billsec     | bigint                   | not null
  disposition | character varying(45)    | not null
  amaflags    | character varying(45)    | not null
  accountcode | character varying(20)    |
  uniqueid    | character varying(32)    | not null
  userfield   | character varying(255)   |

** You can see that the recent server versions are no longer sending 
anything for calldate, and things blow up because that violates the "NOT 
NULL" constraint.

** Here's what happens--no changes anywhere else--after I rename the 
column to "start":

        > [INSERT INTO cdr 
(start,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,uniqueid) 
VALUES ('2008-05-10 02:37:23','"Brian Capouch" 
<2192538181>','2192538181','6060','testcontext','SIP/spa3k-0064dee0','IAX2/bc-x-4','Dial','IAX2/bc-x/6060',9,6,'ANSWERED','DOCUMENTATION','1210401466.0')]


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.