[postgis-devel] Re: PostGIS 1.0 & JDBC locking (WFS)

[strk at refractions.net]

On Wed, Mar 23, 2005 at 05:15:24PM -0800, Jody Garnett wrote:
> strk at refractions.net wrote:
> 
> >On Wed, Mar 23, 2005 at 07:38:36AM -0800, Paul Ramsey wrote:
> > 
> >
> >>Because oid is not usually indexed, and does have to exist (though it 
> >>does in 99% of cases). Primary key would be better in every case where 
> >>it exists.
> >>   
> >>
> >
> >We don't need an index, as the trigger will know the oid of
> >the row immediately (same of extracting a single field rather
> >then the rowid + schema and table names from syscache).
> > 
> >
> Note since we "locked" the row from modifications, does that mean the 
> oid will be stable? We do release the lock
> during any transaction it is used in.

An oid is never changed, and cannot be assigned to by user.
As Paul noticed a table can be created w/out oids.
It seems there are no other default row identifiers... a 
similar one is 'ctid', which addresses a tuple, but it is
a phisical address and changes upon updates (and probably cluseters).

My statement above still holds for user columns (extract a single
field from the tuple without having to retrive schema and table
name), and current code is implemented as such, storing table oid
into authorization_schema instead of 'schema'/'table' pair.

Anyway, the requirement of specifying a column to be used for
identifying a row prevents the code to be written fully with
plpgsql and would require some checks on it to prevent misuse:
does the column has NOT NULL and UNIQUE constraints ? Does the
column exist ? What if the column specified as holding the row-id
is dropped ?

A possible solution and improvement would be:

	- CheckAuth() would add (if non-existing) a column to the
	  table for storing the auth-token (_lock_auth text).
	  This would drop the need of a separate table
	  (authorization_table) and associated query for each
	  update or delete. Would also ensure consistency as
	  lock auth_id would die togheter with the row being
	  locked. Finally it would not take up space for rows
	  not being locked (field would be NULL)

Thanks to Jody for incouraging me in going on with this ;)
I think this is a viable, clean and efficient solution.

Also thanks to Mark for sharing toughts !

--strk;
 
> 
> Jody