Re: [GENERAL] [ADMIN] How Many PG_Locks are considered too many

Thank you appreciated Sent from my iPhone > On 30 Jul 2015, at 20:05, Merlin Moncure wrote: > >> On Thu, Jul 30, 2015 at 9:19 AM, John Scalia wrote: >> Seconding Peter on this one; it's a lot more important should one of those >> locks be hanging around, say for hours or days, not how many hav

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

Heikki, > A-ha, I succeeded to reproduce this now on my laptop, with pgbench! It > seems to be important to have a very large number of connections: > > pgbench -n -c400 -j4 -T600 -P5 > > That got stuck after a few minutes. I'm using commit_delay=100. Because the original reporter seems to have

[GENERAL] PSA: linux kernel bug in TRIM support corrupts data with md raid levels 0 and 10

http://www.spinics.net/lists/raid/msg49452.html (via slashdot) merlin -- 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 copy from with timestamp format

> On 30 Jul 2015, at 17:59, Sherrylyn Branchaw wrote: > > From here: > > http://www.postgresql.org/docs/9.4/interactive/sql-copy.html > > "COPY can only be used with plain tables, not with views. However, you can > write COPY (SELECT * FROM viewname) TO " > > Right, so you can COPY FROM

Re: [GENERAL] [ADMIN] How Many PG_Locks are considered too many

On Thu, Jul 30, 2015 at 9:19 AM, John Scalia wrote: > Seconding Peter on this one; it's a lot more important should one of those > locks be hanging around, say for hours or days, not how many have come and > gone. Also, it's good to focus on *ungranted* locks. Typically the only time I care abo

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

That would need a replica of the data probably which is not possible (tablespace is 4TB). *Spiros Ioannou IT Manager, inAccesswww.inaccess.com M: +30 6973-903808T: +30 210-6802-358* On 30 July 2015 at 21:47, Scott Marlowe wrote: > You might want to try pg replay:

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

You might want to try pg replay: http://laurenz.github.io/pgreplay/ On Thu, Jul 30, 2015 at 7:23 AM, Spiros Ioannou wrote: > I'm very sorry but we don't have a synthetic load generator for our > testing setup, only production and that is on SLA. I would be happy to test > the next release though

Re: [GENERAL] Question about copy from with timestamp format

On 07/30/2015 08:59 AM, Sherrylyn Branchaw wrote: From here: http://www.postgresql.org/docs/9.4/interactive/sql-copy.html "COPY can only be used with plain tables, not with views. However, you can write COPY (SELECT * FROM viewname) TO " Right, so you can COPY FROM a view, but not, as far

Re: [GENERAL] Question about copy from with timestamp format

On 07/30/2015 08:59 AM, Sherrylyn Branchaw wrote: From here: http://www.postgresql.org/docs/9.4/interactive/sql-copy.html "COPY can only be used with plain tables, not with views. However, you can write COPY (SELECT * FROM viewname) TO " Right, so you can COPY FROM a view, but not, as far

Re: [GENERAL] Question about copy from with timestamp format

>From here: http://www.postgresql.org/docs/9.4/interactive/sql-copy.html "COPY can only be used with plain tables, not with views. However, you can write COPY (SELECT * FROM viewname) TO " Right, so you can COPY FROM a view, but not, as far as I can tell, TO a view, unless Alban found a work

Re: [GENERAL] Question about copy from with timestamp format

On 07/30/2015 08:44 AM, Sherrylyn Branchaw wrote: I was thinking that perhaps an updatable view might do the trick? Interesting idea! Are you able to get it to work? I keep getting 'ERROR: cannot copy to view "view_ts_test"' even before my trigger fires. Inserting, though, works fine. From h

Re: [GENERAL] Transaction ID Wraparound Monitoring

On 07/30/2015 08:41 AM, Jan Keirse wrote: On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver wrote: On 07/30/2015 02:55 AM, Jan Keirse wrote: Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be alerted before we get into

Re: [GENERAL] Question about copy from with timestamp format

I was thinking that perhaps an updatable view might do the trick? Interesting idea! Are you able to get it to work? I keep getting 'ERROR: cannot copy to view "view_ts_test"' even before my trigger fires. Inserting, though, works fine. Still curious why the triggers I'm writing won't fire before

Re: [GENERAL] Transaction ID Wraparound Monitoring

On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver wrote: > On 07/30/2015 02:55 AM, Jan Keirse wrote: >> >> Hello, >> >> we have some very write heavy databases and I have our monitoring >> system watch the transaction age of my databases to be alerted before >> we get into problems in case autovacuum

Re: [GENERAL] user connection not recorded?

Adrian Klaver writes: > Alright, but the part that has me confused is this comment in the code: > * In standalone mode and in autovacuum worker processes, we use a fixed > * ID, otherwise we figure it out from the authenticated user name. > and this > else if (IsBackgroundWorker) > I read

Re: [GENERAL] instr detail

here is the example tks help.. http://stackoverflow.com/questions/2965655/how-to-find-the-first-and-last-occurrences-of-a-specific-character-inside-a-stri On Thu, Jul 30, 2015 at 7:38 PM, Ramesh T wrote: > select position('.' in '.T.homas') > result > > 1 > it returns first postion.but

Re: [GENERAL] instr detail

select position('.' in '.T.homas') result 1 it returns first postion.but I need last occurence of ' . ' . actual result -- 3 any help..?appreciated. On Thu, Jul 30, 2015 at 5:00 AM, Tom Lane wrote: > Melvin Davidson writes: > > Based om the definition of Oracle instr(), the equiva

Re: [GENERAL] user connection not recorded?

On 07/30/2015 07:46 AM, Tom Lane wrote: Adrian Klaver writes: Eventually got around to figuring that. So just for my reference, the code snippet I showed from postinit.c seems to show a path where a username is not used but is substituted with BOOTSTRAP_SUPERUSERID. That's single-user mode.

Re: [GENERAL] user connection not recorded?

Adrian Klaver writes: > Eventually got around to figuring that. So just for my reference, the > code snippet I showed from postinit.c seems to show a path where a > username is not used but is substituted with BOOTSTRAP_SUPERUSERID. That's single-user mode. > Am I following that correctly and

Re: [GENERAL] user connection not recorded?

On 07/30/2015 07:21 AM, Tom Lane wrote: Adrian Klaver writes: On 07/30/2015 06:42 AM, Melvin Davidson wrote: I can understand that the host is not available in nslookup, but why is the user not being recorded? A quick look at the source shows that Postgres system process can have NULL user

Re: [GENERAL] user connection not recorded?

On 07/30/2015 07:12 AM, Melvin Davidson wrote: Thanks for the quick reply Adrian. Further testing shows this can happen in other situations. So: aklaver@panda:~> psql -d test psql (9.4.2) Type "help" for help. test=> where trust is set up for local connections yields: [unknown]-2015-07-30

Re: [GENERAL] [ADMIN] How Many PG_Locks are considered too many

Seconding Peter on this one; it's a lot more important should one of those locks be hanging around, say for hours or days, not how many have come and gone. -- Jay On Thu, Jul 30, 2015 at 8:57 AM, Renato Oliveira < renato.olive...@cantabcapital.com> wrote: > Peter thank you much appreciated > > Se

Re: [GENERAL] user connection not recorded?

Thanks for the quick reply Adrian. On Thu, Jul 30, 2015 at 10:09 AM, Adrian Klaver wrote: > On 07/30/2015 06:42 AM, Melvin Davidson wrote: > > I have a puzzling question. > > > > All through the error log, there are connections for [unknown] user. > > EG: > > 2015-07-30 00:00:00 CDT [6577]: [1-

Re: [GENERAL] [ADMIN] How Many PG_Locks are considered too many

Peter thank you much appreciated Sent from my iPhone > On 30 Jul 2015, at 14:54, Peter Eisentraut wrote: > >> On 7/30/15 6:13 AM, Renato Oliveira wrote: >> We have a Nagios plugin, which monitors pg_locks and almost daily we see >> 3000 to 4 pg_locks. >> >> Can we just ignore them, can we

[GENERAL] user connection not recorded?

I have a puzzling question. All through the error log, there are connections for [unknown] user. EG: 2015-07-30 00:00:00 CDT [6577]: [1-1]: : [unknown]: LOG: connection received: host=173.239.101.98 port=50687 The log_line_prefix is %t [%p]: [%l-1]: %h: %u: I can understand that the host is

Re: [GENERAL] [ADMIN] How Many PG_Locks are considered too many

On 7/30/15 6:13 AM, Renato Oliveira wrote: > We have a Nagios plugin, which monitors pg_locks and almost daily we see > 3000 to 4 pg_locks. > > Can we just ignore them, can we let them grow without worrying? > > How many pg_locks are considered unsafe for any given postgres server? That depe

Re: [GENERAL] Logical decoding off of a replica?

Great, thank you for the response! On Thu, Jul 30, 2015 at 7:33 AM, Andres Freund wrote: > On 2015-07-28 17:54:57 +, Curt Micol wrote: >> Hello, >> >> I've been working to get a replica setup to perform logical decoding >> and haven't been able to get the right configuration. I've tried >> ev

Re: [GENERAL] Transaction ID Wraparound Monitoring

On 07/30/2015 02:55 AM, Jan Keirse wrote: Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be alerted before we get into problems in case autovacuum can't keep up to avoid transaction ID wraparound. The query I am execu

[GENERAL] Exclusively locking parent tables while disinheriting children.

Hi, When working with partition sets, we're seeing occasional errors of "could not find inherited attribute..." in Select queries. This is apparently caused when an "ALTER TABLE ... NO INHERIT" runs concurrently with another transaction selecting from the relevant child table. I found an old

[GENERAL] How Many PG_Locks are considered too many

Hi I have few questions, if anyone could help me, it will be very much appreciated. We have a Nagios plugin, which monitors pg_locks and almost daily we see 3000 to 4 pg_locks. Can we just ignore them, can we let them grow without worrying? How many pg_locks are considered unsafe for any gi

Re: [GENERAL] Question about copy from with timestamp format

> On 30 Jul 2015, at 2:27, Sherrylyn Branchaw wrote: > > Based on your PS asking about data types and commenting that you don't want > to put hour in a separate column, it sounds like this is a brand-new table > you're creating. If so, and if this is a one-time COPY operation, you can > creat

[GENERAL] Transaction ID Wraparound Monitoring

Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be alerted before we get into problems in case autovacuum can't keep up to avoid transaction ID wraparound. The query I am executing is this: SELECT max(age(pg_database.dat

Re: [GENERAL] Logical decoding off of a replica?

On 2015-07-28 17:54:57 +, Curt Micol wrote: > Hello, > > I've been working to get a replica setup to perform logical decoding > and haven't been able to get the right configuration. I've tried > everything I can think of. While researching I found this post on > Stack Overflow: > http://stacko