errors on transactions and locks ?
Hi, all
I have some problems with transactions and locks...
I have 5 questions about it...
1. NOTICE: (transaction aborted): queries ignored until END
*ABORT STATE*
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I would like to know what's mean the above message.
- Seems that transactions aborts at smallest syntax error ?
- Seems that every work done until this point is lost.
- ?Am I right?
- If yes, ?what can I do to go on?
Seems that I can't do nothing but COMMIT or ROLLBACK.
- Seems that COMMIT has the same effect of ROLLBACK,
because all changes are lost in anyway.
- If that's true ?why is it neccessary to do COMMIT or ROLLBACK?
- and ?what about locks?
?are all locks released before COMMIT/ROLLBACK?
2. LOCKED FOR EVER AND EVER...
^^^^^^^^^^^^^^^^^^^^^^^^^^^
If user2 try to SELECT a table locked by user1, user2 falls in a trap
he can't do nothing to free himself from this trap,
and he must wait that user1 ends his work.
- ?Is there a way to avoid this trap?
I think that's useless to lock tables even for readonly operations.
- user2 shouldn't be able to UPDATE tables but he should be able
to SELECT tables.
- ...or at least user2 should have the possibility to choice if
he wants wait for ever that a table become available or retry latter
to see if table was unlocked.
A message like:
"TABLE <tablename> IS LOCKED BY USER <username> PLEASE, TRY LATTER"
would be preferable.
- If this interests to someone;
the Oracle'sLOCK TABLE <tablename> IN EXCLUSIVE MODE
allows read access to locked tables.
3. DROP TABLE <tablename> or DELETE FROM <tablename> ?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Dropping a table inside a transactions and then rolling back the work
is equivalent to DELETE FROM <tablename>; the table's structure will
be restored but data will be lost:
postgres=> begin;
BEGIN
postgres=> select * from cities;
code|city
----+-------------
SFO |SAN FRANCISCO
STL |ST. LOUIS
(2 rows)
postgres=> drop table cities;
DROP
postgres=> rollback;
ABORT
postgres=> select * from cities;
code|city
----+----
(0 rows)
4. MIRACLE DROPPED TABLE IS RETURNED.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
User <manuel> dropps a table inside a transactions while
user <jose> is trying to query the same table,
user <manuel> change his mind and rolls back the work,
at this point user <jose> see the result of his query,
and insert a new row to the table.
At this point the dropped table returns with all its data.
--user: manuel-------------------------------------------------
postgres=> select current_user;
getpgusername
-------------
manuel
postgres=> begin;
BEGIN
postgres=> select * from cities;
code|city
----+-------------
SFO |SAN FRANCISCO
STL |ST. LOUIS
(2 rows)
postgres=> lock cities;
DELETE 0
--user jose--------------------------------------------------
postgres=> select current_user;
getpgusername
-------------
jose
postgres=> select * from cities;
--jose was caught in a trap, wait for ever and ever ...
--user manuel again---------------------------------------------
postgres=> drop table cities;
DROP
postgres=> rollback;
ABORT
--user jose again---------------------------------------------
-- (finally jose is "free" and have his query result):
code|city
----+-------------
SFO |SAN FRANCISCO
STL |ST. LOUIS
(2 rows)
-- and now, jose decide to append a new row to the table...
postgres=> insert into cities values ('SJC','SAN JOSE');
INSERT 460390 1
--and user manuel query table... ------------------------------------------
-- et voila'... the table and all its data are returned...
postgres=> select * from cities;
code|city
----+-------------
SFO |SAN FRANCISCO
STL |ST. LOUIS
SJC |SAN JOSE
(3 rows)
5. LOCK AT ROW LEVEL
^^^^^^^^^^^^^^^^^
Massimo Dal Zotto have done a very useful work with locks at row level
(refer to .../contrib/userlock) and it should be interesting to implement
these functions as SQL statements.
--to lock a row(s)...
SELECT user_write_lock_oid(OID), oid, *
FROM cities
WHERE city LIKE 'SAN%';
user_write_lock_oid| oid|code|city
-------------------+------+----+-------------
1|460388|SFO |SAN FRANCISCO
1|460390|SJC |SAN JOSE
--if result of "user_write_lock_oid" is 1, then the row(s) are available
--and you can update it...
--to unlock the row(s)...
SELECT user_write_unlock_oid(OID)
FROM cities
WHERE oid = 460388 OR oid = 460390;
- If this interests to someone, Oracle uses a similar way to locking rows,
take a look...
SELECT ROWID, *
FROM cities
WHERE city LIKE 'SAN%';
FOR UPDATE OF city;
ROWID CODE CITY
__________________________________
460388 SFO SAN FRANCISCO
460390 SJC SAN JOSE
--if row(s) is/are available then you can update it/them...
UPDATE cities
SET city = INITCAP(city)
WHERE rowid = 460388 OR rowid = 460390;
--to unlock the row(s)...
COMMIT
Oracle uses ROWIDs to lock rows, we also have OIDs...
?How much difficult is it to implement ?
Ciao, Jose'
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 aTable = 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 aTable = 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.Show quoted text
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
Import Notes
Reply to msg id not found: l03110703b16273c7decf@147.233.159.109 | Resolved by subject fallback
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)