From Volker Barth:

Just to get a better understanding of the relation between max_identity and (GLOBAL) AUTOINCREMENT column defaults:

The value in syscolumn.max_identity (or systabcol for SA 10 and above) contains the maximum value used for (global) autoincrement columns.

AFAIK, it is only updated by a CHECKPOINT statement.

When building a database with LOAD TABLE statements, each LOAD TABLE statement issues a checkpoint first, so this will automatically update max_identity for all already loaded tables. A regular database shutdown will also perform a checkpoint.

Question: Are there other operations/statements that will update max_identity?

asked 30 Apr '10, 16:54

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

Wow, by incident I asked this question in the newsgroup before I tried here - and now I'm backported...Thanks, Glenn:)

(30 Apr '10, 19:28) Volker Barth

...and that gives Glenn a "Student" badge. Ain't it funny:)

(30 Apr '10, 19:41) Volker Barth

Max_identity column values, column histograms, and other catalog metadata are persistently saved to the catalog whenever the server does an explicit or implicit CHECKPOINT.

LOAD TABLE is one statement that issues an implicit CHECKPOINT because it performs page-level undo if the LOAD TABLE fails. In addition to LOAD TABLE, the following statements in Version 12 (Innsbruck) will trigger an implicit or explicit CHECKPOINT, and hence cause the metadata for AUTOINCREMENT columns to be written to the catalog:

  • LOAD TABLE statement
  • CHECKPOINT statement
  • BACKUP statement
  • REORGANIZE TABLE statement
  • COMMIT statement when the database does not have a transaction log
  • CREATE INDEX statement on a function (a computed column)
  • ALTER TABLE statement - unless the ALTER TABLE trivially adds a nullable column or adds a referential integrity constraint and does not need to physically modify the table's pages in any way
  • DROP TABLE and DROP MATERIALIZED VIEW statements, but only if the table or view contains at least one row
  • CREATE DBSPACE statement
  • DROP DBSPACE statement

Most, though unfortunately not all, of these statements document the side-effect of a checkpoint in their specific documentation, usually in the "Side Effects" section. However, I noticed a few (such as CREATE DBSPACE) that did not do so - and I have corrected this in the 12.0 documentation.

permanent link

answered 30 Apr '10, 17:03

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

edited 30 Apr '10, 19:07

I made some tests with V 8.0.3 and GLOBAL AUTOINCREMENT and modifications of GLOBAL_DATABASE_ID (with immediate explicit checkpoint) and noticed the following: For columns that were updated between changes of GLOBAL_DATABASE_ID, the value of max_identity changed and reflected the new ID range. For columns that were not updated the value of max_identity remained the same and then was not in the current GLOBAL_DATABASE_ID range. This would mean that in those cases, the next default value will be the first from the ID range. - Just a somewhat unexpected behaviour, no bug obviously...

(30 Apr '10, 19:38) Volker Barth

So, to sum up Glenn's elaborate answer:

No, max_identity is only updated by a (explicit or implicit) checkpoint.

permanent link

answered 30 Apr '10, 19:50

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 30 Apr '10, 16:54

question was seen: 3,385 times

last updated: 30 Apr '10, 19:50