Re: [HACKERS] Declarative partitioning - another take

On Mon, Dec 19, 2016 at 10:59 PM, Robert Haas wrote: > On Sun, Dec 18, 2016 at 10:00 PM, Amit Langote > wrote: >> Here are updated patches including the additional information. > > Thanks. Committed 0001. Will have to review the others when

Re: [HACKERS] Declarative partitioning - another take

Robert Haas wrote: > On Tue, Dec 20, 2016 at 10:27 AM, Alvaro Herrera > wrote: > > Even if we decide to keep the message, I think it's not very good > > wording anyhow; as a translator I disliked it on sight. Instead of > > "skipping scan to validate" I would use

Re: [HACKERS] Declarative partitioning - another take

On Tue, Dec 20, 2016 at 10:27 AM, Alvaro Herrera wrote: > Even if we decide to keep the message, I think it's not very good > wording anyhow; as a translator I disliked it on sight. Instead of > "skipping scan to validate" I would use "skipping validation scan", >

Re: [HACKERS] Declarative partitioning - another take

Robert Haas wrote: > On Tue, Dec 20, 2016 at 4:51 AM, Alvaro Herrera > wrote: > > Amit Langote wrote: > > > >> diff --git a/src/backend/commands/tablecmds.c > >> b/src/backend/commands/tablecmds.c > >> index 1c219b03dd..6a179596ce 100644 > >> ---

Re: [HACKERS] Declarative partitioning - another take

On Tue, Dec 20, 2016 at 4:51 AM, Alvaro Herrera wrote: > Amit Langote wrote: > >> diff --git a/src/backend/commands/tablecmds.c >> b/src/backend/commands/tablecmds.c >> index 1c219b03dd..6a179596ce 100644 >> --- a/src/backend/commands/tablecmds.c >> +++

Re: [HACKERS] Declarative partitioning - another take

Amit Langote wrote: > diff --git a/src/backend/commands/tablecmds.c > b/src/backend/commands/tablecmds.c > index 1c219b03dd..6a179596ce 100644 > --- a/src/backend/commands/tablecmds.c > +++ b/src/backend/commands/tablecmds.c > @@ -13297,8 +13297,10 @@ ATExecAttachPartition(List **wqueue,

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/20 12:59, Robert Haas wrote: > On Sun, Dec 18, 2016 at 10:00 PM, Amit Langote > wrote: >> Here are updated patches including the additional information. > > Thanks. Committed 0001. Will have to review the others when I'm less tired. Thanks! > BTW,

Re: [HACKERS] Declarative partitioning - another take

On Sun, Dec 18, 2016 at 10:00 PM, Amit Langote wrote: > Here are updated patches including the additional information. Thanks. Committed 0001. Will have to review the others when I'm less tired. BTW, elog() is only supposed to be used for can't happen error

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/17 11:32, Amit Langote wrote: > On Sat, Dec 17, 2016 at 1:07 AM, Robert Haas wrote: >> On Fri, Dec 16, 2016 at 3:02 AM, Amit Langote >> wrote: >>> Aside from the above, I found few other issues and fixed them in the >>> attached

Re: [HACKERS] Declarative partitioning - another take

On Sat, Dec 17, 2016 at 1:07 AM, Robert Haas wrote: > On Fri, Dec 16, 2016 at 3:02 AM, Amit Langote > wrote: >> Aside from the above, I found few other issues and fixed them in the >> attached patches. Descriptions follow: > > To avoid any

Re: [HACKERS] Declarative partitioning - another take

On Fri, Dec 16, 2016 at 3:02 AM, Amit Langote wrote: > Aside from the above, I found few other issues and fixed them in the > attached patches. Descriptions follow: To avoid any further mistakes on my part, can you please resubmit these with each patch file

Re: [HACKERS] Declarative partitioning - another take

Hi Dmitry, On 2016/12/16 0:40, Dmitry Ivanov wrote: > Hi everyone, > > Looks like "sql_inheritance" GUC is affecting partitioned tables: > > explain (costs off) select * from test; > QUERY PLAN -- > Append > -> Seq Scan on test > -> Seq Scan

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/16 17:38, Greg Stark wrote: > Just poking around with partitioning. I notice that "\d parent" > doesn't list all the partitions, suggesting to use \d+ but a plain > "\d" does indeed list the partitions. That seems a bit strange and > also probably impractical if you have hundreds or

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/16 17:02, Amit Langote wrote: > [PATCH 2/7] Change how RelationGetPartitionQual() and related code works > > Since we always want to recurse, ie, include the parent's partition > constraint (if any), get rid of the argument recurse. > > Refactor out the code doing the mapping of

Re: [HACKERS] Declarative partitioning - another take

Just poking around with partitioning. I notice that "\d parent" doesn't list all the partitions, suggesting to use \d+ but a plain "\d" does indeed list the partitions. That seems a bit strange and also probably impractical if you have hundreds or thousands of partitions. Has this come up in

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/14 1:32, Robert Haas wrote: > On Tue, Dec 13, 2016 at 1:58 AM, Amit Langote > wrote: >> Attaching the above patch, along with some other patches posted earlier, >> and one more patch fixing another bug I found. Patch descriptions follow: >> >>

Re: [HACKERS] Declarative partitioning - another take

Hi everyone, Looks like "sql_inheritance" GUC is affecting partitioned tables: explain (costs off) select * from test; QUERY PLAN -- Append -> Seq Scan on test -> Seq Scan on test_1 -> Seq Scan on test_2 -> Seq Scan on test_1_1 ->

Re: [HACKERS] Declarative partitioning - another take

Hi Amit, On 12/13/2016 09:45 AM, Amit Langote wrote: On 2016/12/13 0:17, Tomas Vondra wrote: On 12/12/2016 07:37 AM, Amit Langote wrote: Hi Tomas, On 2016/12/12 10:02, Tomas Vondra wrote: 2) I'm wondering whether having 'table' in the catalog name (and also in the new relkind) is too

Re: [HACKERS] Declarative partitioning - another take

Hi, On 13.12.2016 21:10, Robert Haas wrote: On Tue, Dec 13, 2016 at 12:22 PM, Ildar Musin wrote: We've noticed that PartitionDispatch object is built on every INSERT query and that it could create unnecessary overhead. Wouldn't it be better to keep it in relcache?

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/09 19:46, Maksim Milyutin wrote: I would like to work on two tasks: - insert (and eventually update) tuple routing for foreign partition. - the ability to create an index on the parent and have all of the children inherit it; The first one has been implemented in pg_pathman

Re: [HACKERS] Declarative partitioning - another take

On Mon, Dec 12, 2016 at 3:06 PM, Amit Langote wrote: > > Hi, > > On 2016/12/11 10:02, Venkata B Nagothi wrote: > > On Fri, Dec 9, 2016 at 11:11 PM, Amit Langote > > wrote: > >> On Fri, Dec 9, 2016 at 3:16 PM, Venkata B Nagothi

Re: [HACKERS] Declarative partitioning - another take

On Tue, Dec 13, 2016 at 12:22 PM, Ildar Musin wrote: > We've noticed that PartitionDispatch object is built on every INSERT query > and that it could create unnecessary overhead. Wouldn't it be better to keep > it in relcache? You might be able to cache some of that data

Re: [HACKERS] Declarative partitioning - another take

Hi hackers, On 08.12.2016 19:44, Dmitry Ivanov wrote: That would be fantastic. I and my colleagues at EnterpriseDB can surely help review; of course, maybe you and some of your colleagues would like to help review our patches, too. Certainly, I'll start reviewing as soon as I get familiar

Re: [HACKERS] Declarative partitioning - another take

On Tue, Dec 13, 2016 at 1:58 AM, Amit Langote wrote: > Attaching the above patch, along with some other patches posted earlier, > and one more patch fixing another bug I found. Patch descriptions follow: > > 0001-Miscallaneous-code-and-comment-improvements.patch >

Re: [HACKERS] Declarative partitioning - another take

On Fri, Dec 9, 2016 at 5:46 AM, Maksim Milyutin wrote: > I would like to work on two tasks: > - insert (and eventually update) tuple routing for foreign partition. > - the ability to create an index on the parent and have all of the children > inherit it; > > The

Re: [HACKERS] Declarative partitioning - another take

