Re: [HACKERS] SPI_prepare() doesn't work well ? - Mailing list pgsql-hackers

Hiroshi, I believe I have fixed this problem.  Let me know if it still
doesn't work.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Hello all,

It seems that SPI_prepare() doesn't work well in some cases.

Pawel Pierscionek [pawel@astercity.net] reported about the
following case 1([SQL] drop table in pgsql).
Michael Contzen [mcontzen@dohle.com] reported about the
following case 2(PL/PGSQL bug using aggregates).
You can find it from pgsql-hackers archive.

1. PL/pgSQL can't execute UTILITY commands.
   SPI_prepare() doesn't copy(save) the utilityStmt member of
   Query type nodes,because copyObject() is not implemented
   for nodes of (Create/Destroy etc)Stmt type.

2. Aggregates in PL/pgSQL cause wrong results.

   create table t1 (i int, a int, b int);
   create table t2 (i int, x int, y int);

   insert into t1 values(1,  1,10);
   insert into t1 values(1,  2,10);
   insert into t1 values(2,  3,10);
   insert into t1 values(2,  4,10);

   create function func1()
   returns int
   as '
   declare
     begin
     insert into t2
      select i,
             sum(a) as x,
             sum(b) as y
      from  t1
      group by i;
     return 1;
    end;
   ' language 'plpgsql';

   select func1();

   select * from t2;

   The result must be the following.

   i| x| y
   - -+--+--
   1| 3|20
   2| 7|20
   (2 rows)

   But the result is as follows.

   i| x| y
   - -+--+--
   1|20|20
   2|20|20
   (2 rows)

  The result of x's are overwritten by y's.

   There is a patch for this case at the end of this mail.
   After I applied it,I got a correct result.
   But I'm not sure this patch is right for all aggregate cases.

   SPI_prepare() doesn't copy(save) nodes of Agg type
   correctly.
   The node of Agg type has a member named aggs.
   It's a list including Aggreg type nodes which exist in
   TargetList(i.e Aggreg type nodes are common to aggs
   member list and TargetList).
   AFAIC the common pointer is not copied to the same
   pointer by copyObject() function.
   In my patch I reconstruct aggs member node from
   new(copied) Agg type node.
   Is it proper to use set_agg_tlist_references() function to
   reconstruct aggs member node for Agg type nodes ?

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp

*** backend/nodes/copyfuncs.c.orig    Tue Jan 19 09:07:48 1999
--- backend/nodes/copyfuncs.c    Wed Jan 20 14:42:37 1999
***************
*** 506,512 ****

      CopyPlanFields((Plan *) from, (Plan *) newnode);

!     Node_Copy(from, newnode, aggs);
      Node_Copy(from, newnode, aggstate);

      return newnode;
--- 506,512 ----

      CopyPlanFields((Plan *) from, (Plan *) newnode);

!     newnode->aggs = set_agg_tlist_references(newnode);
      Node_Copy(from, newnode, aggstate);

      return newnode;