Re: [BUGS] server process exited with exit code -1073741819 on 8.2 Windows

2006-12-20 Thread JEAN-PIERRE PELLETIER 

I've compiled 8.3 devel from
the latest nightly snapshot tarball, did an initdb
and when run, it crash by logging the same messages.

Jean-Pierre Pelletier


From: Alvaro Herrera <[EMAIL PROTECTED]>
To: JEAN-PIERRE PELLETIER <[EMAIL PROTECTED]>
CC: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] server process exited with exit code -1073741819 on 8.2 
Windows

Date: Wed, 20 Dec 2006 15:31:11 -0300

JEAN-PIERRE PELLETIER wrote:

> I just upgraded to PostgreSQL 8.2 and have a function
> which crashes PostgreSQL 8.2 while logging these messages:
>
> server process exited with exit code -1073741819
> terminating any other active server processes

There was an SPI bug which may explain your problem, fixed after 8.2 was
released.  This is the fix:

http://archives.postgresql.org/pgsql-committers/2006-12/msg00063.php

Not sure how you could get a patched version short of compiling it
yourself.  Or you could wait for 8.2.1.

--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.




---(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: [BUGS] server process exited with exit code -1073741819 on 8.2 Windows

2006-12-20 Thread JEAN-PIERRE PELLETIER 
I've compiled the 8.2.0 sources from 
http://www.postgresql.org/ftp/source/v8.2/

and when run, it crash by logging the same messages.

I'll compile from the nightly snapshot tarball (which is probably 8.3 ?) and 
will post

the result.

Jean-Pierre Pelletier


From: Alvaro Herrera <[EMAIL PROTECTED]>
To: JEAN-PIERRE PELLETIER <[EMAIL PROTECTED]>
CC: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] server process exited with exit code -1073741819 on 8.2 
Windows

Date: Wed, 20 Dec 2006 15:31:11 -0300

JEAN-PIERRE PELLETIER wrote:

> I just upgraded to PostgreSQL 8.2 and have a function
> which crashes PostgreSQL 8.2 while logging these messages:
>
> server process exited with exit code -1073741819
> terminating any other active server processes

There was an SPI bug which may explain your problem, fixed after 8.2 was
released.  This is the fix:

http://archives.postgresql.org/pgsql-committers/2006-12/msg00063.php

Not sure how you could get a patched version short of compiling it
yourself.  Or you could wait for 8.2.1.

--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.




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

[BUGS] server process exited with exit code -1073741819 on 8.2 Windows

2006-12-20 Thread JEAN-PIERRE PELLETIER 

Hi,

Tom:
Sorry to email you directly but the mailing lists seem to be down
and you fixed a similar problem I reported back in October.

I just upgraded to PostgreSQL 8.2 and have a function
which crashes PostgreSQL 8.2 while logging these messages:

server process exited with exit code -1073741819
terminating any other active server processes

It crashes under Windows XP Service Pack 2 and Windows Server 2003.
Note that it worked fine with PostgreSQL 8.1

Strangely, Yesterday I got it working a few hours by calling it with
select * from UDFActualPerformanceVsStandard($1,$2::CHAR) from within
another plpgsql function but then it got back to crashing.

Thanks,
Jean-Pierre Pelletier
e-djuster

To Reproduce:

CREATE TABLE Claim (
  ClaimIdINTEGER NOT NULL,
  AssociatePersonId INTEGER NULL,
  IsOnSite   BOOLEAN NOT NULL
);

CREATE TABLE SubTask (
  TaskCode   VARCHAR(3)  NOT NULL,
  subTaskId  SMALLINTNOT NULL,
  ReportTaskCode VARCHAR(2)  NULL
);

CREATE TABLE WorkEntry (
  DurationHour  INTERVAL(0)  NULL,
  TaskCode  VARCHAR(3)   NULL,
  SubTaskId SMALLINT NULL,
  PersonId  INTEGER  NOT NULL,
  ClaimId   INTEGER  NULL,
  ExtensionNo   CHAR(1)  NULL
);

INSERT INTO Claim values (1,0,false);

CREATE TYPE UDTActualPerformanceVsStandard AS (
  ClaimId  INTEGER,
  ExtensionNo  CHAR,
  IsStandard   BOOLEAN,
  StandardRoleId   SMALLINT,
  PersonId INTEGER,
  ReportTaskCode   VARCHAR,
  PersonOrStandardRoleTaskCountItemBIGINT,
  PersonOrStandardRoleTaskDistanceKm   DECIMAL,
  PersonOrStandardRoleTaskDurationHour INTERVAL
);

CREATE OR REPLACE FUNCTION UDFActualPerformanceVsStandard(
  PClaimId INTEGER,
  PExtensionNo CHAR
) RETURNS SETOF UDTActualPerformanceVsStandard AS $$
  DECLARE
 isOnSite  BOOLEAN;
 associatePersonId INTEGER;
 ResultRow UDTActualPerformanceVsStandard%ROWTYPE;
  BEGIN
 SELECT INTO isOnSite, associatePersonId C.IsOnSite, 
C.AssociatePersonId FROM Claim C WHERE PClaimId = C.ClaimId;


 FOR resultRow IN
SELECT
   PClaimId AS ClaimId,
   PExtensionNo AS ExtensionNo,
   IsStandard,
   NULL AS StandardRoleId,
   PersonId,
   ReportTaskCode,
   SUM(PersonOrStandardRoleTaskCountItem),
   SUM(PersonOrStandardRoleTaskDistanceKm),
   SUM(PersonOrStandardRoleTaskDurationHour)
FROM
   (SELECT
  FALSE AS IsStandard,
  WE.PersonId,
  ST.ReportTaskCode,
  CAST(NULL AS BIGINT)  AS PersonOrStandardRoleTaskCountItem,
  CAST(NULL AS DECIMAL) AS PersonOrStandardRoleTaskDistanceKm,
  SUM(WE.DurationHour)  AS PersonOrStandardRoleTaskDurationHour
   FROM
  WorkEntry WE

  INNER JOIN SubTask ST
  ON  WE.TaskCode  = ST.TaskCode
  AND WE.SubTaskId = ST.SubTaskId
   WHERE
  WE.ClaimId = PClaimId
  AND WE.ExtensionNo = PExtensionNo
   GROUP BY
  WE.PersonId,

  ST.ReportTaskCode
   UNION ALL
   SELECT
  FALSE,
  associatePersonId,
  'DE',
  NULL,
  NULL,
  NULL
   ) NamedSubselect
WHERE
  PersonOrStandardRoleTaskCountItemIS NOT NULL
   OR PersonOrStandardRoleTaskDistanceKm   IS NOT NULL
   OR PersonOrStandardRoleTaskDurationHour IS NOT NULL
   OR (associatePersonId = personId AND 'DE' = ReportTaskCode)
GROUP BY
   IsStandard,
   PersonId,
   ReportTaskCode
 LOOP
RETURN NEXT resultRow;
 END LOOP;

 RETURN;
  END;
$$ LANGUAGE PLPGSQL STABLE;

select * from UDFActualPerformanceVsStandard(1,'A');



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

Re: [BUGS] ERROR: failed to build any 4-way joins SQL state: XX000, PostgreSQL

2006-11-08 Thread JEAN-PIERRE PELLETIER 

This is just to confirm that 8.2 beta3 fixes that problem.

Thanks again,
Jean-Pierre Pelletier


From: Tom Lane <[EMAIL PROTECTED]>
To: "JEAN-PIERRE PELLETIER" <[EMAIL PROTECTED]>
CC: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] ERROR: failed to build any 4-way joins SQL state: 
XX000, PostgreSQL 8.2 beta1 Date: Tue, 24 Oct 2006 13:51:31 -0400


"JEAN-PIERRE PELLETIER" <[EMAIL PROTECTED]> writes:
> I have a query that throws "ERROR: failed to build any 4-way joins

Fixed --- thanks for the report!  (This didn't make beta2, but will
be in the next one.)

regards, tom lane




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

[BUGS] ERROR: failed to build any 4-way joins SQL state: XX000, PostgreSQL 8.2 beta1

2006-10-24 Thread JEAN-PIERRE PELLETIER 

Hi,

I have a query that throws "ERROR: failed to build any 4-way joins
SQL state: XX000".

Here's an (arguably) simplified version of it that doesn't require any of my 
table:


select
  1
from
  (select 1 as col) t1

  cross join (select 1 as col) t2

  left outer join (select 1 as col) t3
  on  t1.col = t3.col
  and t2.col = t3.col
  and 1 = t3.col

  left outer join (select 1 as col) t4
  on t3.col = t4.col

I am on PostgreSQL 8.2 beta1 under Windows XP Service Pack 2.

Thanks,
Jean-Pierre Pelletier
e-djuster



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

Re: [BUGS] BUG #2178: NOT IN command don't work

2006-01-24 Thread Jean-Pierre Pelletier 

The expected behavior can be obtained by filtering out the null in
the subquery or by using "not exists" instead of "not in".

Here is an example:

CREATE TEMPORARY TABLE subquerytable (column1 INTEGER);
INSERT INTO subquerytable VALUES(2);
INSERT INTO subquerytable VALUES(NULL);
INSERT INTO subquerytable VALUES(3);

SELECT true WHERE 1 NOT IN (SELECT column1 FROM subquerytable); -- Wrong
SELECT true WHERE 1 NOT IN (SELECT column1 FROM subquerytable WHERE column1 
IS NOT NULL); -- Ok
SELECT true WHERE NOT EXISTS(SELECT * FROM subquerytable WHERE 1 = 
column1); -- Ok


It's not clear to me why "not exists" and "not in" return a different result 
but it must be per SQL spec

as all DBMS I have seen do that (Oracle, SQL Server, MYSQL, ...)

In most queries I have seen column1 is NOT NULL so IN or EXISTS can both be 
used safely.


Jean-Pierre Pelletier
e-djuster 



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

Re: [BUGS] RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3

2005-10-25 Thread Jean-Pierre Pelletier 

Thanks for the speedy fix.

I agree that this is not a typical query, in it Table2.t3id and Table3.t3id 
would always join

(a foreing key constraint ensure that) but columns from Table3 should
sometimes be excluded which is taken care by "table1.extension in 
(table2.replacement)".


- Original Message - 
From: "Tom Lane" <[EMAIL PROTECTED]>

To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
Cc: 
Sent: Tuesday, October 25, 2005 4:34 PM
Subject: Re: [BUGS] RIGHT JOIN is only supported with merge-joinable join 
conditions, PostgreSQL 8.1 beta3




"Jean-Pierre Pelletier" <[EMAIL PROTECTED]> writes:

select
   count(table3.*)
from
   table1
   inner join table2
   on table1.t1id = table2.t1id
   and table1.extension in (table2.original, table2.replacement)
   left outer join table3
   on table2.t3id = table3.t3id
   and table1.extension in (table2.replacement);


I've applied the attached patch (for 8.1, variants as needed for back
branches) to fix this failure.

BTW, I think the reason nobody saw this before is that using a condition
on table1 vs table2 in the outer-join condition for table3 is a bit, er,
weird.  Are you sure that the original query will do what you really
wanted?

But anyway, many thanks for the test case!

regards, tom lane

Index: joinpath.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.96
diff -c -r1.96 joinpath.c
*** joinpath.c 15 Oct 2005 02:49:20 - 1.96
--- joinpath.c 25 Oct 2005 19:52:54 -
***
*** 795,800 
--- 795,801 
 {
 List*result_list = NIL;
 bool isouterjoin = IS_OUTER_JOIN(jointype);
+ bool have_nonmergeable_joinclause = false;
 ListCell   *l;

 foreach(l, restrictlist)
***
*** 803,844 

 /*
 * If processing an outer join, only use its own join clauses in the
! * merge.  For inner joins we need not be so picky.
! *
! * Furthermore, if it is a right/full join then *all* the explicit join
! * clauses must be mergejoinable, else the executor will fail. If we
! * are asked for a right join then just return NIL to indicate no
! * mergejoin is possible (we can handle it as a left join instead). If
! * we are asked for a full join then emit an error, because there is
! * no fallback.
 */
! if (isouterjoin)
! {
! if (restrictinfo->is_pushed_down)
! continue;
! switch (jointype)
! {
! case JOIN_RIGHT:
! if (!restrictinfo->can_join ||
! restrictinfo->mergejoinoperator == InvalidOid)
! return NIL; /* not mergejoinable */
! break;
! case JOIN_FULL:
! if (!restrictinfo->can_join ||
! restrictinfo->mergejoinoperator == InvalidOid)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("FULL JOIN is only supported with merge-joinable join 
conditions")));

! break;
! default:
! /* otherwise, it's OK to have nonmergeable join quals */
! break;
! }
! }

 if (!restrictinfo->can_join ||
 restrictinfo->mergejoinoperator == InvalidOid)
 continue; /* not mergejoinable */

 /*
 * Check if clause is usable with these input rels.  All the vars
--- 804,822 

 /*
 * If processing an outer join, only use its own join clauses in the
! * merge.  For inner joins we can use pushed-down clauses too.
! * (Note: we don't set have_nonmergeable_joinclause here because
! * pushed-down clauses will become otherquals not joinquals.)
 */
! if (isouterjoin && restrictinfo->is_pushed_down)
! continue;

 if (!restrictinfo->can_join ||
 restrictinfo->mergejoinoperator == InvalidOid)
+ {
+ have_nonmergeable_joinclause = true;
 continue; /* not mergejoinable */
+ }

 /*
 * Check if clause is usable with these input rels.  All the vars
***
*** 856,865 
--- 834,870 
 /* lefthand side is inner */
 }
 else
+ {
+ have_nonmergeable_joinclause = true;
 continue; /* no good for these input relations */
+ }

 result_list = lcons(restrictinfo, result_list);
 }

+ /*
+ * If it is a right/full join then *all* the explicit join clauses must 
be
+ * mergejoinable, else the executor will fail. If we are asked for a 
right

+ * join then just return NIL to indicate no mergejoin is possible (we can
+ * handle it as a left join instead). If we are asked for a full join 
then

+ * emit an error, because there is no fallback.
+ */
+ if (have_nonmergeable_joinclause)
+ {
+ switch (jointype)
+ {
+ case JOIN_RIGHT:
+ return NIL; /* not mergejoinable */
+ case JOIN_FULL:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("FULL JOIN is only supported with merge-joinable join 
conditions")));

+ break;
+ default:
+ /* otherwise, it's OK to have nonmergeable join quals */
+ break;
+ }
+ }
+
 return result_list;
 }

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining

[BUGS] Variable not found in subplan target lists, PostgreSQL 8.1 beta3

2005-10-25 Thread Jean-Pierre Pelletier 

Hi,

I have a query that throws error "Variable not found in subplan target 
lists".

This should allow it to be reproduce.

create temporary table table1 (
 col1 integer not null,
 col2 integer not null
);

create temporary table table2 ();

create or replace function udftable1row(integer, integer) returns table1 AS 
$$

  select $1, $2;
$$ language sql immutable;

create or replace function udf(table1) returns void as $$
$$ language sql immutable;

-- This throws "Variable not found in subplan target lists"
select
  udf(t1)
from
  udftable1Row(1,2) t1

  cross join table2;

-- Now that we have Row constructor, I can get rid of
my function udftable1row() and this works ok

select
  udf(t1)
from
  (select (cast(row(1,2) as table1)).*) t1

  cross join table2;

I am on PostgreSQL 8.1 beta3 under Windows XP Service Pack 2.

Thanks,
Jean-Pierre Pelletier
e-djuster 



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

[BUGS] RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3

2005-10-25 Thread Jean-Pierre Pelletier 

Hi,

I have a query that throws error "RIGHT JOIN is only supported with 
merge-joinable join conditions".

This should allow it to be reproduce.

create table table1 (t1id integer not null, extension integer not null);
create table table2 (t1id integer not null, t3id integer not null, original 
integer not null, replacement integer not null);

create table table3 (t3id integer not null);
create unique index table3ix1 on table3 (t3id);
insert into table3 select * from generate_series(1,1);

select
  count(table3.*)
from
  table1

  inner join table2
  on table1.t1id = table2.t1id
  and table1.extension in (table2.original, table2.replacement)

  left outer join table3
  on table2.t3id = table3.t3id
  and table1.extension in (table2.replacement);

I am on PostgreSQL 8.1 beta3 under Windows XP Service Pack 2.

Thanks
Jean-Pierre Pelletier
e-djuster 



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

[BUGS] function currval(character varying) does not exist, PostgreSQL 8.1 beta3

2005-10-20 Thread Jean-Pierre Pelletier 



Hi,
 
   pstmt = 
connection.prepareStatement("select currval(?)"); throws "function 
currval(character varying) does not exist"
 

   Using pgAdmin, I could see that 
currval() now takes a regclass parameter, so
   I got it to work using
 
   pstmt = 
connection.prepareStatement("select currval(cast(cast(? as text) as 
regclass))");
   Is currval(text) gone for good or is 
this an oversight?
   If it's gone for good, is cast(cast(? 
as text) as regclass) the recommended way of handling this?
 
   I am using PostgreSQL 8.1 beta3 
under Windows XP 
Service Pack 2 with JDBC3 8.1 Build 
402.
   It worked fine with PostgreSQL 8.1 
beta2 and with 8.0.*
 
Thanks,
Jean-Pierre Pelletier
e-djuster

Re: [BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 beta2, Windows 2000

2005-10-11 Thread Jean-Pierre Pelletier 

The antivirus is CA eTrust EZ v 7.0.6.7.

I cannot put back the antivirus on that server
because it is now in production mode.

Jean-Pierre Pelletier

- Original Message - 
From: "Qingqing Zhou" <[EMAIL PROTECTED]>

To: 
Sent: Friday, October 07, 2005 3:08 PM
Subject: Re: [BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 beta2, 
Windows 2000





""Jean-Pierre Pelletier"" <[EMAIL PROTECTED]> wrote

Turning off the antivirus fixed the problem.
We haven't have any read/write/open error in more
than  two days.

Thank you very much for your help and keep up the good work.



You are welcome :-) But I still suspect if this really solves the problem 
... by the way, may I know what anti-virus software are you using? And, if 
it is possible, can you please turn on the anti-virus software again and 
check the GetLastError()?


A more detailed "guess" of the problem is here:
http://archives.postgresql.org/pgsql-hackers/2005-07/msg00489.php

Thanks a lot,
Qingqing


---(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 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 beta2, Windows 2000

2005-10-07 Thread Jean-Pierre Pelletier 

Turning off the antivirus fixed the problem.
We haven't have any read/write/open error in more
than  two days.

Thank you very much for your help and keep up the good work.

Our only remaining PostgreSQL problem is with pg_stat_actitivity
being unreliable and the statistics collector being restarted many times
every day.

Any idea what might be causing that?

Jean-Pierre Pelletier

- Original Message - 
From: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>

To: "Qingqing Zhou" <[EMAIL PROTECTED]>
Cc: 
Sent: Wednesday, October 05, 2005 2:58 PM
Subject: Re: [BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 beta2, 
Windows 2000




I'll recompile with the trace that's no problem,
and install the patched release tonight.

After your last email, I've excluded the postgreSQL
directory from the antivirus because I could do it without
rebooting.

I was also sometimes getting read/write or open
error Invalid argument without the server crashing.
After two days, if I haven't seen any of these
error messages there is a very high chance that it's
been fixed by turning off the antivirus.

Jean-Pierre Pelletier

- Original Message - 
From: "Qingqing Zhou" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, October 05, 2005 5:16 PM
Subject: Re: [BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 
beta2,

Windows 2000




""Jean-Pierre Pelletier"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]


Yes, there is an antivirus software on the machine, a reboot is needed
when it's turned off,
I'll be allowed to reboot it tonight or I'll do it sooner if it crashes
before that.

There are around 15 connections to PostgreSQL when it crashes but most
are idle
there may be a few inserts but no bulk inserts, the biggest load would
come from
select statements.



We haven't identified that the failed read/write are caused by anti-virus
software or intensive read/write. If you can compile the source, can you
patch smgrread()/smgrwrite() like this to capture the native windows
error:

void
smgrwrite(SMgrRelation reln, BlockNumber blocknum, char *buffer, bool
isTemp)
{
if (!(*(smgrsw[reln->smgr_which].smgr_write)) (reln, blocknum, buffer,
  isTemp))
  ereport(ERROR,
   (errcode_for_file_access(),
errmsg("could not write block %u of relation %u/%u/%u:%d: %m",
 blocknum,
 reln->smgr_rnode.spcNode,
 reln->smgr_rnode.dbNode,
 reln->smgr_rnode.relNode,
 GetLastError(;
}

Regards,
Qingqing


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



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



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

Re: [BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 beta2, Windows 2000

2005-10-05 Thread Jean-Pierre Pelletier 

I'll recompile with the trace that's no problem,
and install the patched release tonight.

After your last email, I've excluded the postgreSQL
directory from the antivirus because I could do it without
rebooting.

I was also sometimes getting read/write or open
error Invalid argument without the server crashing.
After two days, if I haven't seen any of these
error messages there is a very high chance that it's
been fixed by turning off the antivirus.

Jean-Pierre Pelletier

- Original Message - 
From: "Qingqing Zhou" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, October 05, 2005 5:16 PM
Subject: Re: [BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 beta2,
Windows 2000




""Jean-Pierre Pelletier"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]


Yes, there is an antivirus software on the machine, a reboot is needed
when it's turned off,
I'll be allowed to reboot it tonight or I'll do it sooner if it crashes
before that.

There are around 15 connections to PostgreSQL when it crashes but most
are idle
there may be a few inserts but no bulk inserts, the biggest load would
come from
select statements.



We haven't identified that the failed read/write are caused by anti-virus
software or intensive read/write. If you can compile the source, can you
patch smgrread()/smgrwrite() like this to capture the native windows
error:

void
smgrwrite(SMgrRelation reln, BlockNumber blocknum, char *buffer, bool
isTemp)
{
if (!(*(smgrsw[reln->smgr_which].smgr_write)) (reln, blocknum, buffer,
  isTemp))
  ereport(ERROR,
   (errcode_for_file_access(),
errmsg("could not write block %u of relation %u/%u/%u:%d: %m",
 blocknum,
 reln->smgr_rnode.spcNode,
 reln->smgr_rnode.dbNode,
 reln->smgr_rnode.relNode,
 GetLastError(;
}

Regards,
Qingqing


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



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

Re: [BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 beta2, Windows 2000

2005-10-05 Thread Jean-Pierre Pelletier 


Yes, there is an antivirus software on the machine, a reboot is needed when 
it's turned off,
I'll be allowed to reboot it tonight or I'll do it sooner if it crashes 
before that.


There are around 15 connections to PostgreSQL when it crashes but most are 
idle
there may be a few inserts but no bulk inserts, the biggest load would come 
from

select statements.

Jean-Pierre Pelletier

- Original Message - 
From: "Qingqing Zhou" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, October 05, 2005 3:03 AM
Subject: Re: [BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 beta2, 
Windows 2000





""Jean-Pierre Pelletier"" <[EMAIL PROTECTED]> wrote


I've installed PostgreSQL 8.1 beta2 five days ago and it crashed 3 times 
since then.

Here is what's been logged for the last crash

2005-10-04 11:00:19 FATAL:  could not read block 121 of relation 
1663/16384/2608: Invalid argument


relation 2608 is pg_depend
--
The crash before that was on relation pg_type, the first line logged was:
2005-10-03 10:51:06 FATAL:  could not read block 38 of relation 
1663/16384/1247: Invalid argument

--
The first crash was also on relation pg_depend, but with open instead or 
read
2005-09-30 18:38:53 FATAL:  could not open relation 1663/16384/2608: 
Invalid argument

--



This problem was reported several times before, but not necessarily system 
tables. Is there any anti-virus softwares installed on the same machine? 
Is the database under intensive IO pressure?


Regards,
Qingqing


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

  http://archives.postgresql.org 



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

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

[BUGS] Possibly corrupted shared memory, PostgreSQL 8.1 beta2, Windows 2000

2005-10-04 Thread Jean-Pierre Pelletier 

Hi,

I've installed PostgreSQL 8.1 beta2 five days ago and it crashed 3 times 
since then.

Here is what's been logged for the last crash

2005-10-04 11:00:19 FATAL:  could not read block 121 of relation 
1663/16384/2608: Invalid argument
2005-10-04 11:00:20 LOG:  server process (PID 2592) was terminated by signal 
1

2005-10-04 11:00:20 LOG:  terminating any other active server processes

Than for each connections, the log has:
2005-10-04 11:00:20 WARNING:  terminating connection because of crash of 
another server process
2005-10-04 11:00:20 DETAIL:  The postmaster has commanded this server 
process to roll back the current transaction and exit, because another 
server process exited abnormally and possibly corrupted shared memory.
2005-10-04 11:00:20 HINT:  In a moment you should be able to reconnect to 
the database and repeat your command.


With this in the end:
2005-10-04 11:00:20 LOG:  all server processes terminated; reinitializing
2005-10-04 11:00:21 LOG:  database system was interrupted at 2005-10-04 
10:59:43 Eastern Daylight Time


relation 2608 is pg_depend
--
The crash before that was on relation pg_type, the first line logged was:
2005-10-03 10:51:06 FATAL:  could not read block 38 of relation 
1663/16384/1247: Invalid argument

--
The first crash was also on relation pg_depend, but with open instead or 
read
2005-09-30 18:38:53 FATAL:  could not open relation 1663/16384/2608: Invalid 
argument

--

There was between 14 and 17 connections when these crashes happened.

The database was not reloaded from a backup but created from
.sql scripts for DDL, and data from user tables were reloaded
from files with "copy from".

We are using Windows 2000 Server, Service Pack 4.

Thanks,
Jean-Pierre Pelletier
e-djuster 



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

  http://archives.postgresql.org

[BUGS] Unreliable pg_stat_activity, Windows 2000, PostgreSQL 8.1 beta2

2005-10-04 Thread Jean-Pierre Pelletier 

Hi,

pg_stat_activity sometimes list no connections or a number of connections
much smaller than the number of postgres.exe processes.

We also have these messages in our PostgreSQL log approximately
20 times a day.

FATAL:  could not read from statistics collector pipe: No error
FATAL:  could not write to statistics collector pipe: No connection could be 
made because the target machine actively refused it.


or

FATAL:  could not read from statistics collector pipe: No error
LOG:  statistics collector process (PID 1628) was terminated by signal 1

It looks like the pg_stat_activity problem and the messages become
more frequent as the load/number of connection increases.

We were having the same pg_stat_activity problem and the same messages
with PostgreSQL 8.0.1 but didn't have the messages with PostgreSQL 7.4.1.
we don't know if pg_stat_activity was ok with 7.4.1 because we weren't using
it back then.

We are using Windows 2000 Server, Service Pack 4.

Thanks
Jean-Pierre Pelletier
e-djuster 



---(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: [BUGS] Unreliable pg_stat_activity, Windows 2000, PostgreSQL 8.1 beta2

2005-10-03 Thread Jean-Pierre Pelletier 

We were running 7.4 under Cygwin with Windows 2000 Service Pack 4.

- Original Message - 
From: "Jim C. Nasby" <[EMAIL PROTECTED]>

To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
Cc: 
Sent: Monday, October 03, 2005 6:00 PM
Subject: Re: [BUGS] Unreliable pg_stat_activity, Windows 2000, PostgreSQL 
8.1 beta2




How were you running 7.4? Under cygwin or on a different OS?

On Mon, Oct 03, 2005 at 12:21:09PM -0400, Jean-Pierre Pelletier wrote:

Hi,

pg_stat_activity sometimes list no connections or a number of connections
much smaller than the number of postgres.exe processes.

We also have these messages in our PostgreSQL log approximately
20 times a day.

FATAL:  could not read from statistics collector pipe: No error
FATAL:  could not write to statistics collector pipe: No connection could 
be

made because the target machine actively refused it.

or

FATAL:  could not read from statistics collector pipe: No error
LOG:  statistics collector process (PID 1628) was terminated by signal 1

It looks like the pg_stat_activity problem and the messages become
more frequent as the load/number of connection increases.

We were having the same pg_stat_activity problem and the same messages
with PostgreSQL 8.0.1 but didn't have the messages with PostgreSQL 7.4.1.
we don't know if pg_stat_activity was ok with 7.4.1 because we weren't 
using

it back then.

We are using Windows 2000 Server, Service Pack 4.

Thanks
Jean-Pierre Pelletier
e-djuster


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



--
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461 



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

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

[BUGS] Unreliable pg_stat_activity, Windows 2000, PostgreSQL 8.1 beta2

2005-10-03 Thread Jean-Pierre Pelletier 

Hi,

pg_stat_activity sometimes list no connections or a number of connections
much smaller than the number of postgres.exe processes.

We also have these messages in our PostgreSQL log approximately
20 times a day.

FATAL:  could not read from statistics collector pipe: No error
FATAL:  could not write to statistics collector pipe: No connection could be
made because the target machine actively refused it.

or

FATAL:  could not read from statistics collector pipe: No error
LOG:  statistics collector process (PID 1628) was terminated by signal 1

It looks like the pg_stat_activity problem and the messages become
more frequent as the load/number of connection increases.

We were having the same pg_stat_activity problem and the same messages
with PostgreSQL 8.0.1 but didn't have the messages with PostgreSQL 7.4.1.
we don't know if pg_stat_activity was ok with 7.4.1 because we weren't using
it back then.

We are using Windows 2000 Server, Service Pack 4.

Thanks
Jean-Pierre Pelletier
e-djuster


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

[BUGS] BUG #1585: could not read from statistics collector pipe

2005-04-06 Thread Jean-Pierre Pelletier 

The following bug has been logged online:

Bug reference:  1585
Logged by:  Jean-Pierre Pelletier
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.0.1
Operating system:   Windows 2000 Server, Service Pack 4
Description:could not read from statistics collector pipe
Details: 

We are running PostgreSQL 8.0.1 since february and have these
messages in our PostgreSQL log file:
 
The first time we get the error approx 1/2 hour after restarting PostgreSQL,
it is:
 
FATAL:  could not read from statistics collector pipe: No such file or
directory
LOG:  statistics collector process (PID 1628) was terminated by signal 1

and then a couple of time every hour, we get:
 
FATAL:  could not read from statistics collector pipe: No error
FATAL:  could not write to statistics collector pipe: No connection could be
made because the target machine actively refused it.
or:
FATAL:  could not read from statistics collector pipe: No error
LOG:  statistics collector process (PID 1628) was terminated by signal 1
 
Our application seems unaffected by these messages.
 
We are on Windows 2000 Server, Service Pack 4 and
were successfully running PostgreSQL 7.4.1 before that.

Is this normal and if not, how do we fix that?
 
Thanks
Jean-Pierre Pelletier

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

   http://archives.postgresql.org

[BUGS] could not read, could not write, could not fsync, Windows 2000, PostgreSQL 8.0.1

2005-02-18 Thread Jean-Pierre Pelletier 



We are running PostgreSQL 8.0.1 since last week and 
have thesemessages in our PostgreSQL log file:
 
2005-02-10 10:27:19 FATAL:  could not read 
block 38 of relation 1663/17269/16676: Invalid argument
2005-02-10 10:27:19 FATAL:  could not read 
block 46 of relation 1663/17269/16676: Invalid argument
2005-02-10 10:27:19 FATAL:  could not read 
block 50 of relation 1663/17269/16676: Invalid argument
16676 is table "pgdepend"
 
2005-02-14 12:19:46 FATAL:  could not read block 7 of relation 
1663/17269/1247: Invalid argument2005-02-14 12:19:46 FATAL:  could not 
read block 20 of relation 1663/17269/1247: Invalid argument2005-02-14 
12:19:46 FATAL:  could not read block 22 of relation 1663/17269/1247: 
Invalid argument2005-02-14 12:19:46 FATAL:  could not read block 14 of 
relation 1663/17269/1247: Invalid argument2005-02-14 12:19:46 FATAL:  
could not read block 18 of relation 1663/17269/1247: Invalid 
argument2005-02-14 12:19:46 FATAL:  could not read block 24 of relation 
1663/17269/1247: Invalid argument2005-02-14 12:19:46 FATAL:  could not 
read block 8 of relation 1663/17269/1247: Invalid argument2005-02-14 
12:19:46 FATAL:  could not read block 19 of relation 1663/17269/1247: 
Invalid argument2005-02-14 12:19:46 FATAL:  could not read block 11 of 
relation 1663/17269/1247: Invalid argument2005-02-14 12:19:46 FATAL:  
could not read block 21 of relation 1663/17269/1247: Invalid 
argument2005-02-14 12:19:46 FATAL:  could not read block 25 of relation 
1663/17269/1247: Invalid argument2005-02-14 12:19:46 FATAL:  could not 
read block 23 of relation 1663/17269/1247: Invalid argument2005-02-14 
12:19:46 FATAL:  could not read block 13 of relation 1663/17269/1247: 
Invalid argument2005-02-14 12:19:46 FATAL:  could not read block 9 of 
relation 1663/17269/1247: Invalid argument2005-02-14 12:19:46 FATAL:  
could not read block 12 of relation 1663/17269/1247: Invalid argument
 
1247 is table "pgtype"
 
2005-02-16 10:48:26 ERROR:  could not write block 61 of relation 
1663/17269/16676: Invalid argument
2005-02-16 10:48:26 CONTEXT:  writing block 61 of relation 
1663/17269/16676
16676 is table "pgdepend"
 
2005-02-16 12:47:03 ERROR:  could not write block 3 of relation 
1663/17269/1614690: Invalid argument
2005-02-16 12:47:03 CONTEXT:  writing block 3 of relation 
1663/17269/1614690
We couldn't find what 1614690 is?
 
2005-02-18 05:32:06 LOG:  could not fsync segment 0 of relation 
1663/17269/1677179: Permission denied
2005-02-18 05:32:06 ERROR:  storage sync failed on magnetic disk: 
Permission denied
 
...2005-02-18 07:58:28 ERROR:  storage sync failed on magnetic 
disk: Permission denied
2005-02-18 07:58:29 LOG:  could not fsync segment 0 of relation 
1663/17269/1677179: Permission denied
These two messages are repeated every seconds for almost 2.5 
hoursAgain, we couldn't find what 1677179 is?
 
We are on Windows 2000 Server, Service Pack 4 andwere successfully 
running PostgreSQL 7.4.1 before that.
 
We have done a vacuum, analyze and reindex on pgdepend and pgtype and 
restarted PostgreSQLa few times, we had no problems doing that but the error 
messages are still there.
 
Is this normal and if not, how do we fix 
that? ThanksJean-Pierre Pelletier
 
p.s.: We also have messages "FATAL:  could not read from statistics 
collector pipe" approx. twice an hour.
 

[BUGS] could not read from statistics collector pipe, Windows 2000, PostgreSQL 8.0.1

2005-02-15 Thread Jean-Pierre Pelletier 



We are running PostgreSQL 8.0.1 since last week and 
have thesemessages in our PostgreSQL log file:
 
The first time we get the error approx 1/2 
hour after restarting PostgreSQL, it is:
 
FATAL:  could not read from statistics 
collector pipe: No such file or directory
LOG:  statistics collector process (PID 1628) 
was terminated by signal 1
and then a couple of time every hour, we 
get:
 
FATAL:  could not read from statistics collector pipe: No error
FATAL:  could not write to statistics collector pipe: No connection 
could be made because the target machine actively refused it.
or:
FATAL:  could not read from statistics collector pipe: No error
LOG:  statistics collector process (PID 1628) was terminated by signal 
1
 
Our application seems unaffected by these messages.
 
We are on Windows 2000 Server, Service Pack 4 andwere successfully 
running PostgreSQL 7.4.1 before that.
Is this normal and if not, how do we fix that?
 
ThanksJean-Pierre Pelletier