Re: [QUESTIONS] errors on transactions and locks ?

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: "Jose' Soares Da Silva" <sferac(at)proxy(dot)bazzanese(dot)com>
Cc: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>, questions postgres <pgsql-questions(at)postgreSQL(dot)org>, hackers postgres <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: [QUESTIONS] errors on transactions and locks ?
Date: 1999-09-17 04:45:49
Message-ID: 199909170445.AAA07310@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-interfaces

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Lockhart 1999-09-17 05:58:19 Re: [HACKERS] Join syntax
Previous Message Bruce Momjian 1999-09-17 04:11:46 Re: [HACKERS] pgaccess seems a tad confused

Browse pgsql-interfaces by date

  From Date Subject
Next Message Daniel Fritz 1999-09-17 14:17:16
Previous Message Ken J. Wright 1999-09-17 02:38:59 Re: [INTERFACES] ODBC Question