Обсуждение: C-functions using SPI Missing Magic Block Error

Hello,

I've got a problem with user defined functions in C using SPI.

Using: PostgreSQL 8.3.7, Codeblocks (GNU GCC Compiler), Windows Server 2003 R2

I compiled the file "pgExampleSPI.c" with the following code without any error:

/* Use 32-bit timer (provided header file uses 64-bit timer, not
* compatible with Windows postgreSQL versions */
#define _USE_32BIT_TIME_T

#include "postgres.h"
#include "executor\spi.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern Datum count_person (PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(count_person);

__declspec(dllexport) Datum count_person(PG_FUNCTION_ARGS) {
    int32    ret;
    SPI_connect();
    ret = SPI_exec("SELECT count(*) FROM person", 0);
    SPI_finish();
    PG_RETURN_INT32(ret);
}

- then I've copied the resulting file "pgExampleSPI.dll" into the directory "G:\PostgreSQL\8.3\lib"
- I tried to load to function into PostgreSQL with the command:

CREATE FUNCTION count_person() RETURNS int
     AS 'G:/PostgreSQL/8.3/lib/pgExampleSPI.dll', 'count_person'
     LANGUAGE C STRICT;

- and received the following error description:

ERROR:  incompatible library "G:/PostgreSQL/8.3/lib/pgExampleSPI.dll": missing magic block
TIP:  Extension libraries are required to use the PG_MODULE_MAGIC macro.


After searching google for about 5 hours in couldn't find a way to solve this problem.

Can anybody help me, please?

Thanks, Max.
___________________________________________________________
WEB.DE DSL ab 19,99 Euro/Monat. Bis zu 150,- Euro Startguthaben und
50,- Euro Geldprämie inklusive! https://freundschaftswerbung.web.de

Saitenheini@web.de writes:
> I compiled the file "pgExampleSPI.c" with the following code without any error:

> /* Use 32-bit timer (provided header file uses 64-bit timer, not
> * compatible with Windows postgreSQL versions */
> #define _USE_32BIT_TIME_T

> #include "postgres.h"
> #include "executor\spi.h"

> #ifdef PG_MODULE_MAGIC
> PG_MODULE_MAGIC;
> #endif

That's not going to do anything because you didn't #include the header
that defines PG_MODULE_MAGIC.  I think it's fmgr.h but not sure offhand.
I'd suggest taking out the #ifdef so you actually get an error if the
definition isn't provided.

            regards, tom lane

Thanks for reply Tom!

I've tried several version:

#define _USE_32BIT_TIME_T

#include "postgres.h"
#include "fmgr.h"
#include "executor\spi.h"

/*
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
*/

extern Datum count_person (PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(count_person);

__declspec(dllexport)
Datum count_person(PG_FUNCTION_ARGS) {
 int32    ret;
 SPI_connect();
 ret = SPI_exec("SELECT count(*) FROM person", 0);
 SPI_finish();
 PG_RETURN_INT32(ret);
}

but still the same error occurs.

I also tried to include pgmagic.h, which I found in here:
http://archives.postgresql.org/pgsql-committers/2006-05/msg00369.php
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/Attic/pgmagic.h?rev=1.1&content-type=text/x-cvsweb-markup

but this produces a compiler error:
G:\PostgreSQL\8.3\include\server\pgmagic.h|40|error: conflicting types for 'Pg_magic_struct'|
G:\PostgreSQL\8.3\include\server\fmgr.h|367|error: previous declaration of 'Pg_magic_struct' was here|
G:\PostgreSQL\8.3\include\server\pgmagic.h|45|error: conflicting types for 'PGModuleMagicFunction'|
G:\PostgreSQL\8.3\include\server\fmgr.h|383|error: previous declaration of 'PGModuleMagicFunction' was here|
G:\PostgreSQL\8.3\include\server\pgmagic.h|50|warning: "PG_MODULE_MAGIC" redefined|
G:\PostgreSQL\8.3\include\server\fmgr.h|388|warning: this is the location of the previous definition|
G:\PostgreSQL\8.3\include\server\pgmagic.h|66|warning: "PG_MODULE_MAGIC_DATA" redefined|
G:\PostgreSQL\8.3\include\server\fmgr.h|370|warning: this is the location of the previous definition|
||=== Build finished: 4 errors, 4 warnings ===|

I tried to build it in Visual C++ Express 2010 but compiler error occur (SPI_connect())

Any Idea to solve the problem ?
___________________________________________________________
WEB.DE DSL ab 19,99 Euro/Monat. Bis zu 150,- Euro Startguthaben und
50,- Euro Geldprämie inklusive! https://freundschaftswerbung.web.de

On 07/02/2010 08:13 AM, Saitenheini@web.de wrote:
> Thanks for reply Tom!
>
> I've tried several version:
>
> #define _USE_32BIT_TIME_T
>
> #include "postgres.h"
> #include "fmgr.h"
> #include "executor\spi.h"
>
> /*
> #ifdef PG_MODULE_MAGIC
> PG_MODULE_MAGIC;
> #endif
> */

> but still the same error occurs.

You commented out

   PG_MODULE_MAGIC;

 -- don't do that. Just remove the #ifdef around it.

HTH,

Joe

Thanks, I've already tried that. I doesn't make any difference. Still the same error.

in  Visual C++  I can't even compile it, although I set all links and compiler directories like it was told in this
post:
http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html

I don't have more ideas.
What could I do?

>On 07/02/2010 08:13 AM, Saitenheini@web.de wrote:
>> Thanks for reply Tom!
>>
>> I've tried several version:
>>
>> #define _USE_32BIT_TIME_T
>>
>> #include "postgres.h"
>> #include "fmgr.h"
>> #include "executor\spi.h"
>>
>> /*
>> #ifdef PG_MODULE_MAGIC
>> PG_MODULE_MAGIC;
>> #endif
>> */
>
>> but still the same error occurs.
>
>You commented out
>
>   PG_MODULE_MAGIC;
>
> -- don't do that. Just remove the #ifdef around it.
>
>HTH,
>
>Joe
>
___________________________________________________________
WEB.DE DSL ab 19,99 Euro/Monat. Bis zu 150,- Euro Startguthaben und
50,- Euro Geldprämie inklusive! https://freundschaftswerbung.web.de

On 07/02/2010 08:36 AM, Saitenheini@web.de wrote:
> Thanks, I've already tried that. I doesn't make any difference. Still the same error.
>
> in  Visual C++  I can't even compile it, although I set all links and compiler directories like it was told in this
post:
> http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html
>
> I don't have more ideas.
> What could I do?

Building extensions on Windows is a royal pain (at least for me it is).
I would suggest either:

a) switch to Linux (I know, probably not a helpful suggestion)

 -or-

b) possibly try adding your function to an existing contrib, e.g.
   tablefunc, and see if you can compile that

The build infrastructure on Windows with VC++ is difficult to work with.
In order to get PL/R to compile with VC++ I had to hack the provided
perl build scripts. That could be a third option for you, but (b) is
probably far easier for you.

Joe

I'll try b) and hope I can find a solution.
Thanks for your support Joe.

Kind Regards, Max.

>On 07/02/2010 08:36 AM, Saitenheini@web.de wrote:
>> Thanks, I've already tried that. I doesn't make any difference. Still the same error.
>>
>> in  Visual C++  I can't even compile it, although I set all links and compiler directories like it was told in this
post:
>> http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html
>>
>> I don't have more ideas.
>> What could I do?
>
>Building extensions on Windows is a royal pain (at least for me it is).
>I would suggest either:
>
>a) switch to Linux (I know, probably not a helpful suggestion)
>
> -or-
>
>b) possibly try adding your function to an existing contrib, e.g.
>   tablefunc, and see if you can compile that
>
>The build infrastructure on Windows with VC++ is difficult to work with.
>In order to get PL/R to compile with VC++ I had to hack the provided
>perl build scripts. That could be a third option for you, but (b) is
>probably far easier for you.
>
>Joe
>
>
___________________________________________________________
WEB.DE DSL ab 19,99 Euro/Monat. Bis zu 150,- Euro Startguthaben und
50,- Euro Geldprämie inklusive! https://freundschaftswerbung.web.de