Re: [HACKERS] [GENERAL] table and column information from cursor?
John DeSoi [EMAIL PROTECTED] writes:
I was wondering if there is some way I'm missing to get the table and
column information from a cursor. If I fetch from a cursor, the table
OID and column number values are 0 in the row description. If I execute
the same query directly without a cursor, the row description has the
correct values for table OID and column number. I'm using the v3
protocol via a socket with PostgreSQL 8.0.
This looks a bit messy to fix. The information exists in the Portal
where the cursor itself is stored, but the FETCH is executed in a
different Portal that does not have it. If you trace through the code
you find the immediate failure in printtup_startup, which only knows how
to dig the targetlist out of a plain SELECT Query:
/*
* If this is a retrieve, and we are supposed to emit row
* descriptions, then we send back the tuple descriptor of the tuples.
*/
if (operation == CMD_SELECT myState-sendDescrip)
{
List *targetlist;
if (portal-strategy == PORTAL_ONE_SELECT)
targetlist = ((Query *) linitial(portal-parseTrees))-targetList;
else
targetlist = NIL;
SendRowDescriptionMessage(typeinfo, targetlist, portal-formats);
}
We could probably kluge this code to be able to recognize a FETCH as
well, and then somehow dig the targetlist out of the cursor's portal,
but it sounds pretty ugly ... and it surely wouldn't scale to other
sorts of utility statements that return tuples, although offhand I can't
think of any for which there'd be a clear connection of returned columns
to table columns.
One way to fix this would be to pass the cursor's Portal, and not the
FETCH command's Portal, to the DestReceiver --- not sure how messy that
would be. Otherwise it seems like we need a cleaner way of decorating a
Portal with original-column info, so that a FETCH command could arrange
to copy the info into the Portal where it's executing.
More generally, it may be that attaching the original-column info to
targetlist entries was a bad idea in the first place, and we ought to
keep it someplace else ... not sure where would be better though.
Thoughts anyone?
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] prepared statements don't log arguments?
Oliver Jowett [EMAIL PROTECTED] writes: Simon Riggs wrote: OK, thats what I hoped you'd say. With a prepared query all of the statements execute the same plan, so you don't need to know the exact parameters. This isn't true in 8.0 if you are using the unnamed statement (as the JDBC driver does in some cases): the plan chosen depends on the parameter values given in the first Bind. Oliver, Yes, I was aware of that, but thought it would confuse the issue. I agree that it would be ideal if the parameter values from the first Bind were also logged. However, you don't often need the parameters to do performance tuning. Initial profiling groups similar statements together to find the hot spots. We may find other problems like incorrect SQL, missing join clauses, missing WHERE clauses, need-an- index etc. Most of which can be done without seeing the exact parameters. Even if you suspect a wild first bind parameter as the cause of performance problems, this is still fairly easy to trace - the question of what do you do about it isn't helped a great deal by knowing what the value is. On Fri, 2005-04-08 at 03:11 -0400, Tom Lane wrote: Also, what plan got chosen isn't the only question that a DBA might want the log to answer. Where did my data get screwed up is at least as likely an application. I'm a bit worried about the costs of converting binary-format parameters into text form ... Tom If we have separate requirements, surely they are best met with separate GUC parameters. For performance analysis purposes we only need to see the first parameter set, if ever; but we never need to see all of the parameters. If we had a log_parameters statements with options: log_parameters = none | first | all This would give you the capability to log the data as well, if you required this. As you point out, there would be performance implications to logging all of the parameters including both CPU overhead and log volume. There is also another implication of Data Protection, since you wouldn't necessarily want to show all people seeing the log your data details. Anyway, I don't personally see a need or benefit to log parameters in any case, so I'm happy if anybody wants to raise a TODO item from this, but its not me. I've got a patch to submit that logs the EXEC phase, so you get just the SQL, not the parameters. When we last spoke about this [on ADMIN during Feb] you mentioned that one of the main reasons that this was not done before was people couldn't agree exactly how to proceed. In the meantime, logging just the SQL takes us 95% of the way along the road we wish to travel. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Unicode problems on IRC
Christopher Kings-Lynne wrote: Hey guys, The 'Unicode characters above 0x1' issue keeps rearing its ugly head in the IRC channel. I propose that it be fixed, even backported... This is John Hansen's most recent patch to fix it: http://archives.postgresql.org/pgsql-patches/2004-11/msg00259.php And from what I can tell it was committed, then reverted because it wasn't a bug. It was going to go in for 8.1. We on the channel are starting to think that it is in fact a bug. There are are people with legitimately utf-8 encoded XML documents that they cannot store in PostgreSQL. Apparently in the distant past, Unicode was limited to 0x1, but then was extended. Perhaps we can reopen this case... Uh, I thought we fixed this another way, buy not using Unicode-aware functions for upper/lower/initcap when the locale is C or POSIX. That is backpatched to 8.0.X. Does that not fix the problem reported? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Unicode problems on IRC
On 2005-04-09, Bruce Momjian pgman@candle.pha.pa.us wrote: Uh, I thought we fixed this another way, buy not using Unicode-aware functions for upper/lower/initcap when the locale is C or POSIX. That is backpatched to 8.0.X. Does that not fix the problem reported? Unicode values over 0x are simply not accepted on input, so no, it doesn't fix the problem. What do upper/lower/initcap have to do with it? textin() unconditionally calls pg_verifymbstr, which in turn explicitly checks for such values (if the encoding is UTF8) and throws ERROR if it finds them. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Unicode problems on IRC
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: Sunday, April 10, 2005 8:18 AM To: Christopher Kings-Lynne Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Unicode problems on IRC Christopher Kings-Lynne wrote: Hey guys, The 'Unicode characters above 0x1' issue keeps rearing its ugly head in the IRC channel. I propose that it be fixed, even backported... This is John Hansen's most recent patch to fix it: http://archives.postgresql.org/pgsql-patches/2004-11/msg00259.php And from what I can tell it was committed, then reverted because it wasn't a bug. It was going to go in for 8.1. We on the channel are starting to think that it is in fact a bug. There are are people with legitimately utf-8 encoded XML documents that they cannot store in PostgreSQL. Apparently in the distant past, Unicode was limited to 0x1, but then was extended. Perhaps we can reopen this case... Uh, I thought we fixed this another way, buy not using Unicode-aware functions for upper/lower/initcap when the locale is C or POSIX. That is backpatched to 8.0.X. Does that not fix the problem reported? No, as andrew said, what this patch does, is allow values 0x and at the same time validates the input to make sure it's valid utf8. ... John -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(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: [HACKERS] Unicode problems on IRC
John Hansen [EMAIL PROTECTED] writes: That is backpatched to 8.0.X. Does that not fix the problem reported? No, as andrew said, what this patch does, is allow values 0x and at the same time validates the input to make sure it's valid utf8. The impression I get is that most of the 'Unicode characters above 0x1' reports we've seen did not come from people who actually needed more-than-16-bit Unicode codepoints, but from people who had screwed up their encoding settings and were trying to tell the backend that Latin1 was Unicode or some such. So I'm a bit worried that extending the backend support to full 32-bit Unicode will do more to mask encoding mistakes than it will do to create needed functionality. Not that I'm against adding the functionality. I'm just doubtful that the reports we've seen really indicate that we need it, or that adding it will cut down on the incidence of complaints :-( regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Unicode problems on IRC
Tom Lane wrote: John Hansen [EMAIL PROTECTED] writes: That is backpatched to 8.0.X. Does that not fix the problem reported? No, as andrew said, what this patch does, is allow values 0x and at the same time validates the input to make sure it's valid utf8. The impression I get is that most of the 'Unicode characters above 0x1' reports we've seen did not come from people who actually needed more-than-16-bit Unicode codepoints, but from people who had screwed up their encoding settings and were trying to tell the backend that Latin1 was Unicode or some such. So I'm a bit worried that extending the backend support to full 32-bit Unicode will do more to mask encoding mistakes than it will do to create needed functionality. Yes, that was my impression too. The upper/lower/initcap issue was that some operating systems were testing unicode values even if the local was set to C. That is fixed in 8.0.2, but I now see this is a different problem. Not that I'm against adding the functionality. I'm just doubtful that the reports we've seen really indicate that we need it, or that adding it will cut down on the incidence of complaints :-( Yea, that was my question too. I figured Japan or Chinese would be using these longer values, and if they are fine, why are others having problems. It would be great to find a test case that actually shows the failure. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] prepared statements don't log arguments?
Simon Riggs wrote: I've got a patch to submit that logs the EXEC phase, so you get just the SQL, not the parameters. [...] I assume this replaces the current logging on Parse to avoid duplicate logging? What happens on syntax errors? It's useful to log the statement that failed, but you will need some trickery there since if the Parse logging goes away, we won't have logged anything at the point the error is generated. -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
