Обсуждение: BUG #7940: Auto increment issue
The following bug has been logged on the website:
Bug reference: 7940
Logged by: krishna
Email address: krishnakumar.eeee@gmail.com
PostgreSQL version: 9.0.1
Operating system: Ubuntu
Description: =
Hi,
I'm using PostgreSQL 9.0.1 and driver PostgreSQL 9.0 JDBC4 and my OS
is Ubuntu.I'm using serial type for auto-incrementing column id in my table
everything works fine .when i stop my application and restart the
application its fine but when i backup DB and then restore it back manually
i'm inserting id column by myself during restoring...after restore If i
start the application, the id column doesn't start incrementing from maximum
value of id instead starts from begining (i.e., 1) and getting duplicate key
value violates unique constraint "smsserver_out_pkey" Detail: Key (id)=3D(1)
already exists till upto restored number of entries. ...what is the solution
to over come this issue ...
MY SCHEMA is =
create table smsserver_out
(
id serial NOT NULL,
type varchar(1) NOT NULL,
recipient varchar(16) NOT NULL,
text text NOT NULL,
wap_url text NOT NULL,
wap_expiry_date varchar(50),
wap_signal varchar(1),
create_date varchar(50) NOT NULL,
originator varchar(16) NOT NULL,
encoding varchar(1) NOT NULL,
status_report integer NOT NULL,
flash_sms integer NOT NULL,
src_port integer NOT NULL,
dst_port integer NOT NULL,
sent_date varchar(50),
ref_no varchar(64),
priority integer NOT NULL,
status varchar(1) NOT NULL,
errors integer NOT NULL,
gateway_id varchar(64) NOT NULL,
PRIMARY KEY (id )
)
My Insert Query for restoring the database =
INSERT INTO
smsserver_out(id,type,recipient,text,wap_url,wap_expiry_date,wap_signal,cre=
ate_date,originator,encoding,status_report,flash_sms,src_port,dst_port,sent=
_date,ref_no,priority,status,errors,gateway_id)
On Wed, Mar 13, 2013 at 4:01 AM, <krishnakumar.eeee@gmail.com> wrote: > Hi, > I'm using PostgreSQL 9.0.1 and driver PostgreSQL 9.0 JDBC4 and my OS > is Ubuntu.I'm using serial type for auto-incrementing column id in my table > everything works fine .when i stop my application and restart the > application its fine but when i backup DB and then restore it back manually > i'm inserting id column by myself during restoring...after restore If i > start the application, the id column doesn't start incrementing from maximum > value of id instead starts from begining (i.e., 1) and getting duplicate key > value violates unique constraint "smsserver_out_pkey" Detail: Key (id)=(1) > already exists till upto restored number of entries. ...what is the solution > to over come this issue ... [snip] > My Insert Query for restoring the database > INSERT INTO > smsserver_out(id,type,recipient,text,wap_url,wap_expiry_date,wap_signal,create_date,originator,encoding,status_report,flash_sms,src_port,dst_port,sent_date,ref_no,priority,status,errors,gateway_id) Well, you didn't specify how exactly you are backing up and restoring the table, although from the INSERT snippet you showed it looks like you're not using pg_dump. If you were using pg_dump, it would handle resetting the sequence behind smsserver_out.id to its value at the time of the dump. If you are using some other method to backup and reload the table, you will have to include a call to setval() to set a sane value for the sequence. For the record, reimplementing pg_dump is usually a bad idea. Josh
Hi Josh,
Thanks for your reply .....We are backing up in a file and restoring
it into DB .We have a product which supports multiple databases where data
migration between two different databases say POSTGRES and MySQL can be
done. setval might be the solution for my problem but it will be better if
u find max value and auto increment without using specific methods for this
problem.
Thanks,
Krishna
On Mon, Mar 18, 2013 at 11:09 AM, krishnakumar
<krishnakumar.eeee@gmail.com>wrote:
> Hi Josh,
>
> Thanks for your reply .....We are backing up in a file and restoring
> it into DB .We have a product which supports multiple databases where data
> migration between two different databases say POSTGRES and MySQL can be
> done. setval might be the solution for my problem but it will be better if
> u find max value and auto increment without using specific methods for this
> problem.
>
> Thanks,
> Krishna
>
> On Mon, Mar 18, 2013 at 10:37 AM, krishnakumar <
> krishnakumar.eeee@gmail.com> wrote:
>
>>
>>
>> ---------- Forwarded message ----------
>> From: Josh Kupershmidt <schmiddy@gmail.com>
>> Date: Thu, Mar 14, 2013 at 8:43 PM
>> Subject: Re: [BUGS] BUG #7940: Auto increment issue
>> To: krishnakumar.eeee@gmail.com
>> Cc: pgsql-bugs@postgresql.org
>>
>>
>> On Wed, Mar 13, 2013 at 4:01 AM, <krishnakumar.eeee@gmail.com> wrote:
>>
>> > Hi,
>> > I'm using PostgreSQL 9.0.1 and driver PostgreSQL 9.0 JDBC4 and my
>> OS
>> > is Ubuntu.I'm using serial type for auto-incrementing column id in my
>> table
>> > everything works fine .when i stop my application and restart the
>> > application its fine but when i backup DB and then restore it back
>> manually
>> > i'm inserting id column by myself during restoring...after restore If i
>> > start the application, the id column doesn't start incrementing from
>> maximum
>> > value of id instead starts from begining (i.e., 1) and getting
>> duplicate key
>> > value violates unique constraint "smsserver_out_pkey" Detail: Key
>> (id)=(1)
>> > already exists till upto restored number of entries. ...what is the
>> solution
>> > to over come this issue ...
>>
>> [snip]
>>
>> > My Insert Query for restoring the database
>> > INSERT INTO
>> >
>>
>>
smsserver_out(id,type,recipient,text,wap_url,wap_expiry_date,wap_signal,create_date,originator,encoding,status_report,flash_sms,src_port,dst_port,sent_date,ref_no,priority,status,errors,gateway_id)
>>
>> Well, you didn't specify how exactly you are backing up and restoring
>> the table, although from the INSERT snippet you showed it looks like
>> you're not using pg_dump. If you were using pg_dump, it would handle
>> resetting the sequence behind smsserver_out.id to its value at the
>> time of the dump. If you are using some other method to backup and
>> reload the table, you will have to include a call to setval() to set a
>> sane value for the sequence. For the record, reimplementing pg_dump is
>> usually a bad idea.
>>
>> Josh
>>
>>
>