Re: [GENERAL] pg_tune replacement

On Sun, Jun 12, 2016 at 9:39 PM, Ivan Mincik wrote: > Hi all, > I am looking for some pg_tune replacement, which is working for latest > PostgreSQL versions. What I need is to have some tool, which can compute > some basic, reasonable configuration defaults for basic deployment > scenarios like pg

Re: [GENERAL] reltoastidxid altenates in postgresql 9.4

On Thu, Mar 5, 2015 at 2:02 AM, Yelai, Ramkumar IN BLR STS wrote: > Hi All, > > I am using the following code to know how much disk space could be saved > after deleting certain tables (as a parameter to this function ) > > CREATE OR REPLACE FUNCTION Get_Tables_Recovery_Size( IN tableNames text[]

Re: [GENERAL] Vacuum freeze

On Thu, Dec 4, 2014 at 12:02 PM, Mike Blackwell wrote: > check_postgres.pl (--action=autovac_freeze) recently complained that we > needed to run VACUUM FREEZE. Doing so generated a boatload of WAL files - > perhaps on the order of the of the database itself. > > Is VACUUM FREEZE something that is

Re: [GENERAL] Will pg_repack improve this query performance?

On Wed, Oct 15, 2014 at 5:03 AM, Alban Hertroys wrote: > A CLUSTER would help putting rows with the same to_id together. Disk access > would be less random that way, so it would help some. > > According to your query plan, accessing disks (assuming that’s what made the > difference) was 154 (770

Re: [GENERAL] Limit+Offset query wrong result in Postgres 9.0.3 ?

On Fri, Oct 12, 2012 at 3:33 AM, Alban Hertroys wrote: > On 12 October 2012 04:55, urkpostenardr wrote: >> Hi, >> >> Is this bug in Postgres ? >> If yes, is it fixed in latest release ? >> Second query should return 2 rows instead of 1 ? >> >> create table t(i int); >> insert into t values(1); >>

Re: [GENERAL] Curosity question regarding "LOCK" NOWAIT

On Fri, Sep 21, 2012 at 7:43 PM, David Johnston wrote: > Has there been any discussion regarding adding a time-limited version of > NOWAIT, say: “WAITONLY 50” (milliseconds), when dealing the explicit LOCK > TABLE or the SELECT…FOR(SHARE|UPDATE) commands? I think you could do this by issuing

Re: [GENERAL] when was a db/schema created ?

On Mon, Sep 17, 2012 at 2:16 PM, Gauthier, Dave wrote: > Is there a way to determine when a DB was created (create database...)? If you don't have the CREATE DATABASE statement in your logs, you could try SELECT oid FROM pg_database WHERE datname = 'name of your database'; Then check the crea

Re: [GENERAL] Python + listen/notify

[Please keep the list CC'd] On Wed, Jul 11, 2012 at 3:58 AM, Filipe brandão wrote: > Now, i only need to link these processes (trigger completion and running the > script). > How can i do it with Listen / Notify? Issue a notify in the end of the > trigger and a listen on the python script? Yes,

Re: [GENERAL] Python + listen/notify

On Tue, Jul 10, 2012 at 3:56 PM, Filipe Brandão wrote: > Hi all. > I needed to have a trigger firing after a commit, but allready realized it's > not possible. > As i searched upon a solution i came across the LISTEN / NOTIFY. I haven't > yet realized exactly what i can do with it, but can i use

Re: [GENERAL] TG_COLUMNS_UPDATED

On Tue, Jul 3, 2012 at 2:47 PM, Bartosz Dmytrak wrote: [snip] > FOR v_row IN > SELECT attname > FROM pg_attribute > WHERE attrelid = (quote_ident(TG_TABLE_SCHEMA) || '.' || > quote_ident(TG_TABLE_NAME))::text::regclass > AND attnum > 0 > ORDER BY attnum > LOOP > EXECUTE 'SELECT NOT ($1.' || quot

[GENERAL] psql pager, and searching for function arguments

Hi all, The tip beneath \df on psql's documentation page[0] advises the user: | Tip: To look up functions taking arguments or returning values | of a specific type, use your pager's search capability to scroll | through the \df output. (This tip was put in[1] as an alternative to having a real '

Re: [GENERAL] Need help in transferring FP to Int64 DateTime

On Wed, Jun 6, 2012 at 9:20 AM, Benson Jin wrote: > I am sure this question has been asked before, however, I failed to find any > related topics in the internet. We have a database about 100GB in size. It > was started back in 7.x days and has been upgraded along the way to 9.0. > Because of the

Re: [GENERAL] How to get user privs on a table using select

On Wed, Jun 6, 2012 at 1:41 PM, Gauthier, Dave wrote: > Inside a perl/dbi script, I want to determine which privs (select, insert, > update, delete, ...) user 'foo' has on table 'footable'.   There must be > metadata tables or a view that has this. Try: psql -E dbname \z tablename And look a

Re: [GENERAL] Why are pg_restore taking that long ?

On Sun, May 20, 2012 at 8:39 AM, Poul Møller Hansen wrote: >> Anyway, if you are seeing no activity at the end of the restore for quite >> a while you may want to see if large objects are the reason. >> > > The dump are from a version 9.0.7 and it's being restored in a version > 9.1.3. There are

Re: [GENERAL] Why are pg_restore taking that long ?

On Sat, May 19, 2012 at 10:57 PM, Bosco Rama wrote: > Hey Josh, > > I found the message I was seeing.  It was/is(?) in StartRestoreBlob() and it > looks like this: > >    ahlog(AH, 2, "restoring large object with OID %u\n", oid); > > But I don't know how to find it in the current git tree or how t

Re: [GENERAL] Why are pg_restore taking that long ?

On Fri, May 18, 2012 at 5:58 PM, Bosco Rama wrote: > The per-blob messages are missing whether we use the '-j2' or not and also > regardless of number of '-v's we use. > > I'm glad they've reinstated some of the messages.  Previously there was > a message per blob between the "restoring BLOBS" an

Re: [GENERAL] Why are pg_restore taking that long ?

On Fri, May 18, 2012 at 3:11 PM, Bosco Rama wrote: > One thing you may want to look at (if this is PG 8.4.x) is the number of > large objects in pg_largeobjects.  If your apps don't use large objects > this is not relevant.  If they do, then it may be.  I've noticed that > pg_restore no longer re

Re: [GENERAL] privs drop after alter table

On Thu, May 3, 2012 at 11:14 AM, Gauthier, Dave wrote: > v8.3.4 on linux > After this.. > > alter table fred alter column wilma set default 'dino'; > > ...it appeared that a user that had insert,delete,update privs lost them. > > I regranded and things went back to normal. > > Is it correct for th

Re: [GENERAL] How to get the all the activities running during specific time range.Thanks!Plese help!

On Fri, Apr 27, 2012 at 7:32 AM, leaf_yxj wrote: > I was asked to run a report to my boss all the activities running during a > specific time range. I want to create a script to run it. I know I can query > from  pg_stat_activity for my previous postgres version. But I can't find > this system tab

Re: [GENERAL] PASSWORD vs. md5('somepass')

On Tue, Mar 20, 2012 at 8:28 AM, Alexander Reichstadt wrote: > Hi, > > I look for a way to reproduce the encrypted string stored as a password by > means other than using the CREATE ROLE command. > > When using CREATE ROLEPASSWORD 'somepass' the resulting string for > rolpassword in pg_a

Re: [GENERAL] PG9.1.2 -- 3 day old orphaned/non-responsive query -- pg_(cancel)(terminate)_backend has no effect

On Fri, Jan 13, 2012 at 12:02 PM, Reid Thompson wrote: > The select query has been apparently orphaned by it's originating > terminal or script or application.  pg_/cancel/terminate/_backend both > return t, but the query does not cancel/terminate.   sigterm via the OS > has no effect either. Yo

Re: [GENERAL] Escaping input from COPY

On Tue, Dec 20, 2011 at 7:47 PM, Adrian Klaver wrote: > As far as I know you did not get an answer, which is not the same as there > being > no answer:) I think you will find that the escaping is handled for you. I am rather dubious of the claim that "escaping is handled for you" with copy_from(

Re: [GENERAL] Escaping input from COPY

On Mon, Dec 19, 2011 at 6:56 AM, Roger Leigh wrote: > I'd like to switch to COPY, which should be orders of > magnitude faster.  I see that DBD::Pg has an interface for > this, which looks just fine.  My problem is with how to > escape the data.  I need to use whatever escaping rules > are in use

Re: [GENERAL] LOCK DATABASE

On Thu, Dec 15, 2011 at 2:01 PM, Scott Marlowe wrote: > On Thu, Dec 15, 2011 at 10:17 AM, Eliot Gable > wrote: >> Is this bogus, or is it an upcoming feature? >> >> http://wiki.postgresql.org/wiki/Lock_database LOCK DATABASE was brought up earlier this year: http://archives.postgresql.org/pgsq

Re: [GENERAL] How to get Place Names from Lat Lon

On Thu, Dec 1, 2011 at 6:46 AM, Adarsh Sharma wrote: > I have a position table that contains the lat lon of an entity from time to > time. > Now I want to get the place names from the respective lat lon. You might want to try the PostGIS lists instead. Josh -- Sent via pgsql-general mailing l

Re: [GENERAL] Client SQL Tool

On Mon, Nov 14, 2011 at 10:01 AM, wrote: > Hi all, > > I’ve started a new codeplex project that could be interesting for you. It’s > a simple SQL client for postgreSQL databases. > You can found it here : http://postgresqlclient.codeplex.com/ > > I’ve started with this project, because i want to

Re: [GENERAL] psql HTML mode - quoting HTML characters

On Wed, Oct 26, 2011 at 3:51 PM, Nicholson, Brad (Toronto, ON, CA) wrote: > How do I quote characters like < and > in psql's HTML mode? >From a brief look at print_html_text() and html_escaped_print() in psql's print.c, I don't see any way to tell psql not to escape some block of text in HTML pri

Re: [GENERAL] Materialized views in Oracle

On Fri, Sep 23, 2011 at 3:03 AM, Mike Christensen wrote: > 1) Though I might have given the impression that a “manual complete > refresh” is not useful, I definitely see value in this especially for > data warehousing scenarios.  However, I’d almost want to call this a > “snapshot” and not a “mate

Re: [GENERAL] \d+ not showing TOAST table size?

On Tue, Sep 20, 2011 at 1:26 PM, Jon Nelson wrote: > I have a table with a fair bit of TOAST data in it. > I noticed that \d+ does /not/ include that information (but > pg_total_relation_size does). I assume by "\d+" you meant "\dt+" (\d+ doesn't show sizes at all). On version 9.0 and up, \dt+ us

Re: [GENERAL] What do you like to get ?

On Thu, Sep 15, 2011 at 12:16 PM, Adam Cornett wrote: > This sounds like something that should be in the Postgres wiki > (http://wiki.postgresql.org/wiki) not sure if there is a page (a quick > search didn't turn one up). > On Thu, Sep 15, 2011 at 7:29 AM, Marc Mamin wrote: >> I miss a discussio

Re: [GENERAL] wiki error

On Thu, Jun 23, 2011 at 8:48 PM, John R Pierce wrote: > the 2nd example on http://wiki.postgresql.org/wiki/Index_Maintenance >  doesn't work on 8.4 or 9.0 unless you add ::text to the arguments of the > two pg_relation_size calls near the beginning. > > I don't have wiki edit privs and don't see a

Re: [GENERAL] LOCK TABLE permission requirements

On Wed, Jun 29, 2011 at 7:48 AM, Florian Weimer wrote: > I've been looking around in the 9.0 documentation, but couldn't find the > permission requirements for LOCK TABLE (in particular, LOCK TABLE IN > SHARE MODE).  From the source, you need at least one of UPDATE, DELETE > or TRUNCATE. > > Is th

Re: [GENERAL] Executing \i of psql command using libpq library

On Tue, Jun 14, 2011 at 11:51 AM, Tom Lane wrote: > "Edmundo Robles L." writes: >> How can i get the same behavior of psql -c "\\i './a_lot_of_sentences'" bd >> user, using the libpq library??? > > libpq does not contain any such behavior, so you can't. You can take a look at process_file() in

Re: [GENERAL] maximum size limit for a query string?

On Tue, Jun 7, 2011 at 2:38 PM, Andreas Kretschmer wrote: > Yeah, i think, 16 MByte isn't the real limit, yes. And i've seen > queries larger than that limit, but i can't find the link, sorry. > (maybe depesz.com, dunno, can't remember, i'm sorry) The thread linked seems to mainly be talking abou

Re: [GENERAL] Queries Regarding Postgresql Replication

On Thu, May 5, 2011 at 7:33 AM, Nithya Rajendran wrote: [Disclaimer: I have minimal experience with hot standby, the below is just from reading the docs] > ===> How to find whether current postgres is running as master or slave? SELECT pg_is_in_recovery(); will tell you (should be false for the

Re: [GENERAL] Using bytea field...

On Tue, Mar 8, 2011 at 8:00 PM, David Johnston wrote: > You could avoid the synchronization issues by putting the hash in an > index...in theory...I'd wait for someone else to opine on that particular > option. Yes, a functional index on MD5(your_bytea_column) will work and is the way to go for

Re: [GENERAL] get referential values

On Wed, Jan 19, 2011 at 12:08 PM, Esmin Gracic wrote: > Knowing only tablename (schema_name.table_name) how to retrieve foreign keys > and related values. (using pg_catalog is preferred). I developing framework > for adobe flex (on php and postgresql). > Already figured out how to get primary key,

Re: [GENERAL] possible error in documentation for 9.1

On Wed, Jan 19, 2011 at 12:43 PM, Andreas Kretschmer wrote: > http://developer.postgresql.org/pgdocs/postgres/catalog-pg-class.html > > Column relpersistence, type bool, can contain values 'p','u' and 't'. > whot, a BOOL? FYI, this got fixed today already: commit 357faea82e93712937cd109b48ce0067

Re: [GENERAL] Automate Scripts to make for managing Large Database Servers.

On Wed, Dec 29, 2010 at 11:20 PM, Adarsh Sharma wrote: > Dear all, > > I am working on some common tasks that need to be performed on regular > intervals in a large Database Servers. I find below lists of tasks that need > to be performed and Please tell me if i missed some. > > 1. Tacking Daily,

Re: [GENERAL] Have any tricks not to recreate a standby server to switch to the former primary?

On Sat, Dec 18, 2010 at 10:23 PM, vvoody wrote: > I have two servers, one primary and one standby, which doing warm > standby. Every thing works fine at the beginning. The primary > generates the archive WAL log files and the standby fetchs them to > merge. > > Then, I want to let the standby beco

Re: [GENERAL] monitoring warm standby lag in 8.4?

On Fri, Dec 10, 2010 at 11:27 AM, Greg Sabino Mullane wrote: > Correct. But since we cannot connect to a database in recovery mode, > there are very few options to determine how far 'behind' it is. The > pg_controldata is what the check_postgres program uses. This offers a > rough check which is u

[GENERAL] monitoring warm standby lag in 8.4?

Hi all, I'm wondering if there's an accepted way to monitor a warm standby machine's lag in 8.4. The wiki[1] has a link[2] to a script which parses the output of pg_controldata, looking for a line like this: Time of latest checkpoint:Thu 09 Dec 2010 01:35:46 PM EST But I'm not sure

Re: [GENERAL] REINDEX requirement?

On Tue, Nov 9, 2010 at 4:26 AM, AI Rumman wrote: > How do I know that index require REINDEX? Well, the REINDEX page: gives a few examples of why you might need to reindex. I think the most common reason would probably be due to i

Re: [GENERAL] Old values in statement triggers?

2010/10/21 Grzegorz Jaśkiewicz : > OLD.column_name > NEW.column_name ? I believe OP is asking specifically about statement-level triggers. As the docs say: | Statement-level triggers do not currently have any way to | examine

Re: [GENERAL] pg_temp implicit search path: functions vs. tables

On Thu, Oct 21, 2010 at 12:47 AM, Tom Lane wrote: > Josh Kupershmidt writes: >> pg_temp is being implicitly included in the default search path when >> looking for tables, but not for functions. Is there a reason for this >> difference? > > Yes.  They used to be t

[GENERAL] pg_temp implicit search path: functions vs. tables

Hi all, I notice slightly different handling of the implicit search_path for temporary tables and temporary functions. Consider: (with a default search path): # SHOW search_path; search_path "$user",public (1 row) BEGIN; CREATE TABLE pg_temp.bar(); CREATE FUNCTION pg_t

Re: [GENERAL] Generate a dynamic sequence within a query

On Wed, Oct 20, 2010 at 6:22 PM, David Kerr wrote: > I know I've seen posts on how to do this, but i can't seem to find them. > > I've got a data set > > A, B > A, C > A, D > [...] > > and so on > > and i'd like to be able to wite a query that would result in > > 1,A,B > 2,A,C > 3,A,D > [...] > >

Re: [GENERAL] How to change pg_trigger query so that it works in 9.0 without pg_trigger.tgisconstraint does not exist error

2010/10/16 Andrus Moor : > In 9.0 query below returns error > > 7/42703:ERROR: column pg_trigger.tgisconstraint does not exist > > How to change it so that it works in all servers starting at 8.0 (or at > least > from 8.1) to 9.0 ? > Or if this is not possible how to fix it so that it works in 9 ?

Re: [GENERAL] How to search ignoring spaces and minus signs

2010/10/13 Andrus : > CHAR(20) columns in 8.4 database may contains spaces and - signs like > > 13-333-333 > 12 3-44 > 33 33 333 > 12345 > > User enters code to search without spaces and - signs, like 12344 > How to search for product code ignoring spaces and - signs? > > For example searching for

Re: [GENERAL] column-level update privs + lock table

On Thu, Oct 7, 2010 at 7:43 PM, Josh Kupershmidt wrote: > I noticed that granting a user column-level update privileges doesn't > allow that user to issue LOCK TABLE with any mode other than Access > Share. Anyone think this could be added as a TODO? Josh -- Sent via pgsql-g

[GENERAL] column-level update privs + lock table

Hi all, I noticed that granting a user column-level update privileges doesn't allow that user to issue LOCK TABLE with any mode other than Access Share. The documentation page for LOCK TABLE claims: "All other forms of LOCK require at least one of UPDATE, DELETE, or TRUNCATE privileges.", and I d

Re: [GENERAL] How do I write this query? Distinct, Group By, Order By?

On Wed, Oct 6, 2010 at 3:34 AM, Min Yin wrote: >  Hi Yes that works too. Many Thanks! > > Now as you have probably , what I really want to get the full record of the > user, which is in another table called users. The following query doesn't > seem to work > > select users.id, users.* from users j

Re: [GENERAL] How do I write this query? Distinct, Group By, Order By?

On Tue, Oct 5, 2010 at 10:26 PM, Min Yin wrote: >  Hi There, > > I have a table looks like this: > > (order_id, user_id, order_time) > > One user_id can have multiple orders with order_id as the primary key, now I > want to get a list of users, ordered by their latest order respectively, for > exa

Re: [GENERAL] Postgres, DB2 and Materialized Query Tables (MQTs - DB2s Materialized Views)

On Tue, Oct 5, 2010 at 3:28 PM, Sandeep Srinivasa wrote: > Below is an excerpt from a comment over at Reddit. It seemed quite > interesting to read about the difference in PG and DB2's query optimizer. The entire post makes it sound like the author believes that Postgres has Materialized Views bu

Re: [GENERAL] Listen and do something daemon

On Fri, Sep 24, 2010 at 9:42 PM, KM wrote: > Does this program already exist? > >   $ listen-and-do --database=foo --listen-for="somenotification" \ >      --then-do="some-script" > > It will daemonize itself, issue a 'LISTEN somenotification' on the > database foo, and on each NOTIFY will run som

Re: [GENERAL] funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone

On Fri, Sep 3, 2010 at 4:01 PM, Tom Lane wrote: > Interesting.  I can't imagine how you could have produced these with > plain COPY, since that would go through timestamptzin.  Was it by any > chance a binary COPY?  If so I could believe that funny timestamps could > get in.  Maybe some confusion

Re: [GENERAL] funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone

On Fri, Sep 3, 2010 at 3:40 PM, Tom Lane wrote: > Wow.  You must have gotten those with the help of some arithmetic, > because timestamptzin would never have produced them.  I found out I can > do > > regression=# select extract(epoch from ('2000-01-01 00:00:00'::timestamptz + > '0.00

Re: [GENERAL] funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone

On Fri, Sep 3, 2010 at 3:24 PM, Tom Lane wrote: > regression=# select extract(epoch from ts - '1999-12-31 > 19:00:00-05'::timestamptz) from t1; >      date_part > -- >  1.0761449337e-07 >                    0 > (2 rows) > > This timestamp (2000-01-01 00:00 GMT) is actually

Re: [GENERAL] funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone

On Fri, Sep 3, 2010 at 2:58 PM, Tom Lane wrote: > Is this installation using float or integer timestamps?  If the former, > it might be interesting to look at the subtraction result >        ts - '1999-12-31 19:00:00-05'::timestamptz > I'm thinking some of them might be different by submicrosecond

[GENERAL] funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone

Hi all, I've come across a puzzling situation with a table having a timestamp with time zone column. This column is full of values displaying exactly as '1999-12-31 19:00:00-05', but for some reason Postgres is treating some of these identical-seeming timestamps as being different. If I update al

Re: [GENERAL] Help with pgAndroid Contest

I tried it on my T-Mobile G1, stock 1.6 Android firmware. It downloaded and installed fine, but right after installation, I clicked to open and got an error box saying "... has encountered an unexpected error and closed". But pgQuilt did install successfully, and when I go to my menu of applicat

Re: [GENERAL] How to emulate password generation in PHP with PlpgSQL?

On Sun, Jun 13, 2010 at 8:45 AM, Andre Lopes wrote: > But I'am not getting how to generate the SALT. Can someone give me a clue on > how to do this. The salt() function you posted returns 10 random hexadecimal digits. You could mimic it with something like: SELECT substr(md5(RANDOM()::text), 0,

Re: [GENERAL] Cognitive dissonance

On Tue, Jun 8, 2010 at 5:04 AM, John Gage wrote: > I do suggest that a plain text file of the entire documentation be made part > of the documentation armamentarium. Not that I see a whole lot of utility in this endeavor, but it's possible to do a decent PDF to plain text conversion. I tried some

Re: [GENERAL] Poor query performance on one of two "like" databases in production.

On Fri, May 14, 2010 at 1:28 PM, wrote: > I did run an ANALYZE, then vacuum full, then another ANALYZE on all tables > involved in the query.  That didn't change things.  I compared QUERY TUNING > settings in both postgresql.conf files and they are identical.  There is a > difference in row c

Re: [GENERAL] Pulling data from a constraint def

On Thu, May 13, 2010 at 8:08 PM, Tom Lane wrote: > Well, the inability to change the list of values is certainly an > unpleasant limitation, but is it so fatal that we should hide the > feature from people who could possibly use it?  I think not. I happened upon this article relevant to the subje

Re: [GENERAL] Pulling data from a constraint def

On Wed, May 12, 2010 at 12:58 AM, David Fetter wrote: > If you've measured a performance issue for a table that tiny, it's a > bug that needs fixing in PostgreSQL.  What measurements have you done > so far? Just for fun, I tried it out myself. Here are the times I got on my modest laptop: CHECK

Re: [GENERAL] Pulling data from a constraint def

On Tue, May 11, 2010 at 9:38 PM, Gauthier, Dave wrote: > I have a constraint defined on a table > > > > constraint design_style_is_invalid check (design_style in > ('rls','sdp','rf','ssa','rom','rpt','analog','sdprpt','clkdist','global')), > > > > Is there a way to get the valid values in the

Re: [GENERAL] Lock table, best option?

On Sat, Apr 24, 2010 at 4:46 PM, Andre Lopes wrote: > I need to do a SELECT and an UPDATE, but I will have concurrent processes > doing the same task. > > How can I prevent that the concurrent task don't have the same results in > the SELECT? Locking a table? How can I do that? It sounds like you

Re: [GENERAL] How to terminate a query

On Wed, Apr 21, 2010 at 1:59 AM, A B wrote: >> Sometimes cancel_backend fails because there's no point where the code >> checks for a cancel. > > What would such a point in the code look like? For instance, if your backend is waiting on a socket call in libc, as in this thread: http://archives.po

Re: [GENERAL] psql's \d display of unique index vs. constraint

On Tue, Apr 13, 2010 at 4:53 PM, Scott Marlowe wrote: > Yeah, probably make it show up for \d+ or something. FWIW, I'm not religious about psql's formatting; I'd be happy with this information being displayed only for \d+, in whatever form makes folks happy. I unfortunately don't have much time

[GENERAL] psql's \d display of unique index vs. constraint

Hi all, I often come across tables with either a unique index or a unique constraint on them, and psql isn't helpful at showing the difference between the two. Normally, I don't care which is which, except for when I have to manually drop and recreate the index or constraint to speed up a bulk loa

Re: [GENERAL] [Solved] 8.3 Stats Collector Stuck at 100% CPU

On Thu, Apr 1, 2010 at 4:22 PM, Tom Lane wrote: > Do you have a copy of the 1.2GB file and would you be willing to send me > it if so?  There shouldn't be any especially private info in there, just > table OIDs and access counts.  (1.2GB would be a lot of data to mail but > I bet it gzips down to

Re: [GENERAL] [Solved] 8.3 Stats Collector Stuck at 100% CPU

> Hm.  It sounds like you are "leaking" stats collector table entries for > some reason.  It would be good to fix the underlying problem rather than > just resign yourself to a manual workaround.  Is there anything unusual > about your workload that might trigger this? Don't think the database set

[GENERAL] [Solved] 8.3 Stats Collector Stuck at 100% CPU

Hi all, I wanted to share the solution to $SUBJECT. I noticed that the stats collector process for one of our servers was pegged around 80-100% CPU usage, and looked to have been that way for several days or weeks. Server info: * Postgres 8.3.4 * Linux 2.6.18-92.1.13.el5 #1 SMP x86_64 * CentOS

Re: [GENERAL] stopping processes, preventing connections

> > >> Second, and the more complicated one - what do I do about rogue queries >> that are running when my process starts? Today we had a query that ran since >> yesterday. I called pg_cancel_backend() on it several times and waited for >> almost two hours - to no avail. Eventually I had to ask our

Re: [GENERAL] stopping processes, preventing connections

On Wed, Mar 3, 2010 at 8:31 AM, Herouth Maoz wrote: > > > First, the easy part - regarding allowing/disallowing queries. Is it > possible to GRANT or REVOKE access to tables based on the originating IP? > I'd suggest separating out access to your tables by roles, and then restricting those roles

Re: [GENERAL] Setting a table to be ignored by autovacuum

On Thu, Feb 18, 2010 at 12:37 PM, Richard Huxton wrote: > On 18/02/10 17:20, Chris Barnes wrote: > >> >> I'm trying to have this table ignored by the autovacuum process. >> >> It wasn't created with this in mind, hoping there is still a way? >> > > alter table schema.table SET (autovacuum_enable

Re: [GENERAL] Understanding pg_stat_user_indexes

On Fri, Feb 5, 2010 at 7:32 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > Josh Kupershmidt wrote: > > On Fri, Feb 5, 2010 at 12:02 PM, Steve Crawford < > scrawf...@pinpointresearch.com> wrote: > >> What causes an increment to idx_scan in pg_stat_

Re: [GENERAL] Understanding pg_stat_user_indexes

On Fri, Feb 5, 2010 at 12:02 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > What causes an increment to idx_scan in pg_stat_user_indexes? "Select" > queries only? When used to enforce column uniqueness? When used for > foreign-key constraints? > > The docs are a little sparse here:

[GENERAL] ERROR: relation xxx is still open (Re: Use Trigger to Remove Table ... )

On Mon, Feb 1, 2010 at 10:38 PM, Yan Cheng Cheok wrote: > May I know how I can use trigger technique, to remove the table itself, > when after delete operation, there is 0 row in the table? > > For the record, I think having a trigger drop a table automatically when it's empty is probably a bad i

Re: [GENERAL] 8.3 PL/pgSQL comparing arbitrary records

On Tue, Dec 15, 2009 at 1:23 AM, Pavel Stehule wrote: > Hello > > NEW and OLD is comparable in 8.4. In 8.3 and older you have to use > little bit different syntax > > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body > Thanks, Pavel! Thi

[GENERAL] 8.3 PL/pgSQL comparing arbitrary records

Hi all, Short version of my question: What's the best way to compare arbitrary records (OLD and NEW, in my case) using PL/pgSQL in Postgres 8.3, without knowing anything about the structure of the records? If the answer is to cast OLD and NEW to text, and then compare, as suggested in [1], what fa

Re: [GENERAL] Pgbench tool download

On Mon, Aug 17, 2009 at 11:56 AM, Chris Barnes wrote: > I am looking for pgbench. Is there a good source from which I can download > the most current version? If you installed from source, look under contrib for the pgbench subdirectory. If you installed from your OS's package repository, try look

Re: [GENERAL] Division by zero

On Fri, 31 Jul 2009 11:49:47 -0700 Jeff Davis wrote: > "Find all store locations which have not achieved an average sale price > of $100." > > SELECT store_name FROM sales WHERE totalsales/nsales < 100; > > The person running that query might be missing stores with no sales at > all, and they migh

Re: [GENERAL] PG equivalent of "mysqlhotcopy"?

> I know about pg_dumpall, which creates a humongous SQL file, but is > there something equivalent in the postgresql world, like a > "pgsqlhotcopy" which copies data folders in a similar way as > mysqlhotcopy? If you're lucky enough to be using a filesystem which supports atomic snapshotting of di