Horton

vacuumdb: permission denied for schema "pg_temp_7"

vaibhave postgres <postgresvaibhave@gmail.com>

View thread
From: vaibhave postgres <postgresvaibhave@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Cc: vsekar@microsoft.com
Date: 2024-07-06T11:49:39Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. vacuumdb: Schema-qualify operator in catalog query's WHERE clause.

  2. reindexdb: Skip reindexing temporary tables and indexes.

  3. vacuumdb: Skip temporary tables in query to build list of relations

  4. Use catalog query to discover tables to process in vacuumdb

Attachments

Repo steps

1. Create a temporary table

sample => CREATE TEMPORARY TABLE temp_employees (
>     id SERIAL PRIMARY KEY,
>     name VARCHAR(100),
>     position VARCHAR(50),
>     salary NUMERIC(10, 2)
> );
> CREATE TABLE
> sample  => \dt pg_temp_*.*
>                List of relations
>   Schema   |      Name      | Type  |  Owner
> -----------+----------------+-------+----------
> pg_temp_7 | temp_employees | table | vaibhave
> (1 row)


2. Run vacuumdb

vacuumdb: vacuuming database "sample"
> vacuumdb: error: processing of database " sample  " failed: ERROR:
> permission denied for schema pg_temp_7


Temporary tables can only be accessed within the session which created
them. They should be skipped during vacuumdb.

Suggested Patch is attached