PQprepare in PostgreSQL 7.4 (lack of SAVEPOINTs)
Is there a way to prevent transaction abort when preparing command in
version 7.4?
I googled but haven't been able to find the answer. The issue occurs when
our software trys to prepare a command that is already prepared (by the same
or different app). PQprepare returns error and aborts current transaction.
The company is not willing to upgrade from 7.4 to a later version due to
risk.
SAVEPOINTs are not available in v.7.4. I'm looking at PQdescribePrepared
function. What is the right solution?
Thank you
Konstantin
On Wed, Jun 23, 2010 at 10:55 PM, Konstantin Izmailov <pgfizm@gmail.com> wrote:
Is there a way to prevent transaction abort when preparing command in
version 7.4?I googled but haven't been able to find the answer. The issue occurs when
our software trys to prepare a command that is already prepared (by the same
or different app). PQprepare returns error and aborts current transaction.
The company is not willing to upgrade from 7.4 to a later version due to
risk.SAVEPOINTs are not available in v.7.4. I'm looking at PQdescribePrepared
function. What is the right solution?
Upgrading to a later version of pgsql, or don't issue that command
twice in a transaction.
The risk of upgrading is less than the risk of staying on an
unsupported version of pgsql. The company that won't upgrade is
making a poorly informed decision.
Scott, thank you for the quick answer!
I think it would work if our software tracks which statements have already
been prepared on the connection.
On Wed, Jun 23, 2010 at 8:25 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
Show quoted text
On Wed, Jun 23, 2010 at 10:55 PM, Konstantin Izmailov <pgfizm@gmail.com>
wrote:Is there a way to prevent transaction abort when preparing command in
version 7.4?I googled but haven't been able to find the answer. The issue occurs when
our software trys to prepare a command that is already prepared (by thesame
or different app). PQprepare returns error and aborts current
transaction.
The company is not willing to upgrade from 7.4 to a later version due to
risk.SAVEPOINTs are not available in v.7.4. I'm looking at PQdescribePrepared
function. What is the right solution?Upgrading to a later version of pgsql, or don't issue that command
twice in a transaction.The risk of upgrading is less than the risk of staying on an
unsupported version of pgsql. The company that won't upgrade is
making a poorly informed decision.
On Thu, Jun 24, 2010 at 12:28 AM, Konstantin Izmailov <pgfizm@gmail.com> wrote:
Scott, thank you for the quick answer!
I think it would work if our software tracks which statements have already
been prepared on the connection.
Good chance of it, but honestly, running such an old and now
unsupported version of pgsql is just asking for trouble. I bet
they're not even on the latest 7.4 version, or close, right?
Getting them onto a newer, supported version should become a priority,
because if you guys show up on the lists with some horrible data loss
problem or bug, and your version is 7.4, you're gonna have a hard time
finding someone who can remember enough about that version to help.
Scott Marlowe <scott.marlowe@gmail.com> writes:
On Wed, Jun 23, 2010 at 10:55 PM, Konstantin Izmailov <pgfizm@gmail.com> wrote:
The company�is not willing to upgrade from 7.4 to a later version due to
risk.
The risk of upgrading is less than the risk of staying on an
unsupported version of pgsql. The company that won't upgrade is
making a poorly informed decision.
Indeed. Point out to them that 7.4 is going to be unsupported after the
end of this month:
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy
If they don't have a plan to get off of 7.4 within the pretty near
future, they're fools.
regards, tom lane
lol
Seriosly, this customer issues resulted in improvement of the way our
driver prepares statements. Keeping the map of prepared statements names is
actually faster than using Savepoints (less roundtrips to server).
I found that DECLARE ... CURSOR FOR ... cannot be prepared. Basically I'm
looking for a way to prepare a complex query and then use cursor for reading
tuples. Is this possible?
This works: PREPARE abcd AS SELECT * FROM test; EXECUTE abcd;
This does not work: PREPARE sdsdsd AS DECLARE csr1 CURSOR FOR SELECT * FROM
test;
This does not work (after prepared the query): DECLARE csr1 CURSOR FOR
EXECUTE abcd;
Thank you!
Konstantin
On Wed, Jun 23, 2010 at 9:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Scott Marlowe <scott.marlowe@gmail.com> writes:
On Wed, Jun 23, 2010 at 10:55 PM, Konstantin Izmailov <pgfizm@gmail.com>
wrote:
The company is not willing to upgrade from 7.4 to a later version due to
risk.The risk of upgrading is less than the risk of staying on an
unsupported version of pgsql. The company that won't upgrade is
making a poorly informed decision.Indeed. Point out to them that 7.4 is going to be unsupported after the
end of this month:
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_PolicyIf they don't have a plan to get off of 7.4 within the pretty near
future, they're fools.regards, tom lane
Looks like other people were asking similar question, but there is no
answer:
http://forums.devshed.com/postgresql-help-21/combine-prepare-and-declare-cursor-437562.html
On Mon, Jun 28, 2010 at 1:00 AM, Konstantin Izmailov <pgfizm@gmail.com>wrote:
Show quoted text
lol
Seriosly, this customer issues resulted in improvement of the way our
driver prepares statements. Keeping the map of prepared statements names is
actually faster than using Savepoints (less roundtrips to server).I found that DECLARE ... CURSOR FOR ... cannot be prepared. Basically I'm
looking for a way to prepare a complex query and then use cursor for reading
tuples. Is this possible?
This works: PREPARE abcd AS SELECT * FROM test; EXECUTE abcd;
This does not work: PREPARE sdsdsd AS DECLARE csr1 CURSOR FOR SELECT * FROM
test;
This does not work (after prepared the query): DECLARE csr1 CURSOR FOR
EXECUTE abcd;Thank you!
Konstantin
On Wed, Jun 23, 2010 at 9:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Scott Marlowe <scott.marlowe@gmail.com> writes:
On Wed, Jun 23, 2010 at 10:55 PM, Konstantin Izmailov <pgfizm@gmail.com>
wrote:
The company is not willing to upgrade from 7.4 to a later version due
to
risk.
The risk of upgrading is less than the risk of staying on an
unsupported version of pgsql. The company that won't upgrade is
making a poorly informed decision.Indeed. Point out to them that 7.4 is going to be unsupported after the
end of this month:
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_PolicyIf they don't have a plan to get off of 7.4 within the pretty near
future, they're fools.regards, tom lane
I have figured it out and everything works wonderfully!!!
Here is test code that allows preparing a query with cursor using libpq
library:
PGconn* conn = PQconnectdb("host=localhost dbname=postgres user=postgres
password=12345 port=5432");
PGresult* res = ::PQexec(conn, "START TRANSACTION", 0, 0); // need
transaction scope for the cursor
res = ::PQprepare(conn, "abcd", "DECLARE cur1 CURSOR FOR SELECT * FROM test",
0, NULL);
res = ::PQexecPrepared(conn, "abcd", 0, 0, 0, 0, 0, 0);
res = ::PQexec(conn, "FETCH FORWARD 100 FROM cur1", 0, 0);
int cColumns = PQnfields(res);
int cRows = PQntuples(res);
for (int field_num = 0; field_num < cColumns; field_num++)
{
char* szName = PQfname(res, field_num);
int nColumnSize = PQfsize(res, field_num);
Oid nType = PQftype(res, field_num);
int nMod = PQfmod(res, field_num);
}
res = ::PQexec(conn, "ABORT", 0, 0);
On Mon, Jun 28, 2010 at 9:11 AM, Konstantin Izmailov <pgfizm@gmail.com>wrote:
Show quoted text
Looks like other people were asking similar question, but there is no
answer:
http://forums.devshed.com/postgresql-help-21/combine-prepare-and-declare-cursor-437562.htmlOn Mon, Jun 28, 2010 at 1:00 AM, Konstantin Izmailov <pgfizm@gmail.com>wrote:
lol
Seriosly, this customer issues resulted in improvement of the way our
driver prepares statements. Keeping the map of prepared statements names is
actually faster than using Savepoints (less roundtrips to server).I found that DECLARE ... CURSOR FOR ... cannot be prepared. Basically I'm
looking for a way to prepare a complex query and then use cursor for reading
tuples. Is this possible?
This works: PREPARE abcd AS SELECT * FROM test; EXECUTE abcd;
This does not work: PREPARE sdsdsd AS DECLARE csr1 CURSOR FOR SELECT *
FROM test;
This does not work (after prepared the query): DECLARE csr1 CURSOR FOR
EXECUTE abcd;Thank you!
Konstantin
On Wed, Jun 23, 2010 at 9:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Scott Marlowe <scott.marlowe@gmail.com> writes:
On Wed, Jun 23, 2010 at 10:55 PM, Konstantin Izmailov <
pgfizm@gmail.com> wrote:
The company is not willing to upgrade from 7.4 to a later version due
to
risk.
The risk of upgrading is less than the risk of staying on an
unsupported version of pgsql. The company that won't upgrade is
making a poorly informed decision.Indeed. Point out to them that 7.4 is going to be unsupported after the
end of this month:
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_PolicyIf they don't have a plan to get off of 7.4 within the pretty near
future, they're fools.regards, tom lane