On Fri, 2007-04-06 at 16:08 +0200, Markus Schiltknecht wrote:
> Simon Riggs wrote:
> > i.e. if we have partitions for each year (2001, 2002, 2003 2004, 2005,
> > 2006, 2007) AND we have already proved that 2005 is excluded when we
> > have a WHERE clause saying year >= 2006, then we should be able
> David Fetter wrote:
> > On Fri, Apr 06, 2007 at 09:22:55AM -0700, Joshua D. Drake wrote:
> >
> >>> The people that use it are the people stuck by dogmatic rules about
> >>> "every table must have a primary key" or "every logical constraint
> >>> must be protected by a database constraint". Ie, d
David Fetter wrote:
On Fri, Apr 06, 2007 at 09:22:55AM -0700, Joshua D. Drake wrote:
The people that use it are the people stuck by dogmatic rules about
"every table must have a primary key" or "every logical constraint
must be protected by a database constraint". Ie, database shops run
by the
On Fri, Apr 06, 2007 at 09:22:55AM -0700, Joshua D. Drake wrote:
> >The people that use it are the people stuck by dogmatic rules about
> >"every table must have a primary key" or "every logical constraint
> >must be protected by a database constraint". Ie, database shops run
> >by the CYA princip
But if we could find a way to represent that it would make a lot of common use
cases much more convenient to use.
(But that sounds rather like pie in the sky, actually. Which other
databases can do that, and how do they do it?)
Oracle does it, by building a big index. Few people use it.
Th
Zeugswetter Andreas ADI SD wrote:
(But that sounds rather like pie in the sky, actually. Which other
databases can do that, and how do they do it?)
Oracle does it, by building a big index. Few people use it.
And others allow a different partitioning strategy for each index,
but that has the s
Simon Riggs wrote:
i.e. if we have partitions for each year (2001, 2002, 2003 2004, 2005,
2006, 2007) AND we have already proved that 2005 is excluded when we
have a WHERE clause saying year >= 2006, then we should be able to use
the ordering to prove that partitions for 2004 and before are also
On Fri, 2007-04-06 at 12:47 +0200, Zeugswetter Andreas ADI SD wrote:
> What I think we would like to have is putting the append nodes into an
> order that allows removing the sort node whenever that can be done.
> And
> maybe a merge node (that replaces the append and sort node) that can
> merge pr
> > (But that sounds rather like pie in the sky, actually. Which other
> > databases can do that, and how do they do it?)
>
> Oracle does it, by building a big index. Few people use it.
And others allow a different partitioning strategy for each index,
but that has the same problem of how to r
"Simon Riggs" <[EMAIL PROTECTED]> writes:
> If we partition on invoice_date only, there is an implication that
> people will search for invoices on date range only too, otherwise why
> not just partition on invoice_id. This still works with the compound key
> approach.
Well there are practical pr
On Fri, 2007-04-06 at 01:56 -0400, Tom Lane wrote:
> Markus Schiltknecht <[EMAIL PROTECTED]> writes:
> > Uh.. can you elaborate on that? AFAICS, you would simply have to query
> > multiple btree indexes and make sure non of them is violated.
>
> That only works for the partition-key indexes, ie,
Markus Schiltknecht <[EMAIL PROTECTED]> writes:
> Uh.. can you elaborate on that? AFAICS, you would simply have to query
> multiple btree indexes and make sure non of them is violated.
That only works for the partition-key indexes, ie, ones where you can be
sure a-priori that there cannot be dupl
On Thu, Apr 05, 2007 at 10:00:37PM +0200, Markus Schiltknecht wrote:
> >If someone manages to crack uniqueness for GiST indexes, we'll have our
> >answer, since it has exactly the same problem but on a different scale.
> >(Or vice-versa, if some gets uniqueness for multiple indexes, we can do
> >it
Hi,
Martijn van Oosterhout wrote:
The executor would have to be clever enough to not do a single index
scan, but possibly scan through multiple indexes when asking for
uniqueness, depending on the partitioning rule set.
But it's not the executor that checks uniqueness, it's built into the
btr
Hi,
Zeugswetter Andreas ADI SD wrote:
CREATE INDEX x ON test(a, b, c);
isn't the same as
CRETAE INDEX x ON test(c, b, a);
That is only a problem if you also want to avoid a sort (e.g. for an
order by),
..or if you want to use that index for 'WHERE a = 5'. The first one is
probably helpi
> > That lets you enforce unique constraints as long as the partition
key
> > is part of the unique constraint.
>
> Is that already sufficient?
yes
> That would alter the ordering of
> the columns in the index, no? I mean:
It produces ordered blocks of append nodes for range queries that spa
On Wednesday 04 April 2007 21:17, Gregory Stark wrote:
> "Simon Riggs" <[EMAIL PROTECTED]> writes:
> >> If we don't have multi-table indexes how do we enforce a primary key
> >> against a partitioned set? What about non primary keys that are just
> >> UNIQUE? What about check constraints that aren'
So we are unable to load any of the tables using COPY.
Aww, guess should have stuck to triggers as a first choice. Mea culpa, since
I should have investigated some more before deciding on rules, or should
have prodded you more earlier:)
Regards,
Nikhils
--
EnterpriseDB http://
Hi,
> I had raised this issue about rules/triggers back then and the
> responses seemed to be evenly split as to which ones to use.
Presumably your implementation already uses Triggers for INSERTs though,
so why not use triggers for everything?
No I am using rules for all the 3 cases. I am
On Thu, 2007-04-05 at 16:35 +0530, NikhilS wrote:
> Hi,
>
> > I had raised this issue about rules/triggers back then and
> the
> > responses seemed to be evenly split as to which ones to
> use.
>
> Presumably your implementation already us
On Thu, 2007-04-05 at 13:59 +0530, NikhilS wrote:
> Hi,
> > The only problem I have with this is that the shops I know
> with big
> > partitioned tables favor triggers over rules for both
> performance reason and
> > a cleaner implementation. Even with auto
Hi,
> The only problem I have with this is that the shops I know with big
> partitioned tables favor triggers over rules for both performance reason
and
> a cleaner implementation. Even with automated rule creation this isnt
going
> to change afaics... not to mention we already create our rules
On Wed, Apr 04, 2007 at 09:34:03PM +0200, Markus Schiltknecht wrote:
> Joshua D. Drake wrote:
> >If we don't have multi-table indexes how do we enforce a primary key
> >against a partitioned set?
>
> The executor would have to be clever enough to not do a single index
> scan, but possibly scan t
"Simon Riggs" <[EMAIL PROTECTED]> writes:
>> If we don't have multi-table indexes how do we enforce a primary key
>> against a partitioned set? What about non primary keys that are just
>> UNIQUE? What about check constraints that aren't apart of the exclusion?
>
> I can come up with arbitrary
Robert Treat wrote:
On Wednesday 04 April 2007 09:19, NikhilS wrote:
Our current partitioning solution is based on inheritance. With that in
mind, for 8.3 I thought an implementation based on auto rules creation
would be the way to go.
The only problem I have with this is that the shops I kno
On Wednesday 04 April 2007 09:19, NikhilS wrote:
> Our current partitioning solution is based on inheritance. With that in
> mind, for 8.3 I thought an implementation based on auto rules creation
> would be the way to go.
>
The only problem I have with this is that the shops I know with big
parti
On Wed, 2007-04-04 at 12:10 -0700, Joshua D. Drake wrote:
> Simon Riggs wrote:
> > On Wed, 2007-04-04 at 20:55 +0200, Markus Schiltknecht wrote:
> >
> >> Questioning the other way around: do we need any sort of multi-table
> >> indexes at all, or isn't it enough to teach the planner and executor
Hi,
Andrew Dunstan wrote:
> I guess my point was really that multi-table indexes might have uses
> beyond partitioning.
Aha, now I understand. Thanks for the clarification.
Say I have two tables, each with a field FKed to a field in a third
table. I'd like to create the values to be unique acr
Markus Schiltknecht wrote:
Andrew Dunstan wrote:
David Fetter wrote:
That would be where the provably-distinct part comes in, so yes.
That assumes you can provide some provably distinct test. In the
general case I have in mind that isn't so.
Could you please give a somewhat more concrete e
Andrew Dunstan wrote:
David Fetter wrote:
That would be where the provably-distinct part comes in, so yes.
That assumes you can provide some provably distinct test. In the general
case I have in mind that isn't so.
Could you please give a somewhat more concrete example, I'm not
following h
Simon Riggs wrote:
The planner already uses the Append node to put together multiple plans.
The great thing is it will put together IndexScans and SeqScans as
applicable. No need for multi-scans as a special node type.
Yes... only that mixing 'concurrent' index scans in the right order
would p
Hi,
Joshua D. Drake wrote:
If we don't have multi-table indexes how do we enforce a primary key
against a partitioned set?
The executor would have to be clever enough to not do a single index
scan, but possibly scan through multiple indexes when asking for
uniqueness, depending on the partit
David Fetter wrote:
On Wed, Apr 04, 2007 at 10:07:39AM -0400, Andrew Dunstan wrote:
Simon Riggs wrote:
My hope is to have a mechanism to partition indexes or recognise
that they are partitioned, so that a set of provably-distinct
unique indexes can provide the exact same functionlity as
Simon Riggs wrote:
On Wed, 2007-04-04 at 20:55 +0200, Markus Schiltknecht wrote:
Questioning the other way around: do we need any sort of multi-table
indexes at all, or isn't it enough to teach the planner and executor how
to intelligently scan through (possibly) multiple indexes to get what i
On Wed, 2007-04-04 at 20:55 +0200, Markus Schiltknecht wrote:
> Questioning the other way around: do we need any sort of multi-table
> indexes at all, or isn't it enough to teach the planner and executor how
> to intelligently scan through (possibly) multiple indexes to get what is
> requested?
Hi,
Gregory Stark wrote:
However there are also cases such as where you have a=0..99 in one partition
and a=100..199 in partition two, etc. It could still automatically build
indexes on (a,b,c) on each partition and somehow note that the unique
constraint is guaranteed across the whole partition
"Markus Schiltknecht" <[EMAIL PROTECTED]> writes:
> CREATE INDEX x ON test(a, b, c);
>
> That's why I'd say, the first column of an index would have to be equal to all
> of the columns used in the partitioning key.
That's certainly the simple case. It would be nice to be able to create an
index
On Wed, Apr 04, 2007 at 10:07:39AM -0400, Andrew Dunstan wrote:
> Simon Riggs wrote:
> >My hope is to have a mechanism to partition indexes or recognise
> >that they are partitioned, so that a set of provably-distinct
> >unique indexes can provide the exact same functionlity as a single
> >large un
Hi,
Simon Riggs wrote:
Most high volume tables are Fact tables with potentially more than 1 row
per Object/Dimension, so the unique index isn't appropriate in those
cases.
When partitioning a Major Entity its much easier to regard the PK as the
partitioning key + unique key, which is frequently
On Wed, 2007-04-04 at 16:31 +0200, Markus Schiltknecht wrote:
> But how to create an (unique) index which is completely different from
> the partitioning key?
Don't?
Most high volume tables are Fact tables with potentially more than 1 row
per Object/Dimension, so the unique index isn't appropri
Hi,
Gregory Stark wrote:
Put another way, multi-table indexes defeat the whole purpose of having
partitioned the table in the first place. If you could have managed a single
massive index then you wouldn't have bothered partitioning.
That depends very much on the implementation of the multi-ta
Hi,
Simon Riggs wrote:
I agree with much of your post, though this particular point caught my
eye. If you'll forgive me for jumping on an isolated point in your post:
No problem.
Multi-table indexes sound like a good solution until you consider how
big they would be. The reason we "need" a m
Hi,
NikhilS wrote:
Our current partitioning solution is based on inheritance. With that in
mind, for 8.3 I thought an implementation based on auto rules creation
would be the way to go.
That's completely reasonable. And as I've said, it's probably even a
step towards what I've outlined (auto
Simon Riggs wrote:
My hope is to have a mechanism to partition indexes or recognise that
they are partitioned, so that a set of provably-distinct unique indexes
can provide the exact same functionlity as a single large unique index,
just without the management nightmare.
Will this address t
"Simon Riggs" <[EMAIL PROTECTED]> writes:
> On Wed, 2007-04-04 at 14:20 +0200, Markus Schiltknecht wrote:
>> Both proposals do not have much to do with the missing multi-table
>> indices. It's clear to me that we have to implement those someday,
>> anyway.
>
> I agree with much of your post, thou
On Wed, 2007-04-04 at 14:20 +0200, Markus Schiltknecht wrote:
> Both proposals do not have much to do with the missing multi-table
> indices. It's clear to me that we have to implement those someday,
> anyway.
I agree with much of your post, though this particular point caught my
eye. If you'll f
Hi,
I appreciate you efforts, but I'm not sure if this has been discussed
Thanks Markus.
enough. There seem to be two ideas floating around:
- you are heading for automating the current kludge, which involves
creating partitions and constraints by hand. AFAICT, you want to
suppo
Hi,
NikhilS wrote:
The following things are TODOs:
iv) Auto generate rules using the checks mentioned for the partitions, to
handle INSERTs/DELETEs/UPDATEs to navigate them to the appropriate child.
Note that checks specified directly on the master table will get
inherited
automatically.
Am
Hi,
The following things are TODOs:
iv) Auto generate rules using the checks mentioned for the partitions, to
handle INSERTs/DELETEs/UPDATEs to navigate them to the appropriate child.
Note that checks specified directly on the master table will get inherited
automatically.
Am planning to do
49 matches
Mail list logo
