[SQL] 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_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
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."\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\n" ;
$replaces.= $r."\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\n" ;
$replaces.= $r."\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!!
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] transaction locking
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!
---(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
Re: [SQL] transaction locking
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 any
government bureaucracy, it is the deadwood and the fat that do
the cutting, and the public's services are cut.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
