[GENERAL] PostgreSQL 7.4.1 Released

[Marc G. Fournier]

Its been almost 4 weeks since PostgreSQL 7.4 was released, and, as with
all new releases, several bugs have been identified as administrators
migrate their production databases up from older releases.

As the list of Changes since 7.4 is fairly small, they are included in
this email:

 * Fixed bug in "CREATE SCHEMA" parsing in ECPG (Michael)
 * Fix compile error when "--enable-thread-safety" and "--with-perl"
   are used together (Peter)
 * Fix for subqueries that used hash joins (Tom)
 * Fix free space map compaction bug (Tom)
 * Fix for Borland compiler build of libpq (Bruce)
 * Fix netmask() and hostmask() to return the maximum-length masklen
   (Tom)
 * Several "contrib/pg_autovacuum" fixes
 * Allow compile of "contrib/cube" under Cygwin (Jason Tishler)
 * Fix Solaris use of password file when no passwords are defined
   (Tom)
 * JDBC fix for thread problems, other fixes
 * Fix for bytea index lookups (Joe)
 * Fix information schema for bit data types (Peter)
 * Force zero_damaged_pages to be on during recovery from WAL
 * Prevent some obscure cases of "variable not in subplan target
   lists"
 * Make PQescapeBytea and byteaout consistent with each other (Joe)
 * Escape bytea output for bytes > 0x7e(Joe)
 * Added missing SPI_finish() calls to dblink's
   get_tuple_of_interest() (Joe)
 * New Czech FAQ
 * Fix information schema view constraint_column_usage for foreign
   keys (Peter)
 * ECPG fixes (Michael)
 * Fix bug with multiple IN subqueries and joins in the subqueries
   (Tom)
 * Alllow COUNT('x') to work (Tom)
 * Install ECPG include files for Informix compatibility into
   separate directory (Peter)
 * Fix SSL memory leak (Neil)
 * Prevent "pg_service.conf" from using service name as default
   dbname (Bruce)
 * Fix local ident authentication on FreeBSD (Tom)

This version, as with most minor versions, does not require a dump/reload
to put into place.  That said, there are some changes to the information
schema that require a DROP/reload of that *specific* schema ... please
read the HISTORY file for instruction on how to do this.

As always, this release is available on all our mirrors, viable at:

http://www.postgresql.org/mirrors-www.html

And, thanks to David Fetter, is also available via BitTorrent at:

http://bt.postgresql.org


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

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

Re: [GENERAL] windows distribution

[Adrian Klaver]

On Monday 22 December 2003 08:16 am, Keith C. Perry wrote:

> I'm not familiar with the cygwin environment but is there something about
> that is turning off users to using it to run PG?
>
> It seems to me we should be seeing *more* ports about how PG is performing
> on cygwin instead of questions about a native port- especially from new
> users.
 
The problem is not so much Cygwin as the platform it runs on. It is Unix 
emulation on Windows and therefore is only as stable as the version of 
Windows you are using. I have run Postgres using Cygwin on various Win9x 
versions. The Cygwin installer makes the process easy to do. The problem is 
that it doesn't take much to load down the system. Also, given the frequency 
with which Win9X versions crash you spend alot of time waiting for the 
database to come up again. All in all it was more productive to move Postgres 
to a dedicated Linux server. 
-- 
Adrian Klaver   
[EMAIL PROTECTED]

---(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] CHECK versus a Table for an enumeration

[Alvaro Herrera]

On Mon, Dec 22, 2003 at 04:37:51PM -0500, Melanie Bergeron wrote:

> I want to know what's better between these 2 solutions :
> 
> CREATE TABLE user (
> ...
> user_type   text CHECK(user_type='Root' OR user_type = 'Admin' OR 
> user_type = 'Standard'));
> 
> or the following :
> 
> CREATE TABLE user_type(
> user_type_id integer   PRIMARY KEY,
> user_type_desc text);

Check out the second article at
 
http://www.varlena.com/varlena/GeneralBits/42.php

It contains some discussion on this issue.

-- 
Alvaro Herrera ()
"The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
(Samuel P. Huntington)

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

Re: [GENERAL] CHECK versus a Table for an enumeration

[Paul Thomas]

On 22/12/2003 21:37 Melanie Bergeron wrote:
Hi all!

I want to know what's better between these 2 solutions :

CREATE TABLE user (
...
user_type   text CHECK(user_type='Root' OR user_type = 'Admin' OR 
user_type = 'Standard'));

or the following :

CREATE TABLE user_type(
user_type_id integer   PRIMARY KEY,
user_type_desc text);
CREATE TABLE user (
...
user_type_id   integer,
CONSTRAINT user_type_exists FOREIGN KEY (user_type_id) REFERENCES 
user(user_type_id));

I am really confused so I'll wait for your advices.

Thanks,

Melanie
just my personal opinion but here goes:

For the example you've provided I don't think theres much in it. The 
second version would give you ability to change the text of the user type 
if that were important and to add new user types without having to alter 
constraints. So if I _had_ to choose, I'd take the second option.

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

Re: [GENERAL] COPY - Ignore Problems

[Dann Corbit]

How about this:

Let's call your current table tab.
Insert into a table with the same shape as your table tab called 'lfd'.
Create an index on table lfd on fields lname, fname, workdate.
Delete from lfd where lfd.lname = tab.lname and lfd.fname = tab.fname
and lfd.workdata = tab.workdate
Insert into tab select * from lfd

Or something like that.  SQL*Server has something called
IGNORE_DUPLICATES.  It is the only database I recall that has that
feature.

> -Original Message-
> From: Ron St-Pierre [mailto:[EMAIL PROTECTED] 
> Sent: Monday, December 22, 2003 3:48 PM
> To: [EMAIL PROTECTED]
> Subject: [GENERAL] COPY - Ignore Problems
> 
> 
> I wish to insert data into a table from a very large text 
> file (from a 
> cron script) using COPY. However if the lName (TEXT), fName(TEXT), 
> workDate(DATE) already exist I don't want to insert data and 
> just want 
> to move onto the next record. Is there any way I can tell my bash 
> script/COPY to ignore the case where the unique constraint exists 
> (lName,fName,workDate), and move on to the next record?
> 
> ps 7.4, debian stable
> TIA
> Ron
> 
> 
> ---(end of 
> broadcast)---
> TIP 7: don't forget to increase your free space map settings
> 

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

[GENERAL] COPY - Ignore Problems

[Ron St-Pierre]

I wish to insert data into a table from a very large text file (from a 
cron script) using COPY. However if the lName (TEXT), fName(TEXT), 
workDate(DATE) already exist I don't want to insert data and just want 
to move onto the next record. Is there any way I can tell my bash 
script/COPY to ignore the case where the unique constraint exists 
(lName,fName,workDate), and move on to the next record?

ps 7.4, debian stable
TIA
Ron
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Parser: parse error - please help...

[Alvaro Herrera]

On Mon, Dec 22, 2003 at 04:24:52PM -0700, Andrew Ayers wrote:

> INSERT INTO reg("customer number", "company name", address, city, state, 
> zip, phone, alt_phone, fax, alt_fax, iaccess, browser, provider, 
> software, sversion, ynintegrated, ynnewtest, login, password, txtnetid, 
> alias_1, alias_2, url, approval_password, ynauthalert, loi_date, 
> contract_eff, contract_term, term_code, main_eff, main_term, live_eff, 
> renewal_eff, cs_rep, txtactmanager, txtsalesrep, txtprojmanager, 
> modem_number, dbc_users, intbillableusers, country, mmowarning, 
> mmositefileflags, verified, buildtimedate, ynhipaaba) VALUES ('855', 
> 'Test Company', '1234 West Jazz Street', 'Tempe', 'AZ', '85203', '(602) 
> 123-4567', '(602) 123-4567', '(602) 123-4567', '(602) 123-4567', true, 
> 'Internet Explorer', DEFAULT, 'HP3.5', '13', false, false, 'eci855', 
> DEFAULT, DEFAULT, 'Test', 'Tester', 'http:\\www.eldocomp.com\', DEFAULT, 

Note here:^^

> false, DEFAULT, '09/11/2002', DEFAULT, DEFAULT, '09/11/2002', DEFAULT, 
> DEFAULT, DEFAULT, 'Chris Riesgraf ext. 244', 'Keri Daminelli ext. 246', 
> 'Kerry Winkle ext. 229', DEFAULT, DEFAULT, DEFAULT,
>   '0', 'USA', 'afdsf', DEFAULT, DEFAULT, DEFAULT, false);

You have an escaped ', which will make the string literal continue till
the next '.  You should really be escaping your \ and ' when they are
embedded in data.  Consider something like "Joh\n"; it will insert Joh
followed by a newline.

Also, an URL with \ is quite weird ... I think they like / best.

-- 
Alvaro Herrera ()
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)

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

Re: [GENERAL] restoring database

[Alvaro Herrera]

On Mon, Dec 22, 2003 at 08:14:51PM -0300, Lucas Lain wrote:

> > If it's a plain text dump, make a copy of the backup file.  Edit the
> > copy and delete everything that doesn't relate to the database to be
> > restored.
> 
> it's quite large to do this ...

Maybe you could try getting the line numbers for the data you want using
grep (be sure to anchor the patterns with ^ to make the search faster),
and then extract the data using either awk (see NR) or perl (see $.).
This way you don't have to open the dump with an editor, which would
load the whole file in memory.

-- 
Alvaro Herrera ()
"La fuerza no está en los medios físicos
sino que reside en una voluntad indomable" (Gandhi)

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

[GENERAL] Parser: parse error - please help...

[Andrew Ayers]

All,

I am having a problem with an INSERT onto a table I have created. First 
off, here is the table:

---

CREATE TABLE reg ("customer number" SERIAL PRIMARY KEY, "company name" 
VARCHAR(50) NULL, address VARCHAR(100) NULL, city VARCHAR(50) NULL, 
state VARCHAR(2) NULL, zip VARCHAR(50) NULL, phone VARCHAR(50) NULL, 
alt_phone VARCHAR(50) NULL, fax VARCHAR(50) NULL, alt_fax VARCHAR(50) 
NULL, iaccess BOOL NULL DEFAULT FALSE, browser VARCHAR(50) NULL, 
provider VARCHAR(50) NULL, software VARCHAR(50) NULL, sversion 
VARCHAR(10) NULL, ynintegrated BOOL NULL DEFAULT FALSE, ynnewtest BOOL 
NULL DEFAULT FALSE, login VARCHAR(50) NULL, password VARCHAR(50) NULL, 
txtnetid VARCHAR(11) NULL, alias_1 VARCHAR(50) NULL, alias_2 VARCHAR(50) 
NULL, url VARCHAR(255) NULL, approval_password VARCHAR(20) NULL, 
ynauthalert BOOL NULL DEFAULT FALSE, loi_date VARCHAR(50) NULL, 
contract_eff VARCHAR(50) NULL, contract_term VARCHAR(50) NULL, term_code 
VARCHAR(255) NULL, main_eff VARCHAR(50) NULL, main_term VARCHAR(50) 
NULL, live_eff VARCHAR(50) NULL, renewal_eff VARCHAR(50) NULL, cs_rep 
VARCHAR(50) NULL, txtactmanager VARCHAR(50) NULL, txtsalesrep
VARCHAR(50) NULL, txtprojmanager VARCHAR(50) NULL, modem_number 
VARCHAR(50) NULL, dbc_users VARCHAR(50) NULL, intbillableusers INT4 
NULL, country VARCHAR(50) NULL, mmowarning TEXT NULL, mmositefileflags 
TEXT NULL, verified VARCHAR(50) NULL, buildtimedate VARCHAR(50) NULL, 
ynhipaaba BOOL NULL DEFAULT FALSE);

---

I have a process which loops and inserts multiple records into this 
table. After processing approximately 200 records, it attempts to do the 
following insert:

---

INSERT INTO reg("customer number", "company name", address, city, state, 
zip, phone, alt_phone, fax, alt_fax, iaccess, browser, provider, 
software, sversion, ynintegrated, ynnewtest, login, password, txtnetid, 
alias_1, alias_2, url, approval_password, ynauthalert, loi_date, 
contract_eff, contract_term, term_code, main_eff, main_term, live_eff, 
renewal_eff, cs_rep, txtactmanager, txtsalesrep, txtprojmanager, 
modem_number, dbc_users, intbillableusers, country, mmowarning, 
mmositefileflags, verified, buildtimedate, ynhipaaba) VALUES ('855', 
'Test Company', '1234 West Jazz Street', 'Tempe', 'AZ', '85203', '(602) 
123-4567', '(602) 123-4567', '(602) 123-4567', '(602) 123-4567', true, 
'Internet Explorer', DEFAULT, 'HP3.5', '13', false, false, 'eci855', 
DEFAULT, DEFAULT, 'Test', 'Tester', 'http:\\www.eldocomp.com\', DEFAULT, 
false, DEFAULT, '09/11/2002', DEFAULT, DEFAULT, '09/11/2002', DEFAULT, 
DEFAULT, DEFAULT, 'Chris Riesgraf ext. 244', 'Keri Daminelli ext. 246', 
'Kerry Winkle ext. 229', DEFAULT, DEFAULT, DEFAULT,
  '0', 'USA', 'afdsf', DEFAULT, DEFAULT, DEFAULT, false);

---

This generates the following error (from within VB):

Error -2147467259 - ERROR:  parser: parse error at or near "09" at 
character 847

---

When I try the above insert in psql, I get a prompt like:

dbname'>

Here, if I input ' followed by RETURN, the prompt changes to:

dbname(>

Input ), the prompt changes to:

dbname->

Input ;, the error occurs:

ERROR:  parser: parse error at or near "09" at character 847

---

Something is obviously wrong - but what it is, I cannot tell. I have 
tried to simplify the problem by comparing prior inserts to this one, to 
see what the differences are, to no avail. It is like it is missing a 
paren or a quote, or has an extra - but that doesn't seem to be the case.

Am I missing something obvious, can somebody help?

Thank you,

Andrew L. Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is 
addressed, and may contain information that is privileged, confidential and exempt 
from disclosure under applicable law. If you are not the intended addressee, nor 
authorized to receive for the intended addressee, you are hereby notified that you may 
not use, copy, disclose or distribute to anyone the message or any information 
contained in the message. If you have received this message in error, please 
immediately advise the sender by reply email, and delete the message. 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

[GENERAL] Normalization and regexp

[MT]

Hi,

Since Canada Post hasn't programmed their automated web tools for calculating 
transportation costs to work with anything outside of Windows Internet Explorer, I'm 
obliged to write a web based program optimized for all those *other* browsers, myself. 
Part of this task requires that I set up tables in postgresql that match postal codes 
to transportation cost. 

Canada Post provides a booklet for calculating transportation cost based on package 
weight and the first three characters of the postal code sequence. For instance, if I 
want to send a package to an address that includes G8F 1X1 as the postal code, I take 
the first 3 characters G8F and look them up in table 1.

Table 1

PostalCodeTarrif number
---
G4V   14
G8E-G8G   14
G4R-G4S   13

Since G8F falls in the G8E-G8G range, I now know that the tarrif number is 14. Taking 
the number 14, I go to table 2

Table 2

   For tarrif Code 14
Weight(kg)   Price
--
  1.05.37
  1.55.61
  2.05.82

If the weight of my package is 1kg, the price is 5.37 to send the package to the 
address bearing G8F as the first 3 characters of the postal code.

To render this in the database, I have done the following:

_
   p_code|
=
  pcode_id   | tarrif|
-
G4V  |   14  |
-
G8E  |   14  |
-
G8F  |   14  |
-
G8G  |   14  |
-
G4R  |   13  |
-
G4S  |   13  |
-

__
   price_weight   |
== 
   tarrif   | weight(kg)|  price  | 
--
 14 |1.0|   5.37  |
--
 14 |1.5|   5.61  |
--
 14 |2.0|   5.82  |
--
 13 |1.0|   5.20  |
--
 13 |1.5|   5.32  |
--
 13 |2.0|   5.42  |


Therefore my sql statement would look something like this:

SELECT price FROM price_weight
WHERE p_code.tarrif = price_weight.tarrif
AND pcode = 'G8F'
AND weight = '1.0';

I think this will work, but before I proceed, I'd like to ask 2 questions:

1.
Is it possible to further normalize the data in the p_code and price_weight tables 
above? 

2.
Is it possible to abbreviate the number of records in the p_code table using regular 
expressions. For instance, to avoid repetition, I thought I'd use regular expressions, 
so that instead of entering the postal code into separate rows as such:

G4V 14
G8E 14
G8F 14
G8G 14

I could do something like this:

(G4V | G8[E-G]) 14

Somehow I don't think this is possible, but I'm looking for any way to minimize the 
number of postal codes that I have to enter, since there's a lot of them.

Anyway, I realize these questions may have more to do with database design than 
postgresql per se. If there's a better place to ask them, please point me in the right 
direction.

Thanks,

Mark

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

Re: [GENERAL] restoring database

[Lucas Lain]

>
> If it's a plain text dump, make a copy of the backup file.  Edit the
> copy and delete everything that doesn't relate to the database to be
> restored.

it's quite large to do this ...


> If you dumped in tar or special format, I believe you can use
>   pg_restore -d dbname
> but I haven't done that myself and am not sure if I'm interpreting the
> manpage correctly.

this database is only for connection ..


i like the split solution ... i will try



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

Re: [GENERAL] restoring database

[Oliver Elphick]

On Mon, 2003-12-22 at 14:51, Lucas Lain wrote:
> Hi everybody ... i need to restore only one database from a pg_dumpall backup 
> file... how can i do it?

If it's a plain text dump, make a copy of the backup file.  Edit the
copy and delete everything that doesn't relate to the database to be
restored.

If you dumped in tar or special format, I believe you can use
  pg_restore -d dbname
but I haven't done that myself and am not sure if I'm interpreting the
manpage correctly.

-- 
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
 
 "And there were in the same country shepherds abiding 
  in the field, keeping watch over their flock by night.
  And, lo, the angel of the Lord came upon them, and the
  glory of the Lord shone around them; and they were 
  sore afraid. And the angel said unto them, " Fear not;
  for behold I bring you good tidings of great joy which
  shall be to all people. For unto you is born this day 
  in the city of David a Saviour, which is Christ the 
  Lord."Luke 2:8-11 


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

Re: [GENERAL] Add Primary/Foreign Key in 7.3.2

[Melanie Bergeron]

You can do this by doing
ALTER TABLE mytable
   ADD CONSTRAINT myconstraint FOREIGN KEY (table_id) REFERENCES 
myothertable(table_id);

Melanie

Brian C. Doyle wrote:

Hello all,

I was wondering if there is any way to add a primary and/or foreign 
keys to existing tables in 7.3.2


 
Brian C. Doyle
Director, Internet Services
United Merchant Processing Association
http://www.umpa-us.com
1-800-555-9665 ext 212




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

[GENERAL] CHECK versus a Table for an enumeration

[Melanie Bergeron]

Hi all!

I want to know what's better between these 2 solutions :

CREATE TABLE user (
...
user_type   text CHECK(user_type='Root' OR user_type = 'Admin' OR 
user_type = 'Standard'));

or the following :

CREATE TABLE user_type(
user_type_id integer   PRIMARY KEY,
user_type_desc text);
CREATE TABLE user (
...
user_type_id   integer,
CONSTRAINT user_type_exists FOREIGN KEY (user_type_id) REFERENCES 
user(user_type_id));

I am really confused so I'll wait for your advices.

Thanks,

Melanie

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

Re: [GENERAL] hardware requirements under Redhat (fwd)

[Joshua D. Drake]

The last time I built an RH system, one with 1GB, I had to recompile
the kernel and change the 'High Memory Support' setting to get it to 
use the full 1GB.  

 

That was under RH 8, though.
 

Even on redhat 8, all you have to do is install the bigmem kernel... 
which does ship with RH8.





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



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for 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

Re: [GENERAL] hardware requirements under Redhat (fwd)

[Mike Nolan]

> >Does this config cover the above requirements very well.
> >Does anybody know if RedHat 9 or Fedora can address 2 Gig 
> >of RAM out of the box?
> >
> Yes they can.

The last time I built an RH system, one with 1GB, I had to recompile
the kernel and change the 'High Memory Support' setting to get it to 
use the full 1GB.  

That was under RH 8, though.
-
Mike Nolan


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

Re: [GENERAL] hardware requirements under Redhat

[Joshua D. Drake]

I am considering a generic box with a single 2 - 2.6 Gig processor.
2 Gig of RAM and mirrored 200 Gig drives.
 

Use RAID 5 or 0+1...



Does this config cover the above requirements very well.
Does anybody know if RedHat 9 or Fedora can address 2 Gig 
of RAM out of the box?

 

Yes they can.



many thanks

kd

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



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] hardware requirements under Redhat

[kbd]

Hello:

I know that hardware requirements are dependent upon many factors.
However I need a sanity check on this configuration.

First the purpose of the database.
  - database will be used to support a risk analysis group in a small
financial firm.  
  - there will be daily "bulk" loads to the database
  - as well as updates via GUIs (JSP or java programs)
  - initial size of the database will be 5Gig, could grow to 20 Gig quickly
  - database will support daily batch reports and adhoc quieies/reports
  - database is being designed with normalization and referential integrity
  in mind.  Yes, we are going to think before we build.
  - surrogate key will be used on the tables to reduce key size.
   it is expected that this will reduce the size of primary 
   and foreign keys.


I am considering a generic box with a single 2 - 2.6 Gig processor.
2 Gig of RAM and mirrored 200 Gig drives.

Does this config cover the above requirements very well.
Does anybody know if RedHat 9 or Fedora can address 2 Gig 
of RAM out of the box?

many thanks

kd

---(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] Groff and Weinberg SQL Complete Reference - Sample

[Robert Treat]

Paul,

once you get through the book perhaps you could submit a book review to
the techdocs website, along with a pg_dump of the sample database.

Robert Treat

On Mon, 2003-12-22 at 05:50, Richard Huxton wrote:
> On Monday 22 December 2003 07:47, Paul Ganainm wrote:
> > Not really a PostgreSQL question, but maybe somebody here knows the
> > answer!
> >
> >
> > I recently purchased Groff and Weinberg's The Complete Reference SQL,
> > and a fine meaty tome it is too!
> >
> >
> > However, I find to my absolute *_horror_* that there doesn't appear to
> > be an electronic version of the sample database - it's all down on paper
> > in Appendix A, but no way of downloading it electronically.
> >
> > On the CD, there are files such as office.dat where the data is in the
> > format
> >
> > 22|Denver|Western|108|30.00|186042.00
> > 11|New York|Eastern|106|575000.00|692637.00
> >
> > i.e. pipe delimited.
> 
> Read up on \copy and COPY - you can tell PG to use whatever delimiter you 
> want. Should make it straightforward then.
> 
> -- 
>   Richard Huxton
>   Archonet Ltd
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
>http://archives.postgresql.org

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


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

[GENERAL] How to hide database structure

[Michael Gill]

How can I hide all details from users regarding data and structure,
limiting access to data through functions I create?

I don't want a user to be able to see the structure of a table with \d
{tablename} in psql.

TIA

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

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

Re: [GENERAL] postmaster and logrotate

[Claudio Succa]

Il lun, 2003-12-22 alle 19:40, Dima Tkach ha scritto:
> Hi, everybody!
> 
> I was wonderring if there is a way to make logrotate work with postgres 
> logs?
> More precisely, the question is - is there any way to tell a running 
> postmaster process to reopen its log file?
> I tried kill -HUP ... but that doesn't seem to do anything...
> 
> Is there just no way to do what I want?
> 
> Thanks!
> 
> Dima

In /etc/logrotate.d create a file named i.e. postgres with more or less
the following contents:

/var/log/postgres.log {
weekly
missingok
rotate 4
copytruncate
compress
delaycompress
notifempty
}

Pls notate the 'copytruncate' option which enables the routing of the
output of syslogd to a new file.

Claudio

-- 
Claudio Succa
PERTEL - Torino - Italy
+39-011-437.4141
http://www.pertel.it
http://www.uniassist.it



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

Re: [GENERAL] postmaster and logrotate

[Bruce Momjian]

Dima Tkach wrote:
> Hi, everybody!
> 
> I was wonderring if there is a way to make logrotate work with postgres 
> logs?
> More precisely, the question is - is there any way to tell a running 
> postmaster process to reopen its log file?
> I tried kill -HUP ... but that doesn't seem to do anything...
> 
> Is there just no way to do what I want?

Docs say pipe into logrotate:

http://developer.postgresql.org/docs/postgres/logfile-maintenance.html

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] postmaster and logrotate

[Dima Tkach]

Hi, everybody!

I was wonderring if there is a way to make logrotate work with postgres 
logs?
More precisely, the question is - is there any way to tell a running 
postmaster process to reopen its log file?
I tried kill -HUP ... but that doesn't seem to do anything...

Is there just no way to do what I want?

Thanks!

Dima

---(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] bug in query planning?

[Steven D.Arnold]

On Dec 21, 2003, at 11:47 PM, Tom Lane wrote:

"Steven D.Arnold" <[EMAIL PROTECTED]> writes:
Query (2) below is the same query, but we reverse the order of the
tables.  It's obviously not quite the same query semantically, even
though in my case it should always produce the same result.
Since it is in fact not the same query, I'm unclear on why you expect
it to produce the same plan.
What I expect is for both queries to use the index on the messages 
table!  Why is it not doing that?

FWIW, I believe that 7.4 will recognize that (1) and (3) are
semantically equivalent.
I will try 7.4 and report back.

steve

---(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] Triggers for FK on Views - can they be made deferrable?

[Jan Wieck]

There is a way to create them as CONSTRAINT triggers. It's totally 
non-standard, not guaranteed to exist in future releases, yadda, yadda. 
But it get's you where you want to be now().

Jan

ezra epstein wrote:

I've got the case of a table which has unusual FK constraints.  I'm
implementing them as triggers against a view.  It all works, BUT I don't see
a way to make the triggers executed only on Commit -- i.e., I'd like the
same "deferrable" behavior that true FKs provide.  Does anyone know how to
do this?
Thanks,

Ezra E.



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


--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] FEATURE REQUEST: let arrays support null values.

[Tom Lane]

"ezra epstein" <[EMAIL PROTECTED]> writes:
> I've stumbled against the inability to put null values within an array.
> Are there any plans to remove that limitation?

It's on the TODO list.  I think Joe Conway has made noises about doing
something about it, but I dunno if he expects to get to it during the
7.5 development cycle or not.

> An pointers to where in the code I might start to look if I want to
> enhance this on my own?

The array code is mostly in 

src/include/utils/array.h
src/backend/utils/adt/arrayfuncs.c
src/backend/utils/adt/array_userfuncs.c
src/backend/utils/adt/arrayutils.c

regards, tom lane

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

[GENERAL] Where can I learn some more about the rule system?

[My Deja]

Where can I learn some more about the Rule system in PostgreSQL( ie
besides the manual)?

I'd also want to learn what query trees are generated by my queries?
How can I get them to show up? I am using pgAdmin3.

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

[GENERAL] Unix 2038 And PostgreSQL

[Sai Hertz And Control Systems]

Dear all,

Before reading all this let me assure you nothing is out of control just 
DONT PANIC

I was just going through the pages of  http://www.2038.org 
and was concerned by the fact my computer using Linux will rollover to

Fri Dec 13 20:45:52 1901

after

Tue Jan 19 03:14:07 2038

Now this was very serious for me as my clients use big dates for work
though my linux system indicated  it would be effected by 2038 (Run the 
perl script available on that site)

but a quick

select  date'2038-01-19'  +  1;
gave me result
2038-01-20
Now that was what I looking for
I believe that PostgreSQL guys have worked around the 2038 problem
any suggestions and further finding's are most welcome.

My Systems Config is ,
PostgreSQL 7.3.4
RH 9.0
Regards,
Vishal Kashyap.
Related Links ...
http://news.com.com/2100-7355-5129875.html
http://www.unix-systems.org/version2/whatsnew/year2000.html
http://www.UNIX-systems.org/go/unix .
http://developers.slashdot.org/developers/03/12/21/1952207.shtml?tid=126&tid=128&tid=156
---(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] BLOBS : how to remove them totally

[Nilabhra Banerjee]

Thanx for ur suggestion... But I face a new problem
now...

connection.setAutoCommit works well with postgresql
7.3 .. but with postgresql 7.4 I am getting the
error...
Error in connection == ERROR:  SET AUTOCOMMIT TO OFF
is no longer supported

I have tried pg73jdbc1.jar and pg73jdbc3.jar .. both
gave the same error

But in psql (7.4) the command \set AUTOCOMMIT off is
working. Strangely \set AUTOCOMMIT off is actually
changing the value of AUTOCOMMIT internal variable...
If I type \set autocommit off .. there will create
another variable 'autocommit' and set it to 'off'...
But this wont change the autocommit mode to off.. (The
documentation doesnot tell us of any such caps/small
behaviour!)

Regards
N Banerjee



 --- Kris Jurka <[EMAIL PROTECTED]> wrote: > 
> 
> On Sat, 20 Dec 2003, [iso-8859-1] Nilabhra Banerjee
> wrote:
> 
> > But unfortunately I could not extract this data to
> > frontend thru java... I tried in two ways but got
> the
> > same error...after getting the data in Blob or
> Large
> > Object.
> >
> > Error in connection == FastPath call returned
> ERROR:
> > invalid large-object descriptor: 0
> 
> This is usually a symptom of not being in a
> transaction.  Large objects
> need to be done inside a transaction.  Try adding
> connection.setAutoCommit(false) somewhere in your
> code.
> 
> Kris Jurka



Yahoo! Messenger - Communicate instantly..."Ping" 
your friends today! Download Messenger Now 
http://uk.messenger.yahoo.com/download/index.html

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

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

Re: [GENERAL] windows distribution

[Tony (Unihost)]

A good indicator of the demand that there is for a native Win32 port.  I 
know it's already in the works, but I think that the upswing in 
popularity of PG will be huge once the port is complete. 

Whether you love or loath Windows, it's hard to ignore the potential 
market share for a project like PG.  IMHO it's one of the keys to the 
success of MySQL, but I won't dredge up that ol' chestnut.

Cheers

T.

'Opinions Are Like Navels,  Everybody Has One!'

Russ Brown wrote:

On Fri, 19 Dec 2003 12:11:51 -0800 (PST), Jeff Eckermann 
<[EMAIL PROTECTED]> wrote:

--- Nikola Skoric <[EMAIL PROTECTED]> wrote:

Is there windows distribution of PostgreSQL? I
recieve contradictory
information :-) Some people say there is, some there
isn't. So, is
there? I've been browsing www.postresql.org
(http://www.postgresql.org/mirrors-ftp.html), but I
don't seem to find a
windows binary distribution... If there is one,
where can I download it?
There is no native Windows version of PostgreSQL, yet.
 Plenty of people use Cygwin emulation to run
PostgreSQL on Windows.  Reports are that it runs well
enough, but doesn't scale too well.  There also exists
a beta (proof of concept?) port of PostgreSQL 7.2 by
PeerDirect.  This is reported to work pretty well, but
is not supported by the PostgreSQL development group.
You can read more about these options at
http://techdocs.postgresql.org .
A native Windows port is being actively developed at
present.  No-one knows when it will be completed, but
we hope it will be ready for the next release, maybe
another four to six months.
--
Pozdrav/Regards, Nikola [Nick] Skoric.
"...Usne, tice-rugalice - a u oku tajac
Da sam kaput sa dva lica, da sam Gospo'n
Propalica..."
http://newusers.cjb.net/ - site o Usenetu na
hrvatskom!
---(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


__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
---(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


This question appears to have been posted to the lists pretty much 
every other day lately. Perhaps this information needs to be more 
prominently displayed on the front page of the site: perhaps a box 
containing logos for all major platforms linking either directly to 
the Downloads page or to more information on using PostgreSQL on that 
platform. I reckon that would do the trick.

---(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] windows distribution

[Russ Brown]

On Fri, 19 Dec 2003 12:11:51 -0800 (PST), Jeff Eckermann 
<[EMAIL PROTECTED]> wrote:

--- Nikola Skoric <[EMAIL PROTECTED]> wrote:
Is there windows distribution of PostgreSQL? I
recieve contradictory
information :-) Some people say there is, some there
isn't. So, is
there? I've been browsing www.postresql.org
(http://www.postgresql.org/mirrors-ftp.html), but I
don't seem to find a
windows binary distribution... If there is one,
where can I download it?
There is no native Windows version of PostgreSQL, yet.
 Plenty of people use Cygwin emulation to run
PostgreSQL on Windows.  Reports are that it runs well
enough, but doesn't scale too well.  There also exists
a beta (proof of concept?) port of PostgreSQL 7.2 by
PeerDirect.  This is reported to work pretty well, but
is not supported by the PostgreSQL development group.
You can read more about these options at
http://techdocs.postgresql.org .
A native Windows port is being actively developed at
present.  No-one knows when it will be completed, but
we hope it will be ready for the next release, maybe
another four to six months.
--
Pozdrav/Regards, Nikola [Nick] Skoric.
"...Usne, tice-rugalice - a u oku tajac
Da sam kaput sa dva lica, da sam Gospo'n
Propalica..."
http://newusers.cjb.net/ - site o Usenetu na
hrvatskom!
---(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


__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
---(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
This question appears to have been posted to the lists pretty much every 
other day lately. Perhaps this information needs to be more prominently 
displayed on the front page of the site: perhaps a box containing logos 
for all major platforms linking either directly to the Downloads page or 
to more information on using PostgreSQL on that platform. I reckon that 
would do the trick.

--

Russell Brown

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

[GENERAL] Groff and Weinberg SQL Complete Reference - Sample database?

[Paul Ganainm]

Not really a PostgreSQL question, but maybe somebody here knows the 
answer!


I recently purchased Groff and Weinberg's The Complete Reference SQL, 
and a fine meaty tome it is too!


However, I find to my absolute *_horror_* that there doesn't appear to 
be an electronic version of the sample database - it's all down on paper 
in Appendix A, but no way of downloading it electronically. 

On the CD, there are files such as office.dat where the data is in the 
format

22|Denver|Western|108|30.00|186042.00
11|New York|Eastern|106|575000.00|692637.00

i.e. pipe delimited.


Why *_OH WHY_* is it not in bog standard DML format (i.e. INSERT INTO 
Table_Name VALUES(..., ..., ...)?

I'm going to have to arse about with commas, INSERT INTO &c., which runs 
the risk of typos, plus the table definitions are not in DDL format, in 
fact they're not there at all! Why not?


Has anyone gone to the trouble of putting this data into any sort of 
standard SQL (DDL + DML) format?


TIA.


Paul...


-- 

plinehan  x__AT__x  yahoo  x__DOT__x  com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro

Please do not top-post.


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