Re: [BUGS] BUG #6139: LIMIT doesn't return correct result when the value is huge

2011-08-02 Thread Alex Hunsaker 
On Tue, Aug 2, 2011 at 00:25, Hitoshi Harada  wrote:

> db1=# select * from test_xy order by x LIMIT 9223372036854775807 OFFSET 6;
[ ...]

> db1=# select * from test_xy order by x LIMIT pow(2, 63);
> ERROR:  bigint out of range
> ERROR:  bigint out of range
>
> Maybe a parser converts literal unexpectedly?

pow(2, 63) != 9223372036854775807, pow(2, 63) - 1 does :-). On top of
that pow(2, 63) seems to default to the double variant of pow() which
causes rounding issues. You probably want LIMIT pow(2, 63::numeric)-1.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #6139: LIMIT doesn't return correct result when the value is huge

2011-08-02 Thread Heikki Linnakangas 

On 02.08.2011 09:25, Hitoshi Harada wrote:

db1=# select count(*) from test_xy;
  count
---
 31
(1 row)

db1=# select * from test_xy order by x LIMIT 9223372036854775807 OFFSET 6;
  gid | x  | y
-++
   13 | -0.591943957968476 | -0.481611208406305
(1 row)


The bug seems to occur when LIMIT + OFFSET >= 2^63. In ExecLimit 
function, we check if current position >= offset + limit, and that 
overflows.


I'll commit the attached patch to fix that. Thanks for the report

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
diff --git a/src/backend/executor/nodeLimit.c b/src/backend/executor/nodeLimit.c
index 85d1a6e..f2d356d 100644
--- a/src/backend/executor/nodeLimit.c
+++ b/src/backend/executor/nodeLimit.c
@@ -127,7 +127,7 @@ ExecLimit(LimitState *node)
  * the state machine state to record having done so.
  */
 if (!node->noCount &&
-	node->position >= node->offset + node->count)
+	node->position - node->offset >= node->count)
 {
 	node->lstate = LIMIT_WINDOWEND;
 	return NULL;

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #6140: PostgreSQL Server Service Not Starting

2011-08-02 Thread Steve Boyd 

The following bug has been logged online:

Bug reference:  6140
Logged by:  Steve Boyd
Email address:  sboy...@yahoo.com
PostgreSQL version: 8.3.12
Operating system:   Windows Server 2003 R2 SP2
Description:PostgreSQL Server Service Not Starting
Details: 

Good morning!

I've searched through the bug archives but can't seem to find an answer to
this issue.  We have version 8.3.12 installed and the PostgreSQL server
service is trying to start but doesn't. I receive the following message when
I click on the service:

"The PostgreSQL Server 8.3 service on Local Computer started then stopped. 
Some services stop automatically if they have no work to do, for example,
the Performance Logs and Alerts service."

I've checked the PostgreSQL logs and there is nothing there.  The last log
entry was in May.  I've checked the windows event viewer and get the
following error message:

"could not open process token:  error code 5"  

The service account is in the local administrators group and has full
control over the data directory.  Not sure what has changed.  I even
re-entered the password to confirm that the "logon as a service right" was
granted.

 I was going to try to upgrade to 8.3.15 but since pgdumpall won't work if
the service is not running, that's not an option.

Not sure where else to look.  Any help is appreciated.

Best regards,

Steve

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] backend for database 'A' crashes/is killed -> corrupt index in database 'B'

2011-08-02 Thread Jon Nelson 
On Thu, Mar 31, 2011 at 2:58 AM, Heikki Linnakangas
 wrote:
> On 30.03.2011 21:06, Jon Nelson wrote:
>>
>> The short version is that if a postgresql backend is killed (by the Linux
>> OOM handler, or kill -9, etc...) while operations are
>> taking place in a *different* backend, corruption is introduced in the
>> other
>> backend.  I don't want to say it happens 100% of the time, but it happens
>> every time I test.
>> ...
>>
>> Here is how I am reproducing the problem:
>>
>> 1. Open a psql connection to database A. It may remain idle.
>> 2. Wait for an automated process to connect to database B and start
>> operations. These operations
>> 3. kill -9 the backend for the psql connection to database A.
>>
>> Then I observe the backends all shutting down and postgresql entering
>> recovery mode, which succeeds.
>> Subsequent operations on other databases appear fine, but not for
>> database B: An index on one of the tables in database B is corrupted.
>> It is always the
>> same index.
>>
>> 2011-03-30 14:51:32 UTC   LOG:  server process (PID 3871) was terminated
>> by
>> signal 9: Killed
>> 2011-03-30 14:51:32 UTC   LOG:  terminating any other active server
>> processes
>> 2011-03-30 14:51:32 UTC   WARNING:  terminating connection because of
>> crash
>> of another server process
>> 2011-03-30 14:51:32 UTC   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.
>> 2011-03-30 14:51:32 UTC   HINT:  In a moment you should be able to
>> reconnect
>> to the database and repeat your command.
>> 2011-03-30 14:51:32 UTC databaseB databaseB WARNING:  terminating
>> connection
>> because of crash of another server process
>> 2011-03-30 14:51:32 UTC databaseB databaseB 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.
>> 2011-03-30 14:51:32 UTC databaseB databaseB HINT:  In a moment you should
>> be
>> able to reconnect to the database and repeat your command.
>> 2011-03-30 14:51:32 UTC   LOG:  all server processes terminated;
>> reinitializing
>> 2011-03-30 14:51:32 UTC   LOG:  database system was interrupted; last
>> known
>> up at 2011-03-30 14:46:50 UTC
>> 2011-03-30 14:51:32 UTC databaseB databaseB FATAL:  the database system is
>> in recovery mode
>> 2011-03-30 14:51:32 UTC   LOG:  database system was not properly shut
>> down;
>> automatic recovery in progress
>> 2011-03-30 14:51:32 UTC   LOG:  redo starts at 301/1D328E40
>> 2011-03-30 14:51:33 UTC databaseB databaseB FATAL:  the database system is
>> in recovery mode
>> 2011-03-30 14:51:34 UTC   LOG:  record with zero length at 301/1EA08608
>> 2011-03-30 14:51:34 UTC   LOG:  redo done at 301/1EA08558
>> 2011-03-30 14:51:34 UTC   LOG:  last completed transaction was at log time
>> 2011-03-30 14:51:31.257997+00
>> 2011-03-30 14:51:37 UTC   LOG:  autovacuum launcher started
>> 2011-03-30 14:51:37 UTC   LOG:  database system is ready to accept
>> connections
>> 2011-03-30 14:52:05 UTC databaseB databaseB ERROR:  index ""
>> contains unexpected zero page at block 0
>> 2011-03-30 14:52:05 UTC databaseB databaseB HINT:  Please REINDEX it.
>>
>> What's more, I can execute a 'DELETE from tableB' (where tableB is the
>> table that is the one with the troublesome index) without error, but
>> when I try to *insert* that is when I get a problem. The index is a
>> standard btree index. The DELETE statement has no where clause.
>
> Can you provide a self-contained test script to reproduce this?
>
> Is the corruption always the same, ie. "unexpected zero page at block 0" ?
>
>> My interpretation of these values is that the drives themselves have
>> their write caches disabled.
>
> Ok. It doesn't look like a hardware issue, as there's no OS crash involved.



I wanted to write this quick follow-up, so that anyone searching in
the future might have a chance of learning the resolution to this
issue.

The short version is this:  the database that experienced corrupted
indices was using HASH-based indices. As we all know, as of this
writing, HASH-based indices are _not_ crash-safe. When revised to use
btree-indices, the issue does not re-occur.

-- 
Jon

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] PostgreSQL 8.4

2011-08-02 Thread Glonet NV / Mathieu Aras 
Dear sir,

Can we install PostgreSQL 8.4 on a server 2008? Is it X64 and X32 compatible?

Best Regards,
Mathieu


Mathieu Aras
Systeembeheer & Ondersteuning


math...@glonet.be
http://www.glonet.be

Glonet NV
Beverestraat 59
B-9700 Oudenaarde

tel: +32 (0)55 21.63.82
fax: +32 (0)55 21.63.65

[cid:image001.png@01CC5129.0E9B75C0]


Network Solutions & Services



<>

Re: [BUGS] PostgreSQL 8.4

2011-08-02 Thread Kevin Grittner 
Glonet NV / Mathieu Aras  wrote:
 
> Can we install PostgreSQL 8.4 on a server 2008? Is it X64 and X32 
> compatible?
 
This is not a bug.  A more appropriate list for this question would
have been pgsql-general.
 
I'm not sure what "server 2008" is, but if it's some form of
Windows, version 8.4 should support X32.  I think you need version
9.0 (or the upcoming 9.1 release) to support Windows X64.
 
-Kevin

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #6140: PostgreSQL Server Service Not Starting

2011-08-02 Thread Craig Ringer 

On 2/08/2011 8:01 PM, Steve Boyd wrote:


"could not open process token:  error code 5"

The service account is in the local administrators group and has full
control over the data directory.


AFAIK PostgreSQL service account should *NOT* be part of the local 
administrators group.


That said, the fact that it fails so ungracefully is definitely a bug 
that bears investigation.



Not sure what has changed.  I even
re-entered the password to confirm that the "logon as a service right" was
granted.


The service account won't have been created as a local admin originally, 
so something has been changed.



  I was going to try to upgrade to 8.3.15 but since pgdumpall won't work if
the service is not running, that's not an option.


If you can't get it working normally, one option you have is to copy the 
datadir to a normal user account (or change its permissions), then run 
an instance of PostgreSQL against it manually under that user account 
using pg_ctl - rather than launching it as a service under the service 
account.


That'll at least get you a running instance to dump.

Given the state of the system, be sure to delete the postgres service 
account after uninstalling 8.3 and before updating.


--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] PostgreSQL 8.4

2011-08-02 Thread Craig Ringer 

On 3/08/2011 12:11 AM, Kevin Grittner wrote:

Glonet NV / Mathieu Aras  wrote:


Can we install PostgreSQL 8.4 on a server 2008? Is it X64 and X32
compatible?


This is not a bug.  A more appropriate list for this question would
have been pgsql-general.

I'm not sure what "server 2008" is, but if it's some form of
Windows, version 8.4 should support X32.  I think you need version
9.0 (or the upcoming 9.1 release) to support Windows X64.

-Kevin


http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Can_I_install_a_32-bit_PostgreSQL_on_64-bit_Windows.3F

A 32-bit Pg will run fine on 64-bit windows.

That said, a native PostgreSQL 9.0 x64 build is available, and I'd 
consider using that on a new Win2k8 install instead.o


As Kevin said, this isn't a bug. Please follow the guidelines on the bug 
report form and use the mailing list instead.


(Hmm, maybe we should link to the Nabble forum gateway for -general on 
the bug report page?)


--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #6141: PG Admin's SQL editor can't handle large files

2011-08-02 Thread michael 

The following bug has been logged online:

Bug reference:  6141
Logged by:  michael
Email address:  mpa...@yahoo.com
PostgreSQL version: 9.0
Operating system:   Windows
Description:PG Admin's SQL editor can't handle large files
Details: 

Hi,

I have a script writing an insert query to file.  The file ends up being
about 82MB. 

Step 1: launch the sql editor from pgAdmin
Step 2:   File>open>myfile.sql 

Shortly thereafter, an error message pops up,
"$path/$to/$my/$file/myfile.sql could not be opened because it contains
characters that could not be interpreted."

This always occurs on pgAdmin version 1.12.3(Apr 14 2011, rev:REL-1_12_3)


The workaround is to copy and paste the whole 80MB.  It works, (the insert
query is correct in every way) but the editor is real, real slow.

I can open smaller size (20MB) insert queries just fine.

It might be an unusual request to open/edit 80MB files. If there's some
interest in tackling it, I can even upload sample data somewhere.

Let me know if you need anything else.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #6141: PG Admin's SQL editor can't handle large files

2011-08-02 Thread pasman pasmański 
Hi.
This is not postgresql bug. Try pgadmin-support list.

Btw psql < script is too complicated?

2011/8/2, michael :
>
> The following bug has been logged online:
>
> Bug reference:  6141
> Logged by:  michael
> Email address:  mpa...@yahoo.com
> PostgreSQL version: 9.0
> Operating system:   Windows
> Description:PG Admin's SQL editor can't handle large files
> Details:
>
> Hi,
>
> I have a script writing an insert query to file.  The file ends up being
> about 82MB.
>
> Step 1: launch the sql editor from pgAdmin
> Step 2:   File>open>myfile.sql
>
> Shortly thereafter, an error message pops up,
> "$path/$to/$my/$file/myfile.sql could not be opened because it contains
> characters that could not be interpreted."
>
> This always occurs on pgAdmin version 1.12.3(Apr 14 2011, rev:REL-1_12_3)
>
>
> The workaround is to copy and paste the whole 80MB.  It works, (the insert
> query is correct in every way) but the editor is real, real slow.
>
> I can open smaller size (20MB) insert queries just fine.
>
> It might be an unusual request to open/edit 80MB files. If there's some
> interest in tackling it, I can even upload sample data somewhere.
>
> Let me know if you need anything else.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>


-- 

pasman

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs