ProgrammingError: there is no unique constraint matching given keys for referenced table "xuser"

[Carlos]

Hi,

I'm using latest trunk, with 'xuser' instead of auth_user (which is not the issue because I was getting no errors up until very recently).

Note that everything was working ok (no errors) up until 4 changesets ago, i.e. up until changeset # e33d3578be33 (fixed problem with load and string args).

If I update to that changeset (e33d3578be33), everything works ok again.

web2py™ Version 1.96.0 (2011-05-30 21:04:39)

Python Python 2.6.4: C:\Python26\python.exe

File "C:\web2py\gluon\dal.py", line 4212, in define_table

    polymodel=polymodel)

  File "C:\web2py\gluon\dal.py", line 641, in create_table

    fake_migrate=fake_migrate)

  File "C:\web2py\gluon\dal.py", line 729, in migrate_table

    self.execute(sub_query)

  File "C:\web2py\gluon\dal.py", line 1222, in execute

    return self.log_execute(*a, **b)

  File "C:\web2py\gluon\dal.py", line 1217, in log_execute

    ret = self.cursor.execute(*a,**b)

ProgrammingError: there is no unique constraint matching given keys for referenced table "xuser"

    def log_execute(self,*a,**b):

        self.db._lastsql = a[0]

        t0 = time.time()

        ret = self.cursor.execute(*a,**b)

        self.db._timings.append((a[0],time.time()-t0))

        return ret

Variables

a ('ALTER TABLE xuser ADD xupdated_by__tmp INTEGER REFERENCES xuser(first_name) ON DELETE SET NULL;',)

b {}

self <gluon.dal.PostgreSQLAdapter object at 0x03872450>

ret undefined

self.cursor <cursor object at 0x0390A990; closed: 0>

self.cursor.execute <built-in method execute of psycopg2._psycopg.cursor object at 0x0390A990>

Thanks,

   Carlos

[Massimo Di Pierro]

Can I see your model definition for table xauth?

[Massimo Di Pierro]

Can I also see your databases/...._xauth.table (unpickled)?

On May 30, 10:34 pm, Carlos <carlosgali...@gmail.com> wrote:

[Carlos]

Hi Massimo,

I believe I've found the problem, it happens with any table (not just auth_user).

If I define the 'id' field in any position other than first, and if there are references to the same table, then the error occurs.

E.g.

   db.define_table(

       'xtable',

       Field('xfield'),

       Field('id', 'id'),

       Field('xref', 'reference xtable'),

   )

With the above definition, I will get the following error:

   ProgrammingError: there is no unique constraint matching given keys for referenced table "xtable"

This seems to solve my problem, but now I'm facing other issues (only with the latest changesets in trunk), I'm still checking.

   Carlos

[pbreit]

It sounds like you know what you are doing but I'll point out that typically you don't define an 'id' field since Web2py does that already.

[Massimo Di Pierro]

You are correct. Thanks for reporting this problem.
This should now be fixed in trunk.

Massimo