Re: [GENERAL] DBD::Pg/perl question, kind of...
Neal Clark wrote:
> my $sth = $dbh->prepare(qq{SOME_QUERY});
> $sth->execute;
> while (my $href = $sth->fetchrow_hashref) {
> # do stuff
> }
>
[...]
>
> So with mysql, I can just say $dbh->{'mysql-use-result'} = 1, and
> then it switches so that the fetchrow_hashref calls are actually
> fetching from the database, and I only have one row in memory at a
> time, unless I copy the reference and let it wander off somewhere
> else, or something.
>
> So all I'm really asking is, how does postgre approach the use result/
> store result issue? Can I easily process result sets that are larger
> than memory? And if it handles it similar to mysql, does it also
> cause the same table locking behaviour?
The man page of DBD::Pg says, and my experiments with tcpdump confirm:
RowCacheSize (integer)
Implemented by DBI, not used by this driver.
And more:
Cursors
Although PostgreSQL has a cursor concept, it has not been used in the
current implementation. Cursors in PostgreSQL can only be used inside
a
transaction block. Because only one transaction block at a time is
allowed, this would have implied the restriction not to use any
nested
"SELECT" statements. Hence the "execute" method fetches all data at
once into data structures located in the front-end application. This
approach must to be considered when selecting large amounts of data!
So there is no automatic way of handling it.
You will probably have to consider it in your code and use
SELECT-Statements
with a LIMIT clause.
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] %tsearch gendict snowball spanish
David, you need http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch_snowball_82.gz - patch for 8.2 release, which updates snowball API. You need it only for the new stemmers from sbowball site ! I'm not sure if it will apply for 8.1.8. Oleg On Sun, 11 Mar 2007, David Gama Rodriguez wrote: Hi everyone!! I have an implementation of tsearch2 with spanish stemmers. I updated postgres to 8.1.8 version and I was going to reinstall the tsearch2 contrib, everything was fine until I try to compile the spanish stemmers with gendict $ ./config.sh -n sp -s -p spanish_ISO_8859_1 -v -C'Snowball stemmer for Spanish' Dictname: 'sp' Snowball stemmer: yes Has init method: yes Function prefix: spanish_ISO_8859_1 Source files: stem.c Header files: stem.h Object files: stem.o dict_snowball.o Comment: 'Snowball stemmer for Spanish' Directory: ../../dict_sp Build directory... ok Build Makefile... ok Build dict_sp.sql.in... ok Copy source and header files... ok Build sub-include header... ok Build Snowball stemmer... ok Build README.sp... ok All is done after I get this error: stem.c: En la funci?n 'spanish_ISO_8859_1_close_env': stem.c:1092: error: demasiados argumentos para la funci?n 'SN_close_env' make: *** [stem.o] Error 1 error: too many arguments to function 'SN_close_env' So I search this error on the list and I see some posts related. One of this posts says that I have to patch to get an updated snowball API. I download the patch from: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/gin_tsearch2_81.gz and I apply the patch this way $ cd PG_SRC/ $ patch -b -p0 < gin_tsearch2_81 Everything going fine and recompile tsearch2 So I try to compile the stemmer again $ ./config.sh -n sp -s -p spanish_ISO_8859_1 -v -C'Snowball stemmer for Spanish' $ cd ../../dict_sp $ make stem.c: En la funci?n 'spanish_ISO_8859_1_close_env': stem.c:1092: error: demasiados argumentos para la funci?n 'SN_close_env' make: *** [stem.o] Error 1 And again I have the same error: too many arguments. So my question is why after I apply the patch I have the same error? What did I do wrong? I take some paths to solve this issue I post here which finally works for me: 1.- Download Postgresql-8.1.8 sources 2.- Download the C implementation of snowball http://snowball.tartarus.org/dist/libstemmer_c.tgz 3.- Download the patch to update Snowball API http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/gin_tsearch2_81.gz 4.- Unpack postgresql sources 5.- Unpack Snowball C 6.- Unpack the patch 7.- Apply patch with: $ cp gin_tsearch2_81 PG_SRC/ $ cd PG_SRC $ patch -b -p0 < gin_tsearch2_81 8.- Do configure $ ./configure 9.- Copy the Snowball API to Tsearch2 dir $ cp libstemmer_c/runtime/* PG_SRC/contrib/tsearch2/snowball/ 10.- Copy english and russian stemmers $ cp stem_ISO_8859_1_english.c PG_SRC/contrib/tsearch2/snowball/english_stem.c $ cp stem_ISO_8859_1_english.h PG_SRC/contrib/tsearch2/snowball/english_stem.h $ cp stem_KOI8_R_russian.c PG_SRC/contrib/tsearch2/snowball/russian_stem.c $ cp stem_KOI8_R_russian.h PG_SRC/contrib/tsearch2/snowball/russian_stem.h $ cp stem_UTF_8_russian.h PG_SRC/contrib/tsearch2/snowball/russian_stem_UTF8.h $ cp stem_UTF_8_russian.c PG_SRC/contrib/tsearch2/snowball/russian_stem_UTF8.c 11.- Change in english_stem.c, russian_stem.c, rusian_stem_UTF8.c the line with: #include "../untime/header.h" to: #include "header.h" 12.- Compile tsearch2 $ make $ make install 13.- Copy spanish stemmer $ cp libstemmer_c/src_c/stem_ISO_8859_1_spanish.c PG_SRC/contrib/tsearch2/gendict/stem.c $ cp libstemmer_c/src_c/stem_ISO_8859_1_spanish.h PG_SRC/contrib/tsearch2/gendict/stem.h 14.- Go to gendict directory and do the same sustitution in step 11 with stem.c file 15.- Do: $ ./config.sh -n sp -s -p spanish_ISO_8859_1 -v -C'Snowball stemmer for Spanish' 16.- Go to ../../dict_sp and compile $ make And have no errors finally this works, I have many doubts related with this way of add tsearch2 and snowball spanish like: Is safe to add this to a DB in production? This compilation it's "fine" I mean it's correct?
Re: [GENERAL] Tsearch2 / Create rule on select
Le samedi 10 mars 2007 06:32, Oleg Bartunov a écrit : > On Fri, 9 Mar 2007, Jean-Michel Pour? wrote: > > Le vendredi 09 mars 2007 ЪЪ 10:58 +0100, Magnus Hagander a ЪЪcrit : > >> No idea. Assuming you want to do it beforehand. otherwise, just create > >> the index and see how large it got? > > > > Thank you for your comments. I will add TSeach2 support to phpBB 3.x > > soon. > > > > I had incredible response time on simple queries on more than plain-text > > 200.000 row. About 1 millisecond! Incredible but true. > > > > explain analyse verbose SELECT * FROM phpbb_posts_text WHERE idxfti @@ > > 'jmp'::tsquery limit 100; > > > > Limit (cost=0.00..444.47 rows=100 width=934) (actual time=0.046..0.824 > > rows=100 loops=1) > > -> Index Scan using idxfti_idx on phpbb_posts_text > > (cost=0.00..1053.38 rows=237 width=934) (actual time=0.040..0.418 > > rows=100 loops=1) > > Index Cond: (idxfti @@ '''jmp'''::tsquery) > > [color=red]Total runtime: 1.068 ms > > > > TSearch2 will be used by wikimedia shortly (it is supported in beta > > version). > > I've already seen tsearch2 in 1.9.X release unfortunely, rev 20094 wich start to correctly handle tsearch2 operator in query parser is not in the 1.9.3 mediawiki svn tag but in the branch and trunk. > > > IMHO, it would be better if PostgreSQL parser was able to find Tseach > > indexes alone, rewritting the query automatically. Maybe it is on the > > radar list of hackers. > > btw, we're working on new FTS feature of PostgreSQL, it's basically > tsearch integrated into the pg core. But it has some new features and > new SQL commands for configuring of FTS, so if the matter is not pressing > I'd recommend to check http://mira.sai.msu.su/~megera/pgsql/ftsdoc > > > Kind regards, > > Jean-Michel > > > > > > > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq ---(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] Getting Text data as C string
Hi.
I wrote a simple c stored procedure that accepts only one text parameter
that i want to convert to a c string.
The problem is that i obtain the C string correctly but with spurious
characters at the end... I use these calls to obtain the c string:
text *t_myfield = PG_GETARG_TEXT_P(0);
char *str_myfield = VARDATA(t_myfield);
Here it is the source:
#include "postgres.h" /* general Postgres declarations */
#include "fmgr.h" /* for argument/result macros */
#include "executor/executor.h" /* for GetAttributeByName() */
#include
#include "libpq/pqformat.h" /* needed for send/recv functions */
Datum test_text(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(test_text);
Datum test_text(PG_FUNCTION_ARGS) {
text *t_myfield = PG_GETARG_TEXT_P(0);
char *str_myfield = VARDATA(t_myfield);
elog(NOTICE,"(%s)",str_myfield);
PG_RETURN_TEXT_P(t_myfield);
}
This is the sql to create the function:
CREATE or replace FUNCTION test_text(text)
RETURNS text
AS '/your_path_to_sp.so/sp.so'
LANGUAGE C IMMUTABLE STRICT;
The output is (note the trailing spurious character in the NOTICE line):
NOTICE: (test12345678?)
test_text
--
test12345678
I expected:
NOTICE: (test12345678)
test_text
--
test12345678
Where is the error?
Thank you,
Denis
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] DBD::Pg/perl question, kind of...
"Albe Laurenz" <[EMAIL PROTECTED]> writes: > So there is no automatic way of handling it. > > You will probably have to consider it in your code and use > SELECT-Statements > with a LIMIT clause. Either that, or explicitly DECLARE a CURSOR and use FETCH from that cursor in batches. You can do this in Perl even though the automatic cursor support is not yet there. You are restricted to staying in a transaction while the cursor is open, so if you want to work outside of transactions LIMIT/OFFSET is your only way. -Doug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Is This A Set Based Solution?
On Sat, 10 Mar 2007 00:37:08 -0500, [EMAIL PROTECTED] (Tom Lane) wrote: in <[EMAIL PROTECTED]> >Stefan Berglund <[EMAIL PROTECTED]> writes: >> Below is a small test case that illustrates what I'm attempting which is >> to provide a comma separated list of numbers to a procedure which >> subsequently uses this list in a join with another table. > >> My questions are is this a set based solution and is this the best >> approach in terms of using the data types and methods afforded by >> PostgreSQL? I'm mostly inquiring about the double FOR loop which just >> doesn't feel right to me ... > >It looks pretty ugly to me too, but you haven't explained your problem >clearly enough for anyone to be able to recommend a better solution path. >Why do you feel you need to do this? What is the context? I've been lurking for several thousand posts and I'm flattered that you've responded but I'm also a little flustered that I failed to communicate so I'll try again. In SQL Server I was able to pass a string of IDs such as '1,5,3' to a procedure that would create a set of tuples where each tuple was one of those ids: Row 1 : 1 Row 2: 5 Row 3: 3 I could then use this table in a join with another table. What I want to do is to create a function that takes a comma separated string of numbers and produces a table (where each row is one of those numbers) that can be joined to other tables as in the example first provided. fn_Split_List is supposed to take a list of numbers and return a table of rows of those numbers. I hope this better explains what I'm trying to do but somehow from your reaction I get the feeling that I'm missing something really basic? --- This posting is provided "AS IS" with no warranties and no guarantees either express or implied. Stefan Berglund ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query ... returned 4 columns
I am running 8.1.4 and the o.* notation works. Thanks again, Sorin Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091 ---(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] Is This A Set Based Solution?
Hi-
Below is a small test case that illustrates what I'm attempting which is
to provide a comma separated list of numbers to a procedure which
subsequently uses this list in a join with another table.
My questions are is this a set based solution and is this the best
approach in terms of using the data types and methods afforded by
PostgreSQL? I'm mostly inquiring about the double FOR loop which just
doesn't feel right to me and I'd also like to feel that I'm generally on
the right track before converting the other 400 procedures from SQL
Server 2000 to PostgreSQL.
CREATE TYPE fn_return_int4 AS (N int);
CREATE TABLE test_table (
id SMALLINT not null,
tname varchar(50) not null);
INSERT INTO test_table
SELECT 1, 'Adams'
UNION SELECT 2, 'Baker'
UNION SELECT 3, 'Chrysler'
UNION SELECT 4, 'Douglas'
UNION SELECT 5, 'Everyman';
CREATE OR REPLACE FUNCTION fn_Split_List (
pList TEXT) RETURNS SETOF fn_return_int4 AS $fn_Split_List$
DECLARE
v_row fn_return_int4%rowtype;
v_list alias for $1;
v_delim text := ',';
v_arr text[];
BEGIN
v_arr := string_to_array(v_list, v_delim);
FOR i IN array_lower(v_arr, 1)..array_upper(v_arr, 1) LOOP
FOR v_row IN SELECT v_arr[i] LOOP
RETURN NEXT v_row;
END LOOP;
END LOOP;
RETURN;
END;
$fn_Split_List$ LANGUAGE plpgsql;
SELECT *
FROM
fn_Split_List('5,1,3') SL INNER JOIN
test_table T ON SL.N=T.ID;
I did discover that I was able to define the function with a native type
but then the usage looked a little odd:
SELECT *
FROM
fn_Split_List('5,1,3') SL INNER JOIN
test_table T ON SL=T.ID;
---
Stefan Berglund
---(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] Tsearch2 / Create rule on select
Le samedi 10 mars 2007 à 08:32 +0300, Oleg Bartunov a écrit : > btw, we're working on new FTS feature of PostgreSQL, it's basically > tsearch integrated into the pg core. But it has some new features and > new SQL commands for configuring of FTS, so if the matter is not > pressing I'd recommend > to check http://mira.sai.msu.su/~megera/pgsql/ftsdoc Great. Thank you very much Oleg. As suggested, I will play with tsearch_core-0.37.gz patch. After testing first, I might be interested in using tseach_core on a production database. It is a community forum with 200.000 messages. tseach-core would only be used for searching messages. There is no money at stake, the database is backed-up twice a day using cron jobs. Whenever a bug happens, are there chances that PostgreSQL database gets perverted? On only the parser? If there is no chance to destroy the database, I may be interested in using Tsearch-core in production, quickly. When will tsearch-core become part of CVS-head? Kind regards, Jean-Michel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is This A Set Based Solution?
On Sat, 10 Mar 2007 08:26:32 +0300 (MSK), oleg@sai.msu.su (Oleg Bartunov) wrote: in <[EMAIL PROTECTED]> >I don't know if you could change your schema. but I'd consider your >problem as a overlapping arrays task and use contrib/intarray for that. > >Oleg I can very definitely change my schema at this point. I'm refactoring an application from SQL Server to PostgreSQL and I'm doing a lot of exploring trying to find the best fits. I'll see what I can glean from you've indicated but that sounds like what I'm looking for. The string converts easily to an array but then what's the best way to get from an array to a table? --- This posting is provided "AS IS" with no warranties and no guarantees either express or implied. Stefan Berglund ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Getting Text data as C string
Am Montag, 12. März 2007 12:47 schrieb Denis Gasparin: > I wrote a simple c stored procedure that accepts only one text parameter > that i want to convert to a c string. > The problem is that i obtain the C string correctly but with spurious > characters at the end... I use these calls to obtain the c string: The data in a text datum is not null terminated. You need to call the function textout() to convert. Grep the source code for examples of invoking it. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL crashes on Windows 2003
Hi, The PostgreSQL of a customer database (running on Windows 2003) crashed and wrote the messages below in the logfile. According to the customer the drives are working OK, partitions are not full and nothing has changed in the permissions. The customer could restart the PostgreSQL service manually, without resorting to a backup. Could this be the result of not enough check_point segments? 2007-03-08 22:36:26 ERROR: could not read block 15894 of relation 1663/3227318/3227439: Permission denied 2007-03-08 22:36:26 STATEMENT: UPDATE deliver_cares SET org_personnel_id=$1, spent_time=$2, status=$3, when_registered=$4, actively_registered=$5 WHERE deliver_care_id=$6 2007-03-08 22:37:31 ERROR: could not write block 8 of relation 1663/3227318/4689678: Permission denied 2007-03-08 22:37:31 CONTEXT: writing block 8 of relation 1663/3227318/4689678 2007-03-08 22:38:57 FATAL: could not read from statistics collector pipe: No error 2007-03-08 22:38:57 FATAL: could not write to statistics collector pipe: No connection could be made because the target machine actively refused it. 2007-03-08 22:40:00 PANIC: could not write to log file 9, segment 219 at offset 15073280, length 24576: Permission denied 2007-03-08 22:40:00 STATEMENT: COMMIT This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2007-03-08 22:40:00 LOG: server process (PID 20508) was terminated by signal 3 2007-03-08 22:40:00 LOG: terminating any other active server processes 2007-03-08 22:40:00 WARNING: terminating connection because of crash of another server process 2007-03-08 22:40:00 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. 2007-03-08 22:40:00 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-03-08 22:40:00 WARNING: terminating connection because of crash of another server process 2007-03-08 22:40:00 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. 2007-03-08 22:40:00 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-03-08 22:40:01 LOG: all server processes terminated; reinitializing 2007-03-08 22:40:01 LOG: database system was interrupted at 2007-03-08 22:36:24 W. Europe Standard Time 2007-03-08 22:40:01 LOG: could not read from log file 9, segment 219 at offset 14876672: Permission denied 2007-03-08 22:40:01 LOG: invalid primary checkpoint record 2007-03-08 22:40:01 LOG: could not read from log file 9, segment 219 at offset 14721024: Permission denied 2007-03-08 22:40:01 LOG: invalid secondary checkpoint record 2007-03-08 22:40:01 PANIC: could not locate a valid checkpoint record This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2007-03-08 22:40:01 LOG: startup process (PID 20504) was terminated by signal 3 2007-03-08 22:40:01 LOG: aborting startup due to startup process failure 2007-03-08 22:40:01 LOG: logger shutting down TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL crashes on Windows 2003
On Mon, Mar 12, 2007 at 02:20:44PM +0100, Joost Kraaijeveld wrote: > Hi, > > The PostgreSQL of a customer database (running on Windows 2003) crashed > and wrote the messages below in the logfile. According to the customer > the drives are working OK, partitions are not full and nothing has > changed in the permissions. The customer could restart the PostgreSQL > service manually, without resorting to a backup. What version of PostgreSQL is this? //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL crashes on Windows 2003
On Mon, 2007-03-12 at 14:39 +0100, Magnus Hagander wrote: > What version of PostgreSQL is this? > Oh, forgotten: 8.1.x with no upgrade option (company policy) -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(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] PostgreSQL crashes on Windows 2003
On Mon, Mar 12, 2007 at 02:46:22PM +0100, Joost Kraaijeveld wrote: > On Mon, 2007-03-12 at 14:39 +0100, Magnus Hagander wrote: > > > What version of PostgreSQL is this? > > > Oh, forgotten: 8.1.x with no upgrade option (company policy) It looks to me that you may be a victim of the bug patched in http://archives.postgresql.org/pgsql-committers/2007-01/msg00214.php. Tom, whatever happened about a backpatch for that one? Something you think is going to happen? //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Getting Text data as C string
Peter Eisentraut ha scritto: > Am Montag, 12. März 2007 12:47 schrieb Denis Gasparin: > >> I wrote a simple c stored procedure that accepts only one text parameter >> that i want to convert to a c string. >> The problem is that i obtain the C string correctly but with spurious >> characters at the end... I use these calls to obtain the c string: >> > > The data in a text datum is not null terminated. You need to call the > function textout() to convert. Grep the source code for examples of invoking > it. > > I found these defines into the contrib section: #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp))) #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp))) So i can safely use them to obtain a string pointer from a text pointer and viceversa. I tried and all seemed to work ok. Thank you, Denis
Re: [GENERAL] DBD::Pg/perl question, kind of...
On Mon, Mar 12, 2007 at 08:38:52AM -0400, Douglas McNaught wrote: > You are restricted to staying in a transaction while the cursor is > open, so if you want to work outside of transactions LIMIT/OFFSET > is your only way. http://www.postgresql.org/docs/8.2/interactive/sql-declare.html "If WITH HOLD is specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session. (But if the creating transaction is aborted, the cursor is removed.) A cursor created with WITH HOLD is closed when an explicit CLOSE command is issued on it, or the session ends. In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions." -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL crashes on Windows 2003
Magnus Hagander <[EMAIL PROTECTED]> writes: > It looks to me that you may be a victim of the bug patched in > http://archives.postgresql.org/pgsql-committers/2007-01/msg00214.php. > Tom, whatever happened about a backpatch for that one? It's applied. http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/bgwriter.c regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL crashes on Windows 2003
On Mon, 2007-03-12 at 10:13 -0400, Tom Lane wrote: > Magnus Hagander <[EMAIL PROTECTED]> writes: > > It looks to me that you may be a victim of the bug patched in > > http://archives.postgresql.org/pgsql-committers/2007-01/msg00214.php. > > Tom, whatever happened about a backpatch for that one? > > It's applied. > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/bgwriter.c Thanks, to which version of PostgreSQL is that patch applied? 8.1.7 and higher ? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL crashes on Windows 2003
On Mon, Mar 12, 2007 at 10:13:48AM -0400, Tom Lane wrote: > Magnus Hagander <[EMAIL PROTECTED]> writes: > > It looks to me that you may be a victim of the bug patched in > > http://archives.postgresql.org/pgsql-committers/2007-01/msg00214.php. > > Tom, whatever happened about a backpatch for that one? > > It's applied. > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/bgwriter.c Bah. I looked aruond but didn't find it. Clearly I'm blind. Thanks. So - if you're not on 8.1.7 or 8.1.8, upgrade to 8.1.8 and let us know if you still have the problem. //Magnus ---(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] Is This A Set Based Solution?
Stefan Berglund <[EMAIL PROTECTED]> writes: > On Sat, 10 Mar 2007 00:37:08 -0500, [EMAIL PROTECTED] (Tom Lane) wrote: >> It looks pretty ugly to me too, but you haven't explained your problem >> clearly enough for anyone to be able to recommend a better solution path. >> Why do you feel you need to do this? What is the context? > What I want to do is to create a function that takes a comma separated > string of numbers and produces a table (where each row is one of those > numbers) that can be joined to other tables as in the example first > provided. That was what you said before. The question is why you need to do that. It strikes me that having such a requirement is a symptom of poor data representation choices. Perhaps an array would be better, or maybe you ought to refactor your table layout altogether. But, as I said, you haven't provided any info that would let someone give advice at that level. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] ALTER column TYPE varying question
I need to increase the length of a string field using version 8.1 I was thinking to use ALTER TABLE since now altering a column type should be supported by pg. The column is currently varying(60) and I want to have it varying(120) After executing ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(120) I can see the column definition correctly changes and I can insert rows with longer data in mycolumn. But when I try to update data which were in the table before the ALTER TABLE I get "ERROR: value too long for type character varying(60)" It's like the old rows didn't update correctly keeping the old maximum length. I can see there's an optional USING clause for the ALTER TYPE, but is not really clear to me what should i add in this case, since basically I'd need no convertion... Thanks Paolo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL crashes on Windows 2003
On Mon, 2007-03-12 at 15:37 +0100, Magnus Hagander wrote: > On Mon, Mar 12, 2007 at 10:13:48AM -0400, Tom Lane wrote: > > Magnus Hagander <[EMAIL PROTECTED]> writes: > > > It looks to me that you may be a victim of the bug patched in > > > > http://archives.postgresql.org/pgsql-committers/2007-01/msg00214.php. > > > Tom, whatever happened about a backpatch for that one? > > > > It's applied. > > > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/bgwriter.c > > Bah. I looked aruond but didn't find it. Clearly I'm blind. Thanks. > > So - if you're not on 8.1.7 or 8.1.8, upgrade to 8.1.8 and let us know > if you still have the problem. I wrote 8.1.x but we are actually on 8.1.8 and the problem is there (I just checked because I am not the local system administrator). -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] finding a column by name in psql
Hi everyone, I'm now used to using psql to manage my databases into a terminal, and I found the very convenient "\g |" command that pipes query result in any shell tool I want. But I still have hard time finding some columns in some tables (that may have 300+ cols). I would like to pipe the result of "\d" mytable in some grep but it doesn't work. I have done it once or twice by SELECTing pg_attrib but this requires a lot of typing. I tried to store a procedure but apparently I don't have any language allowed... I'm sure it could do it but and I wondering if I missed some simpler command that would allow me to conveniently search into database structure. i.e. something like "\d mytable *_ts" that could display all cols in mytable ending with "_ts". Any advice ? I'm still on postgresql 7 and will upgrade for new projects. Guillaume ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] finding a column by name in psql
On 3/12/07, Guillaume Bog <[EMAIL PROTECTED]> wrote: Hi everyone, I'm now used to using psql to manage my databases into a terminal, and I found the very convenient "\g |" command that pipes query result in any shell tool I want. But I still have hard time finding some columns in some tables (that may have 300+ cols). I would like to pipe the result of "\d" mytable in some grep but it doesn't work. I have done it once or twice by SELECTing pg_attrib but this requires a lot of typing. I tried to store a procedure but apparently I don't have any language allowed... I'm sure it could do it but and I wondering if I missed some simpler command that would allow me to conveniently search into database structure. i.e. something like "\d mytable *_ts" that could display all cols in mytable ending with "_ts". Have you tried configuring your pager? I use: env: PAGER=less LESS='-iMSx4 -FX' psql: /pset pager=always this allows for spreadsheet style navigation of psql results and you can use search feature of less (slash) to highlight columns/data. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Tracking disk writes? (again)
Hi, is there any way that I can track the number of actual disk writes done by a particular database or database cluster? I already sent this question last Friday and, while even I usually frown upon reposts to lists and forums, I really need some kind of answer, even if it's "you can't" in which case a why would also be cool. erik jones <[EMAIL PROTECTED]> sofware developer 615-296-0838 emma(r)
Re: [GENERAL] ALTER column TYPE varying question
"Paolo Negri" <[EMAIL PROTECTED]> writes:
> I need to increase the length of a string field using version 8.1
8.1.what?
> After executing
> ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(120)
> I can see the column definition correctly changes and I can insert
> rows with longer data in mycolumn.
> But when I try to update data which were in the table before the ALTER
> TABLE I get
> "ERROR: value too long for type character varying(60)"
Please provide a self-contained test case. It works in simple cases:
regression=# create table mytable (mycolumn varchar(60));
CREATE TABLE
regression=# insert into mytable values ('foo');
INSERT 0 1
regression=# ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(120);
ALTER TABLE
regression=# update mytable set mycolumn = repeat('x',100);
UPDATE 1
regression=#
It sounds like you have a case where the old table definition is still
reflected in a cached plan ...
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Tracking disk writes? (again)
Erik, on windows you can use performance monitor to filter down diskrequest. With sysinternals tools you can also go down to disk accesses on process basis. google for sysinternals site:microsoft.com on other platforms I have no information. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tracking disk writes? (again)
On Mar 12, 2007, at 11:22 AM, Harald Armin Massa wrote: Erik, on windows you can use performance monitor to filter down diskrequest. With sysinternals tools you can also go down to disk accesses on process basis. google for sysinternals site:microsoft.com on other platforms I have no information. Ah, good point, I should have specified some system details (although I was hoping I could get at this through postgres as I can when tracking disk reads). We have db servers running on both Ubuntu Linux and Solaris 10. erik jones <[EMAIL PROTECTED]> sofware developer 615-296-0838 emma(r)
Re: [GENERAL] Tracking disk writes? (again)
Erik, for solaris I think dtrace can be of help: http://www.opensolaris.org/os/community/dtrace/ PostgreSQL 8.2 introduced dtrace-ability. From what I read it is specifically created to do this kind of measurement. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords. ---(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] Tracking disk writes? (again)
On Mar 12, 2007, at 11:46 AM, Harald Armin Massa wrote: Erik, for solaris I think dtrace can be of help: http://www.opensolaris.org/os/community/dtrace/ PostgreSQL 8.2 introduced dtrace-ability. From what I read it is specifically created to do this kind of measurement. Hmm... That would be a particulary heavy weight solution to what I was hoping for. Basically, I'm looking to poll the db or "something" to get either the total number of block or page writes done by the db either ever or since my last reading. That way I can graph disk writes over time and compare to the graphs I've already got for transactions committed, transactios rolled back, disk blocks read from disk, and disk blocks found in cache. erik jones <[EMAIL PROTECTED]> sofware developer 615-296-0838 emma(r)
Re: [GENERAL] Tracking disk writes? (again)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/12/07 12:16, Erik Jones wrote: > > On Mar 12, 2007, at 11:46 AM, Harald Armin Massa wrote: > >> Erik, >> >> for solaris I think dtrace can be of help: >> >> http://www.opensolaris.org/os/community/dtrace/ >> >> PostgreSQL 8.2 introduced dtrace-ability. >> >> From what I read it is specifically created to do this kind of >> measurement. > > Hmm... That would be a particulary heavy weight solution to what I was > hoping for. Basically, I'm looking to poll the db or "something" to get > either the total number of block or page writes done by the db either > ever or since my last reading. That way I can graph disk writes over > time and compare to the graphs I've already got for transactions > committed, transactios rolled back, disk blocks read from disk, and disk > blocks found in cache. Like a system montitor that tracks reads, writes, transactions, etc? > > erik jones <[EMAIL PROTECTED]> > sofware developer > 615-296-0838 > emma(r) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF9ZiyS9HxQb37XmcRAiNKAKCxG7RJUEQTpyU6N6bqlllOcvUJ6wCfZh+8 7s3LprWsR2IEB3n+FwyLy58= =cPIV -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tracking disk writes? (again)
On Mon, Mar 12, 2007 at 11:14:14AM -0500, Erik Jones wrote: > Hi, is there any way that I can track the number of actual disk > writes done by a particular database or database cluster? I already > sent this question last Friday and, while even I usually frown upon > reposts to lists and forums, I really need some kind of answer, even > if it's "you can't" in which case a why would also be cool. I'm not sure it's what you mean, but the pg_stats* tables include stuff like blocks read from tables and indexes and the like... You do need to enable it in the config though. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Tsearch2 / Create rule on select
Jean-Michel Pouré wrote: > IMHO, it would be better if PostgreSQL parser was able to find Tseach > indexes alone, rewritting the query automatically. Maybe it is on the > radar list of hackers. How would you "rewrite a query automatically"? PostgreSQL will find it automatically when you attempt to do a match using the fulltext search operator, which is the only one supported by tsearch. I'm not sure what else it could do? //Magnus ---(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] daylight savings patches needed?
We have a 7.4.6 cluster which has been running on an HP B.11.00 box for quite sometime. The IT group applied daylight savings patches to the OS, but the cluster is still showing the incorrect timezone: $ psql -c "select now()" now --- 2007-03-12 13:46:11.369583-05 $ date Mon Mar 12 14:46:18 EDT 2007 It appears that newly initialized 7.4.6 clusters get the proper timezone. I didn't see anything in the FAQ about restarting. Does this mean that we need to restart these clusters in order to get the timezone updates from the OS? Are they cached in the postmaster? TIA. Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] daylight savings patches needed?
On Monday March 12 2007 1:07 pm, Ed L. wrote: > Does this mean that we need to restart these clusters in order > to get the timezone updates from the OS? Are they cached in > the postmaster? Nevermind. I just found it via googling. Would I be correct in understanding that every pre-8.0 cluster must be restarted in order for the OS changes to take affect?!? Perhaps it should be a part of the FAQ answer at http://www.postgresql.org/docs/faqs.FAQ.html#item1.14 Ed ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tracking disk writes? (again)
Martijn van Oosterhout wrote: > On Mon, Mar 12, 2007 at 11:14:14AM -0500, Erik Jones wrote: > > Hi, is there any way that I can track the number of actual disk > > writes done by a particular database or database cluster? I already > > sent this question last Friday and, while even I usually frown upon > > reposts to lists and forums, I really need some kind of answer, even > > if it's "you can't" in which case a why would also be cool. > > I'm not sure it's what you mean, but the pg_stats* tables include stuff > like blocks read from tables and indexes and the like... You do need to > enable it in the config though. But it does not include disk writes, only reads. I think it would make sense to add blocks dirtied and blocks written to the block-level stats. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] DBD::Pg/perl question, kind of...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks for all the replies everyone. Not really knowing what a cursor is, I suppose I have some work to do. I can do the SELECT/LIMIT/ OFFSET approach but that seems like kind of a headache, esp. when its hard to predict what # of rows will max out memory... I'd have to keep that number pretty small, effectively making the same exact query over and over, which sounds pretty slow. I'm not really using pgsql yet, so a lot of this is beyond me, I'm just thinking ahead as I start to migrate from mysql... Thanks again, Neal On Mar 12, 2007, at 7:09 AM, Michael Fuhr wrote: On Mon, Mar 12, 2007 at 08:38:52AM -0400, Douglas McNaught wrote: You are restricted to staying in a transaction while the cursor is open, so if you want to work outside of transactions LIMIT/OFFSET is your only way. http://www.postgresql.org/docs/8.2/interactive/sql-declare.html "If WITH HOLD is specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session. (But if the creating transaction is aborted, the cursor is removed.) A cursor created with WITH HOLD is closed when an explicit CLOSE command is issued on it, or the session ends. In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions." -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (Darwin) iD8DBQFF9ar2OUuHw4wCzDMRAtE6AKCKt226m/qql6lFGw4VkU7tRQC2ogCfebGs B47wxieD8TBK5GgAQbwDUxk= =+rwM -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tracking disk writes? (again)
On Mar 12, 2007, at 1:31 PM, Martijn van Oosterhout wrote: On Mon, Mar 12, 2007 at 11:14:14AM -0500, Erik Jones wrote: Hi, is there any way that I can track the number of actual disk writes done by a particular database or database cluster? I already sent this question last Friday and, while even I usually frown upon reposts to lists and forums, I really need some kind of answer, even if it's "you can't" in which case a why would also be cool. I'm not sure it's what you mean, but the pg_stats* tables include stuff like blocks read from tables and indexes and the like... You do need to enable it in the config though. That is exactly what I mean except that there doesn't seem to be anything in there for tracking writes done by the database. There's the number of reads from disk and reads from the cache, but nothing that I can see wrt writes to disk. erik jones <[EMAIL PROTECTED]> sofware developer 615-296-0838 emma(r)
Re: [GENERAL] Tracking disk writes? (again)
On Mar 12, 2007, at 1:15 PM, Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/12/07 12:16, Erik Jones wrote: On Mar 12, 2007, at 11:46 AM, Harald Armin Massa wrote: Erik, for solaris I think dtrace can be of help: http://www.opensolaris.org/os/community/dtrace/ PostgreSQL 8.2 introduced dtrace-ability. From what I read it is specifically created to do this kind of measurement. Hmm... That would be a particulary heavy weight solution to what I was hoping for. Basically, I'm looking to poll the db or "something" to get either the total number of block or page writes done by the db either ever or since my last reading. That way I can graph disk writes over time and compare to the graphs I've already got for transactions committed, transactios rolled back, disk blocks read from disk, and disk blocks found in cache. Like a system montitor that tracks reads, writes, transactions, etc? Well, disk reads, cache hits, transactions commited, transactions rolled back, index size and usage, etc. are all able to be tracked vi the pg catalogue tables and views. But, I haven't seen anything that will give me numbers on actual disk writes done by the database. erik jones <[EMAIL PROTECTED]> sofware developer 615-296-0838 emma(r)
Re: [GENERAL] DBD::Pg/perl question, kind of...
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Okay, I don't have any postgresql tables big enough to verify this is
doing what I think it is (namely, only keeping one row from my result
set in memory at a time), and I still don't really know much about
cursors or pg, but this appears to be doing what I want to do:
$dbh->do('BEGIN WORK;');
$dbh->do('DECLARE my_cur CURSOR FOR SELECT * FROM my_table ORDER BY
account_id;');
my $sth = $dbh->prepare(qq{FETCH FORWARD 1 FROM my_cur});
$sth->execute;
while (my $href = $sth->fetchrow_hashref) {
my $field1 = $href->{field1};
my $account_id = $href->{account_id};
## do stuff
$sth->execute;
}
$dbh->do("COMMIT WORK;");
really the only thing that doesn't look DBI-ish about it is calling
$sth->execute at the end of the while loop... like to fill up the
statement handler with the data to fetchrow_hashref on the next time
around.
comments?
On Mar 12, 2007, at 12:49 PM, A.M. wrote:
On Mar 12, 2007, at 15:33 , Neal Clark wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Thanks for all the replies everyone. Not really knowing what a
cursor is, I suppose I have some work to do. I can do the SELECT/
LIMIT/OFFSET approach but that seems like kind of a headache, esp.
when its hard to predict what # of rows will max out memory... I'd
have to keep that number pretty small, effectively making the same
exact query over and over, which sounds pretty slow.
I'm not really using pgsql yet, so a lot of this is beyond me, I'm
just thinking ahead as I start to migrate from mysql...
Don't use LIMIT/OFFSET. The cursor is exactly what you want. A
cursor effectively caches the query results on the server side and
feeds the results to the client on demand.
Cheers,
M
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.3 (Darwin)
iD8DBQFF9bhGOUuHw4wCzDMRAsfsAKCt+mtj0ITygdzenTCEZSA/1UibHwCgqPVe
rKEOlx3dCWD50C2kQ7nzhRc=
=RUbR
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?
Bradley Kieser wrote: I hope that someone has cracked this one because I have run into a brick wall the entire week and after 3 all-nighters with bad installations, I would appreciate hearing from others! I am looking for a decent OpenSource CRM system that will run with Postgres. SugarCRM seems to be the most popular but it's MySQL-centric and its opensource parts are very restricted. So if anyone has actually cracked this, please let me know! I really need a good CRM. It has to be OpenSource, not just out of principle, but we need to integrate it into an existing business with established inhouse software so we need to be able to customise the code. Thanks, Brad A coworker of mine (Ryley Breiddal) did some coding and testing for the SugarCRM PostgreSQL port, in conjunction with Jason Felice. We have been running it for a few months with no major problems. http://eraserhead.net/sugarsuite-pgpatch/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Installing with libs of postgresql-libs
Hi list, Could someone tell me what libs are packed in the rpm below ? postgresql-libs-8.2.3-1PGDG.i686.rpm We are concerned about the problem of compatibility with Oracle and we prefer to check all the libs to not put the Oracle system down. I think it is just a metter of rpm -qa postgresql-libs-8.2.3-1PGDG.i686.rpm but I do not have the adm permissions. Any help would be glad Ezequias ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL crashes on Windows 2003
Joost Kraaijeveld wrote: It looks to me that you may be a victim of the bug patched in >> http://archives.postgresql.org/pgsql-committers/2007-01/msg00214.php. Tom, whatever happened about a backpatch for that one? >>> It's applied. >>> >> http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/bgwriter.c >> >> Bah. I looked aruond but didn't find it. Clearly I'm blind. Thanks. >> >> So - if you're not on 8.1.7 or 8.1.8, upgrade to 8.1.8 and let us know >> if you still have the problem. > I wrote 8.1.x but we are actually on 8.1.8 and the problem is there (I > just checked because I am not the local system administrator). Just in case, I'd double-check that. Check the version information on postgres.exe (available through the right-click properties view in Explorer). It could be that the upgrade failed. If it's not then the next logical question is - any antivirus, antispyware or quota software running on that machine? //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] daylight savings patches needed?
On Mon, Mar 12, 2007 at 01:13:42PM -0600, Ed L. wrote: > Would I be correct in understanding that every pre-8.0 cluster > must be restarted in order for the OS changes to take affect?!? Possibly, I imagine many C libraries would cache the timezone data over a fork and might not motice the changes... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] daylight savings patches needed?
On Monday March 12 2007 4:08 pm, Martijn van Oosterhout wrote: > On Mon, Mar 12, 2007 at 01:13:42PM -0600, Ed L. wrote: > > Would I be correct in understanding that every pre-8.0 > > cluster must be restarted in order for the OS changes to > > take affect?!? > > Possibly, I imagine many C libraries would cache the timezone > data over a fork and might not motice the changes... That was a nasty little surprise. Thanks, Ed ---(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 with libs of postgresql-libs
Hi list, Could someone tell me what libs are packed in the rpm below ? postgresql-libs-8.2.3-1PGDG.i686.rpm We are concerned about the problem of compatibility with Oracle and we prefer to check all the libs to not put the Oracle system down. I think it is just a metter of rpm -qa postgresql-libs-8.2.3-1PGDG.i686.rpm but I do not have the adm permissions. For an uninstalled rpm, rpm -qlp PACKAGE.rpm will show you the files that the package will install. postgresql-libs puts a bunch of postgresql specific .so files in /usr/lib and some internationalisation messages in /usr/share/locale. None of which will conflict with Oracle. Cheers, Stuart. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to enforce uniqueness when NULL values are present?
On Saturday 10 March 2007, Christian Schröder wrote: > Let's assume that the values in this table are some limits that are > given for different data (identified by the id). Some of the limits are > only valid after a given date, whereas other limits are valid all the > time. How would you put this information into one or more tables? Of > course, I could use a special date to indicate that a limit is valid all > the time (e.g. 1970-01-01), but I don't think that this is better design > than representing this with a NULL value. Or I could split the data into > two different tables, one with the date column and one without. But then > I had to work with two tables with more or less the same meaning. > Wouldn't it be quite strange to model the same entities (the limits) > with two tables? - SNIP - >1. If a record with a given id and a null value in the date field > exists, no other record with the same id is allowed. >2. If multiple records with the same id exist, they must have > different values in the date field and none of them must have a > null value in this field. Seems to me that this is what you are looking for: TABLE listofids: Column | Type | Modifiers +--+--- id | integer | not null hasdates | bool| default null unique(id, hasdates) TABLE listofidsdates: Column | Type | Modifiers +--+--- listofids_id | integer | not null REFERENCES listofids(id) date | date | not null unique(listofids_id, date) When there are dates, set listofids.hasdates=null. Otherwise, set it to true. Does this seem most properly normalized? (it's how I would do it!) How could this be done better? -Ben ---(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] DBD::Pg/perl question, kind of...
Neal Clark <[EMAIL PROTECTED]> writes:
> comments?
Looks like the right idea. If you have a lot of rows to process,
you'll benefit by fetching in batches, e.g.
my $sth = $dbh->prepare(qq{FETCH FORWARD 1000 FROM my_cur});
# iterate through the result set here
-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
Re: [GENERAL] daylight savings patches needed?
Martijn van Oosterhout writes: > On Mon, Mar 12, 2007 at 01:13:42PM -0600, Ed L. wrote: >> Would I be correct in understanding that every pre-8.0 cluster >> must be restarted in order for the OS changes to take affect?!? > Possibly, I imagine many C libraries would cache the timezone data over > a fork and might not motice the changes... Postgres is hardly the only app that behaves like that. I saw a bunch of traffic on the Red Hat lists yesterday/today about cron, syslogd, etc needing restarts in various releases. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tracking disk writes? (again)
Erik Jones <[EMAIL PROTECTED]> writes: > Well, disk reads, cache hits, transactions commited, transactions > rolled back, index size and usage, etc. are all able to be tracked vi > the pg catalogue tables and views. But, I haven't seen anything that > will give me numbers on actual disk writes done by the database. One of the reasons you don't see that is that a large fraction of the writes are triggered in background by the "bgwriter" process, which operates at too low a level to participate in the stats collection mechanism. I'm not sure what would be involved in refactoring things sufficiently to make that workable, but it'd be nontrivial. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tracking disk writes? (again)
Tom Lane wrote: > Erik Jones <[EMAIL PROTECTED]> writes: > > Well, disk reads, cache hits, transactions commited, transactions > > rolled back, index size and usage, etc. are all able to be tracked vi > > the pg catalogue tables and views. But, I haven't seen anything that > > will give me numbers on actual disk writes done by the database. > > One of the reasons you don't see that is that a large fraction of the > writes are triggered in background by the "bgwriter" process, which > operates at too low a level to participate in the stats collection > mechanism. I'm not sure what would be involved in refactoring things > sufficiently to make that workable, but it'd be nontrivial. You mean that bgwriter cannot send stat messages? Keep in mind that these are block-level stats, so there's no need to peek at the page contents ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Multi DB performance test
http://devloop.org.uk/documentation/database-performance/ Any comments? Regards, Tadej
Re: [GENERAL] Tracking disk writes? (again)
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> One of the reasons you don't see that is that a large fraction of the >> writes are triggered in background by the "bgwriter" process, which >> operates at too low a level to participate in the stats collection >> mechanism. I'm not sure what would be involved in refactoring things >> sufficiently to make that workable, but it'd be nontrivial. > You mean that bgwriter cannot send stat messages? Right. The stats mechanism is attached to relcache entries, which the bgwriter doesn't have. And if it did collect stats, it would never send them because that happens in the outer postgres.c loop (it's not totally clear what would be a good granularity for sending them in bgwriter). And I think it is not a backend in the stats collector's eyes, either. Surely these things could be dealt with, but it'd take some refactoring. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tracking disk writes? (again)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/12/07 22:57, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: >> Tom Lane wrote: >>> One of the reasons you don't see that is that a large fraction of the >>> writes are triggered in background by the "bgwriter" process, which >>> operates at too low a level to participate in the stats collection >>> mechanism. I'm not sure what would be involved in refactoring things >>> sufficiently to make that workable, but it'd be nontrivial. > >> You mean that bgwriter cannot send stat messages? > > Right. The stats mechanism is attached to relcache entries, which the > bgwriter doesn't have. And if it did collect stats, it would never send > them because that happens in the outer postgres.c loop (it's not totally > clear what would be a good granularity for sending them in bgwriter). > And I think it is not a backend in the stats collector's eyes, either. > > Surely these things could be dealt with, but it'd take some refactoring. It would definitely be useful for the DBA to have the data to let him graph each tablespace's read & write activity over time. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF9ieUS9HxQb37XmcRAvcpAJ0VhUsShoGNN/Gjkm98QqQYoAxl+gCfbdXh oTauMDGbl1ZeC3GVGezBHDU= =7qNy -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Multi DB performance test
"Tadej Kanizar" <[EMAIL PROTECTED]> writes: > http://devloop.org.uk/documentation/database-performance/ > Any comments? Old news, see this thread: http://archives.postgresql.org/pgsql-general/2007-02/msg00806.php Most of us stopped reading about the place where they explained that (a) they did zero tuning on each database, and (b) they were comparing myisam to transactional engines. It's amazing how many people do not understand that database performance testing is actually a pretty hard problem in itself --- simplistic tests will almost never show you the real-world bottlenecks. regards, tom lane ---(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] Multi DB performance test
On 12.03.2007, at 22:46, Tom Lane wrote: Most of us stopped reading about the place where they explained that (a) they did zero tuning on each database, and (b) they were comparing myisam to transactional engines. What I don't like about things like that, is that some morons throw something together they call a "performance test", add some colorful graphs to it and it will be quoted for the next ten years, "decision makers" will base their recommendations on it and a lot of followers will just believe. Whether or not this applies to the referenced test - judge yourself. cug ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Incorrect "ERROR: database "xxx" is being accessed by other users"
Hello. I have a database which I cannot delete (the same for rename): # psql -d template1 =# drop database xxx; ERROR: database "xxx" is being accessed by other users But it is NOT accessed by anybody, because: 1. ps ax|grep post shows that there is NO active connections to database xxx; 2. I restarter postmaster and immediately try to delete the database - the same error message 3. I stopped web-server, and now it is GUARANTEED that there is no connections :-) What other diagnostics could I do? P. S. This database has other stranges, like: 1. VALUUM FULL VERBOSE ANALYZE hands at the middle of this database and never stops its execution; 2. there are some problems when I create a foreign key to particular table: it also hangs (postmaster restart does not correct this) 3. database is 2-3 times slow than it could be (if I restore it from the backup to the new place, it works 2-3 times faster) Practically I have already dump+restore this database to a new location (xxx_new) and work with it only, but I want to remove the old (broken?) database xxx and I cannot...
