Re: [GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

[David Kerr]

> On Aug 25, 2015, at 10:45 AM, Bill Moran  wrote:
> 
> On Tue, 25 Aug 2015 10:08:48 -0700
> David Kerr  wrote:
> 
>> Howdy All,
>> 
>> For a very long time I've held the belief that splitting PGDATA and xlog on 
>> linux systems fairly universally gives a decent performance benefit for many 
>> common workloads.
>> (i've seen up to 20% personally).
>> 
>> I was under the impression that this had to do with regular fsync()'s from 
>> the WAL 
>> interfearing with and over-reaching writing out the filesystem buffers. 
>> 
>> Basically, I think i was conflating fsync() with sync(). 
>> 
>> So if it's not that, then that just leaves bandwith (ignoring all of the 
>> other best practice reasons for reliablity, etc.). So, in theory if you're 
>> not swamping your disk I/O then you won't really benefit from relocating 
>> your XLOGs.
> 
> Disk performance can be a bit more complicated than just "swamping." Even if

Funny, on revision of my question, I left out basically that exact line for 
simplicity sake. =)

> you're not maxing out the IO bandwidth, you could be getting enough that some
> writes are waiting on other writes before they can be processed. Consider the
> fact that old-style ethernet was only able to hit ~80% of its theoretical
> capacity in the real world, because the chance of collisions increased with
> the amount of data, and each collision slowed down the overall transfer speed.
> Contrasted with modern ethernet that doesn't do collisions, you can get much
> closer to 100% of the rated bandwith because the communications are 
> effectively
> partitioned from each other.
> 
> In the worst case scenerion, if two processes (due to horrible luck) _always_
> try to write at the same time, the overall responsiveness will be lousy, even
> if the bandwidth usage is only a small percent of the available. Of course,
> that worst case doesn't happen in actual practice, but as the usage goes up,
> the chance of hitting that interference increases, and the effective response
> goes down, even when there's bandwidth still available.
> 
> Separate the competing processes, and the chance of conflict is 0. So your
> responsiveness is pretty much at best-case all the time.

Understood. Now in my previous delve into this issue, I showed minimal/no disk 
queuing, the SAN showed nothing on it's queues and no retries. (of course 
#NeverTrustTheSANGuy) but I still yielded a 20% performance increase by 
splitting the WAL and $PGDATA

But that's besides the point and my data on that environment is long gone.

I'm content to leave this at "I/O is complicated" I just wanted to make sure 
that i wasn't correct but for a slightly wrong reason.

Thanks!

-- 
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] Why does splitting $PGDATA and xlog yield a performance benefit?

[David Kerr]

On Tue, Aug 25, 2015 at 10:16:37AM PDT, Andomar wrote:
> >However, I know from experience that's not entirely true, (although it's not 
> >always easy to measure all aspects of your I/O bandwith).
> >
> >Am I missing something?
> >
> Two things I can think of:
> 
> Transaction writes are entirely sequential.  If you have disks
> assigned for just this purpose, then the heads will always be in the
> right spot, and the writes go through more quickly.
> 
> A database server process waits until the transaction logs are
> written and then returns control to the client. The data writes can
> be done in the background while the client goes on to do other
> things.  Splitting up data and logs mean that there is less chance
> the disk controller will cause data writes to interfere with log
> files.
> 
> Kind regards,
> Andomar
> 

hmm, yeah those are both what I'd lump into "I/O bandwith". 
If your disk subsystem is fast enough, or you're on a RAIDd SAN 
or EBS you'd either overcome that, or not neccssarily be able to.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

[David Kerr]

Howdy All,

For a very long time I've held the belief that splitting PGDATA and xlog on 
linux systems fairly universally gives a decent performance benefit for many 
common workloads.
(i've seen up to 20% personally).

I was under the impression that this had to do with regular fsync()'s from the 
WAL 
interfearing with and over-reaching writing out the filesystem buffers. 

Basically, I think i was conflating fsync() with sync(). 

So if it's not that, then that just leaves bandwith (ignoring all of the other 
best practice reasons for reliablity, etc.). So, in theory if you're not 
swamping your disk I/O then you won't really benefit from relocating your XLOGs.

However, I know from experience that's not entirely true, (although it's not 
always easy to measure all aspects of your I/O bandwith).

Am I missing something?

Thanks


-- 
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] Replication fell out of sync

[David Kerr]

On Mon, Mar 02, 2015 at 04:06:02PM PDT, Adrian Klaver wrote:
> On 03/02/2015 03:25 PM, David Kerr wrote:
> >Howdy,
> >
> >I had an instance where a replica fell out of sync with the master.
> >
> >Now it's in in a state where it's unable to catch up because the master has 
> >already removed the WAL segment.
> >
> >(logs)
> >Mar  2 23:10:13 db13 postgres[11099]: [3-1] user=,db=,host= LOG:  streaming 
> >replication successfully connected to primary
> >Mar  2 23:10:13 db13 postgres[11099]: [4-1] user=,db=,host= FATAL:  could 
> >not receive data from WAL stream: FATAL:  requested WAL segment 
> >0006047C001F has already been removed
> >
> >
> >I was under the impression that when you setup streaming replication if you 
> >specify a restore command like : restore_command = 'cp /arch/%f %p'
> >
> >Then even if the slave falls out of sync, and the master removes the WAL 
> >segment, as long as you can still retrieve the WAL files, then it can bring 
> >itself back into sync.
> >
> >
> >But that doesn't seem to be happening.
> >
> >The restore_command is working
> ># Slave's $PGDATA/pg_xlog/
> >-rw--- 1 postgres postgres 16777216 Mar  2 21:29 0006047C001F
> >-rwx-- 1 postgres postgres 16777216 Mar  2 23:13 RECOVERYXLOG
> 
> Trying to figure out why the error occurred at Mar  2 23:10:13 and
> the file shows a time stamp of Mar  2 21:29, especially since you
> say the WAL segments flew past?
> 
> Are there any other WAL files in the slave ~/pg_xlog?

Turns out just that file had gotten corrupt on copy. When i re-pulled it from 
source life was good.

(phew!)

Thanks all for looking.


-- 
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] Replication fell out of sync

[David Kerr]

On Mon, Mar 02, 2015 at 03:33:22PM PDT, Joshua D. Drake wrote:
> 
> On 03/02/2015 03:25 PM, David Kerr wrote:
> >
> >Howdy,
> >
> >I had an instance where a replica fell out of sync with the master.
> >
> >Now it's in in a state where it's unable to catch up because the master has 
> >already removed the WAL segment.
> >
> >(logs)
> >Mar  2 23:10:13 db13 postgres[11099]: [3-1] user=,db=,host= LOG:  streaming 
> >replication successfully connected to primary
> >Mar  2 23:10:13 db13 postgres[11099]: [4-1] user=,db=,host= FATAL:  could 
> >not receive data from WAL stream: FATAL:  requested WAL segment 
> >0006047C001F has already been removed
> >
> >
> >I was under the impression that when you setup streaming replication if you 
> >specify a restore command like : restore_command = 'cp /arch/%f %p'
> >
> >Then even if the slave falls out of sync, and the master removes the WAL 
> >segment, as long as you can still retrieve the WAL files, then it can bring 
> >itself back into sync.
> 
> If the archive command is also set so that the restore command has a
> file to retrieve, then yes it will work that way.

Yeah it is, it's actually pulling the file down. 

Glad that's how it's supposed to work. I'd rather be unlucky then crazy.  =)



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Replication fell out of sync

[David Kerr]

Howdy,

I had an instance where a replica fell out of sync with the master.

Now it's in in a state where it's unable to catch up because the master has 
already removed the WAL segment.

(logs)
Mar  2 23:10:13 db13 postgres[11099]: [3-1] user=,db=,host= LOG:  streaming 
replication successfully connected to primary
Mar  2 23:10:13 db13 postgres[11099]: [4-1] user=,db=,host= FATAL:  could not 
receive data from WAL stream: FATAL:  requested WAL segment 
0006047C001F has already been removed


I was under the impression that when you setup streaming replication if you 
specify a restore command like : restore_command = 'cp /arch/%f %p'

Then even if the slave falls out of sync, and the master removes the WAL 
segment, as long as you can still retrieve the WAL files, then it can bring 
itself back into sync.


But that doesn't seem to be happening.

The restore_command is working
# Slave's $PGDATA/pg_xlog/
-rw--- 1 postgres postgres 16777216 Mar  2 21:29 0006047C001F
-rwx-- 1 postgres postgres 16777216 Mar  2 23:13 RECOVERYXLOG

I'm on PG 9.2.7, which i know is old, but I'm upgrading shortly.

recovery.conf:
standby_mode  = 'on'
primary_conninfo  = 'host=pgmaster port=5432'
restore_command   = 'cp /arch/%f %p'

relevant info from postgresql.conf:
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
hot_standby = on
hot_standby_feedback = true


I know that to avoid this entirely I need to set wal_keep_segments higher, 
although in this particular case it wouldn't have mattered because a rogue 
program slammed the database and basically 32/64/128 WAL segments went by in a 
short span of time.

However, I really thought that as long as PG could get the archived logs i'd be 
able to recover. 

Was I wrong with that assertion or did i just run into a bug?

Thanks


-- 
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] Monitoring number of backends

[David Kerr]

On Wed, Oct 23, 2013 at 12:11:39PM -0500, andy wrote:
- On 10/23/2013 11:07 AM, David Kerr wrote:
- >On Tue, Oct 22, 2013 at 12:41:58PM -0500, andy wrote:
- >- Hi all.
- >-
- >- My website is about to get a little more popular.  I'm trying to add in
- >- some measurements to determine an upper limit of how many concurrent
- >- database connections I'm currently using.
- >-
- >- I've started running this:
- >-
- >- SELECT sum(numbackends) AS count, sum(xact_commit) as ttlcommit FROM
- >- pg_stat_database;
- >-
- >- Every 10 seconds or so.  I don't think its what I want though.  It seems
- >- way too small.  I'm guessing that its not a measure of the previous 10
- >- seconds.  Its a count of how many backends are in use at the exact
- >- moment I run the sql.
- >-
- >- Is there a cumulative count someplace?
- >-
- >- Thanks for your time,
- >-
- >- -Andy
- >
- >You've gotten good info from the other guys on how to scale your're DB
- >but to answer you're original question. I usually use this query:
- >
- >select count(*) from pg_stat_activity where state <> 'idle';
- >
- >That gives you the # of "active" connections to your database and is
- >something you want to try to get good metrics on.
- >
- >Idle connections have some overhead but if Active > # of CPUs your 
- >performance
- >starts to degrade. Now, really that's pretty normal but, ideally, you need 
- >to
- >know what the ratio of Active Connections to # CPUs still gives you 
- >acceptable
- >performance. And that's really based on your app and hardware.
- >
- >
- 
- How often do you run that?  Once a second?  And graph it?  I was doing 
- it every 10 seconds, but it doesn't give me a good view of the system.

I actually have it as a munin module so it runs every few minutes. 
If I'm actually doing a performance test or something I would run it
every second or every 5 / 10 seconds.

The knowledge is only really useful if you have a good trend built up so you
know how your app responds to having a certian # of active connections.


-- 
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] Monitoring number of backends

[David Kerr]

On Tue, Oct 22, 2013 at 12:41:58PM -0500, andy wrote:
- Hi all.
- 
- My website is about to get a little more popular.  I'm trying to add in 
- some measurements to determine an upper limit of how many concurrent 
- database connections I'm currently using.
- 
- I've started running this:
- 
- SELECT sum(numbackends) AS count, sum(xact_commit) as ttlcommit FROM 
- pg_stat_database;
- 
- Every 10 seconds or so.  I don't think its what I want though.  It seems 
- way too small.  I'm guessing that its not a measure of the previous 10 
- seconds.  Its a count of how many backends are in use at the exact 
- moment I run the sql.
- 
- Is there a cumulative count someplace?
- 
- Thanks for your time,
- 
- -Andy

You've gotten good info from the other guys on how to scale your're DB
but to answer you're original question. I usually use this query:

select count(*) from pg_stat_activity where state <> 'idle';

That gives you the # of "active" connections to your database and is
something you want to try to get good metrics on.

Idle connections have some overhead but if Active > # of CPUs your performance
starts to degrade. Now, really that's pretty normal but, ideally, you need to 
know what the ratio of Active Connections to # CPUs still gives you acceptable 
performance. And that's really based on your app and hardware.


-- 
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] PostgreSQL vs Mongo

[David Kerr]

On Wed, Oct 16, 2013 at 09:30:59AM -0600, CS DBA wrote:
- All;
- 
- One of our clients is talking about moving to Mongo for their 
- reporting/data mart.  I suspect the real issue is the architecture of 
- their data mart schema, however I don't want to start pushing back if I 
- can't back it up.
- 
- Anyone have any thoughts on why we would / would not use Mongo for a 
- reporting environment.
- 
- what are the use cases where mongo is a good fit?
- what are the drawbacks long term?
- is mongo a persistent db or simply a big memory cache?
- does mongo have advantages over Postgres hstore?
- etc...
- 
- Thanks in advance...
- 
- /Kevin

I work with both. 

Mongo doesn't really seem approprite for a datamart. Mongo supports 
Map Reduce and has an Aggregation framework (which will give you a lot
of the functionality of SQL but is much more esoteric) 

You need an index for every query you run and every possibly sort order.
Mongo will cancel you're query if the result set hits a certian size
w/o an index.

Doing ad-hoc queries is HARD. and there are no joins. If it's not in
your document you basically have to pull both documents into your app
and join them by hand.

Writes block reads, massive updates (like into a datamart) will need to "yield"
to allow reads to happen and that only happens at a pagefault.

You need to have enough memory to store you're "working set". or performance 
tanks
In a datamart your working set is frequently the whole thing.

People throw around the "Schemaless" thing, but really there is some schema. you
have to know what you want your document to look like. So this means schema 
changes
as you grow your product, etc. 

In a datamart you're not going to use 10gen's idea schema change methodology 
of "Only Apply Data Model Changes when you access a record" That works if 
you're 
ooking up a single document at a time, but not if you're mostly doing range 
scans 
and aggregations.

Mongo is very limited on how it can sort, we have a number of "sort fields" 
added
to our document that give us a different indexable sort order. like you can't
do ORDER BY CASE statements.

IMO Mongo, like most NoSQL solutons, address write scaling and availablity 
by making it easier to do. You can generally shard w/o bothering the application
too much and you get free seamless failover with the replica sets.


Hope this is helpful

Dave


-- 
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] oids on disk not in pg_class

[David Kerr]

On Mon, Oct 07, 2013 at 06:32:57PM -0400, Guy Rouillier wrote:
- On 10/7/2013 5:58 PM, Steve Atkins wrote:
- >
- >On Oct 7, 2013, at 2:48 PM, Guy Rouillier 
- >wrote:
- >
- >>We have a fairly large (1 TB) database we put on all SSDs because
- >>of a very high insert and update rate (38 million rows/day).  As
- >>our business has grown, we've been running into space constraints,
- >>so we went looking for files we might be able to delete.
- >>
- >>We found a large number (662 out of 1465 total ) and size (219 GB)
- >>of files in the data directory whose name does not correspond to
- >>an oid in the pg_class system catalog table.  That amount of space
- >>would address our current space constraint problems.  Some of
- >>these tables are recent (from today), while others are quite old
- >>(large volume in August and May, with some smaller ones as far back
- >>as February.
- >
- >You need to be looking at pg_class.relfilenode, not pg_class.oid.
- >They're often the same value, but often not.
- 
- Steve, thanks for the quick reply.  I reran the calculations using
- relfilenode instead of oid; they are now showing 214 unrecorded
- filenodes consuming 163 GB.  The older tables (on or before May) are no
- longer showing as unrecorded.  Of the filenodes that are still not
- matching pg_class.relfilenode, I see just one that is consuming the
- majority of the space: 614804 (153 files, 163 GB).
- 
- Failed to mention our runtime platform: we are running Enterprise DB
- 9.2.1.3 on 64-bit Linux (Oracle Linux Server release 6.3).  EDB pointed
- us to this note in the PG documentation
- (http://www.postgresql.org/docs/9.2/static/storage-file-layout.html):
- 
- Note that while a table's filenode often matches its OID, this is not
- necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER
- and some forms of ALTER TABLE, can change the filenode while preserving
- the OID. Avoid assuming that filenode and table OID are the same. Also,
- for certain system catalogs including pg_class itself,
- pg_class.relfilenode contains zero.  The actual filenode number of these
- catalogs is stored in a lower-level data structure, and can be obtained
- using the pg_relation_filenode() function.
- 
- So, I ran "select pg_relation_filenode(614804)" and got no results.  Any
- suggestions on how I can uncover the identify of this node?
- 
- Thanks much.

You could try oid2name: 
http://www.postgresql.org/docs/current/static/oid2name.html


-- 
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] Trouble installing psycopg2

[David Kerr]

On Thu, Sep 26, 2013 at 02:56:14PM -0400, Augori wrote:
- Thanks, for the replies Chris and David.
- 
- Chris, I couldn't find any psycopg files under my Python installs, so I
- decided to try David's advice and yum install python-psycopg2
- It reported success, but it installed it under
- ../usr/lib64/python2.4/site-packages
- 
- I'm working with Python2.7.5, so psycopg2 won't import.   It believe that
- psycopg2 is not written for 2.4 or below.  That's why I installed 2.7.5.
- So my question now is, how can I get yum to install it in the 2.7
- site-packages?
- 
- Thanks.
- 
- PS.  About the virtualenv suggestion, I had tried using use virtualenv and
- virtualenvwrapper, following the instructions on this nice site.
- http://bda.ath.cx/blog/2009/04/08/installing-python-26-in-centos-5-or-rhel5/
- But, I couldn't get the bashrc to run without error, after adding this
- update
- 
- source /opt/bin/virtualenvwrapper_bashrc
- 
- So, I thought I'd throw caution to the wind and skip that part.
- (that's how I got to easy_intall, which at least allowed me to specify
- which python version)

Looking at the easy_install on my system it uses python26
head -1 /usr/bin/easy_install
#!/usr/bin/python2.6

so if you use easy_install you probably just installed it to the 2.6 stuff.

If you need 2.7 for your stuff then you'll definitely want to get virtualenv
working. If you can live with 2.6 then get rid of 2.7 on your machine
and work with the yum packages.

A better virtualenvwrapper setup doc is here (not from 2009)
http://virtualenvwrapper.readthedocs.org/en/latest/



-- 
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] Trouble installing psycopg2

[David Kerr]

On Thu, Sep 26, 2013 at 01:01:54PM -0400, Laura Tateosian wrote:
- Hi,  I'm trying to install psycopg2 on a Centos 5, 64-bit machine.   I have
- both 2.4 and 2.7 Python versions on this machine.  I attempted to install
- using
- easy-install2.7 psycopg2
- 
- The install is not working.  (I can't import psycopg2 in a python 2.7
- session).   yum doesn't list it as installed.  But when I try to install it
- again, easy_install responds that it's already installed.  That's the short
- version of the problem, more details below that may or may not be helpful.
- 
- Thanks,
- LT
- 
- 
- 
- 
- At first, easy-install2.7 psycopg2 gave me similar errors to what's
- described on this page:
- 
http://stackoverflow.com/questions/9600111/install-pg-config-in-centos-5-5-without-yum(something
- to do with pg_config not found).  So I followed the advice of
- installing devel
- 
- yum install postgresql84-devel
- 
- ...succeeded.
- 
- Then, I  "easy-install2.7 psycopg2" again.  In fact I piped the output to a
- file and got what I have below, but it also flashed up an error message
- which didn't get into this file and I can't see on the history.  But from
- that second  that I saw it, I know it had __FILE__ in it.   Now I'm in this
- installed/but not installed kind of limbo.
- ...
- Excluding Packages in global exclude list
- Finished
- Available Packages
- python-psycopg2.x86_64  2.0.14-2.el5
- epel
- python-psycopg2-doc.x86_64  2.0.14-2.el5
- epel
- python-psycopg2-zope.x86_64 2.0.14-2.el5
- epel
- root@server [/opt/bin]# easy_install-2.7 psycopg2
- Searching for psycopg2
- Searching for psycopg2
- Reading http://pypi.python.org/simple/psycopg2/
- Reading http://initd.org/projects/psycopg2
- Best match: psycopg2 2.5.1
- Downloading
- https://pypi.python.org/packages/source/p/psycopg2/psycopg2-2.5.1
- .tar.gz#md5=1b433f83d50d1bc61e09026e906d84c7
- Processing psycopg2-2.5.1.tar.gz
- Writing /tmp/easy_install-UYNn1g/psycopg2-2.5.1/setup.cfg
- Running psycopg2-2.5.1/setup.py -q bdist_egg --dist-dir
- /tmp/easy_install-UYN
- n1g/psycopg2-2.5.1/egg-dist-tmp-FLKSTs
- Adding psycopg2 2.5.1 to easy-install.pth file
- 
- Installed
- /usr/local/lib/python2.7/site-packages/psycopg2-2.5.1-py2.7-linux-x
- 86_64.egg
- Processing dependencies for psycopg2
- Finished processing dependencies for psycopg2


So easy_install isn't going to intall the yum version, and it may install it 
somewhere
that the system python isn't looking for it.

That's generally why you  don't want to use easy_install or pip if you can 
avoid it.
If you DO use them you should use virtualenv and virtualenvwrapper.

Since you have an OS package, Your best bet is to do 'yum install 
python-psycopg2' 
and see if that works for you.



-- 
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] SQL Path in psql

[David Kerr]

On Fri, Sep 06, 2013 at 10:45:26AM -0700, David Johnston wrote:
- lup wrote
- >>
- >>
- > I wonder if this would at least get the full path on-screen for a c/p
- >  \! for d in  $SQLPATH; do find $d -name 
- > 
- > ; done
- > 
- > That said, I would down-vote this suggestion.  I tend to put sql files 
- > in amongst my various project dirs and maintaining the envvar isn't 
- > worth it.
- > 
- > Anything I re-use I make into a function.
- 
- Usually a down-vote there is something that can go wrong if the feature is
- implemented.  Simply not personally having a use for said feature normally
- results in a "+0" vote or something similar.
- 
- The big issue with environment variables is their ability to have resolution
- conflicts. I think the general idea has merit though setting symbolic-links
- (in Linux variants at least) can get you some of the way there in a less
- error-prone (but more verbose) way.
- 
- Ultimately feature requests get made to here then end up on the ToDo listing
- if the idea has traction.
- 
- David J.

I suspect this feature makes more sense on a windows platform. On linux where 
we can
go psql -f ${SQLPATH}/file.sql . it becomes less pressing. Even Oracle
on unix/linux where you can go sqlplus 

Re: [GENERAL] About postgres scale out

[David Kerr]

On Wed, Jul 17, 2013 at 03:10:37PM +0800, Xiang Jun Wu wrote:
- Hello,
- 
- I'd like to ask a common question about scale out for postgres.
- 
- Our current data volume is about 500GB ~ 1TB in one pg cluster(postgres 9.2). 
We've set up master/slave replication to keep sync. 
- To reach better performance from apps side,  we want to use pgbounder like 
app to split read/write on multiple servers instead of single server.
- Sometimes, there is repl lag between master and slave pg servers (e.g. backup 
or other issues unexpected). Is there flexible solution from pgbounder (or 
other ways) to identify that and switch read/write to not staled server smartly?
- 
- 
- Thank you in advance!

PgPool does this for you.

http://www.pgpool.net/docs/latest/pgpool-en.html#replication_mode
---
delay_threshold V3.0 -
Specifies the maximum tolerated replication delay of the standby against the 
primary server in WAL bytes. If
the delay exceeds delay_threshold, pgpool-II does not send SELECT queries to 
the standby server anymore.
Everything is sent to the primary server even if load balance mode is enabled, 
until the standby has
caught-up. If delay_threshold is 0 or sr checking is disabled, the delay 
checking is not performed. This
check is performed every 'sr_check_period'. The default value for 
delay_threshold is 0.
You need to reload pgpool.conf if you change this directive.


-- 
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] Build RPM from Postgres Source

[David Kerr]

On Fri, Jul 12, 2013 at 02:37:19PM -0700, ktewari1 wrote:
- Hi,
- I need to have some different settings(like NAMEDATALEN  etc.) and
- that's why I'm trying to build postgres from the source and to create an rpm
- to be send for install. 
- 
- Now, the build works fine but, I don't see a way to create an RPM.
- 
- I'll greatly appreciate any help.
- 
- Thanks,
- Kul 

I haven't tried this yet, but I've been wanting to. it seems pretty awesome:
https://github.com/jordansissel/fpm


-- 
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] LONG delete with LOTS of FK's

[David Kerr]

On Thu, May 16, 2013 at 06:01:51PM -0500, Larry Rosenman wrote:
- On 2013-05-16 17:52, David Kerr wrote:
- >On Fri, May 10, 2013 at 11:01:15AM -0500, Larry Rosenman wrote:
- >- On 2013-05-10 10:57, Tom Lane wrote:
- >- >Larry Rosenman  writes:
- >- >On 2013-05-10 09:14, Tom Lane wrote:
- >- >... and verify you get a cheap plan for each referencing table.
- >- >
- >- >We don't :(
- >- >
- >- >Ugh.  I bet the problem is that in some of these tables, there are 
- >lots
- >- >and lots of duplicate account ids, such that seqscans look like a 
- >good
- >- >bet when searching for an otherwise-unknown id.  You don't see this
- >- >with a handwritten test for a specific id because then the planner 
- >can
- >- >see it's not any of the common values.
- >- >
- >- >9.2 would fix this for you --- any chance of updating?
- >- >
- >- >  regards, tom lane
- >- I'll see what we can do.  I was looking for a reason, this may be it.
- >-
- >- Thanks for all your help.
- >
- >I haven't seen an explain for this badboy, maybe I missed it (even just 
- >a
- >plain explain might be useful) but you may be running into a situation 
- >where
- >the planner is trying to materialize or hash 2 big tables.
- >
- >I've actually run into that in the past and had some success in PG9.1 
- >running
- >with enable_material=false for some queries.
- >
- >It might be worth a shot to play with that and
- >enable_hashagg/enable_hashjoin=false
- >(If you get a speedup, it points to some tuning/refactoring that could 
- >happen)
- >
- >Dave
- I'll take a look tomorrow, but we WERE seeing Seq Scan's against 
- multi-million
- row tables, so I suspect Tom is right on with the replanning that's in 
- 9.2 fixing
- it, and I'm in the process of validating that.

That seems likely, although you could try enable_seqscan=false as well.

Dave


-- 
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] LONG delete with LOTS of FK's

[David Kerr]

On Fri, May 10, 2013 at 11:01:15AM -0500, Larry Rosenman wrote:
- On 2013-05-10 10:57, Tom Lane wrote:
- >Larry Rosenman  writes:
- >On 2013-05-10 09:14, Tom Lane wrote:
- >... and verify you get a cheap plan for each referencing table.
- >
- >We don't :(
- >
- >Ugh.  I bet the problem is that in some of these tables, there are lots
- >and lots of duplicate account ids, such that seqscans look like a good
- >bet when searching for an otherwise-unknown id.  You don't see this
- >with a handwritten test for a specific id because then the planner can
- >see it's not any of the common values.
- >
- >9.2 would fix this for you --- any chance of updating?
- >
- > regards, tom lane
- I'll see what we can do.  I was looking for a reason, this may be it.
- 
- Thanks for all your help.

I haven't seen an explain for this badboy, maybe I missed it (even just a 
plain explain might be useful) but you may be running into a situation where 
the planner is trying to materialize or hash 2 big tables.

I've actually run into that in the past and had some success in PG9.1 running 
with enable_material=false for some queries.

It might be worth a shot to play with that and 
enable_hashagg/enable_hashjoin=false
(If you get a speedup, it points to some tuning/refactoring that could happen)

Dave


-- 
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] AWS and postgres issues

[David Kerr]

On Apr 8, 2013, at 5:52 PM, Tatsuo Ishii  wrote:

>> 2013/4/9 Tatsuo Ishii :
 While debugging this with a coworker we figured out that pg_ctl was 
 attaching to the tty and then it clicked
 that we needed to be using '-t' where I was using -T or (neither).
>>> 
>>> Are you sure? I checked the pg_ctl source code and could not find any
>>> place attaching to the tty.
>> 
>> I think he means the ssh options -t and -T
> 
> Yes, I know. In my understanding, he is saying because pg_ctl attaches
> to the tty, and ssh should be executed with -t (force ssh to allocate
> pseudo-tty).
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 

Yeah, and i really expected to NOT want to attach to the pseudo-tty.

I was also sort of hoping that it was dropping packets or something like that 
because
then it might have been a similar problem to the one i reported here:
http://www.pgpool.net/pipermail/pgpool-general/2013-February/001418.html

Re: [GENERAL] AWS and postgres issues

[David Kerr]

On Mon, Apr 08, 2013 at 04:24:45PM -0700, David Kerr wrote:
- On Mon, Apr 08, 2013 at 02:59:56PM -0700, David Kerr wrote:
- - On Mon, Apr 08, 2013 at 02:09:42PM -0700, David Kerr wrote:
- - - On Mon, Apr 08, 2013 at 02:14:14PM -0600, Quentin Hartman wrote:
- - - - What version of pgpool are you using?
- - - - 
- - - - Are there other commands you have a problem with? I would suspect that 
the
- - - - restart is causing the postgres server to go away, pgpool decides to
- - - - disconnect, and then it has to be manually added back to the cluster.
- - - - Unless of course you've got automatic failback setup, but even then I 
would
- - - - expect that command to do weird things when issued through middleware 
like
- - - - pgpool, regardless of what sort of infrastructure you are running on.
- - - - 
- - - - QH
- - - 
- - - This is actually from the command line, PgPool isn't involved at all. I 
just
- - - mentioned it to give some context.
- - 
- - I had a brief conversation with Quentin offline which indicated that I 
wasn't
- - being nearly clear nor direct enough.
- - 
- - I believe that this probelm is specific to AWS+Postgres. (and possibly 
specific to 
- - VPC+Amazon Linux). Non postgres commands run fine, and even psql works 
fine. 
- - so far just pg_ctl fails.
- - 
- - I'm running the command directly from an interactive shell, so this is as 
basic as it gets.
- - 
- - The command runs correctly on the remote server, it just never exits the 
ssh connection.
- - specifically I never get: "debug2: channel 0: rcvd close  " as if the 
packet gets 
- - dropped every time.
- - 
- - I don't believe it's a network hiccup because I can reproduce it every 
time. 
- - 
- - It's likely something with Amazon's infrastructure that's eating it, but 
whateever
- - it is, it seems to specifically not like pg_ctl.
- - 
- - Here is what I see happen:
- - [pgpool@ccpgp05 ~]$ ssh -vvv postgres@10.0.1.30 '/usr/pgsql-9.2/bin/pg_ctl 
-D /db/pg -m fast restart'
- - OpenSSH_6.1p1, OpenSSL 1.0.1e-fips 11 Feb 2013
- - [..snip..]
- - debug1: Sending command: /usr/pgsql-9.2/bin/pg_ctl -D /db/pg -m fast restart
- - [..snip..]
- - waiting for server to shut down done
- - server stopped
- - server starting
- - [..snip..]
- - debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
- - debug1: client_input_channel_req: channel 0 rtype e...@openssh.com reply 0
- - debug2: channel 0: rcvd eow
- - debug2: channel 0: close_read
- - debug2: channel 0: input open -> closed
- - ^Cdebug1: channel 0: free: client-session, nchannels 1  # 
< This is where I ^C it
- - debug3: channel 0: status: The following connections are open:
- -   #0 client-session (t4 r0 i3/0 o0/0 fd -1/5 cc -1)
- - 
- - 
- - Notice the input open -> closed is where it basically hangs
- - 
- - Now look at:
- - [pgpool@ccpgp05 ~]$ ssh -vvv postgres@10.0.1.30 'ls -ltr'
- - OpenSSH_6.1p1, OpenSSL 1.0.1e-fips 11 Feb 2013
- - [..snip..]
- - debug1: Sending command: ls -ltr
- - debug2: channel 0: output open -> drain
- - debug1: channel 0: forcing write
- - total 8
- - drwx-- 4 postgres postgres 4096 Apr  4 22:50 9.2
- - drwx-- 2 postgres postgres 4096 Apr  5 22:58 bin
- - [..snip..]
- - debug2: channel 0: input open -> closed
- - debug2: channel 0: rcvd close  
- - debug3: channel 0: will not send data after close
- - debug2: channel 0: almost dead
- - debug2: channel 0: gc: notify user
- - debug2: channel 0: gc: user detached
- - debug2: channel 0: send close
- - debug2: channel 0: is dead
- - debug2: channel 0: garbage collecting
- - debug1: channel 0: free: client-session, nchannels 1
- - debug3: channel 0: status: The following connections are open:
- -   #0 client-session (t4 r0 i3/0 o3/0 fd -1/-1 cc -1)
- - 
- - Transferred: sent 2456, received 2448 bytes, in 0.0 seconds
- - Bytes per second: sent 105666.4, received 105322.3
- - debug1: Exit status 0
- 
- 
- I've verified that it's not related to the linux flavor. I've tried it with a 
Server on both Amazon Linux
- and Ubuntu. And with a client on Amazon Linux and my own desktop.
- 
- I can't be the only person using PG in AWS+VPC, can someone else with a 
similar test bed give it a shot
- and tell me if it works for them? (at least then I'd know if it's likely 
something I'm doing...)
- 
- Thanks

While debugging this with a coworker we figured out that pg_ctl was attaching 
to the tty and then it clicked
that we needed to be using '-t' where I was using -T or (neither).

So mystery solved!


-- 
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] AWS and postgres issues

[David Kerr]

On Mon, Apr 08, 2013 at 02:59:56PM -0700, David Kerr wrote:
- On Mon, Apr 08, 2013 at 02:09:42PM -0700, David Kerr wrote:
- - On Mon, Apr 08, 2013 at 02:14:14PM -0600, Quentin Hartman wrote:
- - - What version of pgpool are you using?
- - - 
- - - Are there other commands you have a problem with? I would suspect that the
- - - restart is causing the postgres server to go away, pgpool decides to
- - - disconnect, and then it has to be manually added back to the cluster.
- - - Unless of course you've got automatic failback setup, but even then I 
would
- - - expect that command to do weird things when issued through middleware like
- - - pgpool, regardless of what sort of infrastructure you are running on.
- - - 
- - - QH
- - 
- - This is actually from the command line, PgPool isn't involved at all. I just
- - mentioned it to give some context.
- 
- I had a brief conversation with Quentin offline which indicated that I wasn't
- being nearly clear nor direct enough.
- 
- I believe that this probelm is specific to AWS+Postgres. (and possibly 
specific to 
- VPC+Amazon Linux). Non postgres commands run fine, and even psql works fine. 
- so far just pg_ctl fails.
- 
- I'm running the command directly from an interactive shell, so this is as 
basic as it gets.
- 
- The command runs correctly on the remote server, it just never exits the ssh 
connection.
- specifically I never get: "debug2: channel 0: rcvd close  " as if the packet 
gets 
- dropped every time.
- 
- I don't believe it's a network hiccup because I can reproduce it every time. 
- 
- It's likely something with Amazon's infrastructure that's eating it, but 
whateever
- it is, it seems to specifically not like pg_ctl.
- 
- Here is what I see happen:
- [pgpool@ccpgp05 ~]$ ssh -vvv postgres@10.0.1.30 '/usr/pgsql-9.2/bin/pg_ctl -D 
/db/pg -m fast restart'
- OpenSSH_6.1p1, OpenSSL 1.0.1e-fips 11 Feb 2013
- [..snip..]
- debug1: Sending command: /usr/pgsql-9.2/bin/pg_ctl -D /db/pg -m fast restart
- [..snip..]
- waiting for server to shut down done
- server stopped
- server starting
- [..snip..]
- debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
- debug1: client_input_channel_req: channel 0 rtype e...@openssh.com reply 0
- debug2: channel 0: rcvd eow
- debug2: channel 0: close_read
- debug2: channel 0: input open -> closed
- ^Cdebug1: channel 0: free: client-session, nchannels 1  # 
< This is where I ^C it
- debug3: channel 0: status: The following connections are open:
-   #0 client-session (t4 r0 i3/0 o0/0 fd -1/5 cc -1)
- 
- 
- Notice the input open -> closed is where it basically hangs
- 
- Now look at:
- [pgpool@ccpgp05 ~]$ ssh -vvv postgres@10.0.1.30 'ls -ltr'
- OpenSSH_6.1p1, OpenSSL 1.0.1e-fips 11 Feb 2013
- [..snip..]
- debug1: Sending command: ls -ltr
- debug2: channel 0: output open -> drain
- debug1: channel 0: forcing write
- total 8
- drwx-- 4 postgres postgres 4096 Apr  4 22:50 9.2
- drwx-- 2 postgres postgres 4096 Apr  5 22:58 bin
- [..snip..]
- debug2: channel 0: input open -> closed
- debug2: channel 0: rcvd close  
- debug3: channel 0: will not send data after close
- debug2: channel 0: almost dead
- debug2: channel 0: gc: notify user
- debug2: channel 0: gc: user detached
- debug2: channel 0: send close
- debug2: channel 0: is dead
- debug2: channel 0: garbage collecting
- debug1: channel 0: free: client-session, nchannels 1
- debug3: channel 0: status: The following connections are open:
-   #0 client-session (t4 r0 i3/0 o3/0 fd -1/-1 cc -1)
- 
- Transferred: sent 2456, received 2448 bytes, in 0.0 seconds
- Bytes per second: sent 105666.4, received 105322.3
- debug1: Exit status 0


I've verified that it's not related to the linux flavor. I've tried it with a 
Server on both Amazon Linux
and Ubuntu. And with a client on Amazon Linux and my own desktop.

I can't be the only person using PG in AWS+VPC, can someone else with a similar 
test bed give it a shot
and tell me if it works for them? (at least then I'd know if it's likely 
something I'm doing...)

Thanks


-- 
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] AWS and postgres issues

[David Kerr]

On Mon, Apr 08, 2013 at 02:09:42PM -0700, David Kerr wrote:
- On Mon, Apr 08, 2013 at 02:14:14PM -0600, Quentin Hartman wrote:
- - What version of pgpool are you using?
- - 
- - Are there other commands you have a problem with? I would suspect that the
- - restart is causing the postgres server to go away, pgpool decides to
- - disconnect, and then it has to be manually added back to the cluster.
- - Unless of course you've got automatic failback setup, but even then I would
- - expect that command to do weird things when issued through middleware like
- - pgpool, regardless of what sort of infrastructure you are running on.
- - 
- - QH
- 
- This is actually from the command line, PgPool isn't involved at all. I just
- mentioned it to give some context.

I had a brief conversation with Quentin offline which indicated that I wasn't
being nearly clear nor direct enough.

I believe that this probelm is specific to AWS+Postgres. (and possibly specific 
to 
VPC+Amazon Linux). Non postgres commands run fine, and even psql works fine. 
so far just pg_ctl fails.

I'm running the command directly from an interactive shell, so this is as basic 
as it gets.

The command runs correctly on the remote server, it just never exits the ssh 
connection.
specifically I never get: "debug2: channel 0: rcvd close  " as if the packet 
gets 
dropped every time.

I don't believe it's a network hiccup because I can reproduce it every time. 

It's likely something with Amazon's infrastructure that's eating it, but 
whateever
it is, it seems to specifically not like pg_ctl.

Here is what I see happen:
[pgpool@ccpgp05 ~]$ ssh -vvv postgres@10.0.1.30 '/usr/pgsql-9.2/bin/pg_ctl -D 
/db/pg -m fast restart'
OpenSSH_6.1p1, OpenSSL 1.0.1e-fips 11 Feb 2013
[..snip..]
debug1: Sending command: /usr/pgsql-9.2/bin/pg_ctl -D /db/pg -m fast restart
[..snip..]
waiting for server to shut down done
server stopped
server starting
[..snip..]
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: client_input_channel_req: channel 0 rtype e...@openssh.com reply 0
debug2: channel 0: rcvd eow
debug2: channel 0: close_read
debug2: channel 0: input open -> closed
^Cdebug1: channel 0: free: client-session, nchannels 1  # < 
This is where I ^C it
debug3: channel 0: status: The following connections are open:
  #0 client-session (t4 r0 i3/0 o0/0 fd -1/5 cc -1)


Notice the input open -> closed is where it basically hangs

Now look at:
[pgpool@ccpgp05 ~]$ ssh -vvv postgres@10.0.1.30 'ls -ltr'
OpenSSH_6.1p1, OpenSSL 1.0.1e-fips 11 Feb 2013
[..snip..]
debug1: Sending command: ls -ltr
debug2: channel 0: output open -> drain
debug1: channel 0: forcing write
total 8
drwx-- 4 postgres postgres 4096 Apr  4 22:50 9.2
drwx-- 2 postgres postgres 4096 Apr  5 22:58 bin
[..snip..]
debug2: channel 0: input open -> closed
debug2: channel 0: rcvd close  
debug3: channel 0: will not send data after close
debug2: channel 0: almost dead
debug2: channel 0: gc: notify user
debug2: channel 0: gc: user detached
debug2: channel 0: send close
debug2: channel 0: is dead
debug2: channel 0: garbage collecting
debug1: channel 0: free: client-session, nchannels 1
debug3: channel 0: status: The following connections are open:
  #0 client-session (t4 r0 i3/0 o3/0 fd -1/-1 cc -1)

Transferred: sent 2456, received 2448 bytes, in 0.0 seconds
Bytes per second: sent 105666.4, received 105322.3
debug1: Exit status 0


Thanks.


-- 
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] AWS and postgres issues

[David Kerr]

On Mon, Apr 08, 2013 at 02:14:14PM -0600, Quentin Hartman wrote:
- What version of pgpool are you using?
- 
- Are there other commands you have a problem with? I would suspect that the
- restart is causing the postgres server to go away, pgpool decides to
- disconnect, and then it has to be manually added back to the cluster.
- Unless of course you've got automatic failback setup, but even then I would
- expect that command to do weird things when issued through middleware like
- pgpool, regardless of what sort of infrastructure you are running on.
- 
- QH

This is actually from the command line, PgPool isn't involved at all. I just
mentioned it to give some context.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] AWS and postgres issues

[David Kerr]

Howdy,

I'm having a couple of problems that I believe are related to AWS and I'm 
wondering
if anyone's seen them / overcome them.

Brief background, I'm running PG 9.2.4 in a VPC on Amazon Linux.
I'm also (attempting) to use PgPool for load balancing/failover.

The overall problem is that it seems like some Postgres commands / operations 
get truncated
at a network/packet level.

For example when I try to run ( From PgPool Server => Postgres Server)
ssh -vvv -T postgres@10.0.1.30 "/usr/pgsql-9.2/bin/pg_ctl -D /db/pg -m fast 
restart"

The command completes successfully on the Postgres server, and the process goes 
away, 
however on the PgPool server that process never dies, it just hangs.

PgPool box:
ps -ef|grep -i ssh|grep -v sshd|grep -v grep
pgpool   27196 26241  0 19:57 pts/000:00:00 ssh -vvv postgres@10.0.1.30 
bash -c '/usr/pgsql-9.2/bin/pg_ctl -D /db/pg -m fast restart'

Postgres box:
ps -ef|grep -i pg_ctl
postgres  2376 26436  0 19:58 pts/100:00:00 grep -i pg_ctl

Other non-postgres commands run over ssh return as expected.

I don't know if this is helpful, but here's an strace of the process:
setsockopt(3, SOL_IP, IP_TOS, [8], 4)   = 0
time(NULL)  = 1365450845
select(7, [3], [3], NULL, NULL) = 1 (out [3])
time(NULL)  = 1365450845
write(3, 
"2O\235qZ\333\2160\333\371\372\374\215\204\337X)\215\321J\5\343\240(\325\316\224W\370(7+"...,
 176) = 176
time(NULL)  = 1365450845
select(7, [3], [], NULL, NULL)  = 1 (in [3])
time(NULL)  = 1365450845
read(3, 
"\303\223BDr5\376I\304Io\4\25\33\6\25>L\214\f_~J\342gc#w\365\5\320\242"..., 
8192) = 80
time(NULL)  = 1365450845
select(7, [3 4], [], NULL, NULL)= 1 (in [3])
time(NULL)  = 1365450845
read(3, 
"\352\366A\360c\315\t\310\361\24z\217H\t\314\342\361\322\335}l6\302)\223\343\361\27&{\234H"...,
 8192) = 128
time(NULL)  = 1365450845
select(7, [3 4], [5], NULL, NULL)   = 1 (out [5])
time(NULL)  = 1365450845
write(5, "waiting for server to shut down."..., 35waiting for server to shut 
down) = 35
time(NULL)  = 1365450845
select(7, [3 4], [], NULL, NULL)= 1 (in [3])
time(NULL)  = 1365450846
read(3, 
"c\264\317\303Q\222\214b\323>\300\354\306j\36\31+\342\360\325Y8\345\322\211?<\0210n\253\211"...,
 8192) = 64
time(NULL)  = 1365450846
select(7, [3 4], [5], NULL, NULL)   = 1 (out [5])
time(NULL)  = 1365450846
write(5, " done\nserver stopped\n", 21 done
server stopped
) = 21
time(NULL)  = 1365450846
select(7, [3 4], [], NULL, NULL)= 1 (in [3])
time(NULL)  = 1365450846
read(3, 
"\253\210\306\251\343lF^6\32|v\374fe\23\32\3ylZ\325[\205\344,x@\4\201\213\351"...,
 8192) = 64
time(NULL)  = 1365450846
select(7, [3 4], [5], NULL, NULL)   = 1 (out [5])
time(NULL)  = 1365450846
write(5, "server starting\n", 16server starting
)   = 16
time(NULL)  = 1365450846
select(7, [3 4], [], NULL, NULL)= 1 (in [3])
time(NULL)  = 1365450846
read(3, "\373 
\347w\354%\314<\6\215\314\207\7\202\274q\341:\270t\366\375\242{9\207:\222\374jy\373"...,
 8192) = 128
close(4)= 0
time(NULL)  = 1365450846
select(7, [3], [], NULL, NULL

and the same thing run with ssh -

debug1: channel 0: new [client-session]
debug3: ssh_session2_open: channel_new: 0
debug2: channel 0: send open
debug1: Requesting no-more-sessi...@openssh.com
debug1: Entering interactive session.
debug2: callback start
debug2: fd 3 setting TCP_NODELAY
debug3: packet_set_tos: set IP_TOS 0x08
debug2: client_session2_setup: id 0
debug1: Sending environment.
debug3: Ignored env HOSTNAME
debug3: Ignored env SHELL
debug3: Ignored env TERM
debug3: Ignored env HISTSIZE
debug3: Ignored env EC2_AMITOOL_HOME
debug3: Ignored env OLDPWD
debug3: Ignored env USER
debug3: Ignored env LS_COLORS
debug3: Ignored env EC2_HOME
debug3: Ignored env MAIL
debug3: Ignored env PATH
debug3: Ignored env PWD
debug3: Ignored env JAVA_HOME
debug1: Sending env LANG = en_US.UTF-8
debug2: channel 0: request env confirm 0
debug3: Ignored env AWS_CLOUDWATCH_HOME
debug3: Ignored env AWS_IAM_HOME
debug3: Ignored env HISTCONTROL
debug3: Ignored env SHLVL
debug3: Ignored env HOME
debug3: Ignored env AWS_PATH
debug3: Ignored env AWS_AUTO_SCALING_HOME
debug3: Ignored env LOGNAME
debug3: Ignored env AWS_ELB_HOME
debug3: Ignored env LESSOPEN
debug3: Ignored env AWS_RDS_HOME
debug3: Ignored env G_BROKEN_FILENAMES
debug3: Ignored env _
debug1: Sending command: bash -c '/usr/pgsql-9.2/bin/pg_ctl -D /db/pg -m fast 

Re: [GENERAL] PG9.2.3. Query hanging: SELECT count(*) FROM pg_catalog.pg_class...

[David Kerr]

On Sat, Feb 16, 2013 at 10:30:44AM -0800, Kevin Grittner wrote:
- David Kerr  wrote:
- 
- > Also, if anyone else stumbles upon this, it only seems to happen with jruby.
- 
- > I have standard ruby programs where this does not occur.
- 
- It sounds like it is at least possible that it is the issue I described here:
- 
- http://www.postgresql.org/message-id/4afbe87202250002c...@gw.wicourts.gov
- 

Thanks, 

I don't think that's this particular issue, but it's something I also need to 
watch out for!



-- 
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] PG9.2.3. Query hanging: SELECT count(*) FROM pg_catalog.pg_class...

[David Kerr]

On Fri, Feb 15, 2013 at 07:01:20PM -0600, Ren Romero Benavides wrote:
- I can confirm it comes from pgpool, and is very weird , I guess the pgpool 
list 
- would be a better fit.
- Cheers.

hmm, ok thanks I'll post there as well.

Also, if anyone else stumbles upon this, it only seems to happen with jruby.
I have standard ruby programs where this does not occur.



- On Friday, February 15, 2013 01:58:55 PM David Kerr wrote:
- > Howdy!
- > 
- > This query is coming from PgPool I believe.
- > 
- > SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname =
- > 'import_jobs' AND c.relpersistence = 'u'
- > 
- > This is a very small database, like 10/15 tables, it's basically empty.
- > If i run this query manually, it comes back immediatly.
- > However according to this it's been running for about an hour.
- > 
- > v3=# select now();
- >   now
- > ---
- >  2013-02-15 21:49:34.470466+00
- > (1 row)
- > 
- > -[ RECORD 5
- > ]+-
- >  datid| 16389
- > datname  | v3
- > pid  | 13905
- > usesysid | 20415
- > usename  | workling
- > application_name |
- > client_addr  | 10.0.0.132
- > client_hostname  |
- > client_port  | 58009
- > backend_start| 2013-02-15 20:49:09.098768+00
- > xact_start   | 2013-02-15 20:49:09.189625+00
- > query_start  | 2013-02-15 20:49:09.198742+00
- > state_change | 2013-02-15 20:49:09.198743+00
- > waiting  | f
- > state| active
- > query| SELECT count(*) FROM pg_catalog.pg_class AS c WHERE
- > c.relname = 'import_jobs' AND c.relpersistence = 'u'
- > 
- > (I actually restarted the application and now there are 4 of these)
- > 
- > Any idea why the query would hang like this?
- > 
- > I don't see it waiting on locks or anything like that.
- > 
- > any ideas?
- > 
- > Thanks
- -- 
- René Romero Benavides @iCodeiExist @PgsqlMx 
- 
- Postgresql Tips en español para la comunidad de México e Hispanoamérica.
- http://postgresql.org.mx 
- 
- 
- 
- 
- 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PG9.2.3. Query hanging: SELECT count(*) FROM pg_catalog.pg_class...

[David Kerr]

Howdy!

This query is coming from PgPool I believe.

SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname = 'import_jobs' 
AND c.relpersistence = 'u'

This is a very small database, like 10/15 tables, it's basically empty.  
If i run this query manually, it comes back immediatly. 
However according to this it's been running for about an hour.

v3=# select now();
  now
---
 2013-02-15 21:49:34.470466+00
(1 row)

-[ RECORD 5 
]+-
datid| 16389
datname  | v3
pid  | 13905
usesysid | 20415
usename  | workling
application_name |
client_addr  | 10.0.0.132
client_hostname  |
client_port  | 58009
backend_start| 2013-02-15 20:49:09.098768+00
xact_start   | 2013-02-15 20:49:09.189625+00
query_start  | 2013-02-15 20:49:09.198742+00
state_change | 2013-02-15 20:49:09.198743+00
waiting  | f
state| active
query| SELECT count(*) FROM pg_catalog.pg_class AS c WHERE 
c.relname = 'import_jobs' AND c.relpersistence = 'u'

(I actually restarted the application and now there are 4 of these)

Any idea why the query would hang like this?

I don't see it waiting on locks or anything like that.

any ideas?

Thanks


-- 
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] Calculating Replication Lag - units

