RE: Rename a column if not already renamed.? - Mailing list pgsql-general

Hi,

Finally resolved this.  Bottom-line some stupidity-bad analysis on my part.
Scenario was - changes were ported from trunk back to a branch and then rolling that branch
back into trunk. 

Altering the rename_column fx to check that old and new name did not exist
was a necessary for  merge process to complete.   

I ended up with an additional patch in trunk that would only be relevant to a upgraded system,
to DROP  IF EXISTS old_column name that was re-added by a trunk patch to when the branch rolled forward.

Obviously nothing to do with 9.6 -> 11.3 postgres upgrade.


Again thanks to all for assistance


Dave



-----Original Message-----
From: Day, David 
Sent: Wednesday, August 21, 2019 2:58 PM
To: 'Adrian Klaver' <adrian.klaver@aklaver.com>; Tom Lane <tgl@sss.pgh.pa.us>
Cc: Luca Ferrari <fluca1978@gmail.com>; pgsql-general@postgresql.org
Subject: RE: Rename a column if not already renamed.?

Restoring into 11.3 instance the 9.6 dump  ? ->  yes.

For the upgrade scenario, I  confirmed that both old column name and new name are in the pg_attribute table at the time
thepatch attempts to rename it.
 
Why both  is a big question.
However,
It is easy enough to re-write the column rename function to deal with the simultaneous possibility.  I will include the
redefinedfunction in the merge patch and see how it goes.
 

I'll update the thread after some further exploration.

Thanks all for your assistance.


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] 
Sent: Wednesday, August 21, 2019 11:47 AM
To: Day, David <david.day@redcom.com>; Tom Lane <tgl@sss.pgh.pa.us>
Cc: Luca Ferrari <fluca1978@gmail.com>; pgsql-general@postgresql.org
Subject: Re: Rename a column if not already renamed.?

On 8/21/19 7:52 AM, Day, David wrote:
> I agree the function could be improved to deal with both old and new name existing simultaneously.
> That is almost certainly the root  cause, and one that I would confirm if the tester and site were currently
availableto me.
 
> 
> Our work flow  for this scenario is something like:
> 
> 1.  9.6 pg_dump takes a snapshot of our  9.6  database.
> 2.  Postgres is upgraded/freshly installed to  11.3..
> 3.  The 9.6 database is restored using the version 11 pg_restore tool.

In 3) you are restoring to the new 11.3 instance, correct?

> 
> 4. Once our application process starts up, it sees there is a patch available in it's old branch that is one greater
thenit's restored  9.6 content.
 
> That happens to be a merge patch which resets the expectations.
> It attempts to apply all patches in the new branch since the point of divergence and runs into my current issue.
>   
> It occurs to me I could simply put an exception handler in the rename column function and I would likely proceed
merrilyalong.
 
> But curiosity is killing me and the cat. What is causing the old name to persist in the pg_attribute table after the
rename.?
 

If you are indeed working on the new instance pg_attribute would have no 'memory' of the dropped column.  It would seem
tome to come down to what is passed into sys.rename_column() as old_name_, new_name.
 

> 
> Would a stale function referencing the old column name be a contributor?
> 
> 
> Regards
> 
> 
> Dave Day
> 
> 
> 
> 
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, August 20, 2019 4:57 PM
> To: Day, David <david.day@redcom.com>
> Cc: Luca Ferrari <fluca1978@gmail.com>; pgsql-general@postgresql.org
> Subject: Re: Rename a column if not already renamed.?
> 
> "Day, David" <david.day@redcom.com> writes:
>> The error is something like column already exists and
> 
> Not sure about the workflow this function is used within, but maybe you need to consider what to do when both the old
andnew column names exist.
 
> Because that sure sounds like what is happening.
> 
>             regards, tom lane
> 
> 
> 
> 
> 


--
Adrian Klaver
adrian.klaver@aklaver.com