Questions tagged [postgresql-12]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
5
votes
1answer
697 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 ...
4
votes
1answer
123 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....
4
votes
1answer
45 views

VERY slow lateral join on relatively small database

My database consists of apps and their reviews (schema below). I'm trying to answer the following question: Given a series of dates from the earliest reviews.review_date to the latest reviews....
4
votes
1answer
370 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
3answers
454 views

Query slow when using function in the WHERE clause

This is fast (49ms): v_cpf_numerico := ext.uf_converte_numerico(new.nr_cpf); select cd_cliente into v_cd_cliente from central.cliente where nr_cpf_cnpj = v_cpf_numerico; This is slow (15 seconds): ...
3
votes
1answer
60 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 ...
3
votes
1answer
466 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) ...
3
votes
1answer
29 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
2answers
200 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
2answers
92 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 ...
2
votes
1answer
215 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> ...
2
votes
1answer
28 views

In a Postgres function, how to skip expressions if input value is null?

I have a working function. But it only works if I literally supply both the city and country variables. CREATE OR REPLACE FUNCTION gridref_by_address(city text, country text) RETURNS SETOF ...
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 ...
2
votes
1answer
35 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
148 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 ...
1
vote
1answer
243 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
100 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 ...
1
vote
2answers
75 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 ...
1
vote
1answer
128 views

Postgresql - sequences bigger than bigint

PostgreSQL sequences (CREATE SEQUENCE) are limited to bigint (which is usually big enough). But in my case I need a bigger sequence. The table will not have this much records, it will be cleaned over ...
1
vote
1answer
31 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 ...
1
vote
1answer
345 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 ...
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 ...
1
vote
1answer
306 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
2answers
466 views

Why isn't it possible to cast to a timestamp in an index in PostgreSQL?

I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. ...
1
vote
2answers
21 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-...
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 ...
1
vote
0answers
50 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
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 ...
1
vote
0answers
17 views

Understanding PostgreSQL Execution Plans

I'm trying to understand the motivation of PostgreSQL 12 query planner here. Database Structure CREATE TABLE subjects ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE, ); CREATE TABLE ...
0
votes
1answer
19 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
1answer
21 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
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
30 views

Why is ALTER faster than UPDATE (for full table rewrites)?

Let's create a garbage table.. CREATE TABLE foo AS SELECT x::text FROM generate_series(1,20e6) AS t(x); Time: 14077.654 ms (00:14.078) Now, let's look at boring UPDATE, UPDATE foo SET x = 'foo'; ...
0
votes
1answer
25 views

Combining table under different alias

I have two tables trips and users. Whenever an user posts a trip, it stores id of the user as foreign key. And while fetching, I am joining those two tables the following way. Query: SELECT title, ...
0
votes
1answer
26 views

How do I loop through every table and alter the PK column like so? (Postgres)

Using psql in Postgres 12, I need to alter every column in every table of my database that's: int/bigint not null primary key to: identity column auto-increment with default value starting at ...
0
votes
1answer
35 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
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
19 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 ...
0
votes
1answer
272 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. ...
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
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 ...
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
1answer
788 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 ...
0
votes
1answer
15 views

SQL - Find Results with MAXimum for given colum

I have a question on how to select best match in PostgreSQL for a given result/condition: I have a setup containing 3 tables. 1 is a result overview table with reliability and status| 2 is a ...
0
votes
1answer
14 views

Stopping cascading logical replication

Given two primary PG12 servers, A and B, let's setup logical replication from A to B. A --> B Both servers can and do receive writes, and don't use sequences or unique constraints so there is no ...
0
votes
0answers
17 views

PostgreSQL functions for creating and dropping declarative partioning

I created function for creating declararative partitions. CREATE OR REPLACE FUNCTION createpartition(table_name varchar(25), forecast_id integer) RETURNS varchar(30) AS $$ DECLARE partition_name ...
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
1answer
16 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
26 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 ...
0
votes
1answer
25 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 ...