Joe Conway wrote:
>>>
>>>>> . multiple values clauses for INSERT
>
> The best way might be to fabricate a selectStmt equiv to
> "SELECT <targetlist> UNION ALL SELECT <targetlist>...",
> but that still feels like a hack.

Here is a patch pursuant to my earlier post. It has the advantage of
being fairly simple and noninvasive.

The major downside is that somewhere between 9000 and 10000
VALUES-targetlists produces "ERROR: stack depth limit exceeded".
Perhaps for the typical use-case this is sufficient though.

I'm open to better ideas, comments, objections...

Thanks,

Joe


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

> The major downside is that somewhere between 9000 and 10000
> VALUES-targetlists produces "ERROR: stack depth limit exceeded".
> Perhaps for the typical use-case this is sufficient though.
>
> I'm open to better ideas, comments, objections...

If the use case is people running MySQL dumps, then there will be
millions of values-targetlists in MySQL dumps.

Chris

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Andrew Dunstan wrote:
> Christopher Kings-Lynne wrote:
>
>>> The major downside is that somewhere between 9000 and 10000
>>> VALUES-targetlists produces "ERROR: stack depth limit exceeded".
>>> Perhaps for the typical use-case this is sufficient though.
>>>
>>> I'm open to better ideas, comments, objections...
>>
>> If the use case is people running MySQL dumps, then there will be
>> millions of values-targetlists in MySQL dumps.
>
> Yeah. The fabricated select hack does feel wrong to me. Taking a quick
> 2 minute look at the grammar it looks like a better bet would be to make
> InsertStmt.targetList a list of lists of values rather than just a list
> of values. Of course, that would make the changes more invasive. Even
> with that we'd still be reading the whole thing into memory ... is there
> a sane way to cache the inline data before statement execution?

I started down the path of making InsertStmt.targetList a list of
targetlists. The problem is finding a reasonable way to make that
available to the executor. Back to the drawing board I guess.

I have similar concerns with the millions of values-targetlists comment
that Chris made. But I don't see how we can cache the data easily short
of inventing a List alternative that spills to disk.

> I guess we can just say that for true bulk load our supported mechanism
> is still just COPY, but it would be a pity to restrict a feature that is
> in the standard that way.

True

Joe

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Christopher Kings-Lynne wrote:

>> The major downside is that somewhere between 9000 and 10000
>> VALUES-targetlists produces "ERROR: stack depth limit exceeded".
>> Perhaps for the typical use-case this is sufficient though.
>>
>> I'm open to better ideas, comments, objections...
>
>
> If the use case is people running MySQL dumps, then there will be
> millions of values-targetlists in MySQL dumps.
>
>

Yeah. The fabricated select hack does feel wrong to me. Taking a quick
2 minute look at the grammar it looks like a better bet would be to make
InsertStmt.targetList a list of lists of values rather than just a list
of values. Of course, that would make the changes more invasive. Even
with that we'd still be reading the whole thing into memory ... is there
a sane way to cache the inline data before statement execution?

I guess we can just say that for true bulk load our supported mechanism
is still just COPY, but it would be a pity to restrict a feature that is
in the standard that way.

cheers

andrew


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

>> If the use case is people running MySQL dumps, then there will be
>> millions of values-targetlists in MySQL dumps.

I did some experimentation just now, and could not get mysql to accept a
command longer than about 1 million bytes. It complains about
Got a packet bigger than 'max_allowed_packet' bytes
which seems a bit odd because max_allowed_packet is allegedly set to
16 million, but anyway I don't think people are going to be loading any
million-row tables using single INSERT commands in mysql either.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

chris.kings-lynne@calorieking.com (Christopher Kings-Lynne) writes:
>> The major downside is that somewhere between 9000 and 10000
>> VALUES-targetlists produces "ERROR: stack depth limit
>> exceeded". Perhaps for the typical use-case this is sufficient
>> though.
>> I'm open to better ideas, comments, objections...
>
> If the use case is people running MySQL dumps, then there will be
> millions of values-targetlists in MySQL dumps.

Curiosity: How do *does* TheirSQL parse that, and not have the One
Gigantic Query blow up their query parser?
--
output = reverse("gro.gultn" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/unix.html
JOHN CAGE (strapped to table): Do you really expect me to conduct this
antiquated tonal system?
LEONARD BERNSTEIN: No, Mr. Cage, I expect you to die!
[With apologies to music and James Bond fans the world over...]

Chris Browne wrote:

>chris.kings-lynne@calorieking.com (Christopher Kings-Lynne) writes:
>
>
>>>The major downside is that somewhere between 9000 and 10000
>>>VALUES-targetlists produces "ERROR: stack depth limit
>>>exceeded". Perhaps for the typical use-case this is sufficient
>>>though.
>>>I'm open to better ideas, comments, objections...
>>>
>>>
>>If the use case is people running MySQL dumps, then there will be
>>millions of values-targetlists in MySQL dumps.
>>
>>
>
>Curiosity: How do *does* TheirSQL parse that, and not have the One
>Gigantic Query blow up their query parser?
>
>

Experimentation shows that mysqldump breaks up the insert into chunks.

Example with 10m rows:

[ad@wired-219 ~]# perl -e 'print "drop table if exists foo; create table
foo (x int);\n"; foreach my $i (0..9_9999) { print "insert into foo
values \n"; foreach my $j (0..99) { print "," if $j; print
"(",100*$i+$j+1,")"; } print ";\n"; } ' > gggggg
[ad@wired-219 ~]# mysql test < gggggg
[ad@wired-219 ~]# mysqldump test foo > aaaaaa
[ad@wired-219 ~]# mysql test < aaaaaa
[ad@wired-219 ~]# grep INSERT aaaaaa | wc -l
104


cheers

andrew





---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> I did some experimentation just now, and could not get mysql to accept a
> command longer than about 1 million bytes. It complains about
> Got a packet bigger than 'max_allowed_packet' bytes
> which seems a bit odd because max_allowed_packet is allegedly set to
> 16 million, but anyway I don't think people are going to be loading any
> million-row tables using single INSERT commands in mysql either.

Ah no, I'm mistaken. It's not by default in mysqldump, but it does seem
"recommended". This is from "man mysqldump":

-e|--extended-insert
Allows utilization of the new, much faster INSERT syntax.


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> I did some experimentation just now, and could not get mysql to accept a
> command longer than about 1 million bytes. It complains about
> Got a packet bigger than 'max_allowed_packet' bytes
> which seems a bit odd because max_allowed_packet is allegedly set to
> 16 million, but anyway I don't think people are going to be loading any
> million-row tables using single INSERT commands in mysql either.

Strange. Last time I checked I thought MySQL dump used 'multivalue
lists in inserts' for dumps, for the same reason that we use COPY


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Christopher Kings-Lynne <chris.kings-lynne@calorieking.com> writes:
> Strange. Last time I checked I thought MySQL dump used 'multivalue
> lists in inserts' for dumps, for the same reason that we use COPY

I think Andrew identified the critical point upthread: they don't try
to put an unlimited number of rows into one INSERT, only a megabyte
or so's worth. Typical klugy-but-effective mysql design approach ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org