http://blog.gmane.org/gmane.comp.db.postgresql.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://comments.gmane.org/gmane.comp.db.postgresql.general/163014 <pre>Hi, I'm not into comparing which DBMS is better as we all know ... kind of ... well ... I'd like to find ressources to look up how one can do X in MSSQL when one knows how it is done in PG's SQL and the other way around. regards Andreas </pre> Andreas 2012-05-26T04:04:16 http://comments.gmane.org/gmane.comp.db.postgresql.general/163012 <pre>I'm trying to build postgres 9.2 beta1 on debian stable from sources, in a way that plays nicely with the debian cluster management stuff. If I configure with libdir=/usr/lib/postgres everything works but the shared libraries end up in the wrong place (ie just where I specified). If I configure with libdir=/usr/lib/postgresql/9.2, which is where I want the libraries to go, psql complains: $ /usr/lib/postgresql/9.2/bin/psql: symbol lookup error: /usr/lib/postgresql/9.2/bin/psql: undefined symbol: PQconnectdbParams Here is my configure command: $ ./configure --build=i486-linux-gnu --prefix=/usr --includedir=/usr/include/postgresql/9.2 --mandir=/usr/share/postgresql/9.2/man --infodir=/usr/share/postgresql/9.2/info --sysconfdir=/etc/postgresql-common --localstatedir=/var --libexecdir=/usr/lib/postgresql/9.2 --libdir=/usr/lib/postgresql/9.2 --srcdir=. -docdir=/usr/share/doc/postgresql-doc-9.2 --datadir=/usr/share/postgresql/9.2/ --bindir=/usr/lib/postgresql/9.2/bin --enable-nls --enable-integer-datetimes --</pre> Marc Munro 2012-05-25T22:05:37 http://comments.gmane.org/gmane.comp.db.postgresql.general/163008 <pre>Hi all I just had the ... pleasure ... of using Windows with Pg again and was in a usability review frame of mind. I landed up trying to restore my database using PgAdmin-III, and was astonished at how difficult and painful it was. The issues weren't all PgAdmin-III either, there are a few Pg-on-Windows issues and a few plain warts in terms of PostgreSQL usability in general. It felt like my first experience with Oracle (ie: screaming, pain and confusion) not the smooth and pleasurable experience I've come to be so used to with Pg. I was sufficiently surprised by some of the issues that I've written up a post on the matter. I intended it to be a few usability notes, though it's turned into a bit more than that. I think it's really imporant to highlight these issues, because if this had been my first experience with PostgreSQL I would have walked away and never, ever, ever come back. It might be premature to post this before I've reviewed and re-edited the post, but hey, a few flames won't hu</pre> Craig Ringer 2012-05-25T09:56:47 http://comments.gmane.org/gmane.comp.db.postgresql.general/162990 <pre>hello I wrote patch for PostgreSQL 9.1 and 9.2 that adds more linestyles and border styles to console http://postgres.cz/wiki/Pretty_borders_in_psql Regards Pavel Stehule </pre> Pavel Stehule 2012-05-24T13:33:38 http://comments.gmane.org/gmane.comp.db.postgresql.general/162987 <pre>I have observed by default posgresql 8.x onwards starts as daemon process. Is there a way to avoid it. I want it to run as normal process. The parent of the server process should be the process that starts the server instead of being PID 1 ( init ). Till 7.4.2 ( Which was the older version used by us ) was using running in normal mode. The same behavior is necessary as we have our watchdog failing to identify that it server is actually running. ( Transcript to help you what I am talking about ) c670e04:rkananth 16] /usr/local/bin/postgres --version postgres (PostgreSQL) 9.0.7 c670e04:rkananth 11] postgres -D /data/db/postgres c670e04:rkananth 12] ps -A | grep post 65165 ?? Ss 0:00.01 /usr/local/bin/postgres 65167 ?? Ss 0:00.00 postgres: writer process (postgres) 65168 ?? Ss 0:00.00 postgres: wal writer process (postgres) 65169 ?? Ss 0:00.00 postgres: autovacuum launcher process (postgres) 65170 ?? Ss 0:00.00 postgres: stats collector process (postgres) 65224 0 S+</pre> Karthik 2012-05-24T10:07:28 http://comments.gmane.org/gmane.comp.db.postgresql.general/162981 <pre>If I run this query: select sum(length(html)) from Indexer.Pages; I get: 15,680,005,116 However, if I type: C:\Program Files\PostgreSQL&gt;dir /s I get: Total Files Listed: 5528 File(s) 7,414,385,333 bytes 575 Dir(s) 43,146,137,600 bytes free So all the Postgres data on disk is a little over 7 gigs, however the total sum of bytes in the HTML column of the Pages table is over 15 gigs. Is PG compressing this data? I'm curious as I was considering converting this column to a byte array and gzip'ing the data to save space, however if PG is already doing this for me, then I'm not going to bother. Thanks! Mike </pre> Mike Christensen 2012-05-24T01:07:01 http://comments.gmane.org/gmane.comp.db.postgresql.general/162977 <pre>Hi, I'm running a PG 9.1.3 on OpenSuse 12.1. I found that pg_log is 2 hours ahead though date on the console shows the right date and time. I have the ntp daemon watching the system's time every 60 minutes so this shouldn't be an issue. The time in PG's logfiles filenames as well as the timestamps within the logs are 2 hours ahead. How can I fix this? </pre> Andreas 2012-05-23T23:11:48 http://comments.gmane.org/gmane.comp.db.postgresql.general/162972 <pre>We're getting ready to do system upgrades to our postgresql hosting cluster. Currently, we're using 8-core servers with 32 GB of RAM in each to host approximately 75 end user databases per server. I'm wondering if there are particular performance bottlenecks we should be aware of as we scale the hardware up? Does PG handle 64 GB of RAM well? 128 GB? 16 cores? 48 cores? SAS/SATA III with SSDs? (etc.) A bit of Googling found this thread indicating potential performance issues at 28ish cores a couple years back http://postgresql.1045698.n5.nabble.com/MIT-benchmarks-pgsql-multicore-up-to-48-performance-td3173545.html Thanks, Ben </pre> Lists 2012-05-23T21:37:38 http://comments.gmane.org/gmane.comp.db.postgresql.general/162946 <pre>Hi guys, I'm interested in a solution that will allow our customers to run reports - which may involve complicated queries - on data which is as up-to-date as possible. One thing I don't want to do is to let the reporting system connect to the production database. I want the indexes in production to be limited to what production needs, and not add indexes that are required for reports, for instance. And basically, I don't want a customer to run a complicated report and degrade the performance of my production system. A replication solution is not very good, either, because of course I can't define indexes differently, I don't want *all* transactions in all tables to be sent, and also, because I may want to cross reference data from different systems. So ideally, I want to have a reporting database, where specific tables (or maybe even just specific columns) from various databases are collected, and have a reporting tool connect to this database. But I want to push the data into into that database as close</pre> Herouth Maoz 2012-05-23T13:11:06 http://comments.gmane.org/gmane.comp.db.postgresql.general/162945 <pre>Hi all, in my production environment I have Postgresql 8.2 + PgPool. The port number I specify in PgPool configuration is not a real portnumber (e.g. 123456). I can define a "dblink" as : dblink('dbname=XXXX host=/var/run port=123456 user=XXXX password=XXXX'::text, 'select ... '::text) t1( ... ); or start "psql" as: psql -U XXXX -h /var/run -p 123456 XXXX without problems. In my development environment I upgraded to 9.1 version and the result is: invalid port number: "123456" in both cases. Does Postgresql 9.1 check the "port number" value although I'm connecting through a "Unix domain socket"? Is it a bug or an intended behavior? I noticed that it accepts values up to 65535 . Thanks in advance Alberto </pre> Alberto Zanon 2012-05-23T12:56:26 http://comments.gmane.org/gmane.comp.db.postgresql.general/162943 <pre>I'm using postgresql 9.1.3 from debian squeeze-backports with zh_CN.UTF-8 locale, i find my main log (which is "/var/log/postgresql/postgresql-9.1-main.log") contains "???" which indicate some sort of charset encoding problem. But error messages related to pgsql is fine, only other system messages have this problem, for example: 2012-05-19 16:06:12 CST ??: ?????????? 2012-05-19 16:06:10 CST I guess it has something to do with packaging problem rather than postgresql itself, but it would be great if you can give me some clue where the problem might be. My best regards. Yi Huang. </pre> yi huang 2012-05-23T01:15:35 http://comments.gmane.org/gmane.comp.db.postgresql.general/162939 <pre>Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I'm finding that I cannot runpg_basebackup at all, or it blocks all SQL queries from running until pg_basebackup has completed (and the load on the box just takes off to over 75.00). By "blocks" I mean that any query that is submitted just hangs and does not return at all until pg_basebackup has stopped. I'm assuming that this isn't expected behavior, so I'm rather confused on what is going on. The command that I'm issuing is: pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres Can someone provide some guidance on how to debug this? thanks! </pre> Lonni J Friedman 2012-05-22T18:42:35 http://comments.gmane.org/gmane.comp.db.postgresql.general/162936 <pre>Hello I'm implementing HA/failover for my PG nodes. I'm using PG9.0 and async replication and linux. Typical problem - if node 1 fails I want the mirror to become active and take over for the master. The solution should be able to initiate the failover of the standby and start re-directing traffic it. I've spent a lot of time looking at PgPool so I'm well aware that it's perfectly capable of this. However, it feels a little bit like overkill since i don't want it's pooler and I'm not load balancing. The other option would be RHCS, which I know will work, but to implement it with pg replication is a little hacky. So, I'm considering HAProxy, does anyone have experience with that for managing HA, good or bad? Thanks. </pre> David Kerr 2012-05-22T16:36:13 http://comments.gmane.org/gmane.comp.db.postgresql.general/162933 <pre>Why jasperserver has been changed from MySQL to PostGreSQL In previous versions of jasperserver, MySQL was the supporting database but in the jasperserver 4.5 version it changed to PostGreSQL. My question is that why jasperserver changed its mind to PostGreSQL. Which of the features of PostGreSQL are powerful than MySQL? Could you please give me a clue?   Thanks and Regards Farhad Koohbor – BI/DW developer</pre> farhad koohbor 2012-05-22T14:31:24 http://comments.gmane.org/gmane.comp.db.postgresql.general/162930 <pre>stop sending mail </pre> Gugu Nomcebo Mthimkhulu 2012-05-22T14:13:20 http://comments.gmane.org/gmane.comp.db.postgresql.general/162917 <pre>Greetings,   I am trying to get the source XML file for the PostgreSQL installer. This is the BitRock InstallBuilder XML file. Can anyone direct me to the proper place to obtain this installer file?   Thank you, Greg</pre> Greg Simpson 2012-05-22T06:03:49 http://comments.gmane.org/gmane.comp.db.postgresql.general/162916 <pre>Hi, We are using Postgres 9.1.1 on a board with Coldfire controller. The postgres processes are crashing and restarting upon executing a particular instruction and it keeps repeating. Even when we tried with Postgres 9.1.3, same problem happens. It works fine until the FINANCIALTRANSACTIONID reaches 1000. But the same setup is working fine on a windows PC. We have tried to compare the configuration differences between windows PC and the board and found that only difference is the Shared Buffers which is 32 on the PC and 24 on the board. I am pasting the server log from the board here. The line highlighted in yellow is the instruction which is causing the crash. Please let us know why this crash is happening and how we can fix it. LOG: redo starts at 0/D9B75B4 LOG: record with zero length at 0/D9BBE5C LOG: redo done at 0/D9BBE22 LOG: last completed transaction was at log time 2012-05-22 02:22:26.641488+00 LOG: database system is ready to accept connections LOG: autovacuum launcher started ERROR: du</pre> Jayashankar K B 2012-05-22T05:57:20 http://comments.gmane.org/gmane.comp.db.postgresql.general/162909 <pre>Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I made no changes to postgresql.conf following the upgrade. I used pg_upgrade on the master to upgrade it, followed by blowing away $PGDATA on all the standbys and rsyncing them fresh from the master. All of the servers have 128GB RAM, and at least 16 CPU cores. Everything appeared to be working fine until last night when the load on the master suddenly took off, and hovered at around 30.00 ever since. Prior to the load spike, the load was hovering around 2.00 (which is actually lower than it was averaging prior to the upgrade when it was often around 4.00). When I got in this morning, I found an autovacuum process that had been running since just before the load spiked, and the pg_dump cronjob that started shortly after the loa</pre> Lonni J Friedman 2012-05-21T21:05:51 http://comments.gmane.org/gmane.comp.db.postgresql.general/162908 <pre>* I am banging my head over this. I want to select distinct values from a varchar column and iterate through the values. * *I want to select the distinct values from this column and loop through them (using as a variable) in a raise notice statement and also in an update statement. I have not been able to do this trying dozens of different approaches. I could not find an example even after searching google. ** So for example, suppose table: mytable has a column "value" that is defined as a varchar: ** for tmp_var in select distinct(value) from mytable where value2='literal' loop raise notice 'I want to print a message here - the tmp_var is [' || tmp_var || ']'; &lt;== error on this line update table set somecolumn = ''' || tmp_var || ''' end loop; I want to use each distinct value in a "raise notice" line and an update statement. ** tmp_var has to be in ' ' ticks or will not work. it is failing on the first FOR statement stating: "invalid input syntax for inte</pre> J.V. 2012-05-21T20:39:40 http://comments.gmane.org/gmane.comp.db.postgresql.general/162903 <pre>I am searching for some logic behind the selection of an index in postgres -- it seems that if I have a composite index based on both columns in a join table, it's only referenced if I query on the first term in the composite index. I've read http://www.postgresql.org/docs/9.1/static/indexes-multicolumn.html over and over and think that this is the same scenario as what I face. As an example: OUTLET: has OUTLET_ID as a primary key, consisting of about a million rows MEDIA: has MEDIA_ID as a primary key, and table consists of only 10 rows OUTLET_MEDIA: a join table used to correlate, and this has about a million rows Each outlet may have 1+ Media (technically, 0 or more, in this schema) Table "public.outlet_media" Column | Type | Modifiers -----------+--------+----------- outlet_id | bigint | not null media_id | bigint | not null Indexes: "outlet_media_pkey" PRIMARY KEY, btree (outlet_id, media_id) Foreign-key constraints: "fkfde1d912281e6fbf" FOREIGN KEY (media_id) REFERENCES media(me</pre> Bill Mitchell 2012-05-21T19:34:42 http://comments.gmane.org/gmane.comp.db.postgresql.general/162896 <pre>Hi all, I don't fully understand how is the cost of a bitmap heap scan computed. For instance when the explain output node is similar to the following: Bitmap Heap Scan on test (cost=17376.49..48595.93 rows=566707 width=6) Recheck Cond: ((text1 = 'A'::text) OR (text1 = 'C'::text)) Filter: (num1 &gt; 1) how is the cost of the node (48595.93 - 17376.49) computed? I think it should be something like: (reltuples * ( index_filtering_factor_A + index_filtering_factor_B) ) * (cpu_tuple_cost + cpu_operator_cost) + (reltuples * ( index_filtering_factor_A + index_filtering_factor_B) ) / tuples_per_pages but this does not equal the optimizer cost, so I guess I'm doing something wrong. Suggestions? Thanks, Luca </pre> Luca Ferrari 2012-05-21T13:35:55 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.general