Re: [GENERAL] optimizing a cpu-heavy query

Tom, On Apr 26, 2011, at 5:00 PM, Tom Lane wrote: > For another couple orders of magnitude, convert the sub-function to C code. > (I don't think you need > a whole data type, just a function that does the scalar product.) That's a 30x speedup, from 12 minutes down to 38s. Thanks Tom!

Re: [GENERAL] tuning on ec2

On 27/04/11 01:34, Joel Reymont wrote: On Apr 26, 2011, at 4:31 PM, Scott Marlowe wrote: What's your work_mem and max_connections set to? I have the default settings, e.g. work_mem = 1MB and max_connections = 100. I'm looking to process 400 requests per second, though. What should I use for

[GENERAL] Best way to construct PostgreSQL ArrayType (_int4) from C int array

At the moment I am using the following code to construct a PostgreSQL array from a C array in my C extension but I am not so sure if this is really the best solution: const int *data = array.data(); // C array Datum *d = (Datum *) palloc(sizeof(Datum) * size); for (int i = 0; i < size; i++) d[i]

[GENERAL] Create Database automatacally after silent installation of postgresql. ?

Hiii Hey i m using postgresql 8.4. now i m install postgresql8.4 silently and it work properly, no issue during this process. Now problem is i want to create one database automatically after install postgresql 8.4. Can u plz inform me is it possible ? and if it is possible then how ? -- Vi

[GENERAL] Re: Create Database automatacally after silent installation of postgresql. ?

hirenlad, 27.04.2011 09:47: Hiii Hey i m using postgresql 8.4. now i m install postgresql8.4 silently and it work properly, no issue during this process. Now problem is i want to create one database automatically after install postgresql 8.4. Can u plz inform me is it possible ? and if

[GENERAL] PHP and PostgreSQL 9.0, pg_connect fails to connect

I'm having trouble figuring out where this one is going wrong. It's a brand new install of PostgreSQL 9.0 from PGDG on a RHEL5 box, running Apache 2.2 and PHP 5.3 (from IUS). - PostgreSQL 9.0 is running and listening on the localhost. I can run pgAdmin III and connect to it over a SSH port-f

Re: [GENERAL] Best way to construct PostgreSQL ArrayType (_int4) from C int array

On Wed, Apr 27, 2011 at 6:02 AM, Adrian Schreyer wrote: > At the moment I am using the following code to construct a PostgreSQL > array from a C array in my C extension but I am not so sure if this is > really the best solution: > > const int *data = array.data(); // C array > Datum *d = (Datum *)

Re: [GENERAL] Re: Create Database automatacally after silent installation of postgresql. ?

On Wed, Apr 27, 2011 at 6:38 PM, Thomas Kellerer wrote: > hirenlad, 27.04.2011 09:47: > > Hiii >> Hey i m using postgresql 8.4. now i m install postgresql8.4 silently >> and it work properly, no issue during this process. Now problem is i want >> to >> create one database automatically afte

Re: [GENERAL] optimizing a cpu-heavy query

Joel Reymont writes: > On Apr 26, 2011, at 5:00 PM, Tom Lane wrote: >> For another couple orders of magnitude, convert the sub-function to C code. >> (I don't think you need >> a whole data type, just a function that does the scalar product.) > That's a 30x speedup, from 12 minutes down to 38s.

Re: [GENERAL] Switching Database Engines

I was able to export the Wiki database into a single file using the conversion tool mentioned previously. root@ideweb1 postgres]# ./mediawiki_mysql2postgres.pl --db=wiki --user=mediawiki --pass=** Writing file "mediawiki_upgrade.pg" As you can see above that generated a new file in my

[GENERAL] Needs Suggestion

Can I calculate the *Buffer Hit ratio* *of a particular query* in postgres? That is *how many times it finds the required page* in its buffer cache, pg_buffercache or the linux buffer cache. -- Thank You, Subham Roy, CSE IIT Bombay.

Re: [GENERAL] PHP and PostgreSQL 9.0, pg_connect fails to connect

On 4/27/2011 9:16 AM, Thomas Harold wrote: - SELinux is running, but there are no denied messages in /var/log/audit/audit.log and no setroubleshooting alerts in /var/log/messages either. Well, interestingly enough it is SELinux getting in the way, but not logging anything. Temporarily disabl

Re: [GENERAL] PHP and PostgreSQL 9.0, pg_connect fails to connect

On Wed, 27 Apr 2011, Thomas Harold wrote: Well, interestingly enough it is SELinux getting in the way, but not logging anything. Temporarily disabling SELinux suddenly makes it work. This is interesting. I don't run SElinux on my Slackware systems, but a PHP application (CMS Made Simple) fa

Re: [GENERAL] PHP and PostgreSQL 9.0, pg_connect fails to connect

On 4/27/2011 11:42 AM, Thomas Harold wrote: On 4/27/2011 9:16 AM, Thomas Harold wrote: - SELinux is running, but there are no denied messages in /var/log/audit/audit.log and no setroubleshooting alerts in /var/log/messages either. Well, interestingly enough it is SELinux getting in the way, b

[GENERAL] timestamp(0) vs. timestamp

Hi all! This may seem unimportant, but I still would like to know. I have columns for timestamps without fractional digits, so I could define them as timestamp(0). However, there is no way fractions could ever enter anyway, because triggers and / or checks guarantee values without fractional seco

[GENERAL] Index not being used for UPDATE?

Hi. Just want to check why, in an UPDATE sql, the JOIN condition is not making use of the index? In both tables being joined, the column in question is in fact the primary key! Table structure and query below. All I want is to take values from a smaller "accesscount" table and update from it the

Re: [GENERAL] Column storage (EXTERNAL/EXTENDED) settings for bytea/text column

Thanks for the help with that, Noah. Indeed the sizes do look like I'd expect them to if I force deflation of the bytea value before inserting it into the EXTENDED column. On Apr 21, 2011, at 2:02 PM, Noah Misch wrote: > On Mon, Apr 11, 2011 at 03:19:23PM -0700, Joel Stevenson wrote: >> crea

[GENERAL] NULL saves disk space?

Possibly a dumb question but there isn't much about this. http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=postgresql+null+value+disk+space I have some BOOLEAN columns. 90% of the cases of the columns is FALSE. Do I save disk space by having them as NULL instead of FALSE? So my application

Re: [GENERAL] PHP and PostgreSQL 9.0, pg_connect fails to connect

On Wed, Apr 27, 2011 at 10:42 AM, Thomas Harold wrote: > On 4/27/2011 9:16 AM, Thomas Harold wrote: > >> - SELinux is running, but there are no denied messages in >> /var/log/audit/audit.log and no setroubleshooting alerts in >> /var/log/messages either. >> >> > Well, interestingly enough it is SE

[GENERAL] PgDay at OSCON

What: PgDay, a day of PostgreSQL sessions Date: Sunday, July 24, 2011 Time: 9:30 am - 5 pm Location: Oregon Convention Center Your Hosts: PDXPUG, the Portland PostgreSQL Users Group Cost: We'd like a $30 donation to SPI for the sessions, but don't let lack of funds deter you from attending. Co

Re: [GENERAL] problem with parent/child table and FKs

On Thu, Apr 21, 2011 at 07:15:38PM +0200, Alban Hertroys wrote: > > What is the suggested approach for this situation ? (there > > will be more tables like "icd10" holding other coding > > systems of fairly diverse nature but all of them sharing > > .code and .term: LOINC, ATC, ICPC-2, ICD-9, ...)

Re: [GENERAL] Best way to construct PostgreSQL ArrayType (_int4) from C int array

The largest arrays I expect at the moment are more or less sparse vectors of around 4.8k elements and I have noticed that the input/output (C/C++ extension) does not scale well with the number of elements in the array. Using a function that sums all elements in the array, this is the time it takes

Re: [GENERAL] PHP and PostgreSQL 9.0, pg_connect fails to connect

On 4/27/2011 12:24 PM, Michael Nolan wrote: On Wed, Apr 27, 2011 at 10:42 AM, Thomas Harold mailto:thomas-li...@nybeta.com>> wrote: On 4/27/2011 9:16 AM, Thomas Harold wrote: - SELinux is running, but there are no denied messages in /var/log/audit/audit.log and no setroubl

Re: [GENERAL] Best way to construct PostgreSQL ArrayType (_int4) from C int array

On Wed, Apr 27, 2011 at 12:00 PM, Adrian Schreyer wrote: > The largest arrays I expect at the moment are more or less sparse > vectors of around 4.8k elements and I have noticed that the > input/output (C/C++ extension) does not scale well with the number of > elements in the array. > > Using a fu

Re: [GENERAL] timestamp(0) vs. timestamp

Erwin Brandstetter writes: > Hi all! > This may seem unimportant, but I still would like to know. > I have columns for timestamps without fractional digits, so I could > define them as timestamp(0). > However, there is no way fractions could ever enter anyway, because > triggers and / or checks g

Re: [GENERAL] Needs Suggestion

On 4/27/2011 9:35 AM, SUBHAM ROY wrote: Can I calculate the *Buffer Hit ratio* *of a particular query* in postgres? That is *how many times it finds the required page* in its buffer cache, pg_buffercache or the linux buffer cache. -- Thank You, Subham Roy, CSE IIT Bombay. There are plenty of

Re: [GENERAL] Index not being used for UPDATE?

Phoenix Kiula writes: > Just want to check why, in an UPDATE sql, the JOIN condition is not making > use of the index? > [ whole-table update done with a hash join ] That's a perfectly good plan. Indexes typically help only when you want to process just part of a table. If you don't believe it,

Re: [GENERAL] Index not being used for UPDATE?

On 4/27/2011 11:15 AM, Phoenix Kiula wrote: Hi. Just want to check why, in an UPDATE sql, the JOIN condition is not making use of the index? In both tables being joined, the column in question is in fact the primary key! Table structure and query below. All I want is to take values from a smal

Re: [GENERAL] Needs Suggestion

On Wed, Apr 27, 2011 at 16:35, SUBHAM ROY wrote: > Can I calculate the Buffer Hit ratio of a particular query in postgres? That > is how many times it finds the required page in its buffer cache, > pg_buffercache or the > linux buffer cache. You can get the information from the pg cache using EXP

Re: [GENERAL] Partial Index [WHERE col IN ('A','B')] Planner Anomalies

I wrote: > "David Johnston" writes: >> If I explain the above query multiple times (without any data changes) I get >> either of the two query plans / the Bitmap Heap one more often. If I >> analyze the table I immediately get the "Index Scan" plan first but >> subsequent explains revert to switc

Re: [GENERAL] Needs Suggestion

I am using postgres 8.4.8, the above command explain(analyze,buffers) is not working. Is there a way to do that. -- Thank You, Subham Roy, CSE IIT Bombay.

Re: [GENERAL] Best way to construct PostgreSQL ArrayType (_int4) from C int array

On Wed, Apr 27, 2011 at 18:06, Merlin Moncure wrote: > On Wed, Apr 27, 2011 at 12:00 PM, Adrian Schreyer wrote: >> The largest arrays I expect at the moment are more or less sparse >> vectors of around 4.8k elements and I have noticed that the >> input/output (C/C++ extension) does not scale well

[GENERAL] explain analyze buffer

I am using postgres 8.4.8. EXPLAIN (ANALYZE,BUFFERS) is not working. So is there any patch available for it? If so, kindly specify from where to get it and how to install it. -- Thank You, Subham Roy, CSE IIT Bombay.

[GENERAL] PostgreSQL Core Team

I'm pleased to announce that effective immediately, Magnus Hagander will be joining the PostgreSQL Core Team. Magnus has been a contributor to PostgreSQL for over 12 years, and played a major part in the development and ongoing maintenance of the native Windows port, quickly becoming a committer t

Re: [GENERAL] explain analyze buffer

On Wed, Apr 27, 2011 at 20:45, SUBHAM ROY wrote: > I am using postgres 8.4.8. > EXPLAIN (ANALYZE,BUFFERS) is not working. So is there any patch available > for it? If so, kindly specify from where to get it and how to install it. This functionality is new in PostgreSQL 9.0. You need to upgrade to

Re: [GENERAL] PG 9.0 - Default postgresql.conf?

Phoenix Kiula wrote: What changes does initdb make -- can I make them by myself? The main thing is that it adjusts shared_buffers down until the server will start without exceeding the OS limits. It also sets some locale parameters within the database. Btw, the default file is FAIL

Re: [GENERAL] PostgreSQL Core Team

On 27/04/2011 19:48, Dave Page wrote: I'm pleased to announce that effective immediately, Magnus Hagander will be joining the PostgreSQL Core Team. Magnus has been a contributor to PostgreSQL for over 12 years, and played a major part in the development and ongoing maintenance of the native Wind

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

Kudos! Cheers, David. On Wed, Apr 27, 2011 at 07:48:48PM +0100, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > > Magnus has been a contributor to PostgreSQL for over 12 years, and > played a major part in the de

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

On 27 April 2011 19:48, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > > Magnus has been a contributor to PostgreSQL for over 12 years, and > played a major part in the development and ongoing maintenance of the

Re: [GENERAL] PostgreSQL Core Team

Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > > Magnus has been a contributor to PostgreSQL for over 12 years, and > played a major part in the development and ongoing maintenance of the > native Windows port,

Re: [GENERAL] explain analyze buffer

On 04/27/2011 12:45 PM, SUBHAM ROY wrote: I am using postgres 8.4.8. |EXPLAIN (ANALYZE,BUFFERS)| is not working. So is there any patch available for it? If so, kindly specify from where to get it and how to install it. -- Thank You, Subham Roy, CSE IIT Bombay. Have you tried just plain exp

Re: [GENERAL] PostgreSQL Core Team

On Apr 27, 2011, at 14:48, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. Congratulations, Magnus! Michael Glaesemann grzm seespotcode net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.or

[GENERAL] Different sort order btwn Linux and Windows

I'm running the following SQL commands on a new PostgreSQL 9.0 database, one on Linux, one on Windows. create table i (j varchar(4)); insert into i (j) values ('A'), ('E'), ('

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

On Wed, Apr 27, 2011 at 1:48 PM, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > Congratulations! -- Jaime Casanova         www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL --

Re: [GENERAL] Different sort order btwn Linux and Windows

On 27 April 2011 20:28, Doug Gorley wrote: > I'm running the following SQL commands on a new PostgreSQL 9.0 database, > one on Linux, one on Windows. > > create table i (j varchar(4)); > insert into i (j) values ('A'), ('E'), (' select j from i order by j asc; > > On the Windows server I get the

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

On Wed, Apr 27, 2011 at 1:48 PM, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > > Magnus has been a contributor to PostgreSQL for over 12 years, and > played a major part in the development and ongoing maintenanc

Re: [GENERAL] Different sort order btwn Linux and Windows

That does indeed appear to be the case -- the database on the Linux server has en_CA.UTF-8 collation, while the database on the Windows server has English_Canada.1252 collation. Thanks! Doug On Wed, Apr 27, 2011 at 1:04 PM, Thom Brown wrote: > On 27 April 2011 20:28, Doug Gorley wrote: > >>

[GENERAL] GIN index not used

I have problem with GIN index. Queries over it takes a lot of time. Some informations: I've got a table with tsvector- textvector: CREATE TABLE mediawiki.pagecontent ( old_id integer NOT NULL DEFAULT nextval('mediawiki.text_old_id_seq'::regclass), old_text text, old_flags text, textvector

[GENERAL] GIN index not used

I have problem with GIN index. Queries over it takes a lot of time. Some informations: I've got a table with tsvector- textvector: CREATE TABLE mediawiki.pagecontent ( old_id integer NOT NULL DEFAULT nextval('mediawiki.text_old_id_seq'::regclass), old_text text, old_flags text, textvector

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

2011/4/27 Dave Page : > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > > Magnus has been a contributor to PostgreSQL for over 12 years, and > played a major part in the development and ongoing maintenance of the > native Windows por

Re: [GENERAL] [ANNOUNCE] PostgreSQL Core Team

Congratulation! Regards, Nado Sent from my BlackBerry® powered by Sinyal Kuat INDOSAT -Original Message- From: Dave Page Sender: pgsql-announce-owner@postgresql.orgDate: Wed, 27 Apr 2011 19:48:48 To: pgsql-announce Cc: PostgreSQL Hackers; Subject: [ANNOUNCE] PostgreSQL Core Team I'm

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

Congratulations. Jeremiah Peschka Microsoft SQL Server MVP MCITP: Database Developer, DBA On Apr 27, 2011 11:49 AM, "Dave Page" wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > > Magnus has been a contributor to PostgreSQL

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

Congratulations!! (2011/04/28 3:48), Dave Page wrote: I'm pleased to announce that effective immediately, Magnus Hagander will be joining the PostgreSQL Core Team. Magnus has been a contributor to PostgreSQL for over 12 years, and played a major part in the development and ongoing maintenan

Re: [GENERAL] [ANNOUNCE] PostgreSQL Core Team

Magnus, congratulations! In my short two years around PostgreSQL your name has been synonymous with super intelligence and a great attitude. All the best Sean On Apr 27, 2011, at 1:48 PM, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining

Re: [GENERAL] [ANNOUNCE] [HACKERS] PostgreSQL Core Team

My Congratulations too ;)!!! Enviado desde mi iPhone El 27/04/2011, a las 18:50, Hiroshi Saito escribió: > Congratulations!! > > (2011/04/28 3:48), Dave Page wrote: >> I'm pleased to announce that effective immediately, Magnus Hagander >> will be joining the PostgreSQL Core Team. >> >> M

Re: [GENERAL] Partitioning an existing table

On Tue, Apr 26, 2011 at 8:28 PM, Greg Smith wrote: > On 04/25/2011 10:10 AM, Vick Khera wrote: >> >> Basically, you create your partitions and set up the necessary triggers >> you want (I re-write the app to insert directly into the correct partition). >>  Then all new data starts going into the p

Re: [GENERAL] Best way to construct PostgreSQL ArrayType (_int4) from C int array

2011/4/27 Adrian Schreyer : > The largest arrays I expect at the moment are more or less sparse > vectors of around 4.8k elements and I have noticed that the > input/output (C/C++ extension) does not scale well with the number of > elements in the array. > > Using a function that sums all elements

[GENERAL] New feature: skip row locks when table is locked.

Hi. Yesterday i have an idea, that sometimes row locks may be skipped, when table is already locked with LOCK command. It may to reduce an overhead from row locks. What do you think about it? -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make ch

Re: [GENERAL] [ANNOUNCE] PostgreSQL Core Team

Grattis Magnus! ;-) --On 27 april 2011 19.48.48 +0100 Dave Page wrote: I'm pleased to announce that effective immediately, Magnus Hagander will be joining the PostgreSQL Core Team. Magnus has been a contributor to PostgreSQL for over 12 years, and played a major part in the development and on

Re: [GENERAL] GIN index not used

On 27 Apr 2011, at 22:55, Mark wrote: > I've got a table with tsvector- textvector: > CREATE TABLE mediawiki.pagecontent > ( > old_id integer NOT NULL DEFAULT > nextval('mediawiki.text_old_id_seq'::regclass), > old_text text, > old_flags text, > textvector tsvector, > CONSTRAINT pagecontent_p

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

Congrats Magnus!!! Thanks for the smart work and keep it up... -- Thanks & Regards, Ashesh Vashi EnterpriseDB INDIA: Enterprise PostgreSQL Company *http://www.linkedin.com/in/asheshvashi* On Thu, Apr 28, 2011 at 12:18 AM, D