Re: transaction locking - Mailing list pgsql-sql

On Wednesday 17 September 2003 22:33, Stephan Szabo pronounced:
> On Wed, 17 Sep 2003, tom baker wrote:
> > 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.
>
> Assuming that they are both in the same transaction, the second insert
> should be able to see the results of the first insert, can you send an
> example sequence of inserts as well? (Also see the note below)
>
> > 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');
> >
> > $sql = "SET CONSTRAINTS ALL DEFERRED" ;
>
> I'm not sure if you know, but this is not going to deferr the constraint
> above because it was created with NOT DEFERRABLE.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

ah, yes, i DID not see the NOT. that is fixed. here is the code that is having
problems (paired down; all error checking has been removed!)

$res=pg_query( "BEGIN TRANSACTION" ) ;

$sql = "LOCK TABLE parts IN SHARE ROW EXCLUSIVE MODE" ;
$res = pg_query( $sql ) ;

$sql = "SET CONSTRAINTS ALL DEFERRED" ;
$res = pg_query( $sql ) ;

if ( $amc_part_no == "" )
{$sql = "SELECT max(amc_part_no) FROM parts WHERE amc_part_no like 'G%'" ;$res = pg_query( $sql ) ;$amc_part_no =
pg_fetch_result($res , 0 , 0 ) ;# and generate the next part number... 
}

$res = pg_query( $sql ) ;

$sql = "INSERT INTO parts VALUES ( '".$make."', '".$amc_part_no."',
'".$group_no."', '".$subgroup_no."', '".$part_name."', '".$description."',
'".$prim_grp."', '".$prim_sbg."', '".$no_req."', '".$weight."',
'".$graphic."' )" ;
$res = pg_query( $sql ) ;

if ( ( $alt_group > "" ) )
{$sql = "INSERT INTO part_group ( make, amc_part_no, group_no ) VALUES (
'$make' , '$amc_part_no' , '$alt_group' ) " ;$res = pg_query( $sql ) ;
}
$sql = "INSERT INTO application VALUES ( '$make','$amc_part_no','$tyears',
'$Amodel' )" ;$res = pg_query( $sql ) ;

if ( $cmplt == 0 || $failed )
{pg_query( "ROLLBACK TRANSACTION" ) ;
}
else
{pg_query( "COMMIT TRANSACTION" ) ;
}

--
regards,
tom baker
former ingres programmer...
You'll never be the man your mother was!