Re: Report of some problem under PL/PGSQL 7.4.7 & 8.0.1

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.