159 questions
3
votes
3
answers
113
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:...
- 33
0
votes
0
answers
24
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 ...
- 141
0
votes
0
answers
65
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,026
0
votes
1
answer
313
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
103
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 ...
- 576
-1
votes
2
answers
148
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 ...
- 47.2k
1
vote
1
answer
384
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.2k
0
votes
0
answers
110
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
2k
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
257
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,327
0
votes
0
answers
86
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
126
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
313
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
2
votes
1
answer
476
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,700
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 ...
- 31
1
vote
0
answers
294
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,673
0
votes
1
answer
850
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
83
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 ...
2
votes
1
answer
584
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
101
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
61
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 ...
- 79
1
vote
2
answers
41
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(...
- 149
0
votes
0
answers
23
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 ...
1
vote
0
answers
199
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 ...
- 11
1
vote
1
answer
525
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 ...
- 26
0
votes
0
answers
27
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
97
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
1
vote
2
answers
43
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
69
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.3k
0
votes
1
answer
335
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,093
2
votes
1
answer
110
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 ...
- 149
2
votes
0
answers
122
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
356
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 $$...
- 38
0
votes
1
answer
105
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,474
0
votes
1
answer
87
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
1
vote
2
answers
191
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, ...
- 398
0
votes
1
answer
58
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
51
views
Problematic read commited behavior - what should I expect?
I have a 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, ...
- 143
0
votes
0
answers
75
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 ...
- 23
0
votes
1
answer
281
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.
...
- 2,013
1
vote
2
answers
4k
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,620
-1
votes
1
answer
259
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 ...
- 221
0
votes
0
answers
269
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
835
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
453
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
1k
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
0
votes
0
answers
134
views
Postgres database in Ubuntu Terminal different from that in Sql Shell and pgAdmin
I am a newbie coder so it's probably something I've installed wrong but I have a bit of a strange issue. I am attempting to run a Java Spring Boot backend application. I am using the Linux WSL ...
1
vote
2
answers
974
views
setting Postgres options during a pg_restore
I am finding it difficult to find the best way to set Postgres options for a pg_restore session only. It would be nice to be able to do this in the environment in which I run pg_restore and not change ...
- 21
2
votes
1
answer
1k
views
PostgreSQL15. ProcSignalBarrier deadlock in DROP TABLESPACE
I creating a tablespace
create tablespace test_tablespace location '/path/to/tablespace/dir';
Next, I create a table and fill it with data
create table test_table (id numeric, file_name varchar) ...
- 21
0
votes
0
answers
290
views
How can I reinstall Postgres to change locale for which it was installed?
I am really new to Postgres so sorry if it an easy/stupid question.
I switched from Postgres 10 to 15 by doing the following:
Installed PostgreSQL 15
Stoped the 15 cluster and drop it
Upgraded 10 ...
- 1