From: | "dandl" <david(at)andl(dot)org> |
---|---|
To: | |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: SPI_exec ERROR in pl/r of R 3.2.4 on PostgreSQL on Windows 7 |
Date: | 2016-05-03 14:39:05 |
Message-ID: | 005501d1a549$96ba1c30$c42e5490$@andl.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Windows 7 vs 10 makes no odds here. What matters is whether your Postgres or R installations are x64 or x86.
Any x64 Windows can run x64 or x86 software perfectly happily, but you can’t mix a DLL with an EXE of a different flavour. They have to match.
Regards
David M Bennett FACS
_____
Andl - A New Database Language - andl.org
From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Dave Cramer
Sent: Tuesday, 3 May 2016 11:55 PM
To: Andre Mikulec <andre_mikulec(at)hotmail(dot)com>
Cc: Joe Conway <mail(at)joeconway(dot)com>; 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
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 <mailto:davec(at)postgresintl(dot)com>
www.postgresintl.com <http://www.postgresintl.com>
On 30 April 2016 at 12:39, Andre Mikulec <andre_mikulec(at)hotmail(dot)com <mailto: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 <mailto:Andre_Mikulec(at)Hotmail(dot)com>
________________________________________
From: Joe Conway <mail(at)joeconway(dot)com <mailto:mail(at)joeconway(dot)com> >
Sent: Friday, April 29, 2016 5:02 PM
To: Andre Mikulec; pgsql-hackers(at)postgresql(dot)org <mailto: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 <mailto:andre_mikulec(at)hotmail(dot)com> >
Sent: Friday, April 29, 2016 10:58 AM
To: pgsql-hackers(at)postgresql(dot)org <mailto: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 <http://postgresql.org>
Regular Windows pre-compiled PostgreSQL 9.5.1 downladed from downloaded from postgresql.org <http://postgresql.org>
Regular Windows pre-compiled PostgreSQL 9.5.2 downladed from downloaded from postgresql.org <http://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.
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
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.
extern int SPI_execute(const char *src, bool read_only, long tcount);
/* 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 <mailto:Andre_Mikulec(at)Hotmail(dot)com>
--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org <mailto:pgsql-hackers(at)postgresql(dot)org> )
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2016-05-03 14:44:09 | Re: max_worker_processes missing some documentation |
Previous Message | Craig Ringer | 2016-05-03 14:36:22 | Re: pg_dump broken for non-super user |