159 questions
- Bountied 0
- Unanswered
- Frequent
- Score
- Trending
- Week
- Month
- Unanswered (my tags)
3
votes
3
answers
120
views
Using a script variable to generate a stored procedure in Postgresql
I would like to use a script variable to set the data type, so that I can generate a few functions with different types, like so:
\set bucket_data_type DATE
Then run a script to generate this function:...
0
votes
0
answers
25
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 ...
0
votes
0
answers
67
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 ...
0
votes
1
answer
324
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 ...
1
vote
2
answers
107
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 ...
-1
votes
2
answers
156
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 ...
1
vote
1
answer
401
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 ...
0
votes
0
answers
119
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
3k
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
268
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?
0
votes
0
answers
91
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
127
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 &...
1
vote
0
answers
319
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
votes
1
answer
501
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 ...
0
votes
2
answers
78
views
Query is extremely slow selecting data for last x events for racer
I have a table race_racer that contains time serialized logs for each racer in a race. I am trying to select the lastest record for a given racer in x number of races prior to a date. Basically ...