2022 Developer Survey is open! Take survey.

Questions tagged [postgresql-extensions]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
37 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 ...
user avatar
2 votes
1 answer
107 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>...
user avatar
  • 35
1 vote
1 answer
35 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. ...
user avatar
  • 2,193
0 votes
1 answer
16 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 (...
user avatar
  • 55.7k
0 votes
1 answer
35 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 ...
user avatar
  • 55.7k
0 votes
1 answer
16 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 ...
user avatar
  • 55.7k
0 votes
1 answer
59 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 ...
user avatar
  • 55.7k
0 votes
1 answer
45 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 ...
user avatar
  • 55.7k
0 votes
1 answer
65 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 ...
user avatar
  • 55.7k
1 vote
2 answers
95 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 ...
user avatar
  • 55.7k
1 vote
1 answer
64 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://...
user avatar
  • 707
0 votes
0 answers
97 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 &...
user avatar
2 votes
1 answer
264 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 ...
user avatar
  • 121
0 votes
2 answers
59 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 ...
user avatar
  • 139
0 votes
1 answer
824 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 ...
user avatar
  • 103
1 vote
1 answer
4k 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 ...
user avatar
  • 283
1 vote
0 answers
721 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/...
user avatar
  • 111
2 votes
1 answer
37 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 ...
user avatar
0 votes
1 answer
111 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 ...
user avatar
1 vote
1 answer
221 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 ...
user avatar
  • 2,807
0 votes
1 answer
425 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 ...
user avatar
  • 123
1 vote
2 answers
1k 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 ...
user avatar
3 votes
1 answer
1k 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) ...
user avatar
2 votes
1 answer
2k 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|...
user avatar
  • 69
1 vote
1 answer
36 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,...
user avatar
0 votes
0 answers
228 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 ...
user avatar
  • 111
0 votes
1 answer
221 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 ...
user avatar
  • 111
1 vote
1 answer
4k 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 ...
user avatar
3 votes
1 answer
1k 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 ...
user avatar
0 votes
1 answer
1k 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 ...
user avatar
  • 1
2 votes
0 answers
1k 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 ...
user avatar
2 votes
1 answer
2k 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. ...
user avatar
  • 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 ...
user avatar
  • 111
0 votes
1 answer
727 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 ...
user avatar
-1 votes
2 answers
7k views

How do I install a PostgreSQL extension in Windows? [closed]

In Linux (with Ubuntu or Debian) if I want to install gen_salt(), I first install the -contrib package. sudo apt install postgresql-contrib Then I install the extension in the database, psql -d ...
user avatar
3 votes
2 answers
96 views

PostgreSQL GiST compress skeleton when no compression required

I'm trying to implement my GiST index, where storage type is the same as column type (bytea). They are even going to have same length because these are bitarrays and unions are just disjunctions of ...
user avatar
  • 133
0 votes
1 answer
167 views

PostgreSQL extension installation, best practices

I have a SQL only postgres extension I want to make public and share on GitHub, but I want to make sure the installation process is clear to users. I've just been copying them to the correct ...
user avatar
  • 1
6 votes
1 answer
2k views

Custom location for postgis extension .control file [closed]

I am trying to install the postgis extension on my database, which resides on a web hosting service. The problem is that the control files seem to have to be found in /package/host/localhost/postgresq-...
user avatar
  • 181
19 votes
1 answer
14k views

PostgreSQL two different ways to generate a UUID: gen_random_uuid vs uuid_generate_v4?

What is the difference between the following two functions gen_random_uuid() provided by pgcrypto extension uuid_generate_v4() provided by uuid-ossp extension Are they both the same behind the ...
user avatar
-1 votes
1 answer
225 views

Indexing the condition lower(f) LIKE '%foo%'

How do I index the following statement with an un-anchored search pattern SELECT somefield FROM sometable WHERE lower(somefield2) like '%foo%'; Some rows have more than 2k bytes.
user avatar
2 votes
1 answer
1k views

Use PGXN to install pg_repack on postgres 9.6 on top of Ubuntu 14.04

So I have Ubuntu 14.04 with postgres 9.6 and I wanted to install pg_repack so I did sudo apt-get install pgxnclient` sudo pgxn install pg_repack but I got an error (sorry I forgot the error) and I ...
user avatar
5 votes
2 answers
382 views

PostgreSQL Citus Extension Temp Table

Is it possible with citus extension in PostgreSQL to create temp table that is copied to each worker node (like reference table)? When I run SQL like this: DROP TABLE IF EXISTS mypoint; CREATE TEMP ...
user avatar
2 votes
1 answer
340 views

Installing Postgresql parray_gin extension on mac, include not found

I am trying to install parray_gin in PostgreSQL 10 on macOS, but I can't get it to build. I'm running pgxn install parray_gin. This first gave me a missing stdio.h error, so I installed the xcode ...
user avatar
3 votes
1 answer
2k views

Installing PostGIS in a different schema causes topology extension to not find geometry type

I have a sql dump of the schema of an existing database that is working correctly. When I try to load this into a new, empty database psql new_db < dump.sql, I get the following error: ERROR: ...
user avatar
  • 133
4 votes
1 answer
15k views

Do I need to execute "create extension pgcrypto" everytime?

I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart....
user avatar
3 votes
4 answers
14k views

How to install pgcrypto extension in postgres 10 on CentOS 7

I'm in the process of creating a postgres database for production in CentOS 7. So I already installed (yum install postgresql10-server postgresql10 after adding the repos of course) and configured ...
user avatar
  • 33
0 votes
1 answer
367 views

When running `make installcheck` I get can not connect to database

On Ubuntu when I run make installcheck I'm getting /usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/9.5/bin' --inputdir=...
user avatar
  • 55.7k
0 votes
1 answer
643 views

`make install` on a module, I get `pgxs.mk:114: recipe for target 'install' failed`

I'm trying to install a PostgreSQL module (pg-bignum). When I try make install, I get the following at the bottom of the log /usr/bin/install: will not overwrite just-created '/usr/share/postgresql/9....
user avatar
  • 55.7k
1 vote
2 answers
802 views

Hash function and custom C type for PostgreSQL

I am creating a custom type for PostgreSQL. The type is as follow: typedef struct { unsigned long prefix; unsigned long long id; } CustomType; I have build all the in and out functions. ...
user avatar
  • 179
0 votes
1 answer
470 views

Postgresql install extension

I installed Posgresql version 10 (stable) through the source package (compiling manually) in the directory opt/pgv10/ .. on my OS LInux Debian Version 8. I would like to use the HIPOPG extension 1, 2 ...
user avatar
  • 191