Lists: | pgsql-hackers |
---|
From: | Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | insert with multiple targetLists |
Date: | 2002-05-01 23:16:56 |
Message-ID: | 20020501191656.3b2d651b.nconway@klamath.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi all,
I've been taking a look at fixing the TODO item:
o Allow INSERT INTO tab (col1, ..) VALUES (val1, ..), (val2, ..)
My first plan of attack was to replace the current list of ResTargets
in InsertStmt with a list of lists. The problem with that approach is
that:
(a) the InsertStmt is converted to a Query. I could also change Query
to use a list of lists (instead of a list) for holding TargetEntry
items, but that would be ugly (since Query is generic, and this
would only be needed for Inserts)
(b) modifying Query would mean a lot of work (e.g. in the rewriter),
adapting all the places that expect targetList to be a list to
instead use a list of lists. Once again, this would be messy.
So, that seems like a bad idea.
ISTM that a better way to do this would be to parse the InsertStmt,
and then execute an INSERT for every targetList in the query.
For example:
INSERT INTO t1 (c1) VALUES (1), (2);
would be executed in a similar fashion to:
INSERT INTO t1 (c1) VALUES (1);
INSERT INTO t1 (c1) VALUES (2);
Does this sound reasonable?
Any suggestions would be welcome.
Cheers,
Neil
--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC
From: | "Rod Taylor" <rbt(at)zort(dot)ca> |
---|---|
To: | "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org>, "PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: insert with multiple targetLists |
Date: | 2002-05-01 23:51:46 |
Message-ID: | 221201c1f16b$275a0450$ad02000a@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
> INSERT INTO t1 (c1) VALUES (1), (2);
>
> would be executed in a similar fashion to:
>
> INSERT INTO t1 (c1) VALUES (1);
> INSERT INTO t1 (c1) VALUES (2);
>
> Does this sound reasonable?
I debated doing the above too. In fact, I had a partial
implementation at one point.
However, the resulting purpose of allowing such a construct is to
enable the speeds copy achieves with the variation that is found in an
insert. So, the above transformation method really doesn't accomplish
much except a new style for many inserts. But it is quite a bit
easier simply to code each insert individually if there is a minimal
speed gain. Large strings may reach query length limits in other
systems using this style (look at a MySQL dump sometime). You're
really only good for about 50 or 60 records in a single insert
statement there.
I'd tend to run it like a copy that can resolving expressions and
defaults.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: insert with multiple targetLists |
Date: | 2002-05-02 02:58:16 |
Message-ID: | 4119.1020308296@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> I've been taking a look at fixing the TODO item:
> o Allow INSERT INTO tab (col1, ..) VALUES (val1, ..), (val2, ..)
> My first plan of attack was to replace the current list of ResTargets
> in InsertStmt with a list of lists.
If you look at the SQL spec, they actually consider VALUES to be a
<table value constructor> which is one of the base cases for <query
expression>. Thus for example this is legal SQL (copied and pasted
straight from the spec):
CONSTRAINT VIEWS_IS_UPDATABLE_CHECK_OPTION_CHECK
CHECK ( ( IS_UPDATABLE, CHECK_OPTION ) NOT IN
( VALUES ( 'NO', 'CASCADED' ), ( 'NO', 'LOCAL' ) ) )
So one should really think of INSERT...VALUES as a form of
INSERT...SELECT rather than a special case of its own. INSERT...SELECT
is currently extremely klugy (look at the hacks in the rewriter for it)
so I think you will not get very far until you redesign the querytree
structure for INSERT...SELECT.
BTW, all the non-trivial cases for VALUES are Full SQL only, not entry
or even intermediate level. So I don't see much point in providing a
half-baked implementation. We've still got lots left to do to cover
all of the entry-SQL spec, and IMHO we ought to focus on that stuff
first...
regards, tom lane
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Rod Taylor <rbt(at)zort(dot)ca> |
Cc: | Neil Conway <nconway(at)klamath(dot)dyndns(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: insert with multiple targetLists |
Date: | 2002-06-02 05:53:11 |
Message-ID: | 200206020553.g525rBK11268@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Rod Taylor wrote:
> > INSERT INTO t1 (c1) VALUES (1), (2);
> >
> > would be executed in a similar fashion to:
> >
> > INSERT INTO t1 (c1) VALUES (1);
> > INSERT INTO t1 (c1) VALUES (2);
> >
> > Does this sound reasonable?
Sounds good to me.
> I debated doing the above too. In fact, I had a partial
> implementation at one point.
>
> However, the resulting purpose of allowing such a construct is to
> enable the speeds copy achieves with the variation that is found in an
> insert. ...
I thought the purpose of the item was merely for compatibility with
other databases that support this syntax. I don't think it will ever
match COPY performance, and I don't think stuffing a huge INSERT into
the database rather than COPY rows will ever be a preferred method.
I only see VALUES used by INSERT so if you can think of a clean way to
make that work as multiple INSERTs, I think it would be a good idea.
Hopefully, it will be one localized change, and we can remove it if we
ever want to support VALUES in more complex situations, as Tom
mentioned.
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026