Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

Questions tagged [postgresql-extensions]

Additional modules supplied by the Postgres project but not installed by default.

Filter by
Sorted by
Tagged with
0 votes
0 answers
8 views

CloudSQL for Postgres S3 extension

In AWS RDS for Postgres, there is an extension called AWS_S3 that provides functions within Postgres that allow me to import data directly from a bucket into a table and export data from a table ...
Jose Rocha's user avatar
2 votes
1 answer
98 views

How to DROP a function in postgres extension upgrade script with required cleanup

Suppose we have a hypothetical extension named as xyz with version 1.0. It has xyz--1.0.sql and xyz.c file. I have declared function named fun() in xyz--1.0.sql file and its definition in xyz.c file. ...
Ayush Vatsa's user avatar
0 votes
0 answers
39 views

How do I add pg_repack to a Postgres container?

I need to create a custom Postgres Docker container with many plugins. So far, I've gotten only one of them working. Another, pg_repack, is giving me grief. My Dockerfile: FROM postgres:16 RUN apt-...
Sturm's user avatar
  • 101
0 votes
1 answer
67 views

PostgreSQL stored C function is 60 times slower than standalone C code

I am trying to build stored function in C, which will take one array of bigint as argument, and return another array which is copy of parameter array but with all elements incremented by 1. My problem ...
Riku Iki's user avatar
0 votes
1 answer
29 views

Is there any way to get the comment string for an extension, as set in the extension control file?

I'm writing a foreign data wrapper program as a PostgreSQL extension. Is there any way to get the comment string set in the extension control file from my C program? The comment was created by ...
yasoo7964's user avatar
1 vote
0 answers
31 views

Writing .spec files for PostgreSQL isolation tests

I am developing a PostgreSQL extension for which I would like to run some tests with concurrent connections. If I understand the documentation correctly, I should be able to do so using the PGXS ...
BigSmoke's user avatar
  • 684
0 votes
2 answers
336 views

PostgreSQL query using BRIN index is slow until REINDEX/VACUUM

PostgreSQL 15.2. I have a partitioned table like this: create table event ( dataset_id integer not null, event_id integer not null, second_offset integer not null, -- other columns ) ...
EM0's user avatar
  • 190
0 votes
1 answer
77 views

Regression testing Issue [make installcheck]

For the regression tests: After running make installcheck on my_extension getting error: echo "+++ regress install-check in +++" && /Users/spartacus/.pgenv/pgsql-15.0/lib/pgxs/src/...
Ishaan Adarsh's user avatar
3 votes
1 answer
625 views

Makefile issue in Creating Postgres Extension

My basic extension code is at location -> /Users/spartacus/Desktop/GSoC/CODE/my_extension my_extension.control # my_extension.control comment = 'Minimal Viable Product' default_version = '1.0' ...
Ishaan Adarsh's user avatar
0 votes
1 answer
191 views

How is PostgreSQL's extension system implemented?

I am interested in how PostgreSQL's extension system is implemented. How can a postgres server call user-defined C which is built separately from the server. This question comes from Reddit u/...
Evan Carroll's user avatar
  • 63.4k
0 votes
1 answer
66 views

How can I get started with developing on PostgreSQL?

Is there any tutorial on how can we build, debug or add features on PostgreSQL database source code, or even develop and debug extensions? I am interest on adding some features on PostgreSQL database ...
Stavros Koureas's user avatar
1 vote
0 answers
295 views

Trying to use the "Object Explorer" in pgAdmin crashes server when pg-strom is installed

I'm trying to get to the bottom of some unusual behavior with pgAdmin4 when the GPU acceleration extension pg-strom is installed in PostgreSQL 14 and 15. Simple queries seem to work fine, but when ...
Sam's user avatar
  • 111
0 votes
2 answers
397 views

PostgreSQL default extension setting values in `.control` files?

I've seen some PostgreSQL extension examples that use the .control file to provide defaults for the extension's settings. This seems to be a remnant from a time in which extension settings had to be ...
BigSmoke's user avatar
  • 684
5 votes
3 answers
2k views

how to match pg_repack binary and db version

I am using this command sudo apt-get install postgresql-11-repack to install pg_repack(default to 1.4.8). postgresql-11-repack is already the newest version (1.4.8-1.pgdg20.04+1). when i run "...
Falcon's user avatar
  • 101
0 votes
0 answers
271 views

plpgsql Extension not updating after replacing with updated sql & control files in postgres extension directory

So I have a custom plpgsql extension that I installed in /usr/share/postgresql/13/extension that works properly. The problem I'm facing is that I'm trying to update the extension by replacing the ...
Jose Lopez's user avatar
0 votes
0 answers
153 views

how to install the postgis extension .... despite everything

here is my problem: I use a PC (with "user" rights) on which the postgresql software is installed (without postgis). I can't download postgis via stackbuilder (port 443 closed) and I can't ...
fcka's user avatar
  • 111
1 vote
2 answers
490 views

Postgresql doesn't working with Citus and pg_stat_statements at the same time

