The following bug has been logged online:
Bug reference: 4407
Logged by: Kevin Jenkins
Email address: gameprogrammer@rakkar.org
PostgreSQL version: 8.3.3 build1400
Operating system: Windows
Description: Bug in PQexecPrepared when using an integer primary key
that does not start at 1
Details:
If I call PQexecPrepared from C++, it can fail incorrectly along the
following lines:
ERROR: insert or update on table "users" violates foreign key constraint
"users
_homecountryid_fk_fkey"
DETAIL: Key (homecountryid_fk)=(1) is not present in table "country".
Using the following table:
CREATE TABLE lobby2.country
(
country_id integer PRIMARY KEY NOT NULL, -- country id
country_sort_id integer NOT NULL, -- display order for a list of
countries...
country_code character varying(2) NOT NULL, -- country 2 letters ISO code,
like...
country_name character varying(100) NOT NULL, -- county's full name
country_has_states boolean NOT NULL DEFAULT false, -- defines if a country
has a pre-defined list of states. can be TRUE or FALSE
country_enable boolean NOT NULL DEFAULT true -- country enabled or
disbaled, can be either true or false
)
WITH (OIDS=FALSE);
INSERT INTO lobby2.country (country_id, country_sort_id, country_code,
country_name, country_has_states, country_enable) VALUES (120, 100, 'AF',
'Afghanistan', false, true);
INSERT INTO lobby2.country (country_id, country_sort_id, country_code,
country_name, country_has_states, country_enable) VALUES (121, 200, 'AL',
'Albania', false, true);
// ...
INSERT INTO lobby2.country (country_id, country_sort_id, country_code,
country_name, country_has_states, country_enable) VALUES (355, 23700, 'US',
'United States', true, true);
INSERT INTO lobby2.country (country_id, country_sort_id, country_code,
country_name, country_has_states, country_enable) VALUES (356, 23800, 'UM',
'United States Minor Outlying Isl', false, true);
// ...
With the following statement:
UPDATE lobby2.users SET homeCountryId_fk=$1::integer WHERE
userId_pk=$2::integer
Where $1::integer is 355 and userId_pk is 1.
The exact same statement, using text instead of a parameter:
UPDATE lobby2.users SET homeCountryId_fk=355 WHERE userId_pk=$2::integer
Works fine. It of course also works in the pgAdmin III query browser.
Adding a phony country that starts at index 1:
-- Phony country
INSERT INTO lobby2.country (country_id, country_sort_id, country_code,
country_name, country_has_states, country_enable) VALUES (1, 1, '1', '1',
false, false);
Fixes the problem.