Hacker News Reader

Comment by cryptonector | Hacker News Reader

I thought PG's table inheritance was widely thought to be broken, do not use.

It isn't broken per se, but the behaviour of inheritance with its many gotchas can be very surprising to a user. Like "IDs are not guaranteed unique across inherited tables" is probably not something you want from your database.

So definitely a case of "do not use", but that's only for your sanity, not because it doesn't work as advertised.

If it shouldn't be used because its properties are never desirable, then it's broken enough that it should be removed.

TFA says "First of all, there is ONE global sequence for all tables, which means that all IDs across the entirety of the systems are unique", that does sound like "IDs are guaranteed unique across table", no? Maybe there's some gotcha there?

That relies on the ID generation to be unique, rather than unique constraints.

E.g. INSERT INTO t_country (id, name) VALUES (1, 'NL') is valid and would not fail in the demonstrated database, but it would result in multiple rows when querying SELECT FROM t_global g WHERE g.id = 1.

A user inserting their own values or resetting the sequence would not necessarily cause immediate unique constraint faulures while duplicate IDs are routed into inherited tables that don't already contain that ID.

scary! definitely a really dangerous footgun and I'm not surprised nobody recommends it.

Reply on news.ycombinator.com