Thread: Updatable Cursors, CTID, and Views

Updatable Cursors, CTID, and Views

From

"Mark Dexter"

Date:

28 July 2004, 03:45:25

From a prior posting on November 5, 2002, I found this text regarding the use of CTID in the ODBC driver:

The driver uses CTID and OID to implement updatable cursors. Please turn off the *Updatable cursors* DSN option if you don't like the behavior. regards, Hiroshi Inoue

I am having a problem where I need to use updatable cursors with views. It appears that the ODBC driver is trying to use the CTID and OID columns and that views don't have these columns. Is it possible to use updatable cursors with views via the PostgreSQL ODBC driver?

Thanks.

Re: Updatable Cursors, CTID, and Views

From

"Dave Page"

Date:

28 July 2004, 07:29:04

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mark Dexter
Sent: 27 July 2004 23:26
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Updatable Cursors, CTID, and Views

From a prior posting on November 5, 2002, I found this text regarding the use of CTID in the ODBC driver:
The driver uses CTID and OID to implement updatable cursors. Please turn off the *Updatable cursors* DSN option if you don't like the behavior. regards, Hiroshi Inoue
I am having a problem where I need to use updatable cursors with views. It appears that the ODBC driver is trying to use the CTID and OID columns and that views don't have these columns. Is it possible to use updatable cursors with views via the PostgreSQL ODBC driver?

Not from what Hiroshi has said (he wrote the cursor code). I imagine he used CTID because it needs to be able to locate the exact version of each row - in a view that won't work because ctid don't exist. I guess you could try adding it to your view definition though that will probably only work for single table views of course.

I'm not sure why he would have used oid given that it may not exist even in a table...

Regards, Dave.

Re: Updatable Cursors, CTID, and Views

From

Janet Borschowa

Date:

28 July 2004, 16:21:24

Hi,
I believe that Hiroshi probably used the ctid because views are not
updateable. It looks like you have to do extra work to update a view and in
that case, I'm not sure how a cursor in the driver would be implemented as
there needs to be a way to uniquely identify a row which is why Hiroshi used
the ctid column.

From the PostgreSQL docs for the SQL statement "CREATE VIEW":
Description

CREATE VIEW defines a view of a query. The view is not physically
materialized. Instead, the query is run every time the view is referenced in
a query.

...

Notes

Currently, views are read only: the system will not allow an insert, update,
or delete on a view. You can get the effect of an updatable view by creating
rules that rewrite inserts, etc. on the view into appropriate actions on
other tables. For more information see CREATE RULE.

Sorry I can't be of help but I don't know if or how you could define a view
with a ctid column and appropriate rules to enable the driver to use a
cursor on it.

========================
Janet Borschowa
Software Engineer, Database Products
Rogue Wave Software, a QUOVADX(tm) Division
(541) 753-1931   FAX: (541) 757-4630
mailto:borschow@roguewave.com <mailto:borschow@roguewave.com>
http://www.roguewave.com <http://www.roguewave.com/>

-----Original Message-----
From: Dave Page [mailto:dpage@vale-housing.co.uk]
Sent: Wednesday, July 28, 2004 12:29 AM
To: Mark Dexter; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Updatable Cursors, CTID, and Views





  _____

From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mark Dexter
Sent: 27 July 2004 23:26
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Updatable Cursors, CTID, and Views

Re: Updatable Cursors, CTID, and Views

From

"Mark Dexter"

Date:

28 July 2004, 22:18:12

It appears from my initial testing that, if you create the view with the CTID and OID columns and then add rules for UPDATE, INSERT, and DELETE, that you can have an updatable cursor against the view. It would be nice if there was an automated way to create the rules needed to make a view updatable. I think it would be possible, since the rule just has to reference each column in the table (for INSERT and UPDATE) and reference the primary key fields in the WHERE clause. This could be similar to the PRIMARY KEY clause in the CREATE TABLE statement, where it automatically adds an index for you.

BTW, if I do this in my live database, I'm going to end up with about 1500 views (I have about 1700 tables) and about 4500 rules (3 rules per view -- one each for UPDATE, INSERT, and DELETE). I assume having this many objects is not going to cause any performance or other problems?

Thanks very much for your help with this. Mark Dexter

-----Original Message-----
From: Janet Borschowa [mailto:borschow@roguewave.com]
Sent: Wednesday, July 28, 2004 9:18 AM
To: Mark Dexter; pgsql-odbc@postgresql.org
Cc: 'Dave Page'
Subject: RE: [ODBC] Updatable Cursors, CTID, and Views

Hi,
I believe that Hiroshi probably used the ctid because views are not updateable. It looks like you have to do extra work to update a view and in that case, I'm not sure how a cursor in the driver would be implemented as there needs to be a way to uniquely identify a row which is why Hiroshi used the ctid column.

From the PostgreSQL docs for the SQL statement "CREATE VIEW":
Description
CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.
...
Notes
Currently, views are read only: the system will not allow an insert, update, or delete on a view. You can get the effect of an updatable view by creating rules that rewrite inserts, etc. on the view into appropriate actions on other tables. For more information see CREATE RULE.
Sorry I can't be of help but I don't know if or how you could define a view with a ctid column and appropriate rules to enable the driver to use a cursor on it.

========================
Janet Borschowa
Software Engineer, Database Products
Rogue Wave Software, a QUOVADX(tm) Division
(541) 753-1931 FAX: (541) 757-4630
mailto:borschow@roguewave.com http://www.roguewave.com
-----Original Message-----
From: Dave Page [mailto:dpage@vale-housing.co.uk]
Sent: Wednesday, July 28, 2004 12:29 AM
To: Mark Dexter; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Updatable Cursors, CTID, and Views

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mark Dexter
Sent: 27 July 2004 23:26
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Updatable Cursors, CTID, and Views

From a prior posting on November 5, 2002, I found this text regarding the use of CTID in the ODBC driver:
The driver uses CTID and OID to implement updatable cursors. Please turn off the *Updatable cursors* DSN option if you don't like the behavior. regards, Hiroshi Inoue
I am having a problem where I need to use updatable cursors with views. It appears that the ODBC driver is trying to use the CTID and OID columns and that views don't have these columns. Is it possible to use updatable cursors with views via the PostgreSQL ODBC driver?
Not from what Hiroshi has said (he wrote the cursor code). I imagine he used CTID because it needs to be able to locate the exact version of each row - in a view that won't work because ctid don't exist. I guess you could try adding it to your view definition though that will probably only work for single table views of course.
I'm not sure why he would have used oid given that it may not exist even in a table...
Regards, Dave.

Re: Updatable Cursors, CTID, and Views

From

"Dave Page"

Date:

29 July 2004, 07:25:10

From: Mark Dexter [mailto:MDEXTER@dexterchaney.com]
Sent: 28 July 2004 17:10
To: Dave Page
Subject: RE: [ODBC] Updatable Cursors, CTID, and Views

Mr. Page: Thanks for the response. It appears that, if I include the CTID and OID columns in the view definition, I can successfully create updatable cursors without getting errors. However, I then run into another apparent problem -- that normal views in PostgreSQL are not updatable. I have started reading about using rules to create, in effect, views that can be updated. However, it appears that using rules is somewhat complex and restrictive.

You are correct that they are not always the easiest things to use, however they are pretty powerful - check out http://www.postgresql.org/docs/7.4/static/rules-views.html and http://www.postgresql.org/docs/7.4/static/rules-update.html for some examples of what you can do. There was a guy working on creating simple view update rules automatically where possible, however this is not as easy as you might think at first glance. I don't know if he's still working on it.

If I may, I'd like to give you a quick idea of what I'm trying to do and perhaps get your opinion on whether this can be done in PostgreSQL. In Oracle and SQL Server, I have created a series of views that are very simple subsets of underlying tables. For example, a customer master table has customers for multiple companies (the first key column). A single-company view is defined as "SELECT * FROM CUSTOMERS WHERE COMPANY_CODE = ff_company()", where ff_company() is a function that returns the current company being worked on.

In these other databases, single-table views are updatable as long as all of the table's columns are either defined in the view, are null-permisive, or have default constraints. So these views can be treated exactly as if they are tables by our application.

My question is: Is it possible to define a view or rule in PostgreSQL that would be updatable (insert, update, and delete) where it is a simple query of all columns for a selected subset of the rows of an underlying table?

Yes, it is. There is a nice example of this in the article " Table defaults with View Rules " at http://www.varlena.com/varlena/GeneralBits/44.php

Thanks so very much for your help. Mark Dexter (Seattle, WA, USA)

No problem.

Regards, Dave.