So, I built the PostgreSQL with citus extension in docker. I use the official documentation in citus, then I run this command in the terminal. docker run -d -v /root/volumes/citus-data:/data --...
Gismat Gusein's user avatar
5 votes
0 answers
520 views

Error with pg_trgm indexes while upgrading postgres on RDS

I want to upgrade the major version of my prod Postgres RDS database, from 11.16 to 12.11. I restored a snapshot to do a dry run. When trying to modify the version on AWS, I have the following error: ...
Lotram's user avatar
  • 101
1 vote
2 answers
1k views

What does it mean by PostgreSQL's ERROR: schema XX is not a member of extension YY?

I have two PostgreSQL (14.5, Ubuntu 22.04) extensions A and B that each creates some functions in a common schema schm. Now I am having trouble re-creating the two extensions as there seems to be ...
tinlyx's user avatar
  • 3,580
-2 votes
1 answer
136 views

PostgreSQL replication options

community! I am wondering if there are any new extensions/solutions which can replicate not the entire PG cluster but by database? Logical replication is not an option. For example, I want my primary ...
George K's user avatar
  • 2,296
0 votes
1 answer
390 views

Filter rows using a probabilistic filter (bloom filter or cuckoo filter) in Postgres

I have a large table (~1B rows) with a btree index on a single bytea column (~20GB, but fits in memory). I'd like to send a query from the client with a probabilistic filter (e.g. a bloom filter or a ...
Jason Dreyzehner's user avatar
2 votes
1 answer
475 views

Environment variables in Postgres C extension

I cannot get environment variable in my PostgreSQL C extension code. For example, this function always returns 111: #include "postgres.h" #include "fmgr.h" #include <stdlib.h>...
Nikola's user avatar
  • 35
4 votes
1 answer
2k views

Where temp table data goes after creation?

As far as I can understand temp_buffers setting serve for storing temp tables data. But as I have created a temp table immediately files gets created for it inside PGDATA along with the main database. ...
George K's user avatar
  • 2,296
0 votes
1 answer
240 views

In what circumstance is Materialize_Random set in allowedMode?

In PostgreSQL there is a field called allowedMode called SFRM_Materialize_Random The Tuplestore must be created with randomAccess = true if SFRM_Materialize_Random is set in allowedModes, but it can (...
Evan Carroll's user avatar
  • 63.4k
0 votes
1 answer
56 views

Is there a way to a maintain the function signature in C and not in a SQL file?

Currently the docs says, There are two ways you can build a composite data value (henceforth a “tuple”): you can build it from an array of Datum values, or from an array of C strings that can be ...
Evan Carroll's user avatar
  • 63.4k
0 votes
1 answer
26 views

Does PostgreSQL provide anything to dump fcinfo?

I'm looking to get into writing extension in C. I want to better understand what this argument is and how they get changed in different invocations. Is there anything that can dump this argument out ...
Evan Carroll's user avatar
  • 63.4k
0 votes
1 answer
400 views

What arguments get passed with PG_FUNCTION_ARGS (with the V1 convention)?

PostgreSQL Documents all their C functions with a "V1" interface, but they don't actually show what they get, PG_FUNCTION_INFO_V1(add_one); Datum add_one(PG_FUNCTION_ARGS) { int32 arg ...
Evan Carroll's user avatar
  • 63.4k
1 vote
1 answer
160 views

What is SFRM_Materialize_Preferred and how can it be used to write more performant functions?

Researching this question, I see there is a value for SetFunctionReturnMode called SFRM_Materialize_Preferred. What is this? Can this be used to write more performant functions? There are multiple ...
Evan Carroll's user avatar
  • 63.4k
0 votes
1 answer
225 views

C Extension: Is it faster to return a set with ValuePerCall or Materialize mode?

PostgreSQL documents it's Set Returning Functions with C extensions like this, C-language functions have two options for returning sets (multiple rows). In one method, called ValuePerCall mode, a ...
Evan Carroll's user avatar
  • 63.4k
1 vote
2 answers
832 views

Is there a method to print/debug from within a C function in PostgreSQL?

When you're extending PostgreSQL in C, how does one introspect/debug/dump a variable? Is there a method other than returning a custom result set to the client? Something like a printf that I can ...
Evan Carroll's user avatar
  • 63.4k
1 vote
1 answer
183 views

Would you recommend to use PL/R in 2021 or is it dead? [closed]

And for many years there exists a PostgreSQL extension to use GNU-R directly in the database: https://www.joeconway.com/plr/ Ubuntu packages are also provided and updated for latest PG: https://...
alfonx's user avatar
  • 827
0 votes
0 answers
609 views

Can't create a Postgres extension for pgaudit

I compiled pgaudit on Windows10 using Visual Studio. I added shared_preload_libraries = 'pgaudit' and restarted the server. When I try to add the extension, I get: ERROR: could not find function &...
Joe Whalley's user avatar
4 votes
1 answer
1k views

List PostgreSQL extensions per database

I have a Postgres 9.5 server with 30 databases. I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin. I know that SELECT * FROM ...
copossum's user avatar
  • 141
0 votes
2 answers
128 views

Debug why Postgres wants to use an uninstalled extension for a function

I recently moved a Postgres function app_user_id from an old, custom extension called intpair into a new, custom extension called heap_exts. This worked well except for machines I restored from a ...
Joe's user avatar
  • 179
0 votes
1 answer
3k views

Create GIN index in a schema with the pg_trgm extension

I'm migrating entities from the public schema into a new one, let's call it myschema. Originally, the pg_trgm extension was installed and the index was created in a schema migration with: CREATE ...
Esteban's user avatar
  • 103
2 votes
1 answer
8k views

Is it possible to add Postgres extensions to AWS Aurora?

I am trying to futureproof a Postgres database so it can be transferred to AWS Aurora. I am using the guide extension uuid-ossp. Is it possible to add PostGres extensions to AWS Aurora? In ...
mikelus's user avatar
  • 323
2 votes
1 answer
2k views

Can't enable wal2json in postgres 12 docker image

I'm trying to enable wal2json in an image extended from postgres:12 image. But i'm getting an error: postgres=# create extension wal2json; ERROR: could not open extension control file "/usr/share/...
Ayush's user avatar
  • 121
2 votes
1 answer
62 views

Are C++ vectors in a user-defined aggregate function a POD for Postgres?

The official postgres documentation (docs) states in section "37.10.11. Using C++ for Extensibility" that If calling backend functions from C++ code, be sure that the C++ call stack contains only ...
ReverseFlowControl's user avatar
0 votes
1 answer
262 views

Custom Aggregate Postgresql C extension with input as initial condition

Is there a way to write a custom aggregate Postgresql C extension function that takes an input from the user and sets it as the initial condition? If so, how? I’ve written several Postgresql ...
ReverseFlowControl's user avatar
1 vote
1 answer
667 views

How to package a PostgreSQL extension as a binary .deb file in Ubuntu?

What is the minimal-effort way to package a PostgreSQL extension into a .deb file for Ubuntu (as of 18.04)? I did some search but find many different suggestions (some outdated). Suppose I want to ...
tinlyx's user avatar
  • 3,580
0 votes
1 answer
903 views

In Amazon RDS, how does one enable PLV8 extension?

I see it is available. Just can't find the right documentation to help me enable it. From the docs, I entered the following command and got: postgres=> select * from pg_available_extensions where ...
dnk8n's user avatar
  • 143
2 votes
2 answers
2k views

Does PostgreSQL btree_gin extension use btree or gin data structure?

In order to define a GIN index on an array and scalar field (for example) you need to enable the btree_gin extension in PostgreSQL. Then you can define your indexes as a GIN index. However I don't ...
collimarco's user avatar
7 votes
1 answer
3k views

How to select functions that belong in a given extension in PostgreSQL?

I am trying to compose a SELECT from pg_catalog that pulls function name, its schema name and the name of extension that created this function. To pull function list I use (a simplified version) ...
oᴉɹǝɥɔ's user avatar
3 votes
1 answer
6k views

LTREE extension does not exist after CREATE EXTENSION

I am trying to create a trigger that uses ltree column. CREATE EXTENSION IF NOT EXISTS ltree; Running SELECT * FROM pg_catalog.pg_extension gives the following output: extname |extowner|...
kravb's user avatar
  • 179
1 vote
1 answer
214 views

Can I do a backup without extension functions?

I use the PostGIS extension on my database. The thing is when I do a backup and then a restore, all the PostGIS functions give a warning during the restore process, because the functions already exist,...
Juan Carlos Oropeza's user avatar
0 votes
0 answers
562 views

Create extension for user

My DB has two users, the superuser and a (non-superuser) worker. I've installed the tsm_system_rows extension using CREATE EXTENSION tsm_system_rows; on the superuser, and I can use the new ...
Madden's user avatar
  • 111
0 votes
1 answer
529 views

How can I only load one function from an extension?

In PostgreSQL, I am loading the uuid-ossp extension so I can use the function that creates random UUIDs in my queries: create extension if not exists "uuid-ossp"; However, as well as making ...
Ben S's user avatar
  • 111
2 votes
1 answer
7k views

Cannot run 'uuid-ossp' functions even when it's activated

I want to generate a v4 uuid in PostgreSQL using the extension uuid-ossp, so in psql I ran: CREATE EXTENSION "uuid-ossp"; Then I ran SELECT uuid_generate_v1(); or SELECT uuid_generate_v4(); but I ...
Fawwaz Yusran's user avatar
3 votes
1 answer
7k views

Postgres wait_event_type Extension

I have a function that was written in Language SQL (not plpgsql) that is scheduled to be called nightly from pgAgent. It was running fine until last night. The function usually takes under 3 minutes ...
dcbeckman's user avatar
0 votes
1 answer
2k views

pg_stat_statements empty with administrator user and unusable for other users

I have an issue with pg_stat_statements, the extension is installed and enabled on the database, was working for a long time, however it now seems that it has crashed. portaldb=> SELECT * FROM ...
MMG's user avatar
  • 1