Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Mon, Jun 02, 2014 at 01:29:25PM -0400, Robert Haas wrote: I agree, but I think it's important to note that Alex's complaint is not unique - the way things work now is a real source of frustration for users. In a previous job, I wrote a schema-upgrade script that dropped all of the views in reverse creation order, applied the schema updates, and then recreated all the views. This worked, but it was a lot of hassle that I would have preferred to avoid, and in a higher-volume application, simultaneously grabbing exclusive locks on a large number of critical views would have been a non-starter. In the job before that, I did the same thing manually, which was no fun at all. This was actually what posted me to write one of my first patches, committed by Bruce as ff1ea2173a92dea975d399a4ca25723f83762e55. Would it be sufficient to automatically pass the type change through only if nothing in the view actually references it in a function, operator, group by, order by, etc? That is, it only appears in the SELECT list unadorned? Or is that too limiting? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane t...@sss.pgh.pa.us wrote: I can see two answers. Answer #1 is that the column type of bar.a changes from int to bigint and the view definition is still SELECT a FROM foo. In that case, showing the user the SQL does not help them see and approve semantic changes because the SQL is completely unchanged. Yeah, we need some way of highlighting the semantic differences, and just printing ruleutils.c output doesn't do that. But if the user is going to put in a change to whatever choice the tool makes by default here, I would expect that change to consist of adding (or removing) an explicit cast in the SQL-text view definition. We can't make people learn some random non-SQL notation for this. Perhaps the displayed output of the tool could look something like CREATE VIEW bar AS SELECT a -- this view output column will now be of type int8 not int4 FROM foo; Or something else; I don't claim to be a good UI designer. But in the end, this is 90% a UI problem, and that means that raw SQL is seriously poorly suited to solve it directly. I guess I don't agree that is 90% a UI problem. There's currently no mechanism whatsoever by means of which a user can change the data type of a column upon which a view depends. If we had such a mechanism, then perhaps someone could build a UI providing the sort of user feedback you're suggesting to help them use it more safely. But isn't the core server support the first thing? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Tuesday, June 3, 2014, Robert Haas [via PostgreSQL] ml-node+s1045698n5805857...@n5.nabble.com wrote: On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane [hidden email] http://user/SendEmail.jtp?type=nodenode=5805857i=0 wrote: I can see two answers. Answer #1 is that the column type of bar.a changes from int to bigint and the view definition is still SELECT a FROM foo. In that case, showing the user the SQL does not help them see and approve semantic changes because the SQL is completely unchanged. Yeah, we need some way of highlighting the semantic differences, and just printing ruleutils.c output doesn't do that. But if the user is going to put in a change to whatever choice the tool makes by default here, I would expect that change to consist of adding (or removing) an explicit cast in the SQL-text view definition. We can't make people learn some random non-SQL notation for this. Perhaps the displayed output of the tool could look something like CREATE VIEW bar AS SELECT a -- this view output column will now be of type int8 not int4 FROM foo; Or something else; I don't claim to be a good UI designer. But in the end, this is 90% a UI problem, and that means that raw SQL is seriously poorly suited to solve it directly. I guess I don't agree that is 90% a UI problem. There's currently no mechanism whatsoever by means of which a user can change the data type of a column upon which a view depends. If we had such a mechanism, then perhaps someone could build a UI providing the sort of user feedback you're suggesting to help them use it more safely. But isn't the core server support the first thing? The current mechanism is DROP VIEWs - ALTER TABLE - CREATE VIEWs The UI would prompt the user for the desired ALTER TABLE parameters, calculate the DROP/CREATE commands, then issue all three sets as a single transaction. Having a more surgical REWRITE RULE command to alter a view without dropping it may provide for performance improvements but, conceptually, the current mechanism should be sufficient to allow for this tool to be developed. The main thing that core could do to help is to store as text of the original create view command - though it may be sufficient to reverse engineer from the rule. Having both available would give any tools more options. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Re-create-dependent-views-on-ALTER-TABLE-ALTER-COLUMN-TYPE-tp5804972p5805864.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane t...@sss.pgh.pa.us wrote: Or something else; I don't claim to be a good UI designer. But in the end, this is 90% a UI problem, and that means that raw SQL is seriously poorly suited to solve it directly. I guess I don't agree that is 90% a UI problem. There's currently no mechanism whatsoever by means of which a user can change the data type of a column upon which a view depends. Sure there is: I already illustrated it. You can temporarily set the view to some dummy definition that doesn't reference the target table, then do the ALTER COLUMN TYPE, then redefine the view the way you want. Wrap it up in a transaction, and it's even transparent. Now, what that doesn't do is let you change the output column type(s) of the view, but I'd argue that entirely independently of this problem it'd be reasonable for CREATE OR REPLACE VIEW to allow changing a column type if the view is unreferenced (ie, basically the same conditions under which a table column type can be changed today). If you want to argue that this is unnecessarily complex, you can do so, but claiming that it's not possible is simply false. I stand by the point that what we lack is a sane UI for helping in complex cases --- and nothing done behind-the-scenes in ALTER TABLE is going to qualify as a sane UI. The complexity in this approach would be easily hidden in a support tool, which will have much bigger problems to solve than whether its eventual command to the backend requires multiple SQL steps. If we had such a mechanism, then perhaps someone could build a UI providing the sort of user feedback you're suggesting to help them use it more safely. But isn't the core server support the first thing? I'm guessing you did not read http://www.postgresql.org/message-id/18723.1401734...@sss.pgh.pa.us regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Tue, Jun 3, 2014 at 10:14 AM, Tom Lane t...@sss.pgh.pa.us wrote: If we had such a mechanism, then perhaps someone could build a UI providing the sort of user feedback you're suggesting to help them use it more safely. But isn't the core server support the first thing? I'm guessing you did not read http://www.postgresql.org/message-id/18723.1401734...@sss.pgh.pa.us Argh, sorry, I saw that go by and it went past my eyes but obviously I didn't really absorb it. I guess we could do it that way. But it seems like quite a hassle to me; I think we're going to continue to get complaints here until this is Easy. And if it can't be made Easy, then we're going to continue to get complaints forever. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: On Tue, Jun 3, 2014 at 10:14 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm guessing you did not read http://www.postgresql.org/message-id/18723.1401734...@sss.pgh.pa.us Argh, sorry, I saw that go by and it went past my eyes but obviously I didn't really absorb it. I guess we could do it that way. But it seems like quite a hassle to me; I think we're going to continue to get complaints here until this is Easy. And if it can't be made Easy, then we're going to continue to get complaints forever. Well, my vision of it is that it *is* easy, if you're using the tool (or, perhaps, one of several tools), and you have a case that doesn't really require careful semantic review. But trying to build this sort of thing into the backend is the wrong approach: it's going to lead to unpleasant compromises and/or surprises. And we'd still have to build that tool someday. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Wed, May 28, 2014 at 8:22 AM, ash a...@commandprompt.com wrote: None of that involves answering hypothetical questions; but what you want to do does, and that I think is the problem in a nutshell. In a nutshell I'd like PostgreSQL to just re-parse the *current* view definition. Should that throw an error, user intervention will be required anyway, but most of the time it should just work. What exactly do you mean by re-parse the current view definition? The only form of the view definition we actually have is already parsed into an internal form (see pg_rewrite) which, for the reasons I've attempted to explain, is not easy to adapt to new column types. I suppose we could deparse that definition and then reparse the results, but that could lead to some very surprising consequences (some of which are security-relevant). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: On Wed, May 28, 2014 at 8:22 AM, ash a...@commandprompt.com wrote: None of that involves answering hypothetical questions; but what you want to do does, and that I think is the problem in a nutshell. In a nutshell I'd like PostgreSQL to just re-parse the *current* view definition. Should that throw an error, user intervention will be required anyway, but most of the time it should just work. What exactly do you mean by re-parse the current view definition? I mean do what the user will have to do in this situation anyway: BEGIN; DROP VIEW ...; ALTER TABLE ...; CREATE VIEW ...; COMMIT; Should this fail, the user will have to work around it, but most of the time it could just work. The only form of the view definition we actually have is already parsed into an internal form (see pg_rewrite) which, for the reasons I've attempted to explain, is not easy to adapt to new column types. I suppose we could deparse that definition and then reparse the results, but that could lead to some very surprising consequences (some of which are security-relevant). Like? -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: What exactly do you mean by re-parse the current view definition? The only form of the view definition we actually have is already parsed into an internal form (see pg_rewrite) which, for the reasons I've attempted to explain, is not easy to adapt to new column types. I suppose we could deparse that definition and then reparse the results, but that could lead to some very surprising consequences (some of which are security-relevant). Note that even if we had the original CREATE VIEW text, as well as the creation-time settings of all relevant GUCs (search_path being the most obvious, but not the only one), reparsing like this could still be risky; it's not the conversion to internal form that's the real issue here. A simple example is that x || 'foo' means something quite different if x is a tsvector than if x is text, and something different from those if x is an array, and something different yet again if x is bit varying. Some of those meanings are close enough that the user might have wanted the substitution, but others perhaps not. Or for another example, if we have foo(x) calling foo(int), and x's type is changed from int4 to int8, should we insert a cast so that the same foo() is still called? Or should we allow the called function to be replaced by foo(int8), foo(numeric), foo(float8), if one of those exist? I see the OP's point that the user is likely to try a naive manual conversion first, but at least then it's on his head to be aware of whether he got the semantics he wants. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Mon, Jun 2, 2014 at 8:52 AM, ash a...@commandprompt.com wrote: On Wed, May 28, 2014 at 8:22 AM, ash a...@commandprompt.com wrote: None of that involves answering hypothetical questions; but what you want to do does, and that I think is the problem in a nutshell. In a nutshell I'd like PostgreSQL to just re-parse the *current* view definition. Should that throw an error, user intervention will be required anyway, but most of the time it should just work. What exactly do you mean by re-parse the current view definition? I mean do what the user will have to do in this situation anyway: BEGIN; DROP VIEW ...; ALTER TABLE ...; CREATE VIEW ...; COMMIT; Should this fail, the user will have to work around it, but most of the time it could just work. You're either missing or choosing to ignore the point that I'm making, which is that we *don't have* the text form of the view anywhere. If you try to get implement what you're proposing, I'm fairly certain that you'll find that you can't. I agree that it would be nice if there were to make that just work; I've wished for it myself - but I don't see a reasonable way to implement it. The only form of the view definition we actually have is already parsed into an internal form (see pg_rewrite) which, for the reasons I've attempted to explain, is not easy to adapt to new column types. I suppose we could deparse that definition and then reparse the results, but that could lead to some very surprising consequences (some of which are security-relevant). Like? Tom's email covers this point, so I won't repeat what he said. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: On Mon, Jun 2, 2014 at 8:52 AM, ash a...@commandprompt.com wrote: Should this fail, the user will have to work around it, but most of the time it could just work. You're either missing or choosing to ignore the point that I'm making, which is that we *don't have* the text form of the view anywhere. Even if we did, I don't think it'd affect this decision. The real problem in my mind is one of user expectations. If the database silently does something behind your back, people expect that that action will be *right* and they don't have to worry about it. I don't think that automatically reparsing views has much chance of clearing that bar. In much simpler, non-extensible SQL systems it could probably work, but for better or worse Postgres has gone all-in on datatype extensibility. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Tom Lane t...@sss.pgh.pa.us writes: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 2, 2014 at 8:52 AM, ash a...@commandprompt.com wrote: Should this fail, the user will have to work around it, but most of the time it could just work. You're either missing or choosing to ignore the point that I'm making, which is that we *don't have* the text form of the view anywhere. Even if we did, I don't think it'd affect this decision. The real problem in my mind is one of user expectations. If the database silently does something behind your back, people expect that that action will be *right* and they don't have to worry about it. I don't think that automatically reparsing views has much chance of clearing that bar. In much simpler, non-extensible SQL systems it could probably work, but for better or worse Postgres has gone all-in on datatype extensibility. Alright, I think I can let it go now. It's just that the behavior was very counter-intuitive to me (and I guess a lot others) at first. Thanks all for your time and in-depth explanation! -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Mon, Jun 2, 2014 at 10:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 2, 2014 at 8:52 AM, ash a...@commandprompt.com wrote: Should this fail, the user will have to work around it, but most of the time it could just work. You're either missing or choosing to ignore the point that I'm making, which is that we *don't have* the text form of the view anywhere. Even if we did, I don't think it'd affect this decision. The real problem in my mind is one of user expectations. If the database silently does something behind your back, people expect that that action will be *right* and they don't have to worry about it. I don't think that automatically reparsing views has much chance of clearing that bar. I agree, but I think it's important to note that Alex's complaint is not unique - the way things work now is a real source of frustration for users. In a previous job, I wrote a schema-upgrade script that dropped all of the views in reverse creation order, applied the schema updates, and then recreated all the views. This worked, but it was a lot of hassle that I would have preferred to avoid, and in a higher-volume application, simultaneously grabbing exclusive locks on a large number of critical views would have been a non-starter. In the job before that, I did the same thing manually, which was no fun at all. This was actually what posted me to write one of my first patches, committed by Bruce as ff1ea2173a92dea975d399a4ca25723f83762e55. From a technical standpoint, I'm not very sure what to do to further improve the situation - which I will broadly characterize as view dependency hell - but if I did have such an idea I might be willing to take a modest risk of user confusion if it seemed likely to also reduce user frustration. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: On Mon, Jun 2, 2014 at 10:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: The real problem in my mind is one of user expectations. If the database silently does something behind your back, people expect that that action will be *right* and they don't have to worry about it. I don't think that automatically reparsing views has much chance of clearing that bar. I agree, but I think it's important to note that Alex's complaint is not unique - the way things work now is a real source of frustration for users. Oh, I quite agree with that. My concern here has to do with automatically and silently making changes that we can't be very sure will meet the user's expectations. Perhaps what we need is some kind of UI/API design whereby the user can inspect/modify/approve the semantic changes in advance of pushing the red button. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
* Robert Haas (robertmh...@gmail.com) wrote: On Mon, Jun 2, 2014 at 10:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: The real problem in my mind is one of user expectations. If the database silently does something behind your back, people expect that that action will be *right* and they don't have to worry about it. I don't think that automatically reparsing views has much chance of clearing that bar. [...] From a technical standpoint, I'm not very sure what to do to further improve the situation - which I will broadly characterize as view dependency hell - but if I did have such an idea I might be willing to take a modest risk of user confusion if it seemed likely to also reduce user frustration. Tom's point goes back to what I was trying to drive at originally- people should have to ask for this. Perhaps we can provide a way for them to ask which is explicit enough that they understand this might not do exactly what you think it does, akin to what happens today with a drop-and-recreate-everything approach. 'CASCADE' might not be sufficient to meet that, maybe 'CASCADE REBUILD' or something? Of course, there is a question about if it's worth it to keep around the exact text of each CREATE VIEW and build all this infrastructure for something which will only work properly in a specific subset of cases and in many others could break silently, essentially installing a very handy looking foot-gun. Not sure I like that either. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
* Tom Lane (t...@sss.pgh.pa.us) wrote: Robert Haas robertmh...@gmail.com writes: I agree, but I think it's important to note that Alex's complaint is not unique - the way things work now is a real source of frustration for users. Oh, I quite agree with that. My concern here has to do with automatically and silently making changes that we can't be very sure will meet the user's expectations. Perhaps what we need is some kind of UI/API design whereby the user can inspect/modify/approve the semantic changes in advance of pushing the red button. No clue how we'd manage to do that with just core, but could we provide something which would make it easier for pgAdmin-or-similar to do that? We could at least spit out NOTICE's and then see if the user decides to commit the change but I'm not sure how well that'd really work. In general, I like the idea.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On 06/02/2014 10:40 AM, Stephen Frost wrote: Tom's point goes back to what I was trying to drive at originally- people should have to ask for this. Perhaps we can provide a way for them to ask which is explicit enough that they understand this might not do exactly what you think it does, akin to what happens today with a drop-and-recreate-everything approach. 'CASCADE' might not be sufficient to meet that, maybe 'CASCADE REBUILD' or something? I think CASCADE is sufficient; what else could a user mean by ALTER TABLE ... CASCADE? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On 2014-06-02 10:48:02 -0700, Josh Berkus wrote: On 06/02/2014 10:40 AM, Stephen Frost wrote: Tom's point goes back to what I was trying to drive at originally- people should have to ask for this. Perhaps we can provide a way for them to ask which is explicit enough that they understand this might not do exactly what you think it does, akin to what happens today with a drop-and-recreate-everything approach. 'CASCADE' might not be sufficient to meet that, maybe 'CASCADE REBUILD' or something? I think CASCADE is sufficient; what else could a user mean by ALTER TABLE ... CASCADE? Please also change the table's children. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On 2014-06-02 13:40:32 -0400, Stephen Frost wrote: Of course, there is a question about if it's worth it to keep around the exact text of each CREATE VIEW and build all this infrastructure for something which will only work properly in a specific subset of cases and in many others could break silently, essentially installing a very handy looking foot-gun. Not sure I like that either. I don't think any solution that requires that is going to be workable. It'll break in far too many cases that currently are handled transparently. Starting with RENAME, followed by lots of other stuff. It'd also be very confusing because pg_dump doesn't use it... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Tom Lane wrote: Oh, I quite agree with that. My concern here has to do with automatically and silently making changes that we can't be very sure will meet the user's expectations. Perhaps what we need is some kind of UI/API design whereby the user can inspect/modify/approve the semantic changes in advance of pushing the red button. I think that instead of forcing the user to append a CASCADE keyword at the end of the command, it could perhaps return a bunch of commands to alter all views. The user would inspect those commands and fix those that need fixing, then rerun the whole bunch. I would imagine a UI similar to git rebase, which first gives you a list of things to do, which you can edit, and upon save-exit the final list of commands is executed. Any error during the execution abort the entire transaction, so if the user makes mistakes the thing is started afresh. If you have a complex maze of views, I think this (or something similar that gives enough leeway to the user) is the only way to enable a nontrivial alteration of one of the tables or views at the bottom. There is no way that we're going to allow automatic schema changes otherwise. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Mon, Jun 2, 2014 at 1:40 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: On Mon, Jun 2, 2014 at 10:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: The real problem in my mind is one of user expectations. If the database silently does something behind your back, people expect that that action will be *right* and they don't have to worry about it. I don't think that automatically reparsing views has much chance of clearing that bar. [...] From a technical standpoint, I'm not very sure what to do to further improve the situation - which I will broadly characterize as view dependency hell - but if I did have such an idea I might be willing to take a modest risk of user confusion if it seemed likely to also reduce user frustration. Tom's point goes back to what I was trying to drive at originally- people should have to ask for this. Well, my point is that we don't yet know what this is, so trying to decide on whether users should be forced to request it or in exactly what manner seems like putting the cart before the horse. We may well need some syntax, but it's too soon to decide what it is. FWIW, I don't think reparsing the original view-text is even remotely plausible. The fact that views stay glued to the same objects even of those objects are renamed is a pretty handy property of the current system, and any sort of reparse-from-scratch technique would give that up. Deparse-and-reparse might be better, but I'll bet that has too many problems to be viable, too (even if I haven't yet thought of what they are). For better or for worse, I think the best we're likely to be able to do is somehow manipulate the already-parsed rewrite rule. I don't have any great ideas about how to do that, either, but it seems less problematic than going back to the SQL representation. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Oh, I quite agree with that. My concern here has to do with automatically and silently making changes that we can't be very sure will meet the user's expectations. Perhaps what we need is some kind of UI/API design whereby the user can inspect/modify/approve the semantic changes in advance of pushing the red button. I think that instead of forcing the user to append a CASCADE keyword at the end of the command, it could perhaps return a bunch of commands to alter all views. The user would inspect those commands and fix those that need fixing, then rerun the whole bunch. I would imagine a UI similar to git rebase, which first gives you a list of things to do, which you can edit, and upon save-exit the final list of commands is executed. Any error during the execution abort the entire transaction, so if the user makes mistakes the thing is started afresh. I think we might be better off thinking about what support would we need to provide to allow a tool for this to be written? than how would we do this entirely inside the backend?. SQL does not deal in user interfaces very well, and we shouldn't try to make it do so. I doubt that we need much help from ALTER TABLE itself. I can envision the tool's red button emitting commands like this: begin; create or replace view v1 as select ... dummy definition ...; create or replace view v2 as select ... dummy definition ...; ... alter table t alter column type ...; create or replace view v1 as select ... new definition ...; create or replace view v2 as select ... new definition ...; ... commit; where the dummy definitions have no reference to t; they could probably just be SELECT null::type1 as colname1, null::type2 as colname2, ... In this way the ALTER TABLE itself would not need any change from current behavior. (But probably we need to allow CREATE OR REPLACE VIEW to change the output column types of the view, if it is currently unreferenced.) We might want to add some capability whereby the transaction could error out if anyone had changed either the target table or any of the dependent views since the transaction in which the tool captured their definitions. But that would be a separate command not part of the ALTER. What's more interesting is where the tool gets the draft modified view definitions from, and how it can highlight exactly what the semantic changes are for the user's benefit. There would likely be value in adding backend capability for parsing/reverse-listing views against hypothetical table definitions, but I'm not sure about details. I concur with Andres' thought that storing original view text doesn't actually help here. Rather, what we might need is a way to change what the reverse-lister does with a view. The behavior of ruleutils.c is pretty well focused on what pg_dump needs, and that may not always be what we want for this. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: FWIW, I don't think reparsing the original view-text is even remotely plausible. The fact that views stay glued to the same objects even of those objects are renamed is a pretty handy property of the current system, and any sort of reparse-from-scratch technique would give that up. Agreed. Deparse-and-reparse might be better, but I'll bet that has too many problems to be viable, too (even if I haven't yet thought of what they are). For better or for worse, I think the best we're likely to be able to do is somehow manipulate the already-parsed rewrite rule. I don't have any great ideas about how to do that, either, but it seems less problematic than going back to the SQL representation. I think deparse-and-reparse is exactly what we have to do, mainly because, if you subscribe to the idea that the user should see and approve semantic changes, what else are we going to show her except SQL? If she wants to adjust the changes, it's even less plausible that the working representation is not SQL text. We might well produce the initial draft form by manipulating the parsed querytree before deparsing, though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Mon, Jun 2, 2014 at 2:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Deparse-and-reparse might be better, but I'll bet that has too many problems to be viable, too (even if I haven't yet thought of what they are). For better or for worse, I think the best we're likely to be able to do is somehow manipulate the already-parsed rewrite rule. I don't have any great ideas about how to do that, either, but it seems less problematic than going back to the SQL representation. I think deparse-and-reparse is exactly what we have to do, mainly because, if you subscribe to the idea that the user should see and approve semantic changes, what else are we going to show her except SQL? If she wants to adjust the changes, it's even less plausible that the working representation is not SQL text. We might well produce the initial draft form by manipulating the parsed querytree before deparsing, though. So I think the scenario we're talking about, simplified down to basics, is something like this: CREATE TABLE foo (a int); CREATE VIEW bar AS SELECT a FROM foo; ALTER TABLE foo ALTER COLUMN a SET DATA TYPE bigint; If we wanted to make that last statement succeed instead of failing, what would we want it to do? I can see two answers. Answer #1 is that the column type of bar.a changes from int to bigint and the view definition is still SELECT a FROM foo. In that case, showing the user the SQL does not help them see and approve semantic changes because the SQL is completely unchanged. Answer #2 is that the column type of bar.a remains int4 and therefore the view definition mutates to something like SELECT a::int4 AS a FROM foo. In that case, showing the user the SQL does help the user understand what is happening ... but, as you say, you'd probably generate the new parse tree by manipulating the existing stored rule. And if you then deparsed it, how would that help? It's not like you can dump out the revised view definition and let the user edit it and put it back in. The view has to get modified as part of the same action as changing the table's column type, or you can't do anything we can't do already. Frankly, I don't think showing that particular thing to the user is necessary anyway; it's not like the semantics of pushing a cast on top of every use of the column within a related view are particularly hard to understand. And, anyway, whatever we do here has to be simple to invoke or we lose most of the advantage. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: On Mon, Jun 2, 2014 at 2:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think deparse-and-reparse is exactly what we have to do, mainly because, if you subscribe to the idea that the user should see and approve semantic changes, what else are we going to show her except SQL? If she wants to adjust the changes, it's even less plausible that the working representation is not SQL text. We might well produce the initial draft form by manipulating the parsed querytree before deparsing, though. So I think the scenario we're talking about, simplified down to basics, is something like this: CREATE TABLE foo (a int); CREATE VIEW bar AS SELECT a FROM foo; ALTER TABLE foo ALTER COLUMN a SET DATA TYPE bigint; If we wanted to make that last statement succeed instead of failing, what would we want it to do? My argument is that that command sequence, if issued exactly like that, SHOULD fail. It is not the backend's task to fix this case, and any smarts you try to put into ALTER TABLE to make it work are certain to do the wrong thing a distressingly high percentage of the time. Rather, it should be possible to build a client-side tool that can help users with such changes. I can see two answers. Answer #1 is that the column type of bar.a changes from int to bigint and the view definition is still SELECT a FROM foo. In that case, showing the user the SQL does not help them see and approve semantic changes because the SQL is completely unchanged. Yeah, we need some way of highlighting the semantic differences, and just printing ruleutils.c output doesn't do that. But if the user is going to put in a change to whatever choice the tool makes by default here, I would expect that change to consist of adding (or removing) an explicit cast in the SQL-text view definition. We can't make people learn some random non-SQL notation for this. Perhaps the displayed output of the tool could look something like CREATE VIEW bar AS SELECT a -- this view output column will now be of type int8 not int4 FROM foo; Or something else; I don't claim to be a good UI designer. But in the end, this is 90% a UI problem, and that means that raw SQL is seriously poorly suited to solve it directly. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
* Robert Haas (robertmh...@gmail.com) wrote: On Mon, May 26, 2014 at 10:39 AM, Stephen Frost sfr...@snowman.net wrote: It'd need to be explicitly requested, eg a 'CASCADE' option. Why? Would any sane person NOT want this behavior? [...] Now maybe there are options other than trying to reproduce what the original CREATE OR REPLACE statement would have done against the new type. For example, we could look through views that depend on t.a and rewrite each reference to that column to t.a::oldtype. This might lead to odd results with multiple nested casts and generally funny behavior if the column is re-typed multiple times; but maybe there's some way to fix that. This. Also, it might not really be the semantics you want if you were hoping the type update would truly cascade. But it might still be better than a sharp stick in the eye, which is kinda what we offer today. I hadn't even considered the idea that we would go through and try to change everything which referenced that view to now be the new type- but in that case, I'd want to know that there were other changes which were happening beyond the single view which I was updating. Perhaps a NOTICE would be enough, but it doesn't feel correct to me. Also consider MatViews which would need to be rewritten for the new type, or pl/pgsql functions which we couldn't possibly fix entirely (we're going to change the variable's type definition because it selects out a column from this view?) and so they'd just break instead. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: Well, pg_dump is trying to do something different than what you're trying to do here. pg_dump wants to make sure that the view, when fed back into psql, creates the same view that exists now, regardless of whether that's what the user created originally. For example, if a view is created referring to table foo, and table foo is later renamed to bar, then pg_dump wants to (and does) dump a statement referring to bar, not foo - even if there's a *new* table called foo against which the view could have been defined. Similarly, pg_dump will schema-qualify functions and operators, or not, based on whether that's necessary to reference the exact same operators that were selected when the original CREATE VIEW command was run, regardless of whether the original references were schema-qualified. Sorry, I don't see how any of the above is a problem in my use case. Should a table has been renamed, naturally we want to re-create the view referring to the *old* table, but under its *new name*. The same goes with schema-qualifying objects. None of that involves answering hypothetical questions; but what you want to do does, and that I think is the problem in a nutshell. In a nutshell I'd like PostgreSQL to just re-parse the *current* view definition. Should that throw an error, user intervention will be required anyway, but most of the time it should just work. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Stephen Frost sfr...@snowman.net writes: I hadn't even considered the idea that we would go through and try to change everything which referenced that view to now be the new type- but in that case, I'd want to know that there were other changes which were happening beyond the single view which I was updating. Perhaps a NOTICE would be enough, but it doesn't feel correct to me. Also consider MatViews which would need to be rewritten for the new type That might be costly but not impossible. A user would need to do that anyway, though manually. or pl/pgsql functions which we couldn't possibly fix entirely (we're going to change the variable's type definition because it selects out a column from this view?) and so they'd just break instead. I'm not suggesting that we try to *fix* functions, but if we can detect function breakage by re-parsing them it would be nice to alert the user of any problems at the instant of running ALTER TABLE statement, not when the user tries to actually run the function (see my mail upthread for sample scenario.) -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
* ash (a...@commandprompt.com) wrote: Stephen Frost sfr...@snowman.net writes: I hadn't even considered the idea that we would go through and try to change everything which referenced that view to now be the new type- but in that case, I'd want to know that there were other changes which were happening beyond the single view which I was updating. Perhaps a NOTICE would be enough, but it doesn't feel correct to me. Also consider MatViews which would need to be rewritten for the new type That might be costly but not impossible. A user would need to do that anyway, though manually. I was pointing out why it should need to be explicitly requested, but all-in-all, I don't really see this proposal going anywhere. It's a neat idea, and if there's a sensible way to do it, then the user should have to explicitly request it, imv. or pl/pgsql functions which we couldn't possibly fix entirely (we're going to change the variable's type definition because it selects out a column from this view?) and so they'd just break instead. I'm not suggesting that we try to *fix* functions, but if we can detect function breakage by re-parsing them it would be nice to alert the user of any problems at the instant of running ALTER TABLE statement, not when the user tries to actually run the function (see my mail upthread for sample scenario.) We're not going to re-parse every function in the system, like, ever. I might be willing to buy off on too bad in those cases (it's not like we're going and fixing them today for ALTER TABLE .. TYPE cases anyway) but the point is that cascading the change to a column's type through all of its dependencies is likely to cause breakage somewhere in even modestly complex systems and we shouldn't just start doing that automatically. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Stephen Frost sfr...@snowman.net writes: * ash (a...@commandprompt.com) wrote: Stephen Frost sfr...@snowman.net writes: Also consider MatViews which would need to be rewritten for the new type That might be costly but not impossible. A user would need to do that anyway, though manually. I was pointing out why it should need to be explicitly requested, but all-in-all, I don't really see this proposal going anywhere. It's a neat idea, and if there's a sensible way to do it, then the user should have to explicitly request it, imv. Ah, understood. or pl/pgsql functions which we couldn't possibly fix entirely (we're going to change the variable's type definition because it selects out a column from this view?) and so they'd just break instead. I'm not suggesting that we try to *fix* functions, but if we can detect function breakage by re-parsing them it would be nice to alert the user of any problems at the instant of running ALTER TABLE statement, not when the user tries to actually run the function (see my mail upthread for sample scenario.) We're not going to re-parse every function in the system, like, ever. Well, only every *affected* function, which might be pretty minimal in the usual case. I might be willing to buy off on too bad in those cases (it's not like we're going and fixing them today for ALTER TABLE .. TYPE cases anyway) but the point is that cascading the change to a column's type through all of its dependencies is likely to cause breakage somewhere in even modestly complex systems and we shouldn't just start doing that automatically. What I am suggesting is that we try to detect such breakage at the time the user runs ALTER TABLE (issuing NOTICE or ERROR at user discretion.) If changing column type of a table breaks some functions down the way, the user will hit it anyway, but better know it soon than when he wants to *run* that function. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
* ash (a...@commandprompt.com) wrote: What I am suggesting is that we try to detect such breakage at the time the user runs ALTER TABLE (issuing NOTICE or ERROR at user discretion.) If changing column type of a table breaks some functions down the way, the user will hit it anyway, but better know it soon than when he wants to *run* that function. Sure, if we had the information about what would break then we could tell the user about it. We don't and that's not likely to ever change... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
ash a...@commandprompt.com writes: Stephen Frost sfr...@snowman.net writes: We're not going to re-parse every function in the system, like, ever. Well, only every *affected* function, which might be pretty minimal in the usual case. We don't store dependency information for function bodies, so there's no way to do this except by reparsing everything in sight. A larger issue with the idea is that a function might fail reparsing for reasons having nothing to do with the proposed ALTER TABLE. For instance, it's not at all unusual for functions to contain references to tables that don't normally exist, but are created when the function is to be called (or maybe even by the function itself). Because of this problem, reparsing, in the sense of detecting semantic rather than purely syntactic problems in a function body, is something that we don't actually do *at all*, ever, except when the function is actually executed. (This is part of the reason why there's no dependency info.) Pavel Stehule has made some efforts towards improving that situation for plpgsql functions: https://commitfest.postgresql.org/action/patch_view?id=884 but that patch remains pretty controversial and may never get committed. Even if it does get in, it wouldn't move the goalposts for any other PL. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Tom Lane t...@sss.pgh.pa.us writes: We don't store dependency information for function bodies, so there's no way to do this except by reparsing everything in sight. A larger issue with the idea is that a function might fail reparsing for reasons having nothing to do with the proposed ALTER TABLE. For instance, it's not at all unusual for functions to contain references to tables that don't normally exist, but are created when the function is to be called (or maybe even by the function itself). Because of this problem, reparsing, in the sense of detecting semantic rather than purely syntactic problems in a function body, is something that we don't actually do *at all*, ever, except when the function is actually executed. (This is part of the reason why there's no dependency info.) Pavel Stehule has made some efforts towards improving that situation for plpgsql functions: https://commitfest.postgresql.org/action/patch_view?id=884 but that patch remains pretty controversial and may never get committed. Even if it does get in, it wouldn't move the goalposts for any other PL. OK, forget functions, I now realize it's not feasible to consider. Can we get back to re-defining views at least? -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
ash wrote: Tom Lane t...@sss.pgh.pa.us writes: We don't store dependency information for function bodies, so there's no way to do this except by reparsing everything in sight. OK, forget functions, I now realize it's not feasible to consider. Can we get back to re-defining views at least? Hi Alex, I think it's reasonable to try and fix the problems for views (and other objects -- there are other things that can depend on table definitions; composite types come to mind) and ignore functions bodies, since you can already get into trouble by using ALTER TABLE today and it's known to be an unsolvable problem. Now -- do we need to do anything about tables used as return types or argument types for functions? alvherre=# create table qux (a int, b text); CREATE TABLE alvherre=# create or replace function test_qux(a qux) returns void language plpgsql as $$ begin raise notice 'the qux we got is %', $1; end; $$; CREATE FUNCTION alvherre=# insert into qux values (1, 'one'); INSERT 0 1 alvherre=# select * from qux, test_qux(qux.*); NOTICE: the qux we got is (1,one) a | b | test_qux ---+-+-- 1 | one | (1 fila) alvherre=# alter table qux add column c timestamptz; ALTER TABLE alvherre=# update qux set c = now(); UPDATE 1 alvherre=# select * from qux, test_qux(qux.*); NOTICE: the qux we got is (1,one,) a | b | c | test_qux ---+-+---+-- 1 | one | 2014-05-28 12:08:28.210895-04 | (1 fila) Notice how the NOTICE has a final comma, meaning the tuple descriptor is aware that there is a third column -- but the value in the table is not null per the UPDATE, so the fact that there's nothing after the comma means this is not being handled correctly. If I close the session and start a fresh one, the result is saner: alvherre=# select * from qux, test_qux(qux.*); NOTICE: the qux we got is (1,one,2014-05-28 12:08:28.210895-04) a | b | c | test_qux ---+-+---+-- 1 | one | 2014-05-28 12:08:28.210895-04 | (1 fila) Maybe we're missing a function cache invalidation or something like that. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
* ash (a...@commandprompt.com) wrote: OK, forget functions, I now realize it's not feasible to consider. I never meant to imply that it was but rather to point out that we might have users who actually want to get an error when they're changing a type definition which goes beyond the scope of the explicit action (and therefore could very well have more side-effects than they realize), rather than just doing it for them. Can we get back to re-defining views at least? I'm still not convinced you'll be able to do it in a sensible and reliable way, but you're certainly welcome to continue exploring. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
David Fetter da...@fetter.org writes: Also worth considering: functions which take any part of the view as a parameter. Sorry, I don't get it: do you suggest we should re-create dependent functions too? I'd throw an error in cases where such functions had an obvious and deterministic dependency on the views, ideally having gone through all such functions first and enumerated them in the error message. Then it would also make sense to start with checking function dependency on the tables themselves, not only the joining views: psql= CREATE TABLE t(id INT); CREATE TABLE psql= CREATE FUNCTION func1() RETURNS SETOF INT AS $$ SELECT id FROM t; $$ LANGUAGE SQL; CREATE FUNCTION psql= ALTER TABLE t ALTER COLUMN id TYPE BIGINT; ALTER TABLE -- Would complain on func1 right away psql= SELECT func1(); ERROR: return type mismatch in function declared to return integer DETAIL: Actual return type is bigint. CONTEXT: SQL function func1 during startup psql= CREATE FUNCTION func2() RETURNS SETOF INT AS $$ SELECT id FROM t; $$ LANGUAGE SQL; ERROR: return type mismatch in function declared to return integer DETAIL: Actual return type is bigint. CONTEXT: SQL function func2 -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Mon, May 26, 2014 at 10:39 AM, Stephen Frost sfr...@snowman.net wrote: * ash (a...@commandprompt.com) wrote: This came up recently on general list (and I've just hit the same issue today): http://www.postgresql.org/message-id/cab7npqtlmmn1ltb5we0v0do57ip0u73ykwzbzytaxdf1caw...@mail.gmail.com Why couldn't postgres re-create the dependent views automatically? I mean it knows which views depend on the altered column and keeps the view definition, no? Might be pretty complicated in the end.. Would a patch likely to be accepted? How hard do you feel this might be to implement? Any caveat that defeats the purpose of such feature? It'd need to be explicitly requested, eg a 'CASCADE' option. Why? Would any sane person NOT want this behavior? I think the question here is whether there's any way to make this work at all, not whether we'd want it if we could get it. Consider: CREATE OR REPLACE VIEW v AS SELECT a + 0 FROM t; If we drop the view, change the column type of t.a, and re-execute the view, + might resolve to a different operator than before (or no operator, at all). Furthermore, the operator to which it resolves will depend on the search path at the time the CREATE OR REPLACE VIEW command is executed. Now, consider the situation in which we want to achieve the same result without having to drop and recreate v. When the column type of t.a is changed, we can use the dependencies to figure out that v might be impacted. We can trace through the rewrite rule to find out where column t.a is referenced. And ... then what? All we know about t.a is that we're applying some operator to it, which is specified by OID. The rewrite rule doesn't tell us the actual *name* by which the operator was referenced in the original view text, nor does it tell us the search path that was in effect at that time. If it did, we could pick the same operator for + that would have been used had t.a been of the new type originally, but as it is, we can't. Now maybe there are options other than trying to reproduce what the original CREATE OR REPLACE statement would have done against the new type. For example, we could look through views that depend on t.a and rewrite each reference to that column to t.a::oldtype. This might lead to odd results with multiple nested casts and generally funny behavior if the column is re-typed multiple times; but maybe there's some way to fix that. Also, it might not really be the semantics you want if you were hoping the type update would truly cascade. But it might still be better than a sharp stick in the eye, which is kinda what we offer today. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Alexander Shulgin wrote Hi Hackers, This came up recently on general list (and I've just hit the same issue today): http://www.postgresql.org/message-id/ CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@.gmail Why couldn't postgres re-create the dependent views automatically? I mean it knows which views depend on the altered column and keeps the view definition, no? Would a patch likely to be accepted? How hard do you feel this might be to implement? Any caveat that defeats the purpose of such feature? Thanks. Would it be possible to handle the specific case of varchar(n) to varchar/text by just ignoring the error? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Re-create-dependent-views-on-ALTER-TABLE-ALTER-COLUMN-TYPE-tp5804972p5805191.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Robert Haas robertmh...@gmail.com writes: It'd need to be explicitly requested, eg a 'CASCADE' option. Why? Would any sane person NOT want this behavior? I think the question here is whether there's any way to make this work at all, not whether we'd want it if we could get it. Consider: CREATE OR REPLACE VIEW v AS SELECT a + 0 FROM t; If we drop the view, change the column type of t.a, and re-execute the view, + might resolve to a different operator than before (or no operator, at all). Furthermore, the operator to which it resolves will depend on the search path at the time the CREATE OR REPLACE VIEW command is executed. Now, consider the situation in which we want to achieve the same result without having to drop and recreate v. When the column type of t.a is changed, we can use the dependencies to figure out that v might be impacted. We can trace through the rewrite rule to find out where column t.a is referenced. And ... then what? All we know about t.a is that we're applying some operator to it, which is specified by OID. The rewrite rule doesn't tell us the actual *name* by which the operator was referenced in the original view text, nor does it tell us the search path that was in effect at that time. If it did, we could pick the same operator for + that would have been used had t.a been of the new type originally, but as it is, we can't. This could be a showstopper indeed. We can look up view def in pg_views view, but it doesn't include any schema references unless they were explicit in the CREATE VIEW statement. On the other hand, pg_dump *can* work around this: if you dump a view that has been defined when a specific search_path was in effect, you'll get correct definition in the schema dump. So why can't we try to learn from pg_dump? Now maybe there are options other than trying to reproduce what the original CREATE OR REPLACE statement would have done against the new type. For example, we could look through views that depend on t.a and rewrite each reference to that column to t.a::oldtype. This might lead to odd results with multiple nested casts and generally funny behavior if the column is re-typed multiple times; but maybe there's some way to fix that. Also, it might not really be the semantics you want if you were hoping the type update would truly cascade. But it might still be better than a sharp stick in the eye, which is kinda what we offer today. No, casting back to oldtype totally defeats the purpose, at least for my usecase. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
David G Johnston david.g.johns...@gmail.com writes: Would it be possible to handle the specific case of varchar(n) to varchar/text by just ignoring the error? Simply for the reference, my case is INT to BIGINT. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Tue, May 27, 2014 at 11:20 PM, ash a...@commandprompt.com wrote: Now, consider the situation in which we want to achieve the same result without having to drop and recreate v. When the column type of t.a is changed, we can use the dependencies to figure out that v might be impacted. We can trace through the rewrite rule to find out where column t.a is referenced. And ... then what? All we know about t.a is that we're applying some operator to it, which is specified by OID. The rewrite rule doesn't tell us the actual *name* by which the operator was referenced in the original view text, nor does it tell us the search path that was in effect at that time. If it did, we could pick the same operator for + that would have been used had t.a been of the new type originally, but as it is, we can't. This could be a showstopper indeed. We can look up view def in pg_views view, but it doesn't include any schema references unless they were explicit in the CREATE VIEW statement. On the other hand, pg_dump *can* work around this: if you dump a view that has been defined when a specific search_path was in effect, you'll get correct definition in the schema dump. So why can't we try to learn from pg_dump? Well, pg_dump is trying to do something different than what you're trying to do here. pg_dump wants to make sure that the view, when fed back into psql, creates the same view that exists now, regardless of whether that's what the user created originally. For example, if a view is created referring to table foo, and table foo is later renamed to bar, then pg_dump wants to (and does) dump a statement referring to bar, not foo - even if there's a *new* table called foo against which the view could have been defined. Similarly, pg_dump will schema-qualify functions and operators, or not, based on whether that's necessary to reference the exact same operators that were selected when the original CREATE VIEW command was run, regardless of whether the original references were schema-qualified. None of that involves answering hypothetical questions; but what you want to do does, and that I think is the problem in a nutshell. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
Hi Hackers, This came up recently on general list (and I've just hit the same issue today): http://www.postgresql.org/message-id/cab7npqtlmmn1ltb5we0v0do57ip0u73ykwzbzytaxdf1caw...@mail.gmail.com Why couldn't postgres re-create the dependent views automatically? I mean it knows which views depend on the altered column and keeps the view definition, no? Would a patch likely to be accepted? How hard do you feel this might be to implement? Any caveat that defeats the purpose of such feature? Thanks. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
ash, * ash (a...@commandprompt.com) wrote: This came up recently on general list (and I've just hit the same issue today): http://www.postgresql.org/message-id/cab7npqtlmmn1ltb5we0v0do57ip0u73ykwzbzytaxdf1caw...@mail.gmail.com Why couldn't postgres re-create the dependent views automatically? I mean it knows which views depend on the altered column and keeps the view definition, no? Might be pretty complicated in the end.. Would a patch likely to be accepted? How hard do you feel this might be to implement? Any caveat that defeats the purpose of such feature? It'd need to be explicitly requested, eg a 'CASCADE' option. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote: Hi Hackers, This came up recently on general list (and I've just hit the same issue today): http://www.postgresql.org/message-id/cab7npqtlmmn1ltb5we0v0do57ip0u73ykwzbzytaxdf1caw...@mail.gmail.com Why couldn't postgres re-create the dependent views automatically? I mean it knows which views depend on the altered column and keeps the view definition, no? Also worth considering: functions which take any part of the view as a parameter. Not, IMHO, worth considering, are functions like this: CREATE OR REPLACE FUNCTION make_task_impossible_for_alex() RETURNS int8 LANGUAGE plpgsql AS $$ DECLARE foo text[] := ARRAY['list','of','views','here']; BEGIN EXECUTE 'SELECT COUNT(*) FROM %', foo[floor(random()*array_upper(foo,1))]; END; $$; That counts pretty strictly as pilot error, not least because it makes things like you want to write not just hard, but impossible. Would a patch likely to be accepted? How hard do you feel this might be to implement? In the general case, impossible. In most sane cases, mostly a matter of chasing down dependencies, which is harder than it first appears, as anyone who's worked on that part of pg_dump can tell you. Any caveat Locking. Given that, you'd want this behavior only with CASCADE, per Stephen's response. that defeats the purpose of such feature? Probably not. I'd certainly like to have the feature :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
David Fetter da...@fetter.org writes: On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote: Hi Hackers, This came up recently on general list (and I've just hit the same issue today): http://www.postgresql.org/message-id/cab7npqtlmmn1ltb5we0v0do57ip0u73ykwzbzytaxdf1caw...@mail.gmail.com Why couldn't postgres re-create the dependent views automatically? I mean it knows which views depend on the altered column and keeps the view definition, no? Also worth considering: functions which take any part of the view as a parameter. Sorry, I don't get it: do you suggest we should re-create dependent functions too? I don't think that's feasible, but there is certainly a use case for silently re-defining the views together with alteration of the joined table. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?
On Tue, May 27, 2014 at 12:37:32AM +0400, ash wrote: David Fetter da...@fetter.org writes: On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote: Hi Hackers, This came up recently on general list (and I've just hit the same issue today): http://www.postgresql.org/message-id/cab7npqtlmmn1ltb5we0v0do57ip0u73ykwzbzytaxdf1caw...@mail.gmail.com Why couldn't postgres re-create the dependent views automatically? I mean it knows which views depend on the altered column and keeps the view definition, no? Also worth considering: functions which take any part of the view as a parameter. Sorry, I don't get it: do you suggest we should re-create dependent functions too? I'd throw an error in cases where such functions had an obvious and deterministic dependency on the views, ideally having gone through all such functions first and enumerated them in the error message. I don't think that's feasible, but there is certainly a use case for silently re-defining the views together with alteration of the joined table. Indeed. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
