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, erachi
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 inde
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 via
> 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 r
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 (e
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 pa
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 in
On Wed, 06 Apr 2005 14:07:36 +0100
Richard Huxton 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 them with ab
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ó
"Du
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 bott
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 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 them wit
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 pro
On Wed, 06 Apr 2005 15:54:29 +0100
Richard Huxton 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 empty. Or h
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? M
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 step
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, kerne
Costin Manda wrote:
On Wed, 06 Apr 2005 15:54:29 +0100
Richard Huxton 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 empty. Or have I
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 kills
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 bcp
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 8
Martijn van Oosterhout 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 killer altogethe
Richard Huxton 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
http://www.ussg.iu.edu/hyperm
Richard Huxton 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 problem, isn't
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
> r
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úéöÖÉÁ');
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: ht
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 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 hav
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 c
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 di
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 broadc
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
"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)
Martijn van Oosterhout 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. The earlier version
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 po
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 probl
On Wed, Apr 06, 2005 at 04:37:59PM -0400, Tom Lane wrote:
> Martijn van Oosterhout 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 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 s
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 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 very long. Pre
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 wi
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
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
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 ac
"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 postgres
On Wednesday 06 April 2005 18:57, Tom Lane wrote:
> Richard Huxton 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 particu
On Wednesday 06 April 2005 18:52, Tom Lane wrote:
> Martijn van Oosterhout 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 overcom
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 n
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 t
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 remov
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 w
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 onl
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 uns
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 at
63 matches
Mail list logo
