Обсуждение: [GENERAL] more nodeError problems and general view failures!
Dear All,
I'm running postgreSQL 6.3.2 under IRIX 5.3 and I'm having a lot of
problems with views especially with error message nodeError: Bad type 0.
Here are a couple of examples of the problem. Example one shows a
nodeError yielding set of commands. Error 2 show problems I've had with
bookbiz, the SQL demonstration database from the Bowman et al. SQL Handbook
[see v. old posting to qpgsql-questions!]. Again I seem to be having a lot
of trouble with views!
I've also included the results of the regression test (I didn't change the
timezone to PST so all time related test were expected to fail).
Thanks for any feedback!
Cheers,
S.
---- Example 1 -----
brecard=> create table test1 (
brecard-> number int,
brecard-> in_words text
brecard-> );
CREATE
brecard=> create view on_test1 as
brecard-> select in_words,number from test1;
CREATE
brecard=> select * from on_test1;
in_words|number
--------+------
(0 rows)
brecard=> insert into test1 values (
brecard-> 1,'one');
INSERT 147317 1
brecard=> insert into test1 values (2,'two');
INSERT 147318 1
brecard=> insert into test1 values (3,'three');
INSERT 147319 1
brecard=> select * from on_test1;
in_words|number
--------+------
one | 1
two | 2
three | 3
(3 rows)
brecard=> create view on_test2 as
brecard-> select in_words,number,number * number as "number squared" from
test1;
CREATE
brecard=> select * from on_test2;
ERROR: nodeRead: Bad type 0
brecard=> select in_words,number,number * number as "number squared" from
test1;
in_words|number|number squared
--------+------+--------------
one | 1| 1
two | 2| 4
three | 3| 9
(3 rows)
brecard=> drop view on_test2;
ERROR: nodeRead: Bad type 0
------- Example 2 ------
brecard=> \connect bookbiz
connecting to new database: bookbiz
bookbiz=> select * from cateries;
PQexec() -- Request was sent to backend, but backend closed the channel
before responding.
This probably means the backend terminated abnormally before or
while processing the request.
bookbiz=> \d cateries
PQexec() -- There is no connection to the backend.
bookbiz=> \connect bookbiz
connecting to new database: bookbiz
bookbiz=> \d cateries
Table = cateries
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| catery | char() | 12 |
| average_price | money | 4 |
+----------------------------------+----------------------------------+-------+
bookbiz=>
-----------------------
---- Regression test results ----
=============== Notes... =================
postmaster must already be running for the regression tests to succeed.
The time zone is now set to PST8PDT explicitly by this regression test
client frontend. Please report any apparent problems to
ports@postgresql.org
See regress/README for more information.
=============== destroying old regression database... =================
ERROR: destroydb: database regression does not exist.
destroydb: database destroy failed on regression.
=============== creating new regression database... =================
=============== running regression queries... =================
boolean .. ok
char .. ok
char2 .. ok
char4 .. ok
char8 .. ok
char16 .. ok
varchar .. ok
text .. ok
strings .. failed
int2 .. failed
int4 .. failed
oid .. ok
oidint2 .. failed
oidint4 .. failed
oidname .. ok
float4 .. ok
float8 .. failed
numerology .. ok
point .. ok
lseg .. ok
box .. ok
path .. ok
polygon .. ok
circle .. ok
geometry .. failed
timespan .. ok
datetime .. ok
reltime .. ok
abstime .. failed
tinterval .. failed
horology .. failed
comments .. ok
create_function_1 .. ok
create_type .. ok
create_table .. ok
create_function_2 .. ok
constraints .. failed
triggers .. ok
copy .. ok
create_misc .. ok
create_aggregate .. ok
create_operator .. ok
create_view .. ok
create_index .. ok
sanity_check .. ok
errors .. failed
select .. ok
select_into .. ok
select_distinct .. ok
select_distinct_on .. ok
subselect .. ok
aggregates .. ok
transactions .. ok
random .. failed
portals .. ok
misc .. ok
arrays .. ok
btree_index .. ok
hash_index .. ok
select_views .. ok
alter_table .. ok
portals_p2 .. ok
+-------------------------+--------------------------------------+
| Stuart Rison | Ludwig Institute for Cancer Research |
+-------------------------+ University College London |
| Tel. (0171) 878 4041 | 91 Riding House Street |
| Fax. (0171) 878 4040 | London, W1P 8BT, UNITED KINGDOM. |
+-------------------------+--------------------------------------+
| stuart@NOJUNK_ludwig.ucl.ac.uk [Remove NOJUNK_ for it to work] |
+----------------------------------------------------------------+
> brecard=> create view on_test2 as
> brecard-> select in_words,number,number * number as "number squared" from
> test1;
Here is the cause. The code could not handle an AS with multiple
words, because of the way it was stored in the rewrite system. This
patch should fix the problem.
Does it fix your second problem too?
---------------------------------------------------------------------------
? src/Makefile.custom
? src/config.log
? src/Makefile.in
? src/config.cache
? src/config.status
? src/GNUmakefile
? src/log
? src/regress.out
? src/Makefile.global
? src/backend/fmgr.h
? src/backend/parse.h
? src/backend/postgres
? src/backend/global1.bki.source
? src/backend/local1_template1.bki.source
? src/backend/global1.description
? src/backend/local1_template1.description
? src/backend/bootstrap/bootparse.c
? src/backend/bootstrap/bootstrap_tokens.h
? src/backend/bootstrap/bootscanner.c
? src/backend/catalog/global1.bki.source
? src/backend/catalog/global1.description
? src/backend/catalog/local1_template1.bki.source
? src/backend/catalog/local1_template1.description
? src/backend/port/Makefile
? src/backend/postmaster/_xlk
? src/backend/utils/Gen_fmgrtab.sh
? src/backend/utils/fmgr.h
? src/backend/utils/fmgrtab.c
? src/bin/cleardbdir/cleardbdir
? src/bin/createdb/createdb
? src/bin/createuser/createuser
? src/bin/destroydb/destroydb
? src/bin/destroyuser/destroyuser
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_dump/Makefile
? src/bin/pg_dump/pg_dump
? src/bin/pg_id/pg_id
? src/bin/pg_passwd/pg_passwd
? src/bin/pg_version/Makefile
? src/bin/pg_version/pg_version
? src/bin/pgtclsh/pgtclsh
? src/bin/pgtclsh/pgtksh
? src/bin/psql/Makefile
? src/bin/psql/psql
? src/include/version.h
? src/include/config.h
? src/include/blocksize.h
? src/interfaces/ecpg/lib/Makefile
? src/interfaces/libpgtcl/Makefile
? src/interfaces/libpq/Makefile
? src/interfaces/libpq/libpq.so.1.1
? src/interfaces/libpq/c.h
? src/lextest/lex.yy.c
? src/lextest/lextest
? src/test/regress/regression.diffs
? src/tools/backend/flow.ps
Index: src/backend/nodes/outfuncs.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/nodes/outfuncs.c,v
retrieving revision 1.36
diff -c -r1.36 outfuncs.c
*** outfuncs.c 1998/06/15 19:28:32 1.36
--- outfuncs.c 1998/07/09 14:48:58
***************
*** 660,666 ****
sprintf(buf, " :restypmod %d ", node->restypmod);
appendStringInfo(str, buf);
appendStringInfo(str, " :resname ");
! appendStringInfo(str, node->resname);
sprintf(buf, " :reskey %d ", node->reskey);
appendStringInfo(str, buf);
sprintf(buf, " :reskeyop %u ", node->reskeyop);
--- 660,667 ----
sprintf(buf, " :restypmod %d ", node->restypmod);
appendStringInfo(str, buf);
appendStringInfo(str, " :resname ");
! sprintf(buf,"\"%s\"", node->resname); /* fix for SELECT col AS "my name" */
! appendStringInfo(str, buf);
sprintf(buf, " :reskey %d ", node->reskey);
appendStringInfo(str, buf);
sprintf(buf, " :reskeyop %u ", node->reskeyop);
***************
*** 849,855 ****
appendStringInfo(str, " :arraylow ");
for (i = 0; i < node->arrayndim; i++)
{
! sprintf(buf, " %d ", node->arraylow.indx[i]);
appendStringInfo(str, buf);
}
appendStringInfo(str, " :arrayhigh ");
--- 850,856 ----
appendStringInfo(str, " :arraylow ");
for (i = 0; i < node->arrayndim; i++)
{
! sprintf(buf, " %d ", node->arraylow.indx[i]);
appendStringInfo(str, buf);
}
appendStringInfo(str, " :arrayhigh ");
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
Erm, dunce question (sorry) How do I apply that patch? Do I cut and paste from ? src/Makefile.custom down into a file and then use patch? And -more scarily- does that mean I have to recompile postgres? That aside, thanks for your help Bruce, as soon I figure out how to apply the patch I'll let you know if it worked! Cheers, [indeed huge grin... I'm off to Silverstone for the British F1 Grand Prix!!] Stuart. PS. BTW, I'll never understand the C behind it but, conceptually speaking, why does creating the view with the space in it and then select from it yield the error but just doing the query 'select in_words,number,number * number as "number squared" from test1;' work? >> brecard=> create view on_test2 as >> brecard-> select in_words,number,number * number as "number squared" from >> test1; > >Here is the cause. The code could not handle an AS with multiple >words, because of the way it was stored in the rewrite system. This >patch should fix the problem. > >Does it fix your second problem too? > >? src/Makefile.custom >...patch follows... +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | | Tel. (0171) 878 4041 | Courtauld Building | | Fax. (0171) 878 4040 | 91 Riding House Street | +-------------------------+ London, W1P 8BT | | stuart@ludwig.ucl.ac.uk | UNITED KINGDOM. | +-------------------------+--------------------------------------+