I ran vacuum analyze and immediately after I ran my query and the
estimated rows are way off. I suspect that it is something in my
configuration, but I don't know what.
I pasted my postgresql.conf file under the explain results.
Thank you
Sim
GroupAggregate (cost=4542.87..4543.12 rows=1 width=32
Hi Craig,
just wanted to mention that there is a new open-source solution
available that now also enables asynchronous, row-based, master-master
replication of PostgreSQL databases.
Name: rubyrep
Project website with full feature list, step-by-step tutorial and
screencast (from zero to running r
Thanks for this. I can't take the machines out of service at present,
but when I can, I'll look into shutting down services and seeing what
happens.
Andrew
On Tue, Jun 23, 2009 at 11:49 AM, Tom Lane wrote:
> Andrew Maclean writes:
>> Messages in the log are consistently of the form:
>> 2009-0
Thanks for this, I can easily determine the number of records I want.
Andrew
On Tue, Jun 23, 2009 at 10:52 AM, Steve
Crawford wrote:
> Andrew Maclean wrote:
>>
>> Is this the best way of getting a table of hourly dates?
>>
>> -- How to generate a table of dates at hourly intervals between two da
On Jun 23, 10:20 am, laconi...@gmail.com (Andrew Smith) wrote:
> Arndt,
>
> Your website says rubyrep runs on Linux and Windows - am I going to have
> difficulties if I want to try it on Solaris 10?
>
> Andrew
>
> 2009/6/23 Arndt Lehmann
>
> > On Jun 16, 7:48 pm, nishkars...@rediffmail.com (Nishka
On Mon, 2009-06-22 at 21:12 -0600, Scott Marlowe wrote:
> On Mon, Jun 22, 2009 at 8:59 PM, Craig
> Ringer wrote:
>
> > So ... it doesn't seem likely that statement-level replication would
> > ever get far in Pg because of nasty issues like this one.
>
> It's exactly what pg_pool does, and you can
On Mon, Jun 22, 2009 at 8:59 PM, Craig
Ringer wrote:
> So ... it doesn't seem likely that statement-level replication would
> ever get far in Pg because of nasty issues like this one.
It's exactly what pg_pool does, and you can choose it if you know what
you're doing. But yes, it's usually a bad
On Mon, Jun 22, 2009 at 8:50 PM, Conrad Lender wrote:
> On 23/06/09 03:44, Scott Marlowe wrote:
>> On Mon, Jun 22, 2009 at 4:51 PM, Gerry Reno wrote:
>>> Londiste is beta. The fact that Skype uses it is because it's part
>>> of Skytools which is their product. They may want to run their own
>>> b
On Mon, 2009-06-22 at 22:20 -0400, Gerry Reno wrote:
> Here is a link that describes the technique:
> http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html?page=1
Ah. You were referring to multiple-master replication, and your
reference to setting non-overlapping sequences
On 23/06/09 03:44, Scott Marlowe wrote:
> On Mon, Jun 22, 2009 at 4:51 PM, Gerry Reno wrote:
>> Londiste is beta. The fact that Skype uses it is because it's part
>> of Skytools which is their product. They may want to run their own
>> beta stuff. I don't.
>
> So, if they said it was general rel
Craig Ringer wrote:
On Mon, 2009-06-22 at 21:29 -0400, Gerry Reno wrote:
I don't know how it could guarantee that. That's really why row-based
is better.
Yep, especially in the face of things like user PL functions, C
functions, etc.
This page:
http://dev.mysql.com/doc/refman/5.0/e
On Tue, 23 Jun 2009 04:41:44 +1000, Mike Toews wrote:
Window functions appear to be the best solution for this style of
problem, and I'm looking forward to their applications. However, I'm
sticking with 8.3 for at least a year, so I'm not able to explore this
solution yet. For now, I can on
Andrew Maclean writes:
> Messages in the log are consistently of the form:
> 2009-06-23 08:28:26 EST WARNING: worker took too long to start; cancelled
> FATAL: could not reattach to shared memory (key=252, addr=023F): 487
> 2009-06-23 08:35:58 EST WARNING: worker took too long to start; can
On Mon, Jun 22, 2009 at 4:51 PM, Gerry Reno wrote:
> Joshua D. Drake wrote:
>>
>> On Mon, 2009-06-22 at 18:35 -0400, Gerry Reno wrote:
>>> Joshua D. Drake wrote:
>>>
>>> It is true. Otherwise show me a viable replication offering for
>>> postgresql that I can put into production and obtain support
On Mon, 2009-06-22 at 21:29 -0400, Gerry Reno wrote:
> I don't know how it could guarantee that. That's really why row-based
> is better.
Yep, especially in the face of things like user PL functions, C
functions, etc.
This page:
http://dev.mysql.com/doc/refman/5.0/en/replication-features-funct
Dario Teixeira writes:
> *However*, if I create a new type (which has an associated pg_class entry),
> and define a function which returns a SETOF that type, RowDescription will
> not tell me its OID. For example:
> ...
> Is this a bug or a conscious decision?
It's intentional; IIRC, the current
Craig Ringer wrote:
On Mon, 2009-06-22 at 20:48 -0400, Gerry Reno wrote:
Anyway, you seem to be unaware that built-in replication for
PostgreSQL already is moving along, with an implementation that's just
not quite production quality yet, and might make into the next version
after 8.4 if t
Arndt,
Your website says rubyrep runs on Linux and Windows - am I going to have
difficulties if I want to try it on Solaris 10?
Andrew
2009/6/23 Arndt Lehmann
> On Jun 16, 7:48 pm, nishkars...@rediffmail.com (Nishkarsh) wrote:
> > Hi Merlin, thanks for the detailed input.
> >
> > As per ur sug
On Mon, 2009-06-22 at 20:48 -0400, Gerry Reno wrote:
> > Anyway, you seem to be unaware that built-in replication for
> > PostgreSQL already is moving along, with an implementation that's just
> > not quite production quality yet, and might make into the next version
> > after 8.4 if things go
Greg Smith writes:
> On Mon, 22 Jun 2009, Gerry Reno wrote:
>> We need something as good as MySQL Replication.
> I certainly hope not, I was hoping for a reliable replication solution
> instead. Wow is the information you get searching for something like
> "mysql replication corruption [replay
Andrew Maclean wrote:
Is this the best way of getting a table of hourly dates?
-- How to generate a table of dates at hourly intervals between two dates.
-- select timestamp 'epoch' + generate_series * interval '1 second' as
dates from generate_series(extract(epoch from date_trunc('hour',
times
Greg Smith wrote:
On Mon, 22 Jun 2009, Gerry Reno wrote:
We need something as good as MySQL Replication.
I certainly hope not, I was hoping for a reliable replication solution
instead. Wow is the information you get searching for something like
"mysql replication corruption [replay log|bin
On Tue, 2009-06-23 at 09:28 +1000, Andrew Maclean wrote:
> [snipity snip snip]
>
> Notwithstanding all the previous discussion. I still think there is a
> problem with postgresql on windows.
I agree, but you don't seem to be prepared take any steps to diagnose
what Pg might be interacting with to
On Mon, 22 Jun 2009, Gerry Reno wrote:
We need something as good as MySQL Replication.
I certainly hope not, I was hoping for a reliable replication solution
instead. Wow is the information you get searching for something like
"mysql replication corruption [replay log|bin log]" scary. I al
Is this the best way of getting a table of hourly dates?
-- How to generate a table of dates at hourly intervals between two dates.
-- select timestamp 'epoch' + generate_series * interval '1 second' as
dates from generate_series(extract(epoch from date_trunc('hour',
timestamp '2001-02-16 20:38:4
BlackMage writes:
> I am having a small issue when entering values into the interval field. Say
> I want to enter a time of 2:03, two minutes and 3 seconds. When I insert
> that into an interval field, it comes up at 02:03:00, 2 hours, 3 minutes.
> The only way I've gotten around this so far is by
Sam Mason writes:
> On Mon, Jun 22, 2009 at 05:55:28PM -0400, Jack Orenstein wrote:
>> Why does adding the value restriction so radically change the execution
>> plan?
> PG doesn't have any cross column statistics and hence it assumes that pk
> and value are uncorrelated.
Even if they are corre
[snipity snip snip]
Notwithstanding all the previous discussion. I still think there is a
problem with postgresql on windows.
Messages in the log are consistently of the form:
2009-06-23 08:28:26 EST WARNING: worker took too long to start; cancelled
FATAL: could not reattach to shared memory (k
On Mon, Jun 22, 2009 at 08:43:43AM -0700, DaNieL wrote:
> Hi guys, i am in trouble with some simple data that seem like doesnt
> use any index.. and i dont know why.
It can be for a couple of reasons; firstly using an index isn't always a
good thing. In your case I'd guess you probably want to pr
On Mon, Jun 22, 2009 at 05:55:28PM -0400, Jack Orenstein wrote:
> ris-# select *
> ris-# from T
> ris-# where pk > 10
> ris-# and value = 'asdf'::bytea
> ris-# order by pk
> ris-# limit 100;
PG thinks that you're going to get 16 rows back matching those
conditions, bitmap heap scans are fa
On Jun 16, 7:48 pm, nishkars...@rediffmail.com (Nishkarsh) wrote:
> Hi Merlin, thanks for the detailed input.
>
> As per ur suggestion i will try to implement Slony-I.
>
> I think i will need some help to do it.
>
> I am useing Postgres 8.3.7, on Windows.
>
> I was following the Slony-I example in
Hi guys, i am in trouble with some simple data that seem like doesnt
use any index.. and i dont know why.
My test database structure is this one:
--
CREATE TABLE users(
id BIGSERIAL NOT NULL PRIMARY KEY,
nickname varchar(50),
email varchar(50) NOT NULL
);
CREATE INDEX users_nick_i
Hey,
I am having a small issue when entering values into the interval field. Say
I want to enter a time of 2:03, two minutes and 3 seconds. When I insert
that into an interval field, it comes up at 02:03:00, 2 hours, 3 minutes.
The only way I've gotten around this so far is by doing 00:02:03. But
Joshua D. Drake wrote:
On Mon, 2009-06-22 at 18:35 -0400, Gerry Reno wrote:
Joshua D. Drake wrote:
It is true. Otherwise show me a viable replication offering for
postgresql that I can put into production and obtain support for it.
Well, you can get support for Slony (known
On Mon, 2009-06-22 at 18:35 -0400, Gerry Reno wrote:
> Joshua D. Drake wrote:
> It is true. Otherwise show me a viable replication offering for
> postgresql that I can put into production and obtain support for it.
Well, you can get support for Slony (known to to be a bit complicated
but stable
Joshua D. Drake wrote:
On Mon, 2009-06-22 at 18:28 -0400, Gerry Reno wrote:
Kevin Barnard wrote:
Have you ever tried any of the postgresql replication offerings? The
only one that is remotely viable is slony and it is so quirky you may as
well forget it. The rest are in some stage
On Mon, 2009-06-22 at 18:28 -0400, Gerry Reno wrote:
> Kevin Barnard wrote:
> >
> Have you ever tried any of the postgresql replication offerings? The
> only one that is remotely viable is slony and it is so quirky you may as
> well forget it. The rest are in some stage of decay/abandonment. The
Kevin Barnard wrote:
On Jun 22, 2009, at 4:53 PM, Gerry Reno wrote:
I noticed that the user survey on the community page does not list
replication among the choices for development priority. For me,
replication is the most important thing that is critically missing
from postgresql. We need s
On Jun 22, 2009, at 4:53 PM, Gerry Reno wrote:
I noticed that the user survey on the community page does not list
replication among the choices for development priority. For me,
replication is the most important thing that is critically missing
from postgresql. We need something as good
On Mon, 2009-06-22 at 17:53 -0400, Gerry Reno wrote:
> I noticed that the user survey on the community page does not list
> replication among the choices for development priority. For me,
> replication is the most important thing that is critically missing from
> postgresql. We need something
On Jun 22, 2009, at 4:57 PM, Dario Teixeira wrote:
*However*, if I create a new type (which has an associated pg_class
entry),
and define a function which returns a SETOF that type,
RowDescription will
not tell me its OID. For example:
CREATE TYPE foobar_t AS (quant int);
CREATE FUNCTION
I have a table that looks like this:
create table T(pk int not null, value bytea, ..., primary key(pk))
I want to scan the table in batches of 100. I'll do this by issuing a sequence
of queries like this:
select *
from T
where pk > ?
and value = ?
order by pk
limit 1
Scott Marlowe wrote:
On Mon, Jun 15, 2009 at 12:23 PM, Mike Toews wrote:
This is easy to compute using a spreadsheet or in R, but how would I do this
with SQL? I'm using 8.3. Advice is appreciated.
FYI (and I'm no expert in this area) R is available as a pl for
postgres, look for pl/R
I noticed that the user survey on the community page does not list
replication among the choices for development priority. For me,
replication is the most important thing that is critically missing from
postgresql. We need something as good as MySQL Replication. Both
statement-based and row-
On Mon, 22 Jun 2009, Jorge Vidal - Disytel wrote:
I've been trying to compile pl/java from cvs with no success. Is it
going to be avaiable for pg 8.4 ?
pl/java CVS builds against 8.4. A common gotcha is that pljava will only
build with JDK 1.4 or 1.5 and fails with 1.6. It can be run
Hi community,
I've been trying to compile pl/java from cvs with no success. Is it
going to be avaiable for pg 8.4 ?
We are going to run some intensive test on our system, and I thought it
was a good idea to test it under 8.4, but for that I need pl/java...
Jorge
--
Sent via pgsql-general m
Hi,
> The information you want is always returned from the query
> as a row description message. This includes the type oid of
> real and computed columns.
>
> See the RowDescription message on this page for details:
Thanks for the reply. Note that is in fact RowDescription that PG'OCaml
is al
On Mon, Jun 22, 2009 at 12:41 PM, Mike Toews wrote:
> Hi Joel,
> An excellent book that I recently stumbled on is /Joe Celko's SQL for
> Smarties/ (recommended by someone on this list), which is a heavy read, but
> has an amazing depth to ANSI SQL. Of particular interest is "Chapter 24:
> Regions,
Hi Joel,
Window functions appear to be the best solution for this style of
problem, and I'm looking forward to their applications. However, I'm
sticking with 8.3 for at least a year, so I'm not able to explore this
solution yet. For now, I can only post-process the output in a non-SQL
environ
On Jun 22, 2009, at 11:43 AM, Dario Teixeira wrote:
How are you talking to the database ? ODBC? JDBC? LibPQ? Something
else?
Or do you want this from within PL/PgSQL ?
I'm hacking on a client-side library which talks directly to the
Postmaster
using the wire protocol [1]. I need this info
"Todd A. Cook" writes:
> Todd A. Cook wrote:
>> Tom Lane wrote:
>>> If you have time to repeat the experiments, it would be interesting to
>>> see what happens with consistent default_statistics_target across 8.3
>>> and 8.4.
>>
>> That would seem to be it:
>> 8.4b2 183 min 84,028,8
2009/6/22 Tom Lane :
> William Temperley writes:
>> I'm wondering if I happened as I'd started the same query twice.
>> The first had work_mem = 1MB so I tried to kill it and started another
>> with work_mem = 1000MB, but both were attempting to insert the same id
>> into a PK:
>> "insert into wor
On Mon, Jun 15, 2009 at 12:23 PM, Mike Toews wrote:
> This is easy to compute using a spreadsheet or in R, but how would I do this
> with SQL? I'm using 8.3. Advice is appreciated.
FYI (and I'm no expert in this area) R is available as a pl for
postgres, look for pl/R or plR
--
Sent via pgsql-ge
On Mon, Jun 22, 2009 at 7:17 AM, Abraham, Danny wrote:
> Hi
>
> We have a PG version 8.2 instance that won't come up after shutdown. The log
> shows the following:
>
> 2009-06-12 21:39:02.669 MDTLOG: database system was shut down at 2009-06-12
> 20:56:51 MDT
> 2009-06-12 21:39:02.748 MDTLOG: c
Hi,
> How are you talking to the database ? ODBC? JDBC? LibPQ? Something else?
> Or do you want this from within PL/PgSQL ?
I'm hacking on a client-side library which talks directly to the Postmaster
using the wire protocol [1]. I need this information to improve some of the
nullability-detectio
On Sat, Jun 20, 2009 at 4:01 PM, Pavel Stehule wrote:
>> *) misc:
>> *) never declare a function to return void
>
> ??? why - when we have not procedures ?
The main reason is that functions returning void can not be used with
binary protocol.
merlin
--
Sent via pgsql-general mailing list (pgsq
Hi Mike,
I happened upon your query, which is related to some stuff I've been
playing with.
Firstly, David's solution below doesn't work. I haven't yet tried to work
out why.
Secondly, I was hoping to be able to solve your problem nicely with
Postgres 8.4's window functions [1,2], which provide
Hi Assaf
Assaf Lavie wrote:
Can anyone please shed light on the difference between the two:
http://stackoverflow.com/questions/1023229/spatial-data-in-postgresql
(login _not_ required)
In general, if you store spatial data typically found in a CAD
environment, the build in spatial features ar
William Temperley writes:
> I'm wondering if I happened as I'd started the same query twice.
> The first had work_mem = 1MB so I tried to kill it and started another
> with work_mem = 1000MB, but both were attempting to insert the same id
> into a PK:
> "insert into world (geom, id) select st_unio
Todd A. Cook wrote:
Tom Lane wrote:
"Todd A. Cook" writes:
First, the numbers:
PG VersionLoad time pg_database_size autovac
--
8.2.13179 min 92,807,992,820on
8.3.7 180 min 84,048,744
2009/6/22 Tom Lane :
> William Temperley writes:
>> I've got two transactions I tried to kill 3 days ago using "select
>> pg_cancel_backend()", then SIGTERM, and have since then been
>> using 100% of a cpu core each. They were supposed to insert the
>> results of large unions with PostGIS and appe
Hi Mike,
I happened upon your query, which is related to some stuff I've been
playing with.
Firstly, David's solution below doesn't work. I haven't yet tried to work
out why.
Secondly, I was hoping to be able to solve your problem nicely with
Postgres 8.4's window functions [1,2], which can prov
William Temperley writes:
> I've got two transactions I tried to kill 3 days ago using "select
> pg_cancel_backend()", then SIGTERM, and have since then been
> using 100% of a cpu core each. They were supposed to insert the
> results of large unions with PostGIS and appear to have failed.
> Could
Hi
We have a PG version 8.2 instance that won't come up after shutdown. The log
shows the following:
2009-06-12 21:39:02.669 MDTLOG: database system was shut down at 2009-06-12
20:56:51 MDT
2009-06-12 21:39:02.748 MDTLOG: checkpoint record is at 9/D712F330
2009-06-12 21:39:02.748 MDTLOG: r
Hi All,
I've got two transactions I tried to kill 3 days ago using "select
pg_cancel_backend()", then SIGTERM, and have since then been
using 100% of a cpu core each. They were supposed to insert the
results of large unions with PostGIS and appear to have failed.
Could someone tell me what's the l
2009/6/22 Dario Teixeira :
>
> Hi,
>
> Is there some way to find out meta-information about the columns generated
> by any SELECT query? If the SELECT returns values from a regular table or
> a view, I can use pg_class and pg_attribute to get the info I need:
>
> CREATE TABLE foobar (quant int);
>
On Mon, 2009-06-22 at 05:26 -0700, Dario Teixeira wrote:
> Is there some way to find out meta-information about the columns generated
> by any SELECT query?
How are you talking to the database ? ODBC? JDBC? LibPQ? Something else?
Or do you want this from within PL/PgSQL ?
You'll usually find tha
Hi,
Is there some way to find out meta-information about the columns generated
by any SELECT query? If the SELECT returns values from a regular table or
a view, I can use pg_class and pg_attribute to get the info I need:
CREATE TABLE foobar (quant int);
SELECT quant FROM foobar;
SELECT attname,
Hi,
Erik Jones writes:
> On Jun 15, 2009, at 5:17 AM, Jasen Betts wrote:
>
>> On 2009-06-14, Garry Saddington wrote:
>>> def backup():
>>>import os
>>>os.popen("c:/scholarpack/postgres/bin/pg_dump scholarpack >
>>> c:/scholarpack/ancillary/scholarpack.sql")
>>
>> are you sure you're
pgadmin does it pretty nicely:
http://pgadmin.org/images/screenshots/pgadmin3_macosx.png
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi everybody,
is there a (stand-alone, command line) tool that converts the output
of EXPLAIN ANALYZE into a tree-like representation of the plan?
Cheers,
Viktor
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.
On Mon, 22 Jun 2009 11:40:08 +0200
Martijn van Oosterhout wrote:
> On Mon, Jun 22, 2009 at 09:53:59AM +0200, Ivan Sergio Borgonovo
> wrote:
> > > The OS knows much less about what anonymous memory (memory not
> > > backed by a file) "means" to a program and can't be as clever
> > > with it. Swapp
On Mon, Jun 22, 2009 at 09:53:59AM +0200, Ivan Sergio Borgonovo wrote:
> > The OS knows much less about what anonymous memory (memory not
> > backed by a file) "means" to a program and can't be as clever with
> > it. Swapping tends to be _much_ more CPU expensive than writing
>
> But issuing a wri
Albe Laurenz, 22.06.2009 09:52:
Sorry to be nitpicking, but maybe in that case it adds to clarity:
A comparison with NULL does not return FALSE, but "undefined" or NULL.
Try to run the following queries:
SELECT 1 = 2;
and
SELECT 1 = NULL;
and observe the different result.
In the context of t
On Mon, 22 Jun 2009 07:26:56 +0800
Craig Ringer wrote:
> > http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html
> > "If specified, the table is created as a temporary table.
> > Temporary tables are automatically dropped at the end of a
> > session"
> >
> > I'd interpret it as a c
Thomas Kellerer wrote:
>> CREATE TABLE test
>> (
>> value uuid
>> );
>>
>> INSERT INTO test VALUES ('----');
>> INSERT INTO test VALUES ('----');
>> INSERT INTO test VALUES (null);
>>
>> select * from test where value != '-
sergio nogueira wrote:
> dmonitor=> create table wereisthetable(col int);
> CREATE TABLE
> dmonitor=> select tablename, tablespace from pg_tables where
> tablename='wereisthetable';
>tablename| tablespace
> +
> wereisthetable |
> (1 row)
>
> dmonitor=> alter
77 matches
Mail list logo
