Re: Status report on writeable CTEs - Mailing list pgsql-hackers

From Marko Tiikkaja
Subject Re: Status report on writeable CTEs
Date
Msg-id 4C408953.40702@cs.helsinki.fi
Whole thread Raw
In response to Re: Status report on writeable CTEs  (Hitoshi Harada <umi.tanuki@gmail.com>)
List pgsql-hackers
On 7/16/10 7:15 PM +0300, Hitoshi Harada wrote:
> 2010/7/17 Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi>:
>> I thought about this, but I don't necessarily like the idea of overloading
>> executor nodes.
>
> Neither do I have good shape for this solution. Maybe it's not good
> idea. But my concern is adding DtScanNode, which looks similar to
> MaterialNode. Of course each purpose is different, but quite big part
> will overlap each other, I think.

The way I see it is that reading from a tuplestore is so simple that we 
shouldn't be trying to merge together nodes just on that basis.  It 
seems to me that we'd have to add CteScan and WorkTableScan nodes there 
too and at that point it would become complicated.

>> I didn't look at this because I thought using a "tuplestore receiver" in the
>> portal logic was simple enough.  Any thoughts on how this would work?
>
> It's just deconstructing queries like:
>
> WITH t AS (INSERT INTO x ... RETURING *)
> SELECT * FROM t;
>
> to
>
> CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *;
> SELECT * FROM t;

That's an idea.  Can we somehow avoid name clashes with user-defined 
temporary tables?

> Another concern is tuplestore's memory exhausting. Tuplestore holds
> tuples in memory as far as the estimated memory usage is within
> work_mem (for *each* not total of all tuplestores!), but if you create
> dozens of tuplestore (and it's quite possible in wCTE use cases) we
> will possibly fail into memory overflow problems.

That doesn't seem very different from a big SELECT query, except with 
wCTEs, you actually *know* how many times the work_mem can be used 
before you run the query and can adjust work_mem accordingly.

That said, I personally could live with a separate GUC for just 
adjusting the work_mem of "wcte tuplestores".  Another option would be 
to unconditionally force the tuplestores to disk, but that sounds painful.


Regards,
Marko Tiikkaja


pgsql-hackers by date:

Previous
From: Aidan Van Dyk
Date:
Subject: Re: SHOW TABLES
Next
From: "Kevin Grittner"
Date:
Subject: Re: SHOW TABLES