pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsafely exchanging primary keys?
9+ messages / 5 participants
[nested] [flat]
* safely exchanging primary keys?
@ 2010-05-24 08:51 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 4 replies; 9+ messages in thread
From: Louis-David Mitterrand @ 2010-05-24 08:51 UTC (permalink / raw)
To: pgsql-sql
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=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?
Thanks,
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: safely exchanging primary keys?
@ 2010-05-24 11:46 jr <jorg.raskowski@tailorware.org.uk>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
3 siblings, 0 replies; 9+ messages in thread
From: jr @ 2010-05-24 11:46 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: safely exchanging primary keys?
@ 2010-05-24 14:05 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
3 siblings, 1 reply; 9+ messages in thread
From: Louis-David Mitterrand @ 2010-05-24 14:05 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: safely exchanging primary keys?
@ 2010-05-24 14:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
3 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2010-05-24 14:22 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: safely exchanging primary keys?
@ 2010-05-24 14:22 Rob Sargent <robjsargent@gmail.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 9+ messages in thread
From: Rob Sargent @ 2010-05-24 14:22 UTC (permalink / raw)
To: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: safely exchanging primary keys?
@ 2010-05-24 14:38 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
3 siblings, 1 reply; 9+ messages in thread
From: Tim Landscheidt @ 2010-05-24 14:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: safely exchanging primary keys?
@ 2010-05-24 17:00 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 1 reply; 9+ messages in thread
From: Louis-David Mitterrand @ 2010-05-24 17:00 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: safely exchanging primary keys?
@ 2010-05-24 17:11 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 9+ messages in thread
From: Louis-David Mitterrand @ 2010-05-24 17:11 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: safely exchanging primary keys?
@ 2010-05-25 01:22 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 9+ messages in thread
From: Tim Landscheidt @ 2010-05-25 01:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-05-25 01:22 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-24 08:51 safely exchanging primary keys? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-05-24 11:46 ` jr <jorg.raskowski@tailorware.org.uk>
2010-05-24 14:05 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-05-24 14:22 ` Rob Sargent <robjsargent@gmail.com>
2010-05-24 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-24 14:38 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-05-24 17:00 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-05-24 17:11 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-05-25 01:22 ` Tim Landscheidt <tim@tim-landscheidt.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox