Questions tagged [postgresql-13]

Specifically for PostgreSQL version 13

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

How do you run "SELECT" on the composite type argument of an SQL function?

Example: https://dbfiddle.uk/bCSwVpd9 Database: CREATE TABLE entities ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY ); CREATE TYPE entity_id AS ( id bigint ); Function: CREATE FUNCTION ...
0 votes
0 answers
7 views

Oracle Reference Partition migration to Postgresql

We are in process of migrating from Oracle 19c to Postgresql v13. We have a table in Oracle which has numerical primary key and is range partitioned based on a date column. This table has 3 child ...
  • 101
0 votes
1 answer
34 views

Is there any impact on PostgreSQL database to run AUTO VACUUM frequently?

I have few(10-15) large table it is about 5GB to 30GB. And rest of them less than 1GB. Some tables data is huge and where daily 5lakh(500000) dead tuples generated. And autovacuum start almost 7 days ...
0 votes
0 answers
36 views

Datas on postgress logic replica are not visible

I'm trying to set up logical replication on postgress 13 and have a problem with accessing datas on replica. Publication and subscrinption is set up properly, I can see that successive pg_walls are ...
0 votes
0 answers
23 views

When and how to run VACUUM FULL for large table at PostgreSQL13?

I have a table "transaction_history" which is near about 35GB. Continuously data is inserting and updating on this table. Since table become larger day by day that's why I archived daily ...
1 vote
1 answer
40 views

How to perform dummy multi-inserts with generate_series()?

Given the tables like this: https://dbfiddle.uk/Z8hOhnYG CREATE TABLE accounts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ); CREATE TABLE profiles ( id bigint GENERATED ALWAYS AS ...
0 votes
0 answers
29 views

Too many client connection at PostgreSQL

I have 500 connection in my 3 node PostgreSQL cluster. But sometimes I found error like "too many clients already" Why it show too many client connection? How does it calculate too many ...
0 votes
0 answers
20 views

pg_cron timezone issue, unable to run at specified time

Unable to run at proper timezone. I want to run two things bash scripts Stored procedure For both case I found it run at different time. Instead of 1 AM and 3 AM pg_corn runs another time. Why it is ...
0 votes
0 answers
18 views

Unable to delete data from dynamically assigned table at PostgreSQL13

Unable to delete from dynamically assigned table data at PostgreSQL13. Basically I want to archive 10 days older PostgreSQL data to another table. I want to pass days, source table and target table as ...
0 votes
1 answer
15 views

Postgresql showing different behavior on two environment having exactly same configuration

We have two environment staging and production. For Database service we are using AWS RDS(postgresql). SELECT * FROM addressfabric_v2_2022_7 WHERE ST_Intersects(ST_GeomFromText('POLYGON((-75.7891846 ...
0 votes
1 answer
13 views

Got message: FATAL: expected SASL response, got message type 88

I have a strange situation. A small program working fine till these days (about half a year). The program (NodeJS) is creating a local connection to DB: 1st: creating new connection to local db. 2nd: ...
0 votes
1 answer
81 views

How to recover Postgresq data from $PGDATA/base files after system crash

I was working on my Ubuntu machine and left it on to grab food. Upon returning, I found it had shut itself down. It was not power failture because I have a home backup system and lights never go off ...
0 votes
0 answers
40 views

Postgres - Debugging slow query

we are using postgres 13.7 My company runs a fairly complicated analytical query, unfortunately, I'm not allowed to disclose the query here. The explain plan has around 30 nodes and joins about 10 ...
  • 135
2 votes
2 answers
94 views

trigram indexing for LIKE ALL filters

I find myself looking for a way to dynamically restrict search results so that all searches must be present: ["A TE","SOME"] search array: "MS A TEXT B SOMETHING" --> ...
0 votes
1 answer
98 views

How to get slow query on PostgreSQL?

Basically I want to log those query which is taking more than 300ms to execute. I don't want to log those query which is less than 300ms. But at PostgreSQL showing all type of log whether it is below ...
0 votes
0 answers
27 views

Suddenly TPS is too high like 90K to 3000K but active session is 250

My DB application connected with pgBouncer and pgpool Node info: 3 Pgbouncer 3 pgpool 3 dbnode (1 is primary 2 is standby) For pgbouncer: default_pool_size=200 ,reserve_pool_size=10 For pgpool: ...
0 votes
0 answers
18 views

why could not get records using rust diesel in PostgreSQL

I am using rust diesel diesel = { version = "1.4.8", features = ["postgres","64-column-tables","chrono","serde_json"] } to do a full text search, now ...
  • 527
0 votes
1 answer
6 views

why the full text search still slow after install the zhparser

I have already installed the zhparser to split the chinese words, then I using this sql command to do a full text search: SELECT * FROM article a WHERE to_tsvector('dolphinzhcfg', title) @@ ...
  • 527
0 votes
1 answer
42 views

Is there a way to get the IP address of a client virtual machine in Postgresql?

I can get the IP address if the Postgres connection is done from the 5432 port. Now If I connect Postgres with 6432 port means the connection comes from pgbouncer at this time I'm not getting the IP ...
0 votes
2 answers
73 views

Reassign ownership and drop old owner in PostgreSQL

I have got a PSQL 13.6 database that was originally created by owner_1, and has been populated over time. For external reasons, I now need to periodically change the owner account of the database and ...
  • 103
2 votes
2 answers
85 views

count(*) queries are too slow even with an index only scan

We currently have a PostgreSQL table that stores audit logs similar to the following: id portal_id created_at action caused_by_id caused_by_type 1 1 2019-09-19 09:39:48.827924 user-logged-in 1 User ...
  • 129
2 votes
1 answer
105 views

PostgreSQL ESCAPE Quoting on TAB-delimited files: MySQL works, Pg doesn't, and I'm confused

I'm working with some SEC data, which is available in very regularly formatted tab-separated files. On MySQL, I can import these files as so: CREATE TABLE import ( adsh text, tag text, version text, ...
2 votes
3 answers
68 views

What will happen to dirty data, if PostgreSQL DB or server abnormally shutdown?

Suppose my PostgreSQL instance suddenly abnormally stopped and data is dirty and not saved in RAM or WAL. In that case what will happen ? Where from I can recover that data? and also if server also ...
3 votes
2 answers
563 views

Postgres - can't drop constraint because objects depend on it even though it is duplicate

I have a situation that I am running into after upgrading my Postgres from 10.7 to 13.6. I have flyway scripts that date over several years that build up the history of the database. The changes to ...
  • 141
0 votes
0 answers
34 views

PG 13.6 : Data corruption error not being sent to syslog

This morning I noticed this error in my PG CSV log file. 2022-05-25 23:59:17.776 JST,,,117110,,628cf2c5.1c976,1,,2022-05-25 23:59:17 JST,9/611296,0,ERROR,XX001,"uncommitted xmin 16395356 from ...
1 vote
1 answer
33 views

Canonical-or-best data type to store a Flake ID in PostgresSQL for two query patterns

I want to generate and write 128-bit k-ordered Flake IDs to a column from clients connected to my PostgreSQL database. These IDs are essentially a large globally-unique numbers that grow with time, ...
2 votes
0 answers
366 views

Postgresql 14 Performance regression versus PG13

I migrated a database from Postgresql 13 to Postgresql 14 recently, and I saw a x2 execution time in a specific case which I can't understand. The issue has been seen on a Postgres on Linux, and I ...
  • 21
0 votes
0 answers
517 views

Foreign Key to Partitioned Table

I have the following basic schema for an ever growing huge transaction table. In order to query speedup & phase out stale data, I need to partition by time & country. CREATE TABLE transaction (...
  • 113
0 votes
1 answer
164 views

How to organize postgresql databases in pgadmin4?

I'm working with postgresql on my local machine with Windows 10, in pgadmin4 I can create new Server Groups and new Servers, if I create 2 new server groups and select localhost for the host, I get ...
0 votes
1 answer
46 views

How to optimized self-join view of 50 millions rows table

I've the following simplified table with ~50M rows. table sample ( id uuid not null primary key, measured_date timestamp with time zone not null, segment_id uuid not null, activity_id ...
0 votes
0 answers
258 views

How to include new key-value pair inside each object of JSON array in Postgres when retrieving records from a table?

I have below two tables in my database schema spec_attribute spec_attr_id spec_attr_name 1 Value1 2 Value2 3 Value3 product_variant_attribute product_id variant_attributes 1 [{"...
  • 133
0 votes
1 answer
646 views

Collation won't set when creating new database

I am trying to set the collation for a new database in PostgreSQL 13 but it does not seem to take effect: postgres=# CREATE DATABASE assets ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'en_US.UTF-8'; ...
0 votes
1 answer
73 views

Converting Query from DB2 to PostgreSQL, Now takes hours to run

I have a project for converting some web pages from using DB2 to the same data/tables/etc within PostgreSQL. I'm running into an issue where a query that would normally run in less than a second for ...
1 vote
1 answer
47 views

Filter table lookup by array value, but returning groupby unfiltered

I have multiple lookup tables and each need to be filtered by an array of values: select array_agg(joined_properties.name) as properties, items.name from items left join ( select properties.name, ...
0 votes
1 answer
17 views

is it possible to replace the window function with custom function

I have a pagination sql with PostgreSQL 13 like this: select *, COUNT(*) OVER () from ( select "article"."id", "article"."user_id&...
  • 527
1 vote
0 answers
48 views

pg_stat_statements vs slow query

Now I want to query the PostgreSQL 13 slow query that take more than 1s. Some told that should open the PostgreSQL slow query log, some recommand to use pg_stat_statements, which one should I choose? ...
  • 527
-1 votes
1 answer
239 views

How to integrate Postgres advisory lock functions into SELECT and UPDATE?

Using Postgres 13 in a Node.js application, I have a simple task to do: SELECT a row (identified by its primary key) Run some application code that cannot be done in the database* with that data. ...
  • 319
4 votes
3 answers
551 views

Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?

In Postgres 13, I have a table which gets updated frequently. However, the update query is rather complicated and uses the same values multiple times. So, using a CTE seems quite a logical thing to do....
  • 319
0 votes
1 answer
198 views

Returning in query whether a column has been changed by the current query or not

I have a query like UPDATE my_table SET my_value = CASE WHEN random() > 0.5 THEN my_value * 2 ELSE my_value END RETURNING *; Now, inside the RETURNING statement I'd like to have a boolean ...
  • 319
3 votes
1 answer
542 views

Performance penalty for COUNT(1) OVER (PARTITION BY NULL)

In my application server, I would like to paginate a dataset using LIMIT and OFFSET, and additionally return the total count of the dataset to the user. Instead of making two remote calls to the ...
0 votes
0 answers
68 views

postgres - why does ALTER COLUMN run out of space?

Why does this POSTGRES command run out of space?..... ALTER TABLE table1 ALTER column field1 TYPE VARCHAR(250); field1 is currently VARCHAR(200), I need to increase it to VARCHAR(250) but after ...
2 votes
1 answer
162 views

postgresql Recursive Update resulting in Sequential Scan

I have a table with a self-referential foreign key, and I would like to update a given parent, and all of the descendants of the parent, with the same recursive update. When using an UPDATE with a ...
0 votes
1 answer
96 views

GROUP BY date column and then group by custom daytime

So I have to group a table according to the date and then the timeday when a product arrived, the timedays would be: morning = [5, 6, 7 , 8, 9] mid_morning = [10, 11] midday = [12, 13, 14] evening = [...
  • 49
3 votes
1 answer
68 views

Add a new row for each jsonb value pairs

I have this table from were I'm running a query: CREATE TABLE IF NOT EXISTS public.preprocess_things ( preprocess_id integer NOT NULL DEFAULT nextval('preprocess_things_preprocess_id_seq'::...
  • 49
1 vote
1 answer
313 views

UPSERT with UPDATE on variable column gives error "command cannot affect row a second time"

Hey I'm trying to insert or update(if the constraints are duplicated) based on a query result, these are the create table statement: CREATE TABLE IF NOT EXISTS public.inventory ( inventory_id serial ...
  • 49
0 votes
1 answer
183 views

Upsert on a table that has multiple fields and jsonb column using WITH statement

So I was trying to do an upsert based on a query result: /* Querying from this table: id | arrive_date | arrive_location | thing_type | thing_count */ CREATE TABLE IF NOT EXISTS public....
  • 49
0 votes
0 answers
110 views

Postgres13 logical replication degrading slowly

We're halfway through a postgres upgrade (from 11 to 13) and have run into a pretty unusual blocker, being that our 13 logical replica can't seem to keep up with changes from our 13 master. For ...
0 votes
0 answers
139 views

PostgreSQL Constraint Where Value Mustn't be Present in Another Column

I have the following table in a postgres database (version 13) CREATE TABLE public.links ( head_id int4 NOT NULL, link_id int4 NOT NULL, CONSTRAINT links_pk PRIMARY KEY (head_id, link_id), ...
1 vote
0 answers
623 views

Postgresql trigger insert on another table after update and update other tables

I have a post table and when I soft delete a post I want to also soft delete rows on the comment and post_image tables using a trigger. I have an intermediate table deleted_post where I insert the ...
0 votes
1 answer
416 views

Postgres 13: Subscription stuck at initialize state

Postgres version: 13.5 I have 2 sets of publication/subscription between my 2 DBs. One of them is working fine and the other one is stuck at initializing state for all the tables. sourcedb=# select * ...