[BUGS] ROLLBACK, SAVEPOINT, COMMIT throwing error in transaction block.
Hi List, Below function throwing error when i execute it on PostgreSQL 9.0. ERROR: SPI_execute_plan_with_paramlist failed executing query "SAVEPOINT my_savepoint": SPI_ERROR_TRANSACTION CONTEXT: PL/pgSQL function "rlbk" line 5 at SQL statement I searched and found that it is reported to pgsql-bugs. Can you please let me know whether this bug has fixed or not. CREATE OR REPLACE FUNCTION rlbk(integer) RETURNS integer AS $BODY$ DECLARE bal_amt ALIAS FOR $1; BEGIN UPDATE accounts SET balance = balance - bal_amt WHERE name = 'Axis'; SAVEPOINT my_savepoint; UPDATE accounts SET balance = balance + bal_amt WHERE name = 'Bob'; ROLLBACK TO my_savepoint; UPDATE accounts SET balance = balance + bal_amt WHERE name = 'Wally'; COMMIT; return bal_amt; end; $BODY$ LANGUAGE 'plpgsql'; -- Thanks & Regards Dhaval Jaiswal | The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon,this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Microland takes all reasonable steps to ensure that its electronic communications are free from viruses. However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software.
[BUGS] postgresql 9.0.3: parallel restore fails with comments on indices
Hi,
postgres version: 9.0.3
OS: debian squeeze 64bit
if pg_restore is used with -jN it fails if the dump has comments on indices.
Steps to reproduce (db testdb):
CREATE DATABASE testdb;
\c testdb
CREATE TABLE tab (id INT);
INSERT INTO tab VALUES (1);
CREATE INDEX idx ON tab USING btree (id);
COMMENT ON INDEX idx IS 'first index';
(pgsql-pg_9.0.3)aha@abt:~/bin$ pg_restore --version
pg_restore (PostgreSQL) 9.0.3
(pgsql-pg_9.0.3)aha@abt:~/bin$ pg_dump -F c testdb > testdb.pg_dump
(pgsql-pg_9.0.3)aha@abt:~/bin$ pg_restore -j2 -c -d testdb testdb.pg_dump
ERROR: relation "idx" does not exist
STATEMENT: COMMENT ON INDEX idx IS 'first index';
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1784; 0 0 COMMENT INDEX idx aha
pg_restore: [archiver (db)] could not execute query: ERROR: relation "idx"
does not exist
Command was: COMMENT ON INDEX idx IS 'first index';
WARNING: errors ignored on restore: 1
(pgsql-pg_9.0.3)aha@abt:~/bin$ pg_restore -c -d testdb testdb.pg_dump
(pgsql-pg_9.0.3)aha@abt:~/bin$ echo $?
0
The problem seems to be a false assumption in pg_backup_archiver.c:
3200 for (next_work_item = AH->toc->next; next_work_item != AH->toc;
next_work_item = next_work_item->next)
3201 {
3202 /* Non-PRE_DATA items are just ignored for now */
3203 if (next_work_item->section == SECTION_DATA ||
3204 next_work_item->section == SECTION_POST_DATA)
3205 continue;
3206
3207 ahlog(AH, 1, "processing item %d %s %s\n",
3208 next_work_item->dumpId,
3209 next_work_item->desc, next_work_item->tag);
3210
3211 (void) restore_toc_entry(AH, next_work_item, ropt, false);
3212
3213 /* there should be no touch of ready_list here, so pass
NULL */
3214 reduce_dependencies(AH, next_work_item, NULL);
3215 }
Comments are in SECTION_NONE so they get restored here regardless of
dependencies, which is obviously wrong
because the comment on an INDEX depends on the INDEX itself and the INDEX is in
SECTION_POST_DATA
Best regards
Arnd
--
Arnd Hannemann
credativ GmbH, HRB Mönchengladbach 12080
Hohenzollernstr. 133, 41061 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz
--
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 #5878: BTREE_BUILD_STATS causes 'make check' to fail
Em 10-02-2011 09:56, Jan-Peter Seifert escreveu: However, as long as I have BTREE_BUILD_STATS defined, 'make check' fails on me with the following message: It is a bug. The attached patch fixes it. I didn't investigate when it was broken; maybe one or two releases ago when someone reorganizes the header files. Those debug macros deserve a refactor but nobody bothers to do it yet. -- Euler Taveira de Oliveira http://www.timbira.com/ diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c index 558ace1..ba01874 100644 *** a/src/backend/access/nbtree/nbtree.c --- b/src/backend/access/nbtree/nbtree.c *** *** 31,36 --- 31,37 #include "storage/lmgr.h" #include "storage/predicate.h" #include "storage/smgr.h" + #include "tcop/tcopprot.h" #include "utils/memutils.h" diff --git a/src/backend/access/nbtree/nbtsort.c b/src/backend/access/nbtree/nbtsort.c index e02f008..fd0e86a 100644 *** a/src/backend/access/nbtree/nbtsort.c --- b/src/backend/access/nbtree/nbtsort.c *** *** 70,75 --- 70,76 #include "access/nbtree.h" #include "miscadmin.h" #include "storage/smgr.h" + #include "tcop/tcopprot.h" #include "utils/rel.h" #include "utils/tuplesort.h" -- 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] ROLLBACK, SAVEPOINT, COMMIT throwing error in transaction block.
"Jaiswal Dhaval Sudhirkumar" writes: > ERROR: SPI_execute_plan_with_paramlist failed executing query "SAVEPOINT > my_savepoint": SPI_ERROR_TRANSACTION This is not a bug. It's an extremely well-documented limitation that you can't use those commands in a plpgsql function. You can get the effect of a savepoint using a BEGIN/EXCEPTION block. 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] postgresql 9.0.3: parallel restore fails with comments on indices
Arnd Hannemann writes: > if pg_restore is used with -jN it fails if the dump has comments on indices. Reproduced here, thanks for the report! > The problem seems to be a false assumption in pg_backup_archiver.c: > ... > Comments are in SECTION_NONE so they get restored here regardless of > dependencies, which is obviously wrong Yeah, that seems to need a bit more thought. It's intentional that comments that are in the PRE_DATA area get restored immediately, so as not to eat all the overhead of a worker subprocess for them. But we can't do it that way for comments intermixed with POST_DATA items. I think the simplest fix is to allow this loop to process SECTION_NONE items only as long as it hasn't skipped any prior items. 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
[BUGS] BUG #5894: Rules' behaviour when SERIAL data types are used
The following bug has been logged online:
Bug reference: 5894
Logged by: Piergiorgio Buongiovanni
Email address: piergiorgio.buongiova...@netspa.it
PostgreSQL version: 8.4.4
Operating system: RedHat Centos
Description:Rules' behaviour when SERIAL data types are used
Details:
We are experiencing a problem on using rules and serial data types with
PostgreSQL.
We are trying to align two tables in two different schemas through rules and
the main table has a column of data type SERIAL.
The case is the following:
CREATE table business.prova
(
iSId SERIAL,
cName varchar,
CONSTRAINT _prova_PK PRIMARY KEY (iSId)
);
CREATE table l10n.Prova
(
iSId integer,
cName varchar,
CONSTRAINT _Prova_PK PRIMARY KEY (iSId),
CONSTRAINT _Prova_FK FOREIGN KEY (iSId) REFERENCES business.Prova (iSId)
);
Now we create a rule in order to align l10n.prova when a row is inserted in
business.prova:
CREATE OR REPLACE RULE _Prova_Ins AS ON INSERT
TO business.Prova
DO ALSO INSERT INTO l10n.Prova VALUES (NEW.iSId, NEW.cName);
We are now ready to insert a value in business.Prova and we expect to see a
copy of that row in l10n.Prova, so we execute the following statement:
INSERT INTO business.Prova (cName) VALUES ('Prova_2');
We obtain the following error message:
ERROR: insert or update on table "prova" violates foreign key constraint
"_prova_fk"
DETAIL: Key (isid)=(384) is not present in table "prova".
** Errore **
ERROR: insert or update on table "prova" violates foreign key constraint
"_prova_fk"
Stato SQL: 23503
Dettaglio: Key (isid)=(384) is not present in table "prova".
To analyze the problem we have dropped the constraint _prova_fk on
l10n.Prova table; After this we can re-execute the previous statement and
this time we have no errors. If we now look at the two tables we found the
following situation:
SELECT * from business.Prova;
385;"Prova_2"
SELECT * from l10n.Prova;
386;"Prova_2"
Why the value of the iSId column is different?
it seems that a new sequence value is used when the INSERT in l10n.Prova
occurs.
We tried the same SQL statement on PostgreSQL 9.0 and we obtain the same
behaviour.
Is there a limit in the use of rules with SERIAL data types? This is not
clear from your documentation.
--
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 #5894: Rules' behaviour when SERIAL data types are used
"Piergiorgio Buongiovanni" writes: > We are experiencing a problem on using rules and serial data types with > PostgreSQL. > We are trying to align two tables in two different schemas through rules and > the main table has a column of data type SERIAL. > ... > Now we create a rule in order to align l10n.prova when a row is inserted in > business.prova: > CREATE OR REPLACE RULE _Prova_Ins AS ON INSERT > TO business.Prova > DO ALSO INSERT INTO l10n.Prova VALUES (NEW.iSId, NEW.cName); By and large, rules don't work very well with commands involving volatile functions like nextval(). That's because a rule is a macro and the macro expansion can result in multiple calls of the volatile function. Best advice is to use a trigger, not a rule, for propagating changes from one table to another. 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 #5878: BTREE_BUILD_STATS causes 'make check' to fail
Euler Taveira de Oliveira writes: > Em 10-02-2011 09:56, Jan-Peter Seifert escreveu: >> However, as long as I have BTREE_BUILD_STATS defined, 'make check' fails on >> me with the following message: > It is a bug. The attached patch fixes it. I didn't investigate when it was > broken; maybe one or two releases ago when someone reorganizes the header > files. Those debug macros deserve a refactor but nobody bothers to do it yet. Confirmed here, will apply. I'll bet lunch that it got broken in Bruce's last "remove unnecessary #includes" patch --- the process he uses for that is utterly incapable of dealing with inclusions that are needed only in certain cases. 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
