Re: [GENERAL] pg_stat_statements -- Historical Query

On Thu, Aug 10, 2017 at 6:23 AM, anand086 wrote: > I was looking for a way to maintain historical query details in Postgres to > answer questions like > > What was the sql call rate between time X and Y? > Did the execution count increase for the query increase between time X

[GENERAL] pg_stat_statements -- Historical Query

Hi, I was looking for a way to maintain historical query details in Postgres to answer questions like What was the sql call rate between time X and Y? Did the execution count increase for the query increase between time X and Y? In past 10mins what all queries were run in the db? and few others

Re: [GENERAL] Audit based on role

Thank you all for your input. We plan to use ALTER USER username SET log_statement = mod when the user account is created. Regards, Anand -- View this message in context: http://www.postgresql-archive.org/Audit-based-on-role-tp5976507p5977104.html Sent from the PostgreSQL - general mailing

Re: [GENERAL] Isolation of schema renames

On 10 August 2017 at 12:14, Tom Lane wrote: > Ben Leslie writes: Thanks for the quick response, I appreciate it. > I'm wondering if I can/should expect schema renames to be isolated. Nope, you should not. That's fine. I think I can achieve what I want

Re: [GENERAL] Isolation of schema renames

On Thu, Aug 10, 2017 at 2:14 PM, Tom Lane wrote: > Ben Leslie writes: >> I'm wondering if I can/should expect schema renames to be isolated. > > Nope, you should not. > > This is not an especially easy thing to fix, because to have the system > behave as

Re: [GENERAL] Isolation of schema renames

Ben Leslie writes: > I'm wondering if I can/should expect schema renames to be isolated. Nope, you should not. This is not an especially easy thing to fix, because to have the system behave as you wish it did, your second transaction would have to be ignoring

[GENERAL] Isolation of schema renames

I'm wondering if I can/should expect schema renames to be isolated. For example, I have two schemas "test" and "test_new". Each with a "test" table (with same columns, but different data). In one transaction I'm renaming the schemas test => test_old, test_new => test. I.e.: BEGIN; ALTER SCHEMA

Re: [GENERAL] Multixact members limit exceeded

On Thu, Aug 10, 2017 at 10:26 AM, Thomas Munro wrote: > eaten a total of n! member space with an average size of n/2 per Erm, math fail, not n! but 1 + 2 + ... + n. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-general mailing list

Re: [GENERAL] Multixact members limit exceeded

Thomas Munro wrote: > One thing I noticed is that there are ~4 billion members (that's how > many you have when you run out of member space), but only ~128 million > multixacts, so I think the average multixact has ~32 members. > Considering the way that multixacts grow by copying and extending

Re: [GENERAL] streaming replication - crash on standby

Hi, Please quote properly on postgres mailing lists. On 2017-08-09 22:31:23 +, Seong Son (US) wrote: > I see. Thank you. > > But the Postgresql process had crashed at that time so the streaming > replication was no longer working. Why would it crash and is that normal? You've given us

Re: [GENERAL] streaming replication - crash on standby

I see. Thank you. But the Postgresql process had crashed at that time so the streaming replication was no longer working. Why would it crash and is that normal? Thanks, Seong This email and any files transmitted with it are intended solely for the use of the individual or entity to whom

Re: [GENERAL] Multixact members limit exceeded

On Wed, Aug 9, 2017 at 10:06 PM, Peter Hunčár wrote: > SELECT relname, age(relminmxid) as mxid_age, I'm pretty sure you can't use age(xid) to compute the age of a multixact ID. Although they have type xid in pg_class and the age(xid) function will happily subtract your multixact

Re: [GENERAL] streaming replication - crash on standby

Hi, On 2017-08-09 22:03:43 +, Seong Son (US) wrote: > The last line from pg_xlogdump of the last WAL file on the crashed standby > server shows the following. > > pg_xlogdump: FATAL: error in WAL record at DF/4CB95FD0: unexpected pageaddr > DB/62B96000 in log segment

Re: [GENERAL] Interesting streaming replication issue

Hi, On 2017-07-27 13:00:17 +1000, James Sewell wrote: > Hi all, > > I've got two servers (A,B) which are part of a streaming replication pair. > A is the master, B is a hot standby. I'm sending archived WAL to a > directory on A, B is reading it via SCP. > > This all works fine normally. I'm on

[GENERAL] streaming replication - crash on standby

The last line from pg_xlogdump of the last WAL file on the crashed standby server shows the following. pg_xlogdump: FATAL: error in WAL record at DF/4CB95FD0: unexpected pageaddr DB/62B96000 in log segment 00DF004C, offset 12148736 I believe this means the standby server

Re: [GENERAL] ErrorCode=-2147467259 storing a .net string

Solved. I recreated the database with LATIN9 encoding. -- 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] Multixact members limit exceeded

