Обсуждение: unique key problem on update

Hi folks.

I've got the table and data shown below. 

I want to add a new page after page 2 so I try to increase the sequence number 
of each row from page 3 onwards to make space in the sequence for the new 
record. However, I get duplicate key errors when I try. Can anyone suggest 
how I get round this.

Also, the final version will be put onto a WordPress web site which means I 
will have to port it to MYSQL which I don't know, so any solution that will 
work with both systems would be a great help.

Ta

Gary


stainburn=# \d skills_pages                                   Table "public.skills_pages"  Column    |         Type
    |                          Modifiers                           
 
-------------+-----------------------+--------------------------------------------------------------sp_id       |
integer              | not null default 
 
nextval('skills_pages_sp_id_seq'::regclass)sp_sequence | integer               | not nullsp_title    | character
varying(80)| sp_narative | text                  | 
 
Indexes:   "skills_pages_pkey" PRIMARY KEY, btree (sp_id)   "skills_pages_sequence" UNIQUE, btree (sp_sequence)

stainburn=# select * from skills_pages;sp_id | sp_sequence |     sp_title     | sp_narative 
-------+-------------+------------------+-------------    1 |          10 | Departments      |     2 |          20 |
InterestGroups  |     3 |          30 | Customer Focused |     4 |          40 | Business Roles   |     5 |          50
|Commercial       |     6 |          60 | People Oriented  |     7 |          70 | Engineering      | 
 
(7 rows)

stainburn=# update skills_pages set sp_sequence=sp_sequence+10 where 
sp_sequence >= 30;
ERROR:  duplicate key value violates unique constraint "skills_pages_sequence"
stainburn=# 

Gary Stainburn wrote on 20.09.2013 18:07:
> I want to add a new page after page 2 so I try to increase the sequence number
> of each row from page 3 onwards to make space in the sequence for the new
> record. However, I get duplicate key errors when I try. Can anyone suggest
> how I get round this.
>
> Also, the final version will be put onto a WordPress web site which means I
> will have to port it to MYSQL which I don't know, so any solution that will
> work with both systems would be a great help.
>

You need to define the primary key as deferrable:

create table skills_pages
( sp_id        serial not null, sp_sequence  integer not null, sp_title     character varying(80), sp_narative  text,
primarykey (sp_id) deferrable
 
);

On Friday 20 September 2013 17:26:58 Thomas Kellerer wrote:
> You need to define the primary key as deferrable:
>
> create table skills_pages
> (
>   sp_id        serial not null,
>   sp_sequence  integer not null,
>   sp_title     character varying(80),
>   sp_narative  text,
>   primary key (sp_id) deferrable
> );

Cheers. I'll look at that. It's actually the second unique index that's the 
problem but I'm guessing I can set that index up as deferrable too.

Hopefully it'll work for mysql too.

-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk 

Gary Stainburn wrote on 20.09.2013 18:30:
>> You need to define the primary key as deferrable:
>>
>> create table skills_pages
>> (
>>    sp_id        serial not null,
>>    sp_sequence  integer not null,
>>    sp_title     character varying(80),
>>    sp_narative  text,
>>    primary key (sp_id) deferrable
>> );
>
> Cheers. I'll look at that. It's actually the second unique index that's the
> problem but I'm guessing I can set that index up as deferrable too.

Ah, sorry didn't see that ;) but, yes it works the same way:

create table skills_pages
(  sp_id        serial not null,  sp_sequence  integer not null,  sp_title     character varying(80),  sp_narative
text, primary key (sp_id),  unique (sp_sequence) deferrable
 
);
 
> Hopefully it'll work for mysql too.
No, it won't.

MySQL neither has deferrable constraints nor does it evaluate them on statement level (they are *always* evaluated
row-by-row).

I have a number of trigger functions on a table that are performing
various calculations.  The table is a column-wise orientation with
multiple columns that could be updated on a single row.  In one of the
triggers, I'm performing a calculation but don't want the code to run if
the OLD and NEW values are the same value.  This can be resulting from
other triggers that are running on the table.  If there is a truly NEW
(non-NULL) value, I want to run the code.

To deal with this, I'm using the following test in my code where I loop
through the columns that could be updated and test to determine which
column on the row is getting a value assigned.


EXECUTE 'SELECT (' ||quote_literal(NEW) || '::' || TG_RELID::regclass
||').' || quote_ident(metric_record.column_name) INTO changed_metric;       
if not changed_metric is null then

EXECUTE 'SELECT (' ||quote_literal(OLD) || '::' || TG_RELID::regclass
||').' || quote_ident(metric_record.column_name) INTO old_value;

if changed_metric <> old_value then

{calculation code}



This is all doing exactly what I want when the row exists.  However, I
think I'm getting an error if there is a new row getting generated.  I'm
getting the following error when the code runs sometimes:

ERROR: record "old" is not assigned yet
SQL state: 55000
Detail: The tuple structure of a not-yet-assigned record is indeterminate.

Is this what's happening?  If so, how can I avoid the issue.



Thanks,
James

On Fri, Sep 20, 2013 at 6:43 PM, James Sharrett <jsharrett@tidemark.net> wrote:

> ERROR: record "old" is not assigned yet
> SQL state: 55000
> Detail: The tuple structure of a not-yet-assigned record is indeterminate.
>
> Is this what's happening?  If so, how can I avoid the issue.

If I get it right you are running the trigger also for an insert,
which of course does not have an old value. You should either set the
trigger to run only on update statements or enforce your check to see
if the trigger has been invoked for something different than insert
statements.

Luca