On Thu, Dec 8, 2016 at 11:58 PM, Amit Langote wrote: > Hierarchical lock manager stuff is interesting. Are you perhaps alluding > to a new *intention* lock mode as described in the literature on multiple > granularity locking [1]? Yes. -- Robert Haas

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/13 0:17, Tomas Vondra wrote: > On 12/12/2016 07:37 AM, Amit Langote wrote: >> >> Hi Tomas, >> >> On 2016/12/12 10:02, Tomas Vondra wrote: >>> >>> 2) I'm wondering whether having 'table' in the catalog name (and also in >>> the new relkind) is too limiting. I assume we'll have

Re: [HACKERS] Declarative partitioning - another take

Hi, On 2016/12/13 2:45, Dmitry Ivanov wrote: > Huh, this code is broken as well. We have to ignore partitions that don't > have any subpartitions. Patch is attached below (v2). Good catch and thanks a lot for the patch! I have revised it a bit and added some explanatory comments to that

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/12 23:14, Peter Eisentraut wrote: > On 12/7/16 1:20 PM, Robert Haas wrote: >> I've committed 0001 - 0006 with that correction and a few other >> adjustments. There's plenty of room for improvement here, and almost >> certainly some straight-up bugs too, but I think we're at a point >>

Re: [HACKERS] Declarative partitioning - another take

Huh, this code is broken as well. We have to ignore partitions that don't have any subpartitions. Patch is attached below (v2). -- Dmitry Ivanov Postgres Professional: http://www.postgrespro.com Russian Postgres Companydiff --git a/src/backend/catalog/partition.c

Re: [HACKERS] Declarative partitioning - another take

Hi guys, Looks like I've just encountered a bug. Please excuse me for the messy email, I don't have much time at the moment. Here's the test case: create table test(val int) partition by range (val); create table test_1 partition of test for values from (1) to (1000) partition by

Re: [HACKERS] Declarative partitioning - another take

On 12/12/2016 07:37 AM, Amit Langote wrote: Hi Tomas, On 2016/12/12 10:02, Tomas Vondra wrote: 2) I'm wondering whether having 'table' in the catalog name (and also in the new relkind) is too limiting. I assume we'll have partitioned indexes one day, for example - do we expect to use the

Re: [HACKERS] Declarative partitioning - another take

On 12/7/16 1:20 PM, Robert Haas wrote: > I've committed 0001 - 0006 with that correction and a few other > adjustments. There's plenty of room for improvement here, and almost > certainly some straight-up bugs too, but I think we're at a point > where it will be easier and less error-prone to

Re: [HACKERS] Declarative partitioning - another take

Hi Tomas, On 2016/12/12 10:02, Tomas Vondra wrote: > On 12/07/2016 07:20 PM, Robert Haas wrote: >> I've committed 0001 - 0006 with that correction and a few other >> adjustments. There's plenty of room for improvement here, and almost >> certainly some straight-up bugs too, but I think we're at

Re: [HACKERS] Declarative partitioning - another take

Hi, On 2016/12/11 10:02, Venkata B Nagothi wrote: > On Fri, Dec 9, 2016 at 11:11 PM, Amit Langote > wrote: >> On Fri, Dec 9, 2016 at 3:16 PM, Venkata B Nagothi >> wrote: >>> I am testing the partitioning feature from the latest master and got the >>>

Re: [HACKERS] Declarative partitioning - another take

Hi, On 12/07/2016 07:20 PM, Robert Haas wrote: > On Wed, Dec 7, 2016 at 11:53 AM, Erik Rijkers wrote: >>> My bad. The fix I sent last night for one of the cache flush issues >>> wasn't quite right. The attached seems to fix it. >> Yes, fixed here too. Thanks. > > Thanks for

Re: [HACKERS] Declarative partitioning - another take

Regards, Venkata B N Database Consultant On Fri, Dec 9, 2016 at 11:11 PM, Amit Langote wrote: > On Fri, Dec 9, 2016 at 3:16 PM, Venkata B Nagothi > wrote: > > Hi, > > > > I am testing the partitioning feature from the latest master and got the > >

Re: [HACKERS] Declarative partitioning - another take

