Content-Type: text/plain;
  charset="us-ascii"
From: tom baker <postgres@atoka-software.com>
Reply-To: postgres@atoka-software.com
Organization: Atoka Software
To: pgsql-sql@postgresql.org
Subject: transaction locking
Date: Wed, 17 Sep 2003 22:07:08 -0700
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200309172207.09134.postgres@atoka-software.com>

hello all

i am (probably) shooting myself in the foot, but here goes the question.

inside of a begin transaction/commit block, i am attempting to insert a rec=
ord=20
into a parts table, and then insert a record that references the part into =
an=20
application table. and the transaction fails with this error message:

ERROR: app_part_ins_trig referential integrity violation - key referenced f=
rom=20
application not found in parts

i understand that since the record was inserted into the parts table *after=
*=20
the BEGIN TRANSACTION statement, the insert into the application table cann=
ot=20
see that a record exists until a commit.=20

any suggestions are greatly appreciated.

the tables are as shown:
CREATE TABLE parts (
	make character varying(16) DEFAULT 'AMC' NOT NULL,
	amc_part_no character varying(8) NOT NULL,
	group_no character varying(2) NOT NULL,
	subgroup_no character varying(8),
	part_name character varying(32) DEFAULT '' NOT NULL,
	description character varying(255),
	prim_grp character(2),
	prim_sbg character(8),
	no_req integer,
	weight numeric(6,2),
	count integer DEFAULT 0,
	ordered integer DEFAULT 0,
	cost numeric(6,2),
	price numeric(6,2),
	graph character varying(128),
	Constraint parts_pkey Primary Key (make, amc_part_no)
);

CREATE TABLE application (
	make character varying(16) DEFAULT 'AMC' NOT NULL,
	amc_part_no character varying(8) NOT NULL,
	year integer NOT NULL,
	model character varying(2) NOT NULL,
	Constraint application_pkey Primary Key (make, amc_part_no, year, model)
);

and the constraint that is causing problems is:
CREATE CONSTRAINT TRIGGER app_part_ins_trig AFTER INSERT OR UPDATE ON=20
application  FROM parts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW=20
EXECUTE PROCEDURE "RI_FKey_check_ins" ('app_part_ins_trig', 'application',=
=20
'parts', 'UNSPECIFIED', 'make', 'make', 'amc_part_no', 'amc_part_no');

inside the program in question, i do a
$res=3Dpg_query( "BEGIN TRANSACTION" ) ;
if (strlen ($r=3Dpg_last_error( $db ) ) )
{
	$replaces.=3D $r."<br>\n" ;
	pg_connection_reset( $db ) ;
	$failed =3D -1 ;
}

$sql =3D "LOCK TABLE parts IN SHARE ROW EXCLUSIVE MODE" ;
$res =3D pg_query( $sql ) ;
if (strlen ($r=3Dpg_last_error( $db ) ) )
{
	$replaces.=3D"SQL: $sql<br>\n" ;
	$replaces.=3D $r."<br>\n" ;
	pg_connection_reset( $db ) ;
	$failed =3D -1 ;
}

$sql =3D "SET CONSTRAINTS ALL DEFERRED" ;
$res =3D pg_query( $sql ) ;
if (strlen ($r=3Dpg_last_error( $db ) ) )
{
	$replaces.=3D"SQL: $sql<br>\n" ;
	$replaces.=3D $r."<br>\n" ;
	pg_connection_reset( $db ) ;
	$failed =3D -1 ;
}

if ( $failed =3D=3D 0 )
{
...


--=20
tia,
tom baker
former ingres programmer...
I'm using my X-RAY VISION to obtain a rare glimpse of the INNER
WORKINGS of this POTATO!!