[BUGS] BUG #1871: operations with data types
The following bug has been logged online: Bug reference: 1871 Logged by: Email address: [EMAIL PROTECTED] PostgreSQL version: 7-8 Operating system: Linux Description:operations with data types Details: May be it is not bug, but anywhere: here you are sample query select '2005-08-31'::date + '1 month'::interval-'1 month'::interval from the mathematical me the resulting value should be '2005-08-31', but .. can you explain this ---(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
[BUGS] Race-condition with failed block-write?
Hi list, I noticed a development machine was responding terribly slow, and found out postgresql had consumed all available memory: USER PID %CPU %MEMVSZ RSS TTY STAT START TIME COMMAND postgres 7465 0.0 0.0 16636 928 ?SSep05 0:00 /usr/bin/postmaster -D /var/lib/postgresql/data postgres 7472 0.0 81.0 3144408 832032 ? DSep05 0:56 \_ postgres: writer process postgres 7473 0.0 0.0 7552 916 ?SSep05 0:00 \_ postgres: stats buffer process postgres 7474 0.0 0.0 6848 900 ?SSep05 0:00 \_ postgres: stats collector process It had taken over 800MB of memory. The machine is a P4 3.0 Ghz (with HT enabled), 1GB of memory a few SATA disks and runs with a recent Gentoo + 2.6.12.5 kernel. The postgresql version that failed was 8.0.3, it may or may not be worth knowing that it runs a 8.1devel on another port and from another directory. The 8.0.3-database was not actively in use, since our current development work was using the 8.1devel. In the postgresql.log a write-failure messages was repeated enough to make the log file 50MB larger: This was the first one: [ - 2005-09-07 13:03:47 CEST @] ERROR: xlog flush request 29/67713428 is not satisfied --- flushed only to 29/2E73E488 [ - 2005-09-07 13:03:47 CEST @] CONTEXT: writing block 21 of relation 1663/2013826/9975789 [ - 2005-09-07 13:03:48 CEST @] ERROR: xlog flush request 29/67713428 is not satisfied --- flushed only to 29/2E73E488 [ - 2005-09-07 13:03:48 CEST @] CONTEXT: writing block 21 of relation 1663/2013826/9975789 [ - 2005-09-07 13:03:48 CEST @] WARNING: could not write block 21 of 1663/2013826/9975789 [ - 2005-09-07 13:03:48 CEST @] DETAIL: Multiple failures --- write error may be permanent. And those 4 lines were repeated up untill the memory was empty, as it seems from the log: TopMemoryContext: -1095880208 total in 264213 blocks; 53793 free (924739 chunks); -1633819096 used MdSmgr: 8192 total in 1 blocks; 8024 free (0 chunks); 168 used Pending Ops Table: 8192 total in 1 blocks; 6112 free (0 chunks); 2080 used DynaHash: 8192 total in 1 blocks; 7488 free (0 chunks); 704 used smgr relation table: 8192 total in 1 blocks; 4048 free (0 chunks); 4144 used LockTable (locallock hash): 8192 total in 1 blocks; 6112 free (0 chunks); 2080 used ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used [ - 2005-09-09 02:42:22 CEST @] ERROR: out of memory [ - 2005-09-09 02:42:22 CEST @] DETAIL: Failed on request of size 16000. After issueing an immediate-shutdown, it starts and runs just fine: [ - 2005-09-09 10:19:51 CEST @] LOG: received fast shutdown request [ - 2005-09-09 10:23:01 CEST @] LOG: received immediate shutdown request [ - 2005-09-09 10:27:20 CEST @] LOG: database system was interrupted at 2005-09-05 11:24:08 CEST [ - 2005-09-09 10:27:20 CEST @] LOG: checkpoint record is at 29/2E73E44C [ - 2005-09-09 10:27:20 CEST @] LOG: redo record is at 29/2E73E44C; undo record is at 0/0; shutdown TRUE [ - 2005-09-09 10:27:20 CEST @] LOG: next transaction ID: 166361; next OID: 55768251 [ - 2005-09-09 10:27:20 CEST @] LOG: database system was not properly shut down; automatic recovery in progress [ - 2005-09-09 10:27:20 CEST @] LOG: record with zero length at 29/2E73E488 [ - 2005-09-09 10:27:20 CEST @] LOG: redo is not required [ - 2005-09-09 10:27:20 CEST @] LOG: database system is ready At 2005-09-05 the machine was rebooted and the only query since then (which may have triggered the issue) was: [2005-09-07 13:03:35 CEST - 2005-09-07 13:03:37 CEST [EMAIL PROTECTED] LOG: statement: SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relname ~ '^pwprijs$' ORDER BY 2, 3; I shut down the postgresql-server after the restart again to prevent further changes to the data-files of it. Best regards, Arjen van der Meijden ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] Error Failed to create process: 2! with Installer
Hi I have the next error Failed to create process: 2! with Windows2000 Professional and Windows2000 Server with installer PostgreSQL 8.0.1, 8.0.2 and 8.0.3 Thanks! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #1872: Failed to create process: 2!
The following bug has been logged online: Bug reference: 1872 Logged by: Hugo Cesar Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1, 8.2 and8.3 Operating system: Win2000 Server and Win2000 Pro Description:Failed to create process: 2! Details: This error appear with the installer when select the language and press the next buttton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1873: Invalid username specified during install
The following bug has been logged online: Bug reference: 1873 Logged by: Lee Benson Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3 Operating system: Windows XP Pressional Description:Invalid username specified during install Details: I am trying to reinstall Postgres 8.0.3 after developing a problem using psql (command line) where I was getting the password authentication failure message. When I tried to reinstall, I got the Invalid username specified: Logon failure: unknown user name or bad password error. If I change the default user from postgres to something else, the installer will move on and finish, but I still can't use psql at the command line. What am I missing? ---(end of broadcast)--- TIP 1: 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] BUG #1874: Non-Execute Privileges enforced on grant
The following bug has been logged online: Bug reference: 1874 Logged by: Mark Diener Email address: [EMAIL PROTECTED] PostgreSQL version: 8.03 Operating system: linux-i686 Description:Non-Execute Privileges enforced on grant Details: It seems the EXECUTE privilege is not the only privilege that is being checked during the execution of a PL/psql procedure language/function. Only a superuser can execute non-trusted languages like python thus making the python language unusable for average user. Only for superusers. What happens when you want the python stored procedures to implement a layer of security for standard users? Then the pl/SQL language enforces SELECT/UPDATE/INSERT privileges on tables. It would appear intuitive that only the EXECUTE privilege should be evaluated when a stored procedure executes. By default, all superuser and owner privileges should be allowed except for the EXECUTE privilege. What happens when you want the pg/SQL stored procedures to implement a layer of security for standard users and you don't want general users to have select/update/insert privilege? It is not an option to skip the select SQL statement within stored procedures. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1868: Initdb error during silent install on windows 2000
The following bug has been logged online: Bug reference: 1868 Logged by: Karim Mardhani Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.1 Operating system: Windows 2000 service pack 4 Description:Initdb error during silent install on windows 2000 Details: I am getting following error from initdb during silent installation of 8.0.1: creating directory C:/Program Files/PostgreSQL/8.0/data/global ... initdb: could not create directory C:/Program Files: File exists I have searched about this issue and usually this happens due to a permission problem. I have checked the permission they look fine to me. Also I have used same install on at least 15 other PCs (Mix of win 2K and win xp) and it worked fine for each one except this one. Any ideas what might be the problem? Following are the permissions on this PC (BTW other PCs have exactly same permissions and install works without any problem): C:\: Everyone = Allow FullControl, No Deny C:\Program Files: Group User = Allow Read Execute, List Folder Content, Read. No Deny The user postgres is member of group Users. One update on this problem. I updated my install script to create a directory md C:\pgdata and update the command line for postgresql install to include DATADIR=C:\pgdata and everything works fine. Maybe the space in Program Files is causing this problem? But then again the installation worked fine for at least 15 other PCs where the data base data was in C:\Program Files. Don't know what's going on here. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #1870: Insertion problem
The following bug has been logged online: Bug reference: 1870 Logged by: Sijin MS Email address: [EMAIL PROTECTED] PostgreSQL version: v7.3.2-3 Operating system: Redhat 9 Description:Insertion problem Details: Dear Sir or Madam: We are a Software Provider and promoting Linux. Most of our clients are using Linux. We are using postgres v7.3.2-3 as a backend for our application software. While inserting data into table, we found that sometime the data is not inserting into the table and also haven't raise any error messages. But if we do the same process again then it is inserting into the table properly. Sijin M.S Database Administrator (Linux), Perfect Software Solutions (clt) Pvt. Ltd. 1st Floor Reshmi Buildings, Behind S.K Temple, Kozhikode, Kerala State, India - 673001. Website: www.perfectlimited.com email : [EMAIL PROTECTED] Mobile : +91 9447392977 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #1867: ODBC dirver Problem
The following bug has been logged online: Bug reference: 1867 Logged by: Dave Goertz Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: Windows XP Professional(Client) Description:ODBC dirver Problem Details: When using ODBC version 07_03_0200 VarChar fields do not pad spaces on the end. When using ODBC version 08_00_0101 the driver DOES pad spaces on the end of the data. This should not be the case. It SHOULD pad them for Char data fields but not for VarChar else what is the point of VarChar? ---(end of broadcast)--- TIP 1: 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] BUG #1875: Function parameter names clash with table column names
The following bug has been logged online:
Bug reference: 1875
Logged by: Byron
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system: Windows XP
Description:Function parameter names clash with table column names
Details:
-
Error Details from PgAdminIII
-
ERROR: syntax error at or near $1 at character 28
QUERY: INSERT INTO test_table_0 ( $1 , $2 , $3 ) VALUES ( $4 , $5 , $6
)
CONTEXT: PL/pgSQL function test_function_0 line 2 at SQL statement
Problem Code
CREATE TABLE test_table_0 (
a varchar(255),
b varchar(255),
c varchar(255)
);
CREATE OR REPLACE FUNCTION test_function_0(a varchar, b varchar, c varchar)
RETURNS INTEGER AS $$
BEGIN
INSERT INTO test_table_0 (a, b, c) VALUES (a, b, c);
RETURN 1;
END
$$ LANGUAGE 'plpgsql';
SELECT test_function_0('a', 'b', 'c');
DROP FUNCTION test_function_0(varchar, varchar, varchar);
DROP TABLE test_table_0;
---
Additional Comments
---
It appears in this case that the parameter names of the
function cannot be the same as the column names used
by the INSERT statement -- clashes.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1875: Function parameter names clash with table column names
On Sat, Sep 10, 2005 at 05:46:44PM +0100, Byron wrote:
The following bug has been logged online:
Bug reference: 1875
Logged by: Byron
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system: Windows XP
Description:Function parameter names clash with table column names
Details:
-
Error Details from PgAdminIII
-
ERROR: syntax error at or near $1 at character 28
QUERY: INSERT INTO test_table_0 ( $1 , $2 , $3 ) VALUES ( $4 , $5 , $6
)
CONTEXT: PL/pgSQL function test_function_0 line 2 at SQL statement
Problem Code
CREATE TABLE test_table_0 (
a varchar(255),
b varchar(255),
c varchar(255)
);
CREATE OR REPLACE FUNCTION test_function_0(a varchar, b varchar, c varchar)
RETURNS INTEGER AS $$
BEGIN
INSERT INTO test_table_0 (a, b, c) VALUES (a, b, c);
RETURN 1;
END
$$ LANGUAGE 'plpgsql';
This should read something like
CREATE OR REPLACE FUNCTION test_function_0(in_a varchar, in_b varchar, in_c
varchar)
RETURNS INTEGER AS $$
BEGIN
INSERT INTO test_table_0 (a, b, c) VALUES (in_a, in_b, in_c);
IF FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END
$$ LANGUAGE 'plpgsql';
SELECT test_function_0('a', 'b', 'c');
DROP FUNCTION test_function_0(varchar, varchar, varchar);
DROP TABLE test_table_0;
---
Additional Comments
---
It appears in this case that the parameter names of the function
cannot be the same as the column names used by the INSERT statement
-- clashes.
This is not really a bug. If the things really are different, in this
case function input parameters and column names, they should also have
different names. PL/PgSQL merely enforces this :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] Race-condition with failed block-write?
Arjen van der Meijden [EMAIL PROTECTED] writes: In the postgresql.log a write-failure messages was repeated enough to make the log file 50MB larger: [ - 2005-09-07 13:03:47 CEST @] ERROR: xlog flush request 29/67713428 is not satisfied --- flushed only to 29/2E73E488 [ - 2005-09-07 13:03:47 CEST @] CONTEXT: writing block 21 of relation 1663/2013826/9975789 ... TopMemoryContext: -1095880208 total in 264213 blocks; 53793 free (924739 chunks); -1633819096 used MdSmgr: 8192 total in 1 blocks; 8024 free (0 chunks); 168 used Pending Ops Table: 8192 total in 1 blocks; 6112 free (0 chunks); 2080 used DynaHash: 8192 total in 1 blocks; 7488 free (0 chunks); 704 used smgr relation table: 8192 total in 1 blocks; 4048 free (0 chunks); 4144 used LockTable (locallock hash): 8192 total in 1 blocks; 6112 free (0 chunks); 2080 used ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used [ - 2005-09-09 02:42:22 CEST @] ERROR: out of memory [ - 2005-09-09 02:42:22 CEST @] DETAIL: Failed on request of size 16000. The pending-ops table only exists in the bgwriter, so it's evidently the bgwriter that was out of memory. I have an old note to myself : Doesn't bgwriter risk leaking memory if ereport out of a checkpoint? : Seems we should have it run checkpoints in a short-term context. : Don't the other daemons have similar issues? It looks to me like you have a case of this actually happening: it kept failing to execute a checkpoint and leaking some more memory each time. I'll move the priority of fixing that up a bit ... The other question is why the failure was occurring in the first place --- corrupt LSN value, apparently, but how'd it get that way? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1870: Insertion problem
On Fri, Sep 09, 2005 at 08:54:05AM +0100, Sijin MS wrote: We are a Software Provider and promoting Linux. Most of our clients are using Linux. We are using postgres v7.3.2-3 as a backend for our application software. While inserting data into table, we found that sometime the data is not inserting into the table and also haven't raise any error messages. But if we do the same process again then it is inserting into the table properly. You haven't given us much to go on, but I'll point out that a number of bugs have been fixed since 7.3.2, some involving data loss. Those bugs aren't necessarily responsible for the behavior you're seeing, but you should consider upgrading nonetheless. If you must stay with 7.3 then consider upgrading to 7.3.10 (the latest). See the Release Notes for a summary of bug fixes and other changes: http://www.postgresql.org/docs/7.3/static/release.html Regarding your application: what language and API are you using? How do users interface with the application (custom GUI, web browser, etc.)? How are you checking for errors? Are you sure that the application *would* detect errors if they occurred (i.e., have you tested the error checking code by intentionally causing errors)? Do the server's logs show any error messages? How often does this happen? Can you duplicate the problem on demand? How long after the insert are you checking whether the data was inserted? How are you checking? Could the data have been inserted and then deleted? Might the inserting transaction have been rolled back? Do you have any triggers that might be silently discarding the insert? -- Michael Fuhr ---(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 #1871: operations with data types
Michael Fuhr [EMAIL PROTECTED] writes: Apparently the two intervals don't cancel each other out (i.e., they're not optimized to zero), Well, no, because + and - associate left-to-right. so effectively we get this: test= select '2005-08-31'::date + '1 month'::interval; ?column? - 2005-09-30 00:00:00 (1 row) This seems to be the crux of the issue: is the above expression valid and if so what should it yield? I think you are right that the SQL spec wants it to raise an error, but the spec's rules about datetime behavior are uselessly narrow minded (last I checked, they still had not heard of daylight savings time ... so they're obviously not trying very hard in this area). The behavior that's in our code now is to round back to the last real day of the month. This might not be the best choice, but raising an error doesn't seem better to me offhand. Date and Darwen seem to think rounding forward to the first day of the next month would be more natural. I'm not sure why; it certainly wouldn't fix the complainant's issue, only surprise him in a different way. Also, even if you like round-forward for the above case, what about subtraction --- what should '2005-10-31' - '1 month' give? Rounding down definitely feels more natural in that case, at least to me. Any comments out there? regards, tom lane ---(end of broadcast)--- TIP 1: 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
