Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
Robert Haas wrote: > On Tue, Jun 7, 2011 at 11:41 AM, Tom Lane wrote: > > Robert Haas writes: > >> If your point here is that you don't want to spend time hacking on > >> this because it's a fairly marginal feature and therefore not terribly > >> high on your priority list, I can understand that. ?But if you're > >> actually defending the current implementation, I'm going to have to > >> respectfully disagree. ?It's broken, and it sucks, and this is not the > >> first complaint we've had about it. > > > > The spec's definition of USING is broken and sucky, and we're > > implementing it as best we can. ?I don't feel a need to invent > > strange nonstandard behavior to work around the fact that USING > > is fragile *by definition*. ?Complain to the standards committee > > about that. > > It's not the standard's committee's fault that our dump won't restore. > They may not have made life any easier for us, but if we're going to > have the feature, then pg_dump ought to work. Otherwise, we're > telling people "you can use this feature, but don't expect to be able > to restore from backup". Not a way to win friends. > > > (Question: would you also have us try to work around the fact that > > USING stops working if you rename one of the join columns?) > > Yeah. In fact, I proposed a patch to do just that in response to bug > #5234, which you shot down. I still don't agree with that. We can > either disallow the original DDL (adding or renaming a column in a way > that causes the dumped representation to become invalid) or we can > change what we dump so that it can be reloaded. Letting the user > change the view definition and then producing an unrestorable dump > seems truly awful to me, regardless of how little help we're getting > from the SQL standards committee. Reminder, pg_upgrade is also going to be unusuable if pg_dump generates an error, even on a view. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
On Tue, Jun 7, 2011 at 11:41 AM, Tom Lane wrote: > Robert Haas writes: >> If your point here is that you don't want to spend time hacking on >> this because it's a fairly marginal feature and therefore not terribly >> high on your priority list, I can understand that. But if you're >> actually defending the current implementation, I'm going to have to >> respectfully disagree. It's broken, and it sucks, and this is not the >> first complaint we've had about it. > > The spec's definition of USING is broken and sucky, and we're > implementing it as best we can. I don't feel a need to invent > strange nonstandard behavior to work around the fact that USING > is fragile *by definition*. Complain to the standards committee > about that. It's not the standard's committee's fault that our dump won't restore. They may not have made life any easier for us, but if we're going to have the feature, then pg_dump ought to work. Otherwise, we're telling people "you can use this feature, but don't expect to be able to restore from backup". Not a way to win friends. > (Question: would you also have us try to work around the fact that > USING stops working if you rename one of the join columns?) Yeah. In fact, I proposed a patch to do just that in response to bug #5234, which you shot down. I still don't agree with that. We can either disallow the original DDL (adding or renaming a column in a way that causes the dumped representation to become invalid) or we can change what we dump so that it can be reloaded. Letting the user change the view definition and then producing an unrestorable dump seems truly awful to me, regardless of how little help we're getting from the SQL standards committee. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
But as a PostgreSQL user, I would like to had a warning when creating a view with USING. It solves my problem. Maybe many others too. 2011/6/7 Robert Haas > On Fri, Jun 3, 2011 at 2:21 PM, Tom Lane wrote: > > Robert Haas writes: > >> On Fri, Jun 3, 2011 at 1:19 PM, Tom Lane wrote: > >>> Now, if the query doesn't involve any explicit reference to > "joinalias.*", > >>> we could probably fake it with some ugly thing involving > >>> COALESCE(leftcol, rightcol) ... but I don't think people will want to > >>> read that, and anyway the idea falls apart as soon as you do have a > >>> whole-row reference. > > > >> Well, it gets internally translated to COALESCE(leftcol, rightcol) > > > > We do that during planning; it's not the form that gets stored in views > > or dumped by pg_dump. I don't really want pg_dump dumping this kind of > > thing, because that locks us down to supporting it that way forever. > > Hmm. > > >> I'm not seeing the problem with whole-row references; can you elaborate > on that? > > > > SELECT somefunc(j.*) FROM (tab1 JOIN tab2 USING (id)) j; > > > > The shape of the record passed to somefunc() depends on removal of the > > second id column. > > Ah, yes. > > > Now you might claim that we could expand the j.* to a ROW() construct > > with an explicit list of columns, which indeed is what happens > > internally. But again, that happens at plan time, it's not what gets > > stored in rules. And that matters, because locking down the column > > expansion too early would break the response to ADD/DROP COLUMN on > > one of the input tables. > > Fair enough, but the current implementation with respect to ADD > COLUMN. And RENAME COLUMN. > > If your point here is that you don't want to spend time hacking on > this because it's a fairly marginal feature and therefore not terribly > high on your priority list, I can understand that. But if you're > actually defending the current implementation, I'm going to have to > respectfully disagree. It's broken, and it sucks, and this is not the > first complaint we've had about it. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Daniel Cristian Cruz クルズ クリスチアン ダニエル
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
Robert Haas writes: > If your point here is that you don't want to spend time hacking on > this because it's a fairly marginal feature and therefore not terribly > high on your priority list, I can understand that. But if you're > actually defending the current implementation, I'm going to have to > respectfully disagree. It's broken, and it sucks, and this is not the > first complaint we've had about it. The spec's definition of USING is broken and sucky, and we're implementing it as best we can. I don't feel a need to invent strange nonstandard behavior to work around the fact that USING is fragile *by definition*. Complain to the standards committee about that. (Question: would you also have us try to work around the fact that USING stops working if you rename one of the join columns?) regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
On Fri, Jun 3, 2011 at 2:21 PM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Jun 3, 2011 at 1:19 PM, Tom Lane wrote: >>> Now, if the query doesn't involve any explicit reference to "joinalias.*", >>> we could probably fake it with some ugly thing involving >>> COALESCE(leftcol, rightcol) ... but I don't think people will want to >>> read that, and anyway the idea falls apart as soon as you do have a >>> whole-row reference. > >> Well, it gets internally translated to COALESCE(leftcol, rightcol) > > We do that during planning; it's not the form that gets stored in views > or dumped by pg_dump. I don't really want pg_dump dumping this kind of > thing, because that locks us down to supporting it that way forever. Hmm. >> I'm not seeing the problem with whole-row references; can you elaborate on >> that? > > SELECT somefunc(j.*) FROM (tab1 JOIN tab2 USING (id)) j; > > The shape of the record passed to somefunc() depends on removal of the > second id column. Ah, yes. > Now you might claim that we could expand the j.* to a ROW() construct > with an explicit list of columns, which indeed is what happens > internally. But again, that happens at plan time, it's not what gets > stored in rules. And that matters, because locking down the column > expansion too early would break the response to ADD/DROP COLUMN on > one of the input tables. Fair enough, but the current implementation with respect to ADD COLUMN. And RENAME COLUMN. If your point here is that you don't want to spend time hacking on this because it's a fairly marginal feature and therefore not terribly high on your priority list, I can understand that. But if you're actually defending the current implementation, I'm going to have to respectfully disagree. It's broken, and it sucks, and this is not the first complaint we've had about it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
Alvaro Herrera wrote: > Would it be possible to dump USING as an equivalent ON condition > on output (nodeToString), and then examine simple ON conditions to > turn them into USING conditions on input (stringToNode)? You know, I'm not necessarily against some warning that a USING clause in a view definition makes that view more fragile in the face of schema changes. If we could invalidate the view at the time of the schema change, that would not offend me at all. The two things which would offend me here are generating a dump which can't be restored in a single transaction because of errors, or essentially deprecating the USING clause in general. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
Excerpts from Tom Lane's message of vie jun 03 10:59:00 -0400 2011: > I'm inclined to write this off as "so don't do that". There's nothing > that pg_dump can do to make this work: it has to use the USING syntax > for the join, and that doesn't offer any way to qualify the column name > on just one side. The only possible fix would be to try to make ALTER > TABLE reject the addition of the conflicting column name to "c" in the > first place. Would it be possible to dump USING as an equivalent ON condition on output (nodeToString), and then examine simple ON conditions to turn them into USING conditions on input (stringToNode)? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
Robert Haas writes: > On Fri, Jun 3, 2011 at 1:19 PM, Tom Lane wrote: >> Now, if the query doesn't involve any explicit reference to "joinalias.*", >> we could probably fake it with some ugly thing involving >> COALESCE(leftcol, rightcol) ... but I don't think people will want to >> read that, and anyway the idea falls apart as soon as you do have a >> whole-row reference. > Well, it gets internally translated to COALESCE(leftcol, rightcol) We do that during planning; it's not the form that gets stored in views or dumped by pg_dump. I don't really want pg_dump dumping this kind of thing, because that locks us down to supporting it that way forever. > I'm not seeing the problem with whole-row references; can you elaborate on > that? SELECT somefunc(j.*) FROM (tab1 JOIN tab2 USING (id)) j; The shape of the record passed to somefunc() depends on removal of the second id column. Now you might claim that we could expand the j.* to a ROW() construct with an explicit list of columns, which indeed is what happens internally. But again, that happens at plan time, it's not what gets stored in rules. And that matters, because locking down the column expansion too early would break the response to ADD/DROP COLUMN on one of the input tables. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
On Fri, Jun 3, 2011 at 1:19 PM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Jun 3, 2011 at 10:59 AM, Tom Lane wrote: >>> Personally my advice is to avoid USING: it wasn't one of the SQL >>> committee's better ideas. > >> I don't understand why we can't just translate the USING into some >> equivalent construct that doesn't involve USING. > > There is no directly equivalent construct, because USING changes the > shape of the output table (the join has one fewer output column than > would exist without USING). > > Now, if the query doesn't involve any explicit reference to "joinalias.*", > we could probably fake it with some ugly thing involving > COALESCE(leftcol, rightcol) ... but I don't think people will want to > read that, and anyway the idea falls apart as soon as you do have a > whole-row reference. Well, it gets internally translated to COALESCE(leftcol, rightcol) anyway, so I hardly think it's too ugly to print it out that way. It's what we're doing; more, it'll work instead of erroring out. This is not the first complaint we've gotten about this problem. I'm not seeing the problem with whole-row references; can you elaborate on that? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
I understood that USING is not a cool thing to use inside views, will avoid it. Still gonna use it in reports and applications, since there is no risk to affect backups and raises an error. It would be nice to had an NOTICE or WARNING about the USING clause inside VIEWs, saying that it's not recommended since a schema change would crash backups. 2011/6/3 Tom Lane > "Daniel Cristian Cruz" writes: > > CREATE TABLE a ( > > id_a serial primary key, > > v text > > ); > > CREATE TABLE b ( > > id_b serial primary key, > > id_a integer REFERENCES a (id_a), > > v text > > ); > > CREATE TABLE c ( > > id_c serial primary key, > > id_b integer references b (id_b), > > v text > > ); > > > CREATE VIEW cba AS > > SELECT c.v AS vc, b.v AS vb, a.v AS va > > FROM c > > JOIN b USING (id_b) > > JOIN a USING (id_a); > > > ALTER TABLE c ADD id_a integer; > > > [ view definition now fails due to multiple "id_a" columns ] > > I'm inclined to write this off as "so don't do that". There's nothing > that pg_dump can do to make this work: it has to use the USING syntax > for the join, and that doesn't offer any way to qualify the column name > on just one side. The only possible fix would be to try to make ALTER > TABLE reject the addition of the conflicting column name to "c" in the > first place. That doesn't seem very practical; it would require ALTER > TABLE to do a tremendous amount of analysis, and exclusively lock all > the dependent views, and then lock all the other tables used in the > views, and so on. > > Personally my advice is to avoid USING: it wasn't one of the SQL > committee's better ideas. > >regards, tom lane > -- Daniel Cristian Cruz クルズ クリスチアン ダニエル
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
"Kevin Grittner" writes: >> Tom Lane wrote: >>> Personally my advice is to avoid USING: it wasn't one of the SQL >>> committee's better ideas. > There's no query you can write with USING that you can't write in a > longer form with ON; but a query of moderate complexity can become > significantly shorter with USING, and thus easier to understand and > less prone to bugs than the ON form. If USING (x) actually were exactly equivalent to ON (a.x = b.x), I'd be less annoyed with it. But it isn't; the committee just couldn't resist the urge to decorate it with some dubious additional behaviors. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
Robert Haas wrote: > Tom Lane wrote: >> Personally my advice is to avoid USING: it wasn't one of the SQL >> committee's better ideas. > > I don't understand why we can't just translate the USING into some > equivalent construct that doesn't involve USING. I proposed that > a while ago and you shot it down, but I didn't find the reasoning > very compelling. There's no query you can write with USING that you can't write in a longer form with ON; but a query of moderate complexity can become significantly shorter with USING, and thus easier to understand and less prone to bugs than the ON form. I think it's a mistake to discourage USING or under-support it compared to the more verbose constructs. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
Robert Haas writes: > On Fri, Jun 3, 2011 at 10:59 AM, Tom Lane wrote: >> Personally my advice is to avoid USING: it wasn't one of the SQL >> committee's better ideas. > I don't understand why we can't just translate the USING into some > equivalent construct that doesn't involve USING. There is no directly equivalent construct, because USING changes the shape of the output table (the join has one fewer output column than would exist without USING). Now, if the query doesn't involve any explicit reference to "joinalias.*", we could probably fake it with some ugly thing involving COALESCE(leftcol, rightcol) ... but I don't think people will want to read that, and anyway the idea falls apart as soon as you do have a whole-row reference. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
On Fri, Jun 3, 2011 at 10:59 AM, Tom Lane wrote: > "Daniel Cristian Cruz" writes: >> CREATE TABLE a ( >> id_a serial primary key, >> v text >> ); >> CREATE TABLE b ( >> id_b serial primary key, >> id_a integer REFERENCES a (id_a), >> v text >> ); >> CREATE TABLE c ( >> id_c serial primary key, >> id_b integer references b (id_b), >> v text >> ); > >> CREATE VIEW cba AS >> SELECT c.v AS vc, b.v AS vb, a.v AS va >> FROM c >> JOIN b USING (id_b) >> JOIN a USING (id_a); > >> ALTER TABLE c ADD id_a integer; > >> [ view definition now fails due to multiple "id_a" columns ] > > I'm inclined to write this off as "so don't do that". There's nothing > that pg_dump can do to make this work: it has to use the USING syntax > for the join, and that doesn't offer any way to qualify the column name > on just one side. The only possible fix would be to try to make ALTER > TABLE reject the addition of the conflicting column name to "c" in the > first place. That doesn't seem very practical; it would require ALTER > TABLE to do a tremendous amount of analysis, and exclusively lock all > the dependent views, and then lock all the other tables used in the > views, and so on. > > Personally my advice is to avoid USING: it wasn't one of the SQL > committee's better ideas. I don't understand why we can't just translate the USING into some equivalent construct that doesn't involve USING. I proposed that a while ago and you shot it down, but I didn't find the reasoning very compelling. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6050: Dump and restore of view after a schema change: can't restore the view
"Daniel Cristian Cruz" writes: > CREATE TABLE a ( > id_a serial primary key, > v text > ); > CREATE TABLE b ( > id_b serial primary key, > id_a integer REFERENCES a (id_a), > v text > ); > CREATE TABLE c ( > id_c serial primary key, > id_b integer references b (id_b), > v text > ); > CREATE VIEW cba AS > SELECT c.v AS vc, b.v AS vb, a.v AS va > FROM c > JOIN b USING (id_b) > JOIN a USING (id_a); > ALTER TABLE c ADD id_a integer; > [ view definition now fails due to multiple "id_a" columns ] I'm inclined to write this off as "so don't do that". There's nothing that pg_dump can do to make this work: it has to use the USING syntax for the join, and that doesn't offer any way to qualify the column name on just one side. The only possible fix would be to try to make ALTER TABLE reject the addition of the conflicting column name to "c" in the first place. That doesn't seem very practical; it would require ALTER TABLE to do a tremendous amount of analysis, and exclusively lock all the dependent views, and then lock all the other tables used in the views, and so on. Personally my advice is to avoid USING: it wasn't one of the SQL committee's better ideas. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
