Re: [BUGS] BUG #4925: "select ... for update" doesn't affect rows from sub-query
On Thu, Jul 30, 2009 at 1:40 AM, Steve Caligo wrote: > Allowing FOR UPDATE in sub-queries has been rejected in this same thread, > you've also mentioned it your previous reply. > http://archives.postgresql.org/pgsql-bugs/2004-10/msg00150.php Um, I didn't write that email. That was Tom Lane. >> This is pretty weird behavior, and I am among those who think it >> sucks. But it is documented. >> >> http://www.postgresql.org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY > > This behavior may be consistent with the transaction isolation levels > PostgreSQL provides (read committed/serializable, while one would need > true repeatable read in this case), it is a huge drawback when working > with the database, as one has to think about potential collateral damage > with every single SELECT...FOR UPDATE / UPDATE query one writes, > especially the complex ones. > > >> An interesting fact is that if you stick another "for update" into the >> subquery here, the command will fail utterly, with the following error >> message: >> >> ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate functions > > See my second link above, it works as designed. > > The document states that the FOR UPDATE is applied to the view or > sub-query, which I assume means: "applied to a SELECT containing a > sub-query or view". As such, I'm expecting a single statement to be an > atomic operation, i.e. always works on the same data snapshot, independent > of the transaction isolation level. > > This atomicity has to be enforced through a full table lock (which is > often the easiest to implement, but also the most expensive > efficiency-wise) or serialization. > > Side note: a cross-database test showed that Oracle, DB2 nor MySQL (with > InnoDB storage engine) run the query as true repeatable read, whether one > makes use of the "skip locked rows" (when available) or not. One always > ends up with two different rows being updated. Sure. I mean, I understand your frustration here, but this started out as a documentation complaint. I think the current behavior is documented reasonably OK; the problem is that the behavior is pretty weird. I'd be all in favor of fixing it, but I'm not sure what would be involved in that or what the trade-offs would be. I suspect if it were easy it would have been done long ago; you're not the first person to complain about it. ...Robert -- 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 #4925: "select ... for update" doesn't affect rows from sub-query
> On Thu, Jul 16, 2009 at 12:34 PM, Steve Caligo > wrote: >> 2) FIRST TRY, USING "UPDATE WHERE ID = ( SELECT )" >> The goal is to have two clients set their unique ID to a >> single/different >> row from the table. First, using "limit" in a slightly different way: > This is pretty clearly NOT the situation described in the > documentation. There is no FOR UPDATE or FOR SHARE anywhere in this > query. You could argue that we should treat an UPDATE statement as > applying an implicit FOR UPDATE to any subqueries found therein, but > that has nothing to do with whether the current behavior matches the > documentation; it's a discussion of whether the current behavior is > good or bad. Should there really be a difference between doing an UPDATE or just requesting rows to be locked for a later modification (update or deletion, whatever) through SELECT ... FOR UPDATE? I'm aware that there's no FOR UPDATE in that query, as its direct use is discouraged in the documentation (SELECT ... LIMIT ... FOR UPDATE) and reported as "odd behavior" previously as well: http://archives.postgresql.org/pgsql-bugs/2004-10/msg00138.php Yes, I'm considering this behavior bad, as I'd expect the UPDATE statement to lock either the whole table (if there's no other way) or only the rows returned by the sub-SELECT, but as far as I can see, such row-level locking can't be achieved in PostgreSQL but through the use of serialization. Allowing FOR UPDATE in sub-queries has been rejected in this same thread, you've also mentioned it your previous reply. http://archives.postgresql.org/pgsql-bugs/2004-10/msg00150.php > This is pretty weird behavior, and I am among those who think it > sucks. But it is documented. > > http://www.postgresql.org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY This behavior may be consistent with the transaction isolation levels PostgreSQL provides (read committed/serializable, while one would need true repeatable read in this case), it is a huge drawback when working with the database, as one has to think about potential collateral damage with every single SELECT...FOR UPDATE / UPDATE query one writes, especially the complex ones. > An interesting fact is that if you stick another "for update" into the > subquery here, the command will fail utterly, with the following error > message: > > ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate functions See my second link above, it works as designed. The document states that the FOR UPDATE is applied to the view or sub-query, which I assume means: "applied to a SELECT containing a sub-query or view". As such, I'm expecting a single statement to be an atomic operation, i.e. always works on the same data snapshot, independent of the transaction isolation level. This atomicity has to be enforced through a full table lock (which is often the easiest to implement, but also the most expensive efficiency-wise) or serialization. Side note: a cross-database test showed that Oracle, DB2 nor MySQL (with InnoDB storage engine) run the query as true repeatable read, whether one makes use of the "skip locked rows" (when available) or not. One always ends up with two different rows being updated. Best regards, Steve Caligo -- 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 #4955: ECPG produces incomplete code
The following bug has been logged online:
Bug reference: 4955
Logged by: Heiko Folkerts
Email address: heiko.folke...@david-bs.de
PostgreSQL version: 8.4
Operating system: Windows XP
Description:ECPG produces incomplete code
Details:
When compiling the following code with ecpg the result is missing some of
the embedded SQL code:
source in a pgc file:
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR synonymstring[500];
long ID;
char* keywordstring;
EXEC SQL END DECLARE SECTION;
QByteArray buf = Keyword.getKeyword().toUtf8();
keywordstring = buf.data();
ID = Keyword.getLanguageID().getID();
EXEC SQL DECLARE synonymcursor CURSOR FOR SELECT synonym FROM
modelisar_data.tfsssynonym s, modelisar_data.tfsskeyword k WHERE k.id =
s.original_id AND k.keyword = :keywordstring AND k.catalog_id = :ID AND
synonym IS NOT NULL;
int a;
EXEC SQL OPEN synonymcursor;
int b;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
int c;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while(true)
{
EXEC SQL FETCH NEXT FROM synonymcursor INTO
:synonymstring;
Result in C-File:
/* exec sql begin declare section */
#line 65 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
struct varchar_synonymstring_65 { int len; char arr[ 500 ]; }
synonymstring ;
#line 66 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
long ID ;
#line 67 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
char * keywordstring ;
/* exec sql end declare section */
#line 68 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
QByteArray buf = Keyword.getKeyword().toUtf8();
keywordstring = buf.data();
ID = Keyword.getLanguageID().getID();
/* declare synonymcursor cursor for select synonym from modelisar_data .
tfsssynonym s , modelisar_data . tfsskeyword k where k . id = s .
original_id and k . keyword = $1 and k . catalog_id = $2 and synonym is
not null */
#line 72 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
int a;
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare synonymcursor
cursor for select synonym from modelisar_data . tfsssynonym s ,
modelisar_data . tfsskeyword k where k . id = s . original_id and k .
keyword = $1 and k . catalog_id = $2 and synonym is not null",
ECPGt_char,&(keywordstring),(long)0,(long)1,(1)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_long,&(ID),(long)1,(long)1,sizeof(long),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);}
#line 74 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
int b;
/* exec sql whenever sql_warning sqlprint ; */
#line 76 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
int c;
/* exec sql whenever not found break ; */
#line 78 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
while(true)
{
{ ECPGdo(__LINE__, 0, 1, NULL, 0,
ECPGst_normal, "fetch next from
synonymcursor", ECPGt_EOIT,
ECPGt_varchar,&(synonymstring),(long)500,(long)1,sizeof(struct
varchar_synonymstring_65),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 81 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
#line 81 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
if (sqlca.sqlwarn[0] == 'W') sqlprint();}
#line 81 "../../repository/TSSDB/TSSDBPGAccessor.pgc"
It seems that the open command for the cursor is not present. The first
fetch passes but with wrong data in the host variables.
--
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] fix: plpgsql: return query and dropped columns problem
Jaime Casanova writes: >> On Mon, Jul 20, 2009 at 10:09 AM, Alvaro >>> Getting rid of the check on natts was "ungood" ... it needs to compare >>> the number of undropped columns of both tupdescs. > patch attached This patch is *still* introducing more bugs than it fixes. The reason is that it has modified validate_tupdesc_compat to allow the tupdescs to be somewhat different, but has fixed only one of the several call sites to deal with the consequences of that. The others will now become crash risks if we apply it as-is. What I would suggest as a suitable plan for a fix is to modify validate_tupdesc_compat so that it returns a flag indicating whether the tupdesc compatibility is exact or requires translation. If translation is required, provide another routine that does that --- probably using a mapping data structure set up by validate_tupdesc_compat, since in some of these cases we'll be processing many tuples. Then the callers just have to know enough to call the tuple-translation function when validate_tupdesc_compat tells them to. There are a number of other places in the system with similar requirements, although none of them seem to be exact matches. In particular ExecEvalConvertRowtype() provides a good template for efficient translation logic, but it's using column name matching rather than positional matching so you couldn't share the setup logic. I'm not sure if it's worth moving all this code into the core so that it can be shared with other future uses (maybe in other PLs), but it's worth considering that. access/common/heaptuple.c or tupdesc.c might be a good place for it if we decide to do that. The executor's junkfilter code is pretty nearly related as well, and perhaps the Right Thing is to make all of this stuff into applications of junkfilters with different setup routines for the different requirements. However the junkfilter code is designed to work with tuples that are in TupleTableSlots, which isn't particularly helpful for plpgsql's uses, so maybe trying to unify with that code is more trouble than it's worth. I'm marking this patch as Waiting on Author again, although perhaps Returned with Feedback would be better since my suggestions amount to wholesale rewrites. 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
Fwd: [BUGS] BUG #4953: Crash with xml functions
Hi, I’m forwarding this mail exchange with Peter Eisentraut to the bugs list. pgSQL version: 8.4 OS: Mac OS X 10.5.8 Let me know any needed additional detail or how I can help, thank you in advance. Da: Peter Eisentraut Data: 29 luglio 2009 21:43:10 GMT+02:00 A: Giorgio Valoti Oggetto: Re: [BUGS] BUG #4953: Crash with xml functions Please send it to the bugs list. On Wednesday 29 July 2009 21:47:59 you wrote: Il giorno 29/lug/09, alle ore 11:26, Peter Eisentraut ha scritto: On Wednesday 29 July 2009 11:39:03 Giorgio Valoti wrote: I get a crash when I invoke xml function like xml_is_well_formed. It works with static xml but not if it’s dynamically generated. It crashes even if I first create the xml dynamically and then invoke the function with static text. Please provide a reproducible test case. select xml_is_well_formed(xmlelement(name root)::text); mmh, this is too easy! There must be something wrong with my setup. Any clues? Grazie -- Giorgio Valoti -- 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] fix: plpgsql: return query and dropped columns problem
2009/7/30 Tom Lane : > Jaime Casanova writes: >>> On Mon, Jul 20, 2009 at 10:09 AM, Alvaro Getting rid of the check on natts was "ungood" ... it needs to compare the number of undropped columns of both tupdescs. > >> patch attached > > This patch is *still* introducing more bugs than it fixes. The reason > is that it has modified validate_tupdesc_compat to allow the tupdescs to > be somewhat different, but has fixed only one of the several call sites > to deal with the consequences of that. The others will now become crash > risks if we apply it as-is. > > What I would suggest as a suitable plan for a fix is to modify > validate_tupdesc_compat so that it returns a flag indicating whether the > tupdesc compatibility is exact or requires translation. If translation > is required, provide another routine that does that --- probably using a > mapping data structure set up by validate_tupdesc_compat, since in some > of these cases we'll be processing many tuples. Then the callers just > have to know enough to call the tuple-translation function when > validate_tupdesc_compat tells them to. > ook I'll to try modify patch in this direction. Pavel > There are a number of other places in the system with similar > requirements, although none of them seem to be exact matches. > In particular ExecEvalConvertRowtype() provides a good template for > efficient translation logic, but it's using column name matching > rather than positional matching so you couldn't share the setup logic. > I'm not sure if it's worth moving all this code into the core so that > it can be shared with other future uses (maybe in other PLs), but it's > worth considering that. access/common/heaptuple.c or tupdesc.c might > be a good place for it if we decide to do that. > > The executor's junkfilter code is pretty nearly related as well, and > perhaps the Right Thing is to make all of this stuff into applications > of junkfilters with different setup routines for the different > requirements. However the junkfilter code is designed to work with > tuples that are in TupleTableSlots, which isn't particularly helpful for > plpgsql's uses, so maybe trying to unify with that code is more trouble > than it's worth. > > I'm marking this patch as Waiting on Author again, although perhaps > Returned with Feedback would be better since my suggestions amount > to wholesale rewrites. > > 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 #4945: Parallel update(s) gone wild
"Dan Boeriu" writes: > Attached is the reproducible test case - I was able to reproduce the problem > on 32 and 64 bit 8.3.6 and 8.4.0 RedHat 5.3 kernel 2.6.18-128.1.16.el5 #1 SMP I looked at this a bit. It's the same issue discussed at http://archives.postgresql.org/pgsql-bugs/2008-09/msg00045.php namely, that the second update finds itself trying to update a large number of tuples that were already updated since its snapshot was taken. That means it has to re-verify that the updated versions of those tuples meet its WHERE qualification. That's done by a function EvalPlanQual that's pretty darn inefficient for complex queries like this one. It's essentially redoing the join (and recomputing the whole sub-SELECT) for each row that needs to be updated. Someday I'd like us to redesign that mechanism, but don't hold your breath ... 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 #4945: Parallel update(s) gone wild
Is there a workaround? To us this is pretty bad news; we receive updates from several partners and constantly update the counts like in the example I sent you... Obviously we can serialize the updates but that would be pretty sad thing to do in a database. Realistically - when will we see this fixed (I understand it has pretty low priority...) ? Thanks a bunch for your time, Dan Boeriu Senior Architect - Roost.com P: (415) 742 8056 Roost.com - 2008 Inman Award Winner for Most Innovative New Technology -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thu 7/30/2009 2:34 PM To: Dan Boeriu Cc: Robert Haas; Craig Ringer; PostgreSQL bugs Subject: Re: [BUGS] BUG #4945: Parallel update(s) gone wild "Dan Boeriu" writes: > Attached is the reproducible test case - I was able to reproduce the problem > on 32 and 64 bit 8.3.6 and 8.4.0 RedHat 5.3 kernel 2.6.18-128.1.16.el5 #1 SMP I looked at this a bit. It's the same issue discussed at http://archives.postgresql.org/pgsql-bugs/2008-09/msg00045.php namely, that the second update finds itself trying to update a large number of tuples that were already updated since its snapshot was taken. That means it has to re-verify that the updated versions of those tuples meet its WHERE qualification. That's done by a function EvalPlanQual that's pretty darn inefficient for complex queries like this one. It's essentially redoing the join (and recomputing the whole sub-SELECT) for each row that needs to be updated. Someday I'd like us to redesign that mechanism, but don't hold your breath ... regards, tom lane
Re: Fwd: [BUGS] BUG #4953: Crash with xml functions
Giorgio Valoti wrote: > Hi, > I’m forwarding this mail exchange with Peter Eisentraut to the bugs > list. > pgSQL version: 8.4 > OS: Mac OS X 10.5.8 I can reproduce this, and the reason seems to be the same problem we fixed in core XML and that Tom was saying a couple of days ago that was present in contrib/xml2 as well: #0 0x7f1ac590f065 in *__GI_raise (sig=) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64 #1 0x7f1ac5912153 in *__GI_abort () at abort.c:88 #2 0x0078cbcf in ExceptionalCondition ( conditionName=0x920548 "!(((header->context) != ((void *)0) && (Node*)((header->context)))->type) == T_AllocSetContext", errorType=0x920421 "BadArgument", fileName=0x9203a0 "/pgsql/source/84_rel/src/backend/utils/mmgr/mcxt.c", lineNumber=589) at /pgsql/source/84_rel/src/backend/utils/error/assert.c:57 #3 0x007b2862 in pfree (pointer=0x120bfc0) at /pgsql/source/84_rel/src/backend/utils/mmgr/mcxt.c:589 #4 0x7f1ac345e186 in pgxml_pfree (ptr=0x120bfc0) at /pgsql/source/84_rel/contrib/xml2/xpath.c:86 #5 0x7f1ac66da12b in xmlCleanupCharEncodingHandlers__internal_alias () at encoding.c:1398 #6 0x7f1ac66e3103 in xmlCleanupParser__internal_alias () at parser.c:13836 #7 0x7f1ac345e5c5 in xml_is_well_formed (fcinfo=0x7fffcec1b0e0) at /pgsql/source/84_rel/contrib/xml2/xpath.c:187 -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
