What's the deal with Deno? We talk with a major contributor to find out. Listen now.

Questions tagged [postgresql-12]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
5
votes
1answer
823 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 ...
5
votes
1answer
468 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 ...
4
votes
1answer
144 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
53 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....
3
votes
3answers
515 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
67 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
562 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
30 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
255 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
109 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
268 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
27 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
37 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
194 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
2answers
37 views

Using NEW on tables with generated columns

I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially ...
1
vote
1answer
463 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
382 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
124 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
87 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
147 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
40 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
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
490 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
576 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
1answer
28 views

PLPGSQL: Cannot use parameters inside a function query

I'm trying to create a function that returns all events with the corresponding week number, and the day of week the event is occurring using crosstab and series generator. I have tested that the ...
1
vote
2answers
22 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
33 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
60 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
40 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
20 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
22 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
15 views

How can I determine what's a better configuration for my app? [closed]

I'm currently using Postgres 12 on AWS RDS and am using their default configuration for the database. I use a gem called PgHero that advises me to use PGTune to improve the efficiency of the database. ...
0
votes
1answer
38 views

Create GIN index in a schema with the pg_trgm extension

I'm migrating entities from the public schema into a new one, let's call it myschema. Originally, the pg_trgm extension was installed and the index was created in a schema migration with: CREATE ...
0
votes
1answer
33 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
28 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
36 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
46 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
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
495 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
77 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
30 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
911 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
0answers
20 views

postgresql server, can connect with SSL, permission denied

I installed and used a Postgresql server a while back and have been using it with Postgis. I would like to use my letsencrypt certificates with the server so i followed this article: https://medium....