Thread: [SQLObject] Switched from mysql to postgres...now sqlobject reusing ids
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Sam's L. <sam...@gm...> - 2008-05-19 07:05:48
|
Hi... I just posted this on the postgres newsgroup...but it seems here might be a better place to get an answer. I doubt there's much overlap between the two groups, but if you see this twice I apologize. I've converted my database from mysql to postgresql. Now it seems to be working...more or less. But sqlobject can't figure out the next unused id when creating a record. Instead it seems to be starting at 1. The program then errors out with a "psycopg2.IntegrityError: duplicate key value violates unique constraint" error. When I rerun it again it tries 2 as the id...so it is incrementing them. How do I make it figure out the right next unused id? (In this case that is 426). Thanks! |
From: Oleg B. <ph...@ph...> - 2008-05-19 07:41:17
|
On Mon, May 19, 2008 at 12:05:45AM -0700, Sam's Lists wrote: > Now it seems to be working...more or less. But sqlobject can't figure > out the next unused id when creating a record. Instead it seems to be > starting at 1. The program then errors out with a > "psycopg2.IntegrityError: duplicate key value violates unique > constraint" error. Not a problem of SQLObject. PostgreSQL uses sequences for serial (autoincremented) fields. For a new empty table sequences are automatically set to start from 1. You have to setup the sequences for all serial fields if you want to start from a specific value. Do SELECT setval('sequence_name', value); to set the value for the sequence. Read http://www.postgresql.org/docs/8.3/static/functions-sequence.html for further reference. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sam's L. <sam...@gm...> - 2008-05-19 08:20:32
|
Thanks for the quick response. I actually had read that while googling for a solution...but I didn't understand it. Now I do...so thank you! Is there some way to get postgres to figure out what to set the next value for each table? I have 35 tables so doing it by hand doesn't seem like fun. I guess I could write something in Python, but that seems like a pain too. Thanks again On Mon, May 19, 2008 at 12:41 AM, Oleg Broytmann <ph...@ph...> wrote: > On Mon, May 19, 2008 at 12:05:45AM -0700, Sam's Lists wrote: > > Now it seems to be working...more or less. But sqlobject can't figure > > out the next unused id when creating a record. Instead it seems to be > > starting at 1. The program then errors out with a > > "psycopg2.IntegrityError: duplicate key value violates unique > > constraint" error. > > Not a problem of SQLObject. PostgreSQL uses sequences for serial > (autoincremented) fields. For a new empty table sequences are automatically > set to start from 1. You have to setup the sequences for all serial fields > if you want to start from a specific value. Do > > SELECT setval('sequence_name', value); > > to set the value for the sequence. Read > > http://www.postgresql.org/docs/8.3/static/functions-sequence.html > > for further reference. > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Microsoft > Defy all challenges. Microsoft(R) Visual Studio 2008. > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: Oleg B. <ph...@ph...> - 2008-05-19 08:37:53
|
On Mon, May 19, 2008 at 01:20:30AM -0700, Sam's Lists wrote: > Is there some way to get postgres to figure out what to set the next value > for each table? I don't know any magic. Sequences are supposed to be automatically handled in case the user doesn't fiddle with serial fields. When one does DB dump (using pg_dump utility) Postgres inserts necessary commands to reset sequences on load. But if one loads data (whether with INSERT command or with COPY) and the data touches serial fields, [s]he must handle sequences by hand. > I have 35 tables so doing it by hand doesn't seem like fun. > I guess I could write something in Python, but that seems like a pain too. [pseudocode] for table in tables: sequnce_name = "%s_id_seq" % table query = "SELECT MAX(id) FROM %s;" % table id = exec(query) query = "SELECT setval(%s, %d);" % (sequnce_name, id) exec(query) It is enough to use id, not id+1 because after setval() next time you execute an INSERT the sequence will be automatically increased before INSERT. The other options is query = "SELECT setval(%s, %d, 0);" % (sequnce_name, id+1) In this case on next INSERT the sequence will only be increased *after* the INSERT, so we set id+1 in advance. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2008-05-19 08:40:13
|
On Mon, May 19, 2008 at 12:37:53PM +0400, Oleg Broytmann wrote: > query = "SELECT setval(%s, %d, 0);" % (sequnce_name, id+1) Oops, a bug. query = "SELECT setval(%s, %d, false);" % (sequnce_name, id+1) ^^^^^ Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Sam's L. <sam...@gm...> - 2008-05-20 02:30:27
|
Thanks! I took your advise and some advice for turbogears list....and came up with this program, just in case anyone has to do the same thing: #!/usr/bin/env python import sqlobject import psycopg2 connection = sqlobject.connectionForURI("postgres://your_connection_scheme_here") sqlobject.sqlhub.processConnection = connection def qa(query): print "\tRunnng query: ", query return connection.queryAll(query) print tables = qa("select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE'") for table in tables: table = table[2] print "Processing table: ", table try: maxid = qa("SELECT max(id) FROM %s;" % table) except psycopg2.ProgrammingError: print "skipping" try: maxid = maxid[0] except TypeError: continue try: maxid = maxid[0] except TypeError: continue if not maxid: continue print "maxid is: ", maxid sequence_name = "%s_id_seq" % table qa("SELECT setval('%s', %i);" % (sequence_name, maxid)) print On Mon, May 19, 2008 at 1:39 AM, Oleg Broytmann <ph...@ph...> wrote: > On Mon, May 19, 2008 at 12:37:53PM +0400, Oleg Broytmann wrote: > > query = "SELECT setval(%s, %d, 0);" % (sequnce_name, id+1) > > Oops, a bug. > query = "SELECT setval(%s, %d, false);" % (sequnce_name, id+1) > ^^^^^ > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Microsoft > Defy all challenges. Microsoft(R) Visual Studio 2008. > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |