get_sql_sequence_reset doesn't work on tables that have no records.

[Tai Lee]

the sql generated by these methods (which only apply to postgresql and postgresql_psycopg2) sets the new sequence to max(pk), which works great when the table has a few records, but fails to reset the sequence when there are no records.

Python 2.4.3 (#1, May  2 2006, 19:34:13) 
[GCC 4.0.0 20041026 (Apple Computer, Inc. build 4061)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from django.contrib.sites import models
>>> from django.core.management import disable_termcolors, get_sql_sequence_reset
>>> from django.db import connection
>>> disable_termcolors()
>>> cursor = connection.cursor()
>>> models.Site.objects.all().delete()
>>> models.Site.objects.all()
[]
>>> s = models.Site.objects.create(id=1, name='example.com', domain='example.com')
>>> s.id
1
>>> for sql in get_sql_sequence_reset(models): cursor.execute(sql)
... 
>>> s = models.Site.objects.create(name='example.com', domain='example.com')
>>> s.id
2L
>>> models.Site.objects.all().delete()
>>> models.Site.objects.all()
[]
>>> for sql in get_sql_sequence_reset(models): cursor.execute(sql)
... 
>>> s = models.Site.objects.create(name='example.com', domain='example.com')
>>> s.id
3L
>>> s = models.Site.objects.create(name='example.com', domain='example.com')
>>> s.id
4L
>>> s.delete()
>>> models.Site.objects.all()
[<Site: example.com>]
>>> for sql in get_sql_sequence_reset(models): cursor.execute(sql)
... 
>>> s = models.Site.objects.create(name='example.com', domain='example.com')
>>> s.id
4L
>>> 

it could be fixed by setting the sequence to max(pk) only if model.objects.count() is greater than 0, otherwise set it back to 0.

[real.human@…]

reset sequence to max(pk) if there are records, otherwise reset sequence to 1.

[real.human@…]

added a patch to fix this bug in postgresql and postgresql_psycopg2 by setting the new sequence to coalesce(max(pk), 1) and passing setval's 3rd argument is_called to true if there are records or false if not.

this ensures that the primary key of the next record inserted will be 1 if the table is empty, or max(pk) + 1 if there are records.

[Chris Beaven]

Patch needs some inline comments explaining the slightly tricky SQL being used, but it seems necessary so accepting.

[real.human@…]

Reset sequence to the max pk value if there are records, otherwise reset it to 1.

[real.human@…]

Inline comments have been added.

[Chris Beaven]

Bumping to ready. Committer may request tests, but they can always push it back down.

[Malcolm Tredinnick]

(In [5455]) Fixed #4432 -- Fixed PostgreSQL sequence resetting in the case when a table has
no rows yet. Thanks, mrmachine.