Re: [HACKERS] INSERT .. ON CONFLICT DO SELECT [FOR ..]

On Tue, Aug 15, 2017 at 7:43 AM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Aug 14, 2017 at 6:23 PM, Marko Tiikkaja <ma...@joh.to> wrote: > > Attached is a patch for $SUBJECT. It might still be a bit rough around > the > > edges and probably light on docs and t

[HACKERS] INSERT .. ON CONFLICT DO SELECT [FOR ..]

Hi, Attached is a patch for $SUBJECT. It might still be a bit rough around the edges and probably light on docs and testing, but I thought I'd post it anyway so I won't forget. .m insert_conflict_select_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Allow INSTEAD OF DELETE triggers to modify the tuple for RETURNING

On Fri, Jul 1, 2016 at 2:12 AM, I wrote: > Currently the tuple returned by INSTEAD OF triggers on DELETEs is only > used to determine whether to pretend that the DELETE happened or not, which > is often not helpful enough; for example, the actual tuple might have been > concurrently UPDATEd by

Re: [HACKERS] PG 10 release notes

On Thu, Apr 27, 2017 at 4:13 PM, Bruce Momjian wrote: > On Thu, Apr 27, 2017 at 08:00:28AM +0530, Amit Kapila wrote: > > > Oh, so non-correlated subqueries can be run in parallel. Yes, that is > > > something we should have in the release notes. How is this? > > > > > >

Re: [HACKERS] Query started showing wrong result after Ctrl+c

On Thu, Oct 12, 2017 at 12:03 PM, tushar wrote: > postgres=# SELECT * FROM ( SELECT n from tv where n= (select * from > (select n from tv limit 1) c)) as c ; > n > -- > 3713 > (1 row) > > This time , query is started showing wrong result. Is this an

Re: [HACKERS] [PATCH] Pattern based listeners for asynchronous messaging (LISTEN/NOTIFY)

Hi Markus, On Sun, Aug 20, 2017 at 9:56 PM, Markus Sintonen wrote: > I also encountered this when I built it with different configuration. I > attached updated patch with the correct number of arguments to > 'similar_escape'. I also added preliminary documentation to

Re: [HACKERS] 200 = 199 + 1?

On Wed, Sep 27, 2017 at 5:45 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Marko Tiikkaja <ma...@joh.to> writes: > > I wonder if the nested loop shouldn't have some kind of a cap on its own > > estimate if it's wildly off of what you'd get by multiplying the child >

Re: [HACKERS] Index expression syntax

On Fri, Sep 29, 2017 at 9:31 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > I wonder why syntax error is produced in this case: > > postgres=# create index metaindex on foo using gin(to_tsvector('english', > x)||to_tsvector('english',y)); > ERROR: syntax error at or near "||" >

Re: [HACKERS] INSERT .. ON CONFLICT DO SELECT [FOR ..]

On Mon, Sep 4, 2017 at 4:09 AM, Peter Geoghegan <p...@bowt.ie> wrote: > On Tue, Aug 15, 2017 at 12:17 AM, Marko Tiikkaja <ma...@joh.to> wrote: > > On Tue, Aug 15, 2017 at 7:43 AM, Peter Geoghegan <p...@bowt.ie> wrote: > >> > >> On Mon, Aug 14, 2017 at

Re: [HACKERS] INSERT .. ON CONFLICT DO SELECT [FOR ..]

On Mon, Sep 4, 2017 at 7:46 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Sep 4, 2017 at 10:05 AM, Marko Tiikkaja <ma...@joh.to> wrote: > But I'm generally against > > interfaces which put arbitrary restrictions on what power users can do on > > the basis that

[HACKERS] 200 = 199 + 1?

Hi, I just came across this very peculiar behavior: =# create table foo(id int primary key); CREATE TABLE =# insert into foo select generate_series(1, 100); INSERT 0 100 =# set enable_hashjoin to false; set enable_mergejoin to false; SET SET =# explain select * from foo where id in

[HACKERS] Re: [BUGS] BUG #14849: jsonb_build_object doesn't like VARIADIC calls very much

On Wed, Oct 25, 2017 at 5:32 PM, Michael Paquier wrote: > On Mon, Oct 23, 2017 at 6:50 AM, Michael Paquier > wrote: > > Okay, attached is what I think a fully implemented patch should look > > like. On top of what Andrew has done, I added

Re: [HACKERS] Writeable CTE patch

I wrote: Attached is the latest version of this patch. Here's that same patch in context diff format. Sorry for the noise. Regards, Marko Tiikkaja *** a/doc/src/sgml/queries.sgml --- b/doc/src/sgml/queries.sgml *** *** 1499,1505 SELECT 3, 'three'; synopsis SELECT

Re: [HACKERS] Writeable CTE patch

an updated patch in a couple of days. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Syntax for partitioning

, and I think it's nice for the general case, but I think the reversed operator weirdness is a bit too much. Maybe we should use something like PARTITION bar VALUES OPERATOR 0 when the user specifies the operator? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers

Re: [HACKERS] Syntax for partitioning

like a bad idea.. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Writeable CTE patch

enough to justify keeping such a wart in the executor. Under the circumstances I'd lean towards this option. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Writeable CTE patch

es_result_relation_info are broken and need to be dropped from the patch. Done. I kept the logic for result relations to allow nested ModifyTable nodes, but I don't think it ever did the right thing with EvalPlanQual() and nested nodes. I'll have think about that. Regards, Marko Tiikkaja -- Sent via pgsql-hackers

Re: [HACKERS] Writeable CTE patch

sure how hard that would be, though; it might not be a practical answer. I'm trying to avoid doing this, at least for now. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [HACKERS] Writeable CTE patch

uses the active snapshot stack. Any ideas? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Aggregate ORDER BY patch

and having this seems like a useful feature in some cases. Although the DISTINCT ON syntax would have a bit more resemblance on the existing syntax, I'd still like to see agg(distinct x order by x,y). Just my $0.02. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers

Re: [HACKERS] alpha3 bundled -- please verify

not figure out what was wrong. Is this documented somewhere? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Writeable CTEs

problem in the original post. The problem only affects INSERT, UDPATE and DELETE where you are actually counting affected rows (i.e. PQcmdTuples(), not PQntuples()) so the this example would work as expected. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers

Re: [HACKERS] Testing with concurrent sessions

for here. What I think would be most useful would be to interleave statements between transactions, not just randomly fire psql sessions and hope for race conditions. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Testing with concurrent sessions

asynchronous connections. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Testing with concurrent sessions

On 2010-01-07 18:13 +0200, Marko Tiikkaja wrote: I had a similar syntax in mind, but instead of using threads, just execute the file in order using asynchronous connections. I completely failed to make the point here which was to somehow mark which statements will (or, should) block. So here

Re: [HACKERS] Review of Writeable CTE Patch

. So it sounds like we should expect an updated patch shortly? Yes. I'm adding more comments and documentation, expect a patch within a couple of days. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] Review of Writeable CTE Patch

on modifying it. A comment explaining this might be in order. If the copy is necessary (e.g. because the snapshot must not be modified externally, and there's actual risk that it is), then maybe it would be better to create a new function RegisterSnapshotCopy instead? Sounds reasonable. Regards, Marko

Re: [HACKERS] Review: listagg aggregate

parameter is stable, then store it and use it as constant. I prefer a) Someone might have a perfectly good use case for using different delimiters. I don't think it's a good idea to be artificially limiting what you can and can't do. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Review of Writeable CTE Patch

the same with UPDATE .. FROM and DELETE .. USING. * The patch includes regression tests, but no error cases in it. More test cases are needed for stupid queries. Ok, I'll add these and send an updated patch in a few hours. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Review of Writeable CTE Patch

(true); ERROR: recursive query t does not have the form non-recursive-term UNION [ALL] recursive-term but I didn't want to throw people off to think that they can use INSERT/UPDATE/RETURNING in a RECURSIVE CTE, just to get complaints about syntax error. Regards, Marko Tiikkaja -- Sent via

Re: [HACKERS] Review of Writeable CTE Patch

) - possible pitfalls of CTEs not being pipelined Right. The documentation in its current state is definitely lacking. I've tried to focus all the time I have in making this patch technically good. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org

Re: [HACKERS] Review of Writeable CTE Patch

On 2010-02-03 18:41 UTC+2, Merlin Moncure wrote: On Wed, Feb 3, 2010 at 11:18 AM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: Right. The documentation in its current state is definitely lacking. I've tried to focus all the time I have in making this patch technically good. Do you

[HACKERS] Writeable CTEs documentation patch

. Is there one somewhere? While working on the docs, I noticed one problem with the patch itself: it doesn't handle multi-statement DO INSTEAD rules correctly. I'm going to submit a fix for that later. Any suggestions, whatsoever, are welcome. Regards, Marko Tiikkaja *** a/doc/src/sgml

Re: [HACKERS] Writeable CTEs patch

On 2010-02-05 07:14 UTC+2, Takahiro Itagaki wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: Here's an updated patch. Only changes from the previous patch are fixing the above issue and a regression test for it. * In the regression tests, almost all of them don't have ORDER

Re: [HACKERS] damage control mode

could turn them into some documentation? David? The documentation has definitely improved from the last time Robert looked at it, but I fear it still needs some more work. I'm willing to do that work, but I need something concrete. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Writeable CTEs patch

On 2010-02-08 18:42 +0200, Robert Haas wrote: On Thu, Feb 4, 2010 at 11:57 AM, Marko Tiikkaja Here's an updated patch. Only changes from the previous patch are fixing the above issue and a regression test for it. - I'm not sure that canSetTag is the right name for the additional argument

[HACKERS] Writeable CTEs and empty relations

asking: is there a simple enough way around this? Would updating scan-rs_nblocks before scanning the first tuple be OK? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [HACKERS] Writeable CTEs and empty relations

On 2010-02-09 01:09 +0200, Tom Lane wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: I traced this down to heapam.c, which has this: ... This doesn't exactly work anymore since we modify the snapshot after calling ExecInitScan(). So don't do that. The executor is generally

Re: [HACKERS] Writeable CTEs patch

On 2010-02-08 18:42 +0200, Robert Haas wrote: On Thu, Feb 4, 2010 at 11:57 AM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: Here's an updated patch. Only changes from the previous patch are fixing the above issue and a regression test for it. - I'm not sure that canSetTag

Re: [HACKERS] Writeable CTEs and empty relations

whenever the scan nodes need to reinit. Does this sound completely unacceptable? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Writeable CTEs and empty relations

On 2010-02-10 02:19 +0200, Tom Lane wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: Does this sound completely unacceptable? You still haven't explained why it's a good idea to change the snapshot after the executor has started. Right at the moment I'm prepared to reject

Re: [HACKERS] Writeable CTEs and empty relations

On 2010-02-10 03:20 +0200, Tom Lane wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: On 2010-02-10 02:19 +0200, Tom Lane wrote: You still haven't explained why it's a good idea to change the snapshot after the executor has started. Right at the moment I'm prepared to reject

Re: [HACKERS] Writeable CTEs and empty relations

On 2010-02-10 21:59 +0200, Robert Haas wrote: On Wed, Feb 10, 2010 at 5:05 AM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 2010-02-10 03:20 +0200, Tom Lane wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: On 2010-02-10 02:19 +0200, Tom Lane wrote: You still haven't

Re: [HACKERS] Writeable CTEs and empty relations

. I'm looking at this, but I can't think of any good way of associating the tuplestores from PortalRunMulti() with the correct CTEs. Any ideas? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] Writeable CTEs and empty relations

On 2010-02-11 00:50 +0200, Marko Tiikkaja wrote: On 2010-02-10 23:57 +0200, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: If the executor has buried in it the assumption that the snapshot can't change after startup, then does that mean that we need to start up and shut down

Re: [HACKERS] Writeable CTEs and empty relations

On 2010-02-11 01:58 +0200, Robert Haas wrote: On Wed, Feb 10, 2010 at 6:25 PM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 2010-02-11 00:50 +0200, Marko Tiikkaja wrote: Ok, what about the following: - after planning the original query, standard_planner() goes through the list

Re: [HACKERS] Writeable CTEs and empty relations

that there would've been a shot, even if I finished the patch on time. Thanks everyone for helping out and reviewing this, especially Robert and Tom. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] Writeable CTEs and empty relations

.. USING on that CTE. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Writeable CTEs and empty relations

On Thu, 11 Feb 2010 10:53:22 -0500, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 11, 2010 at 8:46 AM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 2010-02-11 03:44 +0200, I wrote: I'm going to have to disappoint a bunch of people and give up. :-( Btw. would it make sense

Re: [HACKERS] Writeable CTEs and empty relations

On Thu, 11 Feb 2010 19:28:28 +0200, I wrote: On Thu, 11 Feb 2010 10:53:22 -0500, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 11, 2010 at 8:46 AM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 2010-02-11 03:44 +0200, I wrote: I'm going to have to disappoint a bunch of people

Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

: (b = 2) Total runtime: 0.075 ms (3 rows) Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Remove ROW | ROWS from OFFSET and FETCH

of the syntax optional is just breaking that portability. We already have a non-portable, non-standard syntax. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: access control jails (and introduction as aspiring GSoC student)

. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] join removal

