Questions tagged [postgresql-11]
PostgreSQL version 11
98
questions
0
votes
1answer
27 views
After bulk load, how to ensure an updated visibility map?
We have a number of tables that have an active version, an inactive version, and a view pointing at the active one. We periodically truncate the inactive table, drop the indexes, load a bunch of data ...
-1
votes
0answers
13 views
WAL and log file on separate disk
I am working on migrating Oracle 12cR1 to PostgreSQL11. I am planning to move WAL log and transaction logs on a separate disk and create a symbolic link in the PGDATA directory. I tried to search a ...
2
votes
2answers
85 views
Select previous value in row without using a subquery [closed]
Say you have a table in PostgreSQL 11 with sets of values something like this below.
12 18
11 12
5 12
9 18
7 19
14 18
11 16
6 12
4 11
5 9
Is there a way to do a select without using sub-...
1
vote
1answer
18 views
PostgreSQL planner over estimates and self join query large table optimization
PostgreSQL 11.5
4 core, 32gigs
250gig SSD
Not using default configuration, tuned to fit system memory and cores.
Table+index size 14Gigs
I'm working on a query that self joins to a table via a ...
0
votes
1answer
22 views
Check if PostgreSQL dump is valid
Suppose a dump is provided from pgdumpall
pg_dumpall > outfile.bkp
How to verify if the outfile is a vaild database file for restoration. Just as a precautionary step before restoring it like
...
1
vote
0answers
15 views
Loading Custom Rules into unaccent postgresql hosted into an amazon aws instance
The default unaccent module has no or little support for Greek Letters using the unaccent module as seen in following links:
https://stackoverflow.com/q/49845063
https://www.postgresql-archive.org/...
0
votes
1answer
20 views
Negative amount of remaining transactions before wraparound
Running a PostgreSQL 11 instance with a table of more than two billion rows.
I get the following output when I query for the remaining transactions before a transaction identifier wraparound happens: ...
1
vote
1answer
36 views
HLL aggregation query is very slow
I have the following query
select
ts_date,
sum(bids_placed),
hll_cardinality(hll_union_agg(eligible_uu))
from
performancedata
where
ts_date>='2019-08-01'
and ...
0
votes
0answers
30 views
Reset all sequences so that they continue after max(id) + 1?
It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like:
django.db.utils.IntegrityError: duplicate key value violates ...
0
votes
1answer
23 views
how to update PostgreSQL array of jsonb
I have the structure like this (json pretty):
[{
"uid": "comment_1",
"message": "level 1 - comment 1",
"comments": [{
"uid": "subcomment_1",
"message": ...
0
votes
1answer
30 views
postgresql recovery.conf file location in 11.5
I'm new to postgres. We need to take incremental backups for postgres databases as well as we must be able to perform PITR. I searched a lot on internet and tried to find out any article referencing ...
1
vote
1answer
52 views
Select / update query doesn't finish and await call on query doesn't get resolved resulting in script to get stuck
We are using postgres 11.4 and nodejs to run sql scripts. We use node-postgres module and its Pool feature, setting it to 10.
Database is running on a server with 4GB total memory. When the script ...
0
votes
1answer
9 views
Reporting table size to grafana
I am trying to report partition metric to influxdb so that it can be visualised in grafana. I have partitioned my table using pg_partman. I want to report the size of the default table that is created ...
5
votes
1answer
47 views
Bad execution plan when having partial index and big In-clause in Postgres
Postgres seems to always use sequential scan where it could have used a partial index to get index scan only. It only happens when an in-clause exceeds more than 100 elements.
Given the following ...
0
votes
0answers
31 views
Postgres uses sequential scan with OR condition, but index scan with AND condition
We are doing a full text search on an object. Unfortunately, the object is normalized across different tables, and the full text search results in a condition with several OR, because the term can ...
0
votes
1answer
24 views
Postgresql - how to list indexes on a table that is partitioned?
This command works for non-partitioned tables:
select * from pg_indexes
But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition.
...
1
vote
1answer
35 views
Unable to alter partition table to set logged
I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command.
Afterwards, I ran an alter table set logged ...
0
votes
2answers
37 views
When to ANALYZE/VACUUM/REINDEX when adding primary keys and indexes
I have a recurring job that creates a Postgres (v11) database from a template and loads it with data. The scale of the data is on the order of 10s or 100s of millions of rows, so for performance ...
1
vote
0answers
32 views
Brand new postgres setup - what to check first?
My sys ops team have recently handed over a brand new set of postgres clusters (prod, stage, test etc). We don't have any DBAs and they are under resourced. Mistakes in config have been found before. ...
0
votes
3answers
136 views
Postgresql 11 - slow insert performance when table reaches ~5 million records
I have a table with ~6 million records that gets loaded on a postgresql instance in a small machine (with standard PC HDD).
It seems to be a I/O problem, since there are free memory and processing ...
0
votes
1answer
45 views
slow count queries - what are we doing wrong?
Using Postgresql 9.6.14, running the following query:
SELECT COUNT(*) AS "__count"
FROM "crm_client"
INNER JOIN "crm_role"
ON ("crm_client"."role_ptr_id" = "crm_role"."id")
INNER ...
0
votes
1answer
32 views
Select with if statement return 'column does not exist'
Database, application.
PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1),
Dbeaver 6.1.1.
What I need?
Return right values.
What I do.
Here is part of code.
select
if(available_count < 5, "neni skladem"...
0
votes
1answer
25 views
Options and best practices for Multiple Result Sets in Postgres11
I want to get two (small) result sets and have the benefit of precompilation and 1 round trip to the database. SQL Server allows a stored procedure to contain multiple select statements.
SELECT u....
0
votes
0answers
14 views
PostgreSQL multi-pivot with keys as column names rather than categories?
I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category.
E.g.
create table pivot (id ...
4
votes
1answer
198 views
Postgresql numeric and decimal is automatically rounding off
CREATE TABLE IF NOT EXISTS ttable (
tcol decimal(9,7)
);
insert into ttable(tcol) values(17.4604786);
the value is getting stored as 17.46
Happens the same if I use decimal/numeric type
I am ...
0
votes
1answer
47 views
`cube` query performance issues with ordering
I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the
But ...
1
vote
1answer
28 views
Use jsonb array inside where IN or ANY in PostgreSQL
I want to select all products that have active shipments (those have one or more shipment_ids in shipments table).
My product table has a jsonb column called data like this:
{
"name": "foo",
...
0
votes
1answer
20 views
CTE vs Function/trigger performance in Postgresql
I have a very long CTE query that I have shortened here for readability. But it tries to insert state, county, city, zipcode and street into their own tables and then insert their ID for inserting the ...
1
vote
1answer
17 views
Getting 2 columns from sets of 2 rows in Postgresql
So I have my data organized as:
id(bigint) | report_item (varchar) | report_value (numeric) | stock_id (uuid) | date
1 | netIncome | 100 | stockid1 | 2019-05-01
2 | totalRevenue |1000 | stockid1 | ...
0
votes
1answer
15 views
Posgresql - deadlock on non related queries
I am using Postgresql 11.
I have found an error similar to following in my logs:
org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 332091 waits for ShareLock on ...
0
votes
1answer
40 views
PostgreSQL >= 10 does not generate WAL if there are no transactions
I noticed that PostgreSQL >= 10 does not generate WAL if there are no transactions.
In previous versions, PostgreSQL generated WAL at the expiration of archive_timout, because it wrote some empty ...
0
votes
1answer
90 views
json_object_agg errors on null in field name
PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same.
I have a schema of pages, each page has sections and each section can have different kind of ...
0
votes
1answer
66 views
PostgreSQL logical replication slave - shouldn't it be read-only and available only for subscription updates?
I have database and table collection_city on one server. It has 21 rows. I have database and table collection_city on a second server. It also has 21 rows.
They both have this row:
tinker=# table ...
1
vote
1answer
37 views
PostgreSQL - do we need to DROP the database and recreate it if we want to RESTORE it?
I worked with many different databases (SQL Server, Oracle, IBM db2) and for all of them if we have a backup it is just enough to execute RESTORE command in order to restore the data.
I am beginner ...
0
votes
1answer
48 views
PostgreSQL - PUBLICATION tries to copy the data which already exists in another database?
I have database and table collection_city on one server. It has 21 rows.
I have database and table collection_city on second server. It also has 21 rows.
They both have this row:
tinker=# table ...
0
votes
1answer
31 views
PostgreSQL - how to list all UNIQUE indexes for specific database?
I would like to get a list of all UNIQUE indexes (for unique column combinations) for specific database in PostgreSQL. I searched but could not find the query for this.
I found these two queries.
...
0
votes
1answer
29 views
How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group?
I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees".
The goal: To setup ...
0
votes
1answer
198 views
Postgres Logical Replication - “replication slot ”mysub“ was not created in this database”
I have two database servers 192.168.150.210 and 192.168.150.220
I created publication and subscription successfully for logical replication.
postgresql.conf file is modified on both servers.
...
-1
votes
2answers
32 views
index for ILIKE on postgresql, inconsistent performance
I have a table with about 64 Million rows, I have created the following index on the address_url column
CREATE INDEX trgm_idx_address_link
ON public.address_link USING gin
(address_url ...
0
votes
0answers
25 views
Does Postgresql logical replication support hot standby?
Postgresql has two ways of replication
Physical replication
Logical replication
with physical replication everything is send over and the servers must have the same major release. Which makes ...
1
vote
2answers
67 views
Postgres: why did this trigram index slow down regexp queries?
I have a TEXT keyvalues column in Postgres:
select * from test5 limit 5;
id | keyvalues
----+------------------------------------------------------
1 | ^ first 1 | second 3
...
0
votes
1answer
33 views
index does not get used when there is “OR” in the query
I have an index on the check_timestamp column, the index gets used by the following query
... where check_timestamp < NOW() - INTERVAL '30 days' ORDER BY check_timestamp NULLS FIRST LIMIT 10
...
0
votes
0answers
39 views
Postgresql: how to create a View combining regular Tables A, B, C and pivoted table D with a json field?
I have created a standard view, VIEW, that includes different fields from different tables:
SELECT e.id AS "Employee Id",
concat(e.first_name,' ',e.additional_family_name,' ',e.family_name) AS "...
9
votes
2answers
489 views
Does SELECT remove dead rows like VACUUM does?
I was fiddling with VACUUM and noticed some unexpected behavior where SELECTing rows from a table seems to reduce the work VACUUM has to do afterwards.
Test Data
Note: autovacuum is disabled
CREATE ...
1
vote
1answer
170 views
Postgres Column Level Security
I have a schema snow within a database testdb. I'm trying to achieve column level security for my users. My users are boss, manager and intern. I set all of this table up as the superuser me. They are ...
0
votes
1answer
31 views
Unstructed data field: Query all values from array of objects by key
I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like:
"nickname": "value"
"fullName": "value"
"office": "value"
"unity": "value"
and a ...
3
votes
2answers
71 views
Breaking out of a recursive query in Postgres 11
I'm having some trouble getting the correct data out of a tree structure, where I only want the first nodes in a branch matching criteria to be the result of the query.
I have this table of domains:
...
0
votes
0answers
5 views
Reconfigure PostgreSQL Installation
On Windows (10), is there any way to reconfigure a PostgreSQL installation, specifically when the --with-openssl option was not used? I'm hoping to avoid having to backup, reinstall, and restore ...
0
votes
0answers
55 views
Select JSON elements from nested documnet
I have a JSON document such as:
{ "type" : "episode", "AssetId" : "urn:episode:ABCDEFG", "_id" : { "$oid" : "12345678" } }
Here is the table definition:
CREATE TABLE "v22"."assets_exp" (
"_id" ...
1
vote
1answer
34 views
In PostgresQL, how do I make a query match exactly one row or fail?
I am making an arbitrary DELETE or SELECT query and I know this should effect or return only one row. How can I ensure that the query doesn't execute and I get some signal that something is wrong when ...
