Horton
Menu

Thread

  1. [PATCH] Optional OR REPLACE in CREATE OPERATOR statement

    Svetlana Derevyanko <s.derevyanko@postgrespro.ru> — 2022-07-05T08:40:23Z

     
Hello hackers,
 
It seems useful to have [OR REPLACE] option in CREATE OPERATOR statement, as in CREATE FUNCTION. This option may be good for writing extension update scripts, to avoid errors with re-creating the same operator.
 
Because of cached query plans, only RESTRICT and JOIN options can be changed for existing operator, as in ALTER OPERATOR statement.
(discussed here:  https://www.postgresql.org/message-id/flat/3348985.V7xMLFDaJO%40dinodell )
 
The attached patch will be proposed for September CF.
 
Best regards,
--
Svetlana Derevyanko
Postgres Professional:  http://www.postgrespro.com
The Russian Postgres Company

  2. Re: [PATCH] Optional OR REPLACE in CREATE OPERATOR statement

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-07-05T15:29:28Z

    =?UTF-8?B?U3ZldGxhbmEgRGVyZXZ5YW5rbw==?= <s.derevyanko@postgrespro.ru> writes:
> It seems useful to have [OR REPLACE] option in CREATE OPERATOR statement, as in CREATE FUNCTION. This option may be good for writing extension update scripts, to avoid errors with re-creating the same operator.

No, that's not acceptable.  CREATE OR REPLACE should always produce
exactly the same final state of the object, but in this case we cannot
change the underlying function if the operator already exists.

(At least, not without writing a bunch of infrastructure to update
existing views/rules that might use the operator; which among other
things would create a lot of deadlock risks.)

			regards, tom lane

  3. Re[2]: [PATCH] Optional OR REPLACE in CREATE OPERATOR statement

    Svetlana Derevyanko <s.derevyanko@postgrespro.ru> — 2022-07-06T12:00:55Z

      
>Вторник, 5 июля 2022, 18:29 +03:00 от Tom Lane <tgl@sss.pgh.pa.us>:
> 
>=?UTF-8?B?U3ZldGxhbmEgRGVyZXZ5YW5rbw==?= < s.derevyanko@postgrespro.ru > writes:
>> It seems useful to have [OR REPLACE] option in CREATE OPERATOR statement, as in CREATE FUNCTION. This option may be good for writing extension update scripts, to avoid errors with re-creating the same operator.
>No, that's not acceptable. CREATE OR REPLACE should always produce
>exactly the same final state of the object, but in this case we cannot
>change the underlying function if the operator already exists.
>
>(At least, not without writing a bunch of infrastructure to update
>existing views/rules that might use the operator; which among other
>things would create a lot of deadlock risks.)
>
>regards, tom lane
Hello,
 
> CREATE OR REPLACE should always produce exactly the same final state of the object,
> but in this case we cannot change the underlying function if the operator already exists.
   
Do you mean that for existing operator CREATE OR REPLACE should be the same as DROP OPERATOR and CREATE OPERATOR,  with relevant re-creation of existing view/rules/..., using this operator? If yes, what exactly is wrong with  changing only RESTRICT and JOIN parameters (or is the problem in possible user`s confusion with attempts to change something more?). If no, could you, please, clarify what "final state" here means?
 
Also, if OR REPLACE is unacceptable, then what do you think about IF NOT EXISTS option?
 
Thanks,
 
--
Svetlana Derevyanko
Postgres Professional:  http://www.postgrespro.com
The Russian Postgres Company

  4. Re: Re[2]: [PATCH] Optional OR REPLACE in CREATE OPERATOR statement

    Nikita Malakhov <hukutoc@gmail.com> — 2022-12-12T19:14:08Z

    Hi,

Svetlana, yes, Tom means that CREATE OR REPLACE should always produce
the same result no matter which branch actually worked - CREATE or REPLACE.
REPLACE case must produce exactly the same result as you've mentioned -
DROP and CREATE.

As for IF NOT EXISTS option I agree, it seems a reasonable addition to
simplify
error handling in scripts, go on.


On Wed, Jul 6, 2022 at 3:01 PM Svetlana Derevyanko <
s.derevyanko@postgrespro.ru> wrote:

>
>
> Вторник, 5 июля 2022, 18:29 +03:00 от Tom Lane <tgl@sss.pgh.pa.us>:
>
> =?UTF-8?B?U3ZldGxhbmEgRGVyZXZ5YW5rbw==?= <s.derevyanko@postgrespro.ru
> <http:///compose?To=s.derevyanko@postgrespro.ru>> writes:
> > It seems useful to have [OR REPLACE] option in CREATE OPERATOR
> statement, as in CREATE FUNCTION. This option may be good for
> writing extension update scripts, to avoid errors with re-creating the same
> operator.
>
> No, that's not acceptable. CREATE OR REPLACE should always produce
> exactly the same final state of the object, but in this case we cannot
> change the underlying function if the operator already exists.
>
> (At least, not without writing a bunch of infrastructure to update
> existing views/rules that might use the operator; which among other
> things would create a lot of deadlock risks.)
>
> regards, tom lane
>
> Hello,
>
> > CREATE OR REPLACE should always produce exactly the same final state of
> the object,
> > but in this case we cannot change the underlying function if the
> operator already exists.
>
> Do you mean that for existing operator CREATE OR REPLACE should be the
> same as DROP OPERATOR and CREATE OPERATOR,  with relevant re-creation of
> existing view/rules/..., using this operator? If yes, what exactly is wrong
> with  changing only RESTRICT and JOIN parameters (or is the problem in
> possible user`s confusion with attempts to change something more?). If no,
> could you, please, clarify what "final state" here means?
>
> Also, if OR REPLACE is unacceptable, then what do you think about IF NOT
> EXISTS option?
>
> Thanks,
>
> --
> Svetlana Derevyanko
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>


-- 
Regards,
Nikita Malakhov
Postgres Professional
https://postgrespro.ru/

  5. Re: [PATCH] Optional OR REPLACE in CREATE OPERATOR statement

    Gregory Stark (as CFM) <stark.cfm@gmail.com> — 2023-03-22T03:29:12Z

    On Tue, 5 Jul 2022 at 11:29, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> No, that's not acceptable.  CREATE OR REPLACE should always produce
> exactly the same final state of the object, but in this case we cannot
> change the underlying function if the operator already exists.

It sounds like this patch isn't the direction to go in. I don't know
if IF NOT EXISTS is better but that design discussion should probably
happen after this commitfest.

I'm sorry but I guess I'll mark this patch Rejected.

-- 
Gregory Stark
As Commitfest Manager