Questions tagged [postgresql-extensions]
Additional modules supplied by the Postgres project but not installed by default.
99
questions
0
votes
1
answer
22
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 ...
- 13
1
vote
0
answers
19
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 ...
- 644
0
votes
2
answers
178
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
49
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
288
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
140
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/...
- 62.2k
0
votes
1
answer
61
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
232
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
218
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 ...
- 644
5
votes
2
answers
1k
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 "...
- 71
0
votes
0
answers
242
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
101
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 ...
- 113
1
vote
2
answers
403
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
498
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
993
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,425
-2
votes
1
answer
111
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,319
0
votes
1
answer
307
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
444
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
1k
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,319
0
votes
1
answer
230
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 (...
- 62.2k
0
votes
1
answer
54
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 ...
- 62.2k
0
votes
1
answer
23
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 ...
- 62.2k
0
votes
1
answer
344
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 ...
- 62.2k
1
vote
1
answer
140
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 ...
- 62.2k
0
votes
1
answer
205
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 ...
- 62.2k
1
vote
2
answers
656
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 ...
- 62.2k
1
vote
1
answer
171
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://...
- 807
0
votes
0
answers
499
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 &...
3
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 ...
- 131
0
votes
2
answers
120
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
2
votes
1
answer
7k
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 ...
- 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/...
- 121
2
votes
1
answer
57
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
236
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
575
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,425
0
votes
1
answer
829
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 ...
- 563
6
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)
...
- 471
3
votes
1
answer
5k
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
180
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
499
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
481
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
3
votes
1
answer
6k
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 ...
- 73
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 ...
- 1
2
votes
0
answers
2k
views
How to install PLV8 in PostgreSQL 11 in Ubuntu 16?
I am trying to install PLV8 extension (JavaScript Procedural Language for PostgreSQL) in my PostgreSQL 11 Ubuntu Server 16 and having a hard time. The PostgreSQL 11 was installed using PostgreSQL's ...
2
votes
1
answer
5k
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.
...
- 145
1
vote
1
answer
2k
views
Installing extension as non superuser on Amazon AWS RDS
I would like to allow non super users to install a specific postgres extension. Accordingly to the documentation at https://www.postgresql.org/docs/current/extend-extensions.html that is possible if I ...
- 111
0
votes
1
answer
1k
views
PGAUDIT on RHEL 7.4
I have download PostGreSQL 9.6 and pgaudit 9.6 from my RHEL repository. I have installed the rpm.
How to I verify it is installed correctly? I have not been able to find good directions.
It seems ...
- 113