Re: SPI_exec ERROR in pl/r of R 3.2.4 on PostgreSQL on Windows 7

Joe,

"
Who did the compiling? Did you compile everything yourself, or use
binary installers for some of it? If so, which ones?
"

This is really a continuation of the experience I had with Dave Cramer in here.

Postgresql 9.5 support #1
https://github.com/postgres-plr/plr/issues/1


To try to figure out the problem, ( and perhaps? eliminate Microsoft from the problem),
I compiled a PostgreSQL [debug] version myself.

C:\Users\AnonymousUser\Desktop\PostgreSQL.9.5.1\App\PgSQL>chcp 1252 > nul && "%PGSQL%\bin\psql.exe"
psql (9.5.1)
Type "help" for help.

postgres=# select version();
                                                          version
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.1 on i686-pc-mingw32, compiled by gcc.exe (x86_64-posix-seh-rev0, Built by MinGW-W64 project) 5.3.0, 64-bit
(1 row)

I also built a non-debug plr.dll/plr myself too.
I modified ( mostly simplified ) https://github.com/jconway/plr/blob/master/Makefile
in the Makefile, I eliminated ( by much trial and error ) the OS non_window stuff, the pkg-config stuff, and the  PGXS stuff .

Then I did,
AnonymousUser@ANONYMOUSX /c/Users/AnonymousUser/postgresql-9.4.1/contrib
$ make -C plr clean

AnonymousUser@ANONYMOUSX /c/Users/AnonymousUser/postgresql-9.4.1/contrib
$  make -C plr all

So now I have my own plr.dll.

Then, I followed the instructions ( INSTALL.txt ) found in here.
http://www.joeconway.com/plr/plr-8.3.0.16-pg9.4-win64.zip

However, I used my own plr.dll/plr
Seems, that in the destination, I had to copy plr.dll to plr, but that seems to work fine.

Later, after I finish following "create extension plr;" found in http://www.joeconway.com/plr/doc/plr-install.html

I do

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

postgres=#   select plr_environ();

 (PGDATA,C:/Users/AnonymousUser/Desktop/PostgreSQL.9.5.1/Data/data)
 (PGDATABASE,postgres)
 (PGLOCALEDIR,"C:\\Users\\AnonymousUser\\Desktop\\PostgreSQL.9.5.1\\App\\PgSQL\\share\\")
 (PGLOG,"C:\\Users\\AnonymousUser\\Desktop\\PostgreSQL.9.5.1\\Data\\log.txt")
 (PGSQL,"C:\\Users\\AnonymousUser\\Desktop\\PostgreSQL.9.5.1\\App\\PgSQL")
 (PGSYSCONFDIR,C:/Users/AnonymousUser/Desktop/PostgreSQL.9.5.1/App/PgSQL/etc)
 (PGUSER,postgres)

 (R_ARCH,/x64)
 (R_HOME,C:/Users/AnonymousUser/Desktop/R-3.2.4)
 (R_KEEP_PKG_SOURCE,yes)
 (R_LIBS_USER,"C:\\Users\\AnonymousUser\\Documents/R/win-library/3.2")
 (R_USER,"C:\\Users\\AnonymousUser\\Documents")

NOTE: The directory structure is from Postgre 9.4 Portable,  I just use ONLY the directory structure.
The one and ONLY file I use is the pgsql.cmd batch startup file ( I did my 'environment' and 'user friendly modifications.' )

postgres=#

I do this, I get no results, and no error.

postgres=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'plr_modules' AND relnamespace = 2200;
 relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------
(0 rows)

But, then this ( R language code ) strangely works.

postgres=# select r_version(); # THIS IS THE 'R LANGUAGE' ( IF THE EXTENSION 'works' )
                    r_version
-------------------------------------------------
 (platform,x86_64-w64-mingw32)
 (arch,x86_64)
 (os,mingw32)
 (system,"x86_64, mingw32")
 (status,"")
 (major,3)
 (minor,2.4)
 (year,2016)
 (month,03)
 (day,10)
 ("svn rev",70301)
 (language,R)
 (version.string,"R version 3.2.4 (2016-03-10)")
 (nickname,"Very Secure Dishes")
(14 rows)

This does not work.
postgres=# select upper(typname) || 'OID' as typename, oid from pg_catalog.pg_type where typtype = 'b' order by typname;
ERROR:  could not open file "base/12373/1247": No such file or directory

This ( R language code ) that uses that SQL does not work.

postgres=# select load_r_typenames();
ERROR:  R interpreter expression evaluation error
DETAIL:  Error in pg.spi.exec(sql) :
  error in SQL statement : could not open file "base/12373/1247": No such file or directory
CONTEXT:  In R support function pg.spi.exec
In PL/R function load_r_typenames

In real-time ( exactly right now ), I have exactly  PostgreSQL 9.4.1 (and pl/r and R.3.1.2 )on Windows 7 running on port 5433.
This Postgre 9.1.1 uses the  same hard disk ( 9.4.1 and 9.5.1 (above) share the exact same hard disk.)

C:\Users\AnonymousUser\Desktop\PostgreSQL.9.4.1\App\PgSQL> "%PGSQL%\bin\psql.exe" --port 5433
psql (9.4.1)
Type "help" for help.

postgres=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit
(1 row)

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

postgres=#  select plr_environ();

 (PGDATA,C:/Users/AnonymousUser/Desktop/PostgreSQL.9.4.1/Data/data)
 (PGDATABASE,postgres)
 (PGLOCALEDIR,"C:\\Users\\AnonymousUser\\Desktop\\PostgreSQL.9.4.1\\App\\PgSQL\\share\\")
 (PGLOG,"C:\\Users\\AnonymousUser\\Desktop\\PostgreSQL.9.4.1\\Data\\log.txt")
 (PGSQL,"C:\\Users\\AnonymousUser\\Desktop\\PostgreSQL.9.4.1\\App\\PgSQL")
 (PGSYSCONFDIR,C:/Users/AnonymousUser/Desktop/PostgreSQL.9.4.1/App/PgSQL/etc)
 (PGUSER,postgres)

 (R_ARCH,/x64)
 (R_HOME,C:/Users/AnonymousUser/Desktop/R.3.1.2/App/R-Portable)
 (R_LIBS_USER,"C:\\Users\\AnonymousUser\\Documents/R/win-library/3.1")
 (R_USER,"C:\\Users\\AnonymousUser\\Documents")

This also returns zero rows. ( Should it do that? ).

postgres=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'plr_modules' AND relnamespace = 2200;
 relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages |
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-
(0 rows)

postgres=# select r_version();
                    r_version
-------------------------------------------------
 (platform,x86_64-w64-mingw32)
 (arch,x86_64)
 (os,mingw32)
 (system,"x86_64, mingw32")
 (status,"")
 (major,3)
 (minor,1.2)
 (year,2014)
 (month,10)
 (day,31)
 ("svn rev",66913)
 (language,R)
 (version.string,"R version 3.1.2 (2014-10-31)")
 (nickname,"Pumpkin Helmet")
(14 rows)

This works.  It did not work (above) in PostreSQL 9.5.1.

postgres=# select upper(typname) || 'OID' as typename, oid from pg_catalog.pg_type where typtype = 'b' order by typname;
       typename       |  oid
----------------------+-------
 _ABSTIMEOID          |  1023
...
 XMLOID               |   142
(135 rows)


This ( R language) works. It did not work ( above ) in PostreSQL 9.5.1

postgres=# select load_r_typenames();
 load_r_typenames
------------------
 OK
(1 row)

So in summary, I can not see anything wrong with pl/r.

Something seems not fully right with  the IO of Postgresql 9.5 on Windows [7] [64 bit]

One difference that I can currently see this that 9.4.1 psql uses a different code page than 9.5.1 psql .
9.5.1 psql has to be forced to use 1252 ( chcp 1252 ) .  Does this matter?

Are there any recommended changes to make the IO of 9.5.1 behave like the IO of 9.4.1?

In comparing the 9.4.1 postgresql.conf to the 9.5.1 postgresql.conf,
these are the differences found. ( These are both defaults )

