Re: getting rid of CTIDs

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.

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

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).

regards,
mv

> "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/

Now I have a problem with 7.1.2, psqlodbc-07_02_0001
<http://odbc.postgresql.org/ftpsite/odbc/versions/msi/psqlodbc-07_02_0001.zip>,
server-side updateable
cursor, I'm updating a row, then trying to update it another time but
will get a locking error
since it doesn't seem to fetch the new CTID (fetched 0 rows)

conn=71988336, query='select * , CTID, OID from a'
[ fetched 4 rows ]
conn=71988336, query='update "a" set "nimi" = 'kuus-uus' where ctid =
'(0, 53)' and oid = 1104111'
conn=71988336, query='select * , CTID, OID from a where ctid =
currtid2('a', '(0, 53)') and oid = 1104111'
[ fetched 0 rows ]
conn=71988336, query='COMMIT'
conn=71988336, query='update "a" set "nimi" = 'kuus' where ctid = '(0,
53)' and oid = 1104111'
conn=71988336, query='COMMIT'
conn=71988336, PGAPI_Disconnect

This is from psql session after the odbc one:
mvali=> select *, ctid, oid from a;
a_id | b_b_id | nimi | ctid | oid
------+--------+----------+--------+---------
10 | 1 | kymme | (0,54) | 1104114
8 | 2 | kaheksa | (0,55) | 1104112
1 | 123 | a1 | (0,56) | 1104065
6 | 2 | kuus-uus | (0,57) | 1104111
(4 rows)

mvali=> select *, ctid, oid from a where ctid = currtid2('a', '(0,53)')
and oid=1104111;
a_id | b_b_id | nimi | ctid | oid
------+--------+----------+--------+---------
6 | 2 | kuus-uus | (0,57) | 1104111
(1 row)

thanks
mv

>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.
>
>
>

Margus Va"li wrote:
>
> Now I have a problem with 7.1.2, psqlodbc-07_02_0001
> <http://odbc.postgresql.org/ftpsite/odbc/versions/msi/psqlodbc-07_02_0001.zip>,
> server-side updateable
> cursor, I'm updating a row, then trying to update it another time but
> will get a locking error
> since it doesn't seem to fetch the new CTID (fetched 0 rows)

It was my fault at server side, sorry.
Please apply the following patch to src/backend/access/heap/heapam.c.

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

===================================================================
RCS file: /cvsroot//pgsql/src/backend/access/heap/heapam.c,v
retrieving revision 1.129
retrieving revision 1.129.2.1
diff -c -r1.129 -r1.129.2.1
*** pgsql/src/backend/access/heap/heapam.c 2002/01/15 22:14:17 1.129
--- pgsql/src/backend/access/heap/heapam.c 2002/05/01 01:27:31 1.129.2.1
***************
*** 8,14 ****
*
*
* IDENTIFICATION
! * $Header: /cvsroot//pgsql/src/backend/access/heap/heapam.c,v 1.129
2002/01/15 22:14:17 tgl Exp $
*
*
* INTERFACE ROUTINES
--- 8,14 ----
*
*
* IDENTIFICATION
! * $Header: /cvsroot//pgsql/src/backend/access/heap/heapam.c,v
1.129.2.1 2002/05/01 01:27:31 inoue Exp $
*
*
* INTERFACE ROUTINES
***************
*** 1004,1010 ****
snapshot, 0, (ScanKey) NULL);

linkend = true;
! if ((t_data->t_infomask & HEAP_XMAX_COMMITTED) &&
!ItemPointerEquals(tid, &ctid))
linkend = false;

--- 1004,1010 ----
snapshot, 0, (ScanKey) NULL);

linkend = true;
! if ((t_data->t_infomask & HEAP_XMIN_COMMITTED) != 0 &&
!ItemPointerEquals(tid, &ctid))
linkend = false;