Thread: transaction locking
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!!
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.
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!
On Thu, 18 Sep 2003, tom baker wrote:
> 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 ) ;
Can you print out the queries you think you're sending and turn on query
logging and see what the database things? Inserting only the first three
columns into parts and then a matching application row doesn't seem to
fail for me in 7.3.4 or 7.4 beta 1.
On Thursday 18 September 2003 11:24, Stephan Szabo pronounced:
> On Thu, 18 Sep 2003, tom baker wrote:
> > 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 ) ;
>
> Can you print out the queries you think you're sending and turn on query
> logging and see what the database things? Inserting only the first three
> columns into parts and then a matching application row doesn't seem to
> fail for me in 7.3.4 or 7.4 beta 1.
stephan:
i want to give you a great big thank you. THANK YOU!!!!!
i was shooting myself in the foot vigorously. one sql statements were being
executed twice, without the appropriate error checking (do i feel foolish or
what???? :(( ). see where i put "<<=====" above!
--
regards,
tom baker
former ingres programmer...
Magary's Principle:When there is a public outcry to cut deadwood and fat from anygovernment bureaucracy, it is the
deadwoodand the fat that dothe cutting, and the public's services are cut.