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@xxxxxxxxxxx>; Tom Lane <tgl@xxxxxxxxxxxxx> Cc: Luca Ferrari <fluca1978@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx 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 the patch 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 redefined function 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@xxxxxxxxxxx] Sent: Wednesday, August 21, 2019 11:47 AM To: Day, David <david.day@xxxxxxxxxx>; Tom Lane <tgl@xxxxxxxxxxxxx> Cc: Luca Ferrari <fluca1978@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx 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 available to 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 then it'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 merrily along. > 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 to me 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@xxxxxxxxxxxxx] > Sent: Tuesday, August 20, 2019 4:57 PM > To: Day, David <david.day@xxxxxxxxxx> > Cc: Luca Ferrari <fluca1978@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Rename a column if not already renamed.? > > "Day, David" <david.day@xxxxxxxxxx> 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 and new column names exist. > Because that sure sounds like what is happening. > > regards, tom lane > > > > > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx
