Timing Race

Version 8.4 on Ubuntu Linux (is this still supported?)

I'm getting an error upon inserting a copy version of a database dump. It's very simple, 4 tables,

TableA 2 records (basically a lookup table)
TableB 15 records
TableC 46 records ( many side of 'one to many')
TableD 55 records.( one side of 'one to many')

Each table has a sequence, one has an index.

TableC has a Foreign key to TableD (child/parent respectively)
TableD has a Foreign Key to TableA (child/parent respectively)

The problem I get is that inserting the copy into a blank database gives me errors when it gets to TableD because 2nd
recordin TableA seems not to be available to reference for the foreign key in TableD. It doesn't even matter if I
rearrangethe database dump contents to insert all the parents first, it always fails there. 

However, if I split the file up, and do one table at a time, it works just fine.  Is this a known issue, feature, or I
amdoing something wrong? I thought that doing a database recovery using a copy version of a dump would be a no brainer,
itwould happen all in one transaction? 


Dennis Gearon

Signature Warning
----------------
EARTH has a Right To Life,
  otherwise we all die.

Read 'Hot, Flat, and Crowded'
Laugh at http://www.yert.com/film.php


--- On Sat, 2/20/10, pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org> wrote:

> From: pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org>
> Subject: [pgsql-general] Daily digest v1.9718 (19 messages)
> To: pgsql-general@postgresql.org
> Date: Saturday, February 20, 2010, 1:42 PM
> Message Digest
> Volume 1 : Issue 9718 : "text" Format
>
> Messages in this Issue:
>   Procedures
>   Re: Procedures
>   Re: Procedures
>   Re: Procedures
>   Re: Procedures
>   Re: Procedures
>   Re: Procedures
>   Re: Procedures
>   Re: Procedures
>   Re: Procedures
>   Re: DDL trigger kind functionality in PostGreSQL
>   Re: Procedures
>   Transactions, How to?
>   Re: Transactions, How to?
>   Re: Transactions, How to?
>   Re: PostgreSQL fails to start
>   Re: PostgreSQL fails to start
>   Re: PostgreSQL fails to start
>   Re: PostgreSQL fails to start
>
> ----------------------------------------------------------------------
>
> Date: Sat, 20 Feb 2010 14:02:05 +0530
> From: Nilesh Govindarajan <lists@itech7.com>
> To: pgsql-general@postgresql.org
> Subject: Procedures
> Message-ID: <4B7F9E05.5030907@itech7.com>
>
> How do I create a procedure using plpgsql cursors to print
> the output of
> the query in the cursor (using for loop) ?
>
> In all docs I found, it seems to be a must to return data
> to the call
> which is not what I want.
>
> --
> Nilesh Govindarajan
> Site & Server Adminstrator
> www.itech7.com
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 01:02:37 -0800
> From: John R Pierce <pierce@hogranch.com>
> To: Nilesh Govindarajan <lists@itech7.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <4B7FA52D.9010309@hogranch.com>
>
> Nilesh Govindarajan wrote:
> > How do I create a procedure using plpgsql cursors to
> print the output
> > of the query in the cursor (using for loop) ?
> >
> > In all docs I found, it seems to be a must to return
> data to the call
> > which is not what I want.
> >
>
> what is it going to print it on?   the
> postgres server processes have no
> console or stdout device.
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 18:38:14 +0530
> From: Nilesh Govindarajan <lists@itech7.com>
> To: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <4B7FDEBE.1040202@itech7.com>
>
> On 02/20/2010 02:32 PM, John R Pierce wrote:
> > Nilesh Govindarajan wrote:
> >> How do I create a procedure using plpgsql cursors
> to print the output
> >> of the query in the cursor (using for loop) ?
> >>
> >> In all docs I found, it seems to be a must to
> return data to the call
> >> which is not what I want.
> >>
> >
> > what is it going to print it on? the postgres server
> processes have no
> > console or stdout device.
>
> Okay, so how do I print it to stdout ?
>
> --
> Nilesh Govindarajan
> Site & Server Adminstrator
> www.itech7.com
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 14:21:20 +0100
> From: Thomas Kellerer <spam_eater@gmx.net>
> To: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <hlonka$9iv$1@ger.gmane.org>
>
> Nilesh Govindarajan wrote on 20.02.2010 14:08:
> > On 02/20/2010 02:32 PM, John R Pierce wrote:
> >> Nilesh Govindarajan wrote:
> >>> How do I create a procedure using plpgsql
> cursors to print the output
> >>> of the query in the cursor (using for loop) ?
> >>>
> >>> In all docs I found, it seems to be a must to
> return data to the call
> >>> which is not what I want.
> >>>
> >>
> >> what is it going to print it on? the postgres
> server processes have no
> >> console or stdout device.
> >
> > Okay, so how do I print it to stdout ?
> >
> Even if you could, that would be stdout of the *server*,
> not the one of the client calling the procedure!
>
> Regards
> Thomas
>
>
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 18:53:51 +0530
> From: Nilesh Govindarajan <lists@itech7.com>
> To: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <4B7FE267.8070108@itech7.com>
>
> On 02/20/2010 06:51 PM, Thomas Kellerer wrote:
> > Nilesh Govindarajan wrote on 20.02.2010 14:08:
> >> On 02/20/2010 02:32 PM, John R Pierce wrote:
> >>> Nilesh Govindarajan wrote:
> >>>> How do I create a procedure using plpgsql
> cursors to print the output
> >>>> of the query in the cursor (using for
> loop) ?
> >>>>
> >>>> In all docs I found, it seems to be a must
> to return data to the call
> >>>> which is not what I want.
> >>>>
> >>>
> >>> what is it going to print it on? the postgres
> server processes have no
> >>> console or stdout device.
> >>
> >> Okay, so how do I print it to stdout ?
> >>
> > Even if you could, that would be stdout of the
> *server*, not the one of
> > the client calling the procedure!
> >
> > Regards
> > Thomas
> >
> >
> >
>
> Okay how to stdout it to the client ?
>
> The reason is, I have a query which I need to run
> repeatedly to see if
> some data has been inserted by the web application. So its
> irritating to
> type the query again and again.
>
> --
> Nilesh Govindarajan
> Site & Server Adminstrator
> www.itech7.com
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 13:24:00 +0000
> From: Raymond O'Donnell <rod@iol.ie>
> To: Nilesh Govindarajan <lists@itech7.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <4B7FE270.8090309@iol.ie>
>
> On 20/02/2010 13:08, Nilesh Govindarajan wrote:
> > On 02/20/2010 02:32 PM, John R Pierce wrote:
> >> Nilesh Govindarajan wrote:
> >>> How do I create a procedure using plpgsql
> cursors to print the output
> >>> of the query in the cursor (using for loop) ?
> >>>
> >>> In all docs I found, it seems to be a must to
> return data to the call
> >>> which is not what I want.
> >>>
> >>
> >> what is it going to print it on? the postgres
> server processes have no
> >> console or stdout device.
> >
> > Okay, so how do I print it to stdout ?
>
> Well, it still has to get back to the client from the
> server - hence you
> need to return the data.... stout here will be stdout of
> the server, not
> the client.
>
> Maybe if you say more clearly what it is you *do* want,
> rather than what
> you don't, people may be able to help you. :-)
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 18:58:28 +0530
> From: Nilesh Govindarajan <lists@itech7.com>
> To: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <4B7FE37C.7020709@itech7.com>
>
> On 02/20/2010 06:54 PM, Raymond O'Donnell wrote:
> > On 20/02/2010 13:08, Nilesh Govindarajan wrote:
> >> On 02/20/2010 02:32 PM, John R Pierce wrote:
> >>> Nilesh Govindarajan wrote:
> >>>> How do I create a procedure using plpgsql
> cursors to print the output
> >>>> of the query in the cursor (using for
> loop) ?
> >>>>
> >>>> In all docs I found, it seems to be a must
> to return data to the call
> >>>> which is not what I want.
> >>>>
> >>>
> >>> what is it going to print it on? the postgres
> server processes have no
> >>> console or stdout device.
> >>
> >> Okay, so how do I print it to stdout ?
> >
> > Well, it still has to get back to the client from the
> server - hence you
> > need to return the data.... stout here will be stdout
> of the server, not
> > the client.
> >
> > Maybe if you say more clearly what it is you *do*
> want, rather than what
> > you don't, people may be able to help you. :-)
> >
> > Ray.
> >
>
> Okay here's my query -
>
> select c.cid, c.subject, n.title from comments c, node n
> where c.nid =
> n.nid and c.status != 0;
>
> This is the query to check list of comments requiring admin
> approval and
> also the article titles on which this is posted.
>
> I want to see this result on the screen at psql prompt.
> Since it may
> return multiple rows, a cursor has to be employed here.
>
> Now if I employ a cursor here in the function/procedure,
> how to see the
> results ?
>
> --
> Nilesh Govindarajan
> Site & Server Adminstrator
> www.itech7.com
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 13:42:27 +0000
> From: Raymond O'Donnell <rod@iol.ie>
> To: Nilesh Govindarajan <lists@itech7.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <4B7FE6C3.4030702@iol.ie>
>
> On 20/02/2010 13:28, Nilesh Govindarajan wrote:
> > Okay here's my query -
> >
> > select c.cid, c.subject, n.title from comments c, node
> n where c.nid =
> > n.nid and c.status != 0;
> >
> > This is the query to check list of comments requiring
> admin approval and
> > also the article titles on which this is posted.
> >
> > I want to see this result on the screen at psql
> prompt. Since it may
> > return multiple rows, a cursor has to be employed
> here.
> >
> > Now if I employ a cursor here in the
> function/procedure, how to see the
> > results ?
>
> Have you declared your function to return SETOF the row
> type returned?
> if so, you don't have to use a cursor, and the function
> will simply
> return all the rows.
>
> For example, using SQL (not tested):
>
>   create or replace function comments_for_approval()
>   returns setof record
>   as
>   $$
>     select c.cid, c.subject, n.title
>     from comments c, node n
>     where c.nid = n.nid
>     and c.status != 0;
>   $$
>   language sql;
>
> ....or something like that. If you use pl/pgsql, then
> you'll need to use
> a different idiom:
>
>   create or replace function comments_for_approval()
>   returns setof record
>   as
>   $$
>   declare
>     m_rec record;
>   begin
>     for m_rec in
>       select c.cid, c.subject, n.title
>       from comments c, node n
>       where c.nid = n.nid
>       and c.status != 0
>     loop
>       return next m_rec;
>     end loop;
>     return;
>   end;
>   $$
>   language plpgsql;
>
> Either way, simply execute the query in psql:
>
>   select * from comments_for_approval();
>
> HTH,
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 14:43:55 +0100
> From: Thomas Kellerer <spam_eater@gmx.net>
> To: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <hlooul$d1v$1@ger.gmane.org>
>
> Nilesh Govindarajan wrote on 20.02.2010 14:28:
> > Okay here's my query -
> >
> > select c.cid, c.subject, n.title from comments c, node
> n where c.nid =
> > n.nid and c.status != 0;
> >
> > This is the query to check list of comments requiring
> admin approval and
> > also the article titles on which this is posted.
> >
> > I want to see this result on the screen at psql
> prompt. Since it may
> > return multiple rows, a cursor has to be employed
> here.
>
> Hmm, I don't understand your question.
>
> When you run the query, psql will display the result...
>
> Thomas
>
>
>
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 19:24:59 +0530
> From: Nilesh Govindarajan <lists@itech7.com>
> To: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <4B7FE9B3.5020500@itech7.com>
>
> On 02/20/2010 07:12 PM, Raymond O'Donnell wrote:
> > On 20/02/2010 13:28, Nilesh Govindarajan wrote:
> >> Okay here's my query -
> >>
> >> select c.cid, c.subject, n.title from comments c,
> node n where c.nid =
> >> n.nid and c.status != 0;
> >>
> >> This is the query to check list of comments
> requiring admin approval and
> >> also the article titles on which this is posted.
> >>
> >> I want to see this result on the screen at psql
> prompt. Since it may
> >> return multiple rows, a cursor has to be employed
> here.
> >>
> >> Now if I employ a cursor here in the
> function/procedure, how to see the
> >> results ?
> >
> > Have you declared your function to return SETOF the
> row type returned?
> > if so, you don't have to use a cursor, and the
> function will simply
> > return all the rows.
> >
> > For example, using SQL (not tested):
> >
> >    create or replace function
> comments_for_approval()
> >    returns setof record
> >    as
> >    $$
> >      select c.cid, c.subject, n.title
> >      from comments c, node n
> >      where c.nid = n.nid
> >      and c.status != 0;
> >    $$
> >    language sql;
> >
> > ....or something like that. If you use pl/pgsql, then
> you'll need to use
> > a different idiom:
> >
> >    create or replace function
> comments_for_approval()
> >    returns setof record
> >    as
> >    $$
> >    declare
> >      m_rec record;
> >    begin
> >      for m_rec in
> >        select c.cid, c.subject,
> n.title
> >        from comments c, node n
> >        where c.nid = n.nid
> >        and c.status != 0
> >      loop
> >        return next m_rec;
> >      end loop;
> >      return;
> >    end;
> >    $$
> >    language plpgsql;
> >
> > Either way, simply execute the query in psql:
> >
> >    select * from comments_for_approval();
> >
> > HTH,
> >
> > Ray.
> >
> >
>
> Ah perfect ! problem solved. Thanks !
>
> --
> Nilesh Govindarajan
> Site & Server Adminstrator
> www.itech7.com
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 13:55:35 +0000
> From: Greg Stark <gsstark@mit.edu>
> To: dipti shah <shahdipti1980@gmail.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: DDL trigger kind functionality in PostGreSQL
> Message-ID: <407d949e1002200555q2eb2dcbxdb6708203e882d2a@mail.gmail.com>
>
> On Thu, Feb 18, 2010 at 4:55 AM, dipti shah <shahdipti1980@gmail.com>
> wrote:
> > Hi,
> >
> > I was looking for SQL DDL trigger kind of
> functionality in PostGreSQL but
> > couldn;t find any.
>
> There isn't any.
>
> > Basically I want to make sure that no users
> > should use "DROP" command directly on my database even
> though he/she owner
> > of table or any database object. I want users to use
> stored procedures
> > always to drop or alter the tables and any other
> database objects.
>
> You could revoke their access to drop the objects and
> create SECURITY
> DEFINER functions which do the drops. Then only grant
> execute access
> to the people who should have had access to drop the
> objects in the
> first place.
>
> --
> greg
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 14:00:40 +0000
> From: Raymond O'Donnell <rod@iol.ie>
> To: Nilesh Govindarajan <lists@itech7.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: Procedures
> Message-ID: <4B7FEB08.3080804@iol.ie>
>
> On 20/02/2010 13:54, Nilesh Govindarajan wrote:
>
> >
> > Ah perfect ! problem solved. Thanks !
> >
>
> Glad it was that easy! You ought to read up on
> set-returning functions
> in the docs:
>
>
> http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
>
> See the section on "RETURN NEXT and RETURN QUERY".
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 17:22:25 +0000
> From: Andre Lopes <lopes80andre@gmail.com>
> To: pgsql-general@postgresql.org
> Subject: Transactions, How to?
> Message-ID: <18f98e681002200922y3c98614an1261abcdbc627ad6@mail.gmail.com>
>
> Hi,
>
> I need to do a procedure that does an INSERT and an
> UPDATE.
>
> The procedure works in this way:
>
> [quote]
> CREATE OR REPLACE FUNCTION
> "public"."apr_insert_newsletter_distritos"
> ("pSTRING_ARRAY" varchar, "pEMAIL" varchar, "pITEMS"
> integer,
> "pID_WEBSITE_RECOLHA" varchar) RETURNS void AS
> $body$
> DECLARE
>     pSTRING_ARRAY    alias for $1;
>     pEMAIL      alias for $2;
>     pITEMS     alias for $3;
>     pID_WEBSITE_RECOLHA  alias for $4;
>     vARRAY_DIST    varchar[];
> BEGIN
>  -- Passar o pSTRING_ARRAY para ARRAY
>  SELECT string_to_array(pSTRING_ARRAY, ',') into
> vARRAY_DIST;
>     --RAISE NOTICE 'BILHETE_IDENTIFICACAO: %' ,
> vARRAY_DIST;
>
>     -- Transacção
>
>      FOR i IN 1 .. pITEMS LOOP
>      INSERT INTO
> am_newsletter_distritos (email, id_website_recolha,
> id_distrito)
>      values (pEMAIL,
> pID_WEBSITE_RECOLHA, vARRAY_DIST[i]);
>      END LOOP;
>
>      update am_newsletter_emails set
> subscri_completa = '1'
>      where email = pEMAIL and
> id_website_recolha = pID_WEBSITE_RECOLHA;
>
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY
> INVOKER;
> [/quote]
>
> Compile OK
>
> But I can't complite if I add to the procedure a BEGIN and
> a COMMIT. I think
> this BEGIN and COMMIT will make a real transaction or I'am
> wrong?
>
> [quote]
> CREATE OR REPLACE FUNCTION
> "public"."apr_insert_newsletter_distritos"
> ("pSTRING_ARRAY" varchar, "pEMAIL" varchar, "pITEMS"
> integer,
> "pID_WEBSITE_RECOLHA" varchar) RETURNS void AS
> $body$
> DECLARE
>     pSTRING_ARRAY    alias for $1;
>     pEMAIL      alias for $2;
>     pITEMS     alias for $3;
>     pID_WEBSITE_RECOLHA  alias for $4;
>     vARRAY_DIST    varchar[];
> BEGIN
>  -- Passar o pSTRING_ARRAY para ARRAY
>  SELECT string_to_array(pSTRING_ARRAY, ',') into
> vARRAY_DIST;
>     --RAISE NOTICE 'BILHETE_IDENTIFICACAO: %' ,
> vARRAY_DIST;
>
>     -- Transacção
>     BEGIN
>      FOR i IN 1 .. pITEMS LOOP
>      INSERT INTO
> am_newsletter_distritos (email, id_website_recolha,
> id_distrito)
>      values (pEMAIL,
> pID_WEBSITE_RECOLHA, vARRAY_DIST[i]);
>      END LOOP;
>
>      update am_newsletter_emails set
> subscri_completa = '1'
>      where email = pEMAIL and
> id_website_recolha = pID_WEBSITE_RECOLHA;
>     COMMIT;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY
> INVOKER;
> [/quote]
>
> Compile error: ERROR:  syntax error at end of input at
> character 917
>
>
> What I need to do to have a real atomic transaction?
>
>
> Best Regards,
>
>
> [Attachment of type text/html removed.]
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 18:34:34 +0100
> From: Andreas Kretschmer <akretschmer@spamfence.net>
> To: pgsql-general@postgresql.org
> Subject: Re: Transactions, How to?
> Message-ID: <20100220173434.GA3743@tux>
>
> Andre Lopes <lopes80andre@gmail.com>
> wrote:
>
> > 
> > But I can't complite if I add to the procedure a BEGIN
> and a COMMIT. I think
> > this BEGIN and COMMIT will make a real transaction or
> I'am wrong?
>
> Right, you are wrong ;-)
>
> A function is atomic, you don't need a begin/commit
> inside.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be
> a completely
> unintentional side effect.       
>                
>       (Linus Torvalds)
> "If I was god, I would recompile penguin with
> --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.     
>         N 51.05082°, E 13.56889°
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 17:36:49 +0000
> From: Andre Lopes <lopes80andre@gmail.com>
> To: Andreas Kretschmer <akretschmer@spamfence.net>
> Cc: pgsql-general@postgresql.org
> Subject: Re: Transactions, How to?
> Message-ID: <18f98e681002200936y4ce5ac05s4b7b40839d6bd948@mail.gmail.com>
>
> Tkanks for the reply.
>
> Best Regards,
>
>
>
> On Sat, Feb 20, 2010 at 5:34 PM, Andreas Kretschmer <
> akretschmer@spamfence.net>
> wrote:
>
> > Andre Lopes <lopes80andre@gmail.com>
> wrote:
> >
> > >
> > > But I can't complite if I add to the procedure a
> BEGIN and a COMMIT. I
> > think
> > > this BEGIN and COMMIT will make a real
> transaction or I'am wrong?
> >
> > Right, you are wrong ;-)
> >
> > A function is atomic, you don't need a begin/commit
> inside.
> >
> >
> > Andreas
> > --
> > Really, I'm not out to destroy Microsoft. That will
> just be a completely
> > unintentional side effect.       
>                
>       (Linus Torvalds)
> > "If I was god, I would recompile penguin with
> --enable-fly."   (unknown)
> > Kaufbach, Saxony, Germany, Europe.     
>         N 51.05082°, E 13.56889°
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
>
> [Attachment of type text/html removed.]
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 16:13:45 +0000
> From: Heddon's Gate Hotel <hotel@heddonsgate.co.uk>
> To: rod@iol.ie
> Cc: pgsql-general@postgresql.org
> Subject: Re: PostgreSQL fails to start
> Message-ID: <4B800A39.1010806@heddonsgate.co.uk>
>
> Thanks very much for the tip, Ray - it has led me to
> discover the
> Windows Event Viewer, which I did not even know
> existed.  It was a sort
> of help, because it enabled me - eventually - to diagnose
> that the
> problem was to do with directory permissions.
>
> I had installed PostgreSQL with its data directory set to
> be under the
> Administrator's "home" directory, since that is the account
> I log on as.
>   It seems that PostgreSQL, running as user postgres,
> couldn't see this
> directory.  I have since reinstalled PostgreSQL,
> setting the data
> directory to be somewhere that Windows doesn't seem to
> restrict the
> permissions of (that weirdness is another whole story), and
> it runs fine.
>
> This is a real gotcha in the PostgreSQL Windows
> installation process -
> it should give you at least a warning about trying to
> create a data
> directory in a location that is not accessible to the
> postgres user.
>
> It also seems to show a weakness in PostgreSQL's logging,
> in that (a) it
> would help a lot if it just printed its error to stderr,
> and (b) the log
> message it did send to the event log was of the form
> "directory not
> found" rather than "permission denied".
>
> Eddie
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 16:13:45 +0000
> From: Heddon's Gate Hotel <hotel@heddonsgate.co.uk>
> To: rod@iol.ie
> Cc: pgsql-general@postgresql.org
> Subject: Re: PostgreSQL fails to start
> Message-ID: <4B800A39.1010806@heddonsgate.co.uk>
>
> Thanks very much for the tip, Ray - it has led me to
> discover the
> Windows Event Viewer, which I did not even know
> existed.  It was a sort
> of help, because it enabled me - eventually - to diagnose
> that the
> problem was to do with directory permissions.
>
> I had installed PostgreSQL with its data directory set to
> be under the
> Administrator's "home" directory, since that is the account
> I log on as.
>   It seems that PostgreSQL, running as user postgres,
> couldn't see this
> directory.  I have since reinstalled PostgreSQL,
> setting the data
> directory to be somewhere that Windows doesn't seem to
> restrict the
> permissions of (that weirdness is another whole story), and
> it runs fine.
>
> This is a real gotcha in the PostgreSQL Windows
> installation process -
> it should give you at least a warning about trying to
> create a data
> directory in a location that is not accessible to the
> postgres user.
>
> It also seems to show a weakness in PostgreSQL's logging,
> in that (a) it
> would help a lot if it just printed its error to stderr,
> and (b) the log
> message it did send to the event log was of the form
> "directory not
> found" rather than "permission denied".
>
> Eddie
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 22:38:03 +0100
> From: "Massa, Harald Armin" <chef@ghum.de>
> To: "Heddon's Gate Hotel" <hotel@heddonsgate.co.uk>
> Cc: rod <rod@iol.ie>,
> pgsql-general <pgsql-general@postgresql.org>
> Subject: Re: PostgreSQL fails to start
> Message-ID: <e3e180dc1002201338r30a3f3bbx3c85878667b731dc@mail.gmail.com>
>
> Eddie,
>
>
> > It also seems to show a weakness in PostgreSQL's
> logging, in that (a) it
> > would help a lot if it just printed its error to
> stderr, and (b) the log
> > message it did send to the event log was of the form
> "directory not found"
> > rather than "permission denied".
> >
>
> problem for sending to stderr: there is no stderr for a
> service; so using
> the eventlog is the "thing to do" for services.
>
> And "permission denied" is the error PostgreSQL gets from
> the operating
> system when trying to access its datadirectory; Windows
> will (correctly!)
> not allow an non-permitted user to query for existence of
> things it has no
> access to.
>
> best wishes,
>
> Harald
>
> --
> GHUM Harald Massa
> persuadere et programmare
> Harald Armin Massa
> Spielberger Straße 49
> 70435 Stuttgart
> 0173/9409607
> no fx, no carrier pigeon
> -
> %s is too gigantic of an industry to bend to the whims of
> reality
>
>
> [Attachment of type text/html removed.]
>
> ------------------------------
>
> Date: Sat, 20 Feb 2010 22:42:22 +0100
> From: John Gage <jsmgage@numericable.fr>
> To: pgsql-general@postgresql.org
> Subject: Re: PostgreSQL fails to start
> Message-ID: <695F0338-F8F4-497F-8C1A-93F17CAE1394@numericable.fr>
>
> I have had the same/similar problem on a Mac. 
> Postgres creates a user 
> "postgres" and the only way that user can see files is for
> them to 
> exist outside of any other particular user's home
> directory.  I placed 
> the files in the root directory!?  I would like, I
> think, to give 
> "postgres" privileges in my home directory (emphasis, I
> think).  As a 
> sign of despicable laziness, could I ask where the granting
> of user 
> privileges is documented in the 8.4 docs?
>
> John
>
>
> On Feb 20, 2010, at 5:13 PM, Heddon's Gate Hotel wrote:
>
> > Thanks very much for the tip, Ray - it has led me to
> discover the 
> > Windows Event Viewer, which I did not even know
> existed.  It was a 
> > sort of help, because it enabled me - eventually - to
> diagnose that 
> > the problem was to do with directory permissions.
> >
> > I had installed PostgreSQL with its data directory set
> to be under 
> > the Administrator's "home" directory, since that is
> the account I 
> > log on as.  It seems that PostgreSQL, running as
> user postgres, 
> > couldn't see this directory.  I have since
> reinstalled PostgreSQL, 
> > setting the data directory to be somewhere that
> Windows doesn't seem 
> > to restrict the permissions of (that weirdness is
> another whole 
> > story), and it runs fine.
> >
> > This is a real gotcha in the PostgreSQL Windows
> installation process 
> > - it should give you at least a warning about trying
> to create a 
> > data directory in a location that is not accessible to
> the postgres 
> > user.
> >
> > It also seems to show a weakness in PostgreSQL's
> logging, in that 
> > (a) it would help a lot if it just printed its error
> to stderr, and 
> > (b) the log message it did send to the event log was
> of the form 
> > "directory not found" rather than "permission
> denied".
> >
> > Eddie
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>
> ------------------------------
>
> End of [pgsql-general] Daily digest v1.9718 (19 messages)
> **********
>