I am running into the row is too big (ERROR: 54000: row is too big: size 9976, maximum size 8160) error in Postgres related to role permissions on a couple of my instances. I understand that after v12 there was a TOAST table added to help mitigate that issue. I have upgraded the affected instances to v15, but the issue is still occurring. I have seen many answers about how to structure permissions to avoid this problem, but have not had any luck in finding answers as to how to resolve the problem in a running database. In the email thread where the Postgres developers discussed the implementation of the TOAST tables to address this problem they alluded to manual steps needed to re-init a database to take advantage of the fix, but no reference to the actual steps. For context, I am running on AWS RDS (not Aurora) and using Vault to generate dynamic database credentials. Because of the error I am unable to drop any permissions or modify existing permissions. I have also been unsuccessful in finding a way to locate the specific row in the pg_catalog that is generating the error. Can someone please share any steps that they have taken to resolve this situation without having to recreate the entire database? Specifically, being able to drop permissions and users, and/or initialize the missing TOAST tables in the running database. Thank you for your help!

I have come up with the following query to get the table name, index_type, operator_classes, and uniqueness of indexes based on the answers mostly from @Erwin Brandstetter:

SELECT i.indrelid::regclass::text AS table
     , c.relname AS index_name
     , a.amname AS index_type
     , opc.operator_classes
     , i.indisunique AS is_unique
     , array_agg(att.attname) as column_names
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
JOIN   pg_catalog.pg_am        a ON a.oid = c.relam
CROSS  JOIN LATERAL (
   SELECT ARRAY (SELECT opc.opcname
                 FROM   unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
                 JOIN   pg_opclass opc ON opc.oid = o.oid
                 ORDER  BY o.ord)
   ) opc(operator_classes)
WHERE  n.nspname !~ '^pg_'
AND    c.relkind = ANY (ARRAY['r', 't', 'i'])
group by
   i.indrelid, c.relname, a.amname, opc.operator_classes, i.indisunique
ORDER  BY 1, 2, 3, 4;

This works great, except it returns the wrong column names when the index is created by transforming data from another column. For example, with the following index:

CREATE INDEX _ixtr_luxury_inventory_images ON public.luxury_inventory
 USING gin (f_textarr2text(images) gin_trgm_ops)

returns:

table            | luxury_inventory
index_name       | _ixtr_luxury_inventory_images
index_type       | gin
operator_classes | ['gin_trgm_ops']
is_unique        | False
column_names     | ['f_textarr2text']

We get f_textarr2text as the column name instead of images. How can we get the correct column_names? I'm on Postgres 16.2 if that matters. Update: Here is the table creation SQL:

create table "luxury_inventory" (
id bigserial primary key,
images text[])

CREATE OR REPLACE FUNCTION "public".f_textarr2text(text[]) 
RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT array_to_string($1, ',')$$;

I have created this tbl_cities using the code below:

CREATE TABLE IF NOT EXISTS public.tbl_Cities
  (City_ID SERIAL NOT NULL,
   City_Name VARCHAR(100) NOT NULL,
   City_State_Region VARCHAR(100),
   ID_Country INT NOT NULL,

   CONSTRAINT PK_City_ID PRIMARY KEY (City_ID));

Now I want to query **the exact same order of this columns and their datatype,** in order to make sure, what are the column names and what is the real column order (without scrolling back to the CREATE TABLE statement). In context of the INSERT statements I'm about to write and execute. To learn this info I execute:

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'tbl_cities';

But the output I get is in different order, than the columns exist in reality. I've also tried to order the columns, but the only parameter I know is ORDER BY column_name. In this very case it results in the correct order in which the columns exist in reality.

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'tbl_cities'
ORDER BY column_name;

My **question** is: What is the correct syntax to achieve the **real order in which the columns are in the table,** in case ordering them alphabetically would return different result/order. I have found this question , but it concerns SQL Server and I need to do this on PostgreSQL v14.7. **I'm open to different answers** using not only information_schema.columns but also any kind of this other thing:

SELECT * FROM pg_catalog.pg_tables;

I understand the high-level differences between pg_catalog and information_schema. But in many cases, a tool could choose to use any of them. And I'm wondering if there are performance reasons why we should prefer one or the other. Are queries expected to be faster on one of them? Do they work in the same way in terms of locks?

I am learning about PostgreSQL and am trying to understand basic concepts like schemas, catalogs and tables. I have been reading _PostgreSQL: Up and Running_, and to quote the book: - Schemas are ... the immediate next level of organization within each database, [they] organize your database into logical groups. - Catalogs are system schemas that store PostgreSQL builtin functions and metadata I am currently reading about pg_database , and am confused whether it's a catalog or a table. From the documentation , it says "The **catalog** pg_database stores information about the available databases". But on pgAdmin4, it is listed as a table within the pg_catalog catalog. In the book, there's a query that updates the datistemplate column as if it's a table.

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'mydb';

So is pg_database a catalog or a table? Or is it (somehow) both?