Thread: Editing Views with EditGrid
Hi all, My understanding is that editing views with the EditGrid (similar to editing tables) is currently unsupported. I would be very interested to have this feature in an upcoming version of pgadmin3. I prepared some views and their corresponding insert/update/delete rules but can't edit the views in pgadmin3. Is this on the feature list of an upcoming pgadmin3 version, maybe 1.9x? Thank you for any information on the topic, Andreas -- Andreas Neumann Böschacherstrasse 6, CH-8624 Grüt/Gossau, Switzerland Email: a.neumann@carto.net, Web: * http://www.carto.net/ (Carto and SVG resources) * http://www.carto.net/neumann/ (personal page) * http://www.svgopen.org/ (SVG Open Conference) * http://www.geofoto.ch/ (Georeferenced Photos of Switzerland)
On Tue, May 6, 2008 at 1:57 PM, Andreas Neumann <a.neumann@carto.net> wrote: > Hi all, > > My understanding is that editing views with the EditGrid (similar to > editing tables) is currently unsupported. I would be very interested to > have this feature in an upcoming version of pgadmin3. I prepared some > views and their corresponding insert/update/delete rules but can't edit > the views in pgadmin3. > > Is this on the feature list of an upcoming pgadmin3 version, maybe 1.9x? 1.9 is the current development code - you probably mean 1.10. And unfortunately, no, I don't currently know of anyone working on this feature. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Hi Dave, Thank you for letting me know about the situation. Can you estimate how much work it would be to implement editable views? Maybe based on the experience with the editable tables? Maybe we can find a few interested parties paying for the development of this feature? Andreas Dave Page wrote: > On Tue, May 6, 2008 at 1:57 PM, Andreas Neumann <a.neumann@carto.net> wrote: >> Hi all, >> >> My understanding is that editing views with the EditGrid (similar to >> editing tables) is currently unsupported. I would be very interested to >> have this feature in an upcoming version of pgadmin3. I prepared some >> views and their corresponding insert/update/delete rules but can't edit >> the views in pgadmin3. >> >> Is this on the feature list of an upcoming pgadmin3 version, maybe 1.9x? > > 1.9 is the current development code - you probably mean 1.10. > > And unfortunately, no, I don't currently know of anyone working on this feature. > > -- -- Andreas Neumann Böschacherstrasse 6 CH-8624 Grüt (Gossau ZH) Switzerland Phone: ++41-44-2736668 Email: a.neumann@carto.net Web: http://www.carto.net/neumann/ SVG Examples: http://www.carto.net/papers/svg/samples/ SVG.Open: http://www.svgopen.org/
On Tue, May 6, 2008 at 9:05 PM, Andreas Neumann <a.neumann@carto.net> wrote: > Hi Dave, > > Thank you for letting me know about the situation. Can you estimate how > much work it would be to implement editable views? Maybe based on the > experience with the editable tables? Maybe we can find a few interested > parties paying for the development of this feature? The problem with editing views is not the editing (the code for the tables will do the job just fine with a few minor changes in most cases). The problem comes where you have rules which implement non-trivial updates - for example; consider a view with a total column, an item price, and a quantity. If you update the quantity, there is no straightforward way for pgAdmin to understand that the total price will be automatically updated. In fact, it won't even know that the total column cannot be manually edited. This gets even worse, because the rules may easily affect different rows from that being edited, or even entirely unrelated data. The only safe way to manage that that I can see would be to refresh the entire data set following each edit - and that would be unusably slow. I'm happy to entertain proposals that might work, but this a non-trivial problem, caused largely by Postgres' superior flexibility compared to most other DBMSs. It isn't gonna be easy to fix. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Hi Dave, You are right about the complexity of rules and triggers. But isn't that the same with tables as well? Triggers and rules also work on plain tables and may effect other columns in the same table and other records as well. So in my opinion there isn't much difference between tables and views in this respect. There are also the default values that currently don't reflect immediately after one edited a record in the table. I don't have a clean and easy solution as well. But one might think about a GUI where one lets the user editing the table or view decide whether he wants to reload the current record or the whole table/view after each edit. Per default it wouldn't update, but if the user enables updating of the just edited record or reloading the whole table/view it would. I can definitely see value where the record(s) just edited would automatically reload after being inserted/updated into the table. Reloading just these records would not be a performance problem. I agree that reloading the whole table after each edit would be a performance problem. Again - not much difference between a table and a view. Am I wrong? Regarding the views: one could test if the view has at least the insert, update and delete rules present before enabling the editing of the views. What do you think? Andreas Dave Page wrote: > On Tue, May 6, 2008 at 9:05 PM, Andreas Neumann <a.neumann@carto.net> wrote: >> Hi Dave, >> >> Thank you for letting me know about the situation. Can you estimate how >> much work it would be to implement editable views? Maybe based on the >> experience with the editable tables? Maybe we can find a few interested >> parties paying for the development of this feature? > > The problem with editing views is not the editing (the code for the > tables will do the job just fine with a few minor changes in most > cases). The problem comes where you have rules which implement > non-trivial updates - for example; consider a view with a total > column, an item price, and a quantity. If you update the quantity, > there is no straightforward way for pgAdmin to understand that the > total price will be automatically updated. In fact, it won't even know > that the total column cannot be manually edited. > > This gets even worse, because the rules may easily affect different > rows from that being edited, or even entirely unrelated data. The only > safe way to manage that that I can see would be to refresh the entire > data set following each edit - and that would be unusably slow. > > I'm happy to entertain proposals that might work, but this a > non-trivial problem, caused largely by Postgres' superior flexibility > compared to most other DBMSs. It isn't gonna be easy to fix. > > -- -- Andreas Neumann Böschacherstrasse 6 CH-8624 Grüt (Gossau ZH) Switzerland Phone: ++41-44-2736668 Email: a.neumann@carto.net Web: http://www.carto.net/neumann/ SVG Examples: http://www.carto.net/papers/svg/samples/ SVG.Open: http://www.svgopen.org/
On Fri, May 9, 2008 at 9:36 AM, Andreas Neumann <a.neumann@carto.net> wrote: > Hi Dave, > > You are right about the complexity of rules and triggers. But isn't that > the same with tables as well? That's true, but is quite rare. With views the problem is almost always lurking just around the corner. > I don't have a clean and easy solution as well. But one might think about a > GUI where one lets the user editing the table or view decide whether he > wants to reload the current record or the whole table/view after each edit. > Per default it wouldn't update, but if the user enables updating of the just > edited record or reloading the whole table/view it would. Technically thats possible of course, but I can see it leading to false bug reports because people forget, or don't realise such options exist. > Regarding the views: one could test if the view has at least the insert, > update and delete rules present before enabling the editing of the views. That goes without saying. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com