[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: RE: Rename a column if not already renamed.?
From: "Day, David" <david.day () redcom ! com>
Date: 2019-08-29 13:04:25
Message-ID: 484def8213eb4ea5b988eb4024108892 () redcom ! com
[Download RAW message or body]
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 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@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 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@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 and new column names exist. Because that sure \
> sounds like what is happening.
> regards, tom lane
>
>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic