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

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

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

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@eranet.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@path.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