[David Kerr]

On Tue, Jun 26, 2012 at 09:13:44AM -0700, Steve Crawford wrote:
- On 06/26/2012 08:16 AM, David Kerr wrote:
- >On 06/26/2012 05:11 AM, Stuart Bishop wrote:
- >>On Tue, Jun 26, 2012 at 6:21 AM, David Kerr  wrote:
- >>>On Mon, Jun 25, 2012 at 02:17:22PM -0700, Steve Crawford wrote:
- >>>- On 06/25/2012 01:17 PM, David Kerr wrote:
- >>>->Howdy,
- >>>->
- >>>->When calculating Replication lag, I know that we have to compare the
- >>>->pg_current_xlog_location
- >>>->to pg_last_xlog_receive_location, etc. but what I'm trying to 
- >>>figure out
- >>>->is what are
- >>>->the units that I'm left with after the calculation.
- >>>->
- >>>->(i.e., does the xlog_location imply some time value?)
- >>>->
- >>>->Here's the output of the (slightly modified script)
- >>>->Master: 5003964876715
- >>>->Receive: 5003964876715
- >>>->Replay: 5003964765203
- >>>->
- >>>->receive.value 0
- >>>->apply.value 111512
- >>>->
- >>>->111512 isn't inherently useful to me on its own.
- >>>->
- >>>->Any tips?
- >>>->
- >>>- How about now()-pg_last_xact_replay_timestamp() (however this can 
- >>>be a
- >>>- large number if there have not been any recent transactions on the
- >>>- master). I suppose you could do something like:
- >>>-
- >>>- case when pg_last_xlog_receive_location() =
- >>>- pg_last_xlog_replay_location() then '0 seconds'::interval
- >>>- else now()-pg_last_xact_replay_timestamp() end as log_delay;
- >>>
- >>>i don't know for sure that 111512 is a time value.. that's kind of
- >>>what i'm wondering. If i knew that it was like miliseconds or something
- >>>that would be helpful.
- >>
- >>On the hot standby:
- >>
- >> SELECT now()-pg_last_xact_replay_timestamp() AS lag;
- >>
- >>This gives you the lag time as a PostgreSQL interval.
- >>
- >>(It also might give you a value if you run it on a database that is
- >>not a hot standby if it started in recovery mode).
- >>
- >>It seems difficult or impossible to calculate this on the master.
- >>
- >>
- >
- >Ah, awesome. I don't need to calculate it on the master so that's 
- >perfect.
- >
- >Thanks!
- >
- >
- But beware. If your master server has any idle time you may be misled by 
- the simple calculation. I was running a pgbench test on a replicated 
- pair of machines. It finished this morning sometime so the lag delay 
- shows 02:31:11.651118, a value that might set off alarm bells. That's 
- why I used the case statement to force the interval to 0 if the replay 
- is up-to-date.
- 
- I think it is still worthwhile to check pg_stat_replication on the 
- master to make sure that it is still *sending* logs and perhaps 
- cross-checking the current log position on the master with the replayed 
- log location on the standby to see if they are reasonably close.
- 
- An additional verification check I've toyed with is to have a cron 
- script on the master update a one-row one-column table that holds a 
- timestamp and checking that timestamp on the standby(s) to double-check 
- that it is not too-far out-of-date. (This would also force regular data 
- delivery to the standby so that pg_last_xact_replay_timestamp() should 
- not lag far behind on an otherwise idle server.)
- 
- Cheers,
- Steve
- 

I see, thanks. I was completely missing that you were using different functions
than me and that was throwing me off. =)


-- 
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] Calculating Replication Lag - units

[David Kerr]

On 06/26/2012 05:11 AM, Stuart Bishop wrote:

On Tue, Jun 26, 2012 at 6:21 AM, David Kerr  wrote:

On Mon, Jun 25, 2012 at 02:17:22PM -0700, Steve Crawford wrote:
- On 06/25/2012 01:17 PM, David Kerr wrote:
->Howdy,
->
->When calculating Replication lag, I know that we have to compare the
->pg_current_xlog_location
->to pg_last_xlog_receive_location, etc. but what I'm trying to figure out
->is what are
->the units that I'm left with after the calculation.
->
->(i.e., does the xlog_location imply some time value?)
->
->Here's the output of the (slightly modified script)
->Master: 5003964876715
->Receive: 5003964876715
->Replay: 5003964765203
->
->receive.value 0
->apply.value 111512
->
->111512 isn't inherently useful to me on its own.
->
->Any tips?
->
- How about now()-pg_last_xact_replay_timestamp() (however this can be a
- large number if there have not been any recent transactions on the
- master). I suppose you could do something like:
-
- case when pg_last_xlog_receive_location() =
- pg_last_xlog_replay_location() then '0 seconds'::interval
- else now()-pg_last_xact_replay_timestamp() end as log_delay;

i don't know for sure that 111512 is a time value.. that's kind of
what i'm wondering. If i knew that it was like miliseconds or something
that would be helpful.


On the hot standby:

 SELECT now()-pg_last_xact_replay_timestamp() AS lag;

This gives you the lag time as a PostgreSQL interval.

(It also might give you a value if you run it on a database that is
not a hot standby if it started in recovery mode).

It seems difficult or impossible to calculate this on the master.




d'oh.

looking closer at the script I was using, it says in there that's it's 
kb xlog, so not time but size. That explains a lot.


I like the the replay_timestamp method better though fits in better to 
my management's question of "how much data would we lose?".


Thanks all.


--
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] Calculating Replication Lag - units

[David Kerr]

On 06/26/2012 05:11 AM, Stuart Bishop wrote:

On Tue, Jun 26, 2012 at 6:21 AM, David Kerr  wrote:

On Mon, Jun 25, 2012 at 02:17:22PM -0700, Steve Crawford wrote:
- On 06/25/2012 01:17 PM, David Kerr wrote:
->Howdy,
->
->When calculating Replication lag, I know that we have to compare the
->pg_current_xlog_location
->to pg_last_xlog_receive_location, etc. but what I'm trying to figure out
->is what are
->the units that I'm left with after the calculation.
->
->(i.e., does the xlog_location imply some time value?)
->
->Here's the output of the (slightly modified script)
->Master: 5003964876715
->Receive: 5003964876715
->Replay: 5003964765203
->
->receive.value 0
->apply.value 111512
->
->111512 isn't inherently useful to me on its own.
->
->Any tips?
->
- How about now()-pg_last_xact_replay_timestamp() (however this can be a
- large number if there have not been any recent transactions on the
- master). I suppose you could do something like:
-
- case when pg_last_xlog_receive_location() =
- pg_last_xlog_replay_location() then '0 seconds'::interval
- else now()-pg_last_xact_replay_timestamp() end as log_delay;

i don't know for sure that 111512 is a time value.. that's kind of
what i'm wondering. If i knew that it was like miliseconds or something
that would be helpful.


On the hot standby:

 SELECT now()-pg_last_xact_replay_timestamp() AS lag;

This gives you the lag time as a PostgreSQL interval.

(It also might give you a value if you run it on a database that is
not a hot standby if it started in recovery mode).

It seems difficult or impossible to calculate this on the master.




Ah, awesome. I don't need to calculate it on the master so that's perfect.

Thanks!

--
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] Calculating Replication Lag - units

[David Kerr]

On 6/25/2012 9:55 PM, Raghavendra wrote:


On Tue, Jun 26, 2012 at 1:47 AM, David Kerr mailto:d...@mr-paradox.net>> wrote:

Howdy,

When calculating Replication lag, I know that we have to compare the
pg_current_xlog_location
to pg_last_xlog_receive_location, etc. but what I'm trying to figure
out is what are
the units that I'm left with after the calculation.

(i.e., does the xlog_location imply some time value?)

Here's the output of the (slightly modified script)
Master: 5003964876715
Receive: 5003964876715
Replay: 5003964765203

receive.value 0
apply.value 111512

111512 isn't inherently useful to me on its own.

Any tips?

A common method I did in Oracle, I followed the same, I may be wrong in
calculating exactly. Someone would have better solution on lag calculation.

My checking goes like this, Since its streaming replication, every DML
should be replicated as fast it could to slave.

1. Create table on master as Lagcheck(lagtime timestamp) and insert one
row with now() (current_time of server).
2. every minute update the same row with latest time by putting entry in
cronjob
3. Step 2 will be replicated to Slave (i.e., SR box).
4. Now on slave calculate the lag by now() - lagcheck.lagtime(column
which has value of Master time).

Here you get the time how much slave is behind from master.
Note: Special attention required on Timezones.


Well, I think the way I'm doing it is the correct way, it's even that 
way in Simon's book. I'm just not sure what the # is.. is it miliseconds 
since last update on master. or just some arbitrary number.


--
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] Calculating Replication Lag - units

[David Kerr]

On Mon, Jun 25, 2012 at 02:17:22PM -0700, Steve Crawford wrote:
- On 06/25/2012 01:17 PM, David Kerr wrote:
- >Howdy,
- >
- >When calculating Replication lag, I know that we have to compare the 
- >pg_current_xlog_location
- >to pg_last_xlog_receive_location, etc. but what I'm trying to figure out 
- >is what are
- >the units that I'm left with after the calculation.
- >
- >(i.e., does the xlog_location imply some time value?)
- >
- >Here's the output of the (slightly modified script)
- >Master: 5003964876715
- >Receive: 5003964876715
- >Replay: 5003964765203
- >
- >receive.value 0
- >apply.value 111512
- >
- >111512 isn't inherently useful to me on its own.
- >
- >Any tips?
- >
- How about now()-pg_last_xact_replay_timestamp() (however this can be a 
- large number if there have not been any recent transactions on the 
- master). I suppose you could do something like:
- 
- case when pg_last_xlog_receive_location() =  
- pg_last_xlog_replay_location() then '0 seconds'::interval
- else now()-pg_last_xact_replay_timestamp() end as log_delay;

i don't know for sure that 111512 is a time value.. that's kind of 
what i'm wondering. If i knew that it was like miliseconds or something
that would be helpful.

- But I'm wrapping my head around some replication issues myself so others 
- may have better ideas or corrections.

I've been fairly successful with replication so I'm happy to help there. 
Just trying to shore up my monitoring now!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Calculating Replication Lag - units

[David Kerr]

Howdy,

When calculating Replication lag, I know that we have to compare the 
pg_current_xlog_location
to pg_last_xlog_receive_location, etc. but what I'm trying to figure out is 
what are
the units that I'm left with after the calculation.

(i.e., does the xlog_location imply some time value?)

Here's the output of the (slightly modified script)
Master: 5003964876715
Receive: 5003964876715
Replay: 5003964765203

receive.value 0
apply.value 111512

111512 isn't inherently useful to me on its own.

Any tips?

Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] select current_setting('transaction_isolation')

[David Kerr]

Howdy,

I recently did a log_min_duration_statement=0 run on my app, and found 
~3million copies of
"select current_setting('transaction_isolation')"

I'm a Java + Hibernate stack. Does anyone know if this is a Hibernate artifact? 
or a jdbc artifact?
or something else (implicit to some query pattern or trigger)?

Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] haproxy / pgpool / rhcs

[David Kerr]

Hello

I'm implementing HA/failover for my PG nodes. I'm using PG9.0 and async 
replication and linux.

Typical problem - if node 1 fails I want the mirror to become active and take 
over for the master. 
The solution should be able to initiate the failover of the standby and start 
re-directing traffic it.

I've spent a lot of time looking at PgPool so I'm well aware that it's 
perfectly capable of this. 
However, it feels a little bit like overkill since i don't want it's pooler and 
I'm not load 
balancing. 

The other option would be RHCS, which I know will work, but to implement it 
with pg replication 
is a little hacky.

So, I'm considering HAProxy, does anyone have experience with that for managing 
HA, good or bad? 

Thanks.

-- 
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] postgresql.conf evaluation of duplicate keys

[David Kerr]

On 03/21/2012 07:02 AM, Martin Gerdes wrote:

I've got a question relating to how the postgres configuration is parsed:

If I write into the following into postgresql.conf:
shared_buffers = 24MB
shared_buffers = 32MB

and start up postgres, the command 'show shared_buffers;' answers 
'32MB'. That means the later value in the configuration file took 
precedence. Which is great and what I would expect.
Now the question: Is this just a happy accident and could change at 
any time, or is this documented and guaranteed behaviour (and if so, 
can you point me to the relevant documentation)? I tried to search for 
it, but my google foo is just not strong enough :-)


Answer to the (probably) inevitable question: "What are you trying to 
achieve?" :
I am trying to define a set of defaults, which will get deployed to 
multiple servers, and will be overwritten automatically. Yet I also 
want to have the option to change individual parameters on one server. 
So I want to introduce a line "#==do not modify above this line==". 
Everything above the line will be replaced, everything below it left 
untouched.
So if postgres is actually also parsing a file "postgresql.local" 
whose values are guaranteed to take precedence over "postgresql.conf", 
that would solve my problem as well.


Many thanks in advance!

Martin


I'm doing basically the same thing except my postgresql.conf "includes" 
a "server.conf" and the server.conf would contain any overrides.


I am counting on the behavior you mentioned above to allow this to 
happen as well.


Dave

--
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] huge price database question..

[David Kerr]

On 03/20/2012 07:26 PM, Jim Green wrote:

On 20 March 2012 22:21, David Kerr  wrote:


I'm imagining that you're loading the raw file into a temporary table that
you're going to use to
process / slice new data data into your 7000+ actual tables per stock.


Thanks! would "slice new data data into your 7000+ actual tables per
stock." be a relatively quick operation?


well, it solves the problem of having to split up the raw file by stock 
symbol. From there you can run multiple jobs in parallel to load 
individual stocks into their individual table which is probably faster 
than what you've got going now.


It would probably be faster to load the individual stocks directly from 
the file but then, as you said, you have to split it up first, so that 
may take time.




--
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] huge price database question..

[David Kerr]

On 03/20/2012 07:08 PM, Jim Green wrote:

On 20 March 2012 22:03, David Kerr  wrote:


\copy on 1.2million rows should only take a minute or two, you could make
that table "unlogged"
as well to speed it up more.  If you could truncate / drop / create / load /
then index the table each
time then you'll get the best throughput.

Thanks, Could you explain on the "runcate / drop / create / load /
then index the table each time then you'll get the best throughput."
part.. or point me to some docs?..

Jim


I'm imagining that you're loading the raw file into a temporary table 
that you're going to use to

process / slice new data data into your 7000+ actual tables per stock.

So that table doesn't probably need to be around once you've processed 
your stocks through

that table. so you could just truncate/drop it after you're done.

When you create it, if you avoid indexes the inserts will be faster (it 
doesn't have to rebuild the index every
insert) so then once the table is loaded, you create the indexes (So 
it's actually useful) and then process the

data into the various stock tables.

Dave




--
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] huge price database question..

[David Kerr]

On 03/20/2012 06:50 PM, Jim Green wrote:

On 20 March 2012 21:40, David Kerr  wrote:

On 03/20/2012 04:27 PM, Jim Green wrote:

Greetings list!
I am pretty new to postgresql from mysql and did a fairly extensive
search of the list and came up with a few good ones but didn't find
the exact same situation as I have now. so I am venturing asking here.

I have daily minute stock price data from 2005 on and each day with
columns timestamp, open,high,low,close,volume and a few more. each
day's data is about 1.2million rows. I want import all the data to
postgresql and analyze using R with the help of Rpostgresql.

right now I am having about 7000 tables for individual stock and I use
perl to do inserts, it's very slow. I would like to use copy or other
bulk loading tool to load the daily raw gz data. but I need the split
the file to per stock files first before I do bulk loading. I consider
this a bit messy.

I would seek advise on the following idea:
store everything in a big table, partition by month(this gives a
reasonable number of partitions) and do bulk loading on the daily
file. my queries would consist mostly select on a particular symbol on
a particular day.

Also in the future, I will import daily data to the db every day.

my hardware is 16G Ram, 4x5400rpm raid10 with enough space.

