[BUGS] "like" and "ilike" not working properly on postgresql 9.0.3 where clause, bug fix not mentioned on 9.0.5 either
To whom it may concern, like and ilke not working on postgresql 9.0.3. Bug fix is not mentioned on 9.0.5 either. The same statement works very well on postgresql 8.4. Kind Regards, Mlungisi Sincuba
Re: [BUGS] "like" and "ilike" not working properly on postgresql 9.0.3 where clause, bug fix not mentioned on 9.0.5 either
Hello 2011/11/18 Mlungisi Sincuba : > To whom it may concern, > like and ilke not working on postgresql 9.0.3. Bug fix is not mentioned on > 9.0.5 either. The same statement works very well on postgresql 8.4. > Kind Regards, > Mlungisi Sincuba can you send more informations, please? Can you send a related SQL statement, result from 9.0 and 8.4? Regards Pavel Stehule -- 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] "like" and "ilike" not working properly on postgresql 9.0.3 where clause, bug fix not mentioned on 9.0.5 either
Mlungisi Sincuba wrote: > To whom it may concern, > > like and ilke not working on postgresql 9.0.3. Bug fix is not > mentioned on 9.0.5 either. The same statement works very well on > postgresql 8.4. They work for me. Perhaps you used a different locale configuration under the new version? http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -Kevin -- 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 #6300: duplicate key value violates unique constraint
The following bug has been logged online: Bug reference: 6300 Logged by: Tigran Mkrtchyan Email address: tigran.mkrtch...@desy.de PostgreSQL version: 9.0.4 Operating system: Linux x86_64 Description:duplicate key value violates unique constraint Details: Hi, our application uses PostgreSQL as a backend and heavily relays on transactions. We never check constrains in our application as DB does is better than we can an in an atomic way. This works very-very good! Nevertheless the logfiles are full of 'duplicate key value violates unique constraint'. I have two questions: 1. why it logged with ERROR level as it clients application behavior. 2. is there a way to suppress it or could you add a way to suppress it. Thanks a lot, Tigran. -- 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] BUG #6300: duplicate key value violates unique constraint
On 18 Listopad 2011, 17:42, Tigran Mkrtchyan wrote: > > The following bug has been logged online: > > Bug reference: 6300 > Logged by: Tigran Mkrtchyan > Email address: tigran.mkrtch...@desy.de > PostgreSQL version: 9.0.4 > Operating system: Linux x86_64 > Description:duplicate key value violates unique constraint > Details: > > Hi, > > our application uses PostgreSQL as a backend and heavily > relays on transactions. We never check constrains in our application as DB > does is better than we can an in an atomic way. This works very-very good! > Nevertheless the logfiles are full of 'duplicate key value violates unique > constraint'. I have two questions: If it's a question, then why have you submitted it as a bug? This is not a bug, it's correct and expected behavior - please, repost the questions to the appropriate place, e.g. pgsql-general mailing list. Tomas > > 1. why it logged with ERROR level as it clients application behavior. > > 2. is there a way to suppress it or could you add a way to suppress it. > > > Thanks a lot, >Tigran. > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- 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 #6301: extra space in psql variable expansion
The following bug has been logged online:
Bug reference: 6301
Logged by: M
Email address: sitr...@email.com
PostgreSQL version: 8.4.9
Operating system: Linux
Description:extra space in psql variable expansion
Details:
First of all, thank you for supporting a very useful product!
When psql expands a :variable into a string it appends a space to the
expansion string. For example:
psql (8.4.9)
Type "help" for help.
testdb=> \set my_home /home/crazy
testdb=> \echo :my_home/my-script.sql
/home/crazy /my-script.sql
Note the space between /home/crazy and /my-script.sql in the line above,
while there's no space after /home/crazy two lines above where my_home
variable is initialized.
This prevents use of a variable for storing the directory name where a
script to be sourced resides, as in
\i :my_dir/my-script.sql
because the expanded string contains a space between the expansion of
:my_dir and the following /my-script.sql.
I believe psql should expand the string :my_dir/my-script.sql into
/home/crazy/my-script.sql (note the lack of space between crazy and the
following / character).
No popular scripting language I'm aware of appends the space character to an
expanded variable, so psql's behavior is rather unusual. E.g.
${HOME}/my-script.sql expands into /home/crazy/my-script.sql in a Unix shell
script.
--
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] BUG #6300: duplicate key value violates unique constraint
On Fri, Nov 18, 2011 at 6:52 PM, Tomas Vondra wrote: > On 18 Listopad 2011, 17:42, Tigran Mkrtchyan wrote: >> >> The following bug has been logged online: >> >> Bug reference: 6300 >> Logged by: Tigran Mkrtchyan >> Email address: tigran.mkrtch...@desy.de >> PostgreSQL version: 9.0.4 >> Operating system: Linux x86_64 >> Description: duplicate key value violates unique constraint >> Details: >> >> Hi, >> >> our application uses PostgreSQL as a backend and heavily >> relays on transactions. We never check constrains in our application as DB >> does is better than we can an in an atomic way. This works very-very good! >> Nevertheless the logfiles are full of 'duplicate key value violates unique >> constraint'. I have two questions: > > If it's a question, then why have you submitted it as a bug? Then let me re-phrase it: I think it's a bug to log with level ERROR absolute valid SQL statement. You have error code returned to the client to handle it. If I want to log statements then I can turned that on. This is client appication debuggging and to error handling. Regards, Tigran. >This is not a > bug, it's correct and expected behavior - please, repost the questions to > the appropriate place, e.g. pgsql-general mailing list. > > Tomas > >> >> 1. why it logged with ERROR level as it clients application behavior. >> >> 2. is there a way to suppress it or could you add a way to suppress it. >> >> >> Thanks a lot, >> Tigran. >> >> -- >> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-bugs >> > > > -- 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] BUG #6301: extra space in psql variable expansion
On Fri, Nov 18, 2011 at 1:12 PM, M wrote: > > When psql expands a :variable into a string it appends a space to the > expansion string. For example: > > psql (8.4.9) > Type "help" for help. > > testdb=> \set my_home /home/crazy > testdb=> \echo :my_home/my-script.sql > /home/crazy /my-script.sql > > Note the space between /home/crazy and /my-script.sql in the line above, > while there's no space after /home/crazy two lines above where my_home > variable is initialized. It looks like this is a psql bug which has been fixed recently. I see the OP's reported behavior on a 9.1.1 client, but in 9.2dev it works as expected: test=# \set my_home /home/crazy test=# \echo :my_home/my-script.sql /home/crazy/my-script.sql I don't see the exact commit which fixed this, but I do see some fixes to psql's lexer done recently, such as 928311a463d480ca566e2905a369ac6aa0c3e210, so maybe this case got fixed as a nice side-effect. Josh -- 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] BUG #6300: duplicate key value violates unique constraint
On 18 Listopad 2011, 20:38, Tigran Mkrtchyan wrote: > On Fri, Nov 18, 2011 at 6:52 PM, Tomas Vondra wrote: >> On 18 Listopad 2011, 17:42, Tigran Mkrtchyan wrote: >>> >>> The following bug has been logged online: >>> >>> Bug reference: 6300 >>> Logged by: Tigran Mkrtchyan >>> Email address: tigran.mkrtch...@desy.de >>> PostgreSQL version: 9.0.4 >>> Operating system: Linux x86_64 >>> Description: duplicate key value violates unique constraint >>> Details: >>> >>> Hi, >>> >>> our application uses PostgreSQL as a backend and heavily >>> relays on transactions. We never check constrains in our application as >>> DB >>> does is better than we can an in an atomic way. This works very-very >>> good! >>> Nevertheless the logfiles are full of 'duplicate key value violates >>> unique >>> constraint'. I have two questions: >> >> If it's a question, then why have you submitted it as a bug? > > Then let me re-phrase it: > > I think it's a bug to log with level ERROR absolute valid SQL > statement. You have error code returned to the client to handle it. If > I want to log statements then I can turned that on. This is client > appication debuggging and to error handling. The SQL command may be syntactically correct, but breaking a UNIQUE constraint is an error condition and therefore it's handled like an error condition - the user receives an exception and a message with ERROR level is written to the log. If you don't want to see those messages in the log, there are ways to do that correctly - e.g. by using database sequences. I personally think that an application that generates significant amount of "duplicate values" during normal operation is somehow broken. Anyway this definitely is not a bug, so if you want to discuss it further (and how to fix you application), use pgsql-general mailing list. Tomas -- 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] BUG #6301: extra space in psql variable expansion
On Fri, Nov 18, 2011 at 4:53 PM, Michael Smolsky wrote: > Could you please also confirm that variable expansion works correctly for > psql's \i command as well? > > Test case (type on psql client prompt): > > \set my_dir /some/path/to/sql/script/dir > \i :my_dir/my-script.sql > > I expect the parser to load and execute my-script.sql located within > /some/path/to/sql/script/dir Yup, that works on a 9.2dev client: test=# \set my_dir /tmp/foo/bar/baz test=# \i :my_dir/test.sql ?column? -- 1 (1 row) with test.sql containing the single line "SELECT 1;" Josh -- 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] BUG #6300: duplicate key value violates unique constraint
On Fri, Nov 18, 2011 at 9:52 PM, Tomas Vondra wrote: > On 18 Listopad 2011, 20:38, Tigran Mkrtchyan wrote: >> On Fri, Nov 18, 2011 at 6:52 PM, Tomas Vondra wrote: >>> On 18 Listopad 2011, 17:42, Tigran Mkrtchyan wrote: The following bug has been logged online: Bug reference: 6300 Logged by: Tigran Mkrtchyan Email address: tigran.mkrtch...@desy.de PostgreSQL version: 9.0.4 Operating system: Linux x86_64 Description: duplicate key value violates unique constraint Details: Hi, our application uses PostgreSQL as a backend and heavily relays on transactions. We never check constrains in our application as DB does is better than we can an in an atomic way. This works very-very good! Nevertheless the logfiles are full of 'duplicate key value violates unique constraint'. I have two questions: >>> >>> If it's a question, then why have you submitted it as a bug? >> >> Then let me re-phrase it: >> >> I think it's a bug to log with level ERROR absolute valid SQL >> statement. You have error code returned to the client to handle it. If >> I want to log statements then I can turned that on. This is client >> appication debuggging and to error handling. > > The SQL command may be syntactically correct, but breaking a UNIQUE > constraint is an error condition and therefore it's handled like an error > condition - the user receives an exception and a message with ERROR level > is written to the log. > > If you don't want to see those messages in the log, there are ways to do > that correctly - e.g. by using database sequences. I personally think that > an application that generates significant amount of "duplicate values" > during normal operation is somehow broken. > > Anyway this definitely is not a bug, so if you want to discuss it further > (and how to fix you application), use pgsql-general mailing list. Still not convinced. Our application implements a distributed file system where name space metadata is stored in DB. It's normal that two or mode users trying to create a file with a same name in the same directory. The "duplicate values" are converted into 'file exists' error. I don't think you will be happy of /var/log/messages will be full of error messages when a user tries to create an existing file. Well, of course I can check myself the file exists, but why I need DB transactions if my application have to take care about that. Tigran. > > Tomas > > -- 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] BUG #6300: duplicate key value violates unique constraint
On Fri, Nov 18, 2011 at 14:31, Tigran Mkrtchyan wrote: > > On Fri, Nov 18, 2011 at 9:52 PM, Tomas Vondra wrote: > > Anyway this definitely is not a bug, > > Still not convinced. You are unlikely to convince anyone here otherwise... Its works that way by design. > [...] I don't think you will be happy of /var/log/messages will be > full of error messages when a user tries to create an existing file. > Well, of course I can check myself the file exists, but why I need DB > transactions if my application have to take care about that. If you don't want them logged you could 'catch' them by creating a wrapper function in plpsql, something like: http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE -- 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] BUG #6299: pg_dump, pg_dumpall - Problem with the order of backup functions
Thanks, in this case works :-) However, this does not solve all cases. Unlikely, but possible to create, cyclic case can not be restored: $ psql create database test; \connect test create function fn1(param1 int) returns int as $$ select $1 $$ language sql immutable; create function fn2(param1 int default fn1(8)) returns int as $$ select $1 $$ language sql immutable; create or replace function fn1(param1 int = fn2(3)) returns int as $$ select $1 $$ language sql immutable; \df List of functions Schema | Name | Result data type | Argument data types | Type +--+--+---+ public | fn1 | integer | param1 integer DEFAULT fn2(3) | normal public | fn2 | integer | param1 integer DEFAULT fn1(8) | normal (2 rows) \q $ pg_dump -F c test > test.backup pg_dump: [sorter] WARNING: could not resolve dependency loop among these items: pg_dump: [sorter] FUNCTION fn2 (ID 173 OID 16398) pg_dump: [sorter] FUNCTION fn1 (ID 174 OID 16397) $ psql -c "drop database test" $ psql -c "create database test" $ pg_restore -d test test.backup pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 173; 1255 16398 FUNCTION fn2(integer) postgres pg_restore: [archiver (db)] could not execute query: ERROR: function fn1(integer) does not exist LINE 1: CREATE FUNCTION fn2(param1 integer DEFAULT fn1(8)) RETURNS i... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Command was: CREATE FUNCTION fn2(param1 integer DEFAULT fn1(8)) RETURNS integer LANGUAGE sql IMMUTABLE AS $_$ select $1 $_$; pg_restore: [archiver (db)] could not execute query: ERROR: function public.fn2(integer) does not exist Command was: ALTER FUNCTION public.fn2(param1 integer) OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 174; 1255 16397 FUNCTION fn1(integer) postgres pg_restore: [archiver (db)] could not execute query: ERROR: function fn2(integer) does not exist LINE 1: CREATE FUNCTION fn1(param1 integer DEFAULT fn2(3)) RETURNS i... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Command was: CREATE FUNCTION fn1(param1 integer DEFAULT fn2(3)) RETURNS integer LANGUAGE sql IMMUTABLE AS $_$ select $1 $_$; pg_restore: [archiver (db)] could not execute query: ERROR: function public.fn1(integer) does not exist Command was: ALTER FUNCTION public.fn1(param1 integer) OWNER TO postgres; WARNING: errors ignored on restore: 4 $ psql test \df List of functions Schema | Name | Result data type | Argument data types | Type +--+--+-+-- (0 rows)
Re: [BUGS] BUG #6300: duplicate key value violates unique constraint
"Tomas Vondra" writes: > On 18 Listopad 2011, 20:38, Tigran Mkrtchyan wrote: >> I think it's a bug to log with level ERROR absolute valid SQL >> statement. But it's *not* valid ... it failed. > If you don't want to see those messages in the log, there are ways to do > that correctly - Has anybody suggested raising the log_min_messages setting? regards, tom lane -- 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] BUG #6301: extra space in psql variable expansion
On Sat, Nov 19, 2011 at 12:34 AM, Michael Smolsky > Now waiting for 9.2 package to ship for Ubuntu... Oh - 9.2 won't be released for a while; I tested on a recent git checkout of the main branch. It's possible that the next 9.1 point release (9.1.2) would contain this fix, but I haven't verified this (the way to check would be to do a git checkout of the 9_1_STABLE branch from git, and see if the fix is present there). Josh -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
