Thread: getting rid of CTIDs

> "mvali@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;