Thanks!

Jim.


Seems like you'd want to do this?
http://search.cpan.org/~turnstep/DBD-Pg-2.19.2/Pg.pm#pg_putcopydata
COPY support

DBD::Pg allows for quick (bulk) reading and storing of data by using the
COPY command. The basic process is to use $dbh->do to issue a COPY command,
and then to either add rows using "pg_putcopydata", or to read them by using
"pg_getcopydata".

Thanks! would you comment on the table setup as well?

Jim.



\copy on 1.2million rows should only take a minute or two, you could 
make that table "unlogged"
as well to speed it up more.  If you could truncate / drop / create / 
load / then index the table each

time then you'll get the best throughput.

Dave



--
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] huge price database question..

[David Kerr]

On 03/20/2012 04:27 PM, Jim Green wrote:

Greetings list!
I am pretty new to postgresql from mysql and did a fairly extensive
search of the list and came up with a few good ones but didn't find
the exact same situation as I have now. so I am venturing asking here.

I have daily minute stock price data from 2005 on and each day with
columns timestamp, open,high,low,close,volume and a few more. each
day's data is about 1.2million rows. I want import all the data to
postgresql and analyze using R with the help of Rpostgresql.

right now I am having about 7000 tables for individual stock and I use
perl to do inserts, it's very slow. I would like to use copy or other
bulk loading tool to load the daily raw gz data. but I need the split
the file to per stock files first before I do bulk loading. I consider
this a bit messy.

I would seek advise on the following idea:
store everything in a big table, partition by month(this gives a
reasonable number of partitions) and do bulk loading on the daily
file. my queries would consist mostly select on a particular symbol on
a particular day.

Also in the future, I will import daily data to the db every day.

my hardware is 16G Ram, 4x5400rpm raid10 with enough space.

Thanks!

Jim.



Seems like you'd want to do this?
http://search.cpan.org/~turnstep/DBD-Pg-2.19.2/Pg.pm#pg_putcopydata 


COPY support

DBD::Pg allows for quick (bulk) reading and storing of data by using the 
COPY command. The basic process is to use $dbh->do to issue a COPY 
command, and then to either add rows using "pg_putcopydata", or to read 
them by using "pg_getcopydata".

Re: [GENERAL] : postgres: archiver process failed on 0000000100000F72000000F0

[David Kerr]

On Fri, Nov 11, 2011 at 09:58:56PM +0530, Venkat Balaji wrote:
- Hello,
- 
- WAL Archive process in our production is not working.
- 
- [postgres@hostname]$ ps -ef | grep archive
- postgres 12077 16015  0 10:19 pts/400:00:00 grep archive
- postgres 31126 27607  0 Nov10 ?00:01:18 postgres: archiver process
-   failed on 00010F7200F0
- 
- I see WAL files getting accumulated in pg_xlog location and the status in
- "archive_status" is shown as ".ready".
- 
- Is there anyway we can only restart archiving process without disturbing
- the actual cluster ?
- 
- Actually, we had killed a process using "kill  -9 " and the db went into
- recovery mode and was back up and running.
- 
- We have no issues with the application as well.
- 
- postgres=# select pg_is_in_recovery();
- 
-  pg_is_in_recovery
- ---
-  f
- (1 row)
- 
- Please help to resolve this !

If you fix the problem causing the archiver process to fail it will start 
processing
the logs again.

check your postgres logs and check your archive_command in your postgresql.conf 
and 
make sure that everything is correct there. 

Dave

-- 
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] Foreign Keys and Deadlocks

[David Kerr]

On Thu, Nov 10, 2011 at 09:09:06AM +0100, Csaba Nagy wrote:
- Hi David,
- 
- On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote:
- > So, aside from removing the PKs do i have any other options?
- 
- Sure you have: order the inserts by primary key inside each transaction.
- Then you will not get deadlocks, but inserting the same key again will
- fail of course (but that's the purpose of the primary key, right ?)
- 
- Ordering inserts/updates by the columns which cause locks is the first
- thing to do to avoid dead-locks...
- 
- Cheers,
- Csaba.

ah, hmmm. i'm not sure if that's an option based on how the program works but
I'll forward the suggestion onto the devleoper. thanks!

-- 
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] Foreign Keys and Deadlocks

[David Kerr]

On Wed, Nov 09, 2011 at 11:11:23AM -0300, Alvaro Herrera wrote:
- 
- Excerpts from David Kerr's message of vie nov 04 13:01:29 -0300 2011:
- 
- > I did more digging and found some good discussions on the subject in 
general, but 
- > most of the examples out there contain explicit updates (which is why i was 
confused) 
- > but it looks like it's being addressed. 
- > 
- > 
- > http://justatheory.com/computers/databases/postgresql/fk-locks-project.html
- > http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg158205.html
- > 
http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/
- > 
- > Attached is the script to reproduce it with only inserts (for postarities 
sake)
- 
- Actually, your script as presented has nothing to do with foreign keys.
- The cause for the lock and the deadlock is not in the tuple lock code,
- but in the primary key uniqueness check.  You can duplicate your issue
- with a single one-column table:
- 
- Session one:
- 
- alvherre=# create table pk (a int primary key);
- NOTICE:  CREATE TABLE / PRIMARY KEY creará el índice implícito «pk_pkey» 
para la tabla «pk»
- CREATE TABLE
- alvherre=# begin;
- BEGIN
- alvherre=# insert into pk values (1);
- INSERT 0 1
- 
- Session two:
- 
- alvherre=# begin;
- BEGIN
- alvherre=# insert into pk values (2);
- INSERT 0 1
- alvherre=# insert into pk values (1);
- 
- 
- Now go back to session one and
- 
- alvherre=# insert into pk values (2);
- ERROR:  se ha detectado un deadlock
- DETALLE:  El proceso 17430 espera ShareLock en transacción 710; bloqueado 
por proceso 17495.
- El proceso 17495 espera ShareLock en transacción 709; bloqueado por proceso 
17430.
- SUGERENCIA:  Vea el registro del servidor para obtener detalles de las 
consultas.
- 
- 
- This case is not helped by the patch I'm working on.  As far as I can
- see, if you got rid of the PK in table a in your example script, things
- should work just fine.  There is no way to cause FK-induced deadlocks
- with only inserts in 8.1 and later.

Ok, well that's good to know. I had planned on testing my script w/o FKs but it 
slipped 
my mind.

So, aside from removing the PKs do i have any other options? (we use Hibernate 
and
i don't think that I'll be able to removet he Primary Keys, and a serial 
primary key
probably isn't great for this table because it's sort of a staging area (so it 
gets
written to and wiped out frequently)

Would you consider this a problem in Pg or is it unavoidable?

Thanks

Dave

-- 
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] Foreign Keys and Deadlocks

[David Kerr]

On Thu, Nov 03, 2011 at 03:30:20PM -0700, David Kerr wrote:
- Howdy,
- 
- We have a process that's deadlocking frequently. It's basically multiple 
threads inserting data into a single table.
- 
- That table has FK constraints to 3 other tables. 
- 
- I understand how an FK check will cause a sharelock to be acquired on the 
reference table and in some instances that
- leads to or at least participates in a deadlock.
- 
- I don't think that's the case here, (or at least not the entire case) but I 
could use some assistance in helping 
- to convince my developers of that ;). They'd like to just remove the FK and 
be done with it.

[snip]

So it appears that I'm the big dummy, and that you can deadlock with just 
inserts. 

I did more digging and found some good discussions on the subject in general, 
but 
most of the examples out there contain explicit updates (which is why i was 
confused) 
but it looks like it's being addressed. 


http://justatheory.com/computers/databases/postgresql/fk-locks-project.html
http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg158205.html
http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/

Attached is the script to reproduce it with only inserts (for postarities sake)

drop table a;
drop table b;
drop table c;
drop table d;

create table b ( bref int, description text);
alter table b add primary key (bref);

create table c ( cref int, description text);
alter table c add primary key (cref);

create table d ( dref int, description text);
alter table d add primary key (dref);

create table a ( bref int, cref int, dref int, description text);
alter table a add primary key (bref, cref);
alter table a add foreign key (bref) REFERENCES b(bref);
alter table a add foreign key (cref) REFERENCES c(cref);
alter table a add foreign key (dref) REFERENCES d(dref);


insert into b values (1,'hello');
insert into b values (2,'hello2');
insert into b values (3,'hello3');
insert into b values (4,'hello4');


insert into c values (1,'hello');
insert into c values (2,'hello2');
insert into c values (3,'hello3');
insert into c values (4,'hello4');

insert into d values (1,'hello');
insert into d values (2,'hello2');
insert into d values (3,'hello3');
insert into d values (4,'hello4');


Fire up 2 psqls
#SESSION1
## STEP1
begin;
insert into a values (1,1,1,'hello');
##STEP3
insert into a values (1,2,1,'hello2');


#SESSION2
## STEP2
begin;
insert into a values (1,2,1,'hello2');
## STEP4
insert into a values (1,1,1,'hello');


You'll get:
ERROR:  deadlock detected
DETAIL:  Process 8382 waits for ShareLock on transaction 7222455; blocked by 
process 6981.
Process 6981 waits for ShareLock on transaction 7222456; blocked by process 
8382.
HINT:  See server log for query details

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Foreign Keys and Deadlocks

[David Kerr]

Howdy,

We have a process that's deadlocking frequently. It's basically multiple 
threads inserting data into a single table.

That table has FK constraints to 3 other tables. 

I understand how an FK check will cause a sharelock to be acquired on the 
reference table and in some instances that
leads to or at least participates in a deadlock.

I don't think that's the case here, (or at least not the entire case) but I 
could use some assistance in helping 
to convince my developers of that ;). They'd like to just remove the FK and be 
done with it.

I've had this link sent to me probably 100 times in the past day or so to 
support the theory that postgres is just
deadlocking itself:  
http://archives.postgresql.org/pgsql-general/2004-01/msg00272.php
I think that's a misinterpretation, and I also assume PGs come quite a ways 
since then (i'm on PG9.0).

The actual error is:
Nov 1 13:59:19 db001 postgres-dev[480]: [5470-1] user=x,db=y,pid=480,2011-11-01 
20:59:19 UTC ERROR: deadlock detected
Nov 1 13:59:19 db001 postgres-dev[480]: [5470-2] user=x,db=y,pid=480,2011-11-01 
20:59:19 UTC DETAIL: Process 480 waits for ShareLock on transaction 4537069; 
blocked by process 471.
Nov 1 13:59:19 db001 postgres-dev[480]: [5470-3] #011Process 471 waits for 
ShareLock on transaction 4537063; blocked by process 480.
Nov 1 13:59:19 db001 postgres-dev[480]: [5470-4] #011Process 480: insert into a 
(col1, col2, col3, col4) values ($1, $2, $3, $4)
Nov 1 13:59:19 db001 postgres-dev[480]: [5470-5] #011Process 471: insert into  
a (col1, col2, col3, col4) values ($1, $2, $3, $4)
Nov 1 13:59:19 db001 postgres-dev[480]: [5470-6] user=x,db=y,pid=480,2011-11-01 
20:59:19 UTC HINT: See server log for query details.

Here's the scenario 

table a ( int col1 references b, 
  int col2 references c,  
  int col3 references d, 
  text col4 )


The app, basically, does a ton of parallel, possibly duplicate, inserts into 
table a.
That's all it's supposed to be doing (hibernate's involved though, so anything 
goes).

Nothing else touches those tables.

Is it possible for a deadlock to occur under those circumstances?

I suspect that it has to be a transaction, and that further up in the TX is an 
update to one of
the reference tables in each TX.

If we remove the FKs we no longer get the deadlock, but I'd actually like to 
know the
cause.

Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] OOM Killer / PG9 / RHEL 6.1

[David Kerr]

Howdy,

just a quick check, is 
vm.overcommit_memory = 2
vm.swappiness = 0

Still the way to go with PG9.0 / RHEL 6.1 (64bit) ?

I know we gained some control over the OOM Killer in newer kernels 
and remember reading that maybe postgres could handle it in a different way now.

Thanks

Dave

-- 
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] Server hitting 100% CPU usage, system comes to a crawl.

[David Kerr]

On Thu, Oct 27, 2011 at 02:09:51PM -0600, Brian Fehrle wrote:
- On 10/27/2011 01:48 PM, Scott Marlowe wrote:
- >On Thu, Oct 27, 2011 at 12:39 PM, Brian Fehrle
- >  wrote:
- >>Looking at top, I see no SWAP usage, very little IOWait, and there are a
- >>large number of postmaster processes at 100% cpu usage (makes sense, at 
- >>this
- >>point there are 150 or so queries currently executing on the database).
- >>
- >>  Tasks: 713 total,  44 running, 668 sleeping,   0 stopped,   1 zombie
- >>Cpu(s):  4.4%us, 92.0%sy,  0.0%ni,  3.0%id,  0.0%wa,  0.0%hi,  0.3%si,
- >>  0.2%st
- >>Mem:  134217728k total, 131229972k used,  2987756k free,   462444k buffers
- >>Swap:  8388600k total,  296k used,  8388304k free, 119029580k cached
- >OK, a few points.  1: You've got a zombie process.  Find out what's
- >causing that, it could be a trigger of some type for this behaviour.
- >2: You're 92% sys.  That's bad.  It means the OS is chewing up 92% of
- >your 32 cores doing something.  what tasks are at the top of the list
- >in top?
- >
- Out of the top 50 processes in top, 48 of them are postmasters, one is 
- syslog, and one is psql. Each of the postmasters have a high %CPU, the 
- top ones being 80% and higher, the rest being anywhere between 30% - 
- 60%. Would postmaster 'queries' that are running attribute to the sys 
- CPU usage, or should they be under the 'us' CPU usage?

total spitball here but - I had something similar happen once and it 
was syslog causing the problem.

Are you using regular vanilla syslog? or syslog-ng/rsyslog? my problem
was vanilla syslog. When I moved to -ng/rsyslog or logging to a file 
my problem went away.

Dave

-- 
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] What's the impact of archive_command failing?

[David Kerr]

On 10/18/2011 09:44 AM, Simon Riggs wrote:

On Tue, Oct 18, 2011 at 4:58 PM, David Kerr  wrote:


I have postgres setup for streaming replication and my slave box went down.

My question is, how long can that box stay down before it causes a material 
impact on the master?

The archive_command that I use will not archive logs while the slave is down.

I know the obvious problems:
  * you're not archiving, so you lose all of the recovery related benefits if 
you should crash
  * could run out of disk space in pg_xlog
  * your slave could get out of sync

What i'm concerned with is something like, if the master is unable to archive 
eventually
it will stop writing WALs Or something impacting the performance of the 
database.
or anything along those lines.



When it comes back up it will have to catchup. At some point it will
be quicker to regenerate the standby than to catchup.

Also, at some point you will run out of space in pg_xlog, which would
make the master crash. So probably best to have an archive_command
that starts deleting or compressing files before disk fills, but that
means your slave can then never catch up at that point.



Ok, that's better that I thought. Thanks!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] What's the impact of archive_command failing?

[David Kerr]

I have postgres setup for streaming replication and my slave box went down. 

My question is, how long can that box stay down before it causes a material 
impact on the master?

The archive_command that I use will not archive logs while the slave is down.

I know the obvious problems: 
 * you're not archiving, so you lose all of the recovery related benefits if 
you should crash
 * could run out of disk space in pg_xlog
 * your slave could get out of sync

What i'm concerned with is something like, if the master is unable to archive 
eventually
it will stop writing WALs Or something impacting the performance of the 
database. 
or anything along those lines.

Thanks

-- 
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] Calculating memory allocaiton per process

[David Kerr]

On Thu, Apr 14, 2011 at 03:00:07PM -0400, Jerry Sievers wrote:
- David Kerr  writes:
- 
- > Howdy, 
- >
- > Is there a doc somewhere that has a formula for how much memory PG
- > backend process will use?
- >
- > I'm looking to get something like total_mem = max_connections * (
- > work_mem + temp_buffers ) // I know it's more complicated than that,
- > which is why I'm asking =)
- 
- Depends on your query complexity, load distribution across concurrent
- sessions and session lifetime.
- 
- work_mem will, in cases of queries having multiple sort nodes, have to
- be counted multiple times on behalf of a single backend.
- 
- Some observation of the running system can be your best bet.
- 
- HTH

Yeah, that's the complication that I knew about (but am still not able to
fully 'get', let along vocalize). 

Are there no rules of thumb or upper bounds to help estimate total memory usage?

Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Calculating memory allocaiton per process

[David Kerr]

Howdy, 

Is there a doc somewhere that has a formula for how much memory PG backend 
process will use?

I'm looking to get something like total_mem = max_connections * ( work_mem + 
temp_buffers )
// I know it's more complicated than that, which is why I'm asking =)

Something similar to Table 17-2 here: 
http://www.postgresql.org/docs/9.0/interactive/kernel-resources.html
would be awesome.

Dave

-- 
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] Reordering a table

[David Kerr]

On Tue, Feb 22, 2011 at 04:40:36PM +, Howard Cole wrote:
- Hi,
- 
- a puzzle to solve...
- 
- I have a table with a primary key, and a timestamp, e.g.
- 
- idstamp
- 1 2011-02-01 10:00
- 2 2011-02-01 09:00
- 3 2011-02-01 11:00
- 
- Now for reasons too painful to go into, I need to reorder the id 
- (sequence) so that they are in time order:
- 
- idstamp
- 1 2011-02-01 09:00
- 2 2011-02-01 10:00
- 3 2011-02-01 11:00
- 
- I thought I could do it by adding a third colum with the order in it, 
- but I cannot think of a way to do this short of writing some code
- 
- idstamp order
- 1 2011-02-01 10:002
- 2 2011-02-01 09:001
- 3 2011-02-01 11:003
- 
- Any ideas?
- 
- Thanks.
- Howard
- www.selestial.com

There is almost certianly a slicker way, but what comes to the top of my head is

create new_table (id serial, stamp timestamp)
insert into new_table (stamp) select stamp from old_table order by stamp;
rename old_table old_table_old -- just to be safe
rename new_table old_table

this, of course, doesn't stop stuff from getting out of order again.

Dave

-- 
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] find column name that has under score (_)

[David Kerr]

On Thu, Feb 17, 2011 at 01:55:46PM -0500, akp geek wrote:
- Hi all -
- 
-   I am trying to write a query to find all the column names in
- database that has a underscore in it (_) example souce_id. I know like will
- not work , if where column_name like '%_%' Can you please help?
- 
- Regards

select table_schema,table_name, column_name  from information_schema.columns 
where column_name like '%\\_%';

seems to do the trick.

Dave

-- 
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] pg_dump: schema with OID 58698 does not exist

[David Kerr]

On Fri, Feb 11, 2011 at 03:17:51PM -0500, Tom Lane wrote:
- David Kerr  writes:
- > So i removed the 5 entries from pg_class, but i still get that error 
- > when trying to pg_dump:
- 
- > pg_dump: schema with OID 58698 does not exist
- 
- > Any other ideas where i could look?
- 
- Well, it could be any catalog with a namespace column.  But I'm
- wondering about pg_depend links from those tables you removed to
- the schema.

I'm not sure if it's possible for me to follow the link to pg_depend after 
i deleted objects so I guess i'll have to try that next time if this 
happens again.

The other references to that schema were in pg_type and pg_constraint. After
those were removed I was able to run pg_dump on it.

thanks

Dave

-- 
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] pg_dump: schema with OID 58698 does not exist

[David Kerr]

On 02/09/2011 11:23 AM, David Kerr wrote:

On Wed, Feb 09, 2011 at 02:15:06PM -0500, Tom Lane wrote:
- David Kerr  writes:
->  Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes 
are associated
->  with the wrong (invalid / nonexistant ) schema.
-
->  However, there are correct entries for those objects as well. So these are 
definitly just dangling references.
-
- Please be more specific.  What are the bad entries exactly (what values,
- in which columns of what catalogs) and what do you mean by "there are
- correct entries as well"?

pg_class has 5 relname entries associated with a relnamespace (58698) that 
doesn't exist in pg_namespace.

Those relname entries corrispond to 2 tables and their PKs and a sequence (for 
the PK of one of the tables).

Those objects do have valid entries in pg_class as well (they point to a 
different relnamespace)

Is that more clear?


So i removed the 5 entries from pg_class, but i still get that error 
when trying to pg_dump:


pg_dump: schema with OID 58698 does not exist

Any other ideas where i could look?

thanks

Dave

--
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] pg_dump: schema with OID 58698 does not exist

[David Kerr]

On Wed, Feb 09, 2011 at 02:15:06PM -0500, Tom Lane wrote:
- David Kerr  writes:
- > Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes 
are associated
- > with the wrong (invalid / nonexistant ) schema.
- 
- > However, there are correct entries for those objects as well. So these are 
definitly just dangling references.
- 
- Please be more specific.  What are the bad entries exactly (what values,
- in which columns of what catalogs) and what do you mean by "there are
- correct entries as well"?

pg_class has 5 relname entries associated with a relnamespace (58698) that 
doesn't exist in pg_namespace.

Those relname entries corrispond to 2 tables and their PKs and a sequence (for 
the PK of one of the tables).

Those objects do have valid entries in pg_class as well (they point to a 
different relnamespace)

Is that more clear?

- > When we do a data "refresh" here, i do a drop  cascade; in the DB 
and then 
- > pg_restore -Fc --disable-triggers 
- 
- Hm.  We have seen occasional reports of drop cascade failing to delete
- all the dependent objects, but it's pretty hard to see how that could
- happen ...

I agree!

Dave

-- 
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] pg_dump: schema with OID 58698 does not exist

[David Kerr]

On Wed, Feb 09, 2011 at 09:42:36AM -0800, David Kerr wrote:
- On Tue, Feb 08, 2011 at 10:16:02PM -0500, Tom Lane wrote:
- - David Kerr  writes:
- - > I'm getting the above error in one of my dev DBs.
- - 
- - Would you poke around in the system catalogs and find where the dangling
- - reference is located?  Have you got any idea of how to reproduce this
- - failure from a standing start?
- - 
- - regards, tom lane
- - 
- 
- Ok i'll dig around.
- 
- I spoke with the developer, he claims not to have done anything to the DB. so 
i don't
- know how to reproduce the state. (but i do get the error every time i use 
pg_dump on
- the DB). it seems that this DB is mainly used as a target for junit testing.
- 
- Also, i don't know if this is related but I'm seeing quite a few:
- Feb  9 17:04:17 db001 postgres-9[21285]: [28-1] 
user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was 
incorrectly set in relation "test_event_result_fact" page 4223
- Feb  9 17:04:17 db001 postgres-9[21285]: [29-1] 
user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was 
incorrectly set in relation "test_event_result_fact" page 4224
- Feb  9 17:04:17 db001 postgres-9[21285]: [30-1] 
user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was 
incorrectly set in relation "test_event_result_fact" page 4225
- Feb  9 17:04:17 db001 postgres-9[21285]: [31-1] 
user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was 
incorrectly set in relation "test_event_result_fact" page 4226
- Feb  9 17:04:17 db001 postgres-9[21285]: [32-1] 
user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was 
incorrectly set in relation "test_event_result_fact" page 4227
- Feb  9 17:04:17 db001 postgres-9[21285]: [33-1] 
user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was 
incorrectly set in relation "test_event_result_fact" page 4228
- Feb  9 17:04:17 db001 postgres-9[21285]: [34-1] 
user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was 
incorrectly set in relation "test_event_result_fact" page 4229
- 
- 
- It seems like a system process is reporting it, i tried to vacuum all of my 
DBs to try to 
- narrow down which DB it's in - didn't generate the error. 
- 
- I suppose it's possible that the process is fixing the data and then it gets 
broken again.
- 
- Any pointers would be helpful!


Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes are 
associated
with the wrong (invalid / nonexistant ) schema.

However, there are correct entries for those objects as well. So these are 
definitly just dangling references.

When we do a data "refresh" here, i do a drop  cascade; in the DB and 
then 
pg_restore -Fc --disable-triggers 

the datasets are made by pg_dump -Fc --disable-triggers -s -n  

The only thing strange that may come up is that the dumps were created by 8.3.
(also, i've got multiple developers all with their own DB and this is the only 
one with this particular problem)


The 'PD_ALL_VISIBLE' error above seems to not be related to this particular 
problem. (although it could still be
in this database).

Dave

-- 
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] pg_dump: schema with OID 58698 does not exist

[David Kerr]

On Tue, Feb 08, 2011 at 10:16:02PM -0500, Tom Lane wrote:
- David Kerr  writes:
- > I'm getting the above error in one of my dev DBs.
- 
- Would you poke around in the system catalogs and find where the dangling
- reference is located?  Have you got any idea of how to reproduce this
- failure from a standing start?
- 
-   regards, tom lane
- 

Ok i'll dig around.

I spoke with the developer, he claims not to have done anything to the DB. so i 
don't
know how to reproduce the state. (but i do get the error every time i use 
pg_dump on
the DB). it seems that this DB is mainly used as a target for junit testing.

Also, i don't know if this is related but I'm seeing quite a few:
Feb  9 17:04:17 db001 postgres-9[21285]: [28-1] user=,db=,pid=21285,2011-02-09 
17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation 
"test_event_result_fact" page 4223
Feb  9 17:04:17 db001 postgres-9[21285]: [29-1] user=,db=,pid=21285,2011-02-09 
17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation 
"test_event_result_fact" page 4224
Feb  9 17:04:17 db001 postgres-9[21285]: [30-1] user=,db=,pid=21285,2011-02-09 
17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation 
"test_event_result_fact" page 4225
Feb  9 17:04:17 db001 postgres-9[21285]: [31-1] user=,db=,pid=21285,2011-02-09 
17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation 
"test_event_result_fact" page 4226
Feb  9 17:04:17 db001 postgres-9[21285]: [32-1] user=,db=,pid=21285,2011-02-09 
17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation 
"test_event_result_fact" page 4227
Feb  9 17:04:17 db001 postgres-9[21285]: [33-1] user=,db=,pid=21285,2011-02-09 
17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation 
"test_event_result_fact" page 4228
Feb  9 17:04:17 db001 postgres-9[21285]: [34-1] user=,db=,pid=21285,2011-02-09 
17:04:17 UTC WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation 
"test_event_result_fact" page 4229


It seems like a system process is reporting it, i tried to vacuum all of my DBs 
to try to 
narrow down which DB it's in - didn't generate the error. 

I suppose it's possible that the process is fixing the data and then it gets 
broken again.

Any pointers would be helpful!

Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_dump: schema with OID 58698 does not exist

[David Kerr]

howdy all,

I'm getting the above error in one of my dev DBs.

I've read in the archives that to stop the error from happening I can just 
delete entries in  pg_type and pg_class, however there seemed to be some 
community interest in doing some debugging.

(mentioned in this thread: 
http://archives.postgresql.org/pgsql-bugs/2010-01/msg00090.php )

This is PG 9.0.2 on RedHat 64 bit.

Is there anything you'd like me to check before i fix the problem?

(since it's dev, i plan to just wack the database and re-create it.. so i don't 
need
to worry about if that particular object was used or whatever... )

Thanks

Dave

-- 
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] Problems Authenticating against OpenLDAP

[David Kerr]

On Mon, Dec 06, 2010 at 07:03:59PM +0100, Rados?aw Smogura wrote:
- Try with configuration parameter
- conn_max_pending (number of connections waiting for processing thread)
- conn_max_auth (same, but for authenticated)

ok sounds good, i'll give that a shot!

- If you are using anonymous auth then, by default you have 
- conn_max_pending=100. In your configuration I don't see need to increase 
- threads to 32?

yes the documentation for threads was unclear i just assumed that if the
server was getting bogged down more threads would help =)

- Does your open ldap has PostgreSQL backend? :)

Hehe, not yet. I'm ldap-impaired. so I went with BDB just to get it going.

- Try to increase open ldap log level - to get info about connection opening.

ok sounds good. thanks!

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Problems Authenticating against OpenLDAP

[David Kerr]

I've recently configured Postgres (8.3) to authenticate against OpenLDAP

this is my pg_hba.conf entry:
host all all 0.0.0.0/0 ldap 
"ldap://ldapserver/dc=mydomain,dc=com;uid=;,ou=postgresql,dc=mydomain,dc=com";

Things are working fine most of the time. 

However, every once in a while i'm getting something along the lines of:

Dec  6 08:17:24 devcell-db1 postgres[12401]: [2-1] 
user=xxx.yyy,db=userdb,trans=0[] LOG:  LDAP login failed for user
"uid=xxx.yyy,ou=postgresql,dc=mydomain,dc=com" on
Dec  6 08:17:24 devcell-db1 postgres[12401]: [2-2]  server "ldapserver": error 
code -1

The problem is, i'm not seeing a corresponding error on the OpenLDAP side.

Also, it seems like this only happens under load, like if someone does a unit 
test that
connects to the database 100 times in a few seconds.

Has anyone dealt with this? I've been trying to tune OpenLDAP to handle more
concurrent connections, but without much success. i've set in my
/etc/openldap/slapd.conf
threads 32
concurrency 100

and in
/etc/ldap.conf
threads 100
idle_timelimit 60
bind_timelimit 120

The fact that it's not even logging the  failure worries me, like
something's causing the request not even to hit the server.

Any ideas would be greatly appreciated.

Thanks

Dave

-- 
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] pg_hba LDAP Authentication syntax

[David Kerr]

On Thu, Nov 04, 2010 at 03:35:11PM -0700, Magnus Hagander wrote:
- On Thu, Nov 4, 2010 at 15:30, David Kerr  wrote:
- > On Thu, Nov 04, 2010 at 02:07:29PM -0700, Magnus Hagander wrote:
- > - >
- > - > I'm trying to translate that to the old syntax of:
- > - >      ldap 
"ldap://w.x.y.z/ou=postgresql,dc=domain,dc=com;"
- > - >
- > - > basically, i don't know how to fit cn=admin and ldapbindpassword into 
that string.
- > -
- > - The search+bind feature is not available on 8.3 - it's a new feature in 
9.0.
- >
- > Not 8.4?
- > http://www.postgresql.org/docs/8.4/interactive/auth-methods.html
- >
- 
- No. 8.4 had only the prefix/suffix method, the search/bind method was
- added in 9.0.That's why the parameters for search/bind don't exist in
- 8.4.

Ok thanks.

Dave

-- 
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] pg_hba LDAP Authentication syntax

[David Kerr]

On Thu, Nov 04, 2010 at 02:07:29PM -0700, Magnus Hagander wrote:
- >
- > I'm trying to translate that to the old syntax of:
- >      ldap "ldap://w.x.y.z/ou=postgresql,dc=domain,dc=com;"
- >
- > basically, i don't know how to fit cn=admin and ldapbindpassword into that 
string.
- 
- The search+bind feature is not available on 8.3 - it's a new feature in 9.0.

Not 8.4?
http://www.postgresql.org/docs/8.4/interactive/auth-methods.html

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_hba LDAP Authentication syntax

[David Kerr]

Howdy,

I was hoping someone could help me with ye olde ldap authentication syntax.

I'm currently using PG 8.3.9 and an upgrade is not an option.

Now, that being said, since i'm very new to LDAP i decided to use PG 9 to 
experiment with
since it looks like it has an easier syntax.

So what i've got working in PG9 is the following:
 ldap ldapserver=w.x.y.z ldapbinddn="cn=admin,dc=domain,dc=com" 
ldapbindpasswd="password"
ldapbasedn="ou=postgresql,dc=domain,dc=com" 


I'm trying to translate that to the old syntax of: 
 ldap "ldap://w.x.y.z/ou=postgresql,dc=domain,dc=com;"

basically, i don't know how to fit cn=admin and ldapbindpassword into that 
string.

Thanks

Dave

-- 
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] Generate a dynamic sequence within a query

[David Kerr]

On Wed, Oct 20, 2010 at 09:35:11PM -0700, Darren Duncan wrote:
- Josh Kupershmidt wrote:
- >On Wed, Oct 20, 2010 at 6:22 PM, David Kerr  wrote:
- >>I know I've seen posts on how to do this, but i can't seem to find them.
- >>
- >>I've got a data set
- >>
- >>A, B
- >>A, C
- >>A, D
- >>[...]
- >>
- >>and so on
- >>
- >>and i'd like to be able to wite a query that would result in
- >>
- >>1,A,B
- >>2,A,C
- >>3,A,D
- >>[...]
- >>
- >>PG version is 8.3.
- >
- >If you can upgrade to 8.4, you could use the row_number() window
- >function which is perfectly suited to this task, should be as simple
- >as:
- >
- >SELECT row_number() OVER (), * FROM tablename;
- 
- Yes indeed.  For a simple increment by one sequence, functions like rank() 
- ... see 
- http://www.postgresql.org/docs/9.0/interactive/functions-window.html ... 
- are exactly what you want. -- Darren Duncan


Well, an upgrade's not on tap for a few months. Until then i'll need to
figure out somethnig else.

thanks all.

Dave

-- 
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] Generate a dynamic sequence within a query

[David Kerr]

On Wed, Oct 20, 2010 at 10:32:15PM -0400, Josh Kupershmidt wrote:
- On Wed, Oct 20, 2010 at 6:22 PM, David Kerr  wrote:
- > I know I've seen posts on how to do this, but i can't seem to find them.
- >
- > I've got a data set
- >
- > A, B
- > A, C
- > A, D
- > [...]
- >
- > and so on
- >
- > and i'd like to be able to wite a query that would result in
- >
- > 1,A,B
- > 2,A,C
- > 3,A,D
- > [...]
- >
- > PG version is 8.3.
- 
- If you can upgrade to 8.4, you could use the row_number() window
- function which is perfectly suited to this task, should be as simple
- as:
- 
- SELECT row_number() OVER (), * FROM tablename;

Ah, no chance of that for a while. figures all the  fun stuff is always a 
version
away =)

Dave

-- 
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] Generate a dynamic sequence within a query

[David Kerr]

