Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On 2017/05/08 12:42, Stephen Frost wrote: > Amit, > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: >> Thanks for committing the patch after improving it quite a bit, and sorry >> that I couldn't reply promptly during the last week due to vacation. > > No worries, hopefully you have an

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > Thanks for committing the patch after improving it quite a bit, and sorry > that I couldn't reply promptly during the last week due to vacation. No worries, hopefully you have an opportunity to review the additional changes I made and

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On 2017/05/06 12:28, Stephen Frost wrote: > Noah, > > On Fri, May 5, 2017 at 23:19 Noah Misch wrote: > >> On Thu, May 04, 2017 at 05:47:02PM -0400, Stephen Frost wrote: >>> * Amit Langote (amitlangot...@gmail.com) wrote: On Wed, May 3, 2017 at 12:05 PM,

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Noah, On Fri, May 5, 2017 at 23:19 Noah Misch wrote: > On Thu, May 04, 2017 at 05:47:02PM -0400, Stephen Frost wrote: > > * Amit Langote (amitlangot...@gmail.com) wrote: > > > On Wed, May 3, 2017 at 12:05 PM, Stephen Frost > wrote: > > > > Assuming this

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Thu, May 04, 2017 at 05:47:02PM -0400, Stephen Frost wrote: > * Amit Langote (amitlangot...@gmail.com) wrote: > > On Wed, May 3, 2017 at 12:05 PM, Stephen Frost wrote: > > > Assuming this looks good to you, I'll push it tomorrow, possibly with > > > other minor adjustments

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (amitlangot...@gmail.com) wrote: > On Wed, May 3, 2017 at 12:05 PM, Stephen Frost wrote: > > Assuming this looks good to you, I'll push it tomorrow, possibly with > > other minor adjustments and perhaps a few more tests. > > Your latest patch looks good

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (amitlangot...@gmail.com) wrote: > On Wed, May 3, 2017 at 12:05 PM, Stephen Frost wrote: > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > >> Attached updated patches. > > > > Please find an updated version which corrects the issue with > >

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On Wed, May 3, 2017 at 12:05 PM, Stephen Frost wrote: > Amit, > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: >> Attached updated patches. > > Please find an updated version which corrects the issue with > binary-upgrade of partitioned tables having

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > Attached updated patches. Please find an updated version which corrects the issue with binary-upgrade of partitioned tables having partitions in other schemas, along with a few other minor improvements. If you could take a look at

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > Now that WITH OPTIONS is optional even for CREATE TABLE OF, perhaps it > needs to be mentioned in the release notes? Doesn't strike me as rising to the level of needing to go into the release notes, but I won't object if people feel

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > Sorry about the delay. No worries, I'm just back from being in NY and will take a look at this tomorrow (wrt the open item, I'll provide a status tomorrow). Thanks! Stephen signature.asc Description: Digital signature

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, Sorry about the delay. On 2017/04/27 23:17, Stephen Frost wrote: > Amit, > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: So to summarize what the patches do (some of these were posted earlier) 0002: pg_dump: Do not emit WITH OPTIONS keywords with partition's

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Noah, * Noah Misch (n...@leadboat.com) wrote: > On Sun, Apr 23, 2017 at 11:58:23PM +, Stephen Frost wrote: > > The status is simply that I've been considering Robert's comments regarding > > the documentation and have had a busy weekend. I'll provide an update > > tomorrow. > > This

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Sun, Apr 23, 2017 at 11:58:23PM +, Stephen Frost wrote: > Noah, all, > > On Sun, Apr 23, 2017 at 19:52 Noah Misch wrote: > > > On Sat, Apr 22, 2017 at 01:14:08PM -0700, Noah Misch wrote: > > > On Thu, Apr 20, 2017 at 09:53:28PM -0400, Stephen Frost wrote: > > > > *

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > >> So to summarize what the patches do (some of these were posted earlier) > >> > >> 0002: pg_dump: Do not emit WITH OPTIONS keywords with partition's columns > > > > I'm trying to understand why this is also different. At least on

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On 2017/04/26 23:31, Stephen Frost wrote: >>> I looked through >>> pg_get_partkeydef() and it didn't seem to be particularly expensive to >>> run, though evidently it doesn't handle being passed an OID that it >>> doesn't expect very cleanly: >>> >>> =# select pg_get_partkeydef(oid)

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > On 2017/04/26 0:42, Stephen Frost wrote: > > I'm not sure what you mean here. We're always going to call both > > getInherits() and getPartitions() and run the queries in each, with the > > way the code is written today. In my

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On 2017/04/26 0:42, Stephen Frost wrote: > Amit, > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: >> I think why getPartitions() is separate from getInherits() and then >> flagPartitions() separate from flagInhTables() is because I thought >> originally that mixing the two

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Tue, Apr 25, 2017 at 12:26 PM, Stephen Frost wrote: > Interesting. Seems like the question is really what we mean by "ONLY" > here. For my 2c, at least, if we can check that all of the partitions > already have the constraint enforced, such that the only thing we're >

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Mon, Apr 24, 2017 at 9:17 AM, Stephen Frost wrote: > > I wonder why the restriction is there, which is probably part of the > > reason that I'm thinking of phrasing the documentation that way. > > > > Beyond a matter

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > I think why getPartitions() is separate from getInherits() and then > flagPartitions() separate from flagInhTables() is because I thought > originally that mixing the two would be undesirable. In the partitioning > case,

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Mon, Apr 24, 2017 at 9:17 AM, Stephen Frost wrote: > I wonder why the restriction is there, which is probably part of the > reason that I'm thinking of phrasing the documentation that way. > > Beyond a matter of round to-its, is there a reason why it couldn't (or >

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Fri, Apr 21, 2017 at 1:43 AM, Stephen Frost wrote: > >> + Once > >> + partitions exist, we do not support using ONLY > >> to > >> + add or drop constraints on only the partitioned table. > >> > >> I wonder

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Noah, all, On Sun, Apr 23, 2017 at 19:52 Noah Misch wrote: > On Sat, Apr 22, 2017 at 01:14:08PM -0700, Noah Misch wrote: > > On Thu, Apr 20, 2017 at 09:53:28PM -0400, Stephen Frost wrote: > > > * Noah Misch (n...@leadboat.com) wrote: > > > > On Mon, Apr 17, 2017 at 03:41:25PM

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Sat, Apr 22, 2017 at 01:14:08PM -0700, Noah Misch wrote: > On Thu, Apr 20, 2017 at 09:53:28PM -0400, Stephen Frost wrote: > > * Noah Misch (n...@leadboat.com) wrote: > > > On Mon, Apr 17, 2017 at 03:41:25PM -0400, Stephen Frost wrote: > > > > I've put up a new patch for review on the thread and

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Thu, Apr 20, 2017 at 09:53:28PM -0400, Stephen Frost wrote: > * Noah Misch (n...@leadboat.com) wrote: > > On Mon, Apr 17, 2017 at 03:41:25PM -0400, Stephen Frost wrote: > > > I've put up a new patch for review on the thread and plan to commit > > > that tomorrow, assuming there isn't anything

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Fri, Apr 21, 2017 at 1:43 AM, Stephen Frost wrote: >> + Once >> + partitions exist, we do not support using ONLY to >> + add or drop constraints on only the partitioned table. >> >> I wonder if the following sounds a bit more informative: Once partitions >>

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On 2017/04/21 8:43, Stephen Frost wrote: > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: >> On 2017/04/18 1:43, Stephen Frost wrote: >>> Please take a look at the attached and let me know your thoughts on it. >>> I changed the code to complain again regarding TRUNCATE ONLY,

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Greetings, * Noah Misch (n...@leadboat.com) wrote: > On Mon, Apr 17, 2017 at 03:41:25PM -0400, Stephen Frost wrote: > > I've put up a new patch for review on the thread and plan to commit > > that tomorrow, assuming there isn't anything further. That should > > resolve the immediate issue, but I

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > On 2017/04/18 1:43, Stephen Frost wrote: > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > >> OK, I agree. I tweaked the existing bullet point about differences from > >> traditional inheritance when using ONLY with

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Mon, Apr 17, 2017 at 03:41:25PM -0400, Stephen Frost wrote: > * Noah Misch (n...@leadboat.com) wrote: > > On Thu, Apr 13, 2017 at 11:38:08AM -0400, Robert Haas wrote: > > > On Thu, Apr 13, 2017 at 11:05 AM, Stephen Frost > > > wrote: > > > > Sure, though I won't be able to

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On 2017/04/18 1:43, Stephen Frost wrote: > Amit, > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: >> OK, I agree. I tweaked the existing bullet point about differences from >> traditional inheritance when using ONLY with partitioned tables. > > Please take a look at the

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Noah, all, * Noah Misch (n...@leadboat.com) wrote: > On Thu, Apr 13, 2017 at 11:38:08AM -0400, Robert Haas wrote: > > On Thu, Apr 13, 2017 at 11:05 AM, Stephen Frost wrote: > > > Sure, though I won't be able to today and I've got some doubts about the > > > other patches.

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > OK, I agree. I tweaked the existing bullet point about differences from > traditional inheritance when using ONLY with partitioned tables. Please take a look at the attached and let me know your thoughts on it. I changed the code to

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Thu, Apr 13, 2017 at 11:38:08AM -0400, Robert Haas wrote: > On Thu, Apr 13, 2017 at 11:05 AM, Stephen Frost wrote: > > Sure, though I won't be able to today and I've got some doubts about the > > other patches. I'll have more time tomorrow though. > > OK, cool. I'll mark

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On 2017/04/14 0:05, Stephen Frost wrote: > Robert, > > * Robert Haas (robertmh...@gmail.com) wrote: >> So I think I was indeed confused before, and I think you're basically >> right here, but on one point I think you are not right -- ALTER TABLE >> ONLY .. CHECK () doesn't work on a

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Thu, Apr 13, 2017 at 11:05 AM, Stephen Frost wrote: > Sure, though I won't be able to today and I've got some doubts about the > other patches. I'll have more time tomorrow though. OK, cool. I'll mark you down as the owner on the open items list. -- Robert Haas

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Robert, * Robert Haas (robertmh...@gmail.com) wrote: > So I think I was indeed confused before, and I think you're basically > right here, but on one point I think you are not right -- ALTER TABLE > ONLY .. CHECK () doesn't work on a table with inheritance children > regardless of whether the

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On 2017/04/13 6:22, Robert Haas wrote: > On Wed, Apr 12, 2017 at 3:29 PM, Stephen Frost wrote: >> I'm not following what you're getting at here. >> >> There's already a constraint on the table, and ALTER TABLE ONLY doesn't >> say anything about what happens later on (certainly

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Wed, Apr 12, 2017 at 3:29 PM, Stephen Frost wrote: > I'm not following what you're getting at here. > > There's already a constraint on the table, and ALTER TABLE ONLY doesn't > say anything about what happens later on (certainly it doesn't make new > tables created with

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Wed, Apr 12, 2017 at 3:29 PM, Stephen Frost wrote: > * Robert Haas (robertmh...@gmail.com) wrote: >> On Wed, Apr 12, 2017 at 6:29 AM, Amit Langote >> wrote: >> > Actually, p1 is a partitioned table, so the error. And I realize that >> >

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Wed, Apr 12, 2017 at 6:29 AM, Amit Langote > wrote: > > Actually, p1 is a partitioned table, so the error. And I realize that > > that's a wrong behavior. Currently the check is performed using only the >

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Wed, Apr 12, 2017 at 6:29 AM, Amit Langote wrote: > Actually, p1 is a partitioned table, so the error. And I realize that > that's a wrong behavior. Currently the check is performed using only the > relkind, which is bogus. Specifying ONLY should cause an

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On 2017/04/11 22:12, Stephen Frost wrote: > Amit, > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: >> On 2017/04/11 0:26, Robert Haas wrote: >>> Children can have constraints (including NOT NULL constraints) which >>> parents lack, and can have a different column order, but

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > On 2017/04/11 0:26, Robert Haas wrote: > > Children can have constraints (including NOT NULL constraints) which > > parents lack, and can have a different column order, but must have > > exactly the same column names and types. > >

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On 2017/04/11 0:26, Robert Haas wrote: > On Sun, Apr 9, 2017 at 10:10 PM, Tom Lane wrote: >> While I admit that I've not been paying close attention to the whole >> table partitioning business, I wonder whether we have any clearly written >> down specification about (a) how

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Sun, Apr 9, 2017 at 10:10 PM, Tom Lane wrote: > While I admit that I've not been paying close attention to the whole > table partitioning business, I wonder whether we have any clearly written > down specification about (a) how much partition member tables are allowed > to

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> While I admit that I've not been paying close attention to the whole >> table partitioning business, I wonder whether we have any clearly written >> down specification about (a) how much partition member tables

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Tom, Robert, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haas writes: > > I would appreciate help from other contributors and committers on this > > open item; pg_dump is not my strong point. In the absence of such > > help, I will do my best with it. I will set

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Robert Haas writes: > I would appreciate help from other contributors and committers on this > open item; pg_dump is not my strong point. In the absence of such > help, I will do my best with it. I will set aside time this week to > study this and send another update no

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Sun, Apr 9, 2017 at 7:50 PM, Noah Misch wrote: > The above-described topic is currently a PostgreSQL 10 open item. Robert, > since you committed the patch believed to have created it, you own this open > item. If some other commit is more relevant or if this does not

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Wed, Mar 29, 2017 at 05:38:41PM +0900, Amit Langote wrote: > On 2017/03/29 0:39, Robert Haas wrote: > > On Tue, Mar 28, 2017 at 6:50 AM, Amit Langote > > wrote: > >>> Isn't it bogus that this is generating ALTER TABLE .. SET NOT NULL > >>> columns at all? You

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On 2017/03/29 0:39, Robert Haas wrote: > On Tue, Mar 28, 2017 at 6:50 AM, Amit Langote > wrote: >>> Isn't it bogus that this is generating ALTER TABLE .. SET NOT NULL >>> columns at all? You didn't say anything like that when setting up the >>> database, so why

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Tue, Mar 28, 2017 at 6:50 AM, Amit Langote wrote: >> Isn't it bogus that this is generating ALTER TABLE .. SET NOT NULL >> columns at all? You didn't say anything like that when setting up the >> database, so why should it be there when dumping? > > So we should

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On 2017/03/27 23:30, Robert Haas wrote: > On Fri, Feb 17, 2017 at 3:23 AM, Amit Langote > wrote: >> In certain cases, pg_dump's dumpTableSchema() emits a separate ALTER TABLE >> command for those schema elements of a table that could not be included >> directly in

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On 2017/03/21 1:40, Stephen Frost wrote: > Amit, > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: >> On 2017/02/17 22:32, Stephen Frost wrote: >>> * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: In certain cases, pg_dump's dumpTableSchema() emits a separate ALTER

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

On Fri, Feb 17, 2017 at 3:23 AM, Amit Langote wrote: > In certain cases, pg_dump's dumpTableSchema() emits a separate ALTER TABLE > command for those schema elements of a table that could not be included > directly in the CREATE TABLE command for the table. > > For

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > On 2017/02/17 22:32, Stephen Frost wrote: > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > >> In certain cases, pg_dump's dumpTableSchema() emits a separate ALTER TABLE > >> command for those schema elements of a table that

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Hi Stephen, On 2017/02/17 22:32, Stephen Frost wrote: > Amit, > > * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: >> In certain cases, pg_dump's dumpTableSchema() emits a separate ALTER TABLE >> command for those schema elements of a table that could not be included >> directly in the

Re: [HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

Amit, * Amit Langote (langote_amit...@lab.ntt.co.jp) wrote: > In certain cases, pg_dump's dumpTableSchema() emits a separate ALTER TABLE > command for those schema elements of a table that could not be included > directly in the CREATE TABLE command for the table. Any chance we could start

[HACKERS] pg_dump emits ALTER TABLE ONLY partitioned_table

In certain cases, pg_dump's dumpTableSchema() emits a separate ALTER TABLE command for those schema elements of a table that could not be included directly in the CREATE TABLE command for the table. For example: create table p (a int, b int) partition by range (a); create table p1 partition of p