[GENERAL] Run statements before pg_dump in same transaction?

Hi all! For development purposes, we dump the production database to local. It's fine because the DB is small enough. The company's growing and we want to reduce risks. To that end, we'd like to anonymize the data before it leaves the database server. One solution we thought of would be to run

Re: [GENERAL] Partitioning

all the 2nd query with those values hard-coded in the query. If necessary, and you know where each partition starts, tou could sort in the app and query the correct ranges, in multiple queries. Hope that helps! François Beausoleil

Re: [GENERAL] How to individually list the DDL for all individual data base objects

Le 2014-11-24 à 10:14, Berend Tober a écrit : > Is there a good way to individually list the DDL for all individual data base > objects? > > grep -v '^;' listfile | while read a b c n >do > a=${a/;} > echo $a > f > pg_restore -L f -f outputdir/$a dumpfile >done > > T

Re: [GENERAL] How to get good performance for very large lists/sets?

lect * from … work better? The plan I get for SELECT EXCEPT SELECT ends up with a SetOp Except, while the SELECT WHERE exists() AND NOT exists() plan gives me a Nested Loop Semi Join. The in / not in case gives me a simple Hash Join. My dataset is a parent table partitioned by market and week,

[GENERAL] Is there a way to get both the plan and the results?

+query+plan+in+single+call doesn’t return anything useful. Google neither, for that matter. The PG mailing list archives seem to not have any results on that. I’m specifically using 9.1. Not opposed to upgrade if necessary. Thanks! François Beausoleil -- Sent via pgsql-general mailing list

Re: [GENERAL] Employee modeling question

ersal Person and Organization Data Model » very useful to understand complex questions like that: http://www.tdan.com/view-articles/5014 Hope this helps, François Beausoleil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Disk space available, but getting error "could not write to hash-join temporary file: No space left on device"

> Le 2014-08-10 à 06:31, Seref Arikan a écrit : > > Greetings, > A function aborts with the error in the subject line despite the available > free space on the disk. > I have a bunch of other questions regarding the underlying reason but first > things first: why is pg telling me no space is

Re: [GENERAL] Pairwise array sum aggregate function?

Le 2014-07-28 à 10:58, David G Johnston a écrit : > François Beausoleil wrote >> Hi all, >> >> NOTE: Also posted to StackOverflow: >> http://stackoverflow.com/questions/24997131/pairwise-array-sum-aggregate-function >> >> I have a table with arrays as

[GENERAL] Pairwise array sum aggregate function?

Hi all, NOTE: Also posted to StackOverflow: http://stackoverflow.com/questions/24997131/pairwise-array-sum-aggregate-function I have a table with arrays as one column, and I want to sum the array elements together: > create table regres(a int[] not null); > insert into regres values ('{1,2,3}'

Re: [GENERAL] How can I group all children by their parent ?

3 ORDER BY parent_id, id ? François Beausoleil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index contains unexpected zero page at block error

Le 2014-07-04 à 14:42, François Beausoleil a écrit : > Hi, > > I ran a query on the slave and got the error message in the subject. I > rebuilt the slave two days ago on ZFS (with compression). I’m wondering how > that unexpected zero page happened in the first place. >

[GENERAL] index contains unexpected zero page at block error

Hi, I ran a query on the slave and got the error message in the subject. I rebuilt the slave two days ago on ZFS (with compression). I’m wondering how that unexpected zero page happened in the first place. Master: Linux kalanchoe 3.2.0-58-generic #88-Ubuntu SMP Tue Dec 3 17:37:58 UTC 2013 x86_

Re: [GENERAL] How to access array element in pgsql after array_agg

Le 2014-06-17 à 14:22, Condor a écrit : > I do select array_agg(ids) from x; > in ids I have int and result is : > > array_agg > - >{3843,2,3543,33} > > I want to access one element or first one direct in sql query like: > > select array_agg(ids)[1] from x; > > and

Re: [GENERAL] Best backup strategy for production systems

WAL archives. WAL archives compress very well: 16MB to 4MB is very typical on my system. * My database is too big to do pg_dump (3 TiB), so I dont, but I have weekly base backups, plus the WAL archives which I keep for three weeks. Hope that helps! François Beausoleil -- Sent via pgsql-gen

Re: [GENERAL] Linux vs FreeBSD

Le 2014-04-09 à 16:20, Bruce Momjian a écrit : > On Wed, Apr 9, 2014 at 10:02:07AM -0500, Christofer C. Bell wrote: > > This highlights a more fundamental problem of the difference between a > workstation-based on OS like Ubuntu and a server-based one like Debian > or FreeBSD. I know Ubuntu ha

Re: [GENERAL] Linux vs FreeBSD

Le 2014-04-04 à 08:11, Ray Stell a écrit : > > On Apr 4, 2014, at 12:03 AM, François Beausoleil wrote: > >> I have some performance issues on a Ubuntu 12.04 which I'd like to resolve. >> iowait varies a lot, between 5 and 50%. > > Is the SAN dedicated to th

[GENERAL] Linux vs FreeBSD

27;ve read about problems with certain kernel versions on Ubuntu. I'm not expecting anything magical, just some general guidelines and hints. Did anybody do the migration and was happier after? Thanks for any hints! François Beausoleil $ uname -a Linux munn.ca.seevibes.com 3.2.0-58-generic

Re: [GENERAL] Table Vacuum Taking a Long Time

tions, and then when a partition is untouched, the vacuum daemon should not touch the table. Please provide your exact PostgreSQL version, RAM, disk and other details, relevant postgresql.conf parameters so that we may help more. Cheers! François Beausoleil Seevibes smime.p7s Description: S/MIME cryptographic signature

Re: [GENERAL] How to access NEW or OLD field given only the field's name?

Le 2014-03-19 à 19:48, Adrian Klaver a écrit : > On 03/19/2014 02:01 PM, François Beausoleil wrote: > > Some experimenting showed that NEW.* does not work. So plan B: > > > EXECUTE 'SELECT $1.' || TG_ARGV[0] || INTO total USING NEW; > RAISE EXCEPTION 'Tot

Re: [GENERAL] How to access NEW or OLD field given only the field's name?

Hi, Le 2014-03-19 à 16:19, Adrian Klaver a écrit : > On 03/19/2014 12:48 PM, François Beausoleil wrote: >> >> Cross-posted from >> https://dba.stackexchange.com/questions/61271/how-to-access-new-or-old-field-given-only-the-fields-name >> > > > Well two

[GENERAL] How to access NEW or OLD field given only the field's name?

t', 'impressions_graph'); I tried http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN by doing EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0], but PL/PGsql complains that NEW is an unknown relation. I am specifically tar

Re: [GENERAL] Role Inheritance Without Explicit Naming?

Le 2014-03-03 à 10:53, Adrian Klaver a écrit : > On 03/02/2014 08:48 PM, François Beausoleil wrote: >> Hi all, >> >> I have four roles involved: >> >> meetphil - the database owner, should not login >> mpwebui - the role the web application logs in as,

[GENERAL] Role Inheritance Without Explicit Naming?

es/d/d1/Managing_rights_in_postgresql.pdf which talks a bit about inheritance, but I believe I have the same setup, but I must be wrong. I feel I'm pretty close, but the answer eludes me. It must be something basic. Can anyone spot it? Thanks! François Beausoleil -- In a fresh cluste

Re: [GENERAL] DB Authentication Design

Hello Chris, Le 2014-01-12 à 23:24, Chris Travers a écrit : > > On Sun, Jan 12, 2014 at 6:30 AM, François Beausoleil > wrote: > Hi all, > > I'm thinking that all apps that connect to the database should have their own > user. For example, the web application p

[GENERAL] DB Authentication Design

Hi all, I'm thinking that all apps that connect to the database should have their own user. For example, the web application process is one user, then a report builder process should have another user, and a different process that imports data should have his own too, and so on. Would you gener

Re: [GENERAL] pg_stop_backup running for 10h?

Le 2014-01-12 à 05:38, Magnus Hagander a écrit : > > On Sat, Jan 11, 2014 at 11:26 PM, François Beausoleil > wrote: > Hi all, > > I'm using OmniPITR to build a new slave. According to pg_stat_activity, > pg_stop_backup has been running for nearly 11 hours. The

[GENERAL] pg_stop_backup running for 10h?

Hi all, I'm using OmniPITR to build a new slave. According to pg_stat_activity, pg_stop_backup has been running for nearly 11 hours. The WAL archive command is running just fine and reporting "Segment X successfully sent to all destinations". I had the same issue almost a year ago (http://www

Re: [GENERAL] Deduplication and transaction isolation level

Le 2013-09-25 à 09:04, Merlin Moncure a écrit : > On Tue, Sep 24, 2013 at 10:19 PM, François Beausoleil > wrote: >> Hi all! >> >> I import many, many rows of data into a table, from three or more computers, >> 4 times per hour. I have a primary key, and the quer

[GENERAL] Deduplication and transaction isolation level

s serially through PostgreSQL? Thanks! François Beausoleil Table "public.show_interaction_bindings_2013_09_23" Column|Type

[GENERAL] Query plan for currently executing query?

plan isn't bogus. I'm doing batch imports of data on "PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit". Thanks, François Beausoleil smime.p7s Description: S/MIME cryptographic signature

Re: [GENERAL] Constraint exclusion and overlapping range checks

Le 2013-09-07 à 00:29, Steve Atkins a écrit : > If I have a partitioned table that has some range constraints that look kinda > like they're intended for constraint exclusion, but aren't quite > non-overlapping, will that break anything? > > e.g. > > create table jan ( …, check(created >= '2

Re: [GENERAL] Difference between terminate/cancel?

Le 2013-08-13 à 13:25, Kevin Grittner a écrit : > François Beausoleil wrote: > >> What is the difference between both? > >> I had some processes stuck in IDLE in transaction after a machine >> died (through pgbouncer), and cancel did not close the >> connecti

[GENERAL] Difference between terminate/cancel?

Hi! What is the difference between both? cancel sends QUIT while terminate sends TERM, but what effect does this have on the backends, shared memory, etc? I had some processes stuck in IDLE in transaction after a machine died (through pgbouncer), and cancel did not close the connections, while

[GENERAL] Getting permission denied after grant

n svanalytics creates a table in public, grant select to dataanalysts". Did I miss anything? What did I do wrong? Why can't a dataanalyst view a table's contents? Thanks, François Beausoleil smime.p7s Description: S/MIME cryptographic signature

Re: [GENERAL] Slave promotion failure

Le 2013-06-07 à 12:00, François Beausoleil a écrit : > > Le 2013-06-07 à 07:00, François Beausoleil a écrit : > >> >> Le 2013-06-06 à 18:40, Michael Paquier a écrit : >> >>> On Fri, Jun 7, 2013 at 1:37 AM, François Beausoleil >>>

Re: [GENERAL] Slave promotion failure

Le 2013-06-07 à 07:00, François Beausoleil a écrit : > > Le 2013-06-06 à 18:40, Michael Paquier a écrit : > >> On Fri, Jun 7, 2013 at 1:37 AM, François Beausoleil >> wrote: >> I can't seem to promote the slave: >> >> $ sudo -u postgres touc

Re: [GENERAL] User postgres unable to revoke privileges?

Le 2013-06-06 à 17:59, Tom Lane a écrit : > =?iso-8859-1?Q?Fran=E7ois_Beausoleil?= writes: >> regress=# ALTER DEFAULT PRIVILEGES FOR ROLE dataanalysts IN SCHEMA public >> REVOKE SELECT ON TABLES FROM dataanalysts; >> ERROR: permission denied for schema public > >> I'm logged in as postgres, t

Re: [GENERAL] Slave promotion failure

Le 2013-06-06 à 18:40, Michael Paquier a écrit : > On Fri, Jun 7, 2013 at 1:37 AM, François Beausoleil > wrote: > I can't seem to promote the slave: > > $ sudo -u postgres touch /var/lib/postgresql/9.1/main/recovery.done > # log is silent > This has no effect. r

[GENERAL] Slave promotion failure

Someone has an idea? I did read http://www.postgresql.org/docs/9.1/static/warm-standby-failover.html very carefully. I believe I did everything I was supposed to do. Thanks, François Beausoleil smime.p7s Description: S/MIME cryptographic signature

[GENERAL] User postgres unable to revoke privileges?

dataanalysts=r/svanalytics (3 rows) regress=# ALTER DEFAULT PRIVILEGES FOR ROLE dataanalysts IN SCHEMA public REVOKE SELECT ON TABLES FROM dataanalysts; ERROR: permission denied for schema public I'm logged in as postgres, the database superuser. Why am I getting a permission denied? Thanks!

[GENERAL] DELETE or TRUNCATE?

Hi! I have a process that replaces the contents of a table. The canonical data store is somewhere else. At the moment, the import looks like this: CREATE TEMPORARY TABLE markets_import( LIKE x INCLUDING ALL ); COPY markets_import FROM STDIN; ... \. -- COPY a bunch of other tables BEGIN; TRUNCA

Re: [GENERAL] Most efficient way to insert without duplicates

Hi! Le 2013-04-17 à 14:15, Jeff Janes a écrit : > On Wed, Apr 17, 2013 at 4:26 AM, François Beausoleil > wrote: > > > Insert on public.persona_followers (cost=139261.12..20483497.65 > rows=6256498 width=16) (actual time=4729255.535..4729255.535 rows=0 loops=1) >

[GENERAL] CLUSTER, REINDEX and VACUUM on batch ops

e table, must I reindex and vacuum as well? It is unclear to me if clustering a table reindexes or not: the docs at http://www.postgresql.org/docs/current/static/sql-cluster.html are silent on the matter, but do mention that an ANALYZE is in order. Thanks! François Beausoleil smime.p7s Desc

Re: [GENERAL] pg_stop_backup running for 2h10m?

Le 2013-04-24 à 09:15, hubert depesz lubaczewski a écrit : > On Tue, Apr 23, 2013 at 03:08:52PM -0400, François Beausoleil wrote: >> I used omnipitr to launch a base backup, but I fumbled a couple of >> things, so I Ctrl+C'd *once* the console where I had >> omni

Re: [GENERAL] pg_stop_backup running for 2h10m?

Le 2013-04-23 à 16:25, Kevin Grittner a écrit : > François Beausoleil wrote: > >> xlogs aren't being added to the backup directory. > > Any clue in the server log why that is? I checked but didn't mention it. I ended up calling pg_cancel_backend() on the proc

Re: [GENERAL] pg_stop_backup running for 2h10m?

Le 2013-04-23 à 15:08, François Beausoleil a écrit : > I used omnipitr to launch a base backup, but I fumbled a couple of things, so > I Ctrl+C'd *once* the console where I had omnipitr-backup-master running. > omnipitr-backup-master correctly launched pg_stop_backup, but pg_stop

[GENERAL] pg_stop_backup running for 2h10m?

ed by pg_stat_activity. According to the docs, pg_stop_backup wants to archive all xlogs before returning, but xlogs aren't being added to the backup directory. What could be preventing pg_stop_backup from returning? Thanks! François Beausoleil smime.p7s Description: S/MIME cryptographic signature

Re: [GENERAL] PQgetCopyData() failed - corruption?

isconnect, since the dump that I started on the database server itself is still progressing nicely and does not appear to have problems. Thanks for checking this out, François Beausoleil smime.p7s Description: S/MIME cryptographic signature

[GENERAL] PQgetCopyData() failed - corruption?

list do not have a reference to "PGgetCopyData", nor StackOverflow, dba.stackexchange or ServerFault. Does that error indicate a database corruption? Or just a network interruption? Thanks! François Beausoleil smime.p7s Description: S/MIME cryptographic signature

Re: [GENERAL] Most efficient way to insert without duplicates

Le 2013-04-17 à 14:15, Jeff Janes a écrit : > On Wed, Apr 17, 2013 at 4:26 AM, François Beausoleil > wrote: > > > Insert on public.persona_followers (cost=139261.12..20483497.65 > rows=6256498 width=16) (actual time=4729255.535..4729255.535 rows=0 loops=1) >

Re: [GENERAL] Most efficient way to insert without duplicates

Le 2013-04-16 à 22:51, François Beausoleil a écrit : > Hi all! > > I track Twitter followers in my database. I have the following table: > > # \d persona_followers > Table "public.persona_followers" > Column|

[GENERAL] Most efficient way to insert without duplicates

Hi all! I track Twitter followers in my database. I have the following table: # \d persona_followers Table "public.persona_followers" Column|Type | Modifiers -+-+--- service_id | bigint

Re: [GENERAL] PostgreSQL 9.1.8 Upgrade to 9.1.9

t;Upgrading a PostgreSQL Cluster" page in the docs http://www.postgresql.org/docs/current/static/upgrading.html has indications on when data upgrades are required. Welcome to PostgreSQL! François Beausoleil smime.p7s Description: S/MIME cryptographic signature

Re: [GENERAL] PostgreSQL Backup Booklet

p-Restore-ebook/dp/B00C26J204 > > I've never ordered a kindle eBook since I prefer epub, but there ya go. Maybe > it takes a while to associate them. 4.90 $CDN Thanks for that! François Beausoleil smime.p7s Description: S/MIME cryptographic signature

Re: [GENERAL] DB design advice: lots of small tables?

ts, without fear of colliding with anything else. It's not so much a problem on small lookup tables, but on larger entities (people, companies, etc), referencing through the ID is much, much easier. Hope that helps! François Beausoleil smime.p7s Description: S/MIME cryptographic signature

[GENERAL] Making planner skip hard-coded view values?

absent, since the view specifies the value that's present. Did that change in 9.2 / 9.3? Is this behavior expected? Bye! François Beausoleil $ psql regression create table aired(n int primary key); create table unaired(n int primary key); insert into aired select * from generate_series(1, 10

Re: [GENERAL] round returns -0

en.wikipedia.org/wiki/Signed_zero#Comparisons According to that article, IEEE 754 specifies that 0 == -0 in Java/C/etc. Hope that helps! François Beausoleil smime.p7s Description: S/MIME cryptographic signature

Re: [GENERAL] What could cause a temp table to disappear?

Le 2013-02-27 à 16:59, Tom Lane a écrit : > Adrian Klaver writes: >> On 02/27/2013 01:48 PM, François Beausoleil wrote: >>> Le 2013-02-27 à 16:45, Adrian Klaver a écrit : >>>> Also what client/library are you using to connect with? > >>> I use Ruby &a

Re: [GENERAL] What could cause a temp table to disappear?

Le 2013-02-27 à 16:45, Adrian Klaver a écrit : > On 02/27/2013 01:19 PM, François Beausoleil wrote: > >> >> Any hints? > > See in line notes below. > Also what client/library are you using to connect with? I use Ruby & Sequel. Sequel is a very thin library o

[GENERAL] What could cause a temp table to disappear?

Hi all, I open a transaction, create a few temporary tables, import data in them, then insert into the final tables from the temporary tables and commit. In dev, it works, but not in prod. I'm trying to track down the cause. An outline of the generated SQL is (actual log at bottom): BEGIN; CRE

[GENERAL] Determining last auto vacuum / analyze

Hi all, According to http://heatware.net/databases/how-when-postgres-tables-auto-vacuum-analyze/ (August 2010), running: select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables; should tell me which tables have been auto vacuumed and auto analyzed.

[GENERAL] PGbouncer and batch vs real-time pools

what creates a new pool. The client's name (the one the app connects as) is irrelevant since I hard-code what user I want to use. Did I read the configuration manual right? Any advice from people "familiar with the matter"? Thanks! François Beausoleil -- Sent via pgsql-

Re: [GENERAL] Installing PostgreSQL on OSX Server

Le 2013-01-28 à 14:47, Wolfgang Keller a écrit : >> I have installed and configured PostgreSQL on Windows, FreeBSD, and a >> few Linux flavors, but never OSX. I have the basic directions >> (http://www.enterprisedb.com/resources-community/pginst-guide) and >> found a couple of articles / blogs,

Re: [GENERAL] pg_Restore

kup if you use this Read this section of the manual: http://www.postgresql.org/docs/current/static/runtime-config-wal.html Have a great day! François Beausoleil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Understanding TIMESTAMP WITH TIME ZONE

WITH clause? PG should assume (correctly in this case) that the conversion is to UTC. Thanks! François Beausoleil > -- > Adrian Klaver > adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Picking the first of an order in an aggregate query

Le 2012-12-31 à 15:38, Robert James a écrit : > DISTINCT is a very simple solution! > But I have one problem: In addition to the FIRST fields, I also do > want some aggregate functions. More accurately, it would be: > > SELECT grouping_field, FIRST(field_a), FIRST(field_b), SUM(field_x), > MAX(

Re: [GENERAL] update from a csv file?

Le 2012-12-27 à 09:54, Kirk Wythers a écrit : > I have been using COPY FROM to do a mass import of records from CSV files > into a new database. I have discover however, a small number of records ( a > few thousand) in one of the files that contain new data that needs to be > added to the data

[GENERAL] Literal NUL using COPY TO

Can one use COPY TO stdout to output a literal NUL byte as the delimiter? # \encoding UTF8 # copy (select * from q limit 1) to stdout with (format csv, delimiter E'\000', header false); ERROR: invalid byte sequence for encoding "UTF8": 0x00 # \encoding sql_ascii # copy (select * from q limit 1)

Re: [GENERAL] When is archive_cleanup called?

Le 2012-12-09 à 16:32, Jeff Janes a écrit : > On Fri, Nov 30, 2012 at 6:44 AM, François Beausoleil > wrote: >> >> How come no new restart points were achieved? I had 4008 WAL archives on my >> slave. I expected them to be removed as streaming replication progressed

[GENERAL] ANALYZE and partitioning

Hello all! I have a table with weekly partitions, going back about 9 months. This is a rollup table, and I update the values in the table once per day, plus a final refresh for the previous week on Monday. The parent table has no rows, nor should it contain any. The rollup script does this: B

[GENERAL] When is archive_cleanup called?

anup : LOG : 34 segments removed. It's 2012-12-30 14:35 UTC on the machine. How come no new restart points were achieved? I had 4008 WAL archives on my slave. I expected them to be removed as streaming replication progressed. Are restart points prevented while long queries are runni

[GENERAL] PG under OpenVZ?

Hi! I've found an old thread on OpenVZ: (2008): http://archives.postgresql.org/pgsql-performance/2008-03/msg00076.php And a more recent question that scared me a bit: (2011): http://serverfault.com/questions/281783/running-mongodb-with-openvz On the PostgreSQL general mailing list, I've only f

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] Rank based on the number of matching OR fields?

I do such a thing? http://www.postgresql.org/docs/current/static/textsearch-controls.html#TEXTSEARCH-RANKING Hope that helps, François Beausoleil

[GENERAL] What am I doing wrong?

I'm in the single-slave scenario, with hot standby capabilities, meaning I want to run queries on the slave. I'm running some tests to evaluate pgbarman, on Ubuntu 11.10. I used only packaged PostgreSQL, and I'm running version "PostgreSQL 9.1.5 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real

Re: [GENERAL] N-tile function in postgres

Le 2012-09-24 à 14:12, Rachel Owsley a écrit : > Thank you, François! This is very helpful! I’ll give this query a try. I > don’t know the cross-tab function, but that’s exactly what I want to do for > the column output. Regarding the sample query, I see the min (amount), but > how is the uppe

Re: [GENERAL] N-tile function in postgres

Le 2012-09-24 à 12:32, Rachel Owsley a écrit : > Hi, > > Can anyone help me with an aggregate query I am having trouble with? > > I want to get the top 5 or top 10 most frequently shopped in merchant > categories for each account holder at a bank and put each of the > quintiles/deciles into

[GENERAL] State of the art for foreign keys to child tables?

What's the state of the art for foreign keys on child tables? My use case is this: CREATE TABLE parties(party_id serial primary key); CREATE TABLE positions( PRIMARY KEY(party_id) ) INHERITS(parties); CREATE TABLE organizations( PRIMARY KEY(party_id) ) INHERITS(parties); CREATE TABLE party_names(

Re: [GENERAL] pg_dump -Fd must create directory

Le 2012-09-13 à 16:51, David Salisbury a écrit : > > It looks to me like you're misusing git.. > > You should only git init once, and always use that directory. > Then pg_dump, which should create one file per database > with the file name you've specified. > Not sure of the flags but I'd recom

Re: [GENERAL] Amazon High I/O instances

Le 2012-09-12 à 17:08, Sébastien Lorion a écrit : > As you can see, I am nowhere near the results John mentioned for a 10,000 > scale (about 8000 tps) and I am not sure why. My instance setup and > configuration should be ok, but I am far from an expert (a startup founder > has to wear many ha

[GENERAL] pg_dump -Fd must create directory

lly static. I was thinking of saving some network traffic by transferring only the delta. Any thoughts on this? Is this something that can or can be made to change? Where are the IDs used in the dump coming from? Can they be made stable? Thanks! François Beausoleil $ cat a.sh PGDATABASE=${US

[GENERAL] Should I drop / create extensions after upgraging?

less invasive? I can't find any references to upgrades on http://www.postgresql.org/docs/9.1/static/sql-createextension.html, nor on http://wiki.postgresql.org/wiki/ExtensionPackaging. Where should I have looked to find a reference to upgrading? Thanks! François Beausoleil -- Sent via pgs

Re: [GENERAL] slowness what only full vacuum can solve

ages.2C_max_fsm_relations talks about running more frequently. Hope that helps! François Beausoleil - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Is it possible to create row-wise indexable condition for special case...

Le 2012-08-06 à 01:39, Maxim Boguk a écrit : > I know that for condition like ((field1>value1) or (field1=value1 and > field2>value2)) I could built index on (field1, field2) and use indexable > condition like (field1, field2) > (value1, value2). > > However, I have very tricky query whic

Re: [GENERAL] Pg 9.1: Do I need to run vacuum analyze instead vacuum full?

Le 2012-07-24 à 16:36, Edson Richter a écrit : > I know PostgreSQL from time to time runs automatically the vacuum process. > My applications grows due lots of data import during the day (about > 100Mb/day). > Do I need also to schedule a Vacuum Analyze to happens from time to time? I already a

Re: [GENERAL] Counting # of consecutive rows with specified value(s)?

Le 2012-06-06 à 22:20, Ken Tanzer a écrit : > > I can currently test whether someone has at least a specified number of > consecutive absences with the query below, but it would be better to get the > actual number. > > As a second question, what about getting the number of consecutive record

Re: [GENERAL] Disable Streaming Replication without restarting either master or slave

_replay_resume() ? http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE Hope that helps! François Beausoleil

Re: [GENERAL] COPY from CSV, passing in default value?

FROM '...', WITH (format csv); INSERT salaries(town, country, supervisor, salary) SELECT town, 'County Name', supervisor, salary FROM salaries_import; Hope that helps! François Beausoleil

Re: [GENERAL] Writing data to a text file based on a trigger event...

Le jeudi 12 avril 2012 à 06:58, raghupradeep a écrit : > What I need to know is it possible to write a trigger which will write the > data of the row whose status column gets updated to a text file? I would advise simply INSERTing the old values into a new table. This would be more flexible in

Re: [GENERAL] Help needed to mount a dmp file

Le lundi 9 avril 2012 à 12:47, Lux a écrit : > Hi, > I am new to Postgresql and have no clue. The first task given to me was to > try to mount this dmp file on Postgresql which has been downloaded from Jive. > I am not sure how to go about and where to start. Can someone please help me. > Than

Re: [GENERAL] Streaming replication and empty databases

Le 2012-03-23 à 09:28, Svoop a écrit : > However, can I use the app_staging database on the staging server > independently? [...] > > Plan B is a second pg instance on the staging server, but I'd like to know if > the above setup might work as well. PostgreSQL disallows writes on slaves. Like

Re: [GENERAL] COPY and indices?

Le mercredi 14 mars 2012 à 15:06, Merlin Moncure a écrit : > 2012/3/14 François Beausoleil (mailto:franc...@teksol.info)>: > > In the entirety of my career, I have never found anything more > perplexing than the general reluctance to upgrade hardware to solve > hardware r

Re: [GENERAL] COPY and indices?

Le mardi 13 mars 2012 à 11:15, Merlin Moncure a écrit : > 2012/3/13 François Beausoleil (mailto:franc...@teksol.info)>: > > > > I'll go with the COPY, since I can live with the batched requirements just > > fine. > > 30-40 'in transaction'

Re: [GENERAL] COPY and indices?

Le mardi 13 mars 2012 à 10:48, Merlin Moncure a écrit : > 2012/3/12 François Beausoleil (mailto:franc...@teksol.info)>: > > Currently, I can sustain 30-40 writes per second on a Rackspace VPS. I know > > it's not the ideal solution, but that's what I'm working

[GENERAL] COPY and indices?

Hi all, When using COPY FROM STDIN to stream thousands of rows (20k and more hourly), what happens with indices? Are they updated only once after the operation, or are they updated once per row? Note that I'm not replacing the table's data: I'm appending to what's already there. I suspect bat

[GENERAL] replay_location meaning in pg_stat_replication

Hi all, I'm just wondering what the numbers really mean. The output is "2FA/B3AFB890", for example. Do these numbers represent something meaningful I can use? Bytes, offsets within files, what? How do you monitor the difference between write_location and replay_location? It seems obvious the

[GENERAL] Allowed DML on replicas?

e, to keep the extra schemas around? Thanks! François Beausoleil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general