PostgreSQL

27 September 2024 14:09

One thing you could try. Log all of the queries, and scan them for references to the main table (ID) column. It's brute force. Also, you can scan the application code for references to the main table you are speaking of... And checking how it is referenced.
You have to do this for all of the views as well (luckily those are all available to you).

As you do this, you learn (quickly) that the FK references are easier to define if they are consistently named (we use <PK_table>_id) and LONG ago we decided every table would simply have an ID column EVEN IF there were a natural key, because it avoids multi field keys, and the extra thought process when joining.

PostgreSQL

27 September 2024 11:39

select conrelid::regclass,confrelid::regclass from pg_constraint where contype = 'f'; should be a good start, yes

PostgreSQL

27 September 2024 11:27

Scm modules and hcm vedious available. .....

PostgreSQL

27 September 2024 11:22

If you didn't define FK constraints, looking and guessing is the way forward... good luck, I once had a client who asked me to do that, in a schema with > 1.000 tables. Luckily, the column names were usually a good hint, but that's not necessarily the case. Then the application code is probably the best place to look.

PostgreSQL

27 September 2024 11:17

The pgadmin 4 server could not be contacted


How can i fix this error
Can anyone help?

PostgreSQL

27 September 2024 09:53

Record's id that is used in other tables.

PostgreSQL

27 September 2024 09:42

I actually meant finding all tables that reference that specific record by its ID, not just foreign keys. Is there a way to find all the tables where that record is used?

PostgreSQL

27 September 2024 09:13

Hi everyone, is there a way in PostgreSQL to find the dependent tables and columns for a record based on its id?

PostgreSQL

27 September 2024 07:32

You don't have to. You simply REASSIGN OWNED BY $role TO ?? and then drop role

PostgreSQL

27 September 2024 00:22

You can use REASSIGN OWNED to reassign any objects owned by one role to another. It's specifically to allow you to then drop the role you did the reassign on.

PostgreSQL

26 September 2024 22:53

Dear team,
I am looking for a comprehensive document or suggestions on best practices for the end-to-end setup of PostgreSQL 16 on a new server, including installation, replication setup, monitoring tool configuration, and backup strategy. If anyone has such documentation or can provide insights, it would be greatly appreciated.
Thank you.

PostgreSQL

26 September 2024 18:36

Is there anyone who can help me improve my English speaking skills?

PostgreSQL

26 September 2024 16:44

https://www.postgresql.org/about/news/postgresql-17-released-2936/

and here is the full release notes:
https://www.postgresql.org/docs/17/release-17.html

PostgreSQL

26 September 2024 16:38

1) It requires to really set that everywhere.
2) I doubt that frozen-deleted isn't well-tested and may involve xid clash. Generally safer would be to rewrite xids completely/

3) I thought to write such an importer... Somwtimes. Some global objects should be checked: users, tablespaces...

PostgreSQL

26 September 2024 16:35

Alas! even dicussing this could lead a lot of people coming here to wrong ideas.

*DON'T* try this at home, kids!

PostgreSQL

27 September 2024 13:57

Ask a sysadmin to fix it.

PostgreSQL

27 September 2024 11:29

Ah, I see what you mean. Thanks for the insight! If there is a foreign key constraint, should I be querying the PostgreSQL catalog schema to find all the references? Otherwise, I guess I'll have to check column names and dig into the application code. Appreciate the heads-up:)

PostgreSQL

27 September 2024 11:26

I'm happy to know I'm not the only one having to look at that kind of databases 😄

PostgreSQL

27 September 2024 11:19

Got it, thanks for the clarification! What I meant was, if a table has a primary key like id and that ID is used in other tables (either as a foreign key or in some other way), is there a way to find all the places where that specific ID is being referenced across different tables?

PostgreSQL

27 September 2024 11:09

You seem to have a misconception there. If *you* don't define an ID, your record doesn't have any. The tinme where each tuple had an OID are *long* gone...

PostgreSQL

27 September 2024 09:49

I repeat: "Which ID?"

PostgreSQL

27 September 2024 09:30

Which ID?
If you mean foreign key constraints, those are visible in e.g. the psql \d output:

demo=> create table a (id int primary key);
CREATE TABLE
demo=> create table b (id int, fk_a int references a(id));
CREATE TABLE
demo=> \d a
                 Table "public.a"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "b" CONSTRAINT "b_fk_a_fkey" FOREIGN KEY (fk_a) REFERENCES a(id)

demo=> 

PostgreSQL

27 September 2024 07:33

Also, you can study pg_depend and pg_shdepend relations, this way you can get OIDs of relations owned by role

PostgreSQL

27 September 2024 06:35

How to find out list objects to reassign them to other user/role

PostgreSQL

27 September 2024 00:15

Hi Experts, Any idea how to drop any role/user in postgresql,  while I am droping role/user I am getting 120 objects are dependent on <dbname> and in another db it is showing many privileges of table are dependent. I tried with few queries to identify from internet object/grant list which are dependent on that role/user. But no luck...

PostgreSQL

26 September 2024 18:55

This is not the place. But Duo Lingo is a free phone app. I am using it to learn Russian.

PostgreSQL

26 September 2024 16:47

I'd like more to implement a database restore in an existing cluster.

PostgreSQL

26 September 2024 16:41

Probably easier to implement an AM that uses an undo log instead 😏🤪

PostgreSQL

26 September 2024 16:36

PG 17 was released ~ 1.5 hours ago 🍾

PostgreSQL

26 September 2024 16:34

Rigth! Well, the frozen bit could be set everywhere ;-)