Re: [GENERAL] Bad locking with MS-Access
Hello, Andreas! You mentioned: Use serial or serial4 to create auto-values. Don't use any bigint-types like bigserial. Access doesn't like 8-byte-ints.. Could you please explain why you don't recommend bigserial for primary key ? I use bigserial primary keys in Postgres tables, and din't realise problems with MS Access front-end. What problems could I expect ? Thanks, Zlatko - Original Message - From: Andreas [EMAIL PROTECTED] To: Ets ROLLAND [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Sunday, July 24, 2005 11:01 PM Subject: Re: [GENERAL] Bad locking with MS-Access Ets ROLLAND schrieb: For all the data created BEFORE the transfert to PostgreSQL, all works fine. For the records created SINCE this transfert, it is impossible to modify or delete these records !? MS-Access say that The record is acceded by an other user, even I am the only user. As Richard wrote in his mail, do set row versioning in the ODBC setup. Have a primary key in every table and a timestamp. Be careful not to use to big data types in PG that aren't supportet by Access. Use timestamp(0) to get timestamps compatible to Access' DateTime values. Use serial or serial4 to create auto-values. Don't use any bigint-types like bigserial. Access doesn't like 8-byte-ints. Keep in mind that Access' autovalues are signed, so they'll roll over at about 2 billion. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Bad locking with MS-Access
Hi Zlatko, You mentioned: Use serial or serial4 to create auto-values. Don't use any bigint-types like bigserial. Access doesn't like 8-byte-ints.. Could you please explain why you don't recommend bigserial for primary key ? I use bigserial primary keys in Postgres tables, and din't realise problems with MS Access front-end. What problems could I expect ? It depends on your application. In general you would chose datatypes only as big as you will likely need. PG can handle those huge 64 bit integers. big-serials get stored in big-integers. They are signed so the highest count is 2^63 = 9223372036854775808. As long as server and client can handle the size, use bigint if you consider this helpful. But if you NEED those big numbers, you are screwed with Access as client because it'll throw an error for values above 2^31. Below that limit all seems to be ok, since PG handles the bigints and ODBC translates them transparently to 4-byte-integers to keep Access happy. If you know definitely that this procedure will allways work because the limit won't be reached then you don't need the bigints at all. Depending on the way ODBC translates the numbers you might end up with negative values above 2^31 in Access though in PG the same field would be still positive of course. If you try to push a real Access longint above 2^31 you get an error and the field is empty afterwards. Perhaps one could map a PG bigint to text(20) or decimal(20). I guess there would be a performance penalty though. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Bad locking with MS-Access
Ets ROLLAND schrieb: For all the data created BEFORE the transfert to PostgreSQL, all works fine. For the records created SINCE this transfert, it is impossible to modify or delete these records !? MS-Access say that The record is acceded by an other user, even I am the only user. As Richard wrote in his mail, do set row versioning in the ODBC setup. Have a primary key in every table and a timestamp. Be careful not to use to big data types in PG that aren't supportet by Access. Use timestamp(0) to get timestamps compatible to Access' DateTime values. Use serial or serial4 to create auto-values. Don't use any bigint-types like bigserial. Access doesn't like 8-byte-ints. Keep in mind that Access' autovalues are signed, so they'll roll over at about 2 billion. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Bad locking with MS-Access
Hello ! I made the port of an Access 2002Database to PostgreSQL 8, it seems to work. I still use MS-Access 2002 for the application, using ODBCwith linked tables. For all the data created BEFORE the transfert to PostgreSQL, all works fine. For the records created SINCE this transfert, it is impossible to modify or delete these records !? MS-Access say that "The record is acceded by an other user", even I am the only user. I look for the the options / advanced tab of that MS-database, I don't find an issue. I look for the parameters of my ODBC system daya source without success. I don't find any solution with PostgreSQL... BUT if I modify any field in one of these locked records with the browser of pgAdmin III, then MS-Access accept to modify or delete the corresponding record !? If I write an SQL script to do the same in pgAdmin III (ex. add zero to an integer value), it don't works ! I'm going crazy !... HELP ! Luc
Re: [GENERAL] Bad locking with MS-Access
Ets ROLLAND wrote: Hello ! I made the port of an Access 2002 Database to PostgreSQL 8, it seems to work. I still use MS-Access 2002 for the application, using ODBC with linked tables. For all the data created BEFORE the transfert to PostgreSQL, all works fine. For the records created SINCE this transfert, it is impossible to modify or delete these records !? MS-Access say that The record is acceded by an other user, even I am the only user. Wild guess - Access is trying to identify rows by looking at the data, which includes a timestamp. PG supports fractional-seconds which is confusing Access. All your old timestamps aren't fractional because they were imported from Access. When you define your timestamps in PG you can control how much accuracy you want - might be worth dumping/restoring without the fractions. Look for a row versioning option in your ODBC settings. Also, there's an ODBC list too - check the mailing-list archives for that. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
