Here are the open items.  Thanks to Jan, the only 'hot' item left is the
ps args issue.  People on non-BSD platforms will see all their backends
called 'postmaster', because argv[0] changes do not reflect in ps arg
displays.

I have asked that at least we get set_proctitle() working for Linux, but
no one wants to do it, and we can't remove the it because we would have
to add the exec() back into backend creation, which is impossible at
this point.

At this point, I am not even sure if Marc will allow a fix so late in
the game.  Perhaps we can put it into a minor 6.4 release.

As long as you don't think we are going to get tons of complaints, I am
not worried about it.

Everything else is minor, so we are ready for 6.4 on November 1.

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


Additions
---------
regression test all platforms

Serious Items
------------
change pg args for platforms that don't support argv changes
	(setproctitle()?, sendmail hack?)

Docs
----
generate html/postscript documentation
(User's Guide, Administrator's Guide, Programmer's Guide) (Thomas)
make sure all changes are documented properly
markup and merge JDBC docs from Peter (Thomas, others??)
merge the release notes into doc/src/sgml/release.sgml (Bruce, Thomas)
generate new text-format INSTALL and README from sgml sources (Thomas)
markup of Jan's PL docs

Minor items
-----------
cnf-ify still can exhaust memory, make SET KSQO more generic
permissions on indexes:  what do they do?  should it be prevented?
allow multiple generic operators in expressions without the use of parentheses
document/trigger/rule so changes to pg_shadow create pg_pwd
large objects orphanage
improve group handling
improve PRIMARY KEY handling
generate postmaster pid file and remove flock/fcntl lock code
add ability to specifiy location of lock/socket files

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 28 Oct 1998 11:16:42 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Open 6.4 items
> >
> > Here are the open items.  Thanks to Jan, the only 'hot' item left is the
> > ps args issue.  People on non-BSD platforms will see all their backends
> > called 'postmaster', because argv[0] changes do not reflect in ps arg
> > displays.
> >
> > I have asked that at least we get set_proctitle() working for Linux, but
> > no one wants to do it, and we can't remove the it because we would have
> > to add the exec() back into backend creation, which is impossible at
> > this point.
> 
>     I'm running Linux 2.1.88 and get
> 
>     15572  p2 S     0:01 /usr/local/pgsql/bin/postmaster -o -F
>     16121  p2 S     0:01 /usr/local/pgsql/bin/postgres localhost twieck twieck idle
> 
>     from ps. So what isn't working?

Whoh, this is a shock.  I thought this trick did not work under Linux. 
If it does, and no one has complained, we can consider the issue closed.

Massimo did a nice job of moving my ps status stuff into pg_status.h,
but it looks like the same code that modifies argv[0].  Perhaps it is
the Linux version you are using.

It give us a cheap 'db monitor' capability, wrapped into the 'ps'
command.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 28 Oct 1998 11:49:59 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Open 6.4 items
>
> Here are the open items.  Thanks to Jan, the only 'hot' item left is the
> ps args issue.  People on non-BSD platforms will see all their backends
> called 'postmaster', because argv[0] changes do not reflect in ps arg
> displays.
>
> I have asked that at least we get set_proctitle() working for Linux, but
> no one wants to do it, and we can't remove the it because we would have
> to add the exec() back into backend creation, which is impossible at
> this point.

    I'm running Linux 2.1.88 and get

    15572  p2 S     0:01 /usr/local/pgsql/bin/postmaster -o -F
    16121  p2 S     0:01 /usr/local/pgsql/bin/postgres localhost twieck twieck idle

    from ps. So what isn't working?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Wed, 28 Oct 1998 17:53:03 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Open 6.4 items
> >
> > Here are the open items.  Thanks to Jan, the only 'hot' item left is the
> > ps args issue.  People on non-BSD platforms will see all their backends
> > called 'postmaster', because argv[0] changes do not reflect in ps arg
> > displays.
> >
> 
> Bruce,
> 
>     I  asked  for  it a while ago but forgot about it. Anyway - I
>     think it is better to have  precreated  gram.c,  y.tab.h  and
>     scan.c  files  in  src/pl/plpgsql/src  too. Otherwise ppl not
>     having bison/flex might have a build problem.
> 
>     The only thing required is to take them out of the 'clean' rm
>     in  Makefile.in  and add the bison/flex created files to CVS.
>     If gram.c, y.tab.h and scan.l  are  present  and  newer  than
>     gram.y and scan.l the Makefile will already skip the steps to
>     create them.

Do they fail for people who have standard BSD yacc?  Too large?  No one
has complained about it, but it may be true.

Done.  Removed from Makefile.in, and added via cvs.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 28 Oct 1998 12:06:23 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Open 6.4 items
>
> Here are the open items.  Thanks to Jan, the only 'hot' item left is the
> ps args issue.  People on non-BSD platforms will see all their backends
> called 'postmaster', because argv[0] changes do not reflect in ps arg
> displays.
>

Bruce,

    I  asked  for  it a while ago but forgot about it. Anyway - I
    think it is better to have  precreated  gram.c,  y.tab.h  and
    scan.c  files  in  src/pl/plpgsql/src  too. Otherwise ppl not
    having bison/flex might have a build problem.

    The only thing required is to take them out of the 'clean' rm
    in  Makefile.in  and add the bison/flex created files to CVS.
    If gram.c, y.tab.h and scan.l  are  present  and  newer  than
    gram.y and scan.l the Makefile will already skip the steps to
    create them.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Wed, 28 Oct 1998 18:06:55 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Open 6.4 items
   >     I'm running Linux 2.1.88 and get
   > 
   >     15572  p2 S     0:01 /usr/local/pgsql/bin/postmaster -o -F
   >     16121  p2 S     0:01 /usr/local/pgsql/bin/postgres localhost twieck twieck idle
   > 
   >     from ps. So what isn't working?

   Whoh, this is a shock.  I thought this trick did not work under Linux. 
   If it does, and no one has complained, we can consider the issue closed.

For whatever it's worth, I see the same status stuff changing under
NetBSD 1.3.2.  Seems to work fine.

Cheers,
Brook

Wed, 28 Oct 1998 10:08:56 -0700 (MST)
Brook Milligan <brook@trillium.NMSU.Edu>
Re: [HACKERS] Open 6.4 items
>     The only thing required is to take them out of the 'clean' rm
>     in  Makefile.in  and add the bison/flex created files to CVS.
>     If gram.c, y.tab.h and scan.l  are  present  and  newer  than
>     gram.y and scan.l the Makefile will already skip the steps to
>     create them.

We should do that for the ecpg pre-processor also.

                  - Tom

Wed, 28 Oct 1998 17:20:33 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Open 6.4 items
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Here are the open items.  Thanks to Jan, the only 'hot' item left is the
> ps args issue.  People on non-BSD platforms will see all their backends
> called 'postmaster', because argv[0] changes do not reflect in ps arg
> displays.

> I have asked that at least we get set_proctitle() working for Linux, but
> no one wants to do it, and we can't remove the it because we would have
> to add the exec() back into backend creation, which is impossible at
> this point.

I think I was one of the louder complainers about the lack of proctitle
information, but even I don't think it's worth holding up the release
for --- and it's certainly not worth taking a strong risk of breakage
at this stage.  Let's put it on the to-do-for-6.4.1 list.

			regards, tom lane

Wed, 28 Oct 1998 12:20:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Open 6.4 items 
jwieck@debis.com (Jan Wieck) writes:
>     I  asked  for  it a while ago but forgot about it. Anyway - I
>     think it is better to have  precreated  gram.c,  y.tab.h  and
>     scan.c  files  in  src/pl/plpgsql/src  too. Otherwise ppl not
>     having bison/flex might have a build problem.

There are quite a few places that need lex/yacc capability; plpgsql
is not creating any new build requirement that did not exist before.
(ecpg and bootparse are two examples I can think of offhand.)
We ship the main gram.c file not to avoid requiring lex/yacc, but
because it is too big for some older yaccs.

It might be nice to eliminate the need for lex/yacc capability,
but post-beta3 is NOT the time to be doing "might be nice" stuff.
Leave it be for now.

			regards, tom lane

PS: BTW, I was able to build all the Postgres yacc files on a rather
ancient HPUX yacc, once I added enough -N switches.  According to my
notes,
	YACC:/usr/bin/yacc
	YFLAGS:-d -Np2000 -Ns3000 -Nm100000 -Nl2000 -Na30000 -Nc10000
worked with a pretty recent fileset.  So I'm not sure we even really
need to distribute the main parser's gram.c.  We could have configure
plug in these switches if it fails to find bison...

Wed, 28 Oct 1998 12:30:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Open 6.4 items 
> > Bruce,
> >
> >     I  asked  for  it a while ago but forgot about it. Anyway - I
> >     think it is better to have  precreated  gram.c,  y.tab.h  and
> >     scan.c  files  in  src/pl/plpgsql/src  too. Otherwise ppl not
> >     having bison/flex might have a build problem.
> >
> >     The only thing required is to take them out of the 'clean' rm
> >     in  Makefile.in  and add the bison/flex created files to CVS.
> >     If gram.c, y.tab.h and scan.l  are  present  and  newer  than
> >     gram.y and scan.l the Makefile will already skip the steps to
> >     create them.
>
> Do they fail for people who have standard BSD yacc?  Too large?  No one
> has complained about it, but it may be true.

    They  shouldn't  be  too  large.  But  they get modified with
    sed(1) since this  is  a  second  independend  scanner/parser
    inside  the backend (after loading). I'm not 100% sure if the
    code  generated   by   ANY   other   lex/yacc   accepts   the
    substitutions  or  if  the  resulting  code  is  really  that
    independet as it should be.

>
> Done.  Removed from Makefile.in, and added via cvs.

    Thanks.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Wed, 28 Oct 1998 18:35:01 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Open 6.4 items
On Wed, 28 Oct 1998, Bruce Momjian wrote:

> Here are the open items.  Thanks to Jan, the only 'hot' item left is the
> ps args issue.  People on non-BSD platforms will see all their backends
> called 'postmaster', because argv[0] changes do not reflect in ps arg
> displays.

	Since there were no problem reports for this, I assumed that it
was an 'asthetic change' more then anything.  Once v6.4 is released, I'll
dive into it with the Linux guys here at the University...

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 28 Oct 1998 15:17:45 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Open 6.4 items
Doesn't work under Solaris, but, then again, neither does sendamil's, so
that isn't a shock :)



On Wed, 28 Oct 1998, Brook Milligan wrote:

>    >     I'm running Linux 2.1.88 and get
>    > 
>    >     15572  p2 S     0:01 /usr/local/pgsql/bin/postmaster -o -F
>    >     16121  p2 S     0:01 /usr/local/pgsql/bin/postgres localhost twieck twieck idle
>    > 
>    >     from ps. So what isn't working?
> 
>    Whoh, this is a shock.  I thought this trick did not work under Linux. 
>    If it does, and no one has complained, we can consider the issue closed.
> 
> For whatever it's worth, I see the same status stuff changing under
> NetBSD 1.3.2.  Seems to work fine.
> 
> Cheers,
> Brook
> 

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 28 Oct 1998 15:22:51 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Open 6.4 items
> jwieck@debis.com (Jan Wieck) writes:
> >     I  asked  for  it a while ago but forgot about it. Anyway - I
> >     think it is better to have  precreated  gram.c,  y.tab.h  and
> >     scan.c  files  in  src/pl/plpgsql/src  too. Otherwise ppl not
> >     having bison/flex might have a build problem.
> 
> There are quite a few places that need lex/yacc capability; plpgsql
> is not creating any new build requirement that did not exist before.
> (ecpg and bootparse are two examples I can think of offhand.)
> We ship the main gram.c file not to avoid requiring lex/yacc, but
> because it is too big for some older yaccs.
> 
> It might be nice to eliminate the need for lex/yacc capability,
> but post-beta3 is NOT the time to be doing "might be nice" stuff.
> Leave it be for now.
> 
> 			regards, tom lane
> 
> PS: BTW, I was able to build all the Postgres yacc files on a rather
> ancient HPUX yacc, once I added enough -N switches.  According to my
> notes,
> 	YACC:/usr/bin/yacc
> 	YFLAGS:-d -Np2000 -Ns3000 -Nm100000 -Nl2000 -Na30000 -Nc10000
> worked with a pretty recent fileset.  So I'm not sure we even really
> need to distribute the main parser's gram.c.  We could have configure
> plug in these switches if it fails to find bison...

Again, too late to play with.  BSD yacc doesn't have switches that
enable larger tables, so you have to have gram.c.  Does plpgsql have
tables too large for BSD yacc?  Can someone test it?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 28 Oct 1998 21:35:26 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Open 6.4 items
> On Wed, 28 Oct 1998, Bruce Momjian wrote:
> 
> > Here are the open items.  Thanks to Jan, the only 'hot' item left is the
> > ps args issue.  People on non-BSD platforms will see all their backends
> > called 'postmaster', because argv[0] changes do not reflect in ps arg
> > displays.
> 
> 	Since there were no problem reports for this, I assumed that it
> was an 'asthetic change' more then anything.  Once v6.4 is released, I'll
> dive into it with the Linux guys here at the University...

Looks like it works for most people.  I will remove it from the list,
and see if someone complains.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 28 Oct 1998 22:36:45 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Open 6.4 items
> Doesn't work under Solaris, but, then again, neither does sendamil's, so
> that isn't a shock :)

Man, if sendmail's doesn't work, that is really broken.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 28 Oct 1998 22:37:24 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Open 6.4 items
On Wed, 28 Oct 1998, Bruce Momjian wrote:

> > Doesn't work under Solaris, but, then again, neither does sendamil's, so
> > that isn't a shock :)
> 
> Man, if sendmail's doesn't work, that is really broken.

	Solaris just doesn't have any mechanisms to work around the
limitation, I guess *shrug*  It really sucks when you want to SIGHUP the
"parent process", which, under FreeBSD at least, is the one that states:
-accepting connections, but under Solaris they are *all* the same :)

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 29 Oct 1998 00:32:49 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Open 6.4 items
Doesn't work under UnixWare, either.

The Hermit Hacker wrote:
> Doesn't work under Solaris, but, then again, neither does sendamil's, so
> that isn't a shock :)
> 
> 
> 
> On Wed, 28 Oct 1998, Brook Milligan wrote:
> 
> >    >     I'm running Linux 2.1.88 and get
> >    > 
> >    >     15572  p2 S     0:01 /usr/local/pgsql/bin/postmaster -o -F
> >    >     16121  p2 S     0:01 /usr/local/pgsql/bin/postgres localhost twieck twieck idle
> >    > 
> >    >     from ps. So what isn't working?
> > 
> >    Whoh, this is a shock.  I thought this trick did not work under Linux. 
> >    If it does, and no one has complained, we can consider the issue closed.
> > 
> > For whatever it's worth, I see the same status stuff changing under
> > NetBSD 1.3.2.  Seems to work fine.
> > 
> > Cheers,
> > Brook
> > 
> 
> Marc G. Fournier                                
> Systems Administrator @ hub.org 
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 
> 
> 

-- 
____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/  |LLIE  | (313) 582-1540    | 



Thu, 29 Oct 1998 01:41:12 -0500
"Billy G. Allie" <Bill.Allie@mug.org>
Re: [HACKERS] Open 6.4 items 
Bruce Momjian wrote:
> > Doesn't work under Solaris, but, then again, neither does sendamil's, so
> > that isn't a shock :)
> 
> Man, if sendmail's doesn't work, that is really broken.

If you want strange, here is the output from the UnixWare 7 ps command with 
some different options:

$ ps -ef | fgrep post
   pgsql 10775     1   TS  80  0 01:34:12 ?  0:00 postmaster -S -i -o -F 
   pgsql 10869 10775   TS  80  0 01:45:30 ?  0:00 postmaster -S -i -o -F 

$ ps -e | fgrep post
 10775   TS  80 ?        0:00 postgres
 10869   TS  80 ?        0:00 postgres

Notice that the PIDs are the same even if the program name is different 
depending on the use of the 'f' option.
-- 
____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/  |LLIE  | (313) 582-1540    | 



Thu, 29 Oct 1998 01:48:26 -0500
"Billy G. Allie" <Bill.Allie@mug.org>
Re: [HACKERS] Open 6.4 items 
> Bruce Momjian wrote:
> > > Doesn't work under Solaris, but, then again, neither does sendamil's, so
> > > that isn't a shock :)
> > 
> > Man, if sendmail's doesn't work, that is really broken.
> 
> If you want strange, here is the output from the UnixWare 7 ps command with 
> some different options:
> 
> $ ps -ef | fgrep post
>    pgsql 10775     1   TS  80  0 01:34:12 ?  0:00 postmaster -S -i -o -F 
>    pgsql 10869 10775   TS  80  0 01:45:30 ?  0:00 postmaster -S -i -o -F 
> 
> $ ps -e | fgrep post
>  10775   TS  80 ?        0:00 postgres
>  10869   TS  80 ?        0:00 postgres
> 
> Notice that the PIDs are the same even if the program name is different 
> depending on the use of the 'f' option.

Wow, that really is strange.  Seems like with the -f, it looks in one
place, as though it knows the child did not exec, so they params must be
the same, while with no -f, it looks in the proper area.  The really
weird thing is that even the postmaster is called postgres.  That is
bizarre.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 29 Oct 1998 02:07:59 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Open 6.4 items
> Bruce Momjian wrote:
> > > Doesn't work under Solaris, but, then again, neither does sendamil's, so
> > > that isn't a shock :)
> > 
> > Man, if sendmail's doesn't work, that is really broken.
> 
> If you want strange, here is the output from the UnixWare 7 ps command with 
> some different options:
> 
> $ ps -ef | fgrep post
>    pgsql 10775     1   TS  80  0 01:34:12 ?  0:00 postmaster -S -i -o -F 
>    pgsql 10869 10775   TS  80  0 01:45:30 ?  0:00 postmaster -S -i -o -F 
> 
> $ ps -e | fgrep post
>  10775   TS  80 ?        0:00 postgres
>  10869   TS  80 ?        0:00 postgres
> 
> Notice that the PIDs are the same even if the program name is different 
> depending on the use of the 'f' option.

That is really bizarre.  The postmaster name is changed to postgres too!


Ah, I now know why things work on Linux.  Massimo did more than just
parameterize my argv code.  In include/utils/ps_status.h, he put in
Linux-specific code to handle the ps args.  Seems under Linux, only
argv[0] can be modified for ps display, for some reason.  He erases the
other args, and puts everything in argv[0].

Under non-linux, he uses the argv[0-4] for various displays, like my
orignal code.  The major issue is that for Linux, he writes directly
into argv[0] memory.  He sets the normal i/o static parameters in the
start of the string, then marks the next position, and writes status
information into there.

In non-Linux, we don't write into argv[0], but change argv[0..4] to
point to our own alloc'ed strings, where the memory sits in our own
address space, not in the proc 'environment' address space.  Getting
'ps' to display process address space strings is a real trick, so it is
not surprising some O/S's don't support it.

He may have done it all in argv[0] because he is expanding the size of
argv[0], and if they are layed out sequentially in environment memory,
the changes would over-write each other.  What I don't understand is why
ps when it goes to look at argv[1], does not see garbage because it may
be pointing into the middle of the argv[0] string.)  Can a Linux user
use ps to display all the params, like 'ps -ef' and see if it is showing
argv[1] as part of the argv[0] string?  You may have to get a wide
display to see it all.

The great news is that it seems it is working to write directly into the
environment address space under Linux, so we don't need set_proctitle,
and it is very fast.

Non-linux platforms are not writing directly to environment memory, but
are using the argv[0..4] trick.

FYI, I have re-exec'ed the postmaster in postmaster.h so it has 4 args.
argc is passed as a value to the program, so you can't change it, and if
you don't do the re-exec, ps only looks at the argv[0], and under
non-linux, that is bad because we use argv[0...4].


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 29 Oct 1998 02:31:20 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Open 6.4 items
On Thu, 29 Oct 1998, The Hermit Hacker wrote:
> 	Solaris just doesn't have any mechanisms to work around the
> limitation, I guess *shrug* It really sucks when you want to SIGHUP
> the "parent process", which, under FreeBSD at least, is the one that
> states: -accepting connections, but under Solaris they are *all* the
> same :)

$ ps -eaf
     UID   PID  PPID  C    STIME TTY      TIME CMD
    root     0     0  0   Oct 12 ?        0:01 sched
    root     1     0  0   Oct 12 ?        0:15 /etc/init -
...

You'll note the 'PPID' field.

3 guesses what that stands for.

-- 
| Matthew N. Dodd  | 78 280Z | 75 164E | 84 245DL | FreeBSD/NetBSD/Sprite/VMS |
| winter@jurai.net |      This Space For Rent     | ix86,sparc,m68k,pmax,vax  |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage?   |


Thu, 29 Oct 1998 10:24:36 -0500 (EST)
"Matthew N. Dodd" <winter@jurai.net>
Re: [HACKERS] Open 6.4 items
>
> On Thu, 29 Oct 1998, The Hermit Hacker wrote:
> >  Solaris just doesn't have any mechanisms to work around the
> > limitation, I guess *shrug* It really sucks when you want to SIGHUP
> > the "parent process", which, under FreeBSD at least, is the one that
> > states: -accepting connections, but under Solaris they are *all* the
> > same :)
>
> $ ps -eaf
>      UID   PID  PPID  C    STIME TTY      TIME CMD
>     root     0     0  0   Oct 12 ?        0:01 sched
>     root     1     0  0   Oct 12 ?        0:15 /etc/init -
> ...
>
> You'll note the 'PPID' field.
>
> 3 guesses what that stands for.
>

    Don't see how this is related to the topic - sorry.

    PPID  is  the  parent process ID. sched has no parent (it's a
    kernel pseudo process) and init has sched as father. For  all
    other  processes  the  PPID  is set to init's PID at the time
    their father dies (you'll see lot's of PPID=1).

    But this all has nothing to do with changing the  CMD  column
    of the ps output from inside a running process.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Thu, 29 Oct 1998 17:31:19 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Open 6.4 items
On Thu, 29 Oct 1998, Jan Wieck wrote:
> > On Thu, 29 Oct 1998, The Hermit Hacker wrote:
> > >  Solaris just doesn't have any mechanisms to work around the
> > > limitation, I guess *shrug* It really sucks when you want to SIGHUP
> > > the "parent process", which, under FreeBSD at least, is the one that
> > > states: -accepting connections, but under Solaris they are *all* the
> > > same :)
> >
> > $ ps -eaf
> >      UID   PID  PPID  C    STIME TTY      TIME CMD
> >     root     0     0  0   Oct 12 ?        0:01 sched
> >     root     1     0  0   Oct 12 ?        0:15 /etc/init -
> > ...
> >
> > You'll note the 'PPID' field.
> >
> > 3 guesses what that stands for.
> >
> 
>     Don't see how this is related to the topic - sorry.

I really have to start explaining things using more words don't I.

>     PPID  is  the  parent process ID. sched has no parent (it's a
>     kernel pseudo process) and init has sched as father. For  all
>     other  processes  the  PPID  is set to init's PID at the time
>     their father dies (you'll see lot's of PPID=1).

Reread the bit of text I quoted.  Read my reply.  How does my reply
address the problem scrappy had?

The bits of ps output I quoted were only serving to demonstrate actual
data produced by the ps command I used.  The actual commands weren't
important, only the PID and PPID fields were.

>     But this all has nothing to do with changing the  CMD  column
>     of the ps output from inside a running process.

No, but changing the CMD column is only eye-candy.  Its not necessary to
be able to tell which processes are children and which is the parent.

-- 
| Matthew N. Dodd  | 78 280Z | 75 164E | 84 245DL | FreeBSD/NetBSD/Sprite/VMS |
| winter@jurai.net |      This Space For Rent     | ix86,sparc,m68k,pmax,vax  |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage?   |


Thu, 29 Oct 1998 11:43:59 -0500 (EST)
"Matthew N. Dodd" <winter@jurai.net>
Re: [HACKERS] Open 6.4 items
> Wow, that really is strange.  Seems like with the -f, it looks in one
> place, as though it knows the child did not exec, so they params must be
> the same, while with no -f, it looks in the proper area.  The really
> weird thing is that even the postmaster is called postgres.  That is
> bizarre.

Yes. With -f, it looks in the process's argv... without, it looks in the
task struct where only the executable name was stored (no path or anything
else). It's a feature to prevent processes that spoof as something else...
(not very effective, IMHO)

Taral


Thu, 29 Oct 1998 11:05:38 -0600
"Taral" <taral@mail.utexas.edu>
RE: [HACKERS] Open 6.4 items
On Thu, 29 Oct 1998, Billy G. Allie wrote:

> Doesn't work under UnixWare, either.
> 

	How about sendmail?  That is "broken" too?


> The Hermit Hacker wrote:
> > Doesn't work under Solaris, but, then again, neither does sendamil's, so
> > that isn't a shock :)
> > 
> > 
> > 
> > On Wed, 28 Oct 1998, Brook Milligan wrote:
> > 
> > >    >     I'm running Linux 2.1.88 and get
> > >    > 
> > >    >     15572  p2 S     0:01 /usr/local/pgsql/bin/postmaster -o -F
> > >    >     16121  p2 S     0:01 /usr/local/pgsql/bin/postgres localhost twieck twieck idle
> > >    > 
> > >    >     from ps. So what isn't working?
> > > 
> > >    Whoh, this is a shock.  I thought this trick did not work under Linux. 
> > >    If it does, and no one has complained, we can consider the issue closed.
> > > 
> > > For whatever it's worth, I see the same status stuff changing under
> > > NetBSD 1.3.2.  Seems to work fine.
> > > 
> > > Cheers,
> > > Brook
> > > 
> > 
> > Marc G. Fournier                                
> > Systems Administrator @ hub.org 
> > primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 
> > 
> > 
> 
> -- 
> ____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
> |  /|      | 7436 Hartwell     | Compuserve: 76337,2061
> |-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
> |/  |LLIE  | (313) 582-1540    | 
> 

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 29 Oct 1998 13:19:50 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Open 6.4 items 
On Thu, 29 Oct 1998, Matthew N. Dodd wrote:

> On Thu, 29 Oct 1998, The Hermit Hacker wrote:
> > 	Solaris just doesn't have any mechanisms to work around the
> > limitation, I guess *shrug* It really sucks when you want to SIGHUP
> > the "parent process", which, under FreeBSD at least, is the one that
> > states: -accepting connections, but under Solaris they are *all* the
> > same :)
> 
> $ ps -eaf
>      UID   PID  PPID  C    STIME TTY      TIME CMD
>     root     0     0  0   Oct 12 ?        0:01 sched
>     root     1     0  0   Oct 12 ?        0:15 /etc/init -
> ...
> 
> You'll note the 'PPID' field.
> 
> 3 guesses what that stands for.

	Okay, now you risk getting on my bad side :)  I know what PPID
stands for...now you tell me which of these processes to SIGHUP:

    root 18942 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
    root 18946 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
    root 18948     1  0 13:22:04 ?        0:00 /usr/local/sbin/sendmail
    root 22213     1  0   Oct 26 ?        1:40 /usr/local/sbin/sendmail

	And ya, I know, the one with the older date...the point is that
you can't really automate this, except to do:

	kill -HUP `ps -aef | grep sendmail | awk '{print $2}'`

	And SIGHUP them all...



Thu, 29 Oct 1998 13:24:06 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Open 6.4 items
On Thu, 29 Oct 1998, Jan Wieck wrote:

> >
> > On Thu, 29 Oct 1998, The Hermit Hacker wrote:
> > >  Solaris just doesn't have any mechanisms to work around the
> > > limitation, I guess *shrug* It really sucks when you want to SIGHUP
> > > the "parent process", which, under FreeBSD at least, is the one that
> > > states: -accepting connections, but under Solaris they are *all* the
> > > same :)
> >
> > $ ps -eaf
> >      UID   PID  PPID  C    STIME TTY      TIME CMD
> >     root     0     0  0   Oct 12 ?        0:01 sched
> >     root     1     0  0   Oct 12 ?        0:15 /etc/init -
> > ...
> >
> > You'll note the 'PPID' field.
> >
> > 3 guesses what that stands for.
> >
> 
>     Don't see how this is related to the topic - sorry.
> 
>     PPID  is  the  parent process ID. sched has no parent (it's a
>     kernel pseudo process) and init has sched as father. For  all
>     other  processes  the  PPID  is set to init's PID at the time
>     their father dies (you'll see lot's of PPID=1).
> 
>     But this all has nothing to do with changing the  CMD  column
>     of the ps output from inside a running process.

In Matthew's defence, I think the point he was trying to bring across was
that you should be able to look at hte PPID.  sendmail, when you start,
tends to list its PPID as '1'...but, as I showed in my last email, that
doesn't appear to be "unique"...

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 29 Oct 1998 13:26:23 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Open 6.4 items
> Bruce Momjian wrote:
> > > Doesn't work under Solaris, but, then again, neither does sendamil's, so
> > > that isn't a shock :)
> > 
> > Man, if sendmail's doesn't work, that is really broken.
> 
> If you want strange, here is the output from the UnixWare 7 ps command with 
> some different options:
> 
> $ ps -ef | fgrep post
>    pgsql 10775     1   TS  80  0 01:34:12 ?  0:00 postmaster -S -i -o -F 
>    pgsql 10869 10775   TS  80  0 01:45:30 ?  0:00 postmaster -S -i -o -F 
> 
> $ ps -e | fgrep post
>  10775   TS  80 ?        0:00 postgres
>  10869   TS  80 ?        0:00 postgres

My guess is that ps without -f looks at the inode that opened the file,
and postmaster and postgres have the same inode because postmaster is
symlinked to postgres.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 29 Oct 1998 12:34:59 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Open 6.4 items
>     root 18942 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
>     root 18946 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
>     root 18948     1  0 13:22:04 ?        0:00 /usr/local/sbin/sendmail
>     root 22213     1  0   Oct 26 ?        1:40 /usr/local/sbin/sendmail

Er... if you have this, you have a problem. This means there are TWO
sendmail daemons running... There should only be ONE with a ppid of 1. It
looks like 18948 is blah.

Taral


Thu, 29 Oct 1998 11:36:02 -0600
"Taral" <taral@mail.utexas.edu>
RE: [HACKERS] Open 6.4 items
On Thu, 29 Oct 1998, The Hermit Hacker wrote:
> 	Okay, now you risk getting on my bad side :)  I know what PPID
> stands for...now you tell me which of these processes to SIGHUP:
> 
>     root 18942 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
>     root 18946 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail

2 open connections.

>     root 18948     1  0 13:22:04 ?        0:00 /usr/local/sbin/sendmail

a local delivery spawned by an open connection that closed and left the
local delivery to finish, thus orphaning it to init (PID1)

>     root 22213     1  0   Oct 26 ?        1:40 /usr/local/sbin/sendmail

The listener.

> 	And ya, I know, the one with the older date...the point is that
> you can't really automate this, except to do:

You're trying to kill the listener?

You do know you can use truss and lsof to figure this out as well right?

Or you could do:

	kill -HUP `head -1 /var/run/sendmail.pid`

Also, check out what the BSD style ps shows under Solaris (/usr/ucb/ps).

-- 
| Matthew N. Dodd  | 78 280Z | 75 164E | 84 245DL | FreeBSD/NetBSD/Sprite/VMS |
| winter@jurai.net |      This Space For Rent     | ix86,sparc,m68k,pmax,vax  |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage?   |


Thu, 29 Oct 1998 12:40:26 -0500 (EST)
"Matthew N. Dodd" <winter@jurai.net>
Re: [HACKERS] Open 6.4 items
On Thu, 29 Oct 1998, Taral wrote:
> >     root 18942 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
> >     root 18946 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
> >     root 18948     1  0 13:22:04 ?        0:00 /usr/local/sbin/sendmail
> >     root 22213     1  0   Oct 26 ?        1:40 /usr/local/sbin/sendmail
> 
> Er... if you have this, you have a problem. This means there are TWO
> sendmail daemons running... There should only be ONE with a ppid of 1. It
> looks like 18948 is blah.

No, it looks like there are 4 sendmail daemons running.

The issue is to determine what each of them are doing.

daemon != listener

-- 
| Matthew N. Dodd  | 78 280Z | 75 164E | 84 245DL | FreeBSD/NetBSD/Sprite/VMS |
| winter@jurai.net |      This Space For Rent     | ix86,sparc,m68k,pmax,vax  |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage?   |


Thu, 29 Oct 1998 12:43:18 -0500 (EST)
"Matthew N. Dodd" <winter@jurai.net>
RE: [HACKERS] Open 6.4 items
> I really have to start explaining things using more words don't I.

Only if you want anyone to bother paying attention to you.

                   - Thomas (who didn't)

Thu, 29 Oct 1998 18:17:59 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Open 6.4 items
On Thu, 29 Oct 1998, Taral wrote:

> >     root 18942 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
> >     root 18946 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
> >     root 18948     1  0 13:22:04 ?        0:00 /usr/local/sbin/sendmail
> >     root 22213     1  0   Oct 26 ?        1:40 /usr/local/sbin/sendmail
> 
> Er... if you have this, you have a problem. This means there are TWO
> sendmail daemons running... There should only be ONE with a ppid of 1. It
> looks like 18948 is blah.

Good guess, but that is normal behaviour, at least under Solaris :)  It
took me several 'ps -aef's to actually find it duplicated, it isn't always
there.

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 29 Oct 1998 22:02:42 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
RE: [HACKERS] Open 6.4 items
On Thu, 29 Oct 1998, Matthew N. Dodd wrote:

> On Thu, 29 Oct 1998, The Hermit Hacker wrote:
> > 	Okay, now you risk getting on my bad side :)  I know what PPID
> > stands for...now you tell me which of these processes to SIGHUP:
> > 
> >     root 18942 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
> >     root 18946 22213  0 13:22:03 ?        0:00 /usr/local/sbin/sendmail
> 
> 2 open connections.
> 
> >     root 18948     1  0 13:22:04 ?        0:00 /usr/local/sbin/sendmail
> 
> a local delivery spawned by an open connection that closed and left the
> local delivery to finish, thus orphaning it to init (PID1)
> 
> >     root 22213     1  0   Oct 26 ?        1:40 /usr/local/sbin/sendmail
> 
> The listener.
> 
> > 	And ya, I know, the one with the older date...the point is that
> > you can't really automate this, except to do:
> 
> You're trying to kill the listener?
> 
> You do know you can use truss and lsof to figure this out as well right?
> 
> Or you could do:
> 
> 	kill -HUP `head -1 /var/run/sendmail.pid`

	Yes, of course, but this was not the point of the whole
thread...:)

> Also, check out what the BSD style ps shows under Solaris (/usr/ucb/ps).

	No apparent difference in the output...wait, you'll take me too
literally on that, eh? :)  Both show the sasme information in the last
field../usr/local/sbin/sendmail -bd -q1h :)


Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 29 Oct 1998 22:06:25 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Open 6.4 items
Hallo Jan,

Do I remember right that your pathes to speed up ORDER BYs (by 
omitting them when not needed) did not make it into 6.4 .

If that is the case, are they available anywhere ?

I really need them (fast) for my new project.

-------------
Hannu Krosing

Fri, 30 Oct 1998 18:38:55 +0200
Hannu Krosing <hannu@trust.ee>
ORDER BY optimisations
> Hallo Jan,
> 
> Do I remember right that your pathes to speed up ORDER BYs (by 
> omitting them when not needed) did not make it into 6.4 .
> 
> If that is the case, are they available anywhere ?
> 
> I really need them (fast) for my new project.

LIMIT will probably be added to 6.4.1.  Queries that use '%text%' can
not use indexes because they are not anchored at the beginning. 
fulltextindex is in contrib for those cases.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 30 Oct 1998 12:19:40 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] ORDER BY optimisations
Bruce Momjian wrote:
> 
> > Hallo Jan,
> >
> > Do I remember right that your pathes to speed up ORDER BYs (by
> > omitting them when not needed) did not make it into 6.4 .
> >
> > If that is the case, are they available anywhere ?
> >
> > I really need them (fast) for my new project.
> 
> LIMIT will probably be added to 6.4.1.  


Actually I don't need LIMIT that much (for me using CURSOR/MOVE/FETCH 
is quite ok). The main benefit from LIMIT seems to be the ability 
of giving the (future) optimiser a hint that we actulally need only 
a small part of the whole query so it may be better to use an index.

What I am after, is the patch that removed the redundant sort node 
when the access is already by an index matching the sort.

> Queries that use '%text%' can not use indexes because they are not 
> anchored at the beginning.
> fulltextindex is in contrib for those cases.

It still seems a bit of a cludge, although a useful one as its usage 
is quite different from the use of other indexes. It also seems to be 
quite liberal with wasting space as it makes both an additional table
_and_ an index for the words it indexes.

In fact I'm currently using my own fulltext indexing scheme outside 
the database. I'm planning to work on including it in the pgsql 
backend, once I figure out how the extending of access methods works.

--------------
Hannu

Sat, 31 Oct 1998 16:53:59 +0200
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] ORDER BY optimisations
>
> Hallo Jan,
>
> Do I remember right that your pathes to speed up ORDER BYs (by
> omitting them when not needed) did not make it into 6.4 .
>
> If that is the case, are they available anywhere ?
>
> I really need them (fast) for my new project.
>
> -------------
> Hannu Krosing
>
>

    Yepp, it didn't made it.

    There  where two different ones out, my and one from - hmmm -
    was it Tatsuo or Hinoue? My one  only  suppresses  the  final
    sort if

    1.  the plan is a Sort->IndexScan,

    2.  there is an ORDER BY clause,

    3.  the  index  choosen by the planner matches ALL attributes
        given in the ORDER BY clause  (extra  indexed  attributes
        not in ORDER BY ignored),

    4.  and finally all sort operators are ASCENDING.

    There  are many debugging printf()'s in the patch and I think
    one of them is still active while the  others  are  commented
    out.  You need to comment out the last one yourself after you
    found out that your queries are what causes  it  to  suppress
    the sorts.

    Anyway, you said you need it fast, so here it is.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #


diff -cr src.orig/backend/optimizer/plan/planner.c src/backend/optimizer/plan/planner.c
*** src.orig/backend/optimizer/plan/planner.c	Wed Oct 14 19:12:36 1998
--- src/backend/optimizer/plan/planner.c	Wed Oct 14 23:17:08 1998
***************
*** 48,53 ****
--- 48,59 ----
  
  #include "executor/executor.h"
  
+ #include "utils/builtins.h"
+ #include "utils/syscache.h"
+ #include "access/genam.h"
+ #include "parser/parse_oper.h"
+ 
+ static bool need_sortplan(List *sortcls, Plan *plan);
  static Plan *make_sortplan(List *tlist, List *sortcls, Plan *plannode);
  extern Plan *make_groupPlan(List **tlist, bool tuplePerGroup,
  			   List *groupClause, Plan *subplan);
***************
*** 281,292 ****
  	}
  	else
  	{
! 		if (parse->sortClause)
  			return make_sortplan(tlist, parse->sortClause, result_plan);
  		else
  			return (Plan *) result_plan;
  	}
  
  }
  
  /*
--- 287,450 ----
  	}
  	else
  	{
! 		if (parse->sortClause && need_sortplan(parse->sortClause, result_plan))
  			return make_sortplan(tlist, parse->sortClause, result_plan);
  		else
  			return (Plan *) result_plan;
  	}
  
+ }
+ 
+ static TargetEntry *
+ get_matching_tle(Plan *plan, Resdom *resdom)
+ {
+ 	List		*i;
+ 	TargetEntry	*tle;
+ 
+ 	foreach (i, plan->targetlist) {
+ 		tle = (TargetEntry *)lfirst(i);
+ 		if (tle->resdom->resno == resdom->resno)
+ 			return tle;
+ 	}
+ 	return NULL;
+ }
+ 
+ static bool
+ need_sortplan(List *sortcls, Plan *plan)
+ {
+ 	Relation	indexRel;
+ 	IndexScan	*indexScan;
+ 	Oid		indexId;
+ 	List		*i;
+ 	HeapTuple	htup;
+ 	Form_pg_index	index_tup;
+ 	int		key_no = 0;
+ 
+ 	/*
+ 	printf("check if need_sortplan ... ");
+ 	*/
+ 
+ 	if (nodeTag(plan) != T_IndexScan) {
+ 		/*
+ 		printf("not an index scan\n");
+ 		*/
+ 		return TRUE;
+ 	}
+ 
+ 	indexScan = (IndexScan *)plan;
+ 
+ 	if (plan->lefttree != NULL) {
+ 		/*
+ 		printf("scan has lefttree\n");
+ 		*/
+ 		return TRUE;
+ 	}
+ 	if (plan->righttree != NULL) {
+ 		/*
+ 		printf("scan has righttree\n");
+ 		*/
+ 		return TRUE;
+ 	}
+ 
+ 	if (length(indexScan->indxid) != 1) {
+ 		/*
+ 		printf("scanning multiple indices\n");
+ 		*/
+ 		return TRUE;
+ 	}
+ 
+ 	if (length(sortcls) > 8) {
+ 		/*
+ 		printf("sort clause too long (>8)\n");
+ 		*/
+ 		return TRUE;
+ 	}
+ 
+ 	indexId = lfirsti(indexScan->indxid);
+ 
+ 	indexRel = index_open(indexId);
+ 	if (strcmp(nameout(&(indexRel->rd_am->amname)), "btree") != 0) {
+ 		/*
+ 		printf("not a btree index\n");
+ 		*/
+ 		heap_close(indexRel);
+ 		return TRUE;
+ 	}
+ 	heap_close(indexRel);
+ 
+ 	htup = SearchSysCacheTuple(INDEXRELID,
+ 			ObjectIdGetDatum(indexId), 0, 0, 0);
+ 	if (!HeapTupleIsValid(htup)) {
+ 		elog(ERROR, "cache lookup for index %d failed", indexId);
+ 	}
+ 	index_tup = (Form_pg_index) GETSTRUCT(htup);
+ 
+ 	foreach (i, sortcls) {
+ 		SortClause	*sortcl;
+ 		Resdom		*resdom;
+ 		TargetEntry	*tle;
+ 		Var		*var;
+ 
+ 		sortcl = (SortClause *) lfirst(i);
+ 
+ 		/*
+ 		printf("\nchecking sortclause %s\n", nodeToString(sortcl));
+ 		*/
+ 
+ 		resdom = sortcl->resdom;
+ 		tle = get_matching_tle(plan, resdom);
+ 		if (tle == NULL) {
+ 			/*
+ 			printf("matching target entry not found\n");
+ 			*/
+ 			return TRUE;
+ 		}
+ 		if (nodeTag(tle->expr) != T_Var) {
+ 			/*
+ 			printf("target entry not a Var\n");
+ 			*/
+ 			return TRUE;
+ 		}
+ 		var = (Var *)(tle->expr);
+ 
+ 		if (var->varno != indexScan->scan.scanrelid) {
+ 			/*
+ 			printf("Var not from scanrelid\n");
+ 			*/
+ 			return TRUE;
+ 		}
+ 
+ 		if (var->varattno != index_tup->indkey[key_no]) {
+ 			/*
+ 			printf("attribute sorted does not match indexed att\n");
+ 			*/
+ 			return TRUE;
+ 		}
+ 
+ 		if (oprid(oper("<", resdom->restype, resdom->restype, FALSE)) != sortcl->opoid) {
+ 			/*
+ 			printf("opoid should be %d - is %d\n",
+ 				oprid(oper("<", resdom->restype, resdom->restype, FALSE)), sortcl->opoid);
+ 			*/
+ 			return TRUE;
+ 		}
+ 
+ 		key_no++;
+ 	}
+ 	if (key_no < 8 && index_tup->indkey[key_no] != 0) {
+ 		/*
+ 		printf("there are more indexed fields! ");
+ 		*/
+ 		return TRUE;
+ 	}
+ 
+ 	printf("SUPPRESSING sort over index scan\n");
+ 
+ 	/*
+ 	printf("scan = %s\n\n", nodeToString(indexScan));
+ 	*/
+ 
+ 	return FALSE;
  }
  
  /*

Sat, 31 Oct 1998 17:33:52 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] ORDER BY optimisations
>     1.  the plan is a Sort->IndexScan,
>
>     2.  there is an ORDER BY clause,
>
>     3.  the  index  choosen by the planner matches ALL attributes
>         given in the ORDER BY clause  (extra  indexed  attributes
>         not in ORDER BY ignored),

    Ooops  -  sorry. Took another look at the patch and saw, that
    it actually does not ignore extra attributes  in  the  index.
    Maybe you want to force sort suppression then too and comment
    out the 'return TRUE' for this case.

    BTW: This or an enhanced  version  (suppressing  more  stupid
    sort  cases)  is  my  first  candidate for the v6.4.1 feature
    patch.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Sat, 31 Oct 1998 18:07:04 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] ORDER BY optimisations