Subject: concurent updates

From: "Steve SAUTETNER" <[email-masked]>
Date: Thu, 26 Jul 2001 11:19:36 +0200

hi everybody !

I've got a little problem when updating a primary key in two table
where the primary key of the one is a foreign key from the second :

here are the 2 tables :

create table table1 (id int primary key, col1 int);
create table table2 (id int primary key references table1(id), col2 int);

and the 2 updates :

1) update table2 set id = 1001 where id = 1;
2) update table1 set id = 1001 where id = 1;

i can't execute them separately because of an integrity constraint
violation.
i've got the same error in a BEGIN / COMMIT block containing the updates.

Does any one see how two help me ?

thanks.

---

From: [email-masked] (Andre Schnabel)
Date: Thu, 26 Jul 2001 18:26:27 +0200

Hi,

if you define the foreign key with "ON UPDATE CASCADE" you don't have to
worry about updating table2.
Would look like this:

the tables:
create table table1 (id int primary key, col1 int);
create table table2 (id int primary key references table1(id) ON UPDATE
CASCADE, col2 int);

the (one and only) UPDATE:
update table1 set id = 1001 where id = 1;

Your 2nd UPDATE will be done automatically.

The other solution for your problem is less elegant.
First INSERT a new record ( 1001 , x) for each row (1, x) in table1 into
table1
then UPDATE table2
last DELETE all records (1, x) from table1

Would look like:
BEGIN;
INSERT INTO table1 SELECT 1001, col1 FROM table1 WHERE id=1;
UPDATE table2 set id = 1001 WHERE id = 1;
DELETE FROM table1 where id=1;
COMMIT;

Hope this would help,
Andre

----- Original Message -----
From: Steve SAUTETNER
To: [email-masked]
Sent: Thursday, July 26, 2001 11:19 AM
Subject: [GENERAL] concurent updates

hi everybody !

here are the 2 tables :

and the 2 updates :

thanks.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email-masked])

---

From: Stephan Szabo <[email-masked]>
Date: Thu, 26 Jul 2001 11:00:34 -0700 (PDT)

> Does any one see how two help me ?

Either on update cascade (as suggested by someone else) or making the
constraint deferred in which case you can use a begin...commit block.

---

From: "Dave Cramer" <[email-masked]>
Date: Thu, 26 Jul 2001 14:48:28 -0400

I can't seem to change any rows in this table. Are there locks somewhere
that I can look at or fix

ebox=# select version();
                           version                           
-------------------------------------------------------------
 PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96
(1 row)

ebox=# select eitemcode,itemavail from inventory where itemavail = 1
limit 1;
 eitemcode | itemavail 
-----------+-----------
   6100122 |         1
(1 row)

ebox=# update inventory set itemavail=0 where eitemcode=6100122;
UPDATE 0

Thanks in advance,

Dave

---

From: Stephan Szabo <[email-masked]>
Date: Thu, 26 Jul 2001 12:21:54 -0700 (PDT)

> that I can look at or fix

Hmm, could it perhaps be a view (rather than a table)?  If it is a table,
are there any views or triggers on the table that might be interfering
with the update?

---

From: [email-masked] (J.H.M. Dassen (Ray))
Date: Thu, 26 Jul 2001 19:24:20 +0000 (UTC)

>                            version                           

I wouldn't trust that build for production uses; see
http://gcc.gnu.org/gcc-2.96.html .

> ebox=# update inventory set itemavail=0 where eitemcode=6100122;

What do you get with "select eitemcode,itemavail from inventory where
eitemcode=6100122"? "UPDATE 0" means no row matched the "where" clause.

If this select does return rows, something truely weird is happening. If it
does not return rows, there may be some kind of typing/casting issue at
work.

HTH,
Ray
-- 
Javascript is EVIL!
	keyweed

---

From: "Dave Cramer" <[email-masked]>
Date: Thu, 26 Jul 2001 15:46:23 -0400

ebox=# select eitemcode,itemavail from inventory where
eitemcode=6100122;
 eitemcode | itemavail 
-----------+-----------
   6100122 |         1
(1 row)

Dave

-----Original Message-----
From: [email-masked]
[mailto:[email-masked]] On Behalf Of J.H.M. Dassen
(Ray)
Sent: July 26, 2001 3:24 PM
To: [email-masked]
Subject: [GENERAL] Re: What's going on here?

> limit 1;  eitemcode | itemavail
> -----------+-----------
>    6100122 |         1

If this select does return rows, something truely weird is happening. If
it does not return rows, there may be some kind of typing/casting issue
at work.

HTH,
Ray
-- 
Javascript is EVIL!
	keyweed

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---

From: "Roderick A. Anderson" <[email-masked]>
Date: Thu, 26 Jul 2001 14:14:03 -0700 (PDT)

> ebox=# select eitemcode,itemavail from inventory where itemavail = 1

Did eitemcode get left padded with some whitespace character(s)?

Rod
Rod
-- 
                 Remove the word 'try' from your vocabulary ... 
                     Don't try.  Do it or don't do it ...
                                Steers try!

                                                            Don Aslett

---

From: Tom Lane <[email-masked]>
Date: Fri, 27 Jul 2001 01:56:49 -0400

Seems pretty weird.  May we see the full schema for the table?
"pg_dump -s -t tablename dbname" is the best way.

			regards, tom lane

---

From: "Dave Cramer" <[email-masked]>
Date: Fri, 27 Jul 2001 08:32:23 -0400

Thanks for everyone's input. Dumping the schema for the table was the
hint. I found the trigger that was causeing it to fail.

Would be nice if there was some debug mode to see what it was doing?

Dave

-----Original Message-----
From: [email-masked]
[mailto:[email-masked]] On Behalf Of Tom Lane
Sent: July 27, 2001 1:57 AM
To: [email-masked]
Cc: [email-masked]
Subject: Re: [GENERAL] What's going on here? 

Seems pretty weird.  May we see the full schema for the table? "pg_dump
-s -t tablename dbname" is the best way.

			regards, tom lane