From: Susanne Ebrecht
Date: July 28 2009 4:34am
Subject: Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDB
List-Archive: http://lists.mysql.com/backup/9
Message-Id: <4A6E7FDB.4080508@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; CHARSET=US-ASCII; format=flowed
Content-Transfer-Encoding: 7BIT

Hello Ingo, Kostja and the others,

by accident last Ingo reply here got my attention.

Anyway, I made some SQL Standard researches about the topics truncate 
and auto_inc.

First of all:
Neither TRUNCATE nor AUTO_INCREMENT are in the SQL Standard.

But SERIAL is.

We implemented SERIAL more or minder as an aliases of a BIGINT AUTO_INC 
NOT NULL UNIQUE column.

Generally, our AUTO_INCREMENT feature is really close to a "fix" 
ascending SEQUENCE. SEQUENCES also are defined in SQL Standard.

The only possibilities according to SQL Standard to reset values in 
SEQUENCE are:

1) DROP/RECREATE SEQUENCE
2) ALTER SEQUENCE (ALTER start value, increment value and so on)

SEQUENCE and TABLE are two different objects in SQL Standard.

According to this my logic says that TRUNCATE should clean the TABLE but 
let the sequence (AUTO_INC) as what it is.

Please, never forget that SERIAL is defined in SQL Standard and it is 
defined as INTEGER column with DEFAULT SEQUENCE with start value of 1 
and increment value of 1.

TRUNCATE itself is not defined in SQL Standard but besides us PostgreSQL 
has TRUNCATE. Also afair Informix and DB2 having TRUNCATE same way as 
PostgreSQL.

I looked into PG docs here: 
http://www.postgresql.org/docs/8.3/interactive/sql-truncate.html

Of course I also tested it. As I expected PG Truncate is not resetting 
SERIAL (I mean the SEQUENCE here).

When you really want to reset the SEQUENCE in PostgreSQL you really need 
to ALTER SEQUENCE or use SETVAL() function. Usually, PostgreSQL will 
throw an error when you try to DROP a SEQUENCE that is used in a column 
besides you are using CASCADE. And when you use DROP CASCADE your whole 
column will be deleted too.

According to my researches here I would recommend that we avoid to reset 
AUTO_INC during TRUNCATE. Users won't expect such a behaviour. 
Especially not, when they used SERIAL. The only way to change/reset 
AUTO_INC should be via ALTER.

Just my 2 cent.

Susanne

-- 
Sun Microsystems GmbH 
Dipl.-Inf. Susanne Ebrecht, Support Engineer
33615 Bielefeld, Germany

http://www.sun.com

Registered Office: Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Commercial register of the Local Court of Munich: HRB 161028
Managing Directors: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Chairman of the Supervisory Board: Martin Haering