Questions tagged [postgresql-12]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
12 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
12 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
8 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-...
3
votes
1answer
45 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
18 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 ...
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
27 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 ...
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 ...
1
vote
1answer
145 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 ...
0
votes
1answer
15 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
74 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 ...
1
vote
1answer
90 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 ...
0
votes
1answer
75 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
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
19 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
56 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
56 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
24 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 ...
1
vote
0answers
36 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 ...
1
vote
1answer
126 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 ...
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
2answers
65 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 ...
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 ...
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
26 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
29 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 ...
0
votes
0answers
28 views

PostgreSQL database cluster

I installed PosgreSQL 12 using the certified installer by EnterpriseDB. I went through the docs. In Chapter 18.2. Creating a Database Cluster, it is said that you need to initialize a database ...
0
votes
2answers
72 views

Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?

I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?
4
votes
1answer
225 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 ...
1
vote
2answers
58 views

Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)

I have the following table and indices defined: CREATE TABLE IF NOT EXISTS data ( id serial, job_id bigint NOT NULL, payload jsonb NOT NULL, tags jsonb NULL ); CREATE UNIQUE INDEX IF ...
0
votes
0answers
11 views

posgres instance is listening to old port even after changing to new port number

My machine is having posgres server 11th version,for testing purpose i installed another posgres instance(12 version )in same machine.i changed port number of this new instance to 5433 but when i try ...
3
votes
1answer
307 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
0answers
43 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 ...
2
votes
2answers
126 views

Are there side effects to Postgres 12's NOT MATERIALIZED directive?

I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the ...
2
votes
1answer
137 views

Postgresql query XMLTable with default namespace

I use PostgreSQL 12 to query XML data. My data is as below CREATE TABLE xmldata AS SELECT xml $$ <ROWS xmlns="http://x.y.z"> <ROW id="1"> <COUNTRY_ID>AU</COUNTRY_ID> ...
0
votes
1answer
635 views

Problem Installing PostgreSQL 12.1 Win8.1: “the database cluster initialisation failed”

I'm getting an error at the end of installing PostgreSQL 12.1 which is "the database cluster initialisation failed" I have been searching all the day, so here what I tried: 1- Run the installation ...
5
votes
1answer
514 views

Case-insensitive collation still comparing case-sensitive

I am currently trying to create a table with a text column which will compare case insensitive by default. This is because we have a third party program that executes a search on our database. The ...
1
vote
0answers
82 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
2answers
48 views

Select records that have at least one link to other tables

I want to select all users that have either a post, a photo or a video. I came up with the following query. select users.username from users where exists (select * from posts where users.id = ...
0
votes
0answers
7 views

How to get all variables for the current plpgsql function?

Is it possible to get all the variables of a PL/pgSQL function (including input parameters and DECLAREed variables)? My goal is to collect these variables into a jsonb object with each variable name ...
-1
votes
1answer
716 views

Can PostgreSQL manage a table with 1.5 billion rows?

The table has three columns: id string (up to 400 characters) length which records the length of string The problem was, when I do a query, e.g. select * from table where length = <whatever ...
0
votes
1answer
561 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 ...