[BUGS] BUG #8175: Check constraint fails for valid data. ( rounding related? )
The following bug has been logged on the website: Bug reference: 8175 Logged by: Dan Libby Email address: dan.li...@gmail.com PostgreSQL version: 9.1.6 Operating system: Linux ( Ubuntu 12.04 ) Description: -- Try this script -- create table test1 ( val1 numeric(23,8), val2 numeric(23,8), product numeric(23,8) check( product = val1 * val2 ) ); select (2.23567567*3.7000)::numeric(23,8); insert into test1 values ( 3.7000, 2.23567567, 8.2718 ); insert into test1 values ( 3.7000, 2.23567567, 2.23567567*3.7000 ); insert into test1 values ( 3.7000, 2.23567567, (2.23567567*3.7000)::numeric(23,8) ); -- Actual Results -- select (2.23567567*3.7000)::numeric(23,8); numeric | 8.2718 btcx=# insert into test1 values ( 3.7000, 2.23567567, 8.2718 ); ERROR: new row for relation test1 violates check constraint test1_check btcx=# insert into test1 values ( 3.7000, 2.23567567, 2.23567567*3.7000 ); ERROR: new row for relation test1 violates check constraint test1_check btcx=# insert into test1 values ( 3.7000, 2.23567567, (2.23567567*3.7000)::numeric(23,8) ); ERROR: new row for relation test1 violates check constraint test1_check -- Expected Results -- All values should be inserted successfully. -- 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 #8173: Inserting heap tuples in bulk in COPY patch return wrong line on failure 999 out of 1000 times.
The following bug has been logged on the website: Bug reference: 8173 Logged by: Lloyd Albin Email address: lal...@fhcrc.org PostgreSQL version: 9.2.4 Operating system: SUSE Linux (64-bit) Description: During testing for our 9.2 upgrade, we found that the error messages we were expecting did not match what was given by the program. In looking over the revision notes from our current version of 9.0.12 through the 9.2.4, that we are testing, I believe that I have tracked down the issue to Improve COPY performance by adding tuples to the heap in batches. When I looked at the patch code in http://www.postgresql.org/message-id/4e708759.40...@enterprisedb.com I found that you are inserting 1000 rows at a time. The problem is that on failure, you return either row 1000 or the last line, whichever comes first. This can be confusing as you will see in the demo code below. CREATE TABLE public.table1 ( key INTEGER, PRIMARY KEY(key) ); Create a csv file with only one column of data, numbered from 1 to 1008. Make two copies of the file and name them csv_test.csv and csv_test2.csv. Edit csv_test.csv and change the entry 1000 to 500. Edit csv_test2.csv and change the entry 900 to 500. On 9.0.12 Server COPY public.table1 FROM 'csv_test.csv'; ERROR: duplicate key value violates unique constraint table1_pkey DETAIL: Key (key)=(500) already exists. CONTEXT: COPY table1, line 1000: 500 COPY public.table1 FROM 'csv_test2.csv'; ERROR: duplicate key value violates unique constraint table1_pkey DETAIL: Key (key)=(500) already exists. CONTEXT: COPY table1, line 900: 500 Both times the context gave us the correct information. Now try the same thing on 9.2.4 Server COPY public.table1 FROM 'csv_test.csv'; ERROR: duplicate key value violates unique constraint table1_pkey DETAIL: Key (key)=(500) already exists. CONTEXT: COPY table1, line 1000: 500 COPY public.table1 FROM 'csv_test2.csv'; ERROR: duplicate key value violates unique constraint table1_pkey DETAIL: Key (key)=(500) already exists. CONTEXT: COPY table1, line 1000: 1000 As you can see, the second test returned the last line of the set of tuples being recorded not the line that actually failed. Make a copy of csv_test2.csv and name it csv_test3.csv. Edit csv_test3.csv and remove all entries after 994. COPY public.table1 FROM 'csv_test3.csv'; ERROR: duplicate key value violates unique constraint table1_pkey DETAIL: Key (key)=(500) already exists. CONTEXT: COPY table1, line 995: If you are writing less than 1000 lines then it will return the line after the last line with a value of . Lloyd Albin Statistical Center for HIV/AIDS Research and Prevention (SCHARP) Vaccine and Infectious Disease Division (VIDD) Fred Hutchinson Cancer Research Center (FHCRC) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] pass to install
i can´t install any version of postgres because it requires a password which i dont know, i just want to install it, what can i do?
Re: [BUGS] BUG #8175: Check constraint fails for valid data. ( rounding related? )
dan.li...@gmail.com writes: create table test1 ( val1 numeric(23,8), val2 numeric(23,8), product numeric(23,8) check( product = val1 * val2 ) ); select (2.23567567*3.7000)::numeric(23,8); insert into test1 values ( 3.7000, 2.23567567, 8.2718 ); insert into test1 values ( 3.7000, 2.23567567, 2.23567567*3.7000 ); insert into test1 values ( 3.7000, 2.23567567, (2.23567567*3.7000)::numeric(23,8) ); It's not surprising that these all fail. You'd need to make the check be more like this: check( product = (val1 * val2)::numeric(23,8) ) Otherwise, the check will always fail when the product has more than 8 fractional digits. It's not Postgres' place to decide that that wasn't what you wanted to happen. 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] pass to install
On 05/22/2013 07:50 PM, juancho gonzila jorrel wrote: i can´t install any version of postgres because it requires a password which i dont know, i just want to install it, what can i do? You probably have an existing postgres account. Try changing the password of that account to something that you know, and then using that password for the installer. -- regards, tushar
Re: [BUGS] pass to install
On 05/22/2013 07:50 PM, juancho gonzila jorrel wrote: i can´t install any version of postgres because it requires a password which i dont know, i just want to install it, what can i do? You probably have an existing postgres account. Try changing the password of that account to something that you know, and then using that password for the installer. regards,tushar
Re: [BUGS] pass to install
On 5/22/2013 7:36 AM, tushar wrote: On 05/22/2013 07:50 PM, juancho gonzila jorrel wrote: i can´t install any version of postgres because it requires a password which i dont know, i just want to install it, what can i do? You probably have an existing postgres account. Try changing the password of that account to something that you know, and then using that password for the installer. assuming this is MS Windows (as thats really the only place this shows up), service accounts like 'postgres' don't show up in the Control Panel - Users dumbed down UI. Instead, you need to drill into Administrative Tools - Computer Management - Local Users and Groups - Users THERE you should see 'postgres', and if you right click on it, and chose 'Change Password...' you can set this password. The password for the postgres system service account is ONLY used in the Services manager entry for the postgres server, so if you change it in Local Users when you have an existing postgresql server install, then you have to find the postgresql service in the Services manager, and change the password the service uses to start there. -- john r pierce 37N 122W somewhere on the middle of the left coast
