[BUGS] BUG #1540: Enhancement request: 'ambiguous' column reference in psql
The following bug has been logged online: Bug reference: 1540 Logged by: Richard Neill Email address: [EMAIL PROTECTED] PostgreSQL version: 8.01 Operating system: Linux Description:Enhancement request: 'ambiguous' column reference in psql Details: Dear Postgresql team, I have a small suggestion, which isn't quite a bug, but where psql throws an error which it could in principle recover from. These occur when a column reference is ambiguous, but isn't really, because of information supplied in the join. Here is an example, which I just tested in 8.01. These are the database tables: -- tbl_instruments: instrument character varying priceband smallint -- tbl_prices: priceband smallint pounds double precision --- This query fails: SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE tbl_instruments.priceband=tbl_prices.priceband; ERROR: column reference "priceband" is ambiguous --- This query succeeds: - SELECT instrument,tbl_instruments.priceband,pounds FROM tbl_instruments,tbl_prices WHERE tbl_instruments.priceband=tbl_prices.priceband; -- I think that the first query ought to succeed, since although priceband is ambiguous (it could mean either tbl_prices.priceband or tbl_instruments.priceband), the information in the WHERE clause means that they are explicitly equal, and so it doesn't matter which one we use. Thank you very much for all your work - Postgresql is really useful to me. Richard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1535: ODBC bug in pgslqodbc-08_00_0101
The following bug has been logged online: Bug reference: 1535 Logged by: Miguel Juan Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.2 Operating system: W2000Pro Description:ODBC bug in pgslqodbc-08_00_0101 Details: Hy, I have installed the last version of the ODBC driver (psqlodbc-08_00_0101) and when I make a 'SELECT *' from a table with varchar or char fields, they did not apear in the result records. Neither if I just select this fields in a query. I use the ODBC driver through the BDE (Borland Database Engine) and my server a PostgreSQL 7.4.2 on i386-redhat-linux-gnu. And I have tried with a UNICODE DB and with a SQL_ASCII DB. With MS Acces97 I get the fields name but with all fields as "Deleted" With the version 7.3.2 of ODBC driver works fine. Regards Miguel Juan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1539: Suggestion
The following bug has been logged online: Bug reference: 1539 Logged by: Patrick Boulay Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: Unix Solaris Description:Suggestion Details: Sometime I do transaction and I do a couple of commands on SQL shell and I forgot to commit my commands before testing my software. Is it possible to change a prompt character during a transaction? instead of: database=# it can be database=> or something like that!! After the commit, the prompt come back to database=# example: database=# begin; database=> insert into somewhere values(1); ... database=> end; database=# it's more clear! :) Patrick ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1537: alter table statement
The following bug has been logged online: Bug reference: 1537 Logged by: biju Email address: [EMAIL PROTECTED] PostgreSQL version: 7.2.2 Operating system: linux-gnu Description:alter table statement Details: i tried to execute alter table statement for a particular table. i did it many ways, but not worked. i tried with the same syntax given in the postgresql manual but in vain can u suggest anything abt this... Thanks ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1538: Unable to reattach to shared memory
The following bug has been logged online: Bug reference: 1538 Logged by: Ludwig Lim Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.1 Operating system: Windows XP SP.1 Description:Unable to reattach to shared memory Details: 2005-03-11 12:32:23 LOG: background writer process (PID 868) exited with exit code 0 2005-03-11 12:32:23 LOG: terminating any other active server processes 2005-03-11 12:32:23 LOG: all server processes terminated; reinitializing FATAL: could not reattach to shared memory (key=5432001, addr=0126): Invalid argument FATAL: could not reattach to shared memory (key=5432001, addr=0126): Invalid argument 2005-03-11 12:32:23 LOG: background writer process (PID 1116) exited with exit code 0 2005-03-11 12:32:23 LOG: terminating any other active server processes 2005-03-11 12:32:23 LOG: all server processes terminated; reinitializing FATAL: could not reattach to shared memory (key=5432001, addr=0126): Invalid argument FATAL: could not reattach to shared memory (key=5432001, addr=0126): Invalid argument 2005-03-11 12:32:23 LOG: background writer process (PID 1740) exited with exit code 0 2005-03-11 12:32:23 LOG: terminating any other active server processes 2005-03-11 12:32:23 LOG: all server processes terminated; reinitializing ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1541: Unusually long INSERT times after fresh clean/CREATE TABLES
The following bug has been logged online: Bug reference: 1541 Logged by: John Engelhart Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.1 Operating system: FreeBSD 5.3-p5 Description:Unusually long INSERT times after fresh clean/CREATE TABLES Details: Recently upgraded from 7.4.7 to 8.0.1 and have discovered an oddity. Since I'm developing an SQL based application, I routinely "start from scratch" with a script that deletes all the tables in my database and rebuilds them. A problem started when I upgraded from 7.4.7 to 8.0.1 in that the first run after the clean takes an UNUSUALLY long time to complete, on the order of a few hundred inserts/sec. A normal run takes ~30 seconds. A from scratch run takes ~15 minutes, with the next run completing in 30 seconds with zero changes. I believe I've eliminated any host or build anomolies due to a simple fact: Starting fresh, running the data inserter, and then aborting a few seconds later... then re-running the data-inserter runs at normal speed. It's as if it need a few records in the DB in order to work at normal speeds. It almost seems as if it's doing a worst case INSERT update if there are no primary keys when the BEGIN transaction starts? I have also dropped all CREATE INDEX statements from my rebuild script and it behaves exactly the same. The only other 'unusual' thing remaining would be the various SERIAL NOT NULL UNIQUE PRIMARY KEY statements. The specific tables and data are quite lengthy, and not appropriate for pasting to fully recreate the problem. I can send a tarball if requested. As an example of the times involved (same average times, always repeatable): [EMAIL PROTECTED] simp_sql> time simp_sql [IMMEDIATLY after, no accesses to the database anywhere inbetween] [EMAIL PROTECTED] simp_sql> time simp_sql < test8.cpp INFO: Opening database connection @136/initialize/main_sql.c INFO: Begining parse @47/main/main_sql.c INFO: GC- After parse: Heap 348Kb Free 24Kb @49/main/main_sql.c INFO: Performing house keeping and index rebuild @58/main/main_sql.c INFO: Marking all blocks @61/main/main_sql.c INFO: Performing house keeping and index rebuild @76/main/main_sql.c INFO: GC- Before exiting: Heap 348Kb Free 324Kb @79/main/main_sql.c INFO: Closing database connection @95/cleanup/main_sql.c 2.990u 2.200s 0:40.00 12.9% 82+4398k 0+0io 0pf+0w [EMAIL PROTECTED] simp_sql> FYI, my inserter uses the boehm garbage collection library, so the heap/free numbers are non deterministic between runs. This should not be a contributing issue at all. The "performing house cleaning" is a "vacuum full analyze" command since it's just inserted a large amount data both times. All the INSERTS are wrapped in a BEGIN/COMMIT pair (per run, not per statement). Now a clean refresh, run the inserter with a ^C break, and immediatly re-run it: [EMAIL PROTECTED] simp_sql> reload [drops the database, creates it, then recreates the tables/functions] [EMAIL PROTECTED] simp_sql> time simp_sql < test8.cpp INFO: Opening database connection @136/initialize/main_sql.c INFO: Begining parse @47/main/main_sql.c ^C0.094u 0.075s 0:01.04 15.3% 36+361k 0+0io 0pf+0w [EMAIL PROTECTED] simp_sql> time simp_sql < test8.cpp INFO: Opening database connection @136/initialize/main_sql.c INFO: Begining parse @47/main/main_sql.c INFO: GC- After parse: Heap 348Kb Free 136Kb @49/main/main_sql.c INFO: Performing house keeping and index rebuild @58/main/main_sql.c INFO: Marking all blocks @61/main/main_sql.c INFO: Performing house keeping and index rebuild @76/main/main_sql.c INFO: GC- Before exiting: Heap 348Kb Free 324Kb @79/main/main_sql.c INFO: Closing database connection @95/cleanup/main_sql.c 2.943u 2.218s 0:33.20 15.5% 90+5051k 0+0io 0pf+0w [EMAIL PROTECTED] simp_sql> Hopefully this is enough info to track down and recreate the problem independently. If not, I can send you the program, db dumps, etc to help you recreate it, it's a bit to big to include here. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP
On Thu, 10 Mar 2005, Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) DB2 (version 8.1) gives 0, 1, 0, 1. - Heikki ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1536: alter command
The following bug has been logged online: Bug reference: 1536 Logged by: biju Email address: [EMAIL PROTECTED] PostgreSQL version: 7.2.2 Operating system: linux-gnu Description:alter command Details: i tried to run alter command , but it didnt work. can u help for running alter table statement. Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #1534: 2^32-1 commands limitation in one transcation
The following bug has been logged online: Bug reference: 1534 Logged by: yingqun wang Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: fedora Description:2^32-1 commands limitation in one transcation Details: My plpgsql function loop through a table(40,000) and do a calculation for 24 hours* 365 days for each record. When the preogram run about 74 records, it give a error that can not have more than 2^32-1 commands in one transcation. How can I avoid this? thanks ying ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1533: "*OLD*" relation not recognized in CREATE RULE
The following bug has been logged online: Bug reference: 1533 Logged by: Henk van den Toorn Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.3-7 Operating system: Freebsd 5.3 Description:"*OLD*" relation not recognized in CREATE RULE Details: When creating a rule: CREATE RULE rulename AS ON UPDATE table WHERE OLD.index = NEW.index DO INSTEAD NOTHING; Postgresql responds with ERROR: relation "*OLD*" does not exist All kind of variations on "*OLD*" like: old.index; OLD.index; "*OLD*".index do not work, nor does changing the order of the elements in the WHERE clause. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP
Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) Firebird 1.5.1 FreeBSD 5.3 Database: test SQL> drop table tab; SQL> create table tab (col integer); SQL> select 1 from tab having 1=0; SQL> select 1 from tab having 1=1; 1 SQL> insert into tab values(1); SQL> insert into tab values(2); SQL> select 1 from tab having 1=0; SQL> select 1 from tab having 1=1; 1 SQL> ---(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] [HACKERS] We are not following the spec for HAVING without GROUP BY
On Thu, Mar 10, 2005 at 12:44:50PM -0500, Tom Lane wrote: > Would those of you with access to other DBMSes try this: DB2/LINUX 8.1.6 > create table tab (col integer); > select 1 from tab having 1=0; 1 --- 0 record(s) selected. > select 1 from tab having 1=1; 1 --- 1 1 record(s) selected. > insert into tab values(1); > insert into tab values(2); > select 1 from tab having 1=0; 1 --- 0 record(s) selected. > select 1 from tab having 1=1; 1 --- 1 1 record(s) selected. -joh ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP BY
On Oracle 9.2 you get 0, 0, 0, and 2 rows. --Barry SQL> create table tab (col integer); Table created. SQL> select 1 from tab having 1=0; no rows selected SQL> select 1 from tab having 1=1; no rows selected SQL> insert into tab values (1); 1 row created. SQL> insert into tab values (2); 1 row created. SQL> select 1 from tab having 1=0; no rows selected SQL> select 1 from tab having 1=1; 1 -- 1 1 SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, March 10, 2005 9:45 AM To: pgsql-hackers@postgresql.org; pgsql-bugs@postgresql.org Subject: Re: [HACKERS] We are not following the spec for HAVING without GROUP BY I wrote: > This is quite clear that the output of a HAVING clause is a "grouped > table" no matter whether the query uses GROUP BY or aggregates or not. > What that means is that neither the HAVING clause nor the targetlist > can use any ungrouped columns except within aggregate calls; that is, > select col from tab having 2>1 > is in fact illegal per SQL spec, because col isn't a grouping column > (there are no grouping columns in this query). Actually, it's even more than that: a query with HAVING and no GROUP BY should always return 1 row (if the HAVING succeeds) or 0 rows (if not). If there are no aggregates, the entire from/where clause can be thrown away, because it can have no impact on the result! Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP
Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) regards, tom lane From SQL server 2000 with a service pack, I get: zero rows from the first query (having 1=0); one row, col value 1, from second query (having 1=1); ...run inserts... zero rows from the third query (having 1=0); one row, col value 1, from forth query (having 1=1); K. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP BY
> What that means is that neither the HAVING clause nor the targetlist > can use any ungrouped columns except within aggregate calls; that is, > > select col from tab having 2>1 Informix: select tabname from systables having 2 > 1; 294: The column (tabname) must be in the GROUP BY list. select tabname from systables group by 1 having 2 > 1; all rows returned select tabname from systables group by 1 having 1 > 2; no rows found Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
R: [BUGS] Fault when return strings over 256 characters in PLpgSQL
It could be a pgAdmin III presentation fault. I'm using PostgreSQL 8.0.0 on Windows 2k and I've got the same problem but only when inquiring via pgAdmin, using psql from command line all goes ok. I think that the resulting string are correct, that's why the function "position(\'.\' in userName);" doesn't find the dot. Matteo Brusamolin [EMAIL PROTECTED] wrote: > I have a PLpgSQL function that returns a string (varchar): if this > string > is over 256 characters long then the last three characters are corrupted: > replaced by the string ' (.' I'm skeptical: there is nothing special about 256 characters as far as the varchar implementation is concerned, nor is the string ' (.' of any significance. Running your function (albeit on Linux) yields: neilc=# select testconverttousername(); testconverttousername 123456781234567812345678123456781234567812345678123456781234567812345678 123456781234567812345678123456781234567812345678123456781234567812345678 123456781234567812345678123456781234567812345678123456781234567812345678 123456781234567812345678123456781234567888 (1 row) i.e. what one would expect. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1532: typecast problem between arrays of an int8 derived datatype and varchar[]
The following bug has been logged online: Bug reference: 1532 Logged by: Ezequiel Tolnay Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.1 Operating system: Windows 2003 Server Description:typecast problem between arrays of an int8 derived datatype and varchar[] Details: I've created the cardnumber_t datatype, which is an int8, to provide implicit typecasting with varchar padding the result with zeroes. Conversions work as expected between int4, int8, cardnumber_t and varchar. They also work fine between int4[], int8[] and cardnumber_t[], but when an attempt is made to convert a cardnumber_t[] to a varchar[], the connection is dropped. The code used to create the cardnumber_t is the following: CREATE OR REPLACE FUNCTION cardnumber_t_in(cstring) RETURNS cardnumber_t AS 'int8in' LANGUAGE INTERNAL IMMUTABLE WITH (isstrict); CREATE OR REPLACE FUNCTION cardnumber_t_out(cardnumber_t) RETURNS cstring AS 'int8out' LANGUAGE INTERNAL IMMUTABLE WITH (isstrict); CREATE TYPE cardnumber_t ( INTERNALLENGTH = 8, INPUT = cardnumber_t_in, OUTPUT = cardnumber_t_out, STORAGE = plain, ALIGNMENT = double ); CREATE OR REPLACE FUNCTION to_int8(cardnumber_t) RETURNS int8 AS 'int8up' LANGUAGE INTERNAL IMMUTABLE WITH (isstrict); CREATE CAST (cardnumber_t AS int8) WITH FUNCTION to_int8(cardnumber_t) AS IMPLICIT; CREATE OR REPLACE FUNCTION to_cardnumber_t(int8) RETURNS cardnumber_t AS 'int8up' LANGUAGE INTERNAL IMMUTABLE WITH (isstrict); CREATE CAST (int8 AS cardnumber_t) WITH FUNCTION to_cardnumber_t(int8) AS IMPLICIT; CREATE OR REPLACE FUNCTION to_cardnumber_t(int4) RETURNS cardnumber_t AS 'int48' LANGUAGE INTERNAL IMMUTABLE WITH (isstrict); CREATE CAST (int4 AS cardnumber_t) WITH FUNCTION to_cardnumber_t(int4) AS IMPLICIT; CREATE DOMAIN cardnumber AS cardnumber_t CONSTRAINT ch_cardnumber_range CHECK (VALUE between 1 AND ); CREATE OR REPLACE FUNCTION fc_cardnumber_t_to_varchar (cn cardnumber_t) RETURNS varchar AS $$ BEGIN RETURN substring((1::int8+cn)::varchar, 2, 16); END; $$ LANGUAGE plpgsql; CREATE CAST (cardnumber_t as varchar) WITH FUNCTION fc_cardnumber_t_to_varchar(cardnumber_t) AS IMPLICIT; The following are successful typecast tests: SELECT 10::int4::int8::cardnumber_t::varchar SELECT ((ARRAY[1,2,3])::int8[])::cardnumber_t[] The following fails and drops the connection SELECT ((ARRAY[1,2,3])::cardnumber_t[])::varchar[] ---(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
[BUGS] CC Date format code defaults to current centry
joe=> select to_date('19450323','CCYYMMDD');
to_date
2045-03-23
(1 row)
joe=> select to_date('19450323','MMDD');
to_date
1945-03-23
(1 row)
I thought the former would be "more" correct. But it seems I am mistaken.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] could not read, could not write, could not fsync, Windows 2000, PostgreSQL 8.0.1
I encounter the similar problem in "make check" (win2k-mingw, 8.0.1). The regression test could randomly fail due to "could not write block (Invalid argument)" problem or could not "remove file" problem. Regards, Qingqing p.s. I believe this could be a potential serious problem, so I forward it to pgsql.hackers. --- ""Jean-Pierre Pelletier"" <[EMAIL PROTECTED]> We are running PostgreSQL 8.0.1 since last week and have these messages in our PostgreSQL log file: 2005-02-10 10:27:19 FATAL: could not read block 38 of relation 1663/17269/16676: Invalid argument 2005-02-10 10:27:19 FATAL: could not read block 46 of relation 1663/17269/16676: Invalid argument 2005-02-10 10:27:19 FATAL: could not read block 50 of relation 1663/17269/16676: Invalid argument 16676 is table "pgdepend" 2005-02-14 12:19:46 FATAL: could not read block 7 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 20 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 22 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 14 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 18 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 24 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 8 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 19 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 11 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 21 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 25 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 23 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 13 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 9 of relation 1663/17269/1247: Invalid argument 2005-02-14 12:19:46 FATAL: could not read block 12 of relation 1663/17269/1247: Invalid argument 1247 is table "pgtype" 2005-02-16 10:48:26 ERROR: could not write block 61 of relation 1663/17269/16676: Invalid argument 2005-02-16 10:48:26 CONTEXT: writing block 61 of relation 1663/17269/16676 16676 is table "pgdepend" 2005-02-16 12:47:03 ERROR: could not write block 3 of relation 1663/17269/1614690: Invalid argument 2005-02-16 12:47:03 CONTEXT: writing block 3 of relation 1663/17269/1614690 We couldn't find what 1614690 is? 2005-02-18 05:32:06 LOG: could not fsync segment 0 of relation 1663/17269/1677179: Permission denied 2005-02-18 05:32:06 ERROR: storage sync failed on magnetic disk: Permission denied ... 2005-02-18 07:58:28 ERROR: storage sync failed on magnetic disk: Permission denied 2005-02-18 07:58:29 LOG: could not fsync segment 0 of relation 1663/17269/1677179: Permission denied These two messages are repeated every seconds for almost 2.5 hours Again, we couldn't find what 1677179 is? We are on Windows 2000 Server, Service Pack 4 and were successfully running PostgreSQL 7.4.1 before that. We have done a vacuum, analyze and reindex on pgdepend and pgtype and restarted PostgreSQL a few times, we had no problems doing that but the error messages are still there. Is this normal and if not, how do we fix that? Thanks Jean-Pierre Pelletier p.s.: We also have messages "FATAL: could not read from statistics collector pipe" approx. twice an hour. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #1530: search on uuid indexed article returns no resultat
The following bug has been logged online:
Bug reference: 1530
Logged by: Avram
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.6 testing
Operating system: Debian kernel 2.4.18 testing (pg-uuid 0.01)
Description:search on uuid indexed article returns no resultat
Details:
The SELECT command using a 'uuid' field indexed with pk_situation returns no
result. The error dissapear after the backup and the restore of the database
or after deleting the index.
--
-- PostgreSQL database dump
--
SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- TOC entry 2211 (class 0 OID 0)
-- Name: DUMP TIMESTAMP; Type: DUMP TIMESTAMP; Schema: -; Owner:
--
-- Started on 2005-03-08 09:57:21 Paris, Madrid
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = true;
--
-- TOC entry 1967 (class 1259 OID 43638)
-- Dependencies: 2206 2 903 903
-- Name: situation; Type: TABLE; Schema: public; Owner: postgres;
Tablespace:
--
CREATE TABLE situation (
id uuid DEFAULT newid() NOT NULL,
nom character varying(100) NOT NULL,
description text,
texteexpositif text NOT NULL,
resultatattendu text NOT NULL,
bibliographie text,
datecreation timestamp without time zone NOT NULL,
idseminaire uuid NOT NULL
);
ALTER TABLE public.situation OWNER TO postgres;
--
-- TOC entry 2210 (class 0 OID 43638)
-- Dependencies: 1967
-- Data for Name: situation; Type: TABLE DATA; Schema: public; Owner:
postgres
--
COPY situation (id, nom, description, texteexpositif, resultatattendu,
bibliographie, datecreation, idseminaire) FROM stdin;
47aa019e-8040-11d9-98e1-00e0180dcd8esddsdsdsds ds dsdsdsdss
2005-02-16
17:29:15.27033 05a7c92a-4dbe-11d9-825d-00e0180dcd8e
f30351de-80b9-11d9-9d32-00e0180dcd8edssdsdd fsdds fdsfd dsfsdf dsfdsf
2005-
02-17 08:00:11.821739 05a7c92a-4dbe-11d9-825d-00e0180dcd8e
1a3f3eaa-80e4-11d9-9899-00e0180dcd8esdf dsfdfs sdf sdf dfsdfs
2005-02-17
13:01:56.537195 23693990-803f-11d9-8135-00e0180dcd8e
19105950-81d5-11d9-8d4b-00e0180dcd8edsf sdfdsf dsf dsf dsf
2005-02-18
18:47:03.25149 23693990-803f-11d9-8135-00e0180dcd8e
1f97042c-81d5-11d9-8d4b-00e0180dcd8efsd sdfsdf dfsfsd sdffsd
dsffdsfds 2005
-02-18 18:47:14.214234 23693990-803f-11d9-8135-00e0180dcd8e
a6e6e062-8042-11d9-9818-00e0180dcd8edsf dsfsdf dsf dfs dsfdsf
2005-02-16
17:46:14.04676 23693990-803f-11d9-8135-00e0180dcd8e
f37e7418-8041-11d9-9f4e-00e0180dcd8edsf dsf dsf sdf sdf sdf \r\npublic
DataSet ListeSituationSeminaire(Guid
IdSeminaire)\r\n{\r\ntry\r\n{\r\nreturn
PostgreSqlHelper.ExecuteDataset(this._ChaineConnexion,\r\n"Situation_ListeSituationSeminaire(:a)
as (Id uuid, DateCreation timestamp, IdSeminaire
uuid)",\r\nnew
NpgsqlParameter[] {new
NpgsqlParameter("a", IdSeminaire)});\r\n}\r\ncatch (NpgsqlException
ExceptionSql)\r\n{\r\nthrow new ApplicationException(_MessageErreurSql,
ExceptionSql);\r\n}\r\ncatch (InvalidOperationException
ExceptionOperationInvalide)\r\n{\r\nthrow new
ApplicationException(_MessageErreurOperationInvalide,\r\nExceptionOpe
rationInvalide);\r\n}\r\n}\r\npublic DataSet
ListeSituationSeminaire(Guid IdSeminaire)\r\n{\r\ntry\r\n{\r\nreturn
PostgreSqlHelper.ExecuteDataset(this._ChaineConnexion,\r\n"Situation_ListeSituationSeminaire(:a)
as (Id uuid, DateCreation timestamp, IdSeminaire
uuid)",\r\nnew
NpgsqlParameter[] {new
NpgsqlParameter("a", IdSeminaire)});\r\n}\r\ncatch (NpgsqlException
ExceptionSql)\r\n{\r\nthrow new ApplicationException(_MessageErreurSql,
ExceptionSql);\r\n}\r\ncatch (InvalidOperationException
ExceptionOperationInvalide)\r\n{\r\nthrow new
ApplicationException(_MessageErreurOperationInvalide,\r\nExceptionOpe
rationInvalide);\r\n}\r\n}\r\npublic int NombreSituationSeminaire(Guid
IdSeminaire)\r\n{\r\ntry\r\n{\r\nreturn (int)
PostgreSqlHelper.ExecuteScalar(\r\nthis._ChaineConnexion,\r\n"Situation_NombreSituationSeminaire(:a)"
,\r\nnew
NpgsqlParameter[] {\r\nnew NpgsqlParameter("a",
IdSeminaire)\r\n});\r\n}\r\ncatch (NpgsqlException
ExceptionSql)\r\n{\r\nthrow new ApplicationException(_MessageErreurSql,
ExceptionSql);\r\n}\r\ncatch (InvalidOperationException
ExceptionOperationInvalide)\r\n{\r\nthrow new
ApplicationException(_MessageErreurOperationInvalide,\r\nExceptionOpe
rationInvalide);\r\n} \r\n}\r\n#endregion Méthodes
\r\npublic
DataSet ListeSituationSeminaire(Guid
IdSeminaire)\r\n{\r\ntry\r\n{\r\nreturn
PostgreSqlHelper.ExecuteDataset(this._ChaineConnexion,\r\n"Situation_ListeSituationSeminaire(:a)
as (Id uuid, DateCreation timestamp, IdSeminaire
uuid)",\r\nnew
NpgsqlParameter[] {new
NpgsqlParameter("a", IdSeminaire)});\r\n}\r\ncatch (NpgsqlException
ExceptionSql)\r\n{\r\nthrow new ApplicationException(_MessageErreurSql,
ExceptionSql);\r\n}\r\ncatch (InvalidOperationException
ExceptionOperationInvalide)\r\n{\r\nthrow new
ApplicationException(_MessageErreu
Re: [BUGS] CC Date format code defaults to current centry
Joe Brown wrote:
> joe=> select to_date('19450323','CCYYMMDD');
> to_date
>
> 2045-03-23
> (1 row)
>
> joe=> select to_date('19450323','MMDD');
> to_date
>
> 1945-03-23
> (1 row)
>
> I thought the former would be "more" correct. But it seems I am mistaken.
Uh, 1945 _is_ in the 20th century, but I can see how it is confusing.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1540: Enhancement request: 'ambiguous' column reference
Richard Neill wrote: I think that the first query ought to succeed, since although priceband is ambiguous (it could mean either tbl_prices.priceband or tbl_instruments.priceband), the information in the WHERE clause means that they are explicitly equal, and so it doesn't matter which one we use. Well, it just means the type's equality operator returns true for these two values -- I'm not sure it is wise to assume they are completely interchangeable. More generally, it makes sense to me that resolution of column references is a property of the syntax of a statement, not something derived from its semantics (e.g. the fact that we can infer for some particular statement that two columns are equal). -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1537: alter table statement
On Fri, Mar 11, 2005 at 11:12:07AM +, biju wrote: > PostgreSQL version: 7.2.2 This version is pretty old -- if you must use 7.2, then consider upgrading to 7.2.7. Better yet, try a newer release like 8.0.1. > i tried to execute alter table statement for a particular table. i did it > many ways, but not worked. Please show the exact statement you executed and describe how it didn't work. If you got an error message, then please copy and paste it verbatim. Without more information we can't tell you what's wrong. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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 #1540: Enhancement request: 'ambiguous' column reference in psql
"Richard Neill" <[EMAIL PROTECTED]> writes: > SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE > tbl_instruments.priceband=tbl_prices.priceband; > ERROR: column reference "priceband" is ambiguous > I think that the first query ought to succeed, since although priceband is > ambiguous (it could mean either tbl_prices.priceband or > tbl_instruments.priceband), the information in the WHERE clause means that > they are explicitly equal, and so it doesn't matter which one we use. Doing that would be contrary to the SQL specification, AFAICS. However, you can get the effect you want by writing the query like SELECT instrument,priceband,pounds FROM tbl_instruments JOIN tbl_prices USING (priceband); which both provides the join condition and logically merges the two input columns into just one output column. 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] BUG #1539: Suggestion
"Patrick Boulay" <[EMAIL PROTECTED]> writes: > Is it possible to change a prompt character during a transaction? Yes, read the psql reference page's section about "Prompting". regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1541: Unusually long INSERT times after fresh clean/CREATE TABLES
"John Engelhart" <[EMAIL PROTECTED]> writes: > Since I'm developing an SQL based application, I routinely "start from > scratch" with a script that deletes all the tables in my database and > rebuilds them. A problem started when I upgraded from 7.4.7 to 8.0.1 in > that the first run after the clean takes an UNUSUALLY long time to complete, > on the order of a few hundred inserts/sec. A normal run takes ~30 seconds. > A from scratch run takes ~15 minutes, with the next run completing in 30 > seconds with zero changes. The 8.0 planner is intentionally sensitive to the current actual physical sizes of tables. It sounds like you've managed to get it to plan something on the assumption that the tables are tiny and keep using that plan after they aren't tiny any more. The old planner had the same kind of issue but it was far easier to hit, so "revert that change" isn't an answer that I'm particularly interested in. > Hopefully this is enough info to track down and recreate the problem Not really. 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] BUG #1533: "*OLD*" relation not recognized in CREATE RULE
"Henk van den Toorn" <[EMAIL PROTECTED]> writes: > CREATE RULE rulename AS ON UPDATE > table WHERE OLD.index = NEW.index > DO INSTEAD NOTHING; > Postgresql responds with > ERROR: relation "*OLD*" does not exist Hm, works for me: regression=# create table tab1 (index serial primary key, data text); NOTICE: CREATE TABLE will create implicit sequence "tab1_index_seq" for "serial" column "tab1.index" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tab1_pkey" for table "tab1" CREATE TABLE regression=# create rule r as on update to tab1 where old.index = new.index do instead nothing; CREATE RULE regression=# select version(); version --- PostgreSQL 7.4.7 on hppa-hp-hpux10.20, compiled by GCC 2.95.3 (1 row) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1530: search on uuid indexed article returns no resultat
"Avram" <[EMAIL PROTECTED]> writes: > The SELECT command using a 'uuid' field indexed with pk_situation returns no > result. The error dissapear after the backup and the restore of the database > or after deleting the index. There is no uuid datatype in the standard Postgres distribution. I suppose this is a bug in uuid's comparison routines; I suggest reporting it to wherever you got the uuid datatype from. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1537: alter table statement
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Fri, Mar 11, 2005 at 11:12:07AM +, biju wrote: >> i tried to execute alter table statement for a particular table. i did it >> many ways, but not worked. > Please show the exact statement you executed and describe how it > didn't work. If you got an error message, then please copy and > paste it verbatim. Without more information we can't tell you > what's wrong. I'll make a side bet that he's reading post-7.2 documentation and expecting every feature therein to be in 7.2 ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
