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