On Wed, Oct 20, 2010 at 03:47:19PM -0700, DM wrote:
- select generate_series(1,(select count(*) from tax)), country from tax;
- 
- you should use braces around the sub select.
- 
- Thanks
- Deepak

  Table "public.test"
 Column | Type | Modifiers
+--+---
 col1   | character varying(2) |
 col2   | character varying(2) |


select * from test;
 col1 | col2
--+--
 A| A
 A| B
 A| C
 B| A
 B| B
 B| C
(6 rows)

select generate_series(1,(select count(*) from test)), col1, col2 from test;
 generate_series | col1 | col2
-+--+--
   1 | A| A
   2 | A| A
   3 | A| A
   4 | A| A
   5 | A| A
   6 | A| A
   1 | A| B
   2 | A| B
   3 | A| B
   4 | A| B
   5 | A| B
   6 | A| B
   1 | A| C
   2 | A| C
   3 | A| C
   4 | A| C
   5 | A| C
   6 | A| C
   1 | B| A
   2 | B| A
   3 | B| A
   4 | B| A
   5 | B| A
   6 | B| A
   1 | B| B
   2 | B| B
   3 | B| B
   4 | B| B
   5 | B| B
   6 | B| B
   1 | B| C
   2 | B| C
   3 | B| C
   4 | B| C
   5 | B| C
   6 | B| C
(36 rows)

when what i want is:
1  | A| A
2  | A| B
3  | A| C
4  | B| A
5  | B| B
6  | B| C


thanks

Dave

- 
- On Wed, Oct 20, 2010 at 3:30 PM, David Kerr  wrote:
- 
- > On Wed, Oct 20, 2010 at 11:28:18PM +0100, Raymond O'Donnell wrote:
- > - On 20/10/2010 23:22, David Kerr wrote:
- > - >I know I've seen posts on how to do this, but i can't seem to find them.
- > - >
- > - >I've got a data set
- > - >
- > - >A, B
- > - >A, C
- > - >A, D
- > - >[...]
- > - >
- > - >and so on
- > - >
- > - >and i'd like to be able to wite a query that would result in
- > - >
- > - >1,A,B
- > - >2,A,C
- > - >3,A,D
- > - >[...]
- > - >
- > - >PG version is 8.3.
- > - >
- > - >Any ideas?
- > -
- > - You probably want generate_series():
- > -
- > -http://www.postgresql.org/docs/8.3/static/functions-srf.html
- > -
- > - Ray.
- >
- > I thought, so. what would that look like?
- >
- > select generate_series(1,select count(*) from table), field1, field2 from
- > table
- > doesn't work..
- >
- >
- > thanks
- >
- > Dave
- >
- > --
- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
- > To make changes to your subscription:
- > http://www.postgresql.org/mailpref/pgsql-general
- >

-- 
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] Generate a dynamic sequence within a query

[David Kerr]

On Wed, Oct 20, 2010 at 03:47:19PM -0700, DM wrote:
- select generate_series(1,(select count(*) from tax)), country from tax;
- 
- you should use braces around the sub select.
- 
- Thanks
- Deepak
- 

Ah, great, thanks!

Dave



- On Wed, Oct 20, 2010 at 3:30 PM, David Kerr  wrote:
- 
- > On Wed, Oct 20, 2010 at 11:28:18PM +0100, Raymond O'Donnell wrote:
- > - On 20/10/2010 23:22, David Kerr wrote:
- > - >I know I've seen posts on how to do this, but i can't seem to find them.
- > - >
- > - >I've got a data set
- > - >
- > - >A, B
- > - >A, C
- > - >A, D
- > - >[...]
- > - >
- > - >and so on
- > - >
- > - >and i'd like to be able to wite a query that would result in
- > - >
- > - >1,A,B
- > - >2,A,C
- > - >3,A,D
- > - >[...]
- > - >
- > - >PG version is 8.3.
- > - >
- > - >Any ideas?
- > -
- > - You probably want generate_series():
- > -
- > -http://www.postgresql.org/docs/8.3/static/functions-srf.html
- > -
- > - Ray.
- >
- > I thought, so. what would that look like?
- >
- > select generate_series(1,select count(*) from table), field1, field2 from
- > table
- > doesn't work..
- >
- >
- > thanks
- >
- > Dave
- >
- > --
- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
- > To make changes to your subscription:
- > http://www.postgresql.org/mailpref/pgsql-general
- >

-- 
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] Generate a dynamic sequence within a query

[David Kerr]

On Wed, Oct 20, 2010 at 11:28:18PM +0100, Raymond O'Donnell wrote:
- On 20/10/2010 23:22, David Kerr wrote:
- >I know I've seen posts on how to do this, but i can't seem to find them.
- >
- >I've got a data set
- >
- >A, B
- >A, C
- >A, D
- >[...]
- >
- >and so on
- >
- >and i'd like to be able to wite a query that would result in
- >
- >1,A,B
- >2,A,C
- >3,A,D
- >[...]
- >
- >PG version is 8.3.
- >
- >Any ideas?
- 
- You probably want generate_series():
- 
-http://www.postgresql.org/docs/8.3/static/functions-srf.html
- 
- Ray.

I thought, so. what would that look like?

select generate_series(1,select count(*) from table), field1, field2 from table
doesn't work.. 


thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Generate a dynamic sequence within a query

[David Kerr]

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
[...]

PG version is 8.3.

Any ideas?

Thanks

Dave

-- 
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] Centralized User Management Tool?

[David Kerr]

On Wed, Sep 29, 2010 at 02:23:14PM -0700, Joshua D. Drake wrote:
- > Howdy,
- > 
- > Does anyone know of any tools or methods to handle centralized user 
management within postgres?
- > 
- > I've got about 20 DB servers (and growing) each requiring a different 
number and level of user access 
- > (think dev, qa, staging, production, etc.)
- > 
- > Corporate security guidelines state that all users need to have their own 
login, and since we
- > work with sensitive data, that extends down into the developers space. 
- > 
- > I know i can authenticate using PAM on linux, however that still requires a 
user to get created in
- > the DB. (I'm looking for a tool that helps manage that user creation 
portion)
- 
- ldap

oh, great! i thought that fell under the "authenticate" but not "authorize" 
umbrella.

thanks!

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Centralized User Management Tool?

[David Kerr]

Howdy,

Does anyone know of any tools or methods to handle centralized user management 
within postgres?

I've got about 20 DB servers (and growing) each requiring a different number 
and level of user access 
(think dev, qa, staging, production, etc.)

Corporate security guidelines state that all users need to have their own 
login, and since we
work with sensitive data, that extends down into the developers space. 

I know i can authenticate using PAM on linux, however that still requires a 
user to get created in
the DB. (I'm looking for a tool that helps manage that user creation portion)

Thanks

Dave

-- 
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] Question about Idle in TX

[David Kerr]

On Tue, Aug 03, 2010 at 03:57:27PM -0400, Tom Lane wrote:
- David Kerr  writes:
- > On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote:
- > - In recent versions of PG, no.  Before about 8.3 it was a Really Bad Idea,
- > - because the open transaction would prevent VACUUM from reclaiming storage.
- 
- > We're on 8.3.9, so hopefully it's fairly safe then?
- 
- Should be.  You might want to test it just to make sure I'm recalling
- correctly when that got fixed.  Do a BEGIN in one session, then in
- another session insert and delete some rows in a table, then VACUUM
- VERBOSE and see if they get cleaned up.
- 
-   regards, tom lane
- 
Sorry for the delayed response, been in RedHat training all week.

Seems like it worked fine in 9.0beta3:
SESSION 1
psql 
test=> begin;
BEGIN
test=>

SESSION 2
test=# insert into test (select generate_series(1001,1999,1));
INSERT 0 999
test=# delete from test;
DELETE 1999
test=# VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 1999 row versions in 8 pages
INFO:  "test": found 1999 removable, 0 nonremovable row versions in 8 out of 8
pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": truncated 8 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM


-- 
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] Question about Idle in TX

[David Kerr]

On Tue, Aug 03, 2010 at 03:57:27PM -0400, Tom Lane wrote:
- David Kerr  writes:
- > On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote:
- > - In recent versions of PG, no.  Before about 8.3 it was a Really Bad Idea,
- > - because the open transaction would prevent VACUUM from reclaiming storage.
- 
- > We're on 8.3.9, so hopefully it's fairly safe then?
- 
- Should be.  You might want to test it just to make sure I'm recalling
- correctly when that got fixed.  Do a BEGIN in one session, then in
- another session insert and delete some rows in a table, then VACUUM
- VERBOSE and see if they get cleaned up.
- 
-   regards, tom lane
- 

Ah yeah, good idea. I'll give it a shot. thanks!

Dave

-- 
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] Question about Idle in TX

[David Kerr]

On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote:
- David Kerr  writes:
- > for example: If a java program connects to the DB and does "begin;" 
- > and then internally does a "sleep 6 days"
- 
- > Does that cauz any issues other than eating a connection to the database?
- 
- In recent versions of PG, no.  Before about 8.3 it was a Really Bad Idea,
- because the open transaction would prevent VACUUM from reclaiming storage.
- 
- It's *still* a Really Bad Idea to begin a transaction, do something,
- and then sleep 6 days.  But "BEGIN" without any following commands
- has been fixed to be harmless, mainly because there are so many
- badly designed clients that do exactly that ...
- 
-   regards, tom lane
- 

ah ok, I think i'm in that group. We're using Talend and i think it might be
doing that, and it might be a bit of an effort to stop that from happening.

We're on 8.3.9, so hopefully it's fairly safe then?

Thanks

Dave

-- 
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] Question about Idle in TX

[David Kerr]

On Tue, Aug 03, 2010 at 03:30:46PM -0400, Greg Smith wrote:
- David Kerr wrote:
- >I know that "Idle in TXs" can interfere with Vaccums for example, but
- >I'm not sure if that's due to them usually having some form of lock on a
- >table.
- >  
- 
- Locks aren't the issue.  When you have a transaction open, the database 
- makes sure it can deliver a consistent view of the database for the 

Thanks guys!

I thought that was the case but wanted to be 100% sure before i kicked up a
fuss to my developers =)

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Question about Idle in TX

[David Kerr]

I know that Idle in Transactions are a problem, however I'm trying to 
assess how much of a problem.

for example: If a java program connects to the DB and does "begin;" 
and then internally does a "sleep 6 days"

Does that cauz any issues other than eating a connection to the database?

(note, nothing i have does this, i'm just trying to understand) 

I know that "Idle in TXs" can interfere with Vaccums for example, but
I'm not sure if that's due to them usually having some form of lock on a
table.

Thanks,

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] constraint/rule/trigger - insert into table X where not in table Y

[David Kerr]

Howdy,

I'm trying to think of the best way to handle this situation.

I've got 2 tables,  X and Y

Table X has a field foo varchar(20)
Table Y has a field bar varchar(20)

I want to enforce, that if table X.foo = 'dave' then you can't insert (or 
update) Y.bar = 'dave'

I know this is ideally done in the app, but in the interest of time to market 
i'm looking into
a simple DB solution.

I know i can do this with a trigger, but was wondering if there was a better 
way, like using
a constraint or a rule (seems like that would be pretty slick - but i can't 
wrap my head
around how to make it work)

Thanks

Dave

-- 
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] Uncable to commit: transaction marked for rollback

[David Kerr]

On 7/1/2010 11:10 AM, Tom Lane wrote:

David Kerr  writes:

I'm intermittantly getting this error message in a java app.
using Geronimo / Hibernate / Postgres 8.3.9



javax.transaction.RollbackException: Unable to commit: transaction marked for
rollback


You might have better luck asking about that on pgsql-jdbc --- there's
no such error condition in Postgres proper, so it must be coming from
somewhere on the Java side.

regards, tom lane



Ok - thanks!

Dave

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Uncable to commit: transaction marked for rollback

[David Kerr]

I'm intermittantly getting this error message in a java app. 
using Geronimo / Hibernate / Postgres 8.3.9

javax.transaction.RollbackException: Unable to commit: transaction marked for
rollback

Can someone give me a scenario where this would happen? "unable to commit"
makes everyone immediatly go to database issue. But it seems like an app 
issue to me.

I was thinking that maybe it's a 2 phase commit / XA or something like that.
(TX open, phase 1 commit fails, phase 2 commit throws this error?)

I can't imagine how this would happen within a single transaction setup.

Thanks!

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] UPDATE after Cancle

[David Kerr]

Howdy all -

I just got this odd behavior in my system. 

This is PG 8.3.10 on RedHat 5.4

psql bla
bla=# update blatab set blafield = replace(blafield,'XXX-1','XXX1-') 
where created_by = 'blauser';




Cancel request sent
UPDATE 8231584

I checked and the updated did happen.

autocommit is on, and i wasn't in a TX or anything strange like that.

My guess is that i hit ctl-c during the commit of the data and it wasn't 
able to rollback.

Any idea if that's the case? and if so, is that expected?

Thanks

Dave

-- 
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] Connection Pooling

[David Kerr]

On Mon, Apr 05, 2010 at 09:46:45PM -0600, Scott Marlowe wrote:
- On Mon, Apr 5, 2010 at 2:36 PM, David Kerr  wrote:
- > My app will have over 10k concurrent users. I have huge servers 32 cores 
(64bit), 64GB ram. RedHat linux.
- >
- > Those 10k users will all be logging in as one of 5 application users.
- 
- You should probably also look into memcached to take a lot of the read
- load off of your databases.
- 

Definitely, we've got a memcache hooked into Hibernate (or so i'm told) for the 
biggest app.

thanks!

Dave

-- 
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] Connection Pooling

[David Kerr]

On Mon, Apr 05, 2010 at 10:44:53PM -0400, Merlin Moncure wrote:
- On Mon, Apr 5, 2010 at 4:36 PM, David Kerr  wrote:
- > On Sat, Apr 03, 2010 at 09:32:25PM -0400, Merlin Moncure wrote:
- > Based on a lot of the comments i've gotten here, I'm starting to think that 
I've got the wrong idea about
- > connection pools and pooling in general. So, let me lay out some of my 
assumptions and my problem and
- > maybe we can go from there...
- >
- > My app will have over 10k concurrent users. I have huge servers 32 cores 
(64bit), 64GB ram. RedHat linux.
- >
- > Those 10k users will all be logging in as one of 5 application users.
- >
- > From following this list, and talking to our PG consultants, I know that 
for that many connecitons I need
- > to have a connection pooler. Because with postgres you shouldn't set 
max_connections much higher than 2000
- > (which is pushing it)
- 
- This is correct.  If you go with pgbouncer, you would want to use
- transaction pooling mode obviously.

oh, interesting, I had been looking at session mode, I'll have to check out 
transaction mode.

- > For the 5th application, an ETL job that could have as many as 1000 
concurrent processes/connections,
- > i don't have a java container. it's just a raw jar file that gets run via 
java .
- >
- > That's what I'm aiming to pool, and i'd like to do it without modifying the 
code if possible.
- 
- pgbouncer is totally transparent.  I manage quite a few databases and
- I use it (w/session mode) so I can psql to a single host (localhost),
- and bounce between different databases.  Like I said earlier, you have
- discreet pools based on role -- otherwise there would be no really
- good way to control the role your queries would operate under.  This
- will keep your etl from drilling your box, and if you keep your
- pool_size under the number of cores you have, you will have some
- available if things get really dicey, which is nice.

right, so that's kind of my issue, the ETL process will log in as 1 user, and 
connect to 1 database
wouldn't that mean that if i set max_pool_size = 30 then i'd have at max 30 
connections allowed to
the DB from that user?

- caveats:
- *) no openssl, but stunnel works well (you may have to grab a recent stunnel)
- *) transaction mode blocks use of certain database features, like
- notifies.  again, doesn't sound too bad for you
- 
- doesn't sound like you need openssl though.  If you have the ability
- to set up a test environment, I'd set it up and give it a shot.

right i wouldn't need either of those.

Thanks!

Dave

-- 
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] Connection Pooling

[David Kerr]

On Sat, Apr 03, 2010 at 09:32:25PM -0400, Merlin Moncure wrote:
- On Fri, Mar 26, 2010 at 5:17 PM, David Kerr  wrote:
- > Howdy all,
- >
- > I have some apps that are connecting to my DB via direct JDBC and I'd like 
to pool their connections.
- >
- > I've been looking at poolers for a while, and pgbouncer and pgpool-ii seem 
to be some of the most popular, so
- > i've started with those.
- >
- >
- > I'm setting up pgbouncer, and i've hit a bit of a snag. Hopefully someone 
can tell me if pgbouncer or pgpool are
- > capable of this (and if so, how to do it) or alternatly a pooler that can...
- >
- > What I'd like to be able to do is this (not using pooler syntax, this is 
just a high level of what i want to achive)
- >
- > Say i set max pool size = 10 connections. and max # of pools = 5.
- >
- > That means that i should have 5 connections to my database covering 50 
connections total.
- >
- > I can't really seem to make that work with pgbouncer without naming the 
pools separetly. (pool1 = dbname = a, pool2 = dbname =a)
- > which means my app is tied to a pool (or has to specifically code to rotate 
pools...) which is not really desireable.
- 
- I have a lot of respect for pgbouncer (haven't used pgpool).  One
- possible way to do what you're thinking is to rotate the pool on user.
-  In bouncer each database role gets its own pool (if you understand
- how transaction mode works you can see why it has to work this way).
- Not sure if this is helpful.  Why are you trying to separate the pools
- like that?
- 
- merlin

Based on a lot of the comments i've gotten here, I'm starting to think that 
I've got the wrong idea about
connection pools and pooling in general. So, let me lay out some of my 
assumptions and my problem and
maybe we can go from there...

My app will have over 10k concurrent users. I have huge servers 32 cores 
(64bit), 64GB ram. RedHat linux.

Those 10k users will all be logging in as one of 5 application users. 

>From following this list, and talking to our PG consultants, I know that for 
>that many connecitons I need 
to have a connection pooler. Because with postgres you shouldn't set 
max_connections much higher than 2000 
(which is pushing it) 

For 4 out of the 5 applications, we're using Geronimo which has it's own pooler 
in the manner that
I've described above. 

For the 5th application, an ETL job that could have as many as 1000 concurrent 
processes/connections,
i don't have a java container. it's just a raw jar file that gets run via java 
.

That's what I'm aiming to pool, and i'd like to do it without modifying the 
code if possible.

Thanks

Dave

-- 
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] Connection Pooling

