Hi all,
I would like to increase the database objects names limit from 64
characters to may be 128 characters to avoid name conflicts after
truncation of long table/sequence names.
I have seen a solution to this sometime back which includes (building
from source) modifying a header file then
In response to Allan Kamau :
Hi all,
I would like to increase the database objects names limit from 64
characters to may be 128 characters to avoid name conflicts after
truncation of long table/sequence names.
I have seen a solution to this sometime back which includes (building
from source)
On Fri, Nov 20, 2009 at 11:21 AM, A. Kretschmer
andreas.kretsch...@schollglas.com wrote:
In response to Allan Kamau :
Hi all,
I would like to increase the database objects names limit from 64
characters to may be 128 characters to avoid name conflicts after
truncation of long table/sequence
Hi all,
I'm trying to create a stored procedure using a cursor to udate a
table this is my code:
CREATE OR REPLACE FUNCTION test_select() RETURNS void AS
$BODY$
DECLARE
cur CURSOR FOR SELECT *
FROM zone
WHERE patient =
Hi all,
I'm experiencing a strange behavior with my postgresql 8.3:
performance is degrading after 3/4 days of running time but if I
just restart it performance returns back to it's normal value..
In normal conditions the postgres process uses about 3% of cpu time
but when is in degraded
In response to Allan Kamau :
Thanks Andreas, I too agree it may not be a good idea to have long for
various reasons including porting/upgrading issues and so on, as I
have many tables, I seem to have been caught up in describing table
functionality in the table name :-)
The table-name is the
I have a table
CREATE TABLE rfmitzeit
(
id_rf inet NOT NULL,
id_bf integer,
wert text,
letztespeicherung timestamp without time zone
CONSTRAINT repofeld_id_rf PRIMARY KEY (id_rf),
);
where for one id_bf there are stored mutliple values (wert) at multiple
dates:
id_bf, wert,
Hi Allan,
Am 20.11.2009 10:42, schrieb Allan Kamau:
...
Thanks Andreas, I too agree it may not be a good idea to have long for
various reasons including porting/upgrading issues and so on, as I
have many tables, I seem to have been caught up in describing table
functionality in the table name
Massa, Harald Armin, 20.11.2009 11:07:
I have a table
CREATE TABLE rfmitzeit
(
id_rf inet NOT NULL,
id_bf integer,
wert text,
letztespeicherung timestamp without time zone
CONSTRAINT repofeld_id_rf PRIMARY KEY (id_rf),
);
where for one id_bf there are stored mutliple values (wert) at
id_bf, wert, letztespeicherung:
98, 'blue', 2009-11-09
98, 'red', 2009-11-10
now I have a select to get the youngest value for every id_bf:
Not tested:
SELECT id_bf, wert,
max(letztespeicherung) over (partition by id_bf)
FROM rfmitzeit
no, that does not work:
SELECT * FROM (SELECT *,row_number() OVER (ORDER BY letztespeicherung
DESC) FROM rfmitzeit) t WHERE row_number=1
On Fri, Nov 20, 2009 at 5:31 AM, Massa, Harald Armin c...@ghum.de wrote:
id_bf, wert, letztespeicherung:
98, 'blue', 2009-11-09
98, 'red', 2009-11-10
now I have a
oops, I forgot the partition by. Here's the correct query:
SELECT * FROM (SELECT *,row_number() OVER (PARTITION BY id_rf ORDER BY
letztespeicherung DESC) FROM rfmitzeit) t WHERE row_number=1
You can also do it using SELECT DISTINCT ON:
SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY id_rf,
On Fri, Nov 20, 2009 at 11:07:37AM +0100, Massa, Harald Armin wrote:
Is it possible? How would the SQL utilizing WINDOW-functions look like?
there is no point in using window functions in here - simply use
DISTINCT ON.
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz /
Massa, Harald Armin, 20.11.2009 11:31:
no, that does not work:
id_bf;wert;max
98;blue;2009-11-10 00:00:00
98;red;2009-11-10 00:00:00
result is: I get the date of the youngest value.
My expected result is:
98;red
(that is, the entry of wert that is youngest)
Sorry then I misunderstood your
In response to hubert depesz lubaczewski :
On Fri, Nov 20, 2009 at 11:07:37AM +0100, Massa, Harald Armin wrote:
Is it possible? How would the SQL utilizing WINDOW-functions look like?
there is no point in using window functions in here - simply use
DISTINCT ON.
Right, but he want to extend
Is it possible? How would the SQL utilizing WINDOW-functions look like?
there is no point in using window functions in here - simply use
DISTINCT ON.
and how would I use DISTINCT ON for this query? Please bear in mind,
that there is more then one id_bf (just stopped the sample data with
one
2009/11/20 Massa, Harald Armin c...@ghum.de
Is it possible? How would the SQL utilizing WINDOW-functions look like?
there is no point in using window functions in here - simply use
DISTINCT ON.
and how would I use DISTINCT ON for this query? Please bear in mind,
that there is more
In response to Massa, Harald Armin :
Is it possible? How would the SQL utilizing WINDOW-functions look like?
there is no point in using window functions in here - simply use
DISTINCT ON.
and how would I use DISTINCT ON for this query? Please bear in mind,
that there is more then one
Is there any idle connections exists ?
--
Thanks
Sam Jas
--- On Fri, 20/11/09, Lorenzo Allegrucci lorenzo.allegru...@forinicom.it
wrote:
From: Lorenzo Allegrucci lorenzo.allegru...@forinicom.it
Subject: [GENERAL] Strange performance degradation
To: pgsql-performa...@postgresql.org
Cc:
and how would I use DISTINCT ON for this query? Please bear in mind,
that there is more then one id_bf (just stopped the sample data with
one of them)
I posted the answer more than hour ago:
SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY id_rf,
letztespeicherung DESC;
The equivalent
2009/11/20 silly silly8...@gmail.com
and how would I use DISTINCT ON for this query? Please bear in mind,
that there is more then one id_bf (just stopped the sample data with
one of them)
I posted the answer more than hour ago:
SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY
Thom, depesz, silly,
SELECT DISTINCT ON (id_bf) id_bf, wert, letztespeicherung
FROM rfmitzeit
ORDER BY id_bf, letztespeicherung ASC;
yes, that does work. I put it in the real world query (my example was
reduced to the relevant parts), and it provides an impressive speedup
(down from 2234 to
2009/11/20 Lorenzo Allegrucci lorenzo.allegru...@forinicom.it:
Hi all,
I'm experiencing a strange behavior with my postgresql 8.3:
performance is degrading after 3/4 days of running time but if I
just restart it performance returns back to it's normal value..
In normal conditions the
Lorenzo Allegrucci lorenzo.allegru...@forinicom.it writes:
So, my main question is.. how can just a plain simple restart of postgres
restore the original performance (3% cpu time)?
Are you killing off any long-running transactions when you restart?
regards, tom lane
--
On Thu, Nov 19, 2009 at 10:43:57AM -0500, Ray Stell wrote:
Is there a doc that covers planning disk storage of indices?
http://www.postgresql.org/docs/8.4/interactive/storage-page-layout.html#FTN.AEN82952
53.5. Database Page Layout
http://archives.postgresql.org/pgsql-docs/2002-04/msg3.php
=?ISO-8859-1?Q?nicola_zandon=E0?= nick.za...@gmail.com writes:
When i try to execute this code i get a :
ERROR: cursor cur is not a simply updatable scan of table zone
CONTEXT: SQL statement UPDATE zone SET end_period = $1 WHERE
CURRENT OF $2
PL/pgSQL function test_select line 16 at
On Fri, 20 Nov 2009, Lorenzo Allegrucci wrote:
performance is degrading...
In normal conditions the postgres process uses about 3% of cpu time
but when is in degraded conditions it can use up to 25% of cpu time.
You don't really give enough information to determine what is going on
here.
Due to database corruption, I had to rebuild a database. Originally we
installed PGSQL 7.4 and over the years bumped it up to 8.0. Now I would like
to use 8.4.
I'm trying to apply the plperl language to a 8.4.0_1 SQL_ASCII database and
I keep getting this error message:
Doug Sampson do...@dawnsign.com writes:
orion-root@/usr/ports: make search name=postgresql-plperl
Port: postgresql-plperl-7.4.25_1
It would appear that you forgot to update plperl to 8.4 along with
the core postgres package ... the error message is not tremendously
informative but it seems
Doug Sampson do...@dawnsign.com writes:
orion-root@/usr/ports: make search name=postgresql-plperl
Port: postgresql-plperl-7.4.25_1
It would appear that you forgot to update plperl to 8.4 along with
the core postgres package ... the error message is not tremendously
informative but it
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Allan Kamau
Sent: Friday, November 20, 2009 1:42 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Allowing for longer table names (64
characters)
On Fri,
Hi all,
I got a problem with the compilation of DBD::Pg 2.15.1 on AIX 5.3 TL9.
I have did a 64-bit compilation / installation of PostgreSQL 8.3.8 at
the beginning.
As the "postgres" user, I managed to execute "perl Makefile.PL"
successfully :
Configuring DBD::Pg 2.15.1
PostgreSQL version:
Hi all,
I see in the ora2pg documentation on
http://www.darold.net/projects/ora2pg/ that DBD::Pg is optional and
needed only for 'on the fly' migration.
I need to clarify this...
Does someone can explain me that is under 'on the fly' please ?
Concerning the documentation of ora2pg, is it
Thanks for the response . I was not able to get back on this.. Our set
up is Solaris. will be there any thing that I can use on Solaris
Regards
On Wed, Nov 18, 2009 at 4:58 PM, Joshua D. Drake j...@commandprompt.com wrote:
On Wed, 2009-11-18 at 16:46 -0500, akp geek wrote:
Hi All -
Hi everyone. The following error appeared in our log yesterday:
2009-11-19 13:39:40 PST:10.211.97.171(63815):[25668]: PANIC: stuck spinlock
(0x2aac3678b0e0) detected at dynahash.c:876
Followed by:
2009-11-19 13:44:24 PST::@:[1381]: LOG: server process (PID 25668) was
terminated by signal
On Fri, 2009-11-20 at 12:14 -0500, akp geek wrote:
Thanks for the response . I was not able to get back on this.. Our set
up is Solaris. will be there any thing that I can use on Solaris
They should work on Solaris. It is just Python + Utilities you can
already get (like rsync and ssh).
On Fri, 2009-11-20 at 17:57 +0100, Alexandra Roy wrote:
Hi all,
I see in the ora2pg documentation on
http://www.darold.net/projects/ora2pg/ that DBD::Pg is optional and
needed only for 'on the fly' migration.
I need to clarify this...
Does someone can explain me that is under 'on the
On Fri, Nov 20, 2009 at 12:15 PM, Matt Solnit msol...@soasta.com wrote:
We are running PostgreSQL 8.3.8 (64-bit) on a dedicated Fedora Core 8
machine, in Amazon EC2. This was using an extra-large instance, which
means 4 Xeon cores (2.66 GHz) and 15.5 GB of memory.
considering that ec2 is a
Would someone have a tool that displays statement execution times/stats from
the standard output from postgres logs?
I have attempted pgfouine but not had sucess with the log format.
Does anyone use pgfouine or have something that works for them?
Chris
Chris Barnes wrote:
Would someone have a tool that displays statement execution
times/stats from the standard output from postgres logs?
there's a logging option to put that in the logs, I thought? if so, you
could then display with `tail -f /path/to/postgreslog`
--
Sent via
Hi Merlin. Thanks very much for your reply. We are not using the High-CPU
instance type, so these kernel recommendations to not apply to us. Here is
what we're running:
$ uname -a
Linux domU-12-31-39-09-E8-21 2.6.21.7-2.fc8xen #1 SMP Fri Feb 15 12:34:28 EST
2008 x86_64 x86_64 x86_64
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
!DOCTYPE html PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN
Please don't send mail *only* as HTML to the mailing list
I got a problem with the compilation of
I have the logging options set to display anything longer than a duration of 1
second, but need something to display them.
Chris
Date: Fri, 20 Nov 2009 10:02:11 -0800
From: pie...@hogranch.com
To: compuguruchrisbar...@hotmail.com
CC: pgsql-general@postgresql.org
Subject: Re:
2009/11/20 Chris Barnes compuguruchrisbar...@hotmail.com
Would someone have a tool that displays statement execution times/stats
from the standard output from postgres logs?
I have attempted pgfouine but not had sucess with the log format.
Does anyone use pgfouine or have something that
In response to Chris Barnes compuguruchrisbar...@hotmail.com:
I have the logging options set to display anything longer than a duration of
1 second, but need something to display them.
cat?
Or are you specifically looking for something to analyze them? Slightly
different, and I'm assuming
On Fri, 2009-11-20 at 12:59 -0500, Chris Barnes wrote:
Would someone have a tool that displays statement execution
times/stats from the standard output from postgres logs?
I have attempted pgfouine but not had sucess with the log format.
Does anyone use pgfouine or have something that
So, the release notes for 8.4 had Easier to use Warm Standby in them. What
changes were made that make it easier to use?
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
Sent via pgsql-general mailing
Doug Sampson do...@dawnsign.com writes:
orion-root@/usr/ports: make search name=postgresql-plperl
Port: postgresql-plperl-7.4.25_1
It would appear that you forgot to update plperl to 8.4 along with
the core postgres package ... the error message is not tremendously
informative
On Thu, 2009-11-19 at 15:34 -0500, Chris Browne wrote:
thomas.granv...@gmail.com (Thomas Løcke) writes:
There's a new series of PostgreSQL books available:
PostgreSQL 8.4 Official Documentation - Volume I
PostgreSQL 8.4 Official Documentation - Volume II
And so on, up to volume V I
After trying to get pgfouine to work with stderr, I tried syslog. This seems to
work fine.
Produces nice html format reports. Graphing is also available.
This link gives set up details for setup for pgfouine.
Erik Jones wrote:
So, the release notes for 8.4 had Easier to use Warm Standby in them. What
changes were made that make it easier to use?
http://archives.postgresql.org/pgsql-committers/2009-05/msg00240.php
There were also some performance improvements in the recovery path in
8.4, like
On Friday 30 October 2009 15:30:34 Vick Khera wrote:
I really don't think I'm saturating the disk bandwidth. I see spikes
of 18MB/s or more, but it usually hovers at under 3MB/s according to
iostat output.
Looking at the iostat -x output is often more interesting - especially the
utilization
* Alexandra Roy (alexandra@bull.net) wrote:
Does someone can explain me that is under 'on the fly' please ?
Concerning the documentation of ora2pg, is it the good link ?
It worked well for me, using it mainly to copy table structures and
data. I was doing a one-time move to PG though, not
I get this running mnogosearch against my 8.4.1 database consistently:
PANIC: failed to add item to the right sibling in index logged_in_uid
STATEMENT: INSERT INTO logged_in (orgid, uid, remote_addr,
orig_session_id, new_session_id) VALUES ('394746', '1125200',
'24.251.180.193',
Sam Jas wrote:
Is there any idle connections exists ?
I didn't see any, I'll look better next time.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Brian Modra wrote:
I had a similar problem: I did a large delete, and then a selct which
covered the previous rows.
It took ages, because the index still had those deleted rows.
Possibly the same happens with update.
Try this:
vacuum analyse
reindex database
(your database name instead of
Hi experts -
I am running into issue with pg_standby. May be my
understanding is not correct. Please help. here is what I did .
1. I made changes in the postgresql.conf ( archive_mode = on
,archive_command = 'cp -i %p /opt/postgres/archive/%f' , archive_timeout =
Scott Marlowe scott.marl...@gmail.com writes:
I get this running mnogosearch against my 8.4.1 database consistently:
PANIC: failed to add item to the right sibling in index logged_in_uid
Huh. Don't suppose you can extract a reproducible test case ;-).
What are the exact definitions of the
On Fri, Nov 20, 2009 at 2:33 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Scott Marlowe scott.marl...@gmail.com writes:
I get this running mnogosearch against my 8.4.1 database consistently:
PANIC: failed to add item to the right sibling in index logged_in_uid
Huh. Don't suppose you can extract a
Scott Marlowe scott.marl...@gmail.com writes:
I get this running mnogosearch against my 8.4.1 database consistently:
PANIC: failed to add item to the right sibling in index logged_in_uid
Hmm, if uid is an integer then all the index entries will be the same
size, which eliminates my first
On Fri, Nov 20, 2009 at 3:15 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Scott Marlowe scott.marl...@gmail.com writes:
I get this running mnogosearch against my 8.4.1 database consistently:
PANIC: failed to add item to the right sibling in index logged_in_uid
Hmm, if uid is an integer then all
Scott Marlowe scott.marl...@gmail.com writes:
Access is pretty random actually, and fill factor on this database is
100% because it doesn't really get updated, just appended to. There
are a lot of parallel insertions going on if that helps.
Do you mean you actually have fillfactor set
On Fri, Nov 20, 2009 at 4:03 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Scott Marlowe scott.marl...@gmail.com writes:
Access is pretty random actually, and fill factor on this database is
100% because it doesn't really get updated, just appended to. There
are a lot of parallel insertions going on
Hi,
I've been looking for a way to use log_statement to log only select
statements; is this possible? (I'm using PostgreSQL 8.1.18)
I'd like to know if someone tried to read the data back from the
database, but don't care to see the inserts or updates since these may
have sensitive data like
On Nov 20, 2009, at 9:59 AM, Chris Barnes wrote:
Would someone have a tool that displays statement execution times/
stats from the standard output from postgres logs?
I have attempted pgfouine but not had sucess with the log format.
Does anyone use pgfouine or have something that works for
So I bought this book thinking it was a PDF file which I am fine with.
Its not. They download an .acsm file that will only work with their
proprietary Windoze software.
I am really angry with lulu.com about this.
- Alan Gruskoff
On 11/20/2009 11:35 AM, Joshua D. Drake wrote:
On Thu,
66 matches
Mail list logo
