Re: update rules + views + odbc problems

Hey, here's an update.

Adding the column "ctid" to the view ( and recreating the rewrite rule)
fixed the problem.

Magically I can update the view from access.

Without the column "ctid", it seems impossible to update a view.

Can the developers implement a fix in the odbc driver or is this
something we have to live with?

I'm using access2003 SP1 with odbc snapshot 08-00-0001

Thanks!

With best regards,

Mike Fahey - Systems Administration
********************************************************************
ENTER.NET - "The Road to the Internet Starts Here!" (tm)
(610) 437-2221 * http://www.enter.net/ * email:support(at)enter(dot)net
********************************************************************

Jeff Eckermann wrote:

>--- Mike Fahey <mfahey(at)enter(dot)net> wrote:
>
>
>
>>Hi, i'm runing into problems when I have a view
>>defined and then try to
>>update the view with a rewrite rule in place.
>>
>>At a psql prompt I can update the view just fine.
>>
>>
>>I'm using access 2003 and I have a subform bound to
>>a view in postgres,
>>then I have a rewrite rule defined on the view.
>>
>>With psqlodbc.dll version 07.03.0200 I get the
>>following error in access:
>>
>>No Current Record.
>>
>>With psqlodbc.dll version 08.00.0001 I get the
>>following error in access:
>>
>>Write Conflict
>>
>>This record has been changed by another user since
>>you started editing
>>it. if you
>>save the record you will overwrite the changes the
>>other user made.
>>
>>Copying the changes to the clipboard will let you
>>look at the values
>>the other user entered,
>>and then paste your changes back in if you decide
>>to make changes.
>>
>>Any thoughts?
>>
>>
>
>This problem keeps coming around on this list, and I
>have never seen a real solution, as in a method to do
>what you want.
>
>What is lacking is the "ctid" column, which is a
>system column attached to every table, and which the
>ODBC driver uses as a unique row identifier (I am
>assuming that you have "row versioning" set to true).
>Since the ctid value is supposed to point to the
>actual physical location of the disk, it will be
>unique across the database. So, in theory, you could
>get away with including "ctid" in your view
>definition. But this has never been tested, to my
>knowledge, and I don't understand the implementation
>of either ODBC or PostgreSQL nearly well enough to say
>whether that really would work.
>
>An alternative would be to base the subform on an
>equivalent query defined in Access. Though this could
>be a performance problem, if the view is filtering a
>lot of rows out.
>
>
>
>>Thanks in advance.
>>
>>
>>
>>
>>--
>>With best regards,
>>
>>Mike Fahey - Systems Administration
>>
>>
>>
>********************************************************************
>
>
>> ENTER.NET - "The Road to the Internet Starts
>>Here!" (tm)
>> (610) 437-2221 * http://www.enter.net/ *
>>email:support(at)enter(dot)net
>>
>>
>>
>********************************************************************
>
>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please
>>send an appropriate
>> subscribe-nomail command to
>>majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list
>>cleanly
>>
>>
>>
>
>
>
>
>_______________________________
>Do you Yahoo!?
>Declare Yourself - Register online to vote today!
>http://vote.yahoo.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>