Thread: inheritance. more.

create table master (
id serial,
mdn varchar(11),
meid varchar(18),
min varchar(11),
constraint mmm_master unique (mdn, meid, min)
);
insert into master(mdn, meid, min)
select mdn, meid, min from test_data where meid != '000000000000000000'
limit 10;

Everything works up to this point...

insert into master(mdn, meid, min)
select mdn, meid, min from test_data where meid != '000000000000000000'
limit 10;

And this fails, like I would expect it to.


create table slave (
deleted boolean default false
) inherits (master);

insert into slave(mdn, meid, min)
select mdn, meid, min from test_data where meid != '000000000000000000'
limit 10;
insert into slave(mdn, meid, min)
select mdn, meid, min from test_data where meid != '000000000000000000'
limit 10;

I now have 30 rows in the master table, with duplicates...

On Apr 27, 2008, at 8:23 PM, Tom Allison wrote:

> create table master (
> id serial,
> mdn varchar(11),
> meid varchar(18),
> min varchar(11),
> constraint mmm_master unique (mdn, meid, min)
> );
> insert into master(mdn, meid, min)
> select mdn, meid, min from test_data where meid !=
> '000000000000000000' limit 10;
>
> Everything works up to this point...
>
> insert into master(mdn, meid, min)
> select mdn, meid, min from test_data where meid !=
> '000000000000000000' limit 10;
>
> And this fails, like I would expect it to.
>
>
> create table slave (
> deleted boolean default false
> ) inherits (master);
>
> insert into slave(mdn, meid, min)
> select mdn, meid, min from test_data where meid !=
> '000000000000000000' limit 10;
> insert into slave(mdn, meid, min)
> select mdn, meid, min from test_data where meid !=
> '000000000000000000' limit 10;
>
> I now have 30 rows in the master table, with duplicates...

No, you don't.  You have duplicates in slave, not master, and there is
not unique constraint on slave.  They are physically separate tables
and Postgres doesn't yet handle inheritance of constraints from parent
to child tables via inheritance.

Erik Jones

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

On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones <erik@myemma.com> wrote:

Was it done by design or was it a limitation we couldn't get over?

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

On Apr 28, 2008, at 8:01 PM, Gurjeet Singh wrote:

> On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones <erik@myemma.com> wrote:
>
> Postgres doesn't yet handle inheritance of constraints from parent
> to child tables via inheritance.
>
>
> Was it done by design or was it a limitation we couldn't get over?

My understanding of the lack of a full featured partitioning solution
(based on previous conversations with Tom Lane, Gregory Stark, and the
like) is that the current implementation was pieced together from
other portions of the system -- i.e. the moving parts on the backend
weren't built from the ground up with partitioning in mind.  I'm
currently working on a command line tool that will take a table name
along with a date/timestamp or integer based column on that table and
some optional parameters and write out range based partitions for that
table.  If you'd like I'll save this email and once I've got it stable
and well tested I'll put it up in a public repo somewhere.

Erik Jones

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

On Tue, Apr 29, 2008 at 06:31:30AM +0530, Gurjeet Singh wrote:
> On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones <erik@myemma.com> wrote:
> > Postgres doesn't yet handle inheritance of constraints from parent to
> > child tables via inheritance.

> Was it done by design or was it a limitation we couldn't get over?

Inheritence of most constraints works, just not unique constraints. The
problem of managing a unique index over multiple tables has not yet
been solved (it's a reasonably hard problem).

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Tue, Apr 29, 2008 at 11:47 AM, Martijn van Oosterhout <kleptog@svana.org> wrote:

I completely agree with the difficulty of the problem. One of the advantages of breaking up your data into partitions, as professed by Simon (I think) (and I agree), is that you have smaller indexes, which improve performance. And maybe having one huge index managing the uniqueness across partitioned data just defeats the idea of data partitioning!

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device

> And maybe having one huge index managing the uniqueness across partitioned
> data just defeats the idea of data partitioning!
Except when you want uniqueness across all partitions.

Karsten

--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

"Karsten Hilbert" <Karsten.Hilbert@gmx.net> writes:
>> And maybe having one huge index managing the uniqueness across partitioned
>> data just defeats the idea of data partitioning!

> Except when you want uniqueness across all partitions.

Well, the point was that if the partitioning arrangement guarantees to
put distinct ranges of the key into distinct tables, then a separate
unique constraint on each table would suffice to guarantee global
uniqueness.

You can set up such a thing today, but it's a manual jury-rigged affair.
An automatic partitioning system would be a lot nicer.

            regards, tom lane

Gurjeet Singh wrote:
>  One of the advantages
> of breaking up your data into partitions, as professed by Simon (I think)
> (and I agree), is that you have smaller indexes, which improve performance.
> And maybe having one huge index managing the uniqueness across partitioned
> data just defeats the idea of data partitioning!

Isn't "large indexes are a performance problem" just saying
"we don't implement indexes very well"?   And why are they
a problem - surely a tree-structured index is giving you
range-partitioned subsets as you traverse it?  Why is this
different from manual partitioning into (inherited) tables?

Thanks,
     Jeremy

Because people can be smarter about the data partitioning.

Consider a table of users. Some are active, most are not. The active
users account for nearly all of the users table access, but I still
(occasionally) want to access info about the inactive users.
Partitioning users into active_users and inactive_users allows me to
tell the database (indirectly) that the active users index should stay
in memory, while the inactive users can relegated to disk.

-Nathan

On Thu, May 1, 2008 at 6:02 AM, Jeremy Harris <jgh@wizmail.org> wrote:
> Gurjeet Singh wrote:
>
> >  One of the advantages
> > of breaking up your data into partitions, as professed by Simon (I think)
> > (and I agree), is that you have smaller indexes, which improve
> performance.
> > And maybe having one huge index managing the uniqueness across partitioned
> > data just defeats the idea of data partitioning!
> >
>
>  Isn't "large indexes are a performance problem" just saying
>  "we don't implement indexes very well"?   And why are they
>  a problem - surely a tree-structured index is giving you
>  range-partitioned subsets as you traverse it?  Why is this
>  different from manual partitioning into (inherited) tables?
>
>  Thanks,
>     Jeremy
>
>
>
>  --
>  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-general
>

Nathan Boley wrote:
> Because people can be smarter about the data partitioning.
>
> Consider a table of users. Some are active, most are not. The active
> users account for nearly all of the users table access, but I still
> (occasionally) want to access info about the inactive users.
> Partitioning users into active_users and inactive_users allows me to
> tell the database (indirectly) that the active users index should stay
> in memory, while the inactive users can relegated to disk.
>
> -Nathan
>
> On Thu, May 1, 2008 at 6:02 AM, Jeremy Harris <jgh@wizmail.org> wrote:
>> Gurjeet Singh wrote:
>>
>>>  One of the advantages
>>> of breaking up your data into partitions, as professed by Simon (I think)
>>> (and I agree), is that you have smaller indexes, which improve
>> performance.
>>> And maybe having one huge index managing the uniqueness across partitioned
>>> data just defeats the idea of data partitioning!
>>>
>>  Isn't "large indexes are a performance problem" just saying
>>  "we don't implement indexes very well"?   And why are they
>>  a problem - surely a tree-structured index is giving you
>>  range-partitioned subsets as you traverse it?  Why is this
>>  different from manual partitioning into (inherited) tables?

Agreed, data placement is one reason for partitioning.  But won't
this happen automatically?  Won't, in your example, the active
part of a one-large-index stay in memory while the inactive parts
get pushed out?

Cheers,
   Jeremy

"Nathan Boley" <npboley@gmail.com> writes:

> Because people can be smarter about the data partitioning.
>
> Consider a table of users. Some are active, most are not. The active
> users account for nearly all of the users table access, but I still
> (occasionally) want to access info about the inactive users.
> Partitioning users into active_users and inactive_users allows me to
> tell the database (indirectly) that the active users index should stay
> in memory, while the inactive users can relegated to disk.

(Someone's going to mumble something about partial indexes here.)

The 50,000 ft view of partitioning is it:

a) Lets the database do some work in query plan time instead of at run-time.
   So yes, an index would let you skip scanning parts of the table but you
   still have to do a few comparisons and page accesses on your index at
   run-time. On a partitioned table you do that same work (and it's harder)
   but at plan time.

b) Lets you partition based on a key which isn't indexed at all. Consider in
   the above scenario if you then run a query across *all* active users. Even
   partial indexes won't be very fast but a partitioned table can do a
   sequential scan of a single partition.

c) Makes loading pre-organized segments of data and dropping segments O(1)
   which is makes the data much more manageable.

It's really (c) which is the killer app for partitioned tables. (a) and (b)
are usually just nice side-shows.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!