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 recomp
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 so
On Fri, Nov 20, 2009 at 11:21 AM, A. Kretschmer
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 names.
>> I have seen a so
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" condi
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 t
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, letztesp
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
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 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 mi
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
> > 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 wit
2009/11/20 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 th
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 mo
Is there any idle connections exists ?
--
Thanks
Sam Jas
--- On Fri, 20/11/09, Lorenzo Allegrucci
wrote:
From: Lorenzo Allegrucci
Subject: [GENERAL] Strange performance degradation
To: pgsql-performa...@postgresql.org
Cc: pgsql-general@postgresql.org
Date: Friday, 20 November, 2009, 9:43
> 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
> > 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,
> let
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 :
>
> 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%
Lorenzo Allegrucci 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
--
Sent via pgsql-general maili
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?= 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 SQL stat
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:
orion-root@/home/postgres:
Doug Sampson 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 consistent with th
> Doug Sampson 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 cons
> -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)
>
>
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: 80
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 the
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 wrote:
> On Wed, 2009-11-18 at 16:46 -0500, akp geek wrote:
>> Hi All -
>>
>> I would lik
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 6:
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 'o
On Fri, Nov 20, 2009 at 12:15 PM, Matt Solnit 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 virtualized
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 pgsql-genera
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 GNU/Li
-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 DB
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
> 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
>
>
In response to Chris Barnes :
>
> 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 that's what you meant. Have a
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 th
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 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
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 v
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.
http://www.thelazysysadmin.net/2009/08/pgfouine-automatic-report-setup-with-
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
utilizatio
* 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, n
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', 'c2f5bfe61be2e
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 =
60
Scott Marlowe 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 table and index?
On Fri, Nov 20, 2009 at 2:33 PM, Tom Lane wrote:
> Scott Marlowe 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 ;-).
> Wh
Scott Marlowe 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 theory about there be
On Fri, Nov 20, 2009 at 3:15 PM, Tom Lane wrote:
> Scott Marlowe 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 s
Scott Marlowe 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 somewhere? It didn't show
o
On Fri, Nov 20, 2009 at 4:03 PM, Tom Lane wrote:
> Scott Marlowe 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
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 cre
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 th
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, 2009-1
66 matches
Mail list logo
