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