On 2010-03-29 11:19 +0200, Pavel Stehule wrote: postgres=# explain select a from a left join b on true; This is effectively a cross join and it would give wrong answers. Try SELECT a FROM a LEFT JOIN b ON a.a = b.b; Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql

[HACKERS] INSERT and parentheses

better, but I can't think of anything. Thoughts? Regards, Marko Tiikkaja *** a/src/backend/parser/analyze.c --- b/src/backend/parser/analyze.c *** *** 730,742 transformInsertRow(ParseState *pstate, List *exprlist

Re: [HACKERS] Partitioning/inherited tables vs FKs

; DELETE 1 TXB = select a from foo for share; -- waits What am I missing? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Partitioning/inherited tables vs FKs

Zeilen) T2 DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this blocks, because T1 is still holding the lock). Obviously you wouldn't delete anything with a SHARE lock. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Partitioning/inherited tables vs FKs

suggestion in a more general way, suggesting the use of row-level locks. T2 should be holding an exclusive row-level lock (SELECT ... FOR UPDATE) when checking for references. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Partitioning/inherited tables vs FKs

the rows in a serializable transaction so this doesn't solve the problem. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] (9.1) btree_gist support for searching on not equals

: CREATE UNIQUE INDEX a_single_row ON a ((1)); Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] UPDATE .. RETURNING OLD.*

, and bar was 0, so I couldn't put bar != 0 into the WHERE clause. This time I got around it by using RETURNING bar and checking that it was 1 on the client side, but I can come up with other cases where you can't do that. Comments? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list

[HACKERS] Progress on Writeable CTEs

Hi, I've looked at implementing writeable CTEs on top of my DML node patch (repo here: git://git.postgresql.org/git/writeable_cte.git ) and encountered a few conundrums. You can see what I've done in the actually_write branch of that repo. - Currently we only store the OIDs of the result

Re: [HACKERS] Using results from INSERT ... RETURNING

no good ideas for them. I'll try to provide some more feedback on this after I look it over some more. Thanks! Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Using results from INSERT ... RETURNING

an updated patch in a couple of days. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Using results from INSERT ... RETURNING

Robert Haas wrote: Can you at least take a stab at it? We can fix your grammar, but guessing what's going on without documentation is hard. With some help from David Fetter, I took another try at it. I hope someone finds this helpful. I'm happy to answer any questions. Regards, Marko

Re: [HACKERS] Using results from INSERT ... RETURNING

, but supporting writeable CTEs will be a lot easier as seen in the git repo David pointed you to. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Using results from INSERT ... RETURNING

don't want to provide the means to do this, but writeable CTEs alone aren't meant to handle this. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Using results from INSERT ... RETURNING

and mark this Ready for Committer. Thanks for your patience. Thanks for reviewing! Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Using results from INSERT ... RETURNING

INTO foo WITH t AS ( DELETE FROM bar RETURNING * ) SELECT * FROM t; which is probably the most useful thing you could do with this feature. Am I misinterpreting what you said? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Using results from INSERT ... RETURNING

. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Writeable CTEs and side effects

the data we now have inside the CTEs. This way we can avoid storing useless rows in memory without unexpected behaviour and caveats. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [HACKERS] Writeable CTEs and side effects

Jaime Casanova wrote: On Wed, Oct 7, 2009 at 4:08 PM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: 1) WITH t AS (UPDATE foo SET bar = bar+1 RETURNING *) SELECT * FROM t LIMIT 1; What's problematic here is that only 1 row is read from the CTE, meaning also that only

Re: [HACKERS] Writeable CTEs and side effects

Jaime Casanova wrote: On Wed, Oct 7, 2009 at 4:20 PM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: Right. This is exactly what I'm trying to do, except I think we could easily optimize this case and store only the first processed row inside the CTE. why? we don't should be thinking

Re: [HACKERS] Writeable CTEs and side effects

. Agreed. Does this have any impact on the pending DML-node patch? Not really. This could be done without the patch, but we can use far more of the existing CTE code with the patch. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] Writeable CTEs and side effects

if the CTE isn't referenced, so you could write this as: WITH t1 AS (UPDATE foo SET bar=bar+1), t2 AS (UPDATE baz SET bar=bar+1) VALUES(true); Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] Using results from INSERT ... RETURNING

Tom Lane wrote: Does anyone have a problem with the ModifyTable suggestion, or a better idea? Lacking a better idea, +1 for ModifyTable from me. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] Using results from INSERT ... RETURNING

