DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:from:to:subject:date:user-agent:cc:references:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:message-id:x-virus-scanned;
	b=WZwFOCu+r/wlkHvqbeU4SJPlYs7MqWASV2JxASv+RskJ73bAMSNpswyZRQ5VBKQ/kVIDVsPEm39NRnNsLtVfBjNqgAENw0ZemM2n8CdlD4R8RS/63FM5fk7eHNRqYHI4hjHy6RgdUyE1wQMyhMNdi3ajLBfvuFaeb50STnnjtQc=
From: Jorge Godoy <jgodoy@gmail.com>
To: Alban Hertroys <alban@magproductions.nl>
Subject: Re: [SQL] (Ab)Using schemas and inheritance
Date: Wed, 24 May 2006 14:10:44 -0300
User-Agent: KMail/1.9.1
Cc: "Jim C. Nasby" <jnasby@pervasive.com>,
 pgsql-general@postgresql.org
References: <200605231716.58784.jgodoy@gmail.com>
	<200605241221.55060.jgodoy@gmail.com>
	<44748493.5090107@magproductions.nl>
In-Reply-To: <44748493.5090107@magproductions.nl>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200605241410.46202.jgodoy@gmail.com>

Em Quarta 24 Maio 2006 13:06, Alban Hertroys escreveu:
> Jorge Godoy wrote:
> >
> > But this could be easily done with two commands (besides creating indic=
es
> > again):
> >
> > ALTER TABLE x RENAME TO x_year;
> > CREATE TABLE x () INHERITS FROM base.x;
> >
> > No need to change the rest...
>
> CREATE TABLE sample ();
> ALTER TABLE sample RENAME TO sample_2004;
> CREATE TABLE sample () INHERITS FROM sample_2004;
> ALTER TABLE sample RENAME TO sample_2005;
> CREATE TABLE sample () INHERITS FROM sample_2005;
>
> You really mean that yearly doubling inheritance? I've been told
> (admittedly by people I don't give much credit) that inheriting tables
> works by joining the underlying tables, which could seriously affect
> performance after a few years of doing this.

That's not what I showed above.  What I meant was:

CREATE TABLE base_schema.sample ();
CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client1_schema.sample RENAME TO client1_schema.sample_2004;
CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client1_schema.sample RENAME TO client1_schema.sample_2005;
CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client1_schema.sample RENAME TO client1_schema.sample_2006;
CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample;
=2E..
CREATE TABLE client2_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client2_schema.sample RENAME TO client1_schema.sample_2004;
CREATE TABLE client2_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client2_schema.sample RENAME TO client1_schema.sample_2005;
CREATE TABLE client2_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client2_schema.sample RENAME TO client1_schema.sample_2006;
CREATE TABLE client2_schema.sample () INHERITS FROM base_schema.sample;
=2E..
CREATE TABLE client3_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client3_schema.sample RENAME TO client1_schema.sample_2004;
CREATE TABLE client3_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client3_schema.sample RENAME TO client1_schema.sample_2005;
CREATE TABLE client3_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client3_schema.sample RENAME TO client1_schema.sample_2006;
CREATE TABLE client3_schema.sample () INHERITS FROM base_schema.sample;
=2E..


So I'm always inheriting from the same base and I'm renaming the inherited=
=20
tables, not the parent table.

> Did you try this? It seems to be important.

I've done what I said, not what you said...  Does it look better this way?

> Considering this case with views, you could:
>
> CREATE INDEX sample_2004_idx ON sample (object_id) WHERE date BETWEEN
> '2004-01-01'::date AND '2004-12-31'::date;
>
> CREATE INDEX sample_2005_idx ON sample (object_id) WHERE date BETWEEN
> '2005-01-01'::date AND '2005-12-31'::date;
>
> etc.
>
> You could extend that to also take company_id into account and put the
> indices on the base tables instead. I figure that takes less maintenance.

=46rom what I did to this suggestion or from what you thought I was doing t=
o=20
this suggestion?

> Mind you, to make use of those indices, you need date somewhere in your
> where-clauses.

While with table partitioning like I was saying I would not use this.  (Of=
=20
course I'll have date indices to retrieve information but not to split=20
data...)

> In your inheritence case, if you have tables covering this year only
> (that of course inherit from the complete data set somehow), you could
> use the ONLY statement in your FROM clauses. You wouldn't need to bother
> with date.

If I SELECT from the schema it will only retrieve data from the schema.  If=
 I=20
select from the parent table then I'll have all data available -- and for t=
he=20
parent table I might need some date index.

> > Of course.  This way, to keep the view working, I'd have to use SELECT
> > INTO when archiving data, right?  IIRC, views would still refer to the
> > old table if I rename them (this is a problem on my solution as well if=
 I
> > can't use a common view and make it respect the search_path, as I
> > described on the other message).
>
> By archiving you mean moving yearly data to disk via a temporary table
> or some such? In that case, yes, probably.

Yes, that's what I mean.

> For yearly archiving, dumping one of those inherited tables (containing
> only data for a specific year, after all) could work. Never tried that
> before.

If I'm removing this from the database, then I can dump it.  If I rename it=
=20
data will still be available for processing in the parent table while new=20
data is inserted in the new table.  When the time that I need to keep those=
=20
records there expire, I can dump the table to some backup and remove it fro=
m=20
the database, clearing space and eliminating unneeded tables.

> Hmm... thinking about this, I realize that in your case selecting all
> data in a year could be faster. The planner would certainly choose a
> sequential scan plan, while with all data in a single table (with views
> over them) may trigger an index scan, which would probably be slower.
>
> Depends on whether you're likely to do that, of course.

Probably not often, but if we have benefits here, I believe that those migh=
t=20
also appear on small selects.

> Well, the data is inside those tables, where it's not with views. If you
> accidentaly DROP a column in a table, the data is gone. With a view you
> just don't see it anymore, but it's still there.

Indeed, but with inherited tables I have to DROP ... CASCADE;, what might=20
trigger some advice to whoever is maintaining the database.  But you're rig=
ht=20
on this.  But I'm at the same risk dropping some column from the tables the=
=20
views are derived from... =20

> Oh right, I forgot about that. But what happens if you want to change a
> columns data type for example? I'd add a new column with the new type,
> update it with the data in the column I want to change, drop the
> original column and rename it. What happens if you do that when tables
> inherit the one you're working on?

I'd "ALTER TABLE sample ALTER COLUMN column TYPE ...".  I haven't tested th=
is=20
with regards to inheritance...=20

> Also note that foreign keys to an inherited table don't work properly.
> You get foreign key violations, because the constraint is looking at the
> wrong table, IIRC.

I'll have to check if my partner checked that.  I haven't.  Thanks for teh=
=20
reminder.

> > I'd have to have an exclusive view and use auxiliar tables if I wanted
> > that with views...  For now, from what we've discussed with the client,
> > this won't be the case.  But it is something that we should think about
> > for future expansions...  If there aren't too many exceptions, then we
> > can deal with
>
> Sure, they always say that ;)

Yeah...  And they never know what they want as well...  :-(


> > that somewhat cleanly on both sides with the approach of the auxiliar
> > table and exclusive view, but if there are too many exceptions adapting
> > the table might be better (both are "hard" if there are too many
> > exceptions...).  I haven't gotten into this planning level yet.
>
> You may be able to achieve some customization by inheriting from the
> companies inherited tables again, adding a few columns. It's still a
> pain, of course, as the client code will need to be customized too to
> take advantage of the different table definitions.

Inheriting from inherited tables wasn't in my plan.  But as I said, I haven=
't=20
gotten to this level of details yet.  :-(

> > What do you mean by a schema change?  If we go this route, then this wi=
ll
> > have to be a core part of the database and application design.  Changes
> > here will be critical, so there shouldn't be changes or they should be
> > done incrementally.  Bigger changes will require a maintenance routine
> > that will probably take the system offline for some time (from minutes =
to
> > hours).
> >
> > We're studying what to do to be able to plan things like that.
>
> Well, I know customers... They always want something to be different in
> the end.

hehe...  Yes, they do.  But I believe that they'd want that for all of thei=
r=20
customer.

> What I believe it to be isn't really significant, neither of us has much
> experience using table inheritence or updatable views for things like
> these, it seems.

Both newbies, indeed. ;-)

> One thing that comes to mind now is the timetravel contrib package.
> That's another thing I plan to look into some time, but it may suit your
> needs.

I'll take a look into it.  I confess I don't even know what it does, but I'=
ll=20
check :-)


Thanks, Alban!

=2D-=20
Jorge Godoy      <jgodoy@gmail.com>