[GENERAL] SRF, JDBC and result info
Hi everybody! I have an SRF which is called from a JAVA app with JDBC. Everything works fine and I want now to be able to pass some result-related info to my app. It is not about the format of the results (ResultSetMetaData) or something like that. Is it possible to return some string (or other type of)info together with the result tuples (even if it requiers some hacking i.e. there is no provision for something like that)? Any ideas? Regards, Ntinos Katsaros ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SRF, JDBC and result info
Thank you very much for your reply. The thing is that my SRF is written in C, not plpgsql, but I'll look into RAISE NOTICE anyway.(I think there is something equevalent in libpq) Thanks again, Ntinos Katsaros Kris Jurka writes: On Tue, 8 Mar 2005 [EMAIL PROTECTED] wrote: Hi everybody! I have an SRF which is called from a JAVA app with JDBC. Everything works fine and I want now to be able to pass some result-related info to my app. It is not about the format of the results (ResultSetMetaData) or something like that. Is it possible to return some string (or other type of)info together with the result tuples (even if it requiers some hacking i.e. there is no provision for something like that)? Any ideas? The only idea that comes to mind is using RAISE NOTICE in your plpgsql function and Statement or ResultSet .getWarnings() on the Java side to retrieve that info. There really isn't any other out of band data path. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SRF, JDBC and result info
Solution found! Thanks to Kris Jurka's advise I managed to pass this info using: elog(INFO,... or elog(NOTICE,... . These messages together with .getWarnings() do the job. : e.g. message returned by the SQLWarning: SNOTICEC0M#SUCCESSFUL EXECUTION. NO TUPLES FROM PEER(S): mobileb#Ftestmybuild.cL2558Ranswer Getting the plain message is then trivial (e.g. using flag chars like '#' above) Of cource the appropriate logging must be set in postgresql.conf. Just in case somenone wants to do the same thing. I dont know if this is the best solution (or if any other exists) but it surely works. Regards, Ntinos Katsaros PS: libpq has nothing to do with the above :-)! [EMAIL PROTECTED] writes: Thank you very much for your reply. The thing is that my SRF is written in C, not plpgsql, but I'll look into RAISE NOTICE anyway.(I think there is something equevalent in libpq) Thanks again, Ntinos Katsaros Kris Jurka writes: On Tue, 8 Mar 2005 [EMAIL PROTECTED] wrote: Hi everybody! I have an SRF which is called from a JAVA app with JDBC. Everything works fine and I want now to be able to pass some result-related info to my app. It is not about the format of the results (ResultSetMetaData) or something like that. Is it possible to return some string (or other type of)info together with the result tuples (even if it requiers some hacking i.e. there is no provision for something like that)? Any ideas? The only idea that comes to mind is using RAISE NOTICE in your plpgsql function and Statement or ResultSet .getWarnings() on the Java side to retrieve that info. There really isn't any other out of band data path. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Can't find relation oid
Hi, I have the following problem: I use libpq inside SRF functions (like in dblink) and I create some tables to store results coming from remotly executed queries. (These tables are not temporary cause I want to use them later as a form of cache.) Then I try to open these tables to get the results. Actually I modify an already available Query to refer to these new tables instead of the correspoding local tables. To do so I need the Oids of theses new tables which I get using the following query: select oid from pg_class where relname='foo' The problem is that when the new tables are created for the first time, the above query returns no results and the whole thing fails.Everything works fine if they have already been created by a previous attempt to run my SRF. I tried to isolate each step (creating tables, retrieving results) in a new transaction block (like in testlibpq.c example in the documentation) but with no result. Maybe the whole process described above runs in a sigle transaction block i.e. that of my SRF(?). I'm a little confused here. Any ideas? Regards, Ntinos Katsaros ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Can't find relation oid
Martijn van Oosterhout writes: On Mon, Jan 03, 2005 at 02:32:44PM +0200, [EMAIL PROTECTED] wrote: Hi, I have the following problem: I use libpq inside SRF functions (like in dblink) and I create some tables to store results coming from remotly executed queries. (These tables are not temporary cause I want to use them later as a form of cache.) Then I try to open these tables to get the results. Actually I modify an already available Query to refer to these new tables instead of the correspoding local tables. To do so I need the Oids of theses new tables which I get using the following query: select oid from pg_class where relname='foo' What language? What version of postgresql? I'm using C on PostgreSQL 7.4.2 It's not exactly clear from your description but is there a reason you are using libpq and not the SPI interface? If you create a connection using libpq to the same database then the queries executed through that connection will not be able to see the transaction of the SRF you are writing. The SPI interface is for executing queries within the same transaction. Now I think I understand what happens. You see, I used libpq to create the tables and SPI to retrieve the Oids ($%&^#$%&!!). When I tried to also retrieve the Oid using libp it worked. I suppose using SPI both to create the tables and retrieve the Oids will be the right solution. I'll try it! Hope this helps, It did help to understand what was going on! Thanks!! Ntinos Katsaros ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] PQisBusy() always returns TRUE
Hi,
I'm writing some code for asychronous command processing and I experience
the following problem. Everything seems to be OK (by following the
documentation) until I call PQisBusy(). While PQconsumeInput() returns 1,
PQisBusy() always returns 1! This is the code:
-
// conn is the connection already established in a nonblocking way
//(this is OK, I checked it with sychronous command processing)
PQsetnonblocking(conn,1);
//A simple query for now...
sql = "select * from t1";
if (PQsendQuery(conn,sql)!=1)
elog(ERROR,"Something went wrong with PQsendQuery");
PQflush(conn);
sock = PQsocket(conn);
if (sock < 0)
break;
FD_ZERO(&input_mask);
FD_SET(sock, &input_mask);
if (select(sock + 1, &input_mask, NULL, NULL,NULL) < 0)
elog(ERROR,"Nothing comes to my socket!");
if (PQconsumeInput(conn)!=1) elog(ERROR,"Error in consume...");
else
{
elog(INFO,"OK with PQconsumeInput");
//Will use a cursor and 'while' later when this
//will have been fixed...
if (!PQisBusy(conn))
{
res=PQgetResult(conn);
}
else elog(INFO,"It is too busy to give me the results!");
}
...
-
If I discard PQisBusy the results are retreived without problems.
This code is used inside a SRF that is intended to return tuples from remote
dbs (as in dblink). For the time being I query local tables.
I use v7.4.2.
Is this something too obvious to see it :-) ? Is it something with
nonblocking connection establishment? Any suggestions?
Thanks in advance,
Ntinos Katsaros
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] PQisBusy() always returns TRUE
If understand correctly what you are saying, the right code for this thing
is:
PQconsumeInput(conn);//Try to collect the results
while (PQisBusy(conn)) // while not ready ...
PQconsumeInput(conn); //...retry
res=PQgetResult(conn); // Now get the results
I tried this and it worked. However, the problem now is that it is slow
(surely slower than using blocking commands).
If the code above is right (?), why is that?
Thanks for your response,
Ntinos Katsaros
Tom Lane writes:
[EMAIL PROTECTED] writes:
if (PQconsumeInput(conn)!=1) elog(ERROR,"Error in consume...");
else
{
elog(INFO,"OK with PQconsumeInput");
//Will use a cursor and 'while' later when this
//will have been fixed...
if (!PQisBusy(conn))
{
res=PQgetResult(conn);
}
else elog(INFO,"It is too busy to give me the results!");
}
PQconsumeInput has to be part of the loop. PQisBusy doesn't change any
state, it just tells you whether PQconsumeInput has collected a complete
query result yet.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PQisBusy() always returns TRUE
Martijn van Oosterhout writes: On Sat, Jan 15, 2005 at 09:50:35PM +0200, [EMAIL PROTECTED] wrote: PQconsumeInput(conn);//Try to collect the results while (PQisBusy(conn)) // while not ready ... PQconsumeInput(conn); //...retry res=PQgetResult(conn); // Now get the results I belive if you're waiting in an event loop, you can use PQsocket() to return the fd to use in select. Or if you're doing other work, do that while you're waiting and check isBusy when you have time... Well, I do use select() before calling PQconsumeInput, but I'm not sure what an event loop would be like.I have more than one (remotly executed) queries and I want to pick whatever results are available without being blocked (and even abord a delaying query), i.e. I do have other work to do. Having said that, I think that an event loop would be appropriate but I'm a little confused on its structure. Any pointers? Thanks, Ntinos Katsaros ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Getting table metadata
SELECT * FROM pg_attribute WHERE attrelid=; The problem I'm running into however, is that given a table name, there doesn't seem to be any way to get the table oid. Is there some function or query that does this? I think a way to get the table oid is: select oid from pg_class where relname= Ntinos Katsaros ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
