Re: update rules + views + odbc problems

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