Thread: Wiki editor request

Hi,

I noticed something that I do not think is correct on the PostgreSQL 
wiki and would like to edit it, but don't have the privileges. Could you 
either edit it for me or give me the privileges? My account is based on 
my gmail dmkaplan2000@gmail.com

The think that I noticed that seems incorrect to me has to do with the 
histogram aggregate code snippet here 
https://wiki.postgresql.org/wiki/Aggregate_Histogram. I believed that 
the author misinterpreted the fact that width_bucket uses nbuckets+1 
buckets (actually nbuckets+2 buckets) as not appropriate and tries to 
correct for that, but I don't think current code does what it is 
supposed to. width_bucket returns values between 0 and nbuckets+1 where 
a value of 0 means val<MIN and a value of nbuckets+1 means val>=MAX. 
These first and last buckets are actually useful information that should 
be kept, and the existing code puts into a bucket -1 all values less 
than the MIN and bucket=nbuckets all values that exceed the MAX, which 
is incorrect.

I believe the correct code should be:

CREATE OR REPLACE FUNCTION hist_sfunc (state INTEGER[], val DOUBLE PRECISION,
        MIN DOUBLE PRECISION, MAX DOUBLE PRECISION, nbuckets INTEGER) RETURNS INTEGER[] AS $$
DECLARE
   bucket INTEGER;
   i INTEGER;
BEGIN
   -- This will put values in buckets with a 0 bucket for <MIN and a (nbuckets+1) bucket for >=MAX
   bucket := width_bucket(val, MIN, MAX, nbuckets);

   -- Init the array with the correct number of 0's so the caller doesn't see NULLs
   IF state[0] IS NULL THEN
     FOR i IN SELECT * FROM generate_series(0, nbuckets + 1) LOOP
       state[i] := 0;
     END LOOP;
   END IF;

   state[bucket] = state[bucket] + 1;
   RETURN state;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Tell Postgres how to use the new function
DROP AGGREGATE IF EXISTS histogram (DOUBLE PRECISION, DOUBLE PRECISION, DOUBLE PRECISION, INTEGER);
CREATE AGGREGATE histogram (DOUBLE PRECISION, DOUBLE PRECISION, DOUBLE PRECISION, INTEGER) (
        SFUNC = hist_sfunc,
        STYPE = INTEGER[]
);



To confirm that this is correct, please compare the original histogram 
aggregate with my version using the following query:

WITH a AS (
SELECT generate_series(-2,5,0.5) AS i
)
SELECT array_agg(i) AS values,
        histogram(i,0,3,3) AS counts,
        (histogram(i,0,3,3))[1:3] AS counts_in_limits
FROM a;



Thanks,
David

On 04/27/2018 03:19 AM, David M. Kaplan wrote:
> I noticed something that I do not think is correct on the PostgreSQL
> wiki and would like to edit it, but don't have the privileges. Could you
> either edit it for me or give me the privileges? My account is based on
> my gmail dmkaplan2000@gmail.com

Edit privilege granted.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Hi,

I have a new code snippet that I think would be a useful addition to the 
website, but I can't see how to create a new page and I am not sure I 
have the privileges to do so. Could you help me with that, perhaps 
creating an empty page for me to fill?

The code deals with refreshing materialized views. I have a large set of 
materialized views that depend on each other, but I could find no way to 
refresh them all in the appropriate order so that you respect 
dependencies. I googled the problem, but all the solutions I could find 
just refresh all materialized views without considering dependencies 
among them. Therefore, I created some code to get the list of 
materialized views in the proper order so that they are refreshed in the 
proper order. The code is below. I am using it to refresh all views in a 
database, but it could also be used to find the dependencies of a single 
mat view and refresh just that subset. I would put the code in the 
"works for me" category as my understanding of pg_* tables is limited, 
but I think it is useful as is.

Cheers,
David


--------------------------------------------------
--- A view giving the list of mat views that depend
--- on each mat view
--------------------------------------------------
CREATE OR REPLACE VIEW mat_view_dependencies AS
WITH RECURSIVE 
s(start_schemaname,start_mvname,schemaname,mvname,mvoid,depth) AS (
-- List of mat views -- with no dependencies
SELECT n.nspname AS start_schemaname, c.relname AS start_mvname,
n.nspname AS schemaname, c.relname AS mvname, c.oid AS mvoid, 0 AS depth
FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid
WHERE c.relkind='m'
UNION
-- Recursively find all things depending on previous level
SELECT s.start_schemaname, s.start_mvname,
n.nspname AS schemaname, c.relname AS mvname,
c.oid AS mvoid, depth+1 AS depth
FROM s
JOIN pg_depend d ON s.mvoid=d.refobjid
JOIN pg_rewrite r ON d.objid=r.oid
JOIN pg_class c ON r.ev_class=c.oid AND c.relkind='m'
JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE s.mvoid <> c.oid -- exclude the current MV which always depends on 
itself
)
SELECT * FROM s;

--------------------------------------------------
--- A view that returns the list of mat views in the
--- order they should be refreshed.
--------------------------------------------------
CREATE OR REPLACE VIEW mat_view_refresh_order AS
WITH b AS (
-- Select the highest depth of each mat view name
SELECT DISTINCT ON (schemaname,mvname) schemaname, mvname, depth
FROM mat_view_dependencies
ORDER BY schemaname, mvname, depth DESC
)
-- Reorder appropriately
SELECT schemaname, mvname, depth AS refresh_order
FROM b
ORDER BY depth, schemaname, mvname
;

--------------------------------------------------
--- Query to return the code to refresh all mat views in proper order
--- Should be a function, but haven't created that yet.
--- Instead, put code in a psql variable and then
--- execute that.
--------------------------------------------------
WITH a AS (
SELECT 'REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || mvname || 
'";' AS r
FROM mat_view_refresh_order WHERE schemaname='dmk'
ORDER BY refresh_order
)
SELECT string_agg(r,E'\n') AS script FROM a \gset

\echo :script
-- :script




On 04/29/2018 01:18 AM, Joe Conway wrote:
> On 04/27/2018 03:19 AM, David M. Kaplan wrote:
>> I noticed something that I do not think is correct on the PostgreSQL
>> wiki and would like to edit it, but don't have the privileges. Could you
>> either edit it for me or give me the privileges? My account is based on
>> my gmail dmkaplan2000@gmail.com
> Edit privilege granted.
>
> Joe
>