[BUGS] BUG #5643: ecpg program doesn't process WHENEVER NOT DATA FOUND

2010-09-05 Thread Alexander Pyhalov 

The following bug has been logged online:

Bug reference:  5643
Logged by:  Alexander Pyhalov
Email address:  a...@rsu.ru
PostgreSQL version: 8.4.4
Operating system:   FreeBSD 8.1
Description:ecpg program doesn't process WHENEVER NOT DATA FOUND
Details: 

I have test program in embedded  SQL, which works perfectly on 8.4.3 server
and 8.4.0 client, but refuses to work on 8.4.4. 

The following code (lines 58-66):
 EXEC SQL WHENEVER NOT FOUND DO BREAK;
  while(1){
EXEC SQL FETCH NEXT FROM ecpg_tc INTO
:id,:name,:surname,:dept,:phone,:hiredate,:job,:salary;
printf("error code %ld, state %s, message %s, rows %ld, warning %d\n", 
  sqlca.sqlcode, sqlca.sqlstate, sqlca.sqlerrm.sqlerrmc,
sqlca.sqlerrd[2], 
  sqlca.sqlwarn[0]);
printf("%5d %20s %20s %5s %20s %20s %50s
%10d\n",id,name,surname,dept,phone,hiredate,job,salary);  
  }
  EXEC SQL WHENEVER NOT FOUND CONTINUE;

never breaks on NOT DATA FOUND. 
The output from program looks like:
[13112]: ecpg_execute on line 60: query: fetch next from ecpg_tc; with 0
parameter(s) on connection test
[13112]: ecpg_execute on line 60: using PQexec
[13112]: ecpg_execute on line 60: correctly got 0 tuples with 8 fields
[13112]: raising sqlcode 100 on line 60: no data found on line 60
error code 0, state 0, message , rows 0, warning 0
7   Sergey   Spiridonov   MNT 3431  
2002-07-02   System Administrator  
4800
[13112]: ecpg_execute on line 60: query: fetch next from ecpg_tc; with 0
parameter(s) on connection test
[13112]: ecpg_execute on line 60: using PQexec
[13112]: ecpg_execute on line 60: correctly got 0 tuples with 8 fields
[13112]: raising sqlcode 100 on line 60: no data found on line 60
error code 0, state 0, message , rows 0, warning 0
7   Sergey   Spiridonov   MNT 3431  
2002-07-02   System Administrator  
4800
[13112]: ecpg_execute on line 60: query: fetch next from ecpg_tc; with 0
parameter(s) on connection test
[13112]: ecpg_execute on line 60: using PQexec
[13112]: ecpg_execute on line 60: correctly got 0 tuples with 8 fields
[13112]: raising sqlcode 100 on line 60: no data found on line 60
error code 0, state 0, message , rows 0, warning 0
7   Sergey   Spiridonov   MNT 3431  
2002-07-02   System Administrator  
4800


Where we can see, that sqlcode is raising, but in program we receive
sqlcode=0.
ecpg-generated C code for this fragment is:


  /* exec sql whenever not found  break ; */
#line 58 "test_ecpg.pgc"

  while(1){
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "fetch next from
ecpg_tc", ECPGt_EOIT, 
ECPGt_int,&(id),(long)1,(long)1,sizeof(int), 
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
ECPGt_char,(name),(long)255,(long)1,(255)*sizeof(char), 
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
ECPGt_char,(surname),(long)255,(long)1,(255)*sizeof(char), 
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
ECPGt_char,(dept),(long)4,(long)1,(4)*sizeof(char), 
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
ECPGt_char,(phone),(long)20,(long)1,(20)*sizeof(char), 
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
ECPGt_char,(hiredate),(long)20,(long)1,(20)*sizeof(char), 
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
ECPGt_char,(job),(long)255,(long)1,(255)*sizeof(char), 
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
ECPGt_int,&(salary),(long)1,(long)1,sizeof(int), 
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 60 "test_ecpg.pgc"

if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
#line 60 "test_ecpg.pgc"

if (sqlca.sqlcode < 0) sqlprint();}
#line 60 "test_ecpg.pgc"

printf("error code %ld, state %s, message %s, rows %ld, warning %d\n", 
  sqlca.sqlcode, sqlca.sqlstate, sqlca.sqlerrm.sqlerrmc,
sqlca.sqlerrd[2], 
  sqlca.sqlwarn[0]);
printf("%5d %20s %20s %5s %20s %20s %50s
%10d\n",id,name,surname,dept,phone,hiredate,job,salary);  
  }
  /* exec sql whenever not found  continue ; */
#line 66 "test_ecpg.pgc"


  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "close ecpg_tc",
ECPGt_EOIT, ECPGt_EORT);
#line 68 "test_ecpg.pgc"

if (sqlca.sqlcode < 0) sqlprint();}
#line 68 "test_ecpg.pgc"

-- 
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 #5643: ecpg program doesn't process WHENEVER NOT DATA FOUND

2010-09-05 Thread Michael Meskes 
On Sun, Sep 05, 2010 at 08:21:48AM +, Alexander Pyhalov wrote:
> Bug reference:  5643
> Logged by:  Alexander Pyhalov
> Email address:  a...@rsu.ru
> PostgreSQL version: 8.4.4
> Operating system:   FreeBSD 8.1
> Description:ecpg program doesn't process WHENEVER NOT DATA FOUND
> Details: 
> 
> I have test program in embedded  SQL, which works perfectly on 8.4.3 server
> and 8.4.0 client, but refuses to work on 8.4.4. 
> ...

Could you please send me a test case so I can reproduce the problem here? 

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at googlemail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

-- 
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 #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4

2010-09-05 Thread Valentine Gogichashvili 

The following bug has been logged online:

Bug reference:  5644
Logged by:  Valentine Gogichashvili
Email address:  val...@gmail.com
PostgreSQL version: 9.0RC1
Operating system:   linux
Description:Selecting ROW() in variable with 9.0 not compatible with
8.4
Details: 

After migration to 9.0RC1 some stored procedures, working ok in 8.4 does not
work in 9.0.

The problem is that SELECT ROW(a,b,c) INTO var does not work any longer in
9.0.  

For example:

BEGIN;

CREATE TYPE ta AS (a1 integer, a2 text);
CREATE TYPE tb AS (b1 integer, b2 ta);

DO $DOIT$

DECLARE 
  a ta;
  b tb;
BEGIN

  SELECT 1, 'a' INTO a;  -- ok
  RAISE INFO 'a is %', a;

  SELECT ROW(1, 'a') INTO a; -- ok in 8.4 but fails in 9.0
  RAISE INFO 'a is %', a;

  
  SELECT 1, 'a' INTO b.b2;   -- ok
  RAISE INFO 'b is %', b;

END;

$DOIT$;


ROLLBACK;


With best regards, 

-- Valentine Gogichashvili

-- 
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 #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4

2010-09-05 Thread Tom Lane 
"Valentine Gogichashvili"  writes:
> After migration to 9.0RC1 some stored procedures, working ok in 8.4 does not
> work in 9.0.
> The problem is that SELECT ROW(a,b,c) INTO var does not work any longer in
> 9.0.  

It didn't work in 8.4 either.  I tried the attached and got some variant
of

ERROR:  invalid input syntax for integer: "(1,a)"
CONTEXT:  PL/pgSQL function "foo" line 9 at SQL statement

in every release back to 8.1.

regards, tom lane


CREATE TYPE ta AS (a1 integer, a2 text);
CREATE TYPE tb AS (b1 integer, b2 ta);

create or replace function foo() returns void language plpgsql as $$
DECLARE 
  a ta;
  b tb;
BEGIN

  SELECT 1, 'a' INTO a;  -- ok
  RAISE INFO 'a is %', a;

  SELECT ROW(1, 'a') INTO a; -- ok in 8.4 but fails in 9.0
  RAISE INFO 'a is %', a;

  
  SELECT 1, 'a' INTO b.b2;   -- ok
  RAISE INFO 'b is %', b;

END;
$$;

select foo();

-- 
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] issue about information_schema REFERENTIAL_CONSTRAINTS

2010-09-05 Thread Peter Eisentraut 
On fre, 2010-09-03 at 13:53 -0400, Tom Lane wrote:
> Alvaro Herrera  writes:
> > Excerpts from Fabien COELHO's message of vie sep 03 13:39:19 -0400 2010:
> >> I tried to explained that I'm analyzing other people's schemas. I cannot 
> >> ask all other people on the planet to rewrite their schemas, I pick them 
> >> as they are.
> 
> > Maybe you shouldn't be using the information_schema in the first place.
> 
> It would probably be reasonable to put something into chapter 34 of the
> docs pointing out this limitation.  I'm not sure exactly where though.
> Should we invent a "compatibility issues" section in that chapter,
> analogous to the ones we have for individual SQL commands?  Are there
> other issues worth documenting for the information_schema views?

A very similar issue is already documented here:

http://developer.postgresql.org/pgdocs/postgres/infoschema-triggers.html



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