Serial versus Serial What they don’t tell you about referential integrity and serials.

1. Serial versus SerialWhat they don’t tell you aboutreferential integrity and serials. By: Lloyd Albin 11/6/2012

2. Serials Serials are really integers that have a sequence attached to provide the capability to have a auto incrementing integer. There are three sizes of serials:

3. Creating a Serial (Short Method) The simplest way to create a serial is to use the serial type. CREATE TABLE tablename ( colname SERIAL ); Using the serial type will cause this notice to show up. NOTICE: CREATE TABLE will create implicit sequence "tablename_colname_seq" for serial column "tablename.colname"

4. Creating a Serial (Long Method) The notice can be avoided by creating a serial the long method. CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

5. Creating a Serial (Dump Method) The dump method does the same thing but in a different order and does two extra command. CREATE TABLE tablename ( colname integer NOT NULL ); CREATE SEQUENCE tablename_colname_seq; ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname; SELECT pg_catalog.setval('tablename_colname_seq', 1, true); ALTER TABLE tablenameALTER COLUMN colnameSET DEFAULT nextval('tablename_colname_seq'::regclass);

6. Notes about Serials Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as "owned by" the column, so that it will be dropped if the column or table is dropped. The sequence created for a serial column is automatically dropped when the owning column is dropped. You can drop the sequence without dropping the column, but this will force removal of the column default expression.

7. Moving Tables with Serials When you need to move a table with a serial, you may just simply move the table and the sequence will automatically move with the table. ALTER TABLE old_schemaname.tablename SET SCHEMA new_schemaname;

8. Deleting Tables with Serials When you need to delete a table with a serial, you may just simply delete the table and the sequence will automatically be deleted with the table. DROP TABLE tablename; Even if you use restrict, the sequence will be deleted. DROP TABLE tablename RESTRICT;

9. Deleting Serials attached to Tables According to the docs, 9.2 and older, you may delete the sequence and this will remove the default expression. DROP SEQUENCE tablename_colname_seq; ERROR: cannot drop sequence tablename_colname_seqbecause other objects depend on it DETAIL: default for table tablenamecolumn colnamedepends on sequence tablename_colname_seq HINT: Use DROP ... CASCADE to drop the dependent objects too.

10. Deleting Serials attached to Tables So now we will try dropping the sequence with a cascade command. DROP SEQUENCE tablename_colname_seq CASCADE; This works perfectly leaving the table and having removed the default and switching the serial to a integer.

11. The Problem Everything seems to be great. You go along moving tables/views/etc to other schemas, so anything left over must me ok to drop before dropping the schema. There might even be some sequences that you can drop without any error messages. But the problem appears later when you try entering data into a table and receive this message. INSERT INTO tablenameVALUES (DEFAULT); ERROR: relation "tablename_colname_seq" does not exist

12. Two different types of Serials? So while these two serials may look the same on the outside, they don’t act the same and so must be different on the inside.

13. Finding the difference When looking closely at two tables, one that the sequence moved and one that the sequence did not move, I noticed that they were slightly different. Working Style – Sequence moves: CREATE TABLE tablename ( colnameinteger DEFAULT nextval('tablename_colname_seq'::regclass) NOT NULL ); Problem Style – Sequence does not move: CREATE TABLE tablename ( colnameinteger DEFAULT nextval(('tablename_colname_seq'::text)::regclass) NOT NULL );

14. Fixing the difference To fix the default value, you need to remove the text by issuing an alter table command. ALTER TABLE tablename ALTER COLUMN colname SET DEFAULT nextval('tablename_colname_seq'::regclass);

15. What is fixed and what is not fixed If you now move the tables to a different schema and then try to delete the sequence in the old schema you will get the error message about “cannot drop sequence”. This is good because we are now seeing some referential integrity. But they sequence did not move with the table. If you delete the table the sequence will not be deleted. So there is currently only one way referential integrity.

16. Finding the next difference With my GUI tools the tables/sequence now look the same, so I dumped both types of tables and found one more difference. ALTER SEQUENCE tablename_colname_seqOWNED BY tablename.colname;

17. Finally fixed Now the table acts the way we expect it should. When we move the table, the sequence moves also. We are not allowed to delete the sequence without deleting the table. If we delete the table, the sequence is automatically deleted. We now have bi-directional referential integrity.

18. Finding out how this happened When looking at the Postgres documentation about Sequence Functions, I found when everything changed. It happened between versions 8.0 and 8.1. If you have any serials created in 8.0 or before and are now using 8.1 or newer, you do not have any referential integrity on those early serials but will have either one-way or bi-directional referential integrity on the newer serials.

19. One Way versus Bi-Directional If you have a 1-1 relationship, sequence to table, then you will want bi-directional referential integrity. If you have a 1 to many relationship, one sequence to many tables, then you will want one way referential integrity. This one way relationship keeps you from dropping the sequence when other tables still rely on it. But you must move the sequence separately when moving the tables.

20. Sequence Manipulation Note Note: Before PostgreSQL 8.1, the arguments of the sequence functions were of type text, not regclass, and the above-described conversion from a text string to an OID value would happen at run time during each call. For backwards compatibility, this facility still exists, but internally it is now handled as an implicit coercion from text to regclass before the function is invoked. When you write the argument of a sequence function as an unadorned literal string, it becomes a constant of type regclass. Since this is really just an OID, it will track the originally identified sequence despite later renaming, schema reassignment, etc. This "early binding" behavior is usually desirable for sequence references in column defaults and views. But sometimes you will want "late binding" where the sequence reference is resolved at run time. To get late-binding behavior, force the constant to be stored as a text constant instead of regclass: nextval('foo'::text) foo is looked up at runtime Note that late binding was the only behavior supported in PostgreSQL releases before 8.1, so you may need to do this to preserve the semantics of old applications. Of course, the argument of a sequence function can be an expression as well as a constant. If it is a text expression then the implicit coercion will result in a run-time lookup.

21. How to find out if your affected What we need to do is look at all the default values and see if there are any ‘::text)::regclass)’. SELECT adsrcFROM pg_catalog.pg_attrdef WHERE adnum = 1 AND adsrc LIKE '%::text)::regclass)';

22. Is there any way to automate the fix The answer is yes, with conditions. • If you don’t use the same sequence on more than one table some of the time. It needs to be one way or the other. This is so that you can either do the OWNED BY or not. • If you use the default name formatting for the sequence name, this will allow you to easily figure out the table and field name, otherwise it is more complex but not impossible.

23. In Closing – Recap • ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;This provides referential integrity from the table to the sequence. If you move or drop the table, the sequence will either move or be dropped at the same time. • ALTER TABLE tablenameALTER COLUMN colnameSET DEFAULT nextval('tablename_colname_seq'::regclass);This provides referential integrity from the sequence to the table. You may not delete the sequence if any table relies on it. But must be of type regclass and not a re-cast to type regclass.