Re: update rules + views + odbc problems

--- Mike Fahey <mfahey(at)enter(dot)net> wrote:

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

An interesting question is, if you have multiple
tables joined in your view, which ctid do you choose
to include? You would then lose protection against a
record silently changing under you, at least for those
tables whose ctid's are not included in the view. But
this would not matter, if your app restricted updates
to those done via the view.

>
> 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 suspect the latter.

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

__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail