Questions tagged [postgresql-12]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
1answer
5 views

Why is a GiST index used for filtering on non-leading column?

I always learned and understood that an index can only be used when we have predicates for the leading (or all) columns. Now, to my surprise, I noticed that a GiST index is used in the following query....
0
votes
1answer
24 views

faster ALTER COLUMN

Is there a method to do an ALTER COLUMN in postgres 12 on an huge table without waiting a lifetime? I try to convert a field from bigint to smallint : ALTER TABLE huge ALTER COLUMN result_code TYPE ...
0
votes
0answers
24 views

Deleting specific array element [closed]

In PostgreSQL 12.3 im having this issue while trying to delete a specific element, here's the example with dummy data: Table Lists: GuildID (bigint) | items (jsonb) 123456 | [{"name&...
-3
votes
0answers
17 views

How to install postgresql-client-12 on ubuntu [closed]

similar to this question, but for version 12: How to Install postgresql-client-11 on Ubuntu 18.04 the answer to that question can be modified simply by changing the version: sudo apt-get install curl ...
0
votes
1answer
34 views

Aggregate rows for each group of rows

I am trying to solve a problem which is quite easy to solve in a procedural language, but I fail to solve it in SQL in an efficient manner. Let me first explain the problem. I have a series of events ...
0
votes
1answer
15 views

Create partial index with single value

I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-...
0
votes
0answers
24 views

PostgreSQL efficient lookup for 2 column combinations

I have a PostgreSQL (v. 12) database storing match results. I have integer columns team_home and team_away which have foreign key constraints for team_id in table teams . My question has a few ...
1
vote
1answer
28 views

Query performance degraded after upgrading Postgres

I have a table with several million records in a PostgreSQL 12 database, and after an upgrade from 11 to 12, a few queries started performing horribly. They went from taking ~1 second to ~5 minutes. I ...
2
votes
1answer
33 views

Function in same Session become increasingly slow when run multiple times concurrently

PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit Client is Data Grip and same behavior from my report server which uses ...
2
votes
1answer
114 views

merging many jsonb objects using aggregate functions in PostgreSQL?

Is there a standard function in PostgreSQL (as of 12.x) to concatenate or merge many jsonb objects in a database column into a single jsonb object? I know there is a the || operator since PostgreSQL ...
0
votes
1answer
20 views

Stop pg_restore from returning false errors?

How do you stop pg_restore from returning an error code if it encounters a meaningless non-error like “schema "public" already exists”? I'm trying to automate a database transfer, and ...
0
votes
1answer
18 views

What is the impact of a large number of boolean columns in a table on storage and index performance in PostgreSQL?

I have a lot of boolean attribute for entities in a table and am in need to store even more of them. Currently I have 17 such attributes (Other fields are four bigint fields). I am in need of adding ...
0
votes
0answers
18 views

Will PostgreSQL hash all columns

I have a large PostgreSQL database running on a server with limited memory. I often use inner joins on only one (unique) column as a way of filtering (is this bad practice?). I’ve noticed that the ...
0
votes
1answer
15 views

slow wal replay in backup postgres12 server

We have the following PG12 setup with Master, replica and readonly replica with archive. master and replica both has 2cpu, 4gb ram readonly replica with archive has 2 cpu, 2gb ram below you can ...
0
votes
0answers
10 views

Mismatch of postgresql versions

I installed rh-postgresql12 in my server. These are the steps I used to install and enable it and its dependencies : Installation : yum -y install rh-postgresql12 rh-postgresql12-postgresql rh-...
4
votes
1answer
305 views

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
3
votes
1answer
54 views

Pre Caching Index on a large table in PostgrSQL

