Content-Type: text/plain;
  charset="iso-8859-1"
From: tom baker <postgres@atoka-software.com>
Reply-To: postgres@atoka-software.com
Organization: Atoka Software
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Subject: Re: transaction locking
Date: Thu, 18 Sep 2003 11:11:41 -0700
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <200309172207.09134.postgres@atoka-software.com>
	<20030917223104.D58881@megazone.bigpanda.com>
In-Reply-To: <20030917223104.D58881@megazone.bigpanda.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200309181111.42992.postgres@atoka-software.com>

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 err=
or
> > message:
> >
> > ERROR: app_part_ins_trig referential integrity violation - key referenc=
ed
> > 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 =3D "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 hav=
ing=20
problems (paired down; all error checking has been removed!)

$res=3Dpg_query( "BEGIN TRANSACTION" ) ;

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

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

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

$res =3D pg_query( $sql ) ;

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

if ( ( $alt_group > "" ) )
{
	$sql =3D "INSERT INTO part_group ( make, amc_part_no, group_no ) VALUES (=
=20
'$make' , '$amc_part_no' , '$alt_group' ) " ;
	$res =3D pg_query( $sql ) ;
}

	$sql =3D "INSERT INTO application VALUES ( '$make','$amc_part_no','$tyears=
',=20
'$Amodel' )" ;
	$res =3D pg_query( $sql ) ;

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

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