Partition Reindexing

On 9/4/07, Nik <XLPizza@gmail.com> wrote:

This is on PostgreSQL 8.1.3 on Windows 2003 Advanced Server.

Only 8.2 or newer has CREATE INDEX CONCURRENTLY.

Maybe you could schedule a maintenance window for this.

I have the following table set up:

CREATE TABLE states
(
state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
state character(2),
full_name character varying,
timezone character varying,
CONSTRAINT "PK_state_id" PRIMARY KEY (state_id)
)

CREATE TABLE canadian_provinces
(
-- Inherited: state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
-- Inherited: state character(2),
-- Inherited: full_name character varying,
-- Inherited: timezone character varying,
CONSTRAINT "PK_province_id" PRIMARY KEY (state_id)
)

as expected I can do select * from states and get everything out of
the child table as well. What I can't do is create a FK to the
states table and have it look in the child table as well. Is this on
purpose? Is it possible to have FK that spans into child tables?

Thanks

On 9/14/07, Ketema Harris <ketema@ketema.net> wrote:

I have the following table set up:

CREATE TABLE states
(
state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
state character(2),
full_name character varying,
timezone character varying,
CONSTRAINT "PK_state_id" PRIMARY KEY (state_id)
)

CREATE TABLE canadian_provinces
(
-- Inherited: state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
-- Inherited: state character(2),
-- Inherited: full_name character varying,
-- Inherited: timezone character varying,
CONSTRAINT "PK_province_id" PRIMARY KEY (state_id)
)

as expected I can do select * from states and get everything out of
the child table as well. What I can't do is create a FK to the
states table and have it look in the child table as well. Is this on
purpose?

Not so much on purpose as an artifact of the design process.
PostgreSQL can't span multiple tables with indexes, a unique one of
which is required for a FK to point to a field.

Is it possible to have FK that spans into child tables?

Not really. You might be able to write your own function that
approximates such behavior. I would think some kind of intermediate
table with every value from all the children for that one column could
be used, but performance would suffer.

On Friday 14 September 2007, Ketema Harris <ketema@ketema.net> wrote:

as expected I can do select * from states and get everything out of
the child table as well. What I can't do is create a FK to the
states table and have it look in the child table as well. Is this on
purpose? Is it possible to have FK that spans into child tables?

No.

--
The only difference between conservatives and liberals regarding budget cuts
is tense. Conservatives say they will cut the budget, and then they increase
it. After the budget has increased, liberals say that it has been cut.

On Sep 14, 2007, at 10:35 AM, Ketema Harris wrote:

I have the following table set up:

CREATE TABLE states
(
state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
state character(2),
full_name character varying,
timezone character varying,
CONSTRAINT "PK_state_id" PRIMARY KEY (state_id)
)

CREATE TABLE canadian_provinces
(
-- Inherited: state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
-- Inherited: state character(2),
-- Inherited: full_name character varying,
-- Inherited: timezone character varying,
CONSTRAINT "PK_province_id" PRIMARY KEY (state_id)
)

as expected I can do select * from states and get everything out of
the child table as well. What I can't do is create a FK to the
states table and have it look in the child table as well. Is this
on purpose? Is it possible to have FK that spans into child tables?

I'm assuming you just left out an INHERITS clause or ALTER TABLE
statement to add the inheritance? Anyways, the answer to your
question is no, you'll need to create any dependencies to child
tables separately.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Hi,

Ketema Harris wrote:

as expected I can do select * from states and get everything out of the
child table as well. What I can't do is create a FK to the states table
and have it look in the child table as well. Is this on purpose? Is it
possible to have FK that spans into child tables?

This is a well known (and documented, see [1]Postgres Documentation, Chapter 5.8.1 Caveats (of Inheritance): http://www.postgresql.org/docs/8.2/static/ddl-inherit.html) deficiency. It's due to
the current implementation of indices, which are bound to exactly one
table, meaning they do return a position within the table, but cannot
point to different tables.

Regards

Markus

[1]: Postgres Documentation, Chapter 5.8.1 Caveats (of Inheritance): http://www.postgresql.org/docs/8.2/static/ddl-inherit.html
http://www.postgresql.org/docs/8.2/static/ddl-inherit.html