[GENERAL] ext3 block size
hello. my database size is 5GB. what is the block size recommend? thanks wilson
Re: v7.4 on Windows ... (Was: Re: [GENERAL] v7.4 Beta 1 Bundle ...)
On Wed, 2003-08-06 at 09:22, The Hermit Hacker wrote:
> On Wed, 6 Aug 2003, Robert Treat wrote:
>
> > depends on what you mean by any. I believe that the standard tarball
> > will compile under windows, but to what extent it will actually run I
> > couldn't say. This release is not intended for native use on windows
> > (that had to be pushed back to 7.5), though it will certainly run via
> > cygwin.
>
> Ummm ... will it compile? I thought that the issue with the Windows
> native port was that it wouldn't yet ... something about fork() vs exec()
> that Bruce was working on?
>
Since it's always more fun to speculate than to wait for the proper
answer... I thought it would compile, but wouldn't actually run due to
exec/fork issues? Course that may have been someones development code
and not what got checked in I suppose...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: 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] 7.3.3 behaving differently on OS X 10.2.6 and FreeBSD 4.8-STABLE
I don't know if this is a postgresql bug or a problem with my architecture but I thought I would post here about a strange bug I just came across in my application. I use OS X 10.2.6 as my development machine and FreeBSD 4.8 for my production machines. All systems are running postgresql 7.3.3. I just published some code to production and when testing the production results it blew up with a sql parsing error. The following sql worked fine on my OS X development machine: select u.user_id, u.first_name, u.last_name, u.email_address, w.w9, pm.description as payment_method, count(s.user_id) as documents, sum(s.payment_amount) as amt_sum from ht_user u inner join writer w on u.user_id = w.user_id inner join payment_method pm on w.payment_method_id = pm.payment_method_id left join submission s on u.user_id = s.user_id group by u.user_id, u.first_name, u.last_name, u.email_address, w.w9, pm.description order by lower(last_name) asc But on my production machine postgresql complained about the order by clause-- it wanted the table alias to be on last_name. culley ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] cannot open multi-query plan as cursor
=?iso-8859-2?Q?Egy=FCd_Csaba?= <[EMAIL PROTECTED]> writes: > When I copy it into the console (psql) it runs well, but from function it > sends an error: > ERROR: cannot open multi-query plan as cursor > What does it mean? Could anybody help me? I think most likely it means you made a typo transcribing the query into the function. If you can't figure it out, show us the whole function definition ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] tsearch2 on postgresql 7.3.4
On 06/08/2003 10:43 [EMAIL PROTECTED] wrote: I am trying to setup tsearch2 on postgresql 7.3.4 on a Redhat9 system, installed from rpms. There seemed to be some files required for installation of tsearch missing so I downloaded the src bundle too. Tsearch2 then compiled ok but now the command: psql mydb < tsearch2.sql fails with a message along the lines of: unable to stat $libdir/tsearch2 no such file I read up on valena.com about what $libdir was, but there's no mention of how to find out what the value of $libdior is. how do i find out where $libdir is for the current install? what files do i need to move into $libdir directory to get tsearch2 up and running? Thanks! I just tried a make/make install using 7.3.3 sources. I think the problem is that the make file copies the .so to /usr/lib/postgresql which might not be in your lib path. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] cannot open multi-query plan as cursor
Tom, I 'raise notice'd the whole query by the function (which was quoted in my previous letter). Actually I generate the query on the fly according to the given parameters. I call it in a for R in execute Query statement. As the function is a bit long I quote only a portion of it. FieldList := ''select distinct productid, quantity, date, (select dir from t_changes where id=changeid) as dir ''; FromList := '' from t_stockchanges ''; JoinList := '' join t_prod_in_pgr using (productid) join t_productgroups on (t_productgroups.id=productgroupid) ''; WhereList := '' where productid in (select productid from t_prod_in_pgr where productgroupid = '' || quote_literal(ID) || '') and ''|| ''(date between '' || quote_literal(Date1) || '' and '' || quote_literal(Date2) || '')''; -- etc... Query := FieldList || FromList || JoinList || WhereList; raise notice ''%'', Query; for StockChangesRec in execute Query loop ... end loop AFAIC running the copy-pasted notice from psql console is equivalent with calling it from a for ... in statement. Any suggestions or shall I quote the whole function instead? Thank you very much. -- Csaba -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Thursday, August 07, 2003 6:16 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] (E-mail) Subject: Re: [GENERAL] cannot open multi-query plan as cursor =?iso-8859-2?Q?Egy=FCd_Csaba?= <[EMAIL PROTECTED]> writes: > When I copy it into the console (psql) it runs well, but from function it > sends an error: > ERROR: cannot open multi-query plan as cursor > What does it mean? Could anybody help me? I think most likely it means you made a typo transcribing the query into the function. If you can't figure it out, show us the whole function definition ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.505 / Virus Database: 302 - Release Date: 2003. 07. 30. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.505 / Virus Database: 302 - Release Date: 2003. 07. 30. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] FATAL: The database system is in recovery mode
> I'd highly recommend against using postgresql with cygwin in production, and further, very highly recommend against using it under win9x, due to the stability issues on that platform. Can you just put it on a Win2K or better a linux box and let the clients all connect to that machine? I would second this. We actually explicitly limit connections to 50 with Mammoth PostgreSQL for Win32. Also win9x just doesn't have the preemptiveness needed in the kernel to handle something like PostgreSQL. PostgreSQL on Win32 for WinNT/2000/XP is fairly stable though, as long as you leave it at 50 connections or less. More than likely, when the client app crashes, it's causing a kill -9 to be send the the backend, which is causing a the database to restart or something along those lines. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Anomaly with SUM().
Stephan Szabo wrote: On Fri, 8 Aug 2003, Anthony Best wrote: I've noticed that the SUM() seems to overflow under some situations. The only difference is the order that the data is retrived from the database. Is amount a float type column (float4 or float8)? If so, you're probably just running into issues with float precision problems. Changing the order of the operations can change the final value of a sequence of operations on float. It's "double precision." (Which is float8?). So, should I tweak my join to preserve order, or something else? ---(end of broadcast)--- TIP 3: 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] Changing DB ownership
Why would you want to do that?
Why not do it an easier way and dump the database and restore it into
your new database?
There's got to be a lot of stuff to consider when doing something as
radical as renaming a database.
I am a developer of dental computer systems using Postgres within Red
Hat Advanced Server 2.1.
Jim Apsey
Christopher Murtagh wrote:
Dumb question maybe, but how does one change database ownership? I've
tried several permutations of:
ALTER DATABASE SET ("|'| )owner("|'| ) TO ("|'| )newowner("|'| );
and I tried looking at pg_database and I *was* able to hack this (got a
clue half way through writing this email... sorry):
UPDATE pg_database SET datdba = 504 WHERE datname='chris';
And that worked as expected, but I'm worried that I might have missed
something somewhere else. Will the above UPDATE cause problems down the
road?
Any info/clue would be much appreciated. Thanks in advance.
Cheers,
Chris
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] compile error on slackware 9.0 while --enable-thread-safety
while remove --enable-thread-safety everything ok. what's the matter? the error output: ---8<- make[2]: Entering directory `/usr/laser/postgresql-7.4beta1/src/port' gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../src/include -c -o path.o path.c gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../src/include -c -o threads.o threads.c threads.c: In function `pqGetpwuid': threads.c:49: too few arguments to function `getpwuid_r' threads.c:49: warning: assignment makes pointer from integer without a cast threads.c: In function `pqGethostbyname': threads.c:74: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type threads.c:74: too few arguments to function `gethostbyname_r' threads.c:74: warning: assignment makes pointer from integer without a cast ---8<- Thanks and regards Laser ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 7.3.3 behaving differently on OS X 10.2.6 and FreeBSD 4.8-STABLE
Culley, > But on my production machine postgresql complained about the order by > clause-- it wanted the table alias to be on last_name. I believe this is because you used "u.last_name" earlier in the statement, and the ORDER BY clause doesn't know that's what you mean. That's a guess that doesn't really explain why it'd work under one OS and not under another. Are the two versions of Postgres configured the same? -- David Olbersen iGuard Engineer St. Bernard Software > -Original Message- > From: culley harrelson [mailto:[EMAIL PROTECTED] > Sent: Friday, August 08, 2003 10:48 AM > To: [EMAIL PROTECTED] > Subject: [GENERAL] 7.3.3 behaving differently on OS X 10.2.6 > and FreeBSD > 4.8-STABLE > > > I don't know if this is a postgresql bug or a problem with my > architecture but I thought I would post here about a strange > bug I just > came across in my application. > > I use OS X 10.2.6 as my development machine and FreeBSD 4.8 for my > production machines. All systems are running postgresql > 7.3.3. I just > published some code to production and when testing the production > results it blew up with a sql parsing error. The following > sql worked > fine on my OS X development machine: > > select u.user_id, u.first_name, u.last_name, u.email_address, w.w9, > pm.description as payment_method, count(s.user_id) as documents, > sum(s.payment_amount) as amt_sum from ht_user u inner join > writer w on > u.user_id = w.user_id inner join payment_method pm on > w.payment_method_id = pm.payment_method_id left join submission s on > u.user_id = s.user_id group by u.user_id, u.first_name, u.last_name, > u.email_address, w.w9, pm.description order by lower(last_name) asc > > But on my production machine postgresql complained about the order by > clause-- it wanted the table alias to be on last_name. > > culley > > > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Dump Customizing
On Tue, 5 Aug 2003, Yudha Setiawan wrote: > Dear expert, > > It's Urgent. > How do I dump just for > - Table Structure > - Index > - Constraint > - Type > - Function. > Without > - Create Trigger. > - Data. > i've tried with -X option, but it didn't works > "pg_dump -Upostgres test_yudha1 -fyudha1 -v -s -R -X disable-triggers;" > Thank's for your attention. I don't think there is one that'll drop the create trigger statements so you'll probably need to do some post-processing on a schema only dump to get it. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] INSERT RULE QUERY ORDER
When my RULE takes the form of: CREATE RULE name AS ON INSERT TO table DO INSTEAD ( UPDATE query ; INSERT query) ; The INSERT query doesn't fire and there is no error. Putting the INSERT first allows them to both fire. Can anyone tell me why? I think it has something to do with *NEW* and *OLD* being initialized differently for an UPDATE than for an INSERT. I'd like to know what's going on so I can be confident I'm writing my rules correctly. Thanks all. --- jtocci Fort Wayne, IN
Re: [GENERAL] Empty Output? How Do I Determine the Character?
Hunter Hillegas <[EMAIL PROTECTED]> writes: > I cannot determine what character is stored in a varchar... > For instance: > thedonnaholics=# select state from mailing_list where rec_num = 7; > state > --- > (1 row) I'd say it's either NULL or between one and five space characters. To find out, try something like select '>' || state || '<' from mailing_list where rec_num = 7; regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Empty Output? How Do I Determine the Character?
Ok... I don't think that it is null: thedonnaholics=# select count(*) from mailing_list where state = null; count --- 0 (1 row) I ran the query you suggested but I don't know what it means: thedonnaholics=# select '>' || state || '<' from mailing_list where rec_num = 7; ?column? -- (1 row) Any insight appreciated. Thanks, Hunter > From: Tom Lane <[EMAIL PROTECTED]> > Date: Fri, 08 Aug 2003 19:03:24 -0400 > To: Hunter Hillegas <[EMAIL PROTECTED]> > Cc: PostgreSQL <[EMAIL PROTECTED]> > Subject: Re: [GENERAL] Empty Output? How Do I Determine the Character? > > Hunter Hillegas <[EMAIL PROTECTED]> writes: >> I cannot determine what character is stored in a varchar... >> For instance: >> thedonnaholics=# select state from mailing_list where rec_num = 7; >> state >> --- > >> (1 row) > > I'd say it's either NULL or between one and five space characters. > To find out, try something like > > select '>' || state || '<' from mailing_list where rec_num = 7; > > regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump, psql load problem
Tom Jenkins <[EMAIL PROTECTED]> writes: > that is what gets dumped via pg_dump. however if we take that statement > and paste it into psql (as one long line) or through command line (ie > psql dbname < inusecountrycode.schema) we get an error: > 'ERROR: parser: parse error at or near "SELECT"' > if i manually change the definition to put parenthesis around each > select statement, then psql has no problem. Yeah. This is a known bug in PG 7.2.1 (in the backend, actually, not in pg_dump). [checks CVS logs...] It's fixed as of 7.2.2: 2002-06-15 14:38 tgl * src/backend/utils/adt/ruleutils.c (REL7_2_STABLE): Back-patch 7.3 fix to fully parenthesize UNION/EXCEPT/INTERSECT queries in ruleutils output. The previous partial parenthesization was a hack to get around grammar restrictions that have since been fixed; and as Sam O'Connor pointed out, there were cases where it didn't work. You really should be running 7.2.4 in any case --- there are some nasty bugs fixed in the later 7.2 releases. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
