Upgrade scripts should use CREATE OR REPLACE and the like

Question

Upgrade scripts should use CREATE OR REPLACE and the like

Opened this issue 2 years ago · 4 comments

I was testing 1.4.0 in a database here before it was it was 1.4.0. When I went to upgrade that database to 1.4.1, I got

ERROR:  function "spoly" already exists with same argument types

My solution was to change all the "CREATE FUNCTION" statements in the upgrade script to "CREATE OR REPLACE FUNCTION". The upgrade then worked fine. I feel this should be the standard practice for all upgrade scripts, past and future.

Answer 1 · 2023-11-30T08:12:09.000Z

On Wed, Nov 29, 2023 at 04:26:52PM -0800, Ed Sabol wrote: I was testing 1.4.0 in a database here before it was it was 1.4.0. When I went to upgrade to 1.4.1 and got ``` ERROR: function "spoly" already exists with same argument types ``` My solution was to change all the "CREATE FUNCTION" statements to "CREATE OR REPLACE FUNCTION". The upgrade then worked fine. I feel this should be the standard practice for all upgrade scripts, past and future.

+1 on this. Re-runnable SQL is great; if all statements in our SQL are re-runnable we can perhaps one day stop maintaining the upgraders entirely (except perhaps a single cleanup script to drop severely misguided material) and just run the full SQL on every upgrade. The main trouble I see is that someone would need to research when IF EXISTS and OR REPLACE were added where; I'm not absolutely sure all versions we promise to support know about them in all constructs we use in our SQL scripts.

Answer 2 · 2023-12-01T21:02:09.000Z

I'm surprised that using create or replace in the upgrade script worked to fix the problem because it actually shouldn't: postgres/postgres@b9b21ac

Answer 3 · 2023-12-01T22:07:35.000Z

I'm surprised that using create or replace in the upgrade script worked to fix the problem because it actually shouldn't: postgres/postgres@b9b21ac

Are we reading the same commit message? It says, "Hence, forbid CREATE OR REPLACE of an existing object unless it already belongs to the extension." (Emphasis mine.) So why shouldn't it work?

Answer 4 · 2023-12-01T23:34:09.000Z

Ah right, I had assumed you had created an spoly function outside of pg_sphere and then the upgrade complained it's already there, but it was a function from inside pg_sphere. That explains why it worked, thanks.