Thread: record locks?
Whenever I edit a postgresql record from a MS Access datasheet view or bound control, it seems to lock the record forever and subsrquent edits result in "The record has been changed by another user blah blah ..." and I can no longer edit that record from the datasheet view or bound control (though I can execute a SQL query to update it).
We are using postgresql 7.2 and ODBC driver 7.02.00.01
This persists even if I delete the linked table and relink it, or close and restart Access.
Any ideas?
Fred Parkinson
Is your linked table actually a view with insert/update/delete rules? Then
the update through bound controls will not work in 7.2. 7.3 resolves the
issue.
Otherwise the problem could be float (or other non-exact) columns in your
table. Access remembers old values of record and when updating, it adds them
to where condition:
UPDATE table SET col1 = newvalue WHERE id = old_id AND col1 = oldvalue1 AND
col2 = oldvalue2;
The mentioned error is returned when the update returns 0 modified rows -
someone has changed the record and old values are not current any more. This
is called optimistic locking.
The problem is, that when Access remembers float values, it sometimes rounds
them a bit.
select 1/3::float, 0.333333333333333, 1/3::float = 0.333333333333333;
?column? | ?column? | ?column?
-------------------+-------------------+----------
0.333333333333333 | 0.333333333333333 | f
(1 row)
So the optimistic locking condition is not true any more, although the
record has not changed.
You have to switch on psqlodbc logging and see, what query Access executes.
Then you have determine, what column is causing the error. Then you could
change datatype of the column. Or you could build a view which rounds the
value, add insert/update/delete rules to the view and do all database
manipulation through view. I have seen this problem before in this list, so
maybe someone has better solution.
Tambet
----- Original Message -----
From: Fred Parkinson
To: pgsql-odbc@postgresql.org
Sent: Thursday, February 06, 2003 10:09 PM
Subject: [ODBC] record locks?
Whenever I edit a postgresql record from a MS Access datasheet view or
bound control, it seems to lock the record forever and subsrquent edits
result in "The record has been changed by another user blah blah ..." and I
can no longer edit that record from the datasheet view or bound control
(though I can execute a SQL query to update it).
We are using postgresql 7.2 and ODBC driver 7.02.00.01
This persists even if I delete the linked table and relink it, or close and
restart Access.
Any ideas?
Fred Parkinson
Thanks for responding, clearly i should have been more specific in my description of the problem.
The linked table is a table, created in postgres with the CREATE TABLE command.
I am trying to update boolean fields through check boxs in Access datesheet view, and also through bound controls on forms.
I can insert a record through the Access datasheet view by typing a value into the key field in the '*' record
(the 'add new record' record)
After that I can successfully update fields only if i do so before i leave that record.
After that, any other changes I try to make to the data, for example check a check box to
update a boolean field, i get the "the record has been changed by another user..." message and changes are dropped.
I can make updates to the table subsequently only if i execute sql commands with the Access execute function.
Since that form of update works, executing a sql query in access, the system is correctly locating records by the key field value.
When I ran the odbc driver install i unchecked the 'bool as char' box.
I am actually doing no explicit record locking,
No one else is locking the record because we are testing a new system in a very small development community (2 developers) and the other person is not here!
When our postgres/unix administrator returns i will ask him to turn on psqlodbc logging to see what postgres thinks of attempts to update the fields through bound Access controls and the Access datasheet view.
Thanks for your help with this matter!
Fred Parkinson
>>> "Tambet Matiisen" <t.matiisen@aprote.ee> 02/07/03 01:11AM >>>
Is your linked table actually a view with insert/update/delete rules? Then
the update through bound controls will not work in 7.2. 7.3 resolves the
issue.
Otherwise the problem could be float (or other non-exact) columns in your
table. Access remembers old values of record and when updating, it adds them
to where condition:
UPDATE table SET col1 = newvalue WHERE id = old_id AND col1 = oldvalue1 AND
col2 = oldvalue2;
The mentioned error is returned when the update returns 0 modified rows -
someone has changed the record and old values are not current any more. This
is called optimistic locking.
The problem is, that when Access remembers float values, it sometimes rounds
them a bit.
select 1/3::float, 0.333333333333333, 1/3::float = 0.333333333333333;
?column? | ?column? | ?column?
-------------------+-------------------+----------
0.333333333333333 | 0.333333333333333 | f
(1 row)
So the optimistic locking condition is not true any more, although the
record has not changed.
You have to switch on psqlodbc logging and see, what query Access executes.
Then you have determine, what column is causing the error. Then you could
change datatype of the column. Or you could build a view which rounds the
value, add insert/update/delete rules to the view and do all database
manipulation through view. I have seen this problem before in this list, so
maybe someone has better solution.
Tambet
----- Original Message -----
From: Fred Parkinson
To: pgsql-odbc@postgresql.org
Sent: Thursday, February 06, 2003 10:09 PM
Subject: [ODBC] record locks?
Whenever I edit a postgresql record from a MS Access datasheet view or
bound control, it seems to lock the record forever and subsrquent edits
result in "The record has been changed by another user blah blah ..." and I
can no longer edit that record from the datasheet view or bound control
(though I can execute a SQL query to update it).
We are using postgresql 7.2 and ODBC driver 7.02.00.01
This persists even if I delete the linked table and relink it, or close and
restart Access.
Any ideas?
Fred Parkinson
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
>>> "Tambet Matiisen" <t.matiisen@aprote.ee> 02/07/03 01:11AM >>>
Is your linked table actually a view with insert/update/delete rules? Then
the update through bound controls will not work in 7.2. 7.3 resolves the
issue.
Otherwise the problem could be float (or other non-exact) columns in your
table. Access remembers old values of record and when updating, it adds them
to where condition:
UPDATE table SET col1 = newvalue WHERE id = old_id AND col1 = oldvalue1 AND
col2 = oldvalue2;
The mentioned error is returned when the update returns 0 modified rows -
someone has changed the record and old values are not current any more. This
is called optimistic locking.
The problem is, that when Access remembers float values, it sometimes rounds
them a bit.
select 1/3::float, 0.333333333333333, 1/3::float = 0.333333333333333;
?column? | ?column? | ?column?
-------------------+-------------------+----------
0.333333333333333 | 0.333333333333333 | f
(1 row)
So the optimistic locking condition is not true any more, although the
record has not changed.
You have to switch on psqlodbc logging and see, what query Access executes.
Then you have determine, what column is causing the error. Then you could
change datatype of the column. Or you could build a view which rounds the
value, add insert/update/delete rules to the view and do all database
manipulation through view. I have seen this problem before in this list, so
maybe someone has better solution.
Tambet
----- Original Message -----
From: Fred Parkinson
To: pgsql-odbc@postgresql.org
Sent: Thursday, February 06, 2003 10:09 PM
Subject: [ODBC] record locks?
Whenever I edit a postgresql record from a MS Access datasheet view or
bound control, it seems to lock the record forever and subsrquent edits
result in "The record has been changed by another user blah blah ..." and I
can no longer edit that record from the datasheet view or bound control
(though I can execute a SQL query to update it).
We are using postgresql 7.2 and ODBC driver 7.02.00.01
This persists even if I delete the linked table and relink it, or close and
restart Access.
Any ideas?
Fred Parkinson
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Hello together,
i have the same problem with one table "the record has been changed by another user..." with the same configuration as Fred
but at another table on postgres it works fine with only one boolean field.
i created both tables from an MS Access Database with the Migration Wizard from pgadmin II
i tested the "problem" table with a view from MS Access (with an without the boolean fields)
with boolean fields in the view i get the error message "the record has been changed by another user..." on an update of a field
without the boolean fields it works fine
when i use sql statements from VBA, i have to use a type cast like sqlstring = "... where cbool(mybooleanfield) = true" as a workaround, this will work also
any ideas, why i have problem with one table ?
At least, you all made a great job on postgres, thank you !!
postgres is the most productive tool i ever seen,
best regards
henry
-----Ursprüngliche Nachricht-----
Von: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]Im Auftrag von Fred Parkinson
Gesendet: Donnerstag, 13. Februar 2003 22:05
An: t.matiisen@aprote.ee; pgsql-odbc@postgresql.org
Betreff: Re: [ODBC] record locks?Thanks for responding, clearly i should have been more specific in my description of the problem.The linked table is a table, created in postgres with the CREATE TABLE command.I am trying to update boolean fields through check boxs in Access datesheet view, and also through bound controls on forms.I can insert a record through the Access datasheet view by typing a value into the key field in the '*' record(the 'add new record' record)After that I can successfully update fields only if i do so before i leave that record.After that, any other changes I try to make to the data, for example check a check box toupdate a boolean field, i get the "the record has been changed by another user..." message and changes are dropped.I can make updates to the table subsequently only if i execute sql commands with the Access execute function.Since that form of update works, executing a sql query in access, the system is correctly locating records by the key field value.When I ran the odbc driver install i unchecked the 'bool as char' box.I am actually doing no explicit record locking,No one else is locking the record because we are testing a new system in a very small development community (2 developers) and the other person is not here!When our postgres/unix administrator returns i will ask him to turn on psqlodbc logging to see what postgres thinks of attempts to update the fields through bound Access controls and the Access datasheet view.Thanks for your help with this matter!Fred Parkinson
>>> "Tambet Matiisen" <t.matiisen@aprote.ee> 02/07/03 01:11AM >>>
Is your linked table actually a view with insert/update/delete rules? Then
the update through bound controls will not work in 7.2. 7.3 resolves the
issue.
Otherwise the problem could be float (or other non-exact) columns in your
table. Access remembers old values of record and when updating, it adds them
to where condition:
UPDATE table SET col1 = newvalue WHERE id = old_id AND col1 = oldvalue1 AND
col2 = oldvalue2;
The mentioned error is returned when the update returns 0 modified rows -
someone has changed the record and old values are not current any more. This
is called optimistic locking.
The problem is, that when Access remembers float values, it sometimes rounds
them a bit.
select 1/3::float, 0.333333333333333, 1/3::float = 0.333333333333333;
?column? | ?column? | ?column?
-------------------+-------------------+----------
0.333333333333333 | 0.333333333333333 | f
(1 row)
So the optimistic locking condition is not true any more, although the
record has not changed.
You have to switch on psqlodbc logging and see, what query Access executes.
Then you have determine, what column is causing the error. Then you could
change datatype of the column. Or you could build a view which rounds the
value, add insert/update/delete rules to the view and do all database
manipulation through view. I have seen this problem before in this list, so
maybe someone has better solution.
Tambet
----- Original Message -----
From: Fred Parkinson
To: pgsql-odbc@postgresql.org
Sent: Thursday, February 06, 2003 10:09 PM
Subject: [ODBC] record locks?
Whenever I edit a postgresql record from a MS Access datasheet view or
bound control, it seems to lock the record forever and subsrquent edits
result in "The record has been changed by another user blah blah ..." and I
can no longer edit that record from the datasheet view or bound control
(though I can execute a SQL query to update it).
We are using postgresql 7.2 and ODBC driver 7.02.00.01
This persists even if I delete the linked table and relink it, or close and
restart Access.
Any ideas?
Fred Parkinson
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
You can turn on the psqlodbc logging by yourself. Just open Control Panel -> Administrative Tools -> Data Sources (ODBC), find your DSN and click Configure. Tambet ----- Original Message ----- From: Fred Parkinson To: t.matiisen@aprote.ee ; pgsql-odbc@postgresql.org Sent: Thursday, February 13, 2003 11:04 PM Subject: Re: [ODBC] record locks? Thanks for responding, clearly i should have been more specific in my description of the problem. The linked table is a table, created in postgres with the CREATE TABLE command. I am trying to update boolean fields through check boxs in Access datesheet view, and also through bound controls on forms. I can insert a record through the Access datasheet view by typing a value into the key field in the '*' record (the 'add new record' record) After that I can successfully update fields only if i do so before i leave that record. After that, any other changes I try to make to the data, for example check a check box to update a boolean field, i get the "the record has been changed by another user..." message and changes are dropped. I can make updates to the table subsequently only if i execute sql commands with the Access execute function. Since that form of update works, executing a sql query in access, the system is correctly locating records by the key field value. When I ran the odbc driver install i unchecked the 'bool as char' box. I am actually doing no explicit record locking, No one else is locking the record because we are testing a new system in a very small development community (2 developers) and the other person is not here! When our postgres/unix administrator returns i will ask him to turn on psqlodbc logging to see what postgres thinks of attempts to update the fields through bound Access controls and the Access datasheet view. Thanks for your help with this matter! Fred Parkinson
Wow!! This must have been one of those quantum particle-like problems:
as soon as i turned on psqlodbc loggiing to look closely at the problem, it vanished!!!!!
Thanks a LOT for your help.
Fred Parkinson
Association of Bay Area Governments
>>> "Tambet Matiisen" <tambet.matiisen@mail.ee> 02/14/03 08:10AM >>>
You can turn on the psqlodbc logging by yourself. Just open Control Panel ->
Administrative Tools -> Data Sources (ODBC), find your DSN and click
Configure.
Tambet
----- Original Message -----
From: Fred Parkinson
To: t.matiisen@aprote.ee ; pgsql-odbc@postgresql.org
Sent: Thursday, February 13, 2003 11:04 PM
Subject: Re: [ODBC] record locks?
Thanks for responding, clearly i should have been more specific in my
description of the problem.
The linked table is a table, created in postgres with the CREATE TABLE
command.
I am trying to update boolean fields through check boxs in Access datesheet
view, and also through bound controls on forms.
I can insert a record through the Access datasheet view by typing a value
into the key field in the '*' record
(the 'add new record' record)
After that I can successfully update fields only if i do so before i leave
that record.
After that, any other changes I try to make to the data, for example check a
check box to
update a boolean field, i get the "the record has been changed by another
user..." message and changes are dropped.
I can make updates to the table subsequently only if i execute sql commands
with the Access execute function.
Since that form of update works, executing a sql query in access, the system
is correctly locating records by the key field value.
When I ran the odbc driver install i unchecked the 'bool as char' box.
I am actually doing no explicit record locking,
No one else is locking the record because we are testing a new system in a
very small development community (2 developers) and the other person is not
here!
When our postgres/unix administrator returns i will ask him to turn on
psqlodbc logging to see what postgres thinks of attempts to update the
fields through bound Access controls and the Access datasheet view.
Thanks for your help with this matter!
Fred Parkinson