Shaun Thomas wrote:
> This is PostgreSQL 9.1.9.
>
> So we've had "vacuumdb -avz" launched via cron at 3am for a few years
> now, and recently noticed some queries behaving very badly. While
> checking pg_stat_user_tables, I see this for several hundred of them:
>
> relname |
Gurkan Ozfidan wrote:
> We have been using postgresql-jdbc-8.3-604, and recently we started using
> postgresql-jdbc-9.2,
> everything seems working fine, except we are getting this error and could not
> find the solution yet.
> This related to our reporting and I could say that it is not happenin
Lonni J Friedman wrote:
> top shows over 90% of the load is in sys space. vmstat output
> seems to suggest that its CPU bound (or bouncing back & forth):
Can you run `perf top` during an episode and see what kernel
functions are using all that CPU?
This looks similar to cases I've seen of THP
Gurkan Ozfidan wrote:
>ERROR in createReportTable(): Cannot commit when autoCommit is enabled.
>PART OF THE CODE:
> } catch (Exception e) {
> System.err.println("ERROR in runStorageSQLQuery(): " +
>e.getMessage() + " sqlQuery: "+sqlQuery);
> e.printStackTrace();
I
Hello,
When I upgraded from 9.2 to 9.3, I copied the postgresql.conf and
pg_hba.conf files form 9.2 installation and used those. Is that likely to
cause any issues? I did not see any significant parameter changes from 9.2
to 9.3. So far, it is running fine.
Regards,
Jayadevan
Try with: Select split_part('897.78','.',2)
http://www.postgresql.org/docs/8.4/static/functions-string.html
On Tue, Sep 17, 2013 at 11:33 AM, Giuseppe Broccolo <
giuseppe.brocc...@2ndquadrant.it> wrote:
> Il 17/09/2013 04:21, karinos57 ha scritto:
>
> Hi,
>> I am trying to remove everything be
Hi!
Is it possible to get the plan of a query that's currently running? I have
queries which normally take about 15 minutes, but are now at 2+ hours. I forgot
to add the code to dump the plan to STDOUT, so I really don't know.
It might be data volume, but I want to confirm that the plan isn't b
Suppose I have a lot of data (several GB worth) to store each day, going
back a few years. I am considering a nested partition schema, in which
the data for each day is stored in a separate child table, which
inherits from an empty table for each month, which inherits from an
empty table for e
Hi,
I have this query
explain analyze
select distinct geoip_city(src_ip) , src_ip
from alert where timestamp>=1378512000 and timestamp < 1378598400
The explain takes forever, and CPU goes upto 100%. So I end up killing the
query/explain.
This one, without the function call, comes back in under
François Beausoleil wrote (on 18/09/2013):
Hi!
Is it possible to get the plan of a query that's currently running? I have
queries which normally take about 15 minutes, but are now at 2+ hours. I forgot
to add the code to dump the plan to STDOUT, so I really don't know.
It might be data volume
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jayadevan M
Sent: Wednesday, September 18, 2013 9:08 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Query - CPU issue
Hi,
I have this query
explain analyze
select distinct geoip_city(src_i
On the postgresapp.com you can only download the latest version of Postgres for
the Mac. Is it possible to download a specific version?
Thanks,
GTG
--
Gordon Ross
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.
I guessed the master was in slave mode, because I thought configuration
file had no problem, and the FATAL message was written when the slave
connects to the master.
However, I think that slave's parameter:hot_standby is off, because you
can connect to the master.
Regards,
(13/09/13 0:58
Jayadevan M wrote:
> explain analyze
> select distinct geoip_city(src_ip) , src_ip
> from alert where timestamp>=1378512000 and timestamp < 1378598400
>
> The explain takes forever
What is the longest you have let it run, in hours or minutes?
> This one, without the function call, comes back i
On 18/09/2013 14:44, "Rowan Collins" wrote:
>
>Running an EXPLAIN on the exact query should give you the plan. An
>EXPLAIN ANALYZE would have to wait for the query to complete either way,
>so you wouldn't be able to get it mid-way through a running process.
>
>If you don't know the exact query
On Wed, 18 Sep 2013 16:24:16 +0100
Tim Kane wrote:
>
>
> On 18/09/2013 14:44, "Rowan Collins" wrote:
>
> >
> >Running an EXPLAIN on the exact query should give you the plan. An
> >EXPLAIN ANALYZE would have to wait for the query to complete either
> >way, so you wouldn't be able to get it mid
On Wed, Sep 18, 2013 at 2:02 AM, Kevin Grittner wrote:
> Lonni J Friedman wrote:
>
>> top shows over 90% of the load is in sys space. vmstat output
>> seems to suggest that its CPU bound (or bouncing back & forth):
>
> Can you run `perf top` during an episode and see what kernel
> functions are
On 09/18/2013 07:45 AM, "Gabriel E. Sánchez Martínez" wrote:
Now to the questions. Would constraint exclusion work in a tree
fashion to prune tables from a query plan?
Constraint exclusion works by checking the constraints themselves. So if
your constraints are based on date, they will alway
On 09/18/2013 11:34 AM, Shaun Thomas wrote:
On 09/18/2013 07:45 AM, "Gabriel E. Sánchez Martínez" wrote:
Now to the questions. Would constraint exclusion work in a tree
fashion to prune tables from a query plan?
Constraint exclusion works by checking the constraints themselves. So
if your
On 09/18/2013 11:05 AM, "Gabriel E. Sánchez Martínez" wrote:
Or would it have to check every child partition, as follows?
#
Check partition 2000-01-01, 2000-01-02, ... , 2013-09-18. 5010 checks.
All days but 2012-01-01 are excluded. 5009 partitions excluded with
5010 constraint exclusion chec
On 09/18/2013 02:32 AM, Albe Laurenz wrote:
What happens if you run VACUUM (VERBOSE, ANALYZE) manually?
Are the statistics updated?
Are there any warnings?
It looks like running it manually worked fine, but I only ran it on
specific tables that showed up in the list of un-analyzed.
I see no
I'm trying to upgrade Postgresql from 9.2 to 9.3 in Fedora 18 using this
command as the postgres user
$ pg_upgrade -b /bin -B /usr/pgsql-9.3/bin -d /var/lib/pgsql/data -D
/var/lib/pgsql/9.3/data/ -j 2 -u postgres
The error in the log:
command: "/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
"/var
On 09/18/2013 12:20 PM, Shaun Thomas wrote:
On 09/18/2013 11:05 AM, "Gabriel E. Sánchez Martínez" wrote:
Or would it have to check every child partition, as follows?
#
Check partition 2000-01-01, 2000-01-02, ... , 2013-09-18. 5010 checks.
All days but 2012-01-01 are excluded. 5009 partition
On Tue, Sep 17, 2013 at 1:39 PM, Shaun Thomas wrote:
> Hey,
>
> This is PostgreSQL 9.1.9.
>
>
...
> Apparently something magical happened last Friday, and now analyze is
> broken somehow? Am I missing something, here? The log claims everything
> worked out OK:
>
> 2013-09-17 03:20:37 CDT|STATEME
On Wed, Sep 18, 2013 at 6:08 AM, Jayadevan M wrote:
>
> The function doesn't do much, code given below -
> CREATE OR REPLACE FUNCTION geoip_city(IN p_ip bigint, OUT loc_desc
> character varying)
> RETURNS character varying AS
> $BODY$
> SELECT l.id || l.country ||l.region || l.city FROM blocks
On Wed, Sep 18, 2013 at 2:02 AM, Kevin Grittner wrote:
> Lonni J Friedman wrote:
>
>> top shows over 90% of the load is in sys space. vmstat output
>> seems to suggest that its CPU bound (or bouncing back & forth):
>
> Can you run `perf top` during an episode and see what kernel
> functions are
On Wed, Sep 18, 2013 at 10:30 AM, Lonni J Friedman wrote:
>
> >
> > This looks similar to cases I've seen of THP defrag going wild.
> > Did the OS version or configuration change? Did the PostgreSQL
> > memory settings (like shared_buffers) change?
>
> I think you're onto something here with resp
Thanks,
I am confused now.When checkpoint_segment is set to 3 there are 7 WAL
segments in pg_xlog.When I set checkpoint_segments to 10 there are 11 WAL
segments.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Number-of-WAL-segment-tp5771221p5771321.html
Sent from the
Hello.
I have application architecture, where using Master/Slave replication
strategy. But slaves count about 1100. It's real big. Are there any
problems and whether it is at all? Because I don't saw production
application with slaves count.
On 9/18/2013 12:05 PM, Vladislav Lysov wrote:
I have application architecture, where using Master/Slave
replication strategy. But slaves count about 1100. It's real big. Are
there any problems and whether it is at all? Because I don't saw
production application with slaves count.
the number
On Wed, Sep 18, 2013 at 5:00 AM, Jayadevan M
wrote:
> When I upgraded from 9.2 to 9.3, I copied the postgresql.conf and
> pg_hba.conf files form 9.2 installation and used those. Is that likely to
> cause any issues?
Since 9.2, unix_socket_directory has been renamed to
unix_socket_directories, and
On Sep 18, 2013, at 7:40 AM, Gordon Ross wrote:
> On the postgresapp.com you can only download the latest version of Postgres
> for the Mac. Is it possible to download a specific version?
There are tagged builds on github -
https://github.com/PostgresApp/PostgresApp/releases
Cheers,
Steve
Hi...
I am programming a procedure in plpgsql language and it must return a boolean
response.
The procedure must return TRUE if the query is semantically correct and if not
correct, return FALSE.
This must be done without running the query.
Greetings to all, thanks in advance.
___
On 2013-09-19 02:01, Juan Daniel Santana Rodes wrote:
> I am programming a procedure in plpgsql language and it must return a
> boolean response. The procedure must return TRUE if the query is
> semantically correct and if not correct, return FALSE. This must be
> done without running the query.
Y
Kevin Grittner-5 wrote
> What is the longest you have let it run, in hours or minutes?
I let it run for about 10 minutes and killed it.
Kevin Grittner-5 wrote
> By the way, IMMUTABLE has to be wrong here, since the results
> depend on the state of the database. STABLE is likely the right
> desig
Jayadevan wrote:
> Kevin Grittner-5 wrote
>> What is the longest you have let it run, in hours or minutes?
>
> I let it run for about 10 minutes and killed it.
Doing the arithmetic, that means if the estimated row counts from
the explain of the outer query are right, the function must average
at
Juan Daniel Santana Rodés wrote
> Hi...
> I am programming a procedure in plpgsql language and it must return a
> boolean response.
> The procedure must return TRUE if the query is semantically correct and if
> not correct, return FALSE.
> This must be done without running the query.
> Greetings to
Kevin Grittner-5 wrote
> We already had this. I was asking for you to get EXPLAIN ANALYZE
> output for a run of the SELECT statement inside the geoip_city()
> function.
"Merge Join (cost=9268.34..26193.41 rows=6282 width=24) (actual
time=892.188..892.190 rows=1 loops=1)"
" Merge Cond: (l.id = b
The documentation states that "concurrent execution of a set of
Serializable transactions is guaranteed to produce the same effect as
running them one at a time in some order."
I'm not sure how the following behavior fits that definition. (Note that
this is just an experiment, not a use case. Pure
Rewriting the query in the function like this solved the issue (this is how
it was in the SQL at
https://github.com/tvondra/geoip/blob/master/sql/geoip--0.1.0.sql)
explain analyze
SELECT l.id, l.country, l.region, l.city FROM blocks b JOIN locations l ON
(b.location_id = l.id)
WHERE 37211969
Aaron Carlisle wrote:
> The documentation states that "concurrent execution of a set of
> Serializable transactions is guaranteed to produce the same
> effect as running them one at a time in some order."
>
> I'm not sure how the following behavior fits that definition.
> (Note that this is just
No particular effort; I saw a talk on the topic. I said it's academic
because I can't think of any real world example where this would matter
(the other definition of the word "academic").
On Thu, Sep 19, 2013 at 12:44 AM, Kevin Grittner wrote:
> Aaron Carlisle wrote:
>
> > The documentation st
Jayadevan wrote:
> "Merge Join (cost=9268.34..26193.41 rows=6282 width=24) (actual
> time=892.188..892.190 rows=1 loops=1)"
> " Merge Cond: (l.id = b.location_id)"
> " -> Index Scan using locations_pkey on locations l (cost=0.42..15739.22
> rows=438386 width=24) (actual time=0.022..865.025
Hi my friends...
I wrote in the last post a question similiar to this. But in this post I
clarify better the previous question.
I need know how to evaluated if a query is correct without execute it.
When I say if a query is correct, is that if I run the query, it did not
throw an exception.
Fo
Thank you for the pointers. I will try those suggestions. As I mentioned
later, resolving the query solved the problem for now.
Regards,
Jayadevan
On Thu, Sep 19, 2013 at 11:40 AM, Kevin Grittner-5 [via PostgreSQL] <
ml-node+s1045698n5771567...@n5.nabble.com> wrote:
> Jayadevan <[hidden
> email
45 matches
Mail list logo
