0

I have created a large temporary table. I can find it in the /var/lib/pgpro/1c-14/data/base/16385 directory (where 16385 is the OID of the database).

/var/lib/pgpro/1c-14/data/base/16385$ ls | grep -Eh "t"
t3_36317
t3_36317_fsm
t3_36320
t3_36321

But I don't see it in pg_class. Request

select * from pg_class
where relpersistence = 't'

returns nothing, although the table is guaranteed to exist at the current moment!

Where can I find a temporary table and, for example, find out its size?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
RomanShan
  • 11
  • 5

1 Answers1

1

You should see the entry in pg_class if you are logged into the same DB of the same DB cluster. Not from another DB in the same cluster. Resolve the OID of your database to its name with:

SELECT datname FROM pg_database WHERE oid = '16460';

(Which works from any DB in the same cluster.)

To make sure you are indeed connected to the right database, run in the same session:

SELECT current_database();

But even if you see the entry in pg_class, be aware that you can only access temporary objects from within the same session. Each session has its own temporary schema holding their respective temporary objects.

A more insightful version of your query:

SELECT pg_is_other_temp_schema(relnamespace) AS is_other_temp_schema
     , relnamespace::regnamespace            AS temp_schema
     , pg_my_temp_schema()::regnamespace     AS my_temp_schema
     , pg_size_pretty(pg_total_relation_size(oid)) AS total_relation_size
     , *
FROM   pg_class
WHERE  relpersistence = 't'
ORDER  BY relname, pg_is_other_temp_schema(relnamespace), relnamespace::regnamespace::text;

I threw in the total relation size, since you ask for that. More about that:

Sorting "own" temp objects to the top - if there should be multiple ones of the same name.

The added system information functions are explained in the manual.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • here are the databases https://snipboard.io/pqbI9l.jpg. I use the connection to that database, but the result of the request is empty https://snipboard.io/BPudK1.jpg – RomanShan Nov 29 '22 at 06:25
  • and here is the temporary table file https://snipboard.io/Zz0Wys.jpg – RomanShan Nov 29 '22 at 06:28
  • 1
    @RomanShan: Your screenshot reveals that you are logged into the "postgres" database (the default maintenance database) with pgAdmin. Log into the database named "base" as instructed and try again. – Erwin Brandstetter Nov 29 '22 at 06:38
  • I changed the user so that it is clearly visible that the database is mine :) https://snipboard.io/mzsD6j.jpg. The first parameter is the name of the base – RomanShan Nov 29 '22 at 06:49
  • this is how i create the connection https://snipboard.io/i2OAuU.jpg – RomanShan Nov 29 '22 at 06:51
  • And `SELECT current_database();` confirms that you are, indeed, in the right session connected to the right database? If so, you must be connected to the wrong DB cluster. Or you have zombi files in your db directory (after a server crash?) – Erwin Brandstetter Nov 29 '22 at 07:04
  • the base is correct https://snipboard.io/1bUYGv.jpg – RomanShan Nov 29 '22 at 07:14
  • I only have one cluster on this VM. – RomanShan Nov 29 '22 at 07:20
  • I created a new database and repeated the experiment in it. The result is the same. please tell me, maybe this is a feature of PG-14? I'm just starting to learn PG and it discourages me :( – RomanShan Dec 01 '22 at 10:01
  • I can't explain that. Maybe your VM plays a role, but I can't say. – Erwin Brandstetter Dec 01 '22 at 13:27