Thread: alter_table.sql
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
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
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
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
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
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
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
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
>> 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