Thread: BUG #4407: Bug in PQexecPrepared when using an integer primary key that does not start at 1

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.

"Kevin Jenkins" <gameprogrammer@rakkar.org> writes:
> Description:        Bug in PQexecPrepared when using an integer primary key
> that does not start at 1

I'd say this is pilot error, most likely in the form of putting
parameter values into the wrong elements of the parameter arrays
passed to PQexecPrepared.  (Remember the zero'th element of those
arrays corresponds to parameter symbol $1.)

Even if I thought it was a real bug, it's impossible to investigate
without a far more complete test case than you've provided.  You
haven't shown us any client-side code.

            regards, tom lane