AnonymousUser@ANONYMOUSX /c/Users/AnonymousUser/postgresql-9.5.1/contrib
$ diff --ignore-space-change --ignore-all-space --ignore-blank-lines  --strip-trailing-cr /c/Users/AnonymousUser/Desktop/PostgreSQL.9.4.1/Data/data/postgresql.conf /c/Users/AnonymousUser/Desktop/PostgreSQL.9.5.1/Data/data/postgresql.conf

86d85
< #ssl_renegotiation_limit = 512MB      # amount of data between renegotiations
92a92
> #row_security = on
165c165
< #effective_io_concurrency = 1         # 1-1000; 0 disables prefetching
---
> #effective_io_concurrency = 0         # 1-1000; 0 disables prefetching
187a188
> #wal_compression = off                        # enable compression of full-page writes
199d199
< #checkpoint_segments = 3              # in logfile segments, min 1, 16MB each
200a201,202
> #max_wal_size = 1GB
> #min_wal_size = 80MB
206c208
< #archive_mode = off           # allows archiving to be done
---
> #archive_mode = off           # enables archiving; off, on, or always
230a233,234
> #track_commit_timestamp = off # collect timestamp of transaction commit
>                               # (change requires restart)
259a264,265
> #wal_retrieve_retry_interval = 5s     # time to wait before retrying to
>                                       # retrieve WAL after a failed attempt
434a441
> #log_replication_commands = off
440a448,454
> # - Process Title -
>
> #cluster_name = ''                    # added to process titles if nonempty
>                                       # (change requires restart)
> #update_process_title = on
>
>
452d465
< #update_process_title = on
519a533,534
> #gin_fuzzy_search_limit = 0
> #gin_pending_list_limit = 4MB
578a594
> #operator_precedence_warning = off

Are there any recommended postgresql.conf changes?

What do I do next?
Will anyone help me?
Do I report this to someone?
Do I file a bug?
Do I try to debug PostreSQL 9.5 on Windows myself? ( I am not a C/C++ guy.  I am a DBA. )

Thank you,
Andre Mikulec
Andre_Mikulec@Hotmail.com

________________________________________
From: Joe Conway <mail@joeconway.com>
Sent: Friday, April 29, 2016 5:02 PM
To: Andre Mikulec; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] SPI_exec ERROR in pl/r of R 3.2.4 on PostgreSQL on Windows 7

On 04/29/2016 07:58 AM, Andre Mikulec wrote:
> I am working with pl/r compiled for R.3.2.4 64 bit on PostgreSQL 9.5.1
> 64 bit on Windows 7 64 bit

Who did the compiling? Did you compile everything yourself, or use
binary installers for some of it? If so, which ones?

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

________________________________________

From: Andre Mikulec <andre_mikulec@hotmail.com>
Sent: Friday, April 29, 2016 10:58 AM
To: pgsql-hackers@postgresql.org
Subject: SPI_exec ERROR in pl/r of R 3.2.4 on PostgreSQL on Windows 7

I am working with pl/r compiled for R.3.2.4 64 bit on PostgreSQL 9.5.1 64 bit on Windows 7 64 bit

At the end of this issue, I am getting the following error.
https://github.com/postgres-plr/plr/issues/1

  ERROR:  could not open file "base/12373/2663": No such file or directory
  LINE 1: SELECT NULL FROM pg_catalog.pg_class WHERE relname = 'plr_mo...
                         ^
QUERY:  SELECT NULL FROM pg_catalog.pg_class WHERE relname = 'plr_modules' AND relnamespace = 2200

The error seems to be coming from SPI_exec.

If I run this SQL manually from psql
SELECT NULL FROM pg_catalog.pg_class WHERE relname = 'plr_modules' AND relnamespace = 2200

The result is returned and is correct.

* The problem is not my hard disk. *
I am running multiple versions of PostgreSQL on the same hard disk. *

The following run fine.
Regular Windows pre-compiled PostgreSQL 9.4.1 downladed from downloaded from postgresql.org
Regular Windows pre-compiled PostgreSQL 9.5.1 downladed from downloaded from postgresql.org
Regular Windows pre-compiled PostgreSQL 9.5.2 downladed from downloaded from postgresql.org

