{"action":"opened","issue":{"url":"https://api.github.com/repos/GeoscienceAustralia/agdc/issues/13","labels_url":"https://api.github.com/repos/GeoscienceAustralia/agdc/issues/13/labels{/name}","comments_url":"https://api.github.com/repos/GeoscienceAustralia/agdc/issues/13/comments","events_url":"https://api.github.com/repos/GeoscienceAustralia/agdc/issues/13/events","html_url":"https://github.com/GeoscienceAustralia/agdc/issues/13","id":55779446,"number":13,"title":"DB connections should span api calls","user":{"login":"smr547","id":4327895,"avatar_url":"https://avatars.githubusercontent.com/u/4327895?v=3","gravatar_id":"","url":"https://api.github.com/users/smr547","html_url":"https://github.com/smr547","followers_url":"https://api.github.com/users/smr547/followers","following_url":"https://api.github.com/users/smr547/following{/other_user}","gists_url":"https://api.github.com/users/smr547/gists{/gist_id}","starred_url":"https://api.github.com/users/smr547/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smr547/subscriptions","organizations_url":"https://api.github.com/users/smr547/orgs","repos_url":"https://api.github.com/users/smr547/repos","events_url":"https://api.github.com/users/smr547/events{/privacy}","received_events_url":"https://api.github.com/users/smr547/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/GeoscienceAustralia/agdc/labels/question","name":"question","color":"cc317c"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-01-28T16:31:35Z","updated_at":"2015-01-28T16:31:35Z","closed_at":null,"body":"More a suggestion than a question...\r\n\r\nThe query API currently returns a generator inviting applications to iterate over Tiles and Cells. The generator will hold a database connection (and cursor) open until iteration is complete. Connections and cursors are heavyweight objects (particularly for the DB server). Application Tile processing may be long running and connections may be held open for long periods of time. \r\n\r\nThe concern is that this approach will not scale on HPC where there may be many thousands of applications instances each slowly iterating sets of Tiles. The single DB server can become stressed having to maintain many long-duration cursors. Also connection limits may be reached resulting in denial of service to application instances.\r\n\r\nSuggest using\r\n\r\n```python\r\n    cursor.execute(sql, params)\r\n    tiles = []\r\n       \r\n   for record in cursor:\r\n       tiles.append(Tile.from_db_record(record))\r\n\r\n   conn.close()\r\n   return tiles\r\n```\r\nInstead of\r\n\r\n```python\r\n    for record in result_generator(cursor):\r\n        yield Tile.from_db_record(record)\r\n```\r\n\r\nThe former has the advantage of ensuring that cursors and connections are explicitly closed and, more importantly, have very short life spans on the DB server. The memory and time cost of transporting the full result set to the application host ought to be modest given our Tile numbers and the lightweight nature of Tile instances. "}}