Re: [GENERAL] Buglist

2003-08-19 Thread Lincoln Yeoh
At 03:59 PM 8/19/2003 +0200, Bo Lorentsen wrote:

On Tue, 2003-08-19 at 15:47, Shridhar Daithankar wrote:

 Or have bugzilla setup somewhere. That way the tracking will be hell lot
 visible to outside world..
I agree on this, as it seems messy from outside not to be able to get an
overview of both solved and not solved bugs.
I know that as developer, this may not seem like a big problem, but it
will help non hackers to get an overview.
AFAIK bugzilla requires mysql (for now).

I've recently installed it and if it can be easily made to work with 
postgresql I'd like to know.

Link.

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


Re: [GENERAL] Mailing list in French

2003-08-19 Thread Alvaro Herrera
On Tue, Aug 19, 2003 at 01:40:31PM +0530, Shridhar Daithankar wrote:
 On 19 Aug 2003 at 10:01, Francois Suter wrote:
 
  I am pleased to announce the start of a PostgreSQL general mailing list 
  in French. Its name is pgsql-fr-generale. I hope many of you will join 
  it so that we can make it an interesting place.
 
 Well, the idea of mailing list is to exchange information and help each other. 
 Obviously if few great things happen on some mailing list and others don't come 
 to know it, defeats the purpose of having a mailing list.

Yeah, but there's not too much you can do about it.  You can check what
goes on in the spanish list at
http://tlali.iztacala.unam.mx/mailman/listinfo/pgsql-ayuda

There's a respectable amount of traffic, and I don't think there's
anyone who would want to translate it.


 As an experiment can we have a translation gateway so that these two lists, in 
 general and french-general can interact? I mean the idea should be to help 
 people getting communicate better rather than having a separate list as such.

Err... and who is doing the work, exactly?  (Maybe Google or Babelfish?
Care to set it up?)

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
La grandeza es una experiencia transitoria.  Nunca es consistente.
Depende en gran parte de la imaginación humana creadora de mitos
(Irulan)

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


[GENERAL] Bulk Insert / Update / Delete

2003-08-19 Thread Philip Boonzaaier
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.

Logically then, I would like to SELECT * FROM TABLE
WHERE Values entered here, and then IF FOUND
UPDATE TABLE SET  Values entered here ELSE
INSERT INTO TABLE VALUES Values entered here
END IF;

The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.

Does anyone have any suggestions as to how I can achieve this ?


This message is privileged and confidential and intended for the addressee only. If 
you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you 
have received this in error, please destroy the original message
and contact us at [EMAIL PROTECTED] Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for  
viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.

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

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


Re: [GENERAL] Buglist

2003-08-19 Thread Devrim GUNDUZ

Hi,

On Tue, 19 Aug 2003, Lincoln Yeoh wrote:

 AFAIK bugzilla requires mysql (for now).
 
 I've recently installed it and if it can be easily made to work with 
 postgresql I'd like to know.

https://bugzilla.redhat.com/bugzilla/index.cgi
 Bugzilla News
===
January 1st, 2003
Current Red Hat version of Bugzilla using PostgreSQL code available for 
download.


AFAIK RH runs bugzilla on PostgreSQL (or RHDB, whatever). The code is 
available from there.

Regards,



-- 
Devrim GUNDUZ
[EMAIL PROTECTED]   [EMAIL PROTECTED] 
http://www.tdmsoft.com
http://www.gunduz.org


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


Re: [GENERAL] Buglist

2003-08-19 Thread Bo Lorentsen
On Tue, 2003-08-19 at 16:03, Tom Lane wrote:

 It's still bolted on.  The entire concept that transactional integrity
 is optional is ludicrous, IMHO.  Integrity and optional are
 contradictory.
Good point. Also the problem of MyISAM and InnoDB RI :-)

 One thing you should ask about MySQL is where they keep the system's
 metadata (catalog data).  In Postgres it's under transactional control
 just like everything else, which means it's (a) crash-safe and (b)
 rollback-able.  This is why all DDL changes are rollback-able in PG.
 I honestly don't know what the corresponding arrangements are in MySQL
 ... but I suspect that even in an all-InnoDB database, there is critical
 system data that is outside the InnoDB table handler and thus not
 transaction-safe.
