Thread: Rename a column if not already renamed.?
I have a function used by my patch files which attempts to determine if the old column name exists, if so it proceeds
withthe rename.
CREATE OR REPLACE FUNCTION sys.rename_column(schema_table_ regclass, old_name_ text, new_name text)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
BEGIN
IF EXISTS(SELECT 1 FROM pg_attribute
WHERE attrelid = schema_table_
AND attname = old_name_
AND NOT attisdropped) THEN
EXECUTE format ('ALTER TABLE %s RENAME %s TO %s', schema_table_, old_name_, new_name);
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$function$;
This seems to function correctly except.
If I am upgrading a site from Postgres 9.6 to version 11.3 and the column has already been renamed in the 9.6 branch
theabove existence test fails
and an exception is generated.
It all seems to work correctly if I repeat this same patch in the 11.3 branch.
The function definition is the same for both branches.
I suspect I am overlooking some fundamental issue here.
Anyone with a thought.
Thanks
Dave Day
On 8/20/19 12:07 PM, Day, David wrote:
> I have a function used by my patch files which attempts to determine if the old column name exists, if so it
proceedswith the rename.
>
>
> CREATE OR REPLACE FUNCTION sys.rename_column(schema_table_ regclass, old_name_ text, new_name text)
> RETURNS boolean
> LANGUAGE plpgsql
> AS $function$
> BEGIN
>
> IF EXISTS(SELECT 1 FROM pg_attribute
> WHERE attrelid = schema_table_
> AND attname = old_name_
> AND NOT attisdropped) THEN
> EXECUTE format ('ALTER TABLE %s RENAME %s TO %s', schema_table_, old_name_, new_name);
> RETURN TRUE;
> ELSE
> RETURN FALSE;
> END IF;
>
> END;
> $function$;
>
>
> This seems to function correctly except.
>
> If I am upgrading a site from Postgres 9.6 to version 11.3 and the column has already been renamed in the 9.6 branch
theabove existence test fails
> and an exception is generated.
Not clear to me when in process the function is run?
What is the exception generated?
>
> It all seems to work correctly if I repeat this same patch in the 11.3 branch.
>
> The function definition is the same for both branches.
>
> I suspect I am overlooking some fundamental issue here.
> Anyone with a thought.
>
>
> Thanks
>
>
> Dave Day
>
>
>
>
>
>
>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Tue, Aug 20, 2019 at 9:07 PM Day, David <david.day@redcom.com> wrote:
> EXECUTE format ('ALTER TABLE %s RENAME %s TO %s', schema_table_, old_name_, new_name);
Hard to say without the error, but any chance there is a quoting problem?
EXECUTE format ('ALTER TABLE %s RENAME %I TO %I', schema_table_,
old_name_, new_name);
Luca
Thanks for the feedback.
The error is something like column already exists and
Quoting issue is Unlikely, and as I mentioned it works as expected if Im repeating the patch in 11.3
And I assume would if I was in 9.6.
I suspect it has something to do with the content, or lack of, in the pg_attribute table following an upgrade.
-----Original Message-----
From: Luca Ferrari [mailto:fluca1978@gmail.com]
Sent: Tuesday, August 20, 2019 3:41 PM
To: Day, David <david.day@redcom.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Rename a column if not already renamed.?
On Tue, Aug 20, 2019 at 9:07 PM Day, David <david.day@redcom.com> wrote:
> EXECUTE format ('ALTER TABLE %s RENAME %s TO %s', schema_table_,
> old_name_, new_name);
Hard to say without the error, but any chance there is a quoting problem?
EXECUTE format ('ALTER TABLE %s RENAME %I TO %I', schema_table_, old_name_, new_name);
Luca
On 8/20/19 12:50 PM, Day, David wrote:
> Thanks for the feedback.
>
> The error is something like column already exists and
> Quoting issue is Unlikely, and as I mentioned it works as expected if Im repeating the patch in 11
The above and below us are the parts I am not understanding.
Can you provide us a step-by-step(workflow) for what you are doing?
> And I assume would if I was in 9.6.
>
> I suspect it has something to do with the content, or lack of, in the pg_attribute table following an upgrade.
>
> -----Original Message-----
> From: Luca Ferrari [mailto:fluca1978@gmail.com]
> Sent: Tuesday, August 20, 2019 3:41 PM
> To: Day, David <david.day@redcom.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: Rename a column if not already renamed.?
>
> On Tue, Aug 20, 2019 at 9:07 PM Day, David <david.day@redcom.com> wrote:
>> EXECUTE format ('ALTER TABLE %s RENAME %s TO %s', schema_table_,
>> old_name_, new_name);
>
> Hard to say without the error, but any chance there is a quoting problem?
> EXECUTE format ('ALTER TABLE %s RENAME %I TO %I', schema_table_, old_name_, new_name);
>
> Luca
>
--
Adrian Klaver
adrian.klaver@aklaver.com
"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
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
tome.
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.
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.?
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
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 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 andnew column names exist. > Because that sure sounds like what is happening. > > regards, tom lane > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
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
On 8/21/19 11:58 AM, Day, David wrote: > 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 timethe patch attempts to rename it. > Why both is a big question. I been playing around with that and the only way I can see it happening is that both columns actually exist in the table. This leads to: 1) Examine the output of the pg_dump file to see if the table comes over with both columns. 2) Examine the table in the 11.3 instance to see if has both columns. An indication to me that patch process is adding back the old column. 3) If neither 1) or 2) examine the patch process to see if it has both columns in the table at some point in time and then removes the old column. > However, > It is easy enough to re-write the column rename function to deal with the simultaneous possibility. I will include theredefined function in the merge patch and see how it goes. > > I'll update the thread after some further exploration. > > Thanks all for your assistance. > > -- Adrian Klaver adrian.klaver@aklaver.com
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