Error while inserting in table with custom id

[at]

Hello,

I am getting getting this:

<class 'psycopg2.OperationalError'>(lastval is not yet defined in this session )

while running:

if db(db.user_role).isempty():
    db.user_role.insert(id=1, title='Application Administrator')
    db.user_role.insert(id=2, title='Account Administrator')
    db.user_role.insert(id=3, title='Account Member')

whereas my table defination is simply as follows:

db.define_table('user_role',
    Field('title', type='string', length=50, label=T('User Role Title'))
    )

and in postgres:

CREATE TABLE user_role
(
  id serial NOT NULL,
  title character varying(50),
  CONSTRAINT user_role_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);

Any help please ...

Thanks & Regards
AT

[Niphlod]

was the table created by web2py  ?

[at]

Yes

[Simon Ashley]

Believe it will be failing with the id=1 etc. Interfering with the primary key/ auto increment. Try it without.

[at]

Yes you are right. Without id=1 it's working.
But if I insert data using postgres/psql with id=1, it doesn't give any error as well.

Regards,
AT

[at]

So it's incorrect or impermissible to add records with custom ids?

Thanks

On Thursday, 26 June 2014 16:33:02 UTC+5, at wrote:

Yes you are right. Without id=1 it's working.
But if I insert data using postgres/psql with id=1, it doesn't give any error as well.

Regards,
AT

On Thursday, 26 June 2014 15:39:20 UTC+5, Simon Ashley wrote:

[Greg Sier]

Seems to be a postgresql specific issue. Fails in the DAL at when executing 'select lastval()'. SQlite and MySQL are fine. The command also fails at the pg command line level. This was changed back in December but uncertain if the issue existed prior to this  https://groups.google.com/forum/?fromgroups#!searchin/web2py/select$20lastval(). Only came across it 3 hours before you when attempting bulk_insert from a select.as_list() dict to move between databases. Cant see a quick or easy work around, and the issue probably needs a PR.

[Michele Comitini]

id is implicit and defined as serial, implying that there is a sequence that is used to fill proper values.

id is intended to be unique and primary key: an integer generator that guarantee uniqueness or at least has a very low probability of conflict is the standard way to go.  This is very important in a multiuser environment such as web2py.

Of course you can do inserts with explicit values for the id column, but you should resort to the executesql() method of DAL. And remember to update the related sequence properly to avoid conflicts.

mic

2014-06-26 23:36 GMT+02:00 Greg Sier <greg...@gmail.com>:

Seems to be a postgresql specific issue. Fails in the DAL at when executing 'select lastval()'. SQlite and MySQL are fine. The command also fails at the pg command line level. This was changed back in December but uncertain if the issue existed prior to this  https://groups.google.com/forum/?fromgroups#!searchin/web2py/select$20lastval(). Only came across it 3 hours before you when attempting bulk_insert from a select.as_list() dict to move between databases. Cant see a quick or easy work around, and the issue probably needs a PR.

--
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/z-docU0cUN4/unsubscribe.
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.

--
Greg Sier

Sier Associates AU

+61 7 55736422

--
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.

[Simon Ashley]

I tend to agree with this. We ended up dropping the id field from bulk_insert routines and if we need integrity (for references) in the target databases, we would/ should base it on other fields.