Hello again,
It's possible to lock a row outside a transaction in PostgreSQL ?
Why I want to do this ?
We're using as a Frontend to our future postgreSQL database REALbasic.
We open a connection, do some queries, and now we want to modify an
invoice.
If we open a transaction, we can do a SELECT FOR UPDATE and this
record is locked, the users modify some data, but, before confirm or
deny those changes, the user open a new window and wants to modify a
customer data.
The customer data is selected, if we open a new transaction or not,
we're currently in a transaction, as we are using the same connection
to postgreSQL, so if the user before accepting the customer data
changes, goes again to the invoice that he was modifing, and cancels
de transaction, the data that was changed in the transaction of the
customer, is lost.
If we could lock a row outside a transaction, I could lock the
customer and invoice rows, do some changes, and at the moment of
updating the row, I open the transaction, update the rows, and commit
it, all of this without any user interface dialog, quickly as possible.
I can implement this with some semaphores or some table, but if
someone with a simply command line connectioon connects, will pass
those semaphores and still could change the rows, that's why I want
to lock the rows.
Another posibility is starting a new connection for each module in
our front-end, that way, any connection will have only one
transaction, and all those transactions will be independent from
eachother.
But using this approach, we don't now how many permanent and open
connections PostgreSQL can handle. As this is a completely rewrite,
wwe are starting from zero in PostgreSQL, so we'll be using the
latest stable version, now we're using 8.2 on a linux box with Debian.
Currently with our legacy app have 60 permanent users connected to
our 4D Server using 4D Client, and 150-200 users using a web browser,
but those internet users aren't always connected ...
thanks for your help, any info woul be much appreciated ...
regards,
rai