Re: [GENERAL] ~/.psqlrc file is ignored

On 19 July 2017 at 20:12, vstuart wrote: > Hi David: I see what you are saying; sorry for the confusion. This is how > postgres operates on my system: > > [victoria@victoria ~]$ echo $HOME > /home/victoria > > [victoria@victoria ~]$ which postgres >

Re: [GENERAL] ~/.psqlrc file is ignored

On 18 July 2017 at 19:02, vstuart wrote: > My ~/.psqlrc file is ignored by my PostgreSQL installation (v.9.6.3; Arch > Linux x86_64 platform). > > Suggestions? Do you get anything with "psql -a"? If not, what do you get when you use "psql -af ~/.psqlrc" ? Thom --

[GENERAL] Very slow DELETEs with foreign keys

Hi, I've just noticed a general delete performance issue while testing a patch, and this can be recreated on all recent major versions. I have 2 tables: CREATE TABLE countries ( country text PRIMARY KEY, continent text ); CREATE TABLE contacts ( id serial PRIMARY KEY,

Re: [GENERAL] Very slow DELETEs with foreign keys

On 8 February 2016 at 14:52, Tom Lane <t...@sss.pgh.pa.us> wrote: > Thom Brown <t...@linux.com> writes: >> I've just noticed a general delete performance issue while testing a >> patch, and this can be recreated on all recent major versions. > >> I have 2

Re: [GENERAL] Syntax error for Function

On 20 January 2016 at 12:15, Sachin Srivastava wrote: > I am unable to find out the syntax error in below code, please suggest? > > > > ERROR: syntax error at or near "select" > LINE 44: select Count(0) into sFound from budget_period ... >

Re: [GENERAL] postgresql 9.4 streaming replication

On 19 October 2015 at 09:41, Sven Löschner wrote: > I inserted the following in my pg_hba.conf to test, but it does not work: > > hostreplication rep_user0.0.0.0/0 trust > hostall postgres0.0.0.0/0 trust > >

Re: [GENERAL] How to drop user if objects depend on it

On 7 October 2015 at 11:42, Andrus wrote: > Hi! > > Database idd owner is role idd_owner > Database has 2 data schemas: public and firma1. > User may have directly or indirectly assigned rights in this database and > objects. > User is not owner of any object. It has only

Re: [GENERAL] pg_restore fails to restore sequences

On 28 September 2015 at 21:47, Tom Lane wrote: > Spencer Gardner writes: >> I'm transferring all of the databases on my old postgres server to a new >> server. To do this I'm using pg_dump and then pg_restore: > >> pg_dump --host localhost --port

Re: [GENERAL] pg_restore fails to restore sequences

On 28 September 2015 at 22:21, Spencer Gardner wrote: > Actually, yes. That's the reason for backing up. We had been playing with > BDR on a custom build but have reverted to the stock Ubuntu build for the > time being. So it sounds like the issue is caused by dumping

Re: [GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

On 24 September 2015 at 12:28, Alex Magnum wrote: > Hi, > is it possible to grant select to views and functions without the need to > also grant the user the SELECT privileges to the Tables used in the views or > functions? > > That way I could create read only users on a

Re: [GENERAL] Exclusively locking parent tables while disinheriting children.

On 7 August 2015 at 12:34, Thom Brown <t...@linux.com> wrote: > > On 30 July 2015 at 13:35, Rowan Collins <rowan.coll...@gmail.com> wrote: > >> Hi, >> >> When working with partition sets, we're seeing occasional errors of >> "could

Re: [GENERAL] Exclusively locking parent tables while disinheriting children.

On 30 July 2015 at 13:35, Rowan Collins rowan.coll...@gmail.com wrote: Hi, When working with partition sets, we're seeing occasional errors of could not find inherited attribute... in Select queries. This is apparently caused when an ALTER TABLE ... NO INHERIT runs concurrently with another

Re: [GENERAL] SET LOCAL synchronous_commit TO OFF

On 11 June 2015 at 17:34, Robert DiFalco robert.difa...@gmail.com wrote: I want to make sure I understand the repercussions of this before making it a global setting. As far as I can tell this will put data/referential integrity at risk. It only means that there is a period of time (maybe 600

Re: [GENERAL] What is default password for user postgres

On 1 December 2014 at 09:08, M Tarkeshwar Rao m.tarkeshwar@ericsson.com wrote: Hi all, I installed version 9.1 in my Ubuntu OS, but not able to login. What is default password for user Postgres? The postgres user doesn't have a password by default, which is probably how you should

Re: [GENERAL] How to find earlest possible start times for given duration excluding reservations

On 28 October 2014 15:10, Andrus kobrule...@hot.ee wrote: Hi! I'm looking for finding ealiest possible start times from reservations table. People work from 10:00AM to 21:00PM in every week day except Sunday and public holidays. Jobs for them are reserved at 15 minute intervals and whole

Re: [GENERAL] How to find earlest possible start times for given duration excluding reservations

On 28 October 2014 19:14, Andrus kobrule...@hot.ee wrote: Hi! Would you be able to adapt this to your needs?: Thank you very much. Great solution. I refactored it as shown below. Query returns only dates for single day. Changing limit clause to 300 does not return next day. How

Re: [GENERAL] How to find earlest possible start times for given duration excluding reservations

On 28 October 2014 20:04, Thom Brown t...@linux.com wrote: On 28 October 2014 19:14, Andrus kobrule...@hot.ee wrote: Hi! Would you be able to adapt this to your needs?: Thank you very much. Great solution. I refactored it as shown below. Query returns only dates for single day

Re: [GENERAL] How to find earlest possible start times for given duration excluding reservations

On 28 October 2014 21:07, Andrus kobrule...@hot.ee wrote: Hi! A correction to this. As it stands, it will show times like the following: Thank you. I posted your solution as alternative to Erwin answer in

[GENERAL] Non-capturing expressions

Hi all, It must be that I haven't had enough caffeine today, but I can't figure out why the following expression captures the non-capturing part of the text: # SELECT regexp_matches('postgres','(?:g)r'); regexp_matches {gr} (1 row) I'm expecting '{r}' in the output as I

Re: [GENERAL] Non-capturing expressions

On 25 October 2014 11:49, Francisco Olarte fola...@peoplecall.com wrote: Hi Thom: On Sat, Oct 25, 2014 at 11:24 AM, Thom Brown t...@linux.com wrote: It must be that I haven't had enough caffeine today, but I can't figure out why the following expression captures the non-capturing part

Re: [GENERAL] hstore - jsonb

On 4 April 2014 13:04, Oleg Bartunov obartu...@gmail.com wrote: On Fri, Apr 4, 2014 at 12:20 PM, Armand Turpel armand.turpel.m...@gmail.com wrote: Hi, A few questions about jsonb and hstore: 1. does jsonb replace hstore? no, it's different data type 2. compatibility of jsonb hstore?

Re: [GENERAL] hstore - jsonb

On 4 April 2014 16:15, Oleg Bartunov obartu...@gmail.com wrote: We'll work on contrib/jsonxtra with all operators ported from hstore and release it after 9.4 as separate extension. That would be useful. :) Would there be an aim of getting that in-core for 9.5? -- Thom -- Sent via

Re: [GENERAL] Multi-dimensional arrays

On 15 March 2014 12:51, Raymond O'Donnell r...@iol.ie wrote: Hello all, Here's an odd one (to me anyway) which I ran into today if I have a multidimensional array, why does the following return NULL? select (array[['abc','def'], ['ghi','jkl']])[1] I would have expected it to return

Re: [GENERAL] Multi-dimensional arrays

On 15 March 2014 16:21, Tom Lane t...@sss.pgh.pa.us wrote: Raymond O'Donnell r...@iol.ie writes: True... though that gives you a 2D array, whereas I was hoping for a 1D array from (array[...])[1]. Postgres does not think of multi-D arrays as being arrays of arrays. This is problematic mainly

Re: [GENERAL] 9.1.11 - many backends in semtimedop syscall

On 10 March 2014 15:32, hubert depesz lubaczewski dep...@depesz.com wrote: On Thu, Mar 06, 2014 at 06:03:54PM +0100, hubert depesz lubaczewski wrote: On Thu, Mar 06, 2014 at 12:02:50PM -0500, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: I didn't have a chance to do

Re: [GENERAL] Mysterious DB reset

On 5 March 2014 18:22, Israel Brewster isr...@eraalaska.net wrote: I have a Postgresql 9.2.4 database containing real-time tracking data for our aircraft for the past week (theoretically). It is populated by two different processes: one that runs every few minutes, retrieving data from a

Re: [GENERAL] How can I use the crosstab() function?

On 25 February 2014 23:30, Rob Richardson rdrichard...@rad-con.com wrote: Hello! I am trying to use the crosstab() function in PostgreSQL 9.0 under Windows 7. My table has three columns: a timestamp, a tag name and a tag value. I am trying to generate a table that has one column for

Re: [GENERAL] postgres-fdw questions

On 24 January 2014 09:20, Emmanuel Medernach meder...@clermont.in2p3.fr wrote: Hello, I'm currently testing postgres_fdw feature on PostgreSQL 9.3.2 and I have some questions: - What are the limits to the number of foreign tables ? As far as I'm aware, there isn't one. - What is the

[GENERAL] Unix domain socket inconsistency

Hi all, I'm a bit confused by my development set up. I can connect to PostgreSQL using unix domain sockets by not specifying any host with psql, and the same applies to vacuumdb, createdb and dropdb. However, when I go to use pgbench, it seems to be looking in the wrong place for the domain

Re: [GENERAL] Unix domain socket inconsistency

On 23 December 2013 01:13, Adrian Klaver adrian.kla...@gmail.com wrote: On 12/22/2013 04:51 PM, Thom Brown wrote: PostgreSQL using unix domain sockets by not specifying any host with psql, and the same applies to vacuumdb, createdb and dropdb. However, when I go to use pgbench, it seems

Re: [GENERAL] Unix domain socket inconsistency

On 23 December 2013 01:15, Thom Brown t...@linux.com wrote: On 23 December 2013 01:13, Adrian Klaver adrian.kla...@gmail.com wrote: On 12/22/2013 04:51 PM, Thom Brown wrote: PostgreSQL using unix domain sockets by not specifying any host with psql, and the same applies to vacuumdb, createdb

Re: [GENERAL] Foreign Key violated

On 23 May 2013 15:33, Thom Brown t...@linux.com wrote: On 23 May 2013 10:15, Keith Fiske ke...@omniti.com wrote: Client reported an issue where it appears a foreign key has been violated prod=#\d rma_items [snip] rma_items_rma_id_status_fk FOREIGN KEY (rma_id, rma_status) REFERENCES rmas(id

Re: [GENERAL] Foreign Key violated

On 23 May 2013 10:15, Keith Fiske ke...@omniti.com wrote: Client reported an issue where it appears a foreign key has been violated prod=#\d rma_items [snip] rma_items_rma_id_status_fk FOREIGN KEY (rma_id, rma_status) REFERENCES rmas(id, status) ON UPDATE CASCADE ON DELETE CASCADE prod=#

Re: [GENERAL] Unlogged indexes

On 3 May 2013 21:06, Yang Zhang yanghates...@gmail.com wrote: Guessing the answer's no, but is there any way to construct indexes such that I can safely put them on (faster) volatile storage? (Just to be clear, I'm asking about indexes for *logged* tables.) Yes: CREATE INDEX ... TABLESPACE

Re: [GENERAL] UPDATE using 3 medium-sized tables causes runaway hash table and fills disk

On 26 April 2013 15:39, Rowan Collins rowan.coll...@gmail.com wrote: Hi All, I've come upon some very strange behaviour with an UPDATE query which causes Postgres to consume all the disk space on the server for no apparent reason. Basically, I'm trying to run an UPDATE involving three

Re: [GENERAL] regex help wanted

On 25 April 2013 15:32, Tom Lane t...@sss.pgh.pa.us wrote: Karsten Hilbert karsten.hilb...@gmx.net writes: What I don't understand is: Why does the following return a substring ? select substring ('junk $allergy::test::99$ junk' from '\$[^]+?::[^:]+?\$'); There's a perfectly valid

Re: [GENERAL] Grant problem and how to prevent users to execute OS commands?

On 20 August 2012 19:34, Evil evilofreve...@hotmail.com wrote: Hello List, First time here also beginner to Postgres.So please forgive me for any mistakes. I'm pretty sure i have same problem.= http://archives.postgresql.org/pgsql-admin/2012-03/msg00105.php (After searching it i found it)

Re: [GENERAL] Reading storage parameters

On 21 June 2012 13:12, Daniele Varrazzo daniele.varra...@gmail.com wrote: Hello, is there a way to read the storage parameters values set on a table (i.e. what set with ALTER TABLE foo SET (autovacuum_enabled=false) and so on...)? I can't find it in the docs. SELECT c.reloptions FROM

Re: [GENERAL] Having trouble with pg_dumpall -o

On 1 May 2012 11:12, Matthew Churcher matthew.churc...@realvnc.com wrote: Hi PostgreSQL users, I'm having difficulty migrating a postgres 8.4.11 database to postgres 9.1.2, neither of the included pg_dumpall tools appear to honour the -o or --oids options and fail to dump the table oids from

Re: [GENERAL] Having trouble with pg_dumpall -o

On 1 May 2012 11:55, Matthew Churcher matthew.churc...@realvnc.com wrote: Thanks Thom, that's really useful to know however  I've been unable to get it working with pg_dump either. Are you able to offer any insight there? What command line options are  you using? I get the same result with:

Re: [GENERAL] Having trouble with pg_dumpall -o

On 1 May 2012 11:22, Thom Brown t...@linux.com wrote: On 1 May 2012 11:12, Matthew Churcher matthew.churc...@realvnc.com wrote: Hi PostgreSQL users, I'm having difficulty migrating a postgres 8.4.11 database to postgres 9.1.2, neither of the included pg_dumpall tools appear to honour the -o

Re: [GENERAL] Having trouble with pg_dumpall -o

On 1 May 2012 12:37, Matthew Churcher matthew.churc...@realvnc.com wrote: OK, I think I've worked out what's going on. I've got my wires crossed between table column OIDS (deprecated) and the OID which uniquely identifies each table (?always enabled?). We're not using OID for each column,

Re: [GENERAL] how robust are custom dumps?

On 24 April 2012 16:17, Willy-Bas Loos willy...@gmail.com wrote: Hi, Some 6 years ago, i had a bad experience with a custom dump. It wouldn't restore and my data was lost. What was the experience? Is it possible you had specified a compression level without the format set to custom? That

Re: [GENERAL] Chaining inserts ... This would be cool

On 23 April 2012 21:49, Nick Apperson apper...@gmail.com wrote: There are obviously workarounds for this, but I'm wondering why the following query shouldn't work. It seems like it should. With MVCC already present on the back-end, I can't see any reason other than additional parsing routines

[GENERAL] Unaccent characters

Hi, I had a look at the unaccent.rules file and noticed the following characters aren't properly converted: ß (U+00DF) An eszett represents a double-s SS but this replaces it with one S. Shouldn't this be replace with SS? Æ (U+00C6) and æ (U+00E6) These doesn't have an accent, diacritic or

Re: [GENERAL] oracle linux

On 28 March 2012 16:30, Tom Lane t...@sss.pgh.pa.us wrote: Tomas Vondra t...@fuzzy.cz writes: On 28 Březen 2012, 16:38, Gregg Jaskiewicz wrote: They seem to claim up to 70% speed gain. Did anyone proved it, tested it - with PostgreSQL in particular ? I really don't expect such difference

Re: [GENERAL] language name case sensitivity, also known as plpgsql 'PLpgSQL'

On 26 March 2012 16:30, Gregg Jaskiewicz gryz...@gmail.com wrote: Folks, I'm testing some code on 9.2dev (trunk), and I've noticed that postgresql seems to be fussy about language case when creating a function. So for instance: create function foo() returns int AS $$ BEGIN return 1; END; $$

Re: [GENERAL] broken xlog - recovery plan check

On 24 March 2012 00:45, Colin Taylor colin.tay...@gmail.com wrote: Hi I seem to have an 8.3.9 database with a broken xlog, PANIC:  heap_insert_redo: invalid max offset number My plan is to run pg_resetxlog. Hopefully it then starts up. Test recent data as thoroughly as possible - (script

[GENERAL] Unhelpful initdb error message

Hi all, After building Postgres and trying an initdb, I'm getting the following: thom@swift:~/Development$ initdb The files belonging to this database system will be owned by user thom. This user must also own the server process. The database cluster will be initialized with locale

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 16:02, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: thom@swift:~/Development$ initdb The files belonging to this database system will be owned by user thom. This user must also own the server process. The database cluster will be initialized

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 16:04, Adrian Klaver adrian.kla...@gmail.com wrote: The postmaster.pid is located outside the data directory, but points back to the data directory.   Not sure where Debian, though at a guess somewhere in /var. Any way search for postmaster.pid. I'm not sure, because if I use

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 16:11, Thom Brown t...@linux.com wrote: On 6 March 2012 16:04, Adrian Klaver adrian.kla...@gmail.com wrote: The postmaster.pid is located outside the data directory, but points back to the data directory.   Not sure where Debian, though at a guess somewhere in /var. Any way

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 16:18, Adrian Klaver adrian.kla...@gmail.com wrote: On Tuesday, March 06, 2012 8:11:20 am Thom Brown wrote: On 6 March 2012 16:04, Adrian Klaver adrian.kla...@gmail.com wrote: The postmaster.pid is located outside the data directory, but points back to the data directory

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 16:31, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: On 6 March 2012 16:02, Tom Lane t...@sss.pgh.pa.us wrote: Um ... I assume this is some patched version rather than pristine sources?  It's pretty hard to explain why it's falling over like that. No, I

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 16:40, Adrian Klaver adrian.kla...@gmail.com wrote: On Tuesday, March 06, 2012 8:24:20 am Thom Brown wrote: No, only the ones running as the postgres user. In my original read, I missed the part you had the Ubuntu/Debian packaged version running. Here's the contents

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 17:00, Adrian Klaver adrian.kla...@gmail.com wrote: On Tuesday, March 06, 2012 8:44:10 am Thom Brown wrote: And if I start my development copy, this is the content of its postmaster.pid: 27061 /home/thom/Development/data 1331050950 5488 /tmp localhost

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 17:16, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: Looking back through my terminal log, one thing might lend a clue from before I tried rebuliding it: thom@swift:~/Development$ pg_ctl stop waiting for server to shut downcd .postgre.s

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 17:45, Adrian Klaver adrian.kla...@gmail.com wrote: On Tuesday, March 06, 2012 9:25:17 am Thom Brown wrote: These are in my env output: PATH=/home/thom/Development/psql/bin/:/usr/lib/lightdm/lightdm:/usr/local/s bin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 17:46, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: On 6 March 2012 16:31, Tom Lane t...@sss.pgh.pa.us wrote: [ scratches head... ]  I can't reproduce it with current git tip. And I don't think I can reproduce this if I remove that directory. I've seen

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 17:53, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: /home/thom/Development/data was causing problems so: mv data databroken mkdir data initdb ... working fine again.  I then used the postmaster.pid from this when started up.  But if I do: pg_ctl

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 18:01, Adrian Klaver adrian.kla...@gmail.com wrote: On Tuesday, March 06, 2012 9:53:52 am Tom Lane wrote: Thom Brown t...@linux.com writes: /home/thom/Development/data was causing problems so: mv data databroken mkdir data initdb ... working fine again.  I then used

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 18:20, Tom Lane t...@sss.pgh.pa.us wrote: Bosco Rama postg...@boscorama.com writes: Thom Brown wrote: I've done that a couple times, but no effect.  I think Tom's point about a filesystem bug is probably right. Have you rebooted since this started?  There may be a process

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 18:51, dennis jenkins dennis.jenkins...@gmail.com wrote: On Tue, Mar 6, 2012 at 10:11 AM, Thom Brown t...@linux.com wrote: On 6 March 2012 16:04, Adrian Klaver adrian.kla...@gmail.com wrote: The postmaster.pid is located outside the data directory, but points back to the data

Re: [GENERAL] Unhelpful initdb error message

On 6 March 2012 19:28, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: On 6 March 2012 18:20, Tom Lane t...@sss.pgh.pa.us wrote: Still, I agree with your point: Thom should reboot and see if the misbehavior is still there, because that would be useful info for his bug

Re: [GENERAL] How can I get list of views that are using given column in table?

On 20 February 2012 12:06, hubert depesz lubaczewski dep...@depesz.com wrote: hi I have situation, where I need to change datatype of column. But when I do: alter table xx alter column yy type zz; i get error: ERROR:  cannot alter type of a column used by a view or rule DETAIL:  rule

Re: [GENERAL] How can I get list of views that are using given column in table?

On 20 February 2012 17:29, hubert depesz lubaczewski dep...@depesz.com wrote: On Mon, Feb 20, 2012 at 01:06:29PM +, Thom Brown wrote: You could try this: SELECT distinct dependee.relname FROM pg_depend JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid JOIN pg_class as dependee

[GENERAL] Regex match not back-referencing in function

Hi, Could someone explain the following behaviour? SELECT regexp_replace(E'Hello goodbye ',E'([])','#' || ascii(E'\\1') || E';\\1'); This returns: regexp_replace Hello #92; goodbye (1 row) So it matched: SELECT chr(92); chr - \ (1 row) But notice that

Re: [GENERAL] Regex match not back-referencing in function

On 12 February 2012 18:49, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: What am I missing? I might be more confused than you, but I think you're supposing that the result of ascii(E'\\1') has something to do with the match that the surrounding regexp_replace function

Re: [GENERAL] can't find data type CIText or CI_Text

On 16 January 2012 20:15, Heine Ferreira heine.ferre...@gmail.com wrote: Hi I was told by someone that in order to store text that isn't case sensitive in comparisons I must use CIText or CI_Text. I can't find the data type? Also is this  char or varchar data type? Can you create an index on

Re: [GENERAL] Is the a magic number for WAL files

On 9 December 2011 18:46, Rob Sargent robjsarg...@gmail.com wrote: Along the same lines, what info is embedded in the file name? I see that the second non-zero recently went from 2 to 3.  Significance? 0001003000CF              ^ --| The WAL file name consists of

Re: [GENERAL] .dmp files in postgresql

On 25 November 2011 20:04, Alpha Beta dzjit...@gmail.com wrote: Hi list, I have got two files (template1.dmp, example1.dmp) and I want to open them in postgresql, I tried the following commands: - Import template1 :     * createdb -U postgres template1     * psql -U postgres template1

Re: [GENERAL] .dmp files in postgresql

On 25 November 2011 20:31, Alpha Beta dzjit...@gmail.com wrote: While you say, I opened the file with bloc note and I noticed that it's not a binary file but plain with SQL commands and so on. I tried what you said also but didn't work. Any suggestion? or maybe the commands I'm using doesn't

Re: [GENERAL] Exporting 1 text column from all rows from a table to a file per row?

ASCII files, each containing that column body1. The name of the file does not matter, although it would be nice if they had the extension txt. Does the data contain newlines? If not, you can just export it to a single file then use: split -l 1 exportedfile.txt -- Thom Brown Twitter

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

On 12 November 2011 00:08, Thom Brown t...@linux.com wrote: On 11 November 2011 23:28, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: On 11 November 2011 00:55, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: I just noticed that the VACUUM process

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

On 11 November 2011 23:28, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: On 11 November 2011 00:55, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: I just noticed that the VACUUM process touches a lot of relations (affects mtime) but for one file I

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

On 14 October 2011 12:12, Thom Brown t...@linux.com wrote: Hi, I just noticed that the VACUUM process touches a lot of relations (affects mtime) but for one file I looked at, it didn't change.  This doesn't always happen, and many relations aren't touched at all. I had the following

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

On 11 November 2011 00:55, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: On 14 October 2011 12:12, Thom Brown t...@linux.com wrote: I just noticed that the VACUUM process touches a lot of relations (affects mtime) but for one file I looked at, it didn't change

Re: [GENERAL] IP range in pg_hba.conf?

are masked. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Streaming Replication woes

On 4 November 2011 17:19, Sean Patronis spatro...@add123.com wrote: On 11/04/2011 10:59 AM, Thom Brown wrote: On 4 November 2011 16:50, Sean Patronisspatro...@add123.com  wrote: I am running Postgres 9.1 I have followed the howto here: http://wiki.postgresql.org/wiki/Streaming_Replication

Re: [GENERAL] Named column default expression

( id integer constraint id_default_value check (id 4) default 42 ); a constraint for that column will be created with the specified name. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise

[GENERAL] VACUUM touching file but not updating relation

This is the same as before. What is it doing? Does this happen often? And I can't find out what this particular OID relates to either. I'm using 9.2devel btw. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com

Re: [GENERAL] [9.2devel] why it doesn't do index scan only?

On 9 October 2011 04:35, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/10/8 Thom Brown t...@linux.com: On 8 October 2011 21:13, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/10/8 Thom Brown t...@linux.com: On 8 October 2011 19:47, Pavel Stehule pavel.steh...@gmail.com wrote: I did

Re: [GENERAL] [9.2devel] why it doesn't do index scan only?

On 9 October 2011 11:51, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/10/9 Thom Brown t...@linux.com: On 9 October 2011 04:35, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/10/8 Thom Brown t...@linux.com: On 8 October 2011 21:13, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/10/8

Re: [GENERAL] [9.2devel] why it doesn't do index scan only?

On 9 October 2011 18:38, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/10/9 Pavel Stehule pavel.steh...@gmail.com: 2011/10/9 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2011/10/9 Thom Brown t...@linux.com: On 9 October 2011 04:35, Pavel Stehule pavel.steh

Re: [GENERAL] [9.2devel] why it doesn't do index scan only?

) Filter: (thing = 14) Rows Removed by Filter: 14803172 Total runtime: 121296.999 ms (5 rows) Note: buffer cache cleared between queries. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com

Re: [GENERAL] [9.2devel] why it doesn't do index scan only?

On 8 October 2011 19:30, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/10/8 Thom Brown t...@linux.com: On 8 October 2011 18:53, Pavel Stehule pavel.steh...@gmail.com wrote: Hello 2011/10/8 Tom Lane t...@sss.pgh.pa.us: hubert depesz lubaczewski dep...@depesz.com writes: it is selecting

Re: [GENERAL] [9.2devel] why it doesn't do index scan only?

the same each time). -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription

Re: [GENERAL] [9.2devel] why it doesn't do index scan only?

On 8 October 2011 21:13, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/10/8 Thom Brown t...@linux.com: On 8 October 2011 19:47, Pavel Stehule pavel.steh...@gmail.com wrote: I did it. It is strange, so your times are significantly slower than I have. Have you enabled asserts? The table

Re: [GENERAL] Log Apply Delay

to the directory the standby is looking at. Or change +1 to +1h to leave a gap of an hour instead of a day. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql

Re: [GENERAL] Log duration and statement for slow queries + limiting the number of log files generated

with the assumption that they will roll over when filled? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] Deleting one of 2 identical records

by referencing it in the DELETE statement. For example: DELETE FROM my_table WHERE ctid = '(7296,11)'; It's a shame we don't have a LIMIT on the DELETE clause (looks at hackers). -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http

Re: [GENERAL] Deleting one of 2 identical records

whichever row appears first in the table before its duplicates. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] Deleting one of 2 identical records

distinct rows from the duplicate set to another table, deleting it from the original and copying back. Can't say for sure though since I haven't used it in quite a while. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http

Re: [GENERAL] Why schema of table is removed from explain?

) Output: b.y.id, b.y.things - Seq Scan on b.y (cost=0.00..1.20 rows=20 width=8) Output: b.y.id, b.y.things (9 rows) -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise

Re: [GENERAL] ARRAY_AGG(DISTINCT a ORDER BY b) Error

: accountnumber, amount 1000,100 2000,200 1000,300 You've ordered by amount, but accountnumber has 2 identical values, where the amount is less than the amount corresponding to accountnumber 2000 in one instance, but greater in another. Where does 1000 appear? Before or after 2000? -- Thom

Re: [GENERAL] Not Seeing Syntax Error

'); and the quant column is defined as type real. There are numerous other rows where quant IS NULL. What have I missed? The error message points to the problem. No value, not even NULL, has been specified for 5th column. Either put DEFAULT or NULL in there. You can't put nothing. -- Thom Brown

Re: [GENERAL] include directives in postgresql.conf

module configuration in their own files, so you can add include directives for each of those. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing

Re: [GENERAL] Adding Foreign Key Constraint To Existing Table

) REFERENCES table_b (column_of_table_b); If in future you want foreign key checks to be deferred until the transaction ends, you can add the DEFERRED keyword to the end. This will allow you to violate the foreign key temporarily, as long as you resolve it before the end of the transaction. -- Thom Brown

Re: [GENERAL] Adding Foreign Key Constraint To Existing Table

do this: ALTER TABLE tablename DISABLE TRIGGER ALL; Then it would ignore the foreign key trigger and you could put in mischievous values... but remember to enable it again (replace DISABLE with ENABLE). You'll have to be a superuser to do it though. -- Thom Brown Twitter: @darkixion IRC

Re: [GENERAL] In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation?

'? Hope that was clear. Try coalesce: http://www.postgresql.org/docs/9.0/static/functions-conditional.html#AEN15541 So if foo is a null value, and you used COALESCE(foo, 'bar'), the output would be 'bar', otherwise it would be whatever the value of foo is. -- Thom Brown Twitter: @darkixion IRC

  1   2   3   4   5   6   >