Roundup Tracker - Issues

Issue 2550815

classification
Title: roundup-admin import for postgres causes id to be skipped
Type: behavior Severity: normal
Components: Database Versions: 1.4
process
Status: new Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: ber, rouilj, tekberg
Priority: low Keywords:

Created on 2013-06-27 21:11 by tekberg, last changed 2016-07-06 19:45 by tekberg.

Messages
msg4912 Author: [hidden] (tekberg) Date: 2013-06-27 21:11
From an sqlite based tracker, I did a roundup-admin export of this form

roundup-admin -i $tr_temp export $tracker

where $tr_temp contains a directory with minimal tracker context 
suitable for an export, and $tracker is the name of a tracker to be 
exported (for example 'helpdesk'). In case you didn't know, this creates 
the subdirectory $tracker and puts csv files there containing the 
database contents.

The corresponding roundup-admin import was to a postgres database, using 
a command of the form:

roundup-admin -i $tracker_dest import /tmp/$tracker

Prior to doing that I tar'ed the files created by the import, copied the 
tar file to another host and 'un'tar'ed that file to /tmp/$tracker. The 
$tracker_dest is the 'dest' field for the tracker in the site_config.ini 
file. Note that the tracker was already installed and set up for 
postgres as the back end database.

After doing the roundup-admin import, the problem I noticed is that when 
I create a new issue in the tracker, the issue ID skips a value. For 
example, if there were 3 issues, roundup-admin import will set lastval 
for the sequence _issue_ids to 4. When the nextval function is called it 
will return 5, so the IDs go 1, 2, 3, 5, skipping 4.

For the postgres backend, the setid function is being called, which 
executes the following where classname is 'issue' and setid is 4 
(maxid+1).

SELECT setval(_issue_ids, 4) FROM dual

What this should be is this

SELECT setval(_issue_ids, 4, false) FROM dual

This cause nextval to return 4, and the next time nextval is called it 
will continue incrementing to 5, etc.

After a cursory investigation of the MySQL back end code, it appears 
that a roundup-admin import will exhibit the same behavior with MySQL - 
skipping an ID.

I can provide a patch file for Postgres. I'm not sure how to write a 
test case for this, exhibiting the problem and the fix.

I don't know if this was the desired behavior. Maybe this is a 'feature' 
and not a bug.
msg4913 Author: [hidden] (ber) Date: 2013-06-28 08:04
Hi Tom,
thanks for the detailed report.
It looks like a defect to me. 
I guess it will only be of minor importance, right?
msg5705 Author: [hidden] (rouilj) Date: 2016-07-02 04:00
Tom, did you ever put together a patch for this?

IIUC it means there is a gap in numbering so all existing
issues, users etc. keep the values from before the export
(so links still work), but there is a gap in the sequence
for the next object created in the db.

Is this correct?
msg5769 Author: [hidden] (tekberg) Date: 2016-07-06 19:45
John, I never did create a patch for this. The offending line for postgres is in roundup / backends / 
back_postgresql.py, of course, and is on line 297:

sql = "select setval('_%s_ids', %s) from dual"%(classname, int(setid))

Changing this line to 

sql = "select setval('_%s_ids', %s, false) from dual"%(classname, int(setid))

fixes this problem.

I don't claim to understand the working of the ids table used to simulate sequences in MySQL. I don't 
know if MySQL has the same problem or not.

Right. After the import:

  * The IDs of the existing issues remains the same.

  * There will be an ID gap after the next issue create. 

This probably happens with other types of objects.
History
Date User Action Args
2016-07-06 19:45:37tekbergsetmessages: + msg5769
2016-07-02 04:00:40rouiljsetnosy: + rouilj
messages: + msg5705
2013-06-28 08:04:34bersetpriority: low
messages: + msg4913
2013-06-27 21:11:35tekbergcreate