From owner-pgsql-hackers@hub.org Thu Sep 23 11:01:06 1999

Received: from renoir.op.net (root@renoir.op.net [209.152.193.4])

by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id LAA16162

for <maillist@candle.pha.pa.us>; Thu, 23 Sep 1999 11:01:04 -0400 (EDT)

Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$ Revision: 1.18 $) with ESMTP id KAA28544 for <maillist@candle.pha.pa.us>; Thu, 23 Sep 1999 10:45:54 -0400 (EDT)

Received: from hub.org (hub.org [216.126.84.1])

by hub.org (8.9.3/8.9.3) with ESMTP id KAA52943;

Thu, 23 Sep 1999 10:20:51 -0400 (EDT)

(envelope-from owner-pgsql-hackers@hub.org)

Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Thu, 23 Sep 1999 10:19:58 +0000 (EDT)

Received: (from majordom@localhost)

by hub.org (8.9.3/8.9.3) id KAA52472

for pgsql-hackers-outgoing; Thu, 23 Sep 1999 10:19:03 -0400 (EDT)

(envelope-from owner-pgsql-hackers@postgreSQL.org)

Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])

by hub.org (8.9.3/8.9.3) with ESMTP id KAA52431

for <pgsql-hackers@postgresql.org>; Thu, 23 Sep 1999 10:18:47 -0400 (EDT)

(envelope-from tgl@sss.pgh.pa.us)

Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1])

by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA13253;

Thu, 23 Sep 1999 10:18:02 -0400 (EDT)

To: wieck@debis.com (Jan Wieck)

cc: pgsql-hackers@postgreSQL.org

Subject: Re: [HACKERS] Progress report: buffer refcount bugs and SQL functions

In-reply-to: Your message of Thu, 23 Sep 1999 03:19:39 +0200 (MET DST)

<m11TxXw-0003kLC@orion.SAPserv.Hamburg.dsh.de>

Date: Thu, 23 Sep 1999 10:18:01 -0400

Message-ID: <13251.938096281@sss.pgh.pa.us>

From: Tom Lane <tgl@sss.pgh.pa.us>

Sender: owner-pgsql-hackers@postgreSQL.org

Precedence: bulk

Status: RO

wieck@debis.com (Jan Wieck) writes:

> Tom Lane wrote:

>> What I am wondering, though, is whether this addition is actually

>> necessary, or is it a bug that the functions aren't run to completion

>> in the first place?

> I've said some time (maybe too long) ago, that SQL functions

> returning tuple sets are broken in general.

Indeed they are. Try this on for size (using the regression database):

SELECT p.name, p.hobbies.equipment.name FROM person p;

SELECT p.hobbies.equipment.name, p.name FROM person p;

You get different result sets!?

The problem in this example is that ExecTargetList returns the isDone

flag from the last targetlist entry, regardless of whether there are

incomplete iterations in previous entries. More generally, the buffer

leak problem that I started with only occurs if some Iter nodes are not

run to completion --- but execQual.c has no mechanism to make sure that

they have all reached completion simultaneously.

What we really need to make functions-returning-sets work properly is

an implementation somewhat like aggregate functions. We need to make

a list of all the Iter nodes present in a targetlist and cycle through

the values returned by each in a methodical fashion (run the rightmost

through its full cycle, then advance the next-to-rightmost one value,

run the rightmost through its cycle again, etc etc). Also there needs

to be an understanding of the hierarchy when an Iter appears in the

arguments of another Iter's function. (You cycle the upper one for

*each* set of arguments created by cycling its sub-Iters.)

I am not particularly interested in working on this feature right now,

since AFAIK it's a Berkeleyism not found in SQL92. What I've done

is to hack ExecTargetList so that it behaves semi-sanely when there's

more than one Iter at the top level of the target list --- it still

doesn't really give the right answer, but at least it will keep

generating tuples until all the Iters are done at the same time.

It happens that that's enough to give correct answers for the examples

shown in the misc regress test. Even when it fails to generate all

the possible combinations, there will be no buffer leaks.

So, I'm going to declare victory and go home ;-). We ought to add a

TODO item along the lines of

* Functions returning sets don't really work right

in hopes that someone will feel like tackling this someday.

regards, tom lane

************

From owner-pgsql-general@hub.org Fri Oct 9 18:22:09 1998

Received: from hub.org (majordom@hub.org [209.47.148.200])

by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id SAA04220

for <maillist@candle.pha.pa.us>; Fri, 9 Oct 1998 18:22:08 -0400 (EDT)

Received: from localhost (majordom@localhost)

by hub.org (8.8.8/8.8.8) with SMTP id SAA26960;

Fri, 9 Oct 1998 18:18:29 -0400 (EDT)

(envelope-from owner-pgsql-general@hub.org)

Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Fri, 09 Oct 1998 18:18:07 +0000 (EDT)

Received: (from majordom@localhost)

by hub.org (8.8.8/8.8.8) id SAA26917

for pgsql-general-outgoing; Fri, 9 Oct 1998 18:18:04 -0400 (EDT)

(envelope-from owner-pgsql-general@postgreSQL.org)

X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-general@postgreSQL.org using -f

Received: from gecko.statsol.com (gecko.statsol.com [198.11.51.133])

by hub.org (8.8.8/8.8.8) with ESMTP id SAA26904

for <pgsql-general@postgresql.org>; Fri, 9 Oct 1998 18:17:46 -0400 (EDT)

(envelope-from statsol@statsol.com)

Received: from gecko (gecko [198.11.51.133])

by gecko.statsol.com (8.9.0/8.9.0) with SMTP id SAA00557

for <pgsql-general@postgresql.org>; Fri, 9 Oct 1998 18:18:00 -0400 (EDT)

Date: Fri, 9 Oct 1998 18:18:00 -0400 (EDT)

From: Steve Doliov <statsol@statsol.com>

X-Sender: statsol@gecko

To: pgsql-general@postgreSQL.org

Subject: Re: [GENERAL] Making NULLs visible.

Message-ID: <Pine.GSO.3.96.981009181716.545B-100000@gecko>

MIME-Version: 1.0

Content-Type: TEXT/PLAIN; charset=US-ASCII

Sender: owner-pgsql-general@postgreSQL.org

Precedence: bulk

Status: RO

On Fri, 9 Oct 1998, Bruce Momjian wrote:

> [Charset iso-8859-1 unsupported, filtering to ASCII...]

> > > Yes, \ always outputs as \\, excepts someone changed it last week, and I

> > > am requesting a reversal. Do you like the \N if it is unique?

> >

> > Well, it's certainly clear, but could be confused with \n (newline). Can we

> > have \0 instead?

>

> Yes, but it is uppercase. \0 looks like an octal number to me, and I

> think we even output octals sometimes, don't we?

>

my first suggestion may have been hare-brained, but why not just make the

specifics of the output user-configurable. So if the user chooses \0, so

be it, if the user chooses \N so be it, if the user likes NULL so be it.

but the option would only have one value per database at any given point

in time. so database x could use \N on tuesday and NULL on wednesday, but

database x could never have two references to the characters(s) used to

represent a null value.

steve

From owner-pgsql-general@hub.org Sun Oct 11 17:31:08 1998

Received: from renoir.op.net (root@renoir.op.net [209.152.193.4])

by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id RAA20043

for <maillist@candle.pha.pa.us>; Sun, 11 Oct 1998 17:31:02 -0400 (EDT)

Received: from hub.org (majordom@hub.org [209.47.148.200]) by renoir.op.net (o1/$ Revision: 1.18 $) with ESMTP id RAA03069 for <maillist@candle.pha.pa.us>; Sun, 11 Oct 1998 17:10:34 -0400 (EDT)

Received: from localhost (majordom@localhost)

by hub.org (8.8.8/8.8.8) with SMTP id QAA10856;

Sun, 11 Oct 1998 16:57:34 -0400 (EDT)

(envelope-from owner-pgsql-general@hub.org)

Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Sun, 11 Oct 1998 16:53:35 +0000 (EDT)

Received: (from majordom@localhost)

by hub.org (8.8.8/8.8.8) id QAA10393

for pgsql-general-outgoing; Sun, 11 Oct 1998 16:53:34 -0400 (EDT)

(envelope-from owner-pgsql-general@postgreSQL.org)

X-Authentication-Warning: hub.org: majordom set sender to owner-pgsql-general@postgreSQL.org using -f

Received: from mail1.panix.com (mail1.panix.com [166.84.0.212])

by hub.org (8.8.8/8.8.8) with ESMTP id QAA10378

for <pgsql-general@postgreSQL.org>; Sun, 11 Oct 1998 16:53:28 -0400 (EDT)

(envelope-from tomg@admin.nrnet.org)

Received: from mailhost.nrnet.org (root@mailhost.nrnet.org [166.84.192.39])

by mail1.panix.com (8.8.8/8.8.8/PanixM1.3) with ESMTP id QAA16311

for <pgsql-general@postgreSQL.org>; Sun, 11 Oct 1998 16:53:24 -0400 (EDT)

Received: from admin.nrnet.org (uucp@localhost)

by mailhost.nrnet.org (8.8.7/8.8.4) with UUCP

id QAA16345 for pgsql-general@postgreSQL.org; Sun, 11 Oct 1998 16:28:47 -0400

Received: from localhost (tomg@localhost)

by admin.nrnet.org (8.8.7/8.8.7) with SMTP id QAA11569

for <pgsql-general@postgreSQL.org>; Sun, 11 Oct 1998 16:28:41 -0400

Date: Sun, 11 Oct 1998 16:28:41 -0400 (EDT)

From: Thomas Good <tomg@admin.nrnet.org>

To: pgsql-general@postgreSQL.org

Subject: Re: [GENERAL] Making NULLs visible.

In-Reply-To: <Pine.GSO.3.96.981009181716.545B-100000@gecko>

Message-ID: <Pine.LNX.3.96.981011161908.11556A-100000@admin.nrnet.org>

MIME-Version: 1.0

Content-Type: TEXT/PLAIN; charset=US-ASCII

Sender: owner-pgsql-general@postgreSQL.org

Precedence: bulk

Status: RO

Watching all this go by...as a guy who has to move alot of data

from legacy dbs to postgres, I've gotten used to \N being a null.

My vote, if I were allowed to cast one, would be to have one null

and that would be the COPY command null. I have no difficulty

distinguishing a null from a newline...

At the pgsql command prompt I would find seeing \N rather reassuring.

I've seen alot of these little guys.

---------- Sisters of Charity Medical Center ----------

Department of Psychiatry

----

Thomas Good <tomg@q8.nrnet.org>

Coordinator, North Richmond C.M.H.C. Information Systems

75 Vanderbilt Ave, Quarters 8 Phone: 718-354-5528

Staten Island, NY 10304 Fax: 718-354-5056