Re: [BUGS] BUG #6763: Severe memory leak with arrays and hstore
On Jul 27, 2012, at 8:47 AM, Tom Lane wrote: > Craig Ringer writes: >> OK, it's certainly leaking, but not in the same drastic way I was able >> to reproduce manually a couple of times earlier. Self-contained test >> case attached. > > Using HEAD with stock parameters, I don't see any significant change in > allocated address space (VSZ): it sits right around 170MB. The reported > resident set size (RSS) starts from very little and rises to about > 140MB, but I think that's just an artifact of the process touching more > and more of the shared-buffers array as it runs. The actual backend > memory consumption seems to be just a few meg. > > I can get it to blow out memory if I set work_mem large enough to > persuade the planner to use hash aggregation (whereupon it tries to run > all the array_agg aggregates in parallel). However, that requires > work_mem set to a couple of GB, and I don't think it's really a bug when > the backend goes ahead and uses a couple of GB after I told it it could. > > It's possible that the OP's problem boiled down to the planner making > a drastic underestimate of the number of GROUP BY groups, which could > mislead it into applying hash aggregation when there's not room; or > if the space used per aggregate was a lot more than the 8K that the > planner assumes when dealing with array_agg. But neither of those > errors seems to be happening in this example case. > > regards, tom lane It's good that the bug is not in HEAD. I was testing on 9.1.4. Definitely the size of RSS is not just references to shared buffers because they are 1.8G and the backend RSS got to 4G. My setting for work_mem is 64M, so it's quite conservative - the server was tuned for max concurrency, not for max throughput per single query. Here is the plan of the insert: => explain INSERT INTO new_preferences SELECT user_id,hstore(array_agg(name), array_agg(value)) FROM old_prefs WHERE user_id<20 GROUP BY user_id; QUERY PLAN -- Insert on new_preferences (cost=65615.89..65617.73 rows=67 width=36) -> HashAggregate (cost=65615.89..65617.06 rows=67 width=68) -> Bitmap Heap Scan on old_prefs (cost=17645.25..56555.65 rows=1208032 width=68) Recheck Cond: (user_id < 20) -> Bitmap Index Scan on old_prefs_user_id_ids (cost=0.00..17343.24 rows=1208032 width=0) Index Cond: (user_id < 20) (6 rows) So, it is using hash aggregate as you have suggested. I have tried the query with disabled hash aggregate and it consumes a lot less memory - single query to migrate the whole table finishes with 900M RSS. After "ANALYZE old_prefs" the planner chooses GroupAggregate instead of HashAggregate - you were right about missing statistics of old_prefs. Thank you for figuring out this case Best regards -- luben karavelov -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6763: Severe memory leak with arrays and hstore
On 07/27/2012 07:52 PM, luben karavelov wrote: It's good that the bug is not in HEAD. I was testing on 9.1.4. So was I, and while I thought I'd reproduced it I now suspect I was just seeing shared_buffers touching. Are you able to produce a self-contained SQL test that demonstrates the leak? Does the test program I posted behave differently on your system? -- Craig Ringer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6768: Failure in OBDC
On 07/27/2012 07:52 AM, fabio.lun...@gmail.com wrote: The following bug has been logged on the website: Bug reference: 6768 Logged by: Fábio Hentz Lunkes Email address: fabio.lun...@gmail.com PostgreSQL version: 9.1.0 Operating system: Windows 7 Description: Hellow. My teste to developer application with Microssoft Access, ODBC and Postgres. With grant selet in one field, other fields is revoke permissions, access in table with Microsoft Access is not possible. Failure is generate, to permission denied. In Microsoft MS Query, no error. I think you'll need to explain this in a bit more detail, with: - Table definitions - The EXACT commands you ran - The EXACT error messages As far as I know, running: GRANT SELECT on tablename(column) TO user; shouldn't in any way restrict their existing rights, and the documentation backs that up: http://www.postgresql.org/docs/9.1/static/sql-grant.html A user may performSELECT,INSERT, etc. on a column if he holds that privilege for either the specific column or its whole table. Granting the privilege at the table level and then revoking it for one column will not do what you might wish: the table-level grant is unaffected by a column-level operation. ... so I think you might need to show what's happening in a bit more detail. Beware that there isn't a big Microsoft Access community here. -- Craig Ringer
Re: [BUGS] BUG #6760: make check fails on strings SQL T581 regex test
This problem has been resolved; following a couple of suggestions from Tom
Lane, it became apparent that the cause was due to the xlc compiler. This mail
summarizes the steps and findings, in the hope that it might be useful to
someone else.
My server environment is AIXv7.1 running on POWER7, with xlcV11.1.0.0. I used
the following configure command:
LDFLAGS="-L/usr/local/lib" LIBS="-lmass" CC=xlc_r CFLAGS="-qtune=auto
-qarch=auto -qcache=auto -O2 -I/u
sr/local/include" ./configure --with-openssl --disable-nls
After editing src/include/pg_config.h to comment out the HAVE_WCSTOMBS_L (which
configure incorrectly detects on AIX) I ran make check. This produced the
regex error described in the initial report.
I edited the src/Makefile.global to remove all optimization and ran "make clean
check", to find that I no longer had the regex error.
As this behaviour pointed to a compiler issue, I updated the compiler to
xlcV12.1.0.0 and reran the "make clean check" and the result was no regex error
even with the O2 optimization.
As you may have noticed, I stipulated the use of IBM high-performance maths
library, lmass, in the configure command. Unfortunately, the configure script
places the -lm in front of -lmass in Makefile.global, which as most of the APIs
in lmass are duplicates of those in lm, the lmass routines never get called.
Swapping the order of lm and lmass, such that the LIBS line reads:
LIBS = -lssl -lcrypto -lz -lreadline -lcurses -lld -lmass -lm
Has not only made the maths faster, but has also removed the float8 rounding
error, so the end of the make check now prints:
== shutting down postmaster ==
===
All 126 tests passed.
===
Jez Wain
On 25 Jul 2012, at 19:10, Tom Lane wrote:
> jez.w...@bull.net writes:
>> ***
>> *** 347,354
>> three | f1 | exp_ln_f1
>> ---+--+---
>> | 1004.3 |1004.3
>> !| 1.2345678901234e+200 | 1.23456789012338e+200
>> !| 1.2345678901234e-200 | 1.23456789012339e-200
>> (3 rows)
>
>> -- cube root
>> --- 347,354
>> three | f1 | exp_ln_f1
>> ---+--+---
>> | 1004.3 |1004.3
>> !| 1.2345678901234e+200 | 1.23456789012337e+200
>> !| 1.2345678901234e-200 | 1.2345678901234e-200
>> (3 rows)
>
> This doesn't seem terribly surprising as a platform-specific difference.
>
>> -- T581 regular expression substring (with SQL99's bizarre regexp syntax)
>> SELECT SUBSTRING('abcdefg' FROM 'a#"(b_d)#"%' FOR '#') AS "bcd";
>> ! ERROR: invalid regular expression: parentheses () not balanced
>> ! CONTEXT: SQL function "substring" statement 1
>
> This however isn't too good. It suggests a platform-specific issue in
> the regex library, but hard to say what. Can you dig a little deeper,
> maybe get a stack trace back from the call to errfinish()? Does
> compiling with -O0 change the behavior?
>
> regards, tom lane
[BUGS] BUG #6774: FOR IN SELECT LOOP ignores ORDER BY
The following bug has been logged on the website: Bug reference: 6774 Logged by: Boris Folgmann Email address: bo...@folgmann.de PostgreSQL version: 8.4.12 Operating system: CentOS 6.3 Description: This is an really interesting one! I've trimmed down the problem so you can simply reproduce it by copy & paste: CREATE OR REPLACE FUNCTION ignores_order_by() RETURNS TABLE(datname VARCHAR) AS $$ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM pg_database WHERE datallowconn ORDER BY datname LOOP datname := r.datname; RETURN NEXT; END LOOP; END; $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION respects_order_by() RETURNS TABLE(dn VARCHAR) AS $$ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM pg_database WHERE datallowconn ORDER BY datname LOOP dn := r.datname; RETURN NEXT; END LOOP; END; $$ LANGUAGE 'plpgsql'; SELECT * from ignores_order_by(); SELECT * from respects_order_by(); Now compare the different output! The only difference of the two functions is that the first one uses a variable with the same name of a column. This might be a feature and not a bug, but browsing through the documentation I could not find any documented restrictions on variable names in this context. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6774: FOR IN SELECT LOOP ignores ORDER BY
On Fri, Jul 27, 2012 at 02:56:18PM +, bo...@folgmann.de wrote: > This is an really interesting one! > I've trimmed down the problem so you can simply reproduce it by copy & > paste: > The only difference of the two functions is that the first one uses a > variable with the same name of a column. > This might be a feature and not a bug, but browsing through the > documentation I could not find any documented restrictions on variable names > in this context. and the variable name is the problem. generally - order by datname is understood as "order by *variable datname*". - which is null. change the select to: select d.* from pg_database d order by d.datname and now there is no more problem - because you're no longer using ambiguous identifier. Also, check this: http://www.depesz.com/2009/12/16/waiting-for-8-5-plpgsql-variable-resolution/ Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
