Re: [BUGS] Inconsistency between TO_CHAR() and TO_NUMBER()
On 11.05.2013 01:17, Euler Taveira wrote: On 10-05-2013 13:09, Thomas Kellerer wrote: Tom Lane wrote on 10.05.2013 17:49: I looked into this, and find that the reason it misbehaves is that NUM_numpart_from_char() will treat a '.' as being a decimal point *without any regard to locale considerations*. So even if we have a locale-dependent format string and a locale that says '.' is a thousands separator, it does the wrong thing. It's a bit surprising nobody's complained of this before. I propose the attached patch. I'm slightly worried though about whether this might break any existing applications that are (incorrectly) depending on a D format specifier being able to match '.' regardless of locale. Perhaps we should only apply this to HEAD and not back-patch? +1 only in HEAD. That's because (a) it doesn't crash, (b) it doesn't always produce the wrong answer (only in some specific situation) and (c) it has been like that for years without a complain. For those reasons, it is better to continue with this wrong behavior in back branches than prevent important security updates to be applied (without applying a patch to preserve the wrong answer). This argument is only valid for legacy closed-source apps but seems to have more weight than the bug scenario. +1 for HEAD-only. The Finnish language and locale uses comma (,) as the decimal separator, and it's a real pain in the ass. And if something goes wrong there, it can be *really* subtle. I once had to debug an application where all prices were suddenly rounded down to the nearest euro. And it only happened on some servers (those with locale set to Finnish). It was not a PostgreSQL application, but it turned out to be a bug in the JDBC driver of another DBMS. Would it be possible to be lenient, and also accept . as the decimal separator, when there is no ambiguity? Ie. when . is not the thousands separator. - Heikki -- 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 #8153: check constraint results in a lot of casts
The following bug has been logged on the website:
Bug reference: 8153
Logged by: Marc Mamin
Email address: m...@intershop.de
PostgreSQL version: 9.2.4
Operating system: Linux amp; windows
Description:
hello,
This is not a functional bugs, but it appers (at least for me...) that
following constraint gets defined with some unecessary overweight:
create table test (a varchar);
(1) alter table test add constraint test_ck check ( a in ('a','b'));
I'd expect this to be resolved in CHECK (a = ANY (ARRAY['a'::character
varying, 'b'::character varying]) ).
I've tried further variantes. (3) looks better but there is always a cast on
the table side.
(2) alter table test add constraint test_ck_2 check ( a = ANY
(ARRAY['a'::character varying, 'b'::character varying] ));
(3) alter table test add constraint test_ck_3 check ( a = ANY
(ARRAY['a'::text, 'b'] ));
\d+ test
Table public.test
Column | Type| Modifiers | Storage | Stats target |
Description
+---+---+--+--+-
a | character varying | | extended | |
Check constraints:
test_ck CHECK (a::text = ANY (ARRAY['a'::character varying,
'b'::character varying]::text[]))
test_ck_2 CHECK (a::text = ANY (ARRAY['a'::character varying,
'b'::character varying]::text[]))
test_ck_3 CHECK (a::text = ANY (ARRAY['a'::text, 'b'::text]))
best regards,
Marc Mamin
--
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 #8154: pg_dump throws error beacause of field called new.
The following bug has been logged on the website: Bug reference: 8154 Logged by: Willy-Bas Loos Email address: willy...@gmail.com PostgreSQL version: 9.1.9 Operating system: ubuntu 12.04 server Description: pg_dump (PostgreSQL) 9.1.9 ends in an error when backing up my db, which contains a field called new. Here's the raised message: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: syntax error at or near new LINE 1: ...ag, beginmaand, einddag, eindmaand, fusieafstand, new, opmer... ^ pg_dump: The command was: COPY lsb.zsoorten (id, euringchar, begindag, beginmaand, einddag, eindmaand, fusieafstand, new, opmerkingen, euring) TO stdout; -- 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] Inconsistency between TO_CHAR() and TO_NUMBER()
Heikki Linnakangas hlinnakan...@vmware.com writes: Would it be possible to be lenient, and also accept . as the decimal separator, when there is no ambiguity? Ie. when . is not the thousands separator. I originally coded it that way, but concluded that it was probably a waste of code space. How many locales can you point to where neither the decimal point nor thousands_sep is .? It certainly wouldn't be enough to noticeably reduce the potential pain from this change, so I decided that it'd be better to keep the behavior straightforward and as-documented. 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 #8154: pg_dump throws error beacause of field called new.
willy...@gmail.com writes:
pg_dump (PostgreSQL) 9.1.9 ends in an error when backing up my db, which
contains a field called new.
Works for me:
regression=# create table new (f1 text, new text);
CREATE TABLE
regression=# insert into new (f1, new) values ('1','2');
INSERT 0 1
regression=# copy new(f1,new) to stdout;
1 2
You sure the server is 9.1?
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] Inconsistency between TO_CHAR() and TO_NUMBER()
On 13.05.2013 17:09, Tom Lane wrote: Heikki Linnakangashlinnakan...@vmware.com writes: Would it be possible to be lenient, and also accept . as the decimal separator, when there is no ambiguity? Ie. when . is not the thousands separator. I originally coded it that way, but concluded that it was probably a waste of code space. How many locales can you point to where neither the decimal point nor thousands_sep is .? On my laptop, there are eight locales that use , as the decimal separator and as the thousands separator. $ grep -l ^thousands_sep.*U00A0 /usr/share/i18n/locales/* | xargs grep -l ^decimal_point.*U002C /usr/share/i18n/locales/cs_CZ /usr/share/i18n/locales/et_EE /usr/share/i18n/locales/fi_FI /usr/share/i18n/locales/lv_LV /usr/share/i18n/locales/nb_NO /usr/share/i18n/locales/ru_RU /usr/share/i18n/locales/sk_SK /usr/share/i18n/locales/uk_UA Out of these, ru_RU actually uses . as the LC_MONETARY decimal point, even though it uses , as the LC_NUMERIC decimal point. I think that strengthens the argument for accepting both. I don't speak Russian, but if you pass a monetary value to TO_NUMBER in ru_RU locale, using . as the decimal separator, you probably would expect it to work. According to http://en.wikipedia.org/wiki/Decimal_separator#Examples_of_use, many countries accept either 1 234 567,89 or 1.234.567,89 style, but looking at the locale files installed on my system, the latter style is the one actually used (e.g Germany). - Heikki -- 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 #8154: pg_dump throws error beacause of field called new.
oh, silly me.
I was making a dump of a PostgreSQL 8.4.17 backend with pg_dump
(PostgreSQL) 9.1.9
On Mon, May 13, 2013 at 4:24 PM, Tom Lane t...@sss.pgh.pa.us wrote:
willy...@gmail.com writes:
pg_dump (PostgreSQL) 9.1.9 ends in an error when backing up my db, which
contains a field called new.
Works for me:
regression=# create table new (f1 text, new text);
CREATE TABLE
regression=# insert into new (f1, new) values ('1','2');
INSERT 0 1
regression=# copy new(f1,new) to stdout;
1 2
You sure the server is 9.1?
regards, tom lane
--
Quality comes from focus and clarity of purpose -- Mark Shuttleworth
[BUGS] BUG #8151: client libraries not working on mingw-w64 gcc 4.8
The following bug has been logged on the website: Bug reference: 8151 Logged by: Philip A Reimer Email address: antrei...@gmail.com PostgreSQL version: 9.2.4 Operating system: mingw-w64 Description: I'm getting this error when running psql compiled with gcc 4.8 but don't when it is compiled with gcc 4.7. To compile with gcc 4.7 I used http://hivelocity.dl.sourceforge.net/project/mingw-w64/Toolchains%20targetting%20Win32/Personal%20Builds/rubenvb/gcc-4.7-release/i686-w64-mingw32-gcc-4.7.4-release-linux64_rubenvb.tar.xz and to compile with gcc 4.8 I used both http://hivelocity.dl.sourceforge.net/project/mingw-w64/Toolchains%20targetting%20Win32/Personal%20Builds/rubenvb/gcc-4.8-release/i686-w64-mingw32-gcc-4.8.0-linux64_rubenvb.tar.xz and https://aur.archlinux.org/packages/mingw-w64-gcc/. I compiled on both windows 7 and ArchLinux with the same results. psql --host=192.168.40.240 psql: could not connect to server: Operation would block (0x2733/10035) Is the server running on host 192.168.40.240 and accepting TCP/IP connections on port 5432? To compile on ArchLinux I used this script. Postgresql was extracted to mingw-dir\src. The script was also placed in mingw-dir\src pkgver=9.2.4 builddir=$(pwd)/postgresql-9.2.4-build export PATH=$(pwd)/../bin:$PATH export PATH=$(pwd)/../i686-w64-mingw32/bin:$PATH cd postgresql-$pkgver ./configure --host=i686-w64-mingw32 --prefix=$builddir/ --without-zlib for dir in src/interfaces src/bin/pg_config src/bin/psql; do make -C $dir install done strip -x $builddir/lib/*.dll strip -g $builddir/lib/*.dll strip -g $builddir/lib/*.a -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Odd Behavior After Multiple Deletes
Dear Sirs, We are observing an odd phenomena with PostgresSQL, which is no doubt due to our using the database improperly. That database in question is PostgresSQL Version 8.7.1 and runs on a machine installed with Server 2003. The DELETE commands were being issued both from a Windows XP using PostgresSQL Version 9.1 and at times, from the server itself. 1. We have a table that receives a load of data daily from an older system. Every night, we delete some of the rows on the table using a DELETE command from PgAdminIII, perform a VACUUM Full on that table, and then reload the data using a batch file that contains a series of INSERTS. We run the batch file from the command line prompt. The table seems fine afterwards, and the data is correct. We then we turn off the system for the night. 2.First thing next morning, we look at the data and verify that it is the same as it was the night before. Suddenly, as the day goes by, the older, deleted data appears to bleed back into the table, creating duplicate rows. Users are entering new data to that table during the day, as well as looking at the data we loaded the night before, but none of the duplicate rows are coming from user input, only the system itself. What are we doing wrong? We have not tried dropping the table and starting over, as that would entail reloading much older data. I think that you will probably say, Get your versions in sync right away! It´s just that the duplication of data occurs only in this one file where all the deletes are issued every night. Sincerely, Chaya Gilburt
[BUGS] BUG #8152: strange behavior regarding after triggers and inheritance
The following bug has been logged on the website: Bug reference: 8152 Logged by: Hugo J. Curti Email address: hcu...@exa.unicen.edu.ar PostgreSQL version: 8.4.17 Operating system: Debian GNU/Linux 6.0.7 (squeeze) Description: I don't know if this is really a bug, but it is at least a strange / undocumented behavior. After statments triggers on child tables are sometimes executed and sometimes are not. As I far as I could see, when the after trigger is defined in only one of the tables it may not get executed, wether when it is defined in every inherited table it does, but which one is undetermined. this is a simple example: CREATE FUNCTION test() returns trigger as $$ BEGIN RAISE NOTICE 'Trigger executed' ; RETURN NULL ; END $$ LANGUAGE 'plpgsql' ; CREATE FUNCTION CREATE TABLE a ( a integer ) ; CREATE TABLE CREATE TABLE b ( b integer ) INHERITS( a ) ; CREATE TABLE CREATE TRIGGER ta AFTER UPDATE ON a FOR EACH STATEMENT EXECUTE PROCEDURE test() ; CREATE TRIGGER CREATE TRIGGER tb AFTER UPDATE ON b FOR EACH STATEMENT EXECUTE PROCEDURE test() ; CREATE TRIGGER INSERT INTO b VALUES( 1 , 2 ) ; INSERT 0 1 EXPLAIN ANALYZE update a set a=1 ; NOTICE: Trigger executed QUERY PLAN Append (cost=0.00..65.40 rows=4540 width=8) (actual time=0.035..0.040 rows=1 loops=1) - Seq Scan on a (cost=0.00..34.00 rows=2400 width=6) (actual time=0.005..0.005 rows=0 loops=1) - Seq Scan on b a (cost=0.00..31.40 rows=2140 width=10) (actual time=0.025..0.028 rows=1 loops=1) Trigger tb on b: time=0.869 calls=1 Total runtime: 1.117 ms (5 filas) -- Here trigger tb gets executed. That -- might be correct, but since it is a -- an 'AFTER STATEMENT' trigger I think, -- ta would be a better candidate... -- The strange behavior starts here: CREATE TABLE c ( c integer ) INHERITS( a ) ; CREATE TABLE EXPLAIN ANALYZE update a set a=1 ; QUERY PLAN Append (cost=0.00..96.80 rows=6680 width=9) (actual time=0.021..0.028 rows=1 loops=1) - Seq Scan on a (cost=0.00..34.00 rows=2400 width=6) (actual time=0.005..0.005 rows=0 loops=1) - Seq Scan on b a (cost=0.00..31.40 rows=2140 width=10) (actual time=0.013..0.016 rows=1 loops=1) - Seq Scan on c a (cost=0.00..31.40 rows=2140 width=10) (actual time=0.001..0.001 rows=0 loops=1) Total runtime: 0.188 ms (5 filas) -- The trigger does not get executed -- any more! -- Now, adding the trigger to table c: CREATE TRIGGER tc AFTER UPDATE ON c FOR EACH STATEMENT EXECUTE PROCEDURE test() ; CREATE TRIGGER EXPLAIN ANALYZE update a set a=1 ; NOTICE: Trigger executed QUERY PLAN Append (cost=0.00..96.80 rows=6680 width=9) (actual time=0.041..0.050 rows=1 loops=1) - Seq Scan on a (cost=0.00..34.00 rows=2400 width=6) (actual time=0.004..0.004 rows=0 loops=1) - Seq Scan on b a (cost=0.00..31.40 rows=2140 width=10) (actual time=0.032..0.036 rows=1 loops=1) - Seq Scan on c a (cost=0.00..31.40 rows=2140 width=10) (actual time=0.001..0.001 rows=0 loops=1) Trigger tc on c: time=1.002 calls=1 Total runtime: 1.314 ms (6 filas) -- Now the trigger tc gets executed. -- This is strange. I might expect ta -- because it is an 'AFTER STATEMENT' -- trigger, or tb because the affected -- rows are on table b, but why tc? The workarround I found is to define the after statement trigger in EVERY child table. I hope this helps. Regards, Hugo J. Curti -- 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] Odd Behavior After Multiple Deletes
On 5/12/2013 9:10 AM, Chaya Gilburt wrote: PostgresSQL Version 8.7.1 hopefully, you mean 8.1.7 ? there never was a 8.7 release, it went up to 8.4 then rolled over to 9.0 -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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 #8152: strange behavior regarding after triggers and inheritance
hcu...@exa.unicen.edu.ar writes: PostgreSQL version: 8.4.17 After statments triggers on child tables are sometimes executed and sometimes are not. As I far as I could see, when the after trigger is defined in only one of the tables it may not get executed, wether when it is defined in every inherited table it does, but which one is undetermined. This test case appears to work the way you're expecting in 9.0 and later. I didn't immediately find a mention of such a change in the commit logs; perhaps it got fixed as a side-effect of the changes that moved trigger processing into ModifyTable plan nodes. Anyway, I doubt we'd consider changing trigger behavior in 8.4.x at this late date. You should update to a newer release series if this is a problem for you. 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
[BUGS] BUG #8156: PostGIS crash with immutable functions when immutable function throws an error
The following bug has been logged on the website:
Bug reference: 8156
Logged by: Regina
Email address: l...@pcorp.us
PostgreSQL version: 9.2.4
Operating system: Windows 7 64-bit compiled with visual c++ - EDB 64
Description:
Nothing to do yet unless its obvious to you folks what is wrong here.
I haven't determined if its an issue in how we are compiling PostGIS for
windows or something fundametally wrong in the 9.2 branch on how it handles
windows 64-bit.
Details in this ticket:
http://trac.osgeo.org/postgis/ticket/2185
The issue only seems to exhibit itself in PostgreSQL 9.2.2-9.2.4 (as I
recall 9.2.1 doesn't have this issue).
and it also only happens on windows 7-64bit and windows 2008 64-bit. As far
as I can tell windows 2003 64-bit with same build doesn't have the issue and
as I recall I can't replicate this issue testing under mingw64 either which
we use to compile.
We've only seen it with SQL functions that wrap a PostGIS c function and
that are marked IMMUTABLE STRICT and happens when fed invalid inputs that
would raise an error in the C function. If we take out the IMMUTABLE part
it works fine.
e.g.
This function will crash when used with invalid inputs. such as
ST_AsText('POINT(1 3 hi)')
CREATE OR REPLACE FUNCTION st_astext(text)
RETURNS text AS
' SELECT ST_AsText($1::geometry); '
LANGUAGE sql IMMUTABLE STRICT
COST 100;
This variant the function (note no immutable)
CREATE OR REPLACE FUNCTION st_astextNotImmut(text)
RETURNS text AS
' SELECT ST_AsText($1::geometry); '
LANGUAGE sql STRICT
COST 100;
Makes it not crash.
The 9.3beta1 doesn't have this issue. Nor does the latest 9.1 I have
tested.
--
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] Odd Behavior After Multiple Deletes
On Sunday, May 12, 2013 9:40 PM Chaya Gilburt wrote: Dear Sirs, We are observing an odd phenomena with PostgresSQL, which is no doubt due to our using the database improperly. That database in question is PostgresSQL Version 8.7.1 and runs on a machine installed with Server 2003. The DELETE commands were being issued both from a Windows XP using PostgresSQL Version 9.1 and at times, from the server itself. 1. We have a table that receives a load of data daily from an older system. Every night, we delete some of the rows on the table using a DELETE command from PgAdminIII, perform a VACUUM Full on that table, and then reload the data using a batch file that contains a series of INSERTS. We run the batch file from the command line prompt. The table seems fine afterwards, and the data is correct. We then we turn off the system for the night. 2. First thing next morning, we look at the data and verify that it is the same as it was the night before. Suddenly, as the day goes by, the older, deleted data appears to bleed back into the table, creating duplicate rows. Users are entering new data to that table during the day, as well as looking at the data we loaded the night before, but none of the duplicate rows are coming from user input, only the system itself. What are we doing wrong? The system itself cannot insert rows into your tables. I could think of below 2 reasons for seeing extra rows: 1. Delete has not happened appropriately 2. There are triggers defined on tables which could insert the extra rows you are seeing. Is the problem you described happen more than once? Could you form testcase which can show such behavior? With Regards, Amit Kapila. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
