On 01/31/2011 08:48 PM, Tom Lane wrote:
Nicos Panayides writes:
the following function takes forever to execute as is. I see 'Inserting
original actions in temporary table' and nothing after that. If i
replace orig_user_id in the FOR loop with 1811 (the same orig_user_id
passed as the funct
Hello all,
I am in the process of migrating a system from Postgresql 8.3 to 9.0, and
have run into a problem with the task queue systems I am using.
The task queue controls the allocation of tasks between about 1000
processes working in parallel, and is essentially a table of
record_id
On Monday, January 31, 2011 10:14:29 pm AI Rumman wrote:
> I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1.
>
> I have a table "testtab"
> \d testtab
> id int,
> hours varchar
>
> When I execute the following:
> select sum(hours) from testtab
> I get cast error.
Try:
select sum(hours::i
On 2/1/2011 6:32 AM, Yngve Nysaeter Pettersen wrote:
Hello all,
I am in the process of migrating a system from Postgresql 8.3 to 9.0,
and have run into a problem with the task queue systems I am using.
The task queue controls the allocation of tasks between about 1000
processes working in paral
Hello,
From the documentation of CLUSTER table
(http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I
understand that clustering can be achieved by re-creating the table
like this:
CREATE TABLE newtable AS
SELECT * FROM table ORDER BY columnlist;
My question is: If I upload w
Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to send
data during many minutes, and is there a way to be notified of a potential
error (for example an invalid value in one column) before sending
PQputCopyEnd? Or do I have to send my data in small chunks (for example
batch of 10
As a result of my recent encounter with table bloat and other tuning issues
I've been running into, I'm looking for a good resource for improving my tuning
skills.
My sysadmin ran into the following book:
PostgreSQL 9.0 High Performance, by Gregory Smith, ISBN 184951030X
http://amzn.com/1849510
On 2/1/2011 9:08 AM, Dario Beraldi wrote:
Hello,
From the documentation of CLUSTER table
(http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I
understand that clustering can be achieved by re-creating the table like
this:
CREATE TABLE newtable AS
SELECT * FROM table ORDER BY columnlis
> As a result of my recent encounter with table bloat and other tuning
> issues I've been running into, I'm looking for a good resource for
> improving my tuning skills.
>
> My sysadmin ran into the following book:
>
> PostgreSQL 9.0 High Performance, by Gregory Smith, ISBN 184951030X
> http://amzn
On 02/01/11 08:55, Herouth Maoz wrote:
As a result of my recent encounter with table bloat and other tuning issues
I've been running into, I'm looking for a good resource for improving my tuning
skills.
My sysadmin ran into the following book:
PostgreSQL 9.0 High Performance, by Gregory Smith
On Mon, Jan 31, 2011 at 2:55 PM, bricklen wrote:
> We just had a slew of the following messages in our log. How concerned
> should I be at this point? I have no idea what triggered it.
>
> Version:
> PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc
> (GCC) 4.1.2 20080704 (Red Hat
Hi,
I am trying to compute a hash string with 172 bytes.
I have a openssl RSA private key stored in the postgresql db and a string
with some text.
I want to sign that string with the private key and the SHA1 algorithm and
encode the result in the base64 format.
I am writing it inside the pg da
On 2/1/2011 10:17 AM, Dario Beraldi wrote:
Quoting Andy Colson :
On 2/1/2011 9:08 AM, Dario Beraldi wrote:
Hello,
From the documentation of CLUSTER table
(http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I
understand that clustering can be achieved by re-creating the table like
thi
Nicos Panayides writes:
> On 01/31/2011 08:48 PM, Tom Lane wrote:
>> It seems likely that you're getting a different plan for the generic
>> case because that user id isn't representative of the overall average
>> for the column.
> I tried the prepared statement with both $1 and 1811 for user_id
Quoting Andy Colson :
On 2/1/2011 9:08 AM, Dario Beraldi wrote:
Hello,
From the documentation of CLUSTER table
(http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I
understand that clustering can be achieved by re-creating the table like
this:
CREATE TABLE newtable AS
SELECT * FROM t
Hi,
Thanks for the quick answer, Andy.
On Tue, 01 Feb 2011 16:19:17 +0100, Andy Colson
wrote:
So, if I understand correctly, you:
q = SELECT record_id FROM queue
WHERE project_id = my_project AND state = idle
LIMIT n OFFSET i FOR UPDATE
while not q.eof
update queu
On 2/1/2011 10:59 AM, Yngve Nysaeter Pettersen wrote:
Hi,
Thanks for the quick answer, Andy.
On Tue, 01 Feb 2011 16:19:17 +0100, Andy Colson
wrote:
So, if I understand correctly, you:
q = SELECT record_id FROM queue
WHERE project_id = my_project AND state = idle
LIMIT n OFFSET i FOR UPDATE
"Yngve Nysaeter Pettersen" writes:
> To avoid having the processes trample each other's queries (the first
> attempt was to select the first matching entries of the table, which
> caused one to block all other transactions), one of the steps I took was
> to select a set of idle rows at a ran
On Tue, Feb 1, 2011 at 11:14 AM, Jeff Ross wrote:
> I doubt you'll find a better book resource for recent versions of
> PostgreSQL. Both are well and clearly written, and cover a lot of ground
> in great detail.
>
> Greg Smith has helped me and countless others on this and the Performance
> list
On Tue, 01 Feb 2011 18:18:17 +0100, Tom Lane wrote:
"Yngve Nysaeter Pettersen" writes:
To avoid having the processes trample each other's queries (the first
attempt was to select the first matching entries of the table, which
caused one to block all other transactions), one of the steps I too
On Tue, 01 Feb 2011 18:11:23 +0100, Andy Colson
wrote:
On 2/1/2011 10:59 AM, Yngve Nysaeter Pettersen wrote:
Hi,
Thanks for the quick answer, Andy.
On Tue, 01 Feb 2011 16:19:17 +0100, Andy Colson
wrote:
So, if I understand correctly, you:
q = SELECT record_id FROM queue
WHERE project_
hero...@unicell.co.il (Herouth Maoz) writes:
> As a result of my recent encounter with table bloat and other tuning
> issues I've been running into, I'm looking for a good resource for
> improving my tuning skills.
>
> My sysadmin ran into the following book:
>
> PostgreSQL 9.0 High Performance, by
On 2/1/2011 12:10 PM, Yngve Nysaeter Pettersen wrote:
On Tue, 01 Feb 2011 18:11:23 +0100, Andy Colson
I think the random limit thing is a race condition itself. Whenever
you have multiple processes hitting the same rows you're going to run
into problems. Have you thought of using a sequence ins
On 02/01/2011 06:52 PM, Tom Lane wrote:
Nicos Panayides writes:
On 01/31/2011 08:48 PM, Tom Lane wrote:
It seems likely that you're getting a different plan for the generic
case because that user id isn't representative of the overall average
for the column.
I tried the
Thanks Andy,
On Tue, 01 Feb 2011 19:29:08 +0100, Andy Colson
wrote:
On 2/1/2011 12:10 PM, Yngve Nysaeter Pettersen wrote:
On Tue, 01 Feb 2011 18:11:23 +0100, Andy Colson
I think the random limit thing is a race condition itself. Whenever
you have multiple processes hitting the same rows yo
On 2/1/2011 12:51 PM, Yngve Nysaeter Pettersen wrote:
Thanks Andy,
On Tue, 01 Feb 2011 19:29:08 +0100, Andy Colson
wrote:
On 2/1/2011 12:10 PM, Yngve Nysaeter Pettersen wrote:
On Tue, 01 Feb 2011 18:11:23 +0100, Andy Colson
I think the random limit thing is a race condition itself. Whenev
Hmm...
May I ask how this look in details. If e.g. I do select * from myeshop offset
100 limit 20, I have 1000 rows which rows will be locked?
a) 0 to 120, or
b) all rows will be locked.?
Kind regards,
Radek
Tom Lane Tuesday 01 February 2011 18:18:17
> In 9.0, LIMIT/OFFSET processing is done
On Tue, 01 Feb 2011 20:04:31 +0100, Andy Colson
wrote:
On 2/1/2011 12:51 PM, Yngve Nysaeter Pettersen wrote:
So, AFAICT I am afraid it would not work in the general case for my
project :( .
However, it might be useful in somebody else's project :) .
No, I didn't think it would work for y
On Tue, Feb 1, 2011 at 8:56 AM, Herouth Maoz wrote:
> My sysadmin ran into the following book:
>
> PostgreSQL 9.0 High Performance, by Gregory Smith, ISBN 184951030X
> http://amzn.com/184951030X
highly recommended. Also take a look at the pg admin cookbook from packt.
--
Sent via pgsql-general
i have an older CentOS 4.8 server that is running 8.4.2 and wanted to
update it. it was installed via the older yum repo pre the reorgs...
# rpm -qa |grep postgres
postgresql-server-8.4.2-1PGDG.rhel4
postgresql-jdbc-8.3.605-1PGDG.rhel4
postgresql-libs-8.4.2-1PGDG.rhel4
postgresql-8.4.2-1PGDG.r
In regarding of log message, I get the following error message on my
application for the connection problem and there is NOT any error messages in
the PG log file. It is the same with DbVisualizer - not error messages in the
PG log file.
~~
I have added this as a TODO:
* Improve handling of plus signs in email address user
names, and perhaps improve URL parsing
* http://archives.postgresql.org/pgsql-hackers/2010-10/msg00772.php
-
Herouth Maoz wrote:
My sysadmin ran into the following book:
PostgreSQL 9.0 High Performance, by Gregory Smith, ISBN 184951030X
http://amzn.com/184951030X
That guy's a troublemaker, but I guess he writes OK.
There are three customer reviews at
http://www.amazon.com/PostgreSQL-High-Perform
On 02/01/11 12:07 PM, John R Pierce wrote:
warning: rpmts_HdrFromFdno: V3 DSA signature: NOKEY, key ID e8e345b8
Public key for postgresql-8.4.4-2PGDG.el4.i386.rpm is not installed
Retrieving GPG key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
GPG key at file:///etc/pki/rpm-gpg/RPM-GPG-KEY-P
On 1 February 2011 01:05, Tom Lane wrote:
> Thom Brown writes:
>> I've noticed that if I try to use generate_series to include the upper
>> boundary of int4, it never returns:
>
> I'll bet it's testing "currval > bound" without considering the
> possibility that incrementing currval caused an ove
On Tuesday, February 01, 2011 12:03:20 pm vwu98...@lycos.com wrote:
> When I try to access PG from PC command prompt with the psql command, I get
> the following error message:
>
> psql: server closed the connection unexpectedly
>This probably mean the server terminated abnormally before or w
On 1 Feb 2011, at 7:14, AI Rumman wrote:
> I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1.
That's quite a big step up. You skipped 8.2, 8.3 and 8.4 - all major releases.
My advise: Test very thoroughly for more differences in behaviour.
One thing to start looking at right away is whet
vwu98...@lycos.com writes:
> When I try to access PG from PC command prompt with the psql command, I get
> the following error message:
> psql: server closed the connection unexpectedly
>This probably mean the server terminated abnormally before or while
> processing the request.
> This ti
On Tue, Feb 1, 2011 at 5:36 PM, hlcborg wrote:
> encode(hmac(v_em_crt_conc, v_Private,'sha1'),'base64');
HMAC - key-dependant SHA1
> The Result:
>
> h6CpmrP1QCE/Mp3xn3utUEPtftg= This hash has 28 chars
>
> When I use OpenSSL in command line like this:
>
> ~$ echo "2011-01-31;2011-02-01T13:33
On Tue, 2011-02-01 at 12:14 +0600, AI Rumman wrote:
> I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1.
>
> I have a table "testtab"
> \d testtab
> id int,
> hours varchar
>
> When I execute the following:
> select sum(hours) from testtab
> I get cast error.
>
In 8.3, implicit casts were
Hi
I recently read some of your windows docs on the wiki.
They mentioned that some people experience problems with Postgresql and
firewalls and/or anti-virus programs on windows.
I know that a software firewall is usually not a problem. I have used a
couple of different software firewalls at diffe
On 1 Feb 2011, at 21:15, Greg Smith wrote:
> Herouth Maoz wrote:
>> My sysadmin ran into the following book:
>>
>> PostgreSQL 9.0 High Performance, by Gregory Smith, ISBN 184951030X
>> http://amzn.com/184951030X
>>
>
> That guy's a troublemaker, but I guess he writes OK.
It must be tough to
"Joshua D. Drake" writes:
> On Tue, 2011-02-01 at 12:14 +0600, AI Rumman wrote:
>> I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1.
>>
>> I have a table "testtab"
>> \d testtab
>> id int,
>> hours varchar
>>
>> When I execute the following:
>> select sum(hours) from testtab
>> I get cas
On 1 Feb 2011, at 21:26, Thom Brown wrote:
> On 1 February 2011 01:05, Tom Lane wrote:
>> Thom Brown writes:
>>> I've noticed that if I try to use generate_series to include the upper
>>> boundary of int4, it never returns:
>>
>> I'll bet it's testing "currval > bound" without considering the
>
David Boreham wrote:
> On 9/27/2010 4:53 PM, Tom Lane wrote:
> > The reason it tells you that data will be destroyed is that that could
> > very well happen.
>
> Re-parsing this, I think there was a mis-communication :
>
> I'm not at all suggesting that the doc should _not_ say that data will
Hello,
I have just done a multi-part migration and am having difficulty viewing
binary content stored in a "bytea" field; specifically I stored a PDF in the
existing setup and now receive a "File does not begin with '%PDF-'" message
when I go to pull it up from the new setup.
The specific m
On 2/1/2011 3:44 PM, David Johnston wrote:
Hello,
I have just done a multi-part migration and am having difficulty viewing
binary content stored in a “bytea” field; specifically I stored a PDF in
the existing setup and now receive a “File does not begin with ‘%PDF-‘”
message when I go to pull it
Andy, thank you for the quick response but I figured out the problem. I
recalled reading something regarding the bytea data type in 9.0
migration/release notes and decided to look into that. It turns out that
changing the default output format to "hex" from "escape" is the cause of
the issue. I
Thomas Kellerer wrote:
> Hi,
>
> I would like to suggest to enhance the documentation of the CREATE VIEW
> statement.
>
> I think the fact that a "SELECT *" is internally stored as the expanded
> column list (valid at the time when the view was created) should be
> documented together with the CR
On 1 February 2011 21:32, Alban Hertroys
wrote:
> On 1 Feb 2011, at 21:26, Thom Brown wrote:
>
>> On 1 February 2011 01:05, Tom Lane wrote:
>>> Thom Brown writes:
I've noticed that if I try to use generate_series to include the upper
boundary of int4, it never returns:
>>>
>>> I'll bet
Hi Marko,
OK,
> These two operations are not equivalent.
But...
Can I have this operation done in the Stored Procedure inside the Database?
Plain SHA1, which is signed with RSA signature. and in the end encoded to
base64?
I was looking in the pgcrypto functions, and I haven´t found any that
On Tue, 2011-02-01 at 12:25 -0800, John R Pierce wrote:
> k, I did a yum clean all and its working now.
>
> odd. before it was trying to install...
> postgresql-server i386 8.4.4-2PGDG.el4 pgdg84
>
> and now its installed...
> postgresql-server i386 8.4.6-1PGDG.rhel4
If random sampling is desirable would the following construct limit locking
only to the sampled rows?
SELECT id
FROM tasktable
WHERE id IN (SELECT random_id_sample())
FOR UPDATE
The "random_id_sample" would supply a configurable group of IDs off of
tasktable which the FOR UPDATE would then lock
I have an existing table with 15,492 rows and want to add additional rows
from a .csv file. If I use 'COPY from with delimiter
as ":" csv quote as "'" ' will this overwrite existing rows in the table or
append rows?
Rich
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
T
On Mon, Jan 31, 2011 at 7:52 PM, Scott Marlowe wrote:
> On Mon, Jan 31, 2011 at 5:54 PM, Aleksey Tsalolikhin
> wrote:
>> Situation: Disk usage on production server root filesystem is at 68%
>> utilization (80 GB used), on DR is at 51% (56 GB used). We use
>> SlonyII-1.2.x to keep the DR up to
On 1 February 2011 03:52, Scott Marlowe wrote:
> You can reclaim that space by doing a cluster or vacuum full on the
> subject table.
Yes, but this is a fairly bad idea, particularly prior to PG 9.0 . 9.0
has a new vacuum full implementation that makes it not so bad - it
just rewrites the entire
On Tue, Feb 1, 2011 at 8:13 PM, Peter Geoghegan
wrote:
> On 1 February 2011 03:52, Scott Marlowe wrote:
>> You can reclaim that space by doing a cluster or vacuum full on the
>> subject table.
>
> Yes, but this is a fairly bad idea, particularly prior to PG 9.0 . 9.0
> has a new vacuum full imple
More info here.
http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Feb 1, 2011 at 7:29 PM, Aleksey Tsalolikhin
wrote:
> Bucardo's "check_postgres.pl --action bloat" complains about one table,
> 1 GB wasted. So the other tables must be OK.
Pretty sure that unless you give it more args, the default for bloat
check is to list the first bloated table and st
Hi,
Does postgresql run on windows 2000?
I've tried to install postgresql-9.0.3-1 on windows 2000 but I couldn't connect
to the default local server.
If not, which version can I use for windows 2000?
Thanks,
Dear all,
Few days ago I attached a tablespace to one my databases. Say
/hdd-1/database_name is the path for the tablespace. When I attach it
has a free space of 100 GB.
Now my database size grows to 90 GB. I want to prepare for my upcoming
problem of space.
How can I handle this. Would any
On 2 February 2011 03:28, Rich Shepard wrote:
> I have an existing table with 15,492 rows and want to add additional rows
> from a .csv file. If I use 'COPY from with delimiter
> as ":" csv quote as "'" ' will this overwrite existing rows in the table or
> append rows?
No it wont overwrite, it
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
>> Bucardo's "check_postgres.pl --action bloat" complains about one table,
>> 1 GB wasted. So the other tables must be OK.
> Pretty sure that unless you give it more args, the default for bloat
> check is to list the first bloated table and sto
63 matches
Mail list logo
