Обсуждение: duplicate key violates unique constraint. (repost)
Hi,
Sorry for the lack of info, but I wanted to get a post out there
while I start to figure out my problem since this is a production database.
We're starting to get a number of complaints about "duplicate key
violations" on a index for one of the toast tables. It started
happening after our server was brought down over the weekend, so I
suspect postgresql wasn't shutdown properly. Below is the description
of the index.
I'm doing a re-index on the user table that is associated with the toast
table, is this the right thing to do? Is there something else I should do?
# \d pg_toast.pg_toast_1916266943_index
Index "pg_toast.pg_toast_1916266943_index"
Column | Type
-----------+---------
chunk_id | oid
chunk_seq | integer
primary key, btree, for table "pg_toast.pg_toast_1916266943"
# select version();
version
------------------------------------------------------------------------
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2
Thanks
michael
Michael Guerin <guerin@rentec.com> writes:
> We're starting to get a number of complaints about "duplicate key
> violations" on a index for one of the toast tables. It started
> happening after our server was brought down over the weekend, so I
> suspect postgresql wasn't shutdown properly. Below is the description
> of the index.
> I'm doing a re-index on the user table that is associated with the toast
> table, is this the right thing to do? Is there something else I should do?
I don't think that will touch the toast table --- you should reindex the
toast table or index explicitly.
regards, tom lane
I'm still getting this duplicate key violation error on the toast table. I've re-indexed both the main table and the toast table, would clustering the table, or recreating the table with a select into fix this problem? Also, I believe this index is created by the database so I shouldn't be able to insert data that causes this error, shouldn't the database complain a little louder about this error? -mike Tom Lane wrote: >Michael Guerin <guerin@rentec.com> writes: > > >>We're starting to get a number of complaints about "duplicate key >>violations" on a index for one of the toast tables. It started >>happening after our server was brought down over the weekend, so I >>suspect postgresql wasn't shutdown properly. Below is the description >>of the index. >> >> > > > >>I'm doing a re-index on the user table that is associated with the toast >>table, is this the right thing to do? Is there something else I should do? >> >> > >I don't think that will touch the toast table --- you should reindex the >toast table or index explicitly. > > regards, tom lane > >
before going to well again, i just want to say a big "thank you" to all the folks who generously give their time and help guys like me learn more about postgresql. i've learned a lot and saved a ton on aspirin! i need to insert multiple serial numbers at a time (eg, 100). my current idea is to allow the user to enter a beginning serial number, an ending serial number and then use php to iterate through the numbers and insert them (i'm thinking multiple inserts (get value -> insert, get 2nd value -> insert, get 3rd value, insert, etc...) unless someone can give me an idea how to collect the data then do one big insert). is this the best way to proceed or does pgsql offer some kind of functionality that assists in this type of process. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Fri, Jul 22, 2005 at 01:49:25PM -0700, operationsengineer1@yahoo.com wrote: > > i need to insert multiple serial numbers at a time > (eg, 100). my current idea is to allow the user to > enter a beginning serial number, an ending serial > number and then use php to iterate through the numbers > and insert them (i'm thinking multiple inserts (get > value -> insert, get 2nd value -> insert, get 3rd > value, insert, etc...) unless someone can give me an > idea how to collect the data then do one big insert). In 8.0 you can use INSERT ... SELECT and generate_series(): http://www.postgresql.org/docs/8.0/static/sql-insert.html http://www.postgresql.org/docs/8.0/static/functions-srf.html Example: INSERT INTO foo (serialnum) SELECT * FROM generate_series(1, 100); Although older versions of PostgreSQL don't have generate_series(), it's trivial to write with PL/pgSQL or other languages. Read up on set-returning functions or search the archives for examples. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
--- Michael Fuhr <mike@fuhr.org> wrote: > On Fri, Jul 22, 2005 at 01:49:25PM -0700, > operationsengineer1@yahoo.com wrote: > > > > i need to insert multiple serial numbers at a time > > (eg, 100). my current idea is to allow the user > to > > enter a beginning serial number, an ending serial > > number and then use php to iterate through the > numbers > > and insert them (i'm thinking multiple inserts > (get > > value -> insert, get 2nd value -> insert, get 3rd > > value, insert, etc...) unless someone can give me > an > > idea how to collect the data then do one big > insert). > > In 8.0 you can use INSERT ... SELECT and > generate_series(): > > http://www.postgresql.org/docs/8.0/static/sql-insert.html > http://www.postgresql.org/docs/8.0/static/functions-srf.html > > Example: > > INSERT INTO foo (serialnum) SELECT * FROM > generate_series(1, 100); > > Although older versions of PostgreSQL don't have > generate_series(), > it's trivial to write with PL/pgSQL or other > languages. Read up > on set-returning functions or search the archives > for examples. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > thanks. this is really nice! we should be moving our production db onsite so i will be able to have pgsql 8.0 installed. i will probably have to rethink my webhost if i ever decide to put anything serious on the net. tom is right, they (2wdh.com or cpanel, it doesn't really matter) aren't taking pgsql users seriously - and you just showed me one way they are hurting me (not to mention security holes in older versions)! __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com