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

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] 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...

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

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...

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...

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...

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] 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...

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...

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...

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...

On Tue, Nov 26, 2013 at 3:20 PM, Raymond O'Donnell wrote: > On 26/11/2013 20:30, Merlin Moncure wrote: >> There are not many ways to Hand off information outside of the >> database while a transaction Is running. one way Is to write a Simple >> trigger in plpgsql that 'raise'es A notice every 'n'

Re: [GENERAL] tracking scripts...

On 26/11/2013 20:30, Merlin Moncure wrote: > There are not many ways to Hand off information outside of the > database while a transaction Is running. one way Is to write a Simple > trigger in plpgsql that 'raise'es A notice every 'n' times trigger > condition fires. that'S Essentially the only Cl

Re: [GENERAL] tracking scripts...

On Tue, Nov 26, 2013 at 2:38 PM, John R Pierce wrote: > On 11/26/2013 12:30 PM, Merlin Moncure wrote: >> >> There are not many ways to Hand off information outside of the >> database while a transaction Is running. one way Is to write a Simple >> trigger in plpgsql that 'raise'es A notice every 'n

Re: [GENERAL] tracking scripts...

On 11/26/2013 12:30 PM, Merlin Moncure wrote: There are not many ways to Hand off information outside of the database while a transaction Is running. one way Is to write a Simple trigger in plpgsql that 'raise'es A notice every 'n' times trigger condition fires. that'S Essentially the only Clean

Re: [GENERAL] tracking scripts...

On Tue, Nov 26, 2013 at 9:28 AM, Joey Quinn wrote: > I have a fairly large table (4.3 billion rows) that I am running an update > script on (a bit over 127 thousand individual update queries). I am using > the gui. It has been running for about 24 hours now. Is there any good way > to gauge progre

Re: [GENERAL] tracking scripts...

yeah, unlikely that it is already in the path (I certainly didn't add it yet). Thanks for the command (new version). On Tue, Nov 26, 2013 at 3:13 PM, John R Pierce wrote: > On 11/26/2013 11:45 AM, Joey Quinn wrote: > >> Would that command be from within the psql SQL Shell that came as part of

Re: [GENERAL] tracking scripts...

nope, that appears to be being blocked by the updates... tried "select * from ipv4_table where country='gb' limit 1;" it just sat there... On Tue, Nov 26, 2013 at 3:00 PM, Vick Khera wrote: > > On Tue, Nov 26, 2013 at 2:48 PM, Joey Quinn wrote: > >> The ranges are indeed overlapping, though

Re: [GENERAL] tracking scripts...

On 11/26/2013 11:45 AM, Joey Quinn wrote: Would that command be from within the psql SQL Shell that came as part of the install? (I'm living in Windows land). if you're already in psql, logged onto your database, it would be \i filename.sql psql -f filename.sql dbname...would be at the

Re: [GENERAL] tracking scripts...

On Tue, Nov 26, 2013 at 2:48 PM, Joey Quinn wrote: > The ranges are indeed overlapping, though the update statements were > generated alphabetically rather than in IP order... If the command line > will let me query the table directly without being blocked by the ongoing > updates, then I could g

Re: [GENERAL] tracking scripts...

The ranges are indeed overlapping, though the update statements were generated alphabetically rather than in IP order... If the command line will let me query the table directly without being blocked by the ongoing updates, then I could get a rough order of magnitude of progress by doing a null cou

Re: [GENERAL] tracking scripts...

Sounds like I will have to get comfortable with the command line version of things... sigh... hate that. Would that command be from within the psql SQL Shell that came as part of the install? (I'm living in Windows land). (and thank-you for the command) On Tue, Nov 26, 2013 at 1:24 PM, John R

Re: [GENERAL] tracking scripts...

On Tue, Nov 26, 2013 at 12:24 PM, 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 300 > GB). Am I mi

Re: [GENERAL] tracking scripts...

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 300 GB). Am I missing something f

Re: [GENERAL] tracking scripts...

The ipv4 column is of type inet. It is the primary key (btree access) and access times for queries on individual ip addresses have been around 10-15 ms. On Tue, Nov 26, 2013 at 12:13 PM, Vick Khera wrote: > > On Tue, Nov 26, 2013 at 12:11 PM, Joey Quinn wrote: > >> update ipv4_table set count

Re: [GENERAL] tracking scripts...

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 300 GB). Am I missing something for it to tell me what the last line processed

Re: [GENERAL] tracking scripts...

On Tue, Nov 26, 2013 at 12:11 PM, Joey Quinn wrote: > update ipv4_table set country='xx' where ipv4 between 'xxx.xxx.xxx.xxx' > and 'xxx.xxx.xxx.xxx'; > > There are 127k lines like that (each with a different range and the > appropriate country code). Each is terminated with a semi-colon. Does th

Re: [GENERAL] tracking scripts...

The queries themselves are written like so: update ipv4_table set country='xx' where ipv4 between 'xxx.xxx.xxx.xxx' and 'xxx.xxx.xxx.xxx'; There are 127k lines like that (each with a different range and the appropriate country code). Each is terminated with a semi-colon. Does that make them indiv

Re: [GENERAL] tracking scripts...

Connect to the DB and run "select * from pg_stat_activity" to see what specific query your other connection is running. Then find that in your file to see how far it has progressed. I hope you profiled your queries to make sure they run fast before you started. :) On Tue, Nov 26, 2013 at 10:28

Re: [GENERAL] tracking scripts...

Now it's too late, but maybe you could allow to not use a single transaction ( but instead 127k transactions).4 Then at the end of every transaction you could print something in gui (print for pgscript, raise for plpgsql) or execute a command to write in a file (copy for instance). It would also b

[GENERAL] tracking scripts...

I have a fairly large table (4.3 billion rows) that I am running an update script on (a bit over 127 thousand individual update queries). I am using the gui. It has been running for about 24 hours now. Is there any good way to gauge progress (as in, how many of the individual update queries have fi