[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [HACKERS] SPI_exec ERROR in pl/r of R 3.2.4 on PostgreSQL on Windows 7
From: Andre Mikulec <andre_mikulec () hotmail ! com>
Date: 2016-04-30 16:39:54
Message-ID: CO2PR03MB240702E0C7EEA851684A4A759C670 () CO2PR03MB2407 ! namprd03 ! prod ! outlook ! com
[Download RAW message or body]
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
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic