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'