pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feed8.2 features?
56+ messages / 20 participants
[nested] [flat]
* 8.2 features?
@ 2006-07-13 18:56 Andrew Dunstan <andrew@dunslane.net>
2006-07-13 19:32 ` Re: 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 56+ messages in thread
From: Andrew Dunstan @ 2006-07-13 18:56 UTC (permalink / raw)
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
What is the state of the following items that have been previously
discussed?
. MERGE (at least in PK case)
. multiple values clauses for INSERT
. recursive WITH queries
Thanks
andrew
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
@ 2006-07-13 19:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-13 19:42 ` Re: 8.2 features? Jonah H. Harris <jonah.harris@gmail.com>
2006-07-13 21:02 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 22:12 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
1 sibling, 3 replies; 56+ messages in thread
From: Tom Lane @ 2006-07-13 19:32 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Andrew Dunstan <andrew@dunslane.net> writes:
> What is the state of the following items that have been previously
> discussed?
> . MERGE (at least in PK case)
No submitted patch; no one working on it AFAIK; doesn't look like
something that could get done in the next three weeks.
> . multiple values clauses for INSERT
Also not done, but if we are willing to accept a limited patch
(ie, not necessarily everything that SQL92 says you can do with
VALUES, but at least the INSERT case) I think it could get done.
I might even volunteer to do it ... but won't object if someone
else volunteers to.
> . recursive WITH queries
I believe Jonah has given up on fixing the originally-submitted
patch, but he mentioned at the code sprint that non-recursive
WITH is potentially doable in time for 8.2. Not sure if that's
a sufficiently important case to be worth doing.
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 19:32 ` Re: 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-13 19:42 ` Jonah H. Harris <jonah.harris@gmail.com>
2 siblings, 0 replies; 56+ messages in thread
From: Jonah H. Harris @ 2006-07-13 19:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Dunstan <andrew@dunslane.net>; PostgreSQL-development <pgsql-hackers@postgresql.org>
On 7/13/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > . recursive WITH queries
>
> I believe Jonah has given up on fixing the originally-submitted
> patch, but he mentioned at the code sprint that non-recursive
> WITH is potentially doable in time for 8.2. Not sure if that's
> a sufficiently important case to be worth doing.
A working WITH clause which will work in most usual use-cases will be submitted.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 19:32 ` Re: 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-13 21:02 ` Andrew Dunstan <andrew@dunslane.net>
2006-07-13 21:09 ` Re: 8.2 features? Jonah H. Harris <jonah.harris@gmail.com>
2 siblings, 1 reply; 56+ messages in thread
From: Andrew Dunstan @ 2006-07-13 21:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Tom Lane wrote:
>>. multiple values clauses for INSERT
>>
>>
>
>Also not done, but if we are willing to accept a limited patch
>(ie, not necessarily everything that SQL92 says you can do with
>VALUES, but at least the INSERT case) I think it could get done.
>I might even volunteer to do it ... but won't object if someone
>else volunteers to.
>
>
>
I would be very happy to see it accepted.
cheers
andrew
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 19:32 ` Re: 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-13 21:02 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
@ 2006-07-13 21:09 ` Jonah H. Harris <jonah.harris@gmail.com>
2006-07-13 22:11 ` Re: 8.2 features? David Fetter <david@fetter.org>
0 siblings, 1 reply; 56+ messages in thread
From: Jonah H. Harris @ 2006-07-13 21:09 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL-development <pgsql-hackers@postgresql.org>
On 7/13/06, Andrew Dunstan <andrew@dunslane.net> wrote:
> >>. multiple values clauses for INSERT
>
> I would be very happy to see it accepted.
Same here.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 19:32 ` Re: 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-13 21:02 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 21:09 ` Re: 8.2 features? Jonah H. Harris <jonah.harris@gmail.com>
@ 2006-07-13 22:11 ` David Fetter <david@fetter.org>
0 siblings, 0 replies; 56+ messages in thread
From: David Fetter @ 2006-07-13 22:11 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL-development <pgsql-hackers@postgresql.org>
On Thu, Jul 13, 2006 at 05:09:32PM -0400, Jonah H. Harris wrote:
> On 7/13/06, Andrew Dunstan <andrew@dunslane.net> wrote:
> >>>. multiple values clauses for INSERT
> >
> >I would be very happy to see it accepted.
>
> Same here.
<aol>Me, too!</aol>
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 19:32 ` Re: 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-13 22:12 ` Joe Conway <mail@joeconway.com>
2 siblings, 0 replies; 56+ messages in thread
From: Joe Conway @ 2006-07-13 22:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Dunstan <andrew@dunslane.net>; PostgreSQL-development <pgsql-hackers@postgresql.org>
Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>
>>What is the state of the following items that have been previously
>>discussed?
>>. multiple values clauses for INSERT
>
> Also not done, but if we are willing to accept a limited patch
> (ie, not necessarily everything that SQL92 says you can do with
> VALUES, but at least the INSERT case) I think it could get done.
> I might even volunteer to do it ... but won't object if someone
> else volunteers to.
I'm looking to contribute something useful for the 8.2 release, and it
seems Bernd is going to finish up updateable views himself, so I'd be
glad to take a look (at the limited case, that is). Any landmines I
should watch out for?
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
@ 2006-07-13 23:23 ` Peter Eisentraut <peter_e@gmx.net>
2006-07-14 01:30 ` Re: 8.2 features? Stephen Frost <sfrost@snowman.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
1 sibling, 2 replies; 56+ messages in thread
From: Peter Eisentraut @ 2006-07-13 23:23 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Andrew Dunstan wrote:
> . MERGE (at least in PK case)
I think that died after we figured out that it didn't do the sort of
UPDATE-else-INSERT thing that people wanted out of it.
> . multiple values clauses for INSERT
Susanne Ebrecht <susanne.ebrecht@credativ.de> was last heard to work on
it. Updates, Susanne?
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
@ 2006-07-14 01:30 ` Stephen Frost <sfrost@snowman.net>
2006-07-14 01:50 ` Re: 8.2 features? Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 1 reply; 56+ messages in thread
From: Stephen Frost @ 2006-07-14 01:30 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Andrew Dunstan <andrew@dunslane.net>; PostgreSQL-development <pgsql-hackers@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
* Peter Eisentraut (peter_e@gmx.net) wrote:
> Andrew Dunstan wrote:
> > . MERGE (at least in PK case)
>
> I think that died after we figured out that it didn't do the sort of
> UPDATE-else-INSERT thing that people wanted out of it.
I agree that it's probably not going to happen for 8.2 but I certainly
have uses for the SQL spec's definition of MERGE (table-level instead of
the individual-tuple case). I'd like to see the individual-tuple
UPSERT/REPLACE issue handled as well but I don't believe MERGE lacking
that necessairly means MERGE should be ignored..
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 01:30 ` Re: 8.2 features? Stephen Frost <sfrost@snowman.net>
@ 2006-07-14 01:50 ` Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 56+ messages in thread
From: Jonah H. Harris @ 2006-07-14 01:50 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; Andrew Dunstan <andrew@dunslane.net>; PostgreSQL-development <pgsql-hackers@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
On 7/13/06, Stephen Frost <sfrost@snowman.net> wrote:
> I agree that it's probably not going to happen for 8.2 but I certainly
> have uses for the SQL spec's definition of MERGE (table-level instead of
> the individual-tuple case). I'd like to see the individual-tuple
> UPSERT/REPLACE issue handled as well but I don't believe MERGE lacking
> that necessairly means MERGE should be ignored..
Where does Jan stand on it, I know he was doing some thinking about
how to accomplish it.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
@ 2006-07-14 14:26 ` Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 10:27 ` Re: 8.2 features? Susanne Ebrecht <miracee@miracee.de>
1 sibling, 2 replies; 56+ messages in thread
From: Bernd Helmle @ 2006-07-14 14:26 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Andrew Dunstan <andrew@dunslane.net>; PostgreSQL-development <pgsql-hackers@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
--On Freitag, Juli 14, 2006 01:23:11 +0200 Peter Eisentraut
<peter_e@gmx.net> wrote:
>> . multiple values clauses for INSERT
>
> Susanne Ebrecht <susanne.ebrecht@credativ.de> was last heard to work on
> it. Updates, Susanne?
I've talked to Susanne today and she's just back from hospital and not
available
online until next week.
She was working on the SET (col1, col2) = (val1, val2) syntax for UPDATE
commands.
Don't know what the status is on this, though.
--
Thanks
Bernd
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
@ 2006-07-14 14:45 ` Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 56+ messages in thread
From: Andrew Dunstan @ 2006-07-14 14:45 UTC (permalink / raw)
To: Bernd Helmle <mailings@oopsware.de>; +Cc: Peter Eisentraut <peter_e@gmx.net>; PostgreSQL-development <pgsql-hackers@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Bernd Helmle wrote:
>
>
> --On Freitag, Juli 14, 2006 01:23:11 +0200 Peter Eisentraut
> <peter_e@gmx.net> wrote:
>
>>> . multiple values clauses for INSERT
>>
>>
>> Susanne Ebrecht <susanne.ebrecht@credativ.de> was last heard to work on
>> it. Updates, Susanne?
>
>
> I've talked to Susanne today and she's just back from hospital and not
> available
> online until next week.
> She was working on the SET (col1, col2) = (val1, val2) syntax for
> UPDATE commands.
> Don't know what the status is on this, though.
>
Not the same thing, surely. So maybe we should gratefully accept Joe
Conway's offer to work on it.
cheers
andrew
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
@ 2006-07-17 21:42 ` Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 56+ messages in thread
From: Joe Conway @ 2006-07-17 21:42 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; PostgreSQL-development <pgsql-hackers@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Andrew Dunstan wrote:
> Bernd Helmle wrote:
>>
>> --On Freitag, Juli 14, 2006 01:23:11 +0200 Peter Eisentraut
>> <peter_e@gmx.net> wrote:
>>
>>>> . multiple values clauses for INSERT
>>>
>>> Susanne Ebrecht <susanne.ebrecht@credativ.de> was last heard to work on
>>> it. Updates, Susanne?
>>
>> I've talked to Susanne today and she's just back from hospital and not
>> available online until next week.
>> She was working on the SET (col1, col2) = (val1, val2) syntax for
>> UPDATE commands.
>> Don't know what the status is on this, though.
>
> Not the same thing, surely. So maybe we should gratefully accept Joe
> Conway's offer to work on it.
I've played with this a bit now, and the grammar changes seem pretty
straightforward, but the other half is kind of ugly. I can't see a good
way to propagate multiple targetlists that isn't a big hack.
The best way might be to fabricate a selectStmt equiv to
"SELECT <targetlist> UNION ALL SELECT <targetlist>...",
but that still feels like a hack.
Have there been any past discussions on how this might be implemented
(FWIW I couldn't find any in the archives)? Any better ideas for an
approach?
Thanks,
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-18 04:39 ` Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
0 siblings, 1 reply; 56+ messages in thread
From: Joe Conway @ 2006-07-18 04:39 UTC (permalink / raw)
To: ; +Cc: Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
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
Attachments:
multi-insert.diff (text/x-patch, 3.8K) download
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.551
diff -c -r2.551 gram.y
*** src/backend/parser/gram.y 3 Jul 2006 22:45:39 -0000 2.551
--- src/backend/parser/gram.y 18 Jul 2006 04:19:45 -0000
***************
*** 238,251 ****
qualified_name_list any_name any_name_list
any_operator expr_list attrs
target_list update_target_list insert_column_list
! insert_target_list def_list indirection opt_indirection
! group_clause TriggerFuncArgs select_limit
! opt_select_limit opclass_item_list
! transaction_mode_list_or_empty
TableFuncElementList
prep_type_clause prep_type_list
execute_param_clause using_clause
%type <range> into_clause OptTempTableName
%type <defelt> createfunc_opt_item common_func_opt_item
--- 238,253 ----
qualified_name_list any_name any_name_list
any_operator expr_list attrs
target_list update_target_list insert_column_list
! insert_target_els
! def_list indirection opt_indirection group_clause
! TriggerFuncArgs select_limit opt_select_limit
! opclass_item_list transaction_mode_list_or_empty
TableFuncElementList
prep_type_clause prep_type_list
execute_param_clause using_clause
+ %type <node> insert_target_list insert_target_lists
+
%type <range> into_clause OptTempTableName
%type <defelt> createfunc_opt_item common_func_opt_item
***************
*** 5349,5360 ****
;
insert_rest:
! VALUES '(' insert_target_list ')'
{
$$ = makeNode(InsertStmt);
$$->cols = NIL;
! $$->targetList = $3;
! $$->selectStmt = NULL;
}
| DEFAULT VALUES
{
--- 5351,5370 ----
;
insert_rest:
! VALUES insert_target_lists
{
$$ = makeNode(InsertStmt);
$$->cols = NIL;
! if (((SelectStmt *) $2)->op == SETOP_UNION)
! {
! $$->targetList = NIL;
! $$->selectStmt = $2;
! }
! else
! {
! $$->targetList = ((SelectStmt *) $2)->targetList;
! $$->selectStmt = NULL;
! }
}
| DEFAULT VALUES
{
***************
*** 5370,5381 ****
$$->targetList = NIL;
$$->selectStmt = $1;
}
! | '(' insert_column_list ')' VALUES '(' insert_target_list ')'
{
$$ = makeNode(InsertStmt);
$$->cols = $2;
! $$->targetList = $6;
! $$->selectStmt = NULL;
}
| '(' insert_column_list ')' SelectStmt
{
--- 5380,5399 ----
$$->targetList = NIL;
$$->selectStmt = $1;
}
! | '(' insert_column_list ')' VALUES insert_target_lists
{
$$ = makeNode(InsertStmt);
$$->cols = $2;
! if (((SelectStmt *) $5)->op == SETOP_UNION)
! {
! $$->targetList = NIL;
! $$->selectStmt = $5;
! }
! else
! {
! $$->targetList = ((SelectStmt *) $5)->targetList;
! $$->selectStmt = NULL;
! }
}
| '(' insert_column_list ')' SelectStmt
{
***************
*** 8189,8197 ****
;
insert_target_list:
! insert_target_el { $$ = list_make1($1); }
! | insert_target_list ',' insert_target_el { $$ = lappend($1, $3); }
;
insert_target_el:
--- 8207,8235 ----
;
+ insert_target_lists:
+ insert_target_list
+ {
+ $$ = $1;
+ }
+ | insert_target_lists ',' insert_target_list
+ {
+ $$ = makeSetOp(SETOP_UNION, TRUE, $1, $3);
+ }
+ ;
+
insert_target_list:
! '(' insert_target_els ')'
! {
! SelectStmt *n = makeNode(SelectStmt);
! n->targetList = $2;
! $$ = (Node *) n;
! }
! ;
!
! insert_target_els:
! insert_target_el { $$ = list_make1($1); }
! | insert_target_els ',' insert_target_el { $$ = lappend($1, $3); }
;
insert_target_el:
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-18 12:13 ` Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:49 ` Re: [HACKERS] 8.2 features? Chris Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 56+ messages in thread
From: Christopher Kings-Lynne @ 2006-07-18 12:13 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
> 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
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
@ 2006-07-18 14:36 ` Andrew Dunstan <andrew@dunslane.net>
2006-07-18 15:05 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 56+ messages in thread
From: Andrew Dunstan @ 2006-07-18 14:36 UTC (permalink / raw)
To: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; +Cc: Joe Conway <mail@joeconway.com>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
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
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
@ 2006-07-18 15:05 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 56+ messages in thread
From: Joe Conway @ 2006-07-18 15:05 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
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
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
@ 2006-07-18 18:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-18 21:39 ` Re: [PATCHES] 8.2 features? Thomas Bley <thbley@gmail.com>
2006-07-18 23:41 ` Re: [PATCHES] 8.2 features? Matthew D. Fuller <fullermd@over-yonder.net>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 01:20 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
1 sibling, 4 replies; 56+ messages in thread
From: Tom Lane @ 2006-07-18 18:19 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org, Andrew Dunstan <andrew@dunslane.net>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Joe Conway <mail@joeconway.com>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
>> 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
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-18 21:39 ` Thomas Bley <thbley@gmail.com>
3 siblings, 0 replies; 56+ messages in thread
From: Thomas Bley @ 2006-07-18 21:39 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
from http://dev.mysql.com/doc/refman/4.1/en/blob.html
You can change the message buffer size by changing the value of the
max_allowed_packet variable, but you must do so for both the server and
your client program. For example, both mysql and mysqldump allow you to
change the client-side max_allowed_packet value.
Tom Lane wrote:
>>> 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
>
>
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-18 23:41 ` Matthew D. Fuller <fullermd@over-yonder.net>
3 siblings, 0 replies; 56+ messages in thread
From: Matthew D. Fuller @ 2006-07-18 23:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
On Tue, Jul 18, 2006 at 02:19:01PM -0400 I heard the voice of
Tom Lane, and lo! it spake thus:
>
> 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.
On the contrary, I've hit it several times by just trying to import
[into another database] the output of a mysqldump I just did. Great
design, that...
--
Matthew Fuller (MF4839) | fullermd@over-yonder.net
Systems/Network Administrator | http://www.over-yonder.net/~fullermd/
On the Internet, nobody can hear you scream.
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-19 01:18 ` Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 56+ messages in thread
From: Christopher Kings-Lynne @ 2006-07-19 01:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org, Andrew Dunstan <andrew@dunslane.net>; Joe Conway <mail@joeconway.com>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
> 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
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
@ 2006-07-19 03:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 56+ messages in thread
From: Tom Lane @ 2006-07-19 03:32 UTC (permalink / raw)
To: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; +Cc: pgsql-hackers@postgresql.org, Andrew Dunstan <andrew@dunslane.net>; Joe Conway <mail@joeconway.com>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
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
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-19 04:20 ` Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 56+ messages in thread
From: Joe Conway @ 2006-07-19 04:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Tom Lane wrote:
> 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 ...
OK, so given that we don't need to be able to do 1 million
multi-targetlist insert statements, here is rev 2 of the patch.
It is just slightly more invasive, but performs *much* better. In fact,
it can handle as many targetlists as you have memory to deal with. It
also deals with DEFAULT values in the targetlist.
I've attached a php script that I used to do crude testing. Basically I
tested 3 cases in this order:
single-INSERT-multi-statement:
------------------------------
"INSERT INTO foo2a (f1,f2) VALUES (1,2);"
-- repeat statement $loopcount times
single-INSERT-at-once:
----------------------
"INSERT INTO foo2b (f1,f2) VALUES (1,2);INSERT INTO foo2a (f1,f2)
VALUES (1,2);INSERT INTO foo2a (f1,f2) VALUES (1,2)..."
-- build a single SQL string by looping $loopcount times,
-- and execute it all at once
multi-INSERT-at-once:
---------------------
"INSERT INTO foo2c (f1,f2) VALUES (1,2),(1,2),(1,2)..."
-- build a single SQL string by looping $loopcount times,
-- and execute it all at once
Here are the results:
$loopcount = 100000;
single-INSERT-multi-statement Elapsed time is 34 seconds
single-INSERT-at-once Elapsed time is 7 seconds
multi-INSERT-at-once Elapsed time is 4 seconds
about 370MB peak memory usage
$loopcount = 200000;
single-INSERT-multi-statement Elapsed time is 67 seconds
single-INSERT-at-once Elapsed time is 12 seconds
multi-INSERT-at-once Elapsed time is 9 seconds
about 750MB peak memory usage
$loopcount = 300000;
single-INSERT-multi-statement Elapsed time is 101 seconds
single-INSERT-at-once Elapsed time is 18 seconds
multi-INSERT-at-once Elapsed time is 13 seconds
about 1.1GB peak memory usage
Somewhere beyond this, my machine goes into swap hell, and I didn't have
the patience to wait for it to complete :-)
It would be interesting to see a side-by-side comparison with MySQL
since that seems to be our benchmark on this feature. I'll try to do
that tomorrow if no one beats me to it.
There is only one downside to the current approach that I'm aware of.
The command-result tag is only set by the "original" query, meaning that
even if you insert 300,000 rows using this method, the command-result
tag looks like "INSERT 0 1"; e.g.:
regression=# create table foo2(f1 int default 42,f2 int default 6);
CREATE TABLE
regression=# insert into foo2 (f1,f2) values
(default,12),(default,10),(115,21);
INSERT 0 1
regression=# select * from foo2;
f1 | f2
-----+----
42 | 12
42 | 10
115 | 21
(3 rows)
Any thoughts on how to fix that?
Thanks,
Joe
Attachments:
multi-insert-r2.diff (text/x-patch, 4.7K) download
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.340
diff -c -r1.340 analyze.c
*** src/backend/parser/analyze.c 14 Jul 2006 14:52:21 -0000 1.340
--- src/backend/parser/analyze.c 19 Jul 2006 03:53:35 -0000
***************
*** 657,667 ****
}
else
{
/*
* For INSERT ... VALUES, transform the given list of values to form a
! * targetlist for the INSERT.
*/
! qry->targetList = transformTargetList(pstate, stmt->targetList);
}
/*
--- 657,699 ----
}
else
{
+ ListCell *tlr;
+
/*
* For INSERT ... VALUES, transform the given list of values to form a
! * targetlist for the INSERT. In a multi-targetlist INSERT, append all
! * but the first targetlist to extras_after to be processed later by
! * do_parse_analyze
*/
! qry->targetList = NIL;
! foreach(tlr, stmt->targetList)
! {
! List *tgtlist = (List *) lfirst(tlr);
!
! if (qry->targetList == NIL)
! {
! /* transform the first targetlist */
! qry->targetList = transformTargetList(pstate, tgtlist);
! }
! else
! {
! /*
! * Create an InsertStmt node for each additional targetlist
! * and append to extras_after
! */
! InsertStmt *insnode = makeNode(InsertStmt);
!
! insnode->cols = NIL;
! insnode->targetList = list_make1(tgtlist);
! insnode->selectStmt = NULL;
! insnode->relation = stmt->relation;
!
! if (*extras_after == NIL)
! *extras_after = list_make1(insnode);
! else
! *extras_after = lappend(*extras_after, insnode);
! }
! }
}
/*
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.551
diff -c -r2.551 gram.y
*** src/backend/parser/gram.y 3 Jul 2006 22:45:39 -0000 2.551
--- src/backend/parser/gram.y 19 Jul 2006 03:53:40 -0000
***************
*** 238,247 ****
qualified_name_list any_name any_name_list
any_operator expr_list attrs
target_list update_target_list insert_column_list
! insert_target_list def_list indirection opt_indirection
! group_clause TriggerFuncArgs select_limit
! opt_select_limit opclass_item_list
! transaction_mode_list_or_empty
TableFuncElementList
prep_type_clause prep_type_list
execute_param_clause using_clause
--- 238,247 ----
qualified_name_list any_name any_name_list
any_operator expr_list attrs
target_list update_target_list insert_column_list
! insert_target_els insert_target_list insert_target_lists
! def_list indirection opt_indirection group_clause
! TriggerFuncArgs select_limit opt_select_limit
! opclass_item_list transaction_mode_list_or_empty
TableFuncElementList
prep_type_clause prep_type_list
execute_param_clause using_clause
***************
*** 5349,5359 ****
;
insert_rest:
! VALUES '(' insert_target_list ')'
{
$$ = makeNode(InsertStmt);
$$->cols = NIL;
! $$->targetList = $3;
$$->selectStmt = NULL;
}
| DEFAULT VALUES
--- 5349,5359 ----
;
insert_rest:
! VALUES insert_target_lists
{
$$ = makeNode(InsertStmt);
$$->cols = NIL;
! $$->targetList = $2;
$$->selectStmt = NULL;
}
| DEFAULT VALUES
***************
*** 5370,5380 ****
$$->targetList = NIL;
$$->selectStmt = $1;
}
! | '(' insert_column_list ')' VALUES '(' insert_target_list ')'
{
$$ = makeNode(InsertStmt);
$$->cols = $2;
! $$->targetList = $6;
$$->selectStmt = NULL;
}
| '(' insert_column_list ')' SelectStmt
--- 5370,5380 ----
$$->targetList = NIL;
$$->selectStmt = $1;
}
! | '(' insert_column_list ')' VALUES insert_target_lists
{
$$ = makeNode(InsertStmt);
$$->cols = $2;
! $$->targetList = $5;
$$->selectStmt = NULL;
}
| '(' insert_column_list ')' SelectStmt
***************
*** 8189,8197 ****
;
insert_target_list:
! insert_target_el { $$ = list_make1($1); }
! | insert_target_list ',' insert_target_el { $$ = lappend($1, $3); }
;
insert_target_el:
--- 8189,8215 ----
;
+ insert_target_lists:
+ insert_target_list
+ {
+ $$ = list_make1($1);
+ }
+ | insert_target_lists ',' insert_target_list
+ {
+ $$ = lappend($1, $3);
+ }
+ ;
+
insert_target_list:
! '(' insert_target_els ')'
! {
! $$ = $2;
! }
! ;
!
! insert_target_els:
! insert_target_el { $$ = list_make1($1); }
! | insert_target_els ',' insert_target_el { $$ = lappend($1, $3); }
;
insert_target_el:
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-19 21:41 ` Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 56+ messages in thread
From: Joe Conway @ 2006-07-19 21:41 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Joe Conway wrote:
> Tom Lane wrote:
>
>> 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 ...
>
> OK, so given that we don't need to be able to do 1 million
> multi-targetlist insert statements, here is rev 2 of the patch.
I did some testing today against mysql and found that it will easily
absorb insert statements with 1 million targetlists provided you set
max_allowed_packet high enough for the server. It peaked out at about
600MB, compared to my test similar last night where it was using about
3.8 GB when I killed it.
So the question is, do we care?
If we do, I'll start looking for a new rev 3 strategy (ideas/pointers
etc very welcome). If not, I'll start working on docs and regression test.
Thanks,
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-20 03:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 04:16 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 56+ messages in thread
From: Tom Lane @ 2006-07-20 03:58 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Joe Conway <mail@joeconway.com> writes:
> I did some testing today against mysql and found that it will easily
> absorb insert statements with 1 million targetlists provided you set
> max_allowed_packet high enough for the server. It peaked out at about
> 600MB, compared to my test similar last night where it was using about
> 3.8 GB when I killed it.
> So the question is, do we care?
What's the performance like relative to mysql? It seems hard to believe
that we can afford the overhead of a separate INSERT statement per row
(duplicating all the work of parse analysis, rewrite, planning, executor
start/stop) ... at least not without looking mighty bad.
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-20 04:16 ` Joe Conway <mail@joeconway.com>
2006-07-20 04:36 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 56+ messages in thread
From: Joe Conway @ 2006-07-20 04:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>
>>I did some testing today against mysql and found that it will easily
>>absorb insert statements with 1 million targetlists provided you set
>>max_allowed_packet high enough for the server. It peaked out at about
>>600MB, compared to my test similar last night where it was using about
>>3.8 GB when I killed it.
>
>>So the question is, do we care?
>
> What's the performance like relative to mysql? It seems hard to believe
> that we can afford the overhead of a separate INSERT statement per row
> (duplicating all the work of parse analysis, rewrite, planning, executor
> start/stop) ... at least not without looking mighty bad.
I don't have the exact numbers handy, but not too great.
As I recall, with last night's patch we did 100K inserts in about 4
seconds, and today mysql did 100K in about 1 second. We never finished
the 1 million insert test due to swapping (I killed it after quite a
while), and mysql did 1 million in about 18 seconds (we did 300K in 13
seconds). The hardware was not identical between last night's test and
today's on mysql, but very similar (similar CPUs and memory, although
the machine I did the mysql tests on had scsi drives, while the pg test
was done on sata).
The difficulty is finding a way to avoid all that extra work without a
very ugly special case kludge just for inserts. I've been banging my
head on that on-and-off for a few days now, and every idea looks uglier
than the last. One suggestion I got off list was to figure out a way to
build a tuplestore and use it to feed the executor. That's starting to
sound better and better to me.
Any ideas or guidance would be greatly appreciated.
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 04:16 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-20 04:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 05:18 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 56+ messages in thread
From: Tom Lane @ 2006-07-20 04:36 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Joe Conway <mail@joeconway.com> writes:
> The difficulty is finding a way to avoid all that extra work without a
> very ugly special case kludge just for inserts.
[ thinks a bit ... ]
It seems to me that the reason it's painful is exactly that INSERT
... VALUES is a kluge already. We've special-cased the situation where
the INSERT's <query expression> is a <table value constructor> with
exactly one row --- but actually a <table value constructor> with
multiple rows ought to be allowed anywhere you can currently write
"SELECT ...". So ideally fixing this would include eliminating the
current artificial distinction between two types of INSERT command.
I think the place we'd ultimately like to get to involves changing the
executor's Result node type to have a list of targetlists and sequence
through those lists to produce its results (cf Append --- perhaps while
at it, divorce the "gating node" functionality into a different node
type). That part seems clear, what's a bit less clear is what the
ripple effect on the upstream parser/planner data structures should be.
Should *all* occurrences of Query be changed to have a
list-of-targetlists? Sounds ugly, and I don't understand what it would
mean for any Query other than one representing a VALUES construct.
[ thinks some more ... ]
Maybe the right place to put the list-of-targetlists functionality is
not in Query per se, but in a new type of jointree node. This would
localize the impact as far as changing the datastructures go, but I've
not thought hard enough about what the impact would actually be.
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 04:16 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 04:36 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-20 05:18 ` Joe Conway <mail@joeconway.com>
2006-07-20 05:39 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 56+ messages in thread
From: Joe Conway @ 2006-07-20 05:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>
>>The difficulty is finding a way to avoid all that extra work without a
>>very ugly special case kludge just for inserts.
>
> [ thinks a bit ... ]
>
> It seems to me that the reason it's painful is exactly that INSERT
> ... VALUES is a kluge already. We've special-cased the situation where
> the INSERT's <query expression> is a <table value constructor> with
> exactly one row --- but actually a <table value constructor> with
> multiple rows ought to be allowed anywhere you can currently write
> "SELECT ...". So ideally fixing this would include eliminating the
> current artificial distinction between two types of INSERT command.
>
> I think the place we'd ultimately like to get to involves changing the
> executor's Result node type to have a list of targetlists and sequence
> through those lists to produce its results
I was actually just looking at that and ended up thinking that it might
be better to deal with it one level down in ExecProject (because it is
already passing targetlists directly to ExecTargetList).
> That part seems clear, what's a bit less clear is what the
> ripple effect on the upstream parser/planner data structures should be.
> Should *all* occurrences of Query be changed to have a
> list-of-targetlists? Sounds ugly, and I don't understand what it would
> mean for any Query other than one representing a VALUES construct.
There are certainly many places to be looked at if Query.targetList
becomes a list-of-targetlists (about 153 if I grep'd correctly).
>
> [ thinks some more ... ]
>
> Maybe the right place to put the list-of-targetlists functionality is
> not in Query per se, but in a new type of jointree node. This would
> localize the impact as far as changing the datastructures go, but I've
> not thought hard enough about what the impact would actually be.
OK. You've given me a good bit to think about -- thanks!
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 04:16 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 04:36 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 05:18 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-20 05:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 23:13 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 56+ messages in thread
From: Tom Lane @ 2006-07-20 05:39 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> I think the place we'd ultimately like to get to involves changing the
>> executor's Result node type to have a list of targetlists and sequence
>> through those lists to produce its results
> I was actually just looking at that and ended up thinking that it might
> be better to deal with it one level down in ExecProject (because it is
> already passing targetlists directly to ExecTargetList).
I'd vote against that, because (a) ExecProject is used by all executor
node types, and we shouldn't add overhead to all of them for the benefit
of one; (b) ExecProject doesn't actually have any internal state at the
moment. To keep track of which targetlist to evaluate next, it would
not only need some internal state, it would have to be told the current
"es_direction". This stuff fits much better at the exec node level ---
again, I'd suggest looking at Append for a comparison.
But really the executor part of this is not the hard part; what we need
to think about first is what's the impact on the Query datastructure
that the parser/rewriter/planner use.
I'm still liking the idea of pushing multi-values into a jointree node
type. Basically this would suggest representing "VALUES ..." as if it
were "SELECT * FROM VALUES ..." (which I believe is actually legal
syntax per spec) --- in the general case you'd need to have a Query node
that has a trivial "col1, col2, col3, ..." targetlist and then the
multiple values lists are in some kind of jointree entry. But possibly
this could be short-circuited somehow, at least for INSERT.
BTW, I noticed an interesting property of historical Postgres behavior:
you can put a table reference into a VALUES targetlist.
regression=# create table foo (like tenk1);
CREATE TABLE
regression=# insert into foo values (tenk1.*);
ERROR: missing FROM-clause entry for table "tenk1"
LINE 1: insert into foo values (tenk1.*);
^
regression=# set add_missing_from to 1;
SET
regression=# insert into foo values (tenk1.*);
NOTICE: adding missing FROM-clause entry for table "tenk1"
LINE 1: insert into foo values (tenk1.*);
^
INSERT 0 10000
regression=#
So that last is really exactly equivalent to
insert into foo select * from tenk1;
I do not feel a need to support this sort of thing when there are
multiple VALUES targetlists, but it'd be nice not to break it for the
single-targetlist case. At least not till we're ready to disable
add_missing_from entirely.
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 04:16 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 04:36 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 05:18 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 05:39 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-20 23:13 ` Joe Conway <mail@joeconway.com>
2006-07-21 00:46 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 56+ messages in thread
From: Joe Conway @ 2006-07-20 23:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>I was actually just looking at that and ended up thinking that it might
>>be better to deal with it one level down in ExecProject (because it is
>>already passing targetlists directly to ExecTargetList).
>
> I'd vote against that, because (a) ExecProject is used by all executor
> node types, and we shouldn't add overhead to all of them for the benefit
> of one; (b) ExecProject doesn't actually have any internal state at the
> moment. To keep track of which targetlist to evaluate next, it would
> not only need some internal state, it would have to be told the current
> "es_direction". This stuff fits much better at the exec node level ---
> again, I'd suggest looking at Append for a comparison.
OK.
> But really the executor part of this is not the hard part; what we need
> to think about first is what's the impact on the Query datastructure
> that the parser/rewriter/planner use.
After a quick look, I think changing Query.targetList is too big an
impact, and probably unneeded given your suggestion below.
One of the problems with the current code is that the targetList in the
"VALUES..." case is being used for two purposes -- 1) to define the
column types, and 2) to hold the actual data. By putting the data into a
new node type, I think the targetList reverts to being just a list of
datatypes as it is with INSERT ... SELECT ...
> I'm still liking the idea of pushing multi-values into a jointree node
> type. Basically this would suggest representing "VALUES ..." as if it
> were "SELECT * FROM VALUES ..." (which I believe is actually legal
> syntax per spec) --- in the general case you'd need to have a Query node
> that has a trivial "col1, col2, col3, ..." targetlist and then the
> multiple values lists are in some kind of jointree entry. But possibly
> this could be short-circuited somehow, at least for INSERT.
I'm liking this too. But when you say "jointree node", are you saying to
model the new node type after NestLoop/MergeJoin/HashJoin nodes? These
are referred to as "join nodes" in ExecInitNode. Or as you mentioned a
couple of times, should this look more like an Append node?
Thanks,
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 04:16 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 04:36 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 05:18 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 05:39 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 23:13 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-21 00:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-21 03:09 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-21 14:08 ` Re: [PATCHES] 8.2 features? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 56+ messages in thread
From: Tom Lane @ 2006-07-21 00:46 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Joe Conway <mail@joeconway.com> writes:
> I'm liking this too. But when you say "jointree node", are you saying to
> model the new node type after NestLoop/MergeJoin/HashJoin nodes? These
> are referred to as "join nodes" in ExecInitNode. Or as you mentioned a
> couple of times, should this look more like an Append node?
No, I guess I confused you by talking about the executor representation
at the same time. This is really unrelated to the executor. The join
tree I'm thinking of here is the data structure that dangles off
Query.jointree --- it's a representation of the query's FROM clause,
and (at present) can contain RangeTblRef, FromExpr, and JoinExpr nodes.
See the last hundred or so lines of primnodes.h for some details.
The jointree is used by the planner to compute the plan node tree that
the executor will run, but it's not the same thing.
There are basically two ways you could go about this:
1. Make a new jointree leaf node type to represent a VALUES construct,
and dangle the list of lists of expressions off that.
2. Make a new RangeTblEntry type to represent a VALUES construct, and
just put a RangeTblRef to it into the jointree. The expressions
dangle off the RangeTblEntry.
Offhand I'm not certain which of these would be cleanest. The second
way has some similarities to the way we handle set operation trees
(UNION et al), so it might be worth looking at that stuff. However,
being a RangeTblEntry has a lot of baggage (eg, various routines expect
to find an RTE alias, column names, column types, etc) and maybe we
don't need all that for VALUES.
One advantage of the first way is that you could use the same node
type for the "raw parser output" delivered by gram.y. This is a bit of
a type cheat, because raw parser output is logically distinct from what
parse analysis produces, but we do it in lots of other places too
(JoinExpr for instance is used that way). You should in any case have a
clear idea of the difference between the raw and analyzed parser
representations --- for instance, the raw form won't contain any
datatype info, whereas the analyzed form must. This might or might not
need to be visible directly in the VALUES node --- it might be that you
can rely on the datatype info embedded in the analyzed expressions.
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 04:16 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 04:36 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 05:18 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 05:39 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 23:13 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-21 00:46 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-21 03:09 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 56+ messages in thread
From: Joe Conway @ 2006-07-21 03:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Tom Lane wrote:
> No, I guess I confused you by talking about the executor representation
> at the same time. This is really unrelated to the executor. The join
> tree I'm thinking of here is the data structure that dangles off
> Query.jointree --- it's a representation of the query's FROM clause,
> and (at present) can contain RangeTblRef, FromExpr, and JoinExpr nodes.
> See the last hundred or so lines of primnodes.h for some details.
> The jointree is used by the planner to compute the plan node tree that
> the executor will run, but it's not the same thing.
Ah, that helps. Thanks for the explanation. I'll start digging in again...
Thanks,
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 04:16 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 04:36 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 05:18 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-20 05:39 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 23:13 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-21 00:46 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-21 14:08 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 56+ messages in thread
From: Jim C. Nasby @ 2006-07-21 14:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joe Conway <mail@joeconway.com>; Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
On Thu, Jul 20, 2006 at 08:46:13PM -0400, Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> > I'm liking this too. But when you say "jointree node", are you saying to
> > model the new node type after NestLoop/MergeJoin/HashJoin nodes? These
> > are referred to as "join nodes" in ExecInitNode. Or as you mentioned a
> > couple of times, should this look more like an Append node?
>
> No, I guess I confused you by talking about the executor representation
> at the same time. This is really unrelated to the executor. The join
> tree I'm thinking of here is the data structure that dangles off
> Query.jointree --- it's a representation of the query's FROM clause,
> and (at present) can contain RangeTblRef, FromExpr, and JoinExpr nodes.
> See the last hundred or so lines of primnodes.h for some details.
> The jointree is used by the planner to compute the plan node tree that
> the executor will run, but it's not the same thing.
>
> There are basically two ways you could go about this:
> 1. Make a new jointree leaf node type to represent a VALUES construct,
> and dangle the list of lists of expressions off that.
> 2. Make a new RangeTblEntry type to represent a VALUES construct, and
> just put a RangeTblRef to it into the jointree. The expressions
> dangle off the RangeTblEntry.
>
> Offhand I'm not certain which of these would be cleanest. The second
> way has some similarities to the way we handle set operation trees
> (UNION et al), so it might be worth looking at that stuff. However,
> being a RangeTblEntry has a lot of baggage (eg, various routines expect
> to find an RTE alias, column names, column types, etc) and maybe we
> don't need all that for VALUES.
I misread that to include SRFs, but it got me thinking... another
possibility would be to changes VALUES() so that it was treated as a
function, and allow it to have an arbitrary number of parameters. That
would automatically allow the case of SELECT * FROM VALUES(...). INSERT
would need to learn how to accept SRFs, but that would have the nice
side-effect of allowing INSERT INTO table set_returning_function();
Of course, adding the ability for functions to have an arbitrary
argument list could well be more complex than any of the options
discussed thusfar... though it would be a very handy feature to have.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-28 19:47 ` Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 56+ messages in thread
From: Bruce Momjian @ 2006-07-28 19:47 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Are you going to apply this? Seems it is ready.
---------------------------------------------------------------------------
Joe Conway wrote:
> Tom Lane wrote:
> > 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 ...
>
>
> OK, so given that we don't need to be able to do 1 million
> multi-targetlist insert statements, here is rev 2 of the patch.
>
> It is just slightly more invasive, but performs *much* better. In fact,
> it can handle as many targetlists as you have memory to deal with. It
> also deals with DEFAULT values in the targetlist.
>
> I've attached a php script that I used to do crude testing. Basically I
> tested 3 cases in this order:
>
> single-INSERT-multi-statement:
> ------------------------------
> "INSERT INTO foo2a (f1,f2) VALUES (1,2);"
> -- repeat statement $loopcount times
>
> single-INSERT-at-once:
> ----------------------
> "INSERT INTO foo2b (f1,f2) VALUES (1,2);INSERT INTO foo2a (f1,f2)
> VALUES (1,2);INSERT INTO foo2a (f1,f2) VALUES (1,2)..."
> -- build a single SQL string by looping $loopcount times,
> -- and execute it all at once
>
> multi-INSERT-at-once:
> ---------------------
> "INSERT INTO foo2c (f1,f2) VALUES (1,2),(1,2),(1,2)..."
> -- build a single SQL string by looping $loopcount times,
> -- and execute it all at once
>
> Here are the results:
> $loopcount = 100000;
> single-INSERT-multi-statement Elapsed time is 34 seconds
> single-INSERT-at-once Elapsed time is 7 seconds
> multi-INSERT-at-once Elapsed time is 4 seconds
> about 370MB peak memory usage
>
> $loopcount = 200000;
> single-INSERT-multi-statement Elapsed time is 67 seconds
> single-INSERT-at-once Elapsed time is 12 seconds
> multi-INSERT-at-once Elapsed time is 9 seconds
> about 750MB peak memory usage
>
> $loopcount = 300000;
> single-INSERT-multi-statement Elapsed time is 101 seconds
> single-INSERT-at-once Elapsed time is 18 seconds
> multi-INSERT-at-once Elapsed time is 13 seconds
> about 1.1GB peak memory usage
>
> Somewhere beyond this, my machine goes into swap hell, and I didn't have
> the patience to wait for it to complete :-)
>
> It would be interesting to see a side-by-side comparison with MySQL
> since that seems to be our benchmark on this feature. I'll try to do
> that tomorrow if no one beats me to it.
>
> There is only one downside to the current approach that I'm aware of.
> The command-result tag is only set by the "original" query, meaning that
> even if you insert 300,000 rows using this method, the command-result
> tag looks like "INSERT 0 1"; e.g.:
>
> regression=# create table foo2(f1 int default 42,f2 int default 6);
> CREATE TABLE
> regression=# insert into foo2 (f1,f2) values
> (default,12),(default,10),(115,21);
> INSERT 0 1
> regression=# select * from foo2;
> f1 | f2
> -----+----
> 42 | 12
> 42 | 10
> 115 | 21
> (3 rows)
>
> Any thoughts on how to fix that?
>
> Thanks,
>
> Joe
>
>
[ application/x-php is not supported, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
@ 2006-07-28 19:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
0 siblings, 2 replies; 56+ messages in thread
From: Tom Lane @ 2006-07-28 19:54 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Joe Conway <mail@joeconway.com>; Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Bruce Momjian <bruce@momjian.us> writes:
> Are you going to apply this? Seems it is ready.
I thought Joe was off in a corner doing a whole new version.
(I'm willing to help if he needs help...)
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-28 19:54 ` Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 56+ messages in thread
From: Bruce Momjian @ 2006-07-28 19:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joe Conway <mail@joeconway.com>; Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Are you going to apply this? Seems it is ready.
>
> I thought Joe was off in a corner doing a whole new version.
> (I'm willing to help if he needs help...)
OK, just checking.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-28 20:42 ` Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 56+ messages in thread
From: Joe Conway @ 2006-07-28 20:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>
>>Are you going to apply this? Seems it is ready.
>
> I thought Joe was off in a corner doing a whole new version.
> (I'm willing to help if he needs help...)
>
Yeah, I was going to post the latest tonight.
I'm afraid though that after 2 or so days heading down the last path you
suggested (namely making a new jointree leaf node) I was having trouble,
and at the same time came to the conclusion that adding a new RTE was
alot cleaner and made more sense to me. So I'm hoping you won't want to
send me back to the drawing board again. I believe I have cleaned up the
things you objected to:
1. Now I'm not doing both alternative -- the targetlists are only
attached to the RTE from the point of parse analysis onward.
2. I've eliminated the tuplestore in favor of runtime evaluation
of the targetlists which are in an array (allowing forward or
backward scanning -- although I haven't tested the latter yet).
I've also solved the INSERT related issues that I had earlier:
1. Fixed the rules regression test -- now all regression tests pass
2. Fixed evaluation of DEFAULT values
3. Improved memory consumption and speed some more -- basically
we are approximately equal to mysql as long as we don't swap,
and we consume about twice the RAM as mysql instead of several
times as much. I have more analysis of memory use I'd also like
to share later.
4. I think the INSERT part of this is ready to go basically, but
I need a bit more time to test corner cases.
I've made some progress on "SELECT ... FROM (VALUES ...) AS ..."
1. No more shift/reduce issues
2. The ValuesScan work and memory improvements mentioned above
applies here too.
3. This part still needs the most work though.
I'll post a patch in a few hours -- there is some debug code in there
currently that I should clean up before I send it to the list.
BTW, I'm reserving Saturday, Sunday, and Monday (taking Monday off from
my day job) to work on outstanding issues. I can continue to work
through the end of next Friday, 4 August. After that I'm heading to
Germany on a business trip and my "spare" time will evaporate for a few
weeks.
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-29 06:50 ` Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 56+ messages in thread
From: Joe Conway @ 2006-07-29 06:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Andrew Dunstan <andrew@dunslane.net>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
Joe Conway wrote:
> Tom Lane wrote:
>> I thought Joe was off in a corner doing a whole new version.
>> (I'm willing to help if he needs help...)
>
> Yeah, I was going to post the latest tonight.
Sorry for the delay. Ever see the movie "The Money Pit"? This afternoon
I started to think I lived in that house :-(
Anyway, as mentioned below, I think the attached works well for the
"INSERT ... VALUES (...), (...), ..." and related cases. There are still
things wrong that I have not even tried to fix with respect to FROM
clause VALUES lists. Namely column aliases have no effect, and neither
does "ORDER BY" clause (I'm pretty sure addRangeTableEntryForValues
needs work among other places).
From a memory usage standpoint, I got the following using 1,000,000
values targetlists:
sql length = 6000032
NOTICE: enter transformInsertStmt
MessageContext: 478142520 total in 66 blocks; 5750400 free (3 chunks);
472392120 used
NOTICE: enter transformRangeValues
MessageContext: 478142520 total in 66 blocks; 5749480 free (6 chunks);
472393040 used
NOTICE: enter updateTargetListEntry
MessageContext: 629137464 total in 84 blocks; 44742464 free (999991
chunks); 584395000 used
NOTICE: exit transformInsertStmt
MessageContext: 629137464 total in 84 blocks; 44742408 free (999991
chunks); 584395056 used
NOTICE: start ExecInitValuesScan
MessageContext: 1015013432 total in 130 blocks; 6614008 free (8 chunks);
1008399424 used
NOTICE: end ExecInitValuesScan
MessageContext: 1015013432 total in 130 blocks; 6614008 free (8 chunks);
1008399424 used
ExecutorState: 8024632 total in 3 blocks; 21256 free (8 chunks); 8003376
used
This shows original SQL statement is about 6MB, by the time we get to
parse analysis we're at almost 500 MB, and that memory is never
recovered. Transforming from ResTarget to TargetEntry chews up about
100MB. Then between exiting transformInsertStmt and entering
ExecInitValuesScan we double in memory usage to about 1 GB. It isn't
shown here, but we add another 200 MB or so during tuple projection. So
we top out at about 1.2 GB. Note that mysql tops out at about 600 MB for
this same SQL.
I'm not sure what if anything can be done to improve the above -- I'm
open to suggestions.
Please note that this patch requires an initdb, although I have not yet
bothered to bump CATVERSION.
Thanks for help, comments, suggestions, etc...
Joe
>
> I'm afraid though that after 2 or so days heading down the last path you
> suggested (namely making a new jointree leaf node) I was having trouble,
> and at the same time came to the conclusion that adding a new RTE was
> alot cleaner and made more sense to me. So I'm hoping you won't want to
> send me back to the drawing board again. I believe I have cleaned up the
> things you objected to:
>
> 1. Now I'm not doing both alternative -- the targetlists are only
> attached to the RTE from the point of parse analysis onward.
> 2. I've eliminated the tuplestore in favor of runtime evaluation
> of the targetlists which are in an array (allowing forward or
> backward scanning -- although I haven't tested the latter yet).
>
> I've also solved the INSERT related issues that I had earlier:
>
> 1. Fixed the rules regression test -- now all regression tests pass
> 2. Fixed evaluation of DEFAULT values
> 3. Improved memory consumption and speed some more -- basically
> we are approximately equal to mysql as long as we don't swap,
> and we consume about twice the RAM as mysql instead of several
> times as much. I have more analysis of memory use I'd also like
> to share later.
> 4. I think the INSERT part of this is ready to go basically, but
> I need a bit more time to test corner cases.
>
> I've made some progress on "SELECT ... FROM (VALUES ...) AS ..."
>
> 1. No more shift/reduce issues
> 2. The ValuesScan work and memory improvements mentioned above
> applies here too.
> 3. This part still needs the most work though.
>
> I'll post a patch in a few hours -- there is some debug code in there
> currently that I should clean up before I send it to the list.
>
> BTW, I'm reserving Saturday, Sunday, and Monday (taking Monday off from
> my day job) to work on outstanding issues. I can continue to work
> through the end of next Friday, 4 August. After that I'm heading to
> Germany on a business trip and my "spare" time will evaporate for a few
> weeks.
>
Attachments:
multi-insert-r17.diff.gz (application/x-gzip, 20.8K) download
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-30 01:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 01:37 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 56+ messages in thread
From: Tom Lane @ 2006-07-30 01:27 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Joe Conway <mail@joeconway.com> writes:
>> I'm afraid though that after 2 or so days heading down the last path you
>> suggested (namely making a new jointree leaf node) I was having trouble,
>> and at the same time came to the conclusion that adding a new RTE was
>> alot cleaner and made more sense to me. So I'm hoping you won't want to
>> send me back to the drawing board again. I believe I have cleaned up the
>> things you objected to:
I was just objecting to having both a new RTE type and a new jointree
node type --- you only need one or the other. Opting for the new RTE
type is fine with me, and it probably is a bit cleaner at the end of
the day.
I still dislike the way you're doing things in the executor though.
I don't see the point of using the execScan.c machinery; most of the
time that'll be useless overhead. As I said before, I think the right
direction here is to split Result into two single-purpose node types
and make the non-filter version capable of taking a list of targetlists.
As far as reducing memory use goes, it seems to me that there's no need
for the individual "targetlists" to have ResTarget/TargetEntry
decoration. For the simple case where the expressions are just Const
nodes, this could save something like a third of the space (there's also
a List node per item, which we can't do much about). I think we'd have
to gin up a fake targetlist to attach to the Plan node, but there'd be
only one.
Since the result-node split is my hot button, I'm willing to volunteer
to make it happen. Do you want to concentrate on the remaining
parser-area issues and leave the executor part to me?
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-30 01:37 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 56+ messages in thread
From: Joe Conway @ 2006-07-30 01:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>
>>>I'm afraid though that after 2 or so days heading down the last path you
>>>suggested (namely making a new jointree leaf node) I was having trouble,
>>>and at the same time came to the conclusion that adding a new RTE was
>>>alot cleaner and made more sense to me. So I'm hoping you won't want to
>>>send me back to the drawing board again. I believe I have cleaned up the
>>>things you objected to:
>
>
> I was just objecting to having both a new RTE type and a new jointree
> node type --- you only need one or the other. Opting for the new RTE
> type is fine with me, and it probably is a bit cleaner at the end of
> the day.
Great!
> I still dislike the way you're doing things in the executor though.
> I don't see the point of using the execScan.c machinery; most of the
> time that'll be useless overhead. As I said before, I think the right
> direction here is to split Result into two single-purpose node types
> and make the non-filter version capable of taking a list of targetlists.
OK.
> As far as reducing memory use goes, it seems to me that there's no need
> for the individual "targetlists" to have ResTarget/TargetEntry
> decoration. For the simple case where the expressions are just Const
> nodes, this could save something like a third of the space (there's also
> a List node per item, which we can't do much about). I think we'd have
> to gin up a fake targetlist to attach to the Plan node, but there'd be
> only one.
OK, I'll take a look at that (actually I was just in that general
vicinity anyway).
> Since the result-node split is my hot button, I'm willing to volunteer
> to make it happen. Do you want to concentrate on the remaining
> parser-area issues and leave the executor part to me?
>
Sure, sounds good to me.
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-30 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 18:21 ` Re: [PATCHES] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
1 sibling, 2 replies; 56+ messages in thread
From: Tom Lane @ 2006-07-30 17:15 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-hackers@postgresql.org
I wrote:
> I still dislike the way you're doing things in the executor though.
> I don't see the point of using the execScan.c machinery; most of the
> time that'll be useless overhead. As I said before, I think the right
> direction here is to split Result into two single-purpose node types
> and make the non-filter version capable of taking a list of targetlists.
After more thought I've reconsidered this. The "ValuesScan" node is
still redundant with Result's non-filter case, but we should probably
get rid of the latter not the former. The reason is that in the general
case of VALUES-in-FROM, we do need all the generality of execScan.
Consider
SELECT x,y,x+y
FROM (VALUES (1,2),(3,4),...) AS foo(x,y)
WHERE x < y;
which AFAICS is perfectly legal SQL. We need a qual condition for the
WHERE and a projection step to form the x+y result. We could make a
non-filtering Result clause do all that but it'd really be reinventing
the execScan wheel.
So what I'm currently thinking is
1. Implement ValuesScan.
2. Convert all existing uses of Result without a child node into
ValuesScan.
3. Rename Result to Filter and rip out whatever code is only used for
the no-child-node case.
Steps 2 and 3 are just in the nature of housekeeping and can wait till
after the VALUES feature is in.
As far as avoiding overhead goes, here's what I'm thinking:
* The Values RTE node should contain a list of lists of bare
expressions, without TargetEntry decoration (you probably do not
need ResTarget in the raw parse tree for VALUES, either).
* The ValuesScan plan node will just reference this list-of-lists
(avoiding making a copy). It will need to contain a targetlist
because all plan nodes do, but the base version of that will just
be a trivial "Var 1", "Var 2", etc. (The planner might replace that
with a nontrivial targetlist in cases such as the example above.)
* At runtime, ValuesScan evaluates each sublist of expressions and
stores the results into a virtual tuple slot which is returned as
the "scan tuple" to execScan. If the targetlist is nontrivial then
it is evaluated with this tuple as input. If the targetlist is
a trivial Var list then the existing "physical tuple" optimization
kicks in and execScan will just return the scan tuple unmodified.
So for INSERT ... VALUES, the execScan layer will cost us nothing
in memory space and not much in execution time.
There are still some things I don't like about the way you did
ValuesScan but I'll work on improving that.
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-30 18:21 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 56+ messages in thread
From: Joe Conway @ 2006-07-30 18:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
Tom Lane wrote:
> So what I'm currently thinking is
>
> 1. Implement ValuesScan.
> 2. Convert all existing uses of Result without a child node into
> ValuesScan.
> 3. Rename Result to Filter and rip out whatever code is only used for
> the no-child-node case.
>
> Steps 2 and 3 are just in the nature of housekeeping and can wait till
> after the VALUES feature is in.
Sounds good to me.
> As far as avoiding overhead goes, here's what I'm thinking:
>
> * The Values RTE node should contain a list of lists of bare
> expressions, without TargetEntry decoration (you probably do not
> need ResTarget in the raw parse tree for VALUES, either).
>
> * The ValuesScan plan node will just reference this list-of-lists
> (avoiding making a copy). It will need to contain a targetlist
> because all plan nodes do, but the base version of that will just
> be a trivial "Var 1", "Var 2", etc. (The planner might replace that
> with a nontrivial targetlist in cases such as the example above.)
I'll work on that today.
> * At runtime, ValuesScan evaluates each sublist of expressions and
> stores the results into a virtual tuple slot which is returned as
> the "scan tuple" to execScan. If the targetlist is nontrivial then
> it is evaluated with this tuple as input. If the targetlist is
> a trivial Var list then the existing "physical tuple" optimization
> kicks in and execScan will just return the scan tuple unmodified.
> So for INSERT ... VALUES, the execScan layer will cost us nothing
> in memory space and not much in execution time.
>
> There are still some things I don't like about the way you did
> ValuesScan but I'll work on improving that.
OK.
Thanks,
Joe
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-31 18:57 ` Joe Conway <mail@joeconway.com>
2006-07-31 19:10 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-08-01 01:21 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 56+ messages in thread
From: Joe Conway @ 2006-07-31 18:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Tom Lane wrote:
> As far as avoiding overhead goes, here's what I'm thinking:
>
> * The Values RTE node should contain a list of lists of bare
> expressions, without TargetEntry decoration (you probably do not
> need ResTarget in the raw parse tree for VALUES, either).
>
> * The ValuesScan plan node will just reference this list-of-lists
> (avoiding making a copy). It will need to contain a targetlist
> because all plan nodes do, but the base version of that will just
> be a trivial "Var 1", "Var 2", etc. (The planner might replace that
> with a nontrivial targetlist in cases such as the example above.)
I wanted to post an updated patch even though there are still things not
working again after conversion to bare expressions. Note that I hacked
enough of the executor stuff so I could test my changes on the parser
area. The basic "INSERT ... VALUES (...), (...), ..." does work, but
without DEFAULT again :-(.
The good news is that from a memory and perfomance standpoint, my simple
test now shows us outperforming mysql:
$loopcount = 1000000;
Postgres:
multi-INSERT-at-once Elapsed time is 12 seconds
~420MB
MySQL:
multi-INSERT-at-once Elapsed time is 17 seconds
~600MB
$loopcount = 2000000;
Postgres:
multi-INSERT-at-once Elapsed time is 29 seconds
~730MB
MySQL:
multi-INSERT-at-once Elapsed time is 37 seconds
~1.2GB (this one is from memory -- I didn't write it in my notes)
Joe
Attachments:
multi-insert-r18.diff.gz (application/x-gzip, 20.3K) download
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-07-31 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 20:19 ` Re: [HACKERS] 8.2 features? Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 56+ messages in thread
From: Tom Lane @ 2006-07-31 19:10 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Joe Conway <mail@joeconway.com> writes:
> The good news is that from a memory and perfomance standpoint, my simple
> test now shows us outperforming mysql:
Sweet ;-)
I'm up to my *ss in fixing relation locking, but will get back to your
thing as soon as that's done. I think you're close enough to qualify
as having made the feature freeze deadline, in any case.
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-31 19:10 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-31 20:19 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-07-31 20:44 ` Re: [HACKERS] 8.2 features? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 56+ messages in thread
From: Alvaro Herrera @ 2006-07-31 20:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joe Conway <mail@joeconway.com>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> > The good news is that from a memory and perfomance standpoint, my simple
> > test now shows us outperforming mysql:
>
> Sweet ;-)
I love this team. Kudos!
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-31 19:10 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 20:19 ` Re: [HACKERS] 8.2 features? Alvaro Herrera <alvherre@commandprompt.com>
@ 2006-07-31 20:44 ` Michael Fuhr <mike@fuhr.org>
2006-07-31 21:46 ` Re: [HACKERS] 8.2 features? Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 56+ messages in thread
From: Michael Fuhr @ 2006-07-31 20:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Joe Conway <mail@joeconway.com>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>
On Mon, Jul 31, 2006 at 04:19:43PM -0400, Alvaro Herrera wrote:
> Tom Lane wrote:
> > Joe Conway <mail@joeconway.com> writes:
> > > The good news is that from a memory and perfomance standpoint, my simple
> > > test now shows us outperforming mysql:
> >
> > Sweet ;-)
>
> I love this team. Kudos!
So now it's MySQL users' turn to say, "Sure, but speed isn't
everything...." :-)
--
Michael Fuhr
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-31 19:10 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 20:19 ` Re: [HACKERS] 8.2 features? Alvaro Herrera <alvherre@commandprompt.com>
2006-07-31 20:44 ` Re: [HACKERS] 8.2 features? Michael Fuhr <mike@fuhr.org>
@ 2006-07-31 21:46 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-01 07:15 ` Re: [HACKERS] 8.2 features? Harald Armin Massa <haraldarminmassa@gmail.com>
0 siblings, 1 reply; 56+ messages in thread
From: Joshua D. Drake @ 2006-07-31 21:46 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Joe Conway <mail@joeconway.com>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Michael Fuhr wrote:
> On Mon, Jul 31, 2006 at 04:19:43PM -0400, Alvaro Herrera wrote:
>> Tom Lane wrote:
>>> Joe Conway <mail@joeconway.com> writes:
>>>> The good news is that from a memory and perfomance standpoint, my simple
>>>> test now shows us outperforming mysql:
>>> Sweet ;-)
>> I love this team. Kudos!
>
> So now it's MySQL users' turn to say, "Sure, but speed isn't
> everything...." :-)
"Sure, but speed isn't everything... We can accept 02/31/2006 as a valid
date. Let's see PostgreSQL do that!"
Joshua D. Drake
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-31 19:10 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 20:19 ` Re: [HACKERS] 8.2 features? Alvaro Herrera <alvherre@commandprompt.com>
2006-07-31 20:44 ` Re: [HACKERS] 8.2 features? Michael Fuhr <mike@fuhr.org>
2006-07-31 21:46 ` Re: [HACKERS] 8.2 features? Joshua D. Drake <jd@commandprompt.com>
@ 2006-08-01 07:15 ` Harald Armin Massa <haraldarminmassa@gmail.com>
2006-08-01 07:51 ` Re: [PATCHES] 8.2 features? Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 56+ messages in thread
From: Harald Armin Massa @ 2006-08-01 07:15 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Joshua,
> So now it's MySQL users' turn to say, "Sure, but speed isn't
> > everything...." :-)
> "Sure, but speed isn't everything... We can accept 02/31/2006 as a valid
> date. Let's see PostgreSQL do that!"
>
I got the joke :)
But: it is still a problem when converting. As accepting 2006-02-31 as a
valid date would require brainwashing at least the entire core team, we
should find a "recommended path of date migration from different universes".
My idea would be to:
a) declare date fields as text
b) load the dump of the other db
c) add another column for the date fields, type timestampe (w/wo tz)
d) try to update the column of c) with the converted field from a)
e) replace the failing ones manually
is this really best practice? especially finding the invalid ones would be
challenging :(
idea: sort after the textual date fields; look at hot spots (0000-00-00,
xxxx-02-31)
Are there better ideas? shall we document the best practice somewhere?
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [PATCHES] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-31 19:10 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 20:19 ` Re: [HACKERS] 8.2 features? Alvaro Herrera <alvherre@commandprompt.com>
2006-07-31 20:44 ` Re: [HACKERS] 8.2 features? Michael Fuhr <mike@fuhr.org>
2006-07-31 21:46 ` Re: [HACKERS] 8.2 features? Joshua D. Drake <jd@commandprompt.com>
2006-08-01 07:15 ` Re: [HACKERS] 8.2 features? Harald Armin Massa <haraldarminmassa@gmail.com>
@ 2006-08-01 07:51 ` Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 56+ messages in thread
From: Michael Glaesemann @ 2006-08-01 07:51 UTC (permalink / raw)
To: Harald Armin Massa <haraldarminmassa@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-hackers@postgresql.org
On Aug 1, 2006, at 16:15 , Harald Armin Massa wrote:
> As accepting 2006-02-31 as a valid date would require brainwashing
> at least the entire core team, we should find a "recommended path
> of date migration from different universes".
Have you checked out the mysql2pgsql[1] or my2postgres projects?
Perhaps they've included this as part of the conversion process.
Michael Glaesemann
grzm seespotcode net
[1](http://gborg.postgresql.org/project/mysql2psql/projdisplay.php)
[2](http://pgfoundry.org/projects/my2postgres/)
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-08-01 01:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-01 02:17 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 56+ messages in thread
From: Tom Lane @ 2006-08-01 01:21 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Joe Conway <mail@joeconway.com> writes:
> I wanted to post an updated patch even though there are still things not
> working again after conversion to bare expressions.
I've been through the planner part of this and it looks OK (one or two
small errors). I'm currently messing with a revised version of the
grammar that supports putting VALUES everyplace that the spec allows,
and is a bit simpler than the old one to boot: it folds VALUES and
SELECT together, so we need fewer cases in the INSERT production.
Of course this breaks most of what you did in the parser :-( ...
I'm working on fixing that.
I'm about to go out to dinner but thought I'd post the gram.y and
parsenodes.h files so you could see where I'm headed. These are
diffs from CVS tip, not from your patch.
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-08-01 01:21 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-08-01 02:17 ` Joe Conway <mail@joeconway.com>
2006-08-01 13:01 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 56+ messages in thread
From: Joe Conway @ 2006-08-01 02:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>
>>I wanted to post an updated patch even though there are still things not
>>working again after conversion to bare expressions.
>
> I've been through the planner part of this and it looks OK (one or two
> small errors). I'm currently messing with a revised version of the
> grammar that supports putting VALUES everyplace that the spec allows,
> and is a bit simpler than the old one to boot: it folds VALUES and
> SELECT together, so we need fewer cases in the INSERT production.
> Of course this breaks most of what you did in the parser :-( ...
> I'm working on fixing that.
>
> I'm about to go out to dinner but thought I'd post the gram.y and
> parsenodes.h files so you could see where I'm headed. These are
> diffs from CVS tip, not from your patch.
>
Yup, I can see where you're headed. Looks nice!
In case you can make use of it, here's my latest. I found that I was
being too aggressive at freeing the input nodes to transformExpr() in
transformRangeValues() after using them. In many cases the returned node
is a new palloc'd node, but in some cases it is not.
The other issue I found was that I had neglected to fixup/coerce the raw
expressions ala updateTargetListEntry(). I ended up creating a somewhat
simpler updateValuesExprListEntry() to use on values expression lists.
I have yet to get to the similar/more general issue of coercing values
expression lists to common datatypes (i.e. using select_common_type()).
FWIW, here's a list of non-working cases at the moment:
8<-------------------------------------
create table inserttest (col1 int4, col2 int4 NOT NULL, col3 text
default 'testing');
--doesn't work
---------------
--wrong result
insert into inserttest (col2, col3) values (23, DEFAULT), (24, DEFAULT),
(25, 'hello'), (26, DEFAULT);
select * from (values (3,4),(2,3)) as t1(f1,f2) join (values
(3,8),(2,6)) as t2(f1,f2) using (f1);
select * from (values (3,4),(2,3)) as t1(f1,f2) join (values
(3,8),(2,6)) as t2(f1,f2) using (f1) where t2.f2 = 8;
select * from (values (3,4),(2,3)) as t1(f1,f2) join (values
(3,8),(2,6)) as t2(f1,f2) on t1.f1 = t2.f2 where t1.f1 = 3;
--corrupt result but no crash
select f1,f2 from (values (11,2),(26,'a'),(6,4)) as t(f1,f2) order by 1
desc;
--crash
select f1 from (values (1,2),(2,3)) as t(f1,f2) order by 1 desc;
select f1,f2 from (values (11,'a'),(26,13),(6,'c')) as t(f1,f2) order by
1 desc;
8<-------------------------------------
Joe
Attachments:
multi-insert-r19.diff.gz (application/x-gzip, 21.6K) download
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:18 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-28 19:47 ` Re: [HACKERS] 8.2 features? Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 20:42 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 17:15 ` Re: [PATCHES] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 18:57 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-08-01 01:21 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
2006-08-01 02:17 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
@ 2006-08-01 13:01 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 56+ messages in thread
From: Tom Lane @ 2006-08-01 13:01 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Joe Conway <mail@joeconway.com> writes:
> In case you can make use of it, here's my latest. I found that I was
> being too aggressive at freeing the input nodes to transformExpr() in
> transformRangeValues() after using them. In many cases the returned node
> is a new palloc'd node, but in some cases it is not.
Great, I'll incorporate these updates and keep plugging --- should be
done today barring problems. If you have some spare cycles today,
want to work on regression tests and docs?
regards, tom lane
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-18 18:19 ` Re: [HACKERS] 8.2 features? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-19 01:20 ` Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
3 siblings, 0 replies; 56+ messages in thread
From: Christopher Kings-Lynne @ 2006-07-19 01:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org, Andrew Dunstan <andrew@dunslane.net>; Joe Conway <mail@joeconway.com>; Bernd Helmle <mailings@oopsware.de>; Peter Eisentraut <peter_e@gmx.net>; Patches (PostgreSQL) <pgsql-patches@postgresql.org>; Susanne Ebrecht <susanne.ebrecht@credativ.de>
> 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.
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
@ 2006-07-18 18:49 ` Chris Browne <cbbrowne@acm.org>
2006-07-18 19:46 ` Re: [HACKERS] 8.2 features? Andrew Dunstan <andrew@dunslane.net>
1 sibling, 1 reply; 56+ messages in thread
From: Chris Browne @ 2006-07-18 18:49 UTC (permalink / raw)
To: pgsql-patches@postgresql.org
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...]
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: [HACKERS] 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Re: 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Re: 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Re: [HACKERS] 8.2 features? Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Re: [HACKERS] 8.2 features? Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 18:49 ` Re: [HACKERS] 8.2 features? Chris Browne <cbbrowne@acm.org>
@ 2006-07-18 19:46 ` Andrew Dunstan <andrew@dunslane.net>
0 siblings, 0 replies; 56+ messages in thread
From: Andrew Dunstan @ 2006-07-18 19:46 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-patches@postgresql.org
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
^ permalink raw reply [nested|flat] 56+ messages in thread
* Re: 8.2 features?
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 23:23 ` Re: 8.2 features? Peter Eisentraut <peter_e@gmx.net>
2006-07-14 14:26 ` Re: 8.2 features? Bernd Helmle <mailings@oopsware.de>
@ 2006-07-17 10:27 ` Susanne Ebrecht <miracee@miracee.de>
1 sibling, 0 replies; 56+ messages in thread
From: Susanne Ebrecht @ 2006-07-17 10:27 UTC (permalink / raw)
To: Bernd Helmle <mailings@oopsware.de>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Andrew Dunstan <andrew@dunslane.net>; PostgreSQL-development <pgsql-hackers@postgresql.org>
Am Freitag, den 14.07.2006, 16:26 +0200 schrieb Bernd Helmle:
>
> --On Freitag, Juli 14, 2006 01:23:11 +0200 Peter Eisentraut
> <peter_e@gmx.net> wrote:
>
> >> . multiple values clauses for INSERT
> >
> > Susanne Ebrecht <susanne.ebrecht@credativ.de> was last heard to work on
> > it. Updates, Susanne?
>
> I've talked to Susanne today and she's just back from hospital and not
> available
> online until next week.
> She was working on the SET (col1, col2) = (val1, val2) syntax for UPDATE
> commands.
> Don't know what the status is on this, though.
>
Thanks Peter and Bernd for your postings.
I'am working on
update table set (col1, col2, ...) = (val1, val2, ...), (colx,
coly, ...) = (valx, valy, ...), ...
I hope, it will be finished this week. Most of work is done.
Susanne
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 56+ messages in thread
end of thread, other threads:[~2006-08-01 13:01 UTC | newest]
Thread overview: 56+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-13 18:56 8.2 features? Andrew Dunstan <andrew@dunslane.net>
2006-07-13 19:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-13 19:42 ` Jonah H. Harris <jonah.harris@gmail.com>
2006-07-13 21:02 ` Andrew Dunstan <andrew@dunslane.net>
2006-07-13 21:09 ` Jonah H. Harris <jonah.harris@gmail.com>
2006-07-13 22:11 ` David Fetter <david@fetter.org>
2006-07-13 22:12 ` Joe Conway <mail@joeconway.com>
2006-07-13 23:23 ` Peter Eisentraut <peter_e@gmx.net>
2006-07-14 01:30 ` Stephen Frost <sfrost@snowman.net>
2006-07-14 01:50 ` Jonah H. Harris <jonah.harris@gmail.com>
2006-07-14 14:26 ` Bernd Helmle <mailings@oopsware.de>
2006-07-14 14:45 ` Andrew Dunstan <andrew@dunslane.net>
2006-07-17 21:42 ` Joe Conway <mail@joeconway.com>
2006-07-18 04:39 ` Joe Conway <mail@joeconway.com>
2006-07-18 12:13 ` Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 14:36 ` Andrew Dunstan <andrew@dunslane.net>
2006-07-18 15:05 ` Joe Conway <mail@joeconway.com>
2006-07-18 18:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-18 21:39 ` Thomas Bley <thbley@gmail.com>
2006-07-18 23:41 ` Matthew D. Fuller <fullermd@over-yonder.net>
2006-07-19 01:18 ` Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-19 03:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 04:20 ` Joe Conway <mail@joeconway.com>
2006-07-19 21:41 ` Joe Conway <mail@joeconway.com>
2006-07-20 03:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 04:16 ` Joe Conway <mail@joeconway.com>
2006-07-20 04:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 05:18 ` Joe Conway <mail@joeconway.com>
2006-07-20 05:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-20 23:13 ` Joe Conway <mail@joeconway.com>
2006-07-21 00:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-21 03:09 ` Joe Conway <mail@joeconway.com>
2006-07-21 14:08 ` Jim C. Nasby <jnasby@pervasive.com>
2006-07-28 19:47 ` Bruce Momjian <bruce@momjian.us>
2006-07-28 19:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-28 19:54 ` Bruce Momjian <bruce@momjian.us>
2006-07-28 20:42 ` Joe Conway <mail@joeconway.com>
2006-07-29 06:50 ` Joe Conway <mail@joeconway.com>
2006-07-30 01:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 01:37 ` Joe Conway <mail@joeconway.com>
2006-07-30 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-30 18:21 ` Joe Conway <mail@joeconway.com>
2006-07-31 18:57 ` Joe Conway <mail@joeconway.com>
2006-07-31 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 20:19 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-07-31 20:44 ` Michael Fuhr <mike@fuhr.org>
2006-07-31 21:46 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-01 07:15 ` Harald Armin Massa <haraldarminmassa@gmail.com>
2006-08-01 07:51 ` Michael Glaesemann <grzm@seespotcode.net>
2006-08-01 01:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-01 02:17 ` Joe Conway <mail@joeconway.com>
2006-08-01 13:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 01:20 ` Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-07-18 18:49 ` Chris Browne <cbbrowne@acm.org>
2006-07-18 19:46 ` Andrew Dunstan <andrew@dunslane.net>
2006-07-17 10:27 ` Susanne Ebrecht <miracee@miracee.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox