From: tom baker <postgres@atoka-software.com>
To: pgsql-sql@postgresql.org
Subject: transaction locking
Date: Wed, 17 Sep 2003 22:07:08 -0700
Message-ID: <200309172207.09134.postgres@atoka-software.com> (raw)
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!!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgres@atoka-software.com
Subject: Re: transaction locking
In-Reply-To: <200309172207.09134.postgres@atoka-software.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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