transaction locking - Mailing list pgsql-sql

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 record
into a parts table, and then insert a record that references the part into an
application table. and the transaction fails with this error message:

ERROR: app_part_ins_trig referential integrity violation - key referenced from
application not found in parts

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

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_nocharacter varying(2) NOT NULL,subgroup_no character varying(8),part_name character varying(32) DEFAULT ''
NOTNULL,description character varying(255),prim_grp character(2),prim_sbg character(8),no_req integer,weight
numeric(6,2),countinteger DEFAULT 0,ordered integer DEFAULT 0,cost numeric(6,2),price numeric(6,2),graph character
varying(128),Constraintparts_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,yearinteger 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
application  FROM parts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins" ('app_part_ins_trig', 'application',
'parts', 'UNSPECIFIED', 'make', 'make', 'amc_part_no', 'amc_part_no');

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

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

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

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


--
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!!