Dear PGSQL Masters!
I got a new project: porting a DBASE based application to PGSQL. Some theoretic question I have before I coding/typing.
OS: Win, IDE: Delphi.
Preface:
Delphi uses TDataSet-s (every of them have records/rows).
When use Post method to save the actual record. In DBASE or flat table systems the Post is instantly write the record into the file (isolation level = commit on post). "Edit" or "Append" methods are usable for modify the record.
These methods put "lock" on actual record to protect from other modifications. After "Post" or "Cancel" these locks released.
The construction of the old application is this (and it is repeating with many times, in many forms = "dialogs"):
1.) The edit form is opening a master item (table row) - with master dataset, dbedits, grids, etc. For example: an order.
2.) The edit form is opening details (show them). For example: dispo addresses, bill items, suborders, etc.
3.) Master dataset set locked with Table."Edit".
4.) You can edit the master...
5.) ... or you can edit any of subdetails, or basic data (for example types, kinds, workers, etc) in other forms, and this instantly posted (post = commit). So master kept in "Edit" state after I added a new address (but the new address record saved in DB).
6.) After you finished the form closing, and Master Data posted. Then the lock released.
Because this "lock" alive, never anyone change master or details at once (conflicts resolved with this), only basic data (colors, types, kinds, workers, etc.)
If trying to visualize this under PGSQL, or other RDBMS (except Firebird with IBX/IBO), I everytime blocked on these things:
a.) Lock, protection on the "master" record
b.) Only one living transaction by connection
Firebird with IBX/IBO is allows you to make more living transactions.
In this case I can start a new transaction in every new subdetail form, this not affected on main transaction (master).
begin; update master set id = id where id = :id
begin; insert into detail1 () values(); commit;
begin; update detail2 ...; commit;
master; commit
This update SQL locking the master and we can post/commit on any subdetails, the subdata are flushed into db, no matter that later the master rolled back or not.
But ZEOS, or PGDAC supports only one transaction by connection (and may other dbs (ADO) too).
So if I want to use "lock for update", or "lock record" on master, then the lock will vanishing when any of subdetails commited or rolled back (fail).
How can I protect the master? Can I create "subtransactions" that can commitable without commit the master?
Other problem that I cannot change the mode of transaction, because it is held on connection (AutoCommit = ???).
The bills, or heavily linked data must saved with "No AutoCommit" mode (protected by transaction, all or none = subitems with master at once).
But these subdetails are commitable by rows, that is no matter.
I hope I describe well the situation.
Forms can open other Forms, and subforms can save subdata without releasing master lock, or drop "master's transaction" (ok, in DBASE clones there is no transaction, but may you understand it).
Because users used this method for this time, I must provide same under PGSQL.
What do you thinking about this? Can I do this without doubling the connections by users (if I duplicate the connections, the subforms can use the seconds connection = another transaction)?
Thanks for your help: dd
