web2py / pydal returning id 1 in postgres

[Mark Graves]

Are there any caveats to know about running applications in shell mode (e.g. transactions / returning id)?

I'm running a middleware migration script to migrate someone off an old database to a new one.

When I run a db.table.validate_and_insert() it keeps returning me id 1.

This is yielding a duplicate key error understandably.

It's like pydal and postgres are coming out of sync.

Theres only one connection to the database, but the sequence is not being updated.

Any thoughts?

[Johann Spies]

Which database backend do you use and what does the table definition look like in that backend?

Regards

Johann

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[Anthony]

What does the code look like? Are you calling db.commit() at any point?

[Mark Graves]

I'm using postgres, migrating from latin-1 mysql.

Its a referenced table, and yes I definitely am calling db.commit()

I will post the entire code tonight.

Also, I have ~11k legacy documents to upload to S3 which I wanted to make available via a uniform method.

I was previously attempting to insert them via web2py, but I have encountered segmentation faults in postgres using this method, so I may just s3sync them and create a download link which I store in the database to make my life easier.

Anyone have any experience with that?

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/P3I8e-Elq9I/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Massimo Di Pierro]

very strange. Are you using psycopg2 or pg8000? I do not trust the latter.

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Mark Graves]

I tried both, unfortunately.

Working on posting the code right now

-Mark

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/P3I8e-Elq9I/unsubscribe.

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Mark Graves]

Code available @

https://github.com/gravesmedical/migration

Its just a series of scripts that gets run in the order they are in the scripts directory.

-Mark

[Johann Spies]

What is the output of \d+ <tablename>

in psql?

What does your model in web2py for that model look like?

Johann

[Massimo Di Pierro]

not sure what the problem is. Pydal gets the id of the last inserted record from the adapter which gets it from pgsql. It does not keep its own count. So it is pgsql that is returning the same id.

I noticed that you have:

try:
	result = db.citation.validate_and_insert(**_citation)
	print result
	except:
	print db._lastsql

In general db IO inside a try except is calling for trouble. If the exception is cause by the database you have to rollback and cannot commit.

[Anthony]

I'm not sure if this will help, but have you tried dropping (and re-creating) the tables rather than truncating them before doing the inserts?

Anthony

[Mark Graves]

Still have not figured this out.

Trying a different approach, but I suspect it was Massimo's hunch.

If I locally upload the files without and uploadfs, then I s3sync the files up to my s3bucket and set the uploadfs to that bucket after, will web2py find them automatically?

I know i could dig in and figure this out, im just looking for the easy answer =)

I'm trying to have all the file names at least have the same naming convention.

-Mark