Обсуждение: safely exchanging primary keys?
Hi,
I have this function which swaps primary keys for cabin_types (so that
id_cabin_type ordering reflects natural data ordering):
CREATE OR REPLACE FUNCTION swap_cabin_types(id1 integer, id2 integer) RETURNS integer AS $$declare tmp
integer;begin tmp := nextval('cabin_type_id_cabin_type_seq'); update cabin_type set id_cabin_type=tmp where
id_cabin_type=id1; update cabin_type set id_cabin_type=id1 where id_cabin_type=id2; update cabin_type set
id_cabin_type=id2where id_cabin_type=tmp; return tmp;end;$$LANGUAGE plpgsql;
'id_cabin_type' is a foreign key for two other tables, 'cabin_category'
and 'alert_cabin_type', which have an "on update cascade" clause.
When I run that function it seems the foreign keys are not properly
updated and the data ends up in a mess.
Did I forget something?
Thanks,
On Mon, May 24, 2010 at 10:51:01AM +0200, Louis-David Mitterrand wrote: > Hi, > > I have this function which swaps primary keys for cabin_types (so that > id_cabin_type ordering reflects natural data ordering): Actually this function works fine. My problem was elsewhere. Sorry for barking up the wrong tree.
And relying on keys for a sort order is a very wrong tree :) On 05/24/2010 08:05 AM, Louis-David Mitterrand wrote: > On Mon, May 24, 2010 at 10:51:01AM +0200, Louis-David Mitterrand wrote: >> Hi, >> >> I have this function which swaps primary keys for cabin_types (so that >> id_cabin_type ordering reflects natural data ordering): > > Actually this function works fine. My problem was elsewhere. Sorry for > barking up the wrong tree. >
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> writes:
> When I run that function it seems the foreign keys are not properly
> updated and the data ends up in a mess.
Yeah? Could we see an actual example of what you're talking about?
And which PG version is this?
regards, tom lane
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote:
> I have this function which swaps primary keys for cabin_types (so that
> id_cabin_type ordering reflects natural data ordering):
> CREATE OR REPLACE FUNCTION swap_cabin_types(id1 integer, id2 integer) RETURNS integer
> AS $$
> declare
> tmp integer;
> begin
> tmp := nextval('cabin_type_id_cabin_type_seq');
> update cabin_type set id_cabin_type=tmp where id_cabin_type=id1;
> update cabin_type set id_cabin_type=id1 where id_cabin_type=id2;
> update cabin_type set id_cabin_type=id2 where id_cabin_type=tmp;
> return tmp;
> end;
> $$
> LANGUAGE plpgsql;
> 'id_cabin_type' is a foreign key for two other tables, 'cabin_category'
> and 'alert_cabin_type', which have an "on update cascade" clause.
> When I run that function it seems the foreign keys are not properly
> updated and the data ends up in a mess.
> Did I forget something?
What does "are not properly updated" mean? Anyhow, why don't
you use something simple like (untested):
| UPDATE cabin_type
| SET id_cabin_type =
| CASE
| WHEN id_cabin_type = id1 THEN
| id2
| ELSE
| id1
| END
| WHERE id_cabin_type IN (id1, id2);
Tim
On Mon, May 24, 2010 at 02:38:39PM +0000, Tim Landscheidt wrote: > Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote: > > What does "are not properly updated" mean? Anyhow, why don't Hi, I did follow-up on my own post: the problem was elsewhere. > you use something simple like (untested): > > | UPDATE cabin_type > | SET id_cabin_type = > | CASE > | WHEN id_cabin_type = id1 THEN > | id2 > | ELSE > | id1 > | END > | WHERE id_cabin_type IN (id1, id2); Nice, thanks.
On Mon, May 24, 2010 at 07:00:30PM +0200, Louis-David Mitterrand wrote: > On Mon, May 24, 2010 at 02:38:39PM +0000, Tim Landscheidt wrote: > > you use something simple like (untested): > > > > | UPDATE cabin_type > > | SET id_cabin_type = > > | CASE > > | WHEN id_cabin_type = id1 THEN > > | id2 > > | ELSE > > | id1 > > | END > > | WHERE id_cabin_type IN (id1, id2); > > Nice, thanks. Ah, but this won't work as the UNIQUE PK constraint is in force.
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote: >> > you use something simple like (untested): >> > >> > | UPDATE cabin_type >> > | SET id_cabin_type = >> > | CASE >> > | WHEN id_cabin_type = id1 THEN >> > | id2 >> > | ELSE >> > | id1 >> > | END >> > | WHERE id_cabin_type IN (id1, id2); >> Nice, thanks. > Ah, but this won't work as the UNIQUE PK constraint is in force. Oh, yes, you're right, I didn't have that premise in mind. Tim
hi Louis-David,
> tmp := nextval('cabin_type_id_cabin_type_seq');
seems to me you're adding a newly created key value (for which there
isn't a record yet).
--
regards, jr. (jr@tailorware.org.uk)