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

Does anyone else have a Windows 7 installation we can test this on ?

This
https://github.com/postgres-plr/plr/files/191013/plr-8.3.0.16-pg9.5-win32.zip
is actually a 64 bit version built on windows 10. I've had one confirmation
that it works.

Dave

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 30 April 2016 at 12:39, Andre Mikulec <andre_mikulec(at)hotmail(dot)com> wrote:

> 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(at)ANONYMOUSX /c/Users/AnonymousUser/postgresql-9.4.1/contrib
> $ make -C plr clean
>
> AnonymousUser(at)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(at)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(at)Hotmail(dot)com
>
> ________________________________________
> From: Joe Conway <mail(at)joeconway(dot)com>
> Sent: Friday, April 29, 2016 5:02 PM
> To: Andre Mikulec; pgsql-hackers(at)postgresql(dot)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(at)hotmail(dot)com>
> Sent: Friday, April 29, 2016 10:58 AM
> To: pgsql-hackers(at)postgresql(dot)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(at)Hotmail(dot)com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>