Re: [GENERAL] tracking scripts...

First serious answer : you don't have to use command line, you can use the pgadmin gui, loading your file with all the command, and then hit F6 (or select run as pgscript). This will wrapp each command in a transaction , and will print messages all along. Please test this on a few line before tryin

Re: [GENERAL] tracking scripts...

John R Pierce wrote: > On 11/26/2013 9:24 AM, Joey Quinn wrote: >> When I ran that command (select * from pg_stat_activity"), it returned >> the first six lines of the scripts. I'm fairly sure it has gotten a >> bit beyond that (been running over 24 hours now, and the size has >> increased about 30

Re: [GENERAL] tracking scripts...

I'm not an expert, I would think if you can spare using only one transaction , it would be way way faster to do it ! the system simply could skip keeping log to be ready to roll back for a 1 billion row update ! Of course it would be preferable to use psql to execute statement by statement as se

Re: [GENERAL] tracking scripts...

On 11/27/2013 1:39 AM, Rémi Cura wrote: the system simply could skip keeping log to be ready to roll back for a 1 billion row update thats not how postgres does rollbacks -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via

Re: [GENERAL] tracking scripts...

On 27/11/2013 08:20, Rémi Cura wrote: > First serious answer : > you don't have to use command line, > you can use the pgadmin gui, loading your file with all the command, and > then hit F6 (or select run as pgscript). > This will wrapp each command in a transaction , and will print messages > all

Re: [GENERAL] having difficulty with explain analyze output

On Tue, 26 Nov 2013 14:51:22 -0500, Martijn van Oosterhout wrote: > The Seq Scan took 674ms and was run once (loops=1) > > The Materialise was run 94951 times and took, on average, 0.011ms to > return the first row and 16ms to complete. > > 16.145 * 94951 = 1532983.895 OK, this is helpful. But

[GENERAL] Complex sql, limit-for-each group by, arrays, updates

Hi, So i have (table where data will be read) : CREATE TABLE data (vid,cid,pid,number); Tables where data will be writen/updated: CREATE TABLE pid_top_vids (pid, vid[]) CREATE TABLE pid_top_cids (pid, cid[]) CREATE TABLE cid_top_vids (cid, vid[]) I need to , possibly in 1 query, this will run o

Re: [GENERAL] help interpreting "explain analyze" output

On 11/26/2013 06:24 PM, David Rysdam wrote: > I'm not really looking for information on how to speed this query > up. I'm just trying to interpret the output enough to tell me which step > is slow: > >Seq Scan on mags (cost=0.00..187700750.56 rows=47476 width=4) (actual > time=3004851.889..

Re: [GENERAL] Any advantage of using SSL with a certificate of authority?

On Tue, Nov 26, 2013 at 4:48 PM, Bruce Momjian wrote: > Right. I know of no mechanism to verify a certificate via a public CA > through SSL. Browsers have a list of trusted certificates, but SSL > alone doesn't, as far as I know. > SSL as a library/protocol has mechanisms to verify the certific

Re: [GENERAL] tracking scripts...

Wow, thank-you (sometimes the answer is right there in front of you... very new to Postgres, had wondered what the difference was between the run query and run as PGS script, but hadn't looked into it yet). So, here's the critical question(s) right now (for me)... With the way I launched it, usin

Re: [GENERAL] tracking scripts...

Sorry, if you cancel everything will be rolled back (it is actually what makes DB so powerfull). Unless it finishes I don't know of a way to keep changes. At least on my computer (I don't know if you can generalize this), it is way faster to split into many transaction, so you would gain time. Us

Re: [GENERAL] tracking scripts...

A little reluctant, yes, but not 100%. I'm new to Postgres, but if I end up using it enough, then I will also end up learning some command line stuff. If it continues to look like a good/robust solution for this particular project (think ERIPP plus Shodan

Re: [GENERAL] tracking scripts...

So, no cancel... keeping my fingers crossed (rolling up on 170,000,000 ms - a bit over 47 hours - data folder size now at 1.11 TB). Fortunately, I'm pretty sure this will be my largest batch update (since the info is static, and available all at once, I was able to generate the complete script. In

[GENERAL] Documentation of C functions

Hi, Is there a documentation of postgresql's C functions like SET_VARSIZE for exmaple?   Janek Sendrowski -- 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] tracking scripts...

On Wed, Nov 27, 2013 at 8:35 AM, Joey Quinn wrote: > So, no cancel... keeping my fingers crossed (rolling up on 170,000,000 ms - > a bit over 47 hours - data folder size now at 1.11 TB). > > Fortunately, I'm pretty sure this will be my largest batch update (since the > info is static, and availabl

Re: [GENERAL] tracking scripts...

In this case, I'm updating one column. Wouldn't the "swap" part of that still have to be an update? On Wed, Nov 27, 2013 at 9:50 AM, Merlin Moncure wrote: > On Wed, Nov 27, 2013 at 8:35 AM, Joey Quinn wrote: > > So, no cancel... keeping my fingers crossed (rolling up on 170,000,000 > ms - > >

Re: [GENERAL] Documentation of C functions

Janek Sendrowski wrote: > Is there a documentation of postgresql's C functions like SET_VARSIZE for > exmaple? For things like this consult the source code. In src/include/postgres.h you'll find: /* * VARDATA, VARSIZE, and SET_VARSIZE are the recommended API for most code * for varlena dataty

Re: [GENERAL] tracking scripts...

On Wed, Nov 27, 2013 at 9:00 AM, Joey Quinn wrote: > On Wed, Nov 27, 2013 at 9:50 AM, Merlin Moncure wrote: >> For very large updates on mostly static data it may be better to >> SELECT the data into a new table then swap it in when done. MY rule >> of thumb is that updates are 10x more expensi

[GENERAL] nested query vs left join: query planner very confused

I've got two tables, sigs and mags. It's a one-to-one relationship, mags is just split out because we store a big, less-often-used field there. "signum" is the key field. Sometimes I want to know if I have any orphans in mags, so I do a query like this: select signum from lp.Mags where signum

Re: [GENERAL] having difficulty with explain analyze output

David Rysdam writes: > On Tue, 26 Nov 2013 14:51:22 -0500, Martijn van Oosterhout > wrote: >> The Seq Scan took 674ms and was run once (loops=1) >> >> The Materialise was run 94951 times and took, on average, 0.011ms to >> return the first row and 16ms to complete. >> >> 16.145 * 94951 = 15329

Re: [GENERAL] nested query vs left join: query planner very confused

On Wed, Nov 27, 2013 at 7:56 AM, David Rysdam wrote: > > > At my client's location, the query is very slow (same table size, > similar hardware/config, although they are running 9.0.x and I'm on > 9.2.x). By "slow" I mean like an *hour*. 'explain' has this structure: > > Seq scan on mags >

Re: [GENERAL] nested query vs left join: query planner very confused

On 11/27/2013 04:56 PM, David Rysdam wrote: > I've got two tables, sigs and mags. It's a one-to-one relationship, mags > is just split out because we store a big, less-often-used field > there. "signum" is the key field. > > Sometimes I want to know if I have any orphans in mags, so I do a query >

Re: [GENERAL] nested query vs left join: query planner very confused

David Rysdam writes: > Sometimes I want to know if I have any orphans in mags, so I do a query > like this: > select signum from lp.Mags where signum is not null and signum not > in (select lp.Sigs.signum from lp.Sigs) > (I do this as a subquery because we originally had a old Sybase DB

Re: [GENERAL] nested query vs left join: query planner very confused

David Rysdam wrote > I'd never heard of Materialize before, so I looked into it. Seems to > make a virtual table of the subquery so repetitions of the parent query > don't have to re-do the work. Sounds like it should only help, right? Forgive any inaccuracies but I'm pretty sure about the followi

Re: [GENERAL] nested query vs left join: query planner very confused

On Wed, 27 Nov 2013 11:21:09 -0500, Tom Lane wrote: > DBMS-agnostic frequently means "sucks equally badly on all DBMSes" :-( We've generally been OK (cf the ~50ms runtime for the same query at our site), but we also notice problems sooner than our client sometimes does and can make algorithm impr

Re: [GENERAL] nested query vs left join: query planner very confused

On Wed, 27 Nov 2013 11:06:51 -0500, bricklen wrote: > Has the client ANALYZEd recently? What happens if the client issues > the following commands before executing the query? > VACUUM ANALYZE lp.sigs; > VACUUM ANALYZE lp.mags; > > If that doesn't change the plan, could you post the values for > ef

Re: [GENERAL] Complex sql, limit-for-each group by, arrays, updates

Dorian Hoxha wrote > Hi, > > So i have (table where data will be read) : > CREATE TABLE data (vid,cid,pid,number); > > Tables where data will be writen/updated: > > CREATE TABLE pid_top_vids (pid, vid[]) > CREATE TABLE pid_top_cids (pid, cid[]) > CREATE TABLE cid_top_vids (cid, vid[]) > > I nee

Re: [GENERAL] help interpreting "explain analyze" output

On Tue, Nov 26, 2013 at 12:24:08PM -0500, David Rysdam wrote: > I'm not really looking for information on how to speed this query > up. I'm just trying to interpret the output enough to tell me which step > is slow: You might want to read this: http://www.depesz.com/tag/unexplainable/ Best regard

Re: [GENERAL] nested query vs left join: query planner very confused

David Rysdam writes: > effective_cache_size - 12000MB > shared_buffers - 1024MB > random_page_cost - is commented out > cpu_tuple_cost - commented out > work_mem - commented out > I assume you guys already know

Re: [GENERAL] nested query vs left join: query planner very confused

On Wed, 27 Nov 2013 13:02:20 -0500, Tom Lane wrote: > David Rysdam writes: > >effective_cache_size - 12000MB > >shared_buffers - 1024MB > >random_page_cost - is commented out > >cpu_tuple_cost - commented out > >work

Re: [GENERAL] nested query vs left join: query planner very confused

On Wed, 27 Nov 2013 13:04:54 -0500, David Rysdam wrote: > We deliberately try to keep our queries fairly simple for several > reasons. This isn't the most complicated, but they don't get much more > than this. I'll have them start with 10MB and see what they get. 10MB was enough to get that query

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

On 27 Listopad 2013, 22:39, Brian Wong wrote: >> Date: Fri, 22 Nov 2013 20:11:47 +0100 >> Subject: Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of >> size ??? >> From: t...@fuzzy.cz >> To: bwon...@hotmail.com >> CC: brick...@gmail.com; pgsql-general@postgresql.org >> >> On 19 Listop

[GENERAL] Prefix search on all hstore values

Hi, I have an hstore column that stores a string in several arbitrary languages, so something like this: "en" => "string in english", "zh" => "string in chinese", "fr" => "string in french" Is it possible to construct an index that can be used to determine if a query string is a prefix of ANY of

[GENERAL] unnest on multi-dimensional arrays

It appears that unnest, when called on a multi-dimensional array, effectively flattens the array first. For example: => select * from unnest(array[array[1, 2], array[2, 3]]); unnest 1 2 2 3 (4 rows) while I would have expect something like the following: => s

Re: [GENERAL] unnest on multi-dimensional arrays

Hello postgres=# CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray) RETURNS SETOF anyarray LANGUAGE plpgsql AS $function$ DECLARE s $1%type; BEGIN FOREACH s SLICE 1 IN ARRAY $1 LOOP RETURN NEXT s; END LOOP; RETURN; END; $function$; CREATE FUNCTION postgres=# select reduce_dim(arr

Re: [GENERAL] unnest on multi-dimensional arrays

Zev Benjamin wrote > It appears that unnest, when called on a multi-dimensional array, > effectively flattens the array first. For example: > > => select * from unnest(array[array[1, 2], array[2, 3]]); > unnest > >1 >2 >2 >3 > (4 rows) > > while I woul