pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to conveniently upgrade with specific catalog change
7+ messages / 4 participants
[nested] [flat]
* how to conveniently upgrade with specific catalog change
@ 2020-07-09 09:55 ZongtianHou <zongtianhou@icloud.com>
0 siblings, 2 replies; 7+ messages in thread
From: ZongtianHou @ 2020-07-09 09:55 UTC (permalink / raw)
To: pgsql-admin
Hi, all
I am doing some development based on postgres(8.x.x) and encountered a problem when doing upgrade. Normally, there are two ways for me to do upgrade.
1. when new version without catalog change, I can replace the binary and restart the cluster.
2. when new version with catalog change like add a column or a row to a system table, I can replace the binary and turn allow_system_table_mods to all, do all the changes, then restart.
But, when new version add a new built-in datatype, like json, which will insert a row in pg_type with specific oid, I can not find a simple way to do this. I look up the initdb code, it run the postgres program in bootstrap mode, feed it data from postgres.bki file which support oid specification. Simplify the question, How can I do upgrade conveniently if I just need to insert one row in one system table with specific oid? Can I do it in upgrade mode somehow or I need to run some upgrade program?
Best Regards,
Zongtian Hou
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how to conveniently upgrade with specific catalog change
@ 2020-07-09 10:31 ZongtianHou <zongtianhou@icloud.com>
parent: ZongtianHou <zongtianhou@icloud.com>
1 sibling, 0 replies; 7+ messages in thread
From: ZongtianHou @ 2020-07-09 10:31 UTC (permalink / raw)
To: Marlene Villanueva <villanuevamarlene906@gmail.com>; +Cc: pgsql-admin
Sorry, I can not receive the SMS passcode in china, can you remove the passcode requirement?
> On Jul 9, 2020, at 5:58 PM, Marlene Villanueva <villanuevamarlene906@gmail.com> wrote:
>
> Marlene Villanueva has sent you an email via Gmail confidential mode:
>
> Re: how to conveniently upgrade with specific catalog change <https://confidential-mail.google.com/msg/AA12eCgj5WKFOPhxHAI5aoy2TjPfQs-BRNny2gPccrQmkTlvrqKeaqx0fsA...;
> This message was sent on Jul 9, 2020 at 2:58:55 AM PDT
> You can open it by clicking the link below. This link will only work for zongtianhou@icloud.com.
>
> View the email <https://confidential-mail.google.com/msg/AA12eCgj5WKFOPhxHAI5aoy2TjPfQs-BRNny2gPccrQmkTlvrqKeaqx0fsA...;
>
> Gmail confidential mode gives you more control over the messages you send. The sender may have chosen to set an expiration time, disable printing or forwarding, or track access to this message. Learn more <https://support.google.com/mail/answer/7674059;
> Gmail: Email by Google
> Use is subject to the Google Privacy Policy <https://myaccount.google.com/privacypolicy?hl=en;
> Google LLC, 1600 Amphitheatre Parkway, Mountain View, CA 94043, USA
> You have received this message because someone sent you an email via Gmail confidential mode.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how to conveniently upgrade with specific catalog change
@ 2020-07-09 14:35 Laurenz Albe <laurenz.albe@cybertec.at>
parent: ZongtianHou <zongtianhou@icloud.com>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2020-07-09 14:35 UTC (permalink / raw)
To: ZongtianHou <zongtianhou@icloud.com>; pgsql-admin
On Thu, 2020-07-09 at 17:55 +0800, ZongtianHou wrote:
> I am doing some development based on postgres(8.x.x) and encountered a problem when doing upgrade. Normally, there are two ways for me to do upgrade.
> 1. when new version without catalog change, I can replace the binary and restart the cluster.
> 2. when new version with catalog change like add a column or a row to a system table, I can replace
> the binary and turn allow_system_table_mods to all, do all the changes, then restart.
>
> But, when new version add a new built-in datatype, like json, which will insert a row in pg_type with specific oid,
> I can not find a simple way to do this. I look up the initdb code, it run the postgres program in bootstrap mode,
> feed it data from postgres.bki file which support oid specification. Simplify the question, How can I do upgrade
> conveniently if I just need to insert one row in one system table with specific oid? Can I do it in upgrade mode
> somehow or I need to run some upgrade program?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how to conveniently upgrade with specific catalog change
@ 2020-07-29 09:42 ZongtianHou <zongtianhou@icloud.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 7+ messages in thread
From: ZongtianHou @ 2020-07-29 09:42 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin
Thank you for this info
> On Jul 9, 2020, at 10:35 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Thu, 2020-07-09 at 17:55 +0800, ZongtianHou wrote:
>> I am doing some development based on postgres(8.x.x) and encountered a problem when doing upgrade. Normally, there are two ways for me to do upgrade.
>> 1. when new version without catalog change, I can replace the binary and restart the cluster.
>> 2. when new version with catalog change like add a column or a row to a system table, I can replace
>> the binary and turn allow_system_table_mods to all, do all the changes, then restart.
>>
>> But, when new version add a new built-in datatype, like json, which will insert a row in pg_type with specific oid,
>> I can not find a simple way to do this. I look up the initdb code, it run the postgres program in bootstrap mode,
>> feed it data from postgres.bki file which support oid specification. Simplify the question, How can I do upgrade
>> conveniently if I just need to insert one row in one system table with specific oid? Can I do it in upgrade mode
>> somehow or I need to run some upgrade program?
>
> From 8.4 on, you can use pg_upgrade to upgrade.
>
> Otherwise, you need pg_dumpall and restore.
>
> Just installing the new software only works for upgrades between 8.4.x and 8.4.y
> or 12.x and 12.y.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Function is giving execution error from inside the procedure
@ 2024-02-02 15:04 M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 7+ messages in thread
From: M Sarwar @ 2024-02-02 15:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi,
I am trying to create a simple procedure to updata a table.
CREATE OR REPLACE PROCEDURE bronxdev.pr_asn_wafer_id_update(
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
begin
UPDATE BRONXDEV.ASN_MANUFACTURER_PROFILE
SET WAFER_ID = BRIM(SERIAL_ID, '/')
;
commit;
--rollback;
END
$BODY$;
It is getting compiled without any issue but when I am trying to execute, I am getting BRIM function does not exist error message.
I have tried to google and few other resources and did not help.
Error: ERROR: function brim(character varying, unknown) does not exist LINE 2: SET WAFER_ID = BRIM(SERIAL_ID, '/')
The same UPDATE is directly working from the pgadmin.
I have tried to change LANGUAGE from plpgsql to SQL and that did not help.
My postgres version is 13.2.
Thanks,
Sarwar
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function is giving execution error from inside the procedure
@ 2024-02-02 15:30 David G. Johnston <david.g.johnston@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-02-02 15:30 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Fri, Feb 2, 2024 at 8:04 AM M Sarwar <sarwarmd02@outlook.com> wrote:
>
>
> Error: ERROR: function brim(character varying, unknown) does not exist
> LINE 2: SET WAFER_ID = BRIM(SERIAL_ID, '/')
>
>
I suspect you wanted the btrim function, not the brim function.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function is giving execution error from inside the procedure
@ 2024-02-02 15:53 M Sarwar <sarwarmd02@outlook.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: M Sarwar @ 2024-02-02 15:53 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi David,
You are right.
That is confirming that I messed up the characters in the code.
Thank you,
Sarwar
________________________________
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, February 2, 2024 10:30 AM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Function is giving execution error from inside the procedure
On Fri, Feb 2, 2024 at 8:04 AM M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.com>> wrote:
Error: ERROR: function brim(character varying, unknown) does not exist LINE 2: SET WAFER_ID = BRIM(SERIAL_ID, '/')
I suspect you wanted the btrim function, not the brim function.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-02-02 15:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-09 09:55 how to conveniently upgrade with specific catalog change ZongtianHou <zongtianhou@icloud.com>
2020-07-09 10:31 ` ZongtianHou <zongtianhou@icloud.com>
2020-07-09 14:35 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-07-29 09:42 ` ZongtianHou <zongtianhou@icloud.com>
2024-02-02 15:04 Function is giving execution error from inside the procedure M Sarwar <sarwarmd02@outlook.com>
2024-02-02 15:30 ` Re: Function is giving execution error from inside the procedure David G. Johnston <david.g.johnston@gmail.com>
2024-02-02 15:53 ` Re: Function is giving execution error from inside the procedure M Sarwar <sarwarmd02@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox