Thread: Get diagnistic (row_count) 7.3 vs. 7.4 changes
Hi,
Just trying 7.4.5 and bumped into a problem.
consider following code:
CREATE OR REPLACE FUNCTION rowcount_test() RETURNS bigint AS '
DECLARE
base_hits bigint;
BEGIN
CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data;
GET DIAGNOSTICS base_hits = ROW_COUNT;
RETURN base_hits;
END;
' LANGUAGE PLPGSQL VOLATILE;
in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp
table
in 7.4.5 GET DIAGNOSTICS returns 0
Is it known behavior change or something else.
Thank you.
Maksim Likharev <MLikharev@micropat.com> writes:
> consider following code:
> CREATE OR REPLACE FUNCTION rowcount_test() RETURNS bigint AS '
> DECLARE
> base_hits bigint;
> BEGIN
> CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data;
> GET DIAGNOSTICS base_hits = ROW_COUNT;
> RETURN base_hits;
> END;
> ' LANGUAGE PLPGSQL VOLATILE;
> in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp
> table
> in 7.4.5 GET DIAGNOSTICS returns 0
Hmm. I'm not sure if that's a bug or an improvement. The command did
not return any rows to plpgsql, so in that sense row_count = 0 is
correct, but I can see why you feel you've lost some capability.
Anyone else have an opinion about this?
regards, tom lane
Tom Lane wrote: > Maksim Likharev <MLikharev@micropat.com> writes: > >> in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into >> a temp table in 7.4.5 GET DIAGNOSTICS returns 0 > > > Hmm. I'm not sure if that's a bug or an improvement. The command > did not return any rows to plpgsql, so in that sense row_count = 0 is > correct, but I can see why you feel you've lost some capability. > > Anyone else have an opinion about this? Well, from the manuals: "The currently available status items are ROW_COUNT, the number of rows processed by the last SQL command sent down to the SQL engine" Nothing there about rows being returned. And by analogy: "A PERFORM statement sets FOUND true if it produces (and discards) a row, false if no row is produced." If you've FOUND rows then presumably ROW_COUNT should be non-zero. So set it if rows aren't returned I'd opine. -- Richard Huxton Archonet Ltd
On my opinion row_count should not be null due to the last operation produced some records, copied to the temp table. Basically it's irrelevant for me whether it fixed or not, I'll have to port this code fast and going to use select count. > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Wednesday, December 01, 2004 5:53 PM > To: MLikharev@micropat.com > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes > > > Maksim Likharev <MLikharev@micropat.com> writes: > > consider following code: > > > CREATE OR REPLACE FUNCTION rowcount_test() RETURNS bigint AS ' > > DECLARE > > base_hits bigint; > > BEGIN > > CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data; > > GET DIAGNOSTICS base_hits = ROW_COUNT; > > > RETURN base_hits; > > END; > > ' LANGUAGE PLPGSQL VOLATILE; > > > in 7.3.3 GET DIAGNOSTICS was returning number of selected > rows into a temp > > table > > in 7.4.5 GET DIAGNOSTICS returns 0 > > Hmm. I'm not sure if that's a bug or an improvement. The command did > not return any rows to plpgsql, so in that sense row_count = 0 is > correct, but I can see why you feel you've lost some capability. > > Anyone else have an opinion about this? > > regards, tom lane
Hello.
Seeking further clarity regarding GET DIAGNOSTICS behavior in 7.4.
As described previously GET DIAGNOSTICS in the following example does not work in 7.4.5:
CREATE OR REPLACE FUNCTION "public"."rowcount_test" () RETURNS bigint AS'
DECLARE
base_hits bigint;
BEGIN
base_hits := 0;
CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data;
GET DIAGNOSTICS base_hits = ROW_COUNT;
RETURN base_hits;
END;
'LANGUAGE 'plpgsql' VOLATILE
Base_hits returns 0 and not 1 while 7.3 returns 1. Without base_hits := 0, null would be returned.
Output:
7.3.3
queriesdbtest=# select * from public.rowcount_test();
rowcount_test
---------------
1
(1 row)
7.4.5
queriesdbtest=# select * from public.rowcount_test();
rowcount_test
---------------
0
(1 row)
What is the preferred/recommended way for obtaining rows worked with via the last SQL statement? Can this be a bug in
7.4.5as the documentation indicates that this should work as described?
Thanks in advance,
Rob
Maksim Likharev <MLikharev@micropat.com> writes:
>> consider following code:
>
>
>> CREATE OR REPLACE FUNCTION rowcount_test() RETURNS bigint AS '
>> DECLARE
>> base_hits bigint;
>> BEGIN
>> CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data;
>> GET DIAGNOSTICS base_hits = ROW_COUNT;
>
>
>> RETURN base_hits;
>> END;
>> ' LANGUAGE PLPGSQL VOLATILE;
>
>
>> in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp
>> table
>> in 7.4.5 GET DIAGNOSTICS returns 0
>
>
Hmm. I'm not sure if that's a bug or an improvement. The command did
not return any rows to plpgsql, so in that sense row_count = 0 is
correct, but I can see why you feel you've lost some capability.
Anyone else have an opinion about this?
regards, tom lane
------------------------------------------------------------------------
Subject:
Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes
From:
"Richard Huxton" <dev@archonet.com>
Date:
Thu, 2 Dec 2004 01:34:37 -0800
To:
"Tom Lane" <tgl@sss.pgh.pa.us>
CC:
<MLikharev@micropat.com>, <pgsql-general@postgresql.org>
Tom Lane wrote:
>> Maksim Likharev <MLikharev@micropat.com> writes:
>>
>
>
>>>> in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into
>>>> a temp table in 7.4.5 GET DIAGNOSTICS returns 0
>>
>>
>>
>>
>> Hmm. I'm not sure if that's a bug or an improvement. The command
>> did not return any rows to plpgsql, so in that sense row_count = 0 is
>> correct, but I can see why you feel you've lost some capability.
>>
>> Anyone else have an opinion about this?
>
>
Well, from the manuals:
"The currently available status items are ROW_COUNT, the number of rows
processed by the last SQL command sent down to the SQL engine"
Nothing there about rows being returned.
And by analogy:
"A PERFORM statement sets FOUND true if it produces (and discards) a
row, false if no row is produced."
If you've FOUND rows then presumably ROW_COUNT should be non-zero. So
set it if rows aren't returned I'd opine.
--
Richard Huxton
Archonet Ltd
Because the GET DIAGNOSTICS is after the CREATE TEMP TABLE command, I think zero is the right value, rather than the number of rows in the SELECT. I can see why it was handy to do it the old way in 7.3 but it seems it was a byproduct of GET DIAGNOSTICS not working properly. I suppose the only clean way to do it now is to do a SELECT COUNT(). --------------------------------------------------------------------------- Rob Long wrote: > Hello. > > Seeking further clarity regarding GET DIAGNOSTICS behavior in 7.4. > > As described previously GET DIAGNOSTICS in the following example does not work in 7.4.5: > > CREATE OR REPLACE FUNCTION "public"."rowcount_test" () RETURNS bigint AS' > DECLARE > base_hits bigint; > BEGIN > > base_hits := 0; > > CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data; > GET DIAGNOSTICS base_hits = ROW_COUNT; > > RETURN base_hits; > END; > 'LANGUAGE 'plpgsql' VOLATILE > > Base_hits returns 0 and not 1 while 7.3 returns 1. Without base_hits := 0, null would be returned. > > Output: > > 7.3.3 > queriesdbtest=# select * from public.rowcount_test(); > rowcount_test > --------------- > 1 > (1 row) > > 7.4.5 > queriesdbtest=# select * from public.rowcount_test(); > rowcount_test > --------------- > 0 > (1 row) > > What is the preferred/recommended way for obtaining rows worked with via the last SQL statement? Can this be a bug in7.4.5 as the documentation indicates that this should work as described? > > Thanks in advance, > Rob > > > > Maksim Likharev <MLikharev@micropat.com> writes: > > >> consider following code: > > > > > > >> CREATE OR REPLACE FUNCTION rowcount_test() RETURNS bigint AS ' > >> DECLARE > >> base_hits bigint; > >> BEGIN > >> CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data; > >> GET DIAGNOSTICS base_hits = ROW_COUNT; > > > > > > >> RETURN base_hits; > >> END; > >> ' LANGUAGE PLPGSQL VOLATILE; > > > > > > >> in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp > >> table > >> in 7.4.5 GET DIAGNOSTICS returns 0 > > > > > > Hmm. I'm not sure if that's a bug or an improvement. The command did > not return any rows to plpgsql, so in that sense row_count = 0 is > correct, but I can see why you feel you've lost some capability. > > Anyone else have an opinion about this? > > regards, tom lane > > > ------------------------------------------------------------------------ > > Subject: > Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes > From: > "Richard Huxton" <dev@archonet.com> > Date: > Thu, 2 Dec 2004 01:34:37 -0800 > > To: > "Tom Lane" <tgl@sss.pgh.pa.us> > CC: > <MLikharev@micropat.com>, <pgsql-general@postgresql.org> > > > Tom Lane wrote: > > >> Maksim Likharev <MLikharev@micropat.com> writes: > >> > > > > > >>>> in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into > >>>> a temp table in 7.4.5 GET DIAGNOSTICS returns 0 > >> > >> > >> > >> > >> Hmm. I'm not sure if that's a bug or an improvement. The command > >> did not return any rows to plpgsql, so in that sense row_count = 0 is > >> correct, but I can see why you feel you've lost some capability. > >> > >> Anyone else have an opinion about this? > > > > > > Well, from the manuals: > "The currently available status items are ROW_COUNT, the number of rows > processed by the last SQL command sent down to the SQL engine" > > Nothing there about rows being returned. > > And by analogy: > "A PERFORM statement sets FOUND true if it produces (and discards) a > row, false if no row is produced." > > If you've FOUND rows then presumably ROW_COUNT should be non-zero. So > set it if rows aren't returned I'd opine. > > -- > Richard Huxton > Archonet Ltd > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073