Re: [GENERAL] Strict-typing benefits/costs
Tom Lane wrote:
Ken Johanson <[EMAIL PROTECTED]> writes:
select 5<'6' -> true
select 5>'6' -> false
select 15<'60' -> true
select 15>'60' -> false
These examples miss the point, because they'd give the same answer
whether you think the values are text or integer. Consider instead
these cases:
regression=# select 7 > '60'; -- int > int
?column?
--
f
(1 row)
regression=# select '7' > '60'; -- text > text
?column?
--
t
(1 row)
regression=# select 7 > '08'; -- int > int
?column?
--
f
(1 row)
regression=# select '7' > '08'; -- text > text
?column?
--
t
(1 row)
All of a sudden it seems much more important to be clear about
what data type is involved, no?
Agreed, so should we disallow 7 > '08'? Because that is (tell me if you
disagree), much more hazardous than allowing, say TRIM(7) or
POSITION('7' IN 7). Or for non-failfast comparison of two columns of
dissimilar types (say bigint, integer, real, char).
select 'ba'>'ab' -> true
select 'ab'>'ba' -> false
select '0.5'=.5 -> true (is char comparator or numeric to laymen?)
select '7a'<'070' -> true (is char comparator or numeric to laymen?)
select '7a'<70 -> failfast, good.
Numbers and datetime in sql have exactly prescribed standard char
representations (even if others dbs don't use them for datetimes).
See the datestyle parameter before you maintain that Postgres
should assume that.
I agree. Unless the date style is know to always be iso8601, which is
not true owed to datestyle. Unless sql spec allows for it, could this be
an argument for removing the datestyle implict (non-iso8601) feature?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Strict-typing benefits/costs
Michael Glaesemann wrote:
On Feb 15, 2008, at 18:11 , Ken Johanson wrote:
Tom, is it accurate to assume that newer PG versions will further
tighten type-strictness (say, '2008-01-01' presently being comparable
to a datetime)? Also, do you know of any other vendors that are
heading in this direction (removing by default the autocasts)?
'2008-01-01' does not indicate some kind of string: it's just an untyped
literal. Postgres will determine its type in context.
Exactly, it is performing a context based auto conversion, what some
will call a cast.
select 5<'6' -> true
select 5>'6' -> false
select 15<'60' -> true
select 15>'60' -> false
So one can argue that is is convenient, and safe, to perform the same
implicit/auto conversion for many functions which no longer do that. And
that even if looses-typing / auto cast it allows/encourages bad design,
that does not mean that the all designs cases will be bad. Some users
prefer convenience over type safety, and some of those same users *will*
produce error free code.
On the other hand, should we go the extra mile and failfast when
comparing 5 and '6'? No, because there is clearly only one appropriate
conversion path (cast string to numeric) for the context. Or, some might
argue we should not allow that comparison.
select position('5' in 5)
select position('.' in 5.1)
select position('2008' in current_timestamp)
Numbers and datetime in sql have exactly prescribed standard char
representations (even if others dbs don't use them for datetimes). So
one can argue implicit conversion to char IS safe for these types and
any char-consuming functions.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Tom Lane wrote: Hm, good point, so really we ought to have a separate casting path for numeric types to char(n). However, this section still doesn't offer any support for the OP's desire to auto-size the result; it says that you get an error if the result doesn't fit in the declared length: iv) Otherwise, an exception condition is raised: data exception- string data, right truncation. I don't believe the size is being declared in the OP's (subject line) example: SELECT CAST(123 AS char) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Strict-typing benefits/costs
Tom Lane wrote: It's possible to special-case any particular function you really feel you need this behavior for. We did special-case || (the string concatenation operator), and there was some discussion of also putting in a built-in special case for LIKE, but we desisted from sliding any further down that slippery slope. Since it's possible for users to install such hacks for themselves, as in the example here, http://archives.postgresql.org/pgsql-general/2007-11/msg00538.php there didn't seem to be a convincing case for weakening the type checking for everybody. Tom, is it accurate to assume that newer PG versions will further tighten type-strictness (say, '2008-01-01' presently being comparable to a datetime)? Also, do you know of any other vendors that are heading in this direction (removing by default the autocasts)? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Strict-typing benefits/costs
Jeff Davis wrote: If postgresql were to revert to 8.2 implicit casting behavior, would that actually improve compatibility with other DBMSs? Every DBMS probably has it's own rules for implicit casting, different from every other DBMS. So are you sure it wouldn't just introduce more compatibility problems somewhere else? Or worse, it could hide the problems during migration/testing, and they could surface after you put it into production. In my opinion the autocasting behaviors of the database are probably more consistent and fined tuned, than their explicit cast function. Or in the least, they may actually *save* mistakes that (lay) programmers would make (by adding casts for the sake of PG). ---(end of broadcast)--- TIP 1: 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
[GENERAL] Strict-typing benefits/costs
I continue to hope that 8.4 later (or 8.3 patched) will provide behavior
/ compatibility controls..
I personally have requested others in past, but one outstanding one now,
seems to be an ability to turn back-on implicit type conversion. I'm
struggling to see Mysql, Mssql, Oracle, and others following PG and
turning off their implicit conversion. People will have to choose
whether to re-code (explicit casts) their apps for sake of PG.
Granted, some of the other databases have bugs in their CAST
implementations (see http://bugs.mysql.com/bug.php?id=34562 &
http://bugs.mysql.com/bug.php?id=34564) bugs which makes writing PG
8.3 portable code arduous or impossible when dealing with legacy table
designs and program which counted on implicit casts. But even when mysql
gets fixed, one will ask: "should I re-code my apps just so they will
work with PG>=8.3 (and test the code on other DBs), or should I only
support PG<8.3?
From
http://www.postgresql.org/docs/8.3/static/release-8-3.html:
"these expressions formerly worked:
1) substr(current_date, 1, 4)
2) 23 LIKE '2%'
but will now draw "function does not exist" and "operator does not
exist" errors respectively. Use an explicit cast instead.
3) current_date < 2017-11-17
"
(the section also prominently cites a non-portable cast syntax)
Questions:
For case 1, regarding type safety: we know use of LIKE (and SUBSTR)
requires *implicit or explicit* conversion to a text type. Why require
that explicitly? Closet answer I can see, is that for (example #1)
text-formed dates in some servers are not iso8601 format. But, even
explicit datetime->text cast has this same hazard -- it doesn't require
a format argument.
For case #2, explicit or implicit again have same risk: that base-10 and
with/out decimal, should be the default format.
For case #3, I see "in the presence of automatic casts both sides were
cast to text", but can that not be fixed? Operand 2 yields an integer,
and integer and date compares should failfast, they are not comparable.
(I believe sql requires delimited iso8601 fields, so both 2008041412
and '2008041412' should failfast)
int compare(datetime a, object b)
{
if (typeof(b=="charsequence")
return compare(cast(b as datetime), a);
if (typeof(b)=="date")
return compare(cast(b as datetime), a);
if (typeof(b)=="time") //allowed?
return compare(cast(b as datetime), a);
throw "cannot compare "+a.getClass()+" and "+b.getClass();
}
WHERE (current_date < 2017-11-17) -> FAIL
WHERE (current_date < CAST(2017-11-17 AS datetime)) -> FAIL
WHERE (current_date < '2017-11-17') -> PASS
WHERE (current_date < CAST('2017-11-17' AS datetime)) -> PASS
Sure, we know stricter typing will help performance, by encouraging
more-correct design. For example it may be prudent to ALWAYS failfast
when trying to join key/indexed table columns of differing types (one
char to another's int). But on non-key where conditions? Or make that a
behavior option. Perhaps An SQLWarning could be set when mismatches
occur, yet make a best effort at autocast.
A char to int key comparison affects performance horridly and indicates
(though not always) a potential mis-design, yet it MAY be safe to
autocast the text to numeric before compare, because the narrowing
conversion will assert that only base-10 chars exist.
So, are there other examples of why, esp for runtime/type safety, we
should force explicit conversion?
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Richard Huxton wrote: What I couldn't figure out was what type MySQL was using. I mean, what type is this? mysql> SELECT cast(a as char) as achar FROM tt; +---+ | achar | +---+ | 1 | | 10| +---+ 2 rows in set (0.00 sec) Is it char(2)? mysql> CREATE TEMPORARY TABLE ttchar0 AS SELECT cast(a as char) as achar FROM tt; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> describe ttchar0; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | achar | varchar(11) | YES | | NULL| | +---+-+--+-+-+---+ TA DAH! It looks like you are casting to varchar anyway Ken, it's just that MySQL isn't letting you spell it properly. So does an unconstrained "char" just map to varchar with MySQL then? I think the issue here (subjective) is: is unconstrained CAST(n AS char), a DDL statement in the storage sense, or in the return type/function sense? Thats how PG and the other's CAST differ, anyway. While the spec doesn't seem to qualify that and it's safe to assume 'char' type should just behave the same even in the context of cast + number, both Ms and My's CAST treat numeric inputs as auto-size on select stmts. Add to that, that Mysql does the auto-trim thing which might be affecting/confusing some operations (inserts for starters). I don't know. Surprisingly I've never had a user complain about that trim spec-deviation, nor case-insens compares (though I always teach/code case-folding for portability). Richard, when you say "casting to varchar anyway", it's not possible by syntax (shortcoming in their current cast impl), so using char was a next best thing to try. Your test ultimately applies I think to a table DDL and not select. Anyway, there are to many barriers (real or imagined) for my users to migrate to PG from My and Ms (AS-less labels, result set metadata, auto-generated keys, now stricter typing) so I've already resolved to check back and see how things look when 8.4 comes out. Maybe it'll be more spec compliant... a good thing, for one DB ..or another.. I'm patient and an old pro at this, I've been evaluating PG for about 9 years now, and despite the amazing number of features and advantages it's always had, customers come out in favor of the other DBs because they only need a small subset of them, and rate convenience (ala autocast) over specs which they know nothing of. Well, at least we have standard_conforming_strings now. I digress and am touching on another thread someone brought up. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Alvaro Herrera wrote: If you are arguing that the spec's definition of the CHARACTER type is not really very useful, I think you are going to find a lot of supporters. You can send your complaints to the SQL committee; but then, it is unlikely that this is going to change anytime soon because of the fear of breaking backwards compatibility. Agreed. There may be allot of users who in the case of CAST, need to extract the leftmost digit, and for those database and their users that do this by default, it would cause harm. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Andrew Sullivan wrote: No, you're trying to convey that it is more benign/useful _to you_. Others are arguing that they want to write conformant code, and don't much care what MyOccasionallyReadTheSpec does. It's a pity that SQL conformance is not better across systems, but surely the way to improve that is to reduce the number of products that are being careless, not to increase them? Let's leave it as-is then. We'll conform to the spec, and this good since someone may want to extract the leftmost char from a base-10 number representation. Henceforth SELECT CAST(123 AS char) will and should undisputedly return '1'. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Dean Gibson (DB Administrator) wrote: Then I don't understand. While I've never used MySQL, the MySQL web pages apparently indicate that VARCHAR has been supported since version 3.2: http://dev.mysql.com/doc/refman/4.1/en/char.html Only in DDL and not the cast function, apparently. Mysql 5.1 says: select cast(123 AS varchar) or select cast(123 AS varchar(10)) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar)' at line 1. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Dean Gibson (DB Administrator) wrote: On 2008-02-12 16:17, Ken Johanson wrote: Dean Gibson (DB Administrator) wrote: ... I'm guessing you declare an explicit length of 1 (for portability), or do you "CAST (x as char)"? And one might ask in what context we'd need CHAR(1) on a numeric type, or else if substr/ing or left() make the code more readable for other data types.. Actually, I just write "CHAR" for a length of 1. On a numeric type?.. That's the quintessential part to me... > What is wrong with using VARCHAR for your purpose Simply that a commonly used database (my) does not support it. By "my", do you mean "MySQL", or "MyDatabase"? If the latter, then while it's your business decision (and/or that of your customers), the availability of decent, free databases should make a compelling case for anyone using anything else, to migrate (and never look back) to something full-featured. Yes, Mysql, and yes, it's customer driven. It's like requiring portable C code to use the old, pre-ANSI style of function declarations, because some old C compilers might not support the ANSI style. I think Richard Stallman of the FSF takes that position, but I don't know of anyone else (although I'm sure there are exceptions). Point taken. This is really just a rock and hard place because I'm stuck between 3rd party products (customer API and database x^n). I'm trying to convey here that changing the behavior to a (numb AS varchar) one, practically speaking, is more benign/useful (vs now), even if that is only a non-spec workaround, and "everyone else does it" is an invalid arg. I'm much more concerned about the AS in column labels issue and some driver todos. The pre standard_conforming_strings behavior used to be the full show stopper for PG, and now I only hear smaller compatibility and ease of migration concerns (whether spec or defacto). Things are improving. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Tom Lane wrote: Simply that a commonly used database (my) does not support it. They do support char(n) in this context, which would have the advantage of being standards compliant as well as de-facto portable. Hmm, interesting. Mysql actual returns: select cast(123 AS char(10)) -> '123' (agreed wrong since length is explicit) And PG (Ms also) gives: select cast(123 AS char(10)) -> '123 ' (tested via jdbc driver since hard to visualize the space in psql) and PG: select '='||cast(123 AS char(10))||'=' -> '=123=' (test via psql) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Dean Gibson (DB Administrator) wrote: > On 2008-02-12 07:30, Ken Johanson wrote: >>> >>> Sure, but you're a prime candidate for understanding the value of >>> following the spec if you're trying to write software that works with >>> multiple databases. >> >> The spec has diminished in this (CAST without length) context: >> a) following it produces an output which has no usefulness whatsoever >> (123 != 1) > I *OFTEN* use a cast of CHAR to get just the first character. > I'm guessing you declare an explicit length of 1 (for portability), or do you "CAST (x as char)"? And one might ask in what context we'd need CHAR(1) on a numeric type, or else if substr/ing or left() make the code more readable for other data types.. >> b) all the other databases chose to not follow the spec in the context >> of cast and char with implicit length. > > I doubt that: > > http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/castsp.htm > http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx > The actual behavior is to autosize on MS and My. I do not have DB2 but would be curious to know how it behaves. > >> >> When the length is unqualified, a cast to char should one of: >> >> 1) failfast >> 2) auto-size to char-count (de facto) >> 3) pad to the max-length > > What is wrong with using VARCHAR for your > purpose Simply that a commonly used database (my) does not support it. I HAVE to support that one (too widely in use), better/worse, its not my choice. Even at the expense of its standards deviation, and for doubting the vendor(s) will the change behavior (and break the app). ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Gregory Stark wrote: "Ken Johanson" <[EMAIL PROTECTED]> writes: Tom Lane wrote: SQL92 section 6.1 quoth ::= CHARACTER [] | CHAR [] ... 4) If is omitted, then a of 1 is implicit. Therefore, writing just "char" is defined as equivalent to "char(1)". However when length is not defined I think it will generally be safe(r) to auto-size. In the grand scheme auto-size creates much more sensible output than a 1-char wide one (even if right-padded to max char-length of the type). Sure, but you're a prime candidate for understanding the value of following the spec if you're trying to write software that works with multiple databases. The spec has diminished in this (CAST without length) context: a) following it produces an output which has no usefulness whatsoever (123 != 1) b) all the other databases chose to not follow the spec in the context of cast and char with implicit length. When the length is unqualified, a cast to char should one of: 1) failfast 2) auto-size to char-count (de facto) 3) pad to the max-length It's a bit crazy to be using CHAR and then complaining about padding... I did say earlier that I could at least accept padding to the max-char length, even though in my use-case it wont work. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Tom Lane wrote: SQL92 section 6.1 quoth ::= CHARACTER [] | CHAR [] ... 4) If is omitted, then a of 1 is implicit. Therefore, writing just "char" is defined as equivalent to "char(1)". However when length is not defined I think it will generally be safe(r) to auto-size. In the grand scheme auto-size creates much more sensible output than a 1-char wide one (even if right-padded to max char-length of the type). Also, section 6.10 defines an explicit cast to a fixed-length string type as truncating or padding to the target length (LTD): And PG does this, perfectly. It even right-pads, the other databases (tried My and Ms) do not... Possibly you could get what you want by casting to char(10) or so. Alas the behavior is different. The right padding exists (in PG). So I cannot get uniform behavior (the other DB's fault I agree for not supporting cast as varchar). Unless PG can start throwing an exception in this version when it truncates to implicit-1, I think it should be forgiving and auto-size.. Is it possible to override this built-in cast function with a create-cast? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] SELECT CAST(123 AS char) -> 1
For sake of interoperability (and using an API that requires String-type hashtable keys), I'm trying to find a single CAST (int -> var/char) syntax that works between the most databases. Only char seems to be a candidate, but in 8.3 casting from an integer outputs only the first char... Is this a bug, or would someone like to horrify me by stating something like "spec says this is correct". :-) I noticed this is also occurring on date/time types though that's not my need/concern. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Set server behaviors on a per-session basis?
(re-post, last seemed not to get on-list) Hi all, In the link below we asked if it was possible to set, in this case, the standard_conforming_strings behavior, on a per-connection basis. http://www.grokbase.com/topic/2006/07/27/backslash-as-ordinary-char-vs-not-set-via-a-connection-session-variable/PwunvQ3dQxAVOpprZ606aKELsYU The responses were good to see -- the ability to set it on a role or database context is great.. however in some hosting / shared environments, one-role per behavior isn't always practical, even though it's arguably the ideal approach. A first question: will the current server design easily allow some change that lets us set behaviors (like standard_conforming_strings) on a per-connection basis? Or are per-connection behaviors a substantial rework? A second question: is support for a general notion of "behavior" on the roadmap for any of the three contexts (roles, database, sessions)? Not just standard_conforming_strings, but both conformant and non behaviors. Say accepting relaxed typing (quoted ints etc), implicit AS-keyword in SELECT lists (someday and in sacrifice of regclass), etc. Even to allow newer PGs to behave as old ones. These two features would ease migration *from* non-compliant/3rd party DBs -- and also to ease PG itself *into* standard-compliant modes (e.g 'implicit AS-keyword in SELECT lists'). Also may allow some blackbox apps to migrate from 3rd party DBs. -Ken ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Is PG a moving target?
Magnus Hagander wrote: PG uses a different versioning system than this one?: http://en.wikipedia.org/wiki/Software_versioning#Numeric Or do you mean the changes are not minor? :-) Yes, we use the one stated on our site, not wikipedia ;) See: http://www.postgresql.org/support/versioning Thank you, I understand now. "A major release is numbered by increasing either the first or second part of the version number, e.g. 8.1 to 8.2." ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Is PG a moving target?
Stephen Frost wrote: * Ken Johanson ([EMAIL PROTECTED]) wrote: But given the recent and dramatic example of 8.3's on-by-default stricter typing in functions (now not-autocasting), I worry that kind of change could happen in every minor version (8.4 etc). 8.3 isn't a minor version. PG uses a different versioning system than this one?: http://en.wikipedia.org/wiki/Software_versioning#Numeric Or do you mean the changes are not minor? :-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Is PG a moving target?
I acknowledge that from time to time we must accept changes in the 3rd party software that will break our apps if we (or customers) ever upgrade them (a compounded issue if we have heavily-used deployments in the field and not just in-house ones to maintain). But given the recent and dramatic example of 8.3's on-by-default stricter typing in functions (now not-autocasting), I worry that kind of change could happen in every minor version (8.4 etc). Sure the strict-typing (and other compatibility-breaking changes) is a good thing in the long run, but it discourages anyone trying to: a) port apps from another database b) upgrade PG to get other features, or port apps written against from a PG version that's 1 year older The type-strictness change, as an example, also creates pragmatic vs academic (polarizing) debates around "rtrim(intype)" being innocuous vs sloppy. And "database XYZ is better/worse", e.g balance of ease of use, TCO, vs ACID, strictness etc). The word 'balance' is key. Is there anything now, or in the works, for compatibility emulation? For example to setup my session to act like 8.2 and allow less-strict typing. Or can one write an app against 8.3 and safely assume that 8.4 *could* also add more behavior changes (e.g even more strict-ness in functions where even 8.3 could be *validly argued* as being too loose)?... Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] failed to build any 8-way joins: SQLSTATE: XX000 on PostgreSQL 8.2.6
Tom Lane wrote:
If increasing join_collapse_limit to 9 or more makes the problem go away
for you, that would be additional evidence that what you are seeing is
the same problem already diagnosed. That might be a usable workaround
for you until 8.2.7 comes out...
Thank Tom. That worked around it.
I always knew I hated arbitrary limits. I set it to 1 ("# 1 disables
collapsing of explicit JOINS") and all seems OKAY. That was 3 hours of
my life (experimenting, researching, communicating) under the bridge.
Anyway, back in business.
Ken
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Simplest syntax (or a UI?) to rename columns and sequences and constraints (etc)?
Is there a SQL/ALTER (pg proprietary or otherwise) syntax that uniformly changes the names off all object tied to a column when that column name is changed? Or can anyone recommend a UI that does this? For example in a table 'foo' I have a 'rowid' (pk and auto increment) and I choose to rename it. The default naming convention given to the supporting sequence is 'foo_rowid_seq', so (ignoring safely/ambiguity issues) is it possible rename 'rowid' and have the changes cascade to sequences, constraints, etc? Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] failed to build any 8-way joins: SQLSTATE: XX000 on PostgreSQL 8.2.6
Tom Lane wrote: Please see if this patch fixes it for you: http://archives.postgresql.org/pgsql-committers/2008-01/msg00164.php If not, we'll need a self-contained test case. Please pardon my non-awareness, what comprises a test case for this; is DDL sufficent? I'm electing to go that route for having only RPMs on hand, slow CPU and unfamiliarity with the optimal configure options. Also from the query can you tell if specific changes to it will prove whether the patch will work? I see "clauseless joins of relations that have unexploited join clauses", however I (interpreted this as needing) adding a column from "persParent" to the select list, but the problem persists. Also, I can remove any one of the timezone joins and the query works. Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] failed to build any 8-way joins: SQLSTATE: XX000 on PostgreSQL 8.2.6
Hi all, I've seen other mentions of this happening but this instance is
with a newer server version than the ones that had a patch applied.
Basically, 8 LEFT JOINs, and removing any one of them stop the symptom.
I'll provide more data if the query in itself doesn't not indicate
what's causing the problem...
-Ken
SELECT
NOW() AS a,
host.hostname AS b,
host.serverdom AS c,
host.sitename AS d,
host.appnameAS e,
site.rowid AS f,
site.namefirst AS g,
pers.rowid AS h,
pers.uuid AS i,
pers.namefirst AS j,
pers.nameLast AS k,
logon.token AS l,
siteTz.timezone_id AS o,
siteTz.id AS p,
persTz.timezone_id AS q,
persTz.id AS r,
compTz.timezone_id AS s,
compTz.id AS t
FROM http_host AS host
LEFT JOIN contact AS site ON host.join_contact = site.rowid
LEFT JOIN contact_timezones AS siteTz ON site.timezoneId =
siteTz.timezone_id
LEFT JOIN contact AS pers ON LOWER(pers.logonName) = LOWER('ken')
LEFT JOIN contact_timezones AS persTz ON pers.timezoneId =
persTz.timezone_id
LEFT JOIN contact_rela AS persParent ON pers.rowid = persParent.childId
LEFT JOIN contact AS comp ON persParent.parentId = comp.rowid
LEFT JOIN contact_timezones AS compTz ON comp.timezoneId =
compTz.timezone_id
LEFT JOIN contact_logon AS logon ON pers.rowid = logon.join_contact
WHERE host.hostname = 'localhost'
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Query to get column-names in table via PG tables?
Merlin Moncure wrote: On Jan 15, 2008 1:15 AM, Ken Johanson <[EMAIL PROTECTED]> wrote: The output of this is very verbose and broken into multiple queries making joins difficult for me to understand, I'm afraid; my current experience level likely will not reliably produce a single-query equivalent to the above. I have to again ask for designer expertise on this one. Also a factor is that since the query will be hard coded into a driver, knowledge of how to make it most durable across server versions would be a benefit (assuming the underlying tables change?). One gotcha that I should have mentioned with querying system catalogs is that they may change from version to version. That said, the query you need should be fairly portable with small changes (I'm using 8.3 atm). I think you have given up a little to easily. The system catalogs are fully documented in the docs btw. Let's look at what psql outputs for a typical table with \d: SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(queue)$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; -- this query looks up the oid of the table you are asking for. you probably are not interested in this. SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, relhasoids , reltablespace FROM pg_catalog.pg_class WHERE oid = '155955' -- psql checks for table properties of the table (the oid in this case is 155955). you may not need this, in any event it should be clear what it is doing. SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '155955' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum -- this is the 'column query'. it lists values from pg_attribute for the table in column position order. note the table oid again (155955). you can drop your own table oid here and get the exact results psql gets. Following are more queries that get information for indexes, rules inheritance, etc. Unless you specifically are interested in those things, you can ignore them. It's not as hard as you thinkthe naming can trip you up as well as the use of the hidden 'oid' column if you are not familiar with its usage. Merlin, thought you;d be interested in this. The guys (Tom and Kris) on the jdbc list suggested I use: SELECT 'database.schema.table'::regclass::oid; to get the table's OID. So I wont need to (less directly) search for catalog and schema and tablename in information schema. I'll just be using the pg_ tables passing the OID. It reduces my learning curve hopefully. -Ken Best, Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Query to get column-names in table via PG tables?
Merlin Moncure wrote: On Jan 15, 2008 1:15 AM, Ken Johanson <[EMAIL PROTECTED]> wrote: The output of this is very verbose and broken into multiple queries making joins difficult for me to understand, I'm afraid; my current experience level likely will not reliably produce a single-query equivalent to the above. I have to again ask for designer expertise on this one. Also a factor is that since the query will be hard coded into a driver, knowledge of how to make it most durable across server versions would be a benefit (assuming the underlying tables change?). One gotcha that I should have mentioned with querying system catalogs is that they may change from version to version. That said, the query you need should be fairly portable with small changes (I'm using 8.3 atm). I think you have given up a little to easily. The system catalogs are fully documented in the docs btw. Let's look at what psql outputs for a typical table with \d: SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(queue)$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; -- this query looks up the oid of the table you are asking for. you probably are not interested in this. SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, relhasoids , reltablespace FROM pg_catalog.pg_class WHERE oid = '155955' -- psql checks for table properties of the table (the oid in this case is 155955). you may not need this, in any event it should be clear what it is doing. SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '155955' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum -- this is the 'column query'. it lists values from pg_attribute for the table in column position order. note the table oid again (155955). you can drop your own table oid here and get the exact results psql gets. Following are more queries that get information for indexes, rules inheritance, etc. Unless you specifically are interested in those things, you can ignore them. It's not as hard as you thinkthe naming can trip you up as well as the use of the hidden 'oid' column if you are not familiar with its usage. merlin I sincerely wish I had enough time to learn the servers internal schema design, it seems immensely powerful (an understatement). Time (lack of) lately leaves me always looking for the most direct path though, so admittedly I was looking for someone else (a designer) to answer it. I know that in my past attempts to improvise, I seem to miss(interpret) an important where or on clause, and since I'm writing code for a PG driver I'm hesitant to roll my own on this one. Tino's idea may do what I need but if you have the time and expertise to translate that information_schmea does I'd certainly apprciate that. Thank you again, Ken ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Query to get column-names in table via PG tables?
Tino Wildenhain wrote: Ken Johanson wrote: I am looking for expertise on how to program the equivalent to this query, but using the pg_catalog tables, which I understand have fewer security restrictions than information_schema in some cases: SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position trim I have to again ask for designer expertise on this one. Also a factor is that since the query will be hard coded into a driver, knowledge of how to make it most durable across server versions would be a benefit (assuming the underlying tables change?). Ah driver you say? For which language? Will it be coded in C? This case happens to be the JDBC driver; it's: Statement.executeUpdate(sql, int[] columnINdexes) method.. If you want to do it most reliable and do not want to code for every PG version and also do not want to use information_schema (why btw?) you can also resort to just SELECT * FROM schema.table WHERE false; and then inspect the cursor for column names and datatypes. I think you may be right. This is the obvious and elegantly-simple answer (ashamed that I missed it). It does all I need at this point: to get the column names for the given indexes. Unfort though I suspect it is one extra trip to the server but I was resigned to that anyway (though hoped for a way to do via RETURNING). I was avoiding using information_schema for being told it wil have security restrictions that pg_* wont; but the 'SELECT * FROM .. WHERE false' method overcomes those also (of course :-). Thank you Tino, Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Query to get column-names in table via PG tables?
I am looking for expertise on how to program the equivalent to this query, but using the pg_catalog tables, which I understand have fewer security restrictions than information_schema in some cases: SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position Do what psql does...launch it with psql -E, and it will echo any internal queries it makes back to you. Do \d on a couple of tables and you should see what is going on. The output of this is very verbose and broken into multiple queries making joins difficult for me to understand, I'm afraid; my current experience level likely will not reliably produce a single-query equivalent to the above. I have to again ask for designer expertise on this one. Also a factor is that since the query will be hard coded into a driver, knowledge of how to make it most durable across server versions would be a benefit (assuming the underlying tables change?). Thank you, Ken ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Query to get column-names in table via PG tables?
I am looking for expertise on how to program the equivalent to this query, but using the pg_catalog tables, which I understand have fewer security restrictions than information_schema in some cases: SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position I need this to lookup the column names and their ordinal position for a given table (implementing a driver call). Just curious... but why is ordinal position important here? Because the API spec (JDBC) for the driver supports an argument of column indexes (int array) which are the table's natural position. This is to specify which columns' auto-generated keys to return. http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int[]) So in this case I must pre-fetch the column names from the indexes, and append a RETURNING clause. Inefficient but the only strategy I know of. I wont argue if this API is somewhat dubious in ordinary applications, but the interface requires it be implemented anyway. Ken ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Query to get column-names in table via PG tables?
Hi all, I am looking for expertise on how to program the equivalent to this query, but using the pg_catalog tables, which I understand have fewer security restrictions than information_schema in some cases: SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position I need this to lookup the column names and their ordinal position for a given table (implementing a driver call). Thanks in advance, Ken ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] RETURNING clause: how to specifiy column indexes?
Kris Jurka wrote: Using pg_catalog tables is better than using information_schema because of the way permissions work. For information_schema you must be the table owner, while people who only have permissions to access a table will most likely be able to read pg_catalog. Do you have an equivalent query/join handy that will get the catalog and schema and table and column names frm the pg tables? SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] RETURNING clause: how to specifiy column indexes?
Kris and all, Here is the query I will call to the get the name of columns by ordinal position. Do you see any compatibility drivers will older server versions, or other issues? SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position Ken ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] RETURNING clause: how to specifiy column indexes?
If true, my next idea would be to derive the column name using a subquery in the returning clause. But it sounds like this may have potential security contraints (will any INSERT query always have read access to the PG tables?). And no guarantee of the order matching in the long term. There is no requirement that insert permission on a user table implies read access to pg_catalog. Still many clients will break if they can't read pg_catalog. For example, all of the JDBC driver's MetaData results need to query pg tables, updatable ResultSets need to query pg tables to know what the primary key is and so on. So if this functionality required access to pg_catalog that would neither be unprecedented nor unreasonable. So it sounds like this may be the best approach, do you agree? I'll try and find the cycles to code this up although the limited value of getGeneratedKeys by index makes me think my time would be better spent elsewhere on the JDBC driver. For now at least. If you can respond to my earlier query (5 Dec) about what robustness improvements are needed, I'll start there.. Thanks, Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] RETURNING clause: how to specifiy column indexes?
Kris Jurka wrote: I think the expectation is that: CREATE TABLE t(a int, b int); INSERT INTO t(b,a) VALUES (1,2) RETURNING *; will return 1,2 instead of 2,1 as it does now. In this case the op is not expecting that the (potentially reorganized) table order is driving the results, but the order that they've actually specified the columns in creates the result. Kris, do you have pointers to a spec that says the named-columns should specify the index, or that it should instead be the order in the table? My interpretation from the JDBC spec was that the latter is true, I may be wrong... In the case where it is table-order, then I presume in PG that the "natural" order of the columns (even if reordering is allowed at a alter date) is specified by data in one of the pg_* tables (pg_class, pg_index, etc). Does anyone know if this is true/false? If true, my next idea would be to derive the column name using a subquery in the returning clause. But it sounds like this may have potential security contraints (will any INSERT query always have read access to the PG tables?). And no guarantee of the order matching in the long term. Is there a more elegant approach, like?: INSERT... RETURNING (PG_LIST_KEYS(tblname)) I looked but did not find such a utility. It seems that such function would be best implemented in the server instead of in a driver (eg. having hardcoded subquery to the schema). Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] RETURNING clause: how to specifiy column indexes?
Tom Lane wrote: Kris Jurka <[EMAIL PROTECTED]> writes: I think the expectation is that: CREATE TABLE t(a int, b int); INSERT INTO t(b,a) VALUES (1,2) RETURNING *; will return 1,2 instead of 2,1 as it does now. Hmm ... I see your point, but on what grounds could one argue that a "*" targetlist here should return something different from what "SELECT * FROM t" would return? I'd say that an app that wants that should write INSERT INTO t(b,a) VALUES (1,2) RETURNING b,a; which is surely not that hard if you've got the code to produce the "(b,a)" part. In any case it's not clear this is the same thing Ken is complaining about ... I am only seeking to have the columns returned in the order they appear naturally. JDBC says "This array contains the indexes of the columns in the target table that contain the auto-generated keys that should be made available." For the record I was not "complaining", only citing in advance the fact that while some consider selecting the keys by index to be dubious, it nonetheless must be done because an API requires it. Casting my question into a complaint is another topic. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] RETURNING clause: how to specifiy column indexes?
I am attempting to implement (in a driver)(PG JDBC) support for specifying which column indexes (that generated keys) to return, so I'm searching for a way to get the server to return the values of the columns by their index, not name. By name, it is simply to append the RETURNING clause and column names to the query: INSERT... RETURNING foo,bar Does anyone know how (if) this is possible by index? A standard or server-specific syntax is fine since this is being implemented in a server-driver. Something like?: INSERT... RETURNING [1],[2] (obviously this will not work) Would I otherwise need to?: INSERT... RETURNING * then extract the user-requested columns? This seems inefficient as it returns all columns / non-key ones (blobs etc). While the values of an API that specifies the table's columns by ordinaility may seem dubious, it is an API that I think should be implemented anyway. Thanks, Ken ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Native type for storing fractions (e.g 1/3)?
John D. Burger wrote: Stephane Bortzmeyer wrote: But he can write one in PostgreSQL quite easily. Rational numbers are always the first exercice in CS courses about Abstract Data Types :-) It's a little tricky to get good performance for all the operations: The addition and subtraction operations are complex. They will require approximately two gcd operations, 3 divisions, 3 multiplications and [..trimed] (From the Booost rational package - http://www.boost.org/libs/rational/rational.html) I'd try to link to an existing library that provides rationals, or model my code closely after one. Yes, it may be easy to create composite type but the operands would still be needed. Select n FROM t WHERE frac1 < frac2; John, do you how compatible the Booost license is (or can be made :-) with PG, in the case where adding this to the server as a standard datum-type might be very useful (for me anyway). Ken PS - Sorry for the re-send with-the-list, John ---(end of broadcast)--- TIP 1: 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
[GENERAL] Native type for storing fractions (e.g 1/3)?
In SQL servers in general, or in PG, is there a native field type to store fractions? Or must one resort to char or separate numerator/denominator columns? Thanks, Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Example of RETURNING clause to get auto-generated keys
Now playing devil's advocate, can anyone see scenarios where this will not work as expected? Examples (descriptions not sql necessarily) of those would be helpful too... Just to be sure, will the RETURNING clause work with custom sequences (say, non numeric or increment by two) or other types of key generators?... And how will triggers interfere with it (if at all)? I honestly have limited experience with server generated keys that are not numeric/serial (or uuids), or with cases where triggers, constraints, etc might come into play (I'm used to using the DB mostly as a storage device and using server-side logic..) Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Example of RETURNING clause to get auto-generated keys
Adam Rich wrote: Let's say you have a table with "id, value" columns. And your normal query would be this: INSERT into mytable (id,value) values (1,"foo"),(2,"bar"); Your new query would be like this: INSERT into mytable (id,value) values (1,"foo"),(2,"bar") RETURNING id; And you would get a result back with one column (id) and two rows (the newly inserted keys). You can also return other fields if you like, you're not limited to just the generated keys. Thank you Alvaro and Adam, Now playing devil's advocate, can anyone see scenarios where this will not work as expected? Examples (descriptions not sql necessarily) of those would be helpful too... Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Example of RETURNING clause to get auto-generated keys from INSERT
Greetings, I am looking into possibly contributing some code for one of the existing PG drivers, that will allow us to, after INSERT, get a ResultSet containing the server generated keys (sequences or other). I've been told that (short of implementing a new V4 server protocol) the most effective way to do this, may be to use PG's RETURNING clause. However I could really use some example queries, since I'm not proficient enough with PG and this clause to know how to get the values. I do know that the query should: -support multiple values, ie. insert int tbl (a,b) values (1,2),(3,4), should return a result with 2 rows containing the new keys (one for each column the users declares). -query the values atomically (so that insert by another client won't skew the curval / sequence) (obvious but deserves mention) -ideally be predictable - just in case the sequence doesn't use a increment value of one, or if some other non-sequence (triggers) or numeric (uuids) generator is used. -ideally not require parsing the user INSERT query (for table names etc), though I expect that (in order to use RETURNING) I will have to append to it. The API I'd implement this for (jdbc), does require us to declare what columns we are interested in getting generated keys for, so that might preclude needing resultset metadata to know which columns have server generated keys. So if anyone can give SQL samples of how to best make this work, I would be very much appreciative. Thanks, Ken ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PG Rocks! (was:Backslashes in 8.2 still escape, hwo
You guys, and PG ROCK!! The standard_conforming_strings mode works beautifully... it is (was) the last low-level barrier to using PG in a really professional/interop environment. And as I become familiar, again, with how PG and it's tools have come along, I am SUPER impressed. Many, many kudos!! Top notch!! Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Backslashes in 8.2 still escape, hwo to disable?
Hi, I'm trying out the new 8.2 beta; the following query: select 'ab\cd'; yields: abcd, not ab\cd. Is there a setting I'd need to switch to enable the ANSI/ISO escape behavior? I am using the JDBC driver; not sure if that could be the culprit.. Thanks, Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Backslash as ordinary char vs. not; set via a connection/session
Stefan Kaltenbrunner wrote:
foo=# create table backslash(baz text);
CREATE TABLE
foo=# set standard_conforming_strings to on;
SET
foo=# insert into backslash values ('\\');
INSERT 0 1
foo=# set standard_conforming_strings to off;
SET
foo=# insert into backslash values ('\\');
WARNING: nonstandard use of \\ in a string literal
LINE 1: insert into backslash values ('\\');
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
INSERT 0 1
foo=# select * from backslash;
baz
-
\\
\
(2 rows)
like that ? :-)
Yes - that is the eye candy I'm looking for. Anxiously looking forward
to 8.2 :-)
ken
---(end of broadcast)---
TIP 1: 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: [GENERAL] Backslash as ordinary char vs. not; set via a connection/session
Stefan Kaltenbrunner wrote: postgresql can do that in an even more powerful way - but people tend to not notice much of it in your case that would be: ALTER ROLE foo SET standard_conforming_strings='off' or even: ALTER DATABASE bar SET standard_conforming_strings='off' you can do that for nearly all GUCs (like logging,client_encoding,search_path,) Stefan Stefan and Alvaro, Thank you!!! Yes, that is the feature I'd like... and yes, setting it on a per role or per database level is something I personally would prefer over the connection level. But, is there also a way to set it on the connection? Just because, one can imagine scenarios where two APIs share the same role & database, but one API forces backslashes 'on' during its statement-prepare just playing devil's advocate :-) So is this 'standard_conforming_strings' variable already set-able in a recent build, at the role or db level? Or will that need to wait for 8.2? Thanks again!! ken ---(end of broadcast)--- TIP 1: 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
[GENERAL] Backslash as ordinary char vs. not; set via a connection/session variable
Tom and folks, Will it be possible to set this more standard backslash handling behavior -- and possibly similar conformance modes... in a way similar to how mysql allows? They allow one to issue commands on the connection like: SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES,IGNORE_SPACE,ANSI' The advantage to this is that ISPs, etc can, by default, run the database with the old/incorrect mode (which is more compatible with the correspondingly legacy/broken apps.. and for newer apps to issue that command to make the DB act more standards compliant.. I personally have no need for the old backslash behavior (currently using databases/queries that don't use it), but sometimes one may have to coexist in a shared server environment. (for those who may not know,) the actual overhead of issuing that command (on the client side at least) before each exec is low, since connection pools for example usually already need to issue a 'ping' command (e.g SELECT 1) to test the connection before executing on it. So if the pool/api allows the user to define connection-setup DML, that conveniently takes the place of the ping anyway (if the API supports). And, given that the last user of a pooled connection may have tweaked the settings, it's prob. best to reset them before each exec. I seem to have lost the URL that describes the upcoming string conformance mode.. can't find the 4.1.2.1 section that John Gunther mentioned.. does anyone have that handy? Thank you, ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 8.2 Beta ETA
Does anyone know roughly when there might be an 8.2 beta? Would a rough guess of about November be right (1 year after 8.1)? Regards, ken ---(end of broadcast)--- TIP 1: 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: [GENERAL] Perspective: PostgreSQL usage boon after release of
Tony Caduto wrote: Ken Johanson wrote: Most of the corp folks I know who have tried using PG to augment or replacement a commercial offering just tend to silently pause and wait for this change.. that why this topic isn't really heard very often. It's like going to a car lot to buy a SUV, but they don't have any within sight.. the perspective buyer just moves on without saying anything. I have converted databases from other DBs such as MS SQL server and never had a problem with string escaping, can you please post a example of what you mean? Do you mean inside of functions? Well for a simple (for brevity) example, when you compile a query (not via prepared stmts/argument based compilation) that takes user input, how do you handle both backslashes and single-quotes? In practice the way of doing this is quite different between pg and a iso-compliant db, otherwise you have either code injection, or superfluous backslashes.. "SELECT firstName FROM tbl WHERE lastName = '"+toSql(userInput)+"' " smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] Perspective: PostgreSQL usage boon after release of 8.2
Just a quick thought, and an possibly over-optimistic forecast: I think PG will see a really significant increase in usage, especially 'corporate' use, with the release of the version that has support for (what I consider to be) one of the biggest features in recent memory: standard iso/ansi string escaping. In my experience this has been the biggest roadblock to adoption by companies running a commercial database... it has made migration difficult/daunting, especially for users who cannot rely on prepared statement style APIs (complex dynamic queries, etc). Most of the corp folks I know who have tried using PG to augment or replacement a commercial offering just tend to silently pause and wait for this change.. that why this topic isn't really heard very often. It's like going to a car lot to buy a SUV, but they don't have any within sight.. the perspective buyer just moves on without saying anything. I'm am SOOO looking forward to hearing the beta announcement for 8.2... hopefully that version's still on target for the standard string escape option.. This *one* roadblock will be gone. k ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] escape string type for upcoming 8.1
Bruce Momjian wrote: E'' is more a marker than a type. I realize making E a type might work, but it seems unusual. What we could do is backpatch E'' to 8.0.X as a no-op like it will be in 8.1. Bruce, Is it possible in the 8.1 betas to 'switch on' on the standard SQL escape behavior? This is from the use-case perspective of someone who does not have backwards compatibility concerns, rather, I'd like to preemptively forward-port / certify an app from another databases, onto PostgreSQL -- so all I need to do is switch that config on, if possible. From the changelog: "While this release does not change the handling of backslashes in strings, it does add new configuration parameters to help users migrate applications for future releases: o standard_conforming_strings .. o escape_string_warning .. The standard_conforming_strings value is read-only. " The last quoted sentence seems to answer my question (as no), but hopeful optimism is my motto :-) If it is indeed readonly, can it be made 'writable' before the 8.3 release where is would be made the default behavior? For that matter, if the current backslash behavior stayed as the default for pre-8.3 releases, and the patches are backported, I don't see any harm to the old-style apps/users; yet the correct behavior option is a useful "opt-in" one (one that I would like to try, now, even on 8.1). Thank you, -Ken ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Possible to run the server with ANSI/ISO string
The latest mysql build (5.0.3) now supports standard backslash behavior, using the below config option. set-variable=sql-mode=PIPES_AS_CONCAT,ANSI_QUOTES,NO_BACKSLASH_ESCAPE PG seems to be the last holdout. :-) Ken Ken Johanson wrote: [snip] I think most people agree that being SQL compliant is good. The question is: is it worth the pain for existing users? My guess is that it is worth it, if the users are given the discretion of treading that water.. and to save them future pain by encouraging them to migrate toward 'other-db' compatibility (or merely to migrate to PreparedStatement to eliminate worry and *insure* interop). But where things are right now, I *know* allot of apps specially coded for PG (or mysql) --- using functions like PHPs escapeCslashes()... so they are NOT compatible apps with other DBs. So making the change would at least raises author awareness to use PreparedStatements instead (half the battle is won then because when a PS admin turns on the new escape, their apps still works correctly), or stop using escapeCslashes in favor of a sql-escape function (yes, not 'old pg' compatible, but be able to claim interop with other dbs). A configurable option does not make the pain disappear. Admins are forced to choose one side (either sql compliant or c style) and exclude the other applications. Any app developer that wants to support pre-8.1 apps will have to have a c-style app available. So even if you nip it in the bud, it's not really gone yet because app developers want to support old versions of postgres. As was mentioned earlier, this may not be too much of an issue if the new drivers supported an option in the getConnection call that turned on the new escape, otherwise leaving old escape turned on by default. Sort of like the jdbc version/conformance level that jdbc drivers can report through function calls. In fact PG could forever use the old style escapes by default, except when a modern driver connected to it and they both agree to use the new style. I know if we added the option and deprecated the old style, I would be forced to choose between using deprecated syntax that may not be supported for long, or doing a lot of work to convert and retest applications. Yes - and your app would be inter operable with Oracle, Sybase, etc and have a wider audience (moot point if you use prepared statements obviously) especially in the enterprise... Very worthwhile, imo. Besides, the version-deprecation / version requirements you mention exists in every piece of software I've even seen. Sometime they're okay with a really old version, sometime only the newest will do. This is the very argument for getting PG to offer an (use-optional) escape behavior inline with the rest - to mitigate these version requirements down the road. I think you may have misunderstood what I meant. I am not suggesting that we don't change the database at all between versions, my argument was showing the difficulties when one version has many different shapes due to many incompatible options. Sorry, I misunderstood. Your point is well taken, and I agree. Thank you, ken ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Possible to run the server with ANSI/ISO string
[snip] I think most people agree that being SQL compliant is good. The question is: is it worth the pain for existing users? My guess is that it is worth it, if the users are given the discretion of treading that water.. and to save them future pain by encouraging them to migrate toward 'other-db' compatibility (or merely to migrate to PreparedStatement to eliminate worry and *insure* interop). But where things are right now, I *know* allot of apps specially coded for PG (or mysql) --- using functions like PHPs escapeCslashes()... so they are NOT compatible apps with other DBs. So making the change would at least raises author awareness to use PreparedStatements instead (half the battle is won then because when a PS admin turns on the new escape, their apps still works correctly), or stop using escapeCslashes in favor of a sql-escape function (yes, not 'old pg' compatible, but be able to claim interop with other dbs). A configurable option does not make the pain disappear. Admins are forced to choose one side (either sql compliant or c style) and exclude the other applications. Any app developer that wants to support pre-8.1 apps will have to have a c-style app available. So even if you nip it in the bud, it's not really gone yet because app developers want to support old versions of postgres. As was mentioned earlier, this may not be too much of an issue if the new drivers supported an option in the getConnection call that turned on the new escape, otherwise leaving old escape turned on by default. Sort of like the jdbc version/conformance level that jdbc drivers can report through function calls. In fact PG could forever use the old style escapes by default, except when a modern driver connected to it and they both agree to use the new style. I know if we added the option and deprecated the old style, I would be forced to choose between using deprecated syntax that may not be supported for long, or doing a lot of work to convert and retest applications. Yes - and your app would be inter operable with Oracle, Sybase, etc and have a wider audience (moot point if you use prepared statements obviously) especially in the enterprise... Very worthwhile, imo. Besides, the version-deprecation / version requirements you mention exists in every piece of software I've even seen. Sometime they're okay with a really old version, sometime only the newest will do. This is the very argument for getting PG to offer an (use-optional) escape behavior inline with the rest - to mitigate these version requirements down the road. I think you may have misunderstood what I meant. I am not suggesting that we don't change the database at all between versions, my argument was showing the difficulties when one version has many different shapes due to many incompatible options. Sorry, I misunderstood. Your point is well taken, and I agree. Thank you, ken ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Possible to run the server with ANSI/ISO string
Bruno Wolff III wrote:
On Mon, Feb 28, 2005 at 10:13:00 -0700,
Ken Johanson <[EMAIL PROTECTED]> wrote:
Besides, the version-deprecation / version requirements you mention
exists in every piece of software I've even seen. Sometime they're okay
with a really old version, sometime only the newest will do. This is the
very argument for getting PG to offer an (use-optional) escape behavior
inline with the rest - to mitigate these version requirements down the road.
Shouldn't this data be being passed through some standard code that checks if
escaping is needed? If so, is that the right place to handle whether or not
backslashes need to be escaped in addition to single quotes?
Ideally yes, but its not a requirement in any driver's spec that I'm
familiar with. In fact the driver specs expect or 'claim' some (possibly
implicit) level of sql language compliance -- so that the same query
sent to a different database yields the same result.
insert into tbl (path) values ('c:\test')
The above query *could* and "should* be sent through an escape
preprocessor (PreparedStatement interface) but it is *not* required.
It's also not fair to say that a user can *expect* the above to not work
with PG even though it does with another DB, imo. The user coming from
another DB *won't* expect it to be broken. (I know from experience :-)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Possible to run the server with ANSI/ISO string
I'm a little worried about PostgreSQL having the same problems as PHP. In PHP, every time you want to download an application, you never see "This application works on php 4+". Instead, you see "This application works on php4+ with the following config options set ". Sometimes these applications have conflicting requirements. From an administrator's standpoint, it's a mess. In PostgreSQL I think it would actually be much worse. Right now many applications build a PostgreSQL layer, but will they build two? I think this would cause a divide in the application support (some for config option A some for config option B) in the already smaller-than-we'd-like set of software that supports PostgreSQL. Regards, Jeff Davis There's certainly two perspectives to this. The one you present is certainly valid, but consider the bigger picture... "This application requires the following databases: Oracle versionX, MY SQL version X, Mysql version 5.2 with the no-backslashes option, UltraDB version x" Notice the lack of PG - some apps - most notably commercial ones - will automatically shoot it down if it cant meet certain language requirements. The database itself could meet the latest SQL03 (or whatever we're up to) specs for Object Relational stuff, etc to the tee. The JDBC driver could meet the JDBC spec to the tee for transaction support, etc - but this one low level problem is a total show stopper, because it plainly breaks queries sent through various interfaces in various drivers. Besides, the version-deprecation / version requirements you mention exists in every piece of software I've even seen. Sometime they're okay with a really old version, sometime only the newest will do. This is the very argument for getting PG to offer an (use-optional) escape behavior inline with the rest - to mitigate these version requirements down the road. Thoughts, ken ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Possible to run the server with ANSI/ISO string escapeing
Martijn van Oosterhout wrote: On Sun, Feb 27, 2005 at 06:25:18PM -0700, Ken Johanson wrote: I feel somewhat confident (very actually) that a config option that disabled the backslash behavior globally(*) would be acceptable, BUT leave the current backslash behavior turned on by default so that current users are not impacted at all. Only a conscientious decision by the db admin to turn it on could cause problems, but _only_ if he/she didn't warn all his/her users beforehand of the impending change and its consequences (rtm). It's not just a question of warning the users, all interfaces to the database will instantly break. For example: JDBC, Perl DBI, PHP PEAR etc. They will continue to send queries with the backslashes embedded. These interfaces would need to be modified to handle both situations and detect which situation they're dealing with. All interfaces will NOT break IF the legacy db behavior stays its default. This means NONE of the current users would be hurt until they start experimenting with the new option. Yes, the built in prepared stmt components of those interfaces will still add the backslash by default and break queries for legacy drivers, but this is not an issue for the straight-through query/update exec(s) calls, and prepared stmt users can hack the Prepared stmts behavior until the same option is officially supported in the driver also (probably by auto-detecting what the DB expects its backslashes to look like). Like I said, users should be warned beforehand, that they need to get a hacked or official driver update, if the dbadmin decides to turn on the 'new' mode. Seems prudent to me. The thing is all these interfaces handle the quoting transparently for you, so the code is portable already. What you're complaining about is that you have your own query marshalling and it is not portable. As you say, the portability you describe REQUIRES the use of prepared stmts type queries - but one CANNOT issue a portable query, say, jdbc:stmt.execQuery() or execute update. Those "lower-level" calls need to be portable where string escaping is concerned, and they are not. They arguably break both the JDBC spec and SQL spec since some additional, nonstandard string preprocessing is REQUIRED for them to work. And not to repeat what you already know, but Prepared stmts are not suitable or available for certain query types (performance sometimes better w/o PS, dynamic query building, batch queries, etc) and drivers (small foot print ones). Incidently, if you disable the backslash quoting, how does one enter raw binary data including NUL (\0) characters? I'm not sure if you're talking about API interfaces or shell, or both. If shell, a piped sql-compliant escape processor would clearly be needed. As for APIs, I suspect the current design of each driver handles nulls and the like, stems from how the underlying db protocol was originally built --so they use either run length encoding (chunking), boundary-delimiting, or are null terminated (whichever PG uses natively, I don't know). In any case the very lowest level of the db interface can translate as necessary (doubling the backslashes if necessary), but queries should be able to issue any byte, including nulls, with the only requirement that apostrophes are the string-escape character, for themselves. In other words nulls should need no special treatment from the query interface layer. The only viable solution I can think of is that it is set at *connection* time (maybe extra parameters), and unchangable for the rest of the session. This means that unmodified client interfaces won't see a difference. Yes, sessions (connections) could work, and also perhaps per-user or group, wherein the db the escape handing is handled the old or new way on a per-user basis. I can say, that I for one would enable the no-backslash config option out of the box -globally -so that we can start using pg now without any more upper managerial concerns/excuses about language/interface compliance..I can also say that (what we already know) the longer we wait to provide the 'right' option, the *more* legacy apps (and interfaces) will be built around it and consequently suffer when the need for change eventually comes (almost wholly caused by interop concerns). And market gain is being hurt now by this incompatibility with commercial offerings; that's an unfortunate fact. Even if PostgreSQL implements this now, you will have to wait for new versions of any client libraries before it's usable. See the autocommit disaster for an example why people are not rushing into this... I fully agree. I can see waiting at LEAST 1-3 months before the db itself has changes committed for alpha testing, but that SURE BEATS procrastination --which means years worth of more apps and interfaces being built around the
Re: [GENERAL] Possible to run the server with ANSI/ISO string escapeing
Uh, yea, this is going to require quite a bit of discussion in the group, and I am concerned how it will affect other apps using PostgreSQL. (The mode isn't going to be useful if it breaks plug-in extensions and stuff.) The hard part of this isn't turning off backslash quoting; the code changes to do that would be pretty trivial. The hard part is not breaking vast quantities of existing client code. After our experience with autocommit, no one is going to want to solve it with a GUC variable that can be flipped on and off at random. That would make the compatibility problems that autocommit caused look like a day at the beach :-( I don't actually know a way to solve this that wouldn't impose impossible amounts of pain on our existing users, and I'm afraid that I rank that consideration higher than acquiring new users who won't consider changing their own code. If you can show me a way to provide this behavior without risk of breaking existing code, I'm all ears. regards, tom lane I feel somewhat confident (very actually) that a config option that disabled the backslash behavior globally(*) would be acceptable, BUT leave the current backslash behavior turned on by default so that current users are not impacted at all. Only a conscientious decision by the db admin to turn it on could cause problems, but _only_ if he/she didn't warn all his/her users beforehand of the impending change and its consequences (rtm). (*Or if it's possible, provide the no-backslash config on a per-catalog basis perhaps? -or even per-user/group?, --that would allow individuals to use the legacy mode until they choose otherwise) I can say, that I for one would enable the no-backslash config option out of the box -globally -so that we can start using pg now without any more upper managerial concerns/excuses about language/interface compliance..I can also say that (what we already know) the longer we wait to provide the 'right' option, the *more* legacy apps (and interfaces) will be built around it and consequently suffer when the need for change eventually comes (almost wholly caused by interop concerns). And market gain is being hurt now by this incompatibility with commercial offerings; that's an unfortunate fact. Better to nip it in the bud sooner than later, imo. thoughts, ken ---(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: [GENERAL] Possible to run the server with ANSI/ISO string escapeing
Bruce Momjian wrote: Ken Johanson wrote: Greetings, Does anyone know if it will be possible to run the server with ANSI/ISO string escaping instead of C-style escapes? The C style escaping is a shoot-down for our adoption of postgres, since its non-standard. Not yet, but we have a TODO item: * Allow backslash handling in quoted strings to be disabled for portability The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not SQL-spec compliant, so allow such handling to be disabled. Uh, what is ANSI/ISO escaping actually? I assume you mean only supporting '' for literal quotes rather than \' too. Yes Sir. Being able to disable the backslash-escaping is the desired operation. In circles of business deciding wether to move to opensource databases this is the silently used excuse (by the critics) to dismiss pg since its arguably so fundamental and somewhat dangerous (data loss). Getting pg to behave 'normally' would silence this excuse. Is there anything I can do to help move this up the todo list? Thanks, ken
[GENERAL] Rephrased: Possible to run the server without backslash (C-style) escaping?
More specifically, I'd like to disable the non-standard backslash
escaping behavior of the server, so that backslashes aren't discarded, like:
insert into table values ('This won''t work when inserting a \
character'); -> 'This won't work when inserting a character'
Instead I'd like for only single quotes (apostrophes) to server as the
escape character (for themselves), as in the iso/ansi recommendations
-so that the server behaves the same as Oracle, ms, and most other
databases in this regard.
I am living testament to all the users who switched from the 'big'
databases to pg and had the unpleasant surprise of having lost all the
backslashes chars -as well as having to tweak apps to get them to work.
If nothing else, a VERY prominently placed warning on the docs front
page should warn new users of the nonstandard behavior, imo. This can be
a life saver.
ken
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Possible to run the server with ANSI/ISO string escapeing instead of C-style escapes?
Greetings, Does anyone know if it will be possible to run the server with ANSI/ISO string escaping instead of C-style escapes? The C style escaping is a shoot-down for our adoption of postgres, since its non-standard. Thanks, ken ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
