Questions tagged [postgresql-12]
The postgresql-12 tag has no usage guidance.
56
questions
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 ...
