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

Regards,
Bartek

2012/2/21 Alessandro Gagliardi <alessandro(at)path(dot)com>

> 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.

I don't think that is a problem, because You can implement logic based on
triggers to point data to different tables. This time I suggest to move
logic to store procedures (functions). It is possible to recognize where
data are in inherited tables, co You can recognize where data should be
inserted doc: http://www.postgresql.org/docs/9.1/static/ddl-inherit.html shows
example:

SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 AND c.tableoid = p.oid;

which returns:

relname | name | altitude
----------+-----------+----------
cities | Las Vegas | 2174
cities | Mariposa | 1953
capitals | Madison | 845

the relname column shows where data are in inheritance structure.
Based on that knowledge it is easy to insert data to "seen_its" structure.

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
>>>
>>>
>>
>