create table foo (id integer, name varchar(20));Creating this index does not guarantee that your application is using the SQLPrimaryKeys() call. For example, MS Access 7.0 & 97 require the user to manually specify the key at link time. This key specification is required to modify a table from MA Access. See "Why does the PosgreSQL backend crash every time I browse some tables in MS Access?".
create unique index foo_key on foo using btree(id);
Why does the PostgreSQL backend crash every time I
browse some tables in MS Access?
When an Access user specifies a key at link time, the key is used to
generate queries to browse through the rows of the table.
When the key is multipart the query looks something like:
-- This is a 3 part keyUnforunatly as of the latest release of the driver, this kind of query cause the optimizer in the PostgreSQL backend to devour exponential amounts of memory and eventually crash. On some server systems a two part key will do the job. Using a table with couple rows the query may work in with Access because it builds the query with less lines.
select ... from foo where
(v1 = "?" AND v2 = "?" AND v3 ="?") OR -- line 1
(v1 = "?" AND v2 = "?" AND v3 ="?") OR -- line 2
...
(v1 = "?" AND v2 = "?" AND v3 ="?") OR -- line 27
(v1 = "?" AND v2 = "?" AND v3 ="?"); -- line 28