Thread: Partition Reindexing

What is the effect of reindexing a partition on the inherited table?

For example I have a table 'test' with partitions 'test_01_07',
'test_02_07', 'test_03_07', 'test_04_07', corresponding to data from
January 2007, February 2007, and so on. I noticed that when I run a
reindex on 'test_02_07' (with a large index ~3Gb) the inserts into
'test_04_07' are extremely slow eventually locking up the entire
database.

Is there a way I could run the reindexing/vacuuming/analyzing
processes without affecting the rest of the partitions?

This is on PostgreSQL 8.1.3 on Windows 2003 Advanced Server.

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