[BUGS] "ERROR: Query-specified return tuple and actual function return tuple do not match" after dropping column
PostgreSQL 7.3 on Linux After dropping column functions return error message: "ERROR: Query-specified return tuple and actual function return tuple do not match" template1=# create table test(id integer, cdate timestamp); CREATE TABLE template1=# INSERT INTO test VALUES (1,now()); INSERT 17515 1 template1=# INSERT INTO test VALUES (2,now()); INSERT 17516 1 template1=# INSERT INTO test VALUES (3,now()); INSERT 17517 1 template1=# create function ftest() returns setof test as 'select * from test' language 'sql'; CREATE FUNCTION template1=# select * from ftest(); id | cdate + 1 | 2003-01-12 22:17:19.814273 2 | 2003-01-12 22:17:22.899388 3 | 2003-01-12 22:17:25.246219 (3 rows) template1=# alter table test drop column cdate; ALTER TABLE template1=# select * from ftest(); ERROR: Query-specified return tuple and actual function return tuple do not match template1=# alter table test add column cdate timestamp; ALTER TABLE template1=# select * from ftest(); ERROR: Query-specified return tuple and actual function return tuple do not match template1=# drop function ftest(); DROP FUNCTION template1=# create function ftest() returns setof test as 'select * from test' language 'sql'; CREATE FUNCTION template1=# select * from ftest(); ERROR: Query-specified return tuple and actual function return tuple do not match template1=# create function ftest2() returns setof test as 'select * from test' language 'sql'; CREATE FUNCTION template1=# select * from ftest2(); ERROR: Query-specified return tuple and actual function return tuple do not match Yours sincerely, Andrzej Kosmala ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] "ERROR: Query-specified return tuple and actual function
Andrzej Kosmala wrote: PostgreSQL 7.3 on Linux After dropping column functions return error message: "ERROR: Query-specified return tuple and actual function return tuple do not match" Hmmm, looks like nodeFunctionscan.c:tupledesc_mismatch needs to be taught about attisdropped. I'll submit a patch this evening if no one else gets to it first. Thanks for the report. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Bug #873: 7.3.1 install fails for RH7.3... cannot find ascii_and_mic.so
Jeffrey Graham ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description 7.3.1 install fails for RH7.3... cannot find ascii_and_mic.so Long Description On a RH 7.3 machine using gcc 3.1, everything builds fine (set --prefix to a nonstandard place, and set LDFLAGS=-static). make install fails with this: make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/era/code/support/postgresql-7.3.1/src/backend/utils/mb/conversion_procs/utf8_and_win874' jgraham@bogart conversion_procs> make install /bin/sh ../../../../../config/install-sh -c -m 644 conversion_create.sql /export_fs/era/code/newSupport/share/postgresql make[1]: Entering directory `/era/code/support/postgresql-7.3.1/src/backend/utils/mb/conversion_procs/ascii_and_mic' /bin/sh ../../../../../../config/install-sh -c -m 755 /export_fs/era/code/newSupport/lib/postgresql/ascii_and_mic.so install:no destination specified make[1]: *** [install] Error 1 make[1]: Leaving directory `/era/code/support/postgresql-7.3.1/src/backend/utils/mb/conversion_procs/ascii_and_mic' make: *** [install] Error 2 Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] "ERROR: Query-specified return tuple and actual function
Joe Conway <[EMAIL PROTECTED]> writes: > Andrzej Kosmala wrote: >> PostgreSQL 7.3 on Linux >> After dropping column functions return error message: "ERROR: >> Query-specified return tuple and actual function return tuple do not match" > Hmmm, looks like nodeFunctionscan.c:tupledesc_mismatch needs to be > taught about attisdropped. I'll submit a patch this evening if no one > else gets to it first. Actually, I believe I deliberately left it like that because I was concerned about what would happen in this scenario. I don't think that changing tupledesc_mismatch to ignore attisdropped columns will make things work nicely. If a function is expecting to return (a,b,c) and then you drop b from the table that defines its return type, is the function likely to magically return (a,c)? I doubt it. Letting the code get past the mismatch check is likely to result in core dumps. I had toyed with the notion of forbidding column drop (and maybe column add too, not sure) whenever a table's rowtype appears as a function argument or result type; but I'm not sure that's sufficient to protect against problems ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Bug #871: FW: How to solve the problem
Derek, > Long Description > NOTICE: Message from PostgreSQL backend: > The Postmaster has informed me that some other backend > died abnormally and possibly corrupted shared memory. > I have rolled back the current transaction and am > going to terminate your database system connection and exit. > Please reconnect to the database system and repeat your query. > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. Did you KILL -9 one or more Postgres processes? Then you will get this message, and have to restart the postgresql server to use it again. IF YOU ARE STILL GETTING THIS ERROR AFTER RESTARTING, then please read the following: This somewhat deceptive message, in my experience, is usually caused by a database crash which WAL is unable to easily recover from, such as HDD errors corrupting the Postgres files. Please do the following: WARNING: The below is provided strictly as volunteer peer-to-peer advice. Follow AT YOUR OWN RISK. 1) Shutdown PostgreSQL using "pg_ctl -m fast stop" 2) check your process log to make sure that *all* postgres processes are halted. Give the system some time to shut everything down. 2)a) if you cannot shut down all postgres processes properly, even after 15-20 minutes, try restarting the system. 3) re-start Postgresql. Give it some time to attempt to restore itself; 20-30 minutes may be necessary with a large database and a slow server. 4) Connect to PostgreSQL. If you get that message again, then you have some kind of serious hardware or OS related problem that Postgres can't deal with. If you connect normally, then everything is OK; skip the rest of the suggestions. 5) Shut down postgres again. 6) Use diagnostic tools to examine your system for: a) Hard drive/controller errors; b) bad RAM; c) OS errors; d) other hardware issues 7) correct any problems you find through (6) 8) re-start postgres and restore your database from backup (you do have a backup, yes?) 9) start using postgresql again. Good luck! -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] "ERROR: Query-specified return tuple and actual function
Tom Lane wrote: > Joe Conway <[EMAIL PROTECTED]> writes: >> Hmmm, looks like nodeFunctionscan.c:tupledesc_mismatch needs to be >> taught about attisdropped. I'll submit a patch this evening if no >> one else gets to it first. > > Actually, I believe I deliberately left it like that because I was > concerned about what would happen in this scenario. I don't think > that changing tupledesc_mismatch to ignore attisdropped columns will > make things work nicely. If a function is expecting to return > (a,b,c) and then you drop b from the table that defines its return > type, is the function likely to magically return (a,c)? I doubt it. Assuming tupledesc_mismatch ignored attisdropped attributes, and the function still output the dropped attribute, the error would be triggered due to a mismatch. But then you could adjust your function and be on your merry way. In the case where the function is returning a whole table tuple (e.g. select * from...), everthing should work correctly (and as expected). Without this change, any table that has had a column dropped would not be able to be used as a function's return type at all. > Letting the code get past the mismatch check is likely to result in > core dumps. I don't see how. The code would still ensure that the returned tuple and defined tuple match, wouldn't it? start with table test, attributes (a,b,c) drop attribute b from test tupledesc_mismatch checks that function's output is (a,c) if function actually outputs (a,c), no problem Am I just being thick-headed? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] "ERROR: Query-specified return tuple and actual function
Joe Conway <[EMAIL PROTECTED]> writes: > Without this change, any table that has had a column dropped would not > be able to be used as a function's return type at all. Yup, that was the idea ;-) > start with table test, attributes (a,b,c) > drop attribute b from test > tupledesc_mismatch checks that function's output is (a,c) > if function actually outputs (a,c), no problem But there *is* a problem: tuples conforming to test's tupledescriptor must actually still have three columns. If the function only outputs two columns, it won't work. You might possibly be able to make it work by restructuring the tuple to have a null column in the middle, but I'm not sure about all the consequences. In the meantime, tupledesc_mismatch *is correct as it stands* --- if the test it embodies is not satisfied, you will get core dumps. What you would need to do if you do not like the present behavior is to be willing to restructure the function's result tuple. I believe that there are comparable problems in the other direction: it's possible that the tuple actually returned by the function might contain attisdropped columns that you'd need to ignore to make it match up to the expected result type. I'm not quite sure about how to put together mapping logic that handles all these cases, but it could probably be done. However, all this is just the tip of the iceberg. Suppose I have defined a view CREATE VIEW v AS SELECT a, b, c FROM myfunction() where myfunction returns a rowtype containing a,b,c. What happens if someone drops column b from the table defining the rowtype? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
