pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtransaction locking
5+ messages / 2 participants
[nested] [flat]
* transaction locking
@ 2003-09-18 05:07 tom baker <postgres@atoka-software.com>
0 siblings, 1 reply; 5+ messages in thread
From: tom baker @ 2003-09-18 05:07 UTC (permalink / raw)
To: 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_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."<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!!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: transaction locking
@ 2003-09-18 05:33 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: tom baker <postgres@atoka-software.com>
0 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2003-09-18 05:33 UTC (permalink / raw)
To: tom baker <postgres@atoka-software.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: transaction locking
@ 2003-09-18 18:11 tom baker <postgres@atoka-software.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: tom baker @ 2003-09-18 18:11 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: 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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: transaction locking
@ 2003-09-18 18:24 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: tom baker <postgres@atoka-software.com>
0 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2003-09-18 18:24 UTC (permalink / raw)
To: tom baker <postgres@atoka-software.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: transaction locking
@ 2003-09-18 23:22 tom baker <postgres@atoka-software.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 5+ messages in thread
From: tom baker @ 2003-09-18 23:22 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-09-18 23:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-18 05:07 transaction locking tom baker <postgres@atoka-software.com>
2003-09-18 05:33 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-18 18:11 ` tom baker <postgres@atoka-software.com>
2003-09-18 18:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-18 23:22 ` tom baker <postgres@atoka-software.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox