Thread: Re: [QUESTIONS] errors on transactions and locks ?
On Tue, 21 Apr 1998, Herouth Maoz wrote:
Your example is very exhaustive Herouth. I tried it with SOLID and in fact
it leaves SOLID database inconsistent.
I see that PostgreSQL BEGIN/END statements are slight different from SQL
transactions that begins with a implicit begin transaction (no BEGIN command)
and ends with a ROLLBACK or COMMIT statement.
Until now I thought that END was equal to COMMIT but in the case of:
NOTICE: (transaction aborted): queries ignored until END
*ABORT STATE*
in this case END stands for ROLLBACK/ABORT I think it isn't enough clear.
(I thought all reference to END were changed to COMMIT).
PostgreSQL don't say to the user that all his work will be lost even if he do
COMMIT.
Maybe the following warn is more clear:
NOTICE: (transaction aborted): queries ignored until COMMIT/ROLLBAK
WARN: all changes will be lost even if you use COMMIT.
Of course SQL transaction allows all kind of SQL command because it doesn't
works outside transactions.
PostgreSQL is more restrictive than SQL, then I think we need to know
what kind of statements we can use successful inside transactions and
PostgreSQL should reject all invalid commands.
(I have to change information on BEGIN reference manual page, we have to
document this feature of PostgreSQL).
I've tried the following commands:
o CREATE TABLE works.
o DROP TABLE doesn't work properly after ROLLBACK, the table lives
but it's empty.
o CREATE/DROP INDEX works.
o CREATE/DROP SEQUENCE works.
o CREATE/DROP USER works.
o GRANT/REVOKE works.
o DROP VIEW works.
o CREATE VIEWS aborts transactions see below:
o DROP AGGREGATE works.
o CREATE AGGREGATE doesn't work.
o DROP FUNCTION works.
o CREATE FUNCTION doesn't work.
o ALTER TABLE seems that doesn't work properly see below:
o CREATE/DROP DATABASE removes references from "pg_database" but
don't remove directory /usr/local/pgsql/data/base/name_database.
...Maybe somebody knows what more is valid/invalid inside transactions...
o EXAMPLE ALTER TABLE:
postgres=> begin;
BEGIN
postgres=> \d a
Table = a
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| a | int2 | 2 |
+----------------------------------+----------------------------------+-------+
postgres=> select * from a;
a
-----
32767
(1 rows)
postgres=> alter table a add b int;
ADD
postgres=> \d a
Table = a
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| a | int2 | 2 |
| b | int4 | 4 |
+----------------------------------+----------------------------------+-------+
postgres=> select * from a;
a|b
-----+-
32767|
(1 rows)
postgres=> rollback;
ABORT
postgres=> \d a
Table = a
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| a | int2 | 2 |
+----------------------------------+----------------------------------+-------+
postgres=> select * from a;
a|b <------------------ column b is already here. Why ?
-----+-
32767|
(1 rows)
postgres=> rollback;
ABORT
postgres=> \d a
Table = a
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| a | int2 | 2 |
+----------------------------------+----------------------------------+-------+
postgres=> select * from a;
a|b
-----+-
32767|
(1 rows)
o EXAMPLE CREATE VIEW:
postgres=> begin;
BEGIN
postgres=> create view error as select * from films;
CREATE
postgres=> \d error
Table = error
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| code | char() | 5 |
| title | varchar() | 40 |
| did | int4 | 4 |
| date_prod | date | 4 |
| kind | char() | 10 |
| len | int2 | 2 |
+----------------------------------+----------------------------------+-------+
postgres=> select * from error;
PQexec() -- Request was sent to backend, but backend closed the channel before responding.
This probably means the backend terminated abnormally before or while processing the request.
> At 16:15 +0100 on 21/4/98, Jose' Soares Da Silva wrote:
>
>
> > * Bad, this isn't very friendly.
> >
> > * No. What I would is that PostgreSQL don't abort at every smallest
> > syntax error.
>
> It depends on what you expect from a transaction. The way I see it, a
> transaction is a sequence of operations which either *all* succeed, or
> *all* fail. That is, if one of the operations failed, even for a syntax
> error, Postgres should not allow any of the other operations in the same
> transaction to work.
>
> For example, suppose you want to move money from one bank account to
> another, you'll do something like:
>
> BEGIN;
>
> UPDATE accounts
> SET credit = credit - 20,000
> WHERE account_num = '00-xx-00';
>
> UPDATE accounts
> SET credit = credit + 20000
> WHERE account_num = '11-xx-11';
>
> END;
>
> Now, look at this example. There is a syntax error in the first update
> statement - 20,000 should be without a comma. If Postgres were tolerant,
> your client would have an extra 20,000 dollars in one of his account, and
> the money came from nowhere, which means your bank loses it, and you lose
> your job...
>
> But a real RDBMS, as soon as one of the statement fails - no matter why -
> the transaction would not happen. It notifies you that it didn't happen.
> You can then decide what to do - issue a different transaction, fix the
> program, whatever.
>
> The idea is that the two actions (taking money from one account and putting
> it in another) are considered atomic, inseparable, and dependent. If your
> "real world" thinking says that the next operation should happen, no matter
> if the first one succeeded or failed, then they shouldn't be inside the
> same transaction.
>
> Herouth
>
> On Tue, 21 Apr 1998, Herouth Maoz wrote:
>
> Your example is very exhaustive Herouth. I tried it with SOLID and in fact
> it leaves SOLID database inconsistent.
>
> I see that PostgreSQL BEGIN/END statements are slight different from SQL
> transactions that begins with a implicit begin transaction (no BEGIN command)
> and ends with a ROLLBACK or COMMIT statement.
>
> Until now I thought that END was equal to COMMIT but in the case of:
> NOTICE: (transaction aborted): queries ignored until END
> *ABORT STATE*
> in this case END stands for ROLLBACK/ABORT I think it isn't enough clear.
> (I thought all reference to END were changed to COMMIT).
> PostgreSQL don't say to the user that all his work will be lost even if he do
> COMMIT.
>
> Maybe the following warn is more clear:
> NOTICE: (transaction aborted): queries ignored until COMMIT/ROLLBAK
> WARN: all changes will be lost even if you use COMMIT.
I have changed the text to read:
"all queries ignored until end of transaction block");
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
I think 6.6 will improve this. > On Tue, 21 Apr 1998, Herouth Maoz wrote: > > Your example is very exhaustive Herouth. I tried it with SOLID and in fact > it leaves SOLID database inconsistent. > > I see that PostgreSQL BEGIN/END statements are slight different from SQL > transactions that begins with a implicit begin transaction (no BEGIN command) > and ends with a ROLLBACK or COMMIT statement. > > Until now I thought that END was equal to COMMIT but in the case of: > NOTICE: (transaction aborted): queries ignored until END > *ABORT STATE* > in this case END stands for ROLLBACK/ABORT I think it isn't enough clear. > (I thought all reference to END were changed to COMMIT). > PostgreSQL don't say to the user that all his work will be lost even if he do > COMMIT. > > Maybe the following warn is more clear: > NOTICE: (transaction aborted): queries ignored until COMMIT/ROLLBAK > WARN: all changes will be lost even if you use COMMIT. > > Of course SQL transaction allows all kind of SQL command because it doesn't > works outside transactions. > > PostgreSQL is more restrictive than SQL, then I think we need to know > what kind of statements we can use successful inside transactions and > PostgreSQL should reject all invalid commands. > > (I have to change information on BEGIN reference manual page, we have to > document this feature of PostgreSQL). > > I've tried the following commands: > o CREATE TABLE works. > o DROP TABLE doesn't work properly after ROLLBACK, the table lives > but it's empty. > o CREATE/DROP INDEX works. > o CREATE/DROP SEQUENCE works. > o CREATE/DROP USER works. > o GRANT/REVOKE works. > o DROP VIEW works. > o CREATE VIEWS aborts transactions see below: > o DROP AGGREGATE works. > o CREATE AGGREGATE doesn't work. > o DROP FUNCTION works. > o CREATE FUNCTION doesn't work. > o ALTER TABLE seems that doesn't work properly see below: > o CREATE/DROP DATABASE removes references from "pg_database" but > don't remove directory /usr/local/pgsql/data/base/name_database. > ...Maybe somebody knows what more is valid/invalid inside transactions... > > o EXAMPLE ALTER TABLE: > > postgres=> begin; > BEGIN > postgres=> \d a > > Table = a > +----------------------------------+----------------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------------+-------+ > | a | int2 | 2 | > +----------------------------------+----------------------------------+-------+ > postgres=> select * from a; > a > ----- > 32767 > (1 rows) > postgres=> alter table a add b int; > ADD > postgres=> \d a > > Table = a > +----------------------------------+----------------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------------+-------+ > | a | int2 | 2 | > | b | int4 | 4 | > +----------------------------------+----------------------------------+-------+ > postgres=> select * from a; > a|b > -----+- > 32767| > (1 rows) > > postgres=> rollback; > ABORT > postgres=> \d a > > Table = a > +----------------------------------+----------------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------------+-------+ > | a | int2 | 2 | > +----------------------------------+----------------------------------+-------+ > postgres=> select * from a; > a|b <------------------ column b is already here. Why ? > -----+- > 32767| > (1 rows) > postgres=> rollback; > ABORT > postgres=> \d a > > Table = a > +----------------------------------+----------------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------------+-------+ > | a | int2 | 2 | > +----------------------------------+----------------------------------+-------+ > postgres=> select * from a; > a|b > -----+- > 32767| > (1 rows) > > > o EXAMPLE CREATE VIEW: > > postgres=> begin; > BEGIN > postgres=> create view error as select * from films; > CREATE > postgres=> \d error > > Table = error > +----------------------------------+----------------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------------+-------+ > | code | char() | 5 | > | title | varchar() | 40 | > | did | int4 | 4 | > | date_prod | date | 4 | > | kind | char() | 10 | > | len | int2 | 2 | > +----------------------------------+----------------------------------+-------+ > postgres=> select * from error; > PQexec() -- Request was sent to backend, but backend closed the channel before responding. > This probably means the backend terminated abnormally before or while processing the request. > > > At 16:15 +0100 on 21/4/98, Jose' Soares Da Silva wrote: > > > > > > > * Bad, this isn't very friendly. > > > > > > * No. What I would is that PostgreSQL don't abort at every smallest > > > syntax error. > > > > It depends on what you expect from a transaction. The way I see it, a > > transaction is a sequence of operations which either *all* succeed, or > > *all* fail. That is, if one of the operations failed, even for a syntax > > error, Postgres should not allow any of the other operations in the same > > transaction to work. > > > > For example, suppose you want to move money from one bank account to > > another, you'll do something like: > > > > BEGIN; > > > > UPDATE accounts > > SET credit = credit - 20,000 > > WHERE account_num = '00-xx-00'; > > > > UPDATE accounts > > SET credit = credit + 20000 > > WHERE account_num = '11-xx-11'; > > > > END; > > > > Now, look at this example. There is a syntax error in the first update > > statement - 20,000 should be without a comma. If Postgres were tolerant, > > your client would have an extra 20,000 dollars in one of his account, and > > the money came from nowhere, which means your bank loses it, and you lose > > your job... > > > > But a real RDBMS, as soon as one of the statement fails - no matter why - > > the transaction would not happen. It notifies you that it didn't happen. > > You can then decide what to do - issue a different transaction, fix the > > program, whatever. > > > > The idea is that the two actions (taking money from one account and putting > > it in another) are considered atomic, inseparable, and dependent. If your > > "real world" thinking says that the next operation should happen, no matter > > if the first one succeeded or failed, then they shouldn't be inside the > > same transaction. > > > > Herouth > > -- > Official WWW Site: http://www.postgresql.org > Online Docs & FAQ: http://www.postgresql.org/docs > Searchable Lists: http://www.postgresql.org/mhonarc > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Hi all,
I have again a problem about TRANSACTIONS.
I had some answers about this matter some time ago, but unfortunately the solution wasn't yet found.
Transaction are essentials for a relational database but in the case of PostgreSQL some times it's
impossible
to use them. Right now I'm in a middle of a work and I need to use transactions but I can't go on because
there are some "warnings" that I would like to avoid but I can't.
Problem:
PostgreSQL automatically ABORTS at every error, even a syntax error. I know that a transaction is a sequence of
operationswhich either all succeed, or all fail, and this behavior is correct for batch mode operations, but it is
notuseful in interactive mode where
the user could decide if the transaction should be COMMITed or ROLLBACKed even in presence of errors. Other
databaseshave such behavior.
What about to have a variable to set like:
SET TRANSACTION MODE TO {BATCH | INTERACTIVE}
where: BATCH: the transaction ROLLBACK at first error and COMMIT only if all operations
succeed. INTERACTIVE: leaves the final decision to user to COMMIT or ROLLBACK even if some error occurred.
Comments...
Jose'
<tt>Hi,</tt><tt></tt><p><tt>Its me again,</tt><tt></tt><p><tt>I'm trying to use transactions thru ODBC but it seems to
beimpossible.</tt><br /><tt>I'm populating my tables using transactions thru ODBC and before to INSERT a row to a
table</tt><br/><tt>I check if such row already exist in that table.</tt><br /><tt>if result is FALSE I insert the row
intothe table otherwise I skip the INSERT operation.</tt><br /><tt>I have a log in which ODBC checks for an unexistent
rowbut when I try to INSERT the row</tt><br /><tt>I cannot insert it, there's a duplicate index error.</tt><br /><tt>I
haveonly two index in that table and only one of them is UNIQUE and I know there is no</tt><br /><tt>other row with the
sameindex in that table.</tt><br /><tt>If I use the same program without transactions it works
fine.</tt><tt></tt><p><tt>Anyideas?</tt><tt></tt><p><tt>here the log:</tt><tt></tt><p><tt><DELETED></tt><br
/><tt>conn=61438304,SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=hygea;SERVER=verde</tt><br /><tt>conn=61438304,
query='SELECT"utenti"."azienda","utenti"."inizio_attivita"</tt><br /><tt>FROM "utenti" WHERE ("azienda" = '01879540308'
)'</tt><br /><tt> [ fetched 0 rows ]</tt><br /><tt>conn=61438304, SQLDisconnect</tt><br /><tt>conn=61284284,
query='INSERTINTO "utenti"
("azienda","ragione_sociale","istat","cap","indirizzo","partita_iva","istat_nascita","distretto","data_aggiornamento")</tt><br
/><tt>VALUES('01879540308','FONZAR PAOLO-LUCA-LUCIANO E DANIELA','030120','33050','VIA PROVINCIALE
N.4','01879540308','000000','G10500','1999-11-1700:00:00')'</tt><br /><tt>ERROR from backend during send_query:
'ERROR: Cannot insert a duplicate key into a unique index'</tt><br /><tt>conn=61284284, query='ABORT'</tt><br
/><tt><DELETED></tt><tt></tt><p><tt>andhere the table structure:</tt><tt></tt><p><tt>Table = utenti</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br
/><tt>|Field |Type | Length|</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>|
azienda | char() not null | 16 |</tt><br /><tt>|
ragione_sociale | varchar() not null | 45 |</tt><br /><tt>|
istat | char() not null | 6 |</tt><br /><tt>|
cap | char() | 5 |</tt><br /><tt>|
indirizzo | char() | 40 |</tt><br /><tt>|
civico | char() | 10 |</tt><br /><tt>|
distretto_interno | char() | 3 |</tt><br /><tt>|
frazione | char() | 25 |</tt><br /><tt>|
telefono | char() | 15 |</tt><br /><tt>|
fax | char() | 15 |</tt><br /><tt>|
email | char() | 15 |</tt><br /><tt>|
codice_fiscale | char() | 16 |</tt><br /><tt>|
partita_iva | char() | 11 |</tt><br /><tt>|
cciaa | char() | 8 |</tt><br /><tt>|
data_ccia | date | 4 |</tt><br /><tt>|
data_nascita | date | 4 |</tt><br /><tt>|
istat_nascita | char() | 6 |</tt><br /><tt>|
stato_attivita | char() | 2 |</tt><br /><tt>|
fuori_usl | char() default 'N' | 1 |</tt><br /><tt>|
assegnazione_codice | date | 4 |</tt><br /><tt>|
inizio_attivita | date not null default date( 'cur | 4 |</tt><br /><tt>|
fine_attivita | date | 4 |</tt><br /><tt>|
dpr317 | char() default 'N' | 1 |</tt><br /><tt>|
distretto | char() | 6 |</tt><br /><tt>|
data_aggiornamento | timestamp default now() | 4 |</tt><br /><tt>|
aggiornato_da | char() default CURRENT_USER | 10 |</tt><br /><tt>|
data_esportazione | date | 4 |</tt><br /><tt>|
data_precedente_esp | date | 4 |</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>Indices:
utenti_pkey</tt><br/><tt> utenti_ragione_idx</tt><br /><tt></tt> <p>Thanks for any help. <br />Jose' <br />
<p>josesoares ha scritto: <blockquote type="CITE">Hi all, <p>I have again a problem about TRANSACTIONS. <br />I had
someanswers about this matter some time ago, but unfortunately the solution wasn't yet found. <br />Transaction are
essentialsfor a relational database but in the case of PostgreSQL some times it's <br />impossible <br />to use them.
Rightnow I'm in a middle of a work and I need to use transactions but I can't go on because <br />there are some
"warnings"that I would like to avoid but I can't. <p>Problem: <p> PostgreSQL automatically ABORTS at every error,
evena syntax error. <br /> I know that a transaction is a sequence of operations which either all succeed, or all
fail,and <br /> this behavior is correct for batch mode operations, but it is not useful in interactive mode where
<br/>the user <br /> could decide if the transaction should be COMMITed or ROLLBACKed even in presence of errors.
<br/> Other databases have such behavior. <p>What about to have a variable to set like: <p>SET TRANSACTION MODE TO
{BATCH| INTERACTIVE} <p>where: <br /> BATCH: the transaction ROLLBACK at first error and COMMIT
onlyif all operations <br />succeed. <br /> INTERACTIVE: leaves the final decision to user to COMMIT or
ROLLBACKeven if some error occurred. <p>Comments... <p>Jose' <p>************</blockquote>