Questions tagged [postgresql-extensions]
Additional modules supplied by the Postgres project but not installed by default.
105
questions
1
vote
1
answer
35
views
Optimizing Query to Retrieve Names from pg_buffercache View in PostgreSQL
I had a database with large amount of tables / partition tables where I need to retrieve the database name, relation name, parent relation name and tablespace name instead of their respective OIDs ...
- 55
0
votes
1
answer
45
views
are pg_cron and pgaudit database specific extensions? or is one installation per server enough?
I'm quite versed in postgresql, but new to any extension other than postgis. for example, one of the first things I do when setting up an instance is to install postgis in template1, so it's available ...
- 185
0
votes
0
answers
18
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 ...
- 45
2
votes
1
answer
126
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.
...
- 55
0
votes
0
answers
79
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-...
- 101
0
votes
1
answer
85
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 ...
- 11
0
votes
1
answer
32
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 ...
- 33
1
vote
0
answers
40
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 ...
- 694
0
votes
2
answers
440
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
) ...
- 190
0
votes
1
answer
103
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/...
- 133
3
votes
1
answer
854
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'
...
- 133
0
votes
1
answer
244
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/...
- 64.1k
0
votes
1
answer
72
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 ...
- 170
1
vote
0
answers
323
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 ...
- 111
0
votes
2
answers
537
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 ...
- 694
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 "...
- 101
0
votes
0
answers
315
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 ...
- 13
0
votes
0
answers
166
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 ...
- 125
1
vote
2
answers
546
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 --...
5
votes
0
answers
555
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:
...
- 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 ...
- 3,662
-2
votes
1
answer
146
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 ...
- 2,296
0
votes
1
answer
439
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 ...
- 155
2
votes
1
answer
496
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>...
- 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.
...
- 2,296
0
votes
1
answer
252
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 (...
- 64.1k
0
votes
1
answer
61
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 ...
- 64.1k
0
votes
1
answer
29
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 ...
- 64.1k
0
votes
1
answer
436
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 ...
- 64.1k
1
vote
1
answer
172
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 ...
- 64.1k
0
votes
1
answer
241
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 ...
- 64.1k
1
vote
2
answers
919
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 ...
- 64.1k
1
vote
1
answer
188
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://...
- 827
0
votes
0
answers
666
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 &...
5
votes
2
answers
2k
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 ...
- 151
0
votes
2
answers
143
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 ...
- 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 ...
- 103
3
votes
1
answer
9k
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 ...
- 333
2
votes
1
answer
3k
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/...
- 121
2
votes
1
answer
64
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 ...
0
votes
1
answer
292
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 ...
1
vote
1
answer
718
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 ...
- 3,662
0
votes
1
answer
943
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 ...
- 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 ...
- 623
7
votes
1
answer
4k
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)
...
- 511
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|...
- 179
1
vote
1
answer
232
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,...
0
votes
0
answers
595
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 ...
- 111
0
votes
1
answer
548
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 ...
- 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 ...
- 121