[HACKERS] Pl/perlu, DBD::Oracle and Sys:SigAction problem - crash of backend process
/opt/pg-8.2.9/lib/postgresql/plpgsql.so #25 0xb35f7986 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #26 0xb35f7753 in exec_stmt_block () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #27 0xb35fa133 in plpgsql_exec_function () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #28 0xb35f0218 in plpgsql_call_handler () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #29 0x0814bb1c in ExecMakeFunctionResult () #30 0x0814a256 in ExecProject () #31 0x0815a45d in ExecResult () #32 0x081498c5 in ExecProcNode () #33 0x08148c7b in ExecutorRun () #34 0x0815ff6e in _SPI_execute_plan () #35 0x08160298 in SPI_execute_plan () #36 0xb35f522b in exec_run_select () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #37 0xb35f8281 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #38 0xb35f7352 in exec_stmt_block () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #39 0xb35f7942 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #40 0xb35f901b in exec_stmt_fors () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #41 0xb35f79e6 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #42 0xb35f7352 in exec_stmt_block () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #43 0xb35fa133 in plpgsql_exec_function () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #44 0xb35f0218 in plpgsql_call_handler () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so #45 0x0814eb89 in ExecMakeTableFunctionResult () #46 0x08159f80 in FunctionNext () #47 0x0814fac4 in ExecScan () #48 0x08159f09 in ExecFunctionScan () #49 0x08149920 in ExecProcNode () #50 0x08148c7b in ExecutorRun () #51 0x081d1ba0 in PortalRunSelect () #52 0x081d2ad9 in PortalRun () #53 0x081ce967 in exec_simple_query () #54 0x081cfe88 in PostgresMain () #55 0x081a9816 in BackendRun () #56 0x081a8fef in BackendStartup () #57 0x081a706a in ServerLoop () #58 0x081a650a in PostmasterMain () #59 0x081680a0 in main () When i make this test without ,,timeout'' implementation (without using system signals) everything works fine. I made some traces in functions and process crashes when is trying update or insert row that has already been updated or inserted by other process (when there is no ,,timeout'' code one process is waiting to end of the other - so is working correctly). So I suppose it can has something to do with locks mechanism. Maybe some of you encounter such a problem. I'll be very gratefull of any of your help. Sorry for my childish english. Regards Tomasz Olszak
Re: [HACKERS] Pl/perlu, DBD::Oracle and Sys:SigAction problem - crashof backend process
Than you for your quick answer Adrew, I read some documentation about plperl etc. but didn't see ,,don't even think about it'' advice :). cheers Tomek Dnia 17 sierpnia 2009 18:07 Andrew Dunstan lt;and...@dunslane.netgt; napisał(a): Tomasz Olszak wrote: gt; Greetings! gt; gt; I've encounter repeatable postgres crash. gt; gt; I developed set of pl/perlu functions to import data from oracle to gt; postgres. I should have thought modifying signal handlers in PLPerl was a straight recipe for disaster, even if the code puts them back or thinks it does. My advice would be don't even think about it. cheers andrew
[HACKERS] Problem with accesing Oracle from plperlu function when using remote pg client.
Greetings to All! I've tried to find solution of my problem on other pg mailing lists but without bigger effect. I have a table A in PG. There is also table A in Oracle. I want to import specific row from oracle to pg, so i create plperlu function CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric) RETURNS dok_za AS $BODY$ In IPL: create_connection; select all columns on oracle from table a where id = a_id; returning tuple; $BODY$ LANGUAGE 'plperlu' VOLATILE; then i can use such function in pl/pgsql; DECLARE: var A%ROWTYPE; BEGIN; ... select * into var from import.ora_a_row(100); END;... Like you see it's very, very convenient. And it works, but only when I make select * from import.ora_a_row(100); from psql on postgresql server(local client). When I try to make that select in pgadmin or from remote machine I have tns error: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20 I've tried with different postgresql versions and different perls, and different DBI Oracle packages, so i think pg or perl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about 1.6 gigabyte so I think it's full client(not instant). When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too. But when I've written localhost as host it didn't work (the same with connecting psql -h localhost -U user database ). Anybody ancounter this kind of problem or maybe it's a bug in plperlu? I'll be grateful for any of Your help. Regards Tomasz
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Thank you for quick answer. I understand, but when I print from plperlu function notices with result of `env` they're the same in both cases (from remote and local client). So it looks like that plperlu function is executing from remote and local clients with the same set of environment variable. So I don't have a clue how can I iron out this issue. Dnia 16 marca 2009 15:27 Kenneth Marshall lt;k...@rice.edugt; napisał(a): On Mon, Mar 16, 2009 at 03:16:07PM +0100, Tomasz Olszak wrote: gt; Greetings to All! gt; gt; I've tried to find solution of my problem on other pg mailing lists but without bigger effect. gt; gt; I have a table A in PG. There is also table A in Oracle. gt; I want to import specific row from oracle to pg, so i create plperlu function gt; gt; CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric) gt; RETURNS dok_za AS gt; $BODY$ gt; gt; In IPL: gt; create_connection; gt; select all columns on oracle from table a where id = a_id; gt; returning tuple; gt; gt; $BODY$ gt; LANGUAGE 'plperlu' VOLATILE; gt; gt; then i can use such function in pl/pgsql; gt; gt; DECLARE: gt; var A%ROWTYPE; gt; BEGIN; gt; ... gt; select * into var from import.ora_a_row(100); gt; END;... gt; gt; Like you see it's very, very convenient. gt; gt; And it works, but only when I make select * from import.ora_a_row(100); from psql?? on postgresql server(local client). gt; When I try to make that select in pgadmin or from remote machine I have tns error: gt; gt; TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20 gt; gt; I've tried with different postgresql versions and different perls, and different DBI Oracle packages, so i think pg or perl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about 1.6 gigabyte so I think it's full client(not instant). gt; gt; When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too. gt; But when I've written localhost as host it didn't work (the same with connecting psql -h localhost -U user database ). gt; gt; Anybody ancounter this kind of problem or maybe it's a bug in plperlu? gt; gt; I'll be grateful for any of Your help. gt; gt; Regards gt; gt; Tomasz gt; This looks like an ENVIRONMENT variable problem. The server does not run with the same set of settings as your psql program. I think that it will work once you get those issues ironed out. Good luck, Ken
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah, you're the man :). Thank you very much, I tried to solve it for about 2 weeks. I know that few people in the net have the same problem too. I simply chanche that line, recompile postgresql and wait for some better solution. I know that a lot of people uses DBI-LINK. It simply doesn't work when you envoking functions(for example make_ancessor or somethink like that) from remote client like pgAdmin :). Regards to all Thank you one more time Jonah. Dnia 16 marca 2009 17:26 Jonah H. Harris lt;jonah.har...@gmail.comgt; napisał(a): On Mon, Mar 16, 2009 at 11:09 AM, Tomasz Olszak lt;tols...@o2.plgt; wrote: So it looks like that plperlu function is executing from remote and local clients with the same set of environment variable. It has nothing to do with the environment variables. So I don't have a clue how can I iron out this issue. Finally, my low-level Oracle knowledge does benefit Postgres :) It's a TNS parsing error due to a combination of Oracle's use of a Lispish s-expression-like name-value pair format and Postgres' process listing format for remote connections. On connection, the Oracle client sends the current application name to the Oracle server (which is listed in the V$SESSION view); in the case of Postgres, the program name is the current backend process name text. Because Oracle picks up Postgres' backend text, postgres: www postgres 192.168.1.1(13243), the (13243) screws up Oracle's TNS parser which prevents it from resolving the connection. This doesn't happen when you're connected to PG locally, because the backend text is, postgres: www postgres [local]. The solution to this is to change the following line in src/backend/postmaster/postmaster.c: remote_port[0] == '\0' ? %s : %s(%s) TO remote_port[0] == '\0' ? %s : %s[%s] OR remote_port[0] == '\0' ? %s : %s:%s Which I would prefer as a nice change to make overall. -- Jonah H. Harris, Senior DBA myYearbook.com
