8.2 features?

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

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/

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

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/

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!

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

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?

* 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

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/

--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

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

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

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

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

Hello,

I did some work on mutliple value insert. First: SELECT .. UNION ALL SELECT
is wrong idea. VALUES can contain DEFAULT keyword. Second: It's neccessery
general implementation of table values constructor like SQL:2003. Main
problem what I see is biger request on sources if we implement MVI as
classic PostgreSQL stmt.

Regards
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

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

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

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

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