BUG #7940: Auto increment issue

[k***@gmail.com]

The following bug has been logged on the website:

Bug reference: 7940
Logged by: krishna
Email address: ***@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)=(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,create_date,originator,encoding,status_report,flash_sms,src_port,dst_port,sent_date,ref_no,priority,status,errors,gateway_id)

[Josh Kupershmidt]

Post by k***@gmail.com
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]

Post by k***@gmail.com
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

[krishnakumar]

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

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 <

---------- Forwarded message ----------
Date: Thu, Mar 14, 2013 at 8:43 PM
Subject: Re: [BUGS] BUG #7940: Auto increment issue

Post by k***@gmail.com
Hi,
I'm using PostgreSQL 9.0.1 and driver PostgreSQL 9.0 JDBC4 and my

OS

Post by k***@gmail.com
is Ubuntu.I'm using serial type for auto-incrementing column id in my

table

Post by k***@gmail.com
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

Post by k***@gmail.com
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

Post by k***@gmail.com
value of id instead starts from begining (i.e., 1) and getting

duplicate key

Post by k***@gmail.com
value violates unique constraint "smsserver_out_pkey" Detail: Key

(id)=(1)

Post by k***@gmail.com
already exists till upto restored number of entries. ...what is the

solution

Post by k***@gmail.com
to over come this issue ...

[snip]

Post by k***@gmail.com
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