Re: [GENERAL] Large objects and savepoints - Snapshot reference leak
Thanks Alvaro, that is good to know. At the moment we are stuck with version 9.1.9 and have to stay there at least for Linux. But do I understand correctly, that the warning can be ignored for the moment? On Fri, 2014-01-31 at 15:15 -0300, Alvaro Herrera wrote: Andreas Lubensky wrote: Hi, I'm trying to read/write large objects via libpq. I encapsulated the operations in a transaction but I wanted to put a savepoint before doing any operations, so I can do a rollback in case anything fails without breaking the current transaction. Now, when sth. actually fails and the transaction is rolled back to the savepoint, the next commit results in a warning: Snapshot reference leak: Snapshot 0xb5e4b0 still referenced I'm not sure what to make of that. Can it be ignored? Is rolling back large object operations not possible? What vresion are you running? I wonder if this can be attributed to a bug fixed by this commit: Author: Heikki Linnakangas heikki.linnakan...@iki.fi Branch: master [357f75213] 2013-09-30 12:53:14 +0300 Branch: REL9_3_STABLE Release: REL9_3_1 [f609d0743] 2013-09-30 12:53:56 +0300 Branch: REL9_2_STABLE Release: REL9_2_5 [fc7a38f32] 2013-09-30 12:54:37 +0300 Branch: REL9_1_STABLE Release: REL9_1_10 [cd6c03b5c] 2013-09-30 12:55:57 +0300 Branch: REL9_0_STABLE Release: REL9_0_14 [c5c87f065] 2013-09-30 12:58:51 +0300 Branch: REL8_4_STABLE Release: REL8_4_18 [fef01d419] 2013-09-30 13:00:00 +0300 Fix snapshot leak if lo_open called on non-existent object. lo_open registers the currently active snapshot, and checks if the large object exists after that. Normally, snapshots registered by lo_open are unregistered at end of transaction when the lo descriptor is closed, but if we error out before the lo descriptor is added to the list of open descriptors, it is leaked. Fix by moving the snapshot registration to after checking if the large object exists. Reported by Pavel Stehule. Backpatch to 8.4. The snapshot registration system was introduced in 8.4, so prior versions are not affected (and not supported, anyway). -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- with best regards, Andreas Lubensky Software Engineer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Large objects and savepoints - Snapshot reference leak
Hi, I'm trying to read/write large objects via libpq. I encapsulated the operations in a transaction but I wanted to put a savepoint before doing any operations, so I can do a rollback in case anything fails without breaking the current transaction. Now, when sth. actually fails and the transaction is rolled back to the savepoint, the next commit results in a warning: Snapshot reference leak: Snapshot 0xb5e4b0 still referenced I'm not sure what to make of that. Can it be ignored? Is rolling back large object operations not possible? -- with best regards, Andreas Lubensky Software Engineer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Prepared statements and cursors
That is an interesting approach. However, I see the problem that the functions would have to be removed when no longer needed. If that fails (broken connection etc.), they would be orphaned. Prepared statements are bound to the connection, so when the connection is closed they are gone. On Thu, 2014-01-23 at 15:07 -0600, Merlin Moncure wrote: On Thu, Jan 23, 2014 at 8:31 AM, Andreas Lubensky luben...@cognitec.com wrote: Hello, When implementing a database backend with libpq I realized that it seems to be impossible to declare a cursor on a prepared statement. Is this correct? What is the reason for this limitation? I can't think of any but it can be trivially worked around: create or replace function f() returns void as $$ declare v cursor for select 0; $$ language sql; prepare p as select f(); postgres=# begin; BEGIN postgres=# execute p; f --- (1 row) postgres=# fetch all from v; ?column? -- 0 merlin -- with best regards, Andreas Lubensky Software Engineer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Prepared statements and cursors
Hello, When implementing a database backend with libpq I realized that it seems to be impossible to declare a cursor on a prepared statement. Is this correct? What is the reason for this limitation? -- with best regards, Andreas Lubensky -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Positional parameters and question mark notation
Hi, Is there any way to make PostgreSQL support the question mark (?) notation instead of the dollar sign ($1, $2...) for prepared statements? Most databases use the question mark notation and we have a set of simple queries that are supposed to be portable across different database systems. So far we use PostgreSQL via ODBC, but now we want to switch to a native libpq implementation. -- with best regards, Andreas Lubensky -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Positional parameters and question mark notation
Hi Dmitriy, I considered just doing a string replacement, however this might be potentially dangerous because in theory a query might contain a question mark as part of a string or similar. Although I'm certain it would work for us at the moment, it looks like a problem waiting to happen. Doing a proper replacement (as ODBC probably does) most likely requires a full blown SQL parser... On Mon, 2014-01-20 at 14:08 +0400, Dmitriy Igrishin wrote: Hey Andreas, 2014/1/20 Andreas Lubensky luben...@cognitec.com Hi, Is there any way to make PostgreSQL support the question mark (?) notation instead of the dollar sign ($1, $2...) for prepared statements? Most databases use the question mark notation and we have a set of simple queries that are supposed to be portable across different database systems. So far we use PostgreSQL via ODBC, but now we want to switch to a native libpq implementation. I believe that C-function for replacing '?' with '$n' can easily implemented in the application code. Or you may want to look at libpqtypes: http://libpqtypes.esilo.com/ Or (if you are C++) you may want to look at SOCI: http://soci.sourceforge.net/ -- // Dmitriy. -- with best regards, Andreas Lubensky -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ODBC and bytea
Hi, currently we are trying to integrate Postgres with ODBC and have problems with blobs. We tried to use bytea and were under the impression that bytea would act like a blob in other databases when used through ODBC. So far we could not make it work properly. It seems we still have to do the encoding/decoding of the binary data to bytea ourselves. Is this the correct behavior? I guess we are not the only ones using ODBC and trying to store blobs in Postgres. But nevertheless there is nearly no information on this subject to be found. Any help appreciated :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
