BUG #4407: Bug in PQexecPrepared when using an integer primary key that does not start at 1 - Mailing list pgsql-bugs

From Kevin Jenkins
Subject BUG #4407: Bug in PQexecPrepared when using an integer primary key that does not start at 1
Date
Msg-id 200809070220.m872KWw8039668@wwwmaster.postgresql.org
Whole thread Raw
Responses Re: BUG #4407: Bug in PQexecPrepared when using an integer primary key that does not start at 1
List pgsql-bugs
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.

pgsql-bugs by date:

Previous
From: "Mike Gagnon"
Date:
Subject: BUG #4406: silent install error
Next
From: Bruce Momjian
Date:
Subject: Re: PG 8.3.3 - ERROR: lock AccessShareLock on object 16385/16467/0 is already held