Postgres Pro
Downloads
Home   >   mailing lists

Thread: sql statements using access frontend

sql statements using access frontend

From
"Gavin Mitchell"
Date:

Hi

 

I am getting some strange entries in the postgres logs when updating data in a linked table using access.

 

If I create an UPDATE query I get the following entry which looks ok to me

2011-12-01 10:18:38 GMT LOG:  statement: UPDATE "ua"."pole_survey_tbl" SET "completedby"=E'gavinm' WHERE "pole_survey_id" = 6478

 

But if the data is changed within the table itself or a form based on the table or query it starts a transaction and adds all available fields into the where clause

2011-12-01 10:03:52 GMT LOG:  statement: BEGIN;UPDATE "ua"."pole_survey_tbl" SET "completedby"=E'test'  WHERE "pole_survey_id" = 6478 AND "survey_id" = 1025 and (etc…….)

 

I chopped the log entry short but there are AND entries fo all fields in the table

 

Any help would be much appreciated

 

Thanks

 

Gavin Mitchell

 

Re: sql statements using access frontend

From
Richard Huxton
Date:
On 02/12/11 14:02, Gavin Mitchell wrote:
> But if the data is changed within the table itself or a form based on
> the table or query it starts a transaction and adds all available fields
> into the where clause
>
> 2011-12-01 10:03:52 GMT LOG: statement: BEGIN;UPDATE
> "ua"."pole_survey_tbl" SET "completedby"=E'test' WHERE "pole_survey_id"
> = 6478 AND "survey_id" = 1025 and (etc…….)
>
> I chopped the log entry short but there are AND entries fo all fields in
> the table

It's an implementation of "optimistic locking". Imagine we are both
updating the same row at the same time. If my update gets committed
before yours, then yours won't find a row to update. Otherwise my
changes could be overwritten without you knowing.

--
   Richard Huxton
   Archonet Ltd