Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
16,389
questions
1
vote
1
answer
44
views
How to handle deadlocks while multiple batch inserts?
I need suggestions/ideas of handling a issue with deadlocks.
I'm having multiple batch inserts commands to same table:
insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, ...
- 111
1
vote
0
answers
24
views
ST_SnaptoGrid not working properly
I want to perform ST_SnaptoGrid on a line but the result is very strange. I tested this line of code on 3 separate postgis sessions and the results of all of them was the same:
SELECT ST_AsText(...
- 13
3
votes
1
answer
96
views
PostgreSQL Query Planner overestimating Row Count leads to slow Hash Join
I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred ...
- 31
0
votes
1
answer
40
views
Connecting to AWS RDS succeeds via terminal but fails in pgAdmin 4. Why?
I can establish an SSH tunnel using ssh -L 2222:<RDS-Endpoint>:5432 ec2-user@<EC2-IP> -i ~/.ssh/key.pem and then connect to my RDS using psql "host=localhost port=2222 user=postgres ...
- 103
0
votes
1
answer
28
views
How can I avoid writing a loop when allocating orders to a stock impacted by the orders?
I have three tables in my database representing product storage, order items, and shipments:
CREATE TABLE storage (
product_id INT PRIMARY KEY,
storage1 INT,
storage2 INT
);
CREATE TABLE ...
- 113
1
vote
0
answers
19
views
Writing .spec files for PostgreSQL isolation tests
I am developing a PostgreSQL extension for which I would like to run some tests with concurrent connections. If I understand the documentation correctly, I should be able to do so using the PGXS ...
- 624
1
vote
1
answer
52
views
creating several records from one, based on a numerical value stored in a field of the source record
In a PostgreSQL db, I have a table with a numerical field, among others
In such field, some natural numbers are stored and they represent the number of occurrences of something
like this, roughly
...
- 113
0
votes
0
answers
49
views
connect to mssql with pgloader
I'm trying to convert some data from MS SQL Server to Postgres from a Windows 10 WSL partition.
From the Ubuntu prompt on WSL, I can run the following with no issue:
~$ sqlcmd -C -S tcp:localhost,1432 ...
- 229
0
votes
1
answer
26
views
What is the proper isolation level needed in this postgres query with concurrent transaction?
I have 2 concurrent transactions
on table X with the following column
id
count
and
table Y with the following column
id
somefield
currently there is only 1 row in tableX
id=1
count=0
and a million ...
- 101
-4
votes
1
answer
37
views
Find people absent from the country for a certain time
I have a table that has the "document number" field, the type of flow (entering or leaving the country), date of birth, and the date the flow occurred.
Knowing that a person can leave or ...
- 1
0
votes
0
answers
24
views
How to filter extraneous Unicode values from a column?
I am cleaning up / priming our PostgreSQL 12 database for future data-related activities (e.g. data encryption). I have tried the following methods to delete non-basic Latin / basic accented Latin / ...
1
vote
1
answer
32
views
Column list used by the publication does not cover the replica identity. Cannot Update Table - Logical Replication - Postgres 15
We're doing a Postgres native logical replication between two databases.
Now, we're getting an error trying to update from the publisher one of thousand tables that we have.
When we try to update the ...
1
vote
1
answer
41
views
Postgres: deadlock when ATTACH PARTITION to a table with a lots of INSERTs
I have a partitioned table user_stay which has many INSERTs and UPDATEs.
db=> \d user_stay;
Partitioned table "public.user_stay"
Column | ...
- 113
0
votes
0
answers
20
views
Alter ownership of a table in a schema does not work
create role schema_owner;
create role table_owner;
Current signed in role is postgres
create schema test;
grant schema_owner to postgres;
grant table_owner to postgres;
alter schema test owner to ...
- 1
0
votes
0
answers
14
views
Setting indexes right
Given following SQL query
SELECT dd.* FROM devices_deviceconfiguration_sensors dds
JOIN devices_latest_config dlc ON dlc.config_id = dds.device_configuration_id
JOIN devices_deviceconfiguration dd ON ...
- 127
0
votes
1
answer
31
views
Convert PostgreSQL array type
Given an enum type (e.g. letter).
What is the syntax to convert an existing array to the enum type? The existing values are valid for the enum. For example, how would I convert letter_data in:
create ...
- 103
1
vote
0
answers
33
views
Queries containing ORDER BY and LIMIT started to work slowly
I have a legacy system that uses Posgresql 9.6 and Ubuntu 16.04. Everything was fine several days ago even with standard Postgresql settings. I dumped a database with the compression option (maximum ...
- 111
0
votes
0
answers
35
views
PostgreSQL Optimizer does not find best solution when specifying subset of keys in query
PostgreSQL (Version 15) does not find a (in my opinion) obvious optimization for the query plan, is there a way to help the optimizer with this problem?
I have a specific kind of query where I have to ...
- 9
0
votes
0
answers
26
views
Create index on array of composite types
I have this example
create type price_schedule_day as enum ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday');
create type price_element as
(
price_start ...
- 417
0
votes
0
answers
23
views
PostgreSQL 9.5 Create Publication?
Based on the official doc, CREATE PUBLICATION command is available on PostgreSQL 10 and later. However, I have a requirement to replicate PostgreSQL 9.5 to BigQuery using datastream, which require us ...
- 101
0
votes
1
answer
39
views
Optimizing PostgreSQL Query Using Composite Index and GIN Index
I have a PostgreSQL query that involves multiple conditions and joins. While I've created a composite index to speed up some of the filtering, I'm still experiencing extra rows being fetched due to ...
- 213
1
vote
0
answers
47
views
macos ventura postgres 15 remote connections server closed the connection unexpectedly
macos ventura 13.5.1 postgresql (15.4 (Homebrew))
Remote connections for the server are closed "unexpectedly". I have done all the steps that are needed for postgres to be happy with remote ...
- 13
0
votes
1
answer
26
views
Accessing NEW.* keys dynamically in trigger in PostgreSQL
How you can access the NEW.* RECORD keys dynamically?
For example here I'm trying to trim spaces:
CREATE OR REPLACE FUNCTION trim_spaces() RETURNS TRIGGER AS
$$
DECLARE
_a HSTORE = hstore(NEW);
_k ...
- 131
1
vote
0
answers
18
views
postgresql sub query
I have three tables
kit - contains kit_id and kit_type (kit type is with android or IOS)
appointment - contains appointment_id, created_datetime, kid_id, created_by_id
user - contains user_id, user ...
- 11
0
votes
1
answer
33
views
Slow deletes in Postgresql with foreign key constraints [duplicate]
I have a number of tables relating to an entity and delete them in a transaction to make sure all are removed simultaneously:
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
delete from table1 where entity_id = ...
0
votes
0
answers
20
views
Is there a way to see which rows a postgres query has scanned or modified for any statement?
I'm working on a tool for postgres and trying to find a way to see what rows a given query scans or touches. Ideally, I'd like to get back a list of tables and the primary keys of the tables that the ...
- 1
0
votes
1
answer
28
views
Efficient Way to Determine Records Modified Since Point in Time
Scenario
I have a data pipeline that is streaming to a postgres cluster. I have no control over the streaming data source, but full ownership of the destination. I intend to have processes that poll ...
- 103
1
vote
1
answer
65
views
Postgres GENERATED ALWAYS performance when adding new column
I have a fairly big PostgreSQL 13 table that I need to add a new TEXT column and with this new column I would like to add a GENERATED ALWAYS column with the following definition:
ALTER TABLE my_table
...
1
vote
0
answers
34
views
How to make a condition in where clause as void
I have a function in Postgresql where we have a parameter uuid[]. I basically have 2 requirements.
uuid[] I would like to make it as optional
In the where clause if uuid[] is null or not passed then ...
- 11
0
votes
0
answers
12
views
Detect Delete/Insert/Update in MatView Refresh Concurently
I have a matview which I refresh once in a hour.(50M rows). By running
refresh materialized view concurently my_view;
Note - MatView contains a unique Index too, on column unique_id.
I have a table ...
- 101
0
votes
0
answers
13
views
understand clog fetch
https://speakerdeck.com/macdice/improving-the-slru-subsystem?slide=29
contact author, get the C function source code.
CREATE FUNCTION test_clog_fetch(x1 pg_catalog.xid,
...
- 407
0
votes
0
answers
23
views
Optimizing Performance of Highly Nested Postgres SQL Query
I have been working with a PostgreSQL database and I've encountered a SQL query that seems to have a high degree of nesting, which I suspect might be impacting its performance. I'm seeking advice on ...
- 101
1
vote
1
answer
29
views
View with which role a PostgreSQL setting was changed
Does PostgreSQL record under which user a settings change was carried out?
For example, view if user "postgres" changed the wal_level, or "userA".
If so, how/where can the log be ...
- 69
0
votes
1
answer
26
views
Postgres keeps forgetting my data
I'm fairly new to Postgres, having spent years with MySQL/MariaDB. I have a Postgres 14.9 database for development with two schemas in it. When I lose power, both of the schemas still exist in the ...
- 143
0
votes
1
answer
45
views
Optimizing PostgreSQL Query with DISTINCT ON, Filtering, and Ordering
I'm working on optimizing a query for a PostgreSQL database used in an educational platform.
The query retrieves distinct question_id values along with their is_seen status from the ...
- 213
1
vote
0
answers
16
views
How to Transfer 100GB database from GCP to AWS
I have a 100GB database hosted in GCP's Cloud SQL offering in us-east1
I'd like to transfer this to AWS lightsail databases and hosted in AP-SOUTHEAST-2.
Within AWS I'm using AWS Lightsail Databases ...
- 111
0
votes
0
answers
27
views
Sort by the 3 top highest values in a column (with time frame filter)? Postgresql
I am getting the following rows when I perform this query:
SELECT *
FROM statetable
where "machineName" ='Main01'
ORDER BY statetable_ndx DESC LIMIT 100
machineName
state
stateDuratio
...
- 21
0
votes
1
answer
44
views
How does MVCC work under read committed isolation level in Postgres
Let's say I have two transactions A and B.
Transaction A starts first. then transaction B starts, inserts some data, and commits.
So from transaction A (not committed yet), I can see data were ...
0
votes
0
answers
20
views
5k ms Planning time for 2k rows table
I have a table that has 44 columns and slightly less than 2000 rows. Also, the data are all text or integers. Texts don't go longer than a few characters. Table size is about 1900kb. All my other ...
- 1
2
votes
0
answers
38
views
Why does the same query require more "Heap Fetches" when run on my postgresql replica?
I have a Postgres 13.10 database running on AWS RDS, with a primary instance and a replica. The replication is managed by AWS - it's not logical replication I manage myself.
The replica is configured ...
- 311
0
votes
0
answers
15
views
Why can't I migrate to a specific postgres database?
I am currently evaluating if it makes sense to migrate my existing, self-hosted MariaDB database to a managed PostgreSQL database in the cloud. For this task I am using the tool pgloader version 3.6....
- 101
0
votes
1
answer
69
views
Optimal way to get a total count of rows in a paged query in Postgres?
I need to improve the performance of a paged query for customer orders in a Type2 Postgres db (always insert a new record with a new ID, the newest ID is the current version of the record). Changing ...
- 207
0
votes
1
answer
40
views
postgresql - recovery_target_action not working with recovery_target_time
For the recovery, I am using the following block:
restore_command = 'cp /mnt/backup/%f "%p"'
recovery_target_time = '2023-08-18 10:01:46'
recovery_target_action = 'pause'
But the recovery ...
- 550
0
votes
0
answers
13
views
changing the schema of incoming data for postgresql
I'm currently working on connecting a few large google sheets into postgreSQL. My current system is that the sheet gets extracted & loaded to postgreSQL, I'd then do the transformations ...
- 1
0
votes
0
answers
11
views
Materialized view creation times out after too many creation/deletion with the same name
I was trying to create a MV on a Postgres db, on a table with a few millions records.
During the development process, I migrated & rolled back (CREATE MATERIALIZED VIEW / DROP MATERIALIZED VIEW) a ...
- 101
0
votes
0
answers
55
views
Optimizing query performance
I have this query that is not performing well
explain analyse
WITH RECURSIVE
user_orgs AS (
select o.id from organisation_admins oa JOIN usr_organisation o ON o.id = oa.id where oa....
- 127
0
votes
1
answer
34
views
PostgreSQL: count or sum for every 1 hour interval
Hello I am using this query which gives me the results shown in the picture:
SELECT "t_stamp", "Total_Parts"
FROM myTable
WHERE "MachineName" = 'Main01'
AND "Quality&...
- 21
0
votes
0
answers
13
views
One to one Modeling?
What do you guys think of this model?
Catalog -< Catalog Items | - | Products
and
Catalog -< Catalog Items | - | Services
That is, a Catalog Item, can be one and only one type: Product or ...
0
votes
0
answers
19
views
How to install Docker and Postgres on AL2023
What I want to do:
Install Docker and use a Postgres image to create a database to use with a Nest.js tut
What i have tried:
I have installed docker using:
yum install docker
Which yields when I do ...
0
votes
1
answer
41
views
Query for converting parent child hierarchy multiple tables to single table (adjacency list)
I have chain of parent child tables for eg.
Table A
id
name
Table B
id
name
parent_a
Table C
id
name
parent_b
I want a query to get all tables into a single table with the following columns, ...
- 103