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


Wed, 22 Apr 1998 11:39:51 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
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.

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)

Mon, 15 Jun 1998 22:43:56 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [QUESTIONS] errors on transactions and locks ?
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, Pennsylvania 19026

Fri, 17 Sep 1999 00:45:49 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [QUESTIONS] errors on transactions and locks ?
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 operations which either all succeed, or all fail, and
    this behavior is correct for batch mode operations,  but  it is not useful in interactive mode where
the user
    could decide if the transaction should be COMMITed or ROLLBACKed even in presence of  errors.
    Other databases have 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'



Wed, 01 Dec 1999 11:53:15 +0100
jose soares <jose@sferacarta.com>
TRANSACTION "WARNINGS"
Hi,

Its me again,

I'm trying to use transactions thru ODBC but it seems to be impossible.
I'm populating my tables using transactions thru ODBC and before to INSERT a row to a table
I check if such row already exist in that table.
if result is FALSE I insert the row into the table otherwise I skip the INSERT operation.
I have a log in which ODBC checks for an unexistent row but when I try to INSERT the row
I cannot insert it, there's a duplicate index error.
I have only two index in that table and only one of them is UNIQUE and I know there is no
other row with the same index in that table.
If I use the same program without transactions it works fine.

Any ideas?

here the log:

<DELETED>
conn=61438304, SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=hygea;SERVER=verde
conn=61438304, query='SELECT "utenti"."azienda","utenti"."inizio_attivita"
FROM "utenti" WHERE ("azienda" = '01879540308' ) '
    [ fetched 0 rows ]
conn=61438304, SQLDisconnect
conn=61284284, query='INSERT INTO  "utenti"
("azienda","ragione_sociale","istat","cap","indirizzo","partita_iva","istat_nascita","distretto","data_aggiornamento")

VALUES ('01879540308','FONZAR PAOLO-LUCA-LUCIANO E DANIELA','030120','33050','VIA PROVINCIALE
N.4','01879540308','000000','G10500','1999-11-17 00:00:00')'
ERROR from backend during send_query:  'ERROR:  Cannot insert a duplicate key into a unique index'
conn=61284284, query='ABORT'
<DELETED>

and here the table structure:

Table    = utenti
+----------------------------------+----------------------------------+-------+
|Field                             |Type                              | Length|
+----------------------------------+----------------------------------+-------+
| azienda                          | char() not null                  |    16 |
| ragione_sociale                  | varchar() not null               |    45 |
| istat                            | char() not null                  |     6 |
| cap                              | char()                           |     5 |
| indirizzo                        | char()                           |    40 |
| civico                           | char()                           |    10 |
| distretto_interno                | char()                           |     3 |
| frazione                         | char()                           |    25 |
| telefono                         | char()                           |    15 |
| fax                              | char()                           |    15 |
| email                            | char()                           |    15 |
| codice_fiscale                   | char()                           |    16 |
| partita_iva                      | char()                           |    11 |
| cciaa                            | char()                           |     8 |
| data_ccia                        | date                             |     4 |
| data_nascita                     | date                             |     4 |
| istat_nascita                    | char()                           |     6 |
| stato_attivita                   | char()                           |     2 |
| fuori_usl                        | char() default 'N'               |     1 |
| assegnazione_codice              | date                             |     4 |
| inizio_attivita                  | date not null default date( 'cur |     4 |
| fine_attivita                    | date                             |     4 |
| dpr317                           | char() default 'N'               |     1 |
| distretto                        | char()                           |     6 |
| data_aggiornamento               | timestamp default now()          |     4 |
| aggiornato_da                    | char() default CURRENT_USER      |    10 |
| data_esportazione                | date                             |     4 |
| data_precedente_esp              | date                             |     4 |
+----------------------------------+----------------------------------+-------+
Indices:  utenti_pkey
          utenti_ragione_idx


Thanks for any help.
Jose'


jose soares ha scritto:

> 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 operations which either all succeed, or all fail, and
>     this behavior is correct for batch mode operations,  but  it is not useful in interactive mode where
> the user
>     could decide if the transaction should be COMMITed or ROLLBACKed even in presence of  errors.
>     Other databases have 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'
>
> ************


Hi,
Its me again,
I'm trying to use transactions thru ODBC but it seems to be impossible.
I'm populating my tables using transactions thru ODBC and before
to INSERT a row to a table
I check if such row already exist in that table.
if result is FALSE I insert the row into the table otherwise I
skip the INSERT operation.
I have a log in which ODBC checks for an unexistent row but when
I try to INSERT the row
I cannot insert it, there's a duplicate index error.
I have only two index in that table and only one of them is UNIQUE
and I know there is no
other row with the same index in that table.
If I use the same program without transactions it works fine.
Any ideas?
here the log:
<DELETED>
conn=61438304, SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=hygea;SERVER=verde
conn=61438304, query='SELECT "utenti"."azienda","utenti"."inizio_attivita"
FROM "utenti" WHERE ("azienda" = '01879540308' ) '
    [ fetched 0 rows ]
conn=61438304, SQLDisconnect
conn=61284284, query='INSERT INTO  "utenti" ("azienda","ragione_sociale","istat","cap","indirizzo","partita_iva","istat_nascita","distretto","data_aggiornamento")
VALUES ('01879540308','FONZAR PAOLO-LUCA-LUCIANO E DANIELA','030120','33050','VIA
PROVINCIALE N.4','01879540308','000000','G10500','1999-11-17 00:00:00')'
ERROR from backend during send_query:  'ERROR:  Cannot
insert a duplicate key into a unique index'
conn=61284284, query='ABORT'
<DELETED>
and here the table structure:
Table    = utenti
+----------------------------------+----------------------------------+-------+
|Field                            
|Type                             
| Length|
+----------------------------------+----------------------------------+-------+
| azienda                         
| char() not null                 
|    16 |
| ragione_sociale                 
| varchar() not null              
|    45 |
| istat                           
| char() not null                 
|     6 |
| cap                             
| char()                          
|     5 |
| indirizzo                       
| char()                          
|    40 |
| civico                          
| char()                          
|    10 |
| distretto_interno               
| char()                          
|     3 |
| frazione                        
| char()                          
|    25 |
| telefono                        
| char()                          
|    15 |
| fax                             
| char()                          
|    15 |
| email                           
| char()                          
|    15 |
| codice_fiscale                  
| char()                          
|    16 |
| partita_iva                     
| char()                          
|    11 |
| cciaa                           
| char()                          
|     8 |
| data_ccia                       
| date                            
|     4 |
| data_nascita                    
| date                            
|     4 |
| istat_nascita                   
| char()                          
|     6 |
| stato_attivita                  
| char()                          
|     2 |
| fuori_usl                       
| char() default 'N'              
|     1 |
| assegnazione_codice             
| date                            
|     4 |
| inizio_attivita                 
| date not null default date( 'cur |     4 |
| fine_attivita                   
| date                            
|     4 |
| dpr317                          
| char() default 'N'              
|     1 |
| distretto                       
| char()                          
|     6 |
| data_aggiornamento              
| timestamp default now()         
|     4 |
| aggiornato_da                   
| char() default CURRENT_USER      |   
10 |
| data_esportazione               
| date                            
|     4 |
| data_precedente_esp             
| date                            
|     4 |
+----------------------------------+----------------------------------+-------+
Indices:  utenti_pkey
          utenti_ragione_idx
 
Thanks for any help.
Jose'
 
jose soares ha scritto:
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 operations
which either all succeed, or all fail, and
    this behavior is correct for batch mode operations, 
but  it is not useful in interactive mode where
the user
    could decide if the transaction should be COMMITed
or ROLLBACKed even in presence of  errors.
    Other databases have 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'
************
Tue, 07 Dec 1999 10:47:01 +0100
jose soares <jose@sferacarta.com>
Re: [HACKERS] TRANSACTION "WARNINGS"