[Pdns-users] Continuation of serial difficulties with PDNS / Postgres

[Mitchell Smith]

Greetings,

I appear to be experiencing some difficulties with PowerDNS and the Postgres 
backend, specifically PDNS returning a serial of 0 for every domain.

We are running a custom schema as follows:

CREATE TABLE domains (
    created_timestamp timestamp without time zone NOT NULL,
    domain_id integer DEFAULT nextval('domains_seq'::regclass) NOT NULL,
    is_enabled character varying(5) NOT NULL,
    last_refresh_from_master timestamp without time zone,
    master_ip character varying(15),
    modified_timestamp timestamp without time zone,
    name character varying(255) NOT NULL,
    notified_serial timestamp without time zone,
    type character varying(8) NOT NULL
);

CREATE TABLE records (
    content character varying(255) NOT NULL,
    created_timestamp timestamp without time zone NOT NULL,
    domain_id integer NOT NULL,
    is_dynamic character varying(5) NOT NULL,
    is_enabled character varying(5) NOT NULL,
    modified_timestamp timestamp without time zone,
    name character varying(255) NOT NULL,
    priority integer,
    record_id integer DEFAULT nextval('records_seq'::regclass) NOT NULL,
    ttl integer NOT NULL,
    type character varying(8) NOT NULL
);

The queries in my pdns.conf file look like this:

gpgsql-basic-query=select 
content,ttl,priority,type,domain_id,name,int4(abstime(modified_timestamp)) 
from records where type='%s' and name='%s'
gpgsql-id-query=select 
content,ttl,priority,type,domain_id,name,int4(abstime(modified_timestamp)) 
from records where type='%s' and name='%s' and domain_id=%d
gpgsql-any-query=select 
content,ttl,priority,type,domain_id,name,int4(abstime(modified_timestamp)) 
from records where name='%s'
gpgsql-any-id-query=select 
content,ttl,priority,type,domain_id,name,int4(abstime(modified_timestamp)) 
from records where name='%s' and domain_id=%d
gpgsql-list-query=select 
content,ttl,priority,type,domain_id,name,int4(abstime(modified_timestamp)) 
from records where domain_id=%d
gpgsql-master-zone-query=select master_ip from domains where name='%s' and 
type='SLAVE'
gpgsql-info-zone-query=select 
domain_id,name,master_ip,last_refresh_from_master,notified_serial,type from 
domains where name='%s'
gpgsql-info-all-slaves-query=select 
domain_id,name,master_ip,last_refresh_from_master,type from domains where 
type='SLAVE'
gpgsql-insert-record-query=insert into records 
(content,ttl,priority,type,domain_id,name,is_dynamic) values 
('%s',%d,%d,'%s',%d,'%s',true)
gpgsql-update-serial-query=update domains set notified_serial=%d where 
domain_id=%d
gpgsql-update-lastcheck-query=update domains set last_refresh_from_master=%d 
where domain_id=%d
gpgsql-info-all-master-query=select 
domain_id,name,master_ip,last_refresh_from_master,notified_serial,type from 
domains where type='MASTER'
gpgsql-delete-zone-query=delete from records where domain_id=%d

>From my understanding, if the SOA record for a domain contains 0 as the 
serial, PowerDNS will return a serial in unixtime format, which is our 
modified_timestamp field.

Any hints on where I might be going wrong in the above setup would be very 
much appreciated.

Cheers,

Mitch