Re: [GENERAL] Numbers
On Sun, Jan 22, 2006 at 02:25:33PM -0500, Tom Lane wrote:
I seem to recall that someone had come up with a datatype that would
store numbers with units attached, which seems like what you want here.
Check the PG list archives, and/or poke around on pgfoundry and gborg.
Hmm, I only just noticed this thread, but it might work for him. OTOH,
if feet are the only units he's interested in then it's slight
overkill. Also, it doesn't deal with alternate spellings (1 foot,2
feet). If you decided you would only use abbreviations that wouldn't
matter ofcourse.
http://svana.org/kleptog/pgsql/taggedtypes.html
Quick example:
test=# create table physics_units ( name text, abbrev text );
CREATE TABLE
test=# insert into physics_units values ('feet','ft');
INSERT 2205045 1
test=# insert into physics_units values ('metres','m');
INSERT 2205046 1
test=# select create_tagged_type( 'physics_type', 'float', 'physics_units' );
NOTICE: type physics_type is not yet defined
DETAIL: Creating a shell type definition.
NOTICE: argument type physics_type is only a shell
create_tagged_type
(1 row)
test=# select
create_tagged_operator('physics_type','+','physics_type','physics_type');
NOTICE: +(physics_type,physics_type) = physics_type maps to +(double
precision,double precision) = double precision
create_tagged_operator
(1 row)
test=# select create_tagged_operator('physics_type','*','float','physics_type');
NOTICE: *(physics_type,double precision) = physics_type maps to *(double
precision,double precision) = double precision
create_tagged_operator
(1 row)
test=# select '10 feet'::physics_type + '22 feet'::physics_type;
?column?
--
32 feet
(1 row)
test=# select '10 feet'::physics_type * 5;
?column?
--
50 feet
(1 row)
test=# select '10 feet'::physics_type + '3 metres'::physics_type;
ERROR: Using tagged operator +(physics_type,physics_type) with incompatable
tags (feet,metres)
Have a nice day,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
signature.asc
Description: Digital signature
Re: [GENERAL] logging connections
Title: RE: [GENERAL] logging connections i did the above, however still no looging is being done. for eg if i have a c++ program where i establish a connection to postgres and does some activity. the postgres log should log the above connection to postgres when i run the program. similarly say if i do psql database_name this is also another connection, and shd get logged. however by the changes u suggested, i am not able to do so. thanks, reagrds surabhi -Original Message- From: [EMAIL PROTECTED] on behalf of Nikola Ivanov Sent: Fri 1/20/2006 7:41 PM To: surabhi.ahuja Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] logging connections *** Your mail has been scanned by iiitb VirusWall. ***-*** You need to edit your postgresql.conf file and in the Error reporting and logging section set log_connections=true, log_disconnections=true, and log_hostname=true On 1/20/06, surabhi.ahuja [EMAIL PROTECTED] wrote: i want to know, how i can log connections to postgres. the sample log file is: LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-01-17 18:18:24 CST LOG: checkpoint record is at 0/B035D0 LOG: redo record is at 0/B035D0; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 1267; next OID: 17728 LOG: database system is ready LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: received fast shutdown request LOG: aborting any active transactions FATAL: terminating connection due to administrator command LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-01-20 11:00:00 CST LOG: checkpoint record is at 0/3C339CB4 LOG: redo record is at 0/3C339CB4; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 2283381; next OID: 1159413 LOG: database system is ready LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection however, i am still not able to know how to log into the above file, who has connected etc if i can log something like LOG: connection received: host=client1 port=3775 and also if i shut down postmaster, using pg_ctl stop, no logging takes place to the above log file. How to enable the above, thanks, regards Surabhi -Original Message- From: [EMAIL PROTECTED] on behalf of Tom Lane Sent: Fri 1/20/2006 3:13 AM To: Nik Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Connections not closing *** Your mail has been scanned by iiitb VirusWall. ***-*** Nik [EMAIL PROTECTED] writes: Ok, I simplified the problem. I tried just running psql from the command line, and I noticed that it opens two connection on two different ports, and it closes only one. For example I do the following in the command prompt: C:\ psql -h host_name -p 5432 -d db_name -U user_name Password: 2006-01-19 09:50:29 [unknown] LOG: connection received: host=client1 port=3775 2006-01-19 09:50:31 [unknown] LOG: connection received: host=client1 port=3778 2006-01-19 09:50:31 test LOG: connection authorized: user=user_name database=db_name It tries to connect, gets told it needs a password (the log verbosity level is not high enough to record the rejection), asks you for the password, and connects again. I don't see anything funny here. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] invalid memory alloc request size
Hi, my cron job which is dumping the databse fails this night. I got: pg_dump: ERROR: invalid memory alloc request size 18446744073709551614 pg_dump: SQL command to dump the contents of table spieletipps failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551614 pg_dump: The command was: COPY public.spieletipps (tr_kurzname, mg_name, sp_id, stip_heimtore, stip_gasttore) TO stdout; I am running postgresql-server-8.0.3-1.2 on SuSE Linux 9.3 (x86-64) I had this a few days before and decided to use a recent backup. It works fine for two days only. Maybe my harddisk is broken? Maybe 64-bit is broken? i have no clue and no idea what do to. i ve searched the archives and found a recent thread on HACKERS but sorry guys: i dont know how to produce a backtrace. select count(*) from spieletipps; count -- 11612957 (1 Zeile) works fine. When i do something like this: $ select * from spieletipps where sp_id 1000; Server beendete die Verbindung unerwartet Das heißt wahrscheinlich, daß der Server abnormal beendete bevor oder während die Anweisung bearbeitet wurde. Die Verbindung zum Server wurde verloren. Versuche Reset: Fehlgeschlagen. (it means: server closed the connection unexpectedly. ... Try to reset connection failed.) Please help me! kind regards, janning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] logging connections
Title: RE: [GENERAL] logging connections please clarify the following: log_destination (string) PostgreSQL supports several methods for logging server messages, including stderr and syslog. On Windows, eventlog is also supported. Set this option to a list of desired log destinations separated by commas. The default is to log to stderr only. This option can only be set at server start or in the postgresql.conf configuration file. can u please tell what do stderr and syslog mean? thanks, regards Surabhi Ahuja -Original Message- From: [EMAIL PROTECTED] on behalf of surabhi.ahuja Sent: Mon 1/23/2006 2:04 PM To: Nikola Ivanov Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] logging connections *** Your mail has been scanned by iiitb VirusWall. ***-*** i did the above, however still no looging is being done. for eg if i have a c++ program where i establish a connection to postgres and does some activity. the postgres log should log the above connection to postgres when i run the program. similarly say if i do psql database_name this is also another connection, and shd get logged. however by the changes u suggested, i am not able to do so. thanks, reagrds surabhi -Original Message- From: [EMAIL PROTECTED] on behalf of Nikola Ivanov Sent: Fri 1/20/2006 7:41 PM To: surabhi.ahuja Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] logging connections *** Your mail has been scanned by iiitb VirusWall. ***-*** You need to edit your postgresql.conf file and in the Error reporting and logging section set log_connections=true, log_disconnections=true, and log_hostname=true On 1/20/06, surabhi.ahuja [EMAIL PROTECTED] wrote: i want to know, how i can log connections to postgres. the sample log file is: LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-01-17 18:18:24 CST LOG: checkpoint record is at 0/B035D0 LOG: redo record is at 0/B035D0; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 1267; next OID: 17728 LOG: database system is ready LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: received fast shutdown request LOG: aborting any active transactions FATAL: terminating connection due to administrator command LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-01-20 11:00:00 CST LOG: checkpoint record is at 0/3C339CB4 LOG: redo record is at 0/3C339CB4; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 2283381; next OID: 1159413 LOG: database system is ready LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection however, i am still not able to know how to log into the above file, who has connected etc if i can log something like LOG: connection received: host=client1 port=3775 and also if i shut down postmaster, using pg_ctl stop, no logging takes place to the above log file. How to enable the above, thanks, regards Surabhi -Original Message- From: [EMAIL PROTECTED] on behalf of Tom Lane Sent: Fri 1/20/2006 3:13 AM To: Nik Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Connections not closing *** Your mail has been scanned by iiitb VirusWall. ***-*** Nik [EMAIL PROTECTED] writes: Ok, I simplified the problem. I tried just running psql from the command line, and I noticed that it opens two connection on two different ports, and it closes only one. For example I do the following in the command prompt: C:\ psql -h host_name -p 5432 -d db_name -U user_name Password: 2006-01-19 09:50:29 [unknown] LOG: connection received: host=client1 port=3775 2006-01-19 09:50:31 [unknown] LOG: connection received: host=client1 port=3778 2006-01-19 09:50:31 test LOG: connection authorized: user=user_name database=db_name It tries to connect, gets told it needs a password (the log verbosity level is not high enough to record the rejection), asks you for the password, and connects again. I don't see anything funny here. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [HACKERS] Need help in installing postgresql 8.1.2 on Windows
Hopefully your problem is solved by now; but if not, here's the link: do read the README expanded at the end of the file-list: http://www.postgresql.org/ftp/binary/v8.1.2/win32/ I could give you a long lecture on how to look for the things on your own a little bit before pestering these mailing lists; but I think I should spare you this one time. Good luck with open-source. Gurjeet. On 18/01/06, Sarvjot Kaur [EMAIL PROTECTED] wrote: Sir I am trying to install Globus Toolkit4 on Windows machine. Postgresql8.1.2 is required software for installing GT4. But i cant get installation steps from anywhere.. Please help me and do reply Thanks Sarvjot Yahoo! Photos – Showcase holiday pictures in hardcover Photo Books. You design it and we'll bind it! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] What is made a mistake with SP?
Hi, I need create SP that returns cursos so that I can work with them. I have many SQLs used for search records in database, then I will make SP to return the results. The example that I'm trying is: CREATE OR REPLACE FUNCTION fun_compras_calculado() RETURNS SETOF tipo_compras_calculado AS ' DECLARE linha_comprasRECORD; linha_calculada tipo_compras_calculado; BEGIN linha_calculada.acumulado := 0; FOR linha_compras IN SELECT id, qtd, produto, unitario FROM compras ORDER BY id LOOP linha_calculada.id := linha_compras.id; linha_calculada.produto := linha_compras.produto; linha_calculada.qtd := linha_compras.qtd; linha_calculada.unitario := linha_compras.unitario; linha_calculada.valor := linha_compras.qtd * linha_compras.unitario; linha_calculada.acumulado := linha_calculada.acumulado + linha_calculada.valor; RETURN NEXT linha_calculada; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] DBMirror.pl performance change
I discovered a problem in DBMirror.pl, performance wise.
pending.c stores data in a way
very similar to the PgSQL input \ escaped format.
When the field is of type bytea, and the source of data is binary, then
this produces 2 additional backslashes for every unprintable
char.
The performance in function extractData in DBMirror.pl, really suffers
from this condition, since it breaks data in chunks of \ delimited
strings.
Informally speaking, performance tends to be O(n) where n is the size
of the data.
This can be remedied if we break data in chunks of ' rather than \.
' happens much more infrequently in common binary files (bz2, tiff, jpg,
pdf etc..), and if we notice that odd number of contained \, signals an
intermidiate ', whereas even number of \ signals the final ',
then we can make this routine run much faster.
I attach the new extractData function.
Now replicating a 400 k tiff takes 3 seconds instead of 12 minutes
it used to do.
I am wondering about the state of
http://www.whitebeam.org/library/guide/TechNotes/replicate.rhtm
Please feel free for any comments.
Pete could you test this new DBMirror.pl, to see how it behaves
in comparison with your C++ solution?
--
-Achilleus
sub extractData($$) {
my $pendingResult = $_[0];
my $currentTuple = $_[1];
my $fnumber;
my %valuesHash;
$fnumber = 4;
my $dataField = $pendingResult-getvalue($currentTuple,$fnumber);
my $numofbs;
while(length($dataField)0) {
# Extract the field name that is surronded by double quotes
$dataField =~ m/(\.*?\)/s;
my $fieldName = $1;
$dataField = substr $dataField ,length($fieldName);
$fieldName =~ s/\//g; #Remove the surronding signs.
if($dataField =~ m/(^= )/s) {
#Matched null
$dataField = substr $dataField , length($1);
$valuesHash{$fieldName}=undef;
}
elsif ($dataField =~ m/(^=\')/s) {
#Has data.
my $value;
$dataField = substr $dataField ,2; #Skip the ='
LOOP: { #This is to allow us to use last from a do loop.
#Recommended in perlsyn manpage.
do {
my $matchString;
my $matchString2;
#Find the substring ending with the first ' or first \
$dataField =~ m/(.*?[\'])?/s;
$matchString = $1;
$numofbs = ($matchString =~ tr/\\//) % 2;
if ($numofbs == 1) { #// odd number of \, i.e. intermediate '
$matchString2 = substr $matchString,0, length($matchString)-2;
$matchString2 =~ s//\\/g;
$value .= ($matchString2 . \');
$dataField = substr $dataField,length($matchString);
}
else { #// even number of \, i.e. found end of data
$matchString2 = substr $matchString,0, length($matchString)-1;
$matchString2 =~ s//\\/g;
$value .= $matchString2;
$dataField = substr $dataField,length($matchString)+1;
last;
}
} until(length($dataField)==0);
}
$valuesHash{$fieldName} = $value;
}#else if
else {
logErrorMessage Error in PendingData Sequence Id .
$pendingResult-getvalue($currentTuple,0);
die;
}
} #while
return %valuesHash;
}
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Postgresql/DBA/Sysadmin Consultant in Düsseldorf, Germany
Hi, we are running a very popular german website[*] which has grown over the years since 1995. We manage between 10 and 20 millions pageviews a month. We are a small company and myself is responsible for programming, DBA, system administration and hardware. I am a self-educated person since the beginning of the internet. In last couple of months we encountered a lot of problems with our postgresql installation which we couldn't handle appropiate. Most of them are hardware related. There are lot of reasons why some things went bad. We want to start over before things are getting worse. I would like to talk to some professional Postgresql DBA personally (preferred in german language). Of course we will pay for it. I don't want to talk to sells personal, i want to talk to someone who really knows and has lots of expierence. Some topics: - which hardware to use - where to place our hardware (data center) - backup/failover strategies - performance / postgresql.conf - monitoring performance and system health I had a meeting with sun consultants already and i will have another one. That will be fine. But i would like to talk to some who is independent from company selling things i could need. If there is someone nearby who might help, please answer via personal E-Mail ([EMAIL PROTECTED]). I guess we could meet very for a few hours and we will see if further consultancy is needed. kind regards, janning [*] which i not named to get not listed in google.de, but it's called w w w . k i c k t i p p . d e ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DBMirror.pl performance change
The Whitebeam implementation of DBMirror.pl :
http://www.whitebeam.org/library/guide/TechNotes/replicate.rhtm
is a complete re-write in 'C' which avoids a lot of the text processing, and what text processing is required is done using a state machine rather
than repeated regular expressions. Before I wrote the 'C' implementation I did look at optimising the Perl version. One of my big concerns was the
time taking escaping and re-escaping the strings. I can't remember the details now but as far as I can remember a lot of that is unnecessary. There
seemed to be an unescape of the data then a re-escape to the target database. In practice the data was in the correct format.
We make quite heavy use of both BYTEA and large varchar fields in our database. I did some load testing at the time and found the new version could
replicate 10s of file objects per second - where the Perl version took 10 minutes to replicate a 120K BYTEA field (both on a slowish machine, but the
Perl version wasn't much better on a fast machine *and* took 97% CPU).
I also took the opportunity to make the 'C' version much more tolerant to lost DB connections without having to restart and added a few other tweaks
to make it more flexible.
It's released under the BSD license now as well
Pete
--
http://www.whitebeam.org
http://www.yellowhawk.co.uk
Achilleus Mantzios wrote:
I discovered a problem in DBMirror.pl, performance wise.
pending.c stores data in a way
very similar to the PgSQL input \ escaped format.
When the field is of type bytea, and the source of data is binary, then
this produces 2 additional backslashes for every unprintable
char.
The performance in function extractData in DBMirror.pl, really suffers
from this condition, since it breaks data in chunks of \ delimited
strings.
Informally speaking, performance tends to be O(n) where n is the size
of the data.
This can be remedied if we break data in chunks of ' rather than \.
' happens much more infrequently in common binary files (bz2, tiff, jpg,
pdf etc..), and if we notice that odd number of contained \, signals an
intermidiate ', whereas even number of \ signals the final ',
then we can make this routine run much faster.
I attach the new extractData function.
Now replicating a 400 k tiff takes 3 seconds instead of 12 minutes
it used to do.
I am wondering about the state of
http://www.whitebeam.org/library/guide/TechNotes/replicate.rhtm
Please feel free for any comments.
Pete could you test this new DBMirror.pl, to see how it behaves
in comparison with your C++ solution?
sub extractData($$) {
my $pendingResult = $_[0];
my $currentTuple = $_[1];
my $fnumber;
my %valuesHash;
$fnumber = 4;
my $dataField = $pendingResult-getvalue($currentTuple,$fnumber);
my $numofbs;
while(length($dataField)0) {
# Extract the field name that is surronded by double quotes
$dataField =~ m/(\.*?\)/s;
my $fieldName = $1;
$dataField = substr $dataField ,length($fieldName);
$fieldName =~ s/\//g; #Remove the surronding signs.
if($dataField =~ m/(^= )/s) {
#Matched null
$dataField = substr $dataField , length($1);
$valuesHash{$fieldName}=undef;
}
elsif ($dataField =~ m/(^=\')/s) {
#Has data.
my $value;
$dataField = substr $dataField ,2; #Skip the ='
LOOP: { #This is to allow us to use last from a do loop.
#Recommended in perlsyn manpage.
do {
my $matchString;
my $matchString2;
#Find the substring ending with the first ' or first \
$dataField =~ m/(.*?[\'])?/s;
$matchString = $1;
$numofbs = ($matchString =~ tr/\\//) % 2;
if ($numofbs == 1) { #// odd number of \, i.e. intermediate '
$matchString2 = substr $matchString,0, length($matchString)-2;
$matchString2 =~ s//\\/g;
$value .= ($matchString2 . \');
$dataField = substr $dataField,length($matchString);
}
else { #// even number of \, i.e. found end of data
$matchString2 = substr $matchString,0, length($matchString)-1;
$matchString2 =~ s//\\/g;
$value .= $matchString2;
$dataField = substr $dataField,length($matchString)+1;
last;
}
} until(length($dataField)==0);
}
$valuesHash{$fieldName} = $value;
}#else if
else {
logErrorMessage Error in PendingData Sequence Id .
$pendingResult-getvalue($currentTuple,0);
die;
}
} #while
return %valuesHash;
}
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index
Re: [GENERAL] Postgresql/
am 23.01.2006, um 11:38:28 +0100 mailte Janning Vygen folgendes: I would like to talk to some professional Postgresql DBA personally (preferred in german language). Of course we will pay for it. I don't want to talk to sells personal, i want to talk to someone who really knows and has lots of http://www.credativ.de/ Peter Eisentraut and Michael Meskes, german PostgreSQL-Developers, working there. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] What is made a mistake with SP?
On Jan 23, 2006, at 3:27 AM, Marcos wrote: I need create SP that returns cursos so that I can work with them. I have many SQLs used for search records in database, then I will make SP to return the results. The example that I'm trying is: I did not notice any obvious errors. It would help to know exactly what problem you are having. In other words, what does select * from fun_compras_calculado(); return and if it is not an error, why is it wrong? John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] DBMirror.pl performance change
Peter, It is much more convinient for you to make a test, (just change the last function in DBmirror.pl), than for me (grab whitebeam, compile for FreeBSD, etc...) Of course you would need to use the original .conf format than the one you are using now. It would be interesting to see some numbers. P.S. Please include my address explicitly, pgsql-general comes to me in digest mode. -- -Achilleus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] numeric data type?
OK. Thanks for clarification. - Original Message - From: Doug McNaught [EMAIL PROTECTED] To: Zlatko Matić [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org; Tony Caduto [EMAIL PROTECTED] Sent: Sunday, January 22, 2006 2:39 PM Subject: Re: [GENERAL] numeric data type? Zlatko Matić [EMAIL PROTECTED] writes: So, it seems that numeric without parameters (precision, scale) behave similar to float, but is much exact. Am I right or I missunderstood? Right. It's also considerably slower, since floating point calculations can use the hardware. Unless you're doing a huge number of computations this may not be an issue. -Doug ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] numeric data type?
I have a (only vaguely) related question about NUMERICs. I'm using someone else's schema to copy data from their DB into mine. They use NUMERIC quite a bit, with scale 0, where I would use one of the integer types. My question is whether joining and matching on NUMERIC is likely to be slower than, say, INTEGER. Note that I'm never doing math with these values, they are just IDs. Thanks for any info provided! - John D. Burger MITRE ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] numeric data type?
On Mon, Jan 23, 2006 at 09:48:52AM -0500, John D. Burger wrote: I have a (only vaguely) related question about NUMERICs. I'm using someone else's schema to copy data from their DB into mine. They use NUMERIC quite a bit, with scale 0, where I would use one of the integer types. My question is whether joining and matching on NUMERIC is likely to be slower than, say, INTEGER. Note that I'm never doing math with these values, they are just IDs. Yes, it's will be slower. Whether it's noticable... it depends on how often you do it. The question is really, do you need to use numeric? Will you be multiplying large numbers, do you expect decimals when you divide, etc. Decide your answer to that before deciding about performance issues. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] What is made a mistake with SP?
Hi John Thanks for response. In other words, what does select * from fun_compras_calculado(); return and if it is not an error, why is it wrong? My problem is in CREATE the function, see: [EMAIL PROTECTED] psql teste -U teste teste.sql ERRO: tipo tipo_compras_calculado não existe In english I think that it means ERROR: type tipo_compras_calculado not exists. This is my problem with this SP... Marcos. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] RAID 5 and postgresql
On Sat, 2006-01-21 at 07:09, Sander Steffann wrote: Hi, I would suppliment this with just saying that your controller card is your performance, the only cards I've seen score well on linux, and people have expressed on this list for SCSI are the LSI card, for SATA, LSI, 3ware (now AMCC) and Areca claim good linux support and seem to work well. Steer full clear of Adaptec, Dell and Compaq controllers, and their linux support is abysmal, and the performance reflects that, particularly in RAID 5. Dell has used (and rebranded) Adaptec and LSI controllers for their PERC series, and I agree that the Adaptec controllers perform badly. As far as I know the LSI based controllers are quite good (and some come with 256MB battery backed cache, which is nice :-) Last place I worked we used Dell rackmounts (2600 series mostly) and they came, by default with the Adaptec based controllers. Those were horrific, locking up under load, really poor performance, etc... The LSIs, as you mentioned, were much better. We had exactly one Dell 2600 with the LSI (hmmm. Bet you can't guess who specced that machine out, eh? hehe) with 256 Meg BBCache. While it never locked up or hung, it's I/O was noticeable slower than the machine it replaced, which also had an LSI RAID controller with BBCache, bascially, the same chipset. I'm not sure if it's Dell's BIOS on the mobos, or something with the LSI cards, but the performance was substandard. So if you're working somewhere that you simply have to use Dell (not uncommon), at least make sure you get the LSI based RAID controller. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] invalid memory alloc request size
Janning Vygen [EMAIL PROTECTED] writes: pg_dump: ERROR: invalid memory alloc request size 18446744073709551614 pg_dump: SQL command to dump the contents of table spieletipps failed: PQendcopy() failed. This looks more like a corrupt-data problem than anything else. Have you tried the usual memory and disk testing programs? recent thread on HACKERS but sorry guys: i dont know how to produce a backtrace. Time to learn ;-) gdb /path/to/postgres_executable /path/to/core_file gdb bt gdb q The core file will be somewhere under $PGDATA, named either core or core.n depending on your kernel settings. If you don't see one then it's probable that the postmaster was started under ulimit -c 0. Put ulimit -c unlimited in your postgres startup script, restart, trigger the crash again. It's also a good idea to look in the postmaster log to see if any unusual messages appeared before the crash. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] What is made a mistake with SP?
On Jan 23, 2006, at 8:34 AM, Marcos wrote: ERROR: type tipo_compras_calculado not exists. You have a set returning function (RETURNS setof tipo_compras_calculado), so this means you need to declare this type. So you should have a CREATE TYPE statement somewhere which defines the fields of tipo_compras_calculado. Something like: CREATE TYPE tipo_compras_calculado ( id text, produto integer, ... ); John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Combine, Merge, Concatenate
Dear pgsql users, I have a problem, which is quite hard to solve it in Perl (for me, of course). I have to tables, which looks like First Table: 1|001|002|003 2|006|04|002 Second Table: 001|text1|text2|text3 002|text6|text1|text2 Now I would like to concatenate this two tables into new table: Third Table: 1|text1|text2|text3|text6 * 2|etc *Notes; key=1 from first table; substitute 001 from first table with text1|text2|text3 and 002 with text6 etc... Is that possible in pgsql? Thanks in advance for any notes and suggestions, Cheers, Andre ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Transact SQL compatibility layer
Is there such a thing as a translator/proxy that can pretend to be a Microsoft SQL server and proxy all the commands to a PostgreSQL server, and the results back again? Obviously the purpose of it would be to allow an application written for MSSQL Server to work with PostgreSQL without modification. If not, has such a thing been considered? What would be the obstacles to developing such a proxy? Thanks James (Not subscribed to the list at the moment - I'll check the archives but please cc me on a response) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] joining tables
[EMAIL PROTECTED] writes: Hi, If you have two tables, each with a column called keys and a column called values, and they are both incomplete, such as: table 1: keys | values -+-- 1| (null) 2| two 3| (null) table 2: keys | values -+- 1| one 2| (null) 3| three is there a way to join them, in order to get: keys | values -+- 1| one 2| two 3| three The closest I could get was with NATURAL FULL JOIN: keys | values -+- 1| one 1| (null) 2| two 2| (null) 3| three 3| (null) Thanks Try something like: SELECT key, CASE when table1.value IS NOT NULL THEN k1.value ELSE table2.value END as value FROM table1 FULL JOIN table2 USING(key); You might want to use 'IS DISTINCT FROM table2.value' if you want the value for table1 to be returned in preference to table2.value. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Full Text Indexing Using Tsearch2-Module
Hello All, I have installed Tsearch-Module for full text indexing .But when I search text using gist(idxFTI) index on table I also found all data which have same accent. Example 1.If I try search for MANI word it also search for MANY word. 2.If I try search for ANDY word it also search for ANDI word. Please can you tell me how to avoid this problem ? If I want to search text MANI it should search only for MANI not MANY. -Original Message- From: Jim Nasby [mailto:[EMAIL PROTECTED] Sent: Thursday, January 12, 2006 1:01 AM To: Praveen Kumar (TUV); [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: RE: [SPAM] - how can we use outer join in Postures - Found word(s) if you received this in error in the Text body This should really have been sent to the -general mailing list, so I'm adding it. You will need to use OUTER JOIN syntax to accomplish this in PostgreSQL; see http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html#QUERIES-FROM Note that most databases (including Oracle) now support JOIN syntax instead of other hacks to support outer joins. -Original Message- From: Praveen Kumar (TUV) [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 11, 2006 6:39 AM To: Jim Nasby; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [SPAM] - how can we use outer join in Postures - Found word(s) if you received this in error in the Text body Hello All, I want to create one table with from many different tables using outer joins.Please can you guide how is possible to create in Postgresql. Let we have syntax for creating table in oracle.If we want to create same table in Postgresql then how will we replace (+) in syntax sothat we can use outer join facility in PostgreSQL. CREATE table comp_prod_cert AS select tuv_tuvdotcom_mast.tuvdotcom as tuvdotcom, COALESCE(tuv_location_mast_intl.first_name,tuv_location_mast.first_name) || ' ' || COALESCE(tuv_location_mast_intl.second_name,tuv_location_mast.second_name) as company_name, tuv_certificate_mast.cert_id as cert_id, tuv_certificate_mast.cert_number as certificate_number, tuv_certificate_mast.cust_id as cust_id, COALESCE(tuv_zart_mast_intl.description,tuv_zart_mast.description) as description, tuv_tuvdotcom_mast.tuvdotcom_id as tuvdotcom_id,tuv_tuvdotcom_mast.status_id as status_id, '' page_valid_from, sysdate page_creation_date, tuv_tuvdotcom_mast.tuvdotcom||' '||COALESCE(tuv_tuvdotcom_intl.sublease_company_name,tuv_tuvdotcom_mast.sublease_company_name)||' ' ||COALESCE(tuv_tuvdotcom_intl.marketing_info,tuv_tuvdotcom_mast.marketing_info)||' '||tuv_certificate_mast.cert_number||' ' ||tuv_certificate_mast.scope_english||' '||tuv_certificate_mast.scope_german||' '||tuv_certificate_mast.scope_local||' ' ||tuv_zart_mast.zart_name||' '||COALESCE(tuv_zart_mast_intl.description,tuv_zart_mast.description)||' ' ||COALESCE(tuv_customer_mast_intl.url,tuv_customer_mast.url)||' '||COALESCE(tuv_customer_mast_intl.email,tuv_customer_mast.email)||' ' ||tuv_customer_mast.name_local||' '||tuv_customer_mast.address_local||' '||tuv_customer_mast.building_local||' ' ||tuv_customer_mast.city_local||' '||COALESCE(tuv_customer_mast_intl.title,tuv_customer_mast.title)||' ' ||COALESCE(tuv_customer_mast_intl.first_name,tuv_customer_mast.first_name)||' ' ||COALESCE(tuv_customer_mast_intl.second_name,tuv_customer_mast.second_name)||' ' ||COALESCE(tuv_customer_mast_intl.third_name,tuv_customer_mast.third_name)||' ' ||COALESCE(tuv_customer_mast_intl.fourth_name,tuv_customer_mast.fourth_name)||' ' ||tuv_location_mast.post_code||' '||tuv_location_mast.phone||' '||tuv_location_mast.fax||' ' ||COALESCE(tuv_location_mast_intl.title,tuv_location_mast.title)||' ' ||COALESCE(tuv_location_mast_intl.first_name,tuv_location_mast.first_name)||' ' ||COALESCE(tuv_location_mast_intl.second_name,tuv_location_mast.second_name)||' ' ||COALESCE(tuv_location_mast_intl.third_name,tuv_location_mast.third_name)||' ' ||COALESCE(tuv_location_mast_intl.fourth_name,tuv_location_mast.fourth_name)||' ' ||COALESCE(tuv_location_mast_intl.street_1,tuv_location_mast.street_1)||' ' ||COALESCE(tuv_location_mast_intl.street_2,tuv_location_mast.street_2)||' ' ||COALESCE(tuv_location_mast_intl.city_1,tuv_location_mast.city_1)||' ' ||COALESCE(tuv_location_mast_intl.city_2,tuv_location_mast.city_2)||' ' ||COALESCE(tuv_location_mast_intl.state,tuv_location_mast.state)||' '||COALESCE(tuv_location_mast_intl.country,tuv_location_mast.country) as search_data from tuv_tuvdotcom_mast, tuv_tuvdotcom_intl, tuv_tuvdotcom_type_mast, tuv_tuvdotcom_system_certs, tuv_certificate_mast, tuv_customer_location, tuv_location_mast, tuv_location_mast_intl, tuv_customer_mast, tuv_customer_mast_intl, tuv_zart_mast, tuv_zart_mast_intl where tuv_tuvdotcom_mast.tuvdotcom_id = tuv_tuvdotcom_intl.tuvdotcom_id(+) and tuv_tuvdotcom_mast.tdc_type_id = tuv_tuvdotcom_type_mast.tdc_type_id and
[GENERAL] ROLLBACK triggers?
Hi, First, apologies if my question is a bit off-course. Please feel free to direct me to a different mailing list if not appropriate. I'm currently trying to embed Senna full text search engine (http://qwik.jp/senna/) into postgres. I'm trying to achieve this by using triggers (implemented in C) to cause an update to senna's index at various points. This seemed to work fine until I realized that while postgres' SQL commands could be rolled back, Senna's index remained already-changed. There are other potential issues with regards to transaction safety, but currently this seems to be a problem that I cannot fix by simply patching Senna. So I thought that if there was a rollback trigger, I could call whatever necessary to undo the changes that were made to the index. A quick scan of the docs and the source code tree seems to indicate that there is no such thing as a rollback trigger, short of hacking it. Now, I'm wondering: 1. Is there a rollback/commit trigger? If not, is it planned to be implemented at all? 2. Is there a way to undo changes to data external to postgres when a rollback occurs, OR, only update that external data when a commit occurs? Thanks in advance, --d ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] RAID 5 and postgresql
At 10:01 AM 1/23/2006 -0600, Scott Marlowe wrote: I'm not sure if it's Dell's BIOS on the mobos, or something with the LSI cards, but the performance was substandard. So if you're working somewhere that you simply have to use Dell (not uncommon), at least make sure you get the LSI based RAID controller. How about software RAID? Linux software RAID appears to perform better than most RAID controllers except perhaps those that can do read interleaving for RAID1 (I believe some 3ware controllers can do it). Linux RAID mirroring doesn't do read interleaving, only read balancing, which may not be so good for a single sequential read, but pretty good for concurrent sequential reads - each drive in a mirror set can handle one sequential read. I find many of these RAID controllers fail significantly more than basic SCSI controllers (which hardly ever fail). And the support under Linux for such controllers can be a bit patchy sometimes - you want to be able to easily know if a drive has died. It just seems strange to pay a fair bit for something that doesn't perform well and is less reliable. Of course you get the convenience of the RAID stuff being abstracted away so it just looks like one drive. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] invalid memory alloc request size
Am Montag, 23. Januar 2006 17:05 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: pg_dump: ERROR: invalid memory alloc request size 18446744073709551614 pg_dump: SQL command to dump the contents of table spieletipps failed: PQendcopy() failed. This looks more like a corrupt-data problem than anything else. Have you tried the usual memory and disk testing programs? no, i didn't. What are the usual memory and disk testing programs? ( a few weeks ago i wanted to start a troubleshooting guide for guys like me, but i didn't start yet this needs to be documented.). I am not a system administrator and a hard disk is a black box to me. By the way: the database is still running and serving requests. recent thread on HACKERS but sorry guys: i dont know how to produce a backtrace. Time to learn ;-) gdb /path/to/postgres_executable /path/to/core_file gdb bt gdb q I shouldn't call gdb while my database is up and running, don't i? I tried to find and delete the corrupted row (as you mentioned in http://archives.postgresql.org/pgsql-admin/2006-01/msg00117.php) I found it: $ select sp_id from spieletipps limit 1 offset 387583; Server beendete die Verbindung unerwartet Das heißt wahrscheinlich, daß der Server abnormal beendete bevor oder während die Anweisung bearbeitet wurde. Die Verbindung zum Server wurde verloren. Versuche Reset: Fehlgeschlagen. ! \q and i can get the ctid: $ select ctid from spieletipps limit 1 offset 387583; ctid --- (3397,49) (1 Zeile) but when i want to delete it: $ delete from spieletipps where ctid = '(3397,49)'; Server beendete die Verbindung unerwartet Das heißt wahrscheinlich, daß der Server abnormal beendete bevor oder während die Anweisung bearbeitet wurde. Die Verbindung zum Server wurde verloren. Versuche Reset: Fehlgeschlagen. How can i get rid of it? (I don't have oids in the table, i created them without oids) The core file will be somewhere under $PGDATA, named either core or core.n depending on your kernel settings. If you don't see one then it's probable that the postmaster was started under ulimit -c 0. Put ulimit -c unlimited in your postgres startup script, restart, trigger the crash again. It's also a good idea to look in the postmaster log to see if any unusual messages appeared before the crash. this is form the postmaster log: LOG: server process (PID 14756) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing FATAL: the database system is starting up LOG: database system was interrupted at 2006-01-23 09:46:03 CET LOG: checkpoint record is at 1/D890C0E0 LOG: redo record is at 1/D88F93E8; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 485068; next OID: 16882321 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 1/D88F93E8 LOG: record with zero length at 1/D8953988 LOG: redo done at 1/D8953920 LOG: database system is ready LOG: server process (PID 15198) was terminated by signal 11 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. FATAL: the database system is in recovery mode LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2006-01-23 09:46:15 CET LOG: checkpoint record is at 1/D8953988 LOG: redo record is at 1/D8953988; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 485130; next OID: 16882321 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 1/D89539D0 LOG: record with zero length at 1/D8966BF8 LOG: redo done at 1/D8966BC8 LOG: database system is ready LOG: server process (PID 15400) was terminated by signal 11 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2006-01-23 09:46:24 CET LOG: checkpoint record is at 1/D8966BF8 LOG: redo record is at 1/D8966BF8; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 485183; next OID: 16882321 LOG: database system was not properly shut down; automatic recovery in progress FATAL: the database system
Re: [GENERAL] invalid memory alloc request size
Janning Vygen [EMAIL PROTECTED] writes: I shouldn't call gdb while my database is up and running, don't i? Sure you can. Especially against a core dump --- that mode doesn't have anything to do with the running processes. $ delete from spieletipps where ctid = '(3397,49)'; Server beendete die Verbindung unerwartet Hmm ... as far as I can think at the moment, this suggests a problem with a toasted field; DELETE wouldn't need to look at the contents of a target row except if it has to find and delete subsidiary toast rows. But looking at the gdb backtrace would help to confirm or deny that. Another thing that would be useful at this point is to get a dump of the page containing the corrupted tuple, which we now know is block 3397 of that table. See pg_filedump from http://sources.redhat.com/rhdb/utilities.html Something like pg_filedump -i -f -R 3397 $PGDATA/base//, where is the database OID and is the table's relfilenode. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Transact SQL compatibility layer
--- James Harper [EMAIL PROTECTED] wrote: Is there such a thing as a translator/proxy that can pretend to be a Microsoft SQL server and proxy all the commands to a PostgreSQL server, and the results back again? Obviously the purpose of it would be to allow an application written for MSSQL Server to work with PostgreSQL without modification. If not, has such a thing been considered? What would be the obstacles to developing such a proxy? Assuming your goal is compatibility to a level such that no changes are required in the client application, I'd say it'd be a tremendous amount of work. At the very least you'd have to reverse-engineer mssql server's communication protocol and write a proxy that could understand and translate that to something compatible with postgresql. Then you'd have to deal with the difference between the two server sql dialects. All in all, you'd be much better off designing your application such that it explicitly supports multiple databases to begin with. Regards, Shelby Cain __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Transact SQL compatibility layer
On Sun, 2006-01-22 at 18:46, James Harper wrote: Is there such a thing as a translator/proxy that can pretend to be a Microsoft SQL server and proxy all the commands to a PostgreSQL server, and the results back again? Obviously the purpose of it would be to allow an application written for MSSQL Server to work with PostgreSQL without modification. If not, has such a thing been considered? What would be the obstacles to developing such a proxy? The amount of work required here would be pretty huge. Plus, what versions of MSSQL do you emulate? What about newer versions of both MSSQL and PGSQL. If constant maintenance wasn't done on it, it would soon become a legacy project. My guess is that the only way you could make it happen would be to form a company and sell it commercially. I doubt any folks in the open source community would feel a burning desire to spend a lot of time making PostgreSQL act like it was MSSQL server. There are few, if any, projects that require MSSQL that I'd be interested enough in to put that much effort into adapting PostgreSQL to act like it was MSSQL server. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] DBMirror.pl performance change
Achilleus Mantzios wrote: Peter, It is much more convinient for you to make a test, (just change the last function in DBmirror.pl), than for me (grab whitebeam, compile for FreeBSD, etc...) Of course you would need to use the original .conf format than the one you are using now. It would be interesting to see some numbers. P.S. Please include my address explicitly, pgsql-general comes to me in digest mode. I'll take a look into this when I get a chance. Right now the only replicated systems I have are for live commercial clients - my development systems aren't replicated, just backed-up periodically. It is worth looking through the Perl version some more though. I'm pretty sure I worked around most of the escaping/unescaping when I looked at the 'C' version. I'm pretty sure some of the same approach could be used to improve performance of the Perl version. The main thing I found was that the data table is un-escaped when read from the table and then re-escaped before being sent to the slave database. In practice the data doesn't have to be touched. My own preference right now is to stick with the C version now I have it. Replication is just about simultaneous with negligible CPU usage. When I get a chance, I'm intending decoupling the 'C' version from the whole of Whitebeam so it can be built by itself. At the time I needed a solution quickly so making use of a few Whitebeam utility classes got me there. Pete ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Linux - postgres RAID
I figure this would be a good place to ask. I want to build / buy a new linux postgres box. I was wondering if anyone on this list had some experience with this they'd like to share. I'm thinking somewhere in the $7k - 15k range. The post important things are write speed to the disk and good linux driver support for the raid card. Can anyone recommend a specific raid card / server vendor? Thanks, Rick ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] RAID 5 and postgresql
On Jan 21, 2006, at 8:09 AM, Sander Steffann wrote: Dell has used (and rebranded) Adaptec and LSI controllers for their PERC series, and I agree that the Adaptec controllers perform badly. As far as I know the LSI based controllers are quite good (and some come with 256MB battery backed cache, which is nice :-) Over the last 5 years I've had a *LOT* of Dell equipment, most of which has RAID. The adaptec-based ones are OK. Not great performance, but very easy to manage. The LSI based ones are pretty good, but the older ones were somehow deficient in that the expected performance was never achieved. However, the latest one I have is a PE 1850 with the PERC43/Si (LSI based) which is surprisingly fast. It comes with battery + 256MB RAM and just screams in RAID1 mode (only have two disks on this box). I'd buy that server again anyday. However, for my big DB server arrays, I always go with the LSI branded cards and run them in RAID10 config. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Are indexes used with LIKE?
Hi, Are indexes on VARCHAR columns used with the LIKE operator, and if so, how efficiently are they used? I can imagine that using indexes can be easy with the starting literal characters up to the first percent sign such as in: LIKE 'ZOE%QQWE%' But, after the first % sign, things can get more difficult. The reason I am asking is that we are thinking about discriminating between rows of a table based on a VARCHAR column containing various one-character flags. We could then use the LIKE operator for formulating filter conditions. Any help is appreciated! Regards Peter ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] invalid memory alloc request size
Janning Vygen [EMAIL PROTECTED] writes: Ok, i got the reffilnode from pg_class and compiled pg_filedump. result of ./pg_filedump -i -f -R 3397 /home/postgres8/data/base/12934120/12934361 filedump.txt is attached OK, what's the schema of this table exactly? It looks like there are a couple of text or varchar columns to start, but I'm not sure about the last three columns. but i guess its item 49 which makes trouble 1258: 0100 616c7465 68656964 6500 alteheide... But it doesn't look very diffrent to item 48: 12a0: 0d00 616c7465 68656964 6500 alteheide... If these are both supposed to be strings 'alteheide', then the problem is the bogus length word on the first one: instead of starting with 0100 it should start with 0d00, like the second one does. It's conceivable that this stems from a software problem, but I'm wondering about hardware problems causing dropped bits, myself. Another point is that AFAICS this tuple could not pose a problem for DELETE all by itself, because it doesn't have any toasted fields. Perhaps there is more corruption elsewhere. Could you get a stack trace from the crashed DELETE, rather than a crashed SELECT? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux - postgres RAID
Ok then, the size of the postgres data directory is about 1 GB. The OS will be a version of linux with a 2.6 kernel. 100 GB of total storage would be plenty. The load load would probably be around 2k-3k transactions / minute. I plan on doing a lot of research on my own I am just looking for a place to start looking. On Jan 23, 2006, at 12:19 PM, Wes Williams wrote: For those funds you best be served by defining the database size, projected capacity, load, specific OS, and other details in order to even remotely receive a usable reply. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Rick Gigger Sent: Monday, January 23, 2006 2:13 PM To: pgsql general Subject: [GENERAL] Linux - postgres RAID I figure this would be a good place to ask. I want to build / buy a new linux postgres box. I was wondering if anyone on this list had some experience with this they'd like to share. I'm thinking somewhere in the $7k - 15k range. The post important things are write speed to the disk and good linux driver support for the raid card. Can anyone recommend a specific raid card / server vendor? Thanks, Rick ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux - postgres RAID
On Jan 23, 2006, at 11:13 AM, Rick Gigger wrote: I figure this would be a good place to ask. I want to build / buy a new linux postgres box. I was wondering if anyone on this list had some experience with this they'd like to share. I'm thinking somewhere in the $7k - 15k range. The post important things are write speed to the disk and good linux driver support for the raid card. Can anyone recommend a specific raid card / server vendor? It'll depend on what you're planning on using it for. Performance requirements, capacity and so on. For fast writes you'll probably want something like RAID10 with lots of spindles. SCSI is likely to be somewhat faster, but a lot more expensive. For SATA RAID most people like the 3ware and Areca cards. I'm using this system, from ASA, for pretty much that and it seems OK. 5U server, 24 external bay, 2 internal bay capacity. And it's opterons, which seem to be a bit more PG-friendly than Xeons. It uses the 3ware 9550SX card. (The general opinion here seems to be that the Areca cards are somewhat better than the 3wares, but a lot more expensive). http://www.asaservers.com/config_system.asp?config_id=5USATA Cheers, Steve ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] invalid memory alloc request size
Am Montag, 23. Januar 2006 20:30 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: Ok, i got the reffilnode from pg_class and compiled pg_filedump. result of ./pg_filedump -i -f -R 3397 /home/postgres8/data/base/12934120/12934361 filedump.txt is attached OK, what's the schema of this table exactly? It looks like there are a couple of text or varchar columns to start, but I'm not sure about the last three columns. kicktipp.de= \d spieletipps Tabelle »public.spieletipps« Spalte | Typ| Attribute ---+--+--- tr_kurzname | text | not null mg_name | text | not null sp_id | integer | not null stip_heimtore | smallint | not null stip_gasttore | smallint | not null Indexe: »pk_spieletipps« PRIMARY KEY, btree (tr_kurzname, mg_name, sp_id) »ix_stip_fk_spiele« btree (tr_kurzname, sp_id) CLUSTER Fremdschlüssel-Constraints: »fk_mitglieder« FOREIGN KEY (tr_kurzname, mg_name) REFERENCES mitglieder(tr_kurzname, mg_name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED »fk_tippspieltage2spiele« FOREIGN KEY (tr_kurzname, sp_id) REFERENCES tippspieltage2spiele(tr_kurzname, sp_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED Regeln: cache_stip_delete AS ON DELETE TO spieletipps DO UPDATE tsptcache SET tc_cache = -2 FROM tippspieltage2spiele tspt2sp, spiele sp WHERE tsptcache.tr_kurzname = old.tr_kurzname AND tspt2sp.tr_kurzname = old.tr_kurzname AND tspt2sp.sp_id = old.sp_id AND tspt2sp.sp_id = sp.sp_id AND sp.sp_abpfiff = true AND tsptcache.tspt_sort = tspt2sp.tspt_sort AND sign((old.stip_heimtore - old.stip_gasttore)::double precision) = sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND tsptcache.tc_cache -2 cache_stip_insert AS ON INSERT TO spieletipps DO UPDATE tsptcache SET tc_cache = -2 FROM tippspieltage2spiele tspt2sp, spiele sp WHERE tsptcache.tr_kurzname = new.tr_kurzname AND tspt2sp.tr_kurzname = new.tr_kurzname AND tspt2sp.sp_id = new.sp_id AND tspt2sp.sp_id = sp.sp_id AND sp.sp_abpfiff = true AND tsptcache.tspt_sort = tspt2sp.tspt_sort AND sign((new.stip_heimtore - new.stip_gasttore)::double precision) = sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND tsptcache.tc_cache -2 cache_stip_update AS ON UPDATE TO spieletipps DO UPDATE tsptcache SET tc_cache = -2 FROM tippspieltage2spiele tspt2sp, spiele sp WHERE tsptcache.tr_kurzname = new.tr_kurzname AND tspt2sp.tr_kurzname = new.tr_kurzname AND tspt2sp.sp_id = new.sp_id AND tspt2sp.sp_id = sp.sp_id AND sp.sp_abpfiff = true AND tsptcache.tspt_sort = tspt2sp.tspt_sort AND (sign((new.stip_heimtore - new.stip_gasttore)::double precision) = sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) OR sign((old.stip_heimtore - old.stip_gasttore)::double precision) = sign((sp.sp_heimtore - sp.sp_gasttore)::double precision)) AND tsptcache.tc_cache -2 but i guess its item 49 which makes trouble 1258: 0100 616c7465 68656964 6500 alteheide... But it doesn't look very diffrent to item 48: 12a0: 0d00 616c7465 68656964 6500 alteheide... If these are both supposed to be strings 'alteheide', then the problem is the bogus length word on the first one: instead of starting with 0100 it should start with 0d00, like the second one does. yes, they should both be alteheide. Is it possible to open the file and just fix the bit? It's conceivable that this stems from a software problem, but I'm wondering about hardware problems causing dropped bits, myself. I have no clue, why it happens. But i changed my schema a few month ago to use a materialized view (You see all the rules in this schema above). i need some complicated ranking algorithm to calculate the materialzed view. everything is implemented inside postgresql with rules and functions (pgperl and plpgsql). One more aspect are temp tables to me. I use lots of them for a specific tasks (reusing the calculating algorithm mentioned above for a different data view). With lots of temp tables i got problems with pg_type where some old temp values reside and i got to delete some of them manually a few times per month. After all my feeling is that i encouter problems like this one too often to believe in hardware problems. But this time it seems to be a new one and i have no clue if hardware or software related. At this time i just want to fix it. But if you want to take a close look at it, i will send you all you need. Another point is that AFAICS this tuple could not pose a problem for DELETE all by itself, because it doesn't have any toasted fields. Perhaps there is more corruption elsewhere. Could you get a stack trace from the crashed DELETE, rather than a crashed SELECT? Maybe the rule is a problem? here you are. I did: select ctid from spieletipps limit 1 offset 387439; ctid --- (3397,49) (1 Zeile)
Re: [GENERAL] psql(18967) malloc: *** vm_allocate(size=8421376)
What about upping ulimt(3) via ulimit(1) in builtin(1) -- assuming you're running Bash. ~BAS On Mon, 9 Jan 2006, Ari Kahn wrote: I'm doing a query that really should be too taxing. But when I execute it I get the following error(s): psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3) psql(18967) malloc: *** error: can't allocate region psql(18967) malloc: *** set a breakpoint in szone_error to debug EXPLAIN ANALYZE SELECT a1.qname, a1.symbol, a1.num, a1.ge, a1.start, a1.stop, a1.cr, a1.str, a1.ex FROM singlehits a1, singlehit_ge a2 WHERE a2.cnt1 AND a2.symbol=a2.symbol; QUERY PLAN -- Nested Loop (cost=89.36..6086.42 rows=273312 width=88) (actual time=0.113..24456.508 rows=54952794 loops=1) - Seq Scan on singlehits a1 (cost=0.00..530.82 rows=17082 width=88) (actual time=0.043..71.127 rows=17082 loops=1) - Materialize (cost=89.36..89.52 rows=16 width=0) (actual time=0.000..0.418 rows=3217 loops=17082) - Seq Scan on singlehit_ge a2 (cost=0.00..89.34 rows=16 width=0) (actual time=0.049..5.167 rows=3217 loops=1) Filter: ((cnt 1) AND ((symbol)::text = (symbol)::text)) Total runtime: 30024.664 ms (6 rows) Given this post http://xy1.org/pgsql-general@postgresql.org/msg01154.html I tried both VACUUM FULL and ANALYZE on the DB to no avail. Thanks, Ari ---(end of broadcast)--- TIP 6: explain analyze is your friend l8* -lava x.25 - minix - bitnet - plan9 - 110 bps - ASR 33 - base8 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Fedora and pgadmin3
Hi I have fedora core4 and i've installed the pgadmin III RPM when i try to run the pgadmin i get a 'Speicherzugriffsfehler' which means something like memory access error. can someone help me please thanks martin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] ANN: Bricolage 1.10
It is with great pleasure that the Bricolage development team announces the release of Bricolage 1.10. The culmination of over 19 months of development, version 1.10 represents a significant advance for the celebrated open-source content management and publishing system. Here are some of the highlights: PHP Templating Bricolage is the first content management system to support three different Perl-based templating architectures (Mason, Template Toolkit, and HTML::Template) as well as one in a completely different programming language: PHP 5. Bricolage 1.10 adds PHP templating support, allowing template developers to use the popular Web programming language to formatting their documents for output. This functionality is thanks to a killer new technology, known as PHP::Interpreter, that loads the PHP 5 interpreter into a Perl 5 interpreter, and affords transparent access between PHP and Perl code. The upshot is that PHP templaters get full access to the entire Bricolage API, as well as the ability to use whatever other PHP or Perl libraries they wish. Our expect is that this development will push Bricolage into new environments where PHP developers can make use of the powerful content management and publishing system without having to learn a new programming language. Furthermore, we hope that PHP::Interpreter will act as a bridge between the Perl and PHP communities, such that there is a greater exchange of ideas and a greater ability to use each other's libraries. PHP::Interpreter was developed by OmniTI. PHP::Interpreter and the PHP templating support in Bricolage were sponsored by SAPO--Portugal Online. LDAP Authentication Bricolage 1.10 includes support for a pluggable authentication architecture, and in addition to its built-in authentication has added a module for authentication against an LDAP directory server. This new feature is sure to be welcome in busy enterprises that rely on a directory server, such as Windows Active Directory http://www.microsoft.com/windowsserver2003/technologies/ directory/activ edirectory/default.mspx, Novel eDirectory http://www.novell.com/products/edirectory/, or OpenLDAP http://www.openldap.org/. Authentication can be limited to members of a directory group, and supports LDAP v.3 and TLS connectivity. Contributed by Kineticode. Revamped Interface Bricolage 1.10 sports a completely revamped browser interface that is XHTML compliant and handles all styling via CSS. Yes, our 1999-era table-driven interface is officially a thing of the past. The upshot is that the interface is much more elegant, easier to skin with your own look (by overriding its CSS files), allows search results and editing fields to expand and contract with the browser window size, and delivers pages as much as 70% smaller than they were before. The new interface was Contributed by Marshall Roch. A second major new UI feature is the revamped Bulk Edit interface. Gone is the old Super Bulk Edit interface, with the Bulk Edit revisions overtaking its functionality. Now you can edit the entire contents of a story document, from the top-most element to the bottom-most field, in a single textarea field with no reloads. The secret to allowing the full-text editing of Bricolage's unique hierarchical element structures is Plain Old Documentation, or POD. Subelements are denoted by a new =begin POD tag, and end with a matching =end tag. The result is a much more natural editing interface. Even related stories and media are supported by new POD tags. We believe that this improvement will greatly facilitate the editing process, making Bricolage a much more enjoyable product for content editors to work with. The Bulk Edit revision is complemented by two new additions: diff support and a JavaScript-powered Find and Replace dialog box. Users can now see at a glance the changes between one version of a document and another. The changes are shown on a word-by-word basis, with additions in green with an underline and deletions in red with a strikeout. A similar interface is used to show the differences between versions of templates using the traditional unified diff format rather than word-by word. The JavaScript-powered Find and Replace dialog box can be used to search by strings or regular expressions in a Bulk Edit or Template editing environment. Found bits of text can also be replaced or even globally replaced. We believe that this powerful new feature, combined with the new Bulk Edit interface, makes Bricolage a compelling content editing environment. The Bulk Edit, diff, and Find and Replace features were contributed by Kineticode.
Re: [GENERAL] invalid memory alloc request size
Janning Vygen [EMAIL PROTECTED] writes: OK, what's the schema of this table exactly? ... Regeln: cache_stip_delete AS ON DELETE TO spieletipps DO UPDATE tsptcache SET tc_cache = -2 FROM tippspieltage2spiele tspt2sp, spiele sp WHERE tsptcache.tr_kurzname = old.tr_kurzname AND tspt2sp.tr_kurzname = old.tr_kurzname AND tspt2sp.sp_id = old.sp_id AND tspt2sp.sp_id = sp.sp_id AND sp.sp_abpfiff = true AND tsptcache.tspt_sort = tspt2sp.tspt_sort AND sign((old.stip_heimtore - old.stip_gasttore)::double precision) = sign((sp.sp_heimtore - sp.sp_gasttore)::double precision) AND tsptcache.tc_cache -2 Oh, I should have thought of that: the bare DELETE operation doesn't care what's in the tuple, but this ON DELETE rule sure does. That's why the delete crashes, it's trying to extract the field contents so it can execute the rule. yes, they should both be alteheide. Is it possible to open the file and just fix the bit? Yeah, if you have a suitable hex editor. You'll probably need to shut down the postmaster first, as it may have a cached copy of the page. I have no clue, why it happens. But i changed my schema a few month ago to use a materialized view (You see all the rules in this schema above). i need some complicated ranking algorithm to calculate the materialzed view. everything is implemented inside postgresql with rules and functions (pgperl and plpgsql). One more aspect are temp tables to me. I use lots of them for a specific tasks (reusing the calculating algorithm mentioned above for a different data view). With lots of temp tables i got problems with pg_type where some old temp values reside and i got to delete some of them manually a few times per month. Hmm ... the one part of that that jumps out at me is plperl. We already know that plperl can screw up the locale settings; I wonder whether there are other bugs. Anyway, if you are using plperl I *strongly* recommend updating to the latest PG release ASAP (8.0.6 in your case). If you cannot, at least make sure the postmaster is launched with the same LC_XXX settings in its environment as are embedded in the database. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux - postgres RAID
Rick Gigger wrote: I figure this would be a good place to ask. I want to build / buy a new linux postgres box. I was wondering if anyone on this list had some experience with this they'd like to share. I'm thinking somewhere in the $7k - 15k range. The post important things are write speed to the disk and good linux driver support for the raid card. Can anyone recommend a specific raid card / server vendor? Instead of (or in addition to) RAID configurations, you may want to consider using multiple disks, connected to independent channels (not a problem if you're talking SATA), such that PG can perform simultaneous access to the various filesystems. I'm not sure which parts are critical, or how many different partitions you would need for optimal performance, but the PG Performance mailing list archives should prove useful to find out about this. Maybe you could use SATA Raptor drives -- the 10k RPM, which I believe has a sustained transfer rate in the order of 80 or 90 MB/sec. Do make sure that you get a MB with fast internal bus (533 or 800 MB/sec at least), so that you don't waste your money on multiple independently-connected hard drives that hit a bottleneck when the data reaches the motherboard. And don't even bother to show up again if you were planning to put less than 4GB of memory!! :-) HTH, Carlos -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux - postgres RAID
Thanks! That's just the sort of info I am looking for. I am definitely going with the fastest scsi drives I can get. Probably a 6 or 8 disk system. Is there a huge jump between using 4 and 6 drives, or 6 and 8 drives? On Jan 23, 2006, at 12:42 PM, Steve Atkins wrote: On Jan 23, 2006, at 11:13 AM, Rick Gigger wrote: I figure this would be a good place to ask. I want to build / buy a new linux postgres box. I was wondering if anyone on this list had some experience with this they'd like to share. I'm thinking somewhere in the $7k - 15k range. The post important things are write speed to the disk and good linux driver support for the raid card. Can anyone recommend a specific raid card / server vendor? It'll depend on what you're planning on using it for. Performance requirements, capacity and so on. For fast writes you'll probably want something like RAID10 with lots of spindles. SCSI is likely to be somewhat faster, but a lot more expensive. For SATA RAID most people like the 3ware and Areca cards. I'm using this system, from ASA, for pretty much that and it seems OK. 5U server, 24 external bay, 2 internal bay capacity. And it's opterons, which seem to be a bit more PG-friendly than Xeons. It uses the 3ware 9550SX card. (The general opinion here seems to be that the Areca cards are somewhat better than the 3wares, but a lot more expensive). http://www.asaservers.com/config_system.asp?config_id=5USATA Cheers, Steve ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] invalid memory alloc request size
TOM! Ich will ein Kind von Dir!! (it means 'something like': thank you so much. you just saved my life!) Am Montag, 23. Januar 2006 21:16 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: OK, what's the schema of this table exactly? ... Regeln: cache_stip_delete AS ON DELETE TO spieletipps DO UPDATE tsptcache SET tc_cache = -2 [...] Oh, I should have thought of that: the bare DELETE operation doesn't care what's in the tuple, but this ON DELETE rule sure does. That's why the delete crashes, it's trying to extract the field contents so it can execute the rule. I dropped the rule and deleted the row successfully with the ctid. Thanks a lot for the great support! This problem will be my first article in my PostgreSQL Troubleshooting Guide for Dummies. We really need it for guys like me. yes, they should both be alteheide. Is it possible to open the file and just fix the bit? Yeah, if you have a suitable hex editor. You'll probably need to shut down the postmaster first, as it may have a cached copy of the page. i decided not to poke to postgres internal file storage. I have no clue, why it happens. But i changed my schema a few month ago to use a materialized view (You see all the rules in this schema above). i need some complicated ranking algorithm to calculate the materialzed view. everything is implemented inside postgresql with rules and functions (pgperl and plpgsql). One more aspect are temp tables to me. I use lots of them for a specific tasks (reusing the calculating algorithm mentioned above for a different data view). With lots of temp tables i got problems with pg_type where some old temp values reside and i got to delete some of them manually a few times per month. Hmm ... the one part of that that jumps out at me is plperl. We already know that plperl can screw up the locale settings; I wonder whether there are other bugs. Anyway, if you are using plperl I *strongly* recommend updating to the latest PG release ASAP (8.0.6 in your case). ok, shouldn't i upgrade to 8.1 instead of 8.0.6 if i can? If you cannot, at least make sure the postmaster is launched with the same LC_XXX settings in its environment as are embedded in the database. i will look at it! kind regards janning ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] invalid memory alloc request size
Janning Vygen [EMAIL PROTECTED] writes: Hmm ... the one part of that that jumps out at me is plperl. We already know that plperl can screw up the locale settings; I wonder whether there are other bugs. Anyway, if you are using plperl I *strongly* recommend updating to the latest PG release ASAP (8.0.6 in your case). ok, shouldn't i upgrade to 8.1 instead of 8.0.6 if i can? Up to you --- you have more risk of compatibility issues if you do that, whereas within-branch updates are supposed to be painless. Depends whether you have the time right now to deal with testing your applications against 8.1. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] invalid memory alloc request size
Am Montag, 23. Januar 2006 21:57 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] writes: ok, shouldn't i upgrade to 8.1 instead of 8.0.6 if i can? Up to you --- you have more risk of compatibility issues if you do that, whereas within-branch updates are supposed to be painless. Depends whether you have the time right now to deal with testing your applications against 8.1. ok, i will think about it. one more question: You mentioned standard disk and memory checks. Can you point to some link where i can find more about it or which software do you mean? I guess i have to start looking at it. kind regards, janning ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
hardware checks (was Re: [GENERAL] invalid memory alloc request size)
Janning Vygen [EMAIL PROTECTED] writes: one more question: You mentioned standard disk and memory checks. Can you point to some link where i can find more about it or which software do you mean? I guess i have to start looking at it. The stuff I've heard recommended is memtest86 for memory checks and badblocks for disk checks. But perhaps someone on the list has better ideas. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL]
unsubscribe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: hardware checks (was Re: [GENERAL] invalid memory alloc request
I second Tom: badblocks and memtest86 are what I use and works great on all kinds of hardware. You don't even need a specific OS for memtest86 because you can make a bootable floppy and test any old piece of hardware it recognizes. Terry -- Terry Fielder [EMAIL PROTECTED] Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Installing Postgres 8.1 on Windows Server 2003 R2
I've successfully installed Postgres 8.1.2 on WS 2003 R2 on a Lenovo (IBM) ThinkPad. I used the default installation options and everything seems peachy keen for a single user (using localhost). However, when we started performance testing Postgres(vs. MySQL) using a 3rd party tool (I forget the name) running on the server, Postgres would drop connections after about the 10th or 11th virtual user (of 20)hit the DB. The remaining user processes wouldremain visible in Window's Task Manager and just "hang." We've used the same tool, code, DB model, etc. on the samemachineusing MySQL and have gotten well over 100 virtual users with no issues. Try as I might, I can't find anything that might indicate a cause for Postgres to drop or lock out the connections. The app is written in PHP using PEAR DB class for DB access. Running PHP 5.1.1, Postgres 8.1.2, latest and greatest WS 2003 R2 updates. The app is read/write intensive. Any help in getting us migrated from MySQL to Postgres (which, BTW, is showing about 50% performance gain over MySQL!) would be MORE than appreciated... Carl From: Postgres User [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 6:55 PMTo: Magnus Hagander; pgsql-general@postgresql.orgSubject: Re: Installing Postgres 8.1 on Windows Server 2003 R2 That's what I was afraid of... it's a new install of Win Server 2003 R2, so I can rule out any third party firewall. Windows Firewall isNOT installed. And I've installedPostgres on a Windows XP boxbehind the same router, so it's not a router-firewall issue. It's probably a new R2 feature,I'llping someone at Microsoft because Idon'thave a clue at this point what the problem is... Jon On 1/8/06, Magnus Hagander [EMAIL PROTECTED] wrote: Has anyone tried to install Postgres on Windows Sever 2003 version R2?R2 is actually shipping as a 'new' Microsoft product- it's basically an interim update to Windows Server ( http://www.microsoft.com/windowsserver2003/r2/whatsnewinr2.msp x http://www.microsoft.com/windowsserver2003/r2/whatsnewinr2.mspx ).Not that I know of, but it's been on my list of things to try :-) I've installed Postgres on other versions of Windows with no problem, so I'm afraid that the error I'm seeing now is related to some great new 'feature' from Microsoft.Here's the error message returned by Postgres before install begins- Error binding the test network socket: 10013That error means: An attempt was made to access a socket in a wayforbidden by its access permissions. Microsoft Antispyware has been turned off (closed the application) and Windows Firewall isn't running.There's no other AV or firewall software on this system yet. Any ideas on what might be going on?It certainly *sounds* a lot like a firewall issue :-) I'd double and triple check that. We've seen it several times before and it has AFAIKalways been a firewall.//Magnus
Re: [GENERAL] mac os x compile failure
On Jan 22, 2006, at 3:10 , Tom Lane wrote: That's odd --- AFAIR I've never had trouble building bison on my OS X laptop. What happens when you try? I neglected to take any notes. I remember it was complaining about muscle something-or-other during make. One workaround would be to check out from CVS and then drop in the bison output files from the 8.1.2 release tarball. I'll give that a shot when I get back to that machine, probably this coming weekend. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Quoted NULLs with COPY FROM
I need to load CSV files that have quotes in data fields that I want to map to NULLs in the destination table. So if I see ...,,... that needs to be mapped to a NULL (in an INTEGER field in this particular case). Are there any COPY command options that can do that? It seems that PgSQL COPY expects the NULL to be always unquoted. There is an option (FORCE NOT NULL) for doing the opposite. How do I specify that the NULLs are quoted? I am on 8.0.5. George ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Does this look ethical to you?
I was doing a search on Google and found this link on Navicat's web page http://pgsql.navicat.com/PG_Lightning_Admin/index.php I am kind of ticked off that they are hijacking my product name this way. This isn't really postgresal related but I was just wondering what others thought about this. Thanks, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Quoted NULLs with COPY FROM
George Pavlov wrote: I need to load CSV files that have quotes in data fields that I want to map to NULLs in the destination table. So if I see ...,,... that needs to be mapped to a NULL (in an INTEGER field in this particular case). Are there any COPY command options that can do that? It seems that PgSQL COPY expects the NULL to be always unquoted. There is an option (FORCE NOT NULL) for doing the opposite. How do I specify that the NULLs are quoted? I am on 8.0.5. Did you try NULL AS ''? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does this look ethical to you?
Tony Caduto wrote: I was doing a search on Google and found this link on Navicat's web page http://pgsql.navicat.com/PG_Lightning_Admin/index.php I am kind of ticked off that they are hijacking my product name this way. This isn't really postgresal related but I was just wondering what others thought about this. I don't have a problem with it, especially since they mention it is their version of PostgreSQL (they use our name). -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Does this look ethical to you?
Tony Caduto wrote: I was doing a search on Google and found this link on Navicat's web page http://pgsql.navicat.com/PG_Lightning_Admin/index.php I am kind of ticked off that they are hijacking my product name this way. This isn't really postgresal related but I was just wondering what others thought about this. Well IMHO it is PostgreSQL related as you are both offering a PostgreSQL product. It is not uncommon to do something like this as a keyword on Google Adwords or something but to place a direct URL I would call it fairly shady. I am guessing that by doing so they are getting placement within search engines or something. Sincerely, Joshua D. Drake Thanks, ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Does this look ethical to you?
Bruce Momjian wrote: Tony Caduto wrote: I was doing a search on Google and found this link on Navicat's web page http://pgsql.navicat.com/PG_Lightning_Admin/index.php I am kind of ticked off that they are hijacking my product name this way. This isn't really postgresal related but I was just wondering what others thought about this. I don't have a problem with it, especially since they mention it is their version of PostgreSQL (they use our name). I might be misunderstanding your point Bruce but I believe Tony's problem is not that they are using PostgreSQL's name but they are using Pg Lightning in their name which is Tony's product not Navicats.. Think about as if Command Prompt would to start using SRAAPowergres in their URL's.. At least that I how I read it. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does this look ethical to you?
Joshua D. Drake wrote: Bruce Momjian wrote: Tony Caduto wrote: I was doing a search on Google and found this link on Navicat's web page http://pgsql.navicat.com/PG_Lightning_Admin/index.php I am kind of ticked off that they are hijacking my product name this way. This isn't really postgresal related but I was just wondering what others thought about this. I don't have a problem with it, especially since they mention it is their version of PostgreSQL (they use our name). I might be misunderstanding your point Bruce but I believe Tony's problem is not that they are using PostgreSQL's name but they are using Pg Lightning in their name which is Tony's product not Navicats.. Think about as if Command Prompt would to start using SRAAPowergres in their URL's.. At least that I how I read it. Oh, I thought they were shipping PG lighening admin too. The URL no longer works so I wonder if they thought better of the practice. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Quoted NULLs with COPY FROM
I need to load CSV files that have quotes in data fields that I want to map to NULLs in the destination table. So if I see ...,,... that needs to be mapped to a NULL (in an INTEGER field in this particular case). Are there any COPY command options that can do that? It seems that PgSQL COPY expects the NULL to be always unquoted. There is an option (FORCE NOT NULL) for doing the opposite. How do I specify that the NULLs are quoted? I am on 8.0.5. Did you try NULL AS ''? yes i did. that is the default and does not change the outcome (same errors about trying to insert an empty string into a numeric field. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Does this look ethical to you?
Joshua D. Drake wrote: Tony Caduto wrote: I would call it fairly shady. I am guessing that by doing so they are getting placement within search engines or something. Hi Joshua, Yep, that's the part that I don't like. Using Postgresql in there is one thing as it's good because you have to have Postgresql in order to use any admin program. I once by mistake used Instant Messenger in a title on one of my pages and had links that had Instant Messenger and AOL had a cow and made me remove the links. I don't see how Navicat using PG Lightning Admin in their links and title is any different. They must be threatend by my product :-) I did ask them nicely to remove the link, we shall see. Thanks for the input. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does this look ethical to you?
Tony. I was doing a search on Google and found this link on Navicat's web page http://pgsql.navicat.com/PG_Lightning_Admin/index.php I am kind of ticked off that they are hijacking my product name this way. Well, technically they aren't hijacking your product name, they are hijacking some of your potential users by putting your product name in the page title and page url and saying they have a replacement that's better. It sucks. However, there are some positives. They didn't put your product name in the page keywords. It makes them look bad, sort of like when politicians sling mud. I make it a point NEVER to vote for a candidate that does that. (Obviously I vote for a lot of Independents). They are giving your product some extra credence by even using it as a measuring stick to theirs. But it still sucks.. Occasionally I find somebody stealing my Musicians Classified Listings and I've had the same ticked off feeling. In the long run I just grin and take it (and do what I can to make it harder for other sites to steal listings, but I can't think how you could do something like that in this situation). Maybe if you take the high road you could work something out that would help you both? Well, good luck with it brew == Strange Brew ([EMAIL PROTECTED]) Check out my Stock Option Covered Call website http://www.callpix.com and my Musician's Online Database Exchange http://www.TheMode.com == ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Does this look ethical to you?
Bruce Momjian wrote: Oh, I thought they were shipping PG lighening admin too. The URL no longer works so I wonder if they thought better of the practice. No, PG Lightning Admin is my product name, and I think they are doing something with a link to my page in their page because my page is down now also , something is really fishy with Navicat. Seems odd that my ISP is down and their page has a input problem...hmmm I did send them a email about it about 1/2 hour ago, so maybe they are in the process of fixing this issue. Thanks for your input on this guys. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Does this look ethical to you?
Tony. Maybe if you take the high road you could work something out that would help you both? Well, since the page is gone already I'd say both parties seem to be playing fair. Good deal! brew == Strange Brew ([EMAIL PROTECTED]) Check out my Stock Option Covered Call website http://www.callpix.com and my Musician's Online Database Exchange http://www.TheMode.com == ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Does this look ethical to you?
they have the same kind of page setup for pg Admin: http://pgsql.navicat.com/PG_Admin/index.php this one renders... both pages seem to tell robots not to cache them, so can't view a cached view on google. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Does this look ethical to you?
[EMAIL PROTECTED] wrote: Well, since the page is gone already I'd say both parties seem to be playing fair. Good deal! brew I don't know about that, the link is still there, it just gives a error of No input file specified, which just indicates the index.php in http://pgsql.navicat.com/PG_Lightning_Admin/ is gone or having a problem. I asked them nicely to remove it within 24 hours, so we shall see. Thanks again for the input. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Does this look ethical to you?
I don't know about that, the link is still there, it just gives a error of No input file specified, which just indicates the index.php in http://pgsql.navicat.com/PG_Lightning_Admin/ is gone or having a problem. I asked them nicely to remove it within 24 hours, so we shall see. Well the could just have a generic url rewriter Thanks again for the input. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Does this look ethical to you?
George Pavlov wrote: they have the same kind of page setup for pg Admin: http://pgsql.navicat.com/PG_Admin/index.php this one renders... both pages seem to tell robots not to cache them, so can't view a cached view on google. At least PG Admin is free software and doing that is not really hurting anyone since (at least I don't think) no one is making money off of PG Admin III. I do mention my product as a PG Admin III alternative but I would never use Navicat or EMS PG Manager by name anywhere on my site. I use words like Other Companies etc. I would like to know how they get such good placment on all the search engines, you do a search on Postgresql GUI admin and all you get on the first page is their stuff. Later, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Does this look ethical to you?
the blue links at the top (as it states in the top right) are sponsor'd links ... they pay for those to be there, and, I'd imagine, pay quite heavily :( On Mon, 23 Jan 2006, Tony Caduto wrote: George Pavlov wrote: they have the same kind of page setup for pg Admin: http://pgsql.navicat.com/PG_Admin/index.php this one renders... both pages seem to tell robots not to cache them, so can't view a cached view on google. At least PG Admin is free software and doing that is not really hurting anyone since (at least I don't think) no one is making money off of PG Admin III. I do mention my product as a PG Admin III alternative but I would never use Navicat or EMS PG Manager by name anywhere on my site. I use words like Other Companies etc. I would like to know how they get such good placment on all the search engines, you do a search on Postgresql GUI admin and all you get on the first page is their stuff. Later, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] FATAL: terminating connection due to administrator command
Title: RE: [GENERAL] FATAL: terminating connection due to administrator command The exact message i saw is this: LOG: received fast shutdown request LOG: aborting any active transactions FATAL: terminating connection due to administrator command so does this mean that someone is trying to stop postmaster by sending it a kill signal? i also have these questions: 1. many times i have seen two instances of postmaster running. how does that happen and how to prevent it from happening? 2. into the logfile (which i specify at the time of starting postmaster), i want to add timestamps, for each log/warning etc. How do i do that? thanks, regards Surabhi -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Fri 1/13/2006 8:56 PM To: surabhi.ahuja Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] FATAL: terminating connection due to administrator command *** Your mail has been scanned by iiitb VirusWall. ***-*** surabhi.ahuja [EMAIL PROTECTED] writes: why is it coming: FATAL: terminating connection due to administrator command Something sent the backend a SIGTERM signal. With no more information than that, it's difficult to say more. regards, tom lane
Re: hardware checks (was Re: [GENERAL] invalid memory alloc request size)
Tom Lane [EMAIL PROTECTED] writes: Janning Vygen [EMAIL PROTECTED] writes: one more question: You mentioned standard disk and memory checks. Can you point to some link where i can find more about it or which software do you mean? I guess i have to start looking at it. The stuff I've heard recommended is memtest86 for memory checks and badblocks for disk checks. But perhaps someone on the list has better ideas. I second memtest86, though even the author says memory errors can be tricksy things. Sometimes a large compile finds memory errors that even memtest86 doesn't find (the symptom is gcc crashing). However I fear using badblocks alone is pretty useless these days. Modern IDE drives detect bad blocks and remap them to other locations. If you just use badblocks you'll see mysterious errors that disappear or might not see any errors at all. You need to use tools like smartctl to query the drive's SMART firmware about errors. It's not easy to interpret but if you watch the numbers for a while you can tell if a drive is going bad and continually remapping bad blocks. badblocks is useful still as a way of ensuring that every block is read and written to, but then you have to look at the SMART data to see what happened. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
