[BUGS] BUG #2848: information_schema.key_column_usage does not work
The following bug has been logged online: Bug reference: 2848 Logged by: Tony Marston Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Windows XP Description:information_schema.key_column_usage does not work Details: I have the following query: SELECT key_column_usage.*,constraint_type FROM information_schema.key_column_usage LEFT JOIN information_schema.table_constraints USING (table_schema,table_name,constraint_name) WHERE table_schema='whatever' and table_name='whatever' ORDER BY constraint_type, constraint_name, ordinal_position This works when I am logged on as 'postgres', but if I try it after logging on with a different username it fails with ERROR: relation with OID 18635 does not exist. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2849: Erroronous UPDATE syntax in documentation
The following bug has been logged online: Bug reference: 2849 Logged by: Gurjeet Singh Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.0 Operating system: Windows Description:Erroronous UPDATE syntax in documentation Details: The documentation at http://www.postgresql.org/docs/8.2/interactive/rules-views.html#AEN38727 illustrates the examples using wrong syntax of UPDATE command. The FROM clause is missing; if the FROM clause and the appropriate table is added to the query, it works fine. Sample session: postgres=# create table t1( a int, b int ); CREATE TABLE postgres=# create table t2( a int, b int ); CREATE TABLE postgres=# update t1 set b = t2.b where t1.a = t2.a; ERROR: missing FROM-clause entry for table t2 LINE 1: update t1 set b = t2.b where t1.a = t2.a; ^ postgres=# update t1 set b = t2.b from t2 where t1.a = t2.a; UPDATE 0 postgres=# ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] BUG #2850: Cannot select from information_schema.schemat
The following bug has been logged online: Bug reference: 2850 Logged by: Tony Marston Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Windows XP Description:Cannot select from information_schema.schemat Details: If I am logged on as a user other than 'postgres' and I try the query SELECT * FROM information_schema.schemata I get no results. Yet if I try the equivalent query SELECT * FROM pg_namespace I can see all the available schema names. Why is there a difference when the two queries are supposed to provide the same results? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2849: Erroronous UPDATE syntax in documentation
On 12/21/06, Gurjeet Singh [EMAIL PROTECTED] wrote: The following bug has been logged online: Bug reference: 2849 Logged by: Gurjeet Singh Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.0 Operating system: Windows Description:Erroronous UPDATE syntax in documentation *** Erroneous *** -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
[BUGS] BUG #2851: Error in documentation or in code?
The following bug has been logged online: Bug reference: 2851 Logged by: Gurjeet Singh Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.0 Operating system: Windows XP Professional Description:Error in documentation or in code? Details: The documentation at http://www.postgresql.org/docs/8.2/interactive/config-setting.html states that: Boolean values may be written as ON, OFF, TRUE, FALSE, YES, NO, 1, 0 (all case-insensitive) or any unambiguous prefix of these. But the following doesn't work: postgres=# set enable_seqscan = of; ERROR: parameter enable_seqscan requires a Boolean value postgres=# 'of' is an unambiguous prefix of OFF, but it clearly doesn't work. Is it the documentation that needs fix or is it the code? I tried the following too: set enable_seqscan = of; -- doesn't work set enable_seqscan = off; -- works BTW, I tried TR, TRU, FA, FAL, FALS, YE. They all work fine. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2852: User-defined type name begins with the underscore character (_) can be created
The following bug has been logged online: Bug reference: 2852 Logged by: Pavel Golub Email address: [EMAIL PROTECTED] PostgreSQL version: 8.x Operating system: Windows XP Description:User-defined type name begins with the underscore character (_) can be created Details: Hello, pgsql-bugs. Documentation says: User-defined type names cannot begin with the underscore character (_) and can only be 62 characters long (or in general NAMEDATALEN - 2, rather than the NAMEDATALEN - 1 characters allowed for other names). Type names beginning with underscore are reserved for internally-created array type names. However, such SQL may be executed: CREATE TYPE _my AS (id int4, id2 int4); And then server treats it as an array type. Thus next SQL will be executed too: CREATE TABLE my_table( my_arr my[] ); Checked on PostgreSQL versions (Windows XP): 8.0.6 8.1.0 8.2.0 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2850: Cannot select from information_schema.schemat
Tony Marston [EMAIL PROTECTED] writes: If I am logged on as a user other than 'postgres' and I try the query SELECT * FROM information_schema.schemata I get no results. Yet if I try the equivalent query SELECT * FROM pg_namespace I can see all the available schema names. Why is there a difference when the two queries are supposed to provide the same results? They're not supposed to provide the same results. Per SQL99, the schemata view is supposed to Identify the schemata in a catalog that are owned by a given user. and the SQL definition in the spec makes it clear that it only shows schemas owned by CURRENT_USER or a role that CURRENT_USER is a member of. regards, tom lane ---(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: [BUGS] BUG #2848: information_schema.key_column_usage does not work
Tony Marston [EMAIL PROTECTED] writes: I have the following query: SELECT key_column_usage.*,constraint_type FROM information_schema.key_column_usage LEFT JOIN information_schema.table_constraints USING (table_schema,table_name,constraint_name) WHERE table_schema='whatever' and table_name='whatever' ORDER BY constraint_type, constraint_name, ordinal_position This works when I am logged on as 'postgres', but if I try it after logging on with a different username it fails with ERROR: relation with OID 18635 does not exist. I tried to reproduce this, but it works for me ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] postgresql 8.2.0 -- LIMIT NULL crashes server
Running postgresql 8.2.0 on Debian testing, Linux kernel 2.6.12, Dual Xeon 2.80GHz motherboard, gcc 4.0.3. If I run a statement like this: select * from [table] limit null; it crashes the server. Under 8.1.3, limit NULL was synonymous with limit ALL. Here, the server terminates and restarts with a fatal error message: %:4170 2006-12-21 10:38:10 ESTLOG: server process (PID 4495) was terminated by signal 11 %:4170 2006-12-21 10:38:10 ESTLOG: terminating any other active server processes postgres%tmc:4496 2006-12-21 10:38:10 ESTWARNING: terminating connection because of crash of another server process postgres%tmc:4496 2006-12-21 10:38:10 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. postgres%tmc:4496 2006-12-21 10:38:10 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. postgres%tmc:4497 2006-12-21 10:38:10 ESTWARNING: terminating connection because of crash of another server process postgres%tmc:4497 2006-12-21 10:38:10 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. postgres%tmc:4497 2006-12-21 10:38:10 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. norman%clndb:4499 2006-12-21 10:38:10 ESTFATAL: the database system is in recovery mode %:4170 2006-12-21 10:38:10 ESTLOG: all server processes terminated; reinitializing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] postgresql 8.2.0 -- LIMIT NULL crashes server
Norman Yamada wrote: Running postgresql 8.2.0 on Debian testing, Linux kernel 2.6.12, Dual Xeon 2.80GHz motherboard, gcc 4.0.3. If I run a statement like this: select * from [table] limit null; it crashes the server. thanks for the report - this is already fixed in REL8_2_STABLE and will appear in 8.2.1. for more information see: http://archives.postgresql.org/pgsql-committers/2006-12/msg00025.php Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #2847: Bug with IN statement
The following bug has been logged online:
Bug reference: 2847
Logged by: Sandip
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1
Operating system: Windows XP
Description:Bug with IN statement
Details:
this is my function:
CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
varying, character varying)
RETURNS ret_dv_sp_get_phase AS
$BODY$
SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROMT_PHASE
WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
$BODY$
LANGUAGE 'sql' VOLATILE;
When I run
select * from sp_get_phase ('sandip', 'oms', '4') returns 1
record.this works fine
select * from sp_get_phase ('sandip', 'oms', '1') returns 1 record.this
also works fine... BUT
select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
record.
I tried to execute the SQL statement from the function
SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROMT_PHASE
WHERE (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany') AND
BOOK_NO IN (1,4)
- This Works fine... returns 2 records. What may be the problem?
Thanks in advance.
Regards,
Sandip.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] BUG #2846: inconsistent and confusing handling of underflows, NaNs and INFs
The following bug has been logged online:
Bug reference: 2846
Logged by: Roman Kononov
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.0 and older
Operating system: linux 2.6.15-27-amd64 ubuntu
Description:inconsistent and confusing handling of underflows, NaNs
and INFs
Details:
Please compare the results of the simple queries.
==
test=# select ('NaN'::float4)::int2;
int2
--
0
(1 row)
test=# select ('NaN'::float4)::int4;
int4
-
-2147483648
(1 row)
test=# select ('NaN'::float4)::int8;
ERROR: bigint out of range
test=# select ('nan'::numeric)::int4;
ERROR: cannot convert NaN to integer
==
test=# select abs('INF'::float4);
abs
--
Infinity
(1 row)
test=# select abs('INF'::float8);
ERROR: type double precision value out of range: overflow
==
test=# select -('INF'::float4);
?column?
---
-Infinity
(1 row)
test=# select -('INF'::float8);
ERROR: type double precision value out of range: overflow
==
test=# select (1e-37::float4)*(1e-22::float4);
?column?
--
0
(1 row)
test=# select (1e-37::float4)*(1e-2::float4);
ERROR: type real value out of range: underflow
==
test=# select (1e-300::float8)*(1e-30::float8);
?column?
--
0
(1 row)
test=# select (1e-300::float8)*(1e-20::float8);
ERROR: type double precision value out of range: underflow
==
test=# select ('INF'::float8-'INF'::float8);
?column?
--
NaN
(1 row)
test=# select ('INF'::float8+'INF'::float8);
ERROR: type double precision value out of range: overflow
==
test=# select ('INF'::float4)::float8;
float8
--
Infinity
(1 row)
test=# select ('INF'::float8)::float4;
ERROR: type real value out of range: overflow
==
test=# select cbrt('INF'::float4);
cbrt
--
Infinity
(1 row)
test=# select sqrt('INF'::float4);
ERROR: type double precision value out of range: overflow
==
test=# select ((-32768::int8)::int2)%(-1::int2);
?column?
--
0
(1 row)
test=# select ((-2147483648::int8)::int4)%(-1::int4);
ERROR: floating-point exception
DETAIL: An invalid floating-point operation was signaled. This probably
means an out-of-range result or an invalid operation, such
as division by zero.
==
test=# create table tt (ff float8);
CREATE TABLE
test=# insert into tt values (1e308),(1e308),(1e308);
INSERT 0 3
test=# select * from tt;
ff
1e+308
1e+308
1e+308
(3 rows)
test=# select avg(ff) from tt;
avg
--
Infinity
(1 row)
test=# select stddev(ff) from tt;
stddev
NaN
(1 row)
==
All this things are in float.c
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] BUG #2854: can't log out database system
The following bug has been logged online: Bug reference: 2854 Logged by: Jessica Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Solaris Description:can't log out database system Details: I log into database system, but I don't know how to log out and back to my account prompt. I use psql -test, nothing occurs and I can't be back to my account prompt again. What does it mean such a message as below: FATAL: syntax error in file /home/group/s772/p772-01f/pgsql/CLUST/data/postgresql.conf line 101, near token MB Where can I find a really instruction to how to use postgresql for people who are not experts? Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2853: Internal error XXOO Hangs while attempting to clear table after several successful iterations
The following bug has been logged online: Bug reference: 2853 Logged by: Terry Askew Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.5 Operating system: Windows Server 2000 Description:Internal error XXOO Hangs while attempting to clear table after several successful iterations Details: Batch Process completes, then Data Profiles creation is attempted, but stalls after several Profiles create. To create the next profile,we require a data dump of the current table in memory just created, so that as the profiles are being created on the fly, the table acts as a tempTable. This is the only technical issue left to resolve so that the LIVE system can be used and accepted by our users. Pls Help. Sincerely, Terry Askew Executive VP, Software Development Foresight Technologies, LLC. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2853: Internal error XXOO Hangs while attempting to clear table after several successful iterations
upgrade to 8.2.0 that problem was fixed there (had it myself as well) - thomas - Original Message - From: Terry Askew [EMAIL PROTECTED] To: pgsql-bugs@postgresql.org Sent: Thursday, December 21, 2006 6:13 PM Subject: [BUGS] BUG #2853: Internal error XXOO Hangs while attempting to clear table after several successful iterations The following bug has been logged online: Bug reference: 2853 Logged by: Terry Askew Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.5 Operating system: Windows Server 2000 Description:Internal error XXOO Hangs while attempting to clear table after several successful iterations Details: Batch Process completes, then Data Profiles creation is attempted, but stalls after several Profiles create. To create the next profile,we require a data dump of the current table in memory just created, so that as the profiles are being created on the fly, the table acts as a tempTable. This is the only technical issue left to resolve so that the LIVE system can be used and accepted by our users. Pls Help. Sincerely, Terry Askew Executive VP, Software Development Foresight Technologies, LLC. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2855: SEGV on PL/PGSQL function
Mike wrote: The following bug has been logged online: Bug reference: 2855 Logged by: Mike Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: RHEL AS 4.3 x86_64 Description:SEGV on PL/PGSQL function Details: (retyping this by hand ... forgive any mistakes) I am getting a SEGV every time I attempt to run the following plpgsql function: this seems to be yet another report of the bug already fixed here: http://archives.postgresql.org/pgsql-committers/2006-12/msg00063.php the fix for this will appear in 8.2.1 or you could try to apply the patch manually. Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2854: can't log out database system
Jessica wrote: The following bug has been logged online: Bug reference: 2854 Logged by: Jessica Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Solaris Description:can't log out database system Details: I log into database system, but I don't know how to log out and back to my account prompt. I use psql -test, nothing occurs and I can't be back to my account prompt again. http://www.postgresql.org/docs/8.2/static/app-psql.html has information on how to use the psql commandline client. but i guess your database is not actually called -test ? What does it mean such a message as below: FATAL: syntax error in file /home/group/s772/p772-01f/pgsql/CLUST/data/postgresql.conf line 101, near token MB that looks like a typo in the configuration file - what is on line 101 in that file ? Where can I find a really instruction to how to use postgresql for people who are not experts? the server-administration section in the manual has some information on that: http://www.postgresql.org/docs/8.2/static/admin.html Stefan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2850: Cannot select from information_schema.schemat
--On Donnerstag, Dezember 21, 2006 15:47:40 + Tony Marston [EMAIL PROTECTED] wrote: The fact that the SQL standard says that the schemata view is supposed to Identify the schemata in a catalog that are owned by a given user does not automatically mean that the information can *ONLY* be accessed by the owner. Any user should be able to see the schema to which they have access, owner or not. The standard doesn't specify any given user, it specifies CURRENT_USER. Only if CURRENT_USER is the owner of a schema (or CURRENT_USER inherits ownership by membership) you are able (and allowed) to see the schema. While I admit that this makes it hard to identify catalog schemata by an DBA via the information_schema, the standard is quite clear here. I don't see any ambiguity here If you need to go beyond what the standard allows, you have to use the system catalog. -- Thanks Bernd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] NEED URGENT HELP....
I am using PostgreSql 8.1 with pgAdmin III. OS is XP. this is my
function:
CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
varying, character varying)
RETURNS ret_dv_sp_get_phase AS
$BODY$
SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROM T_PHASE
WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
$BODY$
LANGUAGE 'sql' VOLATILE;
When I run
select * from sp_get_phase ('sandip', 'oms', '4') returns 1
record.this works fine
select * from sp_get_phase ('sandip', 'oms', '1') returns 1
record.this also works fine... BUT
select * from sp_get_phase ('sandip', 'oms', '1,4') this return a
Blank record.
I tried to execute the SQL statement from the function
SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROM T_PHASE
WHERE (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany') AND
BOOK_NO IN (1,4)
- This Works fine... returns 2 records. What may be the problem?
Thanks in advance.
Regards,
Sandip.
--
___
Search for products and services at:
http://search.mail.com
Re: [BUGS] Internal Error XXOO...Mission Critical
The community will need more details to move forward. Please read the following mail; it is a good guide to how to post queries to get answers and responses quickly. http://archives.postgresql.org/pgsql-performance/2004-06/msg00235.php Be a better student, ask good questions :) Good luck. On 12/21/06, Terry Askew [EMAIL PROTECTED] wrote: Postgres continues to hang while we attempt to dump tables after several successful dumps. Is this a memory issue. Where is the data being dumped to…Is it being temporarily stored in some repository that can only handle so much data before it produces an exception message. This is critical in that our user community has all but lost faith in our software. We run a batch process right before data profiles are being created at which point Postgres stalls, and the batch process completes perfectly every night. This will obviously affect any user attempting to iteratively delete large quantities of data before performing the next operation. Please Help….Willing to do whatever it takes to resolve this issue, and I assure you that it will increase the stability of Postgres. Sincerely, Terry Askew Foresight Technologies, LLC. Executive VP, Software Development 770-656-9876 -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
