[BUGS] BUG #5502: Preparing an array return Bug
The following bug has been logged online:
Bug reference: 5502
Logged by: xMoMx
Email address: warit...@gmail.com
PostgreSQL version: 8.3
Operating system: Window
Description:Preparing an array return Bug
Details:
Spending the whole day trying to figure this out myself and finally I gave
up. My postGre simply refuse to prepare an array for insert and update. It
work fine when not preparing though. (But heck we all know we want to
prepare everything right?)
Here's the code:
comm.Parameters.Add(new NpgsqlParameter(myArray, NpgsqlDbType.Array |
NpgsqlDbType.Smallint));
comm.Parameters[0].Value = new Int16[2]{0,0};
Once execute, nothing is return. Not even error log.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5503: error in trigger function with dropped columns
The following bug has been logged online: Bug reference: 5503 Logged by: Maksym Boguk Email address: maxim.bo...@gmail.com PostgreSQL version: 8.4.3 Operating system: Linux 2.6.18-164 Description:error in trigger function with dropped columns Details: This bug hard to describe. But in general if a table contained dropped columns you cannot use return record variable in trigger function. Because you get error like: ERROR: returned row structure does not match the structure of the triggering table DETAIL: Number of returned columns (1) does not match expected column count (3). Test case: postgres=# CREATE TABLE test (f1 text, f2 text, f3 text); CREATE TABLE postgres=# insert into test values (1,2,3); INSERT 0 1 CREATE OR REPLACE FUNCTION test_function() RETURNS trigger AS $$ DECLARE _row record; BEGIN RAISE NOTICE 'NEW record = %', NEW; SELECT * INTO _row FROM test limit 1; RAISE NOTICE '_row record = %', _row; RETURN _row; END; $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# CREATE TRIGGER test_trigger before insert on test for each row EXECUTE PROCEDURE test_function(); CREATE TRIGGER postgres=# insert into test values (1,2,3); NOTICE: NEW record = (1,2,3) NOTICE: _row record = (1,2,3) INSERT 0 1 Ok until now all looks good... now lets drop one column from test: postgres=# ALTER TABLE test drop column f3; ALTER TABLE postgres=# insert into test values (1,2); NOTICE: NEW record = (1,2) NOTICE: _row record = (1,2) ERROR: returned row structure does not match the structure of the triggering table DETAIL: Number of returned columns (2) does not match expected column count (3). CONTEXT: PL/pgSQL function test_function during function exit OOPS! Recreating function doesn't help. Drop/create trigger again doesn't help too: postgres=# DROP TRIGGER test_trigger on test; DROP TRIGGER postgres=# CREATE TRIGGER test_trigger before insert on test for each row EXECUTE PROCEDURE test_function(); CREATE TRIGGER postgres=# insert into test values (1,2); NOTICE: NEW record = (1,2) NOTICE: _row record = (1,2) ERROR: returned row structure does not match the structure of the triggering table DETAIL: Number of returned columns (2) does not match expected column count (3). CONTEXT: PL/pgSQL function test_function during function exit If I drop one more column I start getting next error: postgres=# ALTER TABLE test drop column f2; ALTER TABLE postgres=# insert into test values (1); NOTICE: NEW record = (1) NOTICE: _row record = (1) ERROR: returned row structure does not match the structure of the triggering table DETAIL: Number of returned columns (1) does not match expected column count (3). CONTEXT: PL/pgSQL function test_function during function exit In the same defining _row test%ROWTYPE; producing no errors in both cases. Thank you very much for your attention. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5503: error in trigger function with dropped columns
Maksym Boguk maxim.bo...@gmail.com writes: This bug hard to describe. But in general if a table contained dropped columns you cannot use return record variable in trigger function. This is fixed for 9.0 ... or at least the specific test case you provide doesn't fail. We have not risked back-porting the change though, because there are other aspects of what the new code does that might cause people problems, eg http://archives.postgresql.org/pgsql-hackers/2010-03/msg00444.php http://archives.postgresql.org/message-id/6645.1267926...@sss.pgh.pa.us regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 11.06.2010 21:19, schrieb Robert Haas: On Fri, Jun 11, 2010 at 2:46 PM, Dimitri Fontaine But of course you don't ever do that. What you do once the restore failed on you is fix the schema and the application before to upgrade. Presumably, you mean that YOU don't ever do that. What everybody else does is up to them, and there are plenty of people on this thread saying either (1) they don't want to do what you're proposing or (2) their application doesn't need fixing because it already quotes everything. and 3) the application is fixed already by somebody else (the vendor) -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
[BUGS] BUG #5504: cache lookup failed for function
The following bug has been logged online: Bug reference: 5504 Logged by: Jan Merka Email address: me...@highsphere.net PostgreSQL version: 8.4.4 and 8.4.3 Operating system: Linux Description:cache lookup failed for function Details: After installing postgresql 8.4.4 from sources, I am getting an error ERROR: cache lookup failed for function 2071 Test case: SELECT date '2010-01-01' + interval '1 month'; ERROR: cache lookup failed for function 2071 So it seems like adding an interval to date does not work anymore. Why? And how do I fix it? I have tried to go back to the 8.4.3 version but it now exhibits the same problem even though it worked fine until the 8.4.4 version was installed. What can I do to fix it or help you with fixing? Thank you, Jan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5504: cache lookup failed for function
Jan Merka me...@highsphere.net writes: After installing postgresql 8.4.4 from sources, I am getting an error ERROR: cache lookup failed for function 2071 Test case: SELECT date '2010-01-01' + interval '1 month'; ERROR: cache lookup failed for function 2071 Works for me: psql (8.4.4) Type help for help. regression=# SELECT date '2010-01-01' + interval '1 month'; ?column? - 2010-02-01 00:00:00 (1 row) The error indicates a failure to fetch the pg_proc entry for date_pl_interval, which might indicate system catalog corruption, or it might mean there is something wrong with your postgres executable. Can you do the command in other database(s) in the same installation? What do you get from select * from pg_proc where oid = 2071; ? (If nothing, also try it with enable_indexscan and enable_bitmapscan turned off.) regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5503: error in trigger function with dropped columns
I see... but anyway this bug does not allow use return record value from a trigger in table contained at least one dropped column, and even worse trigger will work on fresh loaded copy of production database and would pass all possible tests, but on production database it is stop working. Moreover, full functional system can become broken if single column dropped from table contained such trigger. E.g. functionality of such trigger depends of dropped column history of the table, which is wrong (IMHO). I was tried another test trigger on table with dropped column, and get even more funny results (trigger awaiting return record contained all rows from table include dropped so I tried construct such record): CREATE OR REPLACE FUNCTION test_function() RETURNS trigger AS $$ DECLARE _row record; BEGIN RAISE NOTICE 'NEW record = %', NEW; SELECT *,2,3 INTO _row FROM test limit 1; RAISE NOTICE '_row record = %', _row; RETURN _row; END; $$ LANGUAGE plpgsql; postgres=# insert into test values (1); NOTICE: NEW record = (1) NOTICE: _row record = (1,2,3) ERROR: returned row structure does not match the structure of the triggering table DETAIL: Returned type integer does not match expected type N/A (dropped column) in column pg.dropped.2. CONTEXT: PL/pgSQL function test_function during function exit I think changes in 9.0 now mask actual bug instead of fix it. If I was wrong, still would be useful to know how to use return record from trigger function in that case, because I can't make a working version at all. On Mon, Jun 14, 2010 at 4:09 AM, Tom Lane t...@sss.pgh.pa.us wrote: Maksym Boguk maxim.bo...@gmail.com writes: This bug hard to describe. But in general if a table contained dropped columns you cannot use return record variable in trigger function. This is fixed for 9.0 ... or at least the specific test case you provide doesn't fail. We have not risked back-porting the change though, because there are other aspects of what the new code does that might cause people problems, eg http://archives.postgresql.org/pgsql-hackers/2010-03/msg00444.php http://archives.postgresql.org/message-id/6645.1267926...@sss.pgh.pa.us regards, tom lane -- Maxim Boguk Senior Postgresql DBA. Skype: maxim.boguk Jabber: maxim.bo...@gmail.com LinkedIn profile: http://nz.linkedin.com/in/maximboguk МойКруг: http://mboguk.moikrug.ru/ Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не все. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5504: cache lookup failed for function
On Sunday, June 13, 2010, Tom Lane wrote: Jan Merka me...@highsphere.net writes: After installing postgresql 8.4.4 from sources, I am getting an error ERROR: cache lookup failed for function 2071 Test case: SELECT date '2010-01-01' + interval '1 month'; ERROR: cache lookup failed for function 2071 Works for me: psql (8.4.4) Type help for help. regression=# SELECT date '2010-01-01' + interval '1 month'; ?column? - 2010-02-01 00:00:00 (1 row) The error indicates a failure to fetch the pg_proc entry for date_pl_interval, which might indicate system catalog corruption, You are correct, at least one database must be corrupted. In the corrupted database: = SELECT * from pg_proc WHERE oid = 2071; proname | pronamespace | proowner | prolang | procost | prorows | provariadic | proisagg | proiswindow | p rosecdef | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults | prosrc | probin | proconfig | proacl -+--+--+-+-+-+-+--+-+-- -+-+---+-+--+-++-+- ---+-+-++++---+ (0 rows) In the 'postgres' database: = SELECT * from pg_proc WHERE oid = 2071; proname | pronamespace | proowner | prolang | procost | prorows | provariadic | proisagg | proisw indow | prosecdef | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proar gtypes | proallargtypes | proargmodes | proargnames | proargdefaults | prosrc | probin | proconfi g | proacl --+--+--+-+-+-+-+--+--- --+---+-+---+-+--+-++-- ---++-+-++--++- --+ date_pl_interval | 11 | 10 | 12 | 1 | 0 | 0 | f| f | f | t | f | i |2 | 0 | 1114 | 1082 1186 || | || date_pl_interval || | or it might mean there is something wrong with your postgres executable. Can you do the command in other database(s) in the same installation? What do you get from select * from pg_proc where oid = 2071; ? (If nothing, also try it with enable_indexscan and enable_bitmapscan turned off.) I have dumped the database with corrupted pg_proc and restored into a new database and the error is now gone. Would you like me to investigate more about the database corruption? Cheers, Jan regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
