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
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
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
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
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
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
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
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
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
"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
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
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
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
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
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
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.
"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
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
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
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
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
---
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
"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)
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
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
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
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
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
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
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:
> 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
--
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
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
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
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
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
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
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
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
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
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
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 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, 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 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
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
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
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
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
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
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 to0x1 are not supportedCONTEXT: COPY bcp_mdc_products, line 120, column description: "Lladró "Ducks in
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
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
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
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
> 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
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
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
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
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
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
63 matches
Mail list logo
