Peter,

    I  just  noticed  that  the new psql doesn't handle semicolon
    inside of unmatched parentheses correct any more. This  is  a
    requirement  for defining multi action rules and was properly
    supported by v6.5.* psql.

    The CURRENT version submits the query buffer as soon,  as  it
    encounters  the  first semicolon outside of a string literal,
    and that is wrong according to the definition of CREATE RULE.


Jan

--

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



Sat, 11 Dec 1999 00:27:54 +0100 (MET)
wieck@debis.com (Jan Wieck)
Error in new psql
> Peter,
> 
>     I  just  noticed  that  the new psql doesn't handle semicolon
>     inside of unmatched parentheses correct any more. This  is  a
>     requirement  for defining multi action rules and was properly
>     supported by v6.5.* psql.
> 
>     The CURRENT version submits the query buffer as soon,  as  it
>     encounters  the  first semicolon outside of a string literal,
>     and that is wrong according to the definition of CREATE RULE.

I assume you mean:

	test=> select (;) 
	ERROR:  parser: parse error at or near ")"


-- 
  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, 10 Dec 1999 19:13:53 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Error in new psql
Bruce Momjian wrote:

> > Peter,
> >
> >     I  just  noticed  that  the new psql doesn't handle semicolon
> >     inside of unmatched parentheses correct any more. This  is  a
> >     requirement  for defining multi action rules and was properly
> >     supported by v6.5.* psql.
> >
> >     The CURRENT version submits the query buffer as soon,  as  it
> >     encounters  the  first semicolon outside of a string literal,
> >     and that is wrong according to the definition of CREATE RULE.
>
> I assume you mean:
>
>    test=> select (;)
>    ERROR:  parser: parse error at or near ")"

Kinda,

    actually I meant

        CREATE RULE myrule AS ON DELETE TO mytable DO (
            DELETE FROM myothertab1 WHERE key = old.key;
            DELETE FROM myothertab2 WHERE key = old.key;
        );
        ERROR:  parser: parse error at or near ""

    This is a possible syntax which (IIRC) got released with v6.4
    and  is  subject  to  the  examples  in   the   rule   system
    documentation.   The  parser still accepts it, so breaking it
    due to changes in  psql  is  an  IMHO  unacceptable  backward
    incompatibility.


Jan

--

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



Sat, 11 Dec 1999 01:41:54 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Error in new psql
> >    test=> select (;)
> >    ERROR:  parser: parse error at or near ")"
> 
> Kinda,
> 
>     actually I meant
> 
>         CREATE RULE myrule AS ON DELETE TO mytable DO (
>             DELETE FROM myothertab1 WHERE key = old.key;
>             DELETE FROM myothertab2 WHERE key = old.key;
>         );
>         ERROR:  parser: parse error at or near ""
> 
>     This is a possible syntax which (IIRC) got released with v6.4
>     and  is  subject  to  the  examples  in   the   rule   system
>     documentation.   The  parser still accepts it, so breaking it
>     due to changes in  psql  is  an  IMHO  unacceptable  backward
>     incompatibility.
> 

Yes, certainly this will be fixed.

-- 
  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, 10 Dec 1999 19:57:27 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Error in new psql
> > I assume you mean:
> >
> >    test=> select (;)
> >    ERROR:  parser: parse error at or near ")"
> 
> Kinda,
> 
>     actually I meant
> 
>         CREATE RULE myrule AS ON DELETE TO mytable DO (
>             DELETE FROM myothertab1 WHERE key = old.key;
>             DELETE FROM myothertab2 WHERE key = old.key;
>         );
>         ERROR:  parser: parse error at or near ""
> 
>     This is a possible syntax which (IIRC) got released with v6.4
>     and  is  subject  to  the  examples  in   the   rule   system
>     documentation.   The  parser still accepts it, so breaking it
>     due to changes in  psql  is  an  IMHO  unacceptable  backward
>     incompatibility.

OK, I fixed it.  Just one addition test in an _if_ statement.

-- 
  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, 10 Dec 1999 20:01:08 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Error in new psql
Bruce Momjian wrote:

> >     actually I meant
> >
> >         CREATE RULE myrule AS ON DELETE TO mytable DO (
> >             DELETE FROM myothertab1 WHERE key = old.key;
> >             DELETE FROM myothertab2 WHERE key = old.key;
> >         );
> >         ERROR:  parser: parse error at or near ""
>
> OK, I fixed it.  Just one addition test in an _if_ statement.

    Thank you.

    You  remember,  that  it's not the first time multiple action
    rules have  been  broken?  The  other  one  was  due  to  the
    EXCEPT/INTERCEPT patch.

    I  added  a check to the rules regression test after that, to
    ensure  it  never  happens  again.   Unfortunately,   Peter's
    enforcement  to use old psql for regression prevented it from
    showing up.

    Don't misunderstand this as some whining about it.  It  is  a
    very  important issue. It shows that the changes made to psql
    can cause backward incompatibilities by themself.

    AFAIK, the proposed procedure to activate the new psql was to
    run the regression test with an old psql, if it's O.K. run it
    again with the new one and replace all expected output files.
    THIS  IS  INADEQUATE  according  to  the results seen in this
    case.

    Don't know if anyone would feel comfortable with it,  but  at
    least,  the postmaster log must be checked to show up exactly
    the same too. The only alternative would be  to  check  every
    old/expected  to  new/results manually (what's really a whole
    lot of damned stupid work).


Jan

--

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



Sat, 11 Dec 1999 02:16:25 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Error in new psql
On 1999-12-11, Jan Wieck mentioned:

>     I  just  noticed  that  the new psql doesn't handle semicolon
>     inside of unmatched parentheses correct any more. This  is  a
>     requirement  for defining multi action rules and was properly
>     supported by v6.5.* psql.

Aah, I knew that there must have been a reason for this parentheses
counting. Patch attached. Backslash-escaping semicolons works as well, by
the way.

-- 
Peter Eisentraut                  Sernanders v�g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden
Sat, 11 Dec 1999 03:00:46 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Error in new psql
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> On 1999-12-11, Jan Wieck mentioned:
> 
> >     I  just  noticed  that  the new psql doesn't handle semicolon
> >     inside of unmatched parentheses correct any more. This  is  a
> >     requirement  for defining multi action rules and was properly
> >     supported by v6.5.* psql.
> 
> Aah, I knew that there must have been a reason for this parentheses
> counting. Patch attached. Backslash-escaping semicolons works as well, by
> the way.

This is the same as the patch I did.  Thanks.

-- 
  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, 10 Dec 1999 21:42:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Error in new psql
On 1999-12-11, Jan Wieck mentioned:

>     I  added  a check to the rules regression test after that, to
>     ensure  it  never  happens  again.   Unfortunately,   Peter's
>     enforcement  to use old psql for regression prevented it from
>     showing up.

To be completely honest, I was just waiting to see what this was good
for. As you have seen (or not), it was more or less disabled but still
there.

Regarding the regression tests, before any more of this stuff gets thrown
around, how do you regenerate the output? Easily? Do it now. As far as I'm
concerned, psql is finished. Anything else will be bug-fixing.

I'm planning on some sort of beta somewhere around Feb 1st with release on
Feb 29th (to prove Y2K compliancy). If we don't come up with a name by
then, we can always start naming it after Norse gods.

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Sat, 11 Dec 1999 03:45:14 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Error in new psql
On 1999-12-10, Bruce Momjian mentioned:

> I assume you mean:
> 
> 	test=> select (;) 
> 	ERROR:  parser: parse error at or near ")"

That was actually a different bug, which must have slipped in on the
latest update. Please use the attached patch. This overlaps with the one
sent in a few minutes ago, but I think you'll easily figure out what's
going on. Just a few lines to delete.

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Sat, 11 Dec 1999 03:45:24 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Error in new psql
>     Don't know if anyone would feel comfortable with it,  but  at
>     least,  the postmaster log must be checked to show up exactly
>     the same too. The only alternative would be  to  check  every
>     old/expected  to  new/results manually (what's really a whole
>     lot of damned stupid work).

I've done a whole lot of dsw before, and will get to it sometime
unless someone does it first...

                  - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Sat, 11 Dec 1999 03:13:47 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Error in new psql
Peter Eisentraut wrote:

> On 1999-12-11, Jan Wieck mentioned:
>
> >     I  added  a check to the rules regression test after that, to
> >     ensure  it  never  happens  again.   Unfortunately,   Peter's
> >     enforcement  to use old psql for regression prevented it from
> >     showing up.
>
> To be completely honest, I was just waiting to see what this was good
> for. As you have seen (or not), it was more or less disabled but still
> there.

    Maybe  it  sounded  the  like, but I really did not wanted to
    citicize your work. It was a great job and IMHO  a  big  leap
    forward  in user friendliness of psql. I expect all this tab-
    completion and  help  stuff  to  be  highly  appreceated  and
    honored. Let me be the first to explicitly say CONGRATS.

    What  I  just  wanted  to  point  out is, that such a little,
    subtle change in psql's input preprocessing could distort  an
    existing  feature.   In  this  case, it's totally clear to me
    that is was  only  disabled  and  still  there.  But  I  only
    stumbled  over  it  because  I tried to create a multi action
    rule by hand to evaluate some comment  I  was  writing  on  a
    list.   Without  that, the proposed procedure (I outlined) to
    update  expected  output  would  have  broken   the   "rules"
    regression test and stamped the broken results into expected.
    So  it  probably  wouldn't  have  been  noticed  until  after
    release.

    And  who  can  guarantee that this kind of flaw cannot happen
    anywhere else?  There are many, very  old  regression  tests.
    Some  of them go back to the roots, Postgres 4.2, and I'm not
    sure anyone ever looked at the expected  results  lately,  if
    they  are  really  what SHOULD be expected. The tenk data for
    example is something where even I don't  know  where  it  was
    coming from, and I already joined the Postgres community with
    release 4.2 back in 1994.

    All  this  IMHO  isn't  really  subject  to   your   personal
    responsibility.   The  interface  of  our  interactive  shell
    needed the now happened polishing for some  time.  Instead  I
    wanted  the backend developers to handle this major change in
    psql, which is a core utility of the regression suite, not as
    lax as past changes to it might have been.  That's all.


Jan

--

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



Sat, 11 Dec 1999 04:17:36 +0100 (MET)
wieck@debis.com (Jan Wieck)
psql & regression (was: Error in new psql)
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> On 1999-12-10, Bruce Momjian mentioned:
> 
> > I assume you mean:
> > 
> > 	test=> select (;) 
> > 	ERROR:  parser: parse error at or near ")"
> 
> That was actually a different bug, which must have slipped in on the
> latest update. Please use the attached patch. This overlaps with the one
> sent in a few minutes ago, but I think you'll easily figure out what's
> going on. Just a few lines to delete.

I don't see any patch attached to this message.

-- 
  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, 10 Dec 1999 22:37:08 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Error in new psql
>     And  who  can  guarantee that this kind of flaw cannot happen
>     anywhere else?  There are many, very  old  regression  tests.
>     Some  of them go back to the roots, Postgres 4.2, and I'm not
>     sure anyone ever looked at the expected  results  lately,  if
>     they  are  really  what SHOULD be expected. The tenk data for
>     example is something where even I don't  know  where  it  was
>     coming from, and I already joined the Postgres community with
>     release 4.2 back in 1994.

Thomas is the regression man, and has checked the output to see that
it was expected in the past.  I assume he will regenerate it soon.

A good point is that he can use the old psql to see any changes/breakage
in the backend code, but can _not_ use the new psql to check because the
output is different.  That is a good point, and I think the one Jan was
making.


-- 
  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, 10 Dec 1999 22:40:52 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: psql & regression (was: Error in new psql)
Bruce Momjian wrote:

> >     And  who  can  guarantee that this kind of flaw cannot happen
> >     anywhere else?  There are many, very  old  regression  tests.
> >     Some  of them go back to the roots, Postgres 4.2, and I'm not
> >     sure anyone ever looked at the expected  results  lately,  if
> >     they  are  really  what SHOULD be expected. The tenk data for
> >     example is something where even I don't  know  where  it  was
> >     coming from, and I already joined the Postgres community with
> >     release 4.2 back in 1994.
>
> Thomas is the regression man, and has checked the output to see that
> it was expected in the past.  I assume he will regenerate it soon.

    Oh  yeah,  I've  seen his response with great pleasure. I did
    not  knew  that  there's  really  someone  taking  care   for
    breakage->expected glitches.

> A good point is that he can use the old psql to see any changes/breakage
> in the backend code, but can _not_ use the new psql to check because the
> output is different.  That is a good point, and I think the one Jan was
> making.

    Yes. The verification, if the new expected output is correct,
    needs one or more eyes (and AFAIK Thomas has good ones - he's
    one of a fistful who notice mistakes in my statements even if
    they are between the lines :-)).


Jan

--

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



Sat, 11 Dec 1999 04:58:09 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: psql & regression (was: Error in new psql)
Posted this a few days ago on pgsql-general and deja, with no response, so
hoping hackers might help...

   Anyone know what this error is or how to prevent it?  Seems to
   usually show up on large queries...

       "ExecInitIndexScan: both left and right op's are rel-vars"

   I've seen it before, but can't recall a solution and couldn't find
   one in archives/deja...

   Thanks in advance...

   Ed

   pgsql 6.5.2, redhat 6.0 (2.2.5-15smp).



Mon, 13 Dec 1999 12:55:21 -0600
Ed Loehr <ELOEHR@austin.rr.com>
[HACKERS] "ExecInitIndexScan: both left and right..." meaning?
Ed Loehr <ELOEHR@austin.rr.com> writes:
>    Anyone know what this error is or how to prevent it?  Seems to
>    usually show up on large queries...
>        "ExecInitIndexScan: both left and right op's are rel-vars"

Sounds like you've found a bug.  How about a specific example of
a query that causes this?

			regards, tom lane

Mon, 13 Dec 1999 17:24:38 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning? 
Tom Lane wrote:

> Ed Loehr <ELOEHR@austin.rr.com> writes:
> >    Anyone know what this error is or how to prevent it?  Seems to
> >    usually show up on large queries...
> >        "ExecInitIndexScan: both left and right op's are rel-vars"
>
> Sounds like you've found a bug.  How about a specific example of
> a query that causes this?

Unfortunately, this is the simplest example I have to offer.  The
following query succeeds numerous times before going into a continuous
failure mode due to the error above.  Vacuuming the DB fixes the
problem temporarily "for a while".

SELECT sum( cet.default_budget_per_unit * cahrn.hr_count )
FROM contract_activity_hr_need cahrn, contract_expense_type cet,
     contract_activity_type_expense_type catet,
     contract_activity_type cat, activity pa
WHERE -- lame attempt at making this easy on the eye...
                  cet.contract_id = 1 AND catet.contract_id = 1 AND
                cahrn.contract_id = 1 AND pa.contract_id = 1 AND
                  cat.contract_id = 1 AND cet.expense_unit_id = 6 AND
             pa.activity_state_id <> 5 AND
             pa.activity_state_id <> 4 AND
                     (pa.billable = 0 OR cahrn.billable = 0) AND
            catet.expense_type_id = cet.expense_type_id AND
           catet.activity_type_id = cat.activity_type_id AND
  cahrn.contract_activity_type_id = cat.id AND
              pa.activity_type_id = cat.activity_type_id;

Without including the rather lengthy schema definition for the 5
tables involved, let me clarify the data types of the example by
saying that every single column in the query above is of type INTEGER
except for cet.default_budget_per_unit in the SELECT clause, which is
of type FLOAT8.   Note that all columns above ending in 'XXX_id' are
foreign keys referencing the 'id' column of the 'XXX' table, which is
declared as type SERIAL.  Note also that every table has a couple of
book-keeping columns ('creation_time' and 'record_status').  For
example, cet.contract_id is an INTEGER value acting as a foreign key
to the 'contract' table:

CREATE TABLE contract (
   id            SERIAL, -- pkey, ref'd as fkey 'contract_id'
   ...
   creation_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   record_status INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE contract_expense_type (
   id           SERIAL,
   contract_id  INTEGER NOT NULL, -- fkey to contract table
   ...
   creation_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   record_status INTEGER NOT NULL DEFAULT 1
);

One might suspect the size of my tuples might be a factor.  I believe
my **largest** rowsize in any table is 152 bytes, though I'm not sure
how VARCHARs are sized (all my varchar values are considerably less
than 256 bytes, and rarely are there more than 2 of these in a table).

I think the error comes from line 862 of
.../src/backend/executor/nodeIndexscan.c, though it's possible it may
have come at times from line 925 of the same file (a similar error msg
differing only by an apostrophe).

Other current configuration details:

    Pgsql configured with:  ./configure --prefix=/usr/local/pgsql
-with-odbc

    PG:  PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc
egcs-2.91.66
    OS:  RH6.1 Linux XXX 2.2.12-20smp #1 SMP Mon Sep 27 10:34:45 EDT
1999 i686 unknown,
    HW:  dual P3 600Mhz w/1Gb RAM and 3 UW 9Gb SCSI drives in software
RAID.
    SW:  Apache 1.3.9 with mod_ssl 2.4.9, mod_perl 1.21, DBI 1.13,
DBD/Pg 0.92

I've also seen this problem on RH6.0, Pg6.5.2, Linux2.2.12-15,
512MbRAM, dual450MhzP3, NoRAID, mod_ssl 2.4.5...

Any help would be greatly appreciated.  I can code around this, of
course, but it'd be nice...

Cheers,
Ed Loehr


Wed, 15 Dec 1999 18:14:57 -0600
Ed Loehr <ELOEHR@austin.rr.com>
Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning?
Ed Loehr <ELOEHR@austin.rr.com> writes:
>> Sounds like you've found a bug.  How about a specific example of
>> a query that causes this?

> Unfortunately, this is the simplest example I have to offer.  The
> following query succeeds numerous times before going into a continuous
> failure mode due to the error above.  Vacuuming the DB fixes the
> problem temporarily "for a while".

Oh my, *that's* interesting.  I have no idea what could be causing that.
The error message you're getting suggests that the planner is generating
an incorrect plan tree for the query, which I'd believe soon enough,
but I don't understand why the behavior would change over time.
A VACUUM could change the planner's results by altering the stored
statistics for the tables --- but if you're not vacuuming, the plan
should be the same every time.

Does the EXPLAIN output showing the query plan change from when it's
working to when it's not?  What would really be helpful is to see the
EXPLAIN VERBOSE output in both states (preferably, the pretty-printed
version that gets put in the postmaster log file, not the compressed
version that gets sent to the client).

Also, what indexes do you have on these tables?

			regards, tom lane

Wed, 15 Dec 1999 23:03:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning? 
Tom Lane wrote:

> Ed Loehr <ELOEHR@austin.rr.com> writes:
> > ... query succeeds numerous times before going into a continuous
> > failure mode due to the error above.  Vacuuming the DB fixes the
> > problem "for a while".
>
> Oh my, *that's* interesting.  I have no idea what could be causing that.
> The error message you're getting suggests that the planner is generating
> an incorrect plan tree for the query, which I'd believe soon enough,
> but I don't understand why the behavior would change over time.
> A VACUUM could change the planner's results by altering the stored
> statistics for the tables --- but if you're not vacuuming, the plan
> should be the same every time.

No intermediate vacuuming is occurring, AFAIK (though I'm trying to figure
out how to trigger vacuuming on this error).  Speculating, does the genetic
algorithm twiddle any of the planner's stats?   I ask because I know some
of my other queries involve 6 or more tables, and I seem to recall that was
a trigger point for genetic algorithms to kick in with default settings.
I am running with defaults.

> Does the EXPLAIN output showing the query plan change from when it's
> working to when it's not?  What would really be helpful is to see the
> EXPLAIN VERBOSE output in both states (preferably, the pretty-printed
> version that gets put in the postmaster log file, not the compressed
> version that gets sent to the client).

I will attempt to capture EXPLAIN output for the problem situation.

> Also, what indexes do you have on these tables?

I have single-column indices on most every foreign key field (ie,
contract_id), some unique and some not, and on every primary key field
(i.e., 'id' in the 'contract' table).  I have a few multi-column indices.
The only types I use in the entire database are INTEGER, SERIAL, FLOAT8,
DATETIME, and VARCHAR, and I have indices involving on all of these types
at one point or another.  I also have a few of what I'd call "overlapping"
indices, i.e.,

    create table mytable (
        id serial,
        dog_id integer,
        cat_id integer,
        ...
    );
    create index mytable_dog_idx on mytable(dog_id);
    create index mytable_cat_idx on mytable(cat_id);
    create index mytable_dogcat_idx on mytable(dog_id,cat_id);

...thinking these indices would allow the fastest lookups from 3 different
angles (at the cost of slower inserts, of course).  Not sure my intuition
here corresponds directly with the technical reality...

Your question also reminds me of a scenario I'd wondered about:

   create table mytable (
        id serial,
        ...
        primary key (id)
    );
    create unique index mytable_id on mytable(id);

The primary key designation implicitly creates a unique index
('mytable_id_pkey', is it?).  What happens if I inadvertently create
another unique index on the same field (other than being worthless,
redundant, and a needless performance hit)?  I believe I have this
situation in some cases as a result of adding the 'primary key' designation
later, and hadn't gotten around to cleaning it up.  Does that smell like a
rat?  Any other ideas?

Cheers,
Ed Loehr


Thu, 16 Dec 1999 00:35:27 -0600
Ed Loehr <ELOEHR@austin.rr.com>
Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning?
Ed Loehr <ELOEHR@austin.rr.com> writes:
> Tom Lane wrote:
>> Oh my, *that's* interesting.  I have no idea what could be causing that.

> Speculating, does the genetic algorithm twiddle any of the planner's
> stats?

No, or at least no more than regular planning does.  Let's say it's not
*supposed* to.  When dealing with a hard-to-characterize bug, it's wise
not to rule anything out...

> I ask because I know some of my other queries involve 6 or
> more tables, and I seem to recall that was a trigger point for genetic
> algorithms to kick in with default settings.

I think the default is 11 tables in 6.5.*.  At least I get

play=> show geqo;
NOTICE:  GEQO is ON beginning with 11 relations
SHOW VARIABLE

>     create index mytable_dog_idx on mytable(dog_id);
>     create index mytable_cat_idx on mytable(cat_id);
>     create index mytable_dogcat_idx on mytable(dog_id,cat_id);

> ...thinking these indices would allow the fastest lookups from 3 different
> angles (at the cost of slower inserts, of course).  Not sure my intuition
> here corresponds directly with the technical reality...

I doubt the 2-column index earns its keep given that you have another
index on the front column.  A multicolumn index is a pretty specialized
beast, so I don't recommend creating one unless you have a very specific
heavily-used query in mind.  (Of course, if you're making a multicol
UNIQUE index to enforce uniqueness of a multicol primary key, that's
a different matter entirely.  But if you're just fishing for performance
improvements, you're probably fishing in the wrong place.)

> Your question also reminds me of a scenario I'd wondered about:
>    create table mytable (
>         id serial,
>         ...
>         primary key (id)
>     );
>     create unique index mytable_id on mytable(id);

> The primary key designation implicitly creates a unique index
> ('mytable_id_pkey', is it?).

Yes, I think so.

> What happens if I inadvertently create
> another unique index on the same field (other than being worthless,
> redundant, and a needless performance hit)?

AFAIK it should work, but as you say it's a useless performance hit.

It's barely conceivable that there's a bug lurking in there, since
it's a very-seldom-exercised case.  But having lots of (nonidentical)
indexes on one table is very well exercised, and it's tough to see
why it would matter if two of them happened to have identical
parameters.

			regards, tom lane

Thu, 16 Dec 1999 02:04:53 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning? 
Tom Lane wrote:
> 
> Ed Loehr <ELOEHR@austin.rr.com> writes:
> >     create index mytable_dog_idx on mytable(dog_id);
> >     create index mytable_cat_idx on mytable(cat_id);
> >     create index mytable_dogcat_idx on mytable(dog_id,cat_id);
> 
> > ...thinking these indices would allow the fastest lookups from 3 different
> > angles (at the cost of slower inserts, of course).  Not sure my intuition
> > here corresponds directly with the technical reality...
> 
> I doubt the 2-column index earns its keep given that you have another
> index on the front column.  A multicolumn index is a pretty specialized
> beast, so I don't recommend creating one unless you have a very specific
> heavily-used query in mind.  (Of course, if you're making a multicol
> UNIQUE index to enforce uniqueness of a multicol primary key, that's
> a different matter entirely.  But if you're just fishing for performance
> improvements, you're probably fishing in the wrong place.)

Actually I think that the first (dog_id) is worthless in this situation as
(dog_id,cat_id) can be used instead of it.

I vaguely remember that Hiroshi posted a patch some time ago that fixed 
the plan to use more then only the first column of multi-column index 
if possible. 

The first column of a multi-column index has always been used afaik.

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

Thu, 16 Dec 1999 12:01:56 +0200
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning?
Tom Lane wrote:

> Does the EXPLAIN output showing the query plan change from when it's
> working to when it's not?  What would really be helpful is to see the
> EXPLAIN VERBOSE output in both states (preferably, the pretty-printed
> version that gets put in the postmaster log file, not the compressed
> version that gets sent to the client).

Yes, the query plan changes between working state and non-working state.
Vaccum triggers the change.  Other things may also, I'm not sure yet.  Here
are the failing and successful query plans, respectively...

QUERY PLAN: (failed due to ExecInitIndexScan left/right rel op error)

Aggregate  (cost=10.05 rows=1 width=48)
  ->  Nested Loop  (cost=10.05 rows=1 width=48)
        ->  Nested Loop  (cost=8.05 rows=1 width=36)
              ->  Nested Loop  (cost=6.05 rows=1 width=24)
                    ->  Nested Loop  (cost=4.05 rows=1 width=16)
                          ->  Index Scan using activity_cid on activity pa  (cost=2.05 rows=1 width=8)
                          ->  Index Scan using contract_activity_type_pkey on contract_activity_type cat  (cost=2.00 rows=2 width=8)
                    ->  Index Scan using contract_activity_type_exp_pkey on contract_activity_type_expense_ catet  (cost=2.00 rows=2 width=8)
              ->  Index Scan using contract_expense_type_pkey on contract_expense_type cet  (cost=2.00 rows=1 width=12)
        ->  Index Scan using contract_activity_hr_need_pkey on contract_activity_hr_need cahrn  (cost=2.00 rows=2 width=12)

VACUUM

QUERY PLAN: (successful query after vacuuming)

Aggregate  (cost=9.58 rows=1 width=48)
  ->  Nested Loop  (cost=9.58 rows=1 width=48)
        ->  Nested Loop  (cost=7.58 rows=1 width=36)
              ->  Nested Loop  (cost=5.53 rows=1 width=28)
                    ->  Nested Loop  (cost=3.53 rows=1 width=16)
                          ->  Seq Scan on contract_activity_type cat  (cost=1.53 rows=1 width=8)
                          ->  Index Scan using contract_activity_type_exp_pkey on contract_activity_type_expense_ catet  (cost=2.00 rows=2 width=8)
                    ->  Index Scan using contract_expense_type_pkey on contract_expense_type cet  (cost=2.00 rows=1 width=12)
              ->  Index Scan using activity_cid on activity pa  (cost=2.05 rows=1 width=8)
        ->  Index Scan using contract_activity_hr_need_pkey on contract_activity_hr_need cahrn  (cost=2.00 rows=2 width=12)

Other ideas?

Cheers,
Ed Loehr


Fri, 17 Dec 1999 16:44:28 -0600
Ed Loehr <ELOEHR@austin.rr.com>
Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning?
Ed Loehr <ELOEHR@austin.rr.com> writes:
> Yes, the query plan changes between working state and non-working state.
> Vaccum triggers the change.  Other things may also, I'm not sure yet.  Here
> are the failing and successful query plans, respectively...

Mmmm ... I suspected it had something to do with indexscan on the inner
side of a nestloop (the optimizer has some strange hacks for that).
Looks like I was right.  Could I trouble you for the EXPLAIN VERBOSE
output, rather than just EXPLAIN?  (Preferably, the pretty-printed form
that gets dumped into the postmaster log, not the unreadable form that
psql shows.)

			regards, tom lane

Fri, 17 Dec 1999 18:11:18 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning? 