Re: [BUGS] BUG in postgres mathematic
Notice how the INT4 rounding is banker's rounding (round to the nearest even number). That is what we would want the INT8 to do as well, not just a simple round like I mentioned before. Again, the INT8 shows truncation. I've been looking around the source code, but I can't see where all this happens. reaster=# SELECT 1.5::FLOAT::INT4; ?column? -- 2 (1 row) reaster=# SELECT 2.5::FLOAT::INT4; ?column? -- 2 (1 row) reaster=# SELECT 1.5::FLOAT::INT8; ?column? -- 1 (1 row) reaster=# SELECT 2.5::FLOAT::INT8; ?column? -- 2 (1 row) On Thursday 25 January 2001 22:52, Tom Lane wrote: > "Robert B. Easter" <[EMAIL PROTECTED]> writes: > > This problem is not specific to Postgres. > > The fact that 5*27.81*100 != 27.81*100*5 is certainly a garden-variety > floating-point roundoff error. However, I think Max has a fair > complaint here: it seems float-to-int8 conversion is truncating, not > rounding like the other conversions to integer do. > > regression=# select 4.7::float8::int4; > ?column? > -- > 5 > (1 row) > > regression=# select 4.7::float8::int8; > ?column? > -- > 4 > (1 row) > > Seems to me this is a bug we should fix. > > regards, tom lane -- Robert B. Easter [EMAIL PROTECTED] - -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- -- http://www.comptechnews.com/~reaster/
Re: [BUGS] Re: [INTERFACES] jdbc driver: Support for 'BOOL'
Quoting Bruce Momjian <[EMAIL PROTECTED]>: > > Seems the BIT type works in Java 7.1 beta now. Has anything changed with boolean types in the backend then, as nothing has been done on the JDBC side? > > [ Charset ISO-8859-1 unsupported, converting... ] > > "Jos? Antonio Matute Calvo" wrote: > > > > > > Problem: > > > If try to use field of type "java.sql.Types.BIT" obtains error > "Unknown > > > Types Value" > > > > > > Resolution: > > > > > > Add this: > > > > > > PreparedStatement.java > > > 460,461d459 > > > < case Types.BIT: > > > < set(parameterIndex, > > > ((Boolean)x).booleanValue() ? "true" : "false"); > > > > Why not set(parameterIndex,x.toString())? > > > > > > -- > > Joseph Shraibman > > [EMAIL PROTECTED] > > Increase signal to noise ratio. http://www.targabot.com > > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania > 19026 > -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [JDBC] Re: [BUGS] no way in LargeObject API to detect short read?
Hmmm, what's the performance issues with this? Is there going to be a problem
with very large LargeObject's?
Quoting "Paul M. Aoki" <[EMAIL PROTECTED]>:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Anyone able to fix this?
>
> here's a hack we've been using in-house (written by Jun Gabayan,
> <[EMAIL PROTECTED]>).
>
> you may not like the style but it's a stab at a solution.
> --
> Paul M. Aoki / Xerox Palo Alto Research Center / Coyote Hill Road
> [EMAIL PROTECTED] / Computer Science Laboratory / Palo Alto, CA
> 94304-1314
>
> Index: LargeObject.java
> ===
> RCS file:
> /project/placeless/cvsroot/placeless2/src/org/postgresql/largeobject/LargeObje
ct.java,v
> retrieving revision 1.1
> retrieving revision 1.3
> diff -r1.1 -r1.3
> 64c64,67
> <
> ---
> >
> > private int pos = 0; //current position
> > private int size = 0;
> >
> 85a89,90
> > pos = tell();
> > size = size();
> 102a108
> > if(fd == 0) return;
> 105a112
> > fd = 0;
> 118a126,132
> > // calculate available data to read to avoid reading pass the
> end
> > // to avoid an exception
> > pos = tell();
> > int avail = size - pos;
> > if(avail == 0) return null;
> > if(avail < len) len = avail;
> > try {
> 123c137,141
> <
> ---
> > }catch(SQLException se) {
> > System.out.println("***LargeObject.read: Caught
> SQLException: " + se.getMessage());
> > return null;
> > }
> >
> 157c175
> < public void read(byte buf[],int off,int len) throws SQLException
> ---
> > public int read(byte buf[],int off,int len) throws SQLException
> 159c177,180
> < System.arraycopy(read(len),0,buf,off,len);
> ---
> > byte mybuf[] = read(len);
> > int sz = (mybuf != null) ? mybuf.length : -1; //must return -1 for
> end of data
> > if(sz > 0) System.arraycopy(mybuf,0,buf,off,sz);
> > return sz;
>
--
Peter Mount [EMAIL PROTECTED]
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [JDBC] Re: [BUGS] JDBC buggy in 7.1beta3
Quoting Barry Lind <[EMAIL PROTECTED]>: > This is no longer a problem in 7.1. The bug here was the same one > introduced by the wrong version of Gunner's bytearray pooling patch > being applied. It was fixed in current sources by Peter commenting out > the bytearray pooling. That's what I thought. I think theres several bugs that are being confused as being the same at the moment. Peter > > thanks, > --Barry > > > Bruce Momjian wrote: > > > > Anyone fixing this bug? > > > > > I reported a JDBC bug in the 7.1 beta1 release back in December and > notice that the same bug is still present in the current beta 3 sources. > The problem relates to nested cursors failing and is quite simple to > reproduce. A simple test case can be found at: > > > > > > http://www.twospuds.com/pg7-1b3-jdbc-bug.tar.gz > > > > > > The 7.0.2 JDBC drivers work fine against the 7.1b3 backend, with a > little tweaking for "long" rows. > > > > > > Peter (Mount) did acknowledge the bug but I have not heard anything > on the list since then. Is anyone looking at it? Come to think of it, > I haven't seen anything on the lists [HACKERS mainly] from Peter for a > while. > > > > > > Stu. > > > -- > > > Stu Coates > > > Chelmsford, England U.K. > > > ICQ:146628 > > > http://www.stucoates.com/ > > > > > > __ > > > FREE Personalized Email at Mail.com > > > Sign up at http://www.mail.com/?sr=signup > > > > > > > -- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup.| Drexel Hill, Pennsylvania > 19026 > -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [BUGS] select fails on indexed varchars.
Alex Krohn <[EMAIL PROTECTED]> writes: > [postgres@penguin pgsql]$ locale > LANG=en_US > LC_CTYPE="en_US" > LC_NUMERIC="en_US" > LC_TIME="en_US" > LC_COLLATE="en_US" > LC_MONETARY="en_US" > LC_MESSAGES="en_US" > LC_ALL=en_US > [postgres@penguin pgsql]$ > Postmaster is running as user pgsql. Any ideas on workarounds? Use locale "C" unless you have a really good reason why you need en_US sorting order. Beware of changing the postmaster's locale on the fly, however, since that will leave you with corrupted (out-of-order) indexes. Safest to dump/initdb in new locale/reload. regards, tom lane
Re: [BUGS] select fails on indexed varchars.
Alex Krohn <[EMAIL PROTECTED]> writes: >> Beware of changing the postmaster's locale on the fly, however, >> since that will leave you with corrupted (out-of-order) indexes. >> Safest to dump/initdb in new locale/reload. > How would I go about changing that? Setting LANG and LC_ALL in the pgsql > users home directory .bashrc? Or do I need to edit the startup file? I'd recommend setting LANG/LC_xxx directly in the script you use to fire up the postmaster. This ensures it will be right no matter whether the postmaster is launched by a boot script, by hand by someone logged in as pgsql, by hand by someone su'd from another account with different locale, yadda yadda. regards, tom lane
Re: [BUGS] JDBC driver throws SQLException while parsing timestamp
I think this is fixed in 7.1beta.
> Alexander Dietrich ([EMAIL PROTECTED]) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> JDBC driver throws SQLException while parsing timestamp
>
> Long Description
> Hi,
>
> there's a discrepancy between PreparedStatement.setTimestamp() and
> ResultSet.getTimestamp() related to a timestamp format that may or
> may not be valid (sorry I can't give a complete code sample):
> -
> I do something like this to store the timestamp:
>
> Connection conn = ConnectionFactory.getConnection();
> PreparedStatement stmt = conn.prepareStatement(getInsertString());
> [...]
> stmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
> stmt.executeUpdate();
> -
> This is an example of what ends up in the database:
> type: timestamp, value: "2001-01-25 11:33:19.50+01"
> (Checked with psql)
> -
> But when retrieving the data:
>
> [...]
> ResultSet rs = stmt.executeQuery(queryStr);
> Timestamp timestamp = rs.getTimestamp("CREATION_TIME");
>
> An SQLException is thrown:
>
> Bad Timestamp Format at 19 in 2001-01-25 11:33:19.50+01
>
> java.lang.Throwable()
> java.lang.Exception()
> java.sql.SQLException()
> org.postgresql.util.PSQLException(java.lang.String, java.lang.Object,
>java.lang.Object)
> java.sql.Timestamp org.postgresql.jdbc2.ResultSet.getTimestamp(int)
> java.sql.Timestamp org.postgresql.jdbc2.ResultSet.getTimestamp(java.lang.String)
> [...]
> -
>
> Obviously the getTimestamp() method chokes on the millisecond field
> in the timestamp. A timestamp like "2001-01-25 11:33:19+01" does not
> pose a problem.
>
> My configuration is:
> Server:
> RedHat 7.0 (x86), 2.2.16-22
> postgresql-server-7.0.3-2
> postgresql-7.0.3-2
> postgresql-jdbc-7.0.3-2
>
> Client:
> JVM: 1.2.2 inside VisualAge 3.5 Patch 2
> JDBC driver: jdbc7.0-1.2.jar
>
> Regards,
> Alexander Dietrich
>
> Sample Code
>
>
> No file was uploaded with this report
>
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [BUGS] select fails on indexed varchars.
Alex Krohn <[EMAIL PROTECTED]> writes: > So I added: > LANG=C > LC_ALL=C > to the /etc/rc.d/init.d/postgres file and stop, restarted the server. I > then dropped and recreated the database. However still same results. LC_COLLATE overrides LC_ALL, I think --- didn't you previously show us that all the LC_xxx family variables were set in your default environment? You may need to set (or unset if you prefer) all of 'em. Also, I'd really recommend an initdb, not the above half-baked approach, because the above will not fix any problems that the template1 indexes might have with a changed sort order. regards, tom lane
Re: [BUGS] BUG in postgres mathematic
>> The fact that 5*27.81*100 != 27.81*100*5 is certainly a garden-variety >> floating-point roundoff error. However, I think Max has a fair >> complaint here: it seems float-to-int8 conversion is truncating, not >> rounding like the other conversions to integer do. I have changed float8-to-int8 to start with an rint() call, the same as float8-to-int4 and float8-to-int2. This should give the same roundoff behavior as the other cases, including round-to-nearest-even if your hardware supports IEEE-compliant float math. Curiously, this change exposed what I take to be a platform dependency in the int8 regress test. It was computing int8(float8(4567890123456789::int8)) and expecting to get back exactly 4567890123456789. However, that value is 53 bits long and so there is no margin for error in a standard IEEE float8 value. I find that at least on HP hardware, rint() treats the value as inexact and rounds to nearest even: regression=# select round(4567890123456788::float8) - 4567890123456780::float8; ?column? -- 8 (1 row) regression=# select round(4567890123456789::float8) - 4567890123456780::float8; ?column? -- 8 (1 row) regression=# select round(4567890123456790::float8) - 4567890123456780::float8; ?column? -- 10 (1 row) regression=# Whether this is a bug in rint or spec-compliant behavior is unclear, but I'll bet HP's hardware is not the only platform that behaves this way. Since I'm not eager to try to develop a new set of platform-specific int8 expected files at this late hour, I just diked out that test instead... regards, tom lane
Re: [BUGS] BUG in postgres mathematic
On Friday 26 January 2001 18:07, Tom Lane wrote: > Curiously, this change exposed what I take to be a platform dependency > in the int8 regress test. It was computing > int8(float8(4567890123456789::int8)) and expecting to get back exactly > 4567890123456789. However, that value is 53 bits long and so there is > no margin for error in a standard IEEE float8 value. I find that at > least on HP hardware, rint() treats the value as inexact and rounds to > nearest even: > > regression=# select round(4567890123456788::float8) - > 4567890123456780::float8; ?column? > -- > 8 > (1 row) > > regression=# select round(4567890123456789::float8) - > 4567890123456780::float8; ?column? > -- > 8 > (1 row) > > regression=# select round(4567890123456790::float8) - > 4567890123456780::float8; ?column? > -- >10 > (1 row) > > regression=# > > Whether this is a bug in rint or spec-compliant behavior is unclear, but > I'll bet HP's hardware is not the only platform that behaves this way. > Since I'm not eager to try to develop a new set of platform-specific > int8 expected files at this late hour, I just diked out that test > instead... Here is what I get on Linux (PIII): reaster=# select round(4567890123456788::float8) - 4567890123456780::float8; ?column? -- 8 (1 row) reaster=# select round(4567890123456789::float8) - 4567890123456780::float8; ?column? -- 9 (1 row) reaster=# select round(4567890123456790::float8) - 4567890123456780::float8; ?column? -- 10 (1 row) I'm not sure what the problem is either. The PIII has an 80-bit FPU but not sure that matters. When there is no exponent, maybe only 52 bits are really in the mantissa. If you try rounding numbers <= 4503599627370495 (2^52 - 1), maybe you'll get expected results. The hidden bit is 0. Could be that round or rint (whatever it is) always makes the hidden bit 1 when I think it should only be 1 when the exponent is nonzero. I'm no float expert! :) Feel free to correct me. -- Robert B. Easter [EMAIL PROTECTED] - -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- -- http://www.comptechnews.com/~reaster/
Re: [BUGS] select fails on indexed varchars.
Alex Krohn <[EMAIL PROTECTED]> writes: > I added to the startup file: > LANG=C > LC_CTYPE=C > LC_NUMERIC=C > LC_TIME=C > LC_COLLATE=C > LC_MONETARY=C > LC_MESSAGES=C > LC_ALL=C Seems reasonable. It's possible you needed "export" commands in there too, but I wouldn't have thought so (anything coming in from the outer environment should be exported already). > After this, my create test and select still produced the same error. Ugh. Hm, maybe I'm barking up the wrong tree. Let's try a direct test. What do you get from select 'a_b'::text < 'ac'::text; select 'A_B'::text < 'ac'::text; On my machine, these produce 't' in C locale, but 'f' in en_US locale. regards, tom lane
Re: [BUGS] JDBC buggy in 7.1beta3
This is no longer a problem in 7.1. The bug here was the same one introduced by the wrong version of Gunner's bytearray pooling patch being applied. It was fixed in current sources by Peter commenting out the bytearray pooling. thanks, --Barry Bruce Momjian wrote: > > Anyone fixing this bug? > > > I reported a JDBC bug in the 7.1 beta1 release back in December and notice that >the same bug is still present in the current beta 3 sources. The problem relates to >nested cursors failing and is quite simple to reproduce. A simple test case can be >found at: > > > > http://www.twospuds.com/pg7-1b3-jdbc-bug.tar.gz > > > > The 7.0.2 JDBC drivers work fine against the 7.1b3 backend, with a little tweaking >for "long" rows. > > > > Peter (Mount) did acknowledge the bug but I have not heard anything on the list >since then. Is anyone looking at it? Come to think of it, I haven't seen anything >on the lists [HACKERS mainly] from Peter for a while. > > > > Stu. > > -- > > Stu Coates > > Chelmsford, England U.K. > > ICQ:146628 > > http://www.stucoates.com/ > > > > __ > > FREE Personalized Email at Mail.com > > Sign up at http://www.mail.com/?sr=signup > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [BUGS] select fails on indexed varchars.
Hi, > Alex Krohn <[EMAIL PROTECTED]> writes: > >> Beware of changing the postmaster's locale on the fly, however, > >> since that will leave you with corrupted (out-of-order) indexes. > >> Safest to dump/initdb in new locale/reload. > > > How would I go about changing that? Setting LANG and LC_ALL in the pgsql > > users home directory .bashrc? Or do I need to edit the startup file? > > I'd recommend setting LANG/LC_xxx directly in the script you use to fire > up the postmaster. This ensures it will be right no matter whether the > postmaster is launched by a boot script, by hand by someone logged in as > pgsql, by hand by someone su'd from another account with different > locale, yadda yadda. So I added: LANG=C LC_ALL=C to the /etc/rc.d/init.d/postgres file and stop, restarted the server. I then dropped and recreated the database. However still same results. Is this only my installation that has this problem? It's just a very plain RedHat 6.2 with rpm'd install of Postgres, so I'm a little wary about how many other people will be having this problem as well. Cheers, Alex Gossamer Threads Inc. -- Alex KrohnEmail: [EMAIL PROTECTED] Internet Consultant Phone: (604) 687-5804 http://www.gossamer-threads.com Fax : (604) 687-5806
Re: [BUGS] select fails on indexed varchars.
Hi, > > So I added: > > LANG=C > > LC_ALL=C > > to the /etc/rc.d/init.d/postgres file and stop, restarted the server. I > > then dropped and recreated the database. However still same results. > > LC_COLLATE overrides LC_ALL, I think --- didn't you previously show us > that all the LC_xxx family variables were set in your default > environment? You may need to set (or unset if you prefer) all of 'em. > > Also, I'd really recommend an initdb, not the above half-baked approach, > because the above will not fix any problems that the template1 indexes > might have with a changed sort order. I added to the startup file: LANG=C LC_CTYPE=C LC_NUMERIC=C LC_TIME=C LC_COLLATE=C LC_MONETARY=C LC_MESSAGES=C LC_ALL=C as well as to the postgres users default environment. I then shut down postmaster, and as user postgres ran `initdb /var/lib/pgsql`. I then ran /etc/rc.d/init.d/postgres start as root, and then as user postgres ran `createdb mytest`. After this, my create test and select still produced the same error. Ugh. Cheers, Alex
Re: [BUGS] select fails on indexed varchars.
Hi Tom, > Alex Krohn <[EMAIL PROTECTED]> writes: > > links=# select * from foo where a like 'Test/%' > > links-# ; > > a > > --- > > (0 rows) > > This looks like an artifact of the known problems with LIKE index > optimization in non-ASCII locales. What locale are you running the > postmaster in? Is this what you are looking for: [postgres@penguin pgsql]$ locale LANG=en_US LC_CTYPE="en_US" LC_NUMERIC="en_US" LC_TIME="en_US" LC_COLLATE="en_US" LC_MONETARY="en_US" LC_MESSAGES="en_US" LC_ALL=en_US [postgres@penguin pgsql]$ Postmaster is running as user pgsql. Any ideas on workarounds? Cheers, Alex
[BUGS] Bug with foreign keys and importing from a pg_dump file?
Your name : Michael Davis Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : Intel Pentium 233 (2 processors) Operating System (example: Linux 2.0.26 ELF) : Linux (Red Hat 6.2) PostgreSQL version (example: PostgreSQL-7.1): PostgreSQL-7.1 Beta 3 Compiler used (example: gcc 2.8.0) : gcc from Ret Hat 6.2 Please enter a FULL description of your problem: Is there a bug with importing from a pg_dump file and foreign keys? If I create two tables where one table has a foreign key relationship to the other and look at the system tables everything looks great. If I then pg_dump, dropdb, createdb, and import the dump file, then the system tables are reporting the foreign key differently. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- createdb tst psql tst CREATE TABLE genericfield ( GenericID int4 PRIMARY KEY, GenericName varchar(32) NOT NULL, Note text ); CREATE TABLE membergenericfield ( MemberGenericID int4 PRIMARY KEY, GenericID int4 NOT NULL, Note text, CONSTRAINT MemberGenericFieldGenericID_fk FOREIGN KEY (GenericID) REFERENCES GenericField(GenericID) ); select t.tgconstrname, c1.relname as TableName, c2.relname as ForiegnTable FROM pg_trigger t left join pg_class c1 on t.tgrelid = c1.relfilenode left join pg_class c2 on t.tgconstrrelid = c2.relfilenode where substr(t.tgname, 1, 3) = 'RI_' order by t.tgconstrname; tgconstrname | tablename |foriegntable ++ membergenericfieldgenericid_fk | membergenericfield | genericfield membergenericfieldgenericid_fk | genericfield | membergenericfield membergenericfieldgenericid_fk | genericfield | membergenericfield (3 rows) \q pg_dump tst > tst.dmp dropdb tst createdb tst psql -a tst postgres < tst.dmp psql tst postgres select t.tgconstrname, c1.relname as TableName, c2.relname as ForiegnTable FROM pg_trigger t left join pg_class c1 on t.tgrelid = c1.relfilenode left join pg_class c2 on t.tgconstrrelid = c2.relfilenode where substr(t.tgname, 1, 3) = 'RI_' order by t.tgconstrname; tgconstrname | tablename | foriegntable ++-- membergenericfieldgenericid_fk | membergenericfield | pg_xactlock membergenericfieldgenericid_fk | genericfield | pg_xactlock membergenericfieldgenericid_fk | genericfield | pg_xactlock (3 rows) \q
Re: [BUGS] select fails on indexed varchars.
Hi Tom, > > I added to the startup file: > > LANG=C > > LC_CTYPE=C > > LC_NUMERIC=C > > LC_TIME=C > > LC_COLLATE=C > > LC_MONETARY=C > > LC_MESSAGES=C > > LC_ALL=C > > Seems reasonable. It's possible you needed "export" commands in there > too, but I wouldn't have thought so (anything coming in from the outer > environment should be exported already). > > > After this, my create test and select still produced the same error. Ugh. > > Hm, maybe I'm barking up the wrong tree. Let's try a direct test. > What do you get from > > select 'a_b'::text < 'ac'::text; > > select 'A_B'::text < 'ac'::text; > > On my machine, these produce 't' in C locale, but 'f' in en_US locale. Seem to be in C locale: links=# select 'a_b'::text < 'ac'::text; ?column? -- t (1 row) links=# select 'A_B'::text < 'ac'::text; ?column? -- t (1 row) links=# Cheers, Alex Gossamer Threads Inc. -- Alex KrohnEmail: [EMAIL PROTECTED] Internet Consultant Phone: (604) 687-5804 http://www.gossamer-threads.com Fax : (604) 687-5806
Re: [BUGS] select fails on indexed varchars.
Hi Tom, > > [postgres@penguin pgsql]$ locale > > LANG=en_US > > LC_CTYPE="en_US" > > LC_NUMERIC="en_US" > > LC_TIME="en_US" > > LC_COLLATE="en_US" > > LC_MONETARY="en_US" > > LC_MESSAGES="en_US" > > LC_ALL=en_US > > [postgres@penguin pgsql]$ > > > Postmaster is running as user pgsql. Any ideas on workarounds? > > Use locale "C" unless you have a really good reason why you need > en_US sorting order. > > Beware of changing the postmaster's locale on the fly, however, > since that will leave you with corrupted (out-of-order) indexes. > Safest to dump/initdb in new locale/reload. How would I go about changing that? Setting LANG and LC_ALL in the pgsql users home directory .bashrc? Or do I need to edit the startup file? Cheers, Alex
Re: [JDBC] Re: [BUGS] no way in LargeObject API to detect short read?
Peter T Mount <[EMAIL PROTECTED]> writes: > Hmmm, what's the performance issues with this? Is there going to be a problem > with very large LargeObject's? you could probably be smarter about caching previous tell() state, if that's what you mean. jun's hack doesn't actually add any extra buffers or copies. -- Paul M. Aoki / Xerox Palo Alto Research Center / Coyote Hill Road [EMAIL PROTECTED] / Computer Science Laboratory / Palo Alto, CA 94304-1314
Re: [BUGS] select fails on indexed varchars.
Alex Krohn <[EMAIL PROTECTED]> writes: >> On my machine, these produce 't' in C locale, but 'f' in en_US locale. > Seem to be in C locale: So it does. Okay, what was the complete test case again? I'm afraid I didn't save your original message because I wrote it off as a known problem ... regards, tom lane
Re: [BUGS] select fails on indexed varchars.
Alex Krohn <[EMAIL PROTECTED]> writes:
>>> Seem to be in C locale:
>>
>> So it does. Okay, what was the complete test case again?
>> I'm afraid I didn't save your original message because I wrote it off
>> as a known problem ...
> Here it is:
> links=# create table foo ( a char(25) );
> CREATE
> links=# create index foodx on foo (a);
> CREATE
> links=# insert into foo values ('Test/Test');
> INSERT 29689 1
> links=# select * from foo;
> a
> ---
> Test/Test
> (1 row)
> links=# select * from foo where a like 'Test/%';
> a
> ---
> (0 rows)
How odd. I get 'Test/Test' from the last select, under both 7.0.2
and current sources, when using C locale. The query certainly looks
like the kind that would suffer from the LIKE-optimization problem in
non-C locales ... but we seem to have established that you've gotten
the postmaster switched into C locale.
What does EXPLAIN VERBOSE select * from foo where a like 'Test/%';
show?
regards, tom lane
Re: [BUGS] select fails on indexed varchars.
Alex Krohn <[EMAIL PROTECTED]> writes:
>> What does EXPLAIN VERBOSE select * from foo where a like 'Test/%';
>> show?
Well, the indexqual is just what it should be for C locale:
:indxqual ((
{ EXPR :typeOid 16 :opType op :oper
{ OPER :opno 1061 :opid 1052 :opresulttype 16 } :args (
{ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29 :varlevelsup 0 :varnoold 1
:varoattno 1}
{ CONST :consttype 1042 :constlen -1 :constisnull false :constvalue 9 [ 9 0 0 0 84
101 115 116 47 ] :constbyval false })}
{ EXPR :typeOid 16 :opType op :oper
{ OPER :opno 1058 :opid 1049 :opresulttype 16 } :args (
{ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29 :varlevelsup 0 :varnoold 1
:varoattno 1}
{ CONST :consttype 1042 :constlen -1 :constisnull false :constvalue 9 [
9 0 0 0 84 101 115 116 48 ] :constbyval false })}))
This mess translates as
a >= 'Test/'::bpchar AND a < 'Test0'::bpchar
which is what the LIKE index optimizer is supposed to generate.
I infer that one or the other of these conditions yields false on your
machine, which should not be happening if the thing is in C locale.
regards, tom lane
Re: [BUGS] Re: Postgres int rounding
> Is postgres going to use the scientific method of rounding or just > the simple one? Or even make it configurable. As I recall, the > scientific method says that 4.5 should be rounded to 4 and 5.5 should > be rounded to 6. The idea was that even numbers were easier to work > with and rounding all the x.5 numbers up as the common method says > will eventually skew your average. Rounding evens down and odds up > would probably generate a number of bug reports from people who are > not aware of this though... I think some standard required the even/odd rounding behavour. > > -Michael > > > The fact that 5*27.81*100 != 27.81*100*5 is certainly a > > garden-variety floating-point roundoff error. However, I think > > Max has a fair complaint here: it seems float-to-int8 conversion > > is truncating, not rounding like the other conversions to integer > > do. > > > > regression=# select 4.7::float8::int4; > > ?column? > > -- > > 5 > > (1 row) > > > > regression=# select 4.7::float8::int8; > > ?column? > > -- > > 4 > > (1 row) > > > > Seems to me this is a bug we should fix. > > _ > http://fastmail.ca/ - Fast Free Web Email for Canadians > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [BUGS] Bug with foreign keys and importing from a pg_dump file?
Michael Davis <[EMAIL PROTECTED]> writes: > Is there a bug with importing from a pg_dump file and foreign keys? If I > create two tables where one table has a foreign key relationship to the > other and look at the system tables everything looks great. If I then > pg_dump, dropdb, createdb, and import the dump file, then the system tables > are reporting the foreign key differently. It looks like pg_dump neglects to emit a "FROM pktable" clause in its CREATE CONSTRAINT TRIGGER commands, and so the tgconstrrelid field of pg_trigger is not restored. This is a bug I think, although the side effects appear to be minor... regards, tom lane
