Code

Opened 7 years ago

Closed 7 years ago

#4432 closed (fixed)

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

Reported by: mrmachine Owned by: adrian
Component: Database layer (models, ORM) Version: master
Severity: Keywords: get_sql_sequence_reset max id empty table no records
Cc: real.human@… Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (2)

django-4432.diff (3.3 KB) - added by real.human@… 7 years ago.
reset sequence to max(pk) if there are records, otherwise reset sequence to 1.
django-4432.2.diff (4.2 KB) - added by real.human@… 7 years ago.
Reset sequence to the max pk value if there are records, otherwise reset it to 1.

Download all attachments as: .zip

Change History (8)

Changed 7 years ago by real.human@…

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

comment:1 Changed 7 years ago by real.human@…

  • Has patch set
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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.

comment:2 Changed 7 years ago by SmileyChris

  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Accepted

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

Changed 7 years ago by real.human@…

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

comment:3 Changed 7 years ago by real.human@…

Inline comments have been added.

comment:4 Changed 7 years ago by SmileyChris

  • Patch needs improvement unset

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

comment:5 Changed 7 years ago by SmileyChris

  • Triage Stage changed from Accepted to Ready for checkin

comment:6 Changed 7 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.