Questions tagged [postgresql-10]

PostgreSQL version 10

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

Diff in idx_tup_read and idx_tup_fetch in postgres pg_stat_all_indexes

I have gone through documentation and previous stackoverflow questions but not findings those helpful. Postgres version 10.6 Even for index only scans values of idx_tup_fetch is increasing. So I am ...
0
votes
0answers
31 views

postgres is choosing wrong index even with STATISTICS 1000

I have a table test_monika with 300k records. Simple query is not using index test_monika_rank_idx instead it is using primary key index test_monika_pkey. Actual rows returned are 1. The planner with ...
0
votes
1answer
25 views

PostgreSQL skips WITH query

Consider this CTE: WITH division_by_zero AS ( SELECT 1/0 ) SELECT 42 It returns 42 instead of raising an error. How can I force PostgreSQL to evaluate the SELECT 1/0?
0
votes
0answers
12 views

Postgres 10 - Targeting schema's function on a multiple schema Database

I'm running PostgresSQL 10, and I have several schemas on my DB with multiple functions. I've created a schemaless script with all the functions on it (I've removed the schema prefix), with this, ...
0
votes
0answers
143 views

postgresql: Invalid data directory | Can't open PID file /var/run/postgresql/10-main.pid (yet?) after start: No such file or directory

This error is showing after running this command: sudo systemctl status postgresql@10-main.service postgresql@10-main.service - PostgreSQL Cluster 10-main Loaded: loaded (/lib/systemd/system/...
1
vote
0answers
51 views

Postgresql Database leaking t99_8989898 files in db directory or db size is showing incorrect in postgres

postgresql version is 10 \l+ showing db size to be 7228 GB i checked all tables sizes those comes out to be 60 GB only using SELECT relname AS "relation", pg_size_pretty(...
-1
votes
2answers
14 views

casting custom string to date in SQL

I have a column with strings such as 2015-W02, meaning the second week of 2015. I would like to convert it to a date (e.g., corresponding to the Saturday of that week). It does not seem that CONVERT ...
0
votes
1answer
27 views

How to get the last non-null value that came before the current row

Say I have the following table: create table tab (ts integer, a integer); insert into tab values (1, 1), (2, 2), (3, null), (4, null), (5, 3); When i query the data there may be some nulls: select * ...
1
vote
1answer
15 views

How to define a parameter that will be used as an IN argument inside a function?

I want to make a function that receives two parameters. The first one represents the salaries from a group of employees, and second one, the codes from a group of departments. Both, P_IN_SALARIES and ...
1
vote
1answer
101 views

Passing parameters for dynamic SQL in a function

I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In ...
0
votes
0answers
51 views

PostgreSQL 10 - create partitions on column that needs to be calculated after importing a csv

I am in the situation where I need to create a table which also has a time column. This table is filled with data taken from a CSV, but the csv has no time column. Only after importing the data inside ...
0
votes
1answer
26 views

Give alias / column definition list to ROWS FROM()

This obviously doesn't work: SELECT regexp_matches[1], regexp_matches[1] FROM ROWS FROM ( regexp_matches('fooBarBaz', '[[:upper:]]', 'g'), regexp_matches('fooBarBaz', '[[:lower:]]', 'g') ); Error:...
0
votes
1answer
145 views

Why is there no max(uuid)/min(uuid) function?

Why can I use a UUID to sort rows: SELECT uuid_nil() ORDER BY 1; But I cannot compute the maximum value: SELECT max(uuid_nil()); [42883] ERROR: function max(uuid) does not exist Hint: No function ...
0
votes
1answer
19 views

VACUUM FULL restored very small amount of disk space

I have a large table with ~130m entries. I recently removed unnecessary entries and it should be left with ~60m. I ran VACUUM(FULL) [table_name] and after it finally finished it restored only 2GB of ...
0
votes
0answers
15 views

PostgreSQL: Migrate Tablespace from Instance/Cluster A to another Instance/Cluster B (different Instance-/Clustername)

I'm looking for a solution to directly migrate (move) a PGS-10.x Tablespace from one Instance (let me give it the name) "Tea" to another Instance "Coffee". You may ask why I do not ...
1
vote
1answer
16 views

Postgres partition avoiding redundant column

Say I have a table, say project_data with lots of rows where the data may be grouped by a project_id integer column, and partition it onto one table for each project_id value. Do I then need the ...
0
votes
0answers
101 views

Deleting child record doesn't delete foreign key reference

I am using PostgreSQL 10.7. I have this strange occurrence where I fail to see the problem. Whenever I delete all child-records referring to a parent, somehow the reference between these two tables ...
2
votes
1answer
55 views

Should I duplicate columns between tables to speed-up aggregations like SUM? [closed]

I have two tables in PostgreSQL 10.12 database: waste_card (id (PK), user_id, manufacture_date, transfer_date, address_id, card_type, .. (and other card_specific_firelds) wastes (user_id, ...
0
votes
1answer
17 views

want to use logical replication with a postgresql10 cluster (that is not using logical replication)

We presently have an active/standby postgresql 10 cluster (based on PAF/clusterlabs). Anyways, the servers are kept in sync using: wal_level = replica This is fine and working. However, we would like ...
0
votes
2answers
38 views

how to change priority in ILIKE

I have this simple table and user records. I just want to get users with ilike or something else to get better order in PostgreSQL 10.12. CREATE TABLE users( ID INT PRIMARY KEY NOT NULL, ...
2
votes
2answers
27 views

How to order some rows one way, and the rest another way?

I am trying to sort rows from a table in PostgreSQL 10.12: CREATE TABLE example ( id serial PRIMARY KEY, val text NOT NULL, x integer NOT NULL, y integer NOT NULL ); INSERT INTO example (...
3
votes
0answers
45 views

Logical Replication/Foreign Data Wrapper Connectivity between Two Aurora Serverless Databases (PostgreSQL 10.7)

I have two Aurora PostgreSQL Serverless databases running in the same region and have PostgreSQL version 10.7. I'm wondering if logical replication is available? The official docs say that it's ...
2
votes
2answers
73 views

Group by time interval and output the source and destination station_id and count

I am stuck with a query: CREATE TABLE public.bulk_sample ( serial_number character varying(255), validation_date timestamp, -- timestamp of entry and exit station_id integer, ...
0
votes
0answers
14 views

Postgresql : UNNESTING & NESTING

I have below jsonb in table. '{ "Owner":[ { "Edgeid":4, "Weight":40, "EdgeColor":"Black" }, { ...
0
votes
1answer
232 views

postgresql : **ERROR: path element at position 1 is not an integer:**

I am trying to update a field value in jsonb column with text value present in another column but getting some syntax errors; not getting any solution. i am trying to swap values of OutgoingVertexid &...
0
votes
0answers
28 views

After upgrading from Postgres 9.2.4 to Postgres 10.5 query is 170x slower

We recently upgraded our Postgres database from 9.2 to 10.5. We noticed a somewhat odd behavior with a query that under the same circumstances became 170x slower. The query does a join between 2 ...
2
votes
1answer
62 views

Function accepts only certain passwords in postgreSQL

I have created a function in a postgreSQL(v10.10) database to create new users or update existing ones. As parameters you pass the username, password, role memberships, first name and last name. In my ...
0
votes
1answer
42 views

left join query running too long

I have two tables, test1 and test2, and these tables have around 600M(70GB) and 10k(2MB) records, respectively. when I run a query with left join on site_id, it is taking too long, even though site_id ...
2
votes
2answers
318 views

42702 'column reference “id” is ambiguous' for RETURNING of table-returning PL/pgSQL function

This works: CREATE OR REPLACE FUNCTION sql_fun() RETURNS TABLE (id UUID) AS $$ INSERT INTO foo DEFAULT VALUES RETURNING id $$ LANGUAGE SQL; SELECT * FROM sql_fun(); This doesn't: CREATE OR ...
0
votes
1answer
20 views

Flip a flag from one row to another in a single query

Given this table: CREATE TABLE rollouts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), device_id TEXT NOT NULL, is_active BOOLEAN NOT NULL DEFAULT ...
0
votes
1answer
34 views

How to ensure only a single row with a certain value

Given this table: CREATE TABLE rollouts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), device_id TEXT NOT NULL, is_active BOOLEAN NOT NULL DEFAULT ...
1
vote
2answers
177 views

Why xlog checkpoint was started?

Consider the following config shared_buffers = 20GB max_wal_size = 2GB min_wal_size = 1GB checkpoint_completion_target = 0.7 checkpoint_timeout = 5min and checkpoints' log 2020-05-25 16:28:37.128 ...
0
votes
0answers
21 views

High RAM usage during Postgres PITR

We're currently running Postgres 10 in our infrastructure with Barman managing weekly full backups and holding onto the WALs. Because of the size of our DB, the WAL volume, and resource constraints ...
0
votes
1answer
93 views

Postgres Server staying in read only mode after a barman recover command is executed

All, I am trying to perform a PITR restore using barman using recover command. The problem is that the server remains in the read-only state once the barman recover is done. Is there a way by which ...
0
votes
1answer
44 views

Kill concurrent materialized view refresh PID

Concurrent refreshes of materialized views don't have a record in pg_stat_activity, other than the initial (~immediately completing) query to start them. Is there some other way to obtain the PID, in ...
1
vote
2answers
36 views

postgres includes listed column in sort key and thus fails to use index

This is a query that occurs reasonably frequently: SELECT DISTINCT id, published_year, first_author FROM publication ORDER BY published_year DESC NULLS LAST LIMIT 10; There is an index that could ...
1
vote
0answers
30 views

Locking rows on top of a subquery

Consider the following query: select qq.id from ( select id from log where create_date <= now() - interval '1 month' limit 1000 ) qq order by qq.id for update; Docs say: When a ...
0
votes
0answers
169 views

PostgreSQL: “PANIC: out of memory”

Got an error while running an application connected to a PostgreSQL DB (10.12). All the connections got disconnected, looking through the server logs, I found the below. TopMemoryContext: 292488 ...
0
votes
0answers
17 views

Posgres 10 database occupied more extra disk space

I executed select datname, pg_size_pretty(pg_database_size(oid)) from pg_database in my Postgres 10 database. It gives that one of my database occupied 165 GB memory as result. And When I executed ...
0
votes
0answers
410 views

How to create partition on existing table in PostgreSQL10?

How can we create partitions on the existing table which has not defined with the portion key when it is created? I have one large table and it has 1B+ records and 600GB in size. Are there any new ...
0
votes
1answer
82 views

Stop logging INSERT INTO statements in PostgreSQL (but keep SELECT)

Using logging_collector = on and log_statement = 'all' (from postgresql.conf), currently my log files are correctly populated with every single query that has been executed. This works great. Except.....
0
votes
0answers
13 views

Why repmgr didn't failover automatically?

Installation - name: Install repmgr repository get_url: url: https://dl.2ndquadrant.com/default/release/get/10/rpm dest: /var/lib/pgsql/repmgr - name: Install repmgr rpm shell: bash /var/...
0
votes
1answer
12 views

In AWS Aurora's performance insights. What qualifies as “Other” and how do I investigate it?

I have a disproportionate amount of queries that are marked as "Other" in performance insights with no additional details. AWS doesn't seem to provide any details about what this means, and asking on ...
0
votes
0answers
26 views

Avoid table bloat while using read-replica in Postgresql 10.x

I have 100+ tables in the Primary server, in that one table (work queue table) is relatively small and it won't grow more than 20MB (~200K rows) at anytime until unless there is no huge bloat. The ...
0
votes
1answer
31 views

How can I find the cause of pg_catalog.pg_attribute constantly being vacuumed?

autovacuum seems to constantly run against pg_catalog.pg_attribute for the past week+. This seems pretty odd since we aren't doing constant table changes. How can I understand why it's doing this and ...
0
votes
0answers
28 views

PostgreSQL 10 not archiving some WAL files

I have a strange case in some PostgreSQL that I monitor. First the software versions and setup: Windows Server 2012 Foundation PostgreSQL 10.10 x64 archive_command = 'copy %p "D:\\backup\\wal\\%...
0
votes
0answers
33 views

Can't create schema from `assumed` role with ALL privileges

For the postgresql 10 have this code: CREATE DATABASE featurestore WITH OWNER admin; CREATE ROLE editor WITH LOGIN PASSWORD XXXX NOINHERIT NOCREATEROLE; CREATE ROLE fstore_migrations WITH NOLOGIN ...
1
vote
1answer
157 views

Cumulative Sum over 12 months by Postgreql 10

I am new to window functioning in postgresql. I have 10 columns in my data set and i want to do the running total based on the MM-YYYY but keep fail to get the correct amount. I tried to manual ...
0
votes
1answer
238 views

How to force stop PostgreSQL process?

I edited a PostgreSQL config file: vi /var/lib/pgsql/10/data/postgresql.conf I didn't save it successfully then it created a .swp file. Everytime I try to edit this file again, the terminal will ...
0
votes
1answer
270 views

Postgres - default date format when interpreting strings

What is the default data format that postgres uses when confronted with a character representation of a date? These SQL statements all work, and the character string is always correctly interpreted ...

1
2 3 4 5
7