Partitioning (Was: Foreign Key to an (abstract?) Parent Table)

That's what I was thinking: a seen_its_music table and a seen_its_thoughts
table and so on. I don't think that's really going to work though because I
don't have ready access to what kind of moment the seen_it was on at the
time. Oh well.

On a related note, since seen_its is such a huge table, I'm thinking of
partitioning it. The best web page I could find on that subject was
http://www.fuzzy.cz/en/articles/automatic-management-of-partitions-in-postgresql/but
I'm hoping someone might be able to direct me to something better.

On Tue, Feb 21, 2012 at 1:13 PM, Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl> wrote:

> Hi,
> You are right - foregin keys are not inherited (doc:
> http://www.postgresql.org/docs/9.1/static/ddl-inherit.html).
>
> hi
> As work around You may crate similar structure of inherited tables
> structure for "seen_its" (with no additional columns). Every table should
> correspond to the table in "moments" structure.
> Then You may implement foreign keys for corresponded tables. In that way
> updates and deletes will work correctly. So, You will have two branches of
> inherited structures.
>
> Hope this helps.
>
> Regards,
> Bartek
>
>
>
> 2012/2/18 Alessandro Gagliardi <alessandro(at)path(dot)com>
>
>> I have
>> CREATE TABLE moments
>> (
>> moment_id character(24) NOT NULL DEFAULT to_char(now(),
>> 'JHH24MISSUS'::text),
>> block_id character(24) NOT NULL,
>> inserted timestamp without time zone NOT NULL DEFAULT now(),
>> CONSTRAINT moments_pkey PRIMARY KEY (moment_id )
>> )
>> which is the parent of a bunch of other tables like
>> CREATE TABLE music
>> (
>> track_id character(24),
>> CONSTRAINT music_pkey PRIMARY KEY (moment_id )
>> )
>> INHERITS (moments)
>> and
>> CREATE TABLE thoughts
>> (
>> body text,
>> CONSTRAINT thought_pkey PRIMARY KEY (moment_id )
>> )
>> INHERITS (moments)
>> In fact, the moments table itself is empty. (In OOP terms, I suppose it
>> would be "abstract".) However, moment_id should be unique across all of
>> the tables that inherit moments.
>> But this question isn't about inheriting a primary key (which I know
>> PostgreSQL doesn't do). Rather, it is about creating a foreign key. You
>> see, I also have
>> CREATE TABLE seen_its
>> (
>> user_id character(24) NOT NULL,
>> moment_id character(24) NOT NULL,
>> created timestamp without time zone,
>> inserted timestamp without time zone DEFAULT now(),
>> CONSTRAINT seen_its_pkey PRIMARY KEY (user_id , moment_id )
>> )
>> and what I would like is for moment_id in seen_its to be a foreign key
>> that applies to all of the tables that inherit from moments. I tried
>> ALTER TABLE seen_its ADD CONSTRAINT seen_it_moment_id FOREIGN KEY
>> (moment_id) REFERENCES moments (moment_id)
>> ON UPDATE NO ACTION ON DELETE NO ACTION;
>> CREATE INDEX fki_seen_it_moment_id ON seen_its(moment_id);
>> but, as you might imagine, it fails because none of the moment_ids in
>> seen_its are in moments, they're in music and thoughts and so on.
>> Is there any way to make this work?
>> Thank you in advance!
>> -Alessandro Gagliardi
>>
>>
>