Re: [BUGS] BUG #4418: Memory leak in query planner

[Tom Lane]

"michael McMaster" <[EMAIL PROTECTED]> writes:
> I intend on creating a view over a large number of tables (possibly > 1000).

Better rethink that plan ... PG's current partitioning support doesn't
scale that high, and filing bugs claiming it's a memory leak isn't
going to make it do so.

regards, tom lane

-- 
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 #4418: Memory leak in query planner

[Jeff Davis]

On Tue, 2008-09-16 at 00:37 +, michael McMaster wrote:
> I intend on creating a view over a large number of tables (possibly > 1000).
>  Each table in the view has a CHECK constraint that partitions the data by a
> time range.

You may try using the inheritance mechanism rather than a UNION ALL
view.

Regards,
Jeff Davis


-- 
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 #4418: Memory leak in query planner

[Michael McMaster]

Tom Lane wrote:

"michael McMaster" <[EMAIL PROTECTED]> writes:

I intend on creating a view over a large number of tables (possibly > 1000).


Better rethink that plan ... PG's current partitioning support doesn't
scale that high, and filing bugs claiming it's a memory leak isn't
going to make it do so.

regards, tom lane



I modified the views to mimic the CHECK constraints on the partitioned 
tables.
ie CREATE VIEW a_view AS select * from a_table where dtstart between  
and 

UNION ALL ...

The query plans generated weren't quite as good, but use an 
insignificant amount of memory, and are still reasonably fast.  This 
solution is an acceptable trade off at the moment.


I still think the memory usage issue is a bug, as there are alternative 
plans that show Postgres -does- scale that high.  I had a quick look 
through the code and I suspect that table inheritance wouldn't have this 
issue.  I'll test inheritance and report my findings.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4420: Problem Finding unique text entries

[Legistrate]

The following bug has been logged online:

Bug reference:  4420
Logged by:  Legistrate
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.3.3
Operating system:   Windows XP
Description:Problem Finding unique text entries
Details: 

SELECT count(*) FROM trigrams UNION
SELECT count(*) FROM (SELECT DISTINCT trigram FROM trigrams) s;
393262
393285
SELECT DISTINCT trigram INTO trigram2 FROM trigrams;
SELECT count(*) FROM trigram2 UNION
SELECT count(*) FROM (SELECT DISTINCT trigram FROM trigram2) s;
393261
393262
SELECT DISTINCT trigram INTO trigram3 FROM trigram2;
SELECT count(*) FROM trigram3 UNION
SELECT count(*) FROM (SELECT DISTINCT trigram FROM trigram3) s;
393261
393261

trigrams:http://www.2shared.com/file/3943447/273b8527/MusicData.html
trigram2:http://www.2shared.com/file/3943480/15ea5f88/MusicData2.html
trigram3:http://www.2shared.com/file/3943484/12879b91/MusicData3.html

When I recently installed 8.3.3 to fix similar bug in 8.3.0 I selected UTF-8
for server and client and the database that trigrams, etc are in is UTF-8. 
However me and RhodiumToad discovered that show lc_collate; returns
"English_United States.1252".  I dont recall seeing an option to change this
in the windows installer, but I could have missed it.  Not sure if this is
affecting the results or not.

Note that:
select distinct trigram from trigrams where trigram=(select trigram from
trigrams where trigram_id=384519);
select distinct trigram from trigrams where trigram_id IN (384519,392701);
SELECT * FROM trigram2 GROUP BY trigram HAVING count(trigram)>1;
all return the same row that seems to be the duplicate that is not
detected.
Also, the original trigrams table was created by inserting the result of
trigrams2 into trigrams to use the SERIAL field as a primary key from the
following:
SELECT DISTINCT trigram INTO trigrams2 FROM (SELECT DISTINCT trigram FROM
artist_trigrams UNION SELECT DISTINCT trigram FROM album_trigrams UNION
SELECT DISTINCT trigram FROM track_trigrams) s;

The content of those tables is much more extensive, but if you need it, let
me know and I will rapidshare it or something.

I was able to create a unique constraint on 'trigram' for both trigram2 and
trigram3, not on trigrams.  For RodiumToad, he couldnt create a unique
constraint on trigram2 and SELECT count(*) FROM (SELECT DISTINCT trigram
FROM trigrams) s; returned 393261 on freebsd with lc_collate=C

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs