Questions tagged [postgresql-13]
Specifically for PostgreSQL version 13
110
questions
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 ...
- 102
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 ...
- 1
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 ...
- 102
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: ...
- 1
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 ...
- 101
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, ...
- 123
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 ...
- 41
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, ...
- 113
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';
...
- 153
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 ...
- 101
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 ...
- 157
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 ...
- 883
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 ...
- 157
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
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 ...
- 111
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 * ...
- 41