Thats a really nice thing for temporary tables, but point in time
backup is a much stonger argument :-)

/BL


---(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] 7.3.4 RPM

2003-08-19 Thread Guy Fraser
Before anyone can make an rpm for you they will need some more information.

What type of CPU are you using ? {SPARC, ALPHA, Pentium ...}

What kernel, and libraries are you using?

Good luck

Guy

Vilson farias wrote:

Hi again

I'm still using RedHat 6.2. I would be happy if I could find some PostgreSQL
7.3.4 RMPs for this old version of RedHat Linux. Are you planning to release
it? Maybe you should start a new User Survey at postgresql.org main page
asking people that use Linux wich version/release they use. This information
could be valuable to coordenate RPMs generation effort.
Best regards,



José Vilson de Mello de Farias
Software Engineer
Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: [EMAIL PROTECTED]
Tel.: +55 48 281 7158
ICQ 11866179


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

 

--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787
There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.




---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


Re: [GENERAL] Bulk Insert / Update / Delete

2003-08-19 Thread Jason Godden
Hi Philip,

Pg is more ansi compliant than most (GoodThing (TM)).  You can use the 'when' 
conditional but not to do what you need.  If I understand you correclty you 
should be able to acheive the same result using two seperate queries and the 
(NOT) EXISTS or (NOT) IN clause.  Failing that have a look at the fine docs 
on pl/pgsql and other postgresql procedural languages which allow you to use 
loops and conditional statements like 'if'.

Rgds,

J

On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
 I want to be able to generate SQL statements that will go through a list of
 data, effectively row by row, enquire on the database if this exists in the
 selected table- If it exists, then the colums must be UPDATED, if not, they
 must be INSERTED.

 Logically then, I would like to SELECT * FROM TABLE
 WHERE Values entered here, and then IF FOUND
 UPDATE TABLE SET  Values entered here ELSE
 INSERT INTO TABLE VALUES Values entered here
 END IF;

 The IF statement gets rejected by the parser. So it would appear that
 PostgreSQL does not support an IF in this type of query, or maybe not at
 all.

 Does anyone have any suggestions as to how I can achieve this ?


 This message is privileged and confidential and intended for the addressee
 only. If you are not the intended recipient you may not disclose, copy or
 in any way use or publish the content hereof, which is subject to
 copyright.If you have received this in error, please destroy the original
 message and contact us at [EMAIL PROTECTED] Any views expressed in this
 message are those of the individual sender, except where the sender
 specifically states them to be the view of Computerkit Retail Systems, its
 subsidiaries or associates. Please note that the recipient must scan this
 e-mail and attachments for  viruses. We accept no liability of whatever
 nature for any loss, liability,damage or expense resulting directly or
 indirectly from this transmission of this message and/or attachments.

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

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


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


Re: [GENERAL] Buglist

2003-08-19 Thread Bo Lorentsen
On Tue, 2003-08-19 at 16:20, Lincoln Yeoh wrote:

 Install an application that can use both DBs. Muck around with it. If you 
 can't tell the difference, then I'd say go with postgresql - transactions 
 isn't bolted on, quite a number of other design wins too. If you can tell 
 the difference and MySQL is better, many of us here would be interested to 
 know.
Ok, thanks, we may need to make a test utility, that is open and fair,
but thats a little hard, as PG have some featurs that MySQL does not.

 Do lots of concurrent updates and inserts and selects for a long time?
I do think I know why you say this :-)

 Have fun!
I like to do this, but I'm not sure that I have the time needed. If I
have the time, and I get some results, I let you now.

/BL


---(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] Mailing list in French

2003-08-19 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes:
 Yeah, but there's not too much you can do about it.  You can check what
 goes on in the spanish list at
 http://tlali.iztacala.unam.mx/mailman/listinfo/pgsql-ayuda

 There's a respectable amount of traffic, and I don't think there's
 anyone who would want to translate it.

Yeah, it could only be workable if automatic translation (babelfish)
were good enough.  I have my doubts...

