public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 2/3] Allow composite types in bootstrap
5+ messages / 4 participants
[nested] [flat]
* [PATCH 2/3] Allow composite types in bootstrap
@ 2020-11-17 15:28 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Justin Pryzby @ 2020-11-17 15:28 UTC (permalink / raw)
---
src/backend/bootstrap/bootstrap.c | 28 ++++++++++++++++++++++++++++
1 file changed, 28 insertions(+)
diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c
index 18eb62ca47..e4fc75ab84 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -916,6 +916,7 @@ gettype(char *type)
{
if (Typ != NIL)
{
+ static bool did_reread PG_USED_FOR_ASSERTS_ONLY = false; /* Already reread pg_types */
ListCell *lc;
foreach (lc, Typ)
@@ -927,6 +928,33 @@ gettype(char *type)
return app->am_oid;
}
}
+
+ /*
+ * The type wasn't known; check again to handle composite
+ * types, added since first populating the array.
+ */
+
+ /*
+ * Once all the types are populated and we handled composite
+ * types, shouldn't need to do that again.
+ */
+ Assert(!did_reread);
+ did_reread = true;
+
+ list_free_deep(Typ);
+ Typ = NULL;
+ populate_typ_array();
+
+ /* Need to avoid infinite recursion... */
+ foreach (lc, Typ)
+ {
+ struct typmap *app = lfirst(lc);
+ if (strncmp(NameStr(app->am_typ.typname), type, NAMEDATALEN) == 0)
+ {
+ Ap = app;
+ return app->am_oid;
+ }
+ }
}
else
{
--
2.26.2
--------------EED78D4A67CFF327F6B880D2
Content-Type: text/x-patch; charset=UTF-8;
name="0003-Extended-statistics-on-expressions-20210122b.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0003-Extended-statistics-on-expressions-20210122b.patch"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Horribly slow pg_upgrade performance with many Large Objects
@ 2025-04-07 21:25 Tom Lane <[email protected]>
2025-04-08 04:52 ` Re: Horribly slow pg_upgrade performance with many Large Objects Michael Paquier <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2025-04-07 21:25 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hannu Krosing <[email protected]> writes:
> I have now met a not insignificant number of cases where pg_upgrade
> performance is really bad when the database has a large number of
> Large Objects.
What version are you testing? We did some work in that area in the
v17 cycle (a45c78e32).
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Horribly slow pg_upgrade performance with many Large Objects
2025-04-07 21:25 Re: Horribly slow pg_upgrade performance with many Large Objects Tom Lane <[email protected]>
@ 2025-04-08 04:52 ` Michael Paquier <[email protected]>
2025-04-08 07:15 ` Re: Horribly slow pg_upgrade performance with many Large Objects Hannu Krosing <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Paquier @ 2025-04-08 04:52 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Apr 07, 2025 at 05:25:32PM -0400, Tom Lane wrote:
> What version are you testing? We did some work in that area in the
> v17 cycle (a45c78e32).
I am puzzled by the target version used here, as well. If there is
more that can be improved, v19 would be the version to consider for
future improvements at this stage.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Horribly slow pg_upgrade performance with many Large Objects
2025-04-07 21:25 Re: Horribly slow pg_upgrade performance with many Large Objects Tom Lane <[email protected]>
2025-04-08 04:52 ` Re: Horribly slow pg_upgrade performance with many Large Objects Michael Paquier <[email protected]>
@ 2025-04-08 07:15 ` Hannu Krosing <[email protected]>
2025-04-08 07:26 ` Re: Horribly slow pg_upgrade performance with many Large Objects Hannu Krosing <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Hannu Krosing @ 2025-04-08 07:15 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; PostgreSQL Hackers <[email protected]>; Nathan Bossart <[email protected]>
I was testing on version 17
On Tue, Apr 8, 2025 at 6:52 AM Michael Paquier <[email protected]> wrote:
>
> On Mon, Apr 07, 2025 at 05:25:32PM -0400, Tom Lane wrote:
> > What version are you testing? We did some work in that area in the
> > v17 cycle (a45c78e32).
>
> I am puzzled by the target version used here, as well.
I was testing on version 17
Here is how you can easily test too (as --binary-upgrade does not dump
the actual data it is ok for the test to not put anything there)
hannuk@db01-c1a:~/work/lo-testing$ createdb -p 5433 lodb
hannuk@db01-c1a:~/work/lo-testing$ psql -p 5433 lodb
psql (17.4 (Ubuntu 17.4-1.pgdg22.04+2))
Type "help" for help.
lodb=# insert into pg_largeobject_metadata(oid, lomowner) SELECT i,
16384 FROM generate_series(1, 100_000_000) g(i);
INSERT 0 100000000
Time: 162414.216 ms (02:42.414)
lodb=#
\q
hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --data-only -t
pg_largeobject_metadata -p 5433 lodb | gzip >
pg_largeobject_metadata.data.gz
real 0m22.094s
user 0m20.741s
sys 0m2.085s
hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --data-only -t
pg_largeobject_metadata --format=custom -p 5433 lodb -f
pg_largeobject_metadata.dump
real 0m20.226s
user 0m18.068s
sys 0m0.824s
> If there is
> more that can be improved, v19 would be the version to consider for
> future improvements at this stage.
If the internal format has changed in 16 the correct way would be to
go through the data-only dump of pg_largeobject_metadata in all cases.
Even for the 100M case where you get the restore in 2 minutes instead
of 100 minutes
hannuk@db01-c1a:~/work/lo-testing$ createdb -p 5434 lodb
hannuk@db01-c1a:~/work/lo-testing$ time pg_restore -p 5434
--exit-on-error --transaction-size=1000 --dbname lodb
pg_largeobject_metadata.dump
real 2m2.277s
user 0m2.594s
sys 0m0.549s
And even in case of the user-visible format change in acl format it is
most likely that changing the visible format using some regexp magic,
or even a dedicated function, would still me much faster than creating
all the LOs though creation commands.
------
The commands I used to do the pg_upgrade-like test were
hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --schema-only
--quote-all-identifiers --binary-upgrade --format=custom
--file=lodb100m.dump -p 5433 lodb
real 1m58.241s
user 0m35.229s
sys 0m17.854s
hannuk@db01-c1a:~/work/lo-testing$ time pg_restore -p 5434
--exit-on-error --transaction-size=1000 --dbname lodb lodb100m.dump
real 100m54.878s
user 3m23.885s
sys 20m33.761s
(I left out the --verbose part that pg_upgrade also sets as I did not
want to get 100M lines of "large object created " messages )
also the postgres server at -p 5434 needs to be started with -b flag
to accept the loading a dump from --binary-upgrade. In Debian/Ubuntu
this can be directly passed to pg_ctlcluster as follows
sudo pg_ctlcluster 17 target -o -b
----
Hannu
> --
> Michael
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Horribly slow pg_upgrade performance with many Large Objects
2025-04-07 21:25 Re: Horribly slow pg_upgrade performance with many Large Objects Tom Lane <[email protected]>
2025-04-08 04:52 ` Re: Horribly slow pg_upgrade performance with many Large Objects Michael Paquier <[email protected]>
2025-04-08 07:15 ` Re: Horribly slow pg_upgrade performance with many Large Objects Hannu Krosing <[email protected]>
@ 2025-04-08 07:26 ` Hannu Krosing <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Hannu Krosing @ 2025-04-08 07:26 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; PostgreSQL Hackers <[email protected]>; Nathan Bossart <[email protected]>
Looked like a bit illogical order on re-reading it so I want to make
clear that the pg_upgrade-like test showing 100min for 100 million LOs
is at the end of last message and the proposed solution is at the
beginning
On Tue, Apr 8, 2025 at 9:15 AM Hannu Krosing <[email protected]> wrote:
>
> I was testing on version 17
>
>
> On Tue, Apr 8, 2025 at 6:52 AM Michael Paquier <[email protected]> wrote:
> >
> > On Mon, Apr 07, 2025 at 05:25:32PM -0400, Tom Lane wrote:
> > > What version are you testing? We did some work in that area in the
> > > v17 cycle (a45c78e32).
> >
> > I am puzzled by the target version used here, as well.
>
> I was testing on version 17
>
> Here is how you can easily test too (as --binary-upgrade does not dump
> the actual data it is ok for the test to not put anything there)
>
> hannuk@db01-c1a:~/work/lo-testing$ createdb -p 5433 lodb
> hannuk@db01-c1a:~/work/lo-testing$ psql -p 5433 lodb
> psql (17.4 (Ubuntu 17.4-1.pgdg22.04+2))
> Type "help" for help.
>
> lodb=# insert into pg_largeobject_metadata(oid, lomowner) SELECT i,
> 16384 FROM generate_series(1, 100_000_000) g(i);
> INSERT 0 100000000
> Time: 162414.216 ms (02:42.414)
> lodb=#
> \q
>
> hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --data-only -t
> pg_largeobject_metadata -p 5433 lodb | gzip >
> pg_largeobject_metadata.data.gz
> real 0m22.094s
> user 0m20.741s
> sys 0m2.085s
>
> hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --data-only -t
> pg_largeobject_metadata --format=custom -p 5433 lodb -f
> pg_largeobject_metadata.dump
> real 0m20.226s
> user 0m18.068s
> sys 0m0.824s
>
> > If there is
> > more that can be improved, v19 would be the version to consider for
> > future improvements at this stage.
>
> If the internal format has changed in 16 the correct way would be to
> go through the data-only dump of pg_largeobject_metadata in all cases.
> Even for the 100M case where you get the restore in 2 minutes instead
> of 100 minutes
>
> hannuk@db01-c1a:~/work/lo-testing$ createdb -p 5434 lodb
> hannuk@db01-c1a:~/work/lo-testing$ time pg_restore -p 5434
> --exit-on-error --transaction-size=1000 --dbname lodb
> pg_largeobject_metadata.dump
>
> real 2m2.277s
> user 0m2.594s
> sys 0m0.549s
>
> And even in case of the user-visible format change in acl format it is
> most likely that changing the visible format using some regexp magic,
> or even a dedicated function, would still me much faster than creating
> all the LOs though creation commands.
>
> ------
> The commands I used to do the pg_upgrade-like test were
>
> hannuk@db01-c1a:~/work/lo-testing$ time pg_dump --schema-only
> --quote-all-identifiers --binary-upgrade --format=custom
> --file=lodb100m.dump -p 5433 lodb
> real 1m58.241s
> user 0m35.229s
> sys 0m17.854s
>
> hannuk@db01-c1a:~/work/lo-testing$ time pg_restore -p 5434
> --exit-on-error --transaction-size=1000 --dbname lodb lodb100m.dump
> real 100m54.878s
> user 3m23.885s
> sys 20m33.761s
>
> (I left out the --verbose part that pg_upgrade also sets as I did not
> want to get 100M lines of "large object created " messages )
>
> also the postgres server at -p 5434 needs to be started with -b flag
> to accept the loading a dump from --binary-upgrade. In Debian/Ubuntu
> this can be directly passed to pg_ctlcluster as follows
>
> sudo pg_ctlcluster 17 target -o -b
>
> ----
> Hannu
>
>
>
>
> > --
> > Michael
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-04-08 07:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-17 15:28 [PATCH 2/3] Allow composite types in bootstrap Justin Pryzby <[email protected]>
2025-04-07 21:25 Re: Horribly slow pg_upgrade performance with many Large Objects Tom Lane <[email protected]>
2025-04-08 04:52 ` Re: Horribly slow pg_upgrade performance with many Large Objects Michael Paquier <[email protected]>
2025-04-08 07:15 ` Re: Horribly slow pg_upgrade performance with many Large Objects Hannu Krosing <[email protected]>
2025-04-08 07:26 ` Re: Horribly slow pg_upgrade performance with many Large Objects Hannu Krosing <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox