These three queries may help you to understand what's going on.
EXPLAIN ANALYZE SELECT count(catalog.id) FROM catalog;
EXPLAIN ANALYZE SELECT count(catalog.id) FROM catalog WHERE flag=false;
EXPLAIN ANALYZE SELECT count(DISTINCT catalog.id) FROM catalog WHERE flag=false;
Regards,
--
NAGAYASU
On Tue, Oct 5, 2010 at 12:09 AM, Fujii Masao masao.fu...@gmail.com wrote:
On Tue, Oct 5, 2010 at 2:57 AM, Rajesh Kumar Mallah
mallah.raj...@gmail.com wrote:
I am currently aiming to setup only SR between 2 servers only.
only SR means that you don't need Hot Standby (i.e., you don't need to
You can create an index on to_tsvector(replace(foo, '-', ' ')) and then
search using ...match..(replace(foo, ...), ...)
On Mon, Oct 4, 2010 at 11:41 AM, Arthur van der Wal
arthurvander...@gmail.com wrote:
Hi,
I want to change the way PostgreSQL splits text into tokens, for example:
With 9.0 there is the new hex encoding for BYTEA, which is activated by
default.
libpq BEFORE 9.0 are not able to decode that encoding.
I am programming with Python, using psycopg2. When psycopg2 is linked with
libpq 9.0, everything works fine with hex-encoding; if psycopg2 is linked
with libpq
2010/10/5 Konstantin Izmailov pgf...@gmail.com:
Let me ask another question related to the INSERT as well. I'm running
periodic import into Postgres and sometimes the application tries to insert
a duplicate row which, as expected, results in integrity violation error.
This aborts entire
Ben,
Thanks for the pointer. I did had a look at this b4 posting this. It
seems more of a back up rather than maintaining two databases. So i
basically have to set up a continuous archive and use the raw file to
update the other database.
I was rather looking for a direct way to maintain to
Scott, Thanks to answering the question. Thats exactly the reason.
Also any operation on the tables is going to be huge. LIke 10s of
thousands of rows inserted of deleted. This basically locks the table
and that y we wanted to update a database and sync it with the
production database when no one
On Tue, Oct 5, 2010 at 09:33, Massa, Harald Armin c...@ghum.de wrote:
With 9.0 there is the new hex encoding for BYTEA, which is activated by
default.
libpq BEFORE 9.0 are not able to decode that encoding.
I am programming with Python, using psycopg2. When psycopg2 is linked with
libpq 9.0,
Hey I know most of you guys are huge Windows fans, and UNIX support
will eventually be dropped in Postgres cuz really who uses that, but I
figure someone in here would love to help me with a Windows issue that
has me confused.
Postgres is working awesome on my Windows 2003 development
I am using two similar queries where one query is using index and other is
not.
I don't know why.
explain analyze
select ticketstatus
from ticketstatus
inner join role2picklist on
role2picklist.picklistvalueid=ticketstatus.picklist_valueid
where roleid!='H1' order by sortid
QUERY PLAN
On Tue, Oct 5, 2010 at 4:04 PM, Rajesh Kumar Mallah
mallah.raj...@gmail.com wrote:
I am currently aiming to setup only SR between 2 servers only.
only SR means that you don't need Hot Standby (i.e., you don't need to
run any query on the standby server)? If so, you can set wal_level to
On Sat, 2010-10-02 at 12:02 -0400, Rajesh Kumar Mallah wrote:
FATAL: too many KnownAssignedXids
That's a bug.
I've a few guesses about that, but I'll put some better instrumentation
in to see if we can prove what's causing it.
Can you reproduce that again, or was that failure isolated to that
I have two tables. Table C has about 300K records in it. Table E has
about a million records in it. Today I tried to run this query.
update C
set result_count = X.result_count
from C
inner join (select c_id, count(c_id) as result_count
On Tue, Oct 5, 2010 at 10:33 PM, Thomas Guettler h...@tbz-pariv.de wrote:
Hi,
just a guess: Counting is slow, since it needs to check all rows. Explained
here:
Actually counting was pretty fast. As I said when I created a table
using the subquery that went pretty fast. The update was the
Hi,
Yes. I have written a script file like the following.
include preamble.sk;
init cluster (id=1, comment='hostname=10.2.26.53 port=5432');
Thanks Regards,
Vishnu S
-Original Message-
From: Devrim GÜNDÜZ [mailto:dev...@gunduz.org]
Sent: Tuesday, October 05, 2010 10:59 AM
To:
Hi,
just a guess: Counting is slow, since it needs to check all rows. Explained
here:
http://wiki.postgresql.org/wiki/Slow_Counting
Thomas Güttler
Tim Uckun wrote:
I have two tables. Table C has about 300K records in it. Table E has
about a million records in it. Today I tried to run
Dear Riggs,
I am so sorry that i am not able to replicate it this time. May be i did
something really stupid that day .
I remember at one point of time i ran the standy without any recorvery.conf
even.
If i ever get that again i will surely try to replicate it and inform.
Regds
Rajesh Kumar
On 5 October 2010 10:20, Tim Uckun timuc...@gmail.com wrote:
The database machine is a linode host.
Good choice ;)
Obvious question, but I'll ask it: Have you run ANALYZE or VACUUM
ANALZYE on the database? Also being able to see the query plan would
help.
--
Thom Brown
Twitter: @darkixion
On Tue, Oct 5, 2010 at 11:38 PM, Thom Brown t...@linux.com wrote:
On 5 October 2010 10:20, Tim Uckun timuc...@gmail.com wrote:
The database machine is a linode host.
Good choice ;)
Obvious question, but I'll ask it: Have you run ANALYZE or VACUUM
ANALZYE on the database? Also being able to
On 5 October 2010 12:06, Tim Uckun timuc...@gmail.com wrote:
On Tue, Oct 5, 2010 at 11:38 PM, Thom Brown t...@linux.com wrote:
On 5 October 2010 10:20, Tim Uckun timuc...@gmail.com wrote:
The database machine is a linode host.
Good choice ;)
Obvious question, but I'll ask it: Have you run
On 10/05/2010 12:39 PM, Konstantin Izmailov wrote:
Howdy,
I've noticed that there is a difference in result of execution of the
following statement:
INSERT INTO testtable(col1) VALUES(NULL);
depending on whether the command is prepared or not.
If I call PQprepare/PQexecPrepared for the
I'd still recommend manually running ANALYZE on any involved tables,
just in case.
Just out of curiosity
Say I did run it. Would it make the query run a few orders of magnitude faster?
If you read my original post you'd see that I let the query run for
over an hour before I killed it.
Dear Postgres!
We have just moved our database to a more powerful pc and since then we
keep getting this strange error message below:
PGRES_FATAL_ERROR FATAL: invalid frontend message type 87
What does this message mean? What could cause this error?
Bets regards
Attila Miklosi
--
Sent via
On Mon, Oct 4, 2010 at 3:51 PM, andrew harvey a.andrewhar...@gmail.com wrote:
command=`psql -c SELECT * FROM pg_stat_database`
when you retain the query result within the variable $command you need
to count all the fields in the entire output in order to select the
particular one that you
On Mon, Oct 4, 2010 at 7:02 PM, Mike Christensen m...@kitchenpc.com wrote:
Hi guys, in my typical ignore the rules when I have an audience of
smart techie people, I thought I'd poll the group about software load
balancing. I know next to nothing about it, but I'm deploying my site
Some
On Tue, Oct 5, 2010 at 7:19 AM, Miklosi Attila amikl...@freemail.hu wrote:
Dear Postgres!
We have just moved our database to a more powerful pc and since then we
keep getting this strange error message below:
PGRES_FATAL_ERROR FATAL: invalid frontend message type 87
What does this message
On Tue, 2010-10-05 at 11:03 +0530, Vishnu S. wrote:
Yes. I have written a script file like the following.
include preamble.sk;
init cluster (id=1, comment='hostname=10.2.26.53 port=5432');
It does not give me the $CLUSTER_NAME information.
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant,
Hi!
Here are the details you asked for:
The hardware :
an intel server board S5500BC,
intel xeon quad core 2GHz processor,
8Gb RAM
and RAID.
The OS is debian linux-lenny
Here are the `uname` details:
Linux central-hu 2.6.26-2-686-bigmem #1 SMP Wed Aug 19 06:34:05 UTC 2009
i686 GNU/Linux
The
On 5 October 2010 14:17, Miklosi Attila amikl...@freemail.hu wrote:
Hi!
Here are the details you asked for:
The hardware :
an intel server board S5500BC,
intel xeon quad core 2GHz processor,
8Gb RAM
and RAID.
The OS is debian linux-lenny
Here are the `uname` details:
Linux central-hu
Hi!
Here are the details you asked for:
The hardware :
an intel server board S5500BC,
intel xeon quad core 2GHz processor,
8Gb RAM
and RAID.
The OS is debian linux-lenny
Here are the `uname` details:
Linux central-hu 2.6.26-2-686-bigmem #1 SMP Wed Aug 19 06:34:05 UTC 2009
i686 GNU/Linux
The
On 10/04/10 15:55, Greg Sabino Mullane wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
This is more of an odd anoyance than anything, but for the past month or
so when I \q out of psql I have to wait 20-25 seconds for the return to
the shell prompt. This is the only copy of psql
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
My question: Which way is available to query the linked libpq version?
That's information your driver should be supplying. For example, in
Perl (DBD::Pg), you would say
if ($dbh-{pg_lib_version} = 9) {
I'd raise a bug with psycopg2 if
On Tue, Oct 5, 2010 at 16:32, Greg Sabino Mullane g...@turnstep.com wrote:
My question: Which way is available to query the linked libpq version?
That's information your driver should be supplying. For example, in
Perl (DBD::Pg), you would say
if ($dbh-{pg_lib_version} = 9) {
I'd raise
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
if ($dbh-{pg_lib_version} = 9) {
I'd raise a bug with psycopg2 if it does not provide that information.
How does the driver figure it out?
DBD::Pg parses pg_config --version, then passes the information
to the C programs for
On 5 October 2010 16:33, Greg Sabino Mullane g...@turnstep.com wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
if ($dbh-{pg_lib_version} = 9) {
I'd raise a bug with psycopg2 if it does not provide that information.
How does the driver figure it out?
DBD::Pg parses
That's getting too complicated. It can be done simply as:
SELECT DISTINCT(test.people.id) test.people.id, test.people.name, test.likes.ref
FROM test.people LEFT JOIN test.likes ON test.people.id = test.likes.ref
ORDER BY test.people.id, test.likes.date DESCENDING
Assuming people.id is unique,
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
I would imagine that most libpq wrapping drivers use libpq's
PQserverVersion(), which returns an integer that looks like 9.
Yeah, I'm familiar with that (I wrote it :) but it's not useful
here for two reasons:
1) It's not available on
I would imagine that most libpq wrapping drivers use libpq's
PQserverVersion(), which returns an integer that looks like 9.
exactly that is exposed as cn.server_version property of the psycopg2
connection object; but it does not help, as a 8.4libpq happily connects to a
9.0 server... for
I need to get a join across about 8 tables to only return rows for the MAX date
in one of the tables for all otherwise identical return results. I am trying to
get the most recent edit of normalized records that are composed of elements
from all the tables. There could be 1 to 100+ successive
Jeff Ross jr...@wykids.org writes:
Thanks for the hint, Greg! It was indeed the .psql_history file.
Although I'm using the default history size, the file was over 5MB in
size and full of a table dump. I truncated the file and no more delay
quitting psql.
Huh ... what version of
Peter Geoghegan peter.geoghega...@gmail.com writes:
On 5 October 2010 16:33, Greg Sabino Mullane g...@turnstep.com wrote:
How does the driver figure it out?
DBD::Pg parses pg_config --version, then passes the information
to the C programs for directive fiddling. I certainly hope
other
On 10/05/10 2:20 AM, Tim Uckun wrote:
The database machine is a linode host. It's got two gigs of RAM I
realize that this is not the ideal host for a database but it's what I
have to work with.
whats the storage like on linode? Is that some sort of virtualized
cloudy thing? wild guess
On Tue, Oct 5, 2010 at 18:41, Tom Lane t...@sss.pgh.pa.us wrote:
Peter Geoghegan peter.geoghega...@gmail.com writes:
On 5 October 2010 16:33, Greg Sabino Mullane g...@turnstep.com wrote:
How does the driver figure it out?
DBD::Pg parses pg_config --version, then passes the information
to the
On 10/05/10 10:35, Tom Lane wrote:
Jeff Rossjr...@wykids.org writes:
Thanks for the hint, Greg! It was indeed the .psql_history file.
Although I'm using the default history size, the file was over 5MB in
size and full of a table dump. I truncated the file and no more delay
quitting psql.
Jeff Ross jr...@wykids.org writes:
On 10/05/10 10:35, Tom Lane wrote:
Huh ... what version of libreadline (or libedit) are you using exactly?
How did the large dump get in there?
According to the README in the source, this is Gnu Readline library,
version 4.3..
Well, that's not terribly
Hi,
I need to download the build of Postgres 9.0 compiled for win32bit.
I see there is available the 9.0.1 version.
But I'm searching specifically the 9.0 for a test.
There some link with old binaries ?
Thx,
Andrea Peri.
--
Sent via pgsql-general mailing list
I'm trying to install 9.0.1 32 bit on Windows 7 x64. I cannot use the 64
bit server because we use the UUID contrib module and it evidently isn't
compatible with Windows 64 bit libraries. When I try and install the 32 bit
server I get the following error message.
Problem running the
We could add a PQlibpqVersion(), maybe, but it would be many years
before client code could rely on that being present.
I think we should.
And in a small way they can already - if they check for it
dynamically, they'll know if it was 9.1 or newer at least :-) It'll be
a long time
On 4 Oct 2010, at 20:40, Thom Brown wrote:
Hi all,
We're currently testing a new javascript change on the PostgreSQL
docs. This is to make sure monospaced fonts still appear at a
reasonable size between browsers. I'd appreciate it if some of you
could do some browser testing.
When our customer runs Crystal Reports reports created in an old version
of CR and edited with CR XI, he gets a large number of warnings in his
Postgres log files that look like this:
2010-10-05 11:28:00 EDTWARNING: nonstandard use of \\ in a string
literal at character 159
2010-10-05 11:28:00
On 5 Oct 2010, at 10:21, AI Rumman wrote:
explain analyze
select cf_1507
from cf_1507
inner join role2picklist on
role2picklist.picklistvalueid=cf_1507.picklist_valueid
where roleid!='H1' order by sortid
A few indexes on role2picklist.sortid and cf_1507.picklist_valueid would
probably
Dear Rob ,
this will give warning:
SELECT co_name from foo where co_name ilike '%\\_%' limit 10;
and this will not:
SELECT co_name from foo where co_name ilike E'%\\_%' limit 10;
regds
Rajesh Kumar Mallah.
Dear List ,
recent change of bytea_output format from escape to hex in 9.0 apparently breaks
popular persistent session handling perl modules like Apache::Session::Postgres
which stores pickled data structures in bytea columns of db table.
It is difficult to guess the root cause from the
On Tue, Oct 5, 2010 at 1:28 PM, Andrea Peri 2007 aperi2...@gmail.com wrote:
Hi,
I need to download the build of Postgres 9.0 compiled for win32bit.
I see there is available the 9.0.1 version.
But I'm searching specifically the 9.0 for a test.
You really want to use the 9.0.1 version. It
Op 5 okt 2010, om 20:12 heeft Rajesh Kumar Mallah het volgende
geschreven:
Dear List ,
recent change of bytea_output format from escape to hex in 9.0
apparently breaks
popular persistent session handling perl modules like
Apache::Session::Postgres
which stores pickled data
On Tue, Oct 5, 2010 at 6:34 PM, Michael Gould
mgo...@intermodalsoftwaresolutions.net wrote:
I'm trying to install 9.0.1 32 bit on Windows 7 x64. I cannot use the 64
bit server because we use the UUID contrib module and it evidently isn't
compatible with Windows 64 bit libraries. When I try
On 5 October 2010 18:45, Alban Hertroys
dal...@solfertje.student.utwente.nl wrote:
On 4 Oct 2010, at 20:40, Thom Brown wrote:
Hi all,
We're currently testing a new javascript change on the PostgreSQL
docs. This is to make sure monospaced fonts still appear at a
reasonable size between
Dear Rob,
Please do not go offlist.
Its not a matter of cancelling,
consider the fragment -- relname like 'coil\\_status'
the underscore has to be escaped because if its not done
'_' will match any *single* character (rule of ilike matching) ,
its escaped so that it matches '_' in strings.
[CC'ing the list to close the loop]
On Tue, Oct 5, 2010 at 7:43 PM, Michael Gould
mgo...@intermodalsoftwaresolutions.net wrote:
Dave,
I found the problem. For some reason icacls.exe can't be found, in fact
none of the executable files in system32 can be found from the c:\.
I'm not sure
It does appear so.. although I'm logged into the network administrator
account. Weird but at least I can now track it down.
Best Regards
Mike Gould
Dave Page dp...@pgadmin.org wrote:
[CC'ing the list to close the loop]
On Tue, Oct 5, 2010 at 7:43 PM, Michael Gould
Good after everyone,
We are experiencing some performance issues on a table with 7 fields,
8,800,000 rows. During some exercises, one thing I noticed is I need to change
the configuration of the system to house pgsql_tmp on a host partition instead
of the drive array... that will get
On 5 Oct 2010, at 20:24, Thom Brown wrote:
Thanks for the feedback.
To me your attached images look quite normal. I do, however, still
get the feeling the font is a tad too small in Firefox. It calculates
a size change of 1.33(recurring), which gets rounded down to 1.3em.
I'm wondering
I would like to know if there is a way to configure 9 to do this.
I have 4 unique databases running on 4 servers.
I would like to have them replicate to a remote site for disaster recovery.
I would like to consolidate these 4 database into one machine and use streaming
replication from the 4
On 10/5/2010 2:12 PM, Chris Barnes wrote:
I would like to know if there is a way to configure 9 to do this.
I have 4 unique databases running on 4 servers.
I would like to have them replicate to a remote site for disaster
recovery.
I would like to consolidate these 4 database into one
Below is an excerpt from a comment over at Reddit. It seemed quite
interesting to read about the difference in PG and DB2's query optimizer.
Can anybody shed any more light on this ?
-Sandeep
--
there are a couple really important things that DB2 does with Materialized
-- create index tags1 on allwikitags(tagword) - after still 18 seconds
-- create index tags6 on allwikitags(tagword,instances desc, pagename) - after
now 32 seconds
-- drop index tags6
-- create index tags2 on allwikitags(instances) - after still 18 seconds
-- create index tags3 on
Can anyone tell me if there is some setting that will stop these warnings
from showing up (other than turning off warning messages completely, which I
am about to suggest that the customer do)? Also, does anyone know why my
computer is not showing warnings while our customer's system is?
On Tue, Oct 5, 2010 at 12:02 PM, Ozz Nixon ozzni...@gmail.com wrote:
Good after everyone,
We are experiencing some performance issues on a table with 7 fields,
8,800,000 rows. During some exercises, one thing I noticed is I need to
change the configuration of the system to house
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
The real problem is that neither of these can be trusted to tell you the
*library* version. PQserverVersion() is something else altogether,
and I wouldn't want to assume that pg_config exactly matches the library
you're linked to --- if
Hey all,
Could you call it as PQlibVersion() or even PQversion() instead of
PQlibpqVersion() ?
2010/10/5 Tom Lane t...@sss.pgh.pa.us
Peter Geoghegan peter.geoghega...@gmail.com writes:
On 5 October 2010 16:33, Greg Sabino Mullane g...@turnstep.com wrote:
How does the driver figure it out?
On Tue, Oct 5, 2010 at 22:39, Greg Sabino Mullane g...@turnstep.com wrote:
The real problem is that neither of these can be trusted to tell you the
*library* version. PQserverVersion() is something else altogether,
and I wouldn't want to assume that pg_config exactly matches the library
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Er...yes it will match exact...because we[1] get the location of the
library files from pg_config as well. :) Handy little utility that.
How do you get pg_config to tell you which of the multiple versions of
libpq that may be installed on
Greg Sabino Mullane g...@turnstep.com writes:
The real problem is that neither of these can be trusted to tell you the
*library* version. PQserverVersion() is something else altogether,
and I wouldn't want to assume that pg_config exactly matches the library
you're linked to --- if it's even
whats the storage like on linode? Is that some sort of virtualized cloudy
thing? wild guess says committed random writes are really slow.
Yes it's some sort of a virtual cloudy thing. I can't tell you more
than that though. They sell virtual machines and this project runs on
them.
Does
On Tue, Oct 5, 2010 at 22:53, Greg Sabino Mullane g...@turnstep.com wrote:
Er...yes it will match exact...because we[1] get the location of the
library files from pg_config as well. :) Handy little utility that.
How do you get pg_config to tell you which of the multiple versions of
libpq
Hi!
I'm on the committee for MySQL Conf this year, and the committee is
specifically seeking talks about PostgreSQL. The idea is to broaden
the scope of the conference to include a lot of different open source
database technology, including a bunch more about Postgres. The
organizing committee is
I'm not sure if this is the correct list for this, but I'm hoping to get
some help with a data model I'm struggling with. Let me know if this should
be elsewhere! :)
We have an event schedule system that basically tells an event handler when
an event should be fired, and keeps a history of
Well, when I did something similar a very long time ago, I put a next time to
fire column in the table. Each time an event is fired, the application updates
that column.
Was a lot simpler than baroque searches for events that would need firing
between the last check and now. Was a lot cleaner
On Tue, Oct 5, 2010 at 3:28 PM, Sandeep Srinivasa s...@clearsenses.com wrote:
Below is an excerpt from a comment over at Reddit. It seemed quite
interesting to read about the difference in PG and DB2's query optimizer.
The entire post makes it sound like the author believes that Postgres
has
I've seen mention that it might be possible for MT5 to re-gain Postgres support
(they dropped it in version 5) through the use of plugins. Does anyone know of
such a thing and/or have an installation working themselves? Would greatly
appreciate any info you can share.
Thanks,
-M@
--
On Oct 5, 4:19 am, amikl...@freemail.hu (Miklosi Attila) wrote:
Dear Postgres!
We have just moved our database to a more powerful pc and since then we
keep getting this strange error message below:
PGRES_FATAL_ERROR FATAL: invalid frontend message type 87
What does this message mean? What
Hi There,
I have a table looks like this:
(order_id, user_id, order_time)
One user_id can have multiple orders with order_id as the primary key,
now I want to get a list of users, ordered by their latest order
respectively, for example, if user A has two orders, one on today, the
other a
On Tue, Oct 5, 2010 at 10:26 PM, Min Yin y...@ai.sri.com wrote:
Hi There,
I have a table looks like this:
(order_id, user_id, order_time)
One user_id can have multiple orders with order_id as the primary key, now I
want to get a list of users, ordered by their latest order respectively,
On 10/06/2010 12:31 AM, Dennis Gearon wrote:
I need to get a join across about 8 tables to only return rows for the MAX date
in one of the tables for all otherwise identical return results. I am trying to
get the most recent edit of normalized records that are composed of elements
from all
Craig,
I think that failure of a previous command is the most possible reason. It
is an unexpected discovery since I keep track of statements that were
already prepared on the connection (to avoid preparing a statement twice).
However, the code might be flowed somehow. Anyway, I decided to use
Thanks for the start. I'll start on it.
Dennis Gearon
Signature Warning
EARTH has a Right To Life,
otherwise we all die.
Read 'Hot, Flat, and Crowded'
Laugh at http://www.yert.com/film.php
--- On Tue, 10/5/10, Craig Ringer cr...@postnewspapers.com.au wrote:
From: Craig
86 matches
Mail list logo