I have a table with about 10mln rows in it with a primary key and an index defined on it: create table test.test_table( date_info date not null, string_data varchar(64) not null, data ...
0
votes
1answer
20 views

Inheritence and Primary Key Constraints

I am currently trying to see how the object oriented features come in play in Postgres. During a few tests I noticed that when you insert in or delete from inheriting tables that those changes also ...
0
votes
0answers
8 views

Losing connection to the server when using PG Routing function pgr_alphaShapes

I am running PG Routing v. 3.0 on Postgresql 12.2 and the pgr_alphaShapes function is giving me a lot of trouble. Following the documentation here, I passed a geometry and a spoon radius. SELECT * ...
0
votes
1answer
19 views

Manually trigger “Write current results to disk” during query

Using PostgreSQL 12, I have the requirement that some data needs to be "refreshed" each night. That means: The database has to do some calculations based on the current date and write the result to ...
0
votes
1answer
18 views

How query errors affect Postgresql DB performance

I have an application stored measurements data in PostgreSQL-12. Couple of processes write data by queries like: INSERT INTO "<measurement>" (<columns>) VALUES (<values>); But for ...
0
votes
1answer
654 views

pgAdmin IV and PostgreSQL 12.0: ERROR: schema “public” already exists

I have a database backup from pgAdmin III with PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg16.04+1) on Ubuntu 16.04 LTS. I would like to restore this database on my pgAdmin IV with PostgreSQL 12.0 on Docker ...
1
vote
2answers
20 views

Postgres not using index when expression in WHERE condition

The table my_table has an index on the column timestamp. The following query makes use of this index: explain select item_id, timestamp position from my_table t where t.timestamp > '2020-05-...
3
votes
1answer
28 views

Postgres 12: Are there performance differences between PRIMARY KEY(A, B) and PRIMARY KEY (B, A) on a table PARTITION BY (A)?

Given a table this table: CREATE TABLE tbl ( a int not null, b int not null, created timestamp not null default now(), primary key(a, b) ) PARTITION BY LIST(a) The query plan for a lookup on ...
1
vote
1answer
251 views

PostgreSQL nondeterministic collations are not supported for LIKE

I am using Postgresql v12. I created a collation like this: CREATE COLLATION ci (provider = icu, locale = 'tr_TR', deterministic = false); I used that collation in a table: create table testtable1 ...
2
votes
1answer
26 views

Are C++ vectors in a user-defined aggregate function a POD for Postgres?

The official postgres documentation (docs) states in section "37.10.11. Using C++ for Extensibility" that If calling backend functions from C++ code, be sure that the C++ call stack contains only ...
0
votes
1answer
43 views

Custom Aggregate Postgresql C extension with input as initial condition

Is there a way to write a custom aggregate Postgresql C extension function that takes an input from the user and sets it as the initial condition? If so, how? I’ve written several Postgresql ...
0
votes
1answer
17 views

multi column index order for order by

I have created the following multi column index CREATE INDEX ON message_log (website, in_use, insert_timestamp); However when I perform the following query, it does not seem to be using the query ...
1
vote
1answer
32 views

How to create date ranges of events coming from an API where open state represents the date range and close state the gaps between the ranges?

I would like to store date/time ranges in PostgreSQL of some arbitrary events that have a state of open or closed and a date/time of when the state changed. Events coming from an API will have the ...
0
votes
0answers
64 views

How to efficiently do partial updates on huge dataset in Postgresql

Disclaimer: I am a total "newb" in regards to PgSql, but due to some unfortunate aligment of circumstances I am in charge of this project which I have to rewrite/migrate away from SqlServer (alone), ...
0
votes
0answers
145 views

Postgresql 12.2 “Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed” on windows 10

I get this error whenever I try to install Postgresql 12.2 "Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed". I followed ...
0
votes
1answer
149 views

FATAL 53300: Remaining connection slots are reserved for non-replication superuser connections

I have a PostgreSQL 12.1 database system (I'll refer to it as PGSQL) running on a remotely hosted VM server (Windows Server 2019). We upgraded the server OS and PGSQL a couple of months ago. ...
1
vote
1answer
165 views

Handling performance problems with jit in postgres 12

tl;dr: What is the best way to handle performance decrease caused by jit? Background: Lately I have migrated from postgres 11 to 12 and noticed, that some queries / procedures run considerably ...
1
vote
1answer
209 views

How to see unclosed PostgreSQL connection while using psycopg2? [closed]

I run python code: con = psycopg2.connect(some_dsn) cur = con.cursor() cur.execute(some_sql) res = cur.fetchone() #con.close() <-- connection is not closed Then I try: ps auxf | grep postgres ...
1
vote
1answer
22 views

Stop rollback of anonymous DO code block?

I'm executing some code through this anonymous DO code feature. I am observing that whenever it fails, all changes made are discarded and the database is rolled back to a state as it was before it ...
0
votes
0answers
20 views

Dynamically creating a new table from a given table name without return type using a PostgreSQL function?

I want to try using PostgreSQL to create a function which given a table name, an existing column name, and a new column name, it selects all data from this table, applies a function to this column ...
2
votes
2answers
75 views

Is PostgreSQL function parallel safety intended for functions which update data?

I am having some trouble understanding function parallel safety. I can understand what it does - which is to specify whether the function will be executed by the parallel master node or whether the ...
1
vote
1answer
77 views

Extract and combine multiple values from a jsonb column

I am looking to extract multiple values from a jsonb column in Postgres, and am running into an issue where some values are coming back null. fiddle Setting up schema: create table jsonb_test (test ...
0
votes
1answer
25 views

Benchmarking set up over HDD+SSD combination? [closed]

I have a Linux system set up (Arch) on an HDD. I have been trying to benchmark some of my queries and found some disk IO bottleneck, and thus opted to move the entire PostgreSQL database from the HDD ...
0
votes
1answer
53 views

Reducing CREATE TABLE overhead?

I have a simple SQL query: SELECT * FROM table; - a simple sequential scan - which takes me 10s. When I add CREATE TABLE AS (CTAS syntax) to it, i.e. my query is CREATE TABLE db_test AS SELECT * FROM ...
0
votes
2answers
71 views

update the timestamp on column when data is filled in another

I am new to Postgresql and SQL in general. I have a table that looks like this id (primary key)|temperature|temperature_timestamp|humidity|humidity_timestamp|...|parameter|parameter_timestamp I ...
1
vote
0answers
42 views

When does the PostgreSQL 12 CTE materialized option provide an optimization advantage?

I have been reading about PostgreSQL's new CTE feature - the MATERIALIZED or NOT MATERIALIZED keyword - which can offer additional optimization opportunities under certain circumstances, provided that ...
0
votes
1answer
22 views

Enforcing lowest transaction isolation level across concurrent sessions?

I am trying to learn transactions better in PostgreSQL and came across this observation. I have a situation where I am creating multiple tables using an SQL statement across multiple sessions. I am ...
0
votes
0answers
46 views

Create expression index for XML query

Schema create table soa_rawdata_lp ( date_created timestamp with time zone default now(), filename varchar(128), xmldata xml ); Each xmldata entry is about 4.2MB size. Currently ...
0
votes
0answers
21 views

Grant READ ONLY permissions to all tables in all databases [duplicate]

I created new role named "support" in my PostgreSQL. Now I need grant "READ ONLY" permissions for this role an ALL exists databases/tables. Also I need automatically granted same permissions on each ...
3
votes
1answer
397 views

Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling

My application currently uses PostgreSQL 11.6. Today, I have tested PostgreSQL 12.1 on a virtual machine and the results were shocking: One important query which takes 100ms on version 11 (same VM) ...
0
votes
1answer
44 views

Logic rules representation and storage in DB column

I have two kind of "elements". One that generates time series (lets call them generator element) and the other that based on some logic rules applied to the time series became "activated" (activable ...
-1
votes
1answer
27 views

Postgresql: LIKE and partition by fields of the initial table

I have an already existing table which I want to partition, looking at the documentation I see that this cannot happen, so I re-created the table and try to partition that: CREATE TABLE tableB (LIKE ...
1
vote
0answers
30 views

PostgreSQL - Variables on transaction-level

Do PostgreSQL have variables at transaction level? If not, is it possible to implement something like that relatively easily? Unfortunately, I am still relatively inexperienced with PostgreSQL and ...
0
votes
1answer
29 views

How to split one table into two in a single statement?

I've got an old table I want to split into two, but I'd rather not run two near-identical queries across the whole thing. The following does not work because you can only return column from the table ...