Thread: BUG #1482: SQL ERROR IN 7.4.7

BUG #1482: SQL ERROR IN 7.4.7

From
"Shujun Huang"
Date:
The following bug has been logged online:

Bug reference:      1482
Logged by:          Shujun Huang
Email address:      shujun.huang@noaa.gov
PostgreSQL version: 7.4.7
Operating system:   RHEL3
Description:        SQL ERROR IN 7.4.7
Details:

RENCENTLY I RAN INTO THIS ERROR CODE 25P01 WHICH IS "NO ACTIVE SQL
TRNASACTION". THE POSTMASTER IS RUNNING OK. THIS HAPPENED AFTER WE UPGRADED
FROM 7.4.6 TO 7.4.7.
ANY INSERT/UPDATE QUERY RUNS OK, A SIMPLE RETRIEVE QUERY IS ALSO RUNNING OK,
ANY RETRIEVE QUERIES INVOLVE IN CURSOR FETCHING IS NOT WORKING.
IT GIVES ABOVE ERROR. IF THERE ARE MORE THAN ONE COURSORS IN A ROUTINE, THE
ERROR CODE IS 34000 WHICH SAYS "IHVALID CURSOR NAME".
ANY IDEA WHAT WAS WRONG?

Re: BUG #1482: SQL ERROR IN 7.4.7

From
Neil Conway
Date:
Shujun Huang wrote:
> RENCENTLY I RAN INTO THIS ERROR CODE 25P01 WHICH IS "NO ACTIVE SQL
> TRNASACTION". THE POSTMASTER IS RUNNING OK. THIS HAPPENED AFTER WE UPGRADED
> FROM 7.4.6 TO 7.4.7.
> ANY INSERT/UPDATE QUERY RUNS OK, A SIMPLE RETRIEVE QUERY IS ALSO RUNNING OK,
> ANY RETRIEVE QUERIES INVOLVE IN CURSOR FETCHING IS NOT WORKING.
> IT GIVES ABOVE ERROR. IF THERE ARE MORE THAN ONE COURSORS IN A ROUTINE, THE
> ERROR CODE IS 34000 WHICH SAYS "IHVALID CURSOR NAME".
> ANY IDEA WHAT WAS WRONG?

Your caps lock key appears to be broken, for starters :)

Non-holdable cursors can only be accessed inside the SQL transaction
that created them. Cursor names must also be session-unique, valid
identifiers. But it is difficult to say more about the cause of the
problem without more information. Please provide the exact sequence of
queries that cause the error messages; one way to find this information
is to set "log_statement = 'all'" in postgresql.conf

-Neil