date:20061201


[EMAIL PROTECTED] wrote:

Hi Again,

I posted earlier about a problem installing 8.1.5 on Windows XP Home.
THat message was delayed, so it likely will not show up.

THe problem is occuring on execution of initdb. I skipped the cluster
creation in the installation so that I could run it manually, but it
made no difference.

The contents of the temp file for the initdb is:

The files belonging to this database system will be owned by user
postgres. This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory C:/Program
Files/PostgreSQL/8.1/data ... ok creating subdirectories ... initdb:
could not create directory C:/Program Files: File exists initdb:
removing contents of data directory C:/Program
Files/PostgreSQL/8.1/data

I tried specifying a folder that doesn't exist, and then I get a
permissions error.  Fine, I logged into the system under the
restricted account and tried running initdb again.


OK. Stop. You've tried a bunch of different things without finding out 
where the initial problem is.


You're running initdb manually, so you'll need to check the permissions 
are in place manually too.


I'm assuming you have a user postgres that will own the files and run 
the processes. Make sure that user postgres has full permissions on 
C:\Program Files\Postgresql\8.1


Log in as user postgres (or use runas to open a shell as that user) 
and then run:

  initd -D C:\Program Files\Postgresql\8.1\data ...
Note the double quotes

Let us know what happens then.

PS - it might be simpler to run the uninstaller, make sure you've 
deleted the PostgreSQL directories and user and just run it again.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] named cache


Matthew Peter wrote:

Is it possible to put an query result into memory? Like SELECT * from table 
WHERE
[...] CACHE cache_name TIMEOUT '1 hour'::interval; So if cache_name exists 
with
the same SQL statement, the result would be fetched from the cache, refreshing 
and
updating the cache with fresh results when it expires? Reducing disk reads, 
query
times, etc. 


No. That's not something PG does internally.

Sounds like you might want to look at memcached/pgmemcached.

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Separation of clients' data within a database


John McCawley wrote:


Of course I still have to modify my web app and schema to facilitate the 
new security structure, but I was never too worried about handling it in 
my app...My concern was allowing people direct access to the underlying 
DB while a) blocking them from viewing others' data, and b) without 
having to drastically modify the fundamental structure of my app.


I'd be tempted by your idea of copying the data to separate databases. 
Reduces impact on your app and gives you one crucial point to test 
against. The disadvantage is that it will only work for the simple 
per-user split you describe, nothing more complex.


Oh, and don't forget that users can see other user and database names.

--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] Restoring database from files on disk

2006-12-01 Thread veejar


Hello!

I had problems with my HDD and i have lost many files. From my pgsql
database files I have only base-folder. In this folder one is
important database:
base/45325101

I reinstall my postgresql-server.
I have created new database with same name and have copied all files
from my old folder to new folder. I have successful connect to this
database, but when I try to browse my tables, I see error message:

ERROR:  could not access status of transaction 18651428
DETAIL:  could not open file /var/db/pgsql/pg_clog/0011: No such
file or directory

How to correct this one database?

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

Re: [GENERAL] named cache

2006-12-01 Thread Willy-Bas Loos


maybe you would find materialized views interesting.
http://www.google.com/search?q=materialized+view+postgresql


On 12/1/06, Matthew Peter [EMAIL PROTECTED] wrote:


Is it possible to put an query result into memory? Like SELECT * from
table WHERE
[...] CACHE cache_name TIMEOUT '1 hour'::interval; So if cache_name
exists with
the same SQL statement, the result would be fetched from the cache,
refreshing and
updating the cache with fresh results when it expires? Reducing disk
reads, query
times, etc.





Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com

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

[GENERAL] COPY FROM and NULL AS does not work

2006-12-01 Thread Arnaud Lesauvage


HI List,

Trying to import data from a text file, with a semicolon as 
 the delimiter, double-quotes as the quoting character.


I would like empty strings to be inserted as NULL values in 
a varchar column. In the text file, they are writen as :

some columns;;some columns

I tried
COPY table (columns) FROM textfile
DELIMITER AS ';' NULL AS '' CSV;
and
COPY table (columns) FROM textfile
DELIMITER AS ';' NULL AS '' CSV QUOTE AS '';
but both commands insert empty strings where the text file's 
data is .


mydb=# CREATE TABLE tmptable
mydb-# (
mydb(#   one character varying,
mydb(#   two character varying
mydb(# )
mydb-# WITHOUT OIDS;
CREATE TABLE
mydb=#
mydb=# COPY tmptable (one, two)
mydb-# FROM 'E:\\Production\\Statistiques-Web\\temp.csv'
mydb-# DELIMITER AS ';' NULL AS '' CSV QUOTE AS '';
COPY
mydb=#
mydb=# SELECT * FROM tmptable WHERE one = '';
 one | two
-+-
 |
 |
(2 rows)

mydb=# SELECT * FROM tmptable WHERE one IS NULL;
 one | two
-+-
(0 rows)

Content of the temp.csv file is :
;
;

What am I doing wrong ?
Thanks !

--
Arnaud

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

Re: [GENERAL] Restoring database from files on disk


veejar wrote:

Hello!

I had problems with my HDD and i have lost many files. From my pgsql
database files I have only base-folder. In this folder one is
important database:
base/45325101



How to correct this one database?


You can't. You need all the files in data/ - recover the whole cluster 
and then dump the one you're interested in.


It that's impossible, there are some tools that can dump on-disk 
structures, but they're not designed for recovering a database and 
you're unlikely to get all your data back that way anyway.


Use your last backup - it'll save you time.
--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

Re: [GENERAL] Tsearch2 - Error using tsquery object in GROUP BBY

2006-12-01 Thread Teodor Sigaev

I'm using 8.1.4 at the moment but I guess I need to update. The 8.2 is 
looking really promising. So with 8.2 I don't need the subselect?


IMHO, don't need


headline function is slow enough.

You think?! ;)


I known :) - computing headline is a hard task
--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

  http://archives.postgresql.org/

Re: [GENERAL] COPY FROM and NULL AS does not work


Arnaud Lesauvage wrote:

HI List,

Trying to import data from a text file, with a semicolon as  the 
delimiter, double-quotes as the quoting character.


I would like empty strings to be inserted as NULL values in a varchar 
column. In the text file, they are writen as :

some columns;;some columns


I'm not sure you can do that. From the manuals:
The CSV format has no standard way to distinguish a NULL value from an 
empty string. PostgreSQL's COPY handles this by quoting. A NULL is 
output as the NULL string and is not quoted, while a data value matching 
the NULL string is quoted. Therefore, using the default settings, a NULL 
is written as an unquoted empty string, while an empty string is written 
with double quotes (). Reading values follows similar rules. You can 
use FORCE NOT NULL to prevent NULL input comparisons for specific columns.


Looks like you'll have to run a separate UPDATE query after the import 
(or pre-process your input file).


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Restoring database from files on disk


veejar wrote:

Unfortunately I don't have any other information: dumps, other folders
from pgsql root directory.
What tools?


Start here:
  http://sources.redhat.com/rhdb/

And you'll want to read and understand the Internals/Physical Storage 
chapters of the manuals.


From past experience recovering data in a variety of circumstances, I'd 
recommend getting what you can easily then writing the rest off as lost. 
Chances are you may never get everything in a consistent state, 
particularly as you've had disk problems which caused this in the first 
place.


PS - Don't forget to cc: the mailing list when you reply. There are 
plenty of people who know more than me around.


PPS - Make sure you have a backup of all the remaining files before you 
begin.


PPPS - Don't forget to put a proper backup strategy in place once you've 
recovered what you can.


Good Luck!

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] COPY FROM and NULL AS does not work

2006-12-01 Thread Dimitri Fontaine

Le vendredi 1 décembre 2006 11:28, Arnaud Lesauvage a écrit :
 HI List,

 Trying to import data from a text file, with a semicolon as
   the delimiter, double-quotes as the quoting character.

 I would like empty strings to be inserted as NULL values in
 a varchar column. In the text file, they are writen as :
 some columns;;some columns

As soon as you quote a string, COPY will consider it not to be NULL, but empty 
string if there's no content.
You have to express NULL as ;; with your settings, as shown here:

pgloader=# create table tmptable(one varchar, two varchar);
CREATE TABLE
pgloader=# copy tmptable from stdin DELIMITER AS ';' NULL AS '' CSV QUOTE 
AS '';
Entrez les données à copier suivies d'un saut de ligne.
Terminez avec un antislash et un point seuls sur une ligne.
 ;
 ;
 \.
pgloader=# select * from tmptable where one is null;
 one | two
-+-
 |
 |
(2 lignes)

Regards,
-- 
Dimitri Fontaine
http://www.dalibo.com/


pgpgJ9yTjgxcF.pgp
Description: PGP signature

Re: [GENERAL] sudden drop in delete performance

2006-12-01 Thread surabhi.ahuja

after my update 
I had my entire data drectory PGDATA removed, 
i had done initdb again
and did lot of inserts (the inserts have given the similar performance)
 
i then do a remove from the db, which is taking time.
when i had postgres 8.0.0 i did not turn fsyn off. 
that time i had moved the pg_xlog directory to a diff partition and created a 
link from PGDATA to its new location.
(i did this because i had heard tha it boosts performanne)
 
but I am doing the same here also (i mean with Postgres 8.1.5)
 
the only thing that i havent changed is the jdbc jar
which is still the one that i used with Postgres 8.0.0
(but will that make it slow?)
 
thanks,
surabhi
  



From: [EMAIL PROTECTED] on behalf of Ben Trewern
Sent: Wed 11/29/2006 7:13 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] sudden drop in delete performance


Did you 'vacuum analyze' after you did the update?
 
Make sure you have the correct indexes in place on your foreign keys.
 
Did you have fsync off on your previous installation?
 
Give some more details and I'm sure people will be able to give better advice 
than me.
 
Regards,
 
Ben

surabhi.ahuja [EMAIL PROTECTED] wrote in message news:[EMAIL 
PROTECTED]
I was using Postgres 8.0.0
 
I have upgraded it to Postgres 8.1.5
 
I have seen that the delete performance has degraded considerably.
 
Nothing else has changed.
 
Please help
thanks
regards
 
Surabhi

Re: [GENERAL] Separation of clients' data within a database

2006-12-01 Thread Niklas Johansson


How about this:

* Have one master schema that holds all physical tables. This schema  
is accessible only by the superuser.


* Create a schema which contains views that mirror the master schema.  
This is the schema that the customers connect to, each using a  
different db role, and since it's a mirror of the master schema, it  
means no change in app structure (except dropping rights management,  
see below).


* Let these views pull their data from the respective master schema  
table (i.e. SELECT * FROM ...) with the addition of a WHERE-clause on  
client_id, that uses a function: ...WHERE client_id IN (get_client_ids 
()).


* The 'get_client_ids()'-function should query a table in the master  
schema that keeps the client_id's that are assigned to each db role  
(e.g. SELECT client_id FROM foo WHERE role=CURRENT_USER), and return  
those client_id's. For a regular customer, it would return one  
client_id, for a supervisor kind of user, it would return two or  
more, perhaps even all, client_id's.


* Have UPDATE and INSERT rules on the views that store the data in  
the actual master schema tables. (The rules would of course have to  
add client_id, this time through a function that can only return one  
client_id.)


To conclude: one master schema, one mirrored customer schema that  
adapts to the db role, one additional table in the master schema to  
handle the rights.



Sincerely,

Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] COPY FROM and NULL AS does not work

2006-12-01 Thread Arnaud Lesauvage


Richard Huxton a écrit :

Arnaud Lesauvage wrote:

HI List,

Trying to import data from a text file, with a semicolon as  the 
delimiter, double-quotes as the quoting character.


I would like empty strings to be inserted as NULL values in a varchar 
column. In the text file, they are writen as :

some columns;;some columns


I'm not sure you can do that. From the manuals:
The CSV format has no standard way to distinguish a NULL value from an 
empty string. PostgreSQL's COPY handles this by quoting. A NULL is 
output as the NULL string and is not quoted, while a data value matching 
the NULL string is quoted. Therefore, using the default settings, a NULL 
is written as an unquoted empty string, while an empty string is written 
with double quotes (). Reading values follows similar rules. You can 
use FORCE NOT NULL to prevent NULL input comparisons for specific columns.


Looks like you'll have to run a separate UPDATE query after the import 
(or pre-process your input file).


Indeed ! I thought that the NULL AS parameter would 
override this, but apparently not !


Thanks for this clarification (and thanks to Dimitri too) !
Since I have an INSERT trigger on this table, I can easily 
handle the '' - NULL there !


Regards
--
Arnaud

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

Re: [GENERAL] PostgreSQL doesn't accept connections when Windows

2006-12-01 Thread George Weaver



From: Jim Nasby


Hmm... are you sure there isn't already something listening on port  5435?


Hi Jim,

How might I determine this?

Would this make a difference when the system is rebooted vs manually 
restarting the server?


Thanks,
George






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

Re: [GENERAL] PostgreSQL doesn't accept connections when Windows

2006-12-01 Thread Thomas H.


How might I determine this?


netstat -a -n

this will list you all current listened ports
you could also try and

telnet localhost 5432

to see if something is listening.

Would this make a difference when the system is rebooted vs manually 
restarting the server?


when another program is occupying the port, it would probabyl not make much 
difference, except if the pgsql service is started before the other tool.


maybe pgsql is already running? ;-)

cheers,
thomas 




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

  http://archives.postgresql.org/

Re: [GENERAL] sudden drop in delete performance

2006-12-01 Thread Bill Moran

In response to surabhi.ahuja [EMAIL PROTECTED]:

 after my update 
 I had my entire data drectory PGDATA removed, 
 i had done initdb again
 and did lot of inserts (the inserts have given the similar performance)

Did you do a VACUUM ANALYZE after loading the new database?

Are you sure your indexes were recreated correctly?

Ben already asked those two questions, in addition to the fsync one.
They're important.

It would also be useful to provide EXPLAIN ANALYZE output for the query
that's giving you trouble.  Without it, the people on this list would
require some supernatural psychic ability to diagnose your problem.

 i then do a remove from the db, which is taking time.
 when i had postgres 8.0.0 i did not turn fsyn off. 
 that time i had moved the pg_xlog directory to a diff partition and created a 
 link from PGDATA to its new location.
 (i did this because i had heard tha it boosts performanne)
  
 but I am doing the same here also (i mean with Postgres 8.1.5)
  
 the only thing that i havent changed is the jdbc jar
 which is still the one that i used with Postgres 8.0.0
 (but will that make it slow?)
  
 thanks,
 surabhi
   
 
 
 
 From: [EMAIL PROTECTED] on behalf of Ben Trewern
 Sent: Wed 11/29/2006 7:13 PM
 To: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] sudden drop in delete performance
 
 
 Did you 'vacuum analyze' after you did the update?
  
 Make sure you have the correct indexes in place on your foreign keys.
  
 Did you have fsync off on your previous installation?
  
 Give some more details and I'm sure people will be able to give better advice 
 than me.
  
 Regards,
  
 Ben
 
   surabhi.ahuja [EMAIL PROTECTED] wrote in message news:[EMAIL 
 PROTECTED]
   I was using Postgres 8.0.0

   I have upgraded it to Postgres 8.1.5

   I have seen that the delete performance has degraded considerably.

   Nothing else has changed.

   Please help
   thanks
   regards

   Surabhi
 
 
 
 
 
 
 
 


-- 
Bill Moran
Collaborative Fusion Inc.

[EMAIL PROTECTED]
Phone: 412-422-3463x4023


IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.


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

Re: [GENERAL] Separation of clients' data within a database

2006-12-01 Thread John McCawley

That's the first idea I've seen that looks like it might actually 
work... (Not that the other ideas were bad, but I just couldn't see how 
I could fit the solutions into my current app)


So what would my user setup look like?  Would it look something like this:

createuser joe
grant select on schema company_a to joe
(whatever other permissions)
alter user joe set search_path='common','company_a';

createuser bob
grant select on schema company_b to bob
(whatever other permissions)
alter user bob set search_path='common','company_b';

How portable is all of this?  Could a comparable structure be 
implemented in MS SQL or Oracle?



Niklas Johansson wrote:


How about this:

* Have one master schema that holds all physical tables. This schema  
is accessible only by the superuser.


* Create a schema which contains views that mirror the master schema.  
This is the schema that the customers connect to, each using a  
different db role, and since it's a mirror of the master schema, it  
means no change in app structure (except dropping rights management,  
see below).


* Let these views pull their data from the respective master schema  
table (i.e. SELECT * FROM ...) with the addition of a WHERE-clause on  
client_id, that uses a function: ...WHERE client_id IN (get_client_ids 
()).


* The 'get_client_ids()'-function should query a table in the master  
schema that keeps the client_id's that are assigned to each db role  
(e.g. SELECT client_id FROM foo WHERE role=CURRENT_USER), and return  
those client_id's. For a regular customer, it would return one  
client_id, for a supervisor kind of user, it would return two or  
more, perhaps even all, client_id's.


* Have UPDATE and INSERT rules on the views that store the data in  
the actual master schema tables. (The rules would of course have to  
add client_id, this time through a function that can only return one  
client_id.)


To conclude: one master schema, one mirrored customer schema that  
adapts to the db role, one additional table in the master schema to  
handle the rights.



Sincerely,

Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly



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

Re: [GENERAL] initdb problem on Windows XP Home

Hi Richard,

Thanks for getting back to me.

Yes, I have a postgres limited account that will own the files.  (This was 
the one created by the installer.)  In
order to ensure the this user has access/full control to C:\Program 
Files\Postgresql\8.1, I used the cacls command
to edit the access control list and add postgres:f.
i.e. I ran
cacls C:\Program Files\PostgreSQL\8.1 /t /e /g postgres:f

(I've discovered this morning that I can boot in safe mode to get the Security 
tab on folder properties in XP Home. 
I'll check that this evening to see if it looks correct.)

However, to continue with your suggestion, after running the cacls statment 
above, I did log in as postgres and then
ran:
initd -D C:\Program Files\Postgresql\8.1\data ...
Yes, I did include the double quotes (due to the space in Program Files) but 
it was still the same result.  could not
create directory C:/Program Files: File exists.

This looks to me like a problem in the folder processing.  At first I thought 
that it wasn't handling the space in
Program Files correctly, so I tried a folder that had no spaces, but this had 
no effect.  The message suggests to me
that it is always trying to create the folder regardless of whether it exists 
or not.

Thank you very much for your reply.  If I have overlooked something, or if I'm 
completely out to lunch with what I've
done/tried, please let me know.  Also, any further suggestions would be 
welcomed.

Thanks again.

Alex.


 OK. Stop. You've tried a bunch of different things without finding out
 where the initial problem is.

 You're running initdb manually, so you'll need to check the permissions
 are in place manually too.

 I'm assuming you have a user postgres that will own the files and run
 the processes. Make sure that user postgres has full permissions on
 C:\Program Files\Postgresql\8.1

 Log in as user postgres (or use runas to open a shell as that user)
 and then run:
initd -D C:\Program Files\Postgresql\8.1\data ...
 Note the double quotes

 Let us know what happens then.

 PS - it might be simpler to run the uninstaller, make sure you've
 deleted the PostgreSQL directories and user and just run it again.

 --
Richard Huxton
Archonet Ltd

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


 [EMAIL PROTECTED] wrote:
  Hi Again,
 
  I posted earlier about a problem installing 8.1.5 on Windows XP Home.
  THat message was delayed, so it likely will not show up.
 
  THe problem is occuring on execution of initdb. I skipped the cluster
  creation in the installation so that I could run it manually, but it
  made no difference.
 
  The contents of the temp file for the initdb is:
 
  The files belonging to this database system will be owned by user
  postgres. This user must also own the server process.
 
  The database cluster will be initialized with locale C.
 
  fixing permissions on existing directory C:/Program
  Files/PostgreSQL/8.1/data ... ok creating subdirectories ... initdb:
  could not create directory C:/Program Files: File exists initdb:
  removing contents of data directory C:/Program
  Files/PostgreSQL/8.1/data
 
  I tried specifying a folder that doesn't exist, and then I get a
  permissions error.  Fine, I logged into the system under the
  restricted account and tried running initdb again.

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

[GENERAL] FK locking still too strong

2006-12-01 Thread Csaba Nagy

Hi all,

While postgres 8.x improved a lot the locking issues related to foreign
keys, the locking used is still stronger than needed.

The following test case deadlocks on postgres but goes through on
oracle:

preparation of tables: 

create table test1(col_fk integer primary key, col_1 integer);
create table test2(col_2 integer primary key, col_fk integer references
test1(col_fk));
insert into test1 (col_fk, col_1) values (1, 1);
insert into test1 (col_fk, col_1) values (2, 2);

session_1:

begin;
update test1 set col_1 = 10 where col_fk = 1;

session_2:

begin;
insert into test2 (col_2, col_fk) values (1, 2);

session_1:

-- this locks on postgres, does not on oracle
update test1 set col_1 = 20 where col_fk = 2;

session_2:

-- deadlock on postgres, goes through on oracle
insert into test2 (col_2, col_fk) values (2, 1);

The problem is that this deadlock cannot be solved by predictable
ordering of any of the 2 sets of statements, because the foreign key is
not sure to keep the same ordering relationship between the primary keys
of the tables (i.e. there can be a smaller col_fk associated to a bigger
col_2 as well as the other way around).

This kind of deadlock is causing us quite some trouble as we have some
operations like this where circumventing it would cause unacceptable
contention (practically serializing all our operation which we carefully
distribute to multiple processing boxes), even if the deadlock wouldn't
be per se a problem (but it is, there are complex updates/inserts on
thousands of rows in those transactions).

Our solution is to patch the postgres sources to omit the shared lock of
the parent row altogether. This leaves the possibility that some orphan
child records slip in if the parent row is deleted while the child row
is updated/inserted, but this is causing us less trouble than the
deadlock, as the delete rate is quite low in our application compared to
the update rate, and the orphan rows will be ignored anyway (of course
this would be unacceptable in many applications, it just happens to be
OK for us). In fact, the postgres regression suite is happily going
through with the patch applied (no locking on FK parent rows). And the
patched version will also not lock/deadlock on the above test case...

Now the real question: is it possible to further relax the lock needed
by the foreign key relationship ? I guess this has something to do with
some form of predicate locking, and I wonder if there is a simple way to
achieve this for supporting the FK locking.

The fact that Oracle doesn't lock/deadlock tells me that it must be
possible to do it, although it is likely not easy...

Cheers,
Csaba.




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

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL doesn't accept connections when Windows

2006-12-01 Thread George Weaver


Hi Thomas


netstat -a -n


Cool!

And -o gives me the pid of the process which I can match with processes 
through the Task Manager...


when another program is occupying the port, it would probabyl not make 
much difference, except if the pgsql service is started before the other 
tool.


maybe pgsql is already running? ;-)


The problem I'm having is that if the server (Windows 2000) is rebooted, the 
PostgreSQL service starts as expected, but won't accept connections.  Bur if 
the PostgreSQL service is then restarted manually (via Administrative Tools 
 Component Services  Restart), the service runs as expected and everything 
is normal.


If I understand what you're suggesting, perhaps upon reboot PostgreSQL is 
starting before another app that listens on the same port, which could cause 
the problem, but if I then restart the PostgreSQL service this solves the 
problem?


Regards,
George 




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

  http://archives.postgresql.org/

Re: [GENERAL] FK locking still too strong

2006-12-01 Thread Russ Brown


Csaba Nagy wrote:

Hi all,

While postgres 8.x improved a lot the locking issues related to foreign
keys, the locking used is still stronger than needed.

The following test case deadlocks on postgres but goes through on
oracle:

preparation of tables: 


create table test1(col_fk integer primary key, col_1 integer);
create table test2(col_2 integer primary key, col_fk integer references
test1(col_fk));
insert into test1 (col_fk, col_1) values (1, 1);
insert into test1 (col_fk, col_1) values (2, 2);

session_1:

begin;
update test1 set col_1 = 10 where col_fk = 1;

session_2:

begin;
insert into test2 (col_2, col_fk) values (1, 2);

session_1:

-- this locks on postgres, does not on oracle
update test1 set col_1 = 20 where col_fk = 2;

session_2:

-- deadlock on postgres, goes through on oracle
insert into test2 (col_2, col_fk) values (2, 1);



Purely out of interest I just tried this on MySQL 5.0.26 and found 
almost the same results. In MySQL session 1 was rolled back, on pg 8.1.5 
session 2 was rolled back.


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

Re: [GENERAL] initdb problem on Windows XP Home

2006-12-01 Thread Alejandro Michelin Salomon \( Adinet \)

[EMAIL PROTECTED] wrote:

Hi Richard,

Thanks for getting back to me.

Yes, I have a postgres limited account that will own the files. (This was
the one created by the installer.) In
order to ensure the this user has access/full control to C:\Program
Files\Postgresql\8.1, I used the cacls command
to edit the access control list and add postgres:f.
i.e. I ran
cacls C:\Program Files\PostgreSQL\8.1 /t /e /g postgres:f

(I've discovered this morning that I can boot in safe mode to get the Security tab on folder properties in XP Home.
I'll check that this evening to see if it looks correct.)

However, to continue with your suggestion, after running the cacls statment
above, I did log in as postgres and then
ran:
initd -D C:\Program Files\Postgresql\8.1\data ...
Yes, I did include the double quotes (due to the space in Program Files) but it
was still the same result. could not
create directory C:/Program Files: File exists.

Hmm. That doesn't make any sense to me. I'm guessing it's a different
error code behind the scenes. I suppose you can issue

cd C:/Program Files
dir
successfully as user postgres?

If so, try forward slashes to match the output of the error message:
C:/Program Files/Postgresql/...

If that's still not working and none of the Windows guys pop up, I'll
try and reproduce it on my XP-Pro laptop.

--
Richard Huxton
Archonet Ltd

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

[GENERAL] Problem with function parameters

Hi :
 
I have installed Pg 8.08
 
I create this function :
CREATE OR REPLACE FUNCTION CALCULO_VALOR_LIQUIDO_HELPDESK( nTipoDesconto
SMALLINT,
   nTipoComissao
SMALLINT,
   nDesconto
NUMERIC,
   nComissao
NUMERIC,
   nTotal NUMERIC )
RETURNS NUMERIC AS $$
 
DECLARE
nValorDesconto NUMERIC(14,2);
nValorComissao NUMERIC(12,2);
 
BEGIN
nValorDesconto := 0;
nValorComissao := 0;
 
IF nTipoDesconto = 0 THEN
nValorDesconto := nDesconto;
ELSE
nValorDesconto := ( nTotal * nDesconto ) / 100;
END IF;
 
IF nTipoComissao = 0 THEN
nValorComissao := nComissao;
ELSE
nValorComissao := ( nTotal * nComissao ) / 100;
END IF;
 
RETURN nTotal - nValorDesconto - nValorComissao;
END;
$$ LANGUAGE plpgsql
CALLED ON NULL INPUT
SECURITY INVOKER;
 
When i test this function, i call the function with this parameters :
 
SELECT CALCULO_VALOR_LIQUIDO_HELPDESK( 0, 1, 10, 10, 1000 );

 
This is the error that happens:
 
ERROR: function calculo_valor_liquido_helpdesk(integer, integer, integer,
integer, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may need to
add explicit type casts.
 
I dont now what the two first parameters are see as integer. When 0 and 1
are true smallint values.
 
What is happeining here?
 
Thanks in advance.
 
Alejandro Michelin Salomon.

Re: [GENERAL] PostgreSQL doesn't accept connections when Windows

2006-12-01 Thread Thomas H.

If I understand what you're suggesting, perhaps upon reboot PostgreSQL is 
starting before another app that listens on the same port, which could 
cause the problem, but if I then restart the PostgreSQL service this 
solves the problem?


if you can start it manually, then its probably not a problem with another 
tool using the same port.


a) do you have a software firewall installed on that machine that might be 
interfering here?


b) is your windows in a domain?
if so, make sure you manually set log on as a service priviledges on the 
domain controller for the local postgres user. the installed only sets local 
priviledges that are overwritten upon reboot.


you find the setting here: start - run - mmc - file - add snapin - 
add - group policy editor - browse - default domain policy
then browse for computer configuration - windows settings -  security 
settings - local policies - user rights - log on as a service. add the 
user there.


regards,
- thomas 




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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Problem with function parameters


Alejandro Michelin Salomon ( Adinet ) wrote:

Hi :
 
I have installed Pg 8.08
 
I create this function :

CREATE OR REPLACE FUNCTION CALCULO_VALOR_LIQUIDO_HELPDESK( nTipoDesconto
SMALLINT,
   nTipoComissao
SMALLINT,



When i test this function, i call the function with this parameters :
 
SELECT CALCULO_VALOR_LIQUIDO_HELPDESK( 0, 1, 10, 10, 1000 );


This is the error that happens:
 
ERROR: function calculo_valor_liquido_helpdesk(integer, integer, integer,

integer, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may need to
add explicit type casts.
 
I dont now what the two first parameters are see as integer. When 0 and 1

are true smallint values.


0 and 1 end up as literal integers, since you've not told it 
otherwise. You have two options:

1. Cast your parameters when you call the function
  select calculo_valor_...(0::smallint, 1::smallint, ...)
2. Define your function to take integers

I'd choose #2, you're not gaining anything by having smallint parameters.

--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] transfer just the data directories from linux to windows?

2006-12-01 Thread Anton Melser


Hi,
We have some backups from our prod server and I was wondering if there
would be any problems with just copying the data directory to a
windows install (same version - 8.1.4) ... any pointers?
Cheers
Antoine

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

Re: [GENERAL] Separation of clients' data within a database

2006-12-01 Thread Niklas Johansson



On 1 dec 2006, at 15.19, John McCawley wrote:
That's the first idea I've seen that looks like it might actually  
work... (Not that the other ideas were bad, but I just couldn't see  
how I could fit the solutions into my current app)


So what would my user setup look like?  Would it look something  
like this:


createuser joe
grant select on schema company_a to joe
(whatever other permissions)
alter user joe set search_path='common','company_a';

createuser bob
grant select on schema company_b to bob
(whatever other permissions)
alter user bob set search_path='common','company_b';


No, you wouldn't need separate schemas for each user, and the users  
should *not* be allowed access to the master schema. The views in the  
customer schema would, as I said, use a function (e.g. get_client_ids 
()) that uses CURRENT_USER (which will evaluate to either joe or bob,  
according to your example above) to lookup the actual client_ids.  
This means that you can grant every user the same rights on the  
customer schema views, and the rights management is done by the  
function (which is better than hardcoding values into the views; if  
the requirements change you just update the function), together with  
an additional table in the master schema. This table could look  
something like this:


role | client_id
-+--
joe  | 100
joe  | 101
bob  | 102

which would mean that joe is a supervisor that can see both client  
100 and client 101, while bob can see only client 102. You would  
probably need some other tables to keep track of which client_id  
should be used or allowed for data insertion if the user has more  
than one client_id, but you get the idea.


How portable is all of this?  Could a comparable structure be  
implemented in MS SQL or Oracle?


As far as I know, yes. (Quite some time since I last had anything to  
do with either of those. Not that I lament the fact... :-)




Sincerely,

Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] 8.1.5 installation problem with initdb on WinXP Home

2006-12-01 Thread Alejandro Michelin Salomon \( Adinet \)

Hi,

I apologize for being yet another noob posting about this problem.  I have 
spent 2 days searching the archives and the
net for a solution to no avail.  Hence my post here.

The problem I am having is on install of 8.1.5 it fails at the initdb call.  In 
checking the log, it seems to fail when
it creates the first data directory.  It gets past the setting file 
permissions, but when it tries to create data/global
it spits out an error message saying that C:\Program Files cannot be created. 
 File exists.  (Please excuse the
fragmented message, I'm going off memory here).  Of course at this point it 
rolls back the install.

Does anyone have a solution to this problem?  I would truly appreciate any help.

Thanks.
Alex.

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

Re: [GENERAL] Extract between year and month

2006-12-01 Thread Syl


Alban Hertroys wrote:
 Syl wrote:
  Try
 
  select *
   from tablename
  where title like ('%nith%')
 and date_trunc('month',recall_date::timestamp) = 
  date_trunc('month','1995-01-01'::timestamp)
 and date_trunc('month',recall_date::timestamp) = 
  date_trunc('month','2006-12-31'::timestamp)
order by recall_date
 

 Actually, that looks a lot like BETWEEN, which is even shorter :)

  select *
from tablename
   where title like ('%nith%')
 and date_trunc('month',recall_date::timestamp)
   between date_trunc('month','1995-01-01'::timestamp)
   and date_trunc('month','2006-12-31'::timestamp)
   order by recall_date


  Fantastic.
 
  Thank you to Matthias and Russell - everything is working perfectly!



Thank you for the accurate and detailed repsonses! All is working
excellent.


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

   http://archives.postgresql.org/

Re: [GENERAL] Create index on array element?

2006-12-01 Thread Dave Bodenstab


Jeff Davis wrote:

On Wed, 2006-11-29 at 14:39 -0600, Dave Bodenstab wrote:


I guess one cannot create an index on an element of an array?

test= create table test ( ordinal int[] );
CREATE TABLE
test= create unique index x1 on test ( ordinal[1] );
ERROR:  syntax error at or near ) at character 45
LINE 1: create unique index x1 on test ( ordinal[1] );
^



You need more parenthesis.

test= create unique index x1 on test ( ( ordinal[1] ) );

The reason is because ordinal[1] is an expression, similar to ordinal
[1] + 5 which would also need parenthesis.

Hope this helps,
Jeff Davis


Excellent!  I never considered that ordinal[1] was an expression.
Thanks.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Data corruption problem...

2006-12-01 Thread Nathan Wilhelmi

Hello - Ran into an interesting problem last week with data unexpectedly 
being deleted. Has anyone else experienced this and have any suggestions 
on how to prevent this in the future?


Here is the environment:

Server: 8.0.3 on Solaris 9
JDBC Driver: 8.0.311

This is the series of statements that cause the problem, but only under 
a certain condition listed below.


delete from X where id in (select bad_id from Z where name='qwerty');
delete from Y where id in (select bad_id from Z where name='qwerty');
delete from Z where name='qwerty');

The first 2 statements have an error, column bad_id does not exist in 
table Z. If I run this normally from PGAdmin the first 2 silently fail, 
and the last succeeds. In this case tables X and Y are untouched. An 
error for this would be nice but the data is OK, so far so good.


Now we have J2EE (Tomcat hosted) app that access this database via 
connection pool / JDBC driver. The problem scenario is as follows:


1) Start the J2EE app and do some work to access the database, although 
I don't think this matters.
2) Shut down the app server, and as result the connection pool starts to 
shut down.
3) Once the connection pool has started to shut down execute the 
statements listed above from PGAdmin.

4) PGAmin will hang for a second like it's waiting for locks.
5) The statement will return normally, reporting the rows affected from 
the last statement.

6) Tables X and Y are now empty. This is not good.

Now if I fix the first 2 statements the problem doesn't happen, I can 
execute it during shutdown and the data is OK. Obviously I would really 
prefer not to have times where executing a bad SQL statement deletes the 
whole table. Has anyone seen this before, any suggestions how to debug 
this or where to start looking?


Thanks!!!

-Nate










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

[GENERAL] Restore database from files (not dump files)?

2006-12-01 Thread wheel

Can a pgsql 8.1 database be restored from the raw file? For one database 
I have only the files found in  the base folder, example C:\PostgreSQL
\data\base\16404, there are many files called 1247, 1248, etc (no 
extension). I think these are the files that make up the database? For 
this db I don't have a dump file, just what is on disk. I do know the 
users and passwords etc. I've hunted around quite a bit in various 
places and most of the restore discussions center around files created 
with pg_dump.

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

   http://archives.postgresql.org/

RES: [GENERAL] Problem with function parameters


Richard Huxton wrote:
---Mensagem original-
--De: Richard Huxton [mailto:[EMAIL PROTECTED] 
--Enviada em: sexta-feira, 1 de dezembro de 2006 12:24
--Para: Alejandro Michelin Salomon ( Adinet )
--Cc: Pgsql-General
--Assunto: Re: [GENERAL] Problem with function parameters
--
--
--Alejandro Michelin Salomon ( Adinet ) wrote:
-- Hi :
--  
-- I have installed Pg 8.08
--  
-- I create this function :
-- CREATE OR REPLACE FUNCTION CALCULO_VALOR_LIQUIDO_HELPDESK( 
-- nTipoDesconto SMALLINT,
--
-- nTipoComissao SMALLINT,
--
-- When i test this function, i call the function with this 
--parameters :
--  
-- SELECT CALCULO_VALOR_LIQUIDO_HELPDESK( 0, 1, 10, 10, 1000 );
-- 
-- This is the error that happens:
--  
-- ERROR: function calculo_valor_liquido_helpdesk(integer, integer, 
-- integer, integer, integer) does not exist SQL state: 42883
-- Hint: No function matches the given name and argument 
--types. You may need to
-- add explicit type casts.
--  
-- I dont now what the two first parameters are see as 
--integer. When 0 
-- and 1 are true smallint values.
--
--0 and 1 end up as literal integers, since you've not told it 
--otherwise. You have two options:
--1. Cast your parameters when you call the function
--   select calculo_valor_...(0::smallint, 1::smallint, ...)
--2. Define your function to take integers
--
--I'd choose #2, you're not gaining anything by having 
--smallint parameters.
--
 
--   Richard Huxton
--   Archonet Ltd
--

I change the parameters to integer and is ok now.

Thanks for your help.

Alejandro Michelin Salomon


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

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Restore database from files (not dump files)?

2006-12-01 Thread Bruce Momjian

wheel wrote:
 Can a pgsql 8.1 database be restored from the raw file? For one database 
 I have only the files found in  the base folder, example C:\PostgreSQL
 \data\base\16404, there are many files called 1247, 1248, etc (no 
 extension). I think these are the files that make up the database? For 
 this db I don't have a dump file, just what is on disk. I do know the 
 users and passwords etc. I've hunted around quite a bit in various 
 places and most of the restore discussions center around files created 
 with pg_dump.

Uh, do you have the entier /data directory tree?  If so, just restore
the directory start it up a binary.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] transfer just the data directories from linux to windows?

2006-12-01 Thread Magnus Hagander

 Hi,
 We have some backups from our prod server and I was wondering 
 if there would be any problems with just copying the data 
 directory to a windows install (same version - 8.1.4) ... any 
 pointers?

If both servers are windows servers, and both are running the same
version of pg, then you can just copy the files over. Make sure the
postgresql service is *stopped* when you do the copying. (Clean out
everything in the data directory before you copy them over)
If either of those two are not the same, you can't.

//Magnus

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

[GENERAL] porducing documentation from postgres

2006-12-01 Thread Antonios Katsikadamos

Hi all. I have a big .sql file written in postgres and i was wondering if 
someone knows a tool to produce documentation from postgres.


Kind regards,

Antonios

 
-
Access over 1 million songs - Yahoo! Music Unlimited.

Re: [GENERAL] porducing documentation from postgres

2006-12-01 Thread John DeSoi



On Dec 1, 2006, at 12:36 PM, Antonios Katsikadamos wrote:

Hi all. I have a big .sql file written in postgres and i was  
wondering if someone knows a tool to produce documentation from  
postgres.



http://www.rbt.ca/autodoc/



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


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

Re: [GENERAL] porducing documentation from postgres

2006-12-01 Thread Merlin Moncure


On 12/1/06, John DeSoi [EMAIL PROTECTED] wrote:


On Dec 1, 2006, at 12:36 PM, Antonios Katsikadamos wrote:

 Hi all. I have a big .sql file written in postgres and i was
 wondering if someone knows a tool to produce documentation from
 postgres.


http://www.rbt.ca/autodoc/



although it's a commercial product, EMS PostgreSQL manager does the
same thing and procudes (IMO) much better html documentation.

merlin

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Separation of clients' data within a database

2006-12-01 Thread John McCawley

Oh, I see, so there's one master schema, and one customer schema, and 
the customer schema views are automatically filtered based on 
login...Makes sense...I will definitely try to implement this, thanks!


Niklas Johansson wrote:



On 1 dec 2006, at 15.19, John McCawley wrote:

That's the first idea I've seen that looks like it might actually  
work... (Not that the other ideas were bad, but I just couldn't see  
how I could fit the solutions into my current app)


So what would my user setup look like?  Would it look something  like 
this:


createuser joe
grant select on schema company_a to joe
(whatever other permissions)
alter user joe set search_path='common','company_a';

createuser bob
grant select on schema company_b to bob
(whatever other permissions)
alter user bob set search_path='common','company_b';



No, you wouldn't need separate schemas for each user, and the users  
should *not* be allowed access to the master schema. The views in the  
customer schema would, as I said, use a function (e.g. get_client_ids 
()) that uses CURRENT_USER (which will evaluate to either joe or bob,  
according to your example above) to lookup the actual client_ids.  
This means that you can grant every user the same rights on the  
customer schema views, and the rights management is done by the  
function (which is better than hardcoding values into the views; if  
the requirements change you just update the function), together with  
an additional table in the master schema. This table could look  
something like this:


role | client_id
-+--
joe  | 100
joe  | 101
bob  | 102

which would mean that joe is a supervisor that can see both client  
100 and client 101, while bob can see only client 102. You would  
probably need some other tables to keep track of which client_id  
should be used or allowed for data insertion if the user has more  
than one client_id, but you get the idea.


How portable is all of this?  Could a comparable structure be  
implemented in MS SQL or Oracle?



As far as I know, yes. (Quite some time since I last had anything to  
do with either of those. Not that I lament the fact... :-)




Sincerely,

Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly



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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] CertFirst Legit?

2006-12-01 Thread Chander Ganesan


Jim Nasby wrote:
If you're concerned about them, you might want to just use someone 
else... I can think of at least 2 other companies that offer training 
classes.


On Nov 27, 2006, at 2:40 PM, [EMAIL PROTECTED] wrote:


I've been tasked with administering one of our PostgreSQL databases and
know little or nothing about the product (though I do have DB
experience).  I've been looking for training and came across an
administration class at 'http://www.postgresql.org/about/event.425' on
the main web site.  When I called the certfirst people offering the
classes I got the impression that they weren't on the up and up.  When I
asked if the class in Newport Beach was sure to run they tried to talk
me into taking a class in their Illinois location, and when I asked
about a class in January in Vegas they told me it was already full but I
could take the December class in Illinois -- but they wouldn't tell me
where their classes in Vegas are held.

Can anyone provide me with a reference for these guys?  Also, they claim
to offer a certification but it is done online using some web site I've
never heard of.

Hello Sir,

We do offer PostgreSQL training courses - from basic to advanced 
courses, including Performance  Tuning courses.  Our courses are 
typically held at our Morrisville, NC location - but from time to time 
we do offer them in the San Jose, CA area.


If you're looking to stay on the west coast because of cost, please note 
that our courses often include an option that includes hotel and 
airfare.  They are priced to be competitive with any local training 
providers that you may have (and are typically less than what you would 
pay for a class around the corner from you!)


For example, our for example, our PostgreSQL Administration course - 5 
days is priced at $2,195 and includes round-trip airfare as well as 5 
nights hotel accommodation and free shuttle services here in 
Morrisville, NC.  I'd be more than happy to provide you with some 
references that you can contact if you have questions about the quality 
of content of the courses that we offer.


Our Morrisville, NC PostgreSQL courses are also guaranteed to run - 
regardless of the number of students that enroll.  So you can base your 
project schedules/plans on the fact that you will get the training when 
you schedule it.


Thank You

--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com



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

   http://www.postgresql.org/docs/faq



--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

  http://www.postgresql.org/docs/faq




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

  http://archives.postgresql.org/

Re: [GENERAL] initdb problem on Windows XP Home

2006-12-01 Thread Shane Ambler


[EMAIL PROTECTED] wrote:

(I've discovered this morning that I can boot in safe mode to get the Security tab on folder properties in XP Home. 
I'll check that this evening to see if it looks correct.)


I haven't used XP home much so I'm not certain it is the same - but the 
security tab is hidden in XP Pro when simple filesharing is on.


Open Folder Options and select the view tab and the last item in the 
advanced settings list is Use Simple File Sharing - having this turned 
on hides the security tab and simplifies the sharing tab.



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Separation of clients' data within a database

2006-12-01 Thread Berend Tober


John McCawley wrote:
Oh, I see, so there's one master schema, and one customer schema, and 
the customer schema views are automatically filtered based on 
login...Makes sense...I will definitely try to implement this, thanks!


I've on-and-off toyed with the idea of accomplishing a similar objective 
by using a temporary table (which are session specific, so different 
logins would see their own temp table). Haven't worked through all the 
details and so am not sure if it makes much sense this way verses using 
a function to identify the current user, but here is a short script to 
illustrate the idea:



CREATE SCHEMA universe;
SET search_path=universe, pg_catalog;

CREATE TABLE customer
(
 customer varchar(12) NOT NULL,
 CONSTRAINT customer_pkey PRIMARY KEY (customer)
);

CREATE TABLE invoice
(
 customer varchar(12) NOT NULL,
 invoice varchar(12) NOT NULL,
 CONSTRAINT invoice_pkey PRIMARY KEY (customer, invoice),
 CONSTRAINT $1 FOREIGN KEY (customer) REFERENCES customer (customer)
);

INSERT INTO customer VALUES ('Alice');
INSERT INTO customer VALUES ('Bob');

INSERT INTO invoice VALUES ('Alice', 'inv a1');
INSERT INTO invoice VALUES ('Alice', 'inv a2');
INSERT INTO invoice VALUES ('Alice', 'inv a3');
INSERT INTO invoice VALUES ('Alice', 'inv a4');

INSERT INTO invoice VALUES ('Bob', 'inv b1');
INSERT INTO invoice VALUES ('Bob', 'inv b2');
INSERT INTO invoice VALUES ('Bob', 'inv b3');

SELECT * FROM customer NATURAL JOIN universe.invoice;
/*
customer | invoice
--+-
Alice| inv a1
Alice| inv a2
Alice| inv a3
Alice| inv a4
Bob  | inv b1
Bob  | inv b2
Bob  | inv b3
(7 rows)
*/

CREATE SCHEMA customer;
SET search_path=customer, pg_catalog;

CREATE TEMPORARY TABLE customer AS SELECT * FROM  universe.customer 
WHERE customer = 'Alice';

SELECT * FROM customer NATURAL JOIN universe.invoice;
/*
customer | invoice
--+-
Alice| inv a1
Alice| inv a2
Alice| inv a3
Alice| inv a4
(4 rows)
*/


DROP TABLE customer;
CREATE TEMPORARY TABLE customer AS SELECT * FROM  universe.customer 
WHERE customer = 'Bob';

SELECT * FROM customer NATURAL JOIN universe.invoice;
/*
customer | invoice
--+-
Bob  | inv b1
Bob  | inv b2
Bob  | inv b3
(3 rows)
*/


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

[GENERAL] DBI module for postgres 1.4.3

2006-12-01 Thread Jasbinder Singh Bali


Hi
I'm using postgresql version 1.4.3.
Trying to connect to it throught perl code.
Just wondering if DBI would be the best tool to use to accomplish this task.
Which version of DBI should I be using.
I mean if any one of you could give me exact pointers to it, would be highly
appreciated.
Thanks,
Jas

Re: [GENERAL] DBI module for postgres 1.4.3

2006-12-01 Thread Martijn van Oosterhout

On Fri, Dec 01, 2006 at 02:49:59PM -0500, Jasbinder Singh Bali wrote:
 Hi
 I'm using postgresql version 1.4.3.

No such version exists. What exactly do you mean?

 Trying to connect to it throught perl code.
 Just wondering if DBI would be the best tool to use to accomplish this task.
 Which version of DBI should I be using.

Whichever version is installed by your system should be fine. Clients
are compatable across many versions.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] DBI module for postgres 1.4.3

2006-12-01 Thread Jasbinder Singh Bali


I've downloaded dbd:pg 1.49.
Should work?

On 12/1/06, Martijn van Oosterhout kleptog@svana.org wrote:


On Fri, Dec 01, 2006 at 02:49:59PM -0500, Jasbinder Singh Bali wrote:
 Hi
 I'm using postgresql version 1.4.3.

No such version exists. What exactly do you mean?

 Trying to connect to it throught perl code.
 Just wondering if DBI would be the best tool to use to accomplish this
task.
 Which version of DBI should I be using.

Whichever version is installed by your system should be fine. Clients
are compatable across many versions.

Have a nice day,
--
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to
litigate.


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFcIr3IB7bNG8LQkwRAnVTAJ4soDezZtEbxosNd+LrmnV2Lm08OwCffIh/
AhHObMuuj5dCXHllcWSCYaI=
=X3Gq
-END PGP SIGNATURE-

Re: [GENERAL] DBI module for postgres 1.4.3

2006-12-01 Thread Joshua D. Drake

On Fri, 2006-12-01 at 21:05 +0100, Martijn van Oosterhout wrote:
 On Fri, Dec 01, 2006 at 02:49:59PM -0500, Jasbinder Singh Bali wrote:
  Hi
  I'm using postgresql version 1.4.3.
 
 No such version exists. What exactly do you mean?

I am guessing he means DBD::Pg 1.4.3 to PostgreSQL version something


Joshua D. Drake


 
  Trying to connect to it throught perl code.
  Just wondering if DBI would be the best tool to use to accomplish this task.
  Which version of DBI should I be using.
 
 Whichever version is installed by your system should be fine. Clients
 are compatable across many versions.
 
 Have a nice day,
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

   http://archives.postgresql.org/

Re: [GENERAL] DBI module for postgres 1.4.3

2006-12-01 Thread Jasbinder Singh Bali


Oops my bad.
That 1.4.3 was pgadmin versioin actually.
Sorry about that

On 12/1/06, Joshua D. Drake [EMAIL PROTECTED] wrote:


On Fri, 2006-12-01 at 21:05 +0100, Martijn van Oosterhout wrote:
 On Fri, Dec 01, 2006 at 02:49:59PM -0500, Jasbinder Singh Bali wrote:
  Hi
  I'm using postgresql version 1.4.3.

 No such version exists. What exactly do you mean?

I am guessing he means DBD::Pg 1.4.3 to PostgreSQL version something


Joshua D. Drake



  Trying to connect to it throught perl code.
  Just wondering if DBI would be the best tool to use to accomplish this
task.
  Which version of DBI should I be using.

 Whichever version is installed by your system should be fine. Clients
 are compatable across many versions.

 Have a nice day,
--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Re: [GENERAL] CertFirst Legit?

2006-12-01 Thread adam

I'm looking at taking this course. The Illinois location is about 30 min from my home.

No one else has taken this course?

 Original Message Subject: Re: [GENERAL] CertFirst Legit?From: Jim Nasby [EMAIL PROTECTED]Date: Thu, November 30, 2006 2:34 pmTo: [EMAIL PROTECTED]Cc: pgsql-general@postgresql.orgIf you're concerned about them, you might want to just use someone else... I can think of at least 2 other companies that offer training classes.On Nov 27, 2006, at 2:40 PM, [EMAIL PROTECTED] wrote: I've been tasked with administering one of our PostgreSQL databases  and know little or nothing about the product (though I do have DB experience). I've been looking for training and came across an administration class at 'http://www.postgresql.org/about/event.425' on the main web site. When I called the certfirst people offering the classes I got the impression that the
 y weren't on the up and up.   When I asked if the class in Newport Beach was sure to run they tried to talk me into taking a class in their Illinois location, and when I asked about a class in January in Vegas they told me it was already full  but I could take the December class in Illinois -- but they wouldn't tell me where their classes in Vegas are held. Can anyone provide me with a reference for these guys? Also, they  claim to offer a certification but it is done online using some web site  I've never heard of. ---(end of  broadcast)--- TIP 3: Have you checked our extensive FAQ?http://www.postgresql.org/docs/faq--Jim Nasby  
 [EMAIL PROTECTED]EnterpriseDB   http://enterprisedb.com   512.569.9461 (cell)---(end of broadcast)---TIP 3: Have you checked our extensive FAQ?   http://www.postgresql.org/docs/faq

[GENERAL] best way to setup raid array in this situation

2006-12-01 Thread Derrick Stensrud

We have a server with 6 32GB SCSI drives and we are thinking about 
setting up 4 drives in RAID 10 for the database itself and using the 
other 2 drives in RAID 1 for the WAL and OS.  Is this a good idea or can 
anyone think of a better idea other than this?  Thanks.


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

[GENERAL] Unsuccessful SIGINT


I have a connection that I am unable to kill with a sigint.

ps auxww for the process in question:
postgres  3578  0.3  3.6 6526396 1213344 ? SDec01   0:32  
postgres: postgres ssprod 192.168.0.52(49333) SELECT


and gdb shows:
(gdb) bt
#0  0x2ba62c18f085 in send () from /lib64/libc.so.6
#1  0x00504765 in internal_flush ()
#2  0x00504896 in internal_putbytes ()
#3  0x005048fc in pq_putmessage ()
#4  0x00505ea4 in pq_endmessage ()
#5  0x0043e37a in printtup ()
#6  0x004e9349 in ExecutorRun ()
#7  0x00567931 in PortalRunSelect ()
#8  0x005685f0 in PortalRun ()
#9  0x00565ea8 in PostgresMain ()
#10 0x00540624 in ServerLoop ()
#11 0x0054131a in PostmasterMain ()
#12 0x0050676e in main ()

lsof on the client machine (192.168.0.52) shows no connections on  
port 49333, so it doesn't appear to be a simple matter of killing the  
client connection. If I have to, I can reboot the client machine, but  
this seems like overkill and I'm not certain this will fix the  
problem. Anything else I can try on the server or the client short of  
restarting the database or rebooting the client?


Brian Wipf
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Unsuccessful SIGINT

Sorry, I forgot to mention this is on PostgreSQL 8.1.5. The server is  
SUSE Linux 10.1, the client is OS X Server 10.4.8.


On 1-Dec-06, at 5:42 PM, Brian Wipf wrote:


I have a connection that I am unable to kill with a sigint.

ps auxww for the process in question:
postgres  3578  0.3  3.6 6526396 1213344 ? SDec01   0:32  
postgres: postgres ssprod 192.168.0.52(49333) SELECT


and gdb shows:
(gdb) bt
#0  0x2ba62c18f085 in send () from /lib64/libc.so.6
#1  0x00504765 in internal_flush ()
#2  0x00504896 in internal_putbytes ()
#3  0x005048fc in pq_putmessage ()
#4  0x00505ea4 in pq_endmessage ()
#5  0x0043e37a in printtup ()
#6  0x004e9349 in ExecutorRun ()
#7  0x00567931 in PortalRunSelect ()
#8  0x005685f0 in PortalRun ()
#9  0x00565ea8 in PostgresMain ()
#10 0x00540624 in ServerLoop ()
#11 0x0054131a in PostmasterMain ()
#12 0x0050676e in main ()

lsof on the client machine (192.168.0.52) shows no connections on  
port 49333, so it doesn't appear to be a simple matter of killing  
the client connection. If I have to, I can reboot the client  
machine, but this seems like overkill and I'm not certain this will  
fix the problem. Anything else I can try on the server or the  
client short of restarting the database or rebooting the client?


Brian Wipf
[EMAIL PROTECTED]




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

Re: [GENERAL] Unsuccessful SIGINT - More Info

Based on the backend_start time in pg_stat_activity, I was able to  
find the problem query in our logs. The query is a simple one, but  
returns a lot of results for a report. This was the error in the logs:


org.postgresql.util.PSQLException: Ran out of memory retrieving query  
results.
at org.postgresql.core.v3.QueryExecutorImpl.processResults 
(QueryExecutorImpl.java:1291)
at org.postgresql.core.v3.QueryExecutorImpl.execute 
(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute 
(AbstractJdbc2Statement.java:452)
at  
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags 
(AbstractJdbc2Statement.java:340)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery 
(AbstractJdbc2Statement.java:239)

...
java.lang.OutOfMemoryError

The instance of the application is no longer running where this error  
occurred, but the server still shows the hung non-sigint-able  
connection.


On 1-Dec-06, at 5:54 PM, Brian Wipf wrote:

Sorry, I forgot to mention this is on PostgreSQL 8.1.5. The server  
is SUSE Linux 10.1, the client is OS X Server 10.4.8.


On 1-Dec-06, at 5:42 PM, Brian Wipf wrote:


I have a connection that I am unable to kill with a sigint.

ps auxww for the process in question:
postgres  3578  0.3  3.6 6526396 1213344 ? SDec01   0:32  
postgres: postgres ssprod 192.168.0.52(49333) SELECT


and gdb shows:
(gdb) bt
#0  0x2ba62c18f085 in send () from /lib64/libc.so.6
#1  0x00504765 in internal_flush ()
#2  0x00504896 in internal_putbytes ()
#3  0x005048fc in pq_putmessage ()
#4  0x00505ea4 in pq_endmessage ()
#5  0x0043e37a in printtup ()
#6  0x004e9349 in ExecutorRun ()
#7  0x00567931 in PortalRunSelect ()
#8  0x005685f0 in PortalRun ()
#9  0x00565ea8 in PostgresMain ()
#10 0x00540624 in ServerLoop ()
#11 0x0054131a in PostmasterMain ()
#12 0x0050676e in main ()

lsof on the client machine (192.168.0.52) shows no connections on  
port 49333, so it doesn't appear to be a simple matter of killing  
the client connection. If I have to, I can reboot the client  
machine, but this seems like overkill and I'm not certain this  
will fix the problem. Anything else I can try on the server or the  
client short of restarting the database or rebooting the client?


Brian Wipf
[EMAIL PROTECTED]




---(end of  
broadcast)---

TIP 6: explain analyze is your friend





---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] initdb problem on Windows XP Home

Hi,

I gave restricted permissions to the postgres user on the C: drive.  After 
doing this, I could run initdb without issue.
 Something doesn't sit right with me with giving access to C: drive, but it 
works.  If anyone has any insights on what
this problem is/was and how I can get around it without granting access to C:, 
I would appreciate it.

Thanks for all your help.

Alex.


 [EMAIL PROTECTED] wrote:
  Hi Richard,
 
  Thanks for getting back to me.
 
  Yes, I have a postgres limited account that will own the files.  (This 
  was the one created by the installer.)  In
  order to ensure the this user has access/full control to C:\Program 
  Files\Postgresql\8.1, I used the cacls
  command to edit the access control list and add postgres:f.
  i.e. I ran
  cacls C:\Program Files\PostgreSQL\8.1 /t /e /g postgres:f
 
  (I've discovered this morning that I can boot in safe mode to get the 
  Security tab on folder properties in XP Home.
  I'll check that this evening to see if it looks correct.)
 
  However, to continue with your suggestion, after running the cacls statment 
  above, I did log in as postgres and then
  ran:
  initd -D C:\Program Files\Postgresql\8.1\data ...
  Yes, I did include the double quotes (due to the space in Program Files) 
  but it was still the same result.  could
  not create directory C:/Program Files: File exists.

 Hmm. That doesn't make any sense to me. I'm guessing it's a different
 error code behind the scenes. I suppose you can issue
cd C:/Program Files
dir
 successfully as user postgres?

 If so, try forward slashes to match the output of the error message:
 C:/Program Files/Postgresql/...

 If that's still not working and none of the Windows guys pop up, I'll
 try and reproduce it on my XP-Pro laptop.
 --
Richard Huxton
Archonet Ltd

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

Re: [GENERAL] Unsuccessful SIGINT - More Info

I finally reboot the client server. It took a couple of minutes after  
that, but the hung connection did go away on the server.


I found a similar cause to my problem in the archives:
http://archives.postgresql.org/pgsql-jdbc/2005-05/msg00044.php

In order for the PostgreSQL JDBC adaptor to not fetch the entire  
result set it is necessary to call Statement.setFetchSize().
From the archive: Currently it only takes effect with autocommit  
off and TYPE_FORWARD_ONLY resultsets


Now I know the cause at least. If anyone has an idea on how to kill a  
similar hung connection without rebooting the server, I would  
appreciate any suggestions.


Thanks,

Brian Wipf
[EMAIL PROTECTED]

On 1-Dec-06, at 6:30 PM, Brian Wipf wrote:

Based on the backend_start time in pg_stat_activity, I was able to  
find the problem query in our logs. The query is a simple one, but  
returns a lot of results for a report. This was the error in the logs:


org.postgresql.util.PSQLException: Ran out of memory retrieving  
query results.
at org.postgresql.core.v3.QueryExecutorImpl.processResults 
(QueryExecutorImpl.java:1291)
at org.postgresql.core.v3.QueryExecutorImpl.execute 
(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute 
(AbstractJdbc2Statement.java:452)
at  
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags 
(AbstractJdbc2Statement.java:340)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery 
(AbstractJdbc2Statement.java:239)

...
java.lang.OutOfMemoryError

The instance of the application is no longer running where this  
error occurred, but the server still shows the hung non-sigint-able  
connection.


On 1-Dec-06, at 5:54 PM, Brian Wipf wrote:

Sorry, I forgot to mention this is on PostgreSQL 8.1.5. The server  
is SUSE Linux 10.1, the client is OS X Server 10.4.8.


On 1-Dec-06, at 5:42 PM, Brian Wipf wrote:


I have a connection that I am unable to kill with a sigint.

ps auxww for the process in question:
postgres  3578  0.3  3.6 6526396 1213344 ? SDec01   0:32  
postgres: postgres ssprod 192.168.0.52(49333) SELECT


and gdb shows:
(gdb) bt
#0  0x2ba62c18f085 in send () from /lib64/libc.so.6
#1  0x00504765 in internal_flush ()
#2  0x00504896 in internal_putbytes ()
#3  0x005048fc in pq_putmessage ()
#4  0x00505ea4 in pq_endmessage ()
#5  0x0043e37a in printtup ()
#6  0x004e9349 in ExecutorRun ()
#7  0x00567931 in PortalRunSelect ()
#8  0x005685f0 in PortalRun ()
#9  0x00565ea8 in PostgresMain ()
#10 0x00540624 in ServerLoop ()
#11 0x0054131a in PostmasterMain ()
#12 0x0050676e in main ()

lsof on the client machine (192.168.0.52) shows no connections on  
port 49333, so it doesn't appear to be a simple matter of killing  
the client connection. If I have to, I can reboot the client  
machine, but this seems like overkill and I'm not certain this  
will fix the problem. Anything else I can try on the server or  
the client short of restarting the database or rebooting the client?


Brian Wipf
[EMAIL PROTECTED]




---(end of  
broadcast)---

TIP 6: explain analyze is your friend





---(end of  
broadcast)---

TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that  
your

  message can get through to the mailing list cleanly





---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] initdb problem on Windows XP Home

Hi Richard,

Some more information tonight.  I logged in under safe mode and checked the 
security settings for C:\Program
Files\PostgreSQL and the user postgres had full control.  However, I could not 
issue a cd C:\Program
Files\PostgreSQL.  I looked at the security for Program Files, and postgres 
was not part included.  I added the user
postgres with Read only permissions.  Still couldn't change to the PostgreSQL 
folder. Changed security permissions to
Read and Execute to include list directory contents. Still couldn't change 
folders.  I think now that initdb is trying
to create the folders because as thev postgres user it can't see the folder so 
assumes it not to be there.  Then tries
to create it, which of course it already exists, so it fails with file exists.  
The million dollar question is why can't
the postgres user access the folder when logged in and security settings show 
it should have access.

Do I need to give permissions to postgres for the C: drive?  Somehow that 
doesn't seem correct.

Any ideas?

Thanks for your help.
Alex.


 [EMAIL PROTECTED] wrote:
  Hi Richard,
 
  Thanks for getting back to me.
 
  Yes, I have a postgres limited account that will own the files.  (This 
  was the one created by the installer.)  In
  order to ensure the this user has access/full control to C:\Program 
  Files\Postgresql\8.1, I used the cacls
  command to edit the access control list and add postgres:f.
  i.e. I ran
  cacls C:\Program Files\PostgreSQL\8.1 /t /e /g postgres:f
 
  (I've discovered this morning that I can boot in safe mode to get the 
  Security tab on folder properties in XP Home.
  I'll check that this evening to see if it looks correct.)
 
  However, to continue with your suggestion, after running the cacls statment 
  above, I did log in as postgres and then
  ran:
  initd -D C:\Program Files\Postgresql\8.1\data ...
  Yes, I did include the double quotes (due to the space in Program Files) 
  but it was still the same result.  could
  not create directory C:/Program Files: File exists.

 Hmm. That doesn't make any sense to me. I'm guessing it's a different
 error code behind the scenes. I suppose you can issue
cd C:/Program Files
dir
 successfully as user postgres?

 If so, try forward slashes to match the output of the error message:
 C:/Program Files/Postgresql/...

 If that's still not working and none of the Windows guys pop up, I'll
 try and reproduce it on my XP-Pro laptop.
 --
Richard Huxton
Archonet Ltd

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

Re: [GENERAL] initdb problem on Windows XP Home