regards, tom lane

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

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


Re: [GENERAL] Bulk Insert / Update / Delete

2003-08-19 Thread Doug McNaught
Philip Boonzaaier [EMAIL PROTECTED] writes:

 I want to be able to generate SQL statements that will go through a list of
 data, effectively row by row, enquire on the database if this exists in the
 selected table- If it exists, then the colums must be UPDATED, if not, they
 must be INSERTED.
 
 Logically then, I would like to SELECT * FROM TABLE
 WHERE Values entered here, and then IF FOUND
 UPDATE TABLE SET  Values entered here ELSE
 INSERT INTO TABLE VALUES Values entered here
 END IF;
 
 The IF statement gets rejected by the parser. So it would appear that
 PostgreSQL does not support an IF in this type of query, or maybe not at
 all.

Nope.  I don't know of an SQL database that does, though I certainly
haven't seen all of them...

 Does anyone have any suggestions as to how I can achieve this ?

Application code that loops through the results of the first query,
and issues UPDATE/INSERT statements as needed?  Or you could do it as
a PL/pgSQL function which might be a little faster.

 This message is privileged and confidential and intended for the addressee only. If 
 you are not the intended recipient you may not disclose, copy or
 in any way use or publish the content hereof, which is subject to copyright.If you 
 have received this in error, please destroy the original message
 and contact us at [EMAIL PROTECTED] Any views expressed in this message
 are those of the individual sender, except where the sender specifically
 states them to be the view of Computerkit Retail Systems, its subsidiaries or
 associates. Please note that the recipient must scan this e-mail and attachments for 
  viruses. We accept no liability of whatever nature for any loss,
 liability,damage or expense resulting directly or indirectly from this transmission
 of this message and/or attachments.

I have companies that force crap like this on mailing list postings...

-Doug

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


Re: [GENERAL] Bulk Insert / Update / Delete

2003-08-19 Thread Doug McNaught
Doug McNaught [EMAIL PROTECTED] writes:

 Philip Boonzaaier [EMAIL PROTECTED] writes:
 
  This message is privileged and confidential and intended for the addressee only. 
  If you are not the intended recipient you may not disclose, copy or
  in any way use or publish the content hereof, which is subject to copyright.If you 
  have received this in error, please destroy the original message
  and contact us at [EMAIL PROTECTED] Any views expressed in this message
  are those of the individual sender, except where the sender specifically
  states them to be the view of Computerkit Retail Systems, its subsidiaries or
  associates. Please note that the recipient must scan this e-mail and attachments 
  for  viruses. We accept no liability of whatever nature for any loss,
  liability,damage or expense resulting directly or indirectly from this transmission
  of this message and/or attachments.
 
 I have companies that force crap like this on mailing list postings...
 hate

Arrghh.

-Doug

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


Re: [GENERAL] Bulk Insert / Update / Delete

2003-08-19 Thread Shridhar Daithankar
On Tuesday 19 August 2003 20:54, Doug McNaught wrote:
 Doug McNaught [EMAIL PROTECTED] writes:
  I have companies that force crap like this on mailing list postings...

  hate

 Arrghh.

Not to troll, but another  mailing list I am on, anybody posting such 
messages/footers is politely excused with links to free webmail services that 
offer clean text mails. Some known domains are also barred from joining 
mailing lists,,

Can not afford to spam excess to 3000 subscribers most of whom pay expensive 
metered dial up access. The is a justified logic behind the actions.

 Shridhar


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

2003-08-19 Thread Vivek Khera
 BL == Bo Lorentsen [EMAIL PROTECTED] writes:

BL Hi ...
BL I'm trying to convince my boss to use posgresql (I need RI, transactions
BL and views), but he keeps comparing the project to mysql. Until now, I
BL found the answers to he's questions on the www.postgresql.org page, but
BL now I'm lost :-)

My big reason to choose postgres was concurrency.  My application has
transactions and updates all over the place, and even as a single
developer in the early stages, I could already see problems with
table-level locking that mysql was giving me.  Who knows what would
have happened in production with hundreds of people hitting the db
simultaneously!  The row-level locking in Postgres has made it
possible for an incredible number of simultaneous actions to be
carried out without any waiting for the users.

Try making a table grow beyond your file size limit in mysql.  You
can't.  Even if you use an OS with 64-bit file pointers (such as
FreeBSD) you can't grow your file beyond 4Gb in mysql (at least with
mysam tables -- dunno about innodb tables).  In Postgres, it is
automagically handled for you.

The *only* drawbacks I find with postgres is the need to dump/restore
on major version updates and the need to vacuum the tables
regularly...

Tops on my wish list is that postgres automatically notice when a row
is no longer needed (all transactional references to it are gone) and
'free' it at that time, rather then needing a special scan to
determine the row is no longer needed and freeing it.

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.Khera Communications, Inc.
Internet: [EMAIL PROTECTED]   Rockville, MD   +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

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


Re: [GENERAL] Mailing list in French

2003-08-19 Thread Francois Suter
I'd be glad to join, but I couldn't find where or how to subscribe...
It's not yet posted on the web site, but you can subscribe by sending a 
mail to [EMAIL PROTECTED], leave the subject line blank and put 
in the body SUBSCRIBE pgsql-fr-generale.

Bye

---
Francois
Home page: http://www.monpetitcoin.com/

Would Descartes have programmed in Pascal? - Umberto Eco

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html


Re: [GENERAL] Buglist

2003-08-19 Thread Shridhar Daithankar
On Tuesday 19 August 2003 21:03, Vivek Khera wrote:
 Tops on my wish list is that postgres automatically notice when a row
 is no longer needed (all transactional references to it are gone) and
 'free' it at that time, rather then needing a special scan to
 determine the row is no longer needed and freeing it.

Heh.. we have autovacuum right. Well it does not work the way you want but it 
works automatically at least.

Couple of releases down the line it will be good enough and vacuum problems 
will be history if people take care to setup autovacuum and FSM parameters 
correctly..

 Shridhar


---(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] Mailing list in French

2003-08-19 Thread Guillaume LELARGE
Le Mardi 19 Août 2003 15:27, Erwan DUROSELLE a écrit :
 I'd be glad to join, but I couldn't find where or how to subscribe...

Juste send a mail to [EMAIL PROTECTED] with
subscribe pgsql-fr-generale
in your email's body.


-- 
Guillaume !-- http://absfr.tuxfamily.org/ --.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [GENERAL] Grouping by date range

2003-08-19 Thread Ron Johnson
On Tue, 2003-08-19 at 02:56, Alexander Litvinov wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 I home your date field have date type. If it is try this:
 
 select date_part('year', date), count(*) from your_table group by 
 date_part('year', date) order by date_part('year', date);

Is the ORDER BY really needed here?

 for month add grouping by date_part('month', date)
 
 if you need to handle large number of rows try to add columns with year and 
 month, write triggers for filling this columns, make indexes and things 
 should be fast.
 
  date| data
  ---
  01/01/01| 123
  01/01/01| abc
  02/01/01| def
  03/03/01| hij
 
  I can see how to group by day - but how do i go about decreasing the
  precision down to months/years.

-- 
-
Ron Johnson, Jr. [EMAIL PROTECTED]
Jefferson, LA USA

My advice to you is to get married: If you find a good wife, 
you will be happy; if not, you will become a philosopher.
Socrates


---(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] Mailing list in French

2003-08-19 Thread Guillaume LELARGE
Le Mardi 19 Août 2003 15:35, Francois Suter a écrit :
  I'd be glad to join, but I couldn't find where or how to subscribe...

 It's not yet posted on the web site, but you can subscribe by sending a
 mail to [EMAIL PROTECTED], leave the subject line blank and put
 in the body SUBSCRIBE pgsql-fr-generale.

Sorry, I didn't see you already replied  : /


-- 
Guillaume !-- http://absfr.tuxfamily.org/ --.

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


Re: [GENERAL] Buglist

2003-08-19 Thread Bruno Wolff III
On Tue, Aug 19, 2003 at 19:17:31 +0530,
  Shridhar Daithankar [EMAIL PROTECTED] wrote:
 
 Making pgsql-bugs a open to non-subscription but moderated list might be a 
 good idea. It really does not matter if a bug gets filed couple of days late 
 but having to have subscribe to another list could be ditterent.

All of the pgsql lists including bugs already work this way.

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


Re: [GENERAL] Buglist

2003-08-19 Thread Joshua D. Drake


It's still bolted on.  The entire concept that transactional integrity
is optional is ludicrous, IMHO.  Integrity and optional are
contradictory.
Obviously you have never voted in a major election ;)

			regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html
 



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


Re: [GENERAL] Buglist

2003-08-19 Thread scott.marlowe
On 19 Aug 2003, Bo Lorentsen wrote:

 On Tue, 2003-08-19 at 18:17, Vivek Khera wrote:
 
  Since the beginning of time (at least MySQL v3.22) MySQL has silently
  ignored the foreign key references in table create statement.  Now
  that they have foreign key support (version 4.x), do they honor those
  statements?  Nope.  You have to use their own syntax to declare your
  FKs.  They still silently ignore the references in the table create
  statements.
 Is this really true ?? Does 4.x still not support FK, then how about
 transactions, does they that not work too ?
 
 Is this not just the MyISAM tables that still got the problem (they are
 verison 3.x) ?

No, the problem is that in SQL spec, you do it with the foreign key 
declaration inside parnes in the create statement like:

create table abc123 
(
  id serial unique, 
  info text);
create table abc1234 
(
  moreinfo text, 
  ref_id int, 
  foreign key (ref_id) 
  references abc123(id)
  on delete cascade
);

In MySQL this syntax is silently swallowed, while their own proper 
syntax is like this:

create table abc123 
(
  id serial unique, 
  info text)
type=innodb;
create table abc1234 
(
  moreinfo text, 
  ref_id int)
foreign key (ref_id) references abc123(id)
on delete CASCADE
type=innodb;

So the syntaxes are different, and one is apparently swallowed without 
error or anything, but in fact you have no fks in place.



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


Re: [GENERAL] 7.3.4 RPM

2003-08-19 Thread Sander Steffann
Hi,

 Before anyone can make an rpm for you they will need some more
information.

 What type of CPU are you using ? {SPARC, ALPHA, Pentium ...}

 What kernel, and libraries are you using?

I will build them for RedHat 6.2 and 7.3 this afternoon. You can find them
in a few hours at http://opensource.nederland.net/, and maybe Lamar can put
them on ftp.postgresql.org.

Bye,
Sander


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


Re: [GENERAL] Buglist

2003-08-19 Thread Bo Lorentsen
On Tue, 2003-08-19 at 23:10, scott.marlowe wrote:

 So the syntaxes are different, and one is apparently swallowed without 
 error or anything, but in fact you have no fks in place.
Thanks, that helped.

/BL


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


Re: [GENERAL] Buglist

2003-08-19 Thread Ian Barwick
On Tuesday 19 August 2003 23:10, scott.marlowe wrote:
 On 19 Aug 2003, Bo Lorentsen wrote:
  On Tue, 2003-08-19 at 18:17, Vivek Khera wrote:
   Since the beginning of time (at least MySQL v3.22) MySQL has silently
   ignored the foreign key references in table create statement.  Now
   that they have foreign key support (version 4.x), do they honor those
   statements?  Nope.  You have to use their own syntax to declare your
   FKs.  They still silently ignore the references in the table create
   statements.
 
  Is this really true ?? Does 4.x still not support FK, then how about
  transactions, does they that not work too ?
 
  Is this not just the MyISAM tables that still got the problem (they are
  verison 3.x) ?

 No, the problem is that in SQL spec, you do it with the foreign key
 declaration inside parnes in the create statement like:

 create table abc123
 (
   id serial unique,
   info text);
 create table abc1234
 (
   moreinfo text,
   ref_id int,
   foreign key (ref_id)
   references abc123(id)
   on delete cascade
 );

 In MySQL this syntax is silently swallowed, while their own proper
 syntax is like this:

 create table abc123
 (
   id serial unique,
   info text)
 type=innodb;
 create table abc1234
 (
   moreinfo text,
   ref_id int)
 foreign key (ref_id) references abc123(id)
 on delete CASCADE
 type=innodb;

(To be precise this will fail with an obscure message; an
index must be created on ref_id)

 So the syntaxes are different, and one is apparently swallowed without
 error or anything, but in fact you have no fks in place.

Just to confuse things further:
1: if the MySQL version running is not configured for innodb tables,
tables created with type=innodb will be silently converted to
MyISAM;

2: These statements will succeed:
create table abc123 (
  id INT unique,
  info text
) type=innodb;

create table abc1234  (
  moreinfo text,
  ref_id int REFERENCES abc123(id)
)  type=innodb;

but the foreign key defined on ref_id is (I presume)
transported to a remote forest in Sweden and eaten
by goats ;-)

Ian Barwick
[EMAIL PROTECTED]


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


Re: [GENERAL] Buglist

2003-08-19 Thread Matthew T. O'Connor
On Tue, 2003-08-19 at 12:13, Vivek Khera wrote:
 There's a big difference between noticing that a table needs to be
 vacuumed and running it and automatically having the backend free a
 row as soon as we know it is eligible to be (as would normally be
 determined by vacuum).

talking beyond my real knowledge
Changing Postgres to perform as mentioned above is non-trivial, it would
basicially change the entire core of the system.  I think this is due to
the fact that postgres uses a non-overwriting storage manager.  This has
many benefits including MVCC, the primary disadvantage is that you need
a vacuum type process
/talking beyond my real knowledge

 One of these days when I can afford a 14-hour dump/restore, I'll
 upgrade to 7.4 and try autovacuum :-)

pg_autovacuum does with with 7.3.x, but the source is only included in
7.4.  Just get the pg_autovacuum directory from contrib and use it.

Matthew


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


Re: [GENERAL] postmaster(s) have high load average

2003-08-19 Thread Matthew T. O'Connor

On Sat, 2003-08-09 at 21:25, Christopher Browne wrote:
 In 7.3 and 7.4, the contrib application, pg_autovacuum can do the
 trick, vacuuming anything that reaches thresholds of
 inserts/deletes/updates, and do so more or less as often as necessary.

Actually pg_autovacuum is not included with 7.3, but works just fine
once you get it compiled.

 If you haven't got a cron job looking something like:
 
0 0 * * * * vacuumdb -a -z  /dev/null 2 /dev/null
 
 then you should probably add that, at least.

might be better to have it only vacuum a few specific tables that cause
most of your problems.  


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


Re: [GENERAL] Buglist

2003-08-19 Thread Vivek Khera
 MTO == Matthew T O'Connor [EMAIL PROTECTED] writes:

MTO talking beyond my real knowledge
MTO Changing Postgres to perform as mentioned above is non-trivial, it would
MTO basicially change the entire core of the system.  I think this is due to
MTO the fact that postgres uses a non-overwriting storage manager.  This has
MTO many benefits including MVCC, the primary disadvantage is that you need
MTO a vacuum type process
MTO /talking beyond my real knowledge

I'm not promoting any change in the MVCC.  What I'm saying is that it
would be really cool if the backend process itself could recognize
that a row is no longer referenced by any transactions upon
termination of the transaction, and release it back to the system.
This is just what vacuum does but in a batched manner.  I would love
to see it incremental.  This would result in pretty much near zero
internal fragmentation, I think.

How hard that is, I have no clue.  Right now my DB is saturating the
disk and having to squeeze vacuum into a saturated disk bandwidth is
not pleasant.  Luckily, the 14-disk raid array just
arrived... hopefully that will have higher bandwidth than the 4-disk
array... ;-)



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


Re: [GENERAL] 7.3.4 RPM

2003-08-19 Thread Lamar Owen
On Tuesday 19 August 2003 17:46, Sander Steffann wrote:
 I will build them for RedHat 6.2 and 7.3 this afternoon. You can find them
 in a few hours at http://opensource.nederland.net/, and maybe Lamar can put
 them on ftp.postgresql.org.

Ah, there you are.  Good.  I'll upload them tomorrow when I'm back on my T1.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org