Questions tagged [postgresql-11]
PostgreSQL version 11
2
votes
2answers
21 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
2 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
23 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
33 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 ...
0
votes
0answers
14 views
PostgreSQL GIN pg_trgm default operator class - Does Not accept data Type uuid
I was trying to create GIN Indexes for an Array that stores uuid data type.
CREATE INDEX IX_Gin_Column1 ON table_name USING gin (column1 gin_trgm_ops);
PostgreSQL raised an error:
ERROR: ...
0
votes
0answers
35 views
PostgreSQL logical replication and partition table
We have a large database and we want a copy of the database in our company for faster access. we are going to achieve this copy by logical replication on PostgreSQL 11.
But we have a big problem.
We ...
0
votes
0answers
23 views
psycopg2.pool.PoolError: trying to put unkeyed connection
I'm trying to get connection pooling to work in my Python Flask web application. The code I am using is as follows:
from contextlib import contextmanager
import logging
from psycopg2 import pool, ...
0
votes
0answers
20 views
index by UDF not used when filtering by subquery column
I have created an index with an expression that calls a user defined function.
I expected that when a subquery selects this expression as a column I could filter by this column and have my index used. ...
0
votes
1answer
26 views
Java driver dont fire query-start probe in postgres 11
While using SystemTap to trace postgres session on PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit.
global livequeries
probe process("/usr/...
1
vote
2answers
55 views
Returning multiple result sets from server-side procedures in Postgres 11
This blog post by a Postgres consulting company mentions that the new server-side procedure support in Postgres 11 would be able to return multiple result sets.
➥ Did this feature indeed appear in ...
2
votes
0answers
35 views
How do I prevent PostgresQL users/roles from having access to any administrator functions?
When I create a user with CREATE ROLE user123 LOGIN on my system it is able to login and immediately perform \du and interact with pg_stat_activity, and probably many other things.
I have ...
4
votes
1answer
30 views
postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client
We are using postgres 11's stored procedures to do looped update of rows in batches. After a batch is updated, a 'progress' table is updated with progress percent and status, THEN a notification (...
0
votes
1answer
19 views
PostgreSQL: Check the structure of foreign tables
How can I check if the structure of a foreign table was changed in the source database in order to know when to re import it. Foreign table was imported by foreign-data wrapper postgres_fdw (...
0
votes
0answers
22 views
ERROR: cannot update table “pg_enum” because it does not have a replica identity and publishes updates
I was trying to update the enum label using the following SQL query
UPDATE pg_enum SET enumlabel = 'Cash Recivied' WHERE enumtypid = '1203702' AND enumlabel = 'Cash';
but it throws an error
ERROR: ...
1
vote
1answer
29 views
PostgreSQL Index Only Scan can NOT return ctid
I have this table in PostgreSQL 11:
CREATE TABLE A (id bigint PRIMARY KEY, text text)
Now I want to fetch the ctid of rows meeting some condition like id = 123.
However, even using pg_hint_plan to ...
1
vote
1answer
33 views
Syntax error when using exception inside for loop [closed]
I am trying to create the following procedure on postgresql
CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN SELECT * FROM some_table ...
0
votes
1answer
35 views
Optimizing one-way syncing of large (wide) tree
I deal with several trees that can vary in size (they all have depths of ~8-10) but it's not unusual for them to consist of ~50-100k nodes.
At the moment, the trees are stored using simple parent_id ...
0
votes
0answers
27 views
Postgres replication delay with inserts into large table
I have a large table (over 35 million rows, most rows contain TOAST'd columns) that I've isolated as the source of growing replication delays in my Postgres instance. When I'm doing inserts into this ...
0
votes
0answers
24 views
cannot have more than 2^32-1 subtransactions in a transaction
I'm trying to run this but after 10 hours I get the error "cannot have more than 2^32-1 subtransactions in a transaction". What is your advice for getting around this issue? I'm hesitant to split up ...
1
vote
1answer
44 views
Index partitioned table to prevent Postgres from doing a sequential scan
I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries.
I have defined a partitioned database based on the first letter of the "identifier" - this is split into ...
0
votes
1answer
34 views
How to eliminate for loop in a nested aggregate problem
I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be ...
3
votes
1answer
92 views
Fastest way to extract full table in Postgres
I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This ...
0
votes
0answers
7 views
PostgreSQL streaming replication - secondary access
I have read that the streaming replication can be set up so the secondary receives the read only queries, but it is not clear to me how that is "turned on"? With Informix I used a connection manager ...
0
votes
1answer
38 views
Safe to use the same temporary table name in parallel transactons?
Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
1
vote
1answer
27 views
How to concatenate arrays with user-defined types in pgsql?
Background
I am using pgsql to build database tables, and have historically defined the tables in static arrays (eg ARRAY[ROW(a,b,...)::user_defined_type,...]). With the upgrade to PostgreSQL 11.2, I’...
1
vote
1answer
130 views
Postgres: ERROR: operator does not exist: point <@> point
Using PostgreSQL 11 on Windows.
Both cube and earthdistance installed and verified by pg_available_extensions.
Restarted PostgreSQL.
[42883] ERROR: operator does not exist: point <@> point.
...
1
vote
1answer
77 views
Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade
I (successfully) followed the upgrade procedure in §18.6.1 of the PostgreSQL Manual to upgrade a PostgreSQL database cluster from 10.7 to 11.2 (running Fedora 29). I would like to remove the old ...
0
votes
5answers
109 views
Create Role or Create user
PostgreSQL version : 11.2
OS platform : RHEL 7.6
I am new to PostgreSQL. I have a CRM application whose data I want to store in my 11.2 PostgreSQL Database.
The Database name will be : crm_db
The ...
2
votes
1answer
29 views
Is it possible to have unique together constraint on a SET of columns in PostgreSQL?
What I want to achieve is the following:
CREATE TABLE foo (
bar1 int,
bar2 int,
type varchar(16),
FOREIGN KEY bar1 REFERENCES bar(id),
FOREIGN KEY bar2 REFERENCES bar(id)
);
...
0
votes
2answers
115 views
Postgres Backup fails with: pg_dump: could not fsync file: Bad file descriptor
I am running Postgres 11 on Windows 10 and this should be my last backup before moving to Linux:
"C:\PostgreSQL\pg11\bin\pg_dump.exe" -U postgres -h localhost -p 5432 --verbose --jobs=4 --format=d --...
1
vote
0answers
30 views
Order of logical decoding output on Postgres 11
Given the following table in PostgreSQL 11.1, what is the order of logical decoding output rows?
CREATE TABLE t1 (
t1_id INTEGER PRIMARY KEY
,name TEXT
)
;
CREATE TABLE t2 (
t2_id INTEGER ...
0
votes
0answers
25 views
Adding LIMIT slows down query
SQL
CREATE TABLE actions (
action_id bigint GENERATED ALWAYS AS IDENTITY,
block_time timestamptz NOT NULL DEFAULT now(),
block_num int NOT NULL,
trx_id text NOT NULL,
...
1
vote
1answer
69 views
JSONB vs BRIN Index
The requirements go like this:
Each user has a wallet that can contain tens to a hundred or so different
currencies, and for each currency an amount
Number of currency can grow > 1600 (make me ...
0
votes
0answers
10 views
Postgresql 11 systemd file changed reload to kill; why?
I'm upgrading from Postgres 9.3 to 11, and I do not understand why the ExecReload default command went from
ExecReload=/usr/pgsql-9.3/bin/pg_ctl reload -D ${PGDATA} -s
to
ExecReload=/bin/kill -...
1
vote
1answer
133 views
JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)
I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data ...
0
votes
0answers
41 views
Sync Production Postgres Database to Laptop - One Way
I am running PostgreSQL 11 on my Windows Laptop and would like to sync with our production database on a (semi) regular basis and then test a number of queries and view creation without running the ...
0
votes
0answers
71 views
postgresql 11 & windows server 2008
I plan to upgrade a PostgreSQL/PostGIS server from 9.6 to 11.
The server is running on windows server 2008 R2 64.
According to the doc, PostgreSQL 11 and Windows Server 2008 has not be tested.
So ...
2
votes
2answers
580 views
Get the max value from a slice of a JSON Array?
I would like to get the max value within a slice of a Json object (typicaly [1,2,3,5,6,7,9,10]) which is contained in a field named Data of the table raw.
The limits Start & End of the slice are ...
0
votes
1answer
80 views
How to know or recover my pg_dump password? [closed]
I would like to backup my Postgre db using pg_dump. However, the first question asked by the prompt is the password.
But I have no idea what it is. Indeed, it is different from my admin account ...
2
votes
2answers
221 views
Containment check tstzrange @> timestamptz not using btree or gist index
Schema:
Column | Type
----------------------+--------------------------
id | integer
event_id | integer ...
3
votes
2answers
255 views
How does the recovery strategy of PostgreSQL exactly work?
I'm testing and trying to figure out how the backup and recovery strategy in PostgreSQL 11 works but it does not work as expected. When I recover the backup, I don't get the correct state. I get any ...
4
votes
1answer
188 views
PostgreSQL How to DEFAULT Partitioned Identity Column?
PostgreSQL 11
What is the best way to generate default values for identity columns on partition tables.
E.g
CREATE TABLE data.log
(
id BIGINT GENERATED ALWAYS AS IDENTITY
...
2
votes
1answer
117 views
PostgreSQL most efficient way to reference multiple tables
I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
5
votes
1answer
110 views
Do BRIN indexes support ENUM types?
BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type.
I thought this code would work:
CREATE TYPE test_enum AS ENUM ('a', 'b');
CREATE TEMPORARY TABLE my_table (
x test_enum
...
1
vote
1answer
68 views
Efficient group by on sorted CSV using PostgreSQL FDW
I have a large CSV file sorted on the first column. Here is an extremely simplified version):
a 7
a 4
b 6
c 3
c 9
c 2
I want to group by the first column and sum the second.
How do I tell the ...
1
vote
0answers
28 views
Hosting postgres wal logs on a separate dedicated server with failover setup
a postgres novice here, so sorry if my questions don't sound meaningful. My postgres server setup goes as follows:
Master(on server A), Upstream(on B, receiving wal from A), Downstream(on C, ...
1
vote
2answers
497 views
Undefined symbol when migrating to Postgres 11 [closed]
After upgrading Postgres 10 to version 11, by using pg_upgrade, I get this error:
could not load library "$libdir/postgis-2.4": ERROR: could not load library "/usr/pgsql-11/lib/postgis-2.4.so": /...
0
votes
1answer
467 views
Installation of PostgreSQL-11: Depends: libllvm6.0 (>= 1:6.0~svn298832-1~) but it is not installable
I've added the official repository to my sources.list,
deb http://apt.postgresql.org/pub/repos/apt/ cosmic-pgdg main 11
When I try to install PostgreSQL-11 on Ubuntu Cosmic (18.10), I get the ...
1
vote
1answer
699 views
Converting VARCHAR to TIMESTAMP with milleseconds yields “ERROR: date/time field value out of range”
I have a VARCHAR column with the value '2018-07-05T16:14:27.1427' and I convert it to timestamp with this command:
select last_run,
to_char(to_timestamp(last_run,'yyyy-MM-dd HH24:mi:ss') - ...
3
votes
1answer
2k views
“column … does not exist” error when calling an SQL procedure (new in PostgreSQL 11)
Using PostgreSQL 11 Beta 2, a very simple trading_holiday table is created in the config schema:
DROP TABLE IF EXISTS config.trading_holiday;
CREATE TABLE config.trading_holiday
(
id smallint ...
