errors on transactions and locks ? - Mailing list pgsql-hackers

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'