[BUGS] BUG #8467: Slightly confusing pgcrypto example in docs
The following bug has been logged on the website: Bug reference: 8467 Logged by: Richard Neill Email address: postgre...@richardneill.org PostgreSQL version: 9.3.0 Operating system: Documentation bug Description: The documentation for pgcrypto: http://www.postgresql.org/docs/current/static/pgcrypto.html (and indeed all versions from 8.3-9.3) contains the following: Example of authentication: SELECT pswhash = crypt('entered password', pswhash) FROM ... ; This returns true if the entered password is correct. I found this confusing, because it's using the same name, "pswhash" in 2 places, one of which is a boolean. It would be, imho, clearer to write the example query as: SELECT is_authenticated = crypt('entered password', pswhash) FROM ... ; [Also, should the default example perhaps use gen_salt('bf'), as opposed to gen_salt('md5') ?] -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8393: "ERROR: failed to locate grouping columns" on grouping by varchar returned from function
The following bug has been logged on the website: Bug reference: 8393 Logged by: Evan Martin Email address: postgre...@realityexists.net PostgreSQL version: 9.2.4 Operating system: Windows 7 x64 SP1 Description: version(): PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit Run the following: CREATE OR REPLACE FUNCTION test_group_by() RETURNS TABLE (my_col varchar(5)) AS $BODY$ SELECT 'hello'::varchar(5); $BODY$ LANGUAGE sql STABLE; SELECT my_col FROM test_group_by() GROUP BY 1; Expected result: 'hello' Actual result: ERROR: failed to locate grouping columns Interestingly, if the function is marked "VOLATILE" it works. Casting the result to "text" also makes it work. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8213: Set-valued function error in union
The following bug has been logged on the website: Bug reference: 8213 Logged by: Eric Soroos Email address: eric-postgre...@soroos.net PostgreSQL version: 9.0.13 Operating system: Ubuntu 10.04, 32bit Description: This has been replicated on 9.2.4 and HEAD by ilmari_ and johto. erics@dev:~/trunk/sql$ psql -a -h 192.168.10.249 -f pg_bug_report.sql \set VERBOSITY verbose \set echo all select version(); version PostgreSQL 9.0.13 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit (1 row) -- this fails. I'd expect it to succeed. select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt union select 2, now()::date ) as foo where dt < now()+'15 days'::interval; psql:pg_bug_report.sql:13: ERROR: 0A000: set-valued function called in context that cannot accept a set LOCATION: ExecMakeFunctionResult, execQual.c:1733 -- this succeeds, but returns a timestamp select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day') as dt union select 2, now()::date ) as foo where dt < now()+'15 days'::interval; id | dt +- 1 | 2013-06-05 00:00:00 1 | 2013-06-06 00:00:00 1 | 2013-06-07 00:00:00 1 | 2013-06-08 00:00:00 1 | 2013-06-09 00:00:00 1 | 2013-06-10 00:00:00 1 | 2013-06-11 00:00:00 1 | 2013-06-12 00:00:00 1 | 2013-06-13 00:00:00 1 | 2013-06-14 00:00:00 1 | 2013-06-15 00:00:00 1 | 2013-06-16 00:00:00 1 | 2013-06-17 00:00:00 1 | 2013-06-18 00:00:00 1 | 2013-06-19 00:00:00 1 | 2013-06-20 00:00:00 2 | 2013-06-05 00:00:00 (17 rows) --this also succeeds, without the where clause select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt union select 2, now()::date ) as foo; id | dt + 1 | 2013-06-05 1 | 2013-06-06 1 | 2013-06-07 1 | 2013-06-08 1 | 2013-06-09 1 | 2013-06-10 1 | 2013-06-11 1 | 2013-06-12 1 | 2013-06-13 1 | 2013-06-14 1 | 2013-06-15 1 | 2013-06-16 1 | 2013-06-17 1 | 2013-06-18 1 | 2013-06-19 1 | 2013-06-20 1 | 2013-06-21 1 | 2013-06-22 1 | 2013-06-23 1 | 2013-06-24 1 | 2013-06-25 1 | 2013-06-26 1 | 2013-06-27 1 | 2013-06-28 1 | 2013-06-29 1 | 2013-06-30 1 | 2013-07-01 1 | 2013-07-02 1 | 2013-07-03 1 | 2013-07-04 1 | 2013-07-05 2 | 2013-06-05 (32 rows) --this also succeeds, without the union select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt ) as foo where dt < now()+'15 days'::interval; id | dt + 1 | 2013-06-05 1 | 2013-06-06 1 | 2013-06-07 1 | 2013-06-08 1 | 2013-06-09 1 | 2013-06-10 1 | 2013-06-11 1 | 2013-06-12 1 | 2013-06-13 1 | 2013-06-14 1 | 2013-06-15 1 | 2013-06-16 1 | 2013-06-17 1 | 2013-06-18 1 | 2013-06-19 1 | 2013-06-20 (16 rows) -- this is the workaround. select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt union all select 2, now()::date ) as foo where dt < now()+'15 days'::interval; id | dt + 1 | 2013-06-05 1 | 2013-06-06 1 | 2013-06-07 1 | 2013-06-08 1 | 2013-06-09 1 | 2013-06-10 1 | 2013-06-11 1 | 2013-06-12 1 | 2013-06-13 1 | 2013-06-14 1 | 2013-06-15 1 | 2013-06-16 1 | 2013-06-17 1 | 2013-06-18 1 | 2013-06-19 1 | 2013-06-20 2 | 2013-06-05 (17 rows) -- this is another workaround: begin; BEGIN create temp view gs as select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day') as dt; CREATE VIEW create temp view container as select id, dt::date from gs union select 2, now()::date; CREATE VIEW select * from container where dt < now()+'15 days'::interval; id | dt + 1 | 2013-06-05 1 | 2013-06-06 1 | 2013-06-07 1 | 2013-06-08 1 | 2013-06-09 1 | 2013-06-10 1 | 2013-06-11 1 | 2013-06-12 1 | 2013-06-13 1 | 2013-06-14 1 | 2013-06-15 1 | 2013-06-16 1 | 2013-06-17 1 | 2013-06-18 1 | 2013-06-19 1 | 2013-06-20 2 | 2013-06-05 (17 rows) rollback; ROLLBACK -- another workaround select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt union select 2, now()::date offset 0 ) as foo where dt < now()+
[BUGS] BUG #6316: function search_path causes set_config() is_local = true to have no effect
The following bug has been logged on the website: Bug reference: 6316 Logged by: Jon Erdman Email address: postgre...@thewickedtribe.net PostgreSQL version: 9.1.1 Operating system: Ubuntu Description: Hi Tom! :) So, found this in 8.3 but tested and it effects everything up to 9.1.1. If search_path on a function is set to anything, calls to set_config() with is_local = true inside that function have no effect. See test case and output below: BEGIN; CREATE OR REPLACE FUNCTION public.setting_bug_true() RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_catalog.set_config( 'search_path', 'pg_catalog', true ); END; $$ SET search_path = public ; CREATE OR REPLACE FUNCTION public.setting_bug_false() RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_catalog.set_config( 'search_path', 'pg_catalog', false ); END; $$ SET search_path = public ; SET search_path = public; SHOW search_path; SELECT public.setting_bug_true(); \echo Search path should now be pg_catalog SHOW search_path; SET search_path = public; SHOW search_path; SELECT public.setting_bug_false(); \echo Oddly, if is_local is false, it *does* work SHOW search_path; ALTER FUNCTION public.setting_bug_true() SET search_path = DEFAULT; SET search_path = public; SHOW search_path; SELECT public.setting_bug_true(); \echo Take search_path off the function and it works!?! /me smells a bug... SHOW search_path; ROLLBACK; And the output: postgres@[local]/cnuapp_dev:5437=# \i ~/bug.sql BEGIN Time: 0.070 ms CREATE FUNCTION Time: 0.208 ms CREATE FUNCTION Time: 0.164 ms SET Time: 0.055 ms search_path - public (1 row) Time: 0.025 ms setting_bug_true -- (1 row) Time: 0.138 ms Search path should now be pg_catalog search_path - public (1 row) Time: 0.022 ms SET Time: 0.019 ms search_path - public (1 row) Time: 0.023 ms setting_bug_false --- (1 row) Time: 0.085 ms Oddly, if is_local is false, it *does* work search_path - pg_catalog (1 row) Time: 0.021 ms ALTER FUNCTION Time: 0.051 ms SET Time: 0.014 ms search_path - public (1 row) Time: 0.018 ms setting_bug_true -- (1 row) Time: 0.108 ms Take search_path off the function and it works!?! /me smells a bug... search_path - pg_catalog (1 row) Time: 0.018 ms ROLLBACK Time: 0.050 ms postgres@[local]/cnuapp_dev:5437=# -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] char(0)
On Oct 17, 2011, at 4:41 PM, Andreas Pflug wrote: > Am 17.10.11 10:53, schrieb Thomas Kellerer: >> Susanne Ebrecht, 17.10.2011 09:31: >>> Hello, >>> >>> I couldn't find that somebody already mentioned it. >>> >>> PostgreSQL isn't supporting CHAR(0). >>> >>> An empty string has a length of 0. >>> >>> CHAR(0) can have two values: NULL and empty string. >>> >>> In MySQL it is very common to simulate not null boolean >>> by using CHAR(0). >>> >>> This is a little bit annoying on migration topics. >> >> While not move on to a cleaner approach during the migration and use a >> "boolean not null"? > > Sounds much too straight forward, not mysql-ish artistic enough... > > Regards, > Andreas yes, if you do proper migration you should try to get rid of stupid design like this.. it is possible to stand on your head actually ... it just makes no sense if you are waiting for the bus. the fact that mysql has something does not implicitly mean that it makes sense to have it as well. a way to get around it would be ... CREATE TYPE my_intentionally_broken_type ... ;). but, i would not see that as recommendation actually :). regards, hans -- Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] char(0)
On Oct 17, 2011, at 9:31 AM, Susanne Ebrecht wrote: > Hello, > > I couldn't find that somebody already mentioned it. > > PostgreSQL isn't supporting CHAR(0). > > An empty string has a length of 0. > > CHAR(0) can have two values: NULL and empty string. > > In MySQL it is very common to simulate not null boolean > by using CHAR(0). > > This is a little bit annoying on migration topics. > > Susanne hello i would actually see it the other way round. supporting char(0) is the bug here ... if somebody used char(0) to simulate boolean not null ... let me not comment on that one for social reasons ;). regards, hans -- Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #1352: 100's of postgres.exe tasks created
The following bug has been logged online: Bug reference: 1352 Logged by: Steve Schafer Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows 2000 Pro, sp 4, up to date Description:100's of postgres.exe tasks created Details: I know I could do more to make this reproducable but I'm don't have a lot of time and I figure it's better to at least report it. Maybe I'm doing something wrong that's making this happen. If so I'm sorry for wasting your time but I'd appreciate knowing what it is. This is 8.0.0rc1 installed on win2kpro from downloaded binaries. If there's a build number, I don't know where to find it. I browsed through the bugs forum but didn't see anything like this. I'm using postgresql with a complex java web application that creates many prepared statements which are used once and not reused. After running this application for a while, I can see a great many instances of postgres.exe running in the task manager. I've seen as many as a couple of hundred. The more I run my application, the more instances appear. Most of them have done no I/O. When I shut down the postgres service, these tasks do not go away. I cannot kill these tasks using the task manager because I get an access denied error. I've even had problems shutting down windows because each of these task pops up error windows. Prior to downloading 8.0.0rc1, I was using 7.4.1 in cygwin and never had this kind of problem. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1351: service postgresql start failed
The following bug has been logged online: Bug reference: 1351 Logged by: taufik Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.2 Operating system: linux redhat 9 Description:service postgresql start failed Details: i having promblem with my posgresql, when I starting service postgresql, it failed In directory /var/lib/pgsql/data , i don'n see file postmaster.pid, can you help me ? thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1350: Backslash ecape charcter violates ISO/ANSI spec and is hazardous
The following bug has been logged online: Bug reference: 1350 Logged by: Ken Johanson Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Any Description:Backslash ecape charcter violates ISO/ANSI spec and is hazardous Details: I can't find any option to run the server in a more standard mode for string escaping rules, of only needing to escape single quotes. The current backslash-escape behavior is a huge problem for SQL statement portability, a spec violation, and blindsides developers coming from Oracle, Sybase, MS, etc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1349: Gap in documentation - pg_dump
The following bug has been logged online: Bug reference: 1349 Logged by: PiotrL Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.6 Operating system: Windows Description:Gap in documentation - pg_dump Details: I'm sorry, but I didn't found a better place to report that: There is no information how to specify user's password in pg_dump documentation. I have spent half of day searching for that (pg_hba.conf was not working for me) and the answer is very simple: You can use SET variables for that: PGUSER=web-user PGPASSWORD=secret Please include that in pg_dump documentation. Regards, Piotr ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1348: Wishlist: \pset expanded with value
The following bug has been logged online: Bug reference: 1348 Logged by: Brad Bowman Email address: [EMAIL PROTECTED] PostgreSQL version: 7.2.1 Operating system: Debian linux Description:Wishlist: \pset expanded with value Details: I hope this is an appropriate forum for this request. Apologies if I got it wrong. Within psql: \pset expanded 1 ignores the value parameter, as expected from the documentation. This means that I can't set it to a known value in scripts run with \i. Thanks ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1347: Bulk Import stopps after a while ( 8.0.0. RC1)
The following bug has been logged online: Bug reference: 1347 Logged by: Bahadur Singh Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows XP Description:Bulk Import stopps after a while ( 8.0.0. RC1) Details: Hello, I have found a trivial problem during the bulk import of data. I have a following situation. - I have 2 big table on SQL Server 2000 with 163000 rows each. - I have written a java program to transfer data from SQL Server 2000 to PosgresSQL 8.0.0 RC1 release. I am updating the data in batches. If my batch size is 1000/2000 records at a time.. This works fine.. And if I change this size to say 20,000, it does only finishes one loop.. and then stays idle. The CPU usage down to 10 % which was before 100 % while applying the first batch of 20, 000 records. The execution of program is halting just at int n [] = stmt.batchUpdate(); I waited one hours at the most to wait above instruction to finish and then stopped manually.. I am not sure that is it JDBC error or PostgreSQL error. I am using JDBC release of PosgresSQL 8.0.0 RC1 release. Thanks Bahadur Include : Log 1 : when does not work.. Log 2 : when works .. -- Log 1 WHEN BATCH SIZE = 2000 --- getMsSqlConnection() :: status = ACTIVE getPgSqlConnection() :: status = ACTIVE Fri Dec 10 16:47:23 CET 2004 Migration: CREATE TABLE EodIDC_S ( "transstep" int, "transactionid" int, "departmentnumber" char (4), "plunumber" char (16), "identnumber" char (16), "quantity" int, "quantitydecimals" int, "packingunit" int, "mmcode" int, "amountsign" char (1), "amountabsolute" int, "code1" char (1), "code2" char (1), "code3" char (1), "idcdate" char (14), "originalitemprice" int, PRIMARY KEY ( TransactionId,TransStep ) ) Fri Dec 10 16:47:23 CET 2004 Migration: CREATE TABLE EodIDC_S FINISHED Fri Dec 10 16:47:23 CET 2004 Migration: CREATE TABLE EodIDC_W ( "transactionid" int, "transstep" int, "iteminfo" char (4), "itemnumber" char (16), "flag1" char (2), "flag2" char (2), "amount" int, "flag3" char (2), "code1" char (1), "flag4" char (2), "code2" char (1), "code3" char (1), "idcdate" char (14), "plunumber" char (16), "quantity" int, PRIMARY KEY ( TransactionId,TransStep ) ) Fri Dec 10 16:47:23 CET 2004 Migration: CREATE TABLE EodIDC_W FINISHED Fri Dec 10 16:47:23 CET 2004 Migration: DDL finished in 109 ms Fri Dec 10 16:47:23 CET 2004 Migration: Exporting Table to PostgreSQL = EodIDC_S Fri Dec 10 16:47:23 CET 2004 Migration: Reading data from MS SQL Server table ... EodIDC_S Fri Dec 10 16:47:24 CET 2004 Migration: Batch sent upto = 2 Fri Dec 10 16:47:24 CET 2004 Migration: EodIDC_S Total SQLs sent to DB=2 Fri Dec 10 16:47:31 CET 2004 Migration: EodIDC_S Records updated in DB=2 Fri Dec 10 16:47:32 CET 2004 Migration: Batch sent upto = 4 Fri Dec 10 16:47:32 CET 2004 Migration: EodIDC_S Total SQLs sent to DB=2 ** NO RESPONSE AFTER ** Process stopped and restarted with batch size 2000 rows -- Log 2 WHEN BATCH SIZE = 2000 -- Fri Dec 10 16:51:02 CET 2004 Migration: CREATE TABLE EodIDC_S ( "transstep" int, "transactionid" int, "departmentnumber" char (4), "plunumber" char (16), "identnumber" char (16), "quantity" int, "quantitydecimals" int, "packingunit" int, "mmcode" int, "amountsign" char (1), "amountabsolute" int, "code1" char (1), "code2" char (1), "code3" char (1), "idcdate" char (14), "originalitemprice" int, PRIMARY KEY ( TransactionId,TransStep ) ) Fri Dec 10 16:51:02 CET 2004 Migration: CREATE TABLE EodIDC_S FINISHED Fri Dec 10 16:51:02 CET 2004 Migration: CREATE TABLE EodIDC_W ( "transactionid" int, "transstep" int, "iteminfo" char (4), "itemnumber" char (16), "flag1" char (2), "flag2" char (2), "amount" int, "flag3" char (2), "code1" char (1), "flag4" char (2), "code2" char (1), "code3" char (1), "idcdate" char (14), "plunumber" char (16), "quantity" int, PRIMARY KEY ( TransactionId,TransStep ) ) Fri Dec 10 16:51:02 CET 2004 Migration: CREATE TABLE EodIDC_W FINI
[BUGS] BUG #1345: pgsql character comparison bug
The following bug has been logged online: Bug reference: 1345 Logged by: Sir Ki-Sirk Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: WINDOW 2003 Enterprise Description:pgsql character comparison bug Details: I am a C++ and pgsql beginner. When using PostgreSQL 8.0.0 Beta 5 in Window-2003-Enterprise I find that PostgreSQL 8.0.0 Beta 5 has simple-character-comparison-bug see below; QUERY= select chr(8) a1 | a2 | a3 | a4 | a5 | a6 | a7 | a8 | a9 ++++++++--- t | t | t | t | t | f | t | t | t (1 row) Oh! the value of a6 field is "f" !!! Please Fix this BUG ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1344: Locale problem
The following bug has been logged online: Bug reference: 1344 Logged by: Jan Kraljič Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.6 Operating system: Linux Debian Description:Locale problem Details: I cannot start postgres because unknow error. In log i can get this: FATAL: XX000: failed to initialize lc_messages to "" LOCATION: InitializeGUCOptions, guc.c:1867 locale returns me this ... LC_MESSAGES="sl_SI" ... - By default i set locale to sl_SI.iso88592 What to do? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1343: Problem with German Umlaut in the installer
The following bug has been logged online: Bug reference: 1343 Logged by: r Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows XP SP2 Description:Problem with German Umlaut in the installer Details: Where: 1. fill in the mask for the service with a password the is the same as the user 2. -> next 3. a dialog asks you if you want to create a new password, because the one you choose is not good. What: the second last word contains a german umlaut ä that is not displayed correctly. There are two wrong characters instead. Maybe something wrong with with the charset? Greets Ronny ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1342: mistake in writing
The following bug has been logged online: Bug reference: 1342 Logged by: Ronny Schöniger Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows Description:mistake in writing Details: The german windows installer contains a little mistake in writing. Where: at the mask where you can set up the service What: "Bitte hier>>>>e<<<< einen Name eingeben..." Just remove the e Greets Ronny ---(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] BUG #1341: problem when showing resulted in the screen
The following bug has been logged online: Bug reference: 1341 Logged by: Pablo Borges Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.6 Operating system: Linux Slackware Description:problem when showing resulted in the screen Details: select * from teleoperador; id | login | ip | tipo +--++-- 0 | pablo| 10.0.0.106 | C 1 | builder | 10.0.0.107 10.0.0.107 | C 2 | reinaldo | 10.0.0.105 | C (3 rows) \d teleoperador Table "public.teleoperador" Column | Type| Modifiers +---+-- id | integer | not null default nextval('public.teleoperador_id_seq'::text) login | character varying | ip | character varying | not null tipo | character(1) | not null Indexes: "teleoperador_pkey" primary key, btree (id) "teleoperador_login_key" unique, btree (login) is one bug? ---(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
[BUGS] BUG #1340: problem when showing resulted in the screen
The following bug has been logged online: Bug reference: 1340 Logged by: Pablo Borges Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.6 Operating system: Linux Slackware Description:problem when showing resulted in the screen Details: select * from teleoperador; id | login | ip | tipo +--++-- 0 | pablo| 10.0.0.106 | C 1 | builder | 10.0.0.107 10.0.0.107 | C 2 | reinaldo | 10.0.0.105 | C (3 rows) \d teleoperador Table "public.teleoperador" Column | Type| Modifiers +---+-- id | integer | not null default nextval('public.teleoperador_id_seq'::text) login | character varying | ip | character varying | not null tipo | character(1) | not null Indexes: "teleoperador_pkey" primary key, btree (id) "teleoperador_login_key" unique, btree (login) is one bug? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1339: enable to init database cluster
The following bug has been logged online: Bug reference: 1339 Logged by: Florent Merlet Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.3 Operating system: CYGWIN_NT Description:enable to init database cluster Details: I manage to use the 7.3 version without any trouble, But since i've download the 7.4.3 version, it fails at initdb. $initdb -D /usr/share/postgresql/data - The files belonging to this database system will be owned by user "Florent Merlet". This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /usr/share/postgresql/data... ok creating directory /usr/share/postgresql/data/base... ok creating directory /usr/share/postgresql/data/global... ok creating directory /usr/share/postgresql/data/pg_xlog... ok creating directory /usr/share/postgresql/data/pg_clog... ok selecting default max_connections... Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 10 selecting default shared_buffers... Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 50 creating configuration files... ok creating template1 database in /usr/share/postgresql/data/base/1... Signal 12 initdb: failed - what does that mean ? Do I have done something wrong ? Maybe it comes from cygwin ? Please help me ... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1338: Problem running PostgreSQL as service
The following bug has been logged online: Bug reference: 1338 Logged by: PJMODOS Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows 2000 Description:Problem running PostgreSQL as service Details: In 8.0 RC1, because of bug in -D parsing in pg_ctl I am unable to run postgres as service when I have space in data path - when i register it with pg_ctl register -D "C:\Program Files\PostgreSQL\data" and try to run it then it won't work and in event log I have this: pg_ctl: no database directory specified and environment variable PGDATA unset. I have found very simple solution, just added + 1 in one of xmallocs and it seems to work, diff follows: --- pg_ctl.cSat Nov 27 19:51:06 2004 +++ pg_ctl_new.cSun Dec 05 10:21:40 2004 @@ -1289,7 +1289,7 @@ { case 'D': { - char *pgdata_D = xmalloc(strlen(optarg)); + char *pgdata_D = xmalloc(strlen(optarg) + 1); char *env_var = xmalloc(strlen(optarg) + 8); strcpy(pgdata_D, optarg); ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1337: Problem running PostgreSQL as service, incl. fix
The following bug has been logged online: Bug reference: 1337 Logged by: PJMODOS Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows 2000 Description:Problem running PostgreSQL as service, incl. fix Details: In 8.0 RC1, because of bug in -D parsing in pg_ctl I am unable to run postgres as service when I have space in data path - when i register it with pg_ctl register -D "C:\Program Files\PostgreSQL\data" and try to run it then it won't work and in event log I have this: pg_ctl: no database directory specified and environment variable PGDATA unset. I have found very simple solution, just added + 1 in one of xmallocs and it seems to work, diff follows: --- pg_ctl.cSat Nov 27 19:51:06 2004 +++ pg_ctl_new.cSun Dec 05 10:21:40 2004 @@ -1289,7 +1289,7 @@ { case 'D': { - char *pgdata_D = xmalloc(strlen(optarg)); + char *pgdata_D = xmalloc(strlen(optarg) + 1); char *env_var = xmalloc(strlen(optarg) + 8); strcpy(pgdata_D, optarg); ---(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] BUG #1336: configure: error: no acceptable C compiler found in $PATH
The following bug has been logged online: Bug reference: 1336 Logged by: Andres Velasquez Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.6 Operating system: Redhat 8 Description:configure: error: no acceptable C compiler found in $PATH Details: [EMAIL PROTECTED] pgsql]# ./configure checking build system type... i686-pc-linux-gnuoldld checking host system type... i686-pc-linux-gnuoldld checking which template to use... linux checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... no checking for cc... no configure: error: no acceptable C compiler found in $PATH help me please. in Spanish Andres ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1335: Wrong sort result in union queries
The following bug has been logged online: Bug reference: 1335 Logged by: snaky Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows 2003/XP Description:Wrong sort result in union queries Details: sorry for my english. Query: select 2 union select 1 Result: 1 2 Why? I think the result must be like this: 2 1 Why PostgreSQL sort union queries by first column by default? Certainly, I understand that I can write general "order by" in the end of query. However, in this case, I can't make queries with "manual" row ording. And what is more, this query does not work properly: (select * from (select 1, 2 union select 4, 3) as a order by 2 desc) union select 1, 1 Result must be like this: 4, 3 1, 2 1, 1 but real result is: 1, 1 1, 2 4, 3 Full version info: PostgreSQL 8.0.0beta1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw special 20030504-1) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1334: PREPARE creates bad execution plan (40x slower)
The following bug has been logged online: Bug reference: 1334 Logged by: A. Steinmetz Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.6 Operating system: Linux Description:PREPARE creates bad execution plan (40x slower) Details: Direct excution of: explain analyze INSERT INTO results SELECT pagesearch.weight,pagesearch.pageid FROM pagesearch,topictrace WHERE pagesearch.wordid=924375 AND pagesearch.catid=topictrace.catid AND topictrace.refid=1 LIMIT 1500; gives: Subquery Scan "*SELECT*" (cost=0.00..11348.27 rows=1500 width=8) (actual time=0.317..44.297 rows=1500 loops=1) -> Limit (cost=0.00..11333.27 rows=1500 width=8) (actual time=0.314..42.909 rows=1500 loops=1) -> Nested Loop (cost=0.00..40202.90 rows=5321 width=8) (actual time=0.311..42.185 rows=1500 loops=1) -> Index Scan using pgscwdidx on pagesearch (cost=0.00..173.32rows=7580 width=12) (actual time=0.167..2.725 rows=1500 loops=1) Index Cond: (wordid = 924375) -> Index Scan using tptrc on topictrace (cost=0.00..5.27 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1500) Index Cond: ((topictrace.refid = 1) AND ("outer".catid = topictrace.catid)) Total runtime: 53.663 ms (8 rows) == Now, executing: prepare t1 (integer,integer) as INSERT INTO results SELECT pagesearch.weight,pagesearch.pageid FROM pagesearch,topictrace WHERE pagesearch.wordid=$1 AND pagesearch.catid=topictrace.catid AND topictrace.refid=$2 LIMIT 1500; explain analyze execute t1 (924375,1); gives: Subquery Scan "*SELECT*" (cost=6569.10..6619.22 rows=17 width=8) (actual time=2013.509..2039.757 rows=1500 loops=1) -> Limit (cost=6569.10..6619.05 rows=17 width=8) (actual time=2013.503..2038.543 rows=1500 loops=1) -> Merge Join (cost=6569.10..6619.05 rows=17 width=8) (actual time=2013.500..2037.904 rows=1500 loops=1) Merge Cond: ("outer".catid = "inner".catid) -> Sort (cost=701.29..721.28 rows=7996 width=12) (actual time=32.194..32.546 rows=1500 loops=1) Sort Key: pagesearch.catid -> Index Scan using pgscwdidx on pagesearch (cost=0.00..182.94 rows=7996 width=12) (actual time=0.176..15.574 rows=9267 loops=1) Index Cond: (wordid = $1) -> Sort (cost=5867.81..5872.71 rows=1960 width=4) (actual time=1981.179..1988.281 rows=31483 loops=1) Sort Key: topictrace.catid -> Index Scan using tptrc on topictrace (cost=0.00..5760.63 rows=1960 width=4) (actual time=0.172..978.313 rows=650273 loops=1) Index Cond: (refid = $2) Total runtime: 2155.218 ms (13 rows) = This means that using a prepared statement instead of a direct query is *40* times slower! Some more information about the tables used: CREATE TEMPORARY TABLE results (weight INTEGER,pageid INTEGER); CREATE INDEX residx ON results (weight); CREATE TABLE pagesearch (serial INTEGER PRIMARY KEY,wordid INTEGER,weight INTEGER,pageid INTEGER,catid INTEGER,ages INTEGER); CREATE INDEX pgscwdidx on pagesearch (wordid); CREATE TABLE topictrace (serial INTEGER PRIMARY KEY,refid INTEGER,catid INTEGER); CREATE INDEX tptrc on topictrace (refid,catid); Data volumes in the non-temporary tables: pagesearch: 4831 rows topictrace: 5271657 rows Note: the same prepared statement works well with other typical databases (e.g. MySQL, SQLite). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1333: vacuum full apparently fails to complete
The following bug has been logged online: Bug reference: 1333 Logged by: Keith Halewood Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: HP-UX 11i Description:vacuum full apparently fails to complete Details: We have a database of approximately 22Gb of which about 18Gb is large objects. The spread of sizes is roughly 16000x 2-6Mb objects, 16000x 1-2Mb objects, 14x 1-9Kb objects. When this database is copied, record by record, to another blank database cluster, a vacuum full completes in approximately an hour, presumably because it has nothing to vacuum. In general, records are added to ordinary tables on a daily basis (some 45 to one table) and this results in approximately 450 large objects being created (1x 2-6Mb, 2x 1-2Mb and about 10x 1-9Kb) Over the past few weeks, a large amount of changes of accumulated data has been made which mostly results in large objects being discarded and recreated. There are triggers in the database which ensure that just before a record is deleted, the large objects in any columns are unlinked. Similarly triggers for record update ensure that changes to the oid column types result in the appropriate unlinks first. All other tables vacuum full successfully. Here is the result of removing approximately 14 large objects (of the 1-9Kb size) and 230 objects (of the 2-6Mb size): INFO: "pg_largeobject": found 1052029 removable, 8638984 nonremovable row versions in 3086785 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 36 to 2084 bytes long. There were 313026 unused item pointers. Total free space (including removable row versions) is 7324762424 bytes. 242145 pages are or will become empty, including 1102 at the end of the table. 3085017 pages containing 7315763688 free bytes are potential move destinations. CPU 495.25s/62.74u sec elapsed 3943.42 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 8638984 row versions in 3819 At this point, the vacuum is *still* in progress (after 8 hours) and disc activity is exclusively the result of this vacuum, about 140 blocks/second with no disc queues. Is this likely to be a bug or just a scalability issue involving vacuum and the large object table? Configuration: HP 9000/J2240, 2xCPU, 4Gb RAM, HP-UX 11i, June 2003 required/gold patches, HP ANSI-C developers bundle B.11.11.04. No HP AutoRAID performance problems. Postgres 7.4.5 Non-default contents of postgresql.conf in this db cluster: max_connections=50 shared_buffers=1 sort_mem=8192 vacuum_mem=81920 max_fsm_pages=4 wal_buffers=32 The major reason we continue to use large objects rather than bytea columns directly in tables is due to the poor/buggy handling of binary column values in the perl DBI an DBD::Pg. Hoping for some insights. Keith Halewood ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1332: wrong results from age function
The following bug has been logged online: Bug reference: 1332 Logged by: Robert Grabowski Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.6 Operating system: Linux Description:wrong results from age function Details: select age('2004-02-01'::date, '2004-01-01'::date); age --- 1 mon (1 row) select age('2004-03-01'::date, '2004-02-01'::date); age - 29 days (1 row) I think, it should be "1 mon". ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1331: after update: vacuum blocks parallel to select
The following bug has been logged online: Bug reference: 1331 Logged by: Michael Enke Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: Linux SuSE 9.1 Kernel 2.6/RedHat 9 Kernel 2.4 Description:after update: vacuum blocks parallel to select Details: Hi group, I found that vacuum blocks until a select cursor is closed but only if the table was updated before. After select/TA finished, vacuum goes through. A second vacuum during parallel select doesn't block until the next update is done. Example code: create table, insert and update: ===vac_blk.sql start= drop table vac_blk; create table vac_blk ( a int2 ); insert into vac_blk values (1); update vac_blk set a=1 where a=1; \echo now call vac_blk binary; \echo if running, call vacuum vac_blk: will hang ==vac_blk.sql stop Example code: Do the select inside a TA: ==vac_blk.pgc start=== exec sql include sqlca; int main(int argc, char **argv) { exec sql begin declare section; int a; char sqlstring[] = "select a from vac_blk"; exec sql end declare section; exec sql connect to [EMAIL PROTECTED]:5432 user myuser identified by mypassword; exec sql PREPARE select_vac_blk FROM :sqlstring; exec sql DECLARE select_cur_vac_blk CURSOR FOR select_vac_blk; exec sql OPEN select_cur_vac_blk; exec sql FETCH select_cur_vac_blk INTO :a; printf("have read a=%i, will now sleep 60s, go vacuum now!\n", a); sleep(60); exec sql disconnect all; return 0; } ==vac_blk.pgc stop it was compiled with vac_blk: vac_blk.pgc ecpg vac_blk.pgc -I/usr/include/pgsql gcc -o vac_blk vac_blk.c -I/usr/include/pgsql -lecpg If you call "vacuum vac_blk;" in psql after creation, insertion and update to vac_blk table and running vac_blk binary, it hangs until the vac_blk program exits. Than the vacuum finishes afterwards. To reproduce this, you must first update the vac_blk table. If no update, the select doesn't block the vacuum. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1330: Incorrect String Order By In tr_TR Locale
The following bug has been logged online: Bug reference: 1330 Logged by: Özgür Çaycı Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Debian Woody Description:Incorrect String Order By In tr_TR Locale Details: I use 8.0 Beta-5. I am using a PostgreSQL cluster which I have created with locale tr_TR. When I use "Order By", the strings are not ordered correctly. The spaces are ignored when I use "Order By" clause. Here is a sample SQL dump to re-create this situation: CREATE TABLE mytable ( mytext character varying(255) ); INSERT INTO mytable VALUES ('Güngör, Ahmet'); INSERT INTO mytable VALUES ('Güngör, Bengi'); INSERT INTO mytable VALUES ('Güngördü, Ersin'); INSERT INTO mytable VALUES ('Güngören, Bora'); INSERT INTO mytable VALUES ('Güngör, Erhan'); INSERT INTO mytable VALUES ('Güngör, Erol'); When I try 'Select * FROM mytable ORDER BY mytext ASC' I get the following output: Güngör, Ahmet Güngör, Bengi Güngördü, Ersin Güngören, Bora Güngör, Erhan Güngör, Erol The expected result is as follows: Güngör, Ahmet Güngör, Bengi Güngör, Erhan Güngör, Erol Güngördü, Ersin Güngören, Bora Özgür ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1329: Bug in IF-ELSEIF-ELSE construct
The following bug has been logged online: Bug reference: 1329 Logged by: Rico Wind Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows XP, SP2 Description:Bug in IF-ELSEIF-ELSE construct Details: Beta 1. The following always returns 4: IF from_date_param=period_begin AND until_date_param=period_end THEN return 1; ELSEIF from_date_param=period_begin THEN return 2; ELSEIF until_date_param=period_end THEN return 3; ELSE return 4; END IF; Whereas the following returns the right answer(not 4 each time). They should be the same. IF from_date_param=period_begin AND until_date_param=period_end THEN return 1; ELSE IF from_date_param = period_begin THEN return 2; END IF; IF until_date_param=period_end THEN return 3; END IF; END IF; RETURN 4; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1328: psql don't accept some valid PGCLIENTENCODING values
The following bug has been logged online: Bug reference: 1328 Logged by: kaaos Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Linux kaaos 2.6.9 #3 Tue Nov 23 13:12:08 MSK 2004 i686 Intel(R) Celeron(R) CPU 1.70GHz GenuineIntel GNU/Linux Description:psql don't accept some valid PGCLIENTENCODING values Details: Hello! First of all, sorry for my English :(. The problem: With PGCLIENTENCODING=KOI8 and server encoding UNICODE psql do not starts and print this message: psql: FATAL: invalid value for parameter "client_encoding": "KOI8" But, if I unset PGCLIENTENCODING, and in psql enter: template1=# \encoding KOI8 then psql accept client encoding: template1=# \encoding KOI8 template1=# show client_encoding; client_encoding - KOI8 (1 row) 'select version()' output: PostgreSQL 8.0.0beta5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 Thank you. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1327: Compare column of varchar FAILURE!
The following bug has been logged online: Bug reference: 1327 Logged by: yychen Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.6 Operating system: Fedora 3 Description:Compare column of varchar FAILURE! Details: Client Charset zh_TW Big5 Server DataBase Encoding by EUC_TW Connect via ODBC 7.3.1 Server OS:Fedora3 But in Linux 9 is correct! Ex: SELECT * FROM tablename WHERE Column_Of_Varchar='String_Of_Big5'; It will Return All data in tablename And no any error message! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1326: Unique and Primary Key index over bigint type doesn't work
The following bug has been logged online: Bug reference: 1326 Logged by: Fernando Kasten Peinado Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: Linux RedHat 7.3 Description:Unique and Primary Key index over bigint type doesn't work Details: index is not used when Type is bigint. sislu=> \d x Table "public.x" Column | Type | Modifiers ++--- id | bigint | not null name | character varying(100) | age| integer| bigid | bigint | Indexes: "x_pkey" primary key, btree (id) "un_x_age" unique, btree (age) "un_x_bigid" unique, btree (bigid) "un_x_name" unique, btree (name) sislu=> explain select * from x where x.id = 12345; QUERY PLAN --- Seq Scan on x (cost=0.00..22.50 rows=2 width=88) Filter: (id = 12345) (2 rows) sislu=> explain select * from x where x.bigid = 12345; QUERY PLAN --- Seq Scan on x (cost=0.00..22.50 rows=2 width=88) Filter: (bigid = 12345) (2 rows) sislu=> explain select * from x where x.name = 'asdf'; QUERY PLAN Index Scan using un_x_name on x (cost=0.00..4.82 rows=2 width=88) Index Cond: ((name)::text = 'asdf'::text) (2 rows) sislu=> explain select * from x where x.age = 1; QUERY PLAN --- Index Scan using un_x_age on x (cost=0.00..4.82 rows=2 width=88) Index Cond: (age = 1) (2 rows) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1325: like error
The following bug has been logged online: Bug reference: 1325 Logged by: gregory Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: win2000 Description:like error Details: I create a table CREATE TABLE tbinvoicerows ( invoicecd varchar(16) NOT NULL, articlecd varchar(5), articlenm varchar(32), familycd varchar(1), articlepriority int4, quantity numeric(8,2), unitprice numeric(8,2), unity varchar(32), vattypecd char(2), vatratio numeric(9,3), amountbt numeric(11,5), currency varchar(20), commratio numeric(9,3), discountbt numeric(8,2), discountratio numeric(9,3), relratio numeric(5,2), "timestamp" timestamp ) WITHOUT OIDS; ALTER TABLE tbinvoicerows OWNER TO sa; after I copy 5 millions lignes on the table after CREATE INDEX "idx_invoiceCd" ON tbinvoicerows USING btree (invoicecd); after select * from tbinvoicerows where invoicecd = 'FLOCAA0025' result in 0 seconde with 2 records NO BUG select * from tbinvoicerows where invoicecd like '%FLOCAA0025%' result in 40 secondes with 2 records NO BUG select * from tbinvoicerows where invoicecd like 'FLOCAA0025%' result in 40 secondes with 2 records BUG Bug : the result is good but I expect a result faster as the first select (like in mssql 7) I test Postgres since One Week for the moment NICE WORK Congratulation Gregory ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1324: create domain strange behaviour
The following bug has been logged online: Bug reference: 1324 Logged by: Tzvetan Tzankov Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Debian Description:create domain strange behaviour Details: I will post a sort test test=# create schema test; CREATE SCHEMA test=# set search_path to test; SET test=# CREATE DOMAIN session_id AS character(9) CONSTRAINT ch_session_id_field CHECK (value ~ '[0-9]{4}-[0-9]{4}-[0-9]{4}-[0-9]{4}'); CREATE DOMAIN test=# test=# CREATE OR REPLACE FUNCTION generate_session_id() RETURNS session_id AS $$ test$# begin test$# return lpad(text(floor(random()*1)), 4, '0') || '-' || lpad(text(floor(random()*1)), 4, '0') || '-' || lpad(text(floor(random()*1)), 4, '0') || '-' || lpad(text(floor(random()*1)), 4, '0'); test$# end; test$# $$ test-# LANGUAGE plpgsql; CREATE FUNCTION test=# CREATE TABLE session ( test(# id session_id NOT NULL default generate_session_id() PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "session_pkey" for table "session" CREATE TABLE test=# insert into session default values; INSERT 2272280 1 test=# select * from session; id - 3199-4274-8097-7843 (1 row) test=# insert into session values ('3199-4274-8097-7842'); ERROR: value too long for type character(9) test=# select version(); version --- -- PostgreSQL 8.0.0beta4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-13) (1 row) well this was first of all, a bug in my domain creating statement, which I should have done character(19) insetead of 9, but instead of generating error on default value it accepted it (when I try to insert a random value by hand it gives however an error) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1323: Bug in Driver ODBC
The following bug has been logged online: Bug reference: 1323 Logged by: Sandro Votre Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows NT, Windows XP Description:Bug in Driver ODBC Details: I work with Delphi language , ... The Delphi use the Borland Database Engineer to control the access to database. For Example : TABLEX have the follows atributes (cod, name, city) select * from TABLEX The return of statement have less columns that the three demonstrated. This don´t happen with pgAdmin This happen only with a statement inside the Delphi. To test can be used the DataBase Explorer that make part of Delphi ok Best regards, ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1322: Bug while Insert into TempResult Select cols ..... statement
The following bug has been logged online: Bug reference: 1322 Logged by: Bahadur Singh Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows 2000 server Description:Bug while Insert into TempResult Select cols . statement Details: PostgreSQL Version : PostgreSQL 8.0.0 Beta 4 OS : Windows Server 2000 I found a bug on Beta 4 release for Windows 2000 when inserting a large amount of data from Select statement using pgdev-306-JDBC2 driver. The Select returns 480,384 rows if executed on pgAdmin III SQL tool. SQL : INSERT INTO TEMPRESULT Select cols .. with some consitions When I call stmt.execute() it inserts onyl 192 rows to result table. More problematic ?? on calling int n = stmt.executeUpdate () it inserts 480,384 rows but returns 239,199 as value of n. Less problematic: Rows in DB are correct. It is working well, if result amount is less (less means about 10,000). Best Wishes Bahadur ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #1321: SSL error: sslv3 alert handshake failure
The following bug has been logged online: Bug reference: 1321 Logged by: T.J. Ferraro Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Mandrake Linux 10 Description:SSL error: sslv3 alert handshake failure Details: After installing 8.0.0beta4 (previously tried with beta3,2,etc) on a linux system with a working 7.4.x installation I was unable to connect with ssl. Tried compiling with OpenSSL 0.9.7d/e. I used certificates created with OpenSSL 0.9.7d/e that both worked fine with 7.4.x but apparently not so with 8.0.0. Server starts fine, but when I attempt to connect to the server with latest pgadmin or psql (8.0.0beta4 both on the pgfoundry binary for windows and a compiled version on Mandrake Linux) the error is always the same. The error message returned to the client is "SSL error: sslv3 alert handshake failure". The log reports: "could not accept SSL connection: 1". ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1320: 7.3.8 server RPM has file error
The following bug has been logged online: Bug reference: 1320 Logged by: Simon Riggs Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.7 Operating system: Fedora Core 1 Description:7.3.8 server RPM has file error Details: Installing postgresql-server-7.3.8-2PGDG.i686.rpm, I get the following error: error: unpacking of archive failed on file /usr/lib/pgsql/utf8_and_gb18030.so;41994c83: cpio: read 7.3.7 RPMs install fine, no problems, runs fine: same site for download... etc The same error on the 7.3.8 version happens on both UK mirror sites, and a US mirror site. [While there I notice: US download was faster than either UK site, wget-> UK system] Digging around, it seems that for 7.3.7 and 7.4.5, the naming convention was *v.v.v-1PGDG* for FC1 and *v.v.v-2PGDG* for FC2. I notice that for 7.3.8 and 7.4.6 both FC1 and FC2 RPMs use the naming convention *v.v.v-2PGDG*. This may be a red herring, since the file *2PGDG* also fails using FC2 on a separate system. Oh, and the bug report web form won't let me use 7.3.8 or 7.4.6 yet. Hmmm...think I'll try the source build now. ---(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
[BUGS] BUG #1319: Windows LIB file libecpg.lib not in build or package
The following bug has been logged online: Bug reference: 1319 Logged by: Tom O'Connell Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows 2000 Professional Description:Windows LIB file libecpg.lib not in build or package Details: The PostgreSQl version 8 BETA 4 package does not contain a libecpg.lib file. The build does not contain a Windows DEF file for libecpg. I was able to create the DEF file and LIB file for libecpg. I have run a few tests and the interface is working. A lib file is shipped in ..\lib\ms for the libpq interface. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1318: pg_restore.exe tables without data
The following bug has been logged online: Bug reference: 1318 Logged by: christian kindler Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: WinXP Description:pg_restore.exe tables without data Details: i made a dump file with pg_dump.exe only option -U specified -> so schema and data is in one big txt file. it seems not to be possible, to restore either with pg_restore.exe nore with psql.exe schama and data. only schema will be restored. even option -a specified for pg_restore nothing happen. thx christian ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1317: plPHP does not recgnize booleans
The following bug has been logged online: Bug reference: 1317 Logged by: Tom Katt Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: RH 7 Linux Description:plPHP does not recgnize booleans Details: plPHP can't understand postgresql boolean... ---(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
[BUGS] BUG #1316: Alter Name of a Serial Field won't change the corresponding SEQUENCE name
The following bug has been logged online: Bug reference: 1316 Logged by: Hongyi Gao Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: SuSe Linux 9 Description:Alter Name of a Serial Field won't change the corresponding SEQUENCE name Details: Alter Name of a Serial Field won't change the corresponding SEQUENCE name say we had a table: atable (field1 Serial) it implies to create a sequence: atable_field1_seq if I alter table atable alter field1 rename to field2 it becomes atable(field2 Serial) however, the sequence stays: atable_field1_seq it's not tablename_fieldname_seq any more This will cause problem when we restore a backup: NOTICE: create table atable implies create a sequence atable_field2_seq ... (here it defaults to tablename_fieldname_seq again) ... SET SEQUENCE atable_field1_seq ... (here it still tries to restore the actually sequence we used) since it's not created, it will fail. And the value of atable_field2_seq is NOT set !) this will ruin the whole field. The same thing may happen if you change the name of a table that has serial field(s). Regards, Hongyi ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1315: unconvertible BIG5 character 0xf9d8
The following bug has been logged online: Bug reference: 1315 Logged by: CN Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Linux Description:unconvertible BIG5 character 0xf9d8 Details: Sorry for cross posting here after 2 days' silence regarding this issue in general list! My 8.0beta2 database was initialized with option "-E UNICODE". Psql sets client encoding to Big5: database1=# \encoding big5 I get this error when inserting Big5 character 0xf9d8: WARNING: ignoring unconvertible BIG5 character 0xf9d8 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1314: STATEMENT_TIMEOUT DOES NOT WORK PROPERLY
The following bug has been logged online: Bug reference: 1314 Logged by: Adnan DURSUN Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: W2K Description:STATEMENT_TIMEOUT DOES NOT WORK PROPERLY Details: Hi, i use PostgreSQL 8 Beta4.I write a sf like that : / CREATE OR REPLACE FUNCTION fn_test() RETURNS "varchar" AS $BODY$ DECLARE c refcursor; r record; BEGIN SET SESSION STATEMENT_TIMEOUT = 1000; OPEN C FOR SELECT * FROM T_KULLANICILAR FOR UPDATE; FETCH C INTO R; RESET STATEMENT_TIMEOUT; RETURN '1'; EXCEPTION WHEN QUERY_CANCELED THEN RAISE EXCEPTION 'UNABLE TO OBTAIN LOCK... :%'; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; / 1.I RUN A THIS "SELECT * FROM T_KULLANICILAR FOR UPDATE" from another session to lock T_KULLANICILAR table rows. 2.While T_KULLANICILAR table was locked,i called sf like "SELECT FN_TEST()" from psql command prompt.But STATEMENT_TIMEOUT doesn't effect. 3.but i enter "SET SESSION STATEMENT_TIMEOUT = 1000;" command before step 2, STATEMENT_TIMEOUT affects and cancel query. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1313: problems with array syntax parsing in SQL statements
The following bug has been logged online: Bug reference: 1313 Logged by: Pascal Pochet Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: Mac OS X Description:problems with array syntax parsing in SQL statements Details: In INSERT statements the string '{}' is correctly parsed as an empty array when '{ }' is not, as if the space was signifiant, and generates a syntax error. Also '{"A", "B"}' will be correctly parsed when '{"A", "B" }' (note the space before the closing brace ) will generate the following entry '{"A","B "} : the space(s) between the latest double quote and the closing brace is/are added to the latest item of the array. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1312: the ordinal 2821 could not be located
The following bug has been logged online: Bug reference: 1312 Logged by: Amie Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: windows 2000 prof Description:the ordinal 2821 could not be located Details: At the point when the installer issues initdb.exe I get the above mentioned "LIBEAY32.dll" error. The initdb.log file is empty. I downloaded "postgresql-8.0.0-beta4.zip". Regards ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1311: Can't crosscompile
The following bug has been logged online: Bug reference: 1311 Logged by: Bernhard Rosenkraenzer Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Ark Linux Description:Can't crosscompile Details: Hi, 8.0.0beta4 can't be crosscompiled (I know that's hardly mainstream usage -- but I need the client libs on a PDA) because it tries to run the zic binary it generated for the target platform rather than the build platform. Quick and dirty [and not really portable] patch: --- postgresql-8.0.0beta4/src/timezone/Makefile.ark 2004-11-10 06:59:27.00000 +0100 +++ postgresql-8.0.0beta4/src/timezone/Makefile 2004-11-10 07:01:21.0 +0100 @@ -13,12 +13,14 @@ include $(top_builddir)/src/Makefile.global override CPPFLAGS := $(CPPFLAGS) +HOST_CC := gcc +HOST_CFLAGS := $(CFLAGS) # files to build into backend OBJS= localtime.o strftime.o pgtz.o # files needed to build zic utility program -ZICOBJS= zic.o ialloc.o scheck.o localtime.o +ZICSOURCES= zic.c ialloc.c scheck.c localtime.c # timezone data files TZDATA := africa antarctica asia australasia europe northamerica southamerica \ @@ -30,8 +32,8 @@ SUBSYS.o: $(OBJS) $(LD) $(LDREL) $(LDOUT) SUBSYS.o $(OBJS) -zic: $(ZICOBJS) - $(CC) $(CFLAGS) $(ZICOBJS) $(LDFLAGS) $(LIBS) -o [EMAIL PROTECTED](X) +zic: $(ZICSOURCES) + $(HOST_CC) $(HOST_CFLAGS) $(CPPFLAGS) $(ZICSOURCES) $(LDFLAGS) $(LIBS) -o [EMAIL PROTECTED](X) install: all installdirs ./zic -d $(DESTDIR)$(datadir)/timezone $(TZDATAFILES) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1310: libecpg.dll missing from msi package
The following bug has been logged online: Bug reference: 1310 Logged by: Tom O'Connell Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows 2000 Professional Description:libecpg.dll missing from msi package Details: The libecpg.dll is missing from the version 8 BETA 4 package. I know this issue was logged against the first BETA package. I checked the BETA 2 and BETA 3 packages and the DLL was not present in either of the packages. I am able to compile C code with embedded SQL but without the runtime DLL, libecpg.dll, the functionality is DOA in version 8. Regards, Tom O'Connell www.bphnx.com ---(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] BUG #1309: PL/PGSQL function: ORDER BY does not accept variables
The following bug has been logged online: Bug reference: 1309 Logged by: Christina Zhang Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.1 Operating system: Linux Description:PL/PGSQL function: ORDER BY does not accept variables Details: When we write a function using PL/PGSQL: CREATE OR REPLACE FUNCTION uf_TestSearch(integer,varchar)RETURNS setof ut_TestSearch AS ' DECLARE v_MyRow ut_TestSearch%rowtype; a_OrderBy ALIAS FOR $1; a_SortAsc ALIAS FOR $2; BEGIN FOR v_MyRow IN SELECT Colum1, Column2, Column3 FROM Table1 ORDER BY a_OrderBy a_SortAsc LOOP RETURN NEXT v_MyRow; END LOOP; RETURN; RETURN; END; 'LANGUAGE 'plpgsql'; Problem: When I use PERFORM uf_TestSearch(1,'ASC'); The returned result set are always sorted by "Column2", no matter what is passed in the first parameter. Could you please check whether the ORDER BY works correctly or not? Thank you, Christina ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1308: Bug with JDBC driver on duplicate
The following bug has been logged online: Bug reference: 1308 Logged by: Simon Lesage-Tremblay Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.2 Operating system: Linux Description:Bug with JDBC driver on duplicate Details: I have the version 7.4.2 of Postgres and I use the JDBC driver version pg74.215.jdbc3 and I run it on JBoss server. My problem is when I insert a record that have a problem of duplicate key, my request fall in a frozing mode. I test my commands with pgadmin and I got a message of duplicate key. So I supposed that is a problem with the driver. This is the line that it's supposed to do : insert INTO data_format_sps(dataformatid, datatype, signed, precision, scale) VALUES (100,1,false,0,0); insert INTO data_format_sps(dataformatid, datatype, signed, precision, scale) VALUES (101,1,true,0,0); insert INTO data_format_sps(dataformatid, datatype, signed, precision, scale) VALUES (102,1,true,0,1); insert INTO data_format_sps(dataformatid, datatype, signed, precision, scale) VALUES (103,2,false,0,0); insert INTO data_format_sps(dataformatid, datatype, signed, precision, scale) VALUES (100,1,false,0,0); Got this error ERROR: duplicate key violates unique constraint "data_format_sps_pkey" with pgadmin. This is a part of the java code that froze : try{ String req = "INSERT INTO data_format_sps (dataformatid, datatype, signed, precision, scale) VALUES (?,?,?,?,?)"; PreparedStatement state = con.prepareStatement(req); state.setInt(1, in_DataFormatID); state.setInt(2, in_DataType); state.setBoolean(3, in_Signed); state.setInt(4, in_Precision); state.setInt(5, in_Scale); state.executeUpdate(); }catch(Exception e){ System.out.println("Got an error " + e.getMessage()); } This is the structure of my table : CREATE TABLE public.data_format_sps ( dataformatid int4 NOT NULL, datatype int4 NOT NULL, signed bool NOT NULL, precision int4 NOT NULL, scale int4 NOT NULL, CONSTRAINT data_format_sps_pkey PRIMARY KEY (dataformatid) ) WITH OIDS; Can you help me? I didn't see fix on that in newer version. Thank you Simon Lesage-Tremblay ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1307: Possible bug inheritance/referential integrity
The following bug has been logged online: Bug reference: 1307 Logged by: Thomas Jacob Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: Gentoo Linux, Kernel 2.6.7 Description:Possible bug inheritance/referential integrity Details: Rows inserted into a table "sub" inherited from a table "super" do not seem to exist in the super-table from the point of view of the foreign key constraint checker in references to the "super"-table, even though a SELECT query displays the rows that were inserted into sub also in super, as should be (See example provided). But they should exist, shouldn't they? Or do I simply not understand PostgreSQL's inheritance model correctly? CREATE TABLE super (id INT PRIMARY KEY); CREATE TABLE super_ref (id INT REFERENCES super); CREATE TABLE sub () INHERITS(super); -- this works INSERT INTO super VALUES(1); INSERT INTO super_ref VALUES(1); -- clean up DELETE FROM super_ref; DELETE FROM super; -- this fails with: ERROR: insert or update on table "super_ref" violates foreign key constraint "$1" INSERT INTO sub VALUES(1); INSERT INTO super_ref VALUES(1); ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1306: locale related issue.
The following bug has been logged online: Bug reference: 1306 Logged by: David Dick Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Debian (Woody) with a Linux 2.6.7 kernel Description:locale related issue. Details: in response to; tar -jxf postgresql-8.0.0beta4.tar.bz2 cd postgresql-8.0.0beta4 ./configure make check combination, i received the following message. postgres 7.4.3 makes check fine *snip* /bin/sh ./pg_regress --temp-install --top-builddir=../../.. --schedule=./parallel_schedule --multibyte=SQL_ASCII == creating temporary installation== == initializing database system == == starting postmaster== running on port 65432 with pid 11316 == creating database "regression" == CREATE DATABASE pg_regress: could not set database default locales make[2]: *** [check] Error 2 rm regress.o make[2]: Leaving directory `/home/dave/postgresql-8.0.0beta4/src/test/regress' make[1]: *** [check] Error 2 make[1]: Leaving directory `/home/dave/postgresql-8.0.0beta4/src/test' make: *** [check] Error 2 [EMAIL PROTECTED]:~/postgresql-8.0.0beta4$ [EMAIL PROTECTED]:~$ uname -a Linux summit 2.6.7 #1 SMP Sun Jul 18 08:02:18 EST 2004 i686 unknown Sorry for the brevity of the report. Would be happy to respond with more detail if requested. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1305: can't use the type 'number' with alter table
The following bug has been logged online: Bug reference: 1305 Logged by: Károly Segesdi Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: slackware 10.0 Description:can't use the type 'number' with alter table Details: somedb=# alter table sometbl add column msisdn number(11); ERROR: syntax error at or near "(" at character 43 you can use the type identifier 'decimal' instead: somedb=# alter table sometbl add column msisdn decimal(11); ALTER TABLE The documentation says theese two types are the same, but it's annoying that you can't use the one that is mentioned more frequently in the docs. postgresql version is 7.4.6, your bugreport form's version field misses this version. ---(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] BUG #1304: update does not honor order of subselect
The following bug has been logged online: Bug reference: 1304 Logged by: Brendan Fagan Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.6 Operating system: RedHat ES 3 Description:update does not honor order of subselect Details: SQL Update command does not follow the order of a WHERE field IN subselect. In the following code, I try to reset the order of rows in a column by updating an order field. Update does not honor the order of output form the subselect. create temp sequence foo increment 1 minvalue 0 start 0; select setval('foo', 0); update nav_content_structure set nav_cannister_ord_num = nextval('foo') where nav_content_id in ( select s.nav_content_id fromnav_content_structure s, nav_content c where s.nav_content_tie_id = 2624 and s.nav_cannister_id is not null and c.nav_content_id = s.nav_content_id order by s.nav_cannister_id, c.nav_content_title ) and nav_content_tie_id = 2624 ---(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] BUG #1303: backend crashes due wrong TCP packet
The following bug has been logged online: Bug reference: 1303 Logged by: Yevgeniy Kondratiev Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: RHEL3 Description:backend crashes due wrong TCP packet Details: over, i think, network problems or ODBC errors there are some wrong packets which crash postgresql backend. TCP package with postgresql data (hex) 58 00 00 00 00 00 doest it. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #1302: Vacuumdb and vacuumlo should disable statement_timeout
The following bug has been logged online: Bug reference: 1302 Logged by: Alex Koh Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.2 Operating system: Linux Redhat 8 Description:Vacuumdb and vacuumlo should disable statement_timeout Details: IMHO vacuumdb and vacuumlo should disable statement_timeout before vacuuming the database. This should prevent timeouts when vacuuming large tables like pg_largeobject. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1299: ordinal 2821 no en contrado en LIBEAY32.DLL
The following bug has been logged online: Bug reference: 1299 Logged by: ALEX EDUARDO QUITIAQUEZ ESQUIVEL Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Win Xp SP2 Description:ordinal 2821 no en contrado en LIBEAY32.DLL Details: Este error sale cuando ya casi está terminando la instalacion y luego hacer rollbak a toda la instalacion por lo que no se puede instalar. Antes tenia instalado El Beta 2 Dev 3 de PostgreSQL, lo desinstalé y lo volví a instalar y pasó sin problema, pero este error sale instalando el Beta 4. Gracias ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1301: pg_restore failed on AMD 64bit machine
The following bug has been logged online: Bug reference: 1301 Logged by: hongyi Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: SuSe 9.1 Description:pg_restore failed on AMD 64bit machine Details: pg_restore has flow on AMD64 machine. When I try restore a backup (made by pg_dump with -Fc option), when it reaches some specific table/at some specific positions in the backup file, it will fail. This only happened on 64bit machines with version 7.4.0-7.4.6 Error Message: pg_restore: [custom archiver] error during file seek: Invalid argument For a specific backup, it'll always fail at the same place. But, if I copy the backup data to other 32bit machines, and run pg_restore with -h remotely restore the data, everything is fine. It's succesfully finished. Tried version 7.4.6, still failed. Tried compile source and install, same. a bug in pg_restore. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1298: 22021: invalid byte sequence for encoding \"UNICODE\": 0xe16d61"
The following bug has been logged online: Bug reference: 1298 Logged by: Lucas Sultanum Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows 2000 server Description:22021: invalid byte sequence for encoding \"UNICODE\": 0xe16d61" Details: Hello, I am not sure if this is a bug or I am doing something wrong. When I execute the following command (insert into a_cadclias values ('6542','65465','amaro','ámaro')) on pgAdmin III Query it works pretty well, but when I try to do the same through a C# App connecting to the database through an ODBC driver I get the following error: "ERROR: 22021: invalid byte sequence for encoding \"UNICODE\": 0xe16d61" I know that it has something to do with the word ámaro because when I take the letter (á)off and replace it with the letter (a) it works fine. Bellow goes the table structure: CREATE TABLE a_cadclias ( dba_clias_cliente "numeric"(8) NOT NULL, dba_clias_associado "numeric"(8) NOT NULL, dba_keyclias_sq "varchar"(8) NOT NULL, teste "varchar"(10), CONSTRAINT dba_keyclias_sq PRIMARY KEY (dba_keyclias_sq) ) WITH OIDS; Att: It is valid to said that I have also tried the Npgsql dll and got the same error. Versions tested: "PostgreSQL 8.0.0beta2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw special 20030504-1)" AND "PostgreSQL 8.0.0beta4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.3.1 (mingw special 20030804-1)" Regards Lucas Sultanum ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #1297: Postgres after 7.3.5 does not works on initdb stage
The following bug has been logged online: Bug reference: 1297 Logged by: shpac Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.7 Operating system: Linux 2.0.40 Description:Postgres after 7.3.5 does not works on initdb stage Details: Postgres after 7.3.5 does not works on initdb stage. I compile 7.3.7 and 7.3.8, but after installing and traing to do initdb script failt on operation: "vacuuming template1...failed". in the /usr/local/pgsql/data/base/1/ comes core here backtrace (gdb postmaster core): (gdb) bt #0 0x4029ef50 in ?? () from /lib/libc.so.6 #1 0x8149c57 in varstr_cmp () #2 0x8149cdb in text_cmp () #3 0x8149f67 in bttextcmp () #4 0x8169577 in ApplySortFunction () #5 0x80b5a1a in compare_scalars () #6 0x40279e97 in ?? () from /lib/libc.so.6 #7 0x40279e0d in ?? () from /lib/libc.so.6 #8 0x40279df4 in ?? () from /lib/libc.so.6 #9 0x40279df4 in ?? () from /lib/libc.so.6 #10 0x40279df4 in ?? () from /lib/libc.so.6 #11 0x40279df4 in ?? () from /lib/libc.so.6 #12 0x40279f60 in ?? () from /lib/libc.so.6 #13 0x80b53db in compute_scalar_stats () #14 0x80b424e in analyze_rel () #15 0x80ca58e in vacuum () #16 0x811c41a in ProcessUtility () #17 0x8119d24 in pg_exec_query_string () #18 0x811ae67 in PostgresMain () #19 0x80e1b8b in main () ---(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] BUG #1296: Server crashes when relation already exists using JDBC
The following bug has been logged online: Bug reference: 1296 Logged by: Bryan Ray Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Win32 (XP SP1 - version 5.1.2600) Description:Server crashes when relation already exists using JDBC Details: I am using the postgresql jdbc driver to access a postgresql 7.5 development version. I wrote some code to create a relation, and tried to catch the exception if it already existed. After catching the exception the next query resulted in an IO exception and the server restarts. The restart is noted in the event viewer: TRAP: FailedAssertion("!(portal->resowner == ((void *)0))", File: "portalmem.c", Line: 561) . And the stack trace is like so: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:142) org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement. at java:346) org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2S at tatement.java:294) org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Stat at ement.java:249) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) org.postgresql.jdbc2.optional.PooledConnectionImpl$StatementHandler.invoke( at PooledConnectionImpl.java:392) at $Proxy2.executeUpdate(Unknown Source) com.logicacmg.uk.rayb.TableUniqueKeyGenerator.initTables(TableUniqueKeyGene at rator.java) com.logicacmg.uk.rayb.TableUniqueKeyGeneratorTest.testInitTables(TableUniqu at eKeyGeneratorTest.java) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at junit.framework.TestCase.runTest(TestCase.java:154) at junit.framework.TestCase.runBare(TestCase.java:127) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at junit.framework.TestSuite.runTest(TestSuite.java:208) at junit.framework.TestSuite.run(TestSuite.java:203) org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestR at unner.java:421) org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner at .java:305) org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunne at r.java:186) Caused by: java.net.SocketException: Connection reset by peer: socket write error at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(Unknown Source) at java.net.SocketOutputStream.write(Unknown Source) at java.io.BufferedOutputStream.flushBuffer(Unknown Source) at java.io.BufferedOutputStream.flush(Unknown Source) at org.postgresql.core.PGStream.flush(PGStream.java:468) org.postgresql.core.v3.QueryExecutorImpl.sendSync(QueryExecutorImpl.java:47 at 4) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:138) ... 26 more Postgres is installed as a service under Win XP service pack 1. I am using the pgdev.306.jdbc3.jar with Sun Java SE 1.4.2_05. When doing the equivalent operations from psql, psql gives the expected result (not restarting teh server): RayB=# SELECT * FROM keygeneration; uniqueness | nextid + myTable| 0 (1 row) RayB=# CREATE TABLE keygeneration (uniqueness VARCHAR(20) PRIMARY KEY, nextid INT); ERROR: relation "keygeneration" already exists RayB=# SELECT * FROM keygeneration; uniqueness | nextid + myTable| 0 (1 row) I have included a JUnit test case below which generated the problem. The java code I used is identical to the code that is on postgres.org: /* * Created on Oct 18, 2004 */ package com.logicacmg.uk.rayb; import java.sql.*; import javax.sql.DataSource; /** * @author RayB * * Purpose: * Design Patterns: */ public class TableUniqueKeyGenerator implements KeyGenerator { private DataSource datasource = null; private String tableName = null; // SQL private static final String CREATE_TABLE_SQL = "CREATE TABLE keygeneration (uniqueness VARCHAR(30) PRIMARY KEY,nextid INT NOT NULL)"; private static final String CREATE_NEXT_KEY_SQL = "INSER
[BUGS] BUG #1295: Problem on trigger
The following bug has been logged online: Bug reference: 1295 Logged by: shancheng Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Fedora2 Description:Problem on trigger Details: I have a table for test. CREATE TABLE _test(id int); Then i insert some records(at least 3 records for obvious result) into the table: INSERT INTO _test VALUES(1); INSERT INTO _test VALUES(2); INSERT INTO _test VALUES(3); And below is my testing function and the point where the problem comes from: CREATE OR REPLACE FUNCTION _test_delete_and_drop() RETURNS void AS $$ BEGIN DELETE FROM _test; DROP TABLE _test; RETURN; END; $$ LANGUAGE plpgsql; When i run the command: SELECT _test_delete_and_drop(); The computer responses: ERROR: relation with OID 1354613 does not exist CONTEXT: SQL query "DELETE FROM _test" PL/pgSQL function "_test_delete_and_drop" line 2 at SQL statement The function is very easy. It just delete the contents of a table and then drop it. The reason that i don't drop the table directly is that i need some cleanup operations. I define several triggers on the table. And when i delete records, the trigger will be activeted. If i don't drop the records beforehand, there will be much useless information left in the database. But i met a very puzzling problem when i do that. So i write the above test case to find out what the problem is. And to simplify the question, i don't define triggers for the table. Could anyone help me? Thanks very much! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1294: Random errors (failed connection)
The following bug has been logged online: Bug reference: 1294 Logged by: Stefanos Harhalakis Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: IRIX 6.5.17 Description:Random errors (failed connection) Details: This bug is tested against pgsql 7.4.1 and 7.4.6 using openssl 0.9.7c and 0.9.7d. It took me about 6 hours and a lot of compiles and step-by-step tracing to figure it out. Every postgresql client that runs under IRIX 6.5 seems to fail to connect to remote clients either when using openssl or not(without ssl there are some (random) successfull connections). The error indicates that no connection is done at all. The test case was: psql or a custom client running on Irix 6.5 postmaster running on Linux 2.6.8 Trying to connect from [EMAIL PROTECTED] -> [EMAIL PROTECTED] was always successfull. Trying to connect from [EMAIL PROTECTED] -> [EMAIL PROTECTED] was always successfull too, so it seemed that this was a problem with clients under Irix only. You will know for sure that this is the case when: You try: psql -h X.X.X.X -U from the Irix box and it fails giving nothing more than "LOG: incomplete startup packet" in the server log, but if you try: strace psql -h X.X.X.X -U then it connects (!!). It seems that something goes wrong when postgresql is compiled to use the poll() system call. I changed the #define HAVE_POLL to #undef HAVE_POLL and it worked (!). Please consider changing the default for IRIX systems to select() instead of poll(). TIA and keep up the good work... <> ---(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
[BUGS] BUG #1293: INSERT INTO test (f1.f2) VALUES (1,2) - wrong diagnostic
The following bug has been logged online: Bug reference: 1293 Logged by: Alexander Kirpa Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: FreeBSD Description:INSERT INTO test (f1.f2) VALUES (1,2) - wrong diagnostic Details: create table test(f1 int4,f2 int4); insert into test (f1.f2) values (1,2); ERROR: cannot assign to a column of type integer because it is not a composite type select f1.f2 from test; relation "f1" does not exist Wrong diagnostic for INSERT ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1292: ecpg precompile bug (valiable typedef & define )
The following bug has been logged online: Bug reference: 1292 Logged by: shigeto aya Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.2 Operating system: Red Hat Linux Advanced Server release 2.1AS/i686 (Pensacola) Description:ecpg precompile bug (valiable typedef & define ) Details: The result to demand cannot be found although the following programs were performed. Demand : data insert -- 100 200 300 400 500 600 700 800 select -- 100 200 300 400 500 600 700 800 Execution result data insert -- 100 200 300 400 500 600 700 800 select -- 1 200 300 400 5 600 700 800 sample program - #include /**/ /* create table test_table ( retu text ); */ /**/ int main() { #define LEN 3 exec sql begin declare section; typedef char TYPE1[LEN+1]; typedef char TYPE2[3+1]; struct { TYPE1 val1;/* NG */ TYPE2 val2;/* OK */ char val3[LEN+1]; /* OK */ char val4[3+1]; /* OK */ } k ; TYPE1 val5; /* NG */ TYPE2 val6; /* OK */ char val7[3+1];/* OK */ char val8[LEN+1]; /* OK */ char fetch_val[4]; exec sql end declare section; exec sql connect to aya; exec sql begin work; printf("data insert -- \n"); strcpy ( k.val1, "100"); printf("%s\n", k.val1); strcpy ( k.val2, "200"); printf("%s\n", k.val2); strcpy ( k.val3, "300"); printf("%s\n", k.val3); strcpy ( k.val4, "400"); printf("%s\n", k.val4); strcpy ( val5, "500"); printf("%s\n", val5); strcpy ( val6, "600"); printf("%s\n", val6); strcpy ( val7, "700"); printf("%s\n", val7); strcpy ( val8, "800"); printf("%s\n", val8); exec sql delete from test_table; exec sql insert into test_table values ( :k.val1 ) ; /* bug ? */ exec sql insert into test_table values ( :k.val2 ) ; /* ok */ exec sql insert into test_table values ( :k.val3 ) ; /* ok */ exec sql insert into test_table values ( :k.val4 ) ; /* ok */ exec sql insert into test_table values ( :val5 ) ; /* bug ? */ exec sql insert into test_table values ( :val6 ) ; /* ok */ exec sql insert into test_table values ( :val7 ) ; /* ok */ exec sql insert into test_table values ( :val8 ) ; /* ok */ exec sql declare acur cursor for select retu1 from test_table ; exec sql open acur; printf("select -- \n"); while(1) { memset ( fetch_val, '\0', sizeof ( fetch_val ) ); exec sql fetch acur into :fetch_val ; if (sqlca.sqlcode != 0 ){ break; } printf ("%s\n",fetch_val); } exec sql commit work; exec sql disconnect; exit( 0 ); } ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1291: Bug during processing string
The following bug has been logged online: Bug reference: 1291 Logged by: Alexander Kirpa Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: FreeBSD 5.2.1current Description:Bug during processing string Details: PostgreSQL 8.0beta3 During processing string with the code zero inside PostgreSQL simple drop (w/o error message) self code zero and rest of line. Sample below. select '12345\00012345'; ?column? -- 12345 (1 row) Time: 0.861 ms Please add error message for this situation and ... possible time for remove this restriction. Best regards, Alexander Kirpa ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1290: Default value and ALTER...TYPE
The following bug has been logged online: Bug reference: 1290 Logged by: Troels Arvin Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Linux, Fedora Core 2 + stuff from Red Hat Rawhide Description:Default value and ALTER...TYPE Details: In latest CVS (updated 2004-10-20 18:30 CEST), a too-large default column value seems to block the complete effects of an ALTER TABLE ... ALTER COLUMN ... TYPE operation, see below: troels=# select version(); version --- - PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20040907 (Red Hat 3.4.2-2) (1 row) troels=# create table lookat_feature( troels(# feature_id char(4), troels(# status varchar(2) default 'TODO' troels(# ); CREATE TABLE troels=# alter table lookat_feature troels-# alter column status type varchar(4); ALTER TABLE troels=# \d lookat_feature Table "public.lookat_feature" Column | Type | Modifiers +--+--- feature_id | character(4) | status | character varying(4) | default 'TODO'::character varying troels=# insert into lookat_feature (feature_id) values('B034'); ERROR: value too long for type character varying(2) If instead, the "DEFAULT 'TODO'" is left out for the "status" column: troels=# create table lookat_feature( troels(# feature_id char(4), troels(# status varchar(2) troels(# ); CREATE TABLE troels=# alter table lookat_feature troels-# alter column status type varchar(4); ALTER TABLE troels=# \d lookat_feature Table "public.lookat_feature" Column | Type | Modifiers +--+--- feature_id | character(4) | status | character varying(4) | troels=# insert into lookat_feature (feature_id) values('B034'); INSERT 17073 1 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1289: make install failed
The following bug has been logged online: Bug reference: 1289 Logged by: nicolas Ferragu Email address: [EMAIL PROTECTED] PostgreSQL version: 7.2.5 Operating system: RedHat AS 2.1 Description:make install failed Details: The make install fail at ./src/nls-global.mk : line 67:for lang in $(LANGUAGES); do because $(LANGUAGES) is null. My configure option was : enable-nls=fr ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1288: Can't create database in a French environment
The following bug has been logged online: Bug reference: 1288 Logged by: Jean-Philippe COURSON Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Slackware Linux 10.0 Description:Can't create database in a French environment Details: 8.0 Beta3. If the following environment variables are set : LC_ALL=fr_FR LANG=fr initdb is unable to create the database template1. Its output is : ... creating template1 database in /home/pgsql/base/1 ... FATAL: XX000: failed to initialize lc_messages to "" LOCATION: InitializeGUOptions, guc.c:2360 ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1287: Bug in www.postgresql.org php-code
The following bug has been logged online: Bug reference: 1287 Logged by: pre Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: web pages Description:Bug in www.postgresql.org php-code Details: Accessing http://archives.postgresql.org/pgsql-novice/2003-09/msg00112.php gives the following error message: Parse error: parse error, unexpected '<' in /home/web/pgsql-archives/pgsql-novice/2003-09/msg00112.php on line 87 ---(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
[BUGS] BUG #1286: indices not used after a pg_restore
The following bug has been logged online: Bug reference: 1286 Logged by: Federico Di Gregorio Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: Debian GNU/Linux sarge Description:indices not used after a pg_restore Details: We have a (big) database with a lot of functional indices (the indices are quite strange but should replicate an old ISAM sorting procedure). After a pg_dump/pg_restore (using the tar format) queries that were using the indices don't use them anymore until the indices are dropped and recreated. After that the indices are used the correct way. Note that after the pg_restore we also tried a complete VACUUM/ANALYZE/REINDEX but the situation does not change. The indices are not used until dropped and recreated. Please, if you discuss this on the bugs mailing list keep me in cc:. Example of one of the indices: CREATE INDEX "MOVIMENTII5" ON movimenti USING btree (upper(((to_char("TYPE_REF", 'S00'::text) || to_char("IDREF", 'S00'::text)) || to_char("IDMOVIMENT", 'S00'::text; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1285: Violacion de segmento
The following bug has been logged online: Bug reference: 1285 Logged by: Ximo Llacer Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: FC2 Description:Violacion de segmento Details: Hi. Most of the time psql shows me this message. Violacion de segmento (violation of segment). ---(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] BUG #1284: The backend has broken the connection.
The following bug has been logged online: Bug reference: 1284 Logged by: Shilpa Upadhye Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Redhat Linux 9.0 Description:The backend has broken the connection. Details: Hi, We have postgres 7.4.5 on Redhat Linux 9.0. We are using jakarta-tomcat-4.1.27,jdk 1.4.x,SOFIA 2.1 and pg74.214.jdbc2.jar We are getting the following error: The backend has broken the connection. Possibly the action you have attempted has caused it to close. at org.postgresql.PG_Stream.ReceiveChar(PG_Stream.java:143) at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:68) at org.postgresql.Connection.ExecSQL(Connection.java:398) at org.postgresql.jdbc2.Statement.execute(Statement.java:130) at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73) org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java at :113) at com.salmonllc.sql.DSDataSourceJDBC.insertRow(DSDataSourceJDBC.java:182) at com.salmonllc.sql.DataStore.update(DataStore.java:1977) at com.salmonllc.sql.DataStore.update(DataStore.java:1907) com.eq.model.operations.contractChargeModel.update(contractChargeModel.java at :426) at com.eq.model.operations.ContractWrapModel.update(ContractWrapModel.java:381) com.eq.controller.operations.ContractDetailsController.submitPerformed(Cont at ractDetailsController.java:378) Sometimes I also get the error 'Database is in recovery mode' Thanks and Regards, Shilpa. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1283: Queries slow down after restoring big db
The following bug has been logged online: Bug reference: 1283 Logged by: Fabio Fucci Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3 Operating system: linux Description:Queries slow down after restoring big db Details: We dumped a database with a lot of data using command pg_dump mydb > mydb.dump Then we reimported the data in this way: 1- createdb anotherdb 2- psql anotherdb < mydb.dump After the data was restored queries(SELECT queries, with JOINs) was very very slow. We solved the problem making a separate dump for the database schema and one for the data and then reimported first the schema and then the data. ---(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
[BUGS] BUG #1282: LIKE clause double-unescapes characters
The following bug has been logged online: Bug reference: 1282 Logged by: Justin Pasher Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.2 Operating system: Debian Linux (unstable) Description:LIKE clause double-unescapes characters Details: Perhaps I'm missing something, but I didn't see an explanation for this behavior in the docs (I also hope this wasn't fixed in a newer version of Postgres, as 7.4.2 is the only one I have access to). It looks like the Postgres query parser "double-unescapes" values for the LIKE clause, but not the = clause. Here's my example: justinp=# CREATE TABLE "test" (id serial, first_name varchar(50), last_name varchar(50), primary key(id)); NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE justinp=# INSERT INTO "test" ("first_name", "last_name") VALUES ('Test\\''n', 'Test''n'); INSERT 26586973 1 justinp=# SELECT * FROM "test"; id | first_name | last_name ++--- 1 | Test\'n| Test'n (1 row) justinp=# SELECT * FROM "test" WHERE "first_name" = 'Test\\''n'; id | first_name | last_name ++--- 1 | Test\'n| Test'n (1 row) justinp=# SELECT * FROM "test" WHERE "first_name" LIKE 'Test\\''n'; id | first_name | last_name ++--- (0 rows) justinp=# SELECT * FROM "test" WHERE "last_name" LIKE 'Test\\''n'; id | first_name | last_name ++--- 1 | Test\'n| Test'n (1 row) >From the results, you can see that the same query with the = converted into a LIKE causes the value of the right hand side to be decoded twice, (making "Test\\''n" turn into "Test\'n", then turn into "Test'n"). It happens this way whether you escape the single quote as '' or as \'. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1281: I cannot find the ODBC driver in available ODBC drivers
The following bug has been logged online: Bug reference: 1281 Logged by: Thomas Reinelöv Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Win2000 Description:I cannot find the ODBC driver in available ODBC drivers Details: After installation of the postgressql driver whithout any errors I cannot find the driver when trying to add it by Settings-ControlPAnel-Administration Tool- Datasource ODBC. When I try to add a System DSN I will not find the Postgres SQL in the available list. I see all the others. Where is the Postgres SQL driver Installed ?? ---(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] BUG #1280: Unexpected EOF at Client Connection
The following bug has been logged online: Bug reference: 1280 Logged by: Shilpa Upadhye Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: freebsd 5.1 Description:Unexpected EOF at Client Connection Details: Hi, Our database configuration is - postgres version 7.4.5 on FreeBSD 5.1. Our Web Server configuration is - SOFIA 2.1 framework, jakarta-tomcat 4.1.27 and pg74.213.jdbc2.jar and jdk 1.4.2.01 on Linux 9.0 We are getting 2 errors 1) We get the error 'Unexpected EOF at Client Connection' and appplication stops responding. This error is shown in the postgres logs. 2) At times we get the message 'Apache Tomcat Stopped' on the web server and tomcat stops. Initially we were using postgres version 7.4.1 on Redhat Linux 9.0. But we were not able to store large string of 18K in the column type text. But we could do it on postgres ver 7.4.5. If anybody has a solution to this please let me know. Thanks and Regards, Shilpa. ---(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
[BUGS] BUG #1279: pg_dump/resore has trouble with implicit sequences of renamed tables
The following bug has been logged online: Bug reference: 1279 Logged by: adrian kierman Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.1 Operating system: fedora2 Description:pg_dump/resore has trouble with implicit sequences of renamed tables Details: Thanks for a brilliant database - pg_dump/restore succesfully handled my 2 gig database transfer except for an issue with implicit sequences. Steps to recreate the bug 1. create a table with a serial data type and implicit sequence 2. rename the table to something else 3. select nextval a couple of times on the sequence to advace it 4. dump and restore it to a different db you'll notice that the sequence of the restored table is set to zero (or rather is_called=false). Thus the value of the sequence has not been loaded. In addition stored procedures making use of the original sequence will not work as the implicit sequence created on resore had a different name based on the now modified table name. potential solution: have pg_dump dump out the sequences explicitly instead of implicitly. -or- create an alter sequence name and use it to set the name back to the old db's sequence name before calling setval I dont know the backend well enough to know the potential issues with these aproaches. once again thanks for postgres ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #1278: PL/pgSQL: ROWTYPE does not care for droped columns
The following bug has been logged online: Bug reference: 1278 Logged by: Michael Howitz Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.7 Operating system: 2.6.8-gentoo-r3 Description:PL/pgSQL: ROWTYPE does not care for droped columns Details: IF you drop a column on a Table which is accessed via a PL/pgSQL-Function using tablename%ROWTYPE you get an Error. It seems that ROWTYPE does not take care about droped columns. Example in code: CREATE TABLE test ( id SERIAL, start_date DATE, testing INTEGER); INSERT INTO test (start_date, testing) VALUES ('2003-05-03', 1); SELECT * from test; -- test.id will be 1 ALTER TABLE test DROP COLUMN start_date; CREATE OR REPLACE FUNCTION dcTest(INTEGER) RETURNS INTEGER AS ' DECLARE tid ALIAS FOR $1; test_rec test%ROWTYPE; BEGIN SELECT INTO test_rec * FROM test WHERE id = tid; RETURN test_rec.testing; END; ' LANGUAGE 'plpgsql'; SELECT dcTest(1); gives the following Error: WARNING: plpgsql: ERROR during compile of dctest near line 0 ERROR: cache lookup for type 0 of test.pg.dropped.2 failed This code works correct in 7.4.1-dbExperts but also fails in 7.3.4-dbExperts. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1277: plpgsql EXECUTE bug in beta3
The following bug has been logged online: Bug reference: 1277 Logged by: Tom Hebbron Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: n/a Description:plpgsql EXECUTE bug in beta3 Details: In beta2, the following script runs as expected: --begin SQL script drop table a cascade; create or replace function execute_sql(text) returns void AS $$begin execute $1; return; end; $$ language plpgsql; select execute_sql('create table a (i integer); insert into a(i) values(1);'); select * from a; --eof SQL script The create table and insert commands are executed OK, and table 'a' contains a single row with value 1 in the column i. Under beta3, the following behaviour is observed: test=# drop table a cascade; ERROR: table "a" does not exist test=# test=# create or replace function execute_sql(text) returns void AS $$begin execute $1; return; end; $$ language plpgsql; CREATE FUNCTION test=# test=# select execute_sql('create table a (i integer); insert into a(i) values(1);'); ERROR: relation "a" does not exist CONTEXT: SQL query "create table a (i integer); insert into a(i) values(1);" PL/pgSQL function "execute_sql" line 1 at execute statement test=# test=# select * from a; ERROR: relation "a" does not exist test=# As demonstrated, some change in the plpgsql EXECUTE handler between beta2 and beta3 has caused multiple statement EXECUTE calls to be unable to see the effects of earlier statements in the same EXECUTE call. calling select execute_sql('begin; create table a (i integer); insert into a(i) values(1); commit;'); has the same results. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1276: Backend panics on SETVAL('..', 0)...
The following bug has been logged online: Bug reference: 1276 Logged by: Sean Chittenden Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: OS-X, FreeBSD Description:Backend panics on SETVAL('..', 0)... Details: I haven't been able to reproduce this in a controlled way, but on a large schema create in a single transaction, I was doing a SETVAL('foo_id_seq', 0) and the backend was panicing, which seems broken. This is using HEAD from a few hrs ago, but it's been going on for a while... I just stumbled across this again while online and it jogged my memory. -sc ERROR: setval: value 0 is out of bounds for sequence "foo_id_seq" (1..9223372036854775807) FATAL: block 0 of 1663/97972/98006 is still referenced (private 1, global 1) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost [EMAIL PROTECTED]: [local] 4030 2004-10-01 03:31:39 PDT LOG: statement: SELECT SETVAL('schemaa.foo_id_seq'::TEXT, (SELECT MAX(id) FROM schemaa.foo)); [EMAIL PROTECTED]: [local] 4030 2004-10-01 03:31:39 PDT ERROR: setval: value 0 is out of bounds for sequence "foo_id_seq" (1..9223372036854775807) [EMAIL PROTECTED]: [local] 4030 2004-10-01 03:31:39 PDT FATAL: block 0 of 1663/97972/98006 is still referenced (private 1, global 1) [EMAIL PROTECTED]: [local] 4030 2004-10-01 03:31:39 PDT LOG: disconnection: session time: 0:00:02.37 user=dba database=db host=[local] port= -- Sean Chittenden ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1275: Perda de velocidade na consulta com join.
The following bug has been logged online: Bug reference: 1275 Logged by: Marcio Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: Seleção de Dados Description:Perda de velocidade na consulta com join. Details: Quando executo um select com este formato fica 10 vezes mais lento do que na versão 7.3.6 Tabela Dupcli indice empdpl,numdpl,seriedpl Tabela Clientes indice empcli,codcli select * from dupcli left join clientes on (empcli = 'G01') and (codcli = codclidpl) se executo com o formato a seguir fica normal como na versão 7.3.6 select * from dupcli left join clientes on (empcli = empdpl) and (codcli = codclidpl) Qualquer duvida estou a disposição. Atenciosamente, Marcio - Neo Sistemas ---(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
[BUGS] BUG #1274: Trigger sequence incorrect
The following bug has been logged online: Bug reference: 1274 Logged by: shancheng Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Fedora 2 Description:Trigger sequence incorrect Details: The problem happens when I was trying the example that is described in "PostgreSQL 8.0.0beta1 Documentation, chapt 33.4 --- A Complete Example". At the last step, as the document says, the result should be: => DELETE FROM ttest; INFO: trigf (fired before): there are 2 rows in ttest INFO: trigf (fired after ): there are 1 rows in ttest INFO: trigf (fired before): there are 1 rows in ttest INFO: trigf (fired after ): there are 0 rows in ttest DELETE 2 But what i see is that: test=# DELETE FROM ttest; NOTICE: trigf (fired before): there are 2 tuples in ttest NOTICE: trigf (fired before): there are 1 tuples in ttest NOTICE: trigf (fired after ): there are 0 tuples in ttest NOTICE: trigf (fired after ): there are 0 tuples in ttest DELETE 2 The execution sequence of the triggers is incorrect. ---(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
[BUGS] BUG #1273: bad path for english.stop in tsearch2
The following bug has been logged online: Bug reference: 1273 Logged by: Werner Bohl Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows XP pro Description:bad path for english.stop in tsearch2 Details: Running Postgresql 8.0beta2-dev3. When issuing a query using tsearch2. It erred, looking for english.stop in /usr/local/psql/share/contrib. Fixed creating that directory structure and placing english.stop there, but it should look for that file under Program Files/PostgreSQL/8.0-beta2-dev3/share/contrib. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1272: text + numeric wrong behaviour
The following bug has been logged online: Bug reference: 1272 Logged by: Damjan Pipan Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.2 Operating system: linux 2.4 Description:text + numeric wrong behaviour Details: CREATE TABLE a1( description text, amount numeric(9,2) ); INSERT INTO a1 VALUES ('asdasd', 370); SELECT * FROM a1; SELECT description::varchar, amount FROM a1; first select does not return amount, second does. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1271: Installation problems (cannot execute initdb.exe)
The following bug has been logged online: Bug reference: 1271 Logged by: Ramzi Fadel Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Windows XP Prof, 5.1.2600 Build 2600 Description:Installation problems (cannot execute initdb.exe) Details: The installation file: postgresql-8.0-beta2-dev3.msi Error log; see below. Steps taken: - Run the installation with a administrator user (I do expect the installation to complete!) - The installation creates an user for the service named "postrunner" - Log off - Log in with the "postrunner" user - Run the installation. The installation fails during creation of the cluster, see below: --- Initdb.log: --- The files belonging to this database system will be owned by user "raf". This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory C:/PGSQL80/data ... ok creating directory C:/PGSQL80/data/global ... ok creating directory C:/PGSQL80/data/pg_xlog ... ok creating directory C:/PGSQL80/data/pg_xlog/archive_status ... ok creating directory C:/PGSQL80/data/pg_clog ... ok creating directory C:/PGSQL80/data/pg_subtrans ... ok creating directory C:/PGSQL80/data/base ... ok creating directory C:/PGSQL80/data/base/1 ... ok creating directory C:/PGSQL80/data/pg_tblspc ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 50 creating configuration files ... ok creating template1 database in C:/PGSQL80/data/base/1 ... execution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromise. See the documentation for more information on how to properly start the server. child process was terminated by signal 1 initdb: failed initdb: removing contents of data directory "C:/PGSQL80/data" ---(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
[BUGS] BUG #1270: stack overflow in thread in fe_getauthname
The following bug has been logged online: Bug reference: 1270 Logged by: Peter Davie Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: OSF/1 4.0f Description:stack overflow in thread in fe_getauthname Details: With the THREAD_SAFETY changes, a buffer is defined on the stack as: char pwdbuf[BUFSIZ]; This buffer overflows the stack when used in a thread. As the application creating the thread cannot be modified to increase the stack size, it would probably be prudent to reduce this buffer size (I believe that BUFSIZ is around 8192 bytes on most modern Unix implementations). To rectify this issue (seg faults attempting to connect to the database), I replaced the above declaration with: char pwdbuf[1024]; Obviously, a manifest constant would be better! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #1269: Copy To Gives Relative path error when explicit path given.
The following bug has been logged online: Bug reference: 1269 Logged by: William Mayhew Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: WIN 2000 Professional Description:Copy To Gives Relative path error when explicit path given. Details: I just installed the 8.0 Beta version I downloaded last night and created a test database today entitled 'testdb'. All file systems on my PC are NTFS. I logged onto the 'testdb' database via the Windows command prompt, using psql. Immediately, I got the following warning: Warning: Console codepage (437) differs from windows codepage (1252) 8-bit characters will not work correctly. See PostgreSQL documentation "Installation on Windows" for details. Next, I created a table called 'users'. Before I continue to describe the problem, please be aware that the exact same problem resulted after leaving the codepage settings unchanged, and after changing the codepage to 437 via the following command: cmd.exe /c chcp 437. I chose 437 because it seemed logical to try that in the event the default codepage had some undesirable effect on the outcome of my problem. OK, back to the problem. After creating a few rows in the users table. Of course, I ran a count(*) query and the number of rows matched the number I inserted into the table. Just to make sure, I queried all rows to verify the exact data. Next, I tried to export the data into a flat file. Here is my syntax and the accompanying error: testdb=# COPY users TO 'D:\Program Files\PostgreSQL\DataFlatFiles\usersdata'; ERROR: relative path not allowed for COPY to file That looks like an explicit filename to me, so how can the server view the quoted filename as a relative path? What exactly is the server looking for? Out of desperation I tried to use double quotes, but that seemed to generate a syntax error. Next, I thought I would try to change the current working directory to the exact location where I wanted to write the output file, "usersdata." On this attempt, I did not refer to the drive letter or the path. Here are the results of my plsql commands: Step 1 testdb-# \! dir Volume in drive D has no label. Volume Serial Number is 78D0-B9A2 Directory of D:\Program Files\PostgreSQL\DataFlatFiles 09/25/2004 07:36p. 09/25/2004 07:36p.. 0 File(s) 0 bytes 2 Dir(s) 6,354,862,080 bytes free Step 2 testdb=# COPY users TO 'usersdata.txt'; ERROR: relative path not allowed for COPY to file Am I doing something wrong or is this an actual bug? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1268: Two different Unicode chars are treated as equal in a query
The following bug has been logged online: Bug reference: 1268 Logged by: Kent Tong Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: RedHat 9 Description:Two different Unicode chars are treated as equal in a query Details: Steps: 1. Create a test database: "createdb -E Unicode -U postgres testdb". 2. Create a test table: "create table testtable (id varchar(100) primary key);". 3. With JDBC, insert a record whose id contains unicode: "insert into testtable values();". 4. With JDBC, try to retrieve a record whose id contains a different unicde: "select from testtable where id=;". It should not find any record but it finds the record created in step 3. Here is the JUnit test case: public class PgSQLTest extends TestCase { private Connection conn; protected void setUp() throws Exception { conn = makeConnection(); } protected void tearDown() throws Exception { conn.close(); } public void testChinese() throws Exception { deleteAll(); insertRow(); PreparedStatement st = conn.prepareStatement("select * from testtable where id=?"); try { st.setString(1, "\u4e94"); ResultSet rs = st.executeQuery(); assertFalse(rs.next()); } finally { st.close(); } } private void insertRow() throws SQLException { PreparedStatement st = conn.prepareStatement("insert into testtable values(?)"); st.setString(1, "\u4e8c"); st.executeUpdate(); st.close(); } private void deleteAll() throws SQLException { PreparedStatement st = conn.prepareStatement("delete from testtable"); st.executeUpdate(); st.close(); } private Connection makeConnection() throws ClassNotFoundException, SQLException { Class.forName("org.postgresql.Driver"); Properties properties = new Properties(); properties.put("user", "postgres"); properties.put("password", ""); return DriverManager.getConnection( "jdbc:postgresql://localhost/testdb", properties); } } ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1267: Suggest TEXTOID parameters be treated like UNKNOWNOID
The following bug has been logged online: Bug reference: 1267 Logged by: Colin Chapman Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: fedora linux Description:Suggest TEXTOID parameters be treated like UNKNOWNOID Details: postgresql-8.0.0beta2 JSP likes to transfer data in parameters as text. When putting values into a int column you get exception this can be demonstrated by. create table d ( numb int ); insert into d values ( '1' ); insert into d values ( '2'::text ); drop table d; --- ./parse_coerce.c2004-08-29 06:06:44.0 +0100 +++ /usr/local/postgresql-8.0.0beta2/src/backend/parser/parse_coerce.c 2004-09-23 13:05:34.383199696 +0100 @@ -137,7 +137,7 @@ /* NB: we do NOT want a RelabelType here */ return node; } - if (inputTypeId == UNKNOWNOID && IsA(node, Const)) + if ( ( ( ( inputTypeId == UNKNOWNOID) || (inputTypeId == TEXTOID ) ) && IsA(node, Const))&& IsA(node, Const) ) { /* * Input is a string constant with previously undetermined type. @@ -197,7 +197,7 @@ return result; } - if (inputTypeId == UNKNOWNOID && IsA(node, Param) && + if ( ( ( inputTypeId == UNKNOWNOID ) || ( inputTypeId == TEXTOID ) ) && IsA(node, Param) && ((Param *) node)->paramkind == PARAM_NUM && pstate != NULL && pstate->p_variableparams) { @@ -220,7 +220,7 @@ (errcode(ERRCODE_UNDEFINED_PARAMETER), errmsg("there is no parameter $%d", paramno))); - if (toppstate->p_paramtypes[paramno - 1] == UNKNOWNOID) + if ( (toppstate->p_paramtypes[paramno - 1] == UNKNOWNOID) || (toppstate->p_paramtypes[paramno - 1] == TEXTOID ) ) { /* We've successfully resolved the type */ toppstate->p_paramtypes[paramno - 1] = targetTypeId; @@ -373,7 +373,7 @@ * If input is an untyped string constant, assume we can convert * it to anything. */ - if (inputTypeId == UNKNOWNOID) +if ( ( inputTypeId == UNKNOWNOID) || (inputTypeId == TEXTOID ) ) continue; /* ---(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
[BUGS] BUG #1266: Improper unique constraint / MVCC activities within single transaction
The following bug has been logged online: Bug reference: 1266 Logged by: Aleksey Fedorchenko Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: MS Windows 2003 Description:Improper unique constraint / MVCC activities within single transaction Details: The following problems were discovered under: 1. postgres (PostgreSQL) 8.0.0beta1 / psql (PostgreSQL) 8.0.0beta1 (native Win32 release) 2. postgres (PostgreSQL) 7.4.2 / psql (PostgreSQL) 7.4.2 (own CygWin 1.5.5 source based build) Test tables definition: /* */ create table buggy_uq ( i integer unique not null ); create table buggy_uq_parent ( i integer primary key ); create table buggy_uq_child ( i integer unique references buggy_uq_parent on delete cascade ); /* */ Test cases: /* */ --case 1 prepare delete from buggy_uq; insert into buggy_uq values (1); insert into buggy_uq values (2); --case 1 test update buggy_uq set i = i + 1; select * from buggy_uq; --expect - SUCCESS --result - ERROR: duplicate key violates unique constraint "buggy_uq_i_key" --case 2 prepare delete from buggy_uq_child; delete from buggy_uq_parent; insert into buggy_uq_parent values (1); insert into buggy_uq_child values (1); --case 2 test delete from buggy_uq_parent; insert into buggy_uq_child values (1); select * from buggy_uq_child; --expect - ERROR: insert or update on table "buggy_uq_child" violates foreign key constraint "$1" --result - ERROR: duplicate key violates unique constraint "buggy_uq_child_i_key" --case 3 prepare delete from buggy_uq_child; delete from buggy_uq_parent; insert into buggy_uq_parent values (1); insert into buggy_uq_child values (1); delete from buggy_uq_child; delete from buggy_uq_parent; --case 3 test insert into buggy_uq_child values (1); select * from buggy_uq_child; --expect - ERROR: insert or update on table "buggy_uq_child" violates foreign key constraint "$1" --result - SUCCESS /* */ Notes and description: As you could you see, the first one is related only to unique constraint itself while second and third are connected with MVCC. On case 1. The result is dependant on values insertion order. For example, if we insert a set of descendant values (e.g. 10,9,8,...) then it works fine othervise (e.g. 1,2,3,...) it fails. Due to the standard it sould work fine in both cases. On case 2 and 3. They are the variances of the single problem - it seems that unique constraint's implicit index is not updated in a moment of value deletion. In the second case we face with problem that values that have to be implicitly deleted from the child table with cascade option still exists in unique index and violates other operation (the shown example slightly differs from real application scenario). In the third case we face with problem that values that were explicitly deleted both from the parent and child tables doesn't exists by itself but still contained in indecies (success child insertion after deletion of parent/child records). It violates operation silently only on transaction commit and followed select returns empty record set. PS: Possibly i'm wrong and all mentioned isn't a bug but feature however in other RDBMS systems (Oracle / MSSQL) such scenarios works fine. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1265: sorting by (ORDER BY) result of an operation for names assigned by AS does not work
The following bug has been logged online: Bug reference: 1265 Logged by: Ansis Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.2 Operating system: Gentoo Linux Description:sorting by (ORDER BY) result of an operation for names assigned by AS does not work Details: The query: SELECT id, lang as name, (SELECT lang FROM participants WHERE id = event.participant) as pname FROM event ORDER BY name || pname; returns an error: ERROR: column "name" does not exist However, "ORDER BY name" works, "ORDER BY name, pname" works etc. Also, if I use original column names, not assigned ones, it works with cncatenation operator too. So, the error occurs only then assigned names are used inside operator - the Postgre does not find this name. It looks like a bug - and an easy fixable one. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1264: Jimminy Cricket
The following bug has been logged online: Bug reference: 1264 Logged by: Nadia Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.7 Operating system: Windows XP Description:Jimminy Cricket Details: This bug looks cute and convincing, but BEWARE. Despite his glib, sweet voice and excellent singing capabilitites he will SRIOUSLY mess up your solitaire program. My solitaire team fell victim to this bug last week at a competition held at Marcy's apartment, where exterminators had just visited, making us believe we were safe from potential bugs. But no. We sadly now all have a piece of "The Cricket" in our hearts and hard drives. Please warn anyone out there who may think their solitaire game is safe to keep their eyes out for Jimminy. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] BUG #1263: Jimminy Cricket
The following bug has been logged online: Bug reference: 1263 Logged by: Nadia Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.7 Operating system: Windows XP Description:Jimminy Cricket Details: This bug looks cute and convincing, but BEWARE. Despite his glib, sweet voice and excellent singing capabilitites he will SRIOUSLY mess up your solitaire program. My solitaire team fell victim to this bug last week at a competition held at Marcy's apartment, where exterminators had just visited, making us believe we were safe from potential bugs. But no. We sadly now all have a piece of "The Cricket" in our hearts and hard drives. Please warn anyone out there who may think their solitaire game is safe to keep their eyes out for Jimminy. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1262: Jimminy Cricket
The following bug has been logged online: Bug reference: 1262 Logged by: Nadia Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.7 Operating system: Windows XP Description:Jimminy Cricket Details: This bug looks cute and convincing, but BEWARE. Despite his glib, sweet voice and excellent singing capabilitites he will SRIOUSLY mess up your solitaire program. My solitaire team fell victim to this bug last week at a competition held at Marcy's apartment, where exterminators had just visited, making us believe we were safe from potential bugs. But no. We sadly now all have a piece of "The Cricket" in our hearts and hard drives. Please warn anyone out there who may think their solitaire game is safe to keep their eyes out for Jimminy. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1261: CREATE DATABASE OWNER not propagating to child objects?
The following bug has been logged online: Bug reference: 1261 Logged by: Sean Chittenden Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: OS-X, FreeBSD Description:CREATE DATABASE OWNER not propagating to child objects? Details: Howdy. I think this problem is best demonstrated with a test case: template1=# CREATE DATABASE foo OWNER someuser; CREATE DATABASE template1=# \c foo You are now connected to database "foo". foo=# \dn List of schemas Name| Owner +--- information_schema | dba pg_catalog | dba pg_toast | dba public | dba (4 rows) ?? I set the owner to someuser. A listing from \l reveals that the database is indeed owned by the user someuser, but, since some user is not a super user, this causes problems when someuser tries to perform operations in the public schema. My use case being, when I create a new database for a user who isn't a super user, I execute the following as someuser: \c foo someuser REVOKE ALL PRIVILEGES ON DATABASE foo FROM PUBLIC CASCADE; GRANT CREATE,TEMPORARY ON DATABASE foo TO someuser; WARNING: no privileges could be revoked REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC CASCADE; WARNING: no privileges were granted GRANT USAGE ON SCHEMA public TO PUBLIC; Which makes sense since someuser doesn't own the schema... but I can't help but think someuser should. I'm guessing dime to dollar most database owners are also superusers so this hasn't been a problem to date. When not a superuser and I try and plop some functions into the public schema as someuser, I get the following: \c foo someuser foo=> SHOW search_path ; search_path -- $user,public (1 row) foo=> CREATE FUNCTION bar() RETURNS VOID AS 'BEGIN RETURN; END;' LANGUAGE 'plpgsql'; ERROR: permission denied for schema public Which seems like the most egregious problem to me. When looking into the createdb() code in src/backend/commands/dbcommands.c, I noticed that the owner is only used to set the database owner and does nothing to set the owner of the objects which are copied from the template database. This seems really scary to me from a security perspective... man I'm sure glad I trust template1... having template1 open for business to anyone by default is creapy, however. "CREATE EMPTY DATABASE foo," anybody? :) The dependency on 'cp -r' would go away if an empty database was created natively in the backend. :) Empty being defined as only pg_catalog, pg_toast, and public with no permissions granted on it (not even the information_schema schema). My $0.02. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1260: standard compilation
The following bug has been logged online: Bug reference: 1260 Logged by: Blazej Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.3 Operating system: Red Hat 9.0 (without updates) Description:standard compilation Details: Posiible bug in contrib/intarray package (function idx) For test I use FoodMart Database from Mondrian Project (mondrian data): http://sourceforge.net/projects/mondrian/ build table (for rank): = BEGIN QUER = CREATE TABLE rank_test ( idx serial NOT NULL, week_of_year int4, the_year int4, rank int4[], CONSTRAINT rank_test_pkey PRIMARY KEY (idx) ) WITH OIDS; = END QUERY = Insert to table rank index of customers: = BEGIN QUER = INSERT INTO rank_test(week_of_year, the_year, rank) SELECT t1.week_of_year, t1.the_year, array_accum(t1.customer_id) FROM (SELECT sum(store_sales*unit_sales), week_of_year, the_year, customer_id FROM ( SELECT * FROM sales_fact_1997 sf97 LEFT OUTER JOIN time_by_day td ON (sf97.time_id = td.time_id) UNION SELECT * FROM sales_fact_1998 sf98 LEFT OUTER JOIN time_by_day td ON (sf98.time_id = td.time_id) UNION SELECT * FROM sales_fact_dec_1998 sf98d LEFT OUTER JOIN time_by_day td ON (sf98d.time_id = td.time_id) ) AS sf_all GROUP BY week_of_year, the_year, customer_id ORDER BY the_year, week_of_year, sum, customer_id ) AS t1 GROUP BY t1.week_of_year, t1.the_year ORDER BY t1.the_year, t1.week_of_year and when I run: SELECT idx, idx(rank, 4676), icount(rank) FROM rank_test WHERE 4767 = ANY (rank) = END QUERY = I get this: idx;idx;icount 2;286;605 10;59;294 11;0;303<- wrong 18;189;285 20;377;505 21;200;323 25;0;227<- wrong 27;0;332<- wrong 28;412;422 31;308;347 33;0;348<- wrong 34;0;470<- wrong 43;311;480 48;330;463 49;0;400<- wrong 56;488;1035 61;247;798 64;236;642 68;413;434 69;701;738 70;497;636 80;692;784 84;0;925<- wrong 89;395;579 92;0;567<- wrong 93;541;768 95;338;651 101;0;578<- wrong 102;117;851 103;160;694 104;0;70<- wrong 106;286;605 114;59;294 115;0;303<- wrong 122;189;285 124;377;505 125;200;323 129;0;227<- wrong 131;0;332<- wrong 132;412;422 135;308;347 137;0;348<- wrong 138;0;470<- wrong 147;311;480 152;330;463 153;0;400<- wrong 160;488;1035 165;247;798 168;236;642 172;413;434 173;701;738 174;497;636 184;692;784 188;0;925<- wrong 193;395;579 196;0;567<- wrong 197;541;768 199;338;651 205;0;578<- wrong 206;117;851 207;160;694 208;0;70<- wrong 210;286;605 218;59;294 219;0;303<- wrong 226;189;285 228;377;505 229;200;323 233;0;227<- wrong 235;0;332<- wrong 236;412;422 239;308;347 241;0;348<- wrong 242;0;470<- wrong 251;311;480 256;330;463 257;0;400<- wrong 264;488;1035 269;247;798 272;236;642 276;413;434 277;701;738 278;497;636 288;692;784 292;0;925<- wrong 297;395;579 300;0;567<- wrong 301;541;768 303;338;651 309;0;578<- wrong 310;117;851 311;160;694 312;0;70<- wrong *** BUG *** for example in this output idx=11 (idx from rank_test table, 11 row) function return idx(rank, 4676)=0 ?? <- here BUG Blazej ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1259: garbage in pg_listener after backend crash
The following bug has been logged online: Bug reference: 1259 Logged by: Vadim Passynkov Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: FreeBSD 4.10-STABLE Description:garbage in pg_listener after backend crash Details: If current backend crash after registers on the notification condition ( 'LISTEN ' SQL command ) row in the pg_listener still exists; == template1=# SELECT pg_backend_pid (); pg_backend_pid 6312 (1 row) template1=# LISTEN test; LISTEN template1=# SELECT * from pg_listener where listenerpid = 6312; relname | listenerpid | notification -+-+-- test|6312 |0 (1 row) template1=# SELECT crash_backend ( ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !> \c template1 pgsql You are now connected to database "template1" as user "pgsql". template1=# SELECT * from pg_listener where listenerpid = 6312; relname | listenerpid | notification -+-+-- test|6312 |0 (1 row) == /* crash_backend.c */ #include "postgres.h" #include "executor/spi.h" PG_FUNCTION_INFO_V1 ( crash_backend ); Datum crash_backend ( PG_FUNCTION_ARGS ) { char *ptr = NULL; *ptr = '1'; PG_RETURN_INT32 ( 1 ); } ====== cc -Wall -fpic -I/usr/local/include/postgresql/server -I/usr/local/include -c crash_backend.c -o crash_backend.o cc -shared -o crash_backend.so crash_backend.o == CREATE OR REPLACE FUNCTION crash_backend ( ) RETURNS integer AS '/usr/home/pvi/pg_bug/crash_backend.so', 'crash_backend' LANGUAGE 'C'; == -- Vadim Passynkov ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1258: backend memory leak after massive 'CREATE/DROP USER'
The following bug has been logged online: Bug reference: 1258 Logged by: Vadim Passynkov Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5 Operating system: FreeBSD 4.10-STABLE Description:backend memory leak after massive 'CREATE/DROP USER' Details: log generated by create_drop_user.sh == Fri Sep 17 18:16:47 EDT 2004 VSZ RSS COMMAND 16612 3988 /usr/local/bin/postmaster (postgres) Creating 3 users Fri Sep 17 18:24:27 EDT 2004 VSZ RSS COMMAND 17572 3904 /usr/local/bin/postmaster (postgres) Dropping 3 users Fri Sep 17 18:29:54 EDT 2004 VSZ RSS COMMAND 18596 4292 /usr/local/bin/postmaster (postgres) Creating 3 users Fri Sep 17 18:37:34 EDT 2004 VSZ RSS COMMAND 20652 4980 /usr/local/bin/postmaster (postgres) Dropping 3 users Fri Sep 17 18:43:02 EDT 2004 VSZ RSS COMMAND 20652 5648 /usr/local/bin/postmaster (postgres) Creating 3 users Fri Sep 17 18:50:47 EDT 2004 VSZ RSS COMMAND 20652 6388 /usr/local/bin/postmaster (postgres) Dropping 3 users Fri Sep 17 18:56:16 EDT 2004 VSZ RSS COMMAND 24748 7064 /usr/local/bin/postmaster (postgres) Creating 3 users Fri Sep 17 19:04:00 EDT 2004 VSZ RSS COMMAND 24748 7796 /usr/local/bin/postmaster (postgres) Dropping 3 users Fri Sep 17 19:09:31 EDT 2004 VSZ RSS COMMAND 24748 8504 /usr/local/bin/postmaster (postgres) Creating 3 users Fri Sep 17 19:17:13 EDT 2004 VSZ RSS COMMAND 24748 9200 /usr/local/bin/postmaster (postgres) Dropping 3 users Fri Sep 17 19:22:41 EDT 2004 VSZ RSS COMMAND 24748 9904 /usr/local/bin/postmaster (postgres) Creating 3 users Fri Sep 17 19:30:23 EDT 2004 VSZ RSS COMMAND 24748 10604 /usr/local/bin/postmaster (postgres) Dropping 3 users Fri Sep 17 19:35:54 EDT 2004 VSZ RSS COMMAND 32952 11312 /usr/local/bin/postmaster (postgres) Creating 3 users Fri Sep 17 19:43:39 EDT 2004 VSZ RSS COMMAND 32952 12016 /usr/local/bin/postmaster (postgres) Dropping 3 users == gcc -I/usr/local/include -L/usr/local/lib -lpq create_drop_user.c -o create_drop_user /* create_drop_user.c */ #include #include #include #include int main ( int argc, char **argv ) { int i, fd; PGconn *pgconn; char buff[1024]; if ( ( pgconn = PQsetdbLogin ( NULL, NULL, NULL, NULL, "template1", "pgsql", NULL ) ) != NULL ) { printf ( "Current PQ fd=%d\n", PQsocket ( pgconn ) ); for ( i = 0; i < 3; i++ ) { sprintf ( buff, "%s USER user%d", argv[1], i ); PQexec ( pgconn, buff ); printf ( "Current user%d\n", i ); } PQexec ( pgconn, "VACUUM FULL" ); PQfinish ( pgconn ); } return 0; } == create_drop_user.sh #!/bin/sh while ( true ); do date ps -ax -o vsz,rss,command | grep '/usr/local/bin/postm\|COMMAND$' | grep -v grep echo "Creating 3 users" ./create_drop_user create > /dev/null date ps -ax -o vsz,rss,command | grep '/usr/local/bin/postm\|COMMAND$' | grep -v grep echo "Dropping 3 users" ./create_drop_user drop > /dev/null done == ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1257: add support for 1-byte integer and 4-bit integer
The following bug has been logged online: Bug reference: 1257 Logged by: stig Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: all Description:add support for 1-byte integer and 4-bit integer Details: hello, i could not find this in the 7.4.5 docs. for the next version of postgresql i would like to see support for 1-byte integers (signed and unsigned) and also for 4-bit (1/2-byte) integers (signed and unsigned). in case both signed/unsigned is a problem to implement, unsigned is more important (at least to me). why is this feature important? having in mind the development of datawarehouses with huge amount of data (hundreds of millions, or billions of rows in fact tables) every byte is of importance. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html