Re: Automatically parsing in-line composite types

[Mitar]

Hi!

On Tue, Oct 29, 2019 at 11:33 AM Tom Lane  wrote:
> [ shrug... ]  In a world where stability of the wire protocol were
> of zero value, maybe we would do that.  In the real world, don't
> hold your breath.

Oh, yes. I would hope this would be possible in backwards compatible
way. I am not too familiar with the wire protocol to know the answer
to that though.

> Clients would also
> have to be prepared to parse and de-escape the data representation,
> which is not trivial in either text or binary cases.

Yes, but currently they cannot be prepared. They simply lack necessary
information. So if they are not prepared, then the state is the same
as it is currently: they get some composite type in its encoded
representation as a value. But if they are prepared, they have
necessary metadata to parse it.

> On the whole I think it's generally better practice to explode your
> composite types into separate fields for transmission to the client.

The issue here is that it is really hard to make a general client for
PostgreSQL. User might want to an arbitrary SQL query. I would like to
be able to parse that automatically, without user having to specify
additional how to parse it, or requiring them to change SQL query, or
showing them encoded representation directly (not very user friendly).

I agree that in simple cases one could just change the SQL query, but
that is not really always possible. For example, aggregating into an
array a related table is very useful because it makes amount of data
transmitted over the wire much smaller (instead of having to repeat
again and again contents of rows of main table).

> Note that the cases where JSON or XML shine are where you don't
> necessarily have a consistent set of fields in different instances
> of the composite values.  Even if we did extend RowDescription to
> support describing composites' sub-fields, it wouldn't be in
> much of a position to deal with that.

Yes, but that case is already handled: you just have a column type
"JSON' (or "JSONB") and it is clear how to automatically parse that.
What I am missing is a way to automatically parse composite types.
Those are generally not completely arbitrary, but are defined by the
query, not by data.

What would be the next step to move this further in some direction?


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

[Mitar]

Hi!

On Tue, Oct 29, 2019 at 9:06 AM Fabio Ugo Venchiarutti
 wrote:
> You can use subqueries and array_agg() to deepen your output tree all
> the way to a stack overflow, a single _to_json() call at the
> top will recursively traverse and convert whatever you feed it.

Yes, what you are describing is exactly the sad state of things: the
only way to meaningfully retrieve inline composite types which are
made when one aggregate things like that, or when you subselect a set
of fields from a table in a sub-query, is that you then convert the
whole thing to JSON and transmit it in that way. Because this is the
only way you can parse things on the client. Because if you leave it
as raw composite type encoding, you cannot really parse that on the
client correctly in all cases without knowing what types are stored
inside those composite types you are getting.

But JSON is not a lossless transport format: it does not support full
floating point spec (no inf, NANs) and for many types of fields it
just converts to string representation of that, which can be
problematic. For example, if you have binary blobs.

So no, JSON is a workaround, but it is sad that we should have to use
it. PostgreSQL seems to be almost there with the support for composite
types and nested query results, only it seems you cannot really parse
it out. I mean, why PostgreSQL even has its own binary format for
results, then it could just transmit everything as JSON. :-) But that
does not really work for many data types.

I think RowDescription should be extended to provide full recursive
metadata about all data types. That would be the best way to do it.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

[Mitar]

Hi!

On Tue, Oct 29, 2019 at 5:23 AM Dave Cramer  wrote:
> Reading the RowDescription is the only way I am aware of.

But that provides only the types for the top-level fields. Not the
inline composite types. If your top-level field is a registered
composite type then yes, it works out if you then go and read from
system tables definitions of those types. But for any other case where
you for example subselect a list of columns from a table in a
sub-query, it does not work out.

I think ideally, with introduction of composite types into PostgreSQL,
RowDescription should have been extended to provide information for
composite types as well, recursively. In that way you would not even
have to go and fetch additional information from other types,
potentially hitting race conditions.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

[Fabio Ugo Venchiarutti]

On 29/10/2019 12:23, Dave Cramer wrote:



On Wed, 23 Oct 2019 at 15:50, Mitar > wrote:


Hi!

Bump my previous question. I find it surprising that it seems this
information is not possible to be reconstructed by the client, when
the server has to have it internally. Is this a new feature request or
am I missing something?

 > I am trying to understand how could I automatically parse an in-line
 > composite type. By in-line composite type I mean a type corresponding
 > to ROW. For example, in the following query:
 >
 > SELECT _id, body, (SELECT array_agg(ROW(comments._id, comments.body))
 > FROM comments WHERE comments.post_id=posts._id) AS comments FROM
posts
 >
 > It looks like I can figure out that "comments" is an array of
records.
 > But then there is no way really to understand how to parse those
 > records? So what are types of fields in the record?
 >
 > I start the parsing process by looking at types returned in
 > RowDescription message and then reading descriptions in pg_type
table.
 >
 > Is there some other way to get full typing information of the
result I
 > am assuming is available to PostreSQL internally?



Reading the RowDescription is the only way I am aware of.


Dave Cramer

da...@postgresintl.com 
www.postgresintl.com 



Perhaps I misunderstood your question, but that sounds like my average 
use-case for the object-relational type system & JSON/JSONB 
functions/types: defining nested structured types as temporary relations 
in my queries and spew out their hierarchical JSON representation - 
often as a single big field (ironically I hate storing JSON in 
relational databases unless I'm storing something really opaque like 
dashboard layouts).



EG:

SELECT
t.relname AS t_name,
array_to_json(ARRAY_AGG(ats)) AS fields_json
FROM
pg_class AS t INNER JOIN (
SELECT
ia.attrelid AS table_id,
ia.attnum AS column_number,
ia.attname AS column_name
FROM
pg_attribute AS ia
) AS ats
ON
(t.relkind = 'r')
AND
(t.relname IN ('pg_type', 'pg_constraint'))
AND
(ats.table_id = t.oid)
GROUP BY
t.relname


You can use subqueries and array_agg() to deepen your output tree all 
the way to a stack overflow, a single _to_json() call at the 
top will recursively traverse and convert whatever you feed it.



In your case you can just emit your composite type as a JSON object or 
array thereof (types and relations are the same thing).






--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--


Notice: 
This email is confidential and may contain copyright material of 
members of the Ocado Group. Opinions and views expressed in this message 
may not necessarily reflect the opinions and views of the members of the 
Ocado Group.


If you are not the intended recipient, please notify us 
immediately and delete all copies of this message. Please note that it is 
your responsibility to scan this message for viruses.


References to the 
"Ocado Group" are to Ocado Group plc (registered in England and Wales with 
number 7098618) and its subsidiary undertakings (as that expression is 
defined in the Companies Act 2006) from time to time. The registered office 
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way, 
Hatfield, Hertfordshire, AL10 9UL.

Re: Automatically parsing in-line composite types

[Dave Cramer]

On Wed, 23 Oct 2019 at 15:50, Mitar  wrote:

> Hi!
>
> Bump my previous question. I find it surprising that it seems this
> information is not possible to be reconstructed by the client, when
> the server has to have it internally. Is this a new feature request or
> am I missing something?
>
> > I am trying to understand how could I automatically parse an in-line
> > composite type. By in-line composite type I mean a type corresponding
> > to ROW. For example, in the following query:
> >
> > SELECT _id, body, (SELECT array_agg(ROW(comments._id, comments.body))
> > FROM comments WHERE comments.post_id=posts._id) AS comments FROM posts
> >
> > It looks like I can figure out that "comments" is an array of records.
> > But then there is no way really to understand how to parse those
> > records? So what are types of fields in the record?
> >
> > I start the parsing process by looking at types returned in
> > RowDescription message and then reading descriptions in pg_type table.
> >
> > Is there some other way to get full typing information of the result I
> > am assuming is available to PostreSQL internally?
>
>
>
Reading the RowDescription is the only way I am aware of.


Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: PostgreSQL - unrecognized win32 error code: 38

[Thomas Munro]

On Tue, Oct 29, 2019 at 9:23 PM ZhenHua Cai  wrote:
> No, it doesn't call any in-core code.

I wondered if this could be coming from the new code in
src/port/pg_p{read,write}.c.  ERROR_HANDLE_EOF is a documented
GetLastError() return value after ReadFile() fails[1], but only for
asynchronous files.  We are using that interface ("overlapped" IO,
their name for asynchronous IO, but the same interface can also do
synchronous IO and happens to support an offset like Unix's pread()),
but we're not opening file handles with FILE_FLAG_OVERLAPPED so we
have a plain old synchronous handle here.

Searching the web for ERROR_HANDLE_EOF leads to a least one
discussion[2] of "mailslots" (like our syslog pipe?) and a couple of
possible underlying NT errors, and following those leads some other
stuff about SMB filesystems and sockets.  Is your database running on
a local or SMB (or other remote) filesystem?  Are you using the
PostgreSQL syslog process?  I don't know anything about Windows at all
but from these breadcrumbs I feel like there has to be a network or
asynchronous communication involved somewhere here, not a plain old
end-of-file on a regular synchronous file operation, which, as Tom
said, we have code paths for that don't look like this (because that
just shows up as 0 bytes read).

[1] 
https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-readfile
[2] 
https://stackoverflow.com/questions/56510727/what-causes-writefile-to-return-error-38-error-handle-eof

Re: PostgreSQL - unrecognized win32 error code: 38

[ZhenHua Cai]

No, it doesn't call any in-core code.
The following is the code in that function

DROP TABLE IF EXISTS "GenericReadList" ;
CREATE TEMP TABLE "GenericReadList"(
"ComputerProfileId" int NOT NULL,
"Trustee" uuid NOT NULL,
"AccessControlType" smallint NULL,
"AceOrder" int NULL
);

INSERT INTO "GenericReadList"

SELECT t1."ComputerProfileId", t1."Trustee", t1."AccessControlType",
t1."AceOrder" FROM

(

SELECT a."ComputerProfileId", s."ADUser" "Trustee", a."AccessControlType",
a."AceOrder" FROM "TmpResult" r

INNER JOIN "TmpTrusteeSid" s ON s."ADUser" = r."Trustee"

INNER JOIN "AccessRights" a ON a."TrusteeSid" = s."Sid" AND
r."ComputerProfileId" = a."ComputerProfileId" AND a."AccessRight" = 4

) t1

On Mon, Oct 28, 2019 at 5:56 PM Michael Paquier  wrote:

> On Mon, Oct 28, 2019 at 09:51:07AM -0700, ZhenHua Cai wrote:
> > The following is the SQL statement of that function.
> >
> > DROP TABLE IF EXISTS "GenericReadList" ;
> > CREATE TEMP TABLE "GenericReadList"(
> > "ComputerProfileId" int NOT NULL,
> > "Trustee" uuid NOT NULL,
> > "AccessControlType" smallint NULL,
> > "AceOrder" int NULL
> > );
>
> The original complain comes from a function called ComputeComputer.
> What does it do and where does it come from?  Does it call any in-core
> code which would cause the failure.  If the function is a SQL
> function, could you provide a self-contained test case?
> --
> Michael
>