Thread: alter_table.sql

alter_table.sql

From
Patrick Welche
Date:
In the alter table regression test, alter_table.sql, it says:

-- 20 values, sorted
SELECT unique1 FROM ten_k WHERE unique1 < 20;

-- 20 values, sorted
SELECT unique2 FROM ten_k WHERE unique2 < 20;

Why sorted? Shouldn't it be

SELECT unique1 FROM ten_k WHERE unique1 < 20 ORDER BY unique1;

if we really expect the output to be sorted?

Cheers,

Patrick


Re: [HACKERS] alter_table.sql

From
Tom Lane
Date:
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> In the alter table regression test, alter_table.sql, it says:
> -- 20 values, sorted
> SELECT unique1 FROM ten_k WHERE unique1 < 20;
> Why sorted? Shouldn't it be
> SELECT unique1 FROM ten_k WHERE unique1 < 20 ORDER BY unique1;
> if we really expect the output to be sorted?

The regression test author evidently expected the optimizer to choose an
indexscan, which will produce the values in sorted order as a byproduct.
I agree this code is bogus in a theoretical sense, but I don't think
it's worth worrying about until we alter the optimizer so far that it
doesn't choose an indexscan for this query.  (Indeed, that might be a
sign of an optimizer bug --- so I'd look into why the behavior changed
before changing the regress test.)

Since our regress tests are checked on the basis of exact equality of
output, in theory every single regress test SELECT that doesn't specify
"ORDER BY" is broken, because in theory the system could choose to put
out the tuples in some other order than what's in the regress test
reference outputs.  But in practice, the implementation-dependent
ordering you get is reproducible across platforms, so the tests
accomplish what they're supposed to.  Every so often we have to throw in
an ORDER BY when we find that one of the test cases isn't so
reproducible.
        regards, tom lane


Re: [HACKERS] alter_table.sql

From
Patrick Welche
Date:
OK - got it! It is because vacuum analyze <tablename> doesn't work for me,
therefore the select doesn't use indices, so uses a sequential rather than
index scan => my rows are returned out of order.

Thanks for the pointer.

Cheers,

Patrick

On Tue, Mar 07, 2000 at 11:19:08AM -0500, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> > In the alter table regression test, alter_table.sql, it says:
> > -- 20 values, sorted
> > SELECT unique1 FROM ten_k WHERE unique1 < 20;
> > Why sorted? Shouldn't it be
> > SELECT unique1 FROM ten_k WHERE unique1 < 20 ORDER BY unique1;
> > if we really expect the output to be sorted?
> 
> The regression test author evidently expected the optimizer to choose an
> indexscan, which will produce the values in sorted order as a byproduct.
> I agree this code is bogus in a theoretical sense, but I don't think
> it's worth worrying about until we alter the optimizer so far that it
> doesn't choose an indexscan for this query.  (Indeed, that might be a
> sign of an optimizer bug --- so I'd look into why the behavior changed
> before changing the regress test.)
> 
> Since our regress tests are checked on the basis of exact equality of
> output, in theory every single regress test SELECT that doesn't specify
> "ORDER BY" is broken, because in theory the system could choose to put
> out the tuples in some other order than what's in the regress test
> reference outputs.  But in practice, the implementation-dependent
> ordering you get is reproducible across platforms, so the tests
> accomplish what they're supposed to.  Every so often we have to throw in
> an ORDER BY when we find that one of the test cases isn't so
> reproducible.
> 
>             regards, tom lane


Re: [HACKERS] alter_table.sql

From
Tom Lane
Date:
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> OK - got it! It is because vacuum analyze <tablename> doesn't work for me,
> therefore the select doesn't use indices, so uses a sequential rather than
> index scan => my rows are returned out of order.

Ah so.  I think you mentioned before that you were seeing trouble with
VACUUM ANALYZE --- we need to find out what the problem is.  What
platform are you on, and what are you seeing exactly?
        regards, tom lane


Re: [HACKERS] alter_table.sql

From
Patrick Welche
Date:
On Tue, Mar 07, 2000 at 12:01:29PM -0500, Tom Lane wrote:
> Ah so.  I think you mentioned before that you were seeing trouble with
> VACUUM ANALYZE --- we need to find out what the problem is.  What
> platform are you on, and what are you seeing exactly?

When I mentioned it Bruce said "works for me" => I assume it is netbsd
specific => I should fix it! The symptom is, if I say
vacuum analyze <tablename>, I get

NOTICE:  Vacuum: table not found
VACUUM

If I omit the tablename, vacuum analyze works. I have been rather pressed
for time, so all I can say is the notice comes from line 360 of
src/backend/commands/vacuum.c...

Cheers,

Patrick


Re: [HACKERS] alter_table.sql

From
Tom Lane
Date:
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> On Tue, Mar 07, 2000 at 12:01:29PM -0500, Tom Lane wrote:
>> Ah so.  I think you mentioned before that you were seeing trouble with
>> VACUUM ANALYZE --- we need to find out what the problem is.  What
>> platform are you on, and what are you seeing exactly?

> When I mentioned it Bruce said "works for me" => I assume it is netbsd
> specific => I should fix it! The symptom is, if I say
> vacuum analyze <tablename>, I get

> NOTICE:  Vacuum: table not found
> VACUUM

> If I omit the tablename, vacuum analyze works.

Hmm.  Since there have been examples of vacuum analyze <tablename> in
the numeric regress test since 6.5, I'd think we'd have heard about it
if there were any widespread problem ;-).  Perhaps it is a platform
issue, but I suspect you will find there are additional constraints that
explain why no one but you is seeing it.  Please do dig into it ... or,
if you do not have time, you could consider giving one of the other
developers a login on your machine and that person could check it out.
        regards, tom lane


Re: [HACKERS] alter_table.sql

From
Patrick Welche
Date:
On Tue, Mar 07, 2000 at 02:29:32PM -0500, Tom Lane wrote:
> 
> Hmm.  Since there have been examples of vacuum analyze <tablename> in
> the numeric regress test since 6.5, I'd think we'd have heard about it
> if there were any widespread problem ;-).  Perhaps it is a platform
> issue, but I suspect you will find there are additional constraints that
> explain why no one but you is seeing it.  Please do dig into it ... or,
> if you do not have time, you could consider giving one of the other
> developers a login on your machine and that person could check it out.

Story so far: I have a table called "found". vacuum() in src/backend/commands/vacuum.c
gets called with vacrel="found". During vc_init() at line 177, vacrel is cleared (="").

More tomorrow...

Cheers,

Patrick


Re: [HACKERS] alter_table.sql

From
Tom Lane
Date:
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> Story so far: I have a table called "found". vacuum() in
> src/backend/commands/vacuum.c gets called with vacrel="found". During
> vc_init() at line 177, vacrel is cleared (="").

What the ???

Somebody broke this code badly since I last looked at it.  The vacuum
initialization sequence has been rearranged so that it does not work:
there is a CommitTransactionCommand call that occurs before the vacuum
parameters have been copied into safe-across-transactions storage.
We are reading already-freed memory at line 186.

Will fix ASAP.

BTW, this also demonstrates that the CLOBBER_FREED_MEMORY testing hack
I put into aset.c needs more work; it ought to clobber implicitly-freed
memory as well as explicitly pfree'd blocks.  Had I done that I would
probably have seen a regression test failure from this bug.  Will add
some more clobbering code and see what else breaks ;-)
        regards, tom lane


Re: [HACKERS] alter_table.sql

From
Tom Lane
Date:
>> Story so far: I have a table called "found". vacuum() in
>> src/backend/commands/vacuum.c gets called with vacrel="found". During
>> vc_init() at line 177, vacrel is cleared (="").

> Somebody broke this code badly since I last looked at it.

Fix committed to CVS.
        regards, tom lane