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