Hello,
I have a machine that uses pgsql version 8.0.1 I don't think the version
is relevant because I had 7.4.1 before and I had the same problem. I have
a PHP script that runs regularily and does this:
select a bunch of lines from a mssql database
insert into postgres the values taken
if insert
Costin Manda wrote:
The thing is the after I updated to 8.0.1 and also (separate ocasion)
after I recreated the database one day, the script runs instantly with
thousands and hundreds of lines inserted and updated per second. However,
after a while the whole process slows down significantly,
A pgInstaller build of PostgreSQL 8.0.2 beta 1 for Windows is available
for testing at http://www.postgresql.org/ftp/binary/v8.0.2beta1/win32/
In addition to the PostgreSQL changes, this version also includes the
following updates:
psqlODBC= 08.00.0101
PgOleDb = 1.0.0.19
Jdbc
Please CC the list as well as replying directly - it means more people
can help.
Costin Manda wrote:
Some more info please:
1. This is this one INSERT statement per transaction, yes? If that
fails, you do an UPDATE
correct.
2. Are there any foreign-keys the insert will be checking?
3. What
Hi,
is there a way to get the name of the two tables involved
in a referential integrity violation via jdbc?
Up to version 7.3 it was possible to get the names by
parsing the error message of the SQLException.
However, some information has moved to the DETAIL line.
Can this line be retrieved
Some more info please:
1. This is this one INSERT statement per transaction, yes? If that
fails, you do an UPDATE
correct.
2. Are there any foreign-keys the insert will be checking?
3. What indexes are there on the main table/foreign-key-related tables?
this is the table, the only
I think I found the problem. I was comparing wrongly some values and
based on that, every time the script was run (that means once every 5
minutes) my script deleted two tables and populated them with about 70
thousand records.
I still don't know why that affected the speed of the database
What is the easiest way to reset the postgres user password?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On Apr 6, 2005, at 8:22 AM, D A GERM wrote:
What is the easiest way to reset the postgres user password?
Using the ALTER USER command. If you don't know the password,
temporarily modify the pg_hba.conf file to allow local connections
without a password ('trust'). You can then login without the
Costin Manda wrote:
I think I found the problem. I was comparing wrongly some values and
based on that, every time the script was run (that means once every 5
minutes) my script deleted two tables and populated them with about 70
thousand records.
I still don't know why that affected the speed
We're testing moving our data to UNICODE from LATIN1, but when I try to import my data, I get the following error:DBD::Pg::st execute failed: ERROR: Unicode characters greater than or equal to0x1 are not supportedCONTEXT: COPY bcp_mdc_products, line 120, column description: "Lladró "Ducks ina
On Wed, 06 Apr 2005 14:07:36 +0100
Richard Huxton dev@archonet.com wrote:
Costin Manda wrote:
I think I found the problem. I was comparing wrongly some values and
based on that, every time the script was run (that means once every 5
minutes) my script deleted two tables and populated
We're testing moving our data to UNICODE from LATIN1, but when I try to
import my data, I get the following error:
DBD::Pg::st execute failed: ERROR: Unicode characters greater than or
equal to
0x1 are not supported
CONTEXT: COPY bcp_mdc_products, line 120, column description: Lladró
Hi,
We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this date we
have many troubles with PostgreSQL and most of them seems to be memory
troubles.
As far as we can see, kernel kills the postmaster process when it begins to
use swap. You can see the output from dmesg at the
Hi,
Some newbie questions that the archive search did not help me
with.
1. Will PGSQL work on CentOS? Should I download RH Enterprise
3.0 binaries? Or RedHat 9.0 binaries?
2. Is there a startup kit with 6-7 easy install steps for a
TOTAL NEWBIE? MySQL seems to be pretty easy to install (sorry
Costin Manda wrote:
On Wed, 06 Apr 2005 14:07:36 +0100
Richard Huxton dev@archonet.com wrote:
Costin Manda wrote:
I think I found the problem. I was comparing wrongly some values and
based on that, every time the script was run (that means once every 5
minutes) my script deleted two tables and
Hervé Piedvache wrote:
Hi,
We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this date we
have many troubles with PostgreSQL and most of them seems to be memory
troubles.
As far as we can see, kernel kills the postmaster process when it begins to
use swap. You can see the output
On Wed, Apr 06, 2005 at 04:35:43PM +0200, Hervé Piedvache wrote:
Hi,
We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this date we
have many troubles with PostgreSQL and most of them seems to be memory
troubles.
As far as we can see, kernel kills the postmaster process
On Wed, 06 Apr 2005 15:54:29 +0100
Richard Huxton dev@archonet.com wrote:
I mean from 5 to 5 minutes
DROP TABLE
CREATE TABLE
INSERT 7 rows in table
I thought you were trying an inserting / updating if it failed? You
shouldn't have any duplicates if the table was already
On Apr 6, 2005, at 10:50 AM, Erick Papadakis wrote:
Hi,
Some newbie questions that the archive search did not help me
with.
1. Will PGSQL work on CentOS? Should I download RH Enterprise
3.0 binaries? Or RedHat 9.0 binaries?
2. Is there a startup kit with 6-7 easy install steps for a
TOTAL NEWBIE?
On Wed, 2005-04-06 at 09:50, Erick Papadakis wrote:
Hi,
Some newbie questions that the archive search did not help me
with.
1. Will PGSQL work on CentOS? Should I download RH Enterprise
3.0 binaries? Or RedHat 9.0 binaries?
2. Is there a startup kit with 6-7 easy install steps for a
On Wednesday 06 April 2005 17:03, Richard Huxton wrote:
Hervé Piedvache wrote:
Hi,
We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this
date we have many troubles with PostgreSQL and most of them seems to be
memory troubles.
As far as we can see, kernel kills the
Costin Manda wrote:
On Wed, 06 Apr 2005 15:54:29 +0100
Richard Huxton dev@archonet.com wrote:
I mean from 5 to 5 minutes
DROP TABLE
CREATE TABLE
INSERT 7 rows in table
I thought you were trying an inserting / updating if it failed? You
shouldn't have any duplicates if the table was already
Hervé Piedvache wrote:
On Wednesday 06 April 2005 17:03, Richard Huxton wrote:
Hervé Piedvache wrote:
Hi,
We have switched to kernel 2.6.11.6 from kernel 2.4.26 ... since this
date we have many troubles with PostgreSQL and most of them seems to be
memory troubles.
As far as we can see, kernel
Subject says it all...I've got to do this soon, and since I would
like PG to take over this company like a VIRUS, I'd like this rollout
to go *very* well.
Any comments, caveats, encomiums, exhortations, anecdotes, war stories,
gentle assurances and pointers of all sorts most welcome.
- Ross
Patrick Hatcher [EMAIL PROTECTED] writes:
We're testing moving our data to UNICODE from LATIN1, but when I try to
import my data, I get the following error:
DBD::Pg::st execute failed: ERROR: Unicode characters greater than or
equal to
0x1 are not supported
CONTEXT: COPY
Hello,
I have a database with encoding latin2, ctype hu_HU, posgresql 8.0.1.
Keyword split is a plperl function:
create or replace function keywords_split(text) returns text as $$
my $text = lc $_[0];
return $text;
$$
language plperl;
My problem is:
$ psql teszt;
Welcome to psql
Martijn van Oosterhout kleptog@svana.org writes:
What I don't understand is that with true strict overcommit, the kernel
should never need to kill your process since there is always in
principle enough room.
Indeed. Are you *sure* you have overcommit turned off? That should
disable the OOM
Richard Huxton dev@archonet.com writes:
You might want to try vm.overcommit_memory=1. You don't appear to be the
only one suffering from an over-zealous oom-killer.
http://www.ussg.iu.edu/hypermail/linux/kernel/0501.2/1295.html
Hmm, in particular Andrea Arcangeli implies here
Richard Huxton dev@archonet.com writes:
Costin Manda wrote:
I thought the problem lied with step 4, but now I see that step 3 was
the culprit and that , indeed, I did not do drop table, create table but
delete from and inserts. I think that recreating these two tables should
solve the
On Wed, 2005-04-06 at 18:18 +0300, Costin Manda wrote:
The script does the following thing:
1. read the count of rows in two tables from the mssql database
2. read the count of rows of the 'mirror' tables in postgres
these are tables that get updated rarely and have a maximum of 10
Mage wrote:
teszt=# select keywords_split('AúéöÖÉÁ');
keywords_split
aúéöÖÉÁ
(1 row)
What happens if you add
use locale;
in your perl function before calling lc ?
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
Daniel Verite wrote:
Mage wrote:
teszt=# select keywords_split('A');
keywords_split
a
(1 row)
What happens if you add
use locale;
in your perl function before calling lc ?
with use locale;:
select keywords_split('A');
ERROR: creation of Perl function
Mage wrote:
with use locale;:
select keywords_split('AúéöÖÉÁ');
ERROR: creation of Perl function failed: 'require' trapped by operation
mask at (eval 6) line 2.
Ah. So maybe it would work with plperlu instead of plperl.
--
Daniel
PostgreSQL-powered mail user agent and storage:
Thank you. I'll take a look at our data export function.
Tom Lane wrote:
Patrick Hatcher [EMAIL PROTECTED] writes:
We're testing moving our data to UNICODE from LATIN1, but when I try to
import my data, I get the following error:
DBD::Pg::st execute failed: ERROR: Unicode characters
On Wed, Apr 06, 2005 at 12:52:55PM -0400, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
What I don't understand is that with true strict overcommit, the kernel
should never need to kill your process since there is always in
principle enough room.
Indeed. Are you
Helloo !
We have a database that contains data that we need to Parse.
Ideally I would like write a C-function, ParseData, and run
select ParseData([data_column]) from datatable where date='2005-05-05';
and have it return 5 columns with the parsed data. Each row in Data_column
will potentially
Daniel Verite wrote:
Mage wrote:
with use locale;:
select keywords_split('A');
ERROR: creation of Perl function failed: 'require' trapped by operation
mask at (eval 6) line 2.
Ah. So maybe it would work with plperlu instead of plperl.
I did, and it didn't help.
Mage
If speed (add/get) is the only concern, image files could be big (~10M),
and database only serves as storage. In the postgresql 8, which type
(bytea vs large object) is the preferred one? Is it true, in general,
that bytea inserts is slower?
Thanks.
johnl
---(end of
It's serious.
teszt=# select lower('A');
lower
-
a
(1 row)
teszt=# create or replace function keywords_split(text) returns text as $$
teszt$# return '';
teszt$# $$
teszt-# language plperlu;
CREATE FUNCTION
teszt=# select keywords_split('');
keywords_split
(1 row)
Otto Blomqvist [EMAIL PROTECTED] writes:
secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test));
ERROR: more than one row returned by a subquery used as an expression
In 8.0 I think it'd work to do
select (x).f1, (x).f2, (x).f3 from
(select testpassbyval(1, number1) as x
Martijn van Oosterhout kleptog@svana.org writes:
What I don't understand is the problem with overcommitting.
The problem with Linux overcommit is that when the kernel does run out
of memory, the process it chooses to kill isn't necessarily one that was
using an unreasonable amount of memory.
Mage [EMAIL PROTECTED] writes:
It's serious.
That's a Perl bug not a Postgres bug: libperl should not change the
process's locale settings, or at least if it does it should restore
the prior settings before returning. It doesn't.
regards, tom lane
According to the 7.4
doc section on monitoring database activity, one should be able to see the
current activity happening in a given postgres process. It mentions that on
Solaris (which we are running on) you need to use /usr/ucb/ps, and it also says
" your
original invocation of the
Hi all,
I ran into this problem and want to share and have a confirmation.
I tried to use COPY function to load bulk data. I craft myself a
UNICODE file from a MSSQL db. I can't load it into the postgresql. I
always get the error: CONTEXT: COPY vd, line 1, column vdnum: "1"
The problem is
On Wed, Apr 06, 2005 at 04:37:59PM -0400, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
What I don't understand is the problem with overcommitting.
The problem with Linux overcommit is that when the kernel does run out
of memory, the process it chooses to kill isn't
David Parker [EMAIL PROTECTED] writes:
According to the 7.4 doc section on monitoring database activity, one
should be able to see the current activity happening in a given postgres
process. It mentions that on Solaris (which we are running on) you need
to use /usr/ucb/ps, and it also says
David Gagnon [EMAIL PROTECTED] writes:
So am I right ? Is Postgresql using UTF-8 and don`t really understand
UNICODE file (UCS-2)? Is there a way I can make the COPY command with a
UNICODE UCS-2 encoding
Postgres only supports UTF-8, not any other encoding of Unicode. Sorry.
Martijn van Oosterhout kleptog@svana.org writes:
Ok, I think the point I'm trying to make is that with strict
autocommit in its current state isn't really that strict and just
causes the problem to happen elsewhere.
Right, but that is surely just a kernel bug, and one that's not been
around
Tom Lane wrote:
Mage [EMAIL PROTECTED] writes:
It's serious.
That's a Perl bug not a Postgres bug: libperl should not change the
process's locale settings, or at least if it does it should restore
the prior settings before returning. It doesn't.
I checked with show all,
On Wed, 2005-04-06 at 17:26, Mage wrote:
Tom Lane wrote:
Mage [EMAIL PROTECTED] writes:
It's serious.
That's a Perl bug not a Postgres bug: libperl should not change the
process's locale settings, or at least if it does it should restore
the prior settings before returning.
Am Mittwoch, den 06.04.2005, 18:12 -0400 schrieb David Gagnon:
Hi all,
I ran into this problem and want to share and have a confirmation.
I tried to use COPY function to load bulk data. I craft myself a
UNICODE file from a MSSQL db. I can't load it into the postgresql. I
always get
OK, thanks. We're using pg_ctl to start it at the moment, but we can
obviously change that.
- DAP
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 06, 2005 6:18 PM
To: David Parker
Cc: postgres general
Subject: Re: [GENERAL] monitoring database
David Parker [EMAIL PROTECTED] writes:
The best bet is to make sure that your postmaster start script
invokes the postmaster as
postmaster
no more. No path (set PATH beforehand instead). No
command-line switches (whatever you might want there can be
put into postgresql.conf
On Wednesday 06 April 2005 18:57, Tom Lane wrote:
Richard Huxton dev@archonet.com writes:
You might want to try vm.overcommit_memory=1. You don't appear to be the
only one suffering from an over-zealous oom-killer.
http://www.ussg.iu.edu/hypermail/linux/kernel/0501.2/1295.html
Hmm, in
On Wednesday 06 April 2005 18:52, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
What I don't understand is that with true strict overcommit, the kernel
should never need to kill your process since there is always in
principle enough room.
Indeed. Are you *sure* you
Hi,
I am trying to write a function that will allow a postgres to convert a
text string into a hex value but there doesnt seem a function to do it,
only one i could find is to_hex(number) example of what i have done is
update table1
Set field2 = encode((select md5('field1')),'hex')
where
Hi all,
I'm trying to use a limit clause with delete, but it doesn't work at the
moment (are there plans to add this - I could try to do up a patch ?).
eg.
delete from table where x='1' limit 1000;
(so truncate is out - I have a 'where' clause).
Is there another way to approach this?
I'm trying
Chris Smith wrote:
I'm trying to use a limit clause with delete, but it doesn't work at the
moment
It isn't in the SQL standard, and it would have undefined behavior: the
sort order of a result set without ORDER BY is unspecified, so you would
have no way to predict which rows DELETE would
I don't care about the order in my particular case, just that I have to
clear the table.
I'll try the subquery and see how I go :)
Thanks!
Neil Conway wrote:
Chris Smith wrote:
I'm trying to use a limit clause with delete, but it doesn't work at
the moment
It isn't in the SQL standard, and it
On Apr 6, 2005 10:22 PM, Tom Lane [EMAIL PROTECTED] wrote:
David Gagnon [EMAIL PROTECTED] writes:
So am I right ? Is Postgresql using UTF-8 and don`t really understand
UNICODE file (UCS-2)? Is there a way I can make the COPY command with a
UNICODE UCS-2 encoding
Postgres only supports
On Thu, Apr 07, 2005 at 12:02:24PM +1000, Neil Conway wrote:
Chris Smith wrote:
I'm trying to use a limit clause with delete, but it doesn't work at the
moment
It isn't in the SQL standard, and it would have undefined behavior: the
sort order of a result set without ORDER BY is
For the archives...
Using 7.4 so IN() is a little slower, so I rewrote it slightly to be
DELETE FROM table WHERE EXISTS (select x from table LIMIT ...);
Works very nicely :)
Thanks again.
Neil Conway wrote:
Chris Smith wrote:
I'm trying to use a limit clause with delete, but it doesn't work
63 matches
Mail list logo
