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 da...@postgresintl.com www.postgresintl.com On 30 April 2016 at 12:39, Andre Mikulec <andre_miku...@hotmail.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@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_miku...@hotmail.com > > ________________________________________ > From: Joe Conway <m...@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_miku...@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_miku...@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 >
