On Thu, Oct 31, 2019 at 03:02:40PM -0400, Isaac Morland wrote:
On Thu, 31 Oct 2019 at 14:50, Stephen Frost <sfr...@snowman.net> wrote:
Greetings,
* Peter Geoghegan (p...@bowt.ie) wrote:
[....]
Absolutely- our lack of such is a common point of issue when folks are
considering using or migrating to PostgreSQL.
Not sure how similar my situation really is, but I find myself wanting to
have indices that cross non-partition members of an inheritance hierarchy:
create table t (
id int,
primary key (id)
);
create table t1 (
a text
) inherits (t);
create table t2 (
b int,
c int
) inherits (t);
So "t"s are identified by an integer; and one kind of "t" has a single text
attribute while a different kind of "t" has 2 int attributes. The idea is
that there is a single primary key constraint on the whole hierarchy that
ensures only one record with a particular id can exist in all the tables
together. I can imagine wanting to do this with other unique constraints
also.
IMO the chances of us supporting global indexes with generic inheritance
hierarchies are about zero. We don't even support creating "partition"
indexes on those hierarchies ...
At present I don't actually use inheritance; instead I put triggers on the
child tables that do an insert on the parent table, which has the effect of
enforcing the uniqueness I want.
Does it? Are you sure it actually works in READ COMMITTED? What exactly
does the trigger do?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
