Skip site navigation (1) Skip section navigation (2)

Re: update rules + views + odbc problems

From: Mike Fahey <mfahey(at)enter(dot)net>
To: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
Cc: Postgres list <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: update rules + views + odbc problems
Date: 2004-10-26 21:25:25
Message-ID: 417EC0C5.5060904@enter.net (view raw or flat)
Thread:
Lists: pgsql-odbc
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
>  
>

In response to

Responses

pgsql-odbc by date

Next:From: Joost KraaijeveldDate: 2004-10-26 21:39:34
Subject: Can I access pg with multiple threads
Previous:From: louise chanDate: 2004-10-26 19:15:26
Subject: UNSUBSCRIBE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group