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 afaict would give you
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 TABLE ADD
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
CREATE
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
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:
Note to Nikhil: Make sure
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 way
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 new
Ü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 syntax when these
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 entered. With
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 with the
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
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
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
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, we run into
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 combined
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 what the
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 becomes an
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 partition
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 minimum
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
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)
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.
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 (
...
...
)
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
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
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 you
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.
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
possible
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?
Many
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
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 we need
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 which
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
-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 creation of Partitions
I am wondering
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 Here
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
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*
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
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
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) above.
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 will
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
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
having to
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 all the
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
[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 hand, I
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
the
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
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
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 share
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 you
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
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
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Josh Berkus
Sent: dinsdag 6 maart 2007 19:45
To: pgsql-hackers@postgresql.org
Cc: Florian G. Pflug; Martijn van Oosterhout; Shane Ambler;
NikhilS; Peter Eisentraut
Subject: Re: [HACKERS] Auto creation
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
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 offering
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
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
58 matches
Mail list logo
