[SQL] Special characters in SQL queries
First a single quote in text, when a backforward slash in a file path, what other special characters need padded in two backslashes before using the data in a SQL statement? After hours' search in the PostgreSQL archive, I find a releted information on the http://www.ca.postgresql.org/users- lounge/docs/7.2/postgres/arrays.html. It's about array data though. How can I find out those characters so that I can pre-process them before a SQL query? Thanks for your information. Vernon ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pl/pgsql how to return multiple values from a function
Stephan , Both of two suggestion work. Thank you very much! Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] About Create Type
Thanks Joe. Yes, that is exactly what I need. Jack ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pl/pgsql how to return multiple values from a function
Thanks Rajesh. It 's very useful reference site. Jack ---(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
[SQL] Cursors and backwards scans and SCROLL
Postgres' implementation of cursors has always had a problem with doing MOVE or FETCH backwards on complex queries. It works okay for simple seqscans and indexscans, but fails for plans involving joins, aggregates, and probably other cases. This happens because the executor routines for those plan types don't cope with being asked to scan backwards. Fixing this directly seems unreasonably difficult, so I'm currently working on fixing it by inserting a Materialize plan node at the top of the plan tree for a cursor, if the plan tree couldn't otherwise support backwards scan. The Materialize node will save aside a copy of each row as it's fetched from the underlying plan, and use this copied table if any backwards scanning is asked for. Of course, copying the data is a waste of cycles if the client never actually asks to scan backwards --- and it could even lead to failures, i.e. running out of disk space. So I think there needs to be a way to control whether Materialize gets added or not. The SQL spec has a provision for this: according to the spec, the client is supposed to say DECLARE foo SCROLL CURSOR FOR ... if he intends to do anything except sequential fetches from the cursor. Postgres presently allows the SCROLL keyword but ignores it. I'd like to set things up so that Materialize is added only when SCROLL appears (and the query plan can't handle backwards scan without it). However, this is going to create backwards-compatibility issues. We have a few options for what to do: 1. Enforce the SQL spec requirement: error out if backwards fetch is done when SCROLL wasn't given. But this will surely break a lot of existing applications that work perfectly well. 2. Error out only if a backwards fetch is actually attempted on a plan tree that can't handle it (which could only happen if SCROLL wasn't given). This is efficient and flexible, but it exposes implementation details to the user, in that whether an error occurs will depend on which plan the system happens to choose. There are cases where identical cursor definitions might allow or not allow backwards fetch depending on the planner's choices. Notice though that errors could occur only in cases that would silently fail in the present code; so existing applications that work reliably would not see such errors. 3. Create a runtime parameter (GUC variable) which when set causes us to assume SCROLL is present even if it's not stated. Setting this to TRUE would allow existing applications to work without modification; when it's FALSE, we'd enforce the spec behavior. The trouble with this is the TRUE setting would likely cause materialization costs to be paid in very many situations where the client has no intention of fetching backwards. I'm presently leaning to #2, even though it exposes implementation details. I'm open to discussion though. Any preferences? Other ideas? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Cursors and backwards scans and SCROLL
Tom, > Postgres' implementation of cursors has always had a problem with doing > MOVE or FETCH backwards on complex queries. Coincidnetally enough, I was just chatting with one of my contractors yesterday about how the one thing that Transact-SQL has to offer is a really good cursor implementation. It would be lovely to improve ours to match. > Fixing this directly seems unreasonably difficult, so I'm currently > working on fixing it by inserting a Materialize plan node at the top of > the plan tree for a cursor, if the plan tree couldn't otherwise support > backwards scan. The Materialize node will save aside a copy of each row > as it's fetched from the underlying plan, and use this copied table if > any backwards scanning is asked for. Sounds good to me. It's also very similar to what T-SQL does for a STATIC or KEYSET cursor, and works very well in their implementation. (FWIW, T-SQL's cursor types, such as DYNAMIC and KEYSET, are unnecessary for Postgres due to MVCC) > 2. Error out only if a backwards fetch is actually attempted on a plan > tree that can't handle it (which could only happen if SCROLL wasn't > I'm presently leaning to #2, even though it exposes implementation > details. I'm open to discussion though. Any preferences? Other ideas? This sounds like a good idea to me in a staggered-implementation sense if it's doable. That is, we'd implement the behavior in #2 in the next version of Postgresql, and the behavior in #1 or in #3 in the version after that.If, however, the implementation of #2 is too difficult, then I think #3 would be a good choice. >From my perspective, the "SCROLL" declaration has *always* been the SQL-spec, and it is the behaviour used by other databases, even if it's been superflous in PostgreSQL until now. So from that point of view, developers who have been not using "SCROLL" have been sloppy and can reasonably expect to have to audit their code in future versions of PostgreSQL. On the other hand, I don't use cursors much in Postgres, so I'm kind of a priest doing marriage counselling as far as that's concerned. PL/pgSQL's "FOR record IN query" is currently both easier and faster than cursors so I use that 90% of the time. -- Josh Berkus Aglio Database Solutions San Francisco ---(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: [SQL] Cursors and backwards scans and SCROLL
> I'm presently leaning to #2, even though it exposes implementation > details. I'm open to discussion though. Any preferences? Other ideas? How about a variable that turns on or off spec enforcement (case #1 or #2). On for 7.4, off for 7.5 the next release, and make it disappear after that. Enforcing spec seems like the least confusing mode to operate under, especially given it could break simply by changing the plan -- which happens automagically (seemingly random). -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Cursors and backwards scans and SCROLL
Rod Taylor <[EMAIL PROTECTED]> writes: > Enforcing spec seems like the least confusing mode to operate under, > especially given it could break simply by changing the plan -- which > happens automagically (seemingly random). Keep in mind though that complaints about the current bugs have been fairly infrequent, which means that most people either don't try to fetch backwards from a cursor, or don't try to do so on complex plans. I'm hesitant to penalize a larger group for the benefit of a smaller one --- which is why enforcing the spec strictly doesn't really appeal to me, even though one could argue that the larger group had it coming. I'd prefer to be permissive about the cases that we can support at no cost, which not-by-coincidence are the cases that have worked correctly up to now. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [HACKERS] Cursors and backwards scans and SCROLL
On Sun, 2003-03-09 at 16:04, Rod Taylor wrote: > How about a variable that turns on or off spec enforcement (case #1 or > #2). On for 7.4, off for 7.5 the next release, and make it disappear > after that. Yeah, that sounds like the best solution to me. IMHO there is a case to be made for skipping straight to defaulting to 'on' in 7.4, and removing the GUC var in 7.5 Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(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
