Error with DefineSavepoint:Unexpected state STARTED
Hi,
I am working on a project of DB migration from Oracle to Postgres. The
application is working well with Oracle. But when switch to Postgres, some
SQL executionss couldn't finish and got an error like:
FATAL: DefineSavepoint: unexpected state STARTED
STATEMENT: SAVEPOINT_per_query_svp;DEALLOCATE
"_PLAN04824560";RELEASE_per_query_SVP_
The below is the c++ code to execute SQL to complete the operation. From
UI, it tries to add/remove some values (remove or insert some records into
table). When running application, nothing happens but with the error above
on DB server side. I did some search for this error, looks not too much.
Any suggestions to fix this issue?
Thanks..
...................................
try {
_statement->prepareStatement( SQL_DELETE_SACTIONS_BY_SID_AND_CID );
_statement->setUnsignedInteger( 1, criteria->cid );
_statement->setUnsignedInteger( 2, criteria->sid );
_statement->execute();
for( size_t i = 0; i < ( criteria->sActions->size() ); i++ ) {
_statement->prepareStatement( SQL_INSERT_SACTION_BY_SID_AND_CID );
_statement->setUnsignedInteger( 1, criteria->cid );
_statement->setUnsignedInteger( 2, criteria->sid );
_statement->setString( 3, ( unsigned int )( ( *( criteria->sActions )
)[i].length() ), ( *( criteria->sActions ) )[i].c_str() );
if( _statement->execute() != ServiceConstants::SUCCESS ) {
return result;
}
}
result = true;
_statement->prepareStatement( "COMMIT" );
_statement->execute();
} catch( ServiceException ex ) {
DatabaseUtilities::logServiceException(
ex, "UpdateBySidAndCid" );
}
return result;
}
const char * const UpdateBySidAndCid::SQL_DELETE_SACTIONS_BY_SID_AND_CID
= "DELETE FROM tableA WHERE Cid = ? AND Sid = ?";
const char * const UpdateBySidAndCid::SQL_INSERT_SACTION_BY_SID_AND_CID
= "INSERT INTO tableA (Cid, Siid, SActionUid) SELECT ?, ?, SActionUid
FROM tableB WHERE SActionName = ?";
Computer Study <computerstudy2010@gmail.com> writes:
I am working on a project of DB migration from Oracle to Postgres. The
application is working well with Oracle. But when switch to Postgres, some
SQL executionss couldn't finish and got an error like:
FATAL: DefineSavepoint: unexpected state STARTED
STATEMENT: SAVEPOINT_per_query_svp;DEALLOCATE
"_PLAN04824560";RELEASE_per_query_SVP_
Well, that's pretty interesting, but you haven't provided nearly enough
information for anyone else to reproduce the problem. (No, a snippet of
C++ that depends on undescribed subroutines and uncertain context doesn't
do it.)
Perhaps you could turn on log_statement = all and capture the SQL that's
actually being sent to the server by this program?
Also, what version of Postgres is this exactly?
Any suggestions to fix this issue?
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks Tom.
The version of Postgres is: 9.4.7
The UI application is to add/remove some permissions through a listbox for
a certain user. For any add/remove, it will first delete all records for
this certain user in the table, then insert the values chose from UI.
In my code snippet, it first execute the DELETE, then execute multiple
INSERT to insert selected options into the table. After all done, commit.
The server is in remote and I don't have detailed logs at this moment. Will
try to ask for that.
My question: for that error 'DefineSavepoint: unexpected state STARTED',
not quite sure happens in which step? In the middle of DELETE or INSERT?
On Mon, Jun 19, 2017 at 9:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Computer Study <computerstudy2010@gmail.com> writes:
I am working on a project of DB migration from Oracle to Postgres. The
application is working well with Oracle. But when switch to Postgres,some
SQL executionss couldn't finish and got an error like:
FATAL: DefineSavepoint: unexpected state STARTED
STATEMENT: SAVEPOINT_per_query_svp;DEALLOCATE
"_PLAN04824560";RELEASE_per_query_SVP_Well, that's pretty interesting, but you haven't provided nearly enough
information for anyone else to reproduce the problem. (No, a snippet of
C++ that depends on undescribed subroutines and uncertain context doesn't
do it.)Perhaps you could turn on log_statement = all and capture the SQL that's
actually being sent to the server by this program?Also, what version of Postgres is this exactly?
Any suggestions to fix this issue?
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
regards, tom lane
On Tue, Jun 20, 2017 at 11:12 AM, Computer Study
<computerstudy2010@gmail.com> wrote:
The UI application is to add/remove some permissions through a listbox for
a certain user. For any add/remove, it will first delete all records for
this certain user in the table, then insert the values chose from UI.In my code snippet, it first execute the DELETE, then execute multiple
INSERT to insert selected options into the table. After all done, commit.The server is in remote and I don't have detailed logs at this moment. Will
try to ask for that.My question: for that error 'DefineSavepoint: unexpected state STARTED',
not quite sure happens in which step? In the middle of DELETE or INSERT?
This error has been around for a while, and funnily you are the second
reporter of this issue within a couple of weeks:
/messages/by-id/0A3221C70F24FB45833433255569204D1F6BE40D@G01JPEXMBYT05
I have been thinking a bit about how to fix that, and wondered about
using a new transaction status to track that, but that finished by
being rather intrusive..
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general