[GENERAL] Long-running performance (MVCC, Vacuum, etc.) - Any fix?

[Eric Brown]

I'm building an appliance where I don't want my customers having to 
tune postgresql in any way from the configuration when I install it. I 
don't even want them to know it is there. Yet in my study of posgresql, 
it seems that even conscientious use of a vacuum daemon or cron job in 
vacuum's various forms leaves databases after a while much larger and 
lower performing than the actual space and performance after a 
dump/restore.

It seems to me that with MVCC, an UPDATE (for example) is really a 
DELETE+INSERT and it takes VACUUM to mark the old DELETED row as free 
space again. So changing one boolean or increasing one counter in a row 
of 100 bytes will relocate this row in a different location. While this 
in itself isn't terrible as some DB page is dirty no matter what, 
unless I'm lucky enough to insert into the same page that changed, my 
index(es) for this table will have to be changed and this will dirty 
yet another page that wouldn't be dirty without MVCC. And this is not 
to mention the resources required by VACUUM. And the disk bloat over 
time would imply that postgresql doesn't always efficiently use 
free-space with records/rows of similar size and so performance 
degrades as there are fewer rows per page over time.

My application is multi-threaded and I could care less about the 
concurrency afforded by MVCC. I'd rather just update the things in 
place and get less postgresql concurrency but more consistent 
long-running performance and disk space utilization.

Is my interpretation correct?
Is there a way to turn off MVCC?
Do fixed sized rows help any?
Is there anybody using this thing in an appliance type application?
I'm quite far along with stored-procedures and whatnot, but if 
postgresql really isn't the right solution due to these reasons, I'm 
curious if anybody has alternate OpenSource suggestions? (I'm actually 
migrating from sleepycat bsddb at the moment because I didn't realize 
the licensing costs involved there. My needs really aren't that 
extensive really - a few associative indexes, cascading delete, etc.)

My large data sets will have the following characteristics:
5,000,000 Rows x 50 bytes/row (could be fixed) w/1 multi-column index, 
1 single-column timestamp index AT 1,000,000 index searches, 300,000 
reads, 150,000 updates per day
720,000 Rows x 32 bytes/row fixed w/ 1 multi-column index, 1 
single-column timestamp index AT 150,000 index search, 150,000 updates, 
100s of reads per day
2,000,000 Rows x 4000 bytes avg/row AT 100,000 inserts per day, 150,000 
reads per day

Thanks,
Eric
Eric Brown
408-571-6341
www.propel.com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Long-running performance (MVCC, Vacuum, etc.) - Any fix?

[Martijn van Oosterhout]

On Tue, Dec 28, 2004 at 02:40:52AM -0800, Eric Brown wrote:
> I'm building an appliance where I don't want my customers having to 
> tune postgresql in any way from the configuration when I install it. I 
> don't even want them to know it is there. Yet in my study of posgresql, 
> it seems that even conscientious use of a vacuum daemon or cron job in 
> vacuum's various forms leaves databases after a while much larger and 
> lower performing than the actual space and performance after a 
> dump/restore.



Firstly, you havn't said what version of PostgreSQL you are using.
Recent versions behave better. Also, the autovacuum daemon will
probably do most of what you want...

> My application is multi-threaded and I could care less about the 
> concurrency afforded by MVCC. I'd rather just update the things in 
> place and get less postgresql concurrency but more consistent 
> long-running performance and disk space utilization.
> 
> Is my interpretation correct?

Yes. Although, for a simple counter you might be able to use sequences,
depending on the exact semantics. They don't take any additional space
when you increment them.

> Is there a way to turn off MVCC?

Nope. This is a Relational Database with everything that entails. If
you don't want that, maybe you should look into a database designed to
be embedded (SQLDB iirc).

> Do fixed sized rows help any?

I doubt it.

> Is there anybody using this thing in an appliance type application?

Probably, but I don't know where...

> I'm quite far along with stored-procedures and whatnot, but if 
> postgresql really isn't the right solution due to these reasons, I'm 
> curious if anybody has alternate OpenSource suggestions? (I'm actually 
> migrating from sleepycat bsddb at the moment because I didn't realize 
> the licensing costs involved there. My needs really aren't that 
> extensive really - a few associative indexes, cascading delete, etc.)

I'd suggest, use the autovacuum daemon, tune your FSM settings to what
you expect the load to be and maybe schedule a database-wide
REINDEX/VACUUM FULL regularly. We found doing it once a month was
enough to keep it tip-top over the long term...

Hope this helps,
-- 
Martijn van Oosterhout  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.


pgp49EbmUDwTD.pgp
Description: PGP signature

Re: [GENERAL] 8.0 rc2 Problem

[Peter Childs]

On Mon, 27 Dec 2004, Peter Childs wrote:

> Tom Lane wrote:
>
> >Peter Childs <[EMAIL PROTECTED]> writes:
> >
> >
> >>2. I loaded out 7.4 database on via a dump and reload. Once done I keep
> >>getting errors when it trys to analyse (I'm using autovacuum) Nothing
> >>unsuall just a straght forward pg_dump from 7.4 loaded into 8.0.
> >>
> >>
> >
> >
> >
> >>Dec 27 07:34:45 ex37 postgres[5745]: [28-1] ERROR:  could not access
> >>status of transaction 2684354560
> >>
> >>
> >
> >Is this repeatable if you start over (re-initdb and reload)?  If so I'd
> >be very interested to see the dump file.
> >
> > regards, tom lane
> >
> >
> >
> I've dumped twice and reloaded twice same problem both times. Its
> difficult to let you see the dump as due to Data Protection. I re-inited
> once and reloaded just to check. I'll try a dump without data and see if
> that causes the same problem If that fails I'll can send you that. Then
> I might try adding data for tables that are not a risk.
> I'll do it tomarrow now, as I'm off as my Wife had a baby yestarday.
>
> Peter Childs
>
> ---(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
>
>
Further examination I orignally used by 7.4 backup (taken
every 12 hours and well tested that they work under 7.4 anyway) rather
than redumping using pg_dump 8.0. I have now dumped using pg_dump version
8.0 same problem. I dumped just the schema. upon load into 8.0 a couple of
lines saying

ERROR: relation "test_id_seq" does not exist
STATEMENT: REVOKE ALL ON TABLE shopping_id_seq TO PUBLIC;
ERROR: relation "test_id_seq" does not exist
STATEMENT: GRANT ALL ON TABLE test_id_seq TO PUBLIC;

Not that important but no problem with the empty database but then
its empty!
Since the dumps are just sql right? I'm guessing that this has to
be some internal problem.

Peter Childs




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] running a query file of 42MB

[Nefnifi, Kasem]

I'm trying to run query generated by an import script. postgresql 8
onder windows XP.
the file is about 42MB, i've opened this file by pgAdminIII and start
the query, it inserts data into 3 tables and after makes some update.
the time is too much and it hangs up without any commit.

any body know another methode to run the query.

i've tried with the command line psql, but get errors because the file
is like:

insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('yyy',(select OID from table1
where pkey='xxx'));
update table1 set field1='a', field2='b', field3='c' where pkey = 'xxx';

insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('zzz',(select OID from table1
where pkey='xxx'));
update table1 set field1='u', field2='v', field3='q' where pkey = 'xxx';
.
.
.etc

the reason to do so is to get the OID for each record inserted into the
table1 and use that in the table2.
i've limited the query to 2 tables but there are more tables and also
more related fields from the same table.

in the psql i've tried this:
>psql -d dbname -u username -f file.sql
i've also tried with
>psql -d dbname -u username < file.sql

but get the error, pkey violatde. should i use a commit between each
record ?

regards,
kasem,

Disclaimer: 
This electronic transmission and any files attached to it are strictly 
confidential and intended solely for the addressee. If you are not 
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this 
transmission in error, please notify the sender by return and delete
the transmission.  Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages 
resulting from any virus transmitted. 
Thank You.


---(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] running a query file of 42MB

[Nefnifi, Kasem]

dear all,

it is okay,
the import by psql is faster and quit, it is running now.
i've found an error in the query, it was the decimal separator that must be "." 
and not ",".
now it's working.
thx to all
kasem,

-Original Message-
From: Nefnifi, Kasem 
Sent: Tuesday, December 28, 2004 2:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] running a query file of 42MB



I'm trying to run query generated by an import script. postgresql 8
onder windows XP.
the file is about 42MB, i've opened this file by pgAdminIII and start
the query, it inserts data into 3 tables and after makes some update.
the time is too much and it hangs up without any commit.

any body know another methode to run the query.

i've tried with the command line psql, but get errors because the file
is like:

insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('yyy',(select OID from table1
where pkey='xxx'));
update table1 set field1='a', field2='b', field3='c' where pkey = 'xxx';

insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('zzz',(select OID from table1
where pkey='xxx'));
update table1 set field1='u', field2='v', field3='q' where pkey = 'xxx';
.
.
.etc

the reason to do so is to get the OID for each record inserted into the
table1 and use that in the table2.
i've limited the query to 2 tables but there are more tables and also
more related fields from the same table.

in the psql i've tried this:
>psql -d dbname -u username -f file.sql
i've also tried with
>psql -d dbname -u username < file.sql

but get the error, pkey violatde. should i use a commit between each
record ?

regards,
kasem,

Disclaimer: 
This electronic transmission and any files attached to it are strictly 
confidential and intended solely for the addressee. If you are not 
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this 
transmission in error, please notify the sender by return and delete
the transmission.  Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages 
resulting from any virus transmitted. 
Thank You.


---(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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Long-running performance (MVCC, Vacuum, etc.) - Any fix?

[Vivek Khera]

> "MvO" == Martijn van Oosterhout  writes:

MvO> I'd suggest, use the autovacuum daemon, tune your FSM settings to what
MvO> you expect the load to be and maybe schedule a database-wide
MvO> REINDEX/VACUUM FULL regularly. We found doing it once a month was
MvO> enough to keep it tip-top over the long term...

If you know your application usage patterns well, scheduling vacuum
via cron probably would be better than autovacuum.  autovacuum runs
whenever it pleases, and doesn't deal well with extremely large tables
(ie, it never runs vacuum on them since the % change may be small even
though millions of rows are "expired").  You can even vacuum differnt
tables on different schedules.  Just make sure the VACUUMs don't 
overlap!

I haven't run reindex or vacuum full since I upgraded to 7.4.x.  The
indexes are not bloating, and sufficient calls to vacuum keep the
"stable set size" of the db pretty constant.  ie, it doesn't grow
without bounds, as the OP fears.

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.Khera Communications, Inc.
Internet: khera@kciLink.com   Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] 8.0 rc2 Problem

[Tom Lane]

Peter Childs <[EMAIL PROTECTED]> writes:
>   Further examination I orignally used by 7.4 backup (taken
> every 12 hours and well tested that they work under 7.4 anyway) rather
> than redumping using pg_dump 8.0. I have now dumped using pg_dump version
> 8.0 same problem. I dumped just the schema. upon load into 8.0 a couple of
> lines saying

> ERROR: relation "test_id_seq" does not exist
> STATEMENT: REVOKE ALL ON TABLE shopping_id_seq TO PUBLIC;
> ERROR: relation "test_id_seq" does not exist
> STATEMENT: GRANT ALL ON TABLE test_id_seq TO PUBLIC;

Could you send me that dump off-list?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Get current trasanction id

[Joachim Zobel]

Am Mo, den 27.12.2004 schrieb Marek Lewczuk um 20:54:
> Michael Fuhr napisał(a):
> > Why do you want the transaction ID?  What problem are you trying
> > to solve?
> > 
> I've already solved the problem - I found somewhere on the web a very 
> simple C function, which returns transaction id. I need transaction id 
> for plperl functions setVar, getVar (using $_SHARED array). Functions 
> can write/read variables which are available either for connection or 
> transaction.

It would be nice to have that in pgplsql. I once needed a transaction Id
in oracle for a historisation trigger. It saves before copies of the
modified records on a per transaction basis.

Sincerely,
Joachim

--
 
"... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden 
koennen."- Bertolt Brecht - Leben des Galilei 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] running a query file of 42MB

[Pierre-Frédéric Caillaud]

	Don't use OIDs for primary keys, please, please.
	Use a Sequence instead, and replace your SELECT oid by SELECT  
currval('sequence_name')


On Tue, 28 Dec 2004 15:44:56 +0100, Nefnifi, Kasem  
<[EMAIL PROTECTED]> wrote:

dear all,
it is okay,
the import by psql is faster and quit, it is running now.
i've found an error in the query, it was the decimal separator that must  
be "." and not ",".
now it's working.
thx to all
kasem,

-Original Message-
From: Nefnifi, Kasem
Sent: Tuesday, December 28, 2004 2:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] running a query file of 42MB

I'm trying to run query generated by an import script. postgresql 8
onder windows XP.
the file is about 42MB, i've opened this file by pgAdminIII and start
the query, it inserts data into 3 tables and after makes some update.
the time is too much and it hangs up without any commit.
any body know another methode to run the query.
i've tried with the command line psql, but get errors because the file
is like:
insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('yyy',(select OID from table1
where pkey='xxx'));
update table1 set field1='a', field2='b', field3='c' where pkey = 'xxx';
insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('zzz',(select OID from table1
where pkey='xxx'));
update table1 set field1='u', field2='v', field3='q' where pkey = 'xxx';
.
.
.etc
the reason to do so is to get the OID for each record inserted into the
table1 and use that in the table2.
i've limited the query to 2 tables but there are more tables and also
more related fields from the same table.
in the psql i've tried this:
psql -d dbname -u username -f file.sql
i've also tried with
psql -d dbname -u username < file.sql
but get the error, pkey violatde. should i use a commit between each
record ?
regards,
kasem,

Disclaimer:
This electronic transmission and any files attached to it are strictly
confidential and intended solely for the addressee. If you are not
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this
transmission in error, please notify the sender by return and delete
the transmission.  Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages
resulting from any virus transmitted.
Thank You.

---(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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
   http://www.postgresql.org/docs/faqs/FAQ.html

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] running a query file of 42MB

[Nefnifi, Kasem]

that is right Pierre, i use an other field nl. id that is incremented auto.

Best Regards / Vriendelijke Groeten / Salutations Distinguées / Freundliche 
Grüße !!! 
Kasem NEFNIFI 
AtosOrigin Belgium N.V. 
Minervastraat  7 
1930 Zaventem (Belgium) 
Tel  : +32(0)2 712 28 30 
Fax : +32(0)2 712 28 63 
GSM   : +32 495 25 12 33 
Email : [EMAIL PROTECTED]   
www.atosorigin.com   



-Original Message-
From: Pierre-Frédéric Caillaud [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 28, 2004 4:10 PM
To: Nefnifi, Kasem; pgsql-general@postgresql.org
Subject: Re: [GENERAL] running a query file of 42MB



Don't use OIDs for primary keys, please, please.
Use a Sequence instead, and replace your SELECT oid by SELECT  
currval('sequence_name')



On Tue, 28 Dec 2004 15:44:56 +0100, Nefnifi, Kasem  
<[EMAIL PROTECTED]> wrote:

> dear all,
>
> it is okay,
> the import by psql is faster and quit, it is running now.
> i've found an error in the query, it was the decimal separator that must  
> be "." and not ",".
> now it's working.
> thx to all
> kasem,
>
> -Original Message-
> From: Nefnifi, Kasem
> Sent: Tuesday, December 28, 2004 2:28 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] running a query file of 42MB
>
>
>
> I'm trying to run query generated by an import script. postgresql 8
> onder windows XP.
> the file is about 42MB, i've opened this file by pgAdminIII and start
> the query, it inserts data into 3 tables and after makes some update.
> the time is too much and it hangs up without any commit.
>
> any body know another methode to run the query.
>
> i've tried with the command line psql, but get errors because the file
> is like:
>
> insert into table1 (pkey) values ('xxx');
> insert into table2 (pkey,field) values ('yyy',(select OID from table1
> where pkey='xxx'));
> update table1 set field1='a', field2='b', field3='c' where pkey = 'xxx';
>
> insert into table1 (pkey) values ('xxx');
> insert into table2 (pkey,field) values ('zzz',(select OID from table1
> where pkey='xxx'));
> update table1 set field1='u', field2='v', field3='q' where pkey = 'xxx';
> .
> .
> .etc
>
> the reason to do so is to get the OID for each record inserted into the
> table1 and use that in the table2.
> i've limited the query to 2 tables but there are more tables and also
> more related fields from the same table.
>
> in the psql i've tried this:
>> psql -d dbname -u username -f file.sql
> i've also tried with
>> psql -d dbname -u username < file.sql
>
> but get the error, pkey violatde. should i use a commit between each
> record ?
>
> regards,
> kasem,
> 
> Disclaimer:
> This electronic transmission and any files attached to it are strictly
> confidential and intended solely for the addressee. If you are not
> the intended addressee, you must not disclose, copy or take any
> action in reliance of this transmission. If you have received this
> transmission in error, please notify the sender by return and delete
> the transmission.  Although the sender endeavors to maintain a
> computer virus free network, the sender does not warrant that this
> transmission is virus-free and will not be liable for any damages
> resulting from any virus transmitted.
> Thank You.
> 
>
> ---(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
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>

Disclaimer: 
This electronic transmission and any files attached to it are strictly 
confidential and intended solely for the addressee. If you are not 
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this 
transmission in error, please notify the sender by return and delete
the transmission.  Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages 
resulting from any virus transmitted. 
Thank You.


---(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] Get current trasanction id

[Marek Lewczuk]

Joachim Zobel napisaÅ(a):
Am Mo, den 27.12.2004 schrieb Marek Lewczuk um 20:54:
Michael Fuhr napisaÅ(a):
Why do you want the transaction ID?  What problem are you trying
to solve?
I've already solved the problem - I found somewhere on the web a very 
simple C function, which returns transaction id. I need transaction id 
for plperl functions setVar, getVar (using $_SHARED array). Functions 
can write/read variables which are available either for connection or 
transaction.

It would be nice to have that in pgplsql. I once needed a transaction Id
in oracle for a historisation trigger. It saves before copies of the
modified records on a per transaction basis.
As I wrote before you can have it in plpgsql - you just need to install 
special c function as e.g getCurrentTransactionId() and you will be able 
to get id from within plpgsql - but I agree that this function should be 
in base SQL functions or at least in contrib modules.

C function is very simple:
getcurrenttransactionid.c:
#include "postgres.h"
#include "access/xact.h"
Datum getcurrenttransactionid(PG_FUNCTION_ARGS)
{
TransactionId xid = GetCurrentTransactionId();
PG_RETURN_INT32((int32) xid);
}
getcurrenttransactionid.sql:
SET search_path = public;
CREATE FUNCTION getCurrentTransactionId()
RETURNS integer
AS 'MODULE_PATHNAME'
LANGUAGE 'c';

Regards,
Marek Lewczuk

---(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] Long-running performance (MVCC, Vacuum, etc.) - Any fix?

[Greg Stark]

Eric Brown <[EMAIL PROTECTED]> writes:

> My application is multi-threaded and I could care less about the concurrency
> afforded by MVCC. I'd rather just update the things in place and get less
> postgresql concurrency but more consistent long-running performance and disk
> space utilization.

Postgres uses MVCC for guaranteeing that it can roll back transactions too...

> Is my interpretation correct?
> Is there a way to turn off MVCC?
> Do fixed sized rows help any?

Postgres doesn't do anything special for fixed size rows.


I would suggest you look at pg_autovacuum, but in general while a
maintenance-free system is everyone's goal it's pretty far off. 

-- 
greg


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

FW: [GENERAL] 8.0 Beta3 worked, RC1 didn't!

[A. Mous]

OK, that’s three so far that are
experiencing the exact same problem.  Does anyone in the know have any
suggestions as to how this can be resolved?

 

Much thanks,

Peter

 

 

 

 

-Original Message-
From: Edgars Diebelis
[mailto:[EMAIL PROTECTED] 
Sent: December
 28, 2004 5:41
 AM
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] 8.0 Beta3
worked, RC1 didn't!

 



Hallo, I have the same
problem as you:





 







I had install
PostgreSQL 8.0 RC2. Remote clients connect to databese, so I have to set
listen_addresses = '*' in the posrgresql.conf file. After this change I have
error message:





 "could not receive server response to SSL negotiation
packet; connection
reset by peer (0x2746/10054)".





 





Have you any solution?
Is this configuration problem or is this bug? Please help!





 





Thx, Edgars

Re: [GENERAL] OSX 10.3.7 broke Postgresql 8.0.0b5?

[Wes]

On 12/18/04 11:03 AM, "Scott Ribe" <[EMAIL PROTECTED]> wrote:

> All the recent OS X .x updates have replaced /etc/rc. For all I know they
> may always have done so, but of course prior to 10.3 we didn't have to
> modify /etc/rc directly.

You shouldn't be modifying /etc/rc directly anyway.  Create a startup script
in /Library/StartupItems, or /etc/mach_init.d.  I believe the latter is new
with 10.3 and is now the preferred method.  Granted, modifying /etc/rc is a
lot easier, but you pay for it later when an update wipes out your changes
and you beat your head against the wall trying to find the problem.

Wes


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] 8.0 Beta3 worked, RC1 didn't!

[Magnus Hagander]

Title: Message



Please 
check this: http://archives.postgresql.org/pgsql-hackers-win32/2004-12/msg00133.php 
and report success/failure.
 
(I am 
assuming the same problem. Please verify that the output in the postgres log is 
the one about failure to attach to shared memory)
 
//Magnus

  
   
  
  OK, that’s three so 
  far that are experiencing the exact same problem.  Does anyone in the 
  know have any suggestions as to how this can be resolved?
   
  Much 
  thanks,
  Peter
   
   
   
   
  -Original 
  Message-From: Edgars 
  Diebelis [mailto:[EMAIL PROTECTED] Sent: December 
  28, 2004 5:41 
  AMTo: [EMAIL PROTECTED]Subject: Re: [GENERAL] 8.0 Beta3 worked, 
  RC1 didn't!
   
  
  Hallo, I have the 
  same problem as you:
  
   
  
  
  I had install 
  PostgreSQL 8.0 RC2. Remote clients connect to databese, so I have to set 
  listen_addresses = '*' in the posrgresql.conf file. After this change I have 
  error message:
  
   "could not receive server response to SSL 
  negotiation packet; connectionreset by peer 
  (0x2746/10054)".
  
   
  
  Have you any 
  solution? Is this configuration problem or is this bug? Please 
  help!
  
   
  
  Thx, 
  Edgars

Re: [GENERAL] OSX 10.3.7 broke Postgresql 8.0.0b5?

[Tom Lane]

Wes <[EMAIL PROTECTED]> writes:
> On 12/18/04 11:03 AM, "Scott Ribe" <[EMAIL PROTECTED]> wrote:
>> All the recent OS X .x updates have replaced /etc/rc. For all I know they
>> may always have done so, but of course prior to 10.3 we didn't have to
>> modify /etc/rc directly.

> You shouldn't be modifying /etc/rc directly anyway.

*Tell* us about it.  Apple used to have a more reasonable method for
setting the SysV IPC parameters, but AFAICT you have to hack /etc/rc.d
now; anyplace else runs too late to affect them.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] OSX 10.3.7 broke Postgresql 8.0.0b5?

[Scott Ribe]

> You shouldn't be modifying /etc/rc directly anyway.  Create a startup script
> in /Library/StartupItems, or /etc/mach_init.d.  I believe the latter is new
> with 10.3 and is now the preferred method.  Granted, modifying /etc/rc is a
> lot easier, but you pay for it later when an update wipes out your changes
> and you beat your head against the wall trying to find the problem.

Startup items can no longer change the sysv kernel settings. I forget
whether that is a 10.2 change, or a 10.3 change, but setting them in a
startup item has no effect and hasn't for a pretty long time.

I was not aware of /etc/mach_init.d, a quick glance at the docs make it
obvious that it runs earlier than SystemStarter, looking at /etc/rc shows
that it runs not that much later than the normal settings of the sysv
parameters. Are you sure changing sysv kernel parameters at that point will
work?


-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] pgsql question

[Joost Kraaijeveld]

Hi all,

I need to to change an ip addres in a plpgsql function from aaa.bbb.ccc.ddd to 
an sring containing "aaa bbb ccc ddd upload".

The following code does not work. It complaines about the replace line (or is 
it the assignment?): 

ERROR:  syntax error at or near "replace" at character 1
CONTEXT:  PL/pgSQL function "getcustomerdownload" line 14 at SQL statement) :


CREATE OR REPLACE FUNCTION getcustomerdownload(text, timestamp, timestamp)
  RETURNS int4 AS
'
DECLARE
my_ipaddress inet;
my_ipaddress_as_text text;
BEGIN
select into my_ipaddress customertable.ipaddress
from
(
select customer.objectid, getcustomername(customer.objectid) as customername, 
customer.ipaddress from customer  
)
as customertable 
where customertable.customername like ''$1%'';

my_ipaddress_as_text := host(my_ipaddress);
replace(my_ipaddress_as_text ,''.'','' '');

return cast( (sum(bytes)/(1024*1024)) as int8) from logs
where
rule_name =  my_ipaddress_as_text | '' upload''
and
that_time between cast( abstime($2) as int4) and cast( abstime($3) as int4);
END
'
LANGUAGE 'plpgsql' VOLATILE;

Does anyone have any idea of how to do this?

TIA


Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [EMAIL PROTECTED]
web: www.askesis.nl 

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] pgsql question

[Mike Rylander]

You need to "replace" into something.  Try

my_ipaddress_as_text := replace(my_ipaddress_as_text,''.'','' '');

Also, the concat operator is ||, not just one |.

Hope that helps!

On Tue, 28 Dec 2004 22:32:47 +0100, Joost Kraaijeveld
<[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> I need to to change an ip addres in a plpgsql function from aaa.bbb.ccc.ddd 
> to an sring containing "aaa bbb ccc ddd upload".
> 
> The following code does not work. It complaines about the replace line (or is 
> it the assignment?):
> 
> ERROR:  syntax error at or near "replace" at character 1
> CONTEXT:  PL/pgSQL function "getcustomerdownload" line 14 at SQL statement) :
> 
> CREATE OR REPLACE FUNCTION getcustomerdownload(text, timestamp, timestamp)
>   RETURNS int4 AS
> '
> DECLARE
> my_ipaddress inet;
> my_ipaddress_as_text text;
> BEGIN
> select into my_ipaddress customertable.ipaddress
> from
> (
> select customer.objectid, getcustomername(customer.objectid) as customername, 
> customer.ipaddress from customer
> )
> as customertable
> where customertable.customername like ''$1%'';
> 
> my_ipaddress_as_text := host(my_ipaddress);
> replace(my_ipaddress_as_text ,''.'','' '');
> 
> return cast( (sum(bytes)/(1024*1024)) as int8) from logs
> where
> rule_name =  my_ipaddress_as_text | '' upload''
> and
> that_time between cast( abstime($2) as int4) and cast( abstime($3) as int4);
> END
> '
> LANGUAGE 'plpgsql' VOLATILE;
> 
> Does anyone have any idea of how to do this?
> 
> TIA
> 
> Groeten,
> 
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: [EMAIL PROTECTED]
> web: www.askesis.nl
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])