[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [HACKERS] patch: function xmltable
From: Pavel Stehule <pavel.stehule () gmail ! com>
Date: 2017-01-31 21:20:49
Message-ID: CAFj8pRA3GE3RAHqLVxGhXtTZt0JuY8AmX1v2owFuYTwCnFLQzw () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi
2017-01-31 14:57 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:
> Pavel Stehule wrote:
> > 2017-01-24 21:38 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:
>
> > > I think it would be good to have a more complex test case in regress =
--
> > > let's say there is a table with some simple XML values, then we use
> > > XMLFOREST (or maybe one of the table_to_xml functions) to generate a
> > > large document, and then XMLTABLE uses that document as input documen=
t.
> >
> > I have a 16K lines long real XML 6.MB. Probably we would not to append =
it
> > to regress tests.
> >
> > It is really fast - original customer implementation 20min, nested our
> > xpath implementation 10 sec, PLPython xml reader 5 sec, xmltable 400ms
>
> That's great numbers, kudos for the hard work here. That will make for
> a nice headline in pg10 PR materials. But what I was getting at is that
> I would like to exercise a bit more of the expression handling in
> xmltable execution, to make sure it doesn't handle just string literals.
>
done
>
> > I have a plan to create tests based on pg_proc and CTE - if all works,
> then
> > the query must be empty
> >
> > with x as (select proname, proowner, procost, pronargs,
> > array_to_string(proargnames,',') as proargnames,
> > array_to_string(proargtypes,',') as proargtypes from pg_proc), y as
> (select
> > xmlelement(name proc, xmlforest(proname, proowner, procost, pronargs,
> > proargnames, proargtypes)) as proc from x), z as (select xmltable.* from
> y,
> > lateral xmltable('/proc' passing proc columns proname name, proowner oi=
d,
> > procost float, pronargs int, proargnames text, proargtypes text)) select
> *
> > from z except select * from x;
>
> Nice one :-)
>
please see attached patch
* enhanced regress tests
* clean memory context work
Regards
Pavel
>
> --
> =C3=81lvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
[Attachment #5 (text/html)]
<div dir="ltr">Hi<br><div class="gmail_extra"><br><div class="gmail_quote">2017-01-31 \
14:57 GMT+01:00 Alvaro Herrera <span dir="ltr"><<a \
href="mailto:alvherre@2ndquadrant.com" \
target="_blank">alvherre@2ndquadrant.com</a>></span>:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><span class="">Pavel Stehule wrote:<br> > 2017-01-24 21:38 \
GMT+01:00 Alvaro Herrera <<a \
href="mailto:alvherre@2ndquadrant.com">alvherre@2ndquadrant.com</a>>:<br> <br>
</span><span class="">> > I think it would be good to have a more complex test \
case in regress --<br> > > let's say there is a table with some simple XML \
values, then we use<br> > > XMLFOREST (or maybe one of the table_to_xml \
functions) to generate a<br> > > large document, and then XMLTABLE uses that \
document as input document.<br> ><br>
> I have a 16K lines long real XML 6.MB. Probably we would not to append it<br>
> to regress tests.<br>
><br>
> It is really fast - original customer implementation 20min, nested our<br>
> xpath implementation 10 sec, PLPython xml reader 5 sec, xmltable 400ms<br>
<br>
</span>That's great numbers, kudos for the hard work here. That will make \
for<br> a nice headline in pg10 PR materials. But what I was getting at is that<br>
I would like to exercise a bit more of the expression handling in<br>
xmltable execution, to make sure it doesn't handle just string \
literals.<br></blockquote><div><br></div><div>done</div><div> </div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> <span class=""><br>
> I have a plan to create tests based on pg_proc and CTE - if all works, then<br>
> the query must be empty<br>
><br>
> with x as (select proname, proowner, procost, pronargs,<br>
> array_to_string(proargnames,',<wbr>') as proargnames,<br>
> array_to_string(proargtypes,',<wbr>') as proargtypes from pg_proc), y as \
(select<br> > xmlelement(name proc, xmlforest(proname, proowner, procost, \
pronargs,<br> > proargnames, proargtypes)) as proc from x), z as (select \
xmltable.* from y,<br> > lateral xmltable('/proc' passing proc columns \
proname name, proowner oid,<br> > procost float, pronargs int, proargnames text, \
proargtypes text)) select *<br> > from z except select * from x;<br>
<br>
</span>Nice one :-)<br></blockquote><div><br></div><div>please see attached \
patch</div><div><br></div><div>* enhanced regress tests</div><div>* clean memory \
context work</div><div><br></div><div>Regards</div><div><br></div><div>Pavel</div><div> \
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> <div class="HOEnZb"><div class="h5"><br>
--<br>
Álvaro Herrera <a href="https://www.2ndQuadrant.com/" \
rel="noreferrer" target="_blank">https://www.2ndQuadrant.com/</a><br> PostgreSQL \
Development, 24x7 Support, Remote DBA, Training & Services<br> \
</div></div></blockquote></div><br></div></div>
["xmltable-42.patch.gz" (application/x-gzip)]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic