Cool trick. Should be an enum instead of text though.
Also the transition table might be an actual SQL table as well instead of switch-cases in a function. - That way it remains a bit more declarative and you can do some meta-queries.
I like the type safety of ENUMs, and I'm actually using them in my application, but they can cause a lot of problems. E.g. it's currently almost impossible to remove an ENUM value [1], and adding a new value can't be done inside of a transaction.
The state transitions could definitely go into a table, but I feel it's overkill in many cases, and would have probably obfuscated the main ideas presented in the article.
Anyway, thanks for your feedback. There is definitely a lot more to explore for anybody who is interested in applying these ideas in practice :)
[1] https://www.postgresql.org/message-id/21012.1459434338%40sss...
In the end it is just quibble about style anyway and one usually end up with what is measured faster in testing..
I agree with your comments about ENUMs, though, especially having built many "static" products that suddenly need their ENUMs changed, and having a group of developers visiting me and asking why it's not as possible as they had assumed :)
YMMV but I don't think a blanket "just use enum" is the correct approach.
BEGIN;
ALTER TYPE my_enum ADD VALUE 'bar' AFTER 'foo';
COMMIT;Also, removing a value is a pain (though, that may have changed now).
I know you can do it — but I've found after trying a bunch of approaches that text with constraints is a better starting point. Just wanted to point it out because it's something I researched a fair amount. Many guides say that enum can be a pain but I decided that I'd use them to be pure, then discovered they were a pain, and now don't use them as a starting point.
ERROR: ALTER TYPE ... ADD cannot run inside a transaction block
"Reduce locking required for adding values to enum types (Andrew Dunstan, Tom Lane)
Previously it was impossible to run ALTER TYPE ... ADD VALUE in a transaction block unless the enum type was created in the same block.
Now, only references to uncommitted enum values from other transactions are prohibited."
https://www.postgresql.org/docs/devel/static/release-10.html...
Secondly, what are your thoughts on using enum generally? Would you recommend them in cases where you don't need to really optimise for narrower columns?
It's not that index structure specific atm, even though it could possibly be avoided for some types (e.g. hash, although there's considerable visibility issues to make that possible). Consider e.g. a btree index, if the deleted enum value ends up in an inner page, you really need to know how it compares to know where to descend to.
> Secondly, what are your thoughts on using enum generally? Would you recommend them in cases where you don't need to really optimise for narrower columns?
I like using them. There's cases where the transactional restrictions make it too problematic, but other than that I think the documentational advantage is substantial, besides just the width.
That would likely add an unnecessary read and slow things down just a little bit.