tired of that topic right now. I'm sorry, it didn't occur to me that this is part of this patch, but I made these changes in my writeable CTE repo, see http://git.postgresql.org/gitweb?p=writeable_cte.git;a=commitdiff;h=41ad3d24af845c67a3866e0946129cf9809fe7e9 Regards, Marko Tiikkaja -- Sent via

Re: [HACKERS] Using results from INSERT ... RETURNING

patch. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Using results from INSERT ... RETURNING

moving backward. Ok. 2. Move actual execution of (non-deferred) AFTER triggers inside ModifyTuple. This might be a good idea in order to have the most consistent results for a series of WITH queries, but I'm not sure. This definitely seems like the best option to me. Regards, Marko Tiikkaja

Re: [HACKERS] Writeable CTEs and side effects

AS (DELETE FROM foo RETURNING id) UPDATE bar SET foo_id = NULL FROM t WHERE t.id = bar.foo_id; Did I misunderstand something here? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

[HACKERS] Writeable CTEs, again

. I'd appreciate any input. Regards, Marko Tiikkaja diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index b2741bc..111ed6a 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1499,7 +1499,7 @@ SELECT 3, 'three'; synopsis SELECT replaceableselect_list

Re: [HACKERS] WIP: push AFTER-trigger execution into ModifyTable node

the CTE for this to actually be a quite significant performance benefit, so I'm not too fancy about the approach you're suggesting. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [HACKERS] WIP: push AFTER-trigger execution into ModifyTable node

or an INSERT. Also, UPDATEs and DELETEs inside CTEs can't have the same result relations. Whether or not we want to break the expected(?) behaviour for statement-level triggers, I have no opinion to way or another. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers

Re: [HACKERS] WIP: push AFTER-trigger execution into ModifyTable node

don't think it matters because those trigger calls would have a different snapshot, right? Or am I missing something? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [HACKERS] WIP: push AFTER-trigger execution into ModifyTable node

, but at least for cursors we'd have to first execute ModifyTable nodes. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Query::targetList and RETURNING

be glad to do it if people think this is useful. Thoughts? Regards, Marko Tiikkaja *** a/src/backend/executor/functions.c --- b/src/backend/executor/functions.c *** *** 1070,1078 check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList, (parse

Re: [HACKERS] Query::targetList and RETURNING

, but this way you only have to special-case in grouping_planner(), and targetList always means the same thing. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Query::targetList and RETURNING

Tom Lane wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: Tom Lane wrote: This doesn't really seem like a good idea from here. You're changing a decision that has something like twenty years' standing in the code, for no real gain. AFAICS this is just going to move the special

Re: [HACKERS] Query::targetList and RETURNING

Tom Lane wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: I wouldn't care for this at all, but with things the way they are right now, the writeable CTE patch has to do quite a few of these: [ shrug... ] How many is quite a few? In a quick search for existing references

[HACKERS] Writeable CTE patch

, Marko Tiikkaja diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index b2741bc..3aa7da5 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1499,7 +1499,7 @@ SELECT 3, 'three'; synopsis SELECT replaceableselect_list/replaceable FROM

Re: [HACKERS] small exclusion constraints patch

that. Having said that, I also think that supporting in exclusion constraints would be useful. I can't come up with a real-world use case right now though. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] Functional dependencies and GROUP BY

to do it. As far as I recall, at least 99% of the user requests for this type of behavior, maybe 100%, would be satisfied by recognizing the group-by-primary-key case. So I think we should do that and be happy. +1 Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers

[HACKERS] Status report on writeable CTEs

any obvious flaws in this? Any feedback is welcome. I'd also be happy to get some help on this project. Regards, Marko Tiikkaja *** a/src/backend/commands/copy.c --- b/src/backend/commands/copy.c *** *** 1092,1098 DoCopy(const CopyStmt *stmt, const char *queryString

Re: [HACKERS] Status report on writeable CTEs

On 7/12/10 9:07 PM +0300, I wrote: Consider: WITH t AS (SELECT 1), t2 AS (SELECT * FROM t2) VALUES (true); That should of course have been SELECT * FROM t, not t2. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Status report on writeable CTEs

queries into a list of Queries and store that list into the CommonTableExprs. The planner would then use this information and also expand the rewrite products. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription

Re: [HACKERS] Status report on writeable CTEs

it. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Status report on writeable CTEs

be to unconditionally force the tuplestores to disk, but that sounds painful. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Rewrite, normal execution vs. EXPLAIN ANALYZE

? And if I didn't, why do we do this differently? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

  1   2   3   4   5   6   7   >