Re: DLZ PostgreSQL help needed

Ooops, sorry about the double message, didn't realize i had attached it 
as well.  Although the attached version looks better.  I hate Thunderbird.

-brian

Brian Hechinger wrote:
> Hello DLZ Team,
> 
> I started working with DLZ and PostgreSQL a couple months ago.  I first
> setup a very basic table that worked with the queries provided in the
> examples.  Or so I thought.  I didn't completely test it, I just plowed
> on to turning it into stored procedures and then finally insane stored
> procedures that worked with the app I was writing.
> 
> In that final form it mostly works, but there are some parts of it that
> are just giving me a terribly hard time.  They tend to be the SOA/NS/MX
> type records that I just can't seem to get right.
> 
> So I've decided that perhaps a bit of "back to basics" is in order. My
> plans for this were two-fold.  First, I really wanted to understand
> exactly what was going on at a basic level so I could make my more
> complicated version work. Second, I really struggled with the dlz pgsql
> docs when I first started working with this.  I wanted to be able to
> have a set of example data and example stored procedures that I could
> contribute to the DLZ project so that people in the future who wanted to
> do exactly what I'm attempting to do don't have to struggle as much as I
> have had to.
> 
> Here's what I am currently running into.  My table looks like this:
> 
> CREATE TABLE dns_records (
>     oid SERIAL PRIMARY KEY NOT NULL,
>     zone VARCHAR,
>     ttl INTEGER,
>     recordtype VARCHAR,
>     host VARCHAR,
>     mx_priority INTEGER,
>     data VARCHAR,
>     primary_ns VARCHAR,
>     resp_person VARCHAR,
>     serial INTEGER,
>     refresh INTEGER,
>     retry INTEGER,
>     expire INTEGER,
>     minimum INTEGER
> );
> 
> My queries are directly out of the documentation, but I'll post them
> anyway just to make sure I didn't fat-finger something.
> 
> dlz "postgres zone" {
>     database "postgres 2
>     {host=localhost port=5432 dbname=dns_test user=dns password=xxxxx}
>     {select zone from dns_records where zone = '%zone%'}
>     {select ttl, recordtype, mx_priority, case when 
> lower(recordtype)='txt' then '\"' || data
>         || '\"' else data end from dns_records where zone = '%zone%' and 
> host = '%record%'
>         and not (recordtype = 'SOA' or recordtype = 'NS')}
>     {select ttl, recordtype, mx_priority, data, resp_person, serial, 
> refresh, retry, expire,
>         minimum from dns_records where zone = '%zone%' and (recordtype = 
> 'SOA' or recordtype='NS')}
>     {select ttl, recordtype, host, mx_priority, data, resp_person, 
> serial, refresh, retry, expire,
>         minimum from dns_records where zone = '%zone%'}
>     {select zone from xfr_table where zone = '%zone%' and client = 
> '%client%'}";
> };
> 
> The data format is going to be ugly, so I'll do it as CSV:
> 
> oid,zone,ttl,recordtype,host,mx_priority,data,primary_ns,resp_person,serial,refresh,retry,expire,minimum 
> 
> 1,example.com,300,SOA,,,,ns1.example.com,dnsadmin.example.com,2008011701,8600,8600,8600,8600 
> 
> 2,example.com,300,NS,@,,ns1.example.com,,,,,,,
> 3,example.com,300,NS,@,,ns2.example.com,,,,,,,
> 4,example.com,300,MX,@,10,mail1.example.com,,,,,,,
> 5,example.com,300,MX,@,20,mail2.example.com,,,,,,,
> 6,example.com,300,A,machine1,,10.0.0.1,,,,,,,
> 7,example.com,300,A,machine2,,10.0.0.2,,,,,,,
> 8,0.0.10.in-addr.arpa,300,PTR,1,,machine1.example.com.,,,,,,,
> 9,0.0.10.in-addr.arpa,300,PTR,2,,machine2.example.com.,,,,,,,
> 10,example.com,300,CNAME,www,,machine1,,,,,,,
> 11,example.com,300,CNAME,ftp,,machine2,,,,,,,
> 
> As far as I can tell, this is good data, maybe that's part of the
> problem?  Do I have my data wrong and that's what's causing my problems?
> 
> If I do a simple A record lookup, I get results:
> 
> $ dig @localhost A www.example.com
> ;; ANSWER SECTION:
> www.example.com.        300     IN      CNAME   machine1.example.com.
> machine1.example.com.   300     IN      A       10.0.0.1
> 
> However, this is the last thing in the log file:
> 
> 21-Jan-2008 08:24:59.787 984 query is 'select ttl, recordtype, 
> mx_priority, data, resp_person, serial, refresh, retry, expire,
>                 minimum from dns_records where zone = 'example.com' and 
> (recordtype = 'SOA' or recordtype='NS')'
> 21-Jan-2008 08:24:59.787 984 executing query for 0 time
> 21-Jan-2008 08:24:59.788 984 rs ok
> 21-Jan-2008 08:24:59.788 984 cleaning up
> 21-Jan-2008 08:24:59.788 984 unlocking mutex
> 21-Jan-2008 08:24:59.789 984 returning
> 21-Jan-2008 08:24:59.789 dns_rdata_fromtext: buffer-0xb7a75ee4:1: near 
> eof: unexpected end of input
> 21-Jan-2008 08:24:59.789 dns_sdlz_putrr returned error. Error code was: 
> unexpected end of input
> 
> That query returns these results:
> 
> dns_test=# select ttl, recordtype, mx_priority, data, resp_person, 
> serial, refresh, retry, expire,
> dns_test-# minimum from dns_records where zone = 'example.com' and 
> (recordtype = 'SOA' or recordtype='NS');
>  ttl | recordtype | mx_priority |      data       |     resp_person   
> |   serial   | refresh | retry | expire | minimum
> -----+------------+-------------+-----------------+----------------------+------------+---------+-------+--------+--------- 
> 
>  300 | SOA        |             |                 | dnsadmin.example.com 
> | 2008011701 |    8600 |  8600 |   8600 |    8600
>  300 | NS         |             | ns1.example.com |   |            
> |         |       |        |
>  300 | NS         |             | ns2.example.com |   |            
> |         |       |        |
> (3 rows)
> 
> Is that not correct?  That certainly looks right to me.
> 
> I don't get NS record info back with my queries, so the fact that it's
> failing that last query makes sense that I don't get the data related to
> that.  But what is wrong with that query that it doesn't like?
> 
> If I try to lookup an MX record, I get the same error on the same query.
> A side question to that is why does that query return mx_priority if
> it's looking at only SOA and NS, neither of which would ever have that
> data?
> 
> Since I've taken the queries from the documentation, I can only assume
> I've gotten my data wrong somehow.
> 
> Please help, this is extremely frustrating!!!
> 
> Thanks,
> 
> -brian
> 
> 
> ------------------------------------------------------------------------
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Bind-dlz-testers mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/bind-dlz-testers

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/