On Fri, Dec 9, 2016 at 3:16 PM, Venkata B Nagothi wrote: > Hi, > > I am testing the partitioning feature from the latest master and got the > following error while loading the data - > > db01=# create table orders_y1993 PARTITION OF orders FOR VALUES FROM > ('1993-01-01') TO

Re: [HACKERS] Declarative partitioning - another take

Hi, everyone! 08.12.16 18:25, Robert Haas wrote: Of course, this is the beginning, not the end. I've been thinking about next steps -- here's an expanded list: - more efficient plan-time partition pruning (constraint exclusion is too slow) - run-time partition pruning - partition-wise join

Re: [HACKERS] Declarative partitioning - another take

Hi, I am testing the partitioning feature from the latest master and got the following error while loading the data - db01=# create table orders_y1993 PARTITION OF orders FOR VALUES FROM ('1993-01-01') TO ('1993-12-31'); CREATE TABLE db01=# copy orders from '/data/orders-1993.csv' delimiter

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/09 0:25, Robert Haas wrote: > On Wed, Dec 7, 2016 at 11:42 PM, Michael Paquier > wrote: >>> Congrats to everyone working on this! This is a large step forward. >> >> Congratulations to all! It was a long way to this result. > > Yes. The last effort in this

Re: [HACKERS] Declarative partitioning - another take

Hi Stephen, On 2016/12/08 22:35, Stephen Frost wrote: >>> * The fact that there's no implementation of row movement should be >>> documented as a limitation. We should also look at removing that >>> limitation. >> >> Yes, something to improve. By the way, since we currently mention INSERT >>

Re: [HACKERS] Declarative partitioning - another take

From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > On 2016/12/09 10:09, Tsunakawa, Takayuki wrote: > > Another requirement was subpartitioning. Will this be possible with the > current infrastructure, or does this need drastic change? > > It does support sub-partitioning, although the

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/09 10:09, Tsunakawa, Takayuki wrote: > Another requirement was subpartitioning. Will this be possible with the > current infrastructure, or does this need drastic change? It does support sub-partitioning, although the syntax is a bit different. Thanks, Amit -- Sent via

Re: [HACKERS] Declarative partitioning - another take

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Alexander > Korotkov > Yes. Getting at least some of this features committed to v10 would be great > and improve partitioning usability a lot. I'm sorry for not contributing to the real

Re: [HACKERS] Declarative partitioning - another take

On Thu, Dec 8, 2016 at 11:44 AM, Dmitry Ivanov wrote: >> That would be fantastic. I and my colleagues at EnterpriseDB can >> surely help review; of course, maybe you and some of your colleagues >> would like to help review our patches, too. > > Certainly, I'll start

Re: [HACKERS] Declarative partitioning - another take

On Thu, Dec 8, 2016 at 11:43 AM, Alexander Korotkov wrote: > Great! And it is very cool that we have basic infrastructure already > committed. Thanks a lot to you and everybody involved. Thanks. >> of course, maybe you and some of your colleagues >> would like to

Re: [HACKERS] Declarative partitioning - another take

That would be fantastic. I and my colleagues at EnterpriseDB can surely help review; of course, maybe you and some of your colleagues would like to help review our patches, too. Certainly, I'll start reviewing as soon as I get familiar with the code. Do you think this is likely to be

Re: [HACKERS] Declarative partitioning - another take

On Thu, Dec 8, 2016 at 7:29 PM, Robert Haas wrote: > On Thu, Dec 8, 2016 at 11:13 AM, Dmitry Ivanov > wrote: > > We (PostgresPro) have been working on pg_pathman for quite a while, and > > since it's obviously going to become the thing of the

Re: [HACKERS] Declarative partitioning - another take

On Thu, Dec 8, 2016 at 11:13 AM, Dmitry Ivanov wrote: > We (PostgresPro) have been working on pg_pathman for quite a while, and > since it's obviously going to become the thing of the past, it would be a > wasted effort if we didn't try to participate. > > For starters,

Re: [HACKERS] Declarative partitioning - another take

Hi everyone, Of course, this is the beginning, not the end. I've been thinking about next steps -- here's an expanded list: - more efficient plan-time partition pruning (constraint exclusion is too slow) - run-time partition pruning - try to reduce lock levels ... We (PostgresPro) have

