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 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.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] plpgsql triggers question -> foo := NEW ?
Hi list, I've searched the interactive docs and found this link: http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html There is no answer to the question below: How to set foo:= NEW or foo:= OLD in plpgsql trigger function (I have the same problem) Any help is appreciated. Thanks Mihail Changalov ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
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]
[SQL] Automated Backup
Dear Gurus, Is there a way to automate the backup databases using pg_dump (like in SQL server)? Regards Kumar
[SQL] Datafiles for Databases
Dear Friends, I was looking for a structure like oracle or ms Sql server in Postgres. What I have expected is individual datafiles for individual databases. But i cant fine such items in the /usr/local/pgsql/data directory. Will the postgres create individual data file for databases? How to get the datafile path of a database? Please shed light. Regards Kumar
[SQL] virus warning
Hi Recently I receive massive mail attack. This attack comes from some postgresql mailing list users. All send-to adresses are taken from users mailboxes which contain postgresql posts. Currently I found two kinds of viruses: 1. Empty post with "Undelivered message to..." body 2. Microsoft "Dear Customer... " based on www.microsoft.com design. Both mails contains some .exe attachement. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] is this explain good or bad???
Hey guys, I'm not sure if this is good or bad but when I want to have a record count of a view it takes at least 75 seconds to complete (PII 1.4Ghz 256Mb RAM). Also when I do a record count of one single table (same table but not joined) it just takes a long time. Firt I want to make sure that the DB is setup correctly before I jump to HW issues. This is my sq_logfile table explain select count(*) from sq_logfile; Aggregate (cost=35988.07..35988.07 rows=1 width=0) -> Seq Scan on sq_logfile (cost=0.00..33493.86 rows=997686 width=0) This is ,y sq_flogfile view based on sq_flogfile explain select count(*) from sq_flogile; Aggregate (cost=128282.68..128282.68 rows=1 width=40) -> Hash Join (cost=8.65..125788.46 rows=997686 width=40) -> Hash Join (cost=6.49..105832.58 rows=997686 width=36) -> Hash Join (cost=4.75..88371.34 rows=997686 width=32) -> Hash Join (cost=3.69..68416.56 rows=997686 width=28) -> Hash Join (cost=2.58..50955.94 rows=997686 width=24) -> Seq Scan on sq_logfile sl (cost=0.00..33493.86 rows=997686 width=20) -> Hash (cost=2.26..2.26 rows=126 width=4) -> Seq Scan on sq_contenttypes ct (cost=0.00..2.26 rows=126 width=4) -> Hash (cost=1.09..1.09 rows=9 width=4) -> Seq Scan on sq_requestmethods rm (cost=0.00..1.09 rows=9 width=4) -> Hash (cost=1.05..1.05 rows=5 width=4) -> Seq Scan on sq_hierarchycodes hc (cost=0.00..1.05 rows=5 width=4) -> Hash (cost=1.59..1.59 rows=59 width=4) -> Seq Scan on sq_resultcodes rc (cost=0.00..1.59 rows=59 width=4) -> Hash (cost=1.93..1.93 rows=93 width=4) -> Seq Scan on sq_clientaddrfqdn cafqdn (cost=0.00..1.93 rows=93 width=4) <---> CREATE TABLE sq_logfile ( id SERIAL8, stime NUMERIC(14,3), tstime TIMESTAMP, duration INTEGER, client_addr_dotted INET, client_addr_fqdn_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_clientaddrfqdn (id), resultcode_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_resultcodes(id), requestsize INTEGER, requestmethod_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_requestmethods (id), url TEXT, rfc931 TEXT, hierarchycode_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_hierarchycodes (id), hierarchycode TEXT, contenttype_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_contenttypes (id), PRIMARY KEY(id) ); -- Everything is sorted bu date/time so we need a index??? CREATE INDEX idx_sq_logfile1 ON sq_logfile (tstime); CREATE INDEX idx_sq_logfile2 ON sq_logfile (tstime, client_addr_dotted); CREATE INDEX idx_sq_logfile3 ON sq_logfile (tstime, rfc931); ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] is this explain good or bad???
explain select count(*) from sq_logfile; Not too helpful. Better choice is: explain analyze select * from sq_logfile; Your explains show that selecting from view is 4 times slower than selecting from a table (35988:128282). It is possible. Anyway counting 1 million rows usualy takes a long time... Regards, Tomasz Myrta ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
