[BUGS] BUG #2146: ECPG, cursros and conditional compilations...

[Chuck Wegrzyn]

The following bug has been logged online:

Bug reference:  2146
Logged by:  Chuck Wegrzyn
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.1.0
Operating system:   Linux
Description:ECPG, cursros and conditional compilations...
Details: 

I don't know if this is known or not, but it was a pretty interesting
"feature" to find.

I started out with a definition of a cursor:

EXEC SQL DECLARE StoreCur CURSOR FOR
  SELECT schemaID, TenantName, Category, ModuleID,
 Argument, EncryptID, RelSpeed
  FROM Schema,Tenant WHERE Schema.TenantID = 
 Tenant.TenantID;

This worked fine. When I decided I wanted to have a different cursor but
keep the old in the source I did the normal C thing...

#if 0
 
#else
 
#endif

To those of you "old hands" you are probably saying: What an idiot that
won't work! How true. When I needed to do was:

// 


I find this rather inconsistent in the way ECPG works.

Even stranger was the error I got from ECPG...

ECPG -o store.c store.ec
store.ec:146: ERROR: cursor StoreCur already defined
store.ec:411: WARNING: cursor `StoreCur´ has been declared but ot opened

make: *** [store.c] Error 3

The warning message looks like garbage insterted after the StoreCur name.

Thanks, Peace and Happy New Year.

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

[BUGS] BUG #2153: atan2(0, 0) ERROR: input is out of range on Solaris

[Norman Young]

The following bug has been logged online:

Bug reference:  2153
Logged by:  Norman Young
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system:   Solaris
Description:atan2(0, 0) ERROR:  input is out of range on Solaris
Details: 

Solaris
===

$ uname -a
SunOS solarishost 5.9 Generic_117171-07 sun4u sparc SUNW,Ultra-60
$ gcc --version
gcc (GCC) 3.3.2
Copyright (C) 2003 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

$ psql -h solarishost -U postgres dbname
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
[snip]
comcube=# SELECT atan2(0, 0);
ERROR:  input is out of range

Linux
=
$ uname -a
Linux linuxhost 2.6.11-1.1369_FC4smp #1 SMP Thu Jun 2 23:08:39 EDT 2005 i686
i686 i386 GNU/Linux

$ rpm -qi postgresql-server-8.0.3-1
Name: postgresql-serverRelocations: (not relocatable)
Version : 8.0.3 Vendor: Red Hat, Inc.
Release : 1 Build Date: Tue 10 May 2005
11:42:55 AM EDT
Install Date: Tue 30 Aug 2005 09:55:42 AM EDT  Build Host:
decompose.build.redhat.com
Group   : Applications/DatabasesSource RPM:
postgresql-8.0.3-1.src.rpm
Size: 9979759  License: BSD
Signature   : DSA/SHA1, Fri 20 May 2005 01:40:20 PM EDT, Key ID
b44269d04f2a6fd2Packager: Red Hat, Inc.

URL : http://www.postgresql.org/
Summary : The programs needed to create and run a PostgreSQL server.

$ psql -h linuxhost -U postgres dbname
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
[snip]
comcube=# SELECT atan2(0, 0);
 atan2
---
 0
(1 row)

Workaround
==
CASE WHEN arg1=0 AND arg2=0 THEN 0 ELSE atan2(ar1, arg2)

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

Re: [BUGS] BUG #2144: Domain NOT NULL constraints ignored in rules

[John Supplee]

Tom Lane wrote:

> Works for me:
> 
> regression=# create domain dint as int not null;
> CREATE DOMAIN
> regression=# create table t1 (f1 dint);
> CREATE TABLE
> regression=# create view v1 as select * from t1;
> CREATE VIEW
> regression=# create rule r1 as on insert to v1 do instead
> regression-# insert into t1 values(new.f1);
> CREATE RULE
> regression=# insert into v1 values(1);
> INSERT 0 1
> regression=# insert into v1 values(null);
> ERROR:  domain dint does not allow null values
> regression=#
> 
> How about a test case?
> 
>   regards, tom lane
> 
>

You need to modify your test case slightly.

test=# create domain dint as int not null;
CREATE DOMAIN
test=# create table t1 (f1 dint, f2 dint);
CREATE TABLE
test=# create view v1 as select * from t1;
CREATE VIEW
test=# create rule r1 as on insert to v1 do instead
test-# insert into t1 values (new.f1, new.f2);
CREATE RULE
test=# insert into v1 values( 1 );
INSERT 0 1
test=# select * from v1;
 f1 | f2
+
  1 |
(1 row)

Notice that f2 has a null value even though the domain constraint should
forbid it.


Now try this:

test=# delete from t1;
DELETE 1
test=# alter table t1 alter column f2 set not null;
ALTER TABLE
test=# insert into v1 values( 1 );
ERROR:  null value in column "f2" violates not-null constraint

Having the constraint on the column correctly forbids the NULL value.  For
now I have tagged all columns with the NOT NULL constraint individually, but
I think this should be fixed.


John Supplee


---(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 #2150: PL/Python function delcared STABLE gets run repeatedly

[Aaron Swartz]

The following bug has been logged online:

Bug reference:  2150
Logged by:  Aaron Swartz
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.1.1
Operating system:   Mac OS X
Description:PL/Python function delcared STABLE gets run repeatedly
Details: 

We have the PL/Python function get_foo(n) that returns an array. If this
function is declared IMMUTABLE, then:

SELECT * FROM tablenamehere WHERE id = any(get_foo(21));

returns quickly. If the function is declared STABLE, however, PostgreSQL
seems to incorrectly run it for every row in the table and it takes forever.

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

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

[BUGS] BUG #2154: conversion problem

[Glauco Carlos Silva]

The following bug has been logged online:

Bug reference:  2154
Logged by:  Glauco Carlos Silva
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system:   Windows XP
Description:conversion problem
Details: 

the result of function (select lower('ÇÁÃÂ')) is ÇÁÃÂ "the expected
result is çáãâ"

and the result of function (select upper('çáãâ')) is çáãâ "the
expected result is ÇÁÃÂ"

PS under the LINUX to

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

[BUGS] BUG #2149: can not enter µM into a varchar field in version 8.1.1

[Abigail Corrigan]

The following bug has been logged online:

Bug reference:  2149
Logged by:  Abigail Corrigan
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.1.1
Operating system:   linux
Description:can not enter µM into a varchar field in version 8.1.1
Details: 

I get an error when trying to insert µM into a varchar field in version
8.1.1 but it was working in version 7.4.  The error leads me to think that
there is a different encoding on the dose_units field in the new database. 
How do we encode the special character "µ" into a varchar field for version
8.1.1?

The error message is as follows:
Invalid UTF-8 byte sequence detected near byte 0xb5

Thanks!

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

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

[BUGS] BUG #2151: ODBC, Large Object, Visual Basic, GetChunk

[Kerim Simsek]

The following bug has been logged online:

Bug reference:  2151
Logged by:  Kerim Simsek
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.1 Windows
Operating system:   Windows 2000 Pro
Description:ODBC, Large Object, Visual Basic, GetChunk
Details: 

Large object Table:

  CREATE TABLE tbl_documents
  (
 iddocument int4 NOT NULL, 
 documentsize int4 NOT NULL DEFAULT 0,
 document lo
  ) 

Visual Basic Code:

  flen = rs!documentsize
  chunk() = rs!Document.GetChunk(flen)

Returns Error:

  Error Number: 3219
  Description: Operation is not allowed in this context.

-- 
NOTES:
  Client OS: Windows 2000
  Postrgresql Server: 8.1 on Windows
  Interface: Visual Basic 6.0
  ODBC: 8.01.01.02 (ANSI)
NO ERRORS ON THE PRODUCTION VERSION:
  Client OS: Windows 2000
  Postrgresql Server: 7.3.4 on Fedora Core
  Interface: Visual Basic 6.0
  ODBC: 8.01.01.02 (ANSI)

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

   http://archives.postgresql.org

[BUGS] BUG #2152: psql crash reproducible

[Andreas Kretschmer]

The following bug has been logged online:

Bug reference:  2152
Logged by:  Andreas Kretschmer
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.1.0
Operating system:   Debian/Linux
Description:psql crash reproducible
Details: 

i wrote a wrong query, and psql crash:

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

test=# select x from (select extract(dow from ('2006/01/01'::date +
(generate_series(0,10)||'days')::interval)::date)) x;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.


I know, the query is wrong, but the client lost the connection. I'm not a
hacker, but i think, this is a bug, in the client or in the server, i don't
know.


Btw.: PG is very great! Thanks all developers for PostgreSQL!

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

[BUGS] BUG #2148: Crashes on simple statement

[Sokolov Yura]

The following bug has been logged online:

Bug reference:  2148
Logged by:  Sokolov Yura
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.0
Operating system:   Slackware Linux 2.6.14.1
Description:Crashes on simple statement
Details: 

select ms from (select 1) ms

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

Re: [BUGS] BUG #2153: atan2(0, 0) ERROR: input is out of range on Solaris

[Tom Lane]

"Norman Young" <[EMAIL PROTECTED]> writes:
> Description:atan2(0, 0) ERROR:  input is out of range on Solaris

Actually, I'd argue that it's the Linux atan2() that's broken.  But
in any case, what you're looking at is a difference in the behavior
of the underlying library functions on the two platforms.  We do not
try to second-guess libm ...

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

Re: [BUGS] BUG #2150: PL/Python function delcared STABLE gets run repeatedly

[Tom Lane]

"Aaron Swartz" <[EMAIL PROTECTED]> writes:
> We have the PL/Python function get_foo(n) that returns an array. If this
> function is declared IMMUTABLE, then:
> SELECT * FROM tablenamehere WHERE id = any(get_foo(21));
> returns quickly. If the function is declared STABLE, however, PostgreSQL
> seems to incorrectly run it for every row in the table and it takes forever.

= ANY isn't indexable in 8.1, so there's no obvious reason why either
version would be fast.  You're going to have to provide more detail if
you want help.  For starters, what does EXPLAIN ANALYZE show in each
case?  What's the schema of the table (\d output will do)?

regards, tom lane

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

   http://archives.postgresql.org

Re: [BUGS] BUG #2152: psql crash reproducible

[Tom Lane]

"Andreas Kretschmer" <[EMAIL PROTECTED]> writes:
> test=# select x from (select extract(dow from ('2006/01/01'::date +
> (generate_series(0,10)||'days')::interval)::date)) x;
> server closed the connection unexpectedly

Works for me as of 8.1 branch tip, so the fix is in either 8.1.1 or
8.1.2.

regards, tom lane

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

Re: [BUGS] BUG #2148: Crashes on simple statement

[Michael Fuhr]

On Fri, Jan 06, 2006 at 10:30:28AM -0600, Jim C. Nasby wrote:
> OS X, Version 8.1.1:
> decibel=# select ms from (select 1) ms;
> server closed the connection unexpectedly

This query doesn't crash for me with the latest CVS.  It might be
the same problem Tom fixed a few weeks ago:

http://archives.postgresql.org/pgsql-bugs/2005-12/msg00128.php
http://archives.postgresql.org/pgsql-committers/2005-12/msg00296.php
http://archives.postgresql.org/pgsql-committers/2005-12/msg00297.php

-- 
Michael Fuhr

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

Re: [BUGS] BUG #2148: Crashes on simple statement

[Tom Lane]

"Sokolov Yura" <[EMAIL PROTECTED]> writes:
> select ms from (select 1) ms

Works for me ... try a more recent 8.0.* release.

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

Re: [BUGS] BUG #2152: psql crash reproducible

[Jaime Casanova]

On 1/6/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Andreas Kretschmer" <[EMAIL PROTECTED]> writes:
> > test=# select x from (select extract(dow from ('2006/01/01'::date +
> > (generate_series(0,10)||'days')::interval)::date)) x;
> > server closed the connection unexpectedly
>
> Works for me as of 8.1 branch tip, so the fix is in either 8.1.1 or
> 8.1.2.
>
>regards, tom lane
>

must be in 8.1.2 because i'm saying the same error in 8.1.1

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

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

Re: [BUGS] BUG #2150: PL/Python function delcared STABLE gets run repeatedly

[Tom Lane]

Aaron Swartz <[EMAIL PROTECTED]> writes:
> The function we're running returns an array, and it is non-trivial to
> compute. We do a simple query with it like "SELECT * FROM
> tablename WHERE id = any(foo(21))").

> When the function is STABLE (or VOLATILE) the function is run
> to generate the array every time. If the function is IMMUTABLE,
> the array is computed only once for this query, as we'd expect,
> and the query is fast.

Oh, you are misunderstanding the point of IMMUTABLE/STABLE.
STABLE essentially gives the planner permission to use the function
in an indexscan qualification.  It does *not* cause any caching of
the function result in other contexts, which is what you seem to be
wishing would happen.

IMMUTABLE/STABLE/VOLATILE are promises from you to the system about
the behavior of the function, not promises from the system about
how it will choose to evaluate the function.

What I'd suggest is recasting the function to return a SETOF result
instead of an array, and then writing

SELECT * FROM tablename WHERE id IN (select * from foo(21))

This should get you a plan that will work reasonably well for you.

regards, tom lane

---(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] [GENERAL] Problems building pg 8.1.1

[Tom Lane]

mordicus <[EMAIL PROTECTED]> writes:
> Le Vendredi 6 Janvier 2006 16:31, Tom Lane a écrit :
>>> ./configure --enable-thread-safety
>>> conftest.c:140:44: error: ./src/tools/thread/thread_test.c: No such file
>>> or directory
>> 
>> Hmm, it works fine for me.  Is that file actually present in your source
>> tree?

> No and that's my fault, I have dowloaded postgresql-base-8.1.1.tar.bz2 in 
> place of postgresql-8.1.1.tar.bz2 and then, tar xjvf  without really 
> looking the file or directory name...

I see.  This is actually a packaging bug then: thread_test.c ought to be
part of the -base split, not the -opt split, because -opt is
specifically stated not to be required to build the base package.

Probably the easiest way to handle that is to move src/tools/thread/
someplace else in the directory tree, because the rest of src/tools/
doesn't seem to belong in -base.

regards, tom lane

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

Re: [BUGS] BUG #2148: Crashes on simple statement

[Jim C. Nasby]

On Thu, Jan 05, 2006 at 04:19:23PM +, Sokolov Yura wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:  2148
> Logged by:  Sokolov Yura
> Email address:  [EMAIL PROTECTED]
> PostgreSQL version: 8.0
> Operating system:   Slackware Linux 2.6.14.1
> Description:Crashes on simple statement
> Details: 
> 
> select ms from (select 1) ms

OS X, Version 8.1.1:
decibel=# select ms from (select 1) ms;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

The backend didn't actually crash, though.
-- 
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 6: explain analyze is your friend

Re: [BUGS] BUG #2150: PL/Python function delcared STABLE gets run repeatedly

[Jim C. Nasby]

On Fri, Jan 06, 2006 at 12:43:06PM -0500, Tom Lane wrote:
> Oh, you are misunderstanding the point of IMMUTABLE/STABLE.
> STABLE essentially gives the planner permission to use the function
> in an indexscan qualification.  It does *not* cause any caching of
> the function result in other contexts, which is what you seem to be
> wishing would happen.

Is caching of results for STABLE/IMMUTABLE functions called with a
constant something that would be reasonable to add? I certainly wish
this happened natively, but I've always hacked around it by

SELECT ... WHERE x = (SELECT foo(21))

so presumably some kind of query transform logic similar to what was
done for min/max might work.

I don't see anything about this on the TODO...
-- 
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 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: [BUGS] BUG #2150: PL/Python function delcared STABLE gets run repeatedly

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Is caching of results for STABLE/IMMUTABLE functions called with a
> constant something that would be reasonable to add?

I tend to think not: the distributed overhead would outweigh the
advantages.  See previous discussions in the archives.

(However, the previous discussions have imagined an explicit cache
that stores function names, arguments, and results.  Your thought
of transforming the call into an InitPlan node might be interesting.
The trick is still to know which functions are expensive enough to
justify the overhead of an InitPlan.)

regards, tom lane

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

   http://archives.postgresql.org

Re: [BUGS] BUG #2150: PL/Python function delcared STABLE gets run repeatedly

[Jim C. Nasby]

On Fri, Jan 06, 2006 at 02:48:59PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Is caching of results for STABLE/IMMUTABLE functions called with a
> > constant something that would be reasonable to add?
> 
> I tend to think not: the distributed overhead would outweigh the
> advantages.  See previous discussions in the archives.
> 
> (However, the previous discussions have imagined an explicit cache
> that stores function names, arguments, and results.  Your thought
> of transforming the call into an InitPlan node might be interesting.
> The trick is still to know which functions are expensive enough to
> justify the overhead of an InitPlan.)
> 

Oh, yeah, actually caching results between queries doesn't make sense; I
think that's pretty much a corner-case that people should be expected to
code up/handle themselves. I'm just looking for only executing the
function once per query.

Yeah, some heuristics to try and tweak between adding a seperate
planning node and just running the function a few times would be nice,
but I suspect that's overkill. I think it would be fine to just assume
that any time you're going to execute a function more than X number of
times (where X should be easy to deduce during query planning), you'll
be better off taking the cost to tweak the plan to only run it once.

How does the overhead of an InitPlan compare to the overhead of calling
a SQL function? Or plpgsql? Or one of the external function languages?
-- 
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 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] BUG #2144: Domain NOT NULL constraints ignored in rules

[Tom Lane]

"John Supplee" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Works for me:

> You need to modify your test case slightly.

OK, got it.  Patch for 8.1 is attached if you need it.  Thanks for the
test case.

regards, tom lane


Index: rewriteManip.c
===
RCS file: /cvsroot/pgsql/src/backend/rewrite/rewriteManip.c,v
retrieving revision 1.92.2.2
diff -c -r1.92.2.2 rewriteManip.c
*** rewriteManip.c  23 Nov 2005 17:21:22 -  1.92.2.2
--- rewriteManip.c  6 Jan 2006 19:41:30 -
***
*** 18,23 
--- 18,24 
  #include "optimizer/clauses.h"
  #include "optimizer/tlist.h"
  #include "parser/parsetree.h"
+ #include "parser/parse_coerce.h"
  #include "parser/parse_relation.h"
  #include "rewrite/rewriteManip.h"
  #include "utils/lsyscache.h"
***
*** 838,844 
else
{
/* Otherwise replace unmatched var with a null */
!   return (Node *) makeNullConst(var->vartype);
}
}
else
--- 839,851 
else
{
/* Otherwise replace unmatched var with a null */
!   /* need coerce_to_domain in case of NOT NULL domain 
constraint */
!   return coerce_to_domain((Node *) 
makeNullConst(var->vartype),
!   
InvalidOid,
!   
var->vartype,
!   
COERCE_IMPLICIT_CAST,
!   false,
!   false);
}
}
else

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