Re: [GENERAL] Looping through cursor row batches
Anyone know the most efficient way of FETCHing a batch of rows, and
looping through them in a function? FETCHing a record at a
time will work, but I was wondering whether this could be done.
You're outsmarting yourself.
:-) One can only try.
plpgsql already does the equivalent of
this under the hood, there is no need for you to try to make it happen
at user level. Just use a plain ol' FOR rec IN SELECT and forget the
explicit cursor.
I'm aware of the implicit cursor use in functions, but recall that (pg8.3.3)
(#1)
FOR rec IN SELECT col from dblink_fetch('cursor'..) DO
is running out of memory (see discussion
http://archives.postgresql.org/pgsql-general/2008-06/msg00031.php) due
to an exception block inside the loop (which is possibly leaking
memory - I tried to reduce it to a concise failing case, still trying).
I'm pre-emptively expecting (pessimistically, I know) an OOM error again with:
(#2)
FOR rec IN SELECT col FROM really_huge_table DO
exception block...
END LOOP;
Anyway, I've found that fetching a batch of (say) 100,000 (instead of
10,000) at a time I reduce the likelihood of running out of memory (a
process which does exactly this has been running for the past day or
two; time will tell). I was pondering whether it's possible to do
what I mentioned in my original post - ie, an explicit cursor as the
source object in a FOR loop so I can have a bit more control over how
many rows are fetched each time (instead of 1 at a time).
So, setting aside my self-outsmartiness, is there a way to achieve this?
Regards
Henry
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Looping through cursor row batches
Greetings, I gather the following isn't possible (which would have been elegant and ideal): FOR rec IN FETCH 10 FROM cursor LOOP ... Anyone know the most efficient way of FETCHing a batch of rows, and looping through them in a function? FETCHing a record at a time will work, but I was wondering whether this could be done. Cheers Henry -- 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] Is it possible to remove the public schema?
No problem at all. It's easy to automate the table linking process. I have a table in access that holds - among other things - the internal and external name of my linked tables, in which database, schema and server they locate. [snip] Thank you very much for the information. Regards Henry This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Is it possible to remove the public schema?
Hello all I've been approached by the development people about removing the 'public' schema. They complain about having to manually remove the 'public_' tag from table names generated by their development software whenever they link to PG via ODBC. Renaming or using another schema is not what they're after either. 1. If it is possible to remove the public schema, what are the ramifications to existing databases in our system (ie, will a dump/restore be required, etc)? 2. If it's not possible, can it be done via the ODBC driver? Thanks Henry This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Is it possible to remove the public schema?
It sounds to me like the real problem is with non-schema-aware client software. They're using Office XP Developer (Access 2000). No hope of fixing that. ...I think your options are to fix that, or downgrade to a non-schema-aware database (eg. Postgres 7.2 or before). ...and miss out on the nourishing goodness of 7.4? I don't think so! Thanks anyway. Regards Henry This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SOLVED: Where clause limited to 8 items?
Check the estimated number of rows returned. It's presumably believing that the a sequential scan will be cheaper for the estimated number of rows. If the estimated number of rows is significantly off, you may wish to change the statistics target (see ALTER TABLE) for col1 and analyze the table again. If it still is choosing a sequential scan over an index scan and the number of rows is similar, you may want to look at the random_page_cost variable. You have to be careful not too lower it too far that other queries are pessimized the other direction, but some experimentation comparing the real times and estimated costs of queries with and without enable_seqscan=off may help. Thanks for the detailed response! Your suggestion was spot-on. Regards Henry This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Where clause limited to 8 items?
Henry Combrinck [EMAIL PROTECTED] writes: The above works fine - the index is used. However, extend the where clause with an extra line (say, col1 = 9) and the index is no longer used. Do explain analyze select ... with both versions and send the results (preferably without line wrapping it). I'm a bit skeptical about your description since I don't see how either query could possibly be using an index here. Why? Either it uses an index, or it doesn't. Being skeptical doesn't change the reality of what is in fact happening. Anyway, the suggestion from Stephan Szabo was the right one. Just in case you're still feeling skeptical: DB=# set enable_seqscan=on; SET DB=# explain analyse select count(*) from test1 where a=1 or a=2 or a=3 or a=4 or a=5 or a=6 or a=7 or a=8; QUERY PLAN - Aggregate (cost=38.75..38.75 rows=1 width=0) (actual time=0.291..0.292 rows=1 loops=1) - Index Scan using test1_pkey, test1_pkey, test1_pkey, test1_pkey, test1_pkey, test1_pkey, test1_pkey, test1_pkey on test1 (cost=0.00..38.72 rows=8 width=0) (actual time=0.089..0.228 rows=8 loops=1) Index Cond: ((a = 1) OR (a = 2) OR (a = 3) OR (a = 4) OR (a = 5) OR (a = 6) OR (a = 7) OR (a = 8)) Total runtime: 0.744 ms (4 rows) DB=# explain analyse select count(*) from test1 where a=1 or a=2 or a=3 or a=4 or a=5 or a=6 or a=7 or a=8 or a=9; QUERY PLAN --- Aggregate (cost=42.52..42.52 rows=1 width=0) (actual time=0.249..0.250 rows=1 loops=1) - Seq Scan on test1 (cost=0.00..42.50 rows=9 width=0) (actual time=0.067..0.182 rows=9 loops=1) Filter: ((a = 1) OR (a = 2) OR (a = 3) OR (a = 4) OR (a = 5) OR (a = 6) OR (a = 7) OR (a = 8) OR (a = 9)) Total runtime: 0.493 ms (4 rows) DB=# When used on a real table (ie, with hundreds of thousands of records), the total runtime peaks at over 8000ms (seq scan)... This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Where clause limited to 8 items?
Hello Searched around, but could not find this mentioned. I've noticed the following behaviour in 7.4.5: [explain analyse] select * from foo where col1 = 1 or col1 = 2 or col1 = 3 or col1 = 4 or col1 = 5 or col1 = 6 or col1 = 7 or col1 = 8; where an index on foo.col1 exists. The above works fine - the index is used. However, extend the where clause with an extra line (say, col1 = 9) and the index is no longer used. Is there a parameter I can SET to extend the number of items allowed for index usage? Any pointers would be appreciated. Henry This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Passing RECORD variable from func1() to func2()
Hello Hopefully someone can shed some light on the following issue. After chatting at irc.freenode.net/#postgresql, without success, this is my last effort before giving up and using a temp table. Essentially, I would like to pass a RECORD variable from one function to another using plpgsql: func2(record) rec1 alias for $1 begin -- do work on rec1.* raise notice ''val1=% val2=%'', rec1.col1, rec1.col2; end; func1() declare temprec record; begin for temprec in select * from table1, table2... loop ... select func2(temprec);/* pass temprec row to func2() */ ... end loop; end; Then call with: SELECT FUNC1(); Is this possible? The docs only speak about RECORD type being used to *return* rows, but not to pass it. Any pointers would be appreciated. Regards Henry This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Passing RECORD variable from func1() to func2()
Henry Combrinck [EMAIL PROTECTED] writes: Essentially, I would like to pass a RECORD variable from one function to another using plpgsql: func2(record) You can't declare a plpgsql function that accepts RECORD; this is simply not supportable. (For one thing, which actual record types should such a function be considered to match? It's a nonstarter even at the level of function argument resolution, let alone the implementation issues.) It has to take some named rowtype, instead. Thanks for the response. Can you give an example of what a named rowtype is? Are you refering to creating some kind of custom TYPE, and then passing that to func2(custom_type_row)? Thanks Henry This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
