Questions tagged [postgresql-11]
PostgreSQL version 11
210
questions
0
votes
0answers
16 views
Postgres insert fails silently
I have a table with about 7000 rows defined
CREATE TABLE public.cage (
cage_id serial NOT NULL,
location_id int4 NOT NULL,
"name" text NULL,
lon numeric(11,8) NOT NULL,
lat numeric(10,8) NOT ...
0
votes
0answers
10 views
PostgreSQL Slave unable to catch up with the Master
We have a four node Patroni cluster setup wherein one of the nodes is unable to catchup with the leader because of huge lag. I have tried the below steps but none of them are working.
Restart the ...
0
votes
0answers
25 views
Postgresql, joining most recent row from one table to another based on dates
We have several tables we have to bring together to match the most recently effective price with the transaction date. The simplified query is shown below:
SELECT t.transaction_id,
price.*
...
0
votes
1answer
36 views
Run DELETE on each INSERT to enforce row limit
Using PostgreSQL 11.
Consider a table like
CREATE TABLE "logs"
(
"id" INTEGER NOT NULL,
"userId" INTEGER NOT NULL,
"timestamp" TIMESTAMP ...
2
votes
1answer
37 views
Why does PostgreSQL accept junk when parsing an INTERVAL
psql (11.8)
Type "help" for help.
public=# select '!@#$%^&*()00:00:00.01<>?<>:'::interval;
interval
-------------
00:00:00.01
(1 row)
Seems to take just about anything......
0
votes
0answers
8 views
postgresql logical replication sync frequency / interval
I've been trying to find something in the docs about frequency / interval of sync between subscriber and publisher under the logical replication model of postgresql
Can this be specified or does it ...
0
votes
1answer
8 views
Problem calling Postgres function
I wrote this function:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
returns table(hour text, average numeric, minimum numeric, ...
4
votes
1answer
76 views
What is the correct way to make an exact copy of a database with pg_dump?
Basically, I'm trying to make a SQL dump that will make an exact copy of a database: user defined functions, relationships, constraints, tables, data, etc.
Is this doable with pg_dump or does psql ...
0
votes
1answer
17 views
In which tables values will be inserted if I insert values into a view that its result is made out of inner join of 3 tables?
Ι have the following view:
CREATE OR REPLACE VIEW public.my_view as
SELECT
col1,
col11
from
table_a JOIN table_b on table_a.id=table_b.a_id
join
(
select id,col56 from table_c where ...
1
vote
1answer
31 views
Same postgres query in two different instances with the same data but with different times
I have the same postgres query running in two different instances restored with the same dump file:
one instance in aws rds => https://explain.depesz.com/s/USMO ('PostgreSQL 11.10 on x86_64-pc-...
0
votes
0answers
10 views
PostgreSQL: How to backup only One Schema from a database and restore it to other schema having same name of database? [duplicate]
I have a database named "A" which has three schemas "B","C" and "D". I want to take backup of schema "C" and restore the data to Schema "B"?...
0
votes
1answer
31 views
How does Postgres decide if to use additional workers?
I have the following table with an added BTREE-index on "captured_at".
CREATE TABLE datagram
(
id bigserial NOT NULL,
src_re integer ...
1
vote
1answer
24 views
Having regex_matches exclude cases where both capturing groups match a 0-length string
I'll trying to write a regex pattern that will help me create an array of length 2 arrays such that each inner array is matched like so:
I'm looking to split sequences that begin with a positive or ...
1
vote
0answers
32 views
Postgres row estimate is very low for hash join
I have a query that keeps thinking that the result of a hash join will be 1 and I don't know which knob to turn to fix it.
The query takes a form field values in a revision of a document and maps them ...
0
votes
1answer
16 views
How I can check whether 2 databases despite being in different postgresql servers of versions are the same?
I copied an existing posttgresql database from one server to another. The source of my data is a postgresql 9.6 whilst the newer server is in postgresql 11.
So I want to know how I can check that both ...
0
votes
1answer
27 views
Multicolumn GIN index not fully facilitated by query planner
I am experiencing unexpected behaviour with ProstgreSQL 11.10 query planner with multicolumn GIN index usage.
After defining an index on two columns (a,b) of a large table and executing a query ...
1
vote
1answer
56 views
PostgreSQL undocumented timenow() function?
Looking at the documentation of PostgreSQL I couldn't find anything on timenow(). Yet if I call the function it works.
So what is the difference between now() and timenow() ?
I am going to guess that ...
0
votes
0answers
17 views
Full text search with mispelled greek words fails to retrieve the epxected results
I have the following table named search_index:
id PK SERIAL
key UNSIGNED INTEGER
value TEXT
And over the table I ηαωε τηε following values
id | key | value
--- | --- | ---
1 | 1 | 'χαλανδρι'
2 ...
0
votes
0answers
62 views
pg_basebackup: COPY stream ended before last file was finished
I am unable to add a replica member back into the cluster.
Setup: Haproxy, patroni, consul, postgresql11
Cluster: Leader, standby, 3 replica(all 3 failed in adding back to cluster)
I could see the ...
2
votes
2answers
147 views
Postgres 11: Query plan uses seq scan after upgrade
The Situation
We have a database hosted on RDS with a few hundred tables, a few of which are quite large.
We recently upgraded the database from 9.5.22 to 11.8 and performance is significantly ...
0
votes
1answer
105 views
Unable to upgrade to postgresql 13
I'm getting the following error:
postgres@:~/>cat loadable_libraries.txt could not load library
"$libdir/plpython2": ERROR: could not access file
"$libdir/plpython2": No such ...
0
votes
1answer
24 views
How can I trace the cause of a memory spike in Postgres?
The title tells the story, but how can I trace the cause of a memory spike in Postgres 11.10? As you can see from the screen shot below there was a memory spike all the way up to 32 GB which caused ...
0
votes
0answers
29 views
Is it possible to record read access to a row?
A table has a creation timestamp column with DEFAULT and an update timestamp column with DEFAULT ... ON UPDATE .... Similar to the ON UPDATE|DELETE ... constraint, we're looking for a way to have a ...
0
votes
0answers
15 views
How do you aggregate values from jsonb_array_elements into a separate list grouping by another column?
I've got a psql table. Two of the columns are:
Table "public.messages"
Column | Type | Collation | Nullable | ...
0
votes
1answer
22 views
Using activity over last few days to label user as “active”
Here's an example dataset.
with activity_cte (day, user_id, act1, act2) as (
values
('2020-01-01'::date, 1, 0, 1),
('2020-01-01'::date, 3, 1, 0),
('2020-01-02'::date, 1, 3, ...
0
votes
0answers
172 views
Error that role of the previous master user does not exist when restoring a database to a different server with different master username
I have to move a postgres database to another server. Both servers are Postgres 11.8 on Amazon RDS. I'm dumping the original jira database like this:
pg_dump \
--dbname=postgresql://postgres:***@...
1
vote
1answer
122 views
PostgreSQL extremely slow counts
I'm experiencing extremely slow count(*) speeds, inside a table with 5845897rows, counting the number of rows takes around 2 minutes (113832.950 ms).
EXPLAIN ANALYZE SELECT COUNT(*) FROM campaigns;
...
1
vote
0answers
14 views
How I can find whitch reports use views that are generated from specific tables?
I have the following table named reports:
report_code PK VARCHAR 5
report_sql TEXT
The report_sql stores the sql code that generates a report. The sql code is in postgresql flavor. So from that ...
0
votes
2answers
31 views
search path is getting updated automatically
In postgres 11.5 for a running session suddenly lot of errors reported in pg_logs "relation does not exist". After enabling extra logging we have found out that just before the errors the ...
0
votes
2answers
64 views
What SQL execution time should I expect when querying 4k records in a 150M records table?
We currently have performance issues with a SQL query on RDS Postgres 11.8. Here is the query:
SELECT id FROM answer_submission WHERE student_id = 3983137;
The table "answer_submission" has ...
1
vote
0answers
64 views
Reduce Count(*) time for many records [closed]
Query:
EXPLAIN ANALYZE select count(*) from product;
ROWS: 534965
EXPLANATION :
Finalize Aggregate (cost=53840.85..53840.86 rows=1 width=8) (actual time=5014.774..5014.774 rows=1 loops=1)
-> ...
5
votes
1answer
99 views
Determine Nodes in Network with PostgreSQL
I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes ...
0
votes
0answers
43 views
How I can find the most resource-intensive queries that has ever run in my database and use a specific table?
In my database I want to check whether my idea on an improvement upon table schema works. Therefore I want so look which are the heaviest select queries ever run into our database and use a specific ...
2
votes
0answers
38 views
Check constraint on a pair of columns
I have a table with this structure:
create table licence_report
(
id bigint default nextval('licence_report_id_seq'::regclass) not null,
created ...
0
votes
1answer
35 views
PostgreSQL:- How to continue execution even INSERT INTO SELECT FROM fails and how to work with refcursor as out param with other output params
In this post i am asking 2 questions (with my limited knowledge of PostgreSQL and PLPGSQL)
I have a function inside which there is a query that inserts records to a table using INSERT INTO SELECT ...
0
votes
1answer
59 views
Encryption Test
I'm using PostgreSQL-11 and I've been given a text string to decrypt as a test. I've got the details of how it was encrypted and its very simple. When I try to decrypt using select pgp_sym_decrypt(...
0
votes
1answer
45 views
PostgreSQL update column in one table with value from another, based on key from first table
Sorry about the title, I wasn't sure how best to word this. My situation is this:
I have two tables:
paper_author [paper_id, author_id, author_name]
authors [author_id, author_name]
Right now, the ...
1
vote
1answer
849 views
ERROR: insufficient columns in PRIMARY KEY constraint definition
I recently upgraded the database from PostgreSQL v9.6 to v11.7. we have some partitioned table with inherence and planning to migrate them to the declaration.
Table DDL:
CREATE TABLE c_account_p
(
...
1
vote
1answer
54 views
Postgres Query not using Index but faster with Index
Sorry if this seems to be a duplicate question. I'm using Postgres 11.6 on AWS RDS. I have 2 tables:
CREATE TABLE public.e
(
id character varying(32) COLLATE pg_catalog."default" NOT ...
0
votes
2answers
184 views
PostgreSQL : FATAL: could not open log file “/path/to/log/filesystem/postgresql-Sun.log”: No such file or directory
I am new to PostgreSQL and stuck on an issue that PostgreSQL isn't starting up. It reported:
FATAL: could not open log file "/path/to/log/filesystem/postgresql-Sun.log": No such file or ...
0
votes
0answers
33 views
SELECT * FROM small_table executes quickly, returns very slowly in PostgreSQL 11
I have a small table (~2500 rows, 37 col) being served by Postgrest API. After months of <1sec response times, it's now up to over 20seconds despite the table not being much larger.
EXPLAIN ANALYSE ...
0
votes
1answer
331 views
Could not resize shared memory segment error
I have a PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) running in a docker container.
I get the following error with some queries:
could not resize shared memory segment "/PostgreSQL.860708388" ...
-1
votes
1answer
333 views
Minor Downgrade of PostgreSQL from 11.9 to 11.7
I have installed the PostgreSQL 11.7 version using yum install on our RHEL 7.7 Development server. Now, when I tried to install PostgreSQL using yum install on our new PRD server, by default, it ...
0
votes
0answers
22 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
0answers
21 views
Trigger having issues on partioned table execution - Postgres - 11
Postgres - Trigger having issues on partitioned table execution
I am using postgres 11 version
I am trying to execute a trigger on partitioned table with customized function and trigger function,
that ...
1
vote
1answer
127 views
Why did adding a self-referential foreign key freeze the table?
I'm running PostgreSQL 11.5. I have a simple table of registers.
create table register(
id serial primary key,
name text not null
);
This table is small (~6000 rows), relatively heavy read (100s ...
1
vote
1answer
23 views
Subtract column value from date in days or months
I want to subtract the value in column lag_days from current_date.
Usually I would do current_date - interval '1 day'
I'm getting syntax error on the following example to subtract by days:
dkarchive=&...
1
vote
1answer
45 views
Function that loops through array parameter values to build multiple WHERE clauses (postgres 11.4)
I have been iterating on an idea presented by Rob Conery in his excellent post to generate monthly reports using PostgreSQL Views.
My version required the View to be refactored into a Function as I ...
1
vote
1answer
358 views
Why are my queries in idle state?
I'm new to postgres , I've aws rds instance running postgresql with engine version 11.5.
All my queries are clientRead has the wait_event. Why do I have all my queries in idle status.Does this mean ...
0
votes
0answers
19 views
Table as an Index?
PostgreSQL 11
Requirements:
Uniqueness must be guaranteed (not duplicated)
MUST not lose any data.
The data I get is NOT uniquely identified, so I am left with semantic comparison of the values to ...
