On 10/01/2011 05:08 PM, Andrew Dunstan wrote:
> While investigating a client problem I just observed that pg_dump takes
> a surprisingly large amount of time to dump a schema with a large number
> of views. The client's hardware is quite spiffy, and yet pg_dump is
> taking many minutes to dump a schema with some 35,000 views. Here's a
> simple test case:
>
> create schema views;
> do 'begin for i in 1 .. 10000 loop execute $$create view views.v_$$
> || i ||$$ as select current_date as d, current_timestamp as ts,
> $_$a$_$::text || n as t, n from generate_series(1,5) as n$$; end
> loop; end;';
>
>
> On my modest hardware this database took 4m18.864s for pg_dump to run.
> Should we be looking at replacing the retail operations which consume
> most of this time with something that runs faster?
How modest? Was there anything else in the database? I tried with 9000
views (because I didn't want to bother increasing
max_locks_per_transaction) and the pg_dump in less than 10 seconds
(8.991s) redirecting (plain-text) output to a file (this is on a Core i5).
> There is also this gem of behaviour, which is where I started:
>
> p1 p2
> begin;
> drop view foo;
> pg_dump
> commit;
> boom.
>
> with this error:
>
> 2011-10-01 16:38:20 EDT [27084] 30063 ERROR: could not open
> relation with OID 133640
> 2011-10-01 16:38:20 EDT [27084] 30064 STATEMENT: SELECT
> pg_catalog.pg_get_viewdef('133640'::pg_catalog.oid) AS viewdef
>
> Of course, this isn't caused by having a large catalog, but it's
> terrible nevertheless. I'm not sure what to do about it.
Couldn't you run pg_dump with --lock-wait-timeout?
Joe