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
64
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
1
vote
2
answers
101
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
vote
1
answer
379
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
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 ...
0
votes
0
answers
85
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
0
answers
311
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
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
0
votes
1
answer
842
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
579
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
56
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
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
1
answer
520
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
1
vote
1
answer
96
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
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
2
votes
1
answer
108
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
0
votes
2
answers
354
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
83
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
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
0
answers
73
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
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
0
votes
0
answers
265
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
444
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/...
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 ...
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