public inbox for [email protected]
help / color / mirror / Atom feedconcurent updates
10+ messages / 7 participants
[nested] [flat]
* concurent updates
@ 2001-07-26 09:19 Steve SAUTETNER <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Steve SAUTETNER @ 2001-07-26 09:19 UTC (permalink / raw)
To: pgsql-general
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: concurent updates
@ 2001-07-26 16:26 Andre Schnabel <[email protected]>
parent: Steve SAUTETNER <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Andre Schnabel @ 2001-07-26 16:26 UTC (permalink / raw)
To: Steve SAUTETNER <[email protected]>; pgsql-general
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 protected]
Sent: Thursday, July 26, 2001 11:19 AM
Subject: [GENERAL] concurent updates
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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email protected])
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: concurent updates
@ 2001-07-26 18:00 Stephan Szabo <[email protected]>
parent: Steve SAUTETNER <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2001-07-26 18:00 UTC (permalink / raw)
To: Steve SAUTETNER <[email protected]>; +Cc: pgsql-general
On Thu, 26 Jul 2001, Steve SAUTETNER wrote:
> 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 ?
Either on update cascade (as suggested by someone else) or making the
constraint deferred in which case you can use a begin...commit block.
^ permalink raw reply [nested|flat] 10+ messages in thread
* What's going on here?
@ 2001-07-26 18:48 Dave Cramer <[email protected]>
parent: Stephan Szabo <[email protected]>
0 siblings, 4 replies; 10+ messages in thread
From: Dave Cramer @ 2001-07-26 18:48 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What's going on here?
@ 2001-07-26 19:21 Stephan Szabo <[email protected]>
parent: Dave Cramer <[email protected]>
3 siblings, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2001-07-26 19:21 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: pgsql-general
On Thu, 26 Jul 2001, Dave Cramer wrote:
>
>
> 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
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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What's going on here?
@ 2001-07-26 19:24 J.H.M. Dassen (Ray) <[email protected]>
parent: Dave Cramer <[email protected]>
3 siblings, 1 reply; 10+ messages in thread
From: J.H.M. Dassen (Ray) @ 2001-07-26 19:24 UTC (permalink / raw)
To: pgsql-general
Dave Cramer <[email protected]> wrote:
> ebox=# select version();
> version
> -------------------------------------------------------------
> PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96
I wouldn't trust that build for production uses; see
http://gcc.gnu.org/gcc-2.96.html .
> ebox=# select eitemcode,itemavail from inventory where itemavail = 1
> limit 1;
> eitemcode | itemavail
> -----------+-----------
> 6100122 | 1
> ebox=# update inventory set itemavail=0 where eitemcode=6100122;
> UPDATE 0
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Re: What's going on here?
@ 2001-07-26 19:46 Dave Cramer <[email protected]>
parent: J.H.M. Dassen (Ray) <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Dave Cramer @ 2001-07-26 19:46 UTC (permalink / raw)
To: pgsql-general
ebox=# select eitemcode,itemavail from inventory where
eitemcode=6100122;
eitemcode | itemavail
-----------+-----------
6100122 | 1
(1 row)
Dave
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of J.H.M. Dassen
(Ray)
Sent: July 26, 2001 3:24 PM
To: [email protected]
Subject: [GENERAL] Re: What's going on here?
Dave Cramer <[email protected]> wrote:
> ebox=# select version();
> version
> -------------------------------------------------------------
> PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96
I wouldn't trust that build for production uses; see
http://gcc.gnu.org/gcc-2.96.html .
> ebox=# select eitemcode,itemavail from inventory where itemavail = 1
> limit 1; eitemcode | itemavail
> -----------+-----------
> 6100122 | 1
> ebox=# update inventory set itemavail=0 where eitemcode=6100122;
> UPDATE 0
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What's going on here?
@ 2001-07-26 21:14 Roderick A. Anderson <[email protected]>
parent: Dave Cramer <[email protected]>
3 siblings, 0 replies; 10+ messages in thread
From: Roderick A. Anderson @ 2001-07-26 21:14 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: pgsql-general
On Thu, 26 Jul 2001, Dave Cramer wrote:
> 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
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What's going on here?
@ 2001-07-27 05:56 Tom Lane <[email protected]>
parent: Dave Cramer <[email protected]>
3 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2001-07-27 05:56 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: What's going on here figured it out?
@ 2001-07-27 12:32 Dave Cramer <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Dave Cramer @ 2001-07-27 12:32 UTC (permalink / raw)
To: 'Tom Lane' <[email protected]>; +Cc: pgsql-general
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 protected]
[mailto:[email protected]] On Behalf Of Tom Lane
Sent: July 27, 2001 1:57 AM
To: [email protected]
Cc: [email protected]
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2001-07-27 12:32 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-26 09:19 concurent updates Steve SAUTETNER <[email protected]>
2001-07-26 16:26 ` Andre Schnabel <[email protected]>
2001-07-26 18:00 ` Stephan Szabo <[email protected]>
2001-07-26 18:48 ` What's going on here? Dave Cramer <[email protected]>
2001-07-26 19:21 ` Re: What's going on here? Stephan Szabo <[email protected]>
2001-07-26 19:24 ` Re: What's going on here? J.H.M. Dassen (Ray) <[email protected]>
2001-07-26 19:46 ` RE: Re: What's going on here? Dave Cramer <[email protected]>
2001-07-26 21:14 ` Re: What's going on here? Roderick A. Anderson <[email protected]>
2001-07-27 05:56 ` Re: What's going on here? Tom Lane <[email protected]>
2001-07-27 12:32 ` RE: What's going on here figured it out? Dave Cramer <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox