Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
-1
votes
1answer
14 views

DBD::Pg::st execute failed: ERROR: canceling statement due to user request

I am using postgresql with pgadmin, I am getting following error DBD::Pg::st execute failed: ERROR: canceling statement due to user request CONTEXT: SQL statement "TRUNCATE mystats_na_apple....
5
votes
1answer
176 views

Will autovacuum block DROP or TRUNCATE?

When the autovacuum process is vacuuming a large table, will queries like DROP and TRUNCATE be blocked until vacuuming completed? In the documentation,it says Also, the standard form of VACUUM can ...
1
vote
1answer
451 views

Window aggregate resulting in slow query on postgres 9.6

I'm using Postgresql 9.6 and I'm running into a performance issue. I have a relatively simple query: explain analyze SELECT * FROM ( SELECT A.person_id, O.session_id, timestamp_ AS ...
0
votes
1answer
14 views

pg_restore fails with drop default on identity column

Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase. See below: pg_restore --verbose -...
0
votes
1answer
146 views

How to mount a pg_basebackup on a stand alone server to retrieve accidently deleted data

Had a bit of a brain fade the other day and deleted all the rows in one of the tables in our database and didn't realise until a couple of hours later. I have a pg_basebackup file system copy created ...
0
votes
1answer
102 views

Large bulk insert performance difference PostgreSQL vs MariaDB (InnoDB)

I've got the very same single-table definition in both PostgreSQL 11.4 and MariaDB 10.4.6 (InnoDB) and I'm seeing a significant difference in INSERT performance I cannot explain for PostgreSQL. My ...
1
vote
1answer
26 views

Why disable Postgres Autovacuum?

I've got a task to work with a 9.6 Postgres DB, and for some reason Autovacuum is disabled. The database was extremely slow, and after a manual VACUUM it could breathe again. The point is, why ...
1
vote
1answer
15 views

How I can find out the foreign wrapper that a foreign table belongs to?

I have found that the following query lists all my foreign tables in my PostgreSQL database: select * from information_schema."tables" where table_type='FOREIGN TABLE'; Now I want to find out the ...
0
votes
1answer
19 views

How to Bulk Insert sqlalchemy subclasses

I am trying to bulk insert SQL-Alchemy Subclasses into the parent table and their respective tables ie fruits tables -> Apple Table and so I insert a table of APPLE and it will insert both the row ...
1
vote
0answers
15 views

How to get db “state version seq” in postgres?

I need to run a script on every database change. The script works on diffs, so I need to know if I handled the previous state (the handler script could be down for some time for example). One way I ...
1
vote
2answers
438 views

PostgreSQL truncating values of real numeric type

Using PostgreSQL 9.2 on Windows Server 2007, I have the following table: CREATE TABLE aaa_dens (read_no INT, position REAL PRIMARY KEY); Some values in position have 4 or more digits after the ...
0
votes
1answer
27 views

How to Calculate Month over Month User Retention based on already active users (not based on user signup date)?

I have a Table which tracks User activity (i-e user started a session at what time ?). This table contains the data from Dec 2018 Till now. I need to calculate monthly retention (not based on signup ...
1
vote
1answer
121 views

Started using UUID's as primary key, would like to execute union of entities with BigSerial Primary Key

Some of my newer tables are now using UUID type fields as PKey and older tables are still using bigserial. I have several tables (let's call them entities) I would like to query returning the primary ...
3
votes
1answer
91 views

Finding optimal indices, different query competing indices

My schema: create TABLE "logs" ( "id" serial not null default nextval('logs_id_seq'::regclass), "request" varchar(1024), "token" varchar(512) default NULL, "was_batch_request" bool ...
0
votes
1answer
63 views

Postgresql function equivalent to Excel's NORMSINV function?

I need Excel's NORMSINV equivalent function for PostgreSQL, preferably plpgsql function. Can somebody help? For reference, here is Excel's normsinv function explained: https://support.microsoft.com/en-...
0
votes
0answers
22 views

Single metadata base table in Postgres

I am a fan of adding timestamps to tables in the form of created_at, updated_at and deleted_at. But it can be tedious to add these fields to every table. I was wondering, is it a bad design decision ...
1
vote
1answer
734 views

Troubleshooting PgPool Query Lags

I have been using PGpool-II for quite a while but just noticed an intermittent lag (slow request taking about 20s to execute) which I am able to replicate consistently. Before I explain here is a ...
0
votes
1answer
43 views

Postgresql, escape character in string_agg function

Question, can I add escape character \ just for fields param_name and value for this? string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params", Result is. {name:"...
0
votes
1answer
20 views

postgres function definition not saving top whitespace

For reference I am running postgres 9.6 . I'd like to write functions inside of postgres where the top of the function saves. create or replace function apqp.apqp_list_gentex_tool_report_getlist(...
6
votes
1answer
2k views

Multiple on conflict targets

I have two unique indexes on columns a and b. I need something like this: insert into my_table (a, b) values (1, 2), (1, 2) on conflict (a) do update set c = 'a_violation' on conflict (b) do update ...
2
votes
1answer
103 views

SELECT operation with four joins results in bad performance

I'm having problems with a slow query. The query purpose is to get doers ids for X job who didn't exceed specified limits on realizations and also are in range of possible job places. Resources ...
0
votes
1answer
19 views

How can I see what query is emitted by the query parser?

Consider this snippet: create table tbl_1(name text); create table tbl_2(name text); create function my_insert(_name text) returns text language sql as $$ insert into tbl_2(name) values (...
0
votes
0answers
16 views

How to achieve this Nested Complex postgresql schema CRUD?

I'm trying to achieve this complex schema. { relation1_id : Number, ... ... item_caption: [ { caption: '', subcaption: '', products: [ { ...
0
votes
1answer
20 views

pgadmin: can't insert jsonb[] values with strings

I have jsonb[] column and it allows me to insert some nested arrays, but only with number values like {{1,2,3}, {4,5,6}, {7,8,9}}. I'm not talking about inserting some nested objects like {{"one": "...
3
votes
1answer
1k views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error OCIEnvCreate failed to create environment handle after installing oracle_fdw. my ...
0
votes
1answer
14 views

How to reset object permissions to default?

I've just been added to a project using Postgresql 11.4 where database security has been an afterthought. As part of tightening things up, I've used ALTER DEFAULT PERMISSIONS to set sane ACLs on any ...
0
votes
2answers
54 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 ...
6
votes
1answer
125 views

Which data model/schema to apply to a timeseries datastore for data sources with varying fields

I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose. About the data The source data which is ...
0
votes
2answers
39 views

PostgreSQL - getting Too many client repeatedly

I am running PSQL with PostGis extension. After the start of server, after about 3 weeks, I start getting fatal errors for connections with "too many clients already". When I restart the server, ...
1
vote
1answer
16 views

What does 'fully determines' mean in the context of postgres functional dependency statistics?

I am following the example of how to use 'create statistics' in postgresql from this page. CREATE TABLE tbl ( col1 int, col2 int ); INSERT INTO tbl SELECT i/...
1
vote
1answer
18 views

using pg_prewarm after an ETL load has finished - worth it?

I have a database with many small reference data tables and a few large time series tables. The reference data tables can easily fit into memory. I have various ETL jobs that load data, mainly by left ...
0
votes
2answers
120 views

Row level security returning no error when blocking all effects of a query

With row level security enabled on tbl, but no policy set, as a user the UPDATE throws no error but does not modify the value: UPDATE tbl SET col = 1; => Query returned successfully: 0 rows ...
0
votes
0answers
13 views

Postgresql, export to xml, <row> rename

When exporting to xml, I have this output. "<product><table xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns=""test""> <row> ... </row> <row> ... etc ...
0
votes
0answers
16 views

Reader rights by default in Postgres

My question is about database permissions and rights in Postgres. How can I create reader rights by default when objects are uploaded into PGadmin 4 schemata? I created the following schema in ...
0
votes
1answer
167 views

Postgres Windows post-install “The database cluster initialization failed”

I've seen many solutions and have been working on this issue for hours now. After installation the post-installation script fails with the message above. This is a development installation of ...
1
vote
1answer
218 views

Postgresql - archive_cleanup_command with multiple standby servers

Regarding the archive_cleanup_command command, the Postgresql 9.6 documentation states the following: Note however that if multiple standby servers are restoring from the same archive directory, ...
5
votes
1answer
3k views

Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING

Setup I have a bulk insert function set_interactions(arg_rows text) that looks like this: with inserts as ( insert into interaction ( thing_id, associate_id, created_time) ...
0
votes
2answers
460 views

PgPool fails to auth in master_slave mode

We have two PostgreSQL servers running in master-slave streaming replication mode, behind pgpool. When we activate master_slave mode in pgpool.conf, we get following error: do_md5 failed in slot 1 ...
0
votes
0answers
18 views

How to prevent regular users to backup database, in Postgresql

How can I prevent/restrict regular users, that do have select rights granted on tables etc, to perform backup of Postgres database? Is there a way to enable users to connect, select, update etc. ...
0
votes
1answer
20 views

How to list all constraints of a table in PostgreSQL by schema?

Yes, I'm aware there is a question very similar to mine, the difference is that I want all constraints, including not null, which no answer is able to do.
0
votes
2answers
20 views

Postgresql - roles without linux accounts?

Basic question: what I'm reading describes how to create linux users in parallel with postgresql roles. However, I don't see the value in creating any of these users in linux - I just want to create ...
3
votes
0answers
32 views

How to optimize this recursive postgresql query?

I've been searching a lot on this topic but I'm stuck at optimizing a query. I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll ...
4
votes
1answer
935 views

PostgreSQL: Run EXPLAIN on all queries in pg_stat_activity

I am investigating high load on a PostgreSQL database on RDS. As a part of the investigation, I'm running the following query: SELECT now()-query_start, state, query FROM pg_stat_activity ORDER BY 1 ...
0
votes
1answer
138 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
0
votes
1answer
15 views

How to create a trigger that checks if a value in 2 fields is present and if it is change it to something else

I am new to db, and trying to create a trigger that checks some fields in a table for a value and if the value is present than it updates it to something else. These are my thoughts so far. CREATE ...
0
votes
2answers
57 views

Table design and query Optimization: Query to find the suitable work from a list of work items

I have table with jsonb column as below CREATE TABLE work ( id SERIAL NOT NULL, work_data JSONB ); sample data is as follows: 100 {"work_id": [7245, 3991, 3358, 1028]} ...
0
votes
2answers
35 views

Migrate large postgresql database (windows -> linux) [closed]

I have a "large" PostgreSQL (9.6 with timescale plugin) database (about 1Tb). And would like to migrate it to a Linux machine. The naive approach would be pg_dump and pg_restore. (Not sure how long ...
0
votes
1answer
290 views

Extract part of a string in Postgres

I have a question how to extract a substring. Here an example string: {{from,005021889988,,Amt},{setup-from,66,,},{alert-to,66,ConTeach Zentrale,ConTeach Zentrale},{conn-to,66,ConTeach Zentrale,...
0
votes
1answer
21 views

How to increase index row maximum size in Postgresql?

I want to perform KNN on 512-dimensional vector using the solution suggested here. I couldn't create table with 512-d cube array. Solved by changing the CUBE_MAX_DIM in the source code. Couldn't ...
1
vote
1answer
24 views

Select in a PostgreSQL function

I'm trying to do my first Function in PostgreSQL, but I can't to do it. I was write this piece of code CREATE OR REPLACE FUNCTION getCars () RETURNS TABLE ( car_name VARCHAR, id_car ...