PostgreSQL’s SERIAL column type is a textbook example of a leaky abstraction. And a very leaky abstraction at that. On the surface, it appears to be remarkably close to other RDMBS’ native SERIAL types or even to MySQL’s auto_increment feature. Add a table, insert some rows, and witness each getting a neat serial number automatically. Great. Go any further than this, though, and everything falls apart.
If you’re a web monkey like me, you’ve probably come this far working on your personal account. But now you want the web server and its application-specific PostgreSQL user to insert data to your shiny new serial-wieldin’ table. And this is where things break down: Inserting a row to the table without specifying a value for the serial column requires a specific access privilege you’ve never heard of. This is because creating a table with a serial column in PostgreSQL implicitly creates a sequence associated with that column. It also implies that the default value for the serial column is the nextval of the sequence, and accessing that nextval is a privilege-requiring operation.
In other words, you have to manually GRANT access to a sequence you did not create and probably don’t know the name of. Similarly, the automatically created sequence does not disappear if you drop the table. If you want to create the table again, you first have to drop the unknown-to-you, not-created-by-you sequence manually.
Oftentimes the abstractions we put in to make users’ lives easier leak so much as to make them completely useless. The SERIAL column was put in to enable people use sequences without knowing about them. But if people ever drop the table or want other users to insert data into it, they have to learn about sequences anyway. But that's not all; they also get bitchslapped by the system for thinking that they could wiggle their way out of learning SQL basics.
I believe that this feature in PostgreSQL does about 30% of the work of a useful, opaque SERIAL column. But I don’t understand why this code is in the tree: the 30% of a feature is not, in itself, useful to anyone.
Okay, I take that back. I understand why it is in the tree. It probably went in there as soon as it didn’t bust the compile or the unit tests—just like every other piece of code in every other open source program.