Re: [QUESTIONS] errors on transactions and locks ? - Mailing list pgsql-hackers

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