[Pdns-users] Slight problem with PostgreSQL backend AXFR query

[ktm at rice.edu]

Dear PDNS community,

While debugging a new installation we noticed that a slow zone transfer
using a PostgreSQL backend would insert duplicate records if the transfer
took over 1 minute and a second transfer was started before the first
one completed. The problem is that the default isolation level for a
postgres DB is READ COMMITTED, which means that the second query sees
an identical picture of the DB as the first query, until the first
query commits. By then it is too late and the second query commits
as well, resulting in duplicate records in the DB. There is an easy
fix, change the transaction isolation level to serializable at the
start of the transfer. The first command is the delete-zone-query
so it should be:

set transaction isolation level serializable; delete from records where domain_id=%d

instead of the bare:

delete from records where domain_id=%d

Then when the second AXFR starts, it errors out and is not allowed to
insert the duplicate records.

Regards,
Ken Marshall