Обсуждение: Changing ids conflicting with serial values?
When I change an id (primary key serial) in a table, the next value returned by the sequence for the id can conflict with that id (e.g., change the id to be id + 1). MySQL seems to handle this transparently by skipping conflicting values, but with PostgreSQL I get primary key conflicts. It seems rather bad if a user can modify an id in a row and cause failures for all future inserts - it's just too fragile. What's the proper way to handle this in PostgreSQL?
Steven Brown <swbrown@ucsd.edu> writes:
> When I change an id (primary key serial) in a table, the next value
> returned by the sequence for the id can conflict with that id (e.g.,
> change the id to be id + 1). MySQL seems to handle this transparently
> by skipping conflicting values, but with PostgreSQL I get primary key
> conflicts. It seems rather bad if a user can modify an id in a row and
> cause failures for all future inserts - it's just too fragile. What's
> the proper way to handle this in PostgreSQL?
Plan A: don't do that. Why in the world is it a good idea to modify an
artificial primary key? It's not like there's some external meaning to
the values.
Plan B: after you do it, adjust the sequence generator with setval().
You can use max() to figure out where to set the generator.
regards, tom lane
Tom Lane wrote: > Steven Brown <swbrown@ucsd.edu> writes: >> When I change an id (primary key serial) in a table, the next value >> returned by the sequence for the id can conflict with that id (e.g., >> change the id to be id + 1). [...] > Plan A: don't do that. Why in the world is it a good idea to modify an > artificial primary key? It's not like there's some external meaning to > the values. I'm granting access to insert/update/delete rows of a table to people, but I don't want all future inserts to fail if they decided to change an id (which they obviously shouldn't, but they /can/). It makes for a fragile system. Should I just be using some sort of trigger to block them from modifying the id, or is there another way to handle it? I.e., how do people normally handle that? It's a migration thing - MySQL prevented this situation due to the way it handles auto_increment (it will never assign you an id that already exists).
Steven Brown wrote: > When I change an id (primary key serial) in a table, the next value > returned by the sequence for the id can conflict with that id (e.g., > change the id to be id + 1). MySQL seems to handle this transparently > by skipping conflicting values, but with PostgreSQL I get primary key > conflicts. It seems rather bad if a user can modify an id in a row > and cause failures for all future inserts - it's just too fragile. > What's the proper way to handle this in PostgreSQL? Why are your users modifying the key in the first place? Typically in an environment where you have an autoincrementing key, that key is static. Meaning it does not change for a particular row. Thus it never conflicts. Could you perhaps explain a little further what it is you are trying to do? Sincerely, Joshua D. Drake > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Steven Brown <swbrown@ucsd.edu> writes: > I'm granting access to insert/update/delete rows of a table to people, > but I don't want all future inserts to fail if they decided to change > an id (which they obviously shouldn't, but they /can/). It makes for > a fragile system. If it shouldn't happen, you should enforce that with a trigger. Easy enough to do. But giving access to an SQL prompt to people who don't know why you shouldn't change a PK ID scares me. :) -Doug
Strange - I had never realized that PostgreSQL would allow you to UPDATE a
primary key value. I thought that other db's I had used (e.g. Sybase,
Oracle, SQL Server, etc.) in the past would not allow that, and you had to
DELETE, then INSERT to modify a row that needed a different primary key.
Of course, that is only for tables whose primary key meant something - no
reason to change a serial-type primary key that does not really mean
anything.
Susan
Tom Lane
<tgl@sss.pgh.pa.us> To: Steven Brown <swbrown@ucsd.edu>
Sent by: cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Changing ids conflicting with serial
values?
pgsql-general-owner@pos |-------------------|
tgresql.org | [ ] Expand Groups |
|-------------------|
11/02/2005 06:38
PM
Steven Brown <swbrown@ucsd.edu> writes:
> When I change an id (primary key serial) in a table, the next value
> returned by the sequence for the id can conflict with that id (e.g.,
> change the id to be id + 1). MySQL seems to handle this transparently
> by skipping conflicting values, but with PostgreSQL I get primary key
> conflicts. It seems rather bad if a user can modify an id in a row and
> cause failures for all future inserts - it's just too fragile. What's
> the proper way to handle this in PostgreSQL?
Plan A: don't do that. Why in the world is it a good idea to modify an
artificial primary key? It's not like there's some external meaning to
the values.
Plan B: after you do it, adjust the sequence generator with setval().
You can use max() to figure out where to set the generator.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------
[snip] On Thu, 2005-11-03 at 18:02, SCassidy@overlandstorage.com wrote: > Strange - I had never realized that PostgreSQL would allow you to UPDATE a > primary key value. I thought that other db's I had used (e.g. Sybase, > Oracle, SQL Server, etc.) in the past would not allow that, and you had to > DELETE, then INSERT to modify a row that needed a different primary key. Well, you're wrong about that too... Oracle at least will definitely let you update a primary key. The primary key is technically nothing more than a unique, not null restriction, plus an index on it, otherwise you can do with it what you wish. > [snip] Cheers, Csaba.
On Wed, 02 Nov 2005 19:29:10 -0800, you wrote: >It's a migration thing - MySQL prevented this >situation due to the way it handles auto_increment (it will never assign >you an id that already exists). AFAIK, in mysql, if you modify a serial by setting it to the max value for this type, mysql will fail all new inserts.
Steven Brown wrote: > When I change an id (primary key serial) in a table, the next value > returned by the sequence for the id can conflict with that id (e.g., > change the id to be id + 1). [...] If you're doing this to have a custom ordering of your data, consider adding another int column without PK so you can change whatever you want, and keep an unique way to access (an identify) your data even if it has been moved... In my case I wanted to sort my row freely, my first tough was to use the already existing id (serial PK) column and change it. That worked fine until I tryied to move a row without freeing the destination first... I added a new column and changed my "order by" to match its name. If this can help, -- MaXX
I think he meant
create sequence test_seq;
select setval('test_seq',(select max(primary_key_id) from my_table));
not max value of a serial type.
Alex
On 11/3/05, Marc Boucher <achernar@gmx.net> wrote:
> On Wed, 02 Nov 2005 19:29:10 -0800, you wrote:
>
> >It's a migration thing - MySQL prevented this
> >situation due to the way it handles auto_increment (it will never assign
> >you an id that already exists).
>
> AFAIK, in mysql, if you modify a serial by setting it to the max value for
> this type, mysql will fail all new inserts.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
Steven Brown <swbrown@ucsd.edu> writes:
> I'm granting access to insert/update/delete rows of a table to people,
> but I don't want all future inserts to fail if they decided to change an
> id (which they obviously shouldn't, but they /can/). It makes for a
> fragile system.
create rule no_pkey_update
as on update to foo where new.id != old.id
do instead nothing;
Simple minded solution with negative aspect that it will silently skip
ANY update trying to change pkey... other changes to record also
discarded.
> Should I just be using some sort of trigger to block them from modifying
> the id, or is there another way to handle it? I.e., how do people
> normally handle that? It's a migration thing - MySQL prevented this
> situation due to the way it handles auto_increment (it will never assign
> you an id that already exists).
Bit more complex but still easy is trigger to just always set new.id
to old.id thereby insuring that it can't be changed.
create function no_pkey_update()
returns trigger
as '
begin
new.id = old.id;
return new;
end'
language plpgsql;
HTH
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/
At 11:49 04/11/2005 -0500, Alex Turner wrote:
> I think he meant
>
> create sequence test_seq;
> select setval('test_seq',(select max(primary_key_id) from my_table));
>
> not max value of a serial type.
What I understand, and from what I know by using mysql, is that mysql
auto-adjust the max value of a serial.
Something like :
- a table with 5000 elements with ids from 1-5000.
- if you update the id in one of the rows and set it to 65000, mysql
updates the serial current value.
- even if you delete, or change the id back to its previous value, the
current value will still be 65000.
- a new inserted row will have the id 65001.
Now assuming the id's maximum value is 65535, and you set one of the rows to
this value, mysql will be unable to find a "nextval" greater than 65535. New
inserts will fail.
I don't know if it is still the case with recent versions of mysql, but that's
what I discovered while testing a web application.
Just to say that even mysql has its problems when a user plays with serial PK.
--
Marc
On Fri, 2005-11-04 at 15:49, Marc Boucher wrote:
> At 11:49 04/11/2005 -0500, Alex Turner wrote:
> > I think he meant
> >
> > create sequence test_seq;
> > select setval('test_seq',(select max(primary_key_id) from my_table));
> >
> > not max value of a serial type.
>
> What I understand, and from what I know by using mysql, is that mysql
> auto-adjust the max value of a serial.
> Something like :
> - a table with 5000 elements with ids from 1-5000.
> - if you update the id in one of the rows and set it to 65000, mysql
> updates the serial current value.
> - even if you delete, or change the id back to its previous value, the
> current value will still be 65000.
> - a new inserted row will have the id 65001.
>
> Now assuming the id's maximum value is 65535, and you set one of the rows to
> this value, mysql will be unable to find a "nextval" greater than 65535. New
> inserts will fail.
> I don't know if it is still the case with recent versions of mysql, but that's
> what I discovered while testing a web application.
Actually, the behaviour is, I believe, dependent on which storage engine
you are using for that table. Reading the page on innodb, it seems that
on db startup a brand new starting point is determined by looking at the
current max in the autoinc field. I found this statement interesting:
QUOTE:
The behavior of the auto-increment mechanism is not defined if a user
assigns a negative value to the column or if the value becomes bigger
than the maximum integer that can be stored in the specified integer
type.
UNQUOTE:
While I can forgive the undefined behaviour for a negative number, the
undefined behaviour at rollover is a bit more bothersome. I'd prefer
it be defined as "we stop inserts until you rectify the situation" than
"undefined".