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

Re: [GENERAL] reltoastidxid altenates in postgresql 9.4

On Thu, Mar 5, 2015 at 2:02 AM, Yelai, Ramkumar IN BLR STS ramkumar.ye...@siemens.com 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(

Re: [GENERAL] Vacuum freeze

On Thu, Dec 4, 2014 at 12:02 PM, Mike Blackwell mike.blackw...@rrd.com 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

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

On Wed, Oct 15, 2014 at 5:03 AM, Alban Hertroys haram...@gmail.com 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

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

On Fri, Oct 12, 2012 at 3:33 AM, Alban Hertroys haram...@gmail.com wrote: On 12 October 2012 04:55, urkpostenardr urkpostena...@gmail.com 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);

Re: [GENERAL] Curosity question regarding LOCK NOWAIT

On Fri, Sep 21, 2012 at 7:43 PM, David Johnston pol...@yahoo.com 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

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

On Mon, Sep 17, 2012 at 2:16 PM, Gauthier, Dave dave.gauth...@intel.com 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

Re: [GENERAL] Python + listen/notify

[Please keep the list CC'd] On Wed, Jul 11, 2012 at 3:58 AM, Filipe brandão fg.bran...@gmail.com 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

Re: [GENERAL] Python + listen/notify

On Tue, Jul 10, 2012 at 3:56 PM, Filipe Brandão fg.bran...@gmail.com 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

Re: [GENERAL] TG_COLUMNS_UPDATED

On Tue, Jul 3, 2012 at 2:47 PM, Bartosz Dmytrak bdmyt...@gmail.com 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

[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] How to get user privs on a table using select

On Wed, Jun 6, 2012 at 1:41 PM, Gauthier, Dave dave.gauth...@intel.com 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

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

On Wed, Jun 6, 2012 at 9:20 AM, Benson Jin benson@troo.com 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.

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

On Sat, May 19, 2012 at 10:57 PM, Bosco Rama postg...@boscorama.com 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

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

On Sun, May 20, 2012 at 8:39 AM, Poul Møller Hansen free...@pbnet.dk 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.

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

On Fri, May 18, 2012 at 5:58 PM, Bosco Rama postg...@boscorama.com 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

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

On Fri, May 18, 2012 at 3:11 PM, Bosco Rama postg...@boscorama.com 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

Re: [GENERAL] privs drop after alter table

On Thu, May 3, 2012 at 11:14 AM, Gauthier, Dave dave.gauth...@intel.com 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

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 leaf_...@163.com 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

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

On Tue, Mar 20, 2012 at 8:28 AM, Alexander Reichstadt l...@mac.com 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

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 reid.thomp...@ateb.com 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

Re: [GENERAL] Escaping input from COPY

On Tue, Dec 20, 2011 at 7:47 PM, Adrian Klaver adrian.kla...@gmail.com 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

Re: [GENERAL] Escaping input from COPY

On Mon, Dec 19, 2011 at 6:56 AM, Roger Leigh rle...@codelibre.net 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

Re: [GENERAL] LOCK DATABASE

On Thu, Dec 15, 2011 at 2:01 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Dec 15, 2011 at 10:17 AM, Eliot Gable egable+pgsql-gene...@gmail.com wrote: Is this bogus, or is it an upcoming feature? http://wiki.postgresql.org/wiki/Lock_database LOCK DATABASE was brought up earlier

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

On Thu, Dec 1, 2011 at 6:46 AM, Adarsh Sharma adarsh.sha...@orkash.com 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

Re: [GENERAL] Client SQL Tool

On Mon, Nov 14, 2011 at 10:01 AM, bjo...@hillebrandar.de 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,

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

On Wed, Oct 26, 2011 at 3:51 PM, Nicholson, Brad (Toronto, ON, CA) bnichol...@hp.com 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

Re: [GENERAL] Materialized views in Oracle

On Fri, Sep 23, 2011 at 3:03 AM, Mike Christensen m...@kitchenpc.com 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”

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

On Tue, Sep 20, 2011 at 1:26 PM, Jon Nelson jnelson+pg...@jamponi.net 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

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

On Thu, Sep 15, 2011 at 12:16 PM, Adam Cornett adam.corn...@gmail.com 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

Re: [GENERAL] wiki error

On Thu, Jun 23, 2011 at 8:48 PM, John R Pierce pie...@hogranch.com 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

Re: [GENERAL] LOCK TABLE permission requirements

On Wed, Jun 29, 2011 at 7:48 AM, Florian Weimer fwei...@bfk.de 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

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

On Tue, Jun 14, 2011 at 11:51 AM, Tom Lane t...@sss.pgh.pa.us wrote: Edmundo Robles L. erob...@sensacd.com.mx 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

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

On Tue, Jun 7, 2011 at 2:38 PM, Andreas Kretschmer akretsch...@spamfence.net 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

Re: [GENERAL] Queries Regarding Postgresql Replication

On Thu, May 5, 2011 at 7:33 AM, Nithya Rajendran r-nit...@hcl.com 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

Re: [GENERAL] Using bytea field...

On Tue, Mar 8, 2011 at 8:00 PM, David Johnston pol...@yahoo.com 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

Re: [GENERAL] possible error in documentation for 9.1

On Wed, Jan 19, 2011 at 12:43 PM, Andreas Kretschmer akretsch...@spamfence.net 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

Re: [GENERAL] get referential values

On Wed, Jan 19, 2011 at 12:08 PM, Esmin Gracic esmin.gra...@gmail.com 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

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

On Wed, Dec 29, 2010 at 11:20 PM, Adarsh Sharma adarsh.sha...@orkash.com 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.

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 wxj.g...@gmail.com 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

[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

Re: [GENERAL] REINDEX requirement?

On Tue, Nov 9, 2010 at 4:26 AM, AI Rumman rumman...@gmail.com wrote: How do I know that index require REINDEX? Well, the REINDEX page: http://www.postgresql.org/docs/current/static/sql-reindex.html gives a few examples of why you might need to reindex. I think the most common reason would

Re: [GENERAL] Old values in statement triggers?

2010/10/21 Grzegorz Jaśkiewicz gryz...@gmail.com: OLD.column_name NEW.column_name ? I believe OP is asking specifically about statement-level triggers. As the docs http://www.postgresql.org/docs/current/static/trigger-definition.html say: | Statement-level triggers do not currently have any

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

On Wed, Oct 20, 2010 at 6:22 PM, David Kerr d...@mr-paradox.net 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 [...]

[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

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

On Thu, Oct 21, 2010 at 12:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Kupershmidt schmi...@gmail.com 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

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 eetas...@online.ee: 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

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

2010/10/13 Andrus kobrule...@hot.ee: 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

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

On Thu, Oct 7, 2010 at 7:43 PM, Josh Kupershmidt schmi...@gmail.com 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

[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

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

On Wed, Oct 6, 2010 at 3:34 AM, Min Yin y...@ai.sri.com 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.*

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

On Tue, Oct 5, 2010 at 3:28 PM, Sandeep Srinivasa s...@clearsenses.com 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

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

On Tue, Oct 5, 2010 at 10:26 PM, Min Yin y...@ai.sri.com 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,

Re: [GENERAL] Listen and do something daemon

On Fri, Sep 24, 2010 at 9:42 PM, KM k...@xacrasis.netx 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

[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

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 t...@sss.pgh.pa.us 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

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 t...@sss.pgh.pa.us 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)

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 t...@sss.pgh.pa.us 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 +

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 t...@sss.pgh.pa.us 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

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

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

On Sun, Jun 13, 2010 at 8:45 AM, Andre Lopes lopes80an...@gmail.com 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

Re: [GENERAL] Cognitive dissonance

On Tue, Jun 8, 2010 at 5:04 AM, John Gage jsmg...@numericable.fr 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

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

On Fri, May 14, 2010 at 1:28 PM, keaton_ad...@mcafee.com 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

Re: [GENERAL] Pulling data from a constraint def

On Thu, May 13, 2010 at 8:08 PM, Tom Lane t...@sss.pgh.pa.us 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

Re: [GENERAL] Pulling data from a constraint def

On Wed, May 12, 2010 at 12:58 AM, David Fetter da...@fetter.org 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

Re: [GENERAL] Pulling data from a constraint def

On Tue, May 11, 2010 at 9:38 PM, Gauthier, Dave dave.gauth...@intel.com 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

Re: [GENERAL] Lock table, best option?

On Sat, Apr 24, 2010 at 4:46 PM, Andre Lopes lopes80an...@gmail.com 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?

Re: [GENERAL] How to terminate a query

On Wed, Apr 21, 2010 at 1:59 AM, A B gentosa...@gmail.com 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:

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

On Tue, Apr 13, 2010 at 4:53 PM, Scott Marlowe scott.marl...@gmail.com 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

[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

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 setup is

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

On Thu, Apr 1, 2010 at 4:22 PM, Tom Lane t...@sss.pgh.pa.us 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

[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

On Wed, Mar 3, 2010 at 8:31 AM, Herouth Maoz hero...@unicell.co.il 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

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 sysadmin

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

On Thu, Feb 18, 2010 at 12:37 PM, Richard Huxton d...@archonet.com 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

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:

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_user_indexes? Select queries only? When used

[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 ycch...@yahoo.com 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

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

On Tue, Dec 15, 2009 at 1:23 AM, Pavel Stehule pavel.steh...@gmail.com 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

[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

Re: [GENERAL] Pgbench tool download

On Mon, Aug 17, 2009 at 11:56 AM, Chris Barnescompuguruchrisbar...@hotmail.com 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

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 might prefer

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