Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
2 votes
1 answer
86 views

Why is my composite index on 3 int columns smaller than the PK index on 1 int column?

I have a table called data_table. Below are schema, indexes, and their sizes. Table "public.data_table" Column | Type | ...
Ezhar's user avatar
  • 21
1 vote
0 answers
23 views

How to make PostgreSQL react on bad execution plans and update its statistics?

When the statistics are outdated, it is obvious that the execution plan will be sub-optimal. AFAIK, nothing will prevent PG to repeat the same bad-planned executions again and again unless one ...
Mahatma_Fatal_Error's user avatar
1 vote
0 answers
43 views

In Postgres C extension development, the argument of function is a tuple, how to iterate all its attributes

This is my .c file Datum show_line(PG_FUNCTION_ARGS) { /* Get Input*/ HeapTupleHeader tup_hdr; TupleDesc tup_desc; Oid tupType; int32 tupTypmod; bool isNull; // int tuplen; ...
Lingze Zeng's user avatar
2 votes
1 answer
60 views

Joining tables using the longest matching ltree path

Given a table like so: path (ltree) a.b.c a.b a d.e f How would I write a query to return the longest matching ltree path given an input? For example: (input) => expected output (a.b.c) =>...
cjheppell's user avatar
  • 303
1 vote
1 answer
34 views

PG - How to drop the type and keep the table

In PG 16, is there a way to avoid the error below when running DROP TYPE user_state;? Query 1 ERROR at Line 1: : ERROR: cannot drop type user_state because other objects depend on it DETAIL: column ...
Sig's user avatar
  • 383
4 votes
0 answers
66 views

Row estimates incorrect when using RLS and current_setting in policy

Context Postgres 14.4. I have a table which has RLS enabled, and I am using values pulled from current_setting in order to determine which rows should be returned when querying the table: create table ...
cjheppell's user avatar
  • 303
2 votes
0 answers
21 views

Is there an algorithmic way to identify potential primary key foreign key relationships in postgres

I have a lots of tables in postgres with a lots of columns per each table. I need to write a program in such a way that it can identify the columns that can be a potential primary and foreign key pair....
Ashwin Prasad's user avatar
2 votes
2 answers
290 views

CASCADE DELETE per statement

Can I execute something like this in Postgresql, that is, execute CASCADE DELETE for just one statement? DELETE CASCADE FROM foo WHERE bar = 'baz'; My goal is to delete not just the selected record(s)...
Timur Shtatland's user avatar
1 vote
0 answers
44 views

PostgreSQL isn't using all available vCPUs of the VM

Running some procedures takes too much time and if I check htop I can see just one vCPU of the Ubuntu VM that is running and the others aren't: postgresql.conf max_connections = 50 shared_buffers = ...
Saad elMoustarhfir's user avatar
1 vote
0 answers
30 views

Why can't grant privileges to a role?

I created a role and set privileges to tables, then created a user and grant the role to it by postgres user. After the user login database, it can't select any tables at all. CREATE ROLE readonly; ...
Alpin Cleopatra's user avatar
1 vote
1 answer
89 views

Error running pg_basebackup

I'm running Postgres16 on Windows server. Start researching on pg_basebackup - when I execute the following command, pg_basebackup.exe -h SERVERNAME -U postgres -p 5432 -D Z:\PostgreSQL\16\backups\ -...
Student's user avatar
  • 29
0 votes
2 answers
86 views

Does dropping a Postgres table cascade to materialized views that depend on it?

I understand that a regular view will be invalid if a table it is dependent on is dropped. Does the same apply to a materialized view? I would assume that in a materialized view since the data has ...
vfclists's user avatar
  • 1,041
1 vote
1 answer
34 views

PostgreSQL Table File On Filesystem Much Smaller Than pg_total_relation_size

I noticed that there is a misalignment between the results I get from querying pg_total_relation_size(table_name) and manually checking the size occupied on the filesystem by the specific table. The ...
Aaron Ullal's user avatar
3 votes
1 answer
116 views

How to re-TOAST specific data after changing compression?

After changing the default_toast_compression from pglz to lz4 I would like to update existing data to the new, faster, compression. The only way appears to be to dump the entire table and recreate it ...
OrangeDog's user avatar
  • 338
1 vote
1 answer
48 views

Upgrade Postgres RDS in AWS fails major version upgrade

I have been attempting to upgrade our AWS RDS Postgres from 11.22 to 12.17 and/or 15.5 In both instances I get the following error in the error log: executing: SELECT datname, datallowconn FROM ...
sweetfa's user avatar
  • 188
1 vote
2 answers
46 views

Can I get the created time of a PostgreSQL DB?

I'm using PostgreSQL-13.0. Is there a way I can get the created time or last modified time of a database? Thanks!
Leon's user avatar
  • 337
1 vote
1 answer
31 views

Why does a user use owner privileges when executing a trigger?

I came across a strange situation that gave me a big headache and after solving it I would also like to understand WHY. Basically, a user had all the privileges he needed to execute a "trigger&...
Sotis's user avatar
  • 296
2 votes
1 answer
55 views

Chain 2 queries with \gexec in psql

I'd like to produce a data dictionary from the catalog tables of a Postgres DB. I wanted to have the name of the table on one line and then the list of columns. I've tried to do it with \gexec: \set ...
Jice's user avatar
  • 23
1 vote
2 answers
24 views

Store a value into a variable

In a query, I am using the NOW() function several times (to extract the day, month, and year of the current date). Is there a way to call the function just one time and save the output?
gbox's user avatar
  • 121
2 votes
1 answer
135 views

Retrieve 1 million rows of user record in postgress

I am preping for a Notification service in which a single notification can be broadcasted to 1 million users at once. What are the available options to store the users record so that i can retrieve it ...
The Keeper's user avatar
1 vote
0 answers
23 views

On demand snapshot and CDC from Postgresql multitenant DB

I have a Postgresql multitenant DB where each table contains tenant level metadata. e.g. Table "connector" Column | Type |...
deGee's user avatar
  • 111
1 vote
1 answer
125 views

Why index scan can be slow in Postgres?

I have a query: WITH route_ids_filtered_by_shipments AS ( SELECT DISTINCT rts.route_id FROM route_to_shipment rts JOIN shipment s ON rts.shipment_id = s....
Nikita Glukhov's user avatar
1 vote
0 answers
414 views

AWS RDS Postgres: How to diagnose CheckpointLag and potential slowups using AWS' Monitoring suite?

We are currently hosting a postgres RDS database and our team is noticing slowup in our querying service. I'm noticing a spike in the metric, CheckpointLag and I've been tasked in trying to find where ...
Andrew Narvaez's user avatar
1 vote
1 answer
92 views

Need help with count query

I'm currently working with a service with a count query running longer than I want. The query doesn't have serious performance issues but I believe there is an improvement. Let me provide a little bit ...
NikMashei's user avatar
  • 111
0 votes
1 answer
63 views

Can't link building C++ application with pqxx library

I had to re-install my operating system and I switched from Ubuntu 22.04 to Linux Mint 21.2. I installed PostgreSQL 16 from the postgres repository. It seems to work fine. Then I downloaded libpqxx 7....
Zephyrus's user avatar
  • 301
0 votes
1 answer
317 views

PostgreSQL SSPI error connection failed: :1), port 5432 failed: fe_sendauth: no password supplied

New to PostgreSQL. Installed Postgresql 16 on Windows server. On pg_ident.conf, I added sspi userxyz@domain userxyz On pg_hba.conf, I added host all userxyz 0.0.0.0/0 sspi map=sspi ...
Student's user avatar
  • 29
0 votes
0 answers
47 views

I can't find syntax error from a PostgreSQL query

A similarly structured query works on Fiddle but I get a syntax error at or near "AND" LINE 9: AND h.paivamaara BETWEEN '2020-12-03'::date AND ... ^ and I ...
jvkloc's user avatar
  • 133
1 vote
1 answer
81 views

Understanding PostgreSQL bitmap heap scan poor performance

I'm trying to optimize a slow query on PostgreSQL 15. The query is quite complex with many JOINs, a UNION and subqueries but 99% of execution time is in the innermost node that seq scan a 700K records ...
St.Mauro's user avatar
1 vote
0 answers
64 views

Odoo 16 restoring 350 MB database Ubuntu Nginx

What I am trying: I am trying to restore Odoo 16 enterprise database with size 350MBs and I am using Digitaloceans and Nginx as a proxy. Error I am getting: Error Code: 413 Request Entity Too Large ...
Tele oceans's user avatar
1 vote
0 answers
30 views

Does creating partitions using pg_partman locks the table

We are exploring and comparing pg_partman and timescale for auto creation and maintenance of partitions. The post here says pg_partman locks the table and may block writes on table. https://www....
dev Joshi's user avatar
  • 111
2 votes
1 answer
41 views

Pass a parameter into an SQL file

In Oracle we have a tiny sql file (truncate_table.sql) that calls a function that truncates a table: BEGIN DBMS_OUTPUT.PUT_LINE('FuncTruncTable executing...'); IF ( FuncTruncTable(upper('&1'...
user281945's user avatar
2 votes
2 answers
86 views

Indexing Entire JSON Column vs Indexing Specific JSON Field in PostgreSQL

I have a PostgreSQL table named item_log with a log column of type jsonb that contains various attributes. One of these attributes is delete_log. I'm querying this table based on the created_at field ...
sujeet's user avatar
  • 235
2 votes
0 answers
60 views

Why does the query plan change dramatically and never complete with a single additional where clause?

The following postgres query changes quite a bit by a single additional WHERE condition. std.a133_findings has an index over (audityear, dbkey), std.a133_findings_augmented has uniqueness constraint ...
diyer0001's user avatar
2 votes
1 answer
55 views

Return state of a row when executing a function in PostgreSQL

For years I have been creating functions that are executed by triggers, and in the function I always returned OLD (for DELETE triggers) or NEW (for INSERT or UPDATE triggers). Is it mandatory to ...
Tom's user avatar
  • 428
0 votes
1 answer
46 views

Split lines against polygons in a PostGIS trigger

I have a table of lines, named segment, stored in the reference schema, and a table of polygones named communes in a donnees_externes schema. I try to create a trigger that split the features of ...
idrizza's user avatar
0 votes
2 answers
82 views

PostgreSQL - 30000 row updates, setting non-indexed boolean column to 'true' takes hours

I am running PostgreSQL 13, and single UPDATE statement that seems like it would be trivial to complete can take hours (at least in one case more than 24 hours). Selecting the rows with a SELECT ...
Justin Lowen's user avatar
0 votes
0 answers
22 views

Optimizing PostgreSQL Performance: Seeking Insights on Automatic Tuning and Best Practices

I'm exploring PostgreSQL performance optimization. Does PostgreSQL have built-in automatic tuning features or recommended extensions? I've experimented with auto-analyze thresholds and settings, but ...
Ezhar's user avatar
  • 21
1 vote
1 answer
75 views

How to make a foreign key checking for NULL values?

I have a table specifying types to distinguish between scalars and lists. When "scalar" exists the value is a scalar and when it is null it is a list. drop table type1 cascade; create table ...
ceving's user avatar
  • 275
0 votes
1 answer
27 views

How to pg_restore some tables with automatic index creation?

How to restore some table from pg_dump custom format, and automatically create it's index ? I just restore some tables from database dump (custom format). pg_restore -d database --table=table1 --table=...
Sapta's user avatar
  • 11
0 votes
1 answer
36 views

INSERT my_table (fields,...) value (value_1,...), (value_2, ...)... is slow in citus

Environment: host: resources:32 cores, 64GB RAM, Docker 24.07 OS: CentOS 7 disk: 10 separate disk partitions The 10 separate disk partitions are as below df -h filesystem capacity used ...
wureka's user avatar
  • 159
0 votes
0 answers
31 views

connection failed: FATAL: role "y" does not exist when .ini has role "x"

I have a Python program for PostgreSQL database management. Today it started throwing psycopg.OperationalError: connection failed: FATAL: role "role_a" does not exist Connection parameters ...
jvkloc's user avatar
  • 133
0 votes
1 answer
29 views

How to use a query to set a custom option?

I tried this but it does not work: CREATE OR REPLACE FUNCTION answer() RETURNS bigint LANGUAGE sql AS $$ SELECT 42 $$; SET session.answer = select answer();
ceving's user avatar
  • 275
2 votes
1 answer
203 views

How to define a function accepting exactly three characters?

I tried this: create or replace function c3 (arg char(3)) returns void language plpgsql as $$ begin raise notice 'Value: %', arg; end; $$; But it accepts more than three characters: select c3('...
ceving's user avatar
  • 275
1 vote
2 answers
84 views

ON predicate of Postgres LATERAL JOINs

How does ON predicate of Postgres LATERAL JOIN work? Let me clarify question a bit. I've read the official documentation and a bunch of articles about this kind of JOIN. As far as I understood it is a ...
Nikita Glukhov's user avatar
0 votes
2 answers
49 views

How to list available users in PostgreSQL having only the data directory?

I messed up a bit with a Dockerized PostgreSQL instance and accidentally overwrote the values I had for POSTGRES_USER and POSTGRES_PASSWORD. They were randomly generated, so guessing them is basically ...
illright's user avatar
  • 111
0 votes
1 answer
52 views

PostgreSQL create database with parameter syntax error

I have the following function for creating a PostgreSQL database with psycopg: def create_database() -> None: """A function for creating a PostgreSQL database.""" ...
jvkloc's user avatar
  • 133
0 votes
1 answer
126 views

Do READ ONLY transactions ever cause serialisation anomalies (or, is SERIALIZABLE ever useful for READ ONLY)?

I am using PostgreSQL. I had attempted to build a system that utilized SERIALIZABLE transactions exclusively, in the expectation that it would simplify my application development. I had marked ...
user22476690's user avatar
0 votes
1 answer
79 views

ERROR: operator class does not exist for access method "btree"

I've encountered an issue while attempting to execute a script. Here's the scenario: -- Script to create collation CREATE COLLATION case_insensitive (PROVIDER = icu, LOCALE ='und-u-ks-level2', ...
channa's user avatar
  • 350
0 votes
0 answers
9 views

Incremental CDC from Postgresql for select rows

I have a postgresql DB which has tables containing multi-tenant data. Example: Table Customer CustId Cust Name First CustA Second CustB Table CustomerShoppingDet Item Name CustId. Buying Date ...
deGee's user avatar
  • 111
0 votes
0 answers
7 views

How to extract and transform filename from org-mode link with PostgreSQL regex?

org-mode file links are generally of the form [[/path/to/file][text description]]. What regex do I need to extract /path/to/file so I can produce strings like file:///path/to/file or file:///path/to/...
vfclists's user avatar
  • 1,041

1
3 4
5
6 7
336