Re: autovacuum hung on simple tables
On Mon, 2022-11-28 at 04:05 +, senor wrote: > I'm a little late getting back to this but still have no solution. > > I had inadvertently executed a vacuum freeze on a table that pg_stat_activity > indicated was currently being autovacuumed. The manual vacuum succeeded while > leaving the previous autovacuum still showing active in pg_stat_activity. No, that cannot be. VACUUM processes are mutually exclusive. It could have been another autovacuum worker (different "pid"). > Manual vacuum always completes, never stalls, but also often does not appear > in pg_stat_progress_vacuum unless it's a longer process. It *does* appear in "pg_stat_progress_vacuum", but perhaps you are too slow to catch it. > It appears the process completes the vacuum but does not register that fact. > relfrozenxid of the main table is what would be expected but an associated > toast table was still very old. Cancelling all pending vacuums of the table > and manually running vacuum completes in a few seconds and both the main > relation and toast are updated as expected with last vacuum time updated > in pg_stat_all_tables. Obviously autovacuum devided to process the main table, but not the TOAST table. That is normal. Manual VACUUM processes both, unless you specify the option PROCESS_TOAST OFF. > Autoacuum and autovacuum analyze both get hung. No, they don't; not unless they are anti-wraparound autovacuum runs and you are holding high locks on the table in a long running transaction. They may be slow to complete, because in v11 autovacuum by default is very slow indeed, as "autovacuum_vacuum_cost_delay" is 20ms. > I often see the same table listed multiple times in pg_stat_activity with > different pids and: > state: active > backend_type: autovacuum worker > wait_event_type: blank > state_change: 10-15 seconds after backend_start - about how long it takes > to manually run vacuum on most tables. That can be parallel workers that are used to scan indexes. > What exactly does autovacuum rely on in the stats file? I ran strace on a > hung autovacuum process and saw a repeated read of > /run/postgresql/db_16384.stat (tempfs). The file is 740MB which is about > the same as other similar installations I've reviewed. I'm lacking in overall > experience in this though. I don't know either, and I am too lazy to read the code on that, but I suspect that it has no connection to your problem. > One final oddity: > I ran a query for oldest relfrozenxid and redirected to file. The query took > around a minute. A few seconds after it finished, I queried for 'active' in > pg_stat_activity and the oldest relfrozenxid query was still listed. > A few seconds later it had cleared. That sounds weird and is hard to believe. Are the disk or the CPU under extreme stress? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Get table catalog from pg_indexes
Igor Korot schrieb am 27.11.2022 um 23:13: >> I've never heard of a database referred to as a catalog. (That's always >> been where a database's metadata -- i.e. the pg_catalog schema -- is stored.) > > In the ODBC terminology the DB is usually referenced as catalog. JDBC uses the same term and the SQL standard as well.
Re: autovacuum hung on simple tables
I'm a little late getting back to this but still have no solution. I 100% agree that updating postgres is best. The usual excuses for not updating apply. It will be done when it is allowed to be done. It remains 11.4 for now. I read through the changelogs up through 11.18 and didn't see anything obviously related. The underlying cause could be pretty involved and something I wouldn't recognize. Thank you Laurenz Albe for reminding me about an important clue. I had inadvertently executed a vacuum freeze on a table that pg_stat_activity indicated was currently being autovacuumed. The manual vacuum succeeded while leaving the previous autovacuum still showing active in pg_stat_activity. Manual vacuum always completes, never stalls, but also often does not appear in pg_stat_progress_vacuum unless it's a longer process. It appears the process completes the vacuum but does not register that fact. relfrozenxid of the main table is what would be expected but an associated toast table was still very old. Cancelling all pending vacuums of the table and manually running vacuum completes in a few seconds and both the main relation and toast are updated as expected with last vacuum time updated in pg_stat_all_tables. pg_stat_progress_vacuum never had any entry. Autoacuum and autovacuum analyze both get hung. I often see the same table listed multiple times in pg_stat_activity with different pids and: state: active backend_type: autovacuum worker wait_event_type: blank state_change: 10-15 seconds after backend_start - about how long it takes to manually run vacuum on most tables. What exactly does autovacuum rely on in the stats file? I ran strace on a hung autovacuum process and saw a repeated read of /run/postgresql/db_16384.stat (tempfs). The file is 740MB which is about the same as other similar installations I've reviewed. I'm lacking in overall experience in this though. One final oddity: I ran a query for oldest relfrozenxid and redirected to file. The query took around a minute. A few seconds after it finished, I queried for 'active' in pg_stat_activity and the oldest relfrozenxid query was still listed. A few seconds later it had cleared. Can a corrupted stats file prevent autovac from reading/writing? > I scripted a vacuum loop using the oldest table list. It's extremely slow but > it was > making better progress than autovacuum was. > > Using ps I see that there were as many worker processes as defined with > autovacuum_max_workers > but pg_stat_activity consistantly showed 19. I killed the script thinking > there might be a conflict. > I saw no difference after 30 minutes so restarted script. I am not sure what exactly you are actually doing here, but you should know that there can only be one VACUUM process per table. If there is already an anti-wraparound autovacuum running on the table, a manual VACUUM will simple be blocked until the autovacuum worker is done. > Never saw anything in pg_stat_progress_vacuum. Now that would be weird, except if VACUUM cannot get the required lock on the table. > vacuum settings: > name | setting > -+--- > autovacuum | on > autovacuum_freeze_max_age | 2 > autovacuum_max_workers | 40 > autovacuum_naptime | 4 > autovacuum_vacuum_cost_delay| 0 > autovacuum_vacuum_cost_limit| 5000 > autovacuum_work_mem | -1 > vacuum_freeze_min_age | 5000 > vacuum_freeze_table_age | 15000 > > I'm now thinking that autovacuum getting hung up is what caused the issue to > begin with. I see nothing > but the successful vacuums from the script and my own fat-fingering commands > in the postgres > logs (set at info). Sorry about the reply formatting. I tried using outlook web in Edge. Maybe that was a mistake. Thanks, Senor
Re: Postgres upgrade 12 - issues with OIDs
On Mon, 28 Nov 2022 at 12:46, Venkata B Nagothi wrote: > Coming back to this thread after a while.. we have to remove OID on a 6 TB (5 > TB of indexes) table and ALTER TABLE is gonna block the table and is gonna > take hours... You may want to look into exploiting table inheritance for this. Something like: create table tab (a int, b int) with oids; -- the existing table begin; -- do make the following atomic alter table tab rename to old_tab; create table tab (a int, b int) without oids; -- new version of the table, without oids alter table old_tab inherit tab; -- make it so querying the new table also gets rows from the old table. commit; -- do this a bunch of times over the course of a few days until old_tab is empty. with del as (delete from old_tab where a in (select a from old_tab limit 1000) returning *) insert into tab select * from del; you can then drop the old table. You'll need to think carefully about unique constraints and any other constraints which are on the table in question. You'll want to do a lot of testing before committing to doing this too. David
Re: Postgres upgrade 12 - issues with OIDs
Coming back to this thread after a while.. we have to remove OID on a 6 TB (5 TB of indexes) table and ALTER TABLE is gonna block the table and is gonna take hours... We operate on pretty much 0 outage we do have a passive site on which we will be doing the activity.. if not ALTER TABLE.. is there any other way to achieve this ? I think logical replication does not allow replicating from one table to another ? Regards, Ven On Sun, 16 May 2021 at 11:00 am, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Saturday, May 15, 2021, Venkata B Nagothi wrote: > >> >> >> *ERROR: column c.relhaspkey does not exist at character 33* >> >> Below is the query generating the error : >> >> STATEMENT: SELECT c.relname AS table_name, c.relhaspkey AS >> has_primary_key FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE >> c.relkind = 'r' AND c.relnamespace = n.oid AND n.nspname = $1 >> >> Any advice on how bad it is and how to get rid of it would be great. >> > > Figure out what is issuing the query and either fix it or upgrade to a > version that has been fixed. > Upon googling, I came to know that this error occurs when a connection to higher version Postgres using lower version binaries. So, might be some job/script in our environment is using 9.5 binaries against 11 version which is generating this error. Will check that out. >
Re: Get table catalog from pg_indexes
On 11/27/22 14:11, Igor Korot wrote: David, On Sun, Nov 27, 2022 at 12:55 PM David G. Johnston wrote: On Sun, Nov 27, 2022 at 11:42 AM Igor Korot wrote: It doesn't say anything about "current" DB - only the DB. Yes, but you must be connected to some database in order to execute this command: "the database" refers to this database you are connected to. Yes, I am and I get that. The catalogs are not information_schema. However, I think I can try "SELECT 1 FROM .pg_indexes...". Will this work? What is that even supposed to mean? It also seems simple enough to just do that asking "will this work" is a waste of time. Just try it. Apparently it looks like this query fails to execute. I am connected to the "draft" database and running SELECT 1 FROM draft.pg_indexes; gives: [quote] ERROR:schema "draft" does not exist [/quote] Because the layout is catalog.schema.table so: SELECT 1 FROM draft.pg_catalog.pg_indexes; Just because this works don't leap to assumption that: .pg_catalog.pg_indexes will work. This only works with the current database name. Thank you/ David J. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Get table catalog from pg_indexes
Ron, On Sun, Nov 27, 2022 at 4:10 PM Ron wrote: > > On 11/27/22 15:55, Adrian Klaver wrote: > > On 11/27/22 13:31, Ron wrote: > >> On 11/27/22 11:22, Igor Korot wrote: > >>> Hi, ALL, > >>> Table pg_indexes does not contain a field for a catalog. > >>> > >>> So how do I get that? > >>> > >>> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND > >>> schemaname = $3 > >> > >> You did not look hard enough, or Google "postgresql pg_indexes". > >> > >> test=# \d pg_indexes > >> View "pg_catalog.pg_indexes" > >> Column | Type | Collation | Nullable | Default > >> +--+---+--+- > >> *schemaname* | name | | | > >> *tablename* | name | | | > >> *indexname* | name | | | > >> tablespace | name | | | > >> indexdef | text | | | > >> > >> https://www.postgresql.org/docs/current/view-pg-indexes.html > > > > What the OP was looking for a field in the above that was catalogname or > > datname per: > > I've never heard of a database referred to as a catalog. (That's always > been where a database's metadata -- i.e. the pg_catalog schema -- is stored.) In the ODBC terminology the DB is usually referenced as catalog. Thank you. > > -- > Angular momentum makes the world go 'round. > >
Re: Get table catalog from pg_indexes
David, On Sun, Nov 27, 2022 at 12:55 PM David G. Johnston wrote: > > On Sun, Nov 27, 2022 at 11:42 AM Igor Korot wrote: >> >> >> It doesn't say anything about "current" DB - only the DB. > > > Yes, but you must be connected to some database in order to execute this > command: "the database" refers to this database you are connected to. Yes, I am and I get that. > > The catalogs are not information_schema. > >> >> However, I think I can try "SELECT 1 FROM .pg_indexes...". >> Will this work? >> > > What is that even supposed to mean? It also seems simple enough to just do > that asking "will this work" is a waste of time. Just try it. Apparently it looks like this query fails to execute. I am connected to the "draft" database and running SELECT 1 FROM draft.pg_indexes; gives: [quote] ERROR:schema "draft" does not exist [/quote] Thank you/ > > David J.
Re: Get table catalog from pg_indexes
On 11/27/22 15:55, Adrian Klaver wrote: On 11/27/22 13:31, Ron wrote: On 11/27/22 11:22, Igor Korot wrote: Hi, ALL, Table pg_indexes does not contain a field for a catalog. So how do I get that? SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND schemaname = $3 You did not look hard enough, or Google "postgresql pg_indexes". test=# \d pg_indexes View "pg_catalog.pg_indexes" Column | Type | Collation | Nullable | Default +--+---+--+- *schemaname* | name | | | *tablename* | name | | | *indexname* | name | | | tablespace | name | | | indexdef | text | | | https://www.postgresql.org/docs/current/view-pg-indexes.html What the OP was looking for a field in the above that was catalogname or datname per: I've never heard of a database referred to as a catalog. (That's always been where a database's metadata -- i.e. the pg_catalog schema -- is stored.) -- Angular momentum makes the world go 'round.
Re: Get table catalog from pg_indexes
Thx, Adrian. On Sun, Nov 27, 2022 at 3:56 PM Adrian Klaver wrote: > > On 11/27/22 13:31, Ron wrote: > > On 11/27/22 11:22, Igor Korot wrote: > >> Hi, ALL, > >> Table pg_indexes does not contain a field for a catalog. > >> > >> So how do I get that? > >> > >> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND > >> schemaname = $3 > > > > You did not look hard enough, or Google "postgresql pg_indexes". > > > > test=# \d pg_indexes > > View "pg_catalog.pg_indexes" > > Column | Type | Collation | Nullable | Default > > +--+---+--+- > > *schemaname* | name | | | > > *tablename* | name | | | > > *indexname* | name | | | > > tablespace | name | | | > > indexdef | text | | | > > > > https://www.postgresql.org/docs/current/view-pg-indexes.html > > What the OP was looking for a field in the above that was catalogname or > datname per: > > https://www.postgresql.org/docs/current/catalog-pg-database.html > > Table "pg_catalog.pg_database" > Column | Type| Collation | Nullable | Default > ---+---+---+--+- > oid | oid | | not null | > datname | name | | not null | > ... > > In other words to filter the pg_index results by database/catalog name. > Since pg_index is scoped to the database you are in when you do the > query that is not going to happen. > > > > > > > > > > > -- > > Angular momentum makes the world go 'round. > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > >
Re: Get table catalog from pg_indexes
On 11/27/22 13:31, Ron wrote: On 11/27/22 11:22, Igor Korot wrote: Hi, ALL, Table pg_indexes does not contain a field for a catalog. So how do I get that? SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND schemaname = $3 You did not look hard enough, or Google "postgresql pg_indexes". test=# \d pg_indexes View "pg_catalog.pg_indexes" Column | Type | Collation | Nullable | Default +--+---+--+- *schemaname* | name | | | *tablename* | name | | | *indexname* | name | | | tablespace | name | | | indexdef | text | | | https://www.postgresql.org/docs/current/view-pg-indexes.html What the OP was looking for a field in the above that was catalogname or datname per: https://www.postgresql.org/docs/current/catalog-pg-database.html Table "pg_catalog.pg_database" Column | Type| Collation | Nullable | Default ---+---+---+--+- oid | oid | | not null | datname | name | | not null | ... In other words to filter the pg_index results by database/catalog name. Since pg_index is scoped to the database you are in when you do the query that is not going to happen. -- Angular momentum makes the world go 'round. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Get table catalog from pg_indexes
On 11/27/22 11:22, Igor Korot wrote: Hi, ALL, Table pg_indexes does not contain a field for a catalog. So how do I get that? SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND schemaname = $3 You did not look hard enough, or Google "postgresql pg_indexes". test=# \d pg_indexes View "pg_catalog.pg_indexes" Column | Type | Collation | Nullable | Default +--+---+--+- *schemaname* | name | | | *tablename* | name | | | *indexname* | name | | | tablespace | name | | | indexdef | text | | | https://www.postgresql.org/docs/current/view-pg-indexes.html -- Angular momentum makes the world go 'round.
Re: Get table catalog from pg_indexes
On Sun, Nov 27, 2022 at 11:53 AM Igor Korot wrote: > > On Sun, Nov 27, 2022 at 12:45 PM Christophe Pettus > wrote: > > > > > In PostgreSQL terminology, a single PostgreSQL instance (the cluster) > can have multiple databases. > > Just like any other DBMS. > The point was the terminology - write "database" and "cluster" instead of "catalog" and whatever (if anything) the SQL Standard has in place for "a group of databases". David J.
Re: Get table catalog from pg_indexes
On 11/27/22 10:42, Igor Korot wrote: Hi, Erik, On Sun, Nov 27, 2022 at 11:59 AM Erik Wienhold wrote: On 27/11/2022 18:22 CET Igor Korot wrote: Table pg_indexes does not contain a field for a catalog. So how do I get that? SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND schemaname = $3 Use SELECT current_database() if you need to know the catalog. pg_indexes only covers the current database[1]. From the lin yo referenced: [quote] The view pg_indexes provides access to useful information about each index in the database. [/quote] It doesn't say anything about "current" DB - only the DB. https://www.postgresql.org/docs/current/catalogs-overview.html "Most system catalogs are copied from the template database during database creation and are thereafter database-specific. A few catalogs are physically shared across all databases in a cluster; these are noted in the descriptions of the individual catalogs." As in: https://www.postgresql.org/docs/current/catalog-pg-database.html "Unlike most system catalogs, pg_database is shared across all databases of a cluster: there is only one copy of pg_database per cluster, not one per database." However, I think I can try "SELECT 1 FROM .pg_indexes...". Will this work? Thank you. [1] https://www.postgresql.org/docs/current/view-pg-indexes.html -- Erik -- Adrian Klaver adrian.kla...@aklaver.com
Re: Get table catalog from pg_indexes
> On Nov 27, 2022, at 10:53, Igor Korot wrote: > Are you saying I can't run any query on other DB? Or connect to DB and > run select? The query you run only looks at the system catalogs for the database you are connected to (with the exception of the small number of global catalogs, like pg_database). So, if you are connected to database "a", you can't query the system catalogs of database "b" for what indexes are in them.
Re: Get table catalog from pg_indexes
On Sun, Nov 27, 2022 at 11:42 AM Igor Korot wrote: > > It doesn't say anything about "current" DB - only the DB. > Yes, but you must be connected to some database in order to execute this command: "the database" refers to this database you are connected to. The catalogs are not information_schema. > However, I think I can try "SELECT 1 FROM .pg_indexes...". > Will this work? > > What is that even supposed to mean? It also seems simple enough to just do that asking "will this work" is a waste of time. Just try it. David J.
Re: Get table catalog from pg_indexes
Hi, Christopher, On Sun, Nov 27, 2022 at 12:45 PM Christophe Pettus wrote: > > > > > On Nov 27, 2022, at 10:42, Igor Korot wrote: > > > > It doesn't say anything about "current" DB - only the DB. > > In PostgreSQL terminology, a single PostgreSQL instance (the cluster) can > have multiple databases. Just like any other DBMS. > The database the connection is currently open to is the current database. Are you saying I can't run any query on other DB? Or connect to DB and run select? Thank you.
Re: Get table catalog from pg_indexes
> On Nov 27, 2022, at 10:42, Igor Korot wrote: > > It doesn't say anything about "current" DB - only the DB. In PostgreSQL terminology, a single PostgreSQL instance (the cluster) can have multiple databases. The database the connection is currently open to is the current database.
Re: Get table catalog from pg_indexes
Hi, Erik, On Sun, Nov 27, 2022 at 11:59 AM Erik Wienhold wrote: > > > On 27/11/2022 18:22 CET Igor Korot wrote: > > > > Table pg_indexes does not contain a field for a catalog. > > > > So how do I get that? > > > > SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND > > schemaname = $3 > > Use SELECT current_database() if you need to know the catalog. > pg_indexes only covers the current database[1]. >From the lin yo referenced: [quote] The view pg_indexes provides access to useful information about each index in the database. [/quote] It doesn't say anything about "current" DB - only the DB. However, I think I can try "SELECT 1 FROM .pg_indexes...". Will this work? Thank you. > > [1] https://www.postgresql.org/docs/current/view-pg-indexes.html > > -- > Erik
Re: Get table catalog from pg_indexes
> On 27/11/2022 18:22 CET Igor Korot wrote: > > Table pg_indexes does not contain a field for a catalog. > > So how do I get that? > > SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND > schemaname = $3 Use SELECT current_database() if you need to know the catalog. pg_indexes only covers the current database[1]. [1] https://www.postgresql.org/docs/current/view-pg-indexes.html -- Erik
Get table catalog from pg_indexes
Hi, ALL, Table pg_indexes does not contain a field for a catalog. So how do I get that? SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND schemaname = $3 Thank you.
