Обсуждение: ERROR: current transaction is aborted, queries ignored until end of transaction block
ERROR: current transaction is aborted, queries ignored until end of transaction block
От
Annabelle Desbois
Дата:
Hello, "ERROR: current transaction is aborted, queries ignored until end of transaction block" This error occurs after a ROLLBACK in a PHP script. All the next queries fail, so what's wrong ? Why the ROLLBACK isn't the end of a transaction block ? Apache 1.3.26 PHP 4.3.0 PostgreSQL 7.3.1 Thx for your help Annabelle -- ---------------------------------------- Annabelle DESBOIS SA REGIE FRANCE Village informatique - BP 3002 17030 La Rochelle CEDEX tel: 05.46.44.75.76 fax: 05.46.45.34.17 e-mail: a.desbois@regie-france.com ----------------------------------------
That's ok I found the problem : BEGIN WORK ERROR: Cannot insert a duplicate key into unique index cde_lig_pkey ERROR: current transaction is aborted, queries ignored until end of transaction block BEGIN WORK I forgot a ROLLBACK... cia Annabelle Annabelle Desbois wrote: > Hello, > > "ERROR: current transaction is aborted, queries ignored until end of > transaction block" > This error occurs after a ROLLBACK in a PHP script. > All the next queries fail, so what's wrong ? > Why the ROLLBACK isn't the end of a transaction block ? > > Apache 1.3.26 > PHP 4.3.0 > PostgreSQL 7.3.1 > > Thx for your help > > Annabelle > -- ---------------------------------------- Annabelle DESBOIS SA REGIE FRANCE Village informatique - BP 3002 17030 La Rochelle CEDEX tel: 05.46.44.75.76 fax: 05.46.45.34.17 e-mail: a.desbois@regie-france.com ----------------------------------------
On Thu, 17 Jul 2003, Annabelle Desbois wrote: > Hello, > > "ERROR: current transaction is aborted, queries ignored until end of > transaction block" > This error occurs after a ROLLBACK in a PHP script. > All the next queries fail, so what's wrong ? > Why the ROLLBACK isn't the end of a transaction block ? > > Apache 1.3.26 > PHP 4.3.0 > PostgreSQL 7.3.1 Are you sure the rollback is happening? We might need to see some code, not sure. What I think you're saying is that this happens: begin; query1; query2; query3; rollback; ERROR: current transaction is aborted, query4; <- This query now fails IS that the sequence you're describing?
> > "ERROR: current transaction is aborted, queries ignored until end of > > transaction block" So am I the only one that finds this behaviour a little odd? It definitely took me by surprise when I first saw it so I don't think other DB cli interfaces do this. I'm pretty sure Oracle's SQLPlus (no paragon of UI design I admit) just gives an error but lets you type further commands without ignoring them. I don't know what isql does in Sybase/MSSQL. I imagine when you're running scripts as batch processing this logic makes sense. It means you don't have to explicitly handle error conditions using PL/PgSQL or another language, any transaction that fails just automatically rolls back. However it's really annoying when in interactive mode. frequently the error is a simple typo or easily corrected error. It's frustrating to have to start over from scratch after making a minor error in the middle of a complex operation. I'm not sure it's logical either. In an application interface transactions don't automatically rollback after an error. The application receives the error and makes the decision whether to branch to some alternate logic but continue with the transaction or to rollback and pass the error up. I guess most people just always use psql in autocommit mode, but I know once I got used to it I was quite happy that SQLPlus was always in autocommit OFF mode. It meant after doing any manual database updates I could double check that the results were what I expected before committing. More than once I avoided deleting the wrong records or updating fields to the wrong values. It's much easier to rollback and try again to start trying to recover data from backups... But the same practice is much less practical with Postgres because any minor error means starting over from scratch. -- greg
Hi, In fact I forgot the ROLLBACK and was doing a BEGIN after without doing a COMMIT or ROLLBACK before :-[ BEGIN WORK ERROR: Cannot insert a duplicate key into unique index cde_lig_pkey ERROR: current transaction is aborted, queries ignored until end of transaction block BEGIN WORK I found it yesterday by displaying all the queries in my scripts and I send the response to pgsql-general ;-) thx Annabelle PS: I'm sorry, in fact the sequence I described above was wrong :-( I thought there was a ROLLBACK but there wasn't :-P scott.marlowe wrote: >On Thu, 17 Jul 2003, Annabelle Desbois wrote: > > > >>Hello, >> >>"ERROR: current transaction is aborted, queries ignored until end of >>transaction block" >>This error occurs after a ROLLBACK in a PHP script. >>All the next queries fail, so what's wrong ? >>Why the ROLLBACK isn't the end of a transaction block ? >> >>Apache 1.3.26 >>PHP 4.3.0 >>PostgreSQL 7.3.1 >> >> > >Are you sure the rollback is happening? We might need to see some code, >not sure. > >What I think you're saying is that this happens: > >begin; >query1; >query2; >query3; >rollback; >ERROR: current transaction is aborted, >query4; <- This query now fails > >IS that the sequence you're describing? > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > -- ---------------------------------------- Annabelle DESBOIS SA REGIE FRANCE Village informatique - BP 3002 17030 La Rochelle CEDEX tel: 05.46.44.75.76 fax: 05.46.45.34.17 e-mail: a.desbois@regie-france.com ----------------------------------------
Ahhh. Glad you got it working. I can't wait for subtransactions to come along. Feel free to ask questions, it's tough at first getting used to the way postgresql does things, but rewarding once you start to get it. On Fri, 18 Jul 2003, Annabelle Desbois wrote: > Hi, > > In fact I forgot the ROLLBACK and was doing a BEGIN after without doing > a COMMIT or ROLLBACK before :-[ > BEGIN WORK > ERROR: Cannot insert a duplicate key into unique index cde_lig_pkey > ERROR: current transaction is aborted, queries ignored until end of > transaction block > BEGIN WORK > > I found it yesterday by displaying all the queries in my scripts and I > send the response to pgsql-general ;-) > > thx > > Annabelle > > PS: I'm sorry, in fact the sequence I described above was wrong :-( I > thought there was a ROLLBACK but there wasn't :-P > > scott.marlowe wrote: > > >On Thu, 17 Jul 2003, Annabelle Desbois wrote: > > > > > > > >>Hello, > >> > >>"ERROR: current transaction is aborted, queries ignored until end of > >>transaction block" > >>This error occurs after a ROLLBACK in a PHP script. > >>All the next queries fail, so what's wrong ? > >>Why the ROLLBACK isn't the end of a transaction block ? > >> > >>Apache 1.3.26 > >>PHP 4.3.0 > >>PostgreSQL 7.3.1 > >> > >> > > > >Are you sure the rollback is happening? We might need to see some code, > >not sure. > > > >What I think you're saying is that this happens: > > > >begin; > >query1; > >query2; > >query3; > >rollback; > >ERROR: current transaction is aborted, > >query4; <- This query now fails > > > >IS that the sequence you're describing? > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > > >