[BUGS] Bug #880: COMMENT ON DATABASE depends on current database
Marcin Kaminski ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description COMMENT ON DATABASE depends on current database Long Description PostgreSQL has mechanism for commenting databases. Database comments can by read by obj_description(oid), psql \l+ command use it. Database comments should be global, but they are not, when we do \l+ on one database, and then on other, results will be different. I consider it is a bug, database is global object (You can connect to it from any database) but their comments are not. Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Optimizer bug in UPDATE with subselect
I've upgraded my DB to 7.3.1 from 7.2. The following update causes the SEQ SCAN instead of INDEX SCAN (in 7.2 there was no bug like that): create table machines( i_sprzetx int, mod char(10), type char(30) ); create table sprzetx( rowid int, ident char(50) ); create index i_sprzetx on sprzetx(ident); inserts vacuum analyze sprzetx; update machines set i_sprzetx=( select g.rowid from sprzetx g where g.ident=( trim(f.type) || trim(f.mod) ) ); When optimizer meets ANY expression int the query clause switches from INDEX to SEQUENTIAL disregarding the expression is constant for row. Helps ALTER COLUMN to machines, UPDATE to TRIM || TRIM but sure it's not solution. -- Czego nie wykorzystujesz, procentuje! mBIZNES Konto i mBIZNES MAX. Efektywne oprocentowanie 5.64% i 6.59%. > http://link.interia.pl/f16c0 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] stored procedure namespace bug (critical) + COALECSE notice
POSTGRESQL BUG REPORT TEMPLATE
Your name : Roman (Averk) Grits
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : AMD Athlon XP
Operating System (example: Linux 2.0.26 ELF) : Red Hat Linux 7.3 @
2.4.18-3
PostgreSQL version (example: PostgreSQL-7.2.1): PostgreSQL-7.2.1
Compiler used (example: gcc 2.95.2) : set up from rpm binary
distribution.
Please enter a FULL description of your problem:
When I create temporary table inside a function overriding persistent one,
usage if it's identifer is bound to persistent table instead of temporary at
the first function in chain. In any descendant calls or code outside the
function temporary table is used. See the code.
Also, COALESCE implementation via CASE suffers much if complex queries
inside it use some table updates (e.g. additions) - it makes them to insert
the data TWICE, leading to very unpleasant results. I had to make another
CASE workaround, but consider making COALESCE more accurate. I guess it's a
bug, so please check how does this case comply with SQL language itself.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
see this (forged-simplified) piece of code:
create table "test_table" ("somedata" text);
create function "test_insert"
(text)
returns bool as'
insert into test_table values ($1);
/* this piece of code inserts data to temporary table*/
select true;
' language sql;
create function "test_select" ()
returns text as'
create temporary table test_table (
"somedata" text);
select test_insert(\'pattern1\');
/* so we have inserted data to newly created table here */
select somedata from test_table;
/* but when we use it inside _this_ function, we refer to persistent table
*/
' language sql;
select test_select();
/* we get NULL here, as there's no data in persistent table */
select somedata from test_table;
/* but we get "pattern1" here as we refer to temporary table that overrides
peristent one, according to documentation*/
While adding few inserts to core functions and dropping test_table after
function call, but before last select I've figured out that test_select()
uses the very first persistent definition while test_insert() and any
clauses outside test_select() use temp definition from test_select(). Seems
like pretty nasty bug - I've spent some time wondering what's up with my
stored procedure code until I guessed it might not be my fault. Please reply
with comments (do you approve or reject this report: COALESCE notice also).
If you know how this problem might be fixed, list the solution below:
-
haven't tried to fix it yet, got only few SQL workarounds
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] Bug #881: floor function returning double precision with integer arguments in 7.3, returns numeric in 7.2 with the same statement
Allan Oepping ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description floor function returning double precision with integer arguments in 7.3, returns numeric in 7.2 with the same statement Long Description Return datatype of the floor function is different between PostgreSQL 7.2 and PostgreSQL 7.3 the documentation for PostgreSQL 7.3 states that PostgreSQL 7.3's floor function should return type numeric. In 7.3 it seems to be returning the double precision datatype. Oracle 8.0.5 correctly executes the test statments along with PostgreSQL 7.2. (we have to be compatible) The documentation mentioned: http://www.postgresql.org/docs/view.php?version=7.3&file=functions-math.html To duplicate: create table floor_test ( test bigint not null ); insert into floor_test (test) values (8); insert into floor_test (test) values (12); insert into floor_test (test) values (136); select * from floor_test where mod(floor(test / 128),2)=1; select * from floor_test where mod(trunc(test / 128,0),2)=1; select * from floor_test where mod(floor(test / 4),2)=1; select * from floor_test where mod(trunc(test / 4,0),2)=1; SELECT version(); delete from floor_test; drop table floor_test; behavior(correct) in 7.2.3(using the psql client): Linux version 2.4.18-17.8.0smp ([EMAIL PROTECTED]) (gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) #1 SMP Tue Oct 8 12:39:01 EDT 2002 internet=# create table floor_test internet-# ( internet(# test bigint not null internet(# ); CREATE internet=# internet=# insert into floor_test (test) values (8); INSERT 11830189 1 internet=# insert into floor_test (test) values (12); INSERT 11830190 1 internet=# insert into floor_test (test) values (136); INSERT 11830191 1 internet=# internet=# select * from floor_test where mod(floor(test / 128),2)=1; test -- 136 (1 row) internet=# select * from floor_test where mod(trunc(test / 128,0),2)=1; test -- 136 (1 row) internet=# select * from floor_test where mod(floor(test / 4),2)=1; test -- 12 (1 row) internet=# select * from floor_test where mod(trunc(test / 4,0),2)=1; test -- 12 (1 row) internet=# internet=# SELECT version(); version - PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) internet=# internet=# delete from floor_test; DELETE 3 internet=# drop table floor_test; DROP behavior in 7.3.1(using the psql client): Linux version 2.4.18-19.7.x ([EMAIL PROTECTED]) (gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-112)) #1 Thu Dec 12 09:00:42 EST 2002 internet=# create table floor_test internet-# ( internet(# test bigint not null internet(# ); CREATE TABLE internet=# internet=# insert into floor_test (test) values (8); INSERT 1938595 1 internet=# insert into floor_test (test) values (12); INSERT 1938596 1 internet=# insert into floor_test (test) values (136); INSERT 1938597 1 internet=# internet=# select * from floor_test where mod(floor(test / 128),2)=1; ERROR: Function mod(double precision, integer) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts internet=# select * from floor_test where mod(trunc(test / 128,0),2)=1; test -- 136 (1 row) internet=# select * from floor_test where mod(floor(test / 4),2)=1; ERROR: Function mod(double precision, integer) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts internet=# select * from floor_test where mod(trunc(test / 4,0),2)=1; test -- 12 (1 row) internet=# internet=# SELECT version(); version - PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) internet=# internet=# delete from floor_test; DELETE 3 internet=# drop table floor_test; DROP TABLE internet=# Sample Code create table floor_test ( test bigint not null ); insert into floor_test (test) values (8); insert into floor_test (test) values (12); insert into floor_test (test) values (136); select * from floor_test where mod(floor(test / 128),2)=1; select * from floor_test where mod(trunc(test / 128,0),2)=1; select * from floor_test where mod(floor(test / 4),2)=1; select * from floor_test where mod(trunc(test / 4,0),2)=1; SELECT version(); delete from floor_test; drop table floor_test; No file was uploaded with this report ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Unable to Install PostGreSQL on Red Hat Linux release 7.2
Dear Sir, I have downloaded fallowing files from http://www.postgresql.org 1. mx-2.0.1-1.i386.rpm 2. postgresql-odbc-7.1.2-5PGDG.i386.rpm 3. postgresql-7.1.2-5PGDG.i386.rpm 4. postgresql-perl-7.1.2-5PGDG.i386.rpm 5. postgresql-contrib-7.1.2-5PGDG.i386.rpm 6. postgresql-python-7.1.2-5PGDG.i386.rpm 7. postgresql-devel-7.1.2-5PGDG.i386.rpm 8. postgresql-server-7.1.2-5PGDG.i386.rpm 9. postgresql-docs-7.1.2-5PGDG.i386.rpm 10. postgresql-tcl-7.1.2-5PGDG.i386.rpm 11. postgresql-jdbc-7.1.2-5PGDG.i386.rpm 12. postgresql-test-7.1.2-5PGDG.i386.rpm 13. postgresql-libs-7.1.2-5PGDG.i386.rpm 14. postgresql-tk-7.1.2-5PGDG.i386.rpm and Tried to install on Red Hat Linux release 7.2 (Enigma) Kernel 2.4.7-10 on an i686, I got the fallowing Errors. [root@majcompnew p7.2]# rpm -i postgresql-contrib-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql = 7.1.2 is needed by postgresql-contrib-7.1.2-5PGDG libcrypto.so.1 is needed by postgresql-contrib-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-contrib-7.1.2-5PGDG libssl.so.1 is needed by postgresql-contrib-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-contrib-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql = 7.1.2 is needed by postgresql-contrib-7.1.2-5PGDG libcrypto.so.1 is needed by postgresql-contrib-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-contrib-7.1.2-5PGDG libssl.so.1 is needed by postgresql-contrib-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-devel-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql-libs = 7.1.2 is needed by postgresql-devel-7.1.2-5PGDG libcrypto.so.1 is needed by postgresql-devel-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-devel-7.1.2-5PGDG libssl.so.1 is needed by postgresql-devel-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-docs-7.1.2-5PGDG.i386.rpm [root@majcompnew p7.2]# rpm -i postgresql-jdbc-7.1.2-5PGDG.i386.rpm [root@majcompnew p7.2]# rpm -i postgresql-libs-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-libs-7.1.2-5PGDG libssl.so.1 is needed by postgresql-libs-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-perl-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-perl-7.1.2-5PGDG libssl.so.1 is needed by postgresql-perl-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-python-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-python-7.1.2-5PGDG libssl.so.1 is needed by postgresql-python-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-server-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql = 7.1.2 is needed by postgresql-server-7.1.2-5PGDG libcrypto.so.1 is needed by postgresql-server-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-server-7.1.2-5PGDG libssl.so.1 is needed by postgresql-server-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-tcl-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-tcl-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-tcl-7.1.2-5PGDG libssl.so.1 is needed by postgresql-tcl-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-test-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql = 7.1.2 is needed by postgresql-test-7.1.2-5PGDG oot@majcompnew p7.2]# rpm -i postgresql-tk-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-tk-7.1.2-5PGDG libpgtcl.so.2 is needed by postgresql-tk-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-tk-7.1.2-5PGDG libssl.so.1 is needed by postgresql-tk-7.1.2-5PGDG I would appreciate if you could provide me help in installing PostGreSQL on Red Hat Linux release 7.2. Thanks and Regards, Sharanayya G M Majoris Systems Pvt. Ltd. Bangalore ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #878: different format of float values in 7.2.and
On Mon, 20 Jan 2003 03:13:38 -0500 (EST) [EMAIL PROTECTED] wrote: > strings without any unnecessary padding zeroes. I use a usual > postgresql*-7.3.1-1PGDG.i386.rpm taken from your site, if it makes > sense. Please take a look into this. > > Sample Code > The following line: > > SELECT 1875/1000.0 > > produces different results. In 7.2.3 it is: > > ?column? > - >1.875 > > while in 7.3.1 it is: > > ?column? > - > 1.87500 qurczak=# SELECT 1875::float/1000.0; ?column? -- 1.875 (1 row) qurczak=# SELECT (1875/1000.0)::float; float8 1.875 (1 row) It works ;] You try something this int/float = yee khmm but int::float/float= float, or something like this ;] -- "Nieobecni nie mają racji" Marcin Winkler ---(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: [BUGS] Bug #881: floor function returning double precision with integer arguments in 7.3, returns numeric in 7.2 with the same statement
[EMAIL PROTECTED] writes: > Return datatype of the floor function is different between PostgreSQL 7.2 and >PostgreSQL 7.3 the documentation for PostgreSQL 7.3 states that PostgreSQL 7.3's >floor function should return type numeric. In 7.3 it seems to be returning the double >precision datatype. Oracle 8.0.5 correctly executes the test statments along with >PostgreSQL 7.2. (we have to be compatible) > The documentation mentioned: > http://www.postgresql.org/docs/view.php?version=7.3&file=functions-math.html This table seems not to have been updated when we added float versions of ceil(), floor(), and sign(). You won't get any support for ripping them out again, though. I suggest adding casts to the queries, or possibly defining a version of mod() that takes float8. regards, tom lane ---(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: [BUGS] Unable to Install PostGreSQL on Red Hat Linux release 7.2
On Thursday 16 January 2003 07:58, Sharanayya G M wrote: > [root@majcompnew p7.2]# rpm -i postgresql-contrib-7.1.2-5PGDG.i386.rpm > error: failed dependencies: > postgresql = 7.1.2 is needed by postgresql-contrib-7.1.2-5PGDG > libcrypto.so.1 is needed by postgresql-contrib-7.1.2-5PGDG > libreadline.so.4.1 is needed by postgresql-contrib-7.1.2-5PGDG > libssl.so.1 is needed by postgresql-contrib-7.1.2-5PGDG You need to install all of these on one line. You also need to get OpenSSL and readline installed. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Optimizer bug in UPDATE with subselect
"Ace" <[EMAIL PROTECTED]> writes: > I've upgraded my DB to 7.3.1 from 7.2. The following update causes the SEQ > SCAN instead of INDEX SCAN (in 7.2 there was no bug like that): I don't believe that. No version of Postgres would have generated an index scan on this query, because you've got a type mismatch: ident is declared char(50) but the result of the trim()||trim() expression will be of type text. I'd recommend changing all the column datatypes from char(N) to text. Then you could get rid of the trim() calls, and save yourself a bunch of disk space too. Those trailing spaces aren't free. regards, tom lane ---(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
