164 questions
0
votes
1
answer
18
views
Postgres Statistics for Column Scans
Where can I find statistics for specific table column scans?
I have a very large table (near 10^9 rows) which has a few indexes however not on all fields. The non-indexed fields are mostly foreign ...
- 101
0
votes
0
answers
57
views
PostgreSQL Trigger: Keep my_table2 column in sync with my_table1 regardless of insert order
I have two tables: my_table1 and my_table2. a_column in my_table2 should always match the corresponding value in my_table1.
However, the tricky part is that rows in my_table2 may be inserted before or ...
- 1,016
0
votes
1
answer
46
views
How to handle roles to dump and restore a PostgreSQL database on another database in the same cluster
I am struggling with restoring a dump for several reasons and I am not even sure I am taking the right direction. Basically my issue seems to depends on my understanding of role and priviledges in ...
- 3
1
vote
2
answers
82
views
Is sub-select necessary when using jsonb_to_recordset
I have been given the task of importing data from a 3rd party. Not knowing what the data would look like, I imported it into a JSON field in a PostgreSQL table. Now to make things easier, I want to ...
- 564
-1
votes
2
answers
97
views
How to sum up numbers in JSONB key-values in PostgreSQL (CockroachDB)?
I have a table with rows with a JSONB in this form (YAML here for readability):
{ foo: 500, bar: 12 }
{ foo: 500, bar: 18, zoo: 5 }
...
There can be arbitrary keys in the objects, but always ...
- 47k
1
vote
1
answer
136
views
Is WITH SELECT ... UPDATE in Postgres safe from race conditions?
I'm working in an environment where I expect several containers to be running queries to "claim" a database row. The multiple containers are for redundancy, but I only ever want one ...
- 20.4k
0
votes
0
answers
54
views
What determines $libdir in PostgreSQL?
Looking at the PostgreSQL/15/data directory on a Windows machine, I found the postgresql.conf file.
In the file, it has #dynamic_library_path = '$libdir'
What determines what $libdir is set to and how ...
0
votes
1
answer
1k
views
Installing PGVector
I tried following the instructions here:
https://github.com/pgvector/pgvector#windows
The error message that I am getting is this:
ERROR: extension "vector" has no installation script nor ...
1
vote
1
answer
140
views
Accidentally dropped information_schema postgres
I accidentally ran and committed drop schema information_schema cascade on postgres 15.4 as a DBA.
How to recreate the said schema?
- 1,299
0
votes
0
answers
77
views
Trying to identify possible causes of flakey postgis error on postgres
We have a postgres trigger that is set to run when eastings and northings values are inserted or updated on a row. The trigger function use postgis to transform the eastings and northings, which are ...
1
vote
1
answer
78
views
Should a single update query create multiple exclusive locks on the same table in postgres (CNPG)?
My system is running instances of postgres managed using CloudNativePG; there's a primary and a couple of replicas.
When I run a query like this update <table> set <boolean-column> = NOT &...
- 117
1
vote
0
answers
255
views
Postgres out-of-memory errors on Heroku stack
We're running a Rails application on Heroku, with Postgres 15.7 (on the plan Heroku Postgres standard-2)
Before upgrading to Postgres 15.7, we were on 12.x with Heroku Postgres standard-0.
We have ...
- 2,093
1
vote
0
answers
328
views
Can't write to table due to multixact “members” limit exceeded (SQLSTATE 54000) after heavy inserts
The database was under sustained high load of inserts into a specific table for several hours.
At some point, all inserts to that table (and some updates I tried to another table) fail with the ...
- 1,620
0
votes
0
answers
92
views
PostgreSQL server closed the connection unexpectedly - temporal_tables extension - pgAdmin4
I recently installed the PostgreSQL extension temporal_tables (v1.2.2) on our PostgreSQL database (version 15.2 installed on Microsoft Windows Server 2022 Standard - x64). Note: Installing this ...
- 25
0
votes
0
answers
98
views
Postgresql RDS - Error: must be member of role
I am in the middle of trying to migrate our Postgresql on an EC2 to Postgresql RDS. When I dump one of the DBs and restore it in RDS. One of the users pops up and gives "Error: must be member of ...
