On Saturday 10 March 2007 00:13, NikhilS wrote:
> Hi,
>
> > > Given that Simon wants to do away with having the master table APPENDed
> >
> > in
> >
> > > the planning phase, this would be better.
> >
> > ISTM you're trading appending the master table for appending the DUMP
> > partition, which afa
Hi,
> Given that Simon wants to do away with having the master table APPENDed
in
> the planning phase, this would be better.
>
ISTM you're trading appending the master table for appending the DUMP
partition, which afaict would give you no gain.
If there are entries in the master table, I th
Hi,
On 3/10/07, Hannu Krosing <[EMAIL PROTECTED]> wrote:
Ühel kenal päeval, R, 2007-03-09 kell 15:41, kirjutas Jim Nasby:
> On Mar 9, 2007, at 3:31 AM, Zeugswetter Andreas ADI SD wrote:
> >>> Since partition is inheritance-based, a simple DROP or "NO
> >> INHERIT"
> >>> will do the job to deal
On Friday 09 March 2007 01:23, NikhilS wrote:
> Hi,
>
> > This follows on from the suggestion I made - taken along the lines of
> > the subject "auto creation of partitions" where I suggested the syntax
> > of partition check(month of mydatecol) and have a new partition created
> > as data was ente
Ühel kenal päeval, R, 2007-03-09 kell 15:41, kirjutas Jim Nasby:
> On Mar 9, 2007, at 3:31 AM, Zeugswetter Andreas ADI SD wrote:
> >>> Since partition is inheritance-based, a simple DROP or "NO
> >> INHERIT"
> >>> will do the job to deal with the partition. Do we want to reinvent
> >>> additional
On Mar 9, 2007, at 3:31 AM, Zeugswetter Andreas ADI SD wrote:
Since partition is inheritance-based, a simple DROP or "NO
INHERIT"
will do the job to deal with the partition. Do we want to reinvent
additional syntax when these are around and are documented?
Well, if the syntax for adding a ne
On Fri, 2007-03-09 at 07:40 -0500, Luke Lonergan wrote:
> What happens to the data when the function is dropped or replaced?
>
Well, that wouldn't happen because you build in a dependency.
I'm not working on this, so don't expect lots of detail. The idea is
essentially to implement things the wa
Lonergan; Zeugswetter Andreas ADI SD; Peter
Eisentraut; pgsql-hackers@postgresql.org
Subject:Re: [HACKERS] Auto creation of Partitions
On Fri, 2007-03-09 at 11:48 +0530, NikhilS wrote:
> Hi,
>
> On 3/9/07, Shane Ambler <[EMAIL PROTECTED]> wrote:
>
> &
Hi,
Why would we support HASH partitions?
If you did, the full syntax for hash clusters should be supported.
In MySQL, Oracle, the syntax for HASH partitions seems to be similar to the
one mentioned. I do not know much about hash clusters though.
If we do the CHECK clauses like that then we
On Fri, 2007-03-09 at 11:48 +0530, NikhilS wrote:
> Hi,
>
> On 3/9/07, Shane Ambler <[EMAIL PROTECTED]> wrote:
>
> > Note to Nikhil: Make sure the new syntax doesn't prevent
> partitions from
> > being placed upon multiple tablespaces in some manner, at
>
> > Since partition is inheritance-based, a simple DROP or "NO
> INHERIT"
> > will do the job to deal with the partition. Do we want to reinvent
> > additional syntax when these are around and are documented?
>
> Well, if the syntax for adding a new partition eventually
> ends up as ALTER TA
Hi,
This follows on from the suggestion I made - taken along the lines of
the subject "auto creation of partitions" where I suggested the syntax
of partition check(month of mydatecol) and have a new partition created
as data was entered. With this scenario dropping the partition when it
was emp
Hi,
On 3/9/07, Shane Ambler <[EMAIL PROTECTED]> wrote:
> Note to Nikhil: Make sure the new syntax doesn't prevent partitions from
> being placed upon multiple tablespaces in some manner, at CREATE TABLE
> time.
What if the syntax was something like -
CREATE TABLE tabname (
...
...
Jim C. Nasby wrote:
On Thu, Mar 08, 2007 at 01:52:27PM +0530, NikhilS wrote:
I think it'd be great to make adding and removing partitions as
simple as ALTER TABLE. I don't think that DELETE should be the
mechanism to drop a partition, though. Again, DML statements
shouldn't be performing DDL.
Note to Nikhil: Make sure the new syntax doesn't prevent partitions from
being placed upon multiple tablespaces in some manner, at CREATE TABLE
time.
What if the syntax was something like -
CREATE TABLE tabname (
...
...
) PARTITION BY
HASH(expr)
| RANGE(expr)
| LIST(expr)
[PARTITIO
One other thought... a lot of this discussion seems to parallel the
debate from a while ago about whether SERIAL should act like a macro
(ie: it just sets everything up and users are free to monkey under the
hood afterwards), or whether it should be it's own 'closed-box'
construct.
Currently, we s
On Thu, Mar 08, 2007 at 10:12:30AM +, Gregory Stark wrote:
> "Jim Nasby" <[EMAIL PROTECTED]> writes:
>
> > One possibility would be to require 3 functions for a partitioned table: one
> > accepts the partitioning key and tells you what partition it's in, one that
> > tells you what the minimu
On Thu, Mar 08, 2007 at 01:52:27PM +0530, NikhilS wrote:
> >I think it'd be great to make adding and removing partitions as
> >simple as ALTER TABLE. I don't think that DELETE should be the
> >mechanism to drop a partition, though. Again, DML statements
> >shouldn't be performing DDL.
>
>
> Since
> > The hard part there is checking that the partition constraints are
> > distinct. If the partition constraints are added one at a time, you
> > can use the predicate testing logic to compare the to-be-added
> > partition's constraint against each of the already added
constraints.
> > That be
"Jim Nasby" <[EMAIL PROTECTED]> writes:
> One possibility would be to require 3 functions for a partitioned table: one
> accepts the partitioning key and tells you what partition it's in, one that
> tells you what the minimum partitioning key for a partition would be, and one
> that tells you wh
On Wed, 2007-03-07 at 21:27 -0700, Jim Nasby wrote:
> On Mar 7, 2007, at 3:26 AM, Simon Riggs wrote:
> > If you know that the constraints on each of the tables is distinct,
> > then
> > building a UNIQUE index on each of the partitions is sufficient to
> > prove
> > that all rows in the combine
On Wed, 2007-03-07 at 22:32 -0500, Luke Lonergan wrote:
> Andreas,
>
> On 3/7/07 11:45 AM, "Zeugswetter Andreas ADI SD" <[EMAIL PROTECTED]>
> wrote:
>
> > Whoa, do you have anything to back that up ?
>
> Sure - when we start to consider designs that implement advanced data
> management features,
Hi,
There are 2 other reasons to favor triggers though:
1) People (Josh Drake comes to mind) have found that if you get over
a tiny number of partitions, the performance of rules is abysmal.
2) I believe it should be possible to construct an update trigger
that allows you to perform updates t
Hi,
Maybe, the dummy entry could be extended to contain the bounds (max/min) for
each of the other involved partitions and they could be updated each time a
DML happens across the partitions. That ways, an update to a particular
partition needs to lock out the others, examine the dummy entries in
On Mar 7, 2007, at 3:26 AM, Simon Riggs wrote:
If you know that the constraints on each of the tables is distinct,
then
building a UNIQUE index on each of the partitions is sufficient to
prove
that all rows in the combined partitioned table are distinct also.
The hard part there is checking
On Mar 6, 2007, at 4:57 AM, NikhilS wrote:
iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified,
pass it on to the children tables.
Since we want to eventually support 'global' indexes, I think we need
to be really careful here. There's actually 3 possible cases:
1) Index* sh
On Mar 6, 2007, at 9:13 AM, Shane Ambler wrote:
NikhilS wrote:
On 3/6/07, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
1) Whether we should use triggers/rules for step number (iii) above.
> Maybe rules is the way to go.
Since this would basically be a case of the updatable rules
problem, you
Andreas,
On 3/7/07 11:45 AM, "Zeugswetter Andreas ADI SD" <[EMAIL PROTECTED]>
wrote:
> Whoa, do you have anything to back that up ?
Sure - when we start to consider designs that implement advanced data
management features, we run into problems with the architecture of
"tables->tables->tables..."
>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] On Behalf Of Alvaro Herrera
>Sent: woensdag 7 maart 2007 15:59
>To: NikhilS
>Cc: Zeugswetter Andreas ADI SD; Peter Eisentraut;
>pgsql-hackers@postgresql.org
>Subject: Re: [HACKERS] Auto creati
> > What is really needed is a data structure that allows range
partitions
> > to be accessed more efficiently. This could make adding partitions
and
> > deciding in which partition a specific value goes an O(logN)
operation.
>
> I think we need to re-evaluate the inheritance mechanism for
> p
On Wed, 2007-03-07 at 10:23 -0500, Luke Lonergan wrote:
> Simon,
>
> On 3/7/07 5:26 AM, "Simon Riggs" <[EMAIL PROTECTED]> wrote:
>
> > What is really needed is a data structure that allows range partitions
> > to be accessed more efficiently. This could make adding partitions and
> > deciding in
Simon,
On 3/7/07 5:26 AM, "Simon Riggs" <[EMAIL PROTECTED]> wrote:
> What is really needed is a data structure that allows range partitions
> to be accessed more efficiently. This could make adding partitions and
> deciding in which partition a specific value goes an O(logN) operation.
I think w
I am wondering if we can implement unique indexes across several tables
(inheritance hierarchy) not by using a single, big index covering all
the tables, but rather by inserting a dummy entry into each partition's
unique index. This dummy entry would have an expanded CTID which would
include the t
Hi,
On 3/7/07, Zeugswetter Andreas ADI SD <[EMAIL PROTECTED]> wrote:
> > iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified,
> > pass it on to the children tables.
>
> How will you maintain a primary key in such a table,
> considering that indexes can't span multiple tables?
M
> > iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified,
> > pass it on to the children tables.
>
> How will you maintain a primary key in such a table,
> considering that indexes can't span multiple tables?
Many partitioning schemes have (or can be made to have) only one
possib
Hi,
If you know that the constraints on each of the tables is distinct, then
building a UNIQUE index on each of the partitions is sufficient to prove
that all rows in the combined partitioned table are distinct also.
The hard part there is checking that the partition constraints are
distinct. I
On Tue, 2007-03-06 at 18:31 +0530, NikhilS wrote:
> On 3/6/07, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> NikhilS wrote:
> > iv) Based on the PRIMARY, UNIQUE, REFERENCES information
> specified,
> > pass it on to the children tables.
>
> How will
Am Mittwoch, 7. März 2007 07:44 schrieb NikhilS:
> Sure, but as Chris mentioned earlier, wouldn't it be useful to maintain
> uniqueness on a
> partition-by-partition basis too?
Many things might be useful, but the aim of the "table partitioning" venture
is believed to be the provision of a transp
Maybe. The most obvious use for automatic partitioning that I can
think of would be based in the value of a timestamptz field rather
than any PK. Of course I tend to work more in the OLTP field than in
DW type apps, where other considerations might apply.
I second that - partitioning on so
Andrew Dunstan wrote:
Tom Lane wrote:
Peter Eisentraut <[EMAIL PROTECTED]> writes:
But when I say
CREATE TABLE ( a int PRIMARY KEY, ... ) PARTITION blah ...
then I expect that the primary key will be enforced across all
partitions. We currently sidestep that issue by not offering
seemingly
Hi,
On 3/7/07, Tom Lane <[EMAIL PROTECTED]> wrote:
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> But when I say
> CREATE TABLE ( a int PRIMARY KEY, ... ) PARTITION blah ...
> then I expect that the primary key will be enforced across all
> partitions. We currently sidestep that issue by not o
Hi Shane,
Maybe I'm looking at auto-maintenance which is beyond any current planning?
Many of your suggestions are useful, but auto-maintenance will be beyond the
current plan.
Regards,
Nikhils
EnterpriseDB http://www.enterprisedb.com
ject: Re: [HACKERS] Auto creation of Partitions
>
>Florian,
>
>> This sounds like what is really needed is a way to lock a certain
>> condition, namely the existance or non-existance of a record with
>> certain values in certain fields. This would not only help
>this c
Tom Lane wrote:
Peter Eisentraut <[EMAIL PROTECTED]> writes:
But when I say
CREATE TABLE ( a int PRIMARY KEY, ... ) PARTITION blah ...
then I expect that the primary key will be enforced across all
partitions. We currently sidestep that issue by not offering seemingly
transparent partition
> Agreed, it needs to Just Work. I think it'd still be useful though
> if we only support auto-partitioning on the primary key, and that
> restriction avoids the indexing problem.
+1
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
---(end of broadcast)
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> But when I say
> CREATE TABLE ( a int PRIMARY KEY, ... ) PARTITION blah ...
> then I expect that the primary key will be enforced across all
> partitions. We currently sidestep that issue by not offering seemingly
> transparent partitioning. But if
Florian,
> This sounds like what is really needed is a way to lock a certain
> condition, namely the existance or non-existance of a record with
> certain values in certain fields. This would not only help this case,
> it would also help RI triggers, because those wouldn't have to acquire
> a shar
NikhilS wrote:
> We will not (I know its a hard thing to do :) ), the intention is to
> use this information from the parent and make it a property of the
> child table. This will avoid the step for the user having to manually
> specify CREATE INDEX and the likes on all the children tables
> one-by
Martijn van Oosterhout wrote:
On Wed, Mar 07, 2007 at 02:43:39AM +1030, Shane Ambler wrote:
I think a way can be devised to maintain the primary key and unique
constraints.
If a search is done on the parent table, the planner knows to rewrite
the query as a union (or union all) of all child tab
On Wed, Mar 07, 2007 at 02:43:39AM +1030, Shane Ambler wrote:
> I think a way can be devised to maintain the primary key and unique
> constraints.
> If a search is done on the parent table, the planner knows to rewrite
> the query as a union (or union all) of all child tables that relate to
> th
[EMAIL PROTECTED] (Peter Eisentraut) writes:
> NikhilS wrote:
>> iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified,
>> pass it on to the children tables.
>
> How will you maintain a primary key in such a table, considering that
> indexes can't span multiple tables?
On the one han
NikhilS wrote:
Hi,
On 3/6/07, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
NikhilS wrote:
> iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified,
> pass it on to the children tables.
How will you maintain a primary key in such a table, considering that
indexes can't span multiple
Hi,
On 3/6/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
"NikhilS" <[EMAIL PROTECTED]> writes:
>the intention is to use this information from the parent and make it a
>property of the child table. This will avoid the step for the user having
to
>manually specify CREATE INDEX and the likes on al
On 3/6/07, NikhilS <[EMAIL PROTECTED]> wrote:
Hi,
On 3/6/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
>
> "NikhilS" <[EMAIL PROTECTED]> writes:
>
> >the intention is to use this information from the parent and make it a
> >property of the child table. This will avoid the step for the user
> hav
"NikhilS" <[EMAIL PROTECTED]> writes:
>the intention is to use this information from the parent and make it a
>property of the child table. This will avoid the step for the user having to
>manually specify CREATE INDEX and the likes on all the children tables
>one-by-one.
Missed the start of this
Hi,
On 3/6/07, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
NikhilS wrote:
> iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified,
> pass it on to the children tables.
How will you maintain a primary key in such a table, considering that
indexes can't span multiple tables?
We w
NikhilS wrote:
> iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified,
> pass it on to the children tables.
How will you maintain a primary key in such a table, considering that
indexes can't span multiple tables?
> 1) Whether we should use triggers/rules for step number (iii) abov
Hi,
This is to get feedback to meet the following TODO:
- Simplify ability to create partitioned tables
This would allow creation of partitioned tables without requiring
creation of rules for INSERT/UPDATE/DELETE, and constraints for rapid
partition selection. Options could include rang
58 matches
Mail list logo
