[GENERAL] Stored function debugging help
Hi All , Im new to writing stored functions in postgresql and in general . I'm trying to write onw with an input parameter and return a set of results stored in a temporary table. I do the following in my function . 1) Get a list of all the consumers and store their id's stored in a temp table. 2) Iterate over a particular table and retrieve values corresponding to each value from the above list and store in a temp table. 3)Return the temp table. Here's the function that I've tried to write by myself , create or replace function getPumps(status varchar) returns setof record as $$--(setof record?) DECLARE cons_id integer[]; i integer; temp table tmp_table;--Point B BEGIN select consumer_id into cons_id from db_consumer_pump_details; FOR i in select * from cons_id LOOP select objectid,pump_id,pump_serial_id,repdate,pumpmake,db_consumer_pump_details.status,db_consumer.consumer_name,db_consumer.wenexa_id,db_consumer.rr_no into tmp_table from db_consumer_pump_details inner join db_consumer on db_consumer.consumer_id=db_consumer_pump_details.consumer_id where db_consumer_pump_details.consumer_id=i and db_consumer_pump_details.status=$1-- Point A order by db_consumer_pump_details.consumer_id,pump_id,createddate desc limit 2 END LOOP; return tmp_table END; $$ LANGUAGE plpgsql; However Im not sure whether im right at the points A and B as I've marked in the code above . As I'm getting a load of unexplained errors. It would be great if someone could help me out with it . Thanks! :) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Stored-function-debugging-help-tp5028300p5028300.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql query gets stuck indefinitely
Hi All I have postgres installed in cluster setup. My system has a script which executes the below query on remote system in cluster. psql -t -q -Uslon -h -d -c"select 1;" But somehow this query got stuck. It didnt return even after the remote system( on which this query was supposed to execute) is rebooted . What could be the reason ?? Thanks... -- Tamanna Madaan | Associate Consultant | GlobalLogic Inc. Leaders in Software R&D Services ARGENTINA | CHILE | CHINA | GERMANY | INDIA | ISRAEL | UKRAINE | UK | USA Office: +0-120-406-2000 x 2971 www.globallogic.com
Re: [GENERAL] Stored function debugging help
On 11/28/11 1:30 AM, JavaNoobie wrote: 1) Get a list of all the consumers and store their id's stored in a temp table. 2) Iterate over a particular table and retrieve values corresponding to each value from the above list and store in a temp table. 3)Return the temp table. couldn't that all be done by a JOIN without involving a temporary table, or iteration? this seems like a conventional programmers approach to problem solving, rather than using the power of the relational database. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to add conversion between LATIN1 and WIN1251 ?
On 26.11.2011 22:33, Adrian Klaver wrote: On Friday, November 25, 2011 11:28:06 pm Condor wrote: No, charset of databases is the same. I use the same ENV when I upgrade sql servers and recreate psql database directory. About client encoding, I never ever has before a configured postgresql on my work station where I connect to servers. Even postgres user and config file did not exists and this worked fine in psql versions below 9.1 That is why I included a link to the Release Notes. There has been a change in behavior in 9.1. I am assuming that you are using psql to connect. If you want the details here is the commit: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=02e14562a806a96f38120c96421d39dfa7394192 -- Adrian Klaver adrian.kla...@gmail.com Sorry, my bad. I read it now. -- Regards, Condor -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] tricking EXPLAIN?
Hallo, if u compare the 2 queries, then they should be equivalent: -- normal -- EXPLAIN ANALYZE SELECT amproc, amprocnum - average AS difference FROMpg_amproc, (SELECT avg(amprocnum) AS average FROMpg_amproc) AS tmp; -- trying to trick explain with a redundant join -- EXPLAIN ANALYZE SELECT amproc, amprocnum - average AS difference FROMpg_amproc INNER JOIN (SELECT avg(amprocnum) AS average FROMpg_amproc) AS tmp ON pg_amproc.amproc = pg_amproc.amproc; If we look at the output of EXPLAIN ANALYZE, then according to the COST the second query is best one, but according to the ACTUAL TIME the first query is best (which seems logical intuitively). So explain is being tricked, and the reason for this seems the number of rows in de nested loop, which are reduced to 1 for explain because of the join. http://www.postgresql.org/docs/8.4/static/using-explain.html Suggestions, comments are always welcome. mvg, Wim Bertels -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tricking EXPLAIN?
On 28 November 2011 12:55, Wim Bertels wrote: > Hallo, > > if u compare the 2 queries, then they should be equivalent: > > -- normal > -- EXPLAIN ANALYZE > SELECT amproc, amprocnum - average AS difference > FROMpg_amproc, >(SELECT avg(amprocnum) AS average >FROMpg_amproc) AS tmp; > > -- trying to trick explain with a redundant join > -- EXPLAIN ANALYZE > SELECT amproc, amprocnum - average AS difference > FROMpg_amproc INNER JOIN >(SELECT avg(amprocnum) AS average >FROMpg_amproc) AS tmp >ON pg_amproc.amproc = pg_amproc.amproc; > > > If we look at the output of EXPLAIN ANALYZE, > then according to the COST the second query is best one, > but according to the ACTUAL TIME the first query is best > (which seems logical intuitively). > > So explain is being tricked, > and the reason for this seems the number of rows in de nested loop, > which are reduced to 1 for explain because of the join. > http://www.postgresql.org/docs/8.4/static/using-explain.html > > Suggestions, comments are always welcome. > > mvg, > Wim Bertels > > > > Hi, could you show us the output of explain analyze? regards Szymon
Re: [GENERAL] tricking EXPLAIN?
On ma, 2011-11-28 at 13:00 +0100, Szymon Guz wrote: > > > On 28 November 2011 12:55, Wim Bertels > wrote: > Hallo, > > if u compare the 2 queries, then they should be equivalent: > > -- normal > -- EXPLAIN ANALYZE > SELECT amproc, amprocnum - average AS difference > FROMpg_amproc, >(SELECT avg(amprocnum) AS average >FROMpg_amproc) AS tmp; "Nested Loop (cost=5.04..13.13 rows=243 width=38) (actual time=0.333..0.953 rows=243 loops=1)" " -> Aggregate (cost=5.04..5.05 rows=1 width=2) (actual time=0.326..0.327 rows=1 loops=1)" "-> Seq Scan on pg_amproc (cost=0.00..4.43 rows=243 width=2) (actual time=0.003..0.157 rows=243 loops=1)" " -> Seq Scan on pg_amproc (cost=0.00..4.43 rows=243 width=6) (actual time=0.002..0.147 rows=243 loops=1)" "Total runtime: 1.117 ms" > > -- trying to trick explain with a redundant join > -- EXPLAIN ANALYZE > SELECT amproc, amprocnum - average AS difference > FROMpg_amproc INNER JOIN >(SELECT avg(amprocnum) AS average >FROMpg_amproc) AS tmp >ON pg_amproc.amproc = pg_amproc.amproc; "Nested Loop (cost=5.04..10.11 rows=1 width=38) (actual time=0.376..80.891 rows=243 loops=1)" " -> Seq Scan on pg_amproc (cost=0.00..5.04 rows=1 width=6) (actual time=0.028..0.249 rows=243 loops=1)" "Filter: ((amproc)::oid = (amproc)::oid)" " -> Aggregate (cost=5.04..5.05 rows=1 width=2) (actual time=0.327..0.328 rows=1 loops=243)" "-> Seq Scan on pg_amproc (cost=0.00..4.43 rows=243 width=2) (actual time=0.002..0.156 rows=243 loops=243)" "Total runtime: 81.101 ms" > > If we look at the output of EXPLAIN ANALYZE, > then according to the COST the second query is best one, > but according to the ACTUAL TIME the first query is best > (which seems logical intuitively). > > So explain is being tricked, > and the reason for this seems the number of rows in de nested > loop, > which are reduced to 1 for explain because of the join. > http://www.postgresql.org/docs/8.4/static/using-explain.html > > Suggestions, comments are always welcome. > > mvg, > Wim Bertels > > > > > > Hi, > could you show us the output of explain analyze? cf supra, Wim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored function debugging help
Well I'm not fond of using a temporary table either. But how would I be able to iterate over a set of consumers while using a join ? From my (limited) , using only a join I would only be able to generate the data for a particular consumer , rather than all of them. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Stored-function-debugging-help-tp5028300p5028732.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored function debugging help
On 28 November 2011 13:36, JavaNoobie wrote: > Well I'm not fond of using a temporary table either. But how would I be able > to iterate over a set of consumers while using a join ? From my (limited) , > using only a join I would only be able to generate the data for a particular > consumer , rather than all of them. It would seem that the join that you already use inside your for-loop would give you the results you want, precisely because of the join that's in it. Provided you take off the limit, of course. Perhaps you want those results DISTINCT ON (consumer_id), but a for-loop is definitely not the way to do that. Not impossible, just very inelegant and slow. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Any experiences with Foreign Data Wrappers (FDW) like mysql_fdw, odbc_fdw, www_fdw or odbc_fdw?
Hi, I'm interested in using Foreign Data Wrappers (FDW) in order to connect PG to CSV files, MongoDB, MS SQL Server and the Web. Was anyone able to compile and use FDWs [1], like mysql_fdw, odbc_fdw or www_fdw, in PG 9.1.1 (besides official file_fdw) under Ubuntu but also Windows? Does anyone have experience, especially with odbc_fdw (e.g. performance) ? Yours, Stefan [1] http://wiki.postgresql.org/wiki/Foreign_data_wrappers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Extending the volume size of the data directory volume
Hi, as I am importing gigabytes of data and the space on the volume where the data dictionary resides just became to small during that process, I resized it dynamically (it is a LVM volume) according to this procedure: http://www.techrepublic.com/blog/opensource/how-to-use-logical-volume-manager-lvm-to-grow-etx4-file-systems-online/3016 Everything went without any problems and the import continued. Now, it is suddenly stuck (pgAdmin shows it as idle (piped connection)) and there is a good chance (as estimated from the space used) it just started using one of the added LE-Blocks (HDD space that was added to the volume). The db imported so far can be accessed just fine. So from the postmaster architecture, is there something that would explain this behaviour based on the hypothesis that newly added space was used? Any chance to revive the import somehow? Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Extending-the-volume-size-of-the-data-directory-volume-tp5030663p5030663.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql query gets stuck indefinitely
On 11/28/2011 05:30 PM, tamanna madaan wrote: Hi All I have postgres installed in cluster setup. My system has a script which executes the below query on remote system in cluster. psql -t -q -Uslon -h -d -c"select 1;" But somehow this query got stuck. It didnt return even after the remote system( on which this query was supposed to execute) is rebooted . What could be the reason ?? The issue will most likely be related to the network or to the client-side host. Perhaps the client machine changed IP addresses (maybe as part of a switch from WiFi to wired or similar) ? Check the man page for psql in 9.1; I think client-side keepalive support got committed for 9.1 . If it didn't, you can always set it globally for all TCP/IP connections on your system. See eg http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/usingkeepalive.html . -- Craig Ringer
Re: [GENERAL] psql query gets stuck indefinitely
On 11/28/2011 05:30 PM, tamanna madaan wrote: Hi All I have postgres installed in cluster setup. My system has a script which executes the below query on remote system in cluster. psql -t -q -Uslon -h -d -c"select 1;" But somehow this query got stuck. It didnt return even after the remote system( on which this query was supposed to execute) is rebooted . What could be the reason ?? I relised just after sending my last message: You should use ps to find out what exactly psql is doing and which system call it's blocked in in the kernel (if it's waiting on a syscall). As you didn't mention your OS I'll assume you're on Linux, where you'd use: ps -C psql -o wchan:80= or ps -p 1234 -o wchan:80= ... where "1234" is the pid of the stuck psql process. In a psql waiting for command line input I see it blocked in the kernel routine "n_tty_read" for example. If you really want to know what it's doing you can also attach gdb and get a backtrace to see what code it's paused in inside psql: gdb -q -p 1234 <<__END__ bt q __END__ If you get a message about "missing debuginfos", lots of lines reading "no debugging symbols found" or lots of lines ending in "?? ()" then you need to install debug symbols. How to do that depends on your OS/distro so I won't go into that; it's documented on the PostgreSQL wiki under "how to get a stack trace" but you probably won't want to bother if this is just for curiosity's sake. You're looking for output that looks like: #1 0x00369d22a131 in rl_getc () from /lib64/libreadline.so.6 #2 0x00369d22a8e9 in rl_read_key () from /lib64/libreadline.so.6 #3 0x00369d215b11 in readline_internal_char () from /lib64/libreadline.so.6 #4 0x00369d216065 in readline () from /lib64/libreadline.so.6 ... etc ... -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql query gets stuck indefinitely
Hi Craig Thanks for your reply . But unfortunately I dont have that process running right now. I have already killed that process . But I have seen this problem sometimes on my setup. It generally happens when the remote system is going slow for some reason (CPU utilization high etc.) . But whatever is the reason , I would assume that the query should return with some error or so in case the system, the query is running on , is rebooted . But it doesn't return and remain stuck. Moreover, the same query sometimes hangs even if it is run on local postgres database so I dont think network issues have any role in that . Please help. Thanks Regards Tamanna On Tue, Nov 29, 2011 at 7:58 AM, Craig Ringer wrote: > On 11/28/2011 05:30 PM, tamanna madaan wrote: > >> Hi All >> I have postgres installed in cluster setup. My system has a script >> which executes the below query on remote system in cluster. >> psql -t -q -Uslon -h -d -c"select 1;" >> But somehow this query got stuck. It didnt return even after the remote >> system( on which this query was supposed to execute) is rebooted . What >> could be the reason ?? >> > > I relised just after sending my last message: > > You should use ps to find out what exactly psql is doing and which system > call it's blocked in in the kernel (if it's waiting on a syscall). As you > didn't mention your OS I'll assume you're on Linux, where you'd use: > > ps -C psql -o wchan:80= > > or > > ps -p 1234 -o wchan:80= > > ... where "1234" is the pid of the stuck psql process. In a psql waiting > for command line input I see it blocked in the kernel routine "n_tty_read" > for example. > > > If you really want to know what it's doing you can also attach gdb and get > a backtrace to see what code it's paused in inside psql: > > gdb -q -p 1234 <<__END__ > bt > q > __END__ > > If you get a message about "missing debuginfos", lots of lines reading "no > debugging symbols found" or lots of lines ending in "?? ()" then you need > to install debug symbols. How to do that depends on your OS/distro so I > won't go into that; it's documented on the PostgreSQL wiki under "how to > get a stack trace" but you probably won't want to bother if this is just > for curiosity's sake. > > You're looking for output that looks like: > > #1 0x00369d22a131 in rl_getc () from /lib64/libreadline.so.6 > #2 0x00369d22a8e9 in rl_read_key () from /lib64/libreadline.so.6 > #3 0x00369d215b11 in readline_internal_char () from > /lib64/libreadline.so.6 > #4 0x00369d216065 in readline () from /lib64/libreadline.so.6 > > ... etc ... > > > -- > Craig Ringer > -- Tamanna Madaan | Associate Consultant | GlobalLogic Inc. Leaders in Software R&D Services ARGENTINA | CHILE | CHINA | GERMANY | INDIA | ISRAEL | UKRAINE | UK | USA Office: +0-120-406-2000 x 2971 www.globallogic.com
Re: [GENERAL] Stored function debugging help
On 11/28/11 4:36 AM, JavaNoobie wrote: Well I'm not fond of using a temporary table either. But how would I be able to iterate over a set of consumers while using a join ? From my (limited) , using only a join I would only be able to generate the data for a particular consumer , rather than all of them. get rid of db_consumer_pump_details.consumer_id=i and and the limit, and the join will do all of them. But, maybe I don't quite understand what it is you're doing. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] odbc_fdw
Hi there! I built the current PostgreSQL 9.1.1 sources under Ubuntu 11.04 (in a VMware under Win7). I followed the steps in this guide: www.thegeekstuff.com/2009/04/linux-postgresql-install-and-configure-from-source It seems to work (I can run the server and connect to it with PgAdmin). Now I'd like to integrate the ODBC_FDW extension in my installation. However, I don't really understand the steps described on the download page: pgxn.org/dist/odbc_fdw/0.1.0 Can anybody tell me how to build it? I'm a software developer myself but a Linux newbie... Thank you for your help! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql query gets stuck indefinitely
On 29/11/11 11:21, tamanna madaan wrote: > Hi Craig > > Thanks for your reply . But unfortunately I dont have that process > running right now. I have already killed that process . But I have > seen this problem sometimes on my setup. > It generally happens when the remote system is going slow for some > reason (CPU utilization high etc.) . But whatever is the reason , I > would assume that the query should return with some error or so > in case the system, the query is running on , is rebooted . But it > doesn't return and remain stuck. Moreover, the same query sometimes > hangs even if it is run on local postgres database so I dont think > network issues have any role in that . Please help. Well, it *really* shouldn't hang locally. To help you further I'll need you to collect the information on the stuck process next time you encounter one and post that as a reply. Maybe with a bit more info we can see what might be going on. -- Craig Ringer
[GENERAL] Lengthy deletion
Hi. I was instructed to delete old records from one of the tables in our production system. The deletion took hours and I had to stop it in mid-operation and reschedule it as a night job. But then I had to do the same when I got up in the morning and it was still running. The odd thing about it: There are 4720965 records in the table, of which I have to delete 3203485. This should not have taken too long, and the EXPLAIN estimate for it seemed to agree with me: bcentral=> explain delete from subscriptions s where (date_registered < '2011-11-13' and operator <> 'P') and service_id not in ( select id from alerts_services ) ; QUERY PLAN - Delete (cost=38885.86..155212.37 rows=1630425 width=6) -> Bitmap Heap Scan on subscriptions s (cost=38885.86..155212.37 rows=1630425 width=6) Filter: ((date_registered < '2011-11-13 00:00:00'::timestamp without time zone) AND (operator <> 'P'::bpchar) AND (NOT (hashed SubPlan 1))) -> Bitmap Index Scan on t_ind (cost=0.00..38473.03 rows=2361115 width=0) Index Cond: ((date_registered < '2011-11-13 00:00:00'::timestamp without time zone) = true) SubPlan 1 -> Seq Scan on alerts_services (cost=0.00..4.58 rows=258 width=4) (7 rows) I got an interesting clue, though, when I canceled the deletion the second time around. I got the following error message: Cancel request sent ERROR: canceling statement due to user request CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."sent_messages" x WHERE $1 OPERATOR(pg_catalog.=) "subscription_id" FOR SHARE OF x" As you can see in the EXPLAIN sentence, I'm trying to delete from a table called "subscriptions", and this context is in another table called "sent_messages" which is related to it by foreign key. Now, I'd say that it was waiting to get a lock on the "sent_messages" table (from which I duly removed the related records before running my delete), and that I should have known that. Only, I was using another connection to monitor pg_stat_activity while the delete is done, and the delete process had "false" in the "waiting" column! bcentral=# SELECT usename, procpid, query_start, client_addr, client_port, current_query, waiting FROM pg_stat_activity WHERE query_start < now() - interval '3 seconds' AND xact_start is not null order by xact_start; -[ RECORD 1 ]-+ usename | bcentral procpid | 20047 query_start | 2011-11-29 02:01:28.968161+02 client_addr | 192.168.34.34 client_port | 55709 current_query | delete : from subscriptions s : where (date_registered < '2011-11-13' and operator <> 'P') and service_id not in ( select id fr om alerts_services ) : ; waiting | f Um... so what gives? What's happening here? The server is PostgreSQL 9.0.4. TIA, Herouth
Re: [GENERAL] Lengthy deletion
"Herouth Maoz" writes: > I was instructed to delete old records from one of the tables in our > production system. The deletion took hours and I had to stop it in > mid-operation and reschedule it as a night job. But then I had to do the same > when I got up in the morning and it was still running. > I got an interesting clue, though, when I canceled the deletion the second > time around. I got the following error message: > Cancel request sent > ERROR: canceling statement due to user request > CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."sent_messages" x WHERE > $1 OPERATOR(pg_catalog.=) "subscription_id" FOR SHARE OF x" Yup, that's a clue all right. I'll bet a nickel that you don't have an index on the foreign key's referencing column (ie, sent_messages.subscription_id). That means each delete in the referenced table has to seqscan the referencing table to see if the delete would result in an FK violation. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