[David Kerr]

On 3/27/2010 12:46 AM, John R Pierce wrote:

Allan Kamau wrote:

You may also have a look at Commons DBCP from Apache software
foundation, "http://commons.apache.org/dbcp/";. I have used it for a
few projects and have had no problems.


for that matter, JDBC has its own connection pooling in java.





It looks like both of those solutions require a coding change. I'm 
hoping for a middleware solution similar to pgpool/pgbouncer.


Thanks!

Dave

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Connection Pooling

[David Kerr]

Howdy all,

I have some apps that are connecting to my DB via direct JDBC and I'd like to 
pool their connections.

I've been looking at poolers for a while, and pgbouncer and pgpool-ii seem to 
be some of the most popular, so
i've started with those.


I'm setting up pgbouncer, and i've hit a bit of a snag. Hopefully someone can 
tell me if pgbouncer or pgpool are 
capable of this (and if so, how to do it) or alternatly a pooler that can...

What I'd like to be able to do is this (not using pooler syntax, this is just a 
high level of what i want to achive)

Say i set max pool size = 10 connections. and max # of pools = 5. 

That means that i should have 5 connections to my database covering 50 
connections total.

I can't really seem to make that work with pgbouncer without naming the pools 
separetly. (pool1 = dbname = a, pool2 = dbname =a)
which means my app is tied to a pool (or has to specifically code to rotate 
pools...) which is not really desireable.

is what i'm looking to do possible in pgbouncer or pgpool? or anything?

Thanks

Dave

-- 
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] ERROR: permission denied to finish prepared transaction

[David Kerr]

On Tue, Feb 16, 2010 at 12:57:28PM -0800, David Kerr wrote:
- I'm seeing a bunch of these error messages:
- Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-1] 
user=xy,db=x,pid=26420 ERROR:  permission denied to finish prepared transaction
- Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-2] 
user=xy,db=x,pid=26420 HINT:  Must be superuser or the user that prepared the
- transaction.
- Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-3] 
user=xy,db=x,pid=26420 STATEMENT:  ROLLBACK PREPARED
- Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-4]
- 
'1197822575_uwQAAABHVE1JRA==_AQBHVE1JRA
- Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-5] 
=='
- 
- 
- As far as I can tell, that means that user xy is trying to rollback a 
prepared TX that it didn't create. 
- is that the only reason this error would show up?
- 
- I'm asking becase, as far as I can tell, xy is the only user on the box that 
would be doing prepared tx's
- 
- this is 8.3


Bump.  =)

Is it possible that this is a permissions error?

any ideas? 

Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ERROR: permission denied to finish prepared transaction

[David Kerr]

I'm seeing a bunch of these error messages:
Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-1] user=xy,db=x,pid=26420 
ERROR:  permission denied to finish prepared transaction
Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-2] user=xy,db=x,pid=26420 
HINT:  Must be superuser or the user that prepared the
transaction.
Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-3] user=xy,db=x,pid=26420 
STATEMENT:  ROLLBACK PREPARED
Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-4]
'1197822575_uwQAAABHVE1JRA==_AQBHVE1JRA
Feb 16 12:04:51 QA-HC01-DB01 postgres-hc01[26420]: [2-5] 
=='


As far as I can tell, that means that user xy is trying to rollback a prepared 
TX that it didn't create. 
is that the only reason this error would show up?

I'm asking becase, as far as I can tell, xy is the only user on the box that 
would be doing prepared tx's

this is 8.3

Thanks

Dave

-- 
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] Deadlock Detected

[David Kerr]

On Thu, Feb 11, 2010 at 05:01:37PM -0500, Vick Khera wrote:
- On Thu, Feb 11, 2010 at 4:50 PM, David Kerr  wrote:
- > currently, i just get "Error: Deadlock Detected" but it doesn't tell me the 
tables involved.
- >
- 
- Where do you see this?  The postgres log file surely has more details.
-  Also, what version etc. etc.

you're right, my mistake. Plenty of info in the log (where I was looking, but 
not closely enough).

thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Deadlock Detected

[David Kerr]

Is there a setting that will give me a more verbose log message when a deadlock 
is detected?

currently, i just get "Error: Deadlock Detected" but it doesn't tell me the 
tables involved.

Thanks

Dave

-- 
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] vacuumdb ERROR: out of memory

[David Kerr]

Magnus Hagander wrote:

On Tue, Feb 9, 2010 at 09:53, David Kerr  wrote:

Guillaume Lelarge wrote:

Le 09/02/2010 09:35, David Kerr a écrit :

Guillaume Lelarge wrote:

Le 09/02/2010 05:49, John R Pierce a écrit :

David Kerr wrote:

maintenance_work_mem = 1GB

So evidently, when it tries to actually allocate 1GB, it can't do it.
Ergo, that setting is too high for your machine.
...

seems like i've got 2GB free.

is this a 64bit postgres build?

if not, you're probably running out of virtual address space in the 32
bit user space, which is limited to like 2gb.


IIRC, the virtual address space in 32bit platforms is 4GB.


IIRC, on Linux that will be a max of 3Gb available to userspace
processes. Certainly not 4Gb - but it could be 2.



it is a 32bit box.


the other possibility, and here I'm not sure, is that
maintenance_work_mem is coming out of shared memory, and if so, you've
exceeeded your SHMMAX kernel limit.


work_mem and maintenance_work_mem are not shared memory. AFAICT, David
need to check if the VACUUM works with a lower setting for
maintenance_work_mem. For example, 512MB could work.



Yes, vacuum -z works with 512MB. so any idea what was causing it not to
work with 1GB?


Tom already explained that. The process couldn't get the 1GB it was
allowed to use with this setting of maintenance_work_mem.



Well, that made sense until I freed up a lot of memory on the box. I had
tried it again with 2GB of free memory available to me to use. My ulimits
are all unlimited. So i'm wondering if there's a kernel setting I need, or
something similar.


You may well be running out of *address space* rather than pure
memory. PostgreSQL is failing to allocate 1Gb of *continuous* memory.
Not just 1Gb of memory anywhere. Shared memory, for example, lives at
a fixed location already. There may be >1Gb free in the address space,
just not where you need it.


Ok that makes sense, it never occurred to me that malloc would require
a contiguous chunk the full size of the allocation request.



In general, when you are starting to talk about things like 1Gb
maintenance_work_mem, you should've switched to 64-bit a while ago :-)


Yes, I know, I actually specced out the server as 64 bit but someone 
messed up and i'm making due with what I have.



oh, hmm, my swap is 517Megs, that probably isn't helping. Usually swap is
1.5/2x available memory, isn't it? (it is for most unix's and oracle, but
i'm not sure about PG and linux)


I don't think that affects this problem.


ok.

Thanks

Dave


--
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] viewing large queries in pg_stat_activity

[David Kerr]

Magnus Hagander wrote:

2010/2/9 David Kerr :

It seems like pg_stat_activity truncates the current_query to about 1024 
characters.

The field is a text, so i'm wondering if there is a way to see the full query?

(I know i can turn on log_statement=all, or log_min_duration_statement) but i'd 
like
something that doesn't require a restart.


The data isn't being tracked, so there is no way to show it. The
length of the query tracked can be found in the parameter
track_activity_query_size. Note that increasing that will obviously
increase the overhead of collecting it.




That's documented in 8.4, does anyone know if it's available in 8.3?

Thanks

Dave

--
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] vacuumdb ERROR: out of memory

[David Kerr]

Guillaume Lelarge wrote:

Le 09/02/2010 09:35, David Kerr a écrit :

Guillaume Lelarge wrote:

Le 09/02/2010 05:49, John R Pierce a écrit :

David Kerr wrote:

maintenance_work_mem = 1GB

So evidently, when it tries to actually allocate 1GB, it can't do it.
Ergo, that setting is too high for your machine.
...

seems like i've got 2GB free.

is this a 64bit postgres build?

if not, you're probably running out of virtual address space in the 32
bit user space, which is limited to like 2gb.


IIRC, the virtual address space in 32bit platforms is 4GB.

it is a 32bit box.


the other possibility, and here I'm not sure, is that
maintenance_work_mem is coming out of shared memory, and if so, you've
exceeeded your SHMMAX kernel limit.


work_mem and maintenance_work_mem are not shared memory. AFAICT, David
need to check if the VACUUM works with a lower setting for
maintenance_work_mem. For example, 512MB could work.



Yes, vacuum -z works with 512MB. so any idea what was causing it not to
work with 1GB?



Tom already explained that. The process couldn't get the 1GB it was
allowed to use with this setting of maintenance_work_mem.


Well, that made sense until I freed up a lot of memory on the box. I had 
tried it again with 2GB of free memory available to me to use. My 
ulimits are all unlimited. So i'm wondering if there's a kernel setting 
I need, or something similar.


oh, hmm, my swap is 517Megs, that probably isn't helping. Usually swap 
is 1.5/2x available memory, isn't it? (it is for most unix's and oracle, 
but i'm not sure about PG and linux)


Thanks

Dave

--
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] vacuumdb ERROR: out of memory

[David Kerr]

Guillaume Lelarge wrote:

Le 09/02/2010 05:49, John R Pierce a écrit :

David Kerr wrote:

maintenance_work_mem = 1GB

So evidently, when it tries to actually allocate 1GB, it can't do it.
Ergo, that setting is too high for your machine.
...

seems like i've got 2GB free.


is this a 64bit postgres build?

if not, you're probably running out of virtual address space in the 32
bit user space, which is limited to like 2gb.



IIRC, the virtual address space in 32bit platforms is 4GB.


it is a 32bit box.


the other possibility, and here I'm not sure, is that
maintenance_work_mem is coming out of shared memory, and if so, you've
exceeeded your SHMMAX kernel limit.



work_mem and maintenance_work_mem are not shared memory. AFAICT, David
need to check if the VACUUM works with a lower setting for
maintenance_work_mem. For example, 512MB could work.




Yes, vacuum -z works with 512MB. so any idea what was causing it not to 
work with 1GB?


Thanks

Dave

--
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] vacuumdb ERROR: out of memory

[David Kerr]

Tom Lane wrote:

David Kerr  writes:

Tom Lane wrote:

David Kerr  writes:

I get:
vacuumdb: vacuuming of database "assessment" failed: ERROR:  out of memory
DETAIL:  Failed on request of size 1073741820.

What have you got maintenance_work_mem set to?



maintenance_work_mem = 1GB


So evidently, when it tries to actually allocate 1GB, it can't do it.
Ergo, that setting is too high for your machine.

regards, tom lane



AHhh, ok. I was thinking that it was filling up the 1GB i allocated to it.

I just dropped the memory allocated to the instance down by about 10GB 
and i'm still getting the error though.


> free
 total   used   free sharedbuffers cached
Mem:  34997288   328218282175460  0 227420   32541844
-/+ buffers/cache:  52564   34944724
Swap:   530136 36 530100


seems like i've got 2GB free.

Food for thought..

I'll look more into it tomorrow morning though.

Thanks

Dave

--
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] vacuumdb ERROR: out of memory

[David Kerr]

Tom Lane wrote:

David Kerr  writes:

I'm getting error:
When I try
vacuumdb -z assessment
or
vacuumdb assessment



I get:
vacuumdb: vacuuming of database "assessment" failed: ERROR:  out of memory
DETAIL:  Failed on request of size 1073741820.


What have you got maintenance_work_mem set to?

regards, tom lane



maintenance_work_mem = 1GB

I don't know if it matters but my biggest relation is 7GB
(total including indexes is 16GB) with my total DB size being 20GB


Dave




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] viewing large queries in pg_stat_activity

[David Kerr]

It seems like pg_stat_activity truncates the current_query to about 1024 
characters.

The field is a text, so i'm wondering if there is a way to see the full query?

(I know i can turn on log_statement=all, or log_min_duration_statement) but i'd 
like
something that doesn't require a restart.

Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] vacuumdb ERROR: out of memory

[David Kerr]

I'm getting error:

When I try
vacuumdb -z assessment
or
vacuumdb assessment

I get:
vacuumdb: vacuuming of database "assessment" failed: ERROR:  out of memory
DETAIL:  Failed on request of size 1073741820.

The only way i can actually analyze the DB is if i do a vacuumdb -f

The database is currently sitting at aproximatly 1/10th of my total data. 

I'm on 8.3.5, SLES 11 Linux .

Any ideas?

Thanks

Dave

-- 
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] PostgreSQL + Hibernate, Apache Mod Security, SQL Injection and you (a love story)

[David Kerr]

On Fri, Feb 05, 2010 at 09:19:40PM +0100, Sebastian Hennebrueder wrote:
- John R Pierce schrieb:
- >David Kerr wrote:
- >>Howdy all,
- >>
- >>We're using Postgres 8.3 with all of our apps connecting to the database
- >>with Hibernate / JPA.
- >>
- >>Our security team is concerned about SQL Injection attacks, and would 
- >>like to implement some mod_security rules to protect against it.
- >>
- >>From what I've read Postgres vanilla is pretty robust when it comes to
- >>dealing with SQL Injection attacks,
- >>
- >
- >that would be a function of how you use Postgresql.   if you do the 
- >typical PHP hacker style of building statements with inline values then 
- >executing them, you're vunerable unless you totally sanitize all your 
- >inputs. see http://xkcd.com/327/
- >
- >if you use parameterized calls (easy in perl, java, etc but not so easy 
- >in php), you're should be immune.  in the past there were some issues 
- >with specific evil mis-coded UTF8 sequences, but afaik, thats been 
- >cleared up for quite a while.
- >
- >
- >>and when you put an abstraction layer like Hibernate on top of it, 
- >>you're basically rock solid against them.
- >
- >I would assume so, but I'm not familiar with the implementation details 
- >of Hibernate.
- >
- >
- >
- It dependends how you use Hibernate. If you do String concatenation
- instead of parameterized queries, then you can encounter the same
- injection problems like SQL.

Ok so Hibernante could suffer from the same issues as any framework.

Thanks

Dave

-- 
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] PostgreSQL + Hibernate, Apache Mod Security, SQL Injection and you (a love story)

[David Kerr]

On Fri, Feb 05, 2010 at 12:09:57PM -0800, John R Pierce wrote:
- that would be a function of how you use Postgresql.   if you do the 
- typical PHP hacker style of building statements with inline values then 
- executing them, you're vunerable unless you totally sanitize all your 
- inputs. see http://xkcd.com/327/

Right, so when dealing with a high security environment you want to assume
someone made a mistake and left you vunerable in this area.

- >Does anyone have experience here? One of our security people found a 
- >generic mod_security config file that had a couple of postgres entries 
- >in it. Is there a full Postgres config for mod_security that the 
- >community recommends?
- >
- >Can anyone give me a good pros or cons of using mod_security when you 
- >have Postgres + Hibernate?
- >
- 
- isn't mod_security purely for Apache httpd applications?  if you're not 
- using apache httpd, it seems like there's no point in using mod_security.

We'll have httpd handing off to Geronimo. From what i can gather mod_security
will balk at any url that contains one of it's keywords. 

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL + Hibernate, Apache Mod Security, SQL Injection and you (a love story)

[David Kerr]

Howdy all,

We're using Postgres 8.3 with all of our apps connecting to the database
with Hibernate / JPA.

Our security team is concerned about SQL Injection attacks, and would 
like to implement some mod_security rules to protect against it.


From what I've read Postgres vanilla is pretty robust when it comes to
dealing with SQL Injection attacks, and when you put an abstraction 
layer like Hibernate on top of it, you're basically rock solid against them.


Does anyone have experience here? One of our security people found a 
generic mod_security config file that had a couple of postgres entries 
in it. Is there a full Postgres config for mod_security that the 
community recommends?


Can anyone give me a good pros or cons of using mod_security when you 
have Postgres + Hibernate?


At this stage in our project I'm trying to avoid making decisions based 
on statements like "PostgreSQL is 100% secure" or "More security can't 
hurt" any change like this impacts our delivery schedule, if we are 
going to do it we need to understand why and what benefits it brings.


Thanks

Dave

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general