Re: [GENERAL] find a substring on a text (data type) column

Hello 2012/11/8 pantelis vlachos : > I was trying to find a substring on a text (data type) column like 'cat foo > dog ...'. > I use the query below > SELECT id FROM table WHERE name LIKE '% foo %'; > Sometimes the query return with nTuples=0 but there are matching rows. > On retry, the query retu

[GENERAL] find a substring on a text (data type) column

I was trying to find a substring on a text (data type) column like 'cat foo dog ...'. I use the query below SELECT id FROM table WHERE name LIKE '% foo %'; Sometimes the query return with nTuples=0 but there are matching rows. On retry, the query return with expected results. Any ideas; (postgres

[GENERAL] Use order by clause, got index scan involved

Hi all: What confused me is that: When I select data using order by clause, I got the following execution plan: postgres=# set session enable_indexscan=true; SET postgres=# explain SELECT * FROM pg_proc ORDER BY oid; QUERY PLAN ---

[GENERAL] How is execution plan cost calculated for index scan

Hi all: I want to see the explain plan for a simple query. My question is : How is the cost calculated? The cost parameter is: random_page_cost= 4 seq_page_cost = 1 cpu_tuple_cost =0.01 cpu_operator_cost =0.0025 And the table and its index physica

Re: [GENERAL] Does PostgreSQL have complete functional test cases?

2012/11/7 Bruce Momjian : > On Tue, Nov 6, 2012 at 09:24:19AM +0100, Pavel Stehule wrote: >> 2012/11/6 Tianyin Xu : >> > Thanks, Pavel! >> > >> > I see. So the regress test cases are the complete functional testing? Am I >> > right? >> >> yes > > Those tests are hardly "complete", as in testing ev

Re: [GENERAL] How to evaluate "explain analyze" correctly soon after "explain" for the same statement ?

Hi tom At frist I have thought that the database parsed my explain statement, so the pre-compiled execution plan will be re-used , which made the statement's second run quick. I think that what you said is right. Thank you 2012/11/7 Tom Lane > =?UTF-8?B?6auY5YGl?= writes: > > It might not be

Re: [GENERAL] Unexpectedly high disk space usage

On Wed, Nov 7, 2012 at 3:15 PM, Lists wrote: > On 11/07/2012 12:42 PM, Tom Lane wrote: >> >> So you've turned off autovacuum, and are carefully not vacuuming the >> system catalogs. That's your problem all right. Is there a >> particularly good reason why this script isn't a one-liner "VACUUM"?

Re: [GENERAL] Plug-pull testing worked, diskchecker.pl failed

On 11/7/2012 3:17 PM, Vick Khera wrote: My most recent big box(es) are built using all Intel 3xx series drives. Like you said, the 7xx series was way too expensive. I have to raise my hand to say that for us 710 series drives are an unbelievable bargain and we buy nothing else now for producti

Re: [GENERAL] Unexpectedly high disk space usage

On Wed, Nov 7, 2012 at 1:58 PM, Scott Marlowe wrote: > On Wed, Nov 7, 2012 at 1:42 PM, Tom Lane wrote: >> Lists writes: >> >>> ... because it >>> occasionally causes transactions and queries to hang when an update >>> causes a vacuum mid-day, effectively taking us offline randomly. >> >> I suspe

Re: [GENERAL] Unexpectedly high disk space usage

Jeff Janes writes: >> WHERE nspname NOT IN ('pg_catalog', 'information_schema') >> AND C.relkind <> 'i' >> AND nspname !~ '^pg_toast' > I question the wisdom of that where clause (from the wiki) > If the pg_catalog relations are big, then they are big and why > shouldn't they get reported as suc

Re: [GENERAL] Unexpectedly high disk space usage

On 11/7/12 3:58 PM, Jeff Janes wrote: WHERE nspname NOT IN ('pg_catalog', 'information_schema') I question the wisdom of that where clause (from the wiki) If the pg_catalog relations are big, then they are big and why shouldn't they get reported as such? And if they are not b

Re: [GENERAL] How to verify pg_dump files

On 11/07/2012 11:56 AM, Igor Neyman wrote: The only 100% fool-proof test would be to restore from your backup files. Regards, Igor Neyman Our internal process is to back up production databases regularly, and then use the backups offsite to populate copies of databases for developer use. This

Re: [GENERAL] Plug-pull testing worked, diskchecker.pl failed

On Wed, Nov 7, 2012 at 3:53 PM, Scott Marlowe wrote: > Is there a comprehensive list of drives that have been tested on the > wiki somewhere? Our current choices seem to be the Intel 3xx series > which STILL suffer from the "whoops I'm now an 8MB drive" bug and the > very expensive SLC 7xx series

Re: [GENERAL] Unexpectedly high disk space usage

On 11/07/2012 12:58 PM, Scott Marlowe wrote: My experience is that if autovac is causing problems with stalled queries etc you're either A: running ancient pg versions (pre 8.3), B: Running WAY too aggressive settings in autovac (100 threads, no nap time, cost limit of 10 etc.) or C: Your I

Re: [GENERAL] Unexpectedly high disk space usage

On 11/07/2012 12:42 PM, Tom Lane wrote: So you've turned off autovacuum, and are carefully not vacuuming the system catalogs. That's your problem all right. Is there a particularly good reason why this script isn't a one-liner "VACUUM"? Back in the 8.x days, we experienced "vacuum full analyz

Re: [GENERAL] Unique/Primary key not inherited in partition, workaround?

Le 2012-11-07 à 13:58, Nicholas Wilson a écrit : > Regarding the caveats here > http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS > > I am attempting to logically structure my location data. Say for example I > have cities/states/countries. I have objects that

Re: [GENERAL] Plug-pull testing worked, diskchecker.pl failed

On Wed, Nov 7, 2012 at 02:12:39PM -0700, Scott Marlowe wrote: > >> >> I don't have the specs to hand, but one of them is a Kingston drive. > >> >> Our local supplier is out of 320 series drives, so we were looking for > >> >> others; will check out the 710s. It's crazy that so few drives can > >>

Re: [GENERAL] Plug-pull testing worked, diskchecker.pl failed

On Wed, Nov 7, 2012 at 2:01 PM, Bruce Momjian wrote: > On Wed, Nov 7, 2012 at 01:53:47PM -0700, Scott Marlowe wrote: >> On Wed, Nov 7, 2012 at 11:59 AM, Bruce Momjian wrote: >> > On Sat, Oct 27, 2012 at 05:41:02PM +1100, Chris Angelico wrote: >> >> On Sat, Oct 27, 2012 at 4:26 PM, Greg Smith wr

Re: [GENERAL] Plug-pull testing worked, diskchecker.pl failed

On Wed, Nov 7, 2012 at 01:53:47PM -0700, Scott Marlowe wrote: > On Wed, Nov 7, 2012 at 11:59 AM, Bruce Momjian wrote: > > On Sat, Oct 27, 2012 at 05:41:02PM +1100, Chris Angelico wrote: > >> On Sat, Oct 27, 2012 at 4:26 PM, Greg Smith wrote: > >> > In general, through, diskchecker.pl is the more

Re: [GENERAL] Unexpectedly high disk space usage

On Wed, Nov 7, 2012 at 1:42 PM, Tom Lane wrote: > Lists writes: > >> ... because it >> occasionally causes transactions and queries to hang when an update >> causes a vacuum mid-day, effectively taking us offline randomly. > > I suspect this claim is based on ancient and no longer very relevant >

Re: [GENERAL] Unexpectedly high disk space usage

Hi Greg, I've added you to the cc list because I'm proposing to change some wiki content which you wrote On Wed, Nov 7, 2012 at 11:54 AM, Lists wrote: > On 11/07/2012 09:01 AM, Jeff Janes wrote: >> >> Ben, did you ever figure out where the space was going? > > > > Now, here's where it gets weird.

Re: [GENERAL] Plug-pull testing worked, diskchecker.pl failed

On Wed, Nov 7, 2012 at 11:59 AM, Bruce Momjian wrote: > On Sat, Oct 27, 2012 at 05:41:02PM +1100, Chris Angelico wrote: >> On Sat, Oct 27, 2012 at 4:26 PM, Greg Smith wrote: >> > In general, through, diskchecker.pl is the more sensitive test. If it >> > fails, storage is unreliable for PostgreSQ

[GENERAL] Unique/Primary key not inherited in partition, workaround?

Regarding the caveats here http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS I am attempting to logically structure my location data. Say for example I have cities/states/countries. I have objects that reference a location, but at any level. An object may referenc

Re: [GENERAL] How to verify pg_dump files

On Wed, Nov 7, 2012 at 10:11 AM, Raymond O'Donnell wrote: > On 07/11/2012 13:01, Gary wrote: > > Can anyone suggest how I could verify that the files created by pg_dump > > are "okay"? They are being created for backup purposes, and the last > > thing I want to do is find out that the backups the

Re: [GENERAL] Does PostgreSQL have complete functional test cases?

On Tue, Nov 6, 2012 at 09:24:19AM +0100, Pavel Stehule wrote: > 2012/11/6 Tianyin Xu : > > Thanks, Pavel! > > > > I see. So the regress test cases are the complete functional testing? Am I > > right? > > yes Those tests are hardly "complete", as in testing every possible input and output. --

Re: [GENERAL] Unexpectedly high disk space usage

Lists writes: > pg_catalog.pg_attribute | 36727480320 Ouch. > Our current process is that every night in the middle of the night, a > script connects to each database on each server and runs a query to get > all tables in each database and, for each, run > "V

Re: [GENERAL] How to verify pg_dump files

On 07/11/2012 18:57, Ryan Delaney wrote: > On Wed, Nov 7, 2012 at 10:11 AM, Raymond O'Donnell > wrote: > > On 07/11/2012 13:01, Gary wrote: > > Can anyone suggest how I could verify that the files created by > pg_dump > > are "okay"? They are being created for

Re: [GENERAL] Problem with streaming replication over SSL

Magnus Hagander wrote: I have streaming replication configured over SSL, and there seems to be a problem with SSL renegotiation. >> [...] After that, streaming replication reconnects and resumes working. Is this an oversight in the replication protocol, or is this work

Re: [GENERAL] Why PGDLLIMPORT is needed

On Mon, Oct 29, 2012 at 04:41:05PM +0800, Craig Ringer wrote: > On 10/29/2012 02:05 PM, 高健 wrote: > > On /src/include/storage/proc.h: > > > > I saw the following line: > > > > extern PGDLLIMPORT PGPROC *MyProc; > > > > I want to know why PGDLLIMPORT is used here? > > > > Does it mean: exten PGP

Re: [GENERAL] How to verify pg_dump files

> -Original Message- > From: Gary [mailto:listgj...@yahoo.co.uk] > Sent: Wednesday, November 07, 2012 8:02 AM > To: pgsql-general@postgresql.org > Subject: How to verify pg_dump files > > Can anyone suggest how I could verify that the files created by pg_dump > are "okay"? They are being

Re: [GENERAL] Unexpectedly high disk space usage

On 11/07/2012 09:01 AM, Jeff Janes wrote: Ben, did you ever figure out where the space was going? I think we've found where the space is going, but I still don't yet know how to resolve it. I modified your query thusly in order to get a total of space used, and got an answer that matches clos

Re: [GENERAL] Extra space when converting number with to_char

On Fri, Oct 26, 2012 at 04:11:42PM -0400, Samuel Gilbert wrote: > Thank you, it works. The documentation gave me the impression that the FM > modifier only applied to date/time since it was under "Usage notes for > date/time formatting:" Uh, I see: FM prefix fill mode (suppress

Re: [GENERAL] help with upgrade from 9.1 to 9.2

On Thu, Oct 25, 2012 at 02:39:09AM -0200, Aníbal Pacheco wrote: > I could after some work, what I want to ask now is this: > In the middle of the pg_restore process I had to stop it (^Z) and remove one > problematic and not needed database from the generated pg_upgrade_dump_db.sql > file and then c

Re: [GENERAL] 9.1 to 9.2 requires a dump/reload?

On Tue, Oct 23, 2012 at 09:41:20AM -0400, Nikolas Everett wrote: > On Mon, Oct 22, 2012 at 6:17 PM, Alan Hodgson wrote: > > On Monday, October 22, 2012 05:55:07 PM Nikolas Everett wrote: > > I see that pg_upgrade is an option. Having never used how long should I > > expect pg_upgrade

Re: [GENERAL] Plug-pull testing worked, diskchecker.pl failed

On Sat, Oct 27, 2012 at 05:41:02PM +1100, Chris Angelico wrote: > On Sat, Oct 27, 2012 at 4:26 PM, Greg Smith wrote: > > In general, through, diskchecker.pl is the more sensitive test. If it > > fails, storage is unreliable for PostgreSQL, period. It's good that you've > > followed up by confir

Re: [GENERAL] How to verify pg_dump files

On 07/11/2012 13:01, Gary wrote: > Can anyone suggest how I could verify that the files created by pg_dump > are "okay"? They are being created for backup purposes, and the last > thing I want to do is find out that the backups themselves are in some > way corrupt. > > I know I can check the outpu

Re: [GENERAL] Parallel Insert and Delete operation

Ramkumar Yelai wrote: [is worried that a database might become inconsistent if conflicting INSERTs and DELETEs occur] > @Albe - I got you first point. The second point is little skeptical because postgres could have been > avoided this lock by using MVCC. Please correct me if I am wrong? Which

Re: [GENERAL] Memory issue on FreeBSD

Vick, fantastic script, thanx! FreeBSD sysctl system is awesome! On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote: On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski wrote: and this is after a few hours of running: Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free Swap: 4

Re: [GENERAL] Postgres no longer starts

came across this problem myself. turned out after much playing around that it was a change to the pg_hba.conf was a syntax error in the all all posgres trust sameuser line. deleted it and postgres fired up from /etc/init.d or as a service. just my very late twopenneth -- View this mess

Re: [GENERAL] Unexpectedly high disk space usage

On Tue, Nov 6, 2012 at 11:28 AM, Tom Lane wrote: > Jeff Janes writes: >> On Tue, Nov 6, 2012 at 10:49 AM, Lists wrote: >>> I followed your example, the result is at the bottom. Based on this it would >>> seem that there are 3-4 databases that seem to be the culprit. How could I >>> get more dept

Re: [GENERAL] How to evaluate "explain analyze" correctly soon after "explain" for the same statement ?

=?UTF-8?B?6auY5YGl?= writes: > It might not be a big problem in a small system. > But when in a production environment, When I want to use explain and > then , soon use explain analyze for the same statement, > How can I avoid the influence of cache and get the right answer for > evaluating pur

Re: [GENERAL] How to evaluate "explain analyze" correctly soon after "explain" for the same statement ?

高健 wrote: > I have one question about the cache clearing. > > If I use the following soon after database startup(or first time I use it): > > postgres=# explain analyze select id,deptno from gaotab where id=200; > The result is: the above explain analyze got a total runtime of 47 ms. > > But If

Re: [GENERAL] Memory issue on FreeBSD

On Τετ 07 Νοε 2012 09:42:47 Frank Broniewski wrote: > Hey, this is really cool. I directly tried the script and there's a line > from the output that caught my eye: > > > mem_gap_vm: + 8812892160 ( 8404MB) [ 26%] Memory gap: UNKNOWN > > is this the shared buffers? I guess so, but I want to

[GENERAL] How to evaluate "explain analyze" correctly soon after "explain" for the same statement ?

Hi all: I have one question about the cache clearing. If I use the following soon after database startup(or first time I use it): postgres=# explain analyze select id,deptno from gaotab where id=200; QUERY PLAN

Re: [GENERAL] Memory issue on FreeBSD

Hey, this is really cool. I directly tried the script and there's a line from the output that caught my eye: > mem_gap_vm: + 8812892160 ( 8404MB) [ 26%] Memory gap: UNKNOWN is this the shared buffers? I guess so, but I want to confirm my guess ... Frank Am 2012-11-07 09:26, schrieb Achil

Re: [GENERAL] Memory issue on FreeBSD

Vick, fantastic script, thanx! FreeBSD sysctl system is awesome! On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote: On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski wrote: and this is after a few hours of running: Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free Swap: 4

Re: [GENERAL] Comparing txid_current() to xmin

On Tue, Nov 06, 2012 at 02:55:40PM -0800, Mike Lewis wrote: > I am trying to make a trigger that updates a row once and only once per > transaction (even if this trigger gets fired multiple times). The general > idea is that for a user we have a version number. When we modify the > user's data, t