Unique check errors when using postgres and explicit ids

Hi list,
I have a question regarding data imports with django
and postgres.
I used the database for my current application and used
the db-dump script from here:
http://www
.djangosnippets.org/snippets/14/

When I load data into the database, I cannot insert new
data afterwards. (Some weird unique check errors.) And yes,
it happens with every model-instance I want to save.
But: If I led out the ids when initially inserting data, it
works.
It took me several hours to do this manually and w/
scripts.
Now I had to add a new field to a model and don't want to
redo that again.

The funny thing is... It works with mysql. So I wondered
if someone has similar problems inserting an explicit id
when the auto field flag is set?

I'm using django SVN and the windows version of psycopg2.
Postgres is running on linux (version 8.1.8). It doesn't
work on
webfaction either, so this information shouldn't be that
helpful.

Thanks for your time,
Kai

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On 7/31/07, Kai Kuehne <kai.kuehnegmail.com> wrote:
>
> Hi list,
> I have a question regarding data imports with django
> and postgres.
> I used the database for my current application and
used
> the db-dump script from here:
> http://www
.djangosnippets.org/snippets/14/
>
> When I load data into the database, I cannot insert
new
> data afterwards. (Some weird unique check errors.)

I would have to agree with lenducha's synopsis.  If the
sequence is
not updated, you will run into duplicate ID problems.  The
easiest
thing to do right now is update all of your sequence curvals
to be
something large, where large is defined as being greater
than count(*)
for each table.

-- 
Kevin

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On 8/1/07, Kai Kuehne <kai.kuehnegmail.com> wrote:

> I'm using django SVN and the windows version of
psycopg2.
> Postgres is running on linux (version 8.1.8). It
doesn't work on
> webfaction either, so this information shouldn't be
that helpful.

Without seeing the exact error, I can think of two possible
causes:

- MySQL is hiding some key-referential integrity problems
that
Postgres is revealing. MySQL MyISAM tables don't check
referential
integrity, so if you have forward references in your data,
Postgres
could be choking where MySQL won't.

- You need to reset your Posgres sequences. './manage.py
sqlsequencereset' will generate the SQL code that will fix
the
problem; this will reset the primary key sequences and allow
you to
add new objects.

I should also point out that this problem doesn't exist with
the
database dump mechanism that is built into Django.
./manage.py
loaddata automatically does a sequence reset after loadin a
fixture.

Yours,
Russ Magee %-)

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On 8/1/07, Kevin Menard <nirvdrumgmail.com> wrote:
> I would have to agree with lenducha's synopsis.  If the
sequence is
> not updated, you will run into duplicate ID problems. 
The easiest
> thing to do right now is update all of your sequence
curvals to be
> something large, where large is defined as being
greater than count(*)
> for each table.

I'm not sure whether this is a problem of the script,
psycopg2
or postgres. Things work with mysql so I have to stick with
that in the near future.

> --
> Kevin

Thanks for answering
Kai

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

Hi Russell

On 8/1/07, Russell Keith-Magee <freakboy3742gmail.com> wrote:
>
> On 8/1/07, Kai Kuehne <kai.kuehnegmail.com> wrote:
>
> > I'm using django SVN and the windows version of
psycopg2.
> > Postgres is running on linux (version 8.1.8). It
doesn't work on
> > webfaction either, so this information shouldn't
be that helpful.
>
> Without seeing the exact error, I can think of two
possible causes:
>
> - MySQL is hiding some key-referential integrity
problems that
> Postgres is revealing. MySQL MyISAM tables don't check
referential
> integrity, so if you have forward references in your
data, Postgres
> could be choking where MySQL won't.

I know, it sounds weird.. but there are no integrity
problems in the database.
If there were problems, I think I should see them popping up
in pgadmin.
I can be wrong, though.

> - You need to reset your Posgres sequences.
'./manage.py
> sqlsequencereset' will generate the SQL code that will
fix the
> problem; this will reset the primary key sequences and
allow you to
> add new objects.

Is this the 'normal' behavior of postgres? I've never used
it
before but this seems very odd to me. I'm deleting the
whole
database and creating it new every time. After deleting I'm
doing this:

>>> manage.py syncdb
>>> db-dump.py load app

Do I have to reset the sequences even I created a new
database?

> I should also point out that this problem doesn't exist
with the
> database dump mechanism that is built into Django.
./manage.py
> loaddata automatically does a sequence reset after
loadin a fixture.

Ok, I will try that. Thank you.
(Wondering why there is db-dump.py when dumpdata works..
but wayne.)

> Yours,
> Russ Magee %-)

Kai

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On 8/1/07, Kai Kuehne <kai.kuehnegmail.com> wrote:
>
> Is this the 'normal' behavior of postgres? I've never
used it
> before but this seems very odd to me. I'm deleting the
whole
> database and creating it new every time. After deleting
I'm doing this:
>
> >>> manage.py syncdb
> >>> db-dump.py load app
>
> Do I have to reset the sequences even I created a new
> database?

Yes. The problem is cause by the way that Postgres (and
Oracle, AFAIK)
allocates primary keys.

Ordinarily, when you create a new object, you don't specify
a primary
key - Postgres will allocate a primary key for you. To
implement this,
Postgres creates a 'sequence' when it creates a table; when
you ask
for a new object, it allocates the next ID in the sequence.

This works fine, until you manually specify a primary key.
When you
save an object and say "Use PK=3", Postgres
doesn't remove 3 from the
sequence of allowed primary key values, so if 3 is the next
value on
the primary key sequence, Postgres will try to use that
value to
create the object - and fail, because that primary key value
is in
use.

The way you work around the problem is to reset the sequence
to
Max(PK) after manually specifying a primary key. That way
you can be
guaranteed that the next sequence will be 1 higher than the
currently
largest primary key.

> (Wondering why there is db-dump.py when dumpdata
works..
> but wayne.)

It was a snippet project that was started when Django's
fixture
loading still new and had some problems with edge cases (as
I recall,
the particular problem was forward references in data).
Rather than
contribute a fix to Django's fixture loading, limodou
decided to start
a snippet project to provide an alternate implementation.

Since that time, many problems with Django's fixture loader
have been
fixed (includng the forward reference problem), and there
isn't really
a need for the alternate implementation.

Yours,
Russ Magee %-)

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

Hi Russel,

On 8/1/07, Russell Keith-Magee <freakboy3742gmail.com> wrote:
> Yes. The problem is cause by the way that Postgres (and
Oracle, AFAIK)
> allocates primary keys.
>
> [postgres sequence explanation]
>
> > (Wondering why there is db-dump.py when dumpdata
works..
> > but wayne.)
>
> It was a snippet project that was started when Django's
fixture
> loading still new and had some problems with edge cases
(as I recall,
> the particular problem was forward references in data).
Rather than
> contribute a fix to Django's fixture loading, limodou
decided to start
> a snippet project to provide an alternate
implementation.
>
> Since that time, many problems with Django's fixture
loader have been
> fixed (includng the forward reference problem), and
there isn't really
> a need for the alternate implementation.

Thanks for the explanation Russel, it works great with
dump/loaddate.
I don't even have to call sqlsequencereset. 

>
> Yours,
> Russ Magee %-)

Kai

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---