subject:"\[HACKERS\] SPI_exec ERROR in pl\/r of R 3.2.4 on PostgreSQL on Windows 7"

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

2016-05-03 Thread dandl

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-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Dave Cramer
Sent: Tuesday, 3 May 2016 11:55 PM
To: Andre Mikulec 
Cc: Joe Conway ; pgsql-hackers@postgresql.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

da...@postgresintl.com  

www.postgresintl.com  

 

On 30 April 2016 at 12:39, Andre Mikulec  > 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

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

2016-05-03 Thread Dave Cramer

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  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

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

2016-04-30 Thread Andre Mikulec

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)

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

2016-04-29 Thread Joe Conway

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



signature.asc
Description: OpenPGP digital signature

[HACKERS] SPI_exec ERROR in pl/r of R 3.2.4 on PostgreSQL on Windows 7

2016-04-29 Thread Andre Mikulec

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();

/* Analyze the temp table with the new contents. */
appendStringInfo(, "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(, 0, sizeof(_SPI_plan));
plan.magic = _SPI_PLAN_MAGIC;
plan.cursor_options = 0;

_SPI_prepare_oneshot_plan(src, );

res = _SPI_execute_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

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

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

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

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

[HACKERS] SPI_exec ERROR in pl/r of R 3.2.4 on PostgreSQL on Windows 7

5 matches

Site Navigation

Mail list logo

Footer information