Skip to main content
Share Your Experience: Take the 2024 Developer Survey
5 votes
Accepted

Why does Postgres consider pg_cancel_backend() as an error?

According to the SQL standard, the database engine must communicate, via the SQLSTATE variable, the condition of every completed command, and the value of SQLSTATE indicates precisely how the command ...
mustaccio's user avatar
  • 26.1k
4 votes
Accepted

PostgreSQL has stopped using index for large IN query?

I'm guessing that ndistinct for "ItemId" has gotten deranged. You can check it out in the pg_stats view to see. This often happens for columns on which the table is clustered, due to a ...
jjanes's user avatar
  • 40.1k
1 vote

Passing contextual information to Postgres statements

I have two ideas: Make sure that log_line_prefix contains %p so that the backend process ID is logged, and on the database client side run SELECT pg_backend_pid() to get the same information from the ...
Laurenz Albe's user avatar
1 vote

Why does Postgres consider pg_cancel_backend() as an error?

You are getting this behaviour because from the point of view of the client program which started the SELECT query, it being cancelled is an error. By default, it is not clear at all that the user who ...
AnoE's user avatar
  • 351
1 vote

How to list all constraints of a table in PostgreSQL?

I'm surprised this thread doesn't contain the built in helper function, pg_get_constraintdef: SELECT conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef FROM pg_catalog.pg_constraint r ...
David V McKay's user avatar
1 vote
Accepted

PostgreSQL: What are different operations make the use of Temp files?

VACUUM and ANALYZE shouldn't create temporary files. First of all, the statistics from pg_stat_database need not be 100% accurate. Second, pg_stat_database.temp_bytes measures the size of temporary ...
Laurenz Albe's user avatar
1 vote

What is the point of shared buffers in postgres?

First of all, PostgreSQL does not only run on Linux, and not all supported operating systems might manage memory in the same way. Second, even if data are in the kernel page cache, PostgreSQL needs a ...
Laurenz Albe's user avatar
1 vote

What is the point of shared buffers in postgres?

PostgreSQL is a system that depends on both shared_buffers as well as system cache. Shared buffers or buffer pool is just like the name indicates, it's a shared area for all sessions connected to ...
mediocre's user avatar
  • 107
1 vote

How to get all roles that a user is a member of (including inherited roles)?

Strongly based on Erwin Brandstetter's answer, but also: Removing duplicates (the same role can appear in multiple places in the "tree" of parent roles) Does not use the magic ::regrole to ...
Michal Charemza's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible