Re: BUG #13601: bit as quoted column in output

The following bug has been logged on the website:

Bug reference: 13601
Logged by: Vicky Soni
Email address: vicky(dot)soni(at)quipment(dot)nl
PostgreSQL version: 9.3.9
Operating system: Windows
Description:

Hi,

I am trying to return a result set using dynamic query from a postgresql
function.

Function runs ok but when returning bit data type in one of the columns from
output table, I get it as "bit" which should be bit only. Now executing same
query outside of the function in normal mode, it returns column with bit
data type only.

Please guide me.

Regards,
Vicky

On Tuesday, September 1, 2015, <vicky(dot)soni(at)quipment(dot)nl> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13601
> Logged by: Vicky Soni
> Email address: vicky(dot)soni(at)quipment(dot)nl <javascript:;>
> PostgreSQL version: 9.3.9
> Operating system: Windows
> Description:
>
> Hi,
>
> I am trying to return a result set using dynamic query from a
> postgresql
> function.
>
> Function runs ok but when returning bit data type in one of the columns
> from
> output table, I get it as "bit" which should be bit only. Now executing
> same
> query outside of the function in normal mode, it returns column with bit
> data type only.
>
> Please guide me.
>
>
Provide examples.

David J.

Hi David,

Please refer following code and attached screenshots.

create or replace function bit_return_testing()
returns table (OutBit bit) as
$BODY$
declare SQL VARCHAR;
Begin
sql:='select cast(1 as bit) ';
raise notice '%',SQL;
RETURN QUERY EXECUTE SQL;

end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

select * from bit_return_testing();

It give me output like this.

[cid:image001(dot)png(at)01D0E5A8(dot)021B62C0]

Now if I call same statement directly, i.e.

select cast(1 as bit) ;

It gives me output like this.

[cid:image002(dot)png(at)01D0E5A8(dot)021B62C0]

Now my question/doubt/concern was why did it throw “bit” to me into first example and not simple bit?

Please advice.

Thanks & Regards,
Vicky Soni
Database Administrator

From: David G. Johnston [mailto:david(dot)g(dot)johnston(at)gmail(dot)com]
Sent: 01 September 2015 22:36
To: Vicky Soni - Quipment India <vicky(dot)soni(at)quipment(dot)nl>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #13601: bit as quoted column in output

On Tuesday, September 1, 2015, <vicky(dot)soni(at)quipment(dot)nl<mailto:vicky(dot)soni(at)quipment(dot)nl>> wrote:
The following bug has been logged on the website:

Bug reference: 13601
Logged by: Vicky Soni
Email address: vicky(dot)soni(at)quipment(dot)nl<javascript:;>
PostgreSQL version: 9.3.9
Operating system: Windows
Description:

Hi,

I am trying to return a result set using dynamic query from a postgresql
function.

Function runs ok but when returning bit data type in one of the columns from
output table, I get it as "bit" which should be bit only. Now executing same
query outside of the function in normal mode, it returns column with bit
data type only.

Please guide me.

Provide examples.

David J.

On Thu, Sep 3, 2015 at 12:21 AM, Vicky Soni - Quipment India
<vicky(dot)soni(at)quipment(dot)nl> wrote:
> Please refer following code and attached screenshots.
>
> create or replace function bit_return_testing()
> returns table (OutBit bit) as
> $BODY$
> declare SQL VARCHAR;
> Begin
> sql:='select cast(1 as bit) ';
> raise notice '%',SQL;
> RETURN QUERY EXECUTE SQL;
> end;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100
> ROWS 1000;
>
> select * from bit_return_testing();
>
> It give me output like this.
>
> [picture of PGAdmin showing a column's type as: "bit"]

In this case, Postgres is losing track of the typmod and giving your
client PQftype(...) = 1560 (the OID for bit) and PQfmod(...) = -1.
PGAdmin is then calling format_type(1560, -1) to find out how to
display that, which gives '"bit"' (bit with double quotes).

That's because format_type is designed to write out type names that
can survive round trips for use by pg_dump, and, somewhat suprisingly,
"bit" with double quotes means bit with typmod -1 whereas bit without
double quotes means bit with typmod 1 AKA bit(1). Gory details here:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/format_type.c

> Now if I call same statement directly, i.e.
>
> select cast(1 as bit) ;
>
> It gives me output like this.
>
> [Picture of PGAdmin showing a column's type as: bit(1)]

In this case PQftype(...) = 1560 and PQfmod(...) = 1, which
format_type renders as 'bit(1)' (without double quotes).

> Now my question/doubt/concern was why did it throw “bit” to me into first example and not simple bit?

Functions don't track the typmods of arguments or return values
(including the out arguments that are used to implement RETURNS
TABLE). You can write them, but they're discarded, so your data type
bit AKA bit(1) was replaced with bit with typmod -1 AKA "bit".

--
Thomas Munro
http://www.enterprisedb.com

Hi Thomas,

In this case, we are not able to retrieve correct datatype of returning function when calling from Application. In our case it is DOTNET and we use NPGSQL.

Is this a bug? Can we expect fix for this or should we live with same?

Please advice.

Thanks & Regards,
Vicky Soni
Database Administrator

-----Original Message-----
From: Thomas Munro [mailto:thomas(dot)munro(at)enterprisedb(dot)com]
Sent: 23 September 2015 08:28
To: Vicky Soni - Quipment India <vicky(dot)soni(at)quipment(dot)nl>
Cc: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #13601: bit as quoted column in output

On Thu, Sep 3, 2015 at 12:21 AM, Vicky Soni - Quipment India <vicky(dot)soni(at)quipment(dot)nl> wrote:
> Please refer following code and attached screenshots.
>
> create or replace function bit_return_testing() returns table (OutBit
> bit) as $BODY$ declare SQL VARCHAR; Begin sql:='select cast(1 as bit)
> '; raise notice '%',SQL; RETURN QUERY EXECUTE SQL; end; $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100
> ROWS 1000;
>
> select * from bit_return_testing();
>
> It give me output like this.
>
> [picture of PGAdmin showing a column's type as: "bit"]

In this case, Postgres is losing track of the typmod and giving your client PQftype(...) = 1560 (the OID for bit) and PQfmod(...) = -1.
PGAdmin is then calling format_type(1560, -1) to find out how to display that, which gives '"bit"' (bit with double quotes).

That's because format_type is designed to write out type names that can survive round trips for use by pg_dump, and, somewhat suprisingly, "bit" with double quotes means bit with typmod -1 whereas bit without double quotes means bit with typmod 1 AKA bit(1). Gory details here:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/format_type.c

> Now if I call same statement directly, i.e.
>
> select cast(1 as bit) ;
>
> It gives me output like this.
>
> [Picture of PGAdmin showing a column's type as: bit(1)]

In this case PQftype(...) = 1560 and PQfmod(...) = 1, which format_type renders as 'bit(1)' (without double quotes).

> Now my question/doubt/concern was why did it throw “bit” to me into first example and not simple bit?

Functions don't track the typmods of arguments or return values (including the out arguments that are used to implement RETURNS TABLE). You can write them, but they're discarded, so your data type bit AKA bit(1) was replaced with bit with typmod -1 AKA "bit".

--
Thomas Munro
http://www.enterprisedb.com

On Wed, Sep 23, 2015 at 6:35 PM, Vicky Soni - Quipment India
<vicky(dot)soni(at)quipment(dot)nl> wrote:
> Hi Thomas,
>
> In this case, we are not able to retrieve correct datatype of returning function when calling from Application. In our case it is DOTNET and we use NPGSQL.
>
> Is this a bug? Can we expect fix for this or should we live with same?

I don't know anything about npgsql or C# sorry, but I assume that it
has the same information available to it. I did notice from a very
quick look at that project's source[1] (if I am looking at the right
thing, which is not a given) that they treat bit(1) specially: they
use a C# bool for bit(1), but they use a BitArray class for any other
size of bit array. At a guess, that might include values in a column
that contains bit strings of size 1 that was described as having type
bit with typmod -1, which means you'd get BitArray of size one
sometimes and bool other times, depending on whether Postgres happens
to provide typmod info. Could that explain what you are seeing?

Otherwise, I would try raising an issue with a minimal reproduction
and description of the expected and actual behaviour over at the
npgsql project and see what they say.

[1] https://github.com/npgsql/npgsql/blob/develop/src/Npgsql/TypeHandlers/BitStringHandler.cs

--
Thomas Munro
http://www.enterprisedb.com