uncommitted xmin 3100586 from before xid cutoff 10339367 needs to be frozen
Hi All, We recently started seeing an error “ERROR: uncommitted xmin 3100586 from before xid cutoff 10339367 needs to be frozen” on our user tables. I’m unable to do ‘vacuum’, ‘vacuum freeze’ or ‘vacuum full’ on Postgres 14.4 running on a windows environment. Error: first come this ERROR: uncommitted xmin 3100586 from before xid cutoff 10339367 needs to be frozen CONTEXT: while scanning block 1403 offset 8 of relation "pg_catalog.pg_attribute" Thanks, Daulat
Re: Not able to purge partition
Can someone please confirm if this behavior of foreign key is expected with the partition created through partman extension and thus we need to have our manual process written for partition purge (in order of child first and then parent) , rather relying on partman partition maintenance to take care drop partition automatically for us? On Fri, 22 Mar, 2024, 12:42 am veem v, wrote: > On Thu, 21 Mar 2024 at 23:39, Laurenz Albe > wrote: > >> On Thu, 2024-03-21 at 22:50 +0530, veem v wrote: >> > So when you mentioned "to create the foreign keys *not* between the >> > partitioned table but between the individual partitions" , can that >> > be done using the same "partman.create_parent" procedure and automated >> > cron job schedule or has to be done any other way manually ? >> >> I don't know the capabilities of partmen, but I would be surprised if >> it could automatically create foreign keys on the partitions. >> >> > Yes, the constraints on each of the child partitions and parent partitions > were getting created automatically. As I see from > information_schema.table_constraints, it shows one foreign key constraint > in each of the child partitions created through the partman procedure. > > It works smoothly without any issue, until we noticed this issue while > trying to purge the partition from the parent table partition. But I > believe this extension is extensively used , so I'm just wondering if I am > missing something here with regards to foreign key creation using this > automated partition creation/partman extension functionality. > > >
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
> On 21 Mar 2024, at 14:27, Alvaro Herrera wrote: > > On 2024-Mar-21, Daniel Gustafsson wrote: > >> On 21 Mar 2024, at 13:28, Alvaro Herrera wrote: >> >>> I very much doubt that they realized that comments were going to be >>> omitted. But clearly it's just a mistake, and easily fixed. >> >> It sure looks like a search/replace kind of bug. I had just typed up the >> exact >> same patch with the addition of a comment on why pg_authid is used and was >> about to hit send when your email came =) Are you committing it or do you >> want >> me to take care of it? > > Hah :-) Please do. Done, backpatched all the way since it's been broken since 2017. -- Daniel Gustafsson
Re: Not able to purge partition
On Thu, 21 Mar 2024 at 23:39, Laurenz Albe wrote: > On Thu, 2024-03-21 at 22:50 +0530, veem v wrote: > > So when you mentioned "to create the foreign keys *not* between the > > partitioned table but between the individual partitions" , can that > > be done using the same "partman.create_parent" procedure and automated > > cron job schedule or has to be done any other way manually ? > > I don't know the capabilities of partmen, but I would be surprised if > it could automatically create foreign keys on the partitions. > > Yes, the constraints on each of the child partitions and parent partitions were getting created automatically. As I see from information_schema.table_constraints, it shows one foreign key constraint in each of the child partitions created through the partman procedure. It works smoothly without any issue, until we noticed this issue while trying to purge the partition from the parent table partition. But I believe this extension is extensively used , so I'm just wondering if I am missing something here with regards to foreign key creation using this automated partition creation/partman extension functionality.
Re: Not able to purge partition
On Thu, 2024-03-21 at 22:50 +0530, veem v wrote: > So when you mentioned "to create the foreign keys *not* between the > partitioned table but between the individual partitions" , can that > be done using the same "partman.create_parent" procedure and automated > cron job schedule or has to be done any other way manually ? I don't know the capabilities of partmen, but I would be surprised if it could automatically create foreign keys on the partitions. Yours, Laurenz Albe
Re: Not able to purge partition
On Thu, 21 Mar, 2024, 6:18 pm Laurenz Albe,
wrote:
What you *can* do is detach the partition and then drop it, but detatching
will
be slow because PostgreSQL has to check for referencing rows.
The best solution is to create the foreign key *not* between the partitioned
tables, but between the individual table partitions. That should be easy if
you have the same partition boundaries for both.
Then you can simply drop a partition from both tables at the same time.
Thank you Laurenz.
With the existing foreign key setup, the drop partitions works fine for the
child tables when they are triggered through the cron job by calling
"run_maintenance_proc". But its failing for parent table partition drop.
And as you mentioned if we do the detach and then drop it works fine for
the parent table.
However, we are creating partitions using the create_parent procedure
something as below ,and thus it creates the default template table based on
the main table structure informations and also the subsequent partitions
are created/dropped accordingly using the scheduled cron as below.
So when you mentioned "*to create the foreign keys *not* between the *
*partitioned** table but between the individual partitions*" , can that be
done using the same "partman.create_parent" procedure and automated cron
job schedule or has to be done any other way manually ?
Additionally , do we have to give call to the cron job in orderly fashion,
i.e child first and then parent table? As because, currently we were just ,
scheduling "run_maintenance_proc" once and all the partition tables
maintenance(i.e. creation of new partition and dropping of old partitions)
was being taken care automatically by that one call.
select partman.create_parent(
p_parent_table := 'schema1.test_part_drop_child',
p_control := 'c2_part_date',
p_type := 'native',
p_interval := '1 day',
p_premake := 5,
p_start_partition => '2024-02-01 00:00:00'
);
update partman.part_config set infinite_time_partitions = 'true' ,
retention = '1 months', retention_keep_table='true',
retention_keep_index='true',retention_schema='public'
where parent_table = 'schema1.test_part_drop_child';
SELECT cron.schedule('@hourly',
);
Re: Question about PostgreSQL upgrade from version 12 to version 15
On Thu, Mar 21, 2024 at 7:48 AM Alvaro Herrera wrote: > On 2024-Mar-21, Joseph Kennedy wrote: > > I'm planning to upgrade my PostgreSQL database from version 12 to > > version 15 using pg_upgrade. After completing the upgrade process, I'm > > curious to know whether it's necessary to reindex the database. > > > > Could anyone please clarify whether reindexing is required after > > completing the upgrade process from PostgreSQL version 12 to version > > 15 using pg_upgrade? > > A reindex(*) is necessary for indexes on textual columns(**), and only > if you're also changing the underlying OS version(***) such that the > collation behavior changes. If you're keeping the database on the same > OS version, there's no need to reindex anything. > > (*) More than reindex actually: you may need to refresh materialized > views and consider carefully any partition bounds you may have, if you > have any partition keys that include textual columns. Even worse: if > you have FDWs on a Postgres server that queries a table from another > Postgres server with different collation libraries, it could bit you > there too. > > (**) textual column in this case means anything that is affected by > collation changes; typically that's things like varchar, text, citext, > etc, for which a collation other than "C" is explicit or implied. You > don't need to do anything for indexes on numbers, dates, geometries, > etc, nor for textual columns where the index is defined with the C > collation. > > (***) the underlying C library changes collation rules rather frequently > (especially glibc), because the developers of said libraries don't > consider that this has any important, permanent impact (but it does > impact indexes for Postgres). Most such changes are innocuous, but from > time to time they make changes that wreak havoc. If you're using ICU > collations with your Postgres 12 databases, you may also be affected if > you upgrade from one ICU version to another. > > > Joe Conway gave a very good presentation on this topic recently: > https://www.postgresql.eu/events/fosdem2024/schedule/session/5127-collation-challenges-sorting-it-out/ > As a bonus, if you do decide to reindex, you'll also benefit from the index deduplication work that was introduced in v13, which should help reduce disk space and make queries a little faster. Robert Treat https://xzilla.net
Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function
On 3/20/24 17:13, Adrian Klaver wrote: On 3/20/24 15:52, Jeff Ross wrote: On 3/20/24 16:25, Adrian Klaver wrote: On 3/20/24 15:18, Jeff Ross wrote: Greetings! I built a trigger fired process that copies an "order" from our production database to our dev database. An order, in this case, is an initial row from a table and all of the rows in all of the tables in that database/schema that are needed to satisfy all of the foreign key constraints for the original insert. Through a web page, one of our folks can select a schema and an order id to copy. That information is then inserted into a table. A trigger attached to that table takes care of copying the necessary rows using a function that uses both plython3u and psycopg2. I can supply the source code if that will help. I think that will help, especially the interaction between psycopg2 and plpython3u. As requested: https://openvistas.net/copy_orders_to_dev.html 1) I have not gone through this thoroughly enough to figure out what is going on. 2) Things I have noticed, may not be relevant. a) from psycopg2 import sql Never used. I have a base template that I use for complex functions--this is a part of that. Never caused a problem before. b) #prod_database_connection.set_session(autocommit=True) #dev_database_connection.set_session(autocommit=True) Why are they commented out? If autocommit is on, the following fails: NOTICE: 217, create temp table if not exists load_temp (like wholesale.load including all) on commit drop; truncate load_temp; ERROR: psycopg2.errors.UndefinedTable: relation "load_temp" does not exist c) prod_database_connection_string = "host='pgbouncer' dbname='%s' application_name = '%s'" dev_database_connection_string = "host='pgbouncer' dbname='%s' application_name = '%s'" What version of PgBouncer? [rossj@cron ~]$ pgbouncer -V PgBouncer 1.21.0 libevent 2.0.21-stable adns: evdns2 tls: OpenSSL 1.0.2k-fips 26 Jan 2017 How is it setup? session mode? d) Why predefine all those cursors()? Why not? Sort of part of the template and the psycopg2 docs say that cursors are cheap. e) Why is database global? I think that's also a holdover from a previous function that I imported into this. Thanks for the reply--gave me a good chance to do a little cleanup. The error is happening when psycopg2 is trying to commit so I'll also ask there. Jeff
Re: Not able to purge partition
- Mail original - > De: "Laurenz Albe" > À: "veem v" , "pgsql-general" > > Envoyé: Jeudi 21 Mars 2024 13:48:19 > Objet: Re: Not able to purge partition > On Thu, 2024-03-21 at 11:07 +0530, veem v wrote: >> CREATE TABLE schema1.test_part_drop_parent >> ( >> c1 varchar(36) NOT NULL , >> c2_part_date timestamp with time zone NOT NULL , >> c3 numeric(12,0) NOT NULL , >> CONSTRAINT test_part_drop_parent_PK PRIMARY KEY (c1,c2_part_date) >> ) PARTITION BY RANGE (c2_part_date); >> >> CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3 ASC); >> >> >> CREATE TABLE schema1.test_part_drop_child >> ( >> C1_child varchar(36) NOT NULL , >> C1 varchar(36) NOT NULL , >> c2_part_date timestamp with time zone NOT NULL , >> C3 numeric(12,0) NOT NULL , >> CONSTRAINT test_part_drop_child_PK PRIMARY KEY (C1_child,c2_part_date), >> CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date) REFERENCES >> schema1.test_part_drop_parent(c1,c2_part_date) >> ) PARTITION BY RANGE (c2_part_date); >> >> CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1, >> c2_part_date); >> >> CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child( >> c1_child, >> c2_part_date); >> >> [create some partitions, then drop a partition of the referenced table] >> > >> SQL Error [P0001]: ERROR: cannot drop table >> schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it >> CONTEXT: SQL statement "DROP TABLE schema1.test_part_drop_parent_p2024_02_01" > > That's normal. If you create a foreign key constraint to a partitioned table, > you > can no longer drop a partition of the referenced table. > > What you *can* do is detach the partition and then drop it, but detatching > will > be slow because PostgreSQL has to check for referencing rows. > > The best solution is to create the foreign key *not* between the partitioned > tables, but between the individual table partitions. That should be easy if > you have the same partition boundaries for both. > Then you can simply drop a partition from both tables at the same time. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com Also, as an aside, test_part_drop_child_idx index is redundant with test_part_drop_child_pk index created for the primary key Regards Gilles
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
On 2024-Mar-21, Daniel Gustafsson wrote: > On 21 Mar 2024, at 13:28, Alvaro Herrera wrote: > > > I very much doubt that they realized that comments were going to be > > omitted. But clearly it's just a mistake, and easily fixed. > > It sure looks like a search/replace kind of bug. I had just typed up the > exact > same patch with the addition of a comment on why pg_authid is used and was > about to hit send when your email came =) Are you committing it or do you want > me to take care of it? Hah :-) Please do. -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/ "Here's a general engineering tip: if the non-fun part is too complex for you to figure out, that might indicate the fun part is too ambitious." (John Naylor) https://postgr.es/m/CAFBsxsG4OWHBbSDM%3DsSeXrQGOtkPiOEOuME4yD7Ce41NtaAD9g%40mail.gmail.com
Re: Not able to purge partition
On Thu, 2024-03-21 at 11:07 +0530, veem v wrote: > CREATE TABLE schema1.test_part_drop_parent > ( > c1 varchar(36) NOT NULL , > c2_part_date timestamp with time zone NOT NULL , > c3 numeric(12,0) NOT NULL , > CONSTRAINT test_part_drop_parent_PK PRIMARY KEY (c1,c2_part_date) > ) PARTITION BY RANGE (c2_part_date); > > CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3 ASC); > > > CREATE TABLE schema1.test_part_drop_child > ( > C1_child varchar(36) NOT NULL , > C1 varchar(36) NOT NULL , > c2_part_date timestamp with time zone NOT NULL , > C3 numeric(12,0) NOT NULL , > CONSTRAINT test_part_drop_child_PK PRIMARY KEY (C1_child,c2_part_date), > CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date) REFERENCES > schema1.test_part_drop_parent(c1,c2_part_date) > ) PARTITION BY RANGE (c2_part_date); > > CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1, > c2_part_date); > > CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child( > c1_child, c2_part_date); > > [create some partitions, then drop a partition of the referenced table] > > SQL Error [P0001]: ERROR: cannot drop table > schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it > CONTEXT: SQL statement "DROP TABLE schema1.test_part_drop_parent_p2024_02_01" That's normal. If you create a foreign key constraint to a partitioned table, you can no longer drop a partition of the referenced table. What you *can* do is detach the partition and then drop it, but detatching will be slow because PostgreSQL has to check for referencing rows. The best solution is to create the foreign key *not* between the partitioned tables, but between the individual table partitions. That should be easy if you have the same partition boundaries for both. Then you can simply drop a partition from both tables at the same time. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
> On 21 Mar 2024, at 13:28, Alvaro Herrera wrote: > > On 2024-Mar-21, Daniel Gustafsson wrote: > >> Comments on roles are stored against the pg_authid catalog relation which is >> the catalog used for dumping roles, but when using --no-role-passwords we >> instead switch to using the pg_roles catalog relation. Since comments are >> dumped for the relations which are dumped, this means that the comments on >> roles are omitted when --no-role-passwords is used. >> >> It's not clear whether that was intentional or not, I'm failing to find the >> thread where it was discussed on a quick mailing list search. > > Here it is: > https://www.postgresql.org/message-id/flat/CAEP4nAz9V4H41_4ESJd1Gf0v%3DdevkqO1%3Dpo91jUw-GJSx8Hxqg%40mail.gmail.com Aha, thanks! I too see no mention of it being intentional here. > I very much doubt that they realized that comments were going to be > omitted. But clearly it's just a mistake, and easily fixed. It sure looks like a search/replace kind of bug. I had just typed up the exact same patch with the addition of a comment on why pg_authid is used and was about to hit send when your email came =) Are you committing it or do you want me to take care of it? -- Daniel Gustafsson
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
On 2024-Mar-21, Daniel Gustafsson wrote: > Comments on roles are stored against the pg_authid catalog relation which is > the catalog used for dumping roles, but when using --no-role-passwords we > instead switch to using the pg_roles catalog relation. Since comments are > dumped for the relations which are dumped, this means that the comments on > roles are omitted when --no-role-passwords is used. > > It's not clear whether that was intentional or not, I'm failing to find the > thread where it was discussed on a quick mailing list search. Here it is: https://www.postgresql.org/message-id/flat/CAEP4nAz9V4H41_4ESJd1Gf0v%3DdevkqO1%3Dpo91jUw-GJSx8Hxqg%40mail.gmail.com I very much doubt that they realized that comments were going to be omitted. But clearly it's just a mistake, and easily fixed. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "After a quick R of TFM, all I can say is HOLY CR** THAT IS COOL! PostgreSQL was amazing when I first started using it at 7.2, and I'm continually astounded by learning new features and techniques made available by the continuing work of the development team." Berend Tober, http://archives.postgresql.org/pgsql-hackers/2007-08/msg01009.php >From baa4bd18f751cde68c5637c4cb8065cf94e92c1c Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 21 Mar 2024 13:27:37 +0100 Subject: [PATCH v1] fix dump of role comments with --no-role-passwords --- src/bin/pg_dump/pg_dumpall.c | 12 ++-- 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 491311fe79..72c30fc66d 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -776,21 +776,21 @@ dumpRoles(PGconn *conn) "rolcreaterole, rolcreatedb, " "rolcanlogin, rolconnlimit, rolpassword, " "rolvaliduntil, rolreplication, rolbypassrls, " - "pg_catalog.shobj_description(oid, '%s') as rolcomment, " + "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, " "rolname = current_user AS is_current_user " "FROM %s " "WHERE rolname !~ '^pg_' " - "ORDER BY 2", role_catalog, role_catalog); + "ORDER BY 2", role_catalog); else if (server_version >= 90500) printfPQExpBuffer(buf, "SELECT oid, rolname, rolsuper, rolinherit, " "rolcreaterole, rolcreatedb, " "rolcanlogin, rolconnlimit, rolpassword, " "rolvaliduntil, rolreplication, rolbypassrls, " - "pg_catalog.shobj_description(oid, '%s') as rolcomment, " + "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, " "rolname = current_user AS is_current_user " "FROM %s " - "ORDER BY 2", role_catalog, role_catalog); + "ORDER BY 2", role_catalog); else printfPQExpBuffer(buf, "SELECT oid, rolname, rolsuper, rolinherit, " @@ -798,10 +798,10 @@ dumpRoles(PGconn *conn) "rolcanlogin, rolconnlimit, rolpassword, " "rolvaliduntil, rolreplication, " "false as rolbypassrls, " - "pg_catalog.shobj_description(oid, '%s') as rolcomment, " + "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, " "rolname = current_user AS is_current_user " "FROM %s " - "ORDER BY 2", role_catalog, role_catalog); + "ORDER BY 2", role_catalog); res = executeQuery(conn, buf->data); -- 2.39.2
Re: Not able to purge partition
Also, i see, its not allowing to drop the parent table partition even all
the respective child partitions are dropped and no child record exists in
the child table. But this started working , when we detach parent partition
and then drop the partitions. So it means if we just change the setup of
the parent partition table in the part_config as
retention_keep_table='true', retention_keep_index='true' and
retention_schema=, it will work without giving error. And
then we have to drop the partitions from that retention_schema through
another procedure may be. Correct me if anyone has done with some other
workaround.
However , i have one question now, if we have child record exists in child
partition table, it wont even allow the detach the respective parent
partition, so is the "CALL partman.run_maintenance_proc()" automatically
ensures the ordering of child and parent table which will be passed through
the partition maintenance ? Or for that also we need to write our own
procedure and schedule through the cron?
Something as below,
CREATE PROCEDURE part_maintenance_proc()
LANGUAGE SQL
AS $$
declare
drop_partition_cursor CURSOR FOR
table_name from information_schema.tables where table_name like
'%test_part_drop_parent%' and table_schema like '%retention_schema%';
drop_partition_record RECORD;
Begin
partman.run_maintenance('cpod.test_part_drop_child');
partman.run_maintenance('cpod.test_part_drop_parent');
OPEN drop_partition_cursor
loop
FETCH NEXT FROM drop_partition_cursor INTO drop_partition_record;
EXIT WHEN NOT FOUND;
drop table drop_partition_record.table_name;
end loop;
close drop_partition_cursor;
END;
$$;
SELECT cron.schedule('@hourly',
);
On Thu, 21 Mar, 2024, 11:07 am veem v, wrote:
> Hello All,
> We created two partitioned tables one being parent and other child. Both
> are range partitioned and one table has a foreign key to the other parent
> table. Now , when we run the partition maintenance job to drop the older
> partitions which meet the partition retention criteria, it's not letting us
> drop any of the partitions from the parent table. Child table works fine
> though.
>
> And when we try using cascade , it drops the foreign key constraint from
> all the partitions. Can you please let me know how to make the partition
> maintenance work in such a scenario while maintaining the foreign key
> intact, as we are expecting the foreign key to be attached to the
> respective partition only but not the full table?
>
> And this partition maintenance job which runs through cron scheduler in
> an automated way is failing on a daily basis for us without being able to
> drop any of the historical partitions from the parent partitioned table.
>
> Below is the test case:-
>
> CREATE TABLE schema1.test_part_drop_parent
> (
> c1 varchar(36) NOT NULL ,
> c2_part_date timestamp with time zone NOT NULL ,
> c3 numeric(12,0) NOT NULL ,
> CONSTRAINT test_part_drop_parent_PK PRIMARY KEY (c1,c2_part_date)
> ) PARTITION BY RANGE (c2_part_date);
>
> CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3
> ASC);
>
>
> CREATE TABLE schema1.test_part_drop_child
> (
> C1_child varchar(36) NOT NULL ,
> C1 varchar(36) NOT NULL ,
> c2_part_date timestamp with time zone NOT NULL ,
> C3 numeric(12,0) NOT NULL ,
> CONSTRAINT test_part_drop_child_PK PRIMARY KEY (C1_child,c2_part_date),
> CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date)
> REFERENCES schema1.test_part_drop_parent(c1,c2_part_date)
> ) PARTITION BY RANGE (c2_part_date);
>
> CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1,
> c2_part_date);
>
> CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child(
> c1_child, c2_part_date);
>
>
> select partman.create_parent(
>p_parent_table := 'schema1.test_part_drop_child',
>p_control := 'c2_part_date',
>p_type := 'native',
>p_interval := '1 day',
>p_premake := 5,
>p_start_partition => '2024-02-01 00:00:00'
> );
>
> update partman.part_config set infinite_time_partitions = 'true' ,
> retention = '1 months', retention_keep_table='false',
> retention_keep_index='false'
> where parent_table = 'schema1.test_part_drop_child';
>
>
> select partman.create_parent(
>p_parent_table := 'schema1.test_part_drop_parent',
>p_control := 'c2_part_date',
>p_type := 'native',
>p_interval := '1 day',
>p_premake := 5,
>p_start_partition => '2024-02-01 00:00:00'
> );
>
> update partman.part_config set infinite_time_partitions = 'true' ,
> retention = '1 months', retention_keep_table='false',
> retention_keep_index='false'
> where parent_table = 'schema1.test_part_drop_parent';
>
>
> select partman.run_maintenance('schema1.test_part_drop_child');
>
> select partman.run_maintenance('schema1.test_part_drop_parent');
>
>
> SQL Error [P0001]: ERROR: cannot drop table
> schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it
> CONTEXT: SQL statement "DROP TABLE
>
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
On Thu, Mar 21, 2024 at 11:52 AM Dominique Devienne wrote: > On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson > wrote: > >> > However I noticed that comments on roles are also omitted from the >> dump, as if --no--comments flag was set - but it wasn't. >> >> Comments on roles are stored against the pg_authid catalog relation > > > Hi. What do you mean? COMMENTs are not stored *in* that relation. > And AFAIK, only accessible via functions using the OID or NAME. > > So the relation used, pg_authid or pg_roles, shouldn't matter, no? > OK, I see now you meant shobj_description(oid, 'pg_authid'), i.e. that 'pg_authid' literal in the function call. Thus your use of "against" in the above sentence. Apologies for my misunderstanding. But that literal in the function call is separate from which relation, pg_authid or pg_roles, one actually SELECT from, as already shown. --DD ``` > select ..., shobj_description(oid, 'pg_authid') > from pg_roles > ... > ``` >
Re: Question about PostgreSQL upgrade from version 12 to version 15
On 2024-Mar-21, Joseph Kennedy wrote: > I'm planning to upgrade my PostgreSQL database from version 12 to > version 15 using pg_upgrade. After completing the upgrade process, I'm > curious to know whether it's necessary to reindex the database. > > Could anyone please clarify whether reindexing is required after > completing the upgrade process from PostgreSQL version 12 to version > 15 using pg_upgrade? A reindex(*) is necessary for indexes on textual columns(**), and only if you're also changing the underlying OS version(***) such that the collation behavior changes. If you're keeping the database on the same OS version, there's no need to reindex anything. (*) More than reindex actually: you may need to refresh materialized views and consider carefully any partition bounds you may have, if you have any partition keys that include textual columns. Even worse: if you have FDWs on a Postgres server that queries a table from another Postgres server with different collation libraries, it could bit you there too. (**) textual column in this case means anything that is affected by collation changes; typically that's things like varchar, text, citext, etc, for which a collation other than "C" is explicit or implied. You don't need to do anything for indexes on numbers, dates, geometries, etc, nor for textual columns where the index is defined with the C collation. (***) the underlying C library changes collation rules rather frequently (especially glibc), because the developers of said libraries don't consider that this has any important, permanent impact (but it does impact indexes for Postgres). Most such changes are innocuous, but from time to time they make changes that wreak havoc. If you're using ICU collations with your Postgres 12 databases, you may also be affected if you upgrade from one ICU version to another. Joe Conway gave a very good presentation on this topic recently: https://www.postgresql.eu/events/fosdem2024/schedule/session/5127-collation-challenges-sorting-it-out/ -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Por suerte hoy explotó el califont porque si no me habría muerto de aburrido" (Papelucho)
Re: Dropping a temporary view?
On Wed, 20 Mar 2024 at 21:01, Celia McInnis wrote: > Correct. But the initial CREATE VIEW was done as a SELECT from the database, > so if the create view was quick, I thought that the select from the view > would be equally quick. Is this a faulty assumption? It is. Create view does not run the query, select from the view does, Create view is like compiling a function, it just checks, select from the view is like running the function. >>> s=> \timing Timing is on. s=> create temporary view tstview as select pg_sleep(1)::text; CREATE VIEW Time: 153.129 ms s=> select * from tstview; pg_sleep -- (1 row) Time: 1009.195 ms (00:01.009) Francisco Olarte.
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson wrote: > > However I noticed that comments on roles are also omitted from the dump, > as if --no--comments flag was set - but it wasn't. > > Comments on roles are stored against the pg_authid catalog relation Hi. What do you mean? ROLEs are not stored in that relation. And AFAIK, only accessible via functions using the OID or NAME. So the relation used, pg_authid or pg_roles, shouldn't matter, no? Here's my own query for example: ``` select rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, oid, shobj_description(oid, 'pg_authid') from pg_roles ... ``` --DD
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
> On 20 Mar 2024, at 18:40, Bartosz Chroł wrote: > > Hello, > I've tried to dump roles using the following call to pg_dumpall: > pg_dumpall.exe --roles-only --no-role-passwords > However I noticed that comments on roles are also omitted from the dump, as > if --no--comments flag was set - but it wasn't. > When I call `pg_dumpall.exe --roles-only` than it works as expected - both > passwords and comments are dumped. > > Is it correct behaviour? It doesn't look like to me, but maybe I'm missing > something. I've checked PostgreSQL 16.2 (on Windows 11 and Ubuntu 20) and > 14.0 (on Windows Server 2019), same everywhere. Comments on roles are stored against the pg_authid catalog relation which is the catalog used for dumping roles, but when using --no-role-passwords we instead switch to using the pg_roles catalog relation. Since comments are dumped for the relations which are dumped, this means that the comments on roles are omitted when --no-role-passwords is used. It's not clear whether that was intentional or not, I'm failing to find the thread where it was discussed on a quick mailing list search. It kind of feels like an accidental bug since the restored role will be in pg_authid where the comment should be attached. -- Daniel Gustafsson
Question about PostgreSQL upgrade from version 12 to version 15
Hello everyone, I'm planning to upgrade my PostgreSQL database from version 12 to version 15 using pg_upgrade. After completing the upgrade process, I'm curious to know whether it's necessary to reindex the database. Could anyone please clarify whether reindexing is required after completing the upgrade process from PostgreSQL version 12 to version 15 using pg_upgrade? Thank you in advance for your assistance! Best regards, Joseph
pg_dumpall with flag --no-role-passwords omits roles comments as well
Hello, I've tried to dump roles using the following call to pg_dumpall: pg_dumpall.exe --roles-only --no-role-passwords However I noticed that comments on roles are also omitted from the dump, as if --no--comments flag was set - but it wasn't. When I call `pg_dumpall.exe --roles-only` than it works as expected - both passwords and comments are dumped. Is it correct behaviour? It doesn't look like to me, but maybe I'm missing something. I've checked PostgreSQL 16.2 (on Windows 11 and Ubuntu 20) and 14.0 (on Windows Server 2019), same everywhere. Best regards Bartek
Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs
On Thu, Mar 21, 2024 at 8:10 AM alex work wrote: > We encounter slow `GRANT ROLES` only on PostgreSQL 16 instances up to 42 > seconds > in production, the client process at PostgresSQL would use 100% of the > CPU. [...] > Using ROLE `acc`, grant `d_` ROLE to a session ROLE: > real0m7.579s [...] PostgreSQL 15 > Using ROLE `acc`, grant `d_` ROLE to a session ROLE: > real0m0.077s > Ouch, that's a ~ 100x regression. Thanks for the write-up, that's worrying. We don't have as many ROLEs, but we do have plenty, so this is worrying. On top of the v16 ROLE changes breaking on ROLE logic, which was fine prior (v12-v15). We've paused for now our planned v16 upgrade, until we have more time to adapt. Like you, I welcome the changes. But it turns out more expensive to adapt to them. And your report certainly makes me wonder whether we should hold off until that perf regression is addressed. Thanks, --DD
Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs
Hello,
we run multiple versions of PostgreSQL instances on production. Some time ago
we add new physical servers and decided to go with latest GA from pgdg APT
repository, that is PostgreSQL 16.
We encounter slow `GRANT ROLES` only on PostgreSQL 16 instances up to 42 seconds
in production, the client process at PostgresSQL would use 100% of the CPU.
Which is a surprise compared to other instances running older PostgreSQL
releases. On production we have a *LOT* of ROLEs, which unfortunately a case
that we did not test before switching the new servers into production mode.
The Application & ROLEs
---
Our application make use of ROLEs. We create group ROLEs for each tenant of our
application, these ROLEs are named with `d_` and `a_` prefix.
A special ROLE, called `acc`, it will be a member to each of these `d_` and
`a_` ROLEs.
The application have a concept of "session", which it would mantain and I think
outside the scope of this e-mail. In relation to PostgreSQL, the application
would create a PostgreSQL ROLE that corresponds to its own (application)
session. It would name these ROLEs with `s_` prefix, which CREATEd and
GRANTed its permission on every application's "session".
When an application "session" started, user with `acc` ROLE would grant
membersip of `d_` ROLE to `s_` ROLE (ie. GRANT ROLE `d_` TO `s_`;)
To make this clear, for example, we have (say) role `d_202402` already existing
and application would create a new ROLE `s_001` which corresponds to
application's "session". Application that connects with special ROLE `acc`
would GRANT ROLE `d_202402` to the ROLE `s_001`, like so:
GRANT d_202402 TO s_001;
In production we have up to 13 thousands of these ROLEs, each:
$ sudo -u postgres psql -p 5531
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
postgres=# select count(*) s_roles_count from pg_catalog.pg_authid
where rolname like 's_%';
s_roles_count
---
13299
(1 row)
postgres=# select count(*) a_roles_count from pg_catalog.pg_authid
where rolname like 'a_%';
a_roles_count
---
12776
(1 row)
postgres=# select count(*) d_roles_count from pg_catalog.pg_authid
where rolname like 'd_%';
d_roles_count
---
13984
(1 row)
The Setup
-
Investigating this slow `GRANT ROLE` we start a VM running Debian 11,
and create a lot of roles.
create special `acc` role and write to some file:
$ echo -e "CREATE ROLE acc WITH LOGIN NOSUPERUSER INHERIT CREATEDB
CREATEROLE NOREPLICATION;\n\n" > create_acc.sql
create a lot of `a_` roles and make sure `acc` is member of each one of them:
$ for idx1 in $(seq -w 1 100); do for idx2 in $(seq -w 1 12); do for
idx3 in $(seq -w 1 10); do echo "CREATE ROLE a_${idx1}${idx2}${idx3}
WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;"; echo "GRANT
a_${idx1}${idx2}${idx3} TO acc WITH ADMIN OPTION;"; done; done; done >
create_a.sql
create a lot of `d_` roles and make sure `acc` is member of each one of them:
$ for idx1 in $(seq -w 1 100); do for idx2 in $(seq -w 1 12); do for
idx3 in $(seq -w 1 10); do echo "CREATE ROLE d_${idx1}${idx2}${idx3}
WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;"; echo "GRANT
d_${idx1}${idx2}${idx3} TO acc WITH ADMIN OPTION;"; done; done; done >
create_d.sql
create a lot of `s_` roles:
$ for idx1 in $(seq -w 1 100); do for idx2 in $(seq -w 1 12); do for
idx3 in $(seq -w 1 10); do echo "CREATE ROLE s_${idx1}${idx2}${idx3}
WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;"; done; done;
done > create_s.sql
merge ROLE creation into one file:
$ cat create_acc.sql create_a.sql create_d.sql create_s.sql >
/tmp/create-roles.sql
PostgreSQL 16
-
Install PostgreSQL 16:
--
$ sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt
$(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ sudo apt install gnupg2
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc
| sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get -y install postgresql-16 postgresql-client-16
Create PostgreSQL 16 instance:
--
$ sudo pg_dropcluster --stop 16 main # drop default Debian cluster
$ sudo pg_createcluster 16 pg16
$ echo "local allacc trust" | sudo tee
/etc/postgresql/16/pg16/pg_hba.conf
$ echo "local allpostgrespeer" | sudo tee -a
/etc/postgresql/16/pg16/pg_hba.conf
$ sudo systemctl start postgresql@16-pg16.service
Import lots of roles:
--
$ sudo -u postgres /usr/lib/postgresql/16/bin/psql -f
/tmp/create-roles.sql -p 5432 -d postgres
Using ROLE `acc`, grant `d_` ROLE to a session ROLE:
--
$ time sudo -u postgres /usr/lib/postgresql/16/bin/psql -U acc
postgres -c 'GRANT d_0010109 TO s_0010109;'
GRANT ROLE
real0m7.579s
user0m0.054s
sys 0m0.020s
This is the surprising behavior for PostgreSQL 16. It seems there's a new logic
in PostgreSQL that checks against each role, and it took 100% of CPU.
At this point we know `acc` is just another
