search

dataegret / pgcompacttable

BSD 3-Clause "New" or "Revised" License
314 stars 48 forks source link

Error "relation "public.pg_toast_5987783_index" does not exist" when running the utility #44

Closed Skryabind closed 2 years ago

Skryabind commented 2 years ago

Hi! I successfully run pgcompacttable on different tables separately:

postgres@database:/home/dimon/pgcompacttable/bin$ ./pgcompacttable -d moyklass -t user_subscriptions
[Mon May 16 23:13:52 2022] (moyklass) Connecting to database
[Mon May 16 23:13:52 2022] (moyklass) Postgres backend pid: 100889
[Mon May 16 23:13:52 2022] (moyklass) Handling tables. Attempt 1
[Mon May 16 23:13:58 2022] (moyklass:public.user_subscriptions) Statistics: 88846 pages (114069 pages including toasts and indexes), it is expected that ~13.670% (12149 pages) can be compacted with the estimated space saving being 94.919MB.
[Mon May 16 23:14:02 2022] (moyklass:public.user_subscriptions) Reindex: public.user_subscriptions_subscription_id_idx, initial size 4099 pages(32.023MB), has been reduced by 33% (10.633MB), duration 3 seconds.
[Mon May 16 23:14:02 2022] (moyklass:public.user_subscriptions) Processing results: 88846 pages left (112708 pages including toasts and indexes), size reduced by 0.000B (10.633MB including toasts and indexes) in total.
[Mon May 16 23:14:02 2022] (moyklass) Processing complete.
[Mon May 16 23:14:02 2022] (moyklass) Processing results: size reduced by 0.000B (10.633MB including toasts and indexes) in total.
[Mon May 16 23:14:02 2022] (moyklass) Disconnecting from database
[Mon May 16 23:14:02 2022] Processing complete: 1 retries to process has been done
[Mon May 16 23:14:02 2022] Processing results: size reduced by 0.000B (10.633MB including toasts and indexes) in total.

After that I run the utility on full database:

./pgcompacttable -d moyklass

Some tables where optimized successfully, but after some time an error "relation "public.pg_toast_5987783_index" does not exist" started appearing for all tables. Relation pg_toast_5987783_index is the same for all tables. This is the first table the error occurs (table name is classes, maybe it is important):

[Mon May 16 23:34:59 2022] (moyklass:public.classes) Statistics: 36086 pages (38337 pages including toasts and indexes), it is expected that ~21.820% (7873 pages) can be compacted with the estimated space saving being 61.513MB.
[Mon May 16 23:35:27 2022] (moyklass:public.classes) SQL Error: ERROR:  relation "public.pg_toast_5987783_index" does not exist
[Mon May 16 23:35:27 2022] (moyklass:public.classes) Table handling interrupt.
[Mon May 16 23:35:27 2022] (moyklass:public.classes) Processing results: 36086 pages (38437 pages including toasts and indexes), size has been reduced by 0.000B (-816.000KB including toasts and indexes) in total. This attempt has been initially expected to compact ~21% more space (7871 pages, 61.498MB)

Now this error appears in any utility run, ex:

postgres@database:/home/dimon/pgcompacttable/bin$ ./pgcompacttable -d moyklass -t user_subscriptions
[Mon May 16 23:51:57 2022] (moyklass) Connecting to database
[Mon May 16 23:51:57 2022] (moyklass) Postgres backend pid: 13168
[Mon May 16 23:51:57 2022] (moyklass) Handling tables. Attempt 1
[Mon May 16 23:51:58 2022] (moyklass:public.user_subscriptions) Statistics: 88846 pages (112709 pages including toasts and indexes), it is expected that ~13.670% (12147 pages) can be compacted with the estimated space saving being 94.902MB.
[Mon May 16 23:51:58 2022] (moyklass:public.user_subscriptions) SQL Error: ERROR:  relation "public.pg_toast_5987783_index" does not exist
[Mon May 16 23:51:58 2022] (moyklass:public.user_subscriptions) Table handling interrupt.
[Mon May 16 23:51:58 2022] (moyklass:public.user_subscriptions) Processing results: 88846 pages left (112709 pages including toasts and indexes), size reduced by 0.000B (0.000B including toasts and indexes) in total.
[Mon May 16 23:51:58 2022] (moyklass) Processing complete.
[Mon May 16 23:51:58 2022] (moyklass) Processing results: size reduced by 0.000B (0.000B including toasts and indexes) in total.
[Mon May 16 23:51:58 2022] (moyklass) Disconnecting from database
[Mon May 16 23:51:58 2022] Processing complete: 1 retries to process has been done
[Mon May 16 23:51:58 2022] Processing results: size reduced by 0.000B (0.000B including toasts and indexes) in total.

The error occurs in this query:

   SELECT
    indexname, tablespace, indexdef,
    regexp_replace(indexdef, E'.* USING (\\w+) .*', E'\\1') AS indmethod,
    conname,
    CASE
        WHEN contype = 'p' THEN 'PRIMARY KEY'
        WHEN contype = 'u' THEN 'UNIQUE'
        ELSE NULL END AS contypedef,
    (
        SELECT
            bool_and(
                deptype IN ('n', 'a', 'i') AND
                NOT (refobjid = indexoid AND deptype = 'n') AND
                NOT (
                    objid = indexoid AND deptype = 'i'
                ))
        FROM pg_catalog.pg_depend
        LEFT JOIN pg_catalog.pg_constraint ON
            pg_catalog.pg_constraint.oid = refobjid
        WHERE
            (objid = indexoid AND classid = pgclassid) OR
            (refobjid = indexoid AND refclassid = pgclassid)
    )::integer AS replace_index_possible,
    (
        SELECT string_to_array(indkey::text, ' ')::int2[] operator(pg_catalog.@>) array[0::int2]
        FROM pg_catalog.pg_index
        WHERE indexrelid = indexoid
    )::integer as is_functional,
    condeferrable as is_deferrable,
    condeferred as is_deferred,
    (contype = 'x') as is_exclude_constraint,
    pg_catalog.pg_relation_size(indexoid) as idxsize
FROM (
    SELECT
        indexname, COALESCE(tablespace, (SELECT spcname AS tablespace FROM pg_catalog.pg_tablespace WHERE oid = (SELECT dattablespace
            FROM pg_catalog.pg_database
            WHERE 
                datname = current_database() AND
                spcname != current_setting('default_tablespace')))) AS tablespace, indexdef,
        (
            quote_ident(schemaname) || '.' ||
            quote_ident(indexname))::regclass AS indexoid,
        'pg_catalog.pg_class'::regclass AS pgclassid
    FROM pg_catalog.pg_indexes
    WHERE
        schemaname = 'public' AND
        tablename = 'user_subscriptions'
) AS sq
LEFT JOIN pg_catalog.pg_constraint ON
    conindid = indexoid AND contype IN ('p', 'u', 'x')
ORDER BY idxsize

It is interesting that if I change LEFT JOIN to JOIN, query executes without any error. Also if I remove condition schemaname = 'public', query executes without error too. Also if I change contype IN ('p', 'u', 'x') to contype IN ('p', 'u'), query works too!

I don't understand why this error happens, I didn't find any relation or index with the name pg_toast_5987783_index. But in fact that this has been started during pgcompacttable run and probably the utility made something wrong in database.

I've restored the dump made after this error happened on other server, and this query runs without errors on restored database. I dont know how to fix this in production database.

Melkij commented 2 years ago

Yeah, I observed such error yesterday. Will fix.

The query itself looks correct, but the postgresql query planner moved the cast to regclass to the join condition. Therefore, it may receive an attempt to search for a knowingly non-existent object. pg_toast_5987783_index is part of postgresql TOAST system, but should be in special schema pg_toast, not in public.

But to be sure it's the same problem, please say:

Skryabind commented 2 years ago

@Melkij Server version PostgreSQL 13.2 This query gives an error. This is the explain:

                                                                        QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=39.23..39.24 rows=1 width=104)
   Sort Key: (pg_relation_size((((quote_ident((n.nspname)::text) || '.'::text) || quote_ident((i.relname)::text)))::regclass, 'main'::text))
   ->  Merge Join  (cost=4.84..39.22 rows=1 width=104)
         Merge Cond: (n.oid = c.relnamespace)
         Join Filter: (x.indexrelid = i.oid)
         ->  Nested Loop Left Join  (cost=0.27..1868.52 rows=453 width=136)
               Join Filter: (pg_constraint.conindid = ((((quote_ident((n.nspname)::text) || '.'::text) || quote_ident((i.relname)::text)))::regclass)::oid)
               ->  Nested Loop  (cost=0.27..83.70 rows=453 width=136)
                     ->  Index Scan using pg_namespace_oid_index on pg_namespace n  (cost=0.27..31.03 rows=1 width=68)
                           Filter: (nspname = 'public'::name)
                     ->  Seq Scan on pg_class i  (cost=0.00..48.14 rows=453 width=72)
                           Filter: (relkind = ANY ('{i,I}'::"char"[]))
               ->  Materialize  (cost=0.00..18.43 rows=120 width=4)
                     ->  Seq Scan on pg_constraint  (cost=0.00..17.83 rows=120 width=4)
                           Filter: (contype = ANY ('{p,u,x}'::"char"[]))
         ->  Materialize  (cost=4.56..18.28 rows=1 width=8)
               ->  Nested Loop  (cost=4.56..18.28 rows=1 width=8)
                     ->  Index Scan using pg_class_relname_nsp_index on pg_class c  (cost=0.28..8.30 rows=1 width=8)
                           Index Cond: (relname = 'user_subscriptions'::name)
                           Filter: (relkind = ANY ('{r,m,p}'::"char"[]))
                     ->  Bitmap Heap Scan on pg_index x  (cost=4.29..9.96 rows=2 width=8)
                           Recheck Cond: (indrelid = c.oid)
                           ->  Bitmap Index Scan on pg_index_indrelid_index  (cost=0.00..4.29 rows=2 width=0)
                                 Index Cond: (indrelid = c.oid)

With the uncommented OFFSET 0 there is no errors.

Melkij commented 2 years ago

Should be fixed now