Alembic CDR PGSQL Definition NOT compatible with cdr_pgsql.c

Asterisk Asterisk Support
1

I always used the postgresql table definition at the documentation and the cdr always worked fine.

Recently I upgraded from 22.1.0 to 22.7.0 and used the alembic scripts in contrib/ast-db-manager to update my database, including the cdr (I dropped the old table in my test environment and redefined it via alembic), but the sql statements in cdr_pgsql.c seems to not be compatible.

[Jan 26 08:06:28] ERROR[1659180]: cdr_pgsql.c:434 pgsql_log: Failed to insert call detail record into database!
[Jan 26 08:06:28] ERROR[1659180]: cdr_pgsql.c:435 pgsql_log: Reason: ERROR: column “calldate” of relation “cdr” does not exist
LINE 1: INSERT INTO cdr (“calldate”, “clid”, “src”, “dst”, "dcontext…

I was just wondering if I am missing something. Is this unmaintained?

The main difference seems to be the absence of the column calldate and the addition fo the columns start, answer, end. Some columns like peeraccount and amaflags have different types.

CREATE TABLE public.cdr
(
    calldate timestamp without time zone NOT NULL,
    clid character varying(80) COLLATE pg_catalog."default" NOT NULL,
    src character varying(80) COLLATE pg_catalog."default" NOT NULL,
    dst character varying(80) COLLATE pg_catalog."default" NOT NULL,
    dcontext character varying(80) COLLATE pg_catalog."default" NOT NULL,
    channel character varying(80) COLLATE pg_catalog."default" NOT NULL,
    dstchannel character varying(80) COLLATE pg_catalog."default" NOT NULL,
    lastapp character varying(80) COLLATE pg_catalog."default" NOT NULL,
    lastdata character varying(80) COLLATE pg_catalog."default" NOT NULL,
    duration integer NOT NULL,
    billsec integer NOT NULL,
    disposition character varying(45) COLLATE pg_catalog."default" NOT NULL,
    amaflags integer NOT NULL,
    accountcode character varying(20) COLLATE pg_catalog."default" NOT NULL,
    uniqueid character varying(150) COLLATE pg_catalog."default" NOT NULL,
    userfield character varying(255) COLLATE pg_catalog."default" NOT NULL,
    peeraccount character varying(20) COLLATE pg_catalog."default" NOT NULL,
    linkedid character varying(150) COLLATE pg_catalog."default" NOT NULL,
    sequence integer NOT NULL
)


CREATE TABLE public.cdr
(
    accountcode character varying(80) COLLATE pg_catalog."default",
    src character varying(80) COLLATE pg_catalog."default",
    dst character varying(80) COLLATE pg_catalog."default",
    dcontext character varying(80) COLLATE pg_catalog."default",
    clid character varying(80) COLLATE pg_catalog."default",
    channel character varying(80) COLLATE pg_catalog."default",
    dstchannel character varying(80) COLLATE pg_catalog."default",
    lastapp character varying(80) COLLATE pg_catalog."default",
    lastdata character varying(80) COLLATE pg_catalog."default",
    start timestamp without time zone,
    answer timestamp without time zone,
    "end" timestamp without time zone,
    duration integer,
    billsec integer,
    disposition character varying(45) COLLATE pg_catalog."default",
    amaflags character varying(45) COLLATE pg_catalog."default",
    userfield character varying(256) COLLATE pg_catalog."default",
    uniqueid character varying(150) COLLATE pg_catalog."default",
    linkedid character varying(150) COLLATE pg_catalog."default",
    peeraccount character varying(80) COLLATE pg_catalog."default",
    sequence integer
)

This is no big deal, since I can always use the working definition, but may be a nuisance to maintain without alembic if something in the cdr changes in the future.

2

Go ahead and open an issue for this at Asterisk Issues and we’ll take a look.

3

Opened Issue #1744

4

I had to completely restart asterisk for it to load the new column definitions. Not even a core reload seemed to suffice. In all my testing i didn’t try to restart asterisk, but seems to work that way.

6

This is the exact same issue with the USECALLMANAGER patch. A simple core reload does not work with it when making any changes to the channel driver. I think the core reload function is much more limited than is publicized.

7

The cdr_pgsql module (and others) won’t do anything if the configuration file itself hasn’t changed. So if you touch /etc/asterisk/cdr_pgsql.confand then do a core reload within Asterisk, it should pick up the updated schema. Not ideal, but at least a workaround.