165 questions
0
votes
0
answers
21
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
0
answers
71
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
74
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,278
0
votes
0
answers
62
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
60
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
179
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
138
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,588
0
votes
0
answers
73
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
62
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 ...
0
votes
2
answers
77
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 ...
- 29
0
votes
0
answers
24
views
how to use a Persistent NFS Volume to store log files of postgresql
I have postGresql db running in the redhat system. the database data and logs file are in the local server.
we start the DB service using the command
/usr/pgsql-15/bin/pg_ctl -D /apac-info-sb/pgsql/...
- 369
0
votes
0
answers
138
views
pg_cron.jobs not supported for Azure Postgresql flexi server through terraform
azurerm Terraform provider issue when creating a pg_cron.jobs after installing pg_cron as Azure extensions.
locals {
default_pg_parameters = {
"azure.extensions" = "pg_cron"
...
- 1,623
0
votes
1
answer
242
views
how to evaluate checkpoints of postgreSQL
I'm newer on postGreSQL. I'm using PG15 on linux server, the server memory is 32G.
the checkpoint_timeout,sharebuffer, wal etc are using default setting on pg_settings.
that means checkpoint_timeout ...
- 369
-2
votes
1
answer
80
views
Efficient SQL full data retrieval combining JOINs and WHERE
I have a quite complex data model in my current project's database and I am wondering if there is a better/more efficient way of getting all data, after applying some filters, in a single efficient ...
0
votes
0
answers
96
views
UPSERT native SQL query for Collection as @Param of JPA Repository method
I need to iterate over collection of Enteties and apply UPSERT for each element atomically. As i see it - just need to send this collection into SQL query by SpEL.
I know, what i can iterate over ...
0
votes
0
answers
25
views
How can I add a key/value to a sub-object in a postgres jsonb column
I have a table with a jsonb column containing data like:
{
"key_a": "value",
"key_b": {
"_reference": "reference to remote object 1"
...
- 43
1
vote
1
answer
142
views
Do I trust "cost" or "actual time" more when using Postgres EXPLAIN ANALYZE?
I'm trying to improve the performance of one of our most expensive queries. I'm running EXPLAIN ANALYZE on the sandbox postgres 15 database, which has a much smaller dataset than the production ...
0
votes
0
answers
70
views
postgres btree index unique constraint does not work
I have a table accounts_coin with unique constraint on (type, id), but it still have two rows with same value of (type, id), this image shows the problem: click
information might help:
postgres ...
0
votes
0
answers
69
views
Postgresql JSONB column - Index - GIN Vs BTree
I have a large table with multiple partitions (total size = 250 GB). The table has a column with a large json stored as a jsonb column. Users will query using criteria on certain fields (one or ...
0
votes
0
answers
70
views
Installing PLV8 extension for Postgresql under RHEL os
I want to ask if there anyone who installed PostgreSQL 15 with the PLv8 extension on RedHat 9 since I want to make sure if the combination that I am using works just fine, otherwise I might need ...
- 1
0
votes
0
answers
38
views
OSGi application upgrade from Postgres 11/12 to Postgres 15
My OSGi application has been running with a Postgres driver that works Postgres 11 without problems. Oddly, everything has been working so I have had little to deal with the libraries needed. I ...
- 59
1
vote
2
answers
34
views
Skip certain types of nodes in DLL
I'm on Postgres 15.1 and have a node table as follows:
CREATE TABLE node (
id TEXT PRIMARY KEY,
is_skippable BOOL,
prev TEXT REFERENCES node(id),
"next" TEXT REFERENCES node(...
- 117
0
votes
0
answers
21
views
SQLAlchemy One to Many Relationship with Relationship to get Latest Based on Timestamp?
I'm using Python3.11 + SQLAlchemy 2.0.23. I have two models Project and Document. Document has project_id on it and a Project can have multiple Documents.
The issue is that I frequently need to get ...
0
votes
0
answers
117
views
pg_rewind: servers diverged at WAL location
We have a primary and one standby as our HA setup for postgres 15.6. we are using repmgr 5.4.1.1 for our HA setup. We were testing a scenario, if a primary goes down, it is able to do automatic ...
0
votes
0
answers
209
views
#citus Coordinator node setup and worker node setup are not working
I would like to setup citus and use it. But the error occurs like below.
Please take a look at my progress and let me know if I missed anything.
Please refer to the table below for server ...
- 16
0
votes
0
answers
106
views
Does anyone have a default PostgreSQL pg_hba.conf and pg_ident.conf File for postgres version 15?
I've messed my pg_hba.conf file up, and I kinda reinstalled 3 times and i opened the pg_hba.conf for version 15 there was nothing inside. so yeah.
if someone has pg_ident.conf file for postgres ...
0
votes
0
answers
62
views
pg_upgrade failing { pg13.7 -> pg15.6} using pglogical method
I am trying to upgrade PostgreSQL (RHEL 7) from version 13.7 to 15.6 using pglogical.
My Standby(destination) machine has following rpms,
postgresql13-pglogical-3.7.16-1.el7.x86_64
postgresql15-...
- 1
0
votes
0
answers
24
views
Install multiple PostgreSQL silent
Hopefully a simple question.
If I Install PostgreSQL x64 Version 15 like this
Process process = new()
{
StartInfo = new ProcessStartInfo
{
...
- 11
1
vote
1
answer
80
views
PostgreSQL: Why "select into" is faster than "select count" from the same sourcetable?
After upgrading PG from 11 to 15, I struggeling with the following situation:
old (working) Setup:
Postgres 11 PostGIS 2.5
Setup:
Postgres 15 PostGIS 3.3
table_1 (~14.000.000 rows)
When I do select ...
- 72
0
votes
0
answers
62
views
Why am I getting seq scan with RLS but not with equivalent query? Hierarchical data, filtering on user + relatives
I'm working with an architecture where users have hierarchical relationships. User100 could have a parent of User50 and a grandparent of User1. User100 would need to be able to see records owned by ...
1
vote
2
answers
37
views
PostgreSQL: `row_alias is null` and `row_alias is not null` inconsistent return value
I have stumbled upon a peculiar phenomena in an SQL select:
select 1
, ui as row_value
, ui is null as "ui is null"
, ui is not null as "ui is not null"
from user_info ui
where ui....
- 13
1
vote
1
answer
41
views
In postgres, how know which query is slow for each schema
I use postgres schemas for a multi-tenant setup:
public
company_a
company_b
I need to see which queries x company are slow, is possible to do this directly with postgres?
P.D: Also, to see which ...
- 16.2k
0
votes
1
answer
181
views
ERROR: functions in index expression must be marked IMMUTABLE in Postgres 15
On pg_restore Postgresql throws an error on index creation:
pg_restore: error: could not execute query: ERROR: functions in index expression must be marked IMMUTABLE
Command was: CREATE INDEX ...
- 1,043
2
votes
1
answer
76
views
Postgres SQL UDF Optimization (Not Inlining?)
I have a problem where I need to round timestamps representing end dates to the end of the current month or end of the prior month depending on where those timestamps fall relative to NOW() assuming ...
- 117
2
votes
0
answers
92
views
Duplicate records are getting inserted in table violating the primary key constraint in postgresql 15.0
I'm using 15.0 version of PostgreSQL. I am facing a strange issue where duplicate data is getting inserted in table violating the primary key. Where as not all the query is showing the duplicate ...
0
votes
2
answers
205
views
Why current_setting() value is undefined within the exception block
After setting the configuration parameter with set_config() I can read it with current_setting(). But if exception ocured and I try to read value within the exception block it is undefined.
do $$...
- 28
0
votes
1
answer
104
views
Self-join query optimizations
I have a table, dev_base_low which stores that have several fields but for this example we will focus on the key fields, from, to, carrier, type, v_type, goods_category. The table contains 4,000 ...
- 5,434
0
votes
1
answer
55
views
OroCRM Enterprise Edition - version 5.1 Setup
I am trying to evaluate OroCRM (OroCRM Enterprise Edition - version 5.1 - latest stable) in an on premise virtual machine. It seems there is no Virtualbox VM for OroCRM, only for OroCommerce. I then ...
- 1
0
votes
0
answers
60
views
What is use case where we can see the benefit out of having witness node in the PG cluster
I have a postgres high availability cluster setup like 1 Primary, 1 Standby in same location (w.r.t repmgr config file)
now i added witness node with in the same location.
while im testing a test case ...
1
vote
2
answers
86
views
Optimizing simple but slow query with OR condition
Some background:
I'm running the following simple select statement on Postgres 15.3 server with 128GB memory, and contrary to my belief it takes ~6 minutes.
The statement in involve two relations, ...
- 378
0
votes
1
answer
35
views
How to create a variable which stores a function result within another function in postgresql?
I’m a big noob.
Function B is part of the body of function A.
Inside a function A, I would like to create a variable to store the function B result in postgresql, and to use that result as a ...
- 3
0
votes
1
answer
39
views
problematic read commited behavior - what should i expect?
small simplified question regarding isolation levels using wildfly, hibernate jpa, jta with isolation level READ_COMMITED. assuming the following scenario:
Thread 1, on table SomeTable, using JPA ...
- 133
0
votes
0
answers
71
views
How to quickly query similar text through postgresql?
I need to query the top few texts that are most similar based on the input content.
The table structure is as follows：
create table documents (
id bigserial primary ...
- 21
0
votes
1
answer
186
views
Postgres: optimizing join with a many-many relationship table
I have a query I'm trying to optimize and am running into some surprising/perplexing results.
The tables I'm working with are features and areas, each of which have their own ids and geometries.
...
- 1,987
1
vote
2
answers
1k
views
Handling Out of Shared Memory Error in PostgreSQL (container) with 80K Sub Partitioned Tables
I have PostgreSql 15.3 running as a docker container.
My docker run configuration is
-m 512g --memory-swap 512g --shm-size=16g
Using this configuration, I loaded 36B rows, taking up about 30T between ...
- 3,480
-1
votes
1
answer
176
views
Unable to use Postgres VER_15_5 using AWS CDDK python
While trying to upgrade minor version AWS Aurora Postgres from 15.3 to 15.5, I am getting below error.
AttributeError: type object 'AuroraPostgresEngineVersion' has no attribute 'VER_15_5'. Did you ...
- 247
0
votes
0
answers
182
views
how to download modules to use with plpython postgresql
I'm trying to use plpython3u with postgresql on windows 11, and I want to use the requests library to make http requests..
So I did this test function:
CREATE OR REPLACE FUNCTION teste()
...
0
votes
0
answers
561
views
An I/O error occurred while sending to the backend postgresql
Just so you understand my case:
Recently I was trying to install plpython3u on postgresql 15, and with a lot of struggle I managed
I set the python environment variables (both for system and user) and ...
0
votes
0
answers
212
views
ERROR: could not load library plpython3u postgresql
I'm trying to install the plpython3u extension in my postgresql with this command: CREATE EXTENSION plpython3u;
mas eu recebo este erro:
ERROR: could not load library "C:/Program Files/PostgreSQL/...
1
vote
0
answers
673
views
How to join a failed node(primary) as standby in #repmgr automatic Failover #Postgresql-15
I have recently setup a automatic Failover configuration using #repmgr in PostgresSQL-15 using 3 nodes (1 primary and 2 secondaries) with repmgrd i'm able to achieve #automaticFailover when the ...
- 11