Re: [HACKERS] Declarative partitioning - another take

On Wed, Dec 7, 2016 at 11:42 PM, Michael Paquier wrote: >> Congrats to everyone working on this! This is a large step forward. > > Congratulations to all! It was a long way to this result. Yes. The last effort in this area which I can remember was by Itagaki Takahiro

Re: [HACKERS] Declarative partitioning - another take

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > Hmm, I had mixed feeling about what to do about that as well. So now, we > have the description of various new features buried into VI. Reference > section of the documentation, which is simply meant as a command > reference. I agree

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/08 3:20, Robert Haas wrote: > On Wed, Dec 7, 2016 at 11:53 AM, Erik Rijkers wrote: >>> My bad. The fix I sent last night for one of the cache flush issues >>> wasn't quite right. The attached seems to fix it. >> Yes, fixed here too. Thanks. > > Thanks for the report

Re: [HACKERS] Declarative partitioning - another take

On Thu, Dec 8, 2016 at 1:39 PM, Andres Freund wrote: > On 2016-12-07 13:20:04 -0500, Robert Haas wrote: >> On Wed, Dec 7, 2016 at 11:53 AM, Erik Rijkers wrote: >> >> My bad. The fix I sent last night for one of the cache flush issues >> >> wasn't quite right.

Re: [HACKERS] Declarative partitioning - another take

On 2016-12-07 13:20:04 -0500, Robert Haas wrote: > On Wed, Dec 7, 2016 at 11:53 AM, Erik Rijkers wrote: > >> My bad. The fix I sent last night for one of the cache flush issues > >> wasn't quite right. The attached seems to fix it. > > Yes, fixed here too. Thanks. > > Thanks

Re: [HACKERS] Declarative partitioning - another take

Hi Robert, On 2016/12/08 3:20, Robert Haas wrote: > On Wed, Dec 7, 2016 at 11:53 AM, Erik Rijkers wrote: >>> My bad. The fix I sent last night for one of the cache flush issues >>> wasn't quite right. The attached seems to fix it. >> Yes, fixed here too. Thanks. > > Thanks

Re: [HACKERS] Declarative partitioning - another take

On 2016/12/08 1:53, Erik Rijkers wrote: > On 2016-12-07 17:38, Robert Haas wrote: >> On Wed, Dec 7, 2016 at 11:34 AM, Amit Langote >> wrote: begin; create schema if not exists s; create table s.t (c text, d text, id serial) partition by list

Re: [HACKERS] Declarative partitioning - another take

On Wed, Dec 7, 2016 at 11:53 AM, Erik Rijkers wrote: >> My bad. The fix I sent last night for one of the cache flush issues >> wasn't quite right. The attached seems to fix it. > Yes, fixed here too. Thanks. Thanks for the report - that was a good catch. I've committed 0001 -

Re: [HACKERS] Declarative partitioning - another take

On 2016-12-07 17:38, Robert Haas wrote: On Wed, Dec 7, 2016 at 11:34 AM, Amit Langote wrote: begin; create schema if not exists s; create table s.t (c text, d text, id serial) partition by list ((ascii(substring(coalesce(c, d, ''), 1, 1; create table

Re: [HACKERS] Declarative partitioning - another take

On Wed, Dec 7, 2016 at 11:34 AM, Amit Langote wrote: >> begin; >> create schema if not exists s; >> create table s.t (c text, d text, id serial) partition by list >> ((ascii(substring(coalesce(c, d, ''), 1, 1; >> create table s.t_part_ascii_065 partition of s.t for

Re: [HACKERS] Declarative partitioning - another take

Hi Erik, On Thu, Dec 8, 2016 at 1:19 AM, Erik Rijkers wrote: > On 2016-12-07 12:42, Amit Langote wrote: > >> 0001-Catalog-and-DDL-for-partitioned-tables-20.patch >> 0002-psql-and-pg_dump-support-for-partitioned-tables-20.patch >> 0003-Catalog-and-DDL-for-partitions-20.patch >>

Re: [HACKERS] Declarative partitioning - another take

On 2016-12-07 12:42, Amit Langote wrote: 0001-Catalog-and-DDL-for-partitioned-tables-20.patch 0002-psql-and-pg_dump-support-for-partitioned-tables-20.patch 0003-Catalog-and-DDL-for-partitions-20.patch 0004-psql-and-pg_dump-support-for-partitions-20.patch

Re: [HACKERS] Declarative partitioning - another take

On Wed, Dec 7, 2016 at 6:42 AM, Amit Langote wrote: > On 2016/12/07 13:38, Robert Haas wrote: >> On Wed, Nov 30, 2016 at 10:56 AM, Amit Langote >> wrote: >>> The latest patch I posted earlier today has this implementation. >> >> I decided

Re: [HACKERS] Declarative partitioning - another take

On Wed, Nov 30, 2016 at 10:56 AM, Amit Langote wrote: > The latest patch I posted earlier today has this implementation. I decided to try out these patches today with #define CLOBBER_CACHE_ALWAYS 1 in pg_config_manual.h, which found a couple of problems: 1.

Re: [HACKERS] Declarative partitioning - another take

On Thu, Dec 1, 2016 at 12:48 AM, Robert Haas wrote: > On Fri, Nov 25, 2016 at 5:49 AM, Amit Langote wrote: >> On 2016/11/25 11:44, Robert Haas wrote: >>> On Thu, Nov 24, 2016 at 6:13 AM, Amit Langote wrote: Also, it does nothing to help the undesirable situation that

Re: [HACKERS] Declarative partitioning - another take

On Tue, Nov 29, 2016 at 6:24 AM, Amit Langote wrote: > # All times in seconds (on my modestly-powerful development VM) > # > # nrows = 10,000,000 generated using: > # > # INSERT INTO $tab > # SELECT '$last'::date - ((s.id % $maxsecs + 1)::bigint || 's')::interval, >

Re: [HACKERS] Declarative partitioning - another take

On Fri, Nov 25, 2016 at 5:49 AM, Amit Langote wrote: > On 2016/11/25 11:44, Robert Haas wrote: >> On Thu, Nov 24, 2016 at 6:13 AM, Amit Langote wrote: >>> Also, it does nothing to help the undesirable situation that one can >>> insert a row with a null partition key

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/17 20:27, Amit Langote wrote: > On 2016/11/16 4:21, Robert Haas wrote: >> Have you done any performance testing on the tuple routing code? >> Suppose we insert a million (or 10 million) tuples into an >> unpartitioned table, a table with 10 partitions, a table with 100 >> partitions, a

Re: [HACKERS] Declarative partitioning - another take

Here are some comments on 0003 patch. 1. In ALTER TABLE documentation we should refer to CREATE TABLE documentation for partition_bound_spec syntax, similar ADD table_constraint note. 2. I think, "of the target table" is missing after all the ... constraints + match. Also, it must have all

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/25 11:44, Robert Haas wrote: > On Thu, Nov 24, 2016 at 6:13 AM, Amit Langote wrote: >> Also, it does nothing to help the undesirable situation that one can >> insert a row with a null partition key (expression) into any of the range >> partitions if targeted directly, because of how

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/25 13:51, Ashutosh Bapat wrote: >> >> I assume you meant "...right after the column name"? >> >> I will modify the grammar to allow that way then, so that the following >> will work: >> >> create table p1 partition of p ( >> a primary key >> ) for values in (1); >> > > That seems

Re: [HACKERS] Declarative partitioning - another take

> > I assume you meant "...right after the column name"? > > I will modify the grammar to allow that way then, so that the following > will work: > > create table p1 partition of p ( > a primary key > ) for values in (1); > That seems to be non-intuitive as well. The way it's written it

Re: [HACKERS] Declarative partitioning - another take

Amit Langote wrote: > On 2016/11/25 4:36, Alvaro Herrera wrote: > > I think CREATE TABLE OF is pretty much a corner case. I agree that > > allowing the constraint right after the constraint name is more > > intuitive. > > I assume you meant "...right after the column name"? Eh, right. --

Re: [HACKERS] Declarative partitioning - another take

On Thu, Nov 24, 2016 at 6:13 AM, Amit Langote wrote: > On 2016/11/23 4:50, Robert Haas wrote: >> On Tue, Nov 22, 2016 at 4:15 AM, Amit Langote >> wrote: The easiest thing to do might be to just enforce that all of the

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/25 4:36, Alvaro Herrera wrote: > Amit Langote wrote: >> On 2016/11/24 15:10, Ashutosh Bapat wrote: >>> On Thu, Nov 24, 2016 at 11:34 AM, Amit Langote wrote: > You have to specify column constraints using the keywords WITH OPTIONS, like below: create table p1

Re: [HACKERS] Declarative partitioning - another take

Amit Langote wrote: > On 2016/11/24 15:10, Ashutosh Bapat wrote: > > On Thu, Nov 24, 2016 at 11:34 AM, Amit Langote wrote: > >> You have to specify column constraints using the keywords WITH OPTIONS, > >> like below: > >> > >> create table p1 partition of p ( > >> a with options primary key >

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/23 4:50, Robert Haas wrote: > On Tue, Nov 22, 2016 at 4:15 AM, Amit Langote > wrote: >>> The easiest thing to do might be to just enforce that all of the >>> partition key columns have to be not-null when the range-partitioned >>> table is defined, and

Re: [HACKERS] Declarative partitioning - another take

On Thu, Nov 24, 2016 at 12:02 PM, Amit Langote wrote: > On 2016/11/24 15:10, Ashutosh Bapat wrote: >> On Thu, Nov 24, 2016 at 11:34 AM, Amit Langote wrote: >>> On 2016/11/24 14:35, Ashutosh Bapat wrote: IIUC, it should allow "create table t1_p1 partition of t1

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/24 15:10, Ashutosh Bapat wrote: > On Thu, Nov 24, 2016 at 11:34 AM, Amit Langote wrote: >> On 2016/11/24 14:35, Ashutosh Bapat wrote: >>> IIUC, it should allow "create table t1_p1 partition of t1 (a primary >>> key) ...", (a primary key) is nothing but "column_name >>>

Re: [HACKERS] Declarative partitioning - another take

On Thu, Nov 24, 2016 at 11:34 AM, Amit Langote wrote: > > Hi Ashutosh, > > On 2016/11/24 14:35, Ashutosh Bapat wrote: >> I am trying to create a partitioned table with primary keys on the >> partitions. Here's the corresponding syntax as per documentation >> CREATE

Re: [HACKERS] Declarative partitioning - another take

Hi Ashutosh, On 2016/11/24 14:35, Ashutosh Bapat wrote: > I am trying to create a partitioned table with primary keys on the > partitions. Here's the corresponding syntax as per documentation > CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ > IF NOT EXISTS ] table_name >

Re: [HACKERS] Declarative partitioning - another take

Unsurprisingly ALTER TABLE worked alter table t1_p1 add primary key(a); ALTER TABLE postgres=# \d+ t1_p1 Table "public.t1_p1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

Re: [HACKERS] Declarative partitioning - another take

I am trying to create a partitioned table with primary keys on the partitions. Here's the corresponding syntax as per documentation CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name PARTITION OF parent_table [ ( { column_name WITH OPTIONS [

Re: [HACKERS] Declarative partitioning - another take

On Tue, Nov 22, 2016 at 4:15 AM, Amit Langote wrote: >> The easiest thing to do might be to just enforce that all of the >> partition key columns have to be not-null when the range-partitioned >> table is defined, and reject any attempt to DROP NOT NULL on them >>

Re: [HACKERS] Declarative partitioning - another take

Hi Amit, I was just reading through your patches and here are some quick review comments for 0001-Catalog-and-DDL-for-partitioned-tables-17.patch. Review comments for 0001-Catalog-and-DDL-for-partitioned-tables-17.patch: 1) @@ -1102,9 +1104,10 @@ heap_create_with_catalog(const char *relname,

Re: [HACKERS] Declarative partitioning - another take

On Fri, Nov 18, 2016 at 5:59 AM, Amit Langote wrote: > Oh but wait, that means I can insert rows with NULLs in the range > partition key if I choose to insert it directly into the partition, > whereas I have been thinking all this while that there could never be >

Re: [HACKERS] Declarative partitioning - another take

On Thu, Nov 17, 2016 at 8:18 PM, Amit Langote wrote: > The reason NULLs in an input row are caught and rejected (with the current > message) before control reaches range_partition_for_tuple() is because > it's not clear to me whether the range bound comparison logic

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/18 1:43, Robert Haas wrote: > On Thu, Nov 17, 2016 at 6:27 AM, Amit Langote wrote: >> OK, I will share the performance results soon. > > Thanks. > >>> Also, in 0006: >>> >>> - I doubt that PartitionTreeNodeData's header comment will survive >>> contact with pgindent. >> >> Fixed by

Re: [HACKERS] Declarative partitioning - another take

On Thu, Nov 17, 2016 at 6:27 AM, Amit Langote wrote: > Meanwhile, here are updated patch that address most of the following comments. OK, I have re-reviewed 0005 and it looks basically fine to me, modulo a few minor nitpicks. "This is called, *iff*" shouldn't have

Re: [HACKERS] Declarative partitioning - another take

On Thu, Nov 17, 2016 at 6:27 AM, Amit Langote wrote: > OK, I will share the performance results soon. Thanks. >> Also, in 0006: >> >> - I doubt that PartitionTreeNodeData's header comment will survive >> contact with pgindent. > > Fixed by adding "/* " at the

Re: [HACKERS] Declarative partitioning - another take

On Tue, Nov 15, 2016 at 5:30 AM, Amit Langote wrote: > On 2016/11/11 19:30, Amit Langote wrote: >> >> Attached updated patches. > > Here is the latest version of the patches with some fixes along with those > mentioned below (mostly in 0003): > > - Fixed the logic

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/11 20:49, Ashutosh Bapat wrote: > I have not looked at the latest set of patches, but in the version > that I have we create one composite type for every partition. This > means that if there are thousand partitions, there will be thousand > identical entries in pg_type. Since all the

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/04 0:49, Robert Haas wrote: > On Thu, Nov 3, 2016 at 7:46 AM, wrote: >> El 2016-10-28 07:53, Amit Langote escribió: >>> @@ -6267,6 +6416,12 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, >>> Relation rel, >>> * Validity checks (permission checks

Re: [HACKERS] Declarative partitioning - another take

I forgot to quote your comments in the email I sent on Friday [1], with new patches that do take care of the following comments. On 2016/11/11 4:04, Robert Haas wrote: > On Thu, Nov 10, 2016 at 7:40 AM, Amit Langote >> >> OK, "partition key" and "partitioning method" it is then. Source code >>

Re: [HACKERS] Declarative partitioning - another take

I have not looked at the latest set of patches, but in the version that I have we create one composite type for every partition. This means that if there are thousand partitions, there will be thousand identical entries in pg_type. Since all the partitions share the same definition (by syntax), it

Re: [HACKERS] Declarative partitioning - another take

On Wed, Nov 9, 2016 at 9:58 PM, Amit Langote wrote: >> With all patches applied, "make check" fails with a bunch of diffs >> that look like this: >> >> Check constraints: >> - "pt1chk2" CHECK (c2 <> ''::text) >> "pt1chk3" CHECK (c2 <> ''::text) > > Hm, I

Re: [HACKERS] Declarative partitioning - another take

On Thu, Nov 10, 2016 at 7:40 AM, Amit Langote wrote: >> I think "partitioning key" is a bit awkward and actually prefer >> "partiton key". But "partition method" sounds funny so I would go >> with "partitioning method". > > OK, "partition key" and "partitioning

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/10 2:00, Robert Haas wrote: > In this latest patch set: > > src/backend/parser/parse_utilcmd.c:3194: indent with spaces. > +*rdatum; This one I will fix. > > With all patches applied, "make check" fails with a bunch of diffs > that look like this:

Re: [HACKERS] Declarative partitioning - another take

On Wed, Nov 9, 2016 at 12:00 PM, Robert Haas wrote: > In this latest patch set: > > src/backend/parser/parse_utilcmd.c:3194: indent with spaces. > +*rdatum; > > With all patches applied, "make check" fails with a bunch of diffs > that

<    1   2   3   4   >