Re: [BUGS] BUG #5487: dblink failed with 63 bytes connection names
On 01/06/10 05:55, Takahiro Itagaki wrote: "Takahiro Itagaki" wrote: Contib/dblink module seems to have a bug in handling connection names in NAMEDATALEN-1 bytes. Here is a patch to fix the bug. I think it comes from wrong usage of snprintf(NAMEDATALEN - 1). It just copies 62 bytes + \0. In addition, it should be safe to use pg_mbcliplen() to truncate extra bytes in connection names because we might return invalid text when a multibyte character is at 62 or 63 bytes. Hmm, seems that dblink should call truncate_identifier() for the truncation, to be consistent with truncation of table names etc. I also spotted this in dblink.c: /* first gather the server connstr options */ if (strlen(servername) < NAMEDATALEN) foreign_server = GetForeignServerByName(servername, true); I think that's wrong. We normally consistently truncate identifiers at creation and at use, so that if you create an object with a very long name and it's truncated, you can still refer to it with the untruncated name because all such references are truncated too. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5484: sum() bug
megafon=# \d aaa Table "public.aaa" Column | Type | Modifiers +--+--- num | real | Yes, really, on smaller quantity of digits of errors is not present. What type of the data is better for using? Numbers up to 10 and accuracy of 2 fractional signs. 30.05.10, 21:43, "Tom Lane" : > "Sergey" writes:8849.15+6464.57=15313.72But > sum()=15313.7You did not show what datatype you're summing, but if the column > isfloat4 this result wouldn't be surprising. float4 is only goodto about six > decimal digits. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5484: sum() bug
On Tue, Jun 1, 2010 at 9:24 AM, viras wrote: > megafon=# \d aaa > Table "public.aaa" > Column | Type | Modifiers > +--+--- > num | real | > > Yes, really, on smaller quantity of digits of errors is not present. > What type of the data is better for using? Numbers up to 10 and accuracy > of 2 fractional signs. numeric is a good choice to avoid loss of precision, but can be a bit slower. You could also try float8. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
On Mon, May 31, 2010 at 10:11 AM, Bruce Momjian wrote: > Robert Haas wrote: >> On Sat, May 29, 2010 at 5:00 PM, Bruce Momjian wrote: >> > I have updated the patch, attached, to clarify that this returns text >> > arrays, and that you can force it to always return one row using >> > COALESCE() and a '|' pattern (the later suggested by Daniele Varrazzo). >> >> I don't find this part to be something we should include in the >> documentation. If we want to include a workaround, how about defining >> a non-SRF that just calls the SRF and returns the first row? > > Remember this has to return one row for no matches, so a simple SRF will > not work. I also have not seen enough demand for another function. A > single doc mention seemed the appropriate level of detail for this. Well, we can debate later whether to add another function to core, but what I meant was that the user having the problem could create a user-defined function that calls regexp_matches() and returns the first row, or NULL. But actually here's an even simpler workaround, which is IMHO less ugly than the original one: SELECT foo, bar, (SELECT regexp_matches(bar, pattern)) FROM table; -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Robert Haas writes: > But actually here's an even simpler workaround, which is IMHO less > ugly than the original one: > SELECT foo, bar, (SELECT regexp_matches(bar, pattern)) FROM table; Doesn't that blow up if the subselect returns more than one row? I think you could make it work by wrapping regexp_matches in a simple (non-SETOF) SQL function, but just writing out the sub-SELECT doesn't do it. This goes back to the recent discussion of why SQL functions can't always be inlined --- the semantics are a bit different in some cases. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Tom Lane wrote: > Robert Haas writes: > > But actually here's an even simpler workaround, which is IMHO less > > ugly than the original one: > > > SELECT foo, bar, (SELECT regexp_matches(bar, pattern)) FROM table; > > Doesn't that blow up if the subselect returns more than one row? > > I think you could make it work by wrapping regexp_matches in a > simple (non-SETOF) SQL function, but just writing out the sub-SELECT > doesn't do it. This goes back to the recent discussion of why SQL > functions can't always be inlined --- the semantics are a bit > different in some cases. If you don't use 'g' as a third argument, it can't return more than one row. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Robert Haas wrote: > On Mon, May 31, 2010 at 10:11 AM, Bruce Momjian wrote: > > Robert Haas wrote: > >> On Sat, May 29, 2010 at 5:00 PM, Bruce Momjian wrote: > >> > I have updated the patch, attached, to clarify that this returns text > >> > arrays, and that you can force it to always return one row using > >> > COALESCE() and a '|' pattern (the later suggested by Daniele Varrazzo). > >> > >> I don't find this part to be something we should include in the > >> documentation. ?If we want to include a workaround, how about defining > >> a non-SRF that just calls the SRF and returns the first row? > > > > Remember this has to return one row for no matches, so a simple SRF will > > not work. ?I also have not seen enough demand for another function. ?A > > single doc mention seemed the appropriate level of detail for this. > > Well, we can debate later whether to add another function to core, but > what I meant was that the user having the problem could create a > user-defined function that calls regexp_matches() and returns the > first row, or NULL. > > But actually here's an even simpler workaround, which is IMHO less > ugly than the original one: > > SELECT foo, bar, (SELECT regexp_matches(bar, pattern)) FROM table; Good idea. Simplified patch attached. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.513 diff -c -c -r1.513 func.sgml *** doc/src/sgml/func.sgml 7 Apr 2010 06:12:52 - 1.513 --- doc/src/sgml/func.sgml 1 Jun 2010 14:40:22 - *** *** 3445,3463 ! The regexp_matches function returns all of the captured ! substrings resulting from matching a POSIX regular expression pattern. ! It has the syntax regexp_matches(string, pattern , flags ). ! If there is no match to the pattern, the function returns ! no rows. If there is a match, the function returns a text array whose n'th element is the substring matching the n'th parenthesized subexpression of the pattern (not counting non-capturing parentheses; see below for ! details). If the pattern does not contain any parenthesized ! subexpressions, then the result is a single-element text array containing ! the substring matching the whole pattern. The flags parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag g causes the function to find --- 3445,3466 ! The regexp_matches function returns a text array of ! all of the captured substrings resulting from matching a POSIX ! regular expression pattern. It has the syntax regexp_matches(string, pattern , flags ). ! The function can return no rows, one row, or multiple rows (see ! the g flag below). If the pattern ! does not match, the function returns no rows. If the pattern ! contains no parenthesized subexpressions, then each row ! returned is a single-element text array containing the substring ! matching the whole pattern. If the pattern contains parenthesized ! subexpressions, the function returns a text array whose n'th element is the substring matching the n'th parenthesized subexpression of the pattern (not counting non-capturing parentheses; see below for ! details). The flags parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag g causes the function to find *** *** 3490,3495 --- 3493,3508 + + It is possible to force regexp_matches() to always + return one row by using a sub-select; this is particularly useful + in a SELECT target list when you want all rows + returned, even non-matching ones: + + SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab; + + + The regexp_split_to_table function splits a string using a POSIX regular expression pattern as a delimiter. It has the syntax -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5484: sum() bug
Robert Haas wrote: > On Tue, Jun 1, 2010 at 9:24 AM, viras wrote: >> What type of the data is better for using? Numbers up to 10 >> and accuracy of 2 fractional signs. > > numeric is a good choice to avoid loss of precision, but can be a > bit slower. > > You could also try float8. Yeah, as long as you remember that this is an *approximate* data type. If you really mean that you're satisfied with an *accuracy* of two fractional digits for a number up to 10, you are OK. But realize that means that 1.01 would actually be 1.0100088817841970012523233890533447265625 and that 10.01 would actually be 10.0094761310517787933349609375 -- accurate to far more than two decimal digits, but not *exact*. If you want exact values based on decimal fractions, you should use numeric. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
