Re: [BUGS] BUG #5971: NpgSql link doesnt work
On Mon, Apr 11, 2011 at 9:01 AM, John Taylor wrote: > > The following bug has been logged online: > > Bug reference: 5971 > Logged by: John Taylor > Email address: kakaoscsig...@gmail.com > PostgreSQL version: 9.0 > Operating system: Windows 7 > Description: NpgSql link doesnt work > Details: > > Hi! > > http://npgsql.projects.postgresql.org/ link doesn't work. Please repair it, > I'd like to download connector for .net . Thanks a lot. It works fine for me. Intermittent network issue perhaps? Or maybe something blocking your access? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 #5971: NpgSql link doesnt work
On Tue, Apr 12, 2011 at 09:29, Dave Page wrote: > On Mon, Apr 11, 2011 at 9:01 AM, John Taylor wrote: >> >> The following bug has been logged online: >> >> Bug reference: 5971 >> Logged by: John Taylor >> Email address: kakaoscsig...@gmail.com >> PostgreSQL version: 9.0 >> Operating system: Windows 7 >> Description: NpgSql link doesnt work >> Details: >> >> Hi! >> >> http://npgsql.projects.postgresql.org/ link doesn't work. Please repair it, >> I'd like to download connector for .net . Thanks a lot. > > It works fine for me. Intermittent network issue perhaps? Or maybe > something blocking your access? Given the timestamp of the email, I would guess this was sent during the big hub.org outage yesterday, at which point all things pgfoundry were offline. So I think it's safe to saythat things have been fixed now. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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 #5973: erreur SERIAL 4
The following bug has been logged online:
Bug reference: 5973
Logged by:
Email address: poiretl...@gmail.com
PostgreSQL version: 9.0.3
Operating system: windows 7
Description:erreur SERIAL 4
Details:
Hello,
I use PostgreSQL PgAdmin 9 and III. I am running Windows 7.
I want a scritp differential generated using the software Aqua Data Studio
9.0.13 (demo for now). I therefore generates a script that I want via Aqua
tilted in pgadmin III. However, I have a problem with the SERIAL 4.
the basic script:
CREATE TABLE USR_UTILISATEUR_UTI (
UTI_ID Serial4 PRIMARY KEY);
Aqua Data Studio, it gives me:
CREATE TABLE public.usr_utilisateur_uti (
uti_id int4 NOT NULL DEFAULT nextval ('usr_utilisateur_uti_uti_id_seq'::
regclass);
except that pgadmin III does not want ... I'm stuck ..
Please, help me ...
Lise
--
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 #5972: Update with subquery: erroneous results for foreign key field
The following bug has been logged online: Bug reference: 5972 Logged by: Paul Email address: paul.co...@punct.ro PostgreSQL version: 9.0.3 Operating system: CentOS 5.5 Description:Update with subquery: erroneous results for foreign key field Details: Hello. We found what we think is a bug while running an update with a subquery in the condition. The relevant database layout is as follows: CREATE TABLE users ( id integer serial PRIMARY KEY, username character varying(32), email character varying(200), password character varying(32), status smallint DEFAULT 1 NOT NULL, rdate timestamp without time zone DEFAULT now() NOT NULL, last_action timestamp without time zone DEFAULT now() NOT NULL, ); CREATE TABLE cart ( id integer serial PRIMARY KEY, userid integer, dt timestamp without time zone DEFAULT now(), status integer DEFAULT 0, optional_firstname character varying(100), optional_lastname character varying(100), optional_email character varying(254) ); ALTER TABLE ONLY cart ADD CONSTRAINT cart_userid_fkey FOREIGN KEY (userid) REFERENCES users(id); We issued the following query, directly through psql: update cart set status = 1 where userid = (select userid from users where email = 'exam...@example.com'); As you can see, the subquery is broken (users table doesn't have the column userid). We missed that when we ran it, and we were stunned to see that the query updated 1573 rows, when we expected it to updated only 1 (even though this one should have failed). We further investigated the problem, and were able to replicate it on other databases as well. It seems that the folowing query has the same result: update cart set status = 1 where userid = (select userid); So it seems that PostgreSQL uses the foreign key as some kind of "shortcut", even though the following query fails (more than one row returned by a subquery) update cart set status = 1 where userid=(select cart.userid from cart, users where cart.userid = users.id); We are still not sure if this is a bug or the desired behaviour, but it seems strange (because the subquery, issued separately, fails). Thank you very much, Paul -- 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 #5972: Update with subquery: erroneous results for foreign key field
"Paul" wrote: > update cart set status = 1 where userid = (select userid from > users where email = 'exam...@example.com'); > > As you can see, the subquery is broken (users table doesn't have > the column userid). By standard, if the identifier isn't defined within the most local scope, each enclosing scope, from the inside out, will be checked. I would expect the above to update each row where cart.userid was not null. I always use and recommend aliases where practical. If you wrote it this way, such a mistake would be clearly identified: update cart set status = 1 where userid = (select u.userid from users u where u.email = 'exam...@example.com'); Not a bug. -Kevin -- 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 #5972: Update with subquery: erroneous results for foreign key field
Thank you very much, i understand now :) Paul On Tue, Apr 12, 2011 at 4:42 PM, Kevin Grittner wrote: > "Paul" wrote: > > > update cart set status = 1 where userid = (select userid from > > users where email = 'exam...@example.com'); > > > > As you can see, the subquery is broken (users table doesn't have > > the column userid). > > By standard, if the identifier isn't defined within the most local > scope, each enclosing scope, from the inside out, will be checked. > I would expect the above to update each row where cart.userid was > not null. > > I always use and recommend aliases where practical. If you wrote it > this way, such a mistake would be clearly identified: > > update cart set status = 1 where userid = (select u.userid from > users u where u.email = 'exam...@example.com'); > > Not a bug. > > -Kevin >
Re: [BUGS] [GENERAL] PostgreSQL backend process high memory usage issue
On Tue, Apr 12, 2011 at 12:48 PM, Shianmiin wrote: > > Merlin Moncure-2 wrote: >> >> >> I am not seeing your results. I was able to run your test on a stock >> config (cut down to 50 schemas though) on a vm with 512mb of memory. >> What is your shared buffers set to? >> >> > > The shared buffers was set to 32MB as in default postgresql.conf > > To save you some time and make the conversion moves faster, I re-do a serial > of tests on a VM with 512MB of memory so that we have a common base. Here is > the test results and observations: > > 1. Setup: VM with 512MB of memory, CentOS 5.5 Final, PostgreSQL 9.0.3, a > fresh db cluster with everything default in all config files. For your > reference, I uploaded my postgresql.conf and top output here > http://postgresql.1045698.n5.nabble.com/file/n4298807/postgresql.conf > postgresql.conf > http://postgresql.1045698.n5.nabble.com/file/n4298807/01_Top_-_VM_with_512MB.gif > 01_Top_-_VM_with_512MB.gif > > 2. using psql to connect to the instance, here is the top output > http://postgresql.1045698.n5.nabble.com/file/n4298807/02_Top_-_VM_with_512MB_-_fresh_connection_.gif > 02_Top_-_VM_with_512MB_-_fresh_connection_.gif > > 3. The follow tests is on a db that initialized with 50 schemas, each with > 50 tables/views > > 3a. single thread test with command "pgbench memoryusagetest -c 1 -j 1 -T > 6000 -f test.sql" > http://postgresql.1045698.n5.nabble.com/file/n4298807/03_Top_-_VM_with_512MB_-_single_thread_test.gif > 03_Top_-_VM_with_512MB_-_single_thread_test.gif > > observations: > (backend process 1) VIRT 478 MB, RES 401 MB SHR 32 MB. > 0% waiting > ==> The single backend process pretty much use up all the physical memory > and maybe some swap spaces. > ==> In the original test, 100 schemas with 100 tables/views per schema, the > process use 1.5 GB > In this test, 50 schemas with 50 tables/views per schema, the process > use 1.5 / 4 = 478 MB. > the memory used is somehow proportional to the number of objects in > the database. > > 3b. two threads test with command "pgbench memoryusagetest -c 2 -j 2 -T > 6000 -f test.sql" > http://postgresql.1045698.n5.nabble.com/file/n4298807/04_Top_-_VM_with_512MB_-_two_threads_test.gif > 04_Top_-_VM_with_512MB_-_two_threads_test.gif > > observations: > (backend process 1) VIRT 476 MB, RES 320 MB SHR 9724 KB. > (backend process 2) VIRT 478 MB, RES 82 MB SHR 6308 KB. > 37.4%waiting > ==> the physically memory were all used up by the two backend processes, > plus 775 MB swap space used. The virtual (physical + swap) of each process > is the same as in the single thread test, i.e. ~ 470MB > ==> please note that there is considerable %waiting here and kswapd0 > starting to work a little hard > > 3c. three threads test with command "pgbench memoryusagetest -c 3 -j 3 -T > 6000 -f test.sql" > http://postgresql.1045698.n5.nabble.com/file/n4298807/05_Top_-_VM_with_512MB_-_three_threads_test.gif > 05_Top_-_VM_with_512MB_-_three_threads_test.gif > > observations: > (backend process 1) VIRT 468 MB, RES 299 MB SHR 18 MB. > (backend process 2) VIRT 418 MB, RES 61 MB SHR 13 MB. > (backend process 3) VIRT 421 MB, RES 61 MB SHR 13 MB. > 42.8%waiting > ==> all physical memory is used and more swap spaces are used, I didn't let > it run long enough to see if the VIRT all go up to 470 MB since when > swapping is considerable, the tests slows down and the VIRT growth slows > down too. The VIRT still in the same range, i.e. 400-ish MB. > ==> the % waiting gets higher and kswapd0 work harder and the tests run > slower > > 3d. four threads test with command "pgbench memoryusagetest -c 4 -j 4 -T > 6000 -f test.sql" > http://postgresql.1045698.n5.nabble.com/file/n4298807/06_Top_-_VM_with_512MB_-_four_threads_test.gif > 06_Top_-_VM_with_512MB_-_four_threads_test.gif > observations: > > Observations: > (backend process 1) VIRT 424 MB, RES 196 MB SHR 21 MB. > (backend process 2) VIRT 416 MB, RES 83 MB SHR 15 MB. > (backend process 3) VIRT 418 MB, RES 86 MB SHR 16 MB. > (backend process 4) VIRT 466 MB, RES 66 MB SHR 16 MB. > 47.8%waiting > ==> all physical memory is used and more swap spaces are used, I didn't let > it run long enough to see if the VIRT all go up to 470 MB since when > swapping is considerable, the tests slows down and the VIRT growth slows > down too. The VIRT still in the same range, i.e. 400-ish MB. > ==> the % waiting gets higher and kswapd0 work even harder and the tests run > even slower > > 3e. A crash test: 60 threads test with command "pgbench memoryusagetest > -c 60 -j 60 -T 6000 -f test.sql" > http://postgresql.1045698.n5.nabble.com/file/n4298807/07_Top_-_VM_with_512MB_-_60_threads_crashing_test.gif > 07_Top_-_VM_with_512MB_-_60_threads_crashing_test.gif > Observations: > (backend process 1) VIRT 148 MB, RES 14 MB SHR 7852 KB. > (backend process 2) VIRT 149 MB, RES 13 MB SHR 7636 KB. > ... > 63.9%waiting > ==> as expected all physical memory is used and here swap space is used u
Re: [BUGS] BUG #5970: intersect and collation on types
"Rikard Pavelic" writes: > The following bug has been logged online: > Bug reference: 5970 > Logged by: Rikard Pavelic > Email address: rikard.pave...@zg.htnet.hr > PostgreSQL version: 9.1.alpha5 > Operating system: Windows XP SP3 > Description:intersect and collation on types > Details: > create table test_collation (id int, name varchar); > insert into test_collation values (1, 'abc'), (2,'bcd'); > --works > select * from test_collation tc > intersect > select * from test_collation tc; > --doesn't work > select tc from test_collation tc > intersect > select tc from test_collation tc; Fixed in HEAD. Thanks for the report! 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 #5973: erreur SERIAL 4
On 12/04/2011 8:57 PM, poiretl...@gmail.com wrote: except that pgadmin III does not want ... I'm stuck .. Please show the exact text of any error messages. Read: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- 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 #5976: Corrupted pages on the production database
The following bug has been logged online: Bug reference: 5976 Logged by: Vlad Arkhipov Email address: arhi...@dc.baikal.ru PostgreSQL version: 9.0.3 Operating system: Red Hat 4.1.2-48, 64-bit Description:Corrupted pages on the production database Details: I have recently encounter a problem with the production database. Eventually pg_dump could not be done. pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not access status of transaction 3499806839 DETAIL: Could not open file "pg_clog/0D09": No such file or directory. pg_dump: The command was: COPY rolap.agg_lab_invests_registration_month (service, invest, sampling_place, material, status, rejection_reason, registration_month, container_type, device, registration_period_1, execution_period_1, authorization_period_1, overdue_period_1, completion_period_1, fact_count, quantity, profile_count, service_count, container_count, reg_period, exec_period, auth_period, over_period, comp_period) TO stdout; A simple SELECT * FROM rolap.agg_lab_invests_registration_month raises the same error. There is also a stand-by server (streaming replication) which does not have this error. I've elaborated on this issue a bit and found 2 corrupted pages in the file of relation. The first one is semi-corrupted, one half of it is correct, but another part contains data from another database table. The second one is completely corrupted and contains data from the same table as the corrupted part of the first page. -- 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] [GENERAL] PostgreSQL backend process high memory usage issue
Merlin Moncure writes: > I think you may have uncovered a leak (I stand corrected). > The number of schemas in your test is irrelevant -- the leak is > happening in proportion to the number of views (set via \setrandom > tidx 1 10). At 1 I don't think it exists at all -- at 100 memory use > grows very fast. I don't think it's a leak, exactly: it's just that the "relcache" entry for each one of these views occupies about 100K. A backend that touches N of the views is going to need about N*100K in relcache space. I can't get terribly excited about that. Trying to reduce the size of the relcache would be a net loss for most usage patterns (ie, we'd end up increasing the amount of re-fetching from the system catalogs that backends would have to do). And I don't think that this test case has much of anything to do with sane application design, anyway. Do you really need that many complex views? Do you really need to have most sessions touching all of them? 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 #5976: Corrupted pages on the production database
On 13.04.2011 06:52, Vlad Arkhipov wrote: There is also a stand-by server (streaming replication) which does not have this error. I've elaborated on this issue a bit and found 2 corrupted pages in the file of relation. The first one is semi-corrupted, one half of it is correct, but another part contains data from another database table. The second one is completely corrupted and contains data from the same table as the corrupted part of the first page. Sounds like broken hardware.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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 #5974: UNION construct type cast gives poor error message
The following bug has been logged online: Bug reference: 5974 Logged by: Jeff Wu Email address: j...@atlassian.com PostgreSQL version: 9.0 Operating system: Mac OS X Description:UNION construct type cast gives poor error message Details: The UNION construct (as noted on this page: http://www.postgresql.org/docs/9.0/static/typeconv-union-case.html) will cast unknown types to TEXT, however, if you try to do three or more UNIONs the order in which the UNIONs are executed will cause some columns to be cast to TEXT prematurely. The result is a type mismatch error. For example: SELECT 1,null,null UNION SELECT 2,3,null UNION SELECT 3,null,4 will fail while SELECT 1,null,null::INTEGER UNION SELECT 2,3,null UNION SELECT 3,null,4 will succeed. This is not a critical error, but I would say that the error message is misleading because it is not obvious that Postgres casts unknown columns to TEXT automatically. The current error message is: ERROR: UNION types text and integer cannot be matched I would suggest something like: ERROR: UNION types text and integer cannot be matched. HINT: Postgres casts unknown types to TEXT by default. Thanks, Jeff -- 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 #5975: Incorrect result from mod function with cast
The following bug has been logged online: Bug reference: 5975 Logged by: John Dickson Email address: jdick...@tnsi.com PostgreSQL version: 8.3.11 Operating system: RedHat EL 5.6 Description:Incorrect result from mod function with cast Details: The mod function (and the % operator) can return an incorrect result when combined with a cast(). The following SQL (executed on 8.3.11) shows a result one higher than the maximum permissible remainder when combined with a cast: - select 1129590 % 66, cast(1129590 as numeric(21, 0)) % 66; ?column? | ?column? --+-- 0 | 66 (1 row) - When executed on Postgres 8.2.5 (on Centos 4.6), the results are: - ?column? | ?column? --+-- 0 |0 (1 row) - We're using the cast here to demonstrate the bug, but we actually strike the problem when operating on values from a numeric(21, 0) column rather than using cast(). We haven't tested this on later releases of 8.3, but didn't see any likely fixes in the changelog. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
