Postgres Pro
Downloads
Home   >   mailing lists

Thread: safely exchanging primary keys?

safely exchanging primary keys?

From
Louis-David Mitterrand
Date:
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,

Re: safely exchanging primary keys?

From
Louis-David Mitterrand
Date:
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.

Re: safely exchanging primary keys?

From
Rob Sargent
Date:
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.
>

Re: safely exchanging primary keys?

From
Tom Lane
Date:
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

Re: safely exchanging primary keys?

From
Tim Landscheidt
Date:
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

Re: safely exchanging primary keys?

From
Louis-David Mitterrand
Date:
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.

Re: safely exchanging primary keys?

From
Louis-David Mitterrand
Date:
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.

Re: safely exchanging primary keys?

From
Tim Landscheidt
Date:
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

Re: safely exchanging primary keys?

From
jr
Date:
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)