Re: [GENERAL] Naming conventions for column names

Sachin Kotwal wrote: > 3. Notify or highlight these changes in release notes because this can > break some existing tools and user code. Notifying people when their tools no longer work with a new server is not the problem; they figure that out pretty quickly once they try the new version. The

Re: [GENERAL] Naming conventions for column names

Sachin Kotwal wrote: > I believe these naming conventions will be at two levels: > > 1. Internal code of PostgreSQL , structures getting used internally > 2. SQL/C functions get executed at the time of database initialization to > create default objects and system catalogs. > > > I will see

Re: [GENERAL] xmin increasing within a transaction block?

Luca Ferrari wrote: > Any other idea? None here. Maybe try attaching a debugger, setting a breakpoint on AssignTransactionId, and grab backtraces when it is hit. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [GENERAL] xmin increasing within a transaction block?

Luca Ferrari wrote: > Hi all, > I suspect this has a trivial explaination, but this is what I'm experiencing: > > > CREATE TABLE foo( i int ); > > BEGIN; > * > INSERT INTO foo(i) VALUES( 1 ); > * > INSERT INTO foo(i) VALUES( 2 ); > * > SELECT xmin, cmin, xmax, cmax, i FROM foo; > xmin | cmin |

Re: [GENERAL] REASSIGN OWNED simply doesn't work

David G. Johnston wrote: > ​You could at least fix the documentation bug since this superuser-only > restriction doesn't show up and is in fact contradicted by the sentence > ​"REASSIGN OWNED requires privileges on both the source role(s) and the > target role." The error message that comes back

Re: [GENERAL] Non-overlapping updates blocking each other

Did you try using SELECT FOR NO KEY UPDATE instead of SELECT FOR UPDATE? However: Seamus Abshere wrote: > My current theory is that, since the table is not clustered by id, rows > with very distant ids get stored in the same page, and the whole page is > locked during an update. But we only

Re: [GENERAL] REASSIGN OWNED simply doesn't work

Sam Gendler wrote: > psql 9.6.3 on OS X. > > I'm dealing with a production database in which all db access has been made > by the same user - the db owner, which isn't actually a superuser because > the db runs on amazon RDS - amazon retains the superuser privilege for its > own users and makes

Re: [GENERAL] startup process stuck in recovery

Tom Lane wrote: > Christophe Pettus writes: > > The problem indeed appear to be a very large number of subtransactions, > > each one creating a temp table, inside a single transaction. It's made > > worse by one of those transactions finally getting replayed on the > >

Re: [GENERAL] Writing on replicas?

Ivan Voras wrote: > The reporting code is *almost* read-only, with the major exception being > that it creates temp tables for intermediate results. The main tables are > not written to, just the temp tables. > > Some time ago when I've asked what to do in this situation, the answer was > that

Re: [GENERAL] archive_command fails but works outside of Postgres

Scott Marlowe wrote: > On Fri, Aug 18, 2017 at 12:40 PM, twoflower wrote: > > I changed my archive_command to the following: > > > > archive_command = 'gsutil cp /storage/postgresql/9.6/main/%p > > gs://my_bucket/pg_xlog/' > > 2017-08-18 18:34:25.057 GMT [1436][0]:

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] 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] Manage slot in logical/pglogical replication

more lines of documentation concerning systemd than we do code. regards, tom lane ---------- Date: Thu, 13 Jul 2017 11:55:31 -0400 From: Alvaro Herrera <alvhe...@2ndquadrant.com> To: dpat <denni@gmail.com> Cc: pgsql-general@postgresql.org ubject: Re:

Re: [GENERAL] Manage slot in logical/pglogical replication

dpat wrote: > i have configure a master-replica replication with new pglogical 2.0. > I have to replicate data over MPLS/VPN, so there is a possibility that the > link temporarily interrupts. > I know that you have to be accurately estimated pg_xlog folder. > How can I handle the prolonged

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

Ken Tanzer wrote: > I didn't see any options for dealing with this, though I'm hoping I'm > missing something easy or obvious. Any suggestions or help would be > appreciated. Thanks. pg_dump doesn't promise that its output is compatible with servers older than itself. I'm afraid you're stuck

Re: [GENERAL] effective_io_concurrency increasing

Peter Geoghegan wrote: > On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes wrote: > > Unfortunately, it is only implemented in very narrow circumstances. You > > have to be doing bitmap index scans of many widely scattered rows to make it > > useful. I don't think that this is

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

Dmitry O Litvintsev wrote: > Hi > > Since I have posted this nothing really changed. I am starting to panic > (mildly). ... > vacuum_cost_delay = 50ms Most likely, this value is far too high. You're causing autovacuum to sleep for a very long time with this setting. Hard to say for

Re: [GENERAL] Unsubscription

chuma.of...@ww-cs.de wrote: > Dear Sir/Madam > > > > How would i unsubscribe the general post? I have removed you from pgsql-general. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via

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

Harry Ambrose wrote: > Hi, > > Please find the jar attached (renamed with a .txt extension as I know some > email services deem jars a security issue). > > The jar accepts the following arguments: > > $1 = host > $2 = database > $3 = username > $4 = password > $5 = port > > It returns its

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

ADSJ (Adam Sjøgren) wrote: > Our database has started reporting errors like this: > > 2017-05-31 13:48:10 CEST ERROR: unexpected chunk number 0 (expected 1) for > toast value 14242189 in pg_toast_10919630 Does the problem still reproduce if you revert commit

Re: [GENERAL] Deadlock with single update statement?

Justin Pryzby wrote: > detail|Process 26871 waits for ShareLock on transaction 13693505; blocked by > process 26646. > Process 26646 waits for ShareLock on transaction 13693504; blocked by process > 26871. > Process 26871: SELECT db_column_name,table_name FROM > huawei_m2000_counter_details

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Thomas Kellerer wrote: > Tom Lane schrieb am 26.05.2017 um 20:18: > > > I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL > > > distribution) to upgrade a 9.6 cluster. > > > pg_upgrade --check fails with the following messages: > > > > > could not load library

Re: [GENERAL] Top posting....

George Neuner wrote: > On Thu, 11 May 2017 13:43:52 -0400, Tom Lane > wrote: > >Personally, when I've scrolled down through a couple of pages of quoted > >and re-quoted text and see no sign of it ending any time soon, I tend > >to stop reading. > > I agree 100%. But

[GENERAL] [p...@vivation.com: Python versus Other Languages using PostgreSQL]

--- Begin Message --- Hello, I noticed that most of the largest web platforms that use PostgreSQL as their primary database, also use Python as their primary back-end language. Yet, according to every benchmark I could find over the last couple of years, back-end languages like PHP, HHVM, and

[GENERAL] [plnc...@gmail.com: Can I safe my Data?]

-- Álvaro Herrera Developer, https://www.PostgreSQL.org/ "The problem with the facetime model is not just that it's demoralizing, but that the people pretending to work interrupt the ones actually working." (Paul

Re: [GENERAL] cluster on brin indexes?

Samuel Williams wrote: > I see this, but no follow up: > > https://www.postgresql.org/message-id/CAEepm%3D2LUCLZ2J4cwPv5DisHqD9BE_AXnqHGqf0Tj-cvtiqVcQ%40mail.gmail.com > > So, is it possible or not? The general idea seems like it should be doable, but I haven't looked at it in detail.

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Tom Lane wrote: > Also you might want to look into how you got into a situation where > you have an anti-wraparound vacuum that's taking so long to run. If there are ALTERs running all the time, regular (non-anti-wraparound) vacuums would be canceled and never get a chance to run. Eventually,

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Scott Marlowe wrote: > Then a ddl gets in line. It has to wait on the vacuum, and the vacuum, > set to run super slow. And everybody waits. On vacuum. Note that this is normally not seen, because autovacuum cancels itself when somebody is blocked behind it -- until the table reaches the

Re: Ynt:[GENERAL] postgresql : could not serialize access due to read/write dependencies among transactions

Neslisah Demirci wrote: > First I started conversation between two person ; > > > sql: 'INSERT INTO "X" > ("id","customer","createdAt","updatedAt","CompanyId") VALUES > (DEFAULT,\'905322653555\',\'2017-01-17 19:13:37.751 +00:00\',\'2017-01-17 > 19:13:37.751 +00:00\',\'1\') RETURNING *;'

Re: [GENERAL] some amazing stuff

Edmundo Robles wrote: > Please, administrator mark this like spam. Yes, I removed the offending address on sight. We do not remove emails from the archive, though. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training &

Re: [GENERAL] checkpoint clarifications needed

Tom DalPozzo wrote: > Hi, > so let's suppose that the WAL is: > LSN 10: start transaction 123 > LSN 11: update tuple 100 >checkpoint position here (not a record but just for understanding) > LSN 12: update tuple 100 > LSN 13: update tuple 100 > LSN 14: checkpoint record ( postion=11) > LSN

Re: [GENERAL] checkpoint clarifications needed

Tom DalPozzo wrote: > 2) I see that a checkpoint position can be right in the middle of a group > of records related to a transaction (in the example, transaction id 10684). > So a checkpoint position is NOT a consistency state point, right? > 4) If I'm right at 2) then, between the checkpoint

Re: [GENERAL] postgres pg_bulkload c filter function in c programming

rajmhn wrote: > But, how this can be accomplished when it have 100's of columns from source. > Need to apply transformations only for few columns as mentioned above. Did you try pgloader? See http://pgloader.io/ -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL

Re: [GENERAL] pg_repack and Postgres versions > 9.4

Gaetano Mendola wrote: > I wonder why this is not a VACUUM option. Because nobody has written the patch. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list

Re: [GENERAL] FOR UPDATE

said assemlal wrote: > Hello, > > PG: 9.4 > CentOS 6 > > I am writing functions to lock results. > > Let's take an example: > > CREATE OR REPLACE FUNCTION usp_locking_my_result($1 VARCHAR(50), $2 > VARCHAR(50)) > RETURNS TEXT AS $$ > SELECT value > FROM my_table > WHERE field1 = $1 AND field2

Re: [GENERAL] pg_class (system) table increasing size.

dhaval jaiswal wrote: > Adding to above. Below are the outputs. I just meant that you need to ANALYZE all these system catalogs so that autovacuum can pick up vacuuming them to remove dead tuples. Do not leave autovacuum turned off anymore. -- Álvaro Herrera

Re: [GENERAL] pg_class (system) table increasing size.

dhaval jaiswal wrote: > I did check and found it was the bloated size of pg_class which was slowing > down the performance. > > It got fixed by adding in routine maintenance task. Things are fine now. Good to know. > However, I want to know how come pg_class (system table) get >

Re: [GENERAL] pg_class (system) table increasing size.

dhaval jaiswal wrote: > select * from pg_stat_sys_tables where relname = 'pg_class'; > > -[ RECORD 1 ]---+--- > relid | 1259 > schemaname | pg_catalog > relname | pg_class > seq_scan| 1838 > seq_tup_read| 3177416 > idx_scan

Re: [GENERAL] MultiXact member wraparound protections are disabled

avi Singh wrote: > Your right we looked back in our old logs and we do see the messages there > as well. Still what I'm not getting is since we restarted the database > after SAN FC re-cable effort auto-vacuum is running on all the threads > continuous. I have never seen auto-vacuum using all the

Re: [GENERAL] MultiXact member wraparound protections are disabled

AnandKumar, Karthik wrote: > Thanks. We started seeing this error right after a SAN FC re-cable effort - > so yes, that would make sense. > We’ll do a little more digging to see if the could have gotten removed. > If that’s an older file that we have in our filesystem backups, is it safe to

Re: [GENERAL] MultiXact member wraparound protections are disabled

AnandKumar, Karthik wrote: > root@site-db01a:/var/lib/pgsql/cmates/data # ls pg_multixact/members > 0001 0002 0003 0004 0005 0006 0007 0008 0009 000A 000B 000C > 000D 000E 000F 0010 0011 0012 0013 0014 0015 0016 0017 0018 0019 > 001A 001B >

Re: [GENERAL] MultiXact member wraparound protections are disabled

AnandKumar, Karthik wrote: > Hi, > > We run postgres 9.4.5. > > Starting this morning, we started seeing messages like the below: > Oct 12 14:07:15 site-db01a postgres[11253]: [106430-1] app=,user=,db=,ip=LOG: > MultiXact member wraparound protections are disabled because oldest >

Re: [GENERAL] BRIN indexes and ORDER BY

Darren Lafreniere wrote: > "In addition to simply finding the rows to be returned by a query, an index > may be able to deliver them in a specific sorted order. This allows a > query's ORDER BY specification to be honored without a separate sorting > step. Of the index types currently supported

Re: [HACKERS] [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Tom Lane wrote: > Thomas Kellerer writes: > > for some reason pg_upgrade failed on Windows 10 for me, with an error > > message that one specifc _vm file couldn't be copied. > > Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new > code for 9.6 and

Re: [GENERAL] PgSQL versions supported on ubuntu 16 and debian 8

John R Pierce wrote: > On 9/19/2016 4:18 AM, MEERA wrote: > > > >Could someone please provide us this information? > > was answered nearly a week ago. Hmm, yeah, but you didn't CC the OP, and she is not subscribed. Meera: you can see the answer here:

Re: [GENERAL] Any work on better parallelization of pg_dump?

Jehan-Guillaume de Rorthais wrote: > > Yeah. I recall there being some stupid limitation in ALTER TABLE .. ADD > > CONSTRAINT USING INDEX to create a primary key from a previously > > existing unique index, which would be very good to fix (I don't recall > > what it was, but it was something

Re: [GENERAL] Any work on better parallelization of pg_dump?

Jehan-Guillaume de Rorthais wrote: > On Mon, 29 Aug 2016 13:38:03 +0200 > hubert depesz lubaczewski wrote: > > > Hi, > > we have rather uncommon case - DB with ~ 50GB of data, but this is > > spread across ~ 8 tables. > > > > Running pg_dump -Fd -jxx dumps in parallel,

Re: [GENERAL] Determining table change in an event trigger

Jonathan Rogers wrote: > I am trying to use an event trigger to do something when a column > changes. I can declare an event trigger "ON sql_drop WHEN TAG IN ('ALTER > TABLE')" to get dropped columns. However, I can't figure out any good > way to determine when a column has been added or altered.

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

Xtra Coder wrote: > May you have the link to 'DO'-discussion to take a look on it? I was trying > to google for something like that, but word 'DO' is too generic to bring > useful results :( Probably this is one: https://www.postgresql.org/message-id/51b624c6@2ndquadrant.com -- Álvaro

Re: [GENERAL] Critical failure of standby

James Sewell wrote: > 2016-08-12 04:43:53 GMT [23614]: [5-1] user=,db=,client= (0:0)LOG: > consistent recovery state reached at 3/8811DFF0 > 2016-08-12 04:43:53 GMT [23614]: [6-1] user=,db=,client= (0:XX000)FATAL: > invalid memory alloc request size 3445219328 > 2016-08-12 04:43:53 GMT

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

support-tiger wrote: > It would be great if the Ruby and Node drivers can be brought under the > Postgres team umbrella and make them as reliable and clearly documented as > the Python or jdbc drivers. Sadly, the PostgreSQL development group does not have the manpower to maintain or document

Re: [GENERAL] pg_archivecleanup standalone bash script

Patrick B wrote: > This has been resolved. How? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Uber migrated from Postgres to MySQL

Patrick B wrote: > > > > I think it's safe to say that that has absolutely nothing to do > > with the size being 3TB. They symptoms you report are a little > > thin to diagnose the actual cause. > > might be... we're using SATA disks... and that's a big problem. But still.. > the size of the DB

Re: [GENERAL] question on parsing postgres sql queries

Kevin Grittner wrote: > On the other hand, try connecting to a database with > psql and typing: > > \h create index > > ... (or any other command name). The help you get there is fished > out of the docs. BTW I noticed a few days ago that we don't have a "where BLAH can be one of" section for

Re: [GENERAL] pasting a lot of commands to psql

Tom Lane wrote: > Francisco Olarte <fola...@peoplecall.com> writes: > > On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> > > wrote: > >> I've wished sometimes for a "\set READLINE off" psql metacommand for > >> this k

Re: [GENERAL] pasting a lot of commands to psql

Merlin Moncure wrote: > Might be a 'xterm vs Mate Terminal' problem. Using raw xterm > performance is great. I like some of the creature comforts of the > mate terminal though. Heh. I've been using lxterminal for a couple of weeks now and I find some of these comfort features rather

Re: [GENERAL] pasting a lot of commands to psql

Merlin Moncure wrote: > On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> > wrote: > > Tom Lane wrote: > > > >> You might have better luck with "psql -n", or maybe not. > > > > I've wished sometimes for a "\s

Re: [GENERAL] pasting a lot of commands to psql

Tom Lane wrote: > You might have better luck with "psql -n", or maybe not. I've wished sometimes for a "\set READLINE off" psql metacommand for this kind of thing. It's pretty annoying when the text being pasted contains tabs and readline uses to do completion. -- Álvaro Herrera

Re: [GENERAL] Fastest memmove in C

FarjadFarid(ChkNet) wrote: > Excellent research and could be well worth checking out. As it could > improve the performance of postgresql engine. 0) We certainly do a lot of memory copying. 1) this work is under the "Code Project Open License" which doesn't look compatible with our Postgres

Re: [GENERAL] Is it possible to use an EVENT TRIGGER to validate a TRIGGER?

Luís Eduardo Oliveira Lizardo wrote: > Hi, > > Is it possible to use an EVENT TRIGGER to validate a TRIGGER definition? > > What I want is to guarantee that the trigger is fired AFTER a STATEMENT, on > INSERT or UPDATE but not on DELETE, like the following example: What you can do with a C

Re: [GENERAL] pg_dump fundenental question

J. Cassidy wrote: > As stated in the original email, I want to know whether compression > (whatever level) is on by default (or not) -  if I supply NO extra > switches/options. I have read the documentation and it is unclear in > this respect. I am a Mainframer and perhaps have a different world

Re: [GENERAL] Postgres Dropped DB have recovered files how to restore

John R Pierce wrote: > On 6/6/2016 4:09 PM, Alvaro Herrera wrote: > >I have no idea about Windows filesystems but you may be able to > >"undelete" the files, as long as you don't touch the partition for > >anything else; search the web for "undelete ntfs&q

Re: [GENERAL] Postgres Dropped DB have recovered files how to restore

lifetronics wrote: > This morning I accidently deleted my database for my OpenERP accounting. I > did not have a good backup system setup so I was unable to do a system > restore. i did manage to recover the files the drop command removed but I > dont know how to get the DB back into postgres? Can

Re: [GENERAL] Log Shipping

Joseph Kregloh wrote: > It is my understanding that if PostgeSQL has log shipping enabled, if for > whatever reason it cannot ship the file the master server will hold it. But > for how long? Forever (which means it dies because of running out of space in the partition containing pg_xlog). >

Re: [GENERAL] ERROR: MultiXactId xxx has not been created yet

Christophe Pettus wrote: > We have a database (PostgreSQL 9.3.10) which is reporting this error on a > TOAST table on a VACUUM. Is there a canonical way of repairing this? The > table is *huge*, so a VACUUM FULL or pg_dump / pg_restore is probably not > going to work. I don't think toast

Re: [GENERAL] PostgreSQL with BDR - PANIC: could not create replication identifier checkpoint

Cameron Smith wrote: > t:2016-05-19 01:14:51.668 UTC d= p=144 a=PANIC: could not create replication > identifier checkpoint "pg_logical/checkpoints/8-F3923F98.ckpt.tmp": Invalid > argument This line corresponds to the following code in BDR's 9.4.4

Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded

Steve Kehlet wrote: > On Mon, May 16, 2016 at 6:18 PM Alvaro Herrera <alvhe...@2ndquadrant.com> > wrote: > > > Not really. Your best bet is to reduce the > > autovacuum_multixact_freeze_min_age limit, so that vacuums are able to > > get rid of

Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded

Steve Kehlet wrote: > Now it's just about preventing this. Our best guess at this point is the > autovacuums aren't working fast enough. Sure enough this instance has our > old values for: > autovacuum_vacuum_cost_delay: 20ms > autovacuum_vacuum_cost_limit: 200 > > We've since started using: >

Re: [GENERAL] WAL files not being recycled

Scott Moynes wrote: > I have a PostgreSQL server that is not recycling WAL files. Log files are > continually created and no old log files are ever removed. > > Running PostgreSQL v 9.4.8 with archive settings: > > archive_mode = on > archive_command = /bin/true > > Checkpoint logging

Re: [GENERAL] EINTR causes panic (data dir on btrfs)

Gustavo Lopes wrote: > Every few weeks, I'm getting a error like this: > > > 2015-02-11 15:31:00 CET PANIC: could not write to log file > > 00010007007D at offset 1335296, length 8192: Interrupted system > > call > > 2015-02-11 15:31:00 CET STATEMENT: COMMIT > > 2015-02-11 15:31:17

Re: [GENERAL] Possible causes for "tuple concurrently updated" error

Jacob Scott wrote: > Arg, should have included this in my initial email :-( > > 9.3.11 OK. So what are the updates doing? Are there any FKs involved? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent

Re: [GENERAL] Possible causes for "tuple concurrently updated" error

On Wed, May 04, 2016 at 11:52:47PM -0700, Jacob Scott wrote: > Hi, > > I'm seeing a "tuple concurrently updated" error thrown while executing > UPDATE statements. I've attempted to diligently review previous threads on > this error (e.g., >

Re: [GENERAL] Vacuum of large tables causing replication delays to hot standby

Tom Lane wrote: > Jeff Mcdowell writes: > > 95% of the time, the delay is only microseconds. But we have discovered > > that whenever the master does an auto vacuum of a large table, the > > transaction replay delay can climb is high as 1 hour. These delays don�t

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

Tom Lane wrote: > Melvin Davidson writes: > > However, Customer Feedback ( > > https://postgresql.uservoice.com/forums/21853-general > > ) does seem to > > indicate it and give positive results. > > I had never heard

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

Bráulio Bhavamitra wrote: > Hi all, > > I'm finally having performance issues with PostgreSQL when doing big > analytics queries over almost the entire database of more than 100gb of > data. > > And what I keep reading all over the web is many databases switching to > columnar store (RedShift,

Re: [GENERAL] Initdb --data-checksums by default

Alex Ignatov wrote: > Hello everyone! > Today in Big Data epoch silent data corruption becoming more and more issue > to afraid of. With uncorrectable read error rate ~ 10^-15 on multiterabyte > disk bit rot is the real issue. > I think that today checksumming data must be mandatory set by

Re: [GENERAL] 9.5 new features

I just noticed this old thread. Thomas Kellerer wrote: > > 62.1. Introduction > > ... > > "A block range is a group of pages that are physically adjacent in the > > table; for each block range, some summary info is stored by the index." > > > > From the above, may I presume that it is best to

Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote: > Hello, > If I get you right: > Latest checkpoint's NextMultiXactId: 2075246000 > Latest checkpoint's oldestMultiXid: 2019511697 > Number of members files: 10820 > Size pg_multixact/members/ (bytes) (2.7Gb): 2887696384 > Pages in file:32 >

Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote: > Yes, VACUUM helps to solve the problem and the WARNING gone away. > But, the problem is that the VACUUM for the entire database (2.4T) takes over > 7 hours, and it has to run every 15-20 hours (about 300 millions > transactions), otherwise: > ERROR: multixact "members"

Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote: > There is nothing: > select * from pg_prepared_xacts; > transaction | gid | prepared | owner | database > -+-+--+---+-- > (0 rows) > It is also noticed that a lot of files in a directory > main/pg_multixact/members/, now - 69640. Can

Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote: > Thanks for your reply. > Yes, the first thing I looked at the statistics from pg_stat_activity. > But I have a transaction is not more than 60 seconds and the condition 'idle > in transaction' lasts only a few seconds. Maybe you have a prepared transaction? See select

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

Tom Lane wrote: > "David G. Johnston" writes: > > On Monday, March 21, 2016, Tom Lane wrote: > >> What about just discarding the old format entirely, and printing one of > >> these two things: > >> > >> Timestamp (every Ns) > >> > >> User Given

[GENERAL] Re: [HACKERS] Request - repeat value of \pset title during \watch interations

David G. Johnston wrote: > Tom doesn't care enough to veto and you don't really care... > > I'll admit it's awkward because it's abbreviated but if someone enters > \watch 5 and then sees (5s) in the title I think they can put two and two > together. > > If the watched query takes a long to

Re: [GENERAL] enum bug

Joshua D. Drake wrote: > On 03/14/2016 08:48 AM, Alvaro Herrera wrote: > >(*) Yes, I'm being a bit sarcastic here, sorry about that. I actually > >learned quite a bit of database design and related topics by translating > >the "General Bits" column she used to w

Re: [GENERAL] enum bug

Elein wrote: Hi Elein, >* When an insert into an enum column fails give the person a hint as to > valid values > -- Lousy message. Show enum list. > insert into badinfo values ('green'); > ERROR: invalid input value for enum rainbow: "green" > LINE 1: insert into badinfo values

Re: [GENERAL] enum bug

Melvin Davidson wrote: > Enums are evil! > http://www.lornajane.net/posts/2010/is-enum-evil ??? This post is about MySQL's enums, which aren't really related to Postgres enums: "In order to change the allowed values of an enum column, we need to issue an alter table statement

Re: Email address VERP problems (was RE: [GENERAL] Does a call to a language handler provide a context/session, and somewhere to keep session data?

David Bennett wrote: > > ow...@postgresql.org] On Behalf Of Alvaro Herrera > > > On that subject. I noticed that Outlook seems to add the "return > > path" > > addresses (sometimes called bounce address or envelope sender) to the > > CC header, whi

Re: [GENERAL] Does a call to a language handler provide a context/session, and somewhere to keep session data?

da...@andl.org wrote: > > ow...@postgresql.org] On Behalf Of Adrian Klaver > > > On that subject. I have followed this thread, but only sort of as the > > quoting your email client is doing tends to obscure the flow. I know > > for me that is making it hard to follow your ideas. > > > > Is that

Re: [GENERAL] query reboot pgsql 9.5.1

David G. Johnston wrote: > ​To be more clear, you need to run a query that will complete in our > lifetime (and without an OOM error) with all three of ANALYZE, BUFFERS, and > TIMING specified for the EXPLAIN. I think the problem is pretty clear. The plan is sensible yet the result doesn't seem

Re: [GENERAL] query reboot pgsql 9.5.1

Felipe de Jesús Molina Bravo wrote: > pba=# \i tablas.sql > DROP TABLE > SELECT 120130 > CREATE INDEX > CREATE INDEX > CREATE INDEX > DROP TABLE > SELECT 91932 > CREATE INDEX > CREATE INDEX > pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb > a LEFT join _gc_cat b on (

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

Derek Elder wrote: > From what I had read, this setting should be on by default. When I checked > our other servers I see that track_counts is on and the autovacuum process > is working correctly on them. Indeed we don't even have the setting > explicitly listed in our postgresql.conf on these

Re: [GENERAL] commit time in logical decoding

Weiping Qu wrote: > Hello Artur, > > Thank you for your reply. > Should it work in a stable version like Postgresql 9.4, since it's enough > for me and I don't care whether it's 9.6 or 9.5. > Nevertheless I will try it using 9.4. Yes, it was introduced by a commit that's in 9.5 and up only, so

Re: [GENERAL] bloated postgres data folder, clean up

Rémi Cura wrote: > Hey dear list, > after a fex years of experiments and crash, > I ended up with a grossly bloated postgres folder. > I had about 8 Go of useless files. Would you add a new page to the wiki with this? https://wiki.postgresql.org/wiki/Category:Administrative_Snippets -- Álvaro

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

Joshua D. Drake wrote: > On 02/29/2016 05:31 AM, Stephen Frost wrote: > >Realistically, ANALYZE is a background/maintenance task that autovacuum > >should be handling for you. > > Incorrect. That would be autoanalyze and although they are similar they are > not the same. ANALYZE is used for a

Re: [GENERAL] FreeBSD x86 and x86_64

MEERA wrote: > Hi all, > > Any information regarding PostgreSQL support on FreeBSD platform? You can see the list of supported platforms here: http://buildfarm.postgresql.org/cgi-bin/show_status.pl We seem to have FreeBSD 9.0 on gcc 4.2 and FreeBSD 10 on clang, both on x86_64. If you want to

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

Tom Lane wrote: > Alvaro Herrera <alvhe...@2ndquadrant.com> writes: > > Mart�n Marqu�s wrote: > >> This really gives little use for recovery_target_xid. :( > > > Hmm, you can still use pg_xlogdump to figure it out from the actual WAL, > > which has th

Re: [GENERAL] Test CMake build

Teodor Sigaev wrote: > Cmake 2 times faster, that is good, but I don't understand why. Which > optimization level does cmake buld use by default? Which compiler does it > take? It's not obvious, because cmake build hides actual compiler command > line. Hm, I don't think having the compile/link

Re: [GENERAL] Test CMake build

Teodor Sigaev wrote: > >Hm, I don't think having the compile/link lines be hidden up is > >acceptable. Many times we need to debug some compile problem, and the > >output is mandatory. > > +1 > > Although it could be fixed by > VERBOSE=1 make Verbose needs to be the default. Having a QUIET

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

Martín Marqués wrote: > El 10/02/16 a las 21:46, Tom Lane escribió: > > We could maybe fix this by redefining %x as "the current or most recent > > xid", so that it'd still be valid for messages issued post-commit. > > But I'm afraid that would add about as many bad behaviors as it would > >

Re: [GENERAL] pg_multixact issues

Thomas Munro wrote: > 4. You could look into whether all those multixacts with many member > are really expected. (Large numbers of concurrent FK checks or > explicit share locks on the same rows perhaps? A good proportion of > this happened on one day this week I see.) I think maybe this

  1   2   3   4   5   6   7   8   9   10   >