Re: Query on support for trigger functions

[Mehul Doshi-A20614]

Hi Magnus,

Thanks. Here's what I did:
1. Downloaded the 8.0 source code.
2. Created a test directory in postgresql-8.0.0\src\
3. Wrote a  sample c snippet as given below:
4. Compiled using gcc (version 3.4.4) in cygwin
5. Used the following make file to generate the dll.

----------------------------------------------------------------------------
-------
 
# Makefile to build test DLL
subdir = src/test
top_builddir = ../..

NAME = test
SO_MAJOR_VERSION = 1
SO_MINOR_VERSION = 0
SHLIB_LINK = $(filter -lintl, $(LIBS)) $(BE_DLLLIBS) -lpcre

SRCS = test.c trigger_test_funcs.c
OBJS = $(SRCS:.c=.o)

include $(top_builddir)/src/Makefile.global
include $(top_builddir)/src/Makefile.shlib

MYCFLAGS = -ansi
MYINCLUDES = -I../include

all: all-lib

%.o: %.c

$(CC) -c $(MYCFLAGS) $(MYINCLUDES) $<

clean:

rm -f $(OBJS) $(NAME).dll

# TODO: dependencies

----------------------------------------------------------------------------
-------

What do I need to do to make it work?

Please let me know.

Thanks & Regards,
Mehul


-----Original Message-----
From: Magnus Hagander [mailto:mha@...]
Sent: Monday, July 25, 2005 8:45 PM
To: Mehul Doshi-A20614; pgsql-hackers-win32@...
Subject: RE: [pgsql-hackers-win32] Query on support for trigger functions
defined in a Shared library in Windows

> Hi,
>
> I would like to know whether the native Windows version of
> PostgreSQL (version 8.0.0) supports trigger functions defined
> in shared libraries. Specifically, I am trying to port a
> Linux shared object used as the snippet below shows to Windows:
>
> CREATE FUNCTION foo() returns trigger AS
> '/path/to/sharedlib.so' LANGUAGE C;
>
> Are DLLs supported in a similar context in Windows?

Yes, they should be.


> What do I need to do to port such code?

Most functions should work without modifications.


> My initial attempts resulted in a failure. I created the DLL
> and attempted to run the SQL CREATE FUNCTION which specified
> the Windows path to the DLL. However the server died without
> any clueful messages.

How exactly did you build your DLL?

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Mehul Doshi-A20614]

Hi Magnus,

Thanks. That helped me. I used MingW to compile the code. I still used 8.0.0
because it is the version that we are currently working on. I will migrate
to 8.0.3 once I am Able to validate the entire setup.

I did get another problem, if you could help me out, it would be great.
Problem Description:
1) I generated the dll using the code.
2) Added the following in postgresql.conf:
dynamic_library_path = 'C:\test\lib,$libdir'
The above didn't work so I went ahead and copied the dll into
C:\Program Files\PostgreSQL\8.0\lib\
3) I restarted the postmaster both times. I got this error both times.
:25: ERROR:  could not load library "C:/Program
Files/PostgreSQL/8.0/lib/testtrigfuncs.dll": dynamic load error

The log shows:
2005-07-26 22:38:07 DEBUG:  InitPostgres
2005-07-26 22:38:07 DEBUG:  StartTransaction
2005-07-26 22:38:07 DEBUG:  name: unnamed; blockState:       DEFAULT; state:
INPROGR, xid/subid/cid: 33475/1/0, nestlvl: 1, children: <>
2005-07-26 22:38:07 DEBUG:  CommitTransaction
2005-07-26 22:38:07 DEBUG:  name: unnamed; blockState:       STARTED; state:
INPROGR, xid/subid/cid: 33475/1/0, nestlvl: 1, children: <>
2005-07-26 22:38:07 DEBUG:  StartTransactionCommand
2005-07-26 22:38:07 DEBUG:  StartTransaction
2005-07-26 22:38:07 DEBUG:  name: unnamed; blockState:       DEFAULT; state:
INPROGR, xid/subid/cid: 33476/1/0, nestlvl: 1, children: <>
2005-07-26 22:38:07 DEBUG:  ProcessUtility
2005-07-26 22:38:07 DEBUG:  find_in_dynamic_libpath: trying "C:/Program
Files/PostgreSQL/8.0/lib/testtrigfuncs.dll"
2005-07-26 22:38:07 ERROR:  could not load library "C:/Program
Files/PostgreSQL/8.0/lib/testtrigfuncs.dll": dynamic load error
2005-07-26 22:38:07 DEBUG:  proc_exit(0)
2005-07-26 22:38:07 DEBUG:  shmem_exit(0)
2005-07-26 22:38:07 DEBUG:  exit(0)

However, when I ran the newly compiled postgres code (along with my test dll
in /usr/local/pgsql/lib) via msys. It didn't give me any such errors and
everything worked out fine. I don't know what I need to do to make this dll
which I built run with the already installed version of PostgreSQL (8.0.0).
I can upgrade it to 8.0.3 if it solves the problem.

Please let me know. Thanks & have a nice day.


Thanks & Regards,
Mehul

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

[Mehul Doshi-A20614]

Hi,

The depends test.dll showed the same result in both places.
Finally I managed to get a break-through. When I copied the dll into the bin
folder,
it started to work fine. It's like postgres seems to be looking at it's own
directory.

Though not an elegant or a good solution, it seems to work. Once the patch
comes in, I
will revert back to putting the dll in the lib folder.

Thanks & Regards,
Mehul

-----Original Message-----
From: Magnus Hagander [mailto:mha@...]
Sent: Thursday, July 28, 2005 1:33 AM
To: Mehul Doshi-A20614; pgsql-hackers-win32@...
Cc: Bruce Momjian
Subject: RE: [pgsql-hackers-win32] Query on support for trigger functions
defined in a Shared library in Windows

> Hi Magnus,
>
> Thanks. That helped me. I used MingW to compile the code. I
> still used 8.0.0 because it is the version that we are
> currently working on. I will migrate to 8.0.3 once I am Able
> to validate the entire setup.
>
> I did get another problem, if you could help me out, it would
> be great.
> Problem Description:
> 1) I generated the dll using the code.
> 2) Added the following in postgresql.conf:
> dynamic_library_path = 'C:\test\lib,$libdir'
> The above didn't work so I went ahead and copied the dll into
> C:\Program Files\PostgreSQL\8.0\lib\
> 3) I restarted the postmaster both times. I got this error both times.
> :25: ERROR:  could not load library "C:/Program
> Files/PostgreSQL/8.0/lib/testtrigfuncs.dll": dynamic load error

Yes. We really need to look at fixing that error message. I had forgotten it
completely :-(

Bruce, you think we can sneak that in after feature freeze? I would call it
a bugfix :-) I haven't looked at what it'd take, but it shouldn't be
*too* hard I think...
If so, please stick it on the TODO so it's not forgotten again.

> However, when I ran the newly compiled postgres code (along
> with my test dll in /usr/local/pgsql/lib) via msys. It didn't
> give me any such errors and everything worked out fine. I
> don't know what I need to do to make this dll which I built
> run with the already installed version of PostgreSQL (8.0.0).

This sounds a lot like a PATH issue. Try running "depends test.dll" both
from within msys and from the command shell. It's quite likely it will show
up as one or more DLLs not loading in one case.


> I can upgrade it to 8.0.3 if it solves the problem.

I doubt it would - AFAIK there has been no changes in these areas.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq