Add pg.throwlog (character message) similar to Add pg.thrownotice (character message). This would facilitate explicit logging from within PL/R functions. By default, NOTICE does not go to the postgres log, but LOG does.

Following function causes a backend crash
test=# CREATE OR REPLACE FUNCTION test_svg () RETURNS text AS $$ library("svglite") # set up to output svg to a string s = svgstring() # fetch the string svg = s() # turn off output to the string dev.off() return(svg) $$ LANGUAGE 'plr' IMMUTABLE;

The following test code

create or replace function altrep_test() returns int[] as $$
  as.numeric( .Internal(mmap_file("postmaster.opts", "int", FALSE, FALSE, FALSE)) )
$$ language plr;

select cardinality(altrep_test());

create or replace function altrep_test2() returns int[] as $$
  .Internal(mmap_file("postmaster.opts", "int", FALSE, FALSE, FALSE))
$$ language plr;

select cardinality(altrep_test2());

will crash on the second test with

Error: cannot access data pointer for this mmaped vector
Fatal error: unable to initialize the JIT

Calls like INTEGER() and REAL() try to materialize objects (unless explicitly prohibited in the example above) whereas INTEGER_ELT and REAL_ELT won't at the expense of not being inlined in newer R.

All fast track copy code should check if (!ALTREP(rval)). Alternatively, I suggest to use corresponding xxx_ELT ALTREP-aware accessors with a small performance penalty. Since there are checks for NA anyway, we might as well just copy an element to avoid extra code complexity.

For some reason we are not generating automatic releases

I am trying to compile plr in the source tree of postgresql-9.6.3. When i execute 'make' or 'USE_PGXS=1 make' the following error appears:
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I"./" -I"-I/usr/lib64/R/include" -I/usr/lib64/R/include -I. -I./ -I/usr/local/pgsql_9.6/include/server -I/usr/local/pgsql_9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -DPKGLIBDIR=\"/usr/local/pgsql_9.6/lib64\" -DDLSUFFIX=\".so\" -DR_HOME_DEFAULT=\"/usr/lib64/R\" -c -o pg_backend_support.o pg_backend_support.c pg_backend_support.c: In function ‘find_in_dynamic_libpath’: pg_backend_support.c:401:2: error: too few arguments to function ‘GetConfigOptionByName’ char *Dynamic_library_path = GetConfigOptionByName("dynamic_library_path", NULL); ^ In file included from /usr/local/pgsql_9.6/include/server/tcop/tcopprot.h:26:0, from plr.h:64, from pg_backend_support.c:33: /usr/local/pgsql_9.6/include/server/utils/guc.h:368:14: note: declared here extern char *GetConfigOptionByName(const char *name, const char **varname, ^ <builtin>: recipe for target 'pg_backend_support.o' failed make: *** [pg_backend_support.o] Error 1

After a little googling around i found this:
https://github.com/laurenz/oracle_fdw/issues/188
Maybe this is the solution for the problem here too. Is there a hint or a workaround how this issue can be solved?

Hello,
I am getting error while trying to install package for postgresql-13-plr.

sudo apt-get install postgresql-13-plr
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgresql-13-plr

pgdg list is updated for postgresql 13, as I am able to install other packages like Postgresql-13 servers , clients.
Could you please tell when this would be available in apt repo.

I'm writing a PL/R function body from which I want to write instrumentation log messages to a 'log' table in the database. I also want to abort the PL/R function with failure if I catch an error in the code. The problem is that if I abort the function with a stop() call in the body, all prior INSERT statements don't commit. I implement my 'INSERT' calls using normal functions:

pg <<- pg.spi.prepare('INSERT INTO demo_model.log (tstamp, message) VALUES (CURRENT_TIMESTAMP, $1);'
    , c(TEXTOID))
pg.spi.execp(pg, list('An info message.'))
## ...
## Code throws error after the above info message, aborting function
## Info message never gets logged to log table.

Either there is a bug, or it is working by design, or there is some control code I'm missing to make this work.

Please test PL/R for PostgreSQL 12/13 and R 4.0.3 (Windows/Centos7)

I recently overcame an issue getting PLR installed. Now that everything is running, I went to run a PLR function and received an error:
SELECT load_r_typenames();

ERROR: could not access "C:/Program Files/PostgreSQL/9.6/lib/plr": No such file or directory

This particular error came from an EnterpriseDB 9.6 install, but I also received the same error (different path) for a BigSQL 9.6 install (I changed my env variables accordingly).

The error isn't very useful. The plr.dll file is certainly in the location specified. What could be making it inaccessible?

Summary: if a table contains a bytea column, plr triggers crash

Example:

create table t_example(
  x text unique not null
);

create function ai_example() returns trigger
language plr as $_$
  return(pg.tg.new)
$_$;

create trigger trg_ai_example 
after insert on t_example 
for each row 
execute procedure ai_example();

That works, but after adding a column of type bytea, the system crashes, see the following commands:

me@demo@[local]> insert into t_example (x) values ('test');
INSERT 0 1
me@demo@[local]> alter table t_example add column file bytea;
ALTER TABLE
me@demo@[local]> insert into t_example (x) values ('create crash');
Server beendete die Verbindung unerwartet
        Das heißt wahrscheinlich, dass der Server abnormal beendete
        bevor oder während die Anweisung bearbeitet wurde.
Die Verbindung zum Server wurde verloren.  Versuche Reset: Fehlgeschlagen.
@?@> \q

The error message means: server closed the connection unexpectedly. ... Try to reset
connection failed

The same error occurs if t_example is created with a bytea column.

Dave,

Postgre languages PL/pgSQL, PL/Perl, and PL/perl can perform Transaction Management (since pg 11).

https://www.postgresql.org/docs/11/plpgsql-transactions.html

Transaction Management (and within the PL languages) was implemented in this commit.

postgres/postgres@8561e48

PL/R deserves to be able to also do this.

Dave Cramer,

I am receiving this message everytime, that I login to Travis (https://travis-ci.org/).

Please be aware travis-ci.org will be shutting down in several weeks, 
with all accounts migrating to travis-ci.com. Please stay tuned here for more information.

I am aware that plr uses Travis to "test a build".

Perhaps, plr can also use another CI to also "test a build", for example, in the case that Travis actually, does stop serving open source projects.

One possible CI, is Github Actions.

I received this error.

checkpoint: Part of the Reproducible R Toolkit from Microsoft
https://mran.microsoft.com/documents/rro/reproducibility/
Error in setwd("~/CHECKPOINTED") : cannot change working directory

But this works.

postgres=# select r_version();
                    r_version
-------------------------------------------------
 (platform,x86_64-w64-mingw32)
 (arch,x86_64)
 (os,mingw32)
 (system,"x86_64, mingw32")
 (status,"")
 (major,3)
 (minor,3.3)
 (year,2017)
 (month,03)
 (day,06)
 ("svn rev",72310)
 (language,R)
 (version.string,"R version 3.3.3 (2017-03-06)")
 (nickname,"Another Canoe")
(14 rows)

But then I modified the site file to be ...
Rprofile.site

if (interactive()) {

  owd <- getwd()
  setwd(paste0(Sys.getenv("R_HOME"),"../.."))

  setwd(owd)

}

Then I get the following error

ERROR:  could not open file "base/16410/164635": No such file or directory
CONTEXT:  SQL statement "SELECT NULL FROM pg_catalog.pg_class WHERE relname = 'plr_modules' AND relnamespace = 2200"

Then I tried

setwd("W:\\R-3.3._\\R_USER_3.3._\\CHECKPOINTED")

then I received back the message ...

Checkpoint: Part of the Reproducible R Toolkit from Microsoft
https://mran.microsoft.com/documents/rro/reproducibility/
Error in authorizeFileSystemUse(checkpointLocation) :
  promise already under evaluation: recursive default argument reference or earlier problems?
ERROR:  could not open file "base/16410/164635": No such file or directory
CONTEXT:  SQL statement "SELECT NULL FROM pg_catalog.pg_class WHERE relname = 'plr_modules' AND relnamespace = 2200"
STATEMENT:  select r_browser();

Then I revered back. Everything works now.

So the case seems the R or plr or postgreSQL does not like the current
directory to be 'attempted to be changed' (at least during R startup).

There is no issue here.
This is just a note of 'something to be aware of'.

This weekend I compiled PostgreSQL 10 (using MSYS2/MINGW64 on Windows 10) and plr 08.03.00.17.

Are there any issues involving plr on PostgreSQL 10, for example, function formal arguments or anything else?

postgres=# select version();
                                                                           version                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0_High_Performance.wide.LangPython on x86_64-w64-mingw32, compiled by x86_64-w64-mingw32-gcc.exe (Rev1, Built by MSYS2 project) 7.2.0, 64-bit
(1 row)

postgres=# select plr_version();
 plr_version
-------------
 08.03.00.17
(1 row)

postgres=# select r_version();
                    r_version
-------------------------------------------------
 (platform,x86_64-w64-mingw32)
 (arch,x86_64)
 (os,mingw32)
 (system,"x86_64, mingw32")
 (status,"")
 (major,3)
 (minor,4.2)
 (year,2017)
 (month,09)
 (day,28)
 ("svn rev",73368)
 (language,R)
 (version.string,"R version 3.4.2 (2017-09-28)")
 (nickname,"Short Summer")
(14 rows)

postgres=#

I am on MacOSX.
I already have R and RStudio installed.
I am running Postgres 13 that was installed using Homebrew.

I cloned the repo and from the plr repo ran:

user@MBP plr % USE_PGXS=1 make

I receive the following error message:

Package libR was not found in the pkg-config search path.
Perhaps you should add the directory containing `libR.pc'
to the PKG_CONFIG_PATH environment variable
No package 'libR' found
Package libR was not found in the pkg-config search path.
Perhaps you should add the directory containing `libR.pc'
to the PKG_CONFIG_PATH environment variable
No package 'libR' found
Package libR was not found in the pkg-config search path.
Perhaps you should add the directory containing `libR.pc'
to the PKG_CONFIG_PATH environment variable
No package 'libR' found
Package libR was not found in the pkg-config search path.
Perhaps you should add the directory containing `libR.pc'
to the PKG_CONFIG_PATH environment variable
No package 'libR' found
Package libR was not found in the pkg-config search path.
Perhaps you should add the directory containing `libR.pc'
to the PKG_CONFIG_PATH environment variable
No package 'libR' found

Dave,

After anyone using Git, edits either of the files, Mkvcbuild.pm or vcregress.pl,
and shifts the contents lines, another person, should not have to re-read those files, and then
re-create the msvc.diff again (after each time that the lines are shifted).

Automation can read those files and re-write inside them, the needed changes.

Exactly, I have Rscript.exe calling a simple R program, that patches Mkvcbuild.pm and vcregress.pl.

"%R_HOME%\bin\%RBIN%\Rscript.exe" --vanilla %APPVEYOR_BUILD_FOLDER%\msvc.diff.R

Here is that automation.

appveyor.yml

https://github.com/AndreMikulec/plr/blob/3d9bf4e06c624b098ed1e29d22a02319549f2826/appveyor.yml#L440

Here is the R file.

msvc.diff.R

https://github.com/AndreMikulec/plr/blob/3d9bf4e06c624b098ed1e29d22a02319549f2826/msvc.diff.R

Here are "automatic patch" plr build successes.

REL_12_0

https://ci.appveyor.com/project/AndreMikulec/plr/builds/38637503/job/t8vfakrbxq29av5b?fullLog=true#L319

REL_13_STABLE

https://ci.appveyor.com/project/AndreMikulec/plr/builds/38637503/job/yrr38jtom6g3s7yh?fullLog=true#L302

master

(build fails for other reasons - but the patching is correct)
https://ci.appveyor.com/project/AndreMikulec/plr/builds/38637503/job/50a3xg0dpi10ig3w?fullLog=true#L302

Dave Cramer,

Requested, (in a possible near future github pull request by me), are the following.

First, the file

PL/R User’s Guide - R Procedural Language

https://github.com/postgres-plr/plr/blob/master/userguide.md
in the Installation section, would include the words and format:

In Mingw, MSYS, or MSYS2,
if R is built and installed using a sub-architecture, as explained in the section Sub-architectures in
https://cran.r-project.org/doc/manuals/r-release/R-admin.html
for example, in an R from

R-x.y.z for Windows (32/64 bit)

https://cran.r-project.org/bin/windows/base/

then, include the environment variable R_ARCH.
For example R_ARCH=/x64 (or R_ARCH=/i386 as appropriate):

export R_HOME=/c/progra~1/R/R-4.0.4
export PATH=$PATH:/c/progra~1/PostgreSQL/13/bin
export R_ARCH=/x64
USE_PGXS=1 make
USE_PGXS=1 make install

Second, when a change is only documentation, the situation is not necessary to build in Appveyor.

The file https://github.com/postgres-plr/plr/blob/master/appveyor.yml
would include:

skip_commits:
  files:
    - *.md

Note, this pull request, is still expected to build, because the exception (skip_commits), does not yet exist in the file appveyor.yml?

Third, when a change is only documentation, the situation is not necessary to build in travis. The single new git commit submission would include "[skip ci]".

Information is from the following:

exclude files from triggering a travis ci build on github

https://stackoverflow.com/questions/32791470/exclude-files-from-triggering-a-travis-ci-build-on-github

If a pull request is made (and accepted), the the single new commit message will be similar to the following:

git commit -m "82 Documentation on Support R sub-architectures [skip ci]"  -m "Description ..........";

This follows:

How to commit a change with both “message” and “description” from the command line? [duplicate]

https://stackoverflow.com/questions/16122234/how-to-commit-a-change-with-both-message-and-description-from-the-command-li

Fourth, do you want files:
plr--8.4--8.4.1.sql renamed to plr--8.4--8.4.2.sql
plr--8.4.1.sql renamed to plr--8.4.2.sql
with the pull request?

Note, a github pull request can not send a tag:

Adding Tags to a Pull Request

https://stackoverflow.com/questions/12278660/adding-tags-to-a-pull-request

Note, if a pull request is made (and accepted),
when it is ready (and has not yet been started), will come from the following:

https://github.com/AndreMikulec/plr/tree/82_Documentation_on_Support_R_sub-architectures

Using mingw64, I am trying to get a 64 bit plr.dll extension made for 64 bit R on 64 bit windows.
I am stuck.

Here is where I am stuck.
I am getting this error, that it is looking for DLSUFFIX. Maybe It can not find a Makefile. I do not know.

AnonymousUser@ANONYMOUSX /c/Users/AnonymousUser/postgresql-9.5.1/contrib/plr
$ cd /c/Users/AnonymousUser/postgresql-9.5.1/contrib/plr

$ make  USE_PGXS=1 PG_CONFIG=/c/MinGW/msys/1.0/local/pgsql/bin/pg_config.exe

x86_64-w64-mingw32-gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels \
  -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2  \
  -I/c/Users/AnonymousUser/Desktop/R-3.2.4/include -I. -I./ -Ic:/MinGW/msys/1.0/local/pgsql/include/server \
  -Ic:/MinGW/msys/1.0/local/pgsql/include/internal -I./src/include/port/win32  \
  -DEXEC_BACKEND  "-Ic:/MinGW/msys/1.0/local/pgsql/lib/pgxs/src/makefiles/../../src/include/port/win32" \
  -Ic:/MinGW/msys/1.0/local/pgsql/include/server/port/win32  -c -o pg_backend_support.o pg_backend_support.c

pg_backend_support.c:296:2: error: #error "DLSUFFIX must be defined to compile this file."
 #error "DLSUFFIX must be defined to compile this file."
  ^
pg_backend_support.c: In function 'expand_dynamic_library_name':
pg_backend_support.c:333:37: error: 'DLSUFFIX' undeclared (first use in this function)
  new = palloc(strlen(name) + strlen(DLSUFFIX) + 1);
                                     ^
pg_backend_support.c:333:37: note: each undeclared identifier is reported only once for each function it appears in

I try just simply 'make.'

AnonymousUser@ANONYMOUSX /c/Users/AnonymousUser/postgresql-9.5.1/contrib/plr
$ make

x86_64-w64-mingw32-gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement \
  -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing              \
  -fwrapv -fexcess-precision=standard -O2  -I/c/Users/AnonymousUser/Desktop/R-3.2.4/include       \
  -I. -I. -I../../src/include -I./src/include/port/win32                                            \
  -DEXEC_BACKEND  "-I../../src/include/port/win32"  -c -o pg_backend_support.o pg_backend_support.c

pg_backend_support.c:296:2: error: #error "DLSUFFIX must be defined to compile this file."
 #error "DLSUFFIX must be defined to compile this file."
  ^
pg_backend_support.c: In function 'expand_dynamic_library_name':
pg_backend_support.c:333:37: error: 'DLSUFFIX' undeclared (first use in this function)
  new = palloc(strlen(name) + strlen(DLSUFFIX) + 1);
                                     ^
pg_backend_support.c:333:37: note: each undeclared identifier is reported only once for each function it appears in
make: *** [pg_backend_support.o] Error 1

I all thre combos produced the same error.

make          
make          USE_PGXS=1 
make          USE_PGXS=1 PG_CONFIG=/c/MinGW/msys/1.0/local/pgsql/bin/pg_config.exe

I did notice, that the source code has a Makefile.global.in, but not a Makefile.global ( that is seen in the code of plr/Makefile).
Do I supposed to rename the file. Or, do I do something else.

https://github.com/jconway/plr/Makefile

ifdef USE_PGXS
ifndef PG_CONFIG
PG_CONFIG := pg_config
endif
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/plr                             
top_builddir = ../..
include $(top_builddir)/src/Makefile.global      # I do not have this file:  I have Makefile.global.in, Makefile, Makefile.shlib, nls-global.mk, win32.mak
include $(top_srcdir)/contrib/contrib-global.mk  
endif

I check the source

postgresql-9.5.1.tar.gz_SOURCE_EXTRACTED\postgresql-9.5.1\src

Makefile.global is not in there either.

Thank you,
Andre Mikulec
[email protected]

I am trying to use Visual Studio 2013 Community Edition to compile a 64bit plr.dll for R 3.2.4 64bit and PostgreSQL 9.5.1 64bit on Windows 7 64bit.

I am stuck.

I have basically tried to follow the two instructions here.

This seems to have gone O.K. I have an R.lib now. I do not know if it works or not. I did not understand the part about the 'diffs.'

I successfully did the following recently in order to build 64 bit PL/R on Windows 7:
https://github.com/jconway/plr/blob/master/compilingplr.md

I combined the R.lib with the Postgre.lib in here . . .
I followed the rest of the instrucions here, but ended up getting a whole bunch of token errors.
So I am stuck again.

Compiling PostgreSQL extensions with Visual Studio on Windows
Posted On 11 Jan, 2014 - By craig.ringer - With 38 Comments
http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows/

If anyone has method to make a plr.dll, I am all ears. I want to repeat it with the exact same version of Visual Studio, the exact same version of PostgreSQL, and the exact same version of R.

Thank you,
Andre Mikulec
[email protected]

I would like to use PL/R with R 4.0.0 and PostgreSQL 12.3 on Windows. Could you please provide a package for these versions?

I'm wanting to understand how PL/R invokes R. Does it start one R process and pass it R functions one-at-a-time, waiting for each to complete before invoking the next? Does it start a separate R process for each invoked R function as needed? Something else?

I apologize if this is the wrong place to ask this; if there is a PL/R mailing list I will he happy to direct my question appropriately. I'm concerned about concurrency where PostgreSQL is on the back-end of a website that serves concurrent requests.

Hello!

Where can I find a pl/r release that works on a postgresql 9.6 instance installed on a windows 7 (x32) machine??? I'm already using the pl/r extension successfully on a Linux Centos7 server but I can't run it on my local machine...

Regards,

Miguel Ortega

see https://www.postgresql.org/docs/devel/static/sql-createprocedure.html

Not sure if anything needs to be done.

plr fails to build against PG 11beta1:

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fdebug-prefix-map=/build/postgresql-11-LcCi7f/postgresql-11-11~beta1=. -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I"./" -I"-I/usr/share/R/include" -I/usr/share/R/include -I. -I./ -I/usr/include/postgresql/11/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/mit-krb5 -DPKGLIBDIR=\"/usr/lib/postgresql/11/lib\" -DDLSUFFIX=\".so\" -DR_HOME_DEFAULT=\"/usr/lib/R\"  -c -o plr.o plr.c
plr.c: In function ‘do_compile’:
plr.c:959:35: error: ‘FormData_pg_proc {aka struct FormData_pg_proc}’ has no member named ‘proiswindow’; did you mean ‘prokind’?
  function->iswindow = procStruct->proiswindow;
                                   ^~~~~~~~~~~
                                   prokind
plr.c:1077:76: error: invalid type argument of ‘->’ (have ‘FormData_pg_attribute {aka struct FormData_pg_attribute}’)
     function->result_fld_elem_typid[i] = get_element_type(tupdesc->attrs[i]->atttypid);
                                                                            ^~

A release with a fix would be appreciated so we can update the Debian package for PG11.
Thanks!

Dave and Joe,

No issue is here.

The case seems that the compiled plr on R 3.4 (eventually) also works on R 3.5.
However the 'pickup' is not immediate.

First, I tried keeping the plr on R 3.4 with postgres pointing to an R 3.5.

I received this error.

postgres=# create extension plr;
ERROR:  could not load library "C:/PG-10._/lib/plr.dll": The specified module could not be found.

At that point, I went forward and compiled a plr on R 3.5.

AnonymousUser@ANONYMOUST MINGW64 /w/postgres_constructions/build_pg.10.0_High_Performance.wide.LangPython
$ make -C contrib/plr clean
make: Entering directory '/w/postgres_constructions/build_pg.10.0_High_Performance.wide.LangPython/contrib/plr'

Your R_HOME environment variable should be set. It is R_HOME="/w/R-3.5._/App/R-Portable"

... omitted ...

AnonymousUser@ANONYMOUST MINGW64 /w/postgres_constructions/build_pg.10.0_High_Performance.wide.LangPython
$ make -C contrib/plr all
make: Entering directory '/w/postgres_constructions/build_pg.10.0_High_Performance.wide.LangPython/contrib/plr'

Your R_HOME environment variable should be set. It is R_HOME="/w/R-3.5._/App/R-Portable"

x86_64-w64-mingw32-gcc ...omitted..

Shut down postgres

Change the PG-10._.bat file contents

REM R version 3.4.2 (2017-09-28) -- "Short Summer"
REM set     R_HOME=W:\R-3.4._\App\R-Portable
REM R version 3.5.0 (2018-04-23) -- "Joy in Playing"
    set     R_HOME=W:\R-3.5._\App\R-Portable 

REM     seems 10 is required
    set     PATH=%R_HOME%\bin\x64;%PATH%

In C:\PG-10._\lib
Rename plr.dll to plr.dll.BACKUP.180427.R.3.4.Production

From W:\postgres_constructions\build_pg.10.0_High_Performance.wide.LangPython\contrib\plr
Copy plr.dll to C:\PG-10._\lib

Startup
PG-10._.bat

postgres=# create extension plr;
ERROR:  could not load library "C:/PG-10._/lib/plr.dll": The specified module could not be found.

Next, I saved old postgres/postres plr functions to a file.sql
Then, I did.

postgres=# drop extension plr cascade;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to function print(text)
drop cascades to function r_median(double precision[])
drop cascades to function r_any_os_ffunc(anyarray)
drop cascades to function r_any_os_sfunc(anyarray,anyelement)
drop cascades to function r_any_os(anyelement)
DROP EXTENSION
postgres=#

postgres=# create extension plr;
ERROR:  could not load library "C:/PG-10._/lib/plr.dll": The specified module could not be found.

Stop postgresql

swap out to old R 3.4 plr.dll
update PG-10._.bat pointing from R 3.5 to 3.4 

Start postgresql

create extenion plr;
drop extension plr;

Stop postgresql

swap out to new R 3.5 plr.dll
update PG-10._.bat pointing from R 3.4 to 3.5

Start postgresql

postgres=# create extension plr;
ERROR:  could not load library "C:/PG-10._/lib/plr.dll": The specified module could not be found.

Stop postgresql

swap out to old R 3.4 plr.dll
update PG-10._.bat pointing from R 3.5 to 3.4

Start postgresql

create extension plr;
drop extension plr;

Stop postgresql

swap out to new R 3.5 plr.dll
update PG-10._.bat pointing from R 3.4 to 3.5 

Start postgresql
On the third try the plr on R 3.5 extension was found.

Waiting for 0 seconds, press a key to continue ...
psql (10.0_High_Performance.wide.LangPython)
Type "help" for help.

postgres=# create extension plr;
CREATE EXTENSION
postgres=# select plr_version();
 plr_version
-------------
 08.03.00.17
(1 row)

postgres=# select r_version();
                    r_version
-------------------------------------------------
 (platform,x86_64-w64-mingw32)
 (arch,x86_64)
 (os,mingw32)
 (system,"x86_64, mingw32")
 (status,"")
 (major,3)
 (minor,5.0)
 (year,2018)
 (month,04)
 (day,23)
 ("svn rev",74626)
 (language,R)
 (version.string,"R version 3.5.0 (2018-04-23)")
 (nickname,"Joy in Playing")
(14 rows)
postgres=# drop extension plr;
DROP EXTENSION

However, now the R 3.4 compiled extension now works on R 3.5
Stop postgresql

swap out to old R 3.4 plr.dll
CHANGE DIFFERENT PG-10._.bat pointing from R 3.4 to 3.5 

Start postgresql

postgres=# create extension plr;
CREATE EXTENSION
postgres=# select r_version();
                    r_version
-------------------------------------------------
 (platform,x86_64-w64-mingw32)
 (arch,x86_64)
 (os,mingw32)
 (system,"x86_64, mingw32")
 (status,"")
 (major,3)
 (minor,5.0)   -- R 3.5 using R 3.4 DLL
 (year,2018)
 (month,04)
 (day,23)
 ("svn rev",74626)
 (language,R)
 (version.string,"R version 3.5.0 (2018-04-23)")
 (nickname,"Joy in Playing")
(14 rows)

Thefore, the case seems that the old R 3.4 plr does work on R 3.5.
However, the pickup is not immediate.
Some repetitive shutdown/startups/shutdown/starts seem necessary.

Dave,

I have been reading the userguide.md.

The case may seem that some enhancements may be useful.

1. Adjusted PostgreSQL keyword capitalization plus syntax enhancements.
2. Added a paragraph starting with If R is built and installed using a sub-architecture
3. Added the comprehensive section: Installing from a Pre-Built "plr"
4. Added another easy to understand window example (and some reorganization).
5. Added documentation on Stored Procedures (I just used the the "regression test case").
6. Added documentation on the Inline Handler (I just used the "regression test case").

I have done that. Check it out.
https://github.com/AndreMikulec/postgres_plr/blob/2307f69e3c74b46167b0fa9a78f8e864b1e0d923/userguide.md

I have added to the changelog.md

• [Support R sub-architectures - February 08, 2021 - git hash - 8250e99]

Check that out:
https://github.com/AndreMikulec/postgres_plr/blob/2307f69e3c74b46167b0fa9a78f8e864b1e0d923/changelog.md

What do you think?
Can the documentation (or some of the documentation), be included in plr?
What changes do you recommend?

I did not found any example of returning new additional attributes in case of window function from PL/R documentation.

-- Input data: *************************************************************************************

CREATE TABLE ts_der_sample (custid int, month int, usage numeric, billing numeric);

INSERT INTO ts_der_sample VALUES (1,1,52,26);
INSERT INTO ts_der_sample VALUES (1,2,54,54);
INSERT INTO ts_der_sample VALUES (1,3,58,58);
INSERT INTO ts_der_sample VALUES (1,4,47,47);
INSERT INTO ts_der_sample VALUES (1,5,38,38);
INSERT INTO ts_der_sample VALUES (1,6,22,22);

INSERT INTO ts_der_sample VALUES (2,1,22,44);
INSERT INTO ts_der_sample VALUES (2,2,24,24);
INSERT INTO ts_der_sample VALUES (2,3,30,44);
INSERT INTO ts_der_sample VALUES (2,4,28,28);
INSERT INTO ts_der_sample VALUES (2,5,31,44);
INSERT INTO ts_der_sample VALUES (2,6,30,44);

INSERT INTO ts_der_sample VALUES (3,1,22,44);
INSERT INTO ts_der_sample VALUES (3,2,24,24);
INSERT INTO ts_der_sample VALUES (3,3,NULL,44);
INSERT INTO ts_der_sample VALUES (3,4,28,28);
INSERT INTO ts_der_sample VALUES (3,5,31,NULL);
INSERT INTO ts_der_sample VALUES (3,6,30,44);

-- PL/R function which gives output as array: ********************************************************

CREATE OR REPLACE FUNCTION TS_DER_01_31(numeric,character)
RETURNS numeric[] AS
$BODY$

# ///////////////////////////////////////////////////////////////////////////
# Type of derivate: Static aggregation
# Derivate: Largest mean shift between two consecutive windows
# Author: Jüri Kuusik
# Last modified: 13.03.2020
# ///////////////////////////////////////////////////////////////////////////

x <- farg1
var_name <- arg2
df <- data.frame(a=as.numeric(0), b=as.integer(0))
vecResult <- base::tryCatch(expr=as.numeric(feasts::shift_level_max(x)), error=function(e){c(NA,NA)})
df$a[1] <- vecResult[1]
df$b[1] <- vecResult[2]
names(df) <- paste0(c('TS_DER_01_30_size_','TS_DER_01_30_time_index_'),var_name)
return(df)

$BODY$
LANGUAGE 'plr' WINDOW;

-- test function:
select *,
TS_DER_01_31(usage,'usage') OVER w AS TS_DER_01_31_usage
from ts_der_sample where custid < 3
WINDOW w AS (PARTITION BY custid ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

select *,
TS_DER_01_31(usage,'usage') OVER w AS TS_DER_01_31_usage
from ts_der_sample where custid = 3
WINDOW w AS (PARTITION BY custid ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

-- Non working PL/R function for having output as additional attributes:

CREATE OR REPLACE FUNCTION TS_DER_01_31(numeric)
RETURNS table(TS_DER_01_31_size numeric, TS_DER_01_31_time_index numeric) AS
$BODY$

# ///////////////////////////////////////////////////////////////////////////
# Type of derivate: Static aggregation
# Derivate: Largest mean shift between two consecutive windows
# Author: Jüri Kuusik
# Last modified: 16.03.2020
# ///////////////////////////////////////////////////////////////////////////

x <- farg1
df <- data.frame(a=as.numeric(0), b=as.integer(0))
vecResult <- base::tryCatch(expr=as.numeric(feasts::shift_level_max(x)), error=function(e){c(NA,NA)})
df$a[1] <- vecResult[1]
df$b[1] <- vecResult[2]
return(df)

$BODY$
LANGUAGE 'plr' WINDOW;

-- test function:
select *,
TS_DER_01_31(usage) OVER w
from ts_der_sample where custid < 3
WINDOW w AS (PARTITION BY custid ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

select *,
TS_DER_01_31(usage) OVER w
from ts_der_sample where custid = 3
WINDOW w AS (PARTITION BY custid ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

Rejoice! A new R release (v 4.1.0)

https://github.com/r-windows/r-base/releases/tag/v4.1.0
https://github.com/r-windows/r-base/releases/download/v4.1.0/R-4.1.0-win.zip

https://cran.r-project.org/doc/manuals/r-release/NEWS.html

Just now (within the last hour)
https://cran.r-project.org/bin/windows/base/
plr Appveyor downlaod link
https://cran.r-project.org/bin/windows/base/R-4.1.0-win.exe

https://www.jumpingrivers.com/blog/new-features-r410-pipe-anonymous-functions/

R-4.1.0 is released!
Rejoice! A new R release (v 4.1.0) is due on 18th May 2021. 
Typically most major R releases don’t contain that many new features, 
but this release does contain some interesting and important changes.

simple function like:
create function rfloat4(f float4) returns float4 as $$ return (as.numeric(f)) $$ language plr;

select float4(null)

causes the server process to terminate

I have freshly installed

• postgresql-12.5-1-windows-x64.exe
• R-3.6.0-win.exe

Downloaded and placed the files to their folders for

• plr-b479c024-pg12-R3.6.0-x64-Release.zip
• created a new empty database
• created plr extension
• ran checks
  Checked that [64-bit] C:\R\R-3.6.0 works in R-Studio
  SELECT public.plr_environ() - returns (R_HOME,"C:\R\R-3.6.0")
  SELECT public.plr_version() - returns 8.4

SELECT public.r_version() - never returns and completely freezes Postgres (must delete pid file to restart)

I am not sure what could I check or change.
Postgres log doesn't contain any useful info.

Window function accepting int4 will actually get every other element because array is allocated with sizeof(Datum) while fast track memory copy uses sizeof(int). 64 bit systems are affected.

I'll work on it.

When updating from 8.4.2 to 8.4.4, we get the following:

$ psql -c 'ALTER EXTENSION plr UPDATE;'
ERROR:  extension "plr" has no update path from version "8.4.2" to version "8.4.4"

It looks like there are missing upgrade script(s):

$ ls $prefix/share/extension/plr*
.../share/extension/plr--8.3.0.18--8.4.sql
.../share/extension/plr--8.4.1--8.4.2.sql
.../share/extension/plr--8.4.4.sql
.../share/extension/plr--8.4--8.4.1.sql
.../share/extension/plr.control
.../share/extension/plr--unpackaged--8.4.4.sql

Fresh Win10x64 machine. Installed EnterpriseDB's PostgreSQL 10.0 and R 3.4.2.

Path includes:

C:\Program Files\PostgreSQL\10\bin
C:\Program Files\PostgreSQL\10\lib
C:\Program Files\R\R-3.4.2\bin\x64

R_HOME is:

C:\Program Files\R\R-3.4.2

Using PLR 8.3.0.17 for pg10 and R3.4.2.

plr.dll is in:

C:\Program Files\PostgreSQL\10\lib\

plr.control, plr--8.3.0.17.sql, plr--unpackaged--8.3.0.17.sql are in:

C:\Program Files\PostgreSQL\10\share\extension\

README.plr is in:

C:\Program Files\PostgreSQL\10\doc\extension\

Then I start up the service, connect to a db and try:
CREATE EXTENSION plr SCHEMA plr VERSION "8.3.0.17";
Which yields:

ERROR: could not find function "plr_call_handler" in file "C:/Program Files/PostgreSQL/10/lib/plr.dll"

This is a new message for me. Not finding anything useful on Google. Ideas?

P.S. I can't wait for the day when: pgc install plr-pg10 is a thing for BigSQL! =)

beta version 64bit plr.dll build on win10 using VC 2013

plr-8.3.0.16-pg9.5-win32.zip

OS: Windows Server 2016 Standard
PotsgreSQL: 12.1
R: 4.0.2 (installed 64-bit only in C:\Program Files\R, not C:\Program Files\R\R-4.0.2)

Set system environment variable R_HOME=C:\Progra~1\R.
Added %R_HOME%\bin\x64 and "C:\Program Files\PostgreSQL\12\bin" to the system environment variable Path.
Installed MSYS2.
Used 64-bit make.exe from http://www.equation.com/servlet/equation.cmd?fa=make (copied in C:\Windows) because it's easier than adding make to MSYS2.
Downloaded https://github.com/postgres-plr/plr as ZIP, unarchived and in plr-master directory executed commands from MSYS2 MinGW 64-bit shell:

export R_HOME=/c/progra~1/R
export PATH=$PATH:/c/progra~1/PostgreSQL/12/bin
USE_PGXS=1 make

Error returned:

Makefile:33: C:/progra~1/PostgreSQL/12/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory
make: *** No rule to make target 'C:/progra~1/PostgreSQL/12/lib/pgxs/src/makefiles/pgxs.mk'.  Stop.

Sure there is no C:\Program Files\PostgreSQL\12\lib\pgxs directory as Windows installer (https://www.postgresql.org/download/windows/) doesn't create such. Readme is chaotic for Windows users and it's not clear if existing PostgreSQL installation can be used at all, or source files must be used instead and PostgreSQL compiled with PL/R support.

Binaries for R 4.x and PostgreSQL 12.x would be preferred as requested in #70.

The file userguide.md should have an example of a

custom type (tuple) argument in a window function

from the recent commit/pull
Add support for pg custom type (tuple) arguments in window PL/R functions #56
fffa692

Dave,

I did build using PostgreSQL 14:

To Appveyor (Windows builds)

• added Git tag REL_14_0
• added EnterpriseDB 14.0-1 Windows x86-64

To Github Actions (Linux builds)

• added 14
• removed 9.5

Today
master...AndreMikulec:master_pg_REL_14_0_plr_REL8_4_4

Final Appveyor
https://ci.appveyor.com/project/AndreMikulec/plr/builds/40989829

Final Github Actions
https://github.com/AndreMikulec/plr/runs/3769652863

Shall I squash, re-test and re-build, and then create a pull request?

Update changelog to reflect the #56
make sure the sql files are correct for create extension
tag 8.4.2
inform users

plr binnaries are incompatible with Postgresql 10

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;f=src/backend/executor/functions.c;h=a9c35cf85ca1ff72f16f0f10d7ddee6e582b62b8

Breaks the current build

I've been using PLR on Postgres 9.4 successfully for a long time on a Windows 7 machine, but I'm currently faced with setting up a new machine with a fresh install of Windows 10 x64. On the new machine, I'm looking to upgrade to Postgres 9.6 with R 3.3.1, and BigSQL's PGC command line interface has proven extremely easy for setting up extensions (e.g. PostGIS) for their distributions. I was hoping to be able to install PLR via PGC's easy interface, but no such luck.

I managed to find the 9.6 Windows plr.dll here. I dropped it into C:\PostgreSQL\pg96\lib\postgresql, which is a slightly different location from what I'm used to on EnterpriseDB distributions. I placed plr.control, plr.sql, plr--8.3.0.17.sql and plr--unpackaged--8.3.0.17.sql into C:\PostgreSQL\pg96\share\postgresql\extension and then attempted to install the extension with the following error:

ERROR: could not load library "C:/POSTGR~1/pg96/lib/postgresql/plr.dll": The specified module could not be found.

To be sure, I tried installing PLR 8.3.0.16 on an instance of BigSQL's Postgres 9.5 with R 3.3.0 using the plr.dll from here, but I ran into the same error.

Do you think these problems are just related to the BigSQL distributions? Has anyone gotten PLR working on those yet?

The code in postgresql that elicited this change has been reverted

All,

I created a github repository that uses MSYS2 and Appveyor to build (a postgreSQL 13 database and) the extension "plr". That repository is here.

https://github.com/AndreMikulec/postgresql_ext_plr_build

Releases are here: I built each PG database version (currently only 13) each time to two(2) versions of R (currently 3.6.3 and 4.0.3).

https://github.com/AndreMikulec/postgresql_ext_plr_build/releases

However, R on Windows, the R from, https://cran.r-project.org/bin/windows/base/, is now always sub-architecture.

Sub-architectures
https://cran.r-project.org/doc/manuals/r-release/R-admin.html#Sub_002darchitectures

Therefore, from a downloaded R from , https://cran.r-project.org/bin/windows/base/
the install, always installs R exe files and dll files into bin/x64 or bin/i386.

This install always installs R exes and dlls into bin/x64:

R-4.0.3-win.exe /SP- /VERYSILENT /DIR=%R_HOME%OLD /COMPONENTS=main,x64,translations /NOICONS /TASKS=

This install always installs R exes and dlls into bin/i386 (no more just \bin):

R-4.0.3-win.exe /SP- /VERYSILENT /DIR=%R_HOME%OLD /COMPONENTS=main,i386,translations /NOICONS /TASKS=

Therefore, to be able to build "plr" using (almost) this Makefile

https://github.com/postgres-plr/plr/blob/master/Makefile

I had to modify the Makefile here

https://github.com/AndreMikulec/postgresql_ext_plr_Makefile/blob/master/Makefile

with the change from

r_libdir1x = ${R_HOME}/bin
r_libdir2x = ${R_HOME}/lib

to

r_libdir1x = ${R_HOME}/bin${R_ARCH}
r_libdir2x = ${R_HOME}/lib${R_ARCH}

If this would be a permanent change in the Makefile https://github.com/postgres-plr/plr/blob/master/Makefile, I think that this is a safe change. If the environment variable R_ARCH is not set, then ${R_ARCH} becomes a zero length string and the Makefile stays the same. If R_ARCH is set, then the sub-architecture works.

The only way R_ARCH would every be set can be the reasons (1) The user ran the build from within R itself (this would be rare and weird), or (2) the user manually created an R_ARCH environmental variable (because the user has a sub-architecture version of R).

Would prefer to drop my github repository

https://github.com/AndreMikulec/postgresql_ext_plr_Makefile

and just use the correct Makefile in

https://github.com/postgres-plr/plr/blob/master/Makefile

What do you think?

Thanks,
Andre

It seems that there is no plr for pg 9.6 for window
I always get an error message when I try to "create extension plr" as below.

ERROR: could not load library "C:\Postgs~1\pg96\lib\plr.dll": The specified m
odule could not be found.

https://github.com/postgres-plr/plr/blob/master/pg_backend_support.c#L151

In order for the next test to pass

All,

First,
from the excellent appveyor.yml and Appveyor builds here (from Mikhail Titov):
sqlite_fdw/.appveyor.yml
https://github.com/mlt/sqlite_fdw/blob/9903e2f1d70726bfb3819908750f40126940279c/.appveyor.yml
and here
https://ci.appveyor.com/project/PurpleI2P/sqlite-fdw/history

Second, from this information here:

Developing PostgreSQL for Windows, Part 3
March 24, 2020
by Peter Eisentraut

https://www.2ndquadrant.com/en/blog/developing-postgresql-windows-part-3/

The MSVC appveyor.yml is here:
https://www.postgresql.org/message-id/d8e78714-dc77-4a64-783f-e863ba4d951f%402ndquadrant.com
and here:
https://www.postgresql.org/message-id/attachment/109034/0001-AppVeyor-configuration-for-MSVC.patch
and here:
petere/postgresql@b2e54ad
and ran here:
https://ci.appveyor.com/project/petere/postgresql/builds/36786751?fullLog=true

Third, from the information here:

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: msys2
Date: 2018-10-07 19:32:44
Message-ID: [email protected]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
2018-10-07 19:32:44 from Andrew Dunstan <andrew(at)dunslane(dot)net>
Lists: pgsql-hackers
https://www.postgresql.org/message-id/9f4f22be-f9f1-b350-bc06-521226b87f7a%40dunslane.net

Fourth, from the information here:

Package: mingw-w64-x86_64-postgresql
Libraries for use with PostgreSQL (mingw-w64)
https://github.com/msys2/MINGW-packages/blob/master/mingw-w64-postgresql/PKGBUILD
and
https://packages.msys2.org/package/mingw-w64-x86_64-postgresql

I conclude that I think that I have enough information to try to build a Visual Studio compiled "plr"
with the "REL_13_STABLE" PostreSQL database. Since Appveyor has not yet provided
a PostgreSQL 13 database, (just like in the Mikhail Titov case),
and if the appveyor.yml matrix option REL_13_STABLE is chosen, the solution, is to compile a PostgreSQL 13 database.

With you permission, I would like to try to "build "plr" with REL_13_STABLE, Visual Studio, Appveyor", and pull it into "plr" at "https://github.com/postgres-plr/plr".

Currently, I have the time and motivation. I may not have time or motivation later.

What do you think?

Thanks,
Andre

Hi Joseph,

I compiled PLR (Master) successfully on MacOs Mojave.
When I create the extension and try to create a function from the manual, I get the following error:

test=# create extension plr;
CREATE EXTENSION
test=# CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
test'# if (arg1 > arg2)
test'# return(arg1)
test'# else
test'# return(arg2)
test'# ' LANGUAGE 'plr' STRICT;
psql: ERROR: R interpreter parse error
DETAIL: R parse error caught in "
if (arg1 > arg2)
return(arg1)
else
return(arg2)
".
test=#

But when I put the "else" directly after "return(arg1)" then everything is fine:

test=# CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
test'# if (arg1 > arg2)
test'# return(arg1) else
test'# return(arg2)
test'# ' LANGUAGE 'plr' STRICT;
CREATE FUNCTION
test=#

Could it be that PLR extension has problems with line feeds?

On PostgreSQL 11 with PLR 8.4 everything works fine as expected!

James

I'm attempting to use a PLR window function to perform DBSCAN clustering, as an alternative to the function in PostGIS. Here is the code I am using:

CREATE OR REPLACE FUNCTION PLR_ClusterDBSCAN(x double precision, y double precision, eps double precision, minpoints integer)
RETURNS int
AS $$
    if (pg.state.firstpass == TRUE) {
        pg.state.firstpass <<- FALSE
        c <- dbscan(cbind(farg1, farg2), eps=eps, MinPts=minpoints)
        assign("cluster", c$cluster, env = .GlobalEnv)
    }

    return(cluster[prownum])
$$ WINDOW LANGUAGE PLR;

On a sample dataset, this takes 89502 ms to run the following query:

SELECT count(DISTINCT cid) from (select PLR_ClusterDBSCAN(x, y, 300, 15) OVER() AS cid FROM mtl_crimes) sq;

Whereas the data can be loaded into R (outside of Postgres) and the clusters are computed in 300 ms (including I/O).

Is there something grossly wrong with the PLR function posted above?

Dave,

Here is the line from the Makefile .

The version converter file, .sql file number four(4), is missing.

plr--8.3.0.18--8.4.sql

The converter file is not getting installed (in MSYS2).
E.g.
https://ci.appveyor.com/project/AndreMikulec/plr/builds/38800254/job/bvmr9j5446guyyqt#L150

/usr/bin/install -c -m 644 .//plr--8.4.1.sql .//plr--8.4--8.4.1.sql .//plr--unpackaged--8.4.1.sql  'C:/msys64/mingw64/share/POSTGR~1/extension/'

The postgres-plr last Github Action upon master (in Ubuntu), also does not install the converter file.
https://github.com/postgres-plr/plr/runs/2325975008?check_suite_focus=true#step:5:23

/usr/bin/install -c -m 644 .//plr--8.4.1.sql .//plr--8.4--8.4.1.sql .//plr--unpackaged--8.4.1.sql  '/usr/local/pgsql/share/extension/'

Note, the contents of the postgres-plr msvc .zip Github releases
have all four(4) .sql files 
(because they are copied from build, and not copied from install).

The case seems proper, to have all the .sql files.
E.g., cube" has all of the .sql files including all of the converters (spread over two lines)

https://github.com/postgres/postgres/blob/112d411fbeb56afd18c117e20b524a86afc9aba5/contrib/cube/Makefile#L10
https://github.com/postgres/postgres/blob/112d411fbeb56afd18c117e20b524a86afc9aba5/contrib/cube/Makefile#L11

DATA = cube--1.2.sql cube--1.2--1.3.sql cube--1.3--1.4.sql cube--1.4--1.5.sql \
	cube--1.1--1.2.sql cube--1.0--1.1.sql