Thread: Allowing COPY into views
Hello, I would like to submit a patch so that the COPY statement can import data into a view. (Maybe if this works I'll see about copying out of a view.) Rather than spend a whole lot of time figuring out all the calls to use and all the detail, I'm going to go ahead and post now. That way if this is simply not going to fly I don't have to spend any more time on it. Otherwise, I'll post more as I work it out. Any and all help and/or comment is appreciated. The basic approach I'm thinking of is to: 1) Check to see if the view has an rule that allows INSERT, if not then exit with an error. I thought I would use a stored procedure for this but having read the FAQ_DEV perhaps I'll use SearchSysCache(). 2) Allocate memory for char* and construct an 'INSERT ...' statement corresponding to the COPY command that inserts into the view. (Just how much memory should be allocated? Is there a standard buffer pre-alocated somewhere or a standard buffer size?) 3) makeNode(T_PrepareStmt) (What should name be?) 4) Iterate over COPY statement's stmnt->options to produce prepared statement's argtypes. The DefElem arg values can be used without further ado. (I don't need to check to see that they are type T_TypeName because the parser would have taken care of that for the COPY statement, right? (I didn't look.) There's no point in doing a copyObject on the TypeName, right?) 5) Parse the (char*) 'INSERT...' statement. 6) Call PrepareQuery() on the parsed INSERT statement. I'm thinking this will result in a query plan with the rules expanded. 7) Execute the prepared statement for each row of data. Deallocate memory for (char*) 'INSERT...' statement. Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
On 04/19/2007 08:41:55 AM, Karl O. Pinc wrote: > I would like to submit a patch so that the > COPY statement can import data into a view. > 2) Allocate memory for char* and construct an > 'INSERT ...' statement corresponding > to the COPY command that inserts into the view. > (Just how much memory should be allocated? > Is there a standard buffer pre-alocated somewhere or a > standard buffer size?) I'll use a StringInfo. And I forgot, the final step is to add regression tests. One for view with an INSERT rule and one to catch the error for a view without an INSERT rule. Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
Karl O. Pinc wrote: > > (Maybe if this works I'll see about copying > out of a view.) > You know we already have COPY (SELECT ...) TO ... don't you? cheers andrew
Karl O. Pinc wrote: > > On 04/19/2007 08:41:55 AM, Karl O. Pinc wrote: > > >I would like to submit a patch so that the > >COPY statement can import data into a view. > > >2) Allocate memory for char* and construct an > >'INSERT ...' statement corresponding > >to the COPY command that inserts into the view. > >(Just how much memory should be allocated? > >Is there a standard buffer pre-alocated somewhere or a > >standard buffer size?) > > I'll use a StringInfo. > > And I forgot, the final step is to add regression > tests. One for view with an INSERT rule and one > to catch the error for a view without an INSERT rule. I'm not sure the plan is OK as stated. You wouldn't want to force to parse the query again for each row. Rather, create a prepared statement (already parsed, because you obtain it from the parsetree stored in the INSERT rule) to pass to the executor. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
"Karl O. Pinc" <kop@meme.com> writes: > Rather than spend a whole lot of time figuring > out all the calls to use and all the detail, > I'm going to go ahead and post now. That way > if this is simply not going to fly I don't have > to spend any more time on it. Otherwise, > I'll post more as I work it out. It probably doesn't make sense to work on just this one case. There are folks looking at update-able views. If we had that then this would simplify to simply recognizing the case and invoking the rewriter to get you the insert statement to use. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On 04/19/2007 09:39:18 AM, Alvaro Herrera wrote: > > I'm not sure the plan is OK as stated. You wouldn't want to force to > parse the query again for each row. Rather, create a prepared > statement > (already parsed, because you obtain it from the parsetree stored in > the > INSERT rule) to pass to the executor. I wasn't going to parse and plan an INSERT for each row of data to COPY. Step 7 is the step that iterates over the rows of COPY data. I make my own PREPAREd insert and execute it in step 7 for each row of COPY data. It seems to me it'd be eaiser to write my own INSERT statement and call raw_parser on it than to try to modify the parsed INSERT statement stored in the INSERT rule. (I didn't know that was even there.) This is because the COPY statement could only insert into some columns of the view. It just seems easier to me to write an INSERT statement and parse it than to take stuff out of an already existing raw parse tree. Am I wrong? Thanks for the help. Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
On 04/19/2007 09:33:44 AM, Andrew Dunstan wrote: > Karl O. Pinc wrote: >> >> (Maybe if this works I'll see about copying >> out of a view.) >> > > You know we already have > > COPY (SELECT ...) TO ... > > don't you? Sure. It'd just be syntatic suger for the COPY (SELECT ...) form, so end-users don't have to think about whether they're using a view or a relation. (Dunno if I'll ever get to it anyway....) Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
Gregory Stark wrote: > "Karl O. Pinc" <kop@meme.com> writes: > > > Rather than spend a whole lot of time figuring > > out all the calls to use and all the detail, > > I'm going to go ahead and post now. That way > > if this is simply not going to fly I don't have > > to spend any more time on it. Otherwise, > > I'll post more as I work it out. > > It probably doesn't make sense to work on just this one case. There are folks > looking at update-able views. If we had that then this would simplify to > simply recognizing the case and invoking the rewriter to get you the insert > statement to use. The threads to updatable views have concluded rejecting the patches, and with ideas that require rather extensive rewriting of the rule manager, so I'd think that handling this one case (COPY view FROM foo) would be useful on its own, and would carry a lot less work than the whole thing. Updatable views are not going to handle COPY anyway ... -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Karl O. Pinc wrote: > > On 04/19/2007 09:39:18 AM, Alvaro Herrera wrote: > > > > >I'm not sure the plan is OK as stated. You wouldn't want to force to > >parse the query again for each row. Rather, create a prepared > >statement > >(already parsed, because you obtain it from the parsetree stored in > >the > >INSERT rule) to pass to the executor. > > I wasn't going to parse and plan an INSERT for each row > of data to COPY. Step 7 is the step that iterates over > the rows of COPY data. I make my own PREPAREd insert > and execute it in step 7 for each row of COPY data. > > It seems to me it'd be eaiser to write my own INSERT statement > and call raw_parser on it than to try to modify the parsed > INSERT statement stored in the INSERT rule. (I didn't know > that was even there.) Based on past records, I would guess that that approach would be rejected or at least frowned upon. It happened to the COPY (query) TO patch. > This is because the COPY statement could only insert into some columns > of the view. Then you would fill the missing values with DEFAULT or NULL expressions. > It just seems easier to me to write an INSERT statement > and parse it than to take stuff out of an already > existing raw parse tree. Am I wrong? Well, if it's easier for you to do it, go ahead; but if you want the patch accepted that's a whole'nother matter. I wrote several easy patches that were rejected. Maybe the easy patch serves as proof of concept to make sure it all works, which you then rework to do things properly. We don't care _how_ you arrived at a patch, if it's good. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
"Alvaro Herrera" <alvherre@commandprompt.com> writes: > The threads to updatable views have concluded rejecting the patches, and > with ideas that require rather extensive rewriting of the rule manager, I have some ideas on a different approach to this if anyone's thinking of starting fresh but I had the impression that the patches were rejected because they were unnecessarily complex, not because the overall approach was rejected. > Updatable views are not going to handle COPY anyway ... Well I noticed this as I was writing it. Even once you have updateable views you would still have to have code in COPY supporting creating insert statements which isn't how it works now. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark wrote: > > "Alvaro Herrera" <alvherre@commandprompt.com> writes: > > > The threads to updatable views have concluded rejecting the patches, and > > with ideas that require rather extensive rewriting of the rule manager, > > I have some ideas on a different approach to this if anyone's thinking of > starting fresh What ideas? > but I had the impression that the patches were rejected because > they were unnecessarily complex, not because the overall approach was > rejected. Well, there was the unneeded complexity as one argument, but the "with check option" stuff needed a big rework as well. http://groups.google.es/group/pgsql.hackers/browse_thread/thread/ed69543ea417a2f/d0b628881ead1e05 -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
"Alvaro Herrera" <alvherre@commandprompt.com> writes: > What ideas? Basically I think what we should do is extend SQL to support things like UPDATE (subquery) SET ... DELETE FROM (subquery) WHERE ... Ie, do the update-able view magic in the planner and executor rather than in the rewriter. Then the rewriter just has the same work to do that it does for SELECTs, namely substitute the view in the right place in the query. We don't have the rewriter try to reverse-engineer SELECTS and flatten the view into the outer query, it shouldn't be trying to do that for UPDATEs and DELETEs either. Oracle does actually support syntax like this, even for joins, and it's awfully convenient for UPDATE where it handles the same kind of cases we use our UPDATE ... FROM syntax. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com