patches for items from TODO list

Sergey Ten wrote:

Hello all,

We would like to contribute to the Postgresql community by implementing
the following items from the TODO list
(http://developer.postgresql.org/todo.php):
. Allow COPY to understand \x as a hex byte . Allow COPY to optionally
include column headings in the first line . Add XML output to COPY

The changes are straightforward and include implementation of the
features as well as modification of the regression tests and documentation.

Before sending a diff file with the changes, we would like to know if
these features have been already implemented.

Please check the web site version. Someone has already implemented
"Allow COPY to optionally include column headings in the first line".

As far as XML, there has been discussion on where that should be done?
In the backend, libpq, or psql. It will need discussion on hackers. I
assume you have read the developer's FAQ too.

-- 
  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

Please check the web site version. Someone has already implemented
"Allow COPY to optionally include column headings in the first line".

As far as XML, there has been discussion on where that should be done?
In the backend, libpq, or psql. It will need discussion on hackers. I
assume you have read the developer's FAQ too.

The other issue is 'what XML format'? Find me a standard data dump XML
DTD and I'll change phpPgAdmin to use it as well.

Otherwise, phpPgAdmin's is quite simple.

Chris

Thank you to all who replied for your time.

We have checked http://momjian.postgresql.org/cgi-bin/pgpatches and
http://momjian.postgresql.org/cgi-bin/pgpatches2, and could not find patches
with words "copy" or "xml" in the subject. Could you please clarify what the
website version is and where it can be found? Is it sources available at
ftp://ftp.postgresql.org?

Best regards,
Jason, Sergey

Sergey Ten wrote:

Thank you to all who replied for your time.

We have checked http://momjian.postgresql.org/cgi-bin/pgpatches and
http://momjian.postgresql.org/cgi-bin/pgpatches2, and could not find
patches
with words "copy" or "xml" in the subject. Could you please clarify what
the
website version is and where it can be found? Is it sources available at
ftp://ftp.postgresql.org?

I think the website version of the TODO is that one on www.postgresql.org
(see http://www.postgresql.org/docs/faqs.TODO.html, from
Developers->Roadmap).
"-Allow COPY to optionally include column headings in the first line" is
already completed (-). That is what Bruce referenced (below).

The patch has already been commited and is therefore not on the patches list
anymore:
http://archives.postgresql.org/pgsql-committers/2005-05/msg00075.php

(I searched for "copy" in the mailing list archives of pgsql-committers.)

Bruce Momjian wrote:

Please check the web site version. Someone has already implemented
"Allow COPY to optionally include column headings in the first line".

Hope that clarifies the situation a little bit.

Best Regards,
Michael Paesold

Hello all,

Thank you to all who replied for suggestions and help. Enclosed please find
code changes for the following items:
- Allow COPY to understand \x as a hex byte, and
- Add XML output to COPY
The changes include implementation of the features as well as modification
of the copy regression test.

After a careful consideration we decided to
- put XML implementation in the backend and
- use XML format described below, with justification of our decision.

The XML schema used by the COPY TO command was designed for ease of use and
to avoid the problem of column names appearing in XML element names.
XML doesn't allow spaces and punctuation in element names but Postgres does
allow these characters in column names; therefore, a direct mapping would be
problematic.

The solution selected places the column names into attribute fields where
any special characters they contain can be properly escaped using XML
entities. An additional attribute is used to distinguish null fields from
empty ones.

The example below is taken from the test suite. It demonstrates some basic
XML escaping in row 2. Row 3 demonstrates the difference between an empty
string (in col2) and a null string (in col3). If a field is null it will
always be empty but a field which is empty may or may not be null.
Always check the value of the 'null' attribute to be sure when a field is
truly null.

<?xml version='1.0'?>
<table>
<row>
<col name='col1' null='n'>Jackson, Sam</col>
<col name='col2' null='n'>\h</col>
</row>
<row>
<col name='col1' null='n'>It is &quot;perfect&quot;.</col>
<col name='col2' null='n'>&#09;</col>
</row>
<row>
<col name='col1' null='n'></col>
<col name='col2' null='y'></col>
</row>
</table>

Please let us know if about any concerns, objections the proposed change may
cause.

Best regards,
Jason Lucas, Sergey Ten
SourceLabs

On Sun, May 15, 2005 at 04:44:57PM +0800, Christopher Kings-Lynne wrote:

Looks like hierarchical queries are now officially stalled :(

Anyone want to take this up for 8.1?

Sergei and Jason,

Feel like taking SQL:1999 WITH RECURSIVE? It would be a giant help to
the PostgreSQL community. :)

http://gppl.moonbone.ru/index.shtml

has part of it, and

http://candle.pha.pa.us/mhonarc/patches2/msg00175.html

has others.

There's also MERGE, which is covered starting on page 47 of
http://wiscorp.com/sql/SQL2003Features.pdf

also pp 839-845 of 5WD-02-Foundation-2003-09.pdf which is part of
this:
http://wiscorp.com/sql/sql_2003_standard.zip

and an overview here:
http://www.varlena.com/varlena/GeneralBits/73.php

Cheers,
D

Hi,

I haven't done any significant progress on that way because of lack of
free time.
Beside this, I'm recently changed my job and now I'm woking for MySQL.
I think it's not possible for me to continue work on PostgreSQL.
Feel free to take the patch and develop it further as long as you
mention me as author of initial version.

Regards, Evgen.

On 5/5/05, Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:

Hi Evgen,

I just keep pinging this patch thread every once in a while to make sure
it doesn't get forgotten :)

How is the syncing with 8.1 CVS coming along?

Chris

Evgen Potemkin wrote:

Hi hackers!

I have done initial implementation of SQL99 WITH clause (attached).
It's now only for v7.3.4 and haven't a lot of checks and restrictions.
It can execute only simple WITH queries like
WITH tree AS (SELECT id,pnt,name,1::int AS level FROM t WHERE id=0
UNION SELECT t.id,t.pnt,t.name,tree.level+1 FROM t JOIN tree ON
tree.id=t.pnt ) SELECT * FROM tree;
It would be great if someone with knowledge of Pg internals can make a
kind of code review and make some advices how to better implement all
this.

Regards, Evgen.

------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Wed, May 11, 2005 at 07:01:50PM -0700, Sergey Ten wrote:

Hello all,

We would like to contribute to the Postgresql community by implementing
the following items from the TODO list
(http://developer.postgresql.org/todo.php):
. Allow COPY to understand \x as a hex byte . Allow COPY to optionally
include column headings in the first line . Add XML output to COPY

The changes are straightforward and include implementation of the
features as well as modification of the regression tests and documentation.

Before sending a diff file with the changes, we would like to know if
these features have been already implemented.

Best regards,
Jason Lucas and Sergey Ten
SourceLabs

Dependable Open Source Systems

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

David--

My boss has given me approval to put up to 8 hours per week of SourceLabs'
time in on the SQL99 hierarchical query implementation. (I'm free, of
course, to supplement this with whatever of my own time I can spare.) I'm
willing to take on the work. What's the next step?

--Jason

On Mon, May 16, 2005 at 03:09:18PM -0700, jason@sourcelabs.com wrote:

David--

My boss has given me approval to put up to 8 hours per week of
SourceLabs' time in on the SQL99 hierarchical query implementation.

That's great! :)

(I'm free, of course, to supplement this with whatever of my own
time I can spare.) I'm willing to take on the work. What's the
next step?

I suppose the first thing would be to look over the patches I
mentioned and the SQL:2003 specification, then put together a
preliminary patch and send it to -hackers.

Cheers,
David.
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

David Fetter <david@fetter.org> writes:

What's the next step?

I suppose the first thing would be to look over the patches I
mentioned and the SQL:2003 specification, then put together a
preliminary patch and send it to -hackers.

You can get useful feedback long before having anything that looks
like a patch --- and I'd encourage you to do so. Send us design
notes, rough data structures, etc.

Quite honestly, hierarchical queries aren't the easiest thing in the
world and wouldn't be my recommendation of the first ... or even the
second ... backend hacking project for a new posthacker to tackle.
If that's where you feel you must start, OK, but try to get as much
feedback as soon as you can, sooner not later.

I seem to recall some discussion of how to do this in the past;
have you trolled the pghackers archives?

regards, tom lane

On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

What's the next step?

I suppose the first thing would be to look over the patches I
mentioned and the SQL:2003 specification, then put together a
preliminary patch and send it to -hackers.

...

I seem to recall some discussion of how to do this in the past;
have you trolled the pghackers archives?

I think that Jasons inspiration for doing it came from the the fact that
there are already now abandoned patches for doing it.

So studying/understanding the current patch, and describing and getting
feedback from pgsql-hackers should be quite a good way of gaining
insight in trickier parts of postgres.

So it will not be jumping at new problem and writing a patch, but rather
trying to get an existing patch into good shape for being accepted in
the backend.

--
Hannu Krosing <hannu@skype.net>

Hannu Krosing <hannu@skype.net> writes:

On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:

I seem to recall some discussion of how to do this in the past;
have you trolled the pghackers archives?

I think that Jasons inspiration for doing it came from the the fact that
there are already now abandoned patches for doing it.

Having looked over the latest patch, my advice would be to ignore it :-(
It's almost completely devoid of documentation, except for comments
that he copied-and-pasted from elsewhere without modification. Wrong
comments are even worse than none.

What I'd like to see before anyone writes a line of code is a text
document explaining how this is going to work: what's the plan tree
structure, what happens at execution time, how much of the SQL99 spec
is going to get implemented. If you don't have that understanding
first, you're going to get buried in irrelevant details.

regards, tom lane

Tom Lane wrote:

Hannu Krosing <hannu@skype.net> writes:

On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:

I seem to recall some discussion of how to do this in the past;
have you trolled the pghackers archives?

I think that Jasons inspiration for doing it came from the the fact that
there are already now abandoned patches for doing it.

Having looked over the latest patch, my advice would be to ignore it :-(
It's almost completely devoid of documentation, except for comments
that he copied-and-pasted from elsewhere without modification. Wrong
comments are even worse than none.

What I'd like to see before anyone writes a line of code is a text
document explaining how this is going to work: what's the plan tree
structure, what happens at execution time, how much of the SQL99 spec
is going to get implemented. If you don't have that understanding
first, you're going to get buried in irrelevant details.

I have updated the developer's FAQ to cover these suggestions on how to
start a patch:

1.4) What do I do after choosing an item to work on?

Send an email to pgsql-hackers with a proposal for what you want to do
(assuming your contribution is not trivial). Working in isolation is not
advisable because others might be working on the same TODO item, or you
might have misunderstood the TODO item. In the email, discuss both the
internal implementation method you plan to use, and any user-visible
changes (new syntax, etc). For complex patches, it is important to get
community feeback on your proposal before starting work. Failure to do
so might mean your patch is rejected.

-- 
  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

On T, 2005-05-17 at 11:22 -0400, Tom Lane wrote:

Hannu Krosing <hannu@skype.net> writes:

On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:

I seem to recall some discussion of how to do this in the past;
have you trolled the pghackers archives?

I think that Jasons inspiration for doing it came from the the fact that
there are already now abandoned patches for doing it.

Having looked over the latest patch, my advice would be to ignore it :-(
It's almost completely devoid of documentation, except for comments
that he copied-and-pasted from elsewhere without modification. Wrong
comments are even worse than none.

ANd even worse - this is from the README on the website:

----8<---------8<---------8<---------8<---------8<---------8<---------8<-----
WHAT'S THIS

This is a patch which allows PgSQL to make hierarchical queries a la
Oracle do.

(c) Evgen Potemkin 2003,2004, < gppl at inbox dot ru >, entirely based
on PostgreSQL (http://www.postgresql.org)
Patch itself distributed under GPL. No warranty of any kind is given,
use it at your own risk.
----8<---------8<---------8<---------8<---------8<---------8<---------8<-----

So the license is also incompatible :(

--
Hannu Krosing <hannu@skype.net>

Dnia 13-05-2005, pią o godzinie 16:01 -0700, Sergey Ten napisał(a):

<?xml version='1.0'?>
<table>
<row>
<col name='col1' null='n'>Jackson, Sam</col>
<col name='col2' null='n'>\h</col>
</row>
<row>
<col name='col1' null='n'>It is &quot;perfect&quot;.</col>
<col name='col2' null='n'>&#09;</col>
</row>
<row>
<col name='col1' null='n'></col>
<col name='col2' null='y'></col>
</row>
</table>

Why didn't you do something to the effect of

<?xml version='1.0'?>
<table>
<cols>
<col name='col1'/>
<col name='col2'/>
</cols>
<row>
<col null='n'>Jackson, Sam</col>
<col null='n'>\h</col>
</row>
<row>
<col null='n'>It is &quot;perfect&quot;.</col>
<col null='n'>&#09;</col>
</row>
<row>
<col null='n'></col>
<col null='y'></col>
</row>
</table>

This avoids repeating the column names in every row, which don't change
over the rows anyway. By reducing redundant information it also makes
structurally invalid XML less likely (whether that is relevant depends
on what people do with the XML data).

Also you could encode the XML output as UTF-8, which would make the
files more readable for humans if the text data is not ASCII.

Markus

--
Markus Bertheau <twanger@bluetwanger.de>

Sergey Ten wrote:

After a careful consideration we decided to
- put XML implementation in the backend

What advantage does putting the XML output mode in the backend provide?

-Neil

On Tue, May 17, 2005 at 11:24:19PM +0300, Hannu Krosing wrote:

On T, 2005-05-17 at 11:22 -0400, Tom Lane wrote:

Hannu Krosing <hannu@skype.net> writes:

On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:

I seem to recall some discussion of how to do this in the past;
have you trolled the pghackers archives?

I think that Jasons inspiration for doing it came from the the fact that
there are already now abandoned patches for doing it.

Having looked over the latest patch, my advice would be to ignore it :-(
It's almost completely devoid of documentation, except for comments
that he copied-and-pasted from elsewhere without modification. Wrong
comments are even worse than none.

ANd even worse - this is from the README on the website:

----8<---------8<---------8<---------8<---------8<---------8<---------8<-----
WHAT'S THIS

This is a patch which allows PgSQL to make hierarchical queries a la
Oracle do.

(c) Evgen Potemkin 2003,2004, < gppl at inbox dot ru >, entirely based
on PostgreSQL (http://www.postgresql.org)
Patch itself distributed under GPL. No warranty of any kind is given,
use it at your own risk.

It's the notes on the CONNECT BY patch, not the WITH patch. The WITH
patch, as far as I can tell, is in the public domain.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

Markus,

Thank you for your reply.
We considered embedding of an XML schema first followed by data. We decided
to stick to our current data format to make sure stateless XML parsers can
process it as well. Would it be better to add an option to the COPY command,
to allow embedding an XML schema, so more advanced XML parsers can take
advantage of it?

Thanks,
Jason, Sergey

Neil,

We think that putting it in the backend will make access from other
components easier.

Thank you,
Sergey

Sergey Ten wrote:

We think that putting it in the backend will make access from other
components easier.

In what way?

It seems to me that this can be done just as easily in a client
application / library, without cluttering the backend with yet another
COPY output format. It would also avoid the need to mandate a single XML
schema -- different clients will likely have different requirements.
Since I am skeptical of the value of this feature in the first place, I
think it would do less damage if implemented outside the backend.

-Neil