Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lxrug-00084t-Gb for pgsql-hackers@arkaria.postgresql.org; Mon, 28 Jun 2021 14:07:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lxruf-0000q2-BL for pgsql-hackers@arkaria.postgresql.org; Mon, 28 Jun 2021 14:07:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lxruf-0000pt-2B for pgsql-hackers@lists.postgresql.org; Mon, 28 Jun 2021 14:07:01 +0000 Received: from 6.mo6.mail-out.ovh.net ([87.98.177.69]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lxruc-00057k-89 for pgsql-hackers@lists.postgresql.org; Mon, 28 Jun 2021 14:07:00 +0000 Received: from player692.ha.ovh.net (unknown [10.108.16.135]) by mo6.mail-out.ovh.net (Postfix) with ESMTP id CB06A2567EC for ; Mon, 28 Jun 2021 16:06:56 +0200 (CEST) Received: from darold.net (lfbn-lyo-1-1368-22.w86-207.abo.wanadoo.fr [86.207.177.22]) (Authenticated sender: gilles@darold.net) by player692.ha.ovh.net (Postfix) with ESMTPSA id 0C66C1F8ADE89 for ; Mon, 28 Jun 2021 14:06:55 +0000 (UTC) Authentication-Results: garm.ovh; auth=pass (GARM-96R001b5a30d55-6abd-45b6-a686-818f51f0b775, EB9D96BE9C43CC06DDCE4E428FB6D31BEE7ADC33) smtp.auth=gilles@darold.net X-OVh-ClientIp: 86.207.177.22 Subject: Re: Deparsing rewritten query To: pgsql-hackers@lists.postgresql.org References: <20210627041138.zklczwmu3ms4ufnk@nol> <3176554.1624804492@sss.pgh.pa.us> <20210627150343.zslwnw7g564f2zz4@nol> <8085.1624806845@sss.pgh.pa.us> From: Gilles Darold Message-ID: <8f0b88a8-e9b8-0fd8-4dd1-ad14c4fdca0e@darold.net> Date: Mon, 28 Jun 2021 16:06:54 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.11.0 MIME-Version: 1.0 In-Reply-To: <8085.1624806845@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------B2EB4DF37C6E80F2874FA6FE" Content-Language: en-US X-Ovh-Tracer-Id: 15528411518250912668 X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: 0 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgeduledrfeehgedgjedvucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecuqfggjfdpvefjgfevmfevgfenuceurghilhhouhhtmecuhedttdenucenucfjughrpefuvfhfhffkffgfgggjtgesrgdtreertdefjeenucfhrhhomhepifhilhhlvghsucffrghrohhlugcuoehgihhllhgvshesuggrrhholhgurdhnvghtqeenucggtffrrghtthgvrhhnpefhgfehkeejjeegleelgefgkeffvddtudeufeefleejgfekteevtedvheelueduveenucffohhmrghinhepuggrrhholhgurdhnvghtnecukfhppedtrddtrddtrddtpdekiedrvddtjedrudejjedrvddvnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmohguvgepshhmthhpqdhouhhtpdhhvghlohepphhlrgihvghrieelvddrhhgrrdhovhhhrdhnvghtpdhinhgvtheptddrtddrtddrtddpmhgrihhlfhhrohhmpehgihhllhgvshesuggrrhholhgurdhnvghtpdhrtghpthhtohepphhgshhqlhdqhhgrtghkvghrsheslhhishhtshdrphhoshhtghhrvghsqhhlrdhorhhg List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------B2EB4DF37C6E80F2874FA6FE Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Le 27/06/2021 à 17:14, Tom Lane a écrit : > Julien Rouhaud writes: >> On Sun, Jun 27, 2021 at 10:34:52AM -0400, Tom Lane wrote: >>> It's not very hard to imagine someday moving view >>> expansion into the planner on efficiency grounds, leaving the rewriter >>> handling only the rare uses of INSERT/UPDATE/DELETE rules. >> Agreed. One the other hand having such a function in core may ensure that any >> significant change in those area will keep an API to retrieve the final query >> representation. > My point is precisely that I'm unwilling to make such a promise. > > I do not buy that this capability is worth very much, given that > we've gotten along fine without it for twenty-plus years. If you > want to have it as an internal, might-change-at-any-time API, > that seems all right. If you're trying to lock it down as something > that will be there forevermore, you're likely to end up with nothing. > > regards, tom lane I have to say that such feature would be very helpful for some DBA and especially migration work. The problem is when you have tons of views that call other views in the from or join clauses. These views also call other views, etc. I have had instances where there were up to 25 nested views calls. When you want to clean up this kind of code, using get_query_def () will help save a lot of manual rewrite time and headache to get the final code executed. If we could at least call get_query_def()through an extension if we didn't have a functionit would be ideal for DBAs.I agree this is unusual but when it does happen to you being able to call get_query_def () helps a lot. -- Gilles Darold http://www.darold.net/ --------------B2EB4DF37C6E80F2874FA6FE Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
Le 27/06/2021 à 17:14, Tom Lane a écrit :
Julien Rouhaud <rjuju123@gmail.com> writes:

On Sun, Jun 27, 2021 at 10:34:52AM -0400, Tom Lane wrote:

It's not very hard to imagine someday moving view
expansion into the planner on efficiency grounds, leaving the rewriter
handling only the rare uses of INSERT/UPDATE/DELETE rules.


      

        
Agreed.  One the other hand having such a function in core may ensure that any
significant change in those area will keep an API to retrieve the final query
representation.


      

      
My point is precisely that I'm unwilling to make such a promise.

I do not buy that this capability is worth very much, given that
we've gotten along fine without it for twenty-plus years.  If you
want to have it as an internal, might-change-at-any-time API,
that seems all right.  If you're trying to lock it down as something
that will be there forevermore, you're likely to end up with nothing.

			regards, tom lane


    

    


    

    
I
            have to say that such feature would be very helpful for some
            DBA and especially migration work. The
            problem is when you have tons of views that call other views
            in the from or join clauses. These
            views also call other views, etc. I
            have had instances where there were up to 25 nested views
            calls. When
            you want to clean up this kind of code, using get_query_def
            () will help save a lot of manual rewrite time and headache
            to get the final code executed.

    


          

    

        If
            we could at least call get_query_def() through an extension
            if we didn't have a function it would be ideal for DBAs.
          I
            agree this is unusual but when it does happen to you being
            able to call get_query_def () helps a lot. 

          

    

    
-- 
Gilles Darold
http://www.darold.net/

  


--------------B2EB4DF37C6E80F2874FA6FE--