Re: [GENERAL] Dump/Reload pg_statistic to cut time from pg_upgrade?
On Mon, 2013-07-08 at 14:20 -0500, Jerry Sievers wrote:
> Planning to pg_upgrade some large (3TB) clusters using hard link
> method. Run time for the upgrade itself takes around 5 minutes.
> Nice!! Origin version 8.4 and destination version 9.1.
>
> Unfortunately the post-upgrade analyze of the entire cluster is going
> to take a minimum of 1.5 hours running several threads to analyze all
> tables. This was measured in an R&D environment.
...
> Anyway, perhaps there are other good reasons I should *not* attempt
> this but it turns out that the stats table can't be reloaded with it's
> own dump so this of course is a show-stopper.
>
> psql:d:456: ERROR: cannot accept a value of type anyarray
> CONTEXT: COPY pg_statistic, line 1, column stavalues1:
> "{_assets,_income,_liabilities,assets,income,liabilities}"
[ late response, but might still be useful to someone ]
You can work around the problem with a little effort if you call
array_in directly. It takes the type output (cstring), element type
(oid), and element typmod (integer).
To dump the pg_statistics table, you have to output all of the columns
plus the type ID and the typmod, and then load it back in by doing
something like:
insert into pg_statistic
select starelid, ...,
array_in(stavalues1, the_element_type, -1), ...
from my_statistics_dump;
The element typmod is always -1 for pg_statistic. To get the element
type, you can join against pg_attribute. The only problem is, you don't
actually want the attribute type, you want the type used for the
statistics, which is normally the same but could be different. I don't
think the statypid is stored in the catalog, so you'd have to inventory
the types that you use and figure out a mapping of the type to it's
statistics type looking at the typanalyze routines.
So, it's possible to do, but not worth the effort unless you are quite
concerned about the analyze time post-upgrade.
It would be nice if we had a better way to backup, transfer, and upgrade
statistics. However, allowing statistics to be upgraded could be a
challenge if the statistics format changes between releases.
Regards,
Jeff Davis
--
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] Why is NULL = unbounded for rangetypes?
On Fri, 2013-08-30 at 11:22 +0200, Andreas Joseph Krogh wrote: > But I agree that returning NULL would be OK, then it would be easy to > catch in queries when starting playing with range-types in queries. > Having it implicitly mean infinity comes as a surprise, to me at > least. Agreed. This was discussed at the time, and the original version of Range Types experimented with other means of specifying unbounded ranges in order to avoid this possible confusion. Unfortunately, everything we tried was awkward one way or another; and we eventually made the decision to go with greater convenience, even if it could cause some confusion. Regards, Jeff Davis -- 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] Adding ip4r to Postgresql core?
On Wed, 2013-08-07 at 23:24 -0700, Chris Travers wrote: > On Wed, Aug 7, 2013 at 9:44 PM, Tom Lane wrote: > So the obvious question today is whether this isn't > duplicative of the > range datatype stuff. IOW, why wouldn't we be better off to > invent > inetrange and call it good? INET allows a mask, and so doesn't offer an obvious total order. Interestingly, a mask is somewhat like a range, so perhaps we could use a range type that considers a mask to be an alternate representation of a range. I thought about that briefly, but it seemed more likely to lead to confusion or backwards-compatibility problems. If we just had an IP(v4|v6) type with no mask and a total order, adding a range type would be trivial. > actually this misses the one area where ip4r is really helpful and > that is GiST support. If you want to have an exclusion constraint > which specifies that no two cidr blocks in a table can contain > eachother, you can do this easily with ip4r but it takes a lot of work > without it. A lot of work (much of it by Alexander Korotkov) has already gone into improving range type [sp-]gist indexes. And range types go together well with exclusion constraints. So, I'm not sure I follow how this is a reason to use ip4r rather than a range type -- can you clarify? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] store multiple rows with the SELECT INTO statement
Hi, Why is it only possible to store one row by a query which returns multiple rows using the SELECT INTO statement. and How can I do a Query on a record varialbe, somehow like this: SELECT * FROM v_rec Janek Sendrowski
Re: [GENERAL] store multiple rows with the SELECT INTO statement
On 09/01/2013 05:23 PM, Janek Sendrowski wrote: Hi, Why is it only possible to store one row by a query which returns multiple rows using the SELECT INTO statement. and How can I do a Query on a record varialbe, somehow like this: SELECT * FROM v_rec You can't a record variable can only hold a single row. FYI SELECT INTO in plpgsql is not the same as the SQL SELECT INTO: http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW Tip: Note that this interpretation of SELECT with INTO is quite different from PostgreSQL's regular SELECT INTO command, wherein the INTO target is a newly created table. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT. One way I have gotten around this to create a temporary table in the function and fill it with data and then select from it as suggested above. Janek Sendrowski -- Adrian Klaver adrian.kla...@gmail.com -- 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] My Experiment of PG crash when dealing with huge amount of data
>To spare memory, you would want to use something like: >insert into test01 select generate_series, >repeat(chr(int4(random()*26)+65),1024) from >generate_series(1,2457600); Thanks a lot! What I am worrying about is that: If data grows rapidly, maybe our customer will use too much memory , Is ulimit command a good idea for PG? Best Regards 2013/9/1 Jeff Janes > On Fri, Aug 30, 2013 at 2:10 AM, 高健 wrote: > > > > > > postgres=# insert into test01 values(generate_series(1,2457600),repeat( > > chr(int4(random()*26)+65),1024)); > > The construct "values (srf1,srf2)" will generate its entire result set > in memory up front, it will not "stream" its results to the insert > statement on the fly. > > To spare memory, you would want to use something like: > > insert into test01 select generate_series, > repeat(chr(int4(random()*26)+65),1024) from > generate_series(1,2457600); > > Cheers, > > Jeff >
Re: [GENERAL] My Experiment of PG crash when dealing with huge amount of data
=?UTF-8?B?6auY5YGl?= writes: > If data grows rapidly, maybe our customer will use too much memory , Is > ulimit command a good idea for PG? There's no received wisdom saying that it is. There's a fairly widespread consensus that disabling OOM kill can be a good idea, but I don't recall that many people have tried setting specific ulimits on server processes. Keep in mind that exceeding a ulimit would cause queries to fail outright (whether the server was under much load or not), versus just getting slower if the server starts to swap under too much load. I can imagine situations where that would be considered a good tradeoff, but it's hardly right for everyone. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
