Re: getting rid of CTIDs

> "mvali(at)hot" wrote:
>
> Hello
> We script ADO with VB over pgODBC and we intend
> to use server-side recordsets (the ODBC cursor
> handling that is). The data is to be fetched from
> views and updated as well using rules.
>
> Now having defined a dumb table WITHOUT OIDS
> and created insert/update/select rules that select
> OID from one of the tables in the join,
>
> create table v(oid oid, id int4, ...) without oids;
> create rule "_RETv" as on select to v do instead
> (
> SELECT a.oid, a.id, b... FROM a, b WHERE b.a_id = a.id
> ),
>
> we still have the problem that the driver uses CTID's to
> enforce optimistic locking despite Row versioning
> switched off.

TIDs are mainly for fast access to DB. Scan by TIDs
is the fastest random access. Optimistic concurrency
checking is never the first purpose. Also note the
row versioning switch isn't essentially for the ODBC
driver but for the upper application like Access.
The server side cursors are implemented using the
concurrency check with the internal row versioning.

> Now how can we effectively turn off optimistic locking
> done with CTID-s in the driver and go on with plain
> OIDs?

OIDs are for identification. It doesn't guarantee
fast access to db. The combination of TIDs and OIDs
has been my plan since I introduced TID scan in 7.0.

> We've already considered to hack the driver to replace
> the CTID to something that we could match in the views
> (the views have automatically their own ctid-s so we
> could for example make the ODBC driver select ctid2 instead or
> even disable the feature in general to have the row
> identification done only with OIDs).

I've posted pgsql-hackers a proposal that allows

CREATE VIEW .. AS SELECT CTID, ...
which is currently rejected.
Please wait for a while to see if I can do it.

regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/