{"action":"created","issue":{"url":"https://api.github.com/repos/brianc/node-postgres/issues/1650","repository_url":"https://api.github.com/repos/brianc/node-postgres","labels_url":"https://api.github.com/repos/brianc/node-postgres/issues/1650/labels{/name}","comments_url":"https://api.github.com/repos/brianc/node-postgres/issues/1650/comments","events_url":"https://api.github.com/repos/brianc/node-postgres/issues/1650/events","html_url":"https://github.com/brianc/node-postgres/issues/1650","id":323321349,"number":1650,"title":"Using graphql on large result errors out","user":{"login":"adviner","id":2251324,"avatar_url":"https://avatars0.githubusercontent.com/u/2251324?v=4","gravatar_id":"","url":"https://api.github.com/users/adviner","html_url":"https://github.com/adviner","followers_url":"https://api.github.com/users/adviner/followers","following_url":"https://api.github.com/users/adviner/following{/other_user}","gists_url":"https://api.github.com/users/adviner/gists{/gist_id}","starred_url":"https://api.github.com/users/adviner/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/adviner/subscriptions","organizations_url":"https://api.github.com/users/adviner/orgs","repos_url":"https://api.github.com/users/adviner/repos","events_url":"https://api.github.com/users/adviner/events{/privacy}","received_events_url":"https://api.github.com/users/adviner/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2018-05-15T17:55:20Z","updated_at":"2018-05-16T00:17:00Z","closed_at":null,"author_association":"NONE","body":"I'm using the following in a web application\r\nnode 10.10\r\npostgres 10\r\nOS: Windows/Linux\r\n\r\nI'm using the following code to make my queries:\r\n\r\nFile: index.js\r\nconst dbPool = new Pool(nconf.get(`${process.env.NODE_ENV}:venuePool`));\r\nconst dbContext = DBContext(dbPool);\r\n\r\nlet options = {\r\n    dbContext: dbContext,\r\n    ...\r\n}\r\n\r\napp.use('*', (request, response, next) => {\r\nreq.venue = options;\r\nnext();\r\n});\r\n\r\nThen in the grapql I pull the options.dbContext to use select1(), select() etc\r\n\r\n\r\nFile: pg_context.js\r\n\r\nmodule.exports = (db_context) => {\r\n\r\n\tlet context = db_context;\r\n\r\n\tconst api = {\r\n\r\n\t\tselect1: async(sql, params = [])  => {\r\n\t\t\ttry\r\n\t\t\t{\r\n\t\t\t\tconst res = await context.query(sql, params);\r\n\t\t\t\tif(res !== undefined && res.rows.length > 0)\r\n\t\t\t\t\treturn res.rows[0];\r\n\t\t\t\telse\r\n\t\t\t\t\tthrow new Error('No entry found');\r\n\t\t\t}\r\n\t\t\tcatch(err){\r\n\t\t\t\tthrow new Error(err);\r\n\t\t\t}\r\n\t\t},\r\n\r\n\t\tselect: async(sql, params = [])  => {\r\n\r\n\t\t\ttry\r\n\t\t\t{\r\n\t\t\t\tconst res = await context.query(sql, params);\r\n\t\t\t\tif(res !== undefined)\r\n\t\t\t\t\treturn res.rows;\r\n\t\t\t\telse\r\n\t\t\t\t\tthrow new Error('No entry found');\r\n\t\t\t}\r\n\t\t\tcatch(err){\r\n\t\t\t\tthrow new Error(err);\r\n\t\t\t}\r\n\t\t}\t\r\n\t}\r\n}\t\t\r\n\r\nMy database config params:\r\n\r\n            \"ssl\": false,\r\n            \"max\": 200,\r\n            \"idleTimeoutMillis\": 1000,\r\n            \"connectionTimeoutMillis\": 10000\r\n\r\nMy postgres conf (using pgtune)\r\n\r\n\tmax_connections = 200\r\n\tshared_buffers = 512MB\r\n\teffective_cache_size = 6GB\r\n\tmaintenance_work_mem = 512MB\r\n\tcheckpoint_completion_target = 0.7\r\n\twal_buffers = 16MB\r\n\tdefault_statistics_target = 100\r\n\trandom_page_cost = 1.1\r\n\twork_mem = 13107kB\r\n\tmin_wal_size = 1GB\r\n\tmax_wal_size = 2GB\r\n\r\nmy graphql query:\r\n\r\n\tquery Q($first_name:String!, $last_name:String!,\r\n\t\t\t$initial_book_date_option:String!, $order_by:JSON!){   \r\n\t\t\r\n\t  queryInmates(first_name:$first_name, last_name:$last_name){\r\n\t\tid, first_name, last_name, middle_name, sex\r\n\t\tjail_inmate{\r\n\t\t  id, inmate_id, number, release_date,\r\n\t\t  jail_bookings(initial_book_date_option:$initial_book_date_option,\r\n\t\t  order_by:$order_by){\r\n\t\t\tid, number, initial_book_date, current_intake_date, release_date\r\n\t\t  }\r\n\t\t}\r\n\t  }\r\n\t}\r\n\r\nSo this return a large result and the first set of the result has data, but eventually, the data will have errors in the array.\r\n\r\nex:\r\n\r\n    {\r\n      \"message\": \"Error: Error: connect ECONNREFUSED 127.0.0.1:5432\",\r\n      \"locations\": [\r\n        {\r\n          \"line\": 6,\r\n          \"column\": 5\r\n        }\r\n      ],\r\n      \"path\": [\r\n        \"queryInmates\",\r\n        243,\r\n        \"jail_inmate\"\r\n      ]\r\n    },\r\n     {\r\n        \"id\": \"f29874d0-5202-11e8-b048-17a13476491e\",\r\n        \"first_name\": \"KIRSTI ANNE\",\r\n        \"last_name\": \"QUINTANAR\",\r\n        \"middle_name\": \"ELIZABETH\",\r\n        ...\r\n     }\r\n\r\nI thought that const res = await context.query(sql, params);\r\nwould automatically return to the pool.  Am I missing anything?  Am I not using the pool correctly, since this seems to be the issue of running out?  I'm only using one Pool() instance for all web request.  Should I make a new instance for each web request?\r\n\r\n I even tried using the promise with the same results example:\r\n\r\n\t\tselect1: async (sql, params = []) => {\r\n\r\n\t\t\treturn new Promise((resolve, reject) => {\r\n\t\t\t\r\n\t\t        context.connect((err, client, done) => {\r\n\r\n\t\t            if (err) {\r\n\r\n\t\t            \tdone();\r\n\t\t                reject(err.message);\r\n\r\n\t\t            } else {\r\n\r\n\t\t                client.query(sql, params, (err, results) => {\r\n\r\n\t\t                \tclient.release();\r\n\r\n\t\t                    if (err) {\r\n\t\t                        reject(err.message);\r\n\t\t                    } else {\r\n\t\t\t\t\t\t\t\tif(results !== undefined && results.rows.length > 0)\r\n\t\t\t\t\t\t\t\t\tresolve(results.rows[0]);\r\n\t\t\t\t\t\t\t\telse\r\n\t\t\t\t\t\t\t\t\treject('No entry found');\r\n\t\t                    }\r\n\t\t                });\r\n\t\t            }\r\n\t\t        });\r\n\t\t\t});\r\n\t\t},\r\n\r\nFor the sake of testing I increased postgres \r\nmax_connections = 1000 and node pg \"max\": 1000\r\nI get less of the ECONNREFUSED.\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/brianc/node-postgres/issues/comments/389354852","html_url":"https://github.com/brianc/node-postgres/issues/1650#issuecomment-389354852","issue_url":"https://api.github.com/repos/brianc/node-postgres/issues/1650","id":389354852,"user":{"login":"charmander","id":1889843,"avatar_url":"https://avatars1.githubusercontent.com/u/1889843?v=4","gravatar_id":"","url":"https://api.github.com/users/charmander","html_url":"https://github.com/charmander","followers_url":"https://api.github.com/users/charmander/followers","following_url":"https://api.github.com/users/charmander/following{/other_user}","gists_url":"https://api.github.com/users/charmander/gists{/gist_id}","starred_url":"https://api.github.com/users/charmander/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/charmander/subscriptions","organizations_url":"https://api.github.com/users/charmander/orgs","repos_url":"https://api.github.com/users/charmander/repos","events_url":"https://api.github.com/users/charmander/events{/privacy}","received_events_url":"https://api.github.com/users/charmander/received_events","type":"User","site_admin":false},"created_at":"2018-05-16T00:17:00Z","updated_at":"2018-05-16T00:17:00Z","author_association":"COLLABORATOR","body":"> My database config params:\r\n>\r\n> ```javascript\r\n>         \"ssl\": false,\r\n>         \"max\": 200,\r\n>         \"idleTimeoutMillis\": 1000,\r\n>         \"connectionTimeoutMillis\": 10000\r\n> ```\r\n\r\nIf `max_connections` is the same as the pool max, any connections coming from elsewhere will cause problems. Does setting this to the pg default of 10 stop the errors?\r\n\r\n> I'm only using one Pool() instance for all web request. Should I make a new instance for each web request?\r\n\r\nOne instance for all requests is correct."}}
{"actor":{"display_login":"charmander"}}