Hi, Guillaume I agree it's not so clear from the documentation below, but as far as I know the CTID is the physical position of the row inside the heap. Every UPDATE and some VACUUM operations can affect it.
Even if the CTID may be stable over a DELETE as of today, some future storage engine development (like HOT enhancements) may put an end to that. In general, the CTID is not guaranteed to remain the same unless you hold a FOR UPDATE lock on the row. The CTID like the OID is part of internal system information that can change at the discretion of the Postgresql development team without any respect to backward compatibility such as happened with OID. A further reason to not use a CTID is that we will need to re-test on every major upgrade version. I wish there was another option to make it the same as mysql and initially I hoped there was, but after much investigation the only safe option is to use a serial sequential column. The current implementation for OIDs is not ideal and has been deprecated see http://www.postgresql.org/docs/8.3/static/datatype-oid.html. "The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables." Attached is a patch for a sequential serial column called dlr_id that will work for all current versions of Postgresql. I've been told that if anything changes in the future implementation for a sequential serial column, Postgresql will provide a migration path and it won't just be deprecated such as a value used for internal system information. Stripe and others, you now have all the pros and cons to choose an approach, we are not for OID and CTID as discussed above. The recommended Postgresql table would then be: CREATE TABLE dlr ( dlr_id SERIAL PRIMARY KEY, smsc VARCHAR(48), ts VARCHAR(48), destination VARCHAR(48), source VARCHAR(48), service VARCHAR(48), url VARCHAR(255), mask smallint, status smallint, boxc VARCHAR(48) ); CREATE INDEX dlr_smsc_ts ON dlr(smsc, ts); Smallint is suggested as mask and status will not overrun the max value of 32767. If the Kannel developers feel that the mast and status values could someday be larger than 32767 and then integer should be used instead see http://www.postgresql.org/docs/8.1/interactive/datatype.html Regards
postgresqlSerialID.patch
Description: Binary data
