Asking Postgres for results._keys that don't exist, in a loop.

What steps will reproduce the problem?
1.  Set up Postgres as a provider (mySQL probably does the same thing)
2.  Have a partially generated result set; in my example, I have 98 million 
passwords, and only 5% of them have results computed.
3.  Set up the ~/.pyrit/config to go to postgres as a default
4.  Change /etc/postgresql/8.3/main/postgresql.conf   - set up
      log_statement = 'all'
5.  Stop and start postgres (/etc/init.d/postgresql-8.3 stop)
     (/etc/init.d/postgresql-8.3 start)
6.  Run pyrit -r WPA.cap -e 'essid' attack_db
7.  Cancel a few seconds later with Ctrl-C, once.
8.  Reset the logging from step 4 to off
9.  stop postgres
10. Look at /var/log/postgresql/postgresql-8.3-main.log
11. In particular, copy and paste one or two of the very repetitive, apparently 
looped statements into pgadmin3's query windows or psql, and REMOVE the 
results_buffer column from the select statement (or pgadmin3 hangs), i.e.
SELECT results._key AS results__key
, results.essid_id AS results_essid_id
, results."numElems" AS "results_numElems"
--, results.results_buffer AS results_results_buffer
        FROM results JOIN essids ON essids.essid_id = results.essid_id
        WHERE essids.essid = E'essid'
        -- AND results._key = E'keyfromfile'
        ORDER BY results._key
         LIMIT 1 OFFSET 0
12.  If you take the LIMIT 1 OFFSET 0 off, and add ORDER BY results._key, you 
should see that at least most of the results._key values that are being 
requested don't exist.

What is the expected output? What do you see instead?
1) I would expect that at the beginning of attack_db, a quick query would be 
run to determine which results._key values exist.
2) I would expect that only results._key values which exist would be asked for.
3) I would expect that results._key would not be in the list of columns 
returned, since it's part of the WHERE clause with an equality clause.
4) I would hope that the LIMIT 1 OFFSET 1 clause would have been tested for 
performance; a brief view leads me to believe that due to unique indexes on 
results and essids, at most 1 row could be returned in the first place, 
rendering the LIMIT pointless.

What version of the product are you using? On what operating system?
Pyrit 0.4.0-dev r288
Backtrack 4 R2 VMWare image (Ubuntu based)
Postgresql-8.3 - OS supplied version

Please provide any additional information below.
I would be happy to assist with the database end, certainly, though my primary 
skills in recent years are with other databases, both administration and 
development.
Original issue reported on code.google.com by Optimiza...@gmail.com on 22 Jan 2011 at 8:10
dwaaan / pyrit

Asking Postgres for results._keys that don't exist, in a loop. #244
