Richard Wesley wrote:
How do I tell when I have run out of new table IDs? And if I have, will a backup and restore fix the problem? We define temporary tables for doing various kinds of OLAP work. We delete them, but AFAIK, the IDs do not get reused.
Sean Leyne answers:
By "table ID" are you referring to the system's ID for the Table?
If so, then:
- you can execute the following SQL to find out the last ID value: SELECT MAX( rdb$relation_id) FROM rdb$relations
- if you have reached the limit, a backup/restore will not solve the problem.
I don't know where the next Table ID value is stored (I thought a system defined Generator, but can't find it). A backup/restore will not reset the ID, and trying to manually set the ID will get you into other problems.
Ann Harrison adds:
In fact, a backup and restore will solve the problem if you have dropped tables. At one point we considered scavenging old table ids ... it might have happened ... but it got more complicated the more we looked at it. RDB$RELATION_ID is a short, probably a signed short, and so you'll get int trouble if the value is more than 32767.
If you get there by creating and dropping tables on a regular basis, then backup and restore will fix it. If you've actually got 32139 tables (the first 128 are reserved for system tables) then you've got a really complex database.
Ivan Prenosil adds:
Simple test (just generating some Create&Delete Table commands using copy&paste) shows that FB has no problem after reaching 32k table id. It simply restarts from 128 (skiping existing table ids).