Обсуждение: 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?

What do I need to do to port such code?

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.

psql:d:/test.sql:4: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:d:/test.sql:4: connection to server was lost

The pg_log logs the following:
2005-07-25 20:30:26 DEBUG:  InitPostgres
2005-07-25 20:30:26 DEBUG:  StartTransaction
2005-07-25 20:30:26 DEBUG:  name: unnamed; blockState:       DEFAULT; state:
INPROGR, xid/subid/cid: 116556/1/0, nestlvl: 1, children: <>
2005-07-25 20:30:26 DEBUG:  CommitTransaction
2005-07-25 20:30:26 DEBUG:  name: unnamed; blockState:       STARTED; state:
INPROGR, xid/subid/cid: 116556/1/0, nestlvl: 1, children: <>
2005-07-25 20:30:26 DEBUG:  StartTransactionCommand
2005-07-25 20:30:26 DEBUG:  StartTransaction
2005-07-25 20:30:26 DEBUG:  name: unnamed; blockState:       DEFAULT; state:
INPROGR, xid/subid/cid: 116557/1/0, nestlvl: 1, children: <>
2005-07-25 20:30:26 DEBUG:  ProcessUtility
2005-07-25 20:30:26 DEBUG:  find_in_dynamic_libpath: trying "C:/Program
Files/PostgreSQL/8.0/lib/foo"
2005-07-25 20:30:26 DEBUG:  find_in_dynamic_libpath: trying "C:/Program
Files/PostgreSQL/8.0/lib/foo.dll"
2005-07-25 20:30:26 DEBUG:  CommitTransactionCommand
2005-07-25 20:30:26 DEBUG:  CommitTransaction
2005-07-25 20:30:26 DEBUG:  name: unnamed; blockState:       STARTED; state:
INPROGR, xid/subid/cid: 116557/1/2, nestlvl: 1, children: <>
2005-07-25 20:30:26 LOG:  could not send data to client: An operation was
attempted on something that is not a socket.
2005-07-25 20:30:26 LOG:  could not receive data from client: An operation
was attempted on something that is not a socket.
2005-07-25 20:30:26 LOG:  unexpected EOF on client connection
2005-07-25 20:30:26 DEBUG:  proc_exit(0)
2005-07-25 20:30:26 DEBUG:  shmem_exit(0)
2005-07-25 20:30:26 DEBUG:  exit(0)
2005-07-25 20:30:26 DEBUG:  reaping dead processes
2005-07-25 20:30:26 DEBUG:  waiting on 4 children
2005-07-25 20:30:26 DEBUG:  server process (PID 6092) exited with exit code
0
2005-07-25 20:30:26 DEBUG:  waiting on 3 children


Thanks & Regards,
Mehul

> 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

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

You cannot use cygwin GCC to build extensions to the win32 version of
postgresql. Only for the cygwin version (I would assume that works, I
haven't tested it. I know it doesn't work for the win32 version). You
will need to use the MingW build system to build your DLLs.

(BTW, yuo really want 8.0.3 and not 8.0.0 for your systems. No changes
in the build system stuff, but several other fixes you definitly want)

//Magnus

> 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

"Magnus Hagander" <mha@sollentuna.net> writes:
>> 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 :-)

Me too.  That's been on the radar for awhile --- please do send in
a patch.

            regards, tom lane

Hi,

This is related to the problem that I faced some time ago. I have
created a dll library file with inbuilt constraints and triggers.
I also built the same for Linux environment. Here's what I am facing.
Any clues as to what I am doing wrong?

Linux: Fedora Core 2 Running Postgresql 7.4.2
Windows: Windows XP Service Pack 2 running PostgreSQL 8.1.2

The testtrigfuncs.dll is placed in C:\Program Files\Test\lib\ directory
and in Linux it is placed in /opt/WMS/rel/lib/testtrigfuncs.so

On linux when I do the following it works:
createdb test
test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert() RETURNS
trigger
test-#   AS '/opt/WMS/rel/lib/testtrigfuncs.so'
test-#   LANGUAGE C ;
CREATE FUNCTION
test=#

On Windows for the same I get: (Includes extra \ to account for Windows)

test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert() RETURNS
trigger
test-#   AS 'C:\\Program Files\\Test\\lib\\testtrigfuncs.dll'
test-#   LANGUAGE C ;
ERROR:  could not load library "C:\Program
Files\Test\lib\testtrigfuncs.dll": The specified module could not be
found.

I went and modified the dynamic_library_path to
dynamic_library_path = 'C:\Program Files\Test\lib;$libdir'

I also added C:\Program Files\Test\lib to the PATH variable. I restarted
postgres service and then retried the below.

test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert() RETURNS
trigger
test-#   AS 'C:\Program Files\Test\lib\testtrigfuncs.dll'
test-#   LANGUAGE C ;
ERROR:  component in parameter "dynamic_library_path" is not an absolute
path
test=#

What is this absolute path? Where do I set it? While I know that if I
place
testtrigfuncs.dll in C:\Program Files\PostgreSQL\8.1\bin folder and do
the
same it works.

test=# CREATE OR REPLACE FUNCTION trigf_stdhlr_pid_insert() RETURNS
trigger
test-#   AS 'C:\\Program Files\\PostgreSQL\\8.1\\bin\\testtrigfuncs.dll'
test-#   LANGUAGE C ;
CREATE FUNCTION
test=#

Any clues on what I need to do to go ahead would be great. I want to be
able to do the
same thing on both Linux and Windows without having to write in
PostgreSQL\bin folder
where my program has no use of being there.

Thanks & Regards,
Mehul



-----Original Message-----
From: Mehul Doshi-A20614
Sent: Wednesday, July 27, 2005 8:35 AM
To: 'Magnus Hagander'; pgsql-hackers-win32@postgresql.org
Cc: Mehul Doshi-A20614
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

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

> On Windows for the same I get: (Includes extra \ to account
> for Windows)
>
> test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert()
> RETURNS trigger
> test-#   AS 'C:\\Program Files\\Test\\lib\\testtrigfuncs.dll'
> test-#   LANGUAGE C ;
> ERROR:  could not load library "C:\Program
> Files\Test\lib\testtrigfuncs.dll": The specified module could
> not be found.
>
> I went and modified the dynamic_library_path to
> dynamic_library_path = 'C:\Program Files\Test\lib;$libdir'
>
> I also added C:\Program Files\Test\lib to the PATH variable.
> I restarted postgres service and then retried the below.

Are you sure you added this to the SYSTEM path and not the USER path?
Because if it's the second, it won't affect the pg service. I'd also try
a reboot after changing the one in SYSTEM - I've seen cases where it
didn't "take" even if the service was restarted.

//Magnus

2006/1/24, Mehul Doshi-A20614 <mehul@motorola.com>:
> This is related to the problem that I faced some time ago. I have
> created a dll library file with inbuilt constraints and triggers.
> I also built the same for Linux environment. Here's what I am facing.
> Any clues as to what I am doing wrong?
>
> Linux: Fedora Core 2 Running Postgresql 7.4.2
> Windows: Windows XP Service Pack 2 running PostgreSQL 8.1.2
>
> The testtrigfuncs.dll is placed in C:\Program Files\Test\lib\ directory
> and in Linux it is placed in /opt/WMS/rel/lib/testtrigfuncs.so
>
> On linux when I do the following it works:
> createdb test
> test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert() RETURNS
> trigger
> test-#   AS '/opt/WMS/rel/lib/testtrigfuncs.so'
> test-#   LANGUAGE C ;
> CREATE FUNCTION
> test=#
>
> On Windows for the same I get: (Includes extra \ to account for Windows)
>
> test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert() RETURNS
> trigger
> test-#   AS 'C:\\Program Files\\Test\\lib\\testtrigfuncs.dll'
> test-#   LANGUAGE C ;
> ERROR:  could not load library "C:\Program
> Files\Test\lib\testtrigfuncs.dll": The specified module could not be
> found.

Surround the path with extra "" because of the space in the path.

AS '"C:\\Program Files\\Test\\lib\\testtrigfuncs.dll"'

Or better move your lib to a path without spaces.

> I went and modified the dynamic_library_path to
> dynamic_library_path = 'C:\Program Files\Test\lib;$libdir'
>
> I also added C:\Program Files\Test\lib to the PATH variable. I restarted
> postgres service and then retried the below.
>
> test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert() RETURNS
> trigger
> test-#   AS 'C:\Program Files\Test\lib\testtrigfuncs.dll'
> test-#   LANGUAGE C ;
> ERROR:  component in parameter "dynamic_library_path" is not an absolute
> path
> test=#
>
> What is this absolute path? Where do I set it? While I know that if I
> place
> testtrigfuncs.dll in C:\Program Files\PostgreSQL\8.1\bin folder and do
> the
> same it works.
>
> test=# CREATE OR REPLACE FUNCTION trigf_stdhlr_pid_insert() RETURNS
> trigger
> test-#   AS 'C:\\Program Files\\PostgreSQL\\8.1\\bin\\testtrigfuncs.dll'
> test-#   LANGUAGE C ;
> CREATE FUNCTION
> test=#
>
> Any clues on what I need to do to go ahead would be great. I want to be
> able to do the
> same thing on both Linux and Windows without having to write in
> PostgreSQL\bin folder
> where my program has no use of being there.
>
> Thanks & Regards,
> Mehul
>
>
>
> -----Original Message-----
> From: Mehul Doshi-A20614
> Sent: Wednesday, July 27, 2005 8:35 AM
> To: 'Magnus Hagander'; pgsql-hackers-win32@postgresql.org
> Cc: Mehul Doshi-A20614
> 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
>
> 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 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>


--
Reini Urban
http://phpwiki.org/
http://spacemovie.mur.at/

Hi,

Here's a weird scenario that seems to be coming out of so many different
options.
a) When the dll is placed in C:\Program Files\PostgreSQL\8.0\bin it
works.
b) When the dll is placed in C:\Program Files\Test\lib, it fails if
dynamic_library_path is set to 'C:\Program Files\Test\lib;$libdir'

However I had help from two people on this,
i) Jean Marc: Replace \ with / i.e dynamic_library_path is set to
'C:/Program Files/Test/lib;$libdir'
ii) Thomas Hallgreen: Do \\ instead of just \. i.e. dynamic_library_path
is set to 'C:\\Program Files\\Test\\lib;$libdir'

Now both of these "start to" work after some time only. There is no
logic that I can explain as to how they work.
Here's what I have tried to do to understand this behavior.

a) Uninstall postgres completely and delete the data folder.
b) Re-install Postgres.
c) Modify the postgresql.conf file with either of the methods. I used
method (ii) since it is more Windows like.
d) Modify the System Variable PATH to contain the extra term "C:\Program
Files\Test\lib"
d) Now I stop the service, start the service. I even rebooted my system.
e) On a new command prompt, I test it out ... Error as before. I can
verify the PATH variable does have C:\Program Files\Test\lib in it.
f) Anyways since I can't do much, I go back and add the dll to the bin
folder and start using it.
g) I can now go back to first command prompt and retest it starts to
work!!!. It works with libdir.
i)I can also go back and modify and delete the dll file that I placed in
the bin folder.
j) I can again retest the dll and it works fine.

Infact it starts to take the dynamic path perfectly.
test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert() RETURNS
trigger
test-#   AS 'testrigfuncs'
test-#   LANGUAGE C ;
CREATE FUNCTION
test=# \q

I have tried this on 4 different systems running Windows XP (Service
Packs 1 & 2) using PostgreSQL 8.0 as well as PostgreSQL 8.1.
Any clues as to why it won't work till I placed the dll in
Postgresql\bin folder and then once I have placed the dll there and
used it, it starts to work at either places even if I delete the dll
from the bin folder.

Thanks & Regards,
Mehul


-----Original Message-----
From: pgsql-hackers-win32-owner@postgresql.org
[mailto:pgsql-hackers-win32-owner@postgresql.org] On Behalf Of Magnus
Hagander
Sent: Friday, January 27, 2006 2:32 AM
To: Mehul Doshi-A20614; pgsql-hackers-win32@postgresql.org
Subject: Re: [pgsql-hackers-win32] Query on support for trigger
functions defined in a Shared library in Windows

> On Windows for the same I get: (Includes extra \ to account for
> Windows)
>
> test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert() RETURNS
> trigger
> test-#   AS 'C:\\Program Files\\Test\\lib\\testtrigfuncs.dll'
> test-#   LANGUAGE C ;
> ERROR:  could not load library "C:\Program
> Files\Test\lib\testtrigfuncs.dll": The specified module could not be
> found.
>
> I went and modified the dynamic_library_path to dynamic_library_path =

> 'C:\Program Files\Test\lib;$libdir'
>
> I also added C:\Program Files\Test\lib to the PATH variable.
> I restarted postgres service and then retried the below.

Are you sure you added this to the SYSTEM path and not the USER path?
Because if it's the second, it won't affect the pg service. I'd also try
a reboot after changing the one in SYSTEM - I've seen cases where it
didn't "take" even if the service was restarted.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings