Обсуждение: Report of some problem under PL/PGSQL 7.4.7 & 8.0.1

Hi everyone,

    I made a tetris under pl/pgsql and i encounter some problem with this
non commun use of pl/pgsql. For each problem, i didn't see information
about them, so my report :

- Array problem (7.4.7 & 8.0.1) :

I got a lot of problem with using array, like i saw under the ML,
multidimensional array are not friendly to use so i used 1 dimension
array but i needed to put data at point (x,y).
The problem is how the array is created and how i can put data into it.
I use this little test function :

------------------------------
CREATE OR REPLACE FUNCTION test_array() RETURNS integer AS '
DECLARE
a integer;
b integer;
c integer;
array varchar[];
BEGIN

array := ''{}'';

a := 1;
WHILE a < 17 LOOP
        b := 1;
        WHILE b < 17 LOOP
c:= a + b * 16;
RAISE NOTICE ''%'', c;
                array[c] := ''&'';
                b := b + 1;
        END LOOP;
        a := a + 1;
END LOOP;


return 0;

END;
' LANGUAGE plpgsql;
----------------------------

Error message :

----------------------------
tetris=# select test_array();
NOTICE:  17
NOTICE:  33
ERROR:  invalid array subscripts
CONTEXT:  PL/pgSQL function "test_array" line 16 at assignment
----------------------------

To correct this error message, i need to make 2 init, the first with
"array := ''{}'';" and a second one by insert data into it with
incremential pointer :

a := 0;
WHILE a <= 999 LOOP
    array[a] := '' '';
END LOOP;

Then my function test_array() work properly.

But i dont understand why PL allow me to assign 2 times data into my
array with "random" pointer and not the 3rd times.

BTW, i dont really understand why i need to make "array := ''{}'';",
some people who test it from pgsqlfr-general ML try without making this
init and get not problem with "random" pointer. But array was <<null>>.


- Problem with table refresh

For my game, i need to detect keystroke, so i made an infinit loop
waiting for key to be press.
I have two case for 7.4.7 & 8.0.1, under 8.0.1 it seems to work
properly.

Under 7.4.7, i need to make :
WHILE a = 0 LOOP
    for rGetkey IN SELECT * FROM getkey LOOP
        a := 1;
    END LOOP;
    select into a count(key) FROM getkey;
END LOOP;

Or i will allways have "a = 0" (maybe i miss something).

But under 8.0.1, its ok with :
WHILE a = 0 LOOP
    select into a count(key) FROM getkey;
END LOOP;


- Some features :
I was surprise to see that i cant put any "'" after --. I thought it was
detect as comment, so all after it on the line will not be compile

Same as table refresh, i didnt understand why the function now() (7.4.7
& 8.0.1) dont refresh it self under a PL function and needed to use
timeofday();

The test function :

------------------------------------------
CREATE OR REPLACE FUNCTION test_now() RETURNS varchar AS '
DECLARE
a timestamp;
b integer;
BEGIN
b := 1;
while b <> 0 LOOP
    select into a now();
    RAISE NOTICE ''%'', a;
END LOOP;
return ''a'';

END;
' LANGUAGE plpgsql;
------------------------------------------

That's all,
Regards,

On Thu, 24 Feb 2005, Froggy / Froggy Corp. wrote:

> tetris=# select test_array();
> NOTICE:  17
> NOTICE:  33
> ERROR:  invalid array subscripts
> CONTEXT:  PL/pgSQL function "test_array" line 16 at assignment
> ----------------------------
>
> To correct this error message, i need to make 2 init, the first with
> "array := ''{}'';" and a second one by insert data into it with
> incremential pointer :
>
> a := 0;
> WHILE a <= 999 LOOP
>     array[a] := '' '';
> END LOOP;
>
> Then my function test_array() work properly.
>
> But i dont understand why PL allow me to assign 2 times data into my
> array with "random" pointer and not the 3rd times.

It appears to allow you to assign once (the notice came before the error
so the 33 is where it fails).  In the first one there's no array bounds
set yet and so the index used generates the initial array bounds, but it
looks like after that you can only extend an index after that by indexing
to adjacent or overlapping areas, not distinct ones.
From the docs: "A stored array value can be enlarged by assigning to an
element adjacent to those already present, or by assigning to a slice that
is adjacent to or overlaps the data already present."

> BTW, i dont really understand why i need to make "array := ''{}'';",
> some people who test it from pgsqlfr-general ML try without making this
> init and get not problem with "random" pointer. But array was <<null>>.

Because array if unset is NULL and PostgreSQL treats indexing into a NULL
array similarly to adding NULL integers or concatenating NULL strings and
thus returns a NULL output.  It's fairly annoying, but having it do
something else would be marginally inconsistent.

> - Problem with table refresh
>
> For my game, i need to detect keystroke, so i made an infinit loop
> waiting for key to be press.
> I have two case for 7.4.7 & 8.0.1, under 8.0.1 it seems to work
> properly.
>
> Under 7.4.7, i need to make :
> WHILE a = 0 LOOP
>     for rGetkey IN SELECT * FROM getkey LOOP
>         a := 1;
>     END LOOP;
>     select into a count(key) FROM getkey;
> END LOOP;
>
> Or i will allways have "a = 0" (maybe i miss something).
>
> But under 8.0.1, its ok with :
> WHILE a = 0 LOOP
>     select into a count(key) FROM getkey;
> END LOOP;

Between 7.4 and 8.0 there were alot of changes into what visibility
statements inside functions have, so it doesn't surprise me that they'd be
different.  I think the 7.4 case was seeing the same snapshot with the
simple loop while 8.0 would for volatile functions get new snapshots
between statements and for static/immutable functions it won't get a new
snapshot even with the for loop (I'm not sure why that works in 7.4).

> - Some features :
> I was surprise to see that i cant put any "'" after --. I thought it was
> detect as comment, so all after it on the line will not be compile

The main parser won't consider the -- as a comment beginning because it's
in a literal string, but the function language's parser won't get it until
after the main parser tries to figure out the function end. I'm not sure
how this could be made better without the main parser having knowledge
about each pl language.

From 8.0 onwards it's probably best to use the dollar-quoting to minimize
random strangeness with single quotes.

> Same as table refresh, i didnt understand why the function now() (7.4.7
> & 8.0.1) dont refresh it self under a PL function and needed to use
> timeofday();

Because now() is defined as transaction start time which doesn't change
within a transaction.