Re: COPY FROM WHEN condition

Hi, On 2019-04-05 12:59:06 +1300, David Rowley wrote: > I read through the final commit to see what had changed and I noticed > that I had forgotten to remove nbuffers from CopyMultiInsertInfo when > changing from a hash table to a List. > > Patch to fix is attached. Pushed, thanks.

Re: COPY FROM WHEN condition

On Fri, 5 Apr 2019 at 12:32, Andres Freund wrote: > I've pushed this now. Besides those naming changes, I'd to re-add the > zero initialization (I did end up seing compiler warnings when compiling > with optimizations). Also some comment fixes. Thanks for pushing. > I added one more flush locat

Re: COPY FROM WHEN condition

Hi, On 2019-04-04 12:04:28 -0700, Andres Freund wrote: > On 2019-04-03 22:20:00 -0700, Andres Freund wrote: > > On 2019-04-03 20:00:09 +1300, David Rowley wrote: > > > Oops, I forgot about that one. v4 attached. > > > > I'm pretty happy with this. I'm doing some minor changes (e.g. don't > > like

Re: COPY FROM WHEN condition

On 2019-04-03 22:20:00 -0700, Andres Freund wrote: > Hi, > > On 2019-04-03 20:00:09 +1300, David Rowley wrote: > > Oops, I forgot about that one. v4 attached. > > I'm pretty happy with this. I'm doing some minor changes (e.g. don't > like the function comment formatting that much, the tableam cal

Re: COPY FROM WHEN condition

On Thu, 4 Apr 2019 at 18:20, Andres Freund wrote: > I'm pretty happy with this. I'm doing some minor changes (e.g. don't > like the function comment formatting that much, the tableam callback > needs docs, stuff like that), and then I'm going to push it tomorrow. > > I'm planning to attribute it t

Re: COPY FROM WHEN condition

Hi, On 2019-04-03 20:00:09 +1300, David Rowley wrote: > Oops, I forgot about that one. v4 attached. I'm pretty happy with this. I'm doing some minor changes (e.g. don't like the function comment formatting that much, the tableam callback needs docs, stuff like that), and then I'm going to push it

Re: COPY FROM WHEN condition

On Wed, 3 Apr 2019 at 18:56, Andres Freund wrote: > > +/* Class the buffer full if there are >= this many bytes of tuples stored > > */ > > +#define MAX_BUFFERED_BYTES 65535 > > Random aside: This seems pretty small (but should be changed separately. Yeah, my fingers were hovering over

Re: COPY FROM WHEN condition

On 2019-04-03 18:44:32 +1300, David Rowley wrote: > (Fixed all of what you mentioned) > > On Wed, 3 Apr 2019 at 06:57, Andres Freund wrote: > > > +/* > > > + * CopyMultiInsertInfo_Flush > > > + * Write out all stored tuples in all buffers out to the > > > tables. > > > + * > > > + * To

Re: COPY FROM WHEN condition

(Fixed all of what you mentioned) On Wed, 3 Apr 2019 at 06:57, Andres Freund wrote: > > +/* > > + * CopyMultiInsertInfo_Flush > > + * Write out all stored tuples in all buffers out to the tables. > > + * > > + * To save us from ending up with buffers for 1000s of partitions we remove >

Re: COPY FROM WHEN condition

Gah, ignore this mail - I've somehow accidentally sent this early. I'm not quite sure, but I miskeyed, and emacs sent it early, without going through mutt...

Re: COPY FROM WHEN condition

On 2019-04-03 06:41:49 +1300, David Rowley wrote: > However, I've ended up not doing it that way as the patch requires > more than just an array of TupleTableSlots to be stored in the > ResultRelInfo, it'll pretty much need all of what I have in > CopyMultiInsertBuffer, which includes line numbers

Re: COPY FROM WHEN condition

On 2019-04-03 06:41:49 +1300, David Rowley wrote: > However, I've ended up not doing it that way as the patch requires > more than just an array of TupleTableSlots to be stored in the > ResultRelInfo, it'll pretty much need all of what I have in > CopyMultiInsertBuffer, which includes line numbers

Re: COPY FROM WHEN condition

On Tue, 2 Apr 2019 at 14:11, Andres Freund wrote: > Why do we need that list membership check? If you append the > ResultRelInfo to the list when creating the ResultRelInfo's slots array, > you don't need to touch the list after a partition lookup - you know > it's a member if the ResultRelInfo ha

Re: COPY FROM WHEN condition

Hi, On 2019-04-02 14:06:52 +1300, David Rowley wrote: > On Tue, 2 Apr 2019 at 13:59, Andres Freund wrote: > > > > On 2019-04-02 13:41:57 +1300, David Rowley wrote: > > > On Tue, 2 Apr 2019 at 05:19, Andres Freund wrote: > > > > Thanks! I'm not quite clear whether you planning to continue working

Re: COPY FROM WHEN condition

On Tue, 2 Apr 2019 at 13:59, Andres Freund wrote: > > On 2019-04-02 13:41:57 +1300, David Rowley wrote: > > On Tue, 2 Apr 2019 at 05:19, Andres Freund wrote: > > > Thanks! I'm not quite clear whether you planning to continue working on > > > this, or whether this is a handoff? Either is fine with

Re: COPY FROM WHEN condition

Hi, On 2019-04-02 13:41:57 +1300, David Rowley wrote: > On Tue, 2 Apr 2019 at 05:19, Andres Freund wrote: > > > > On 2019-04-02 04:23:20 +1300, David Rowley wrote: > > > On Mon, 1 Apr 2019 at 08:05, Andres Freund wrote: > > > > I'll work on pushing all the other pending tableam patches today - >

Re: COPY FROM WHEN condition

On Tue, 2 Apr 2019 at 05:19, Andres Freund wrote: > > On 2019-04-02 04:23:20 +1300, David Rowley wrote: > > On Mon, 1 Apr 2019 at 08:05, Andres Freund wrote: > > > I'll work on pushing all the other pending tableam patches today - > > > leaving COPY the last non pluggable part. You'd written in a

Re: COPY FROM WHEN condition

Hi, On 2019-04-02 04:23:20 +1300, David Rowley wrote: > On Mon, 1 Apr 2019 at 08:05, Andres Freund wrote: > > I'll work on pushing all the other pending tableam patches today - > > leaving COPY the last non pluggable part. You'd written in a private > > email that you might try to work on this on

Re: COPY FROM WHEN condition

On Mon, 1 Apr 2019 at 08:05, Andres Freund wrote: > I'll work on pushing all the other pending tableam patches today - > leaving COPY the last non pluggable part. You'd written in a private > email that you might try to work on this on Monday, so I think I'll give > this a shot on Tuesday if you'v

Re: COPY FROM WHEN condition

Hi, On 2019-04-01 02:00:26 +1300, David Rowley wrote: > On Fri, 29 Mar 2019 at 01:15, Andres Freund wrote: > > On 2019-03-28 20:48:47 +1300, David Rowley wrote: > > > I had a look at this and performance has improved again, thanks. > > > However, I'm not sure if the patch is exactly what we need,

Re: COPY FROM WHEN condition

On Fri, 29 Mar 2019 at 01:15, Andres Freund wrote: > On 2019-03-28 20:48:47 +1300, David Rowley wrote: > > I had a look at this and performance has improved again, thanks. > > However, I'm not sure if the patch is exactly what we need, let me > > explain. > > I'm not entirely sure either, I just h

Re: COPY FROM WHEN condition

Hi, On 2019-03-28 20:48:47 +1300, David Rowley wrote: > I had a look at this and performance has improved again, thanks. > However, I'm not sure if the patch is exactly what we need, let me > explain. I'm not entirely sure either, I just haven't really seen an alternative that's convincing. > W

Re: COPY FROM WHEN condition

On Wed, 27 Mar 2019 at 18:49, Andres Freund wrote: > Here's a version that applies onto HEAD. It needs a bit more cleanup > (I'm not sure I like the addition of ri_batchInsertSlots to store slots > for each partition, there's some duplicated code around slot array and > slot creation, docs for the

Re: COPY FROM WHEN condition

Hi, On 2019-01-29 07:22:16 -0800, Andres Freund wrote: > On January 29, 2019 6:25:59 AM PST, Tomas Vondra > wrote: > >On 1/29/19 8:18 AM, David Rowley wrote: > >> ... > >> Here are my performance tests of with and without your change to the > >> memory contexts (I missed where you posted your re

Re: COPY FROM WHEN condition

Hi, On 2019-01-30 10:33:30 +1300, David Rowley wrote: > On Wed, 30 Jan 2019 at 10:12, Andres Freund wrote: > > > > On 2019-01-30 10:05:35 +1300, David Rowley wrote: > > > On Wed, 30 Jan 2019 at 04:22, Andres Freund wrote: > > > > I think I might have a patch addressing the problem incidentally.

Re: COPY FROM WHEN condition

On Wed, 30 Jan 2019 at 10:12, Andres Freund wrote: > > On 2019-01-30 10:05:35 +1300, David Rowley wrote: > > On Wed, 30 Jan 2019 at 04:22, Andres Freund wrote: > > > I think I might have a patch addressing the problem incidentally. For > > > pluggable storage I slotified copy.c, which also remov

Re: COPY FROM WHEN condition

Hi, On 2019-01-30 10:05:35 +1300, David Rowley wrote: > On Wed, 30 Jan 2019 at 04:22, Andres Freund wrote: > > I think I might have a patch addressing the problem incidentally. For > > pluggable storage I slotified copy.c, which also removes the first > > heap_form_tuple. Quite possible that no

Re: COPY FROM WHEN condition

On Tue, 29 Jan 2019 at 22:51, Tomas Vondra wrote: > > On 1/29/19 8:18 AM, David Rowley wrote: > > So looks like your change slows this code down 4% for this test case. > > That's about twice as bad as the 2.2% regression that I had to solve > > for the multi-insert partition patch (of which you've

Re: COPY FROM WHEN condition

On Wed, 30 Jan 2019 at 04:22, Andres Freund wrote: > I think I might have a patch addressing the problem incidentally. For > pluggable storage I slotified copy.c, which also removes the first > heap_form_tuple. Quite possible that nothing more is needed. I've removed the > batch context altoget

Re: COPY FROM WHEN condition

On Wed, 30 Jan 2019 at 03:26, Tomas Vondra wrote: > > On 1/29/19 8:18 AM, David Rowley wrote: > > (details about performance regression) > > How do I reproduce this? I don't see this test in the thread you linked, > so I'm not sure how many partitions you were using, what's the structure > of the

Re: COPY FROM WHEN condition

Hi, On January 29, 2019 6:25:59 AM PST, Tomas Vondra wrote: >On 1/29/19 8:18 AM, David Rowley wrote: >> ... >> Here are my performance tests of with and without your change to the >> memory contexts (I missed where you posted your results). >> >> $ cat bench.pl >> for (my $i=0; $i < 8912891; $i

Re: COPY FROM WHEN condition

On 1/29/19 8:18 AM, David Rowley wrote: > ... > Here are my performance tests of with and without your change to the > memory contexts (I missed where you posted your results). > > $ cat bench.pl > for (my $i=0; $i < 8912891; $i++) { > print "1\n1\n2\n2\n"; > } > 36a1281f86c: (with your change) >

Re: COPY FROM WHEN condition

On 1/29/19 8:18 AM, David Rowley wrote: > On Wed, 23 Jan 2019 at 06:35, Tomas Vondra > wrote: >> It turned out to be a tad more complex due to partitioning, because when >> we find the partitions do not match, the tuple is already allocated in >> the "current" context (be it per-tuple or batch

Re: COPY FROM WHEN condition

On Wed, 23 Jan 2019 at 06:35, Tomas Vondra wrote: > It turned out to be a tad more complex due to partitioning, because when > we find the partitions do not match, the tuple is already allocated in > the "current" context (be it per-tuple or batch). So we can't just free > the whole context at tha

Re: COPY FROM WHEN condition

On 1/22/19 10:00 AM, Surafel Temesgen wrote: > > > On Mon, Jan 21, 2019 at 6:22 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > I think the condition can be just > >     if (contain_volatile_functions(cstate->whereClause)) { ... } > > I've pushed a fix for the

Re: COPY FROM WHEN condition

Hi, On 2019-01-22 18:35:21 +0100, Tomas Vondra wrote: > On 1/21/19 11:15 PM, Tomas Vondra wrote: > > On 1/21/19 7:51 PM, Andres Freund wrote: > >> I'm *not* convinced by this. I think it's bad enough that we do this for > >> normal COPY, but for WHEN, we could end up *never* resetting before the >

Re: COPY FROM WHEN condition

On 1/21/19 11:15 PM, Tomas Vondra wrote: > > > On 1/21/19 7:51 PM, Andres Freund wrote: >> Hi, >> >> On 2019-01-21 16:22:11 +0100, Tomas Vondra wrote: >>> >>> >>> On 1/21/19 4:33 AM, Tomas Vondra wrote: On 1/21/19 3:12 AM, Andres Freund wrote: > On 2019-01-20 18:08:05 -0800,

Re: COPY FROM WHEN condition

On Mon, Jan 21, 2019 at 6:22 PM Tomas Vondra wrote: > > I think the condition can be just > > if (contain_volatile_functions(cstate->whereClause)) { ... } > > > yes it can be. regards Surafel

Re: COPY FROM WHEN condition

On 1/21/19 7:51 PM, Andres Freund wrote: > Hi, > > On 2019-01-21 16:22:11 +0100, Tomas Vondra wrote: >> >> >> On 1/21/19 4:33 AM, Tomas Vondra wrote: >>> >>> >>> On 1/21/19 3:12 AM, Andres Freund wrote: On 2019-01-20 18:08:05 -0800, Andres Freund wrote: > On 2019-01-20 21:00:21 -0500,

Re: COPY FROM WHEN condition

Hi, On 2019-01-21 16:22:11 +0100, Tomas Vondra wrote: > > > On 1/21/19 4:33 AM, Tomas Vondra wrote: > > > > > > On 1/21/19 3:12 AM, Andres Freund wrote: > >> On 2019-01-20 18:08:05 -0800, Andres Freund wrote: > >>> On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: > > > On 1/20/

Re: COPY FROM WHEN condition

On 1/21/19 4:33 AM, Tomas Vondra wrote: > > > On 1/21/19 3:12 AM, Andres Freund wrote: >> On 2019-01-20 18:08:05 -0800, Andres Freund wrote: >>> On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: On 1/20/19 8:24 PM, Andres Freund wrote: > Hi, > > On 2019-01-20 00:24:05

Re: COPY FROM WHEN condition

On 1/21/19 3:12 AM, Andres Freund wrote: > On 2019-01-20 18:08:05 -0800, Andres Freund wrote: >> On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: >>> >>> >>> On 1/20/19 8:24 PM, Andres Freund wrote: Hi, On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > On 1/14/19 10:25 PM,

Re: COPY FROM WHEN condition

On 1/21/19 3:12 AM, Andres Freund wrote: > On 2019-01-20 18:08:05 -0800, Andres Freund wrote: >> On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: >>> >>> >>> On 1/20/19 8:24 PM, Andres Freund wrote: Hi, On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > On 1/14/19 10:25 PM,

Re: COPY FROM WHEN condition

On 2019-01-20 18:08:05 -0800, Andres Freund wrote: > On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: > > > > > > On 1/20/19 8:24 PM, Andres Freund wrote: > > > Hi, > > > > > > On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > > > > On 1/14/19 10:25 PM, Tomas Vondra wrote: > > > > > On 12/13/

Re: COPY FROM WHEN condition

On 2019-01-20 21:00:21 -0500, Tomas Vondra wrote: > > > On 1/20/19 8:24 PM, Andres Freund wrote: > > Hi, > > > > On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > > > On 1/14/19 10:25 PM, Tomas Vondra wrote: > > > > On 12/13/18 8:09 AM, Surafel Temesgen wrote: > > > > > > > > > > > > > > > O

Re: COPY FROM WHEN condition

On 1/20/19 8:24 PM, Andres Freund wrote: Hi, On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: On 1/14/19 10:25 PM, Tomas Vondra wrote: On 12/13/18 8:09 AM, Surafel Temesgen wrote: On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra mailto:tomas.von...@2ndquadrant.com>> wrote: Can you al

Re: COPY FROM WHEN condition

Hi, On 2019-01-20 00:24:05 +0100, Tomas Vondra wrote: > On 1/14/19 10:25 PM, Tomas Vondra wrote: > > On 12/13/18 8:09 AM, Surafel Temesgen wrote: > >> > >> > >> On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra > >> mailto:tomas.von...@2ndquadrant.com>> wrote: > >> > >> > >> Can you also update the

Re: COPY FROM WHEN condition

On Sun, Jan 20, 2019 at 2:24 AM Tomas Vondra wrote: > > Pushed, thanks for the patch. > > cheers > > > Thank you regards Surafel

Re: COPY FROM WHEN condition

On 1/14/19 10:25 PM, Tomas Vondra wrote: > On 12/13/18 8:09 AM, Surafel Temesgen wrote: >> >> >> On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra >> mailto:tomas.von...@2ndquadrant.com>> wrote: >> >> >> Can you also update the docs to mention that the functions called from >> the WHERE claus

Re: COPY FROM WHEN condition

On 12/13/18 8:09 AM, Surafel Temesgen wrote: > > > On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > Can you also update the docs to mention that the functions called from > the WHERE clause does not see effects of the COPY itself? > > >

Re: COPY FROM WHEN condition

On Wed, Dec 12, 2018 at 9:28 PM Tomas Vondra wrote: > > Can you also update the docs to mention that the functions called from > the WHERE clause does not see effects of the COPY itself? > > *Of course, i also add same comment to insertion method selection* *regards * *Surafel* diff --git a/d

Re: COPY FROM WHEN condition

On 12/12/18 7:05 AM, Surafel Temesgen wrote: > > > On Tue, Dec 4, 2018 at 12:44 PM Alvaro Herrera > wrote: > > After reading this thread, I think I like WHERE better than FILTER. > Tally: > > WHERE: Adam Berlin, Lim Myungkyu, Dean Rasheed, yours t

Re: COPY FROM WHEN condition

On Tue, Dec 4, 2018 at 12:44 PM Alvaro Herrera wrote: > After reading this thread, I think I like WHERE better than FILTER. > Tally: > > WHERE: Adam Berlin, Lim Myungkyu, Dean Rasheed, yours truly > FILTER: Tomas Vondra, Surafel Temesgen > > > accepting tally result i change the keyword to WHERE

Re: COPY FROM WHEN condition

On 12/6/18 4:52 PM, Robert Haas wrote: > On Wed, Nov 28, 2018 at 6:17 PM Tomas Vondra > wrote: >>> Comparing with overhead of setting snapshot before evaluating every row >>> and considering this >>> >>> kind of usage is not frequent it seems to me the behavior is acceptable >> >> I'm not reall

Re: COPY FROM WHEN condition

On Wed, Nov 28, 2018 at 6:17 PM Tomas Vondra wrote: > > Comparing with overhead of setting snapshot before evaluating every row > > and considering this > > > > kind of usage is not frequent it seems to me the behavior is acceptable > > I'm not really buying the argument that this behavior is acce

Re: COPY FROM WHEN condition

On 12/4/18 10:44 AM, Alvaro Herrera wrote: > After reading this thread, I think I like WHERE better than FILTER. > Tally: > > WHERE: Adam Berlin, Lim Myungkyu, Dean Rasheed, yours truly > FILTER: Tomas Vondra, Surafel Temesgen > > Couldn't find others expressing an opinion in this regard. > W

Re: COPY FROM WHEN condition

After reading this thread, I think I like WHERE better than FILTER. Tally: WHERE: Adam Berlin, Lim Myungkyu, Dean Rasheed, yours truly FILTER: Tomas Vondra, Surafel Temesgen Couldn't find others expressing an opinion in this regard. On 2018-Nov-30, Tomas Vondra wrote: > I think it should be eno

Re: COPY FROM WHEN condition

On 11/30/18 2:00 PM, Surafel Temesgen wrote: On Thu, Nov 29, 2018 at 2:17 AM Tomas Vondra mailto:tomas.von...@2ndquadrant.com>> wrote: (c) allow VOLATILE functions in the FILTER clause, but change the behavior to make the behavior sane  Did changing the behavior means getting ne

Re: COPY FROM WHEN condition

On Thu, Nov 29, 2018 at 2:17 AM Tomas Vondra wrote: (c) allow VOLATILE functions in the FILTER clause, but change the > behavior to make the behavior sane > Did changing the behavior means getting new snapshot before evaluating a tuple to ensure the function sees results of any previously execu

Re: COPY FROM WHEN condition

On 11/26/18 2:25 PM, Surafel Temesgen wrote: > > > On Sat, Nov 24, 2018 at 5:09 AM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > 1) While comparing this to the FILTER clause we already have for > aggregates, I've noticed the aggregate version is > >     FILTER '

Re: COPY FROM WHEN condition

On Sat, Nov 24, 2018 at 5:09 AM Tomas Vondra wrote: > > 1) While comparing this to the FILTER clause we already have for > aggregates, I've noticed the aggregate version is > > FILTER '(' WHERE a_expr ')' > > while here we have > > FILTER '(' a_expr ')' > > For a while I was thinking that

Re: COPY FROM WHEN condition

On Sat, Nov 24, 2018 at 12:02 PM Dean Rasheed wrote: > Right now we have 2 syntaxes for filtering rows in queries, both of > which use WHERE immediately before the condition: > > 1). SELECT ... FROM ... WHERE condition > > 2). SELECT agg_fn FILTER (WHERE condition) FROM ... > > I'm not a huge fan

Re: COPY FROM WHEN condition

On Sat, 24 Nov 2018 at 02:09, Tomas Vondra wrote: > On 11/23/18 12:14 PM, Surafel Temesgen wrote: > > On Sun, Nov 11, 2018 at 11:59 PM Tomas Vondra > > mailto:tomas.von...@2ndquadrant.com>> wrote: > > So, what about using FILTER here? We already use it for aggregates when > > filtering row

Re: COPY FROM WHEN condition

On 11/23/18 12:14 PM, Surafel Temesgen wrote: > > > On Sun, Nov 11, 2018 at 11:59 PM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> wrote: > > > So, what about using FILTER here? We already use it for aggregates when > filtering rows to process. > > i think its good idea and de

Re: COPY FROM WHEN condition

On Sun, Nov 11, 2018 at 11:59 PM Tomas Vondra wrote: > > So, what about using FILTER here? We already use it for aggregates when > filtering rows to process. > > i think its good idea and describe its purpose more. Attache is a patch that use FILTER instead regards Surafel diff --git a/doc/src/sg

RE: COPY FROM WHEN condition

>> COPY table_name WHERE (some_condition) >> >> Users should already be familiar with the idea that WHERE performs a filter. >> > So, what about using FILTER here? We already use it for aggregates when > filtering rows to process. > That being said, I have no strong feelings either way. I'd be

Re: COPY FROM WHEN condition

On 11/9/18 4:51 PM, Adam Berlin wrote: > As a newcomer to this patch, when I read this example: > > COPY table_name WHEN (some_condition) > > .. I expect COPY to only be run when the condition is true, and I do not expect the WHEN clause to filter rows. I'm curious what you think about: > H

Re: COPY FROM WHEN condition

As a newcomer to this patch, when I read this example: COPY table_name WHEN (some_condition) .. I expect COPY to only be run when the condition is true, and I do not expect the WHEN clause to filter rows. I'm curious what you think about: COPY table_name WHERE (some_condition) Users should alr

Re: COPY FROM WHEN condition

David Fetter wrote: > It also seems like a violation of separation of concerns to couple > FEBE to grammar, so there'd need to be some way to do those things > separately, too. After re-reading psql/copy.c, I withdraw what I said upthread: it doesn't appear necessary to add anything to su

Re: COPY FROM WHEN condition

> > > > SELECT x.a, sum(x.b) > > FROM ( COPY INLINE '/path/to/foo.txt' FORMAT CSV ) as x( a integer, b > numeric, c text, d date, e json) ) > > Apologies for bike-shedding, but wouldn't the following be a better > fit with the current COPY? > > COPY t(a integer, b numeric, c text, d date, e jso

Re: COPY FROM WHEN condition

On Fri, Nov 02, 2018 at 12:58:12PM +0100, Daniel Verite wrote: > Pavel Stehule wrote: > > > > SELECT x.a, sum(x.b) > > > FROM ( COPY INLINE '/path/to/foo.txt' FORMAT CSV ) as x( a integer, b > > > numeric, c text, d date, e json) ) > > > WHERE x.d >= '2018-11-01' > > > > > > > > Without some

Re: COPY FROM WHEN condition

On Thu, Nov 01, 2018 at 10:57:25PM -0400, Corey Huinker wrote: > > > > > Are you thinking something like having a COPY command that provides > > > results in such a way that they could be referenced in a FROM clause > > > (perhaps a COPY that defines a cursor…)? > > > > That would also be nice, but

Re: COPY FROM WHEN condition

On 11/02/2018 03:57 AM, Corey Huinker wrote: > > Are you thinking something like having a COPY command that provides > > results in such a way that they could be referenced in a FROM clause > > (perhaps a COPY that defines a cursor…)? > > That would also be nice, but what I was

Re: COPY FROM WHEN condition

Pavel Stehule wrote: > > SELECT x.a, sum(x.b) > > FROM ( COPY INLINE '/path/to/foo.txt' FORMAT CSV ) as x( a integer, b > > numeric, c text, d date, e json) ) > > WHERE x.d >= '2018-11-01' > > > > > Without some special feature this example is not extra useful. It is based > on copy on ser

Re: COPY FROM WHEN condition

hi, On Wed, Oct 31, 2018 at 10:54 AM Masahiko Sawada wrote: > On Tue, Oct 30, 2018 at 11:47 PM Surafel Temesgen > wrote: > > I've looked at this patch and tested. > > When I use a function returning string in WHEN clause I got the following > error: > > =# copy test from '/tmp/aaa.csv' (format

Re: COPY FROM WHEN condition

pá 2. 11. 2018 v 3:57 odesílatel Corey Huinker napsal: > > Are you thinking something like having a COPY command that provides >> > results in such a way that they could be referenced in a FROM clause >> > (perhaps a COPY that defines a cursor…)? >> >> That would also be nice, but what I was thin

Re: COPY FROM WHEN condition

> > > Are you thinking something like having a COPY command that provides > > results in such a way that they could be referenced in a FROM clause > > (perhaps a COPY that defines a cursor…)? > > That would also be nice, but what I was thinking of was that some > highly restricted subset of cases o

Re: COPY FROM WHEN condition

On Wed, Oct 31, 2018 at 11:21:33PM +, Nasby, Jim wrote: > On Oct 11, 2018, at 10:35 AM, David Fetter wrote: > > > >> It didn't get far, but you may want to take a look at a rejected patch for > >> copy_srf() (set returning function) > >> https://www.postgresql.org/message-id/CADkLM%3DdoeiWQX4

Re: COPY FROM WHEN condition

On Oct 11, 2018, at 10:35 AM, David Fetter wrote: > >> It didn't get far, but you may want to take a look at a rejected patch for >> copy_srf() (set returning function) >> https://www.postgresql.org/message-id/CADkLM%3DdoeiWQX4AGtDNG4PsWfSXz3ai7kY%3DPZm3sUhsUeev9Bg%40mail.gmail.com >> https://com

Re: COPY FROM WHEN condition

On Tue, Oct 30, 2018 at 11:47 PM Surafel Temesgen wrote: > > > Hi, > Thank you for looking at it . > On Sun, Oct 28, 2018 at 7:19 PM Tomas Vondra > wrote: >> >> >> 1) I think this deserves at least some regression tests. Plenty of tests >> already use COPY, but there's no coverage for the new pi

Re: COPY FROM WHEN condition

Hi, Thank you for looking at it . On Sun, Oct 28, 2018 at 7:19 PM Tomas Vondra wrote: > > 1) I think this deserves at least some regression tests. Plenty of tests > already use COPY, but there's no coverage for the new piece. So let's > add a new test suite, or maybe add a couple of tests into co

RE: COPY FROM WHEN condition

Hello, Basically, this patch worked very well in my tests. > 3) For COPY TO, the WHEN clause is accepted but ignored, leading to confusing > cases like this: I found same issue. postgres=# copy t1 to '/home/lmk/t1.data' when c1 < 1; In the 'COPY TO' statement, 'WHEN clause' does not do an

Re: COPY FROM WHEN condition

Hi, I've taken a quick look at this on the way back from pgconf.eu, and it seems like a nice COPY improvement in a fairly good shape. Firstly, I think it's a valuable because it allows efficiently importing a subset of data. Currently, we either have to create an intermediate table, copy all the

Re: COPY FROM WHEN condition

On Fri, Oct 26, 2018 at 9:09 AM 임명규 wrote: > Hello, > > I want test this patch, but can't apply it. > > (master)$ git apply copy_from_when_con_v1.patch > i use patch and it work try (master)$ patch -p1 < copy_from_when_con_v1.patch regards Surafel

RE: COPY FROM WHEN condition

Hello, I want test this patch, but can't apply it. (master)$ git apply copy_from_when_con_v1.patch error: patch failed: src/backend/commands/copy.c:849 error: src/backend/commands/copy.c: patch does not apply fix or another way, let me know. Best regards, Myungkyu, Lim

Re: COPY FROM WHEN condition

On Thu, Oct 11, 2018 at 05:12:48AM -0400, Corey Huinker wrote: > On Thu, Oct 11, 2018 at 5:04 AM Surafel Temesgen > wrote: > > > > > > > On Thu, Oct 11, 2018 at 12:00 PM Christoph Moench-Tegeder < > > c...@burggraben.net> wrote: > > > >> You can: > >> COPY ( query ) TO 'filename'; > >> > > it i

Re: COPY FROM WHEN condition

On Thu, Oct 11, 2018 at 5:04 AM Surafel Temesgen wrote: > > > On Thu, Oct 11, 2018 at 12:00 PM Christoph Moench-Tegeder < > c...@burggraben.net> wrote: > >> You can: >> COPY ( query ) TO 'filename'; >> > it is for COPY FROM > > regards > Surafel > It didn't get far, but you may want to take a

Re: COPY FROM WHEN condition

On Thu, Oct 11, 2018 at 12:00 PM Christoph Moench-Tegeder < c...@burggraben.net> wrote: > You can: > COPY ( query ) TO 'filename'; > it is for COPY FROM regards Surafel

Re: COPY FROM WHEN condition

## Surafel Temesgen (surafel3...@gmail.com): > Currently we can not moves data from a file to a table based on some > condition on a certain column You can: COPY ( query ) TO 'filename'; There's even an example in the documentation: https://www.postgresql.org/docs/10/static/sql-copy.html "To c