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
