postgresql error ... <class 'psycopg2.ProgrammingError'>(relation "auth_user" already exists)

[Carlos]

Hi,

I'm getting the following error in my local installation with
postgresql when using auth.define_tables(migrate=True):

<class 'psycopg2.ProgrammingError'>(relation "auth_user" already
exists)

web2py™ Version 1.89.1 (2010-11-12 15:14:36)
Python Python 2.6.4: C:\Python26\python.exe

Traceback (most recent call last):
File "C:\web2py\gluon\restricted.py", line 188, in restricted
exec ccode in environment
File "C:/web2py/applications/test/models/model.py", line 38, in
<module>
auth.define_tables(migrate=True)
File "C:\web2py\gluon\tools.py", line 1163, in define_tables
format='%(first_name)s %(last_name)s (%(id)s)')
File "C:\web2py\gluon\sql.py", line 1371, in define_table
t._create(migrate=migrate, fake_migrate=fake_migrate)
File "C:\web2py\gluon\sql.py", line 1823, in _create
self._db._execute(query)
File "C:\web2py\gluon\sql.py", line 1026, in <lambda>
self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
ProgrammingError: relation "auth_user" already exists

Any ideas what could be wrong?.

Thanks,

Carlos

[Carlos]

Hi all,

Can anybody please help with this?.

Thanks!,

Carlos

[mdipierro]

Corrupted database/*.table

did you delete them?

On Nov 12, 3:45 pm, Carlos <carlosgali...@gmail.com> wrote:

[Carlos]

Hi Massimo,

I did not delete any *.table files located at:

.\web2py\applications\(app)\databases\

Should I try deleting them?.

How can this get corrupted?.

What else should I try?.

Thanks,

Carlos

[Carlos]

Hi,

Maybe the problem is that, for the same app, I started with sqlite and
then switched to progresql?.

Anyways, I just dropped the database in progresql and deleted all
files in /databases/, and it seems to be working ok now (so far).

Can you please confirm the above (switching from sqlite to progresql)
caused my problems?.

I want to know, in order to confirm I will not have these problems in
a production environment.

Thanks again,

Carlos

[mdipierro]

On Nov 13, 12:23 pm, Carlos <carlosgali...@gmail.com> wrote:
> Hi,
>

> Maybe the problem is that, for the same app, I started with sqlite and
> then switched to progresql?.
>
> Anyways, I just dropped the database in progresql and deleted all
> files in /databases/, and it seems to be working ok now (so far).
>
> Can you please confirm the above (switching from sqlite to progresql)
> caused my problems?.

No. This should not be a problem unless you deleted the sqlite
database and the .table AFTER the postgresql db was created.

[Carlos]

Hi Massimo,

I did not delete anything when this problem appeared, I just switched
from sqlite to postresql in the DAL.

Should I worry about this problem appearing again (in my local machine
or in a production environment)?.

Should I test anything else?.

Thanks,

Carlos

[Johann Spies]

On 13 November 2010 20:44, Carlos <carlos...@gmail.com> wrote:

Hi Massimo,

I did not delete anything when this problem appeared, I just switched
from sqlite to postresql in the DAL.

Should I worry about this problem appearing again (in my local machine
or in a production environment)?.

I have seen this several times when I change definitions of tables using Postgresql as backend.  What I normally do in such sitations is  to export the database table to a csv-file through Web2py or if that is not possible using either the commandline or PGAdminIII, drop the table and remove the table's entry in web2py/applications/myapp/databases.

Web2py will then recreate the table and you can import the csv-file.  If the latter was created with outside web2py, you will have to edit the column headers before importing it.

I am not sure whether this is good practice but this has saved me some frustrations.

Regards
Johann

--
 May grace and peace be yours in abundance through the full knowledge of God and of Jesus our Lord!  His divine power has given us everything we need for life and godliness through the full knowledge of the one who called us by his own glory and excellence.
                                                    2 Pet. 1:2b,3a

[Carlos]

Thanks Johann for confirming this problem.

Massimo, is there another easier/faster/better way to solve this in a
production environment?.

Thanks,

Carlos


On Nov 15, 2:57 am, Johann Spies <johann.sp...@gmail.com> wrote:

[mdipierro]

> I have seen this several times when I change definitions of tables using
> Postgresql as backend.

this very much confuses web2py which keeps its own metadata about what
is in the db.

On Nov 15, 2:57 am, Johann Spies <johann.sp...@gmail.com> wrote:

[Carlos]

From what Johann said, I understood the table definitions were changed
in web2py (using postgresql as the backend), not in postgresql
directly ... or did I misunderstand it?.

[Johann Spies]

On 15 November 2010 18:17, Carlos <carlos...@gmail.com> wrote:

From what Johann said, I understood the table definitions were changed
in web2py (using postgresql as the backend), not in postgresql
directly ... or did I misunderstand it?.

Yes, that is what I meant.

Regards
Johann
--

[Carlos]

So, based on Johann's confirmation, then this is a problem, isn't it?.

Regards,

Carlos


On Nov 16, 12:05 am, Johann Spies <johann.sp...@gmail.com> wrote:

[mdipierro]

The error you are getting:

ProgrammingError: relation "auth_user" already exists

This is not a migration problem. The DB is not complaining about an
ALTER TABLE but about a CREATE TABLE.

This means your database/*_auth_user.table is either missing or
corrupted.
It would be useful to have steps to reproduce the problem.

Check in your database folder. You should have two of those files (one
for sqlite and one for postgresql). Can you send them to me? I am
assuming you have a custom auth_user. Can you send me that too?

Massimo

[Carlos]

Hi Massimo,

I'll send you the requested files if/when I get the error again (I
deleted the previous ones).

Btw it was not a custom auth_user.

Johann, please send the files when you get this error too.

Thanks,

Carlos

[Russell]

Hi,

I've had this problem come up quite a few times. It appears to be a
migration issue and is usually fixed by this...

auth.define_tables(migrate=False)

Thanks
Russell

[Johann Spies]

On 16 November 2010 21:48, Carlos <carlos...@gmail.com> wrote:

Johann, please send the files when you get this error too.

It happened just now again.  I  have changed the type of a field from 'date' to 'integer' (I want None or year) and the following typical error occured:

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19.

Traceback (most recent call last

): File "/home/js/web2py/gluon/restricted.py", line 188, in restricted exec ccode in environment File "/home/js/web2py/applications/kb/models/kb5.py", line 229, in <module> signature) File "/home/js/web2py/gluon/sql.py", line 1378, in define_table t._create(migrate=migrate, fake_migrate=fake_migrate) File "/home/js/web2py/gluon/sql.py", line 1874, in _create fake_migrate=fake_migrate) File "/home/js/web2py/gluon/sql.py", line 1947, in _migrate self._db._execute(sub_query) File "/home/js/web2py/gluon/sql.py", line 1033, in <lambda>

self._execute = lambda *a, **b: self._cursor.execute(*a, **b)

ProgrammingError: column "datum__tmp" is of type integer but expression is of type date LINE 1: UPDATE outeur_instansie SET datum__tmp=datum; ^ HINT: You will need to rewrite or cast the expression.

Error snapshot Detailed traceback description

<class 'psycopg2.ProgrammingError'>(column "datum__tmp" is of type integer but expression is of type date LINE 1: UPDATE outeur_instansie SET datum__tmp=datum; ^ HINT: You will need to rewrite or cast the expression. )

Regards
Johann
 

[mdipierro]

This is a reasonable error now.

when you change a field type (but not the name) web2py tries to
convert the data. In this case postgres fails because it does not know
how to convert dates to integers.

You have to do it in multiple steps:

- remove the field from define_table
- run appadmin once (field will be dropped)
- add the field again with same name and different type
- run appadmin once (new field will be created)

web2py will not try to attepmt to post the data in the column and it
will be lost, but no error.



On Nov 18, 1:20 am, Johann Spies <johann.sp...@gmail.com> wrote:

> <http://localhost:8000/admin/default/edit/kb/models/kb5.py>, line 229,

[Johann Spies]

On 18 November 2010 14:03, mdipierro <mdip...@cs.depaul.edu> wrote:

This is a reasonable error now.

when you change a field type (but not the name) web2py tries to
convert the data. In this case postgres fails because it does not know
how to convert dates to integers.

You have to do it in multiple steps:

- remove the field from define_table
- run appadmin once (field will be dropped)
- add the field again with same name and different type
- run appadmin once (new field will be created)

web2py will not try to attepmt to post the data in the column and it
will be lost, but no error.

Thanks. 

Regards
Johann

[Tom Atkins]

I am getting the same error as reported by Carlos and cannot fix it.

I have just set up a new server (Ubuntu 10.04 - postgres 8.4, Python 2.6.5) and have web2py trunk up and running fine.  I have an unmodified 'welcome' app which runs perfectly with sqlite.

I then delete all the files in databases, cache and  sessions.  Next I create a postgresql database and grant permissions to the appropriate user.  I have double checked this and the user can create tables on the database.

Next change the connection details in the welcome app db.py to:

db = DAL('postgres://myuser:mypasswordlocalhost/mydb')

Then visit the URL and I get the ticket below.

Any help gratefully appreciated.  (I had this running fine on a previous server)

------------------------

Traceback (most recent call last):

  File "/home/www-data/web2py/gluon/restricted.py", line 188, in restricted

    exec ccode in environment

  File "/home/www-data/web2py/applications/welcome/models/db.py", line 32, in <module>

    auth.define_tables()                           # creates all needed tables

  File "/home/www-data/web2py/gluon/tools.py", line 1177, in define_tables

    format='%(first_name)s %(last_name)s (%(id)s)')

  File "/home/www-data/web2py/gluon/dal.py", line 3489, in define_table

    polymodel=polymodel)

  File "/home/www-data/web2py/gluon/dal.py", line 531, in create_table

    self.create_sequence_and_triggers(query,table)

  File "/home/www-data/web2py/gluon/dal.py", line 1512, in create_sequence_and_triggers

    self.execute('CREATE SEQUENCE %s;' % sequence_name)

  File "/home/www-data/web2py/gluon/dal.py", line 1078, in execute

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

  File "/home/www-data/web2py/gluon/dal.py", line 1075, in log_execute

    return self.cursor.execute(*a,**b)

ProgrammingError: relation "auth_user_id_seq" already exists

ERROR SNAPSHOT  

<class 'psycopg2.ProgrammingError'>(relation "auth_user_id_seq" already exists

Frames

File /home/www-data/web2py/gluon/restricted.py in restricted at line 188 code arguments variables

File /home/www-data/web2py/applications/welcome/models/db.py in <module> at line 32 code arguments variables

File /home/www-data/web2py/gluon/tools.py in define_tables at line 1177 code arguments variables

File /home/www-data/web2py/gluon/dal.py in define_table at line 3489 code arguments variables

File /home/www-data/web2py/gluon/dal.py in create_table at line 531 code arguments variables

File /home/www-data/web2py/gluon/dal.py in create_sequence_and_triggers at line 1512 code arguments variables

File /home/www-data/web2py/gluon/dal.py in execute at line 1078 code arguments variables

File /home/www-data/web2py/gluon/dal.py in log_execute at line 1075 code arguments variables

Function argument list

(self=<gluon.dal.PostgreSQLAdapter object>, *a=('CREATE SEQUENCE auth_user_id_Seq;',), **b={})

Code listing

1070.
1071.
1072.
1073.
1074.
1075.
1076.
1077.
1078.
1079.

def create_sequence_and_triggers(self, query, table, **args):
self.execute(query)

def log_execute(self,*a,**b):
self.db._lastsql = a[0]
return self.cursor.execute(*a,**b)

def execute(self,*a,**b):
return self.log_execute(*a, **b)

Variables

a('CREATE SEQUENCE auth_user_id_Seq;',)
self.cursor<cursor object at 0xba1152cc; closed: 0>
self<gluon.dal.PostgreSQLAdapter object>
b{}
self.cursor.execute<built-in method execute of psycopg2._psycopg.cursor object>

[Tom Atkins]

(PS I have tried auth.define_tables(migrate='False') and it doesn't help)

[Massimo Di Pierro]

You are using web2py trunk right? Did you set a sequence_name
manually?

[Tom Atkins]

On 4 February 2011 14:33, Massimo Di Pierro <massimo....@gmail.com> wrote:

You are using web2py trunk right?

Yes - just a clean version of web2py pulled by Hg from Google code. 

 

Did you set a sequence_name
manually?

No (not even sure what sequence_name is!) -  I tried another app of my own and get the same result. 

[Massimo Di Pierro]

This is a trunk bug. The problem does should be there in stable. I
will fix this tonight.

On Feb 4, 12:50 pm, Tom Atkins <minkto...@gmail.com> wrote:

[Tom Atkins]

Thanks for looking into this Massimo.  I just pulled the most recent version and still get the same error as originally described.  I added a comment here:

http://code.google.com/p/web2py/issues/detail?id=173&can=1&sort=-id

Let me know if you need any more info.  I definitely have the latest version.

[Ovidio Marinho]

here I also have this same problem. I also still have problems with the ID in version 191.6.

Ouvir

Ler foneticamente

Dicionário - Ver dicionário detalhado

Traduza qualquer site

• Yomuiri Online-Japão
• Telegraph.co.uk-Reino Unido
• BBC News-Reino Unido
• News.de-Alemanha
• Guardian.co.uk-Reino Unido
• Sueddeutsche.de-Alemanha
• The White House-Estados Unidos
• Xinhua Net-China
• Elle-França
• Zamalek Fans-árabe
• Machu Picchu-espanhol
• Bild.de-Alemanha

Fazer mais com o Google Tradutor

• Pesquise as melhores receitas de sushi em japonês! Descubra o poder da Pesquisa traduzida do Google.
• Estabeleça sua empresa globalmente. Anuncie em vários idiomas usando o Google Global Market Finder.
• Traduza agora. Traduza textos direto da sua página inicial do iGoogle.
• Mantenha contato com seus amigos virtuais de Paris. Ative a tradução automática de e-mails e bate-papos no Gmail.

2011/2/4 Massimo Di Pierro <massimo....@gmail.com>

--
        Ovidio Marinho Falcao Neto
             ovid...@gmail.com
         Tecnologia da Informaçao
         Casa Civil do Governador
         83 3214 7885 - 88269088
                  Paraiba

[Tom Atkins]

Oops - sorry, ignore my last message. I had an old version of dal.pyc... removed that and restarted web2py and everything is working great.  Many thanks.

[Massimo Di Pierro]

The least comment in the issue thread says it is fixed after deleting
the pyc. Is that not correct?

On Feb 6, 7:26 am, Tom Atkins <minkto...@gmail.com> wrote:
> Thanks for looking into this Massimo.  I just pulled the most recent version
> and still get the same error as originally described.  I added a comment
> here:
>
> http://code.google.com/p/web2py/issues/detail?id=173&can=1&sort=-id
>
> <http://code.google.com/p/web2py/issues/detail?id=173&can=1&sort=-id>Let me
> know if you need any more info.  I definitely have the latest version.
>

[Tom Atkins]

On 6 February 2011 15:51, Massimo Di Pierro <massimo....@gmail.com> wrote:

The least comment in the issue thread says it is fixed after deleting
the pyc. Is that not correct?

Yes - fixed after dal.pyc was recreated with new version. 

[Ovidio Marinho]

I have the same problem, already found the solution?

TRACEBACK

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19.

20.

Traceback (most recent call last

): File "c:\web2py\gluon\restricted.py", line 188, in restricted exec ccode in environment File "c:/web2py/applications/demanda/models/db.py", line 39, in <module> auth.define_tables() # creates all needed tables File "c:\web2py\gluon\tools.py", line 1177, in define_tables format='%(first_name)s %(last_name)s (%(id)s)') File "c:\web2py\gluon\dal.py", line 3490, in define_table polymodel=polymodel) File "c:\web2py\gluon\dal.py", line 531, in create_table self.create_sequence_and_triggers(query,table) File "c:\web2py\gluon\dal.py", line 1513, in create_sequence_and_triggers

self.execute('CREATE SEQUENCE %s;' % sequence_name

) File "c:\web2py\gluon\dal.py", line 1079, in execute return self.log_execute(*a, **b) File "c:\web2py\gluon\dal.py", line 1076, in log_execute return self.cursor.execute(*a,**b) ProgrammingError: ERRO: relação "auth_user_id_seq" já existe

2011/2/6 Massimo Di Pierro <massimo....@gmail.com>

[Ovidio Marinho]

Here if I create a new db postgres does not create the tables,already in the database sqlite, normally creates.

2011/2/4 Massimo Di Pierro <massimo....@gmail.com>

You are using web2py trunk right? Did you set a sequence_name

[Massimo Di Pierro]

this problem has been already fixed in trunk some time ago. Check the
nightly build from the download page.

Massimo

On Feb 13, 8:43 am, Ovidio Marinho <ovidio...@gmail.com> wrote:
> Here if I create a new db postgres does not create the tables,already in the
> database sqlite, normally creates.
>

> 2011/2/4 Massimo Di Pierro <massimo.dipie...@gmail.com>

>              ovidio...@gmail.com

[Carlos]

Hi,

I just upgraded to the latest web2py version (1.92.1 source) in my development local win7 environment with postgresql and migrate=True, and my original problem occurred once again.

Traceback (most recent call last):

  File "C:\web2py\gluon\restricted.py", line 188, in restricted

    exec ccode in environment

  File "C:/web2py/applications/nube/models/model_06_tables_pre.py", line 87, in <module>

    dict(name='TEST'),

  File "C:/web2py/applications/nube/models/model_04_nube.py", line 327, in table

    self._table(xtable)

  File "C:/web2py/applications/nube/models/model_04_nube.py", line 289, in _table

    format=xtable.format)

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

    polymodel=polymodel)

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

    self.create_sequence_and_triggers(query,table)

  File "C:\web2py\gluon\dal.py", line 1528, in create_sequence_and_triggers

    self.execute(query)

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

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

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

    return self.cursor.execute(*a,**b)

ProgrammingError: relation "xuser" already exists

<class 'psycopg2.ProgrammingError'>(relation "xuser" already exists)

(note that I'm using 'xuser' instead of 'auth_user')

My upgrade process consisted on the following simple steps:

  1. rename the previous web2py folder.

  2. install the new web2py version (hence the database folder is now empty).

  3. copy all my files (models, views, controllers, static) from previous version to new version.

And as soon as I try to open my app, I get the above error.

The only way I could solve it was to recreate the postgresql database.

I don't yet have a production system, but this problem scares me a lot when in a production system.

Should it have worked based on the upgrade steps I mentioned above?.

How can this issue be solved?.

Thanks again,

   Carlos

[Massimo Di Pierro]

are you copying the content of databases/ folder?

[Carlos]

Massimo,

No, I did not copy the files from 'databases' folder ... should I have done so?.

   Carlos

[Carlos]

Hi Massimo,

Can you please provide your feedback on this?.

I just don't want to face these show-stopper problems when my system goes to production mode.

Thanks,

   Carlos

[James McGlynn]

I realize this thread is 5 years old, but it still came up when searching for help with the error above and I just wanted to add my struggles.

I was getting the following error when deploying to Heroku:

<class 'psycopg2.ProgrammingError'> relation "web2py_session_3muses" already exists

and it was because I had "applications/*/databases" in my .gitignore file! I won't go in to why that was there, but if anyone makes the same silly mistake as me I hope this comment helps them.