Hello, there are currently no transactions whatsoever, the app is paused. I can even restart the database if needed. I ran vacuum full, because as I mentioned above it seemed to me that manual vacuum did not change the relminmxid of a table. Unfortunately, an upgrade is not an option :(

[GENERAL] About using IMCS moldule

Hi, I'm trying to use imcs module to store table space in memory. But, It dose not work with 9.6 version anymore. Releasing 9.6, lwlockassign() function has been deleted, cause of some issues. So, What I want to ask you something is that postgresql decide not to support to imcs module officially

[GENERAL] ErrorCode=-2147467259 storing a .net string

The database has UTF8 encoding. The Windows machine's locale where the string was created is set to es_AR.utf8. When sending the data (thru Devart's Devart.Data.PostgreSql module) the server returned the error in the subject. I don't know how exactly the offending string was encoded. Any help

Re: [GENERAL] Multixact members limit exceeded

Hi, On 2017-08-09 16:30:03 -0400, Alvaro Herrera wrote: > > One particular table before vacuum full: > > > >relname| relminmxid | table_size > > --++ > > delayed_jobs | 1554151198 | 21 GB > > > > And

Re: [GENERAL] Multixact members limit exceeded

Peter Hunčár wrote: > Hi, > > Thank you, yes those are the 'urgent' tables, I'd talk to the developers > regarding the locks.I too think, there's something 'fishy' going on. I bet you have a lot of subtransactions -- maybe a plpgsql block with an EXCEPTION clause that's doing something

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 3:32 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Aug 9, 2017 at 12:26 PM, Melvin Davidson > wrote: > >> *>I'm am wondering whether "REASSIGNED OWNED" **needs fixing as well* >> >> *Possibly, but as the op is on 9.3, it is not

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 12:26 PM, Melvin Davidson wrote: > *>I'm am wondering whether "REASSIGNED OWNED" **needs fixing as well* > > *Possibly, but as the op is on 9.3, it is not available to him.* > ​You should check the docs again...​ > *I would also argue that since*

Re: [GENERAL] Multixact members limit exceeded

Hi, Thank you, yes those are the 'urgent' tables, I'd talk to the developers regarding the locks.I too think, there's something 'fishy' going on. Anyway, could it be that autovacuum blocks manual vacuum? Because I ran vacuum (full, verbose) and some tables finished quite fast, with huge amount

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

On Wed, Aug 9, 2017 at 6:27 AM, ADSJ (Adam Sjøgren) wrote: > On 2017-06-21 Adam Sjøgren wrote: > >> Adam Sjøgren wrote: > >>> Meanwhile, I can report that I have upgraded from 9.3.14 to 9.3.17 and >>> the errors keep appearing the log.

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 3:00 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson > wrote: > >> >> >> On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Wed, Aug 9, 2017

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson wrote: > > > On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane wrote: >> >>> Scott Marlowe

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane wrote: > >> Scott Marlowe writes: >> > Seems like something that should be handled by alter doesn't it? >> >> I have

Re: [GENERAL] Multixact members limit exceeded

Hi, On 2017-08-09 10:06:48 +, Peter Hunčár wrote: > We started feeding it several weeks ago and everything went smoothly until > we hit this issue: > > 2017-08-09 05:21:50.946 WIB >DETAIL: This command would create a multixact > with 2 members, but the remaining space is only enough for 0

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane wrote: > Scott Marlowe writes: > > Seems like something that should be handled by alter doesn't it? > > I have some vague memory that we intentionally didn't implement > ALTER EXTENSION OWNER because we were

Re: [GENERAL] Data checksum with pg upgradecluster

On Sun, Aug 6, 2017 at 2:43 PM, Karsten Hilbert wrote: > > Yes. Been there done that. > > Karsten > Thanks Karsten, it worked! Daniel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 12:19 PM, Scott Marlowe wrote: > On Wed, Aug 9, 2017 at 10:10 AM, Tom Lane wrote: > > Melvin Davidson writes: > >> *UPDATE pg_extensionSET extowner = {oid_of_new_owner} WHERE > extowner = > >>

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

Scott Marlowe writes: > Seems like something that should be handled by alter doesn't it? I have some vague memory that we intentionally didn't implement ALTER EXTENSION OWNER because we were unsure what it ought to do about ownership of objects belonging to the

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 10:10 AM, Tom Lane wrote: > Melvin Davidson writes: >> *UPDATE pg_extensionSET extowner = {oid_of_new_owner} WHERE extowner = >> {oid_from_above_statement};* > > Note you'll also have to modify the rows in pg_shdepend that

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

Melvin Davidson writes: > *UPDATE pg_extensionSET extowner = {oid_of_new_owner} WHERE extowner = > {oid_from_above_statement};* Note you'll also have to modify the rows in pg_shdepend that reflect this ownership property. regards, tom lane --

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 11:20 AM, Melvin Davidson wrote: > > On Wed, Aug 9, 2017 at 10:42 AM, Colin 't Hart > wrote: > >> Hi, >> >> Why does >> ALTER EXTENSION name OWNER TO new_owner; >> not exist? >> >> I have a bunch of extensions that were

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 10:42 AM, Colin 't Hart wrote: > Hi, > > Why does > ALTER EXTENSION name OWNER TO new_owner; > not exist? > > I have a bunch of extensions that were installed by a role that I want > to drop. So I thought I would do like I do for other object types: >

[GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

Hi, Why does ALTER EXTENSION name OWNER TO new_owner; not exist? I have a bunch of extensions that were installed by a role that I want to drop. So I thought I would do like I do for other object types: ALTER name OWNER TO new_owner; But that doesn't exist for extensions. I also can't drop the

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

r...@bb-c.de (Rainer J.H. Brandt) writes: > I have found my mistake. I apologize for not telling the whole truth > about my build process. The answer is what it had to be: There was one > step I had forgotten about: My build script stripped all binaries, i.e. > it ran /usr/bin/strip on all of

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

Unfortunately we still see it frequently :( On 9 August 2017 at 14:29, Achilleas Mantzios wrote: > On 09/08/2017 15:27, ADSJ (Adam Sjøgren) wrote: > >> On 2017-06-21 Adam Sjøgren wrote: >> >> Adam Sjøgren wrote: >>>

Re: [GENERAL] invalid byte sequence for encoding

On Wed, 2017-08-09 at 14:21 +0200, basti wrote: > Hello, > i have a webapp convert from ascii to uft8. > > Now I get in postgres > > ERROR:  invalid byte sequence for encoding "UTF8": 0xfc > > Now I try to log all queries with log_statement = 'all'. > All queries are longed expected this one.

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

On 09/08/2017 15:27, ADSJ (Adam Sjøgren) wrote: On 2017-06-21 Adam Sjøgren wrote: Adam Sjøgren wrote: Meanwhile, I can report that I have upgraded from 9.3.14 to 9.3.17 and the errors keep appearing the log. Just to close this, for the record: We

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

On 2017-06-21 Adam Sjøgren wrote: > Adam Sjøgren wrote: >> Meanwhile, I can report that I have upgraded from 9.3.14 to 9.3.17 and >> the errors keep appearing the log. Just to close this, for the record: We haven't seen the errors since 2017-06-30. We

[GENERAL] invalid byte sequence for encoding

Hello, i have a webapp convert from ascii to uft8. Now I get in postgres ERROR: invalid byte sequence for encoding "UTF8": 0xfc Now I try to log all queries with log_statement = 'all'. All queries are longed expected this one. Is there a way to debug this. Best Regards, Basti -- Sent via

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

Hello everybody, I have found my mistake. I apologize for not telling the whole truth about my build process. The answer is what it had to be: There was one step I had forgotten about: My build script stripped all binaries, i.e. it ran /usr/bin/strip on all of them. That's especially

[GENERAL] pg_upgrade fails right after printing "Running in verbose mode"

Hello. Lately we upgraded pg version in our app to 9.6.1 from 9.2. However one of the customers reported, that upgrade process failed on Windows Server 2008 R2. Our installer's logs showed that pg_upgrade returned -1073741515 which translates to 0xC135, which in turn means binary is missing

[GENERAL] Multixact members limit exceeded

Hello We have a fairly large postgresql-9.4 database (currently 70TB and growing to approx 80TB ) running on Centos 7. The HW is 48 core Xeon with 180GB of RAM with data on a enterprise grade SAN storage. We started feeding it several weeks ago and everything went smoothly until we hit this