[ psqlodbc-Bugs-1010758 ] Potential memory leak with savepoints and cursors
От | |
---|---|
Тема | [ psqlodbc-Bugs-1010758 ] Potential memory leak with savepoints and cursors |
Дата | |
Msg-id | 20100128235950.E367710715FA@pgfoundry.org обсуждение исходный текст |
Список | pgsql-odbc |
Bugs item #1010758, was opened at 2010-01-28 18:41 You can respond by visiting: http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010758&group_id=1000125 Category: None Group: None Status: Open Resolution: None Priority: 3 Submitted By: Florian Nigsch (fnigsch) Assigned to: Nobody (None) Summary: Potential memory leak with savepoints and cursors Initial Comment: Problem: When executing a large number (100,000s) of queries the server eventually exhausts available memory and crashes. Likely cause: Problem with the release of savepoints; as seen in the PostgreSQL logs below, a savepoint is always createdimmediately before the cursor is closed, and this savepoint is then released once the cursor is closed. Shouldn'tthe savepoint be released before the enclosing cursor is closed? The logs below show that the savepoint creation/releaseis not enclosed by the cursor creation/destruction. psqlODBC driver used: psqlodbc_08_04_0100.zip PostgreSQL server: 8.4.0 LOG: statement: BEGIN;declare "SQL_CUR05898D98" cursor with hold for select * from ...;fetch 100 in "SQL_CUR05898D98" LOG: statement: declare "SQL_CUR0589C5B0" cursor for select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum,a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c. oid, d.adsrc, case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c inner joinpg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid = 632801 8) inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_typet on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum;fetch 100 in "SQL_CUR0589C5B0" LOG: statement: SAVEPOINT _per_query_svp_;close "SQL_CUR0589C5B0";RELEASE _per_query_svp_ LOG: statement: SAVEPOINT _per_query_svp_;close "SQL_CUR05898D98";RELEASE _per_query_svp_ LOG: statement: declare "SQL_CUR05898D98" cursor with hold for select * from ...;fetch 100 in "SQL_CUR05898D98" LOG: statement: SAVEPOINT _per_query_svp_;close "SQL_CUR05898D98";RELEASE _per_query_svp_ LOG: statement: declare "SQL_CUR05898D98" cursor with hold for select * from ...;fetch 100 in "SQL_CUR05898D98" LOG: statement: SAVEPOINT _per_query_svp_;close "SQL_CUR05898D98";RELEASE _per_query_svp_ ---------------------------------------------------------------------- Comment By: Tom Lane (tgl) Date: 2010-01-28 23:59 Message: Hmm ... since my test didn't replicate it, I suspect there is something specific to your query and/or schema that causesthe issue. Please verify that you can reproduce the problem directly in psql (without ODBC), and if so submit a testcase to pgsql-bugs@postgresql.org. ---------------------------------------------------------------------- Comment By: Florian Nigsch (fnigsch) Date: 2010-01-28 23:12 Message: Results with 8.4.2: After 110,000 queries the postgresql server process stands at 2.5GB of memory usage. LOG: statement: SAVEPOINT _EXEC_SVP_01AF8D08 LOG: execute fetch from <unnamed>/SQL_CUR01AF8D08: declare "SQL_CUR01AF8D08" cursor with hold for select * from tt limit 100;fetch 100 in "SQL_CUR01AF8D08" LOG: statement: SAVEPOINT _per_query_svp_;close "SQL_CUR01AF8D08";RELEASE _per_query_svp_ LOG: statement: RELEASE _EXEC_SVP_01AF8D08 LOG: statement: SAVEPOINT _EXEC_SVP_01AF8D08 LOG: statement: declare "SQL_CUR01AF8D08" cursor with hold for select * from tt limit 100;fetch 100 in "SQL_CUR01AF8D08" LOG: statement: RELEASE _EXEC_SVP_01AF8D08 ---------------------------------------------------------------------- Comment By: Florian Nigsch (fnigsch) Date: 2010-01-28 19:48 Message: I just tried with 8.4.1 (will try with 8.4.2 after this post). I executed several 10,000s of queries, always a simple "select * from table limit 100", for every 1,000 queries the postgresql server process consumed approx 25MB of memory. After 75,000 queries it was at 1.7GB, and steadily growing. From the log: LOG: statement: SAVEPOINT _EXEC_SVP_01AF8D08 LOG: execute fetch from <unnamed>/SQL_CUR01AF8D08: declare "SQL_CUR01AF8D08" cursor with hold for select * from tt limit 100;fetch 100 in "SQL_CUR01AF8D08" LOG: statement: SAVEPOINT _per_query_svp_;close "SQL_CUR01AF8D08";RELEASE _per_query_svp_ LOG: statement: RELEASE _EXEC_SVP_01AF8D08 LOG: statement: SAVEPOINT _EXEC_SVP_01AF8D08 LOG: statement: declare "SQL_CUR01AF8D08" cursor with hold for select * from tt limit 100;fetch 100 in "SQL_CUR01AF8D08" LOG: statement: RELEASE _EXEC_SVP_01AF8D08 ---------------------------------------------------------------------- Comment By: Tom Lane (tgl) Date: 2010-01-28 19:03 Message: Try something newer than 8.4.0 --- I don't see a leak with that type of sequence in CVS tip, either HEAD or 8.4 branch. ---------------------------------------------------------------------- You can respond by visiting: http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010758&group_id=1000125
В списке pgsql-odbc по дате отправления:
Предыдущее
От:Дата:
Сообщение: [ psqlodbc-Bugs-1010758 ] Potential memory leak with savepoints and cursors
Следующее
От: "Pairat Paisalyakit"Дата:
Сообщение: Error when update VarBinary field C# psqlodbc_08_04_0200