Обсуждение: 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.

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: pgsql-general@postgresql.org
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 majordomo@postgresql.org)

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.

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

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?

Dave Cramer <Dave@micro-automation.net> 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

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

Dave

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


Dave Cramer <Dave@micro-automation.net> 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

Why do you need to change the value of the id field??  The id field shouldn't
have any meaning attached to it beyond the fact that it uniquely identifies a
row in the table, and of course its usage as a foreign key when it serves that
role.  If you just want to change what numbers get assigned, I think you can
update the SEQUENCE that table1 uses.

If you really need to do this, you might have better luck using a trigger to do
a cascading update from table1 to table2, and then ONLY issue the update to
table1, counting on the trigger to update table2.



"Steve SAUTETNER" <ssa%informactis.com@interlock.lexmark.com> on 07/26/2001
05:19:36 AM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
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 majordomo@postgresql.org)

Unless you have over simplified your example, why do you have two tables?
Wouldn't:

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

do the same thing in one table?  I would think that ANY schema that has two
tables with the SAME primary key can be resolved to one table without losing
anything.

len morgan

> 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 majordomo@postgresql.org)
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

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

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

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: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: July 27, 2001 1:57 AM
To: Dave@micro-automation.net
Cc: pgsql-general@postgresql.org
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