Re: [GENERAL] convert text field to utf8 in sql_ascii database

Andy Colson wrote: > I am in the middle of a process to get all my data into utf8. As its > not all converted yet, my database encoding is SQL_ASCII. > > I am getting external apps fixed up to write utf8 to the database, and > so far so good. But, I ran across some stuff that needs a one time >

Re: [GENERAL] Performance degradation 8.4 -> 9.1

On 11/17/2011 02:24 PM, Joseph Shraibman wrote: This query is taking much longer on 9.1 than it did on 8.4. Why is it using a seq scan? To answer that question in all cases, it's necessary to know a) the query, b) the PostgreSQL version, c) the table definitions including what indexes ex

Re: [GENERAL] monitoring sql queries

On Thu, Nov 17, 2011 at 02:32:22PM -0700, J.V. wrote: > How is this accomplished? > > Is it possible to log queries to a table with additional information? > > 1) num rows returned (if a select) > 2) time to complete the query > 3) other info? > > How is enabling this actually done? please chec

Re: [GENERAL] Performance degradation 8.4 -> 9.1

On 18 Listopad 2011, 11:39, Greg Smith wrote: > On 11/17/2011 02:24 PM, Joseph Shraibman wrote: >> This query is taking much longer on 9.1 than it did on 8.4. Why is it >> using a seq scan? >> > > To answer that question in all cases, it's necessary to know a) the > query, b) the PostgreSQL versio

Re: [GENERAL] Authentication configuration for local connections on Windows

Thanks! On Thu, Nov 17, 2011 at 7:33 PM, Adrian Klaver wrote: > On Thursday, November 17, 2011 3:41:22 pm deepak wrote: > > Hi ! > > > Although, it is not clear what options I have to use while > > building/configuring? > > This same configuration used to work with Postgres 9.0.3, though. > > > >

Re: [GENERAL] Huge number of INSERTs

On Fri, Nov 18, 2011 at 9:25 AM, Steve Crawford wrote: >> Database only? Or is it also your webserver? It's my webserver and DB. Webserver is nginx, proxying all PHP requests to apache in the backend. > What version of PostgreSQL? What OS? What OS tuning, if any, have you done? > (Have you i

Re: [GENERAL] Huge number of INSERTs

On 18/11/11 12:30, Phoenix Kiula wrote: I've currently disabled any INSERT functions on my website...but even with disabled INSERTs and only SELECTs alive, I still see the "psql: FATAL: sorry, too many clients already" message. As Tomas has said, this is nothing to do with inserts and everythi

Re: [GENERAL] VACUUM touching file but not updating relation

On 12 November 2011 00:08, Thom Brown wrote: > On 11 November 2011 23:28, Tom Lane wrote: >> Thom Brown writes: >>> On 11 November 2011 00:55, Tom Lane wrote: Thom Brown writes: > I just noticed that the VACUUM process touches a lot of relations > (affects mtime) but for one file

Re: [GENERAL] VACUUM touching file but not updating relation

Thom Brown writes: >> On 11 November 2011 23:28, Tom Lane wrote: >>> I observe that _bt_delitems_vacuum() unconditionally dirties the page >>> and writes a WAL record, whether it has anything to do or not; and that >>> if XLogStandbyInfoActive() then btvacuumscan will indeed call it despite >>> t

Re: [GENERAL] VACUUM touching file but not updating relation

On Fri, Nov 18, 2011 at 2:47 PM, Tom Lane wrote: > Thom Brown writes: >>> On 11 November 2011 23:28, Tom Lane wrote: I observe that _bt_delitems_vacuum() unconditionally dirties the page and writes a WAL record, whether it has anything to do or not; and that if XLogStandbyInfoActi

Re: [GENERAL] VACUUM touching file but not updating relation

On Fri, Nov 18, 2011 at 2:47 PM, Tom Lane wrote: > Thom Brown writes: >>> On 11 November 2011 23:28, Tom Lane wrote: I observe that _bt_delitems_vacuum() unconditionally dirties the page and writes a WAL record, whether it has anything to do or not; and that if XLogStandbyInfoActi

Re: [GENERAL] VACUUM touching file but not updating relation

Simon Riggs writes: > On Fri, Nov 18, 2011 at 2:47 PM, Tom Lane wrote: >> Well, it's expected given the current coding in the btree vacuum logic. >> It's not clear to me why it was written like that, though. > The code works as designed. > _bt_delitems_vacuum() is only ever called with nitems =

Re: [GENERAL] VACUUM touching file but not updating relation

On Fri, Nov 18, 2011 at 3:18 PM, Tom Lane wrote: > What Thom's complaining about is that the buffer may be marked dirty > unnecessarily, ie when there has been no actual data change. OK, I'll patch it. --  Simon Riggs   http://www.2ndQuadrant.com/  PostgreSQL Development, 24x7

Re: [GENERAL] Using the internal data dictionary

Thanks, guys! I'll take a closer look at the information_schema and pgAdmin and Maestro. Reinventing the wheel isn't a problem as this job is not critical, but the educational experience in looking at the system from another POV may be the bigger prize. - Bill On 11/17/2011 8:34 PM, David

Re: [GENERAL] Using the internal data dictionary

On Fri, Nov 18, 2011 at 10:54 AM, Bill Thoen wrote: > Thanks, guys! > > I'll take a closer look at the information_schema and pgAdmin and Maestro. > Reinventing the wheel isn't a problem as this job is not critical, but the > educational experience in looking at the system from another POV may b

Re: [GENERAL] Result of ORDER-BY

> See the other remark in this thread about GROUP BY and ORDER BY. Note > that GROUP BY used to cause ORDER BY every time, because it was always > implemented with a sort. That hasn't been true for several releases, > and if you're relying on that side effect it could be the cause of > this, alth

Re: [GENERAL] Result of ORDER-BY

> The query as shown does't actually have an ORDER BY clause in it; > did you write GROUP BY where you meant ORDER BY? Thank you for your reply. I tried all combinations - GROUP BY - ORDER BY - GROUP BY & ORDER BY the result is always the same. -- Sent via pgsql-general mailing list (pgsql-gene

Re: [GENERAL] Huge number of INSERTs

On 11/18/2011 04:30 AM, Phoenix Kiula wrote: On Fri, Nov 18, 2011 at 9:25 AM, Steve Crawford wrote: Database only? Or is it also your webserver? It's my webserver and DB. Webserver is nginx, proxying all PHP requests to apache in the backend. You still didn't answer what "massive traffic"

[GENERAL] Upgrading from 8.3.14 to 8.3.16 on Windows

I just ran the upgrade process for updating my PostgreSQL installation (running on Windows XP) from 8.3.14 to 8.3.16. I used pgInstaller's UPGRADE script to conduct the upgrade. It appeared to work without problems. When I tried to verify that the upgrade took place, I noticed something

[GENERAL] How to use like with a list

Hi: How can I search on a csv list of values using "like" where each value is to be appended with a wildcarded string? Example: list = 'jo,mo,do,fo' I want to pull all names from a table with name like.. 'jol%' or 'mol%' or'dol%' or 'sol%' would match "jolly, molly, moleman,dollface, solarboy

[GENERAL] How to install latest stable postgresql on Debian

I tried to install latest PostgreSql on plain Debian using instructions from http://backports-master.debian.org/Instructions/ I added line described there to sources and tried root@EEPOLDB01:~# apt-get -t squeeze-backports install postgresql-9.1 Reading package lists... Done Building dependency

Re: [GENERAL] How to install latest stable postgresql on Debian

On 18/11/2011 19:59, Andrus wrote: > I tried to install latest PostgreSql on plain Debian using instructions from > > http://backports-master.debian.org/Instructions/ > > I added line described there to sources and tried > > root@EEPOLDB01 :~# apt-get -t squeeze-backpor

Re: [GENERAL] How to use like with a list

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Friday, November 18, 2011 2:56 PM To: pgsql-general@postgresql.org Subject: [GENERAL] How to use like with a list Hi: How can I search on a csv list of values using "like"

Re: [GENERAL] How to use like with a list

On 11/18/11 11:55 AM, Gauthier, Dave wrote: Hi: How can I search on a csv list of values using "like" where each value is to be appended with a wildcarded string? Example: list = 'jo,mo,do,fo' I want to pull all names from a table with name like.. 'jo*l%*' or 'mo*l%*' or'do*l%*' or 'so*l

Re: [GENERAL] How to use like with a list

On Fri, Nov 18, 2011 at 12:13 PM, John R Pierce wrote: > where field ~ '^(jo|mo|do|fo)' Don't forget to add the l as the end: where field ~ '^(jo|mo|do|fo)l' -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscri

Re: [GENERAL] How to use like with a list

On 11/18/11 12:18 PM, Richard Broersma wrote: On Fri, Nov 18, 2011 at 12:13 PM, John R Pierce wrote: > where field ~ '^(jo|mo|do|fo)' Don't forget to add the l as the end: where field ~ '^(jo|mo|do|fo)l' ah, yeah, that. and to complete the original requirement... where field ~ '^(' || r

Re: [GENERAL] How to use like with a list

The example was a general case. It won't be jo and mo and fo. In fact, the values will be stored in a csv perl scalar. If you know perl... $str = "jo,mo,do,fo"; Using DBI, I need to "prepare" a query that will accept a string like the one above. So... select name,age,weight from people_ta

Re: [GENERAL] How to use like with a list

On 11/18/11 12:37 PM, Gauthier, Dave wrote: bi_dev=# select name from test where name ~ '^(' || replace('jo,mo,do,fo', ',', '|') || ')l'; ERROR: argument of WHERE must be type boolean, not type text ah, needs () around the right side of the ~ expression, not sure why. does ~ have higher ex

Re: [GENERAL] How to use like with a list

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Friday, November 18, 2011 3:37 PM To: John R Pierce; PostgreSQL Subject: Re: [GENERAL] How to use like with a list The example was a general case. It w

Re: [GENERAL] operator precedence (was: How to use like with a list)

On 11/18/11 12:47 PM, John R Pierce wrote: does ~ have higher expression priority than || or something? speaking of... the precedence table [1] seems somewhat short of operators... the regex operators like ~ ~* etc aren't on there, nor is string concatenation || ... I'd expect the regex p

Re: [GENERAL] CLONE DATABASE (with copy on write?)

"Clark C. Evans" writes: > Hello all! > > Our company has some headaches in our application development > and deployment process. The chief problem is, "creating stages", > which to this audience is, cloning a database efficiently, > making and testing a few changes, perhaps recording the >

[GENERAL] MS SQL Server (2005, 2008) ==> PostgreSQL 9.x

What are some of my options for dumping data / constraints out of SQL Server and importing to PostgreSQL 9.x? I can probably recreate the schema in PostgreSQL with a tool and make sure all constraints are there ( < 100 tables ), but want to know the best way to get data over. Text or number

Re: [GENERAL] How to use like with a list

BINGO ! Thanks :-) -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Friday, November 18, 2011 3:47 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to use like with a list On 11/18/11 12

Re: [GENERAL] Is it ever necessary to vacuum a table that only gets inserts/updates?

On 18/11/11 04:59, Tom Lane wrote: Craig Ringer writes: On Nov 17, 2011 1:32 PM, "Tom Lane" wrote: If it's purely an insert-only table, such as a logging table, then in principle you only need periodic ANALYZEs and not any VACUUMs. Won't a VACUUM FREEZE (or autovac equivalent) be necessary e

Re: [GENERAL] Is it ever necessary to vacuum a table that only gets inserts/updates?

On Fri, Nov 18, 2011 at 2:56 PM, Gavin Flower wrote: > On 18/11/11 04:59, Tom Lane wrote: > >> Craig Ringer writes: >> >>> On Nov 17, 2011 1:32 PM, "Tom Lane" wrote: >>> If it's purely an insert-only table, such as a logging table, then in principle you only need periodic ANALYZEs and

Re: [GENERAL] How to install latest stable postgresql on Debian

Ray, thank you. Did you do "apt-get update" after adding the line to the source list? Yes I tried. I tried it again and it looks like the instructons provided in debian site are invalid. How to fix ? Andrus. root@EEPOLDB01:~# cat /etc/apt/sources.list deb http://www.backports.debian.org/

Re: [GENERAL] How to install latest stable postgresql on Debian

Ray, Did you do "apt-get update" after adding the line to the source list? I fixed this but now another issue arises. Installaton fails with error below. How to fix this ? root@EEPOLDB01:~# apt-get install postgresql-9.1 Reading package lists... Done Building dependency tree Reading state inf

Re: [GENERAL] How to install latest stable postgresql on Debian

On Friday, November 18, 2011 2:59:44 pm Andrus wrote: > Ray, > > thank you. > > >Did you do "apt-get update" after adding the line to the source list? > > Yes I tried. I tried it again and it looks like the instructons provided in > debian site are invalid. > How to fix ? Take out the www. htt

Re: [GENERAL] How to install latest stable postgresql on Debian

Adrian, thank you. I'm sorry for stupid mistake. I uninstalled 8.4. Trying to install 9.1 now returns root@EEPOLDB01:~# apt-get install postgresql-9.1 Reading package lists... Done Building dependency tree Reading state information... Done Some packages could not be installed. This may mean that

Re: [GENERAL] How to install latest stable postgresql on Debian

On Friday, November 18, 2011 3:15:01 pm Andrus wrote: > Adrian, > > thank you. I'm sorry for stupid mistake. > I uninstalled 8.4. Trying to install 9.1 now returns How did you uninstall 8.4? From below it would seem it is still around. > > Andrus. -- Adrian Klaver adrian.kla...@gmail.com -

[GENERAL] 0.0.0.0 addresses in postgresql.conf on Windows

Hi! It appears that when I try to configure listen_addresses in postgresql.conf (on Windows) with '0.0.0.0' , pg_ctl doesn't properly detect that server has started and blocks forever. C:\pg\pgsql>bin\pg_ctl.exe -D data -w start waiting for server to start.

Re: [GENERAL] How to install latest stable postgresql on Debian

How did you uninstall 8.4? From below it would seem it is still around. Thank you. After adding -t switch to apt-get I was able to install 9.1. To start it I invoked /etc/init.d/postgresql manually. How to force it to start after server is rebooted automatically ? free -g returns

Re: [GENERAL] Foreign Tables

Thank you for your response... > Foreign tables in 9.1 are read-only, so you can't write to them. Making > foreign tables writable is a TODO item, but ISTM it's difficult to > implement it for even 9.2. So the answer to your question 1a) is "No". > > BTW, I'm interested in your use case very mu

[GENERAL] Installed. Now what?

Hi. I use CentOS 5, 64bit. PG is 9.0.5. I did "yum install pgbouncer" and got this: --- Running Transaction Installing : libevent 1/2 Installing : pgbouncer 2/2 war