5 ms·
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. - Th
by ysleepy 9y ago
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.
- aidos 9y agoI disagree about enum. I've tried using it but I found that it's too hard to manage/migrate in postgres. Obviously there are a few different ways of achieving the enumish behaviour in postgres (or other dbs) — nowadays I just start with text with a constraint and upgrade to a real table if I need more detail. YMMV but I don't think a blanket "just use enum" is the correct approach.
- emidln 9y agoThis is too hard to put into a sql file and run with your migrations tool? BEGIN; ALTER TYPE my_enum ADD VALUE 'bar' AFTER 'foo'; COMMIT;
- aidos 9y agoFrom the docs (as I suspect you already know): ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block 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.
- felixge 9y agoYeah, this doesn't actually work. You'll get this error: ERROR: ALTER TYPE ... ADD cannot run inside a transaction block
- lfittl 9y agoFWIW, this is fixed in the upcoming Postgres 10: "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#idm44799998312080 https://www.postgresql.org/docs/devel/static/release-10.html...
- felixge 9y agoSweet. That's certainly a nice improvement :). If removing ENUM values will be supported at some point as well, I can see myself recommending them unconditionally.
- anarazel 9y agoWould you be ok if dropping a value were to just mark it as 'deleted' from the catalogs, and no new values could be assigned? The reason enums have these weird restrictions is that they can appear in indexes, even after a value has been deleted (or its creation rolled back). If we don't know how to compare them after deletion, the index can't be correctly traversed anymore...
- felixge 9y agoYeah, I think that'd be reasonable. My main motivation for using ENUMs is type safety and small column width. What you're proposing sounds like it would keep both of these advantages :)
- aidos 9y ago2 Questions! What's the index data structure that holds old enum values even after the value itself is not used anymore? 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?
- felixge 9y agoYeah, maybe I'll update the post and mention ENUMs. 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.pgh.pa.us https://www.postgresql.org/message-id/21012.1459434338%40sss...
- ysleepy 9y agoHm, I didn't know that about enums. In the end it is just quibble about style anyway and one usually end up with what is measured faster in testing..
- jerrysievert 9y agobut if you were to push those states into a table, you'll likely end up with degraded performance due to the joins. 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 :)
- mozumder 9y ago> Also the transition table might be an actual SQL table as well instead of switch-cases in a function. That would likely add an unnecessary read and slow things down just a little bit.
- ysleepy 9y agoIt depends. The table will reside in memory and it will just be a scan over a few bytes. Possibly cheaper than firing up the PL/pgSQL interpreter.