On 1/29/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> Vlad <[EMAIL PROTECTED]> writes:
> > 2. We ran several tests and found 8.3 generally 10% slower than 8.2.6.
>
> The particular case you are showing here seems to be all about the speed
> of hash aggregation --- at least the time differential is most
On Mon, 28 Jan 2008, Tom Lane wrote:
I speculate that you're noticing the slightly slower/more complicated
hash function that 8.3 uses for integers.
There was a similar slowdown in the Clodaldo case you tracked down
recently. Is it worth considering an addition to the release notes
warning
Vlad <[EMAIL PROTECTED]> writes:
> 2. We ran several tests and found 8.3 generally 10% slower than 8.2.6.
The particular case you are showing here seems to be all about the speed
of hash aggregation --- at least the time differential is mostly in the
HashAggregate step. What is the data type of a
Andy Colson <[EMAIL PROTECTED]> writes:
> An assignment of a value to a PL/pgSQL variable or row/record field is
> written as:
> variable := expression;
> Notice I just use = and not :=.
> My question is, is there a difference? It seems to work both ways, so
> I'm a little confused.
Yeah, plp
John Gateley <[EMAIL PROTECTED]> writes:
> Somehow I have managed to have two tables with duplicate keys.
> ...
> But, mostly, I'm wondering how I managed to get in this state,
What PG version is this? We've fixed some bugs in the past that
could give rise to duplicated rows.
On Mon, 2008-01-28 at 20:57 -0500, Greg Smith wrote:
> On Tue, 29 Jan 2008, Ow Mun Heng wrote:
>
> > Can you let me know what is the sql used to generate such a nice summary
> > of the tables?
>
> Might as well dupe the old text; this went out to the performance list:
>
> Greg Sabino Mullane re
On Tue, 29 Jan 2008, Ow Mun Heng wrote:
Can you let me know what is the sql used to generate such a nice summary
of the tables?
Might as well dupe the old text; this went out to the performance list:
Greg Sabino Mullane released a Nagios plug-in for PostgreSQL that you can
grab at http://buc
On Mon, 2008-01-28 at 22:17 +, Jeremy Harris wrote:
> We have one problematic table, which has a steady stream of entries
> and a weekly mass-delete of ancient history. The "bloat" query from
> Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns:
>
> schemaname | tablenam
Decibel! wrote:
> On Tue, Jan 22, 2008 at 08:20:30PM +0100, Gevik Babakhani wrote:
> > Dear all,
> >
> > I have created a group for PostgreSQL professionals at LinkedIn.com
> > Feel free to join if you like.
> >
> > http://www.linkedin.com/e/gis/51776/760A11717C03
>
> How is that different than
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---
Harald Armin Massa wrote:
> hello,
>
> within pgsql in \? the command help there is:
>
> \du [PATTERN] list user
Noted that to use autovacuum we need to turn on stats_row_level (along
with stats_start_collector that is on by default). Since
stats_row_level is off by default, I wonder what sort of overhead is
incurred since it sounds like it could add up if it's storing additional
stats information on eve
Christopher Browne wrote:
Is it possible that this table didn't see many updates, today?
Nope; about 24000 (according to the id sequence).
- Jeremy
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
On Jan 28, 2008 10:17 PM, Jeremy Harris <[EMAIL PROTECTED]> wrote:
> Hi,
>
> We're starting to run autovacuum for the first time on a system
> that's been running with nightly cron-driven vacuum for some time.
>
> Version:
> PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1
for now, are you able to insert duplicate keys(primary-key) into the two
tables you mentioned? if you can, check if your index is valid or not.
if index is valid, check if the unique contraint is still valid or not
-- perhaps you turned off the unique constraint, and insert the dup key,
and didn't
> This last bit often means there's some overhead in the systems
> timeofday() function calls.
>
> If you just use \timing from psql, and run the script without explain
> analyze, what speeds do you get on each?
>
17480ms (8.2.6)
20342ms (8.3RC2)
--
Vlad
---(end of broad
On Jan 28, 2008 3:56 PM, Vlad <[EMAIL PROTECTED]> wrote:
> Hello,
>
> 1. Freshly imported DB size on disk was about 3% smaller for 8.3
> 2. We ran several tests and found 8.3 generally 10% slower than 8.2.6.
> We took special measures to make sure that no third factors involved
> (no other apps run
On Jan 28, 2008 4:26 PM, John Gateley <[EMAIL PROTECTED]> wrote:
> On Mon, 28 Jan 2008 14:11:21 -0800
> "Dann Corbit" <[EMAIL PROTECTED]> wrote:
>
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > > [EMAIL PROTECTED] On Behalf Of John Gateley
> > > Sent: Mond
Pavel:
thanks for your feedback. To me plans generated by 8.2 and 8.3 are
equal and only differ by execution times. (I don't know, maybe email
wrap'ed lines, so I've attached plans to my message). Also, I confirm
that that parameter was increased (to 100) before the ran tests.
On Jan 28, 2008 4:2
On 28/01/2008, Pavel Stehule <[EMAIL PROTECTED]> wrote:
> Hello
>
> 8.3 plan is not optimal.
>
> >-> Hash Join (cost=2379.09..108954.69 rows=550548 width=52)
> > (actual time=76.188..8177.510 rows=2593557 loops=1)
>
> please, try to increase statistics
I am blind, I am sorry, It's noise, you
Hello
8.3 plan is not optimal.
>-> Hash Join (cost=2379.09..108954.69 rows=550548 width=52)
> (actual time=76.188..8177.510 rows=2593557 loops=1)
please, try to increase statistics
default_statistics_target (in postgresql.conf) to 100 and repeat
import and your test.
Regards
Pavel Stehul
On Mon, 28 Jan 2008 14:11:21 -0800
"Dann Corbit" <[EMAIL PROTECTED]> wrote:
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > [EMAIL PROTECTED] On Behalf Of John Gateley
> > Sent: Monday, January 28, 2008 2:04 PM
> > To: pgsql-general@postgresql.org
> > Subject:
Hi all,
I was reading the doc's on functions when I came across this:
"
An assignment of a value to a PL/pgSQL variable or row/record field is
written as:
variable := expression;
"
and I realized, I didn't do that! My assignments look like:
tmp = extract(minute from result);
if tmp > 30 th
Hi,
We're starting to run autovacuum for the first time on a system
that's been running with nightly cron-driven vacuum for some time.
Version:
PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20070418 (Red Hat 4.1.2-10)
We have one problematic table, which has a ste
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of John Gateley
> Sent: Monday, January 28, 2008 2:04 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Table has duplicate keys, what did I do
>
> Somehow I have managed to have tw
Somehow I have managed to have two tables with duplicate keys.
In both tables, the key is an integer, filled from a sequence.
There is only 1 duplicated entry in each table: in the first
table, there are two ID "1"s, and in the second table there are
two ID "123456"s (the second table entry is link
On Mon, Jan 28, 2008 at 04:38:01PM -0500, Tom Lane wrote:
> Sam Mason <[EMAIL PROTECTED]> writes:
> > I've just noticed that the handling of COUNT(record) and (record IS
> > NULL) aren't consistent with my understanding of them. If I run the
> > following query:
>
> > SELECT
> > NULL
Hello,
I wanted to share performance-related test results for Postgresql
8.3RC2 and 8.2.6. In both cases we used a freshly imported database
followed by analyze verbose command. Same server was used for testing
(2.6.23.14-107.fc8 x86_64) for each versions; both postgreses were
compiled with "-O3 -
"Tom Lane" <[EMAIL PROTECTED]> writes:
> Sam Mason <[EMAIL PROTECTED]> writes:
>> I've just noticed that the handling of COUNT(record) and (record IS
>> NULL) aren't consistent with my understanding of them. If I run the
>> following query:
>
>> SELECT
>> NULL IS NULL, COUNT( NULL
Sam Mason <[EMAIL PROTECTED]> writes:
> I've just noticed that the handling of COUNT(record) and (record IS
> NULL) aren't consistent with my understanding of them. If I run the
> following query:
> SELECT
> NULL IS NULL, COUNT( NULL ),
> (NULL,NULL) IS NULL, COUNT((NULL,NUL
"Richard M. Kues" <[EMAIL PROTECTED]> writes:
> CREATE TEMPORARY SEQUENCE s;
> SELECT
> nextval('s'), t.name
> FROM
> (
> SELECT
>tablename AS name
> FROM
>pg_tables
> ORDER BY
>tablename
> ) AS t
> WHERE
> t.name = 'pg_am'
> ;
> The result is:
> 1
On Tue, Jan 22, 2008 at 08:20:30PM +0100, Gevik Babakhani wrote:
> Dear all,
>
> I have created a group for PostgreSQL professionals at LinkedIn.com
> Feel free to join if you like.
>
> http://www.linkedin.com/e/gis/51776/760A11717C03
How is that different than the existing Postgres group?
--
D
Hallo all
During a performance tuning session I had a complex
query that gives some form of ranking. The "correct" way to
solve this, is the use of a scalar subquery that provides
the rank (or use "dense_rank over" in oracle).
But in my case the query is much too slow in this special case.
Even
On 1/28/08, Dominique Bessette - Halsema <[EMAIL PROTECTED]> wrote:
>
>
>
> I dont really understand the question but here's an xml example of what is
> getting posted to postgres, and i'm trying to figure out how to do a
> connection.close() type of thing in it.
>
>
Postgres doesn't have a built-
"Paul Houselander" <[EMAIL PROTECTED]> writes:
> $uweeklysqlu = "UPDATE stats_? SET
> statdate=?,inctr=inctr+?,insize=insize+?,inspam=inspam+?,invir=invir+?,outct
> r=outctr+?,outsize=outsize+?,outspam=outspam+?,outvir=outvir+? WHERE
> account=? AND stattype=? AND statmode=? AND extract(week from
>
i forgot to add on the xml code
http://www.opengis.net/wfs
http://schemas.opengis.net/wfs/1.0.0/WFS-transaction.xsd
http://www.opengeospatial.net/cite
http://:/geoserver/wfs/DescribeFeatureType?typename=cite:tracks,cite:alias
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xmlns:cite="
I dont really understand the question but here's an xml example of what is
getting posted to postgres, and i'm trying to figure out how to do a
connection.close() type of thing in it.
http://www.opengis.net/wfs
http://schemas.opengis.net/wfs/1.0.0/WFS-transaction.xsd
http://www.opengeospatial.n
Calum,
I do not know about Java and jdbc, but had similiar problems with
various Python-Database adapters. Have you double-checked that true
and false are valid options?
at http://www.postgresql.org/docs/current/interactive/libpq-connect.html
you can read
sslmode
This option determines whet
In article <[EMAIL PROTECTED]>,
Rainer Bauer <[EMAIL PROTECTED]> writes:
> Hopefully it won't be down for too long as I use a newsreader to read
> the lists.
I use www.gmane.org for that.
---(end of broadcast)---
TIP 9: In versions below 8.0, the
Hi, i have a question:
I have a database and i want to create a administrator user with total
control and another that only could make queries and could not see nor
modify the functions.
The reason is that in the database that i will distribute are the
functions with a big part of the software lo
>and another that only could make queries and could not see nor
>modify the functions.
Up until 8.1 it is possible to see all the functions (plpgsql code) in
pgAdmin, even if you don't have access rights to the schema.
I'm not sure how this is in 8.2 or 8.3?
WBL
On Jan 22, 2008 7:02 AM, Sim Zacks
PostgreSQL 8.3 RC2 is now available
The community testing of RC1 has yielded positive results. We avoided
several nasty bugs and are now releasing 8.3 RC2. We need the entire
community to continue testing to help us get to the final release.
Please report your bugs before the end of the month! .
Don't this satisfy your requirement?
(This isn't tested. There may be some syntax error.)
DELETE FROM your_table T
WHERE uid >
(SELECT MIN(uid)
FROM your_table M
WHERE M.somevalue = T.somevalue
)
;
The result I expected is:
SELECT * FROM your_table;
uid|somevalue
Yes I checked the file permissions... In fact I logged in as the user
specified in the service and started postgres via the command line
with
postgres -D ../data
and the postgres.conf parameters were loaded as confirmed with a "show
all" in psql
And still running postgresql from the service does n
Hello all,
I've noticed that when using Java and postgres-jdbc, setting ssl=false
in Properties doesn't get read, and ssl=false and ssl=true both cause
SSL to be tried.
E.g:
String url = "jdbc:postgresql://"+host+"/"+database;
Properties props = new Properties();
pr
Hi All
I have PostgreSQL 8.2.6 running on Windows. I tryed install slovak
dictionary for tsearch2.
INSERT INTO pg_ts_dict
VALUES('ispell_slovak','spell_init(internal)','DictFile="C:/slovak_utf8.dict",
AffFile="C:/slovak_utf8.aff", StopFile="C:/slovak_utf8.stop"',
'spell_lexize(internal,
Hi,
I've just noticed that the handling of COUNT(record) and (record IS
NULL) aren't consistent with my understanding of them. If I run the
following query:
SELECT
NULL IS NULL, COUNT( NULL ),
(NULL,NULL) IS NULL, COUNT((NULL,NULL));
The IS NULL checks both return TRUE as
"Harald Fuchs" <[EMAIL PROTECTED]> writes:
> If you want to select both columns, but have uniqueness over the first
> only, you can use a derived table:
>
> SELECT tbl.name, tbl.comment
> FROM tbl
> JOIN (SELECT name FROM tbl GROUP BY name HAVING count(*) = 1) AS t
> ON t.name = tbl.name
>
Or u
Hello Magnus,
MH> Yes, it's been down for quite a long time. AFAIK, Marc has a plan for
MH> fixing it, but I don't know the timeframe.
Thanks Magnus. I was not sure whether it was really the server.
Hopefully it won't be down for too long as I use a newsreader to read
the lists.
Rainer
---
On Mon, Jan 28, 2008 at 03:11:10PM +0100, H??kan Jacobsson wrote:
> Resulting in 4 columns in the ResultSet like:
>
> count(*)_from_table2_between_fromdate1_and_todate1 = X
> count(*)_from_table2_between_fromdate2_and_todate2 = Y
> count(*)_from_table3_between_fromdate1_and_todate1 = Z
> count(*
Håkan Jacobsson wrote:
> I don't get it=). How do I input the second daterange in this query?
Through whatever mechanism you are using to issue the query.
> Also, I have the ID from table1. Its known in the query.
Oops. I forgot that part in my reply. So my where clause is
wrong, though eas
Hi Adam and all,
I don't get it=). How do I input the second daterange in this query?
Also, I have the ID from table1. Its known in the query.
Wouldn't I need to use a UNION for this kind of query?
Håkan Jacobsson - System Developer
---
Adam Rich wrote:
Resulting in 4 columns in the ResultSet like:
count(*)_from_table2_between_fromdate1_and_todate1 = X
count(*)_from_table2_between_fromdate2_and_todate2 = Y
count(*)_from_table3_between_fromdate1_and_todate1 = Z
count(*)_from_table3_between_fromdate2_and_todate2 = V
> Resulting in 4 columns in the ResultSet like:
>
> count(*)_from_table2_between_fromdate1_and_todate1 = X
> count(*)_from_table2_between_fromdate2_and_todate2 = Y
> count(*)_from_table3_between_fromdate1_and_todate1 = Z
> count(*)_from_table3_between_fromdate2_and_todate2 = V
>
> Is thi
Hi all,
I have three tables like this:
table1 with column table1_ID
table2 with columns table1_ID, date ..etc
table3 with columns table1_ID, date ..etc
I would like to create one query to retrieve the rowcount ( count(*) ) from
both table2 and table3
WHERE date BETWEEN fromdate1 AND todate1
On Mon, Jan 28, 2008 at 10:07:43AM +0100, Rainer Bauer wrote:
> Hello,
>
> I cannot retrieve any list messages through the news server anymore
> (since last Tuesday). Are there any known problems?
>
> The reported error is: "503 NNTP server unavailable".
Yes, it's been down for quite a long time
Hi
Im in the process of moving an application that is currently using Postgres
7.4.8 to a new system running Postgres 8.1.9
Ive managed to use pg_dump to get all the data migrated across, however I
have an update script that causes an error on the new system but works on
the older one, I just can
Hi Greg,
first fo all: thanks a lot. i think i understood most of your comments, but -
of course - have now more questions than before :-)
Am Montag, 28. Januar 2008 01:07 schrieb Greg Smith:
> On Sun, 27 Jan 2008, [EMAIL PROTECTED] wrote:
> > ok, at the moment i got some traffic and my load is
In article <[EMAIL PROTECTED]>,
Phil Rhoades <[EMAIL PROTECTED]> writes:
> People,
>> select count(*) as cnt, name from tst group by name having count(*) = 1
> This worked for my basic example but not for my actual problem - I get
> "column comment must appear in the GROUP BY clause or be used i
Hello,
I cannot retrieve any list messages through the news server anymore
(since last Tuesday). Are there any known problems?
The reported error is: "503 NNTP server unavailable".
Rainer
---(end of broadcast)---
TIP 3: Have you checked our exten
59 matches
Mail list logo