THe problem is not security.
I am gave 'Full Access' to Administators group , EveryOne group, and Users group to
the directories containing all of the PostgreSQL directries containing both/either data and binaries.

I have shutdown all virus software: AVG.

The pl/r and plr.dll for R 3.1.2 64 bit runs fine on PostgreSQL 9.4.1 64bit on Windows 7 64
The pl/r source code has not changed at least since PostgreSQL 9.4.1.

I have physically examined the pl/r source code.
It seems relatively simple to understand.

THe error seems to only come from here.
https://raw.githubusercontent.com/jconway/plr/master/plr.c


static bool
haveModulesTable(Oid nspOid)
{
StringInfo
sql = makeStringInfo();
char
  *sql_format = "SELECT NULL "
"FROM pg_catalog.pg_class "
"WHERE "
"relname = 'plr_modules' AND "
"relnamespace = %u";
    int  spiRc;

appendStringInfo(sql, sql_format, nspOid);

spiRc = SPI_exec(sql->data, 1);
if (spiRc != SPI_OK_SELECT)
/* internal error */
elog(ERROR, "haveModulesTable: select from pg_class failed");

return SPI_processed == 1;
}


I noticed that the using in the SPI_exec function *seems* to be similar in the source code.

https://raw.githubusercontent.com/postgres/postgres/3aff33aa687e47d52f453892498b30ac98a296af/src/test/regress/regress.c

query = (char *) palloc(100 + NAMEDATALEN * 3 +
strlen(fieldval) + strlen(fieldtype));

sprintf(query, "insert into %s select * from %s where %s = '%s'::%s",
SPI_getrelname(rel), SPI_getrelname(rel),
SPI_fname(tupdesc, 1),
fieldval, fieldtype);

if ((ret = SPI_exec(query, 0)) < 0)
elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (insert ...) returned %d",
when, *level, ret);



AND SPI_exec *seems* to be similar here

https://raw.githubusercontent.com/postgres/postgres/8b99edefcab1e82c43139a2c7dc06d31fb27b3e4/src/backend/commands/matview.c

StringInfoData querybuf;
initStringInfo(&querybuf);

/* Analyze the temp table with the new contents. */
appendStringInfo(&querybuf, "ANALYZE %s", tempname);
if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
elog(ERROR, "SPI_exec failed: %s", querybuf.data);

It is defined here.

https://raw.githubusercontent.com/postgres/postgres/3aff33aa687e47d52f453892498b30ac98a296af/src/include/executor/spi.h

extern int SPI_execute(const char *src, bool read_only, long tcount);


https://raw.githubusercontent.com/postgres/postgres/39c283e498de1bb7c3d5beadfffcf3273ae8cc27/src/backend/executor/spi.c

/* Parse, plan, and execute a query string */
int
SPI_execute(const char *src, bool read_only, long tcount)
{
_SPI_plan
plan;
int
res;

if (src == NULL || tcount < 0)
return SPI_ERROR_ARGUMENT;

res = _SPI_begin_call(true);
if (res < 0)
return res;

memset(&plan, 0, sizeof(_SPI_plan));
plan.magic = _SPI_PLAN_MAGIC;
plan.cursor_options = 0;

_SPI_prepare_oneshot_plan(src, &plan);

res = _SPI_execute_plan(&plan, NULL,
InvalidSnapshot, InvalidSnapshot,
read_only, true, tcount);

_SPI_end_call(true);
return res;
}


/* Obsolete version of SPI_execute */
int
SPI_exec(const char *src, long tcount)
{
return SPI_execute(src, false, tcount);
}


My Big question is the following,

Has there been any change in the PostgreSQL IO code from 9.4.1. to 9.5.1
that may be possibly causing this problem ( in SPI_exec or elsewhere )?

  ERROR:  could not open file "base/12373/2663": No such file or directory

Any answers with any possibilities of any directions are welcome.

Thank you,
Andre Mikulec
Andre_Mikulec@Hotmail.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers