The following bug has been logged online: Bug reference: 4407 Logged by: Kevin Jenkins Email address: [EMAIL PROTECTED] 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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
