Re: [GENERAL] Serializable isolation -- are predicate locks still held across all databases?

ng on the list, where it will compete with other possible enhancements on a cost/benefit basis. Thanks for raising the issue! -- Kevin Grittner VMware vCenter Server https://www.vmware.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subs

Re: [GENERAL] Serializable isolation -- are predicate locks still held across all databases?

at this point I'm inclined to recommend the workaround of using a separate cluster; but if we get other reports it might be worth adding to the list of enhancements that SSI could use. Thanks! -- Kevin Grittner VMware vCenter Server https://www.vmware.com/ -- 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] update error with serializable

On Fri, Jan 20, 2017 at 11:27 AM, Rob Sargent wrote: > On 01/20/2017 10:05 AM, Kevin Grittner wrote: >> https://www.postgresql.org/message-id/flat/d8joa0eh9yw@dalvik.ping.uio.no#d8joa0eh9yw@dalvik.ping.uio.no > Configurable or dynamic? Wouldn't something related to tup

Re: [GENERAL] update error with serializable

ed to make that configurable. https://www.postgresql.org/message-id/flat/d8joa0eh9yw@dalvik.ping.uio.no#d8joa0eh9yw@dalvik.ping.uio.no If you are able to build from source, you might want to test the efficacy of the patch for your situation. -- Kevin Grittner EDB: http://www.enterprisedb.com The E

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

epeatable read: https://wiki.postgresql.org/wiki/SSI And of course, if you haven't already read the fine manual on the topic: https://www.postgresql.org/docs/current/static/mvcc.html -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (p

Re: [GENERAL] raise notice question

by default) if we get stored procedures which can return a complex result stream like TDS does. The series of literals and results sets of different types is something which can be quite useful to DBAs. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- S

Re: [GENERAL] Re: Are new connection/security features in order, given connection pooling?

onnection pooler connect to the server with a login with rights to do the appropriate SET ROLE (preferably without requiring superuser rights). -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@po

Re: [GENERAL] Materialized view vs. view

rg/wiki/Jargon : "A main driving force in the creation of technical jargon is precision and efficiency of communication when a discussion must easily range from general themes to specific, finely differentiated details without circumlocution." -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] Materialized view vs. view

but to try to keep terminology clear, to facilitate efficient communication. There are some terms we have been unable to avoid using with different meanings in different contexts (e.g., "serialization"); that's unfortunate, but hard to avoid. I want to keep it to the minimum neces

Re: [GENERAL] Materialized view vs. view

ed text suggests, a materialized view is essentially a cache of the results of the specified query. While, in rare cases, this may be captured to provide the query results as of some particular moment in time, the overwhelming reason for creating a materialized view is to improve performance over a non-m

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

hint bits may be another part of it. The first access to each page after the bulk load would require some extra work for visibility checking and would cause a page rewrite for the hint bits. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-gen

Re: [GENERAL] Row value expression much faster than equivalent OR clauses

tamp > '2016-12-19T20:34:22.315Z' OR (e.sequenceNumber >= 0 AND (e.sequenceNumber > 0 OR (e.aggregateIdentifier > 'dev:642e1953-2562-4768-80d9-0c3af9b0ff84') -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: [GENERAL] Love Your Database project — Thoughts on effectively handling constraints?

On Fri, Dec 16, 2016 at 3:54 PM, Guyren Howe wrote: > What I need to do is turn this into something similar to the equivalent > Rails-side constraint failure, which is a nicely formatted error message on > the model object. Can you show what the text in such a message looks like?

Re: [GENERAL] does postgres log the create/refresh of a materialized view anywhere?

On Tue, Dec 13, 2016 at 7:37 PM, Melvin Davidson wrote: > On Tue, Dec 13, 2016 at 7:36 PM, Kevin Grittner wrote: >> On Tue, Dec 13, 2016 at 5:57 PM, Jonathan Vanasco wrote: >> >>> Is there a way to find out when a materialized view was >>> created/refreshed?

Re: [GENERAL] does postgres log the create/refresh of a materialized view anywhere?

t currently tracked in the system catalogs. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] FreeBSD 10 => 11: Dump and reload your PostgreSQL database unless you like it broken

where most DBAs understood the point of being able to set a client_encoding that is different from the server_encoding, I think I would need to pop the cork on some champagne. Hm. Maybe a topic for a blog post -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] Methods to quiesce PostgreSQL DB without configuring log archival

last snapshot is completes) to take a PITR-style recovery. Be sure to follow all the rules for PITR-style backup and recovery, like deleting the postmaster.pid file and all files under pg_xlog before starting the recovery. And of course, do NOT delete the backup_label file created by pg_start_

Re: [GENERAL] max_connections limit violation not showing in pg_stat_activity

On Wed, Nov 23, 2016 at 4:43 AM, Charles Clavadetscher wrote: > From: Kevin Grittner [mailto:kgri...@gmail.com] >> Is it possible to upgrade? You are missing over a year's worth >> of fixes for serious bugs and security vulnerabilities. > > Yes. Actually it is fo

Re: [GENERAL] max_connections limit violation not showing in pg_stat_activity

ions on the configuration > of work_mem (if I remember well) Each connection can allocate one work_mem allocation per node which requires a sort, hash, CTE, etc. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pg

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

On Mon, Nov 21, 2016 at 11:34 AM, dhaval jaiswal wrote: > Due to business impact auto vacuum is off. You have now discovered some of the the negative business impact of turning it off. If you leave it off, much worse is likely to follow. -- Kevin Grittner EDB: http://www.enterprisedb.com

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

uld probably need to raise autovacuum_vacuum_cost limit. And if autovacuum somehow got turned *off* you are likely to have all kinds of problems with bloat, and may need to schedule some down time to get it cleaned up. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL C

Re: [GENERAL] Locking question

te) and cursors (supported by most database products, including the three you mention). -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgres

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

On Wed, Oct 26, 2016 at 3:20 PM, Peter Geoghegan wrote: > On Mon, Oct 24, 2016 at 8:07 AM, Kevin Grittner wrote: >> My initial thought is that since reducing the false positive rate >> would only help when there was a high rate of conflicts under the >> existing patch,

Re: [GENERAL] Locking question

application code), which will cause a write conflict if two transactions try to update the same total at the same time, or by using explicit locking controlled from the application. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mai

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

lse positive serialization failures is a worthy goal, but it's gotta make sense from a cost/benefit perspective. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] out-of-order XID insertion in KnownAssignedXids

p_label file things look exactly like a crash recovery, which is why it just goes to the last usable checkpoint; that's the correct behavior for crash recovery. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] out-of-order XID insertion in KnownAssignedXids

On Thu, Oct 20, 2016 at 8:21 AM, wrote: > Version : 9.2.13 You are missing over a year's worth of bug fixes. https://www.postgresql.org/support/versioning/ > - remove a file called backup_label http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.htm

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

On Thu, Oct 13, 2016 at 5:26 PM, Thomas Munro wrote: > On Fri, Oct 14, 2016 at 2:04 AM, Kevin Grittner wrote: >> Where do you see a problem if REPEATABLE READ handles INSERT/ON >> CONFLICT without error? > I think the ON CONFLICT > equivalent might be something like

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

On Thu, Oct 13, 2016 at 3:16 PM, Kevin Grittner wrote: > On Thu, Oct 13, 2016 at 2:16 PM, Peter Geoghegan wrote: >> We must still determine if a fix along the lines of the one proposed >> by Thomas is basically acceptable (that is, that it does not clearly >> break any

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

On Thu, Oct 13, 2016 at 2:16 PM, Peter Geoghegan wrote: > On Thu, Oct 13, 2016 at 6:19 AM, Kevin Grittner wrote: >> Every situation that generates a false positive hurts performance; >> we went to great lengths to minimize those cases. >> To generate a >> serial

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

set had successfully committed, and that it was a transaction which had done writes. To generate a serialization failure on a single transaction has to be considered a bug, because a retry *CAN NOT SUCCEED*! This is likely to break many frameworks designed to work with serializ

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

On Wed, Oct 12, 2016 at 8:06 PM, Thomas Munro wrote: > On Thu, Oct 13, 2016 at 10:06 AM, Kevin Grittner wrote: >> On Wed, Oct 12, 2016 at 3:02 PM, Peter Geoghegan wrote: >> >>> I agree that the multi-value case is a bug. >> >>> I think that it should

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

On Wed, Oct 12, 2016 at 5:21 PM, Peter Geoghegan wrote: > On Wed, Oct 12, 2016 at 2:06 PM, Kevin Grittner wrote: >> If the "proper" fix is impossible (or just too freaking ugly) we >> might fall back on the fix Thomas suggested, but I would like to >> take advan

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

On Wed, Oct 12, 2016 at 3:55 PM, Peter Geoghegan wrote: > On Wed, Oct 12, 2016 at 1:41 PM, Kevin Grittner wrote: >> Aren't these two completely separate and independent bugs? > > Technically they are, but they are both isolated to the same small > function. Surely it'

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

If the "proper" fix is impossible (or just too freaking ugly) we might fall back on the fix Thomas suggested, but I would like to take advantage of the "special properties" of the INSERT/ON CONFLICT DO NOTHING code to avoid false positives where we can. -- Kevin Grittner EDB

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

on-failure strategies will be befuddle by this >> doomed transaction. And as you and Vitaly have said, there is >> literally no concurrent update. > > I think that you have the right idea, but we still need to fix that > buffer lock bug I mentioned... Aren't these two completely

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

On Wed, Oct 12, 2016 at 10:06 AM, Kevin Grittner wrote: > The test in ExecCheckHeapTupleVisible() seems wrong to me. It's > not immediately obvious what the proper fix is. To identify what cases ExecCheckHeapTupleVisible() was meant to cover I commented out the body of the func

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

onstraint" and doesn't run to > "ExecCheckHeapTupleVisible" check. > The "ExecInsert" handles constraint checks but not later checks like > ExecCheckHeapTupleVisible. The test in ExecCheckHeapTupleVisible() seems wrong to me. It's not immediately obvious what the proper fix is. Peter, do you have any ideas on this? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

On Wed, Oct 12, 2016 at 2:50 AM, Albe Laurenz wrote: > Kevin Grittner wrote: >> I don't see that on development HEAD. What version are you >> running? What is your setting for default_transaction_isolation? > > The subject says SERIALIZABLE, and I can see it on my

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

ess due to concurrent update > =# END; > ROLLBACK I don't see that on development HEAD. What version are you running? What is your setting for default_transaction_isolation? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-gen

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

reate table ddl_test(id int); > ERROR: duplicate key value violates unique constraint > "pg_type_typname_nsp_index" > DETAIL: Key (typname, typnamespace)=(ddl_test, 2200) already exists. > test=# commit ; > ROLLBACK I recommend using a transactional advisory lock to seriali

Re: [GENERAL] Time travel?

ble without warning... > How is it possible for the WAL file to be accessed BEFORE it was > created? Perhaps renaming it counts as "creation" without affecting access time. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-

Re: [GENERAL] isnull() function in pgAdmin3

LL does not evaluate to TRUE. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Update two tables returning id from insert CTE Query

ationship intact all the way through -- perhaps by adding name_last to table_1. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [GENERAL] Update two tables returning id from insert CTE Query

lue of 124312. Effectively the database is complaining that it can only store one value, not a set of values. I can only guess at what you might be intending to ask the database to do. Can you explain what you are trying to do? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] isnull() function in pgAdmin3

27;))) then 'RPG_INV' when ((("s"."Funding_Date") is null or ("s"."Funding_Date" <> '')) and (("s"."Actual_Close_Date" = '

Re: [GENERAL] Incrementally refreshed materialized view

alternative for how to go about that, although operating a row at a time you probably won't approach the speed of statement-level set logic for statements that affect very many rows. :-( -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsq

Re: [GENERAL] Incrementally refreshed materialized view

= delta.dst) UNION ALL SELECT after.src, delta.dst, 1 * delta."count(t)" FROM hop2 after JOIN "Δ(link)" delta ON (delta.src = after.dst) ) x(src, dst, "count(t)") GROUP BY src, dst HAVING sum("count(t

Re: [GENERAL] Incrementally refreshed materialized view

ype=pdf The first step in using either of those techniques (counting or DRed) is to capture a delta relation to feed into the relational algebra used by these techniques. As a first step in that direction I have been floating a patch to implement the SQL-standard "transition tables" f

Re: [GENERAL] COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID

o zero on customer insert, and which you increment to get values for the second key column in the contact table. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Understanding Postgres Memory Usage

s between PSS and USS == total shared memory.) RSS has the usual meaning. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

er can stand behind it and feel as good as possible about circumstances should that happen. You might want to keep a copy of the email or memo in which you point this out, in case anyone's memory gets foggy during such a crisis. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enter

Re: [GENERAL] Serializable read and blocking

e even with SET TRANSACTION SERIALIZEABLE mode. > I am specifically interested in the 3rd condition (- Writers do not > block readers.) Yes. https://wiki.postgresql.org/wiki/SSI http://vldb.org/pvldb/vol5/p1850_danrkports_vldb2012.pdf -- Kevin Grittner EDB: http://www.enterprisedb.com

Re: [GENERAL] Column order in multi column primary key

fortunate trigger for rehashing old flame-wars. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Materialized view auto refresh

s more immediate issues for particular end users; but I expect to get back to it Real Soon Now. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription

Re: [GENERAL] Column order in multi column primary key

large scale, by modifying one column of one row. That is, of course, a double-edged sword -- in discussing design alternatives with the CPAs who were going to be auditing financial data stored in a database, they didn't tend to see that as nearly as much of a plus as some programmers do. -- K

Re: [GENERAL] Detecting if current transaction is modifying the database

D and just returned NULL if none has yet been assigned. I'm not sure what the best name would be for such a function when we already have a function called txid_current() which does something different from that. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] Allowing multiple versions of PG under NetBSD

kes a restart after a crash less problematic and it is generally better from a security standpoint, so you might want to look for a way to allow it. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postg

Re: [GENERAL] Uber migrated from Postgres to MySQL

tabase objects, that might be a hard one to overcome, but it might be something with an easy solution in the pg_upgrade options or server configuration. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-genera

Re: [GENERAL] Uber migrated from Postgres to MySQL

symptoms you report are a little thin to diagnose the actual cause. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] question on parsing postgres sql queries

he parser commiters share some lights on how the documentation > process interacts with the parser commits ? There is no automated interaction there -- it depends on human attention. On the other hand, try connecting to a database with psql and typing: \h create index ... (or any other command

Re: [GENERAL] Uber migrated from Postgres to MySQL

t the cluster under the new version* you can fall back to the old version. I remember a couple times that we saw something during a pg_upgrade --link run that we weren't expecting, and did exactly that so we could investigate and try again later. -- Kevin Grittner EDB: http://www.enterprise

Re: [GENERAL] Uber migrated from Postgres to MySQL

ng advantage of the available features. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] unique constraint with several null values

I can't do much about >> the data model itself right now, I need to protect the integrity >> of the data. Rather than unique constraints, you could add a unique index on the COALESCE of each column with some impossible value. -- Kevin Grittner EDB: http://www.enterprisedb

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

ze a; vacuum analyze b; vacuum analyze c; select id, b1_name from v; explain (analyze, buffers, verbose) select id, b1_name from v; I'm seeing the unreferenced tables pruned from the plan, and a 1ms execution time for the select from the view. -- Kevin Grittner EDB: http://www.enterprisedb.

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

important enough to you you could submit a patch or fund development of such a feature; but since it would add at least some small amount of planning time to every inner join just to avoid specifying that the join is an optional one when writing the query, it seems to me unlikely to be accepted. --

Re: [GENERAL] PSQL does not remove obvious useless joins

0 width=278) (actual time=0.006..0.006 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on b b1 (cost=0.00..12.60 rows=260 width=278) (actual time=0.002..0.003 rows=3 loops=1) Planning time: 0.177 ms Execution time: 0.044 ms (8 rows) No

Re: [GENERAL] Sequences, txids, and serial order of transactions

assigned in the apparent order of execution of the serializable transactions, I'm afraid that I don't know of any good solution for that right now. There has been some occasional talk of providing a way to read the AOoE, but nothing has come of it so far. -- Kevin Grittner EDB: http://www

Re: [GENERAL] dumb question

mal setup (like the above) helps in getting good answers quickly. >> do note, this is whats known as an 'anti-join', and these can be pretty >> expensive on large tables. > > +1 *Can* be. Proper indexing can make them very reasonable. -- Kevin Grittner EDB: http:

Re: [GENERAL] plql and or clausule

On Tue, May 31, 2016 at 4:18 PM, wrote: > ERROR: el operador no existe: character varying == character varying > LINE 1: SELECT OLD.Peticionario == NEW.Peticionario or OLD.interlocc... Perhaps you want the = operator? -- Kevin Grittner EDB: http://www.enterprisedb.com The Ente

Re: [GENERAL] full text search index

ector ? I very much doubt that full text search is going to be helpful here -- perhaps trigrams with an appropriate gist or gin index could help. Depending on table sizes and data present, picking out rows based on the OR of scanning for a sequence of characters in a couple character string

Re: [GENERAL] Timestamp with timezone output

00:00:00+02 > -[ RECORD 7 ]---+--- > expiration_date | 2015-11-27 00:00:00+01 > > Shouldn't all value be converted to the same timezone ? Perhaps your local time zone ends Daylight Saving Time between those dates, so the offset from UTC is different on those dates? -- Kevin Gri

Re: [GENERAL] Increased I/O / Writes

sparent huge pages. Yeah, be sure those are configured to be disabled in a way that "sticks" on your OS. When you get to version 9.4 you will notice that we support huge pages directly. That would be expected to work without problems even though TRANSPARENT huge pages are debilitating.

Re: [GENERAL] Increased I/O / Writes

to predict exactly. You might want to go over this page: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ... and then read the documentation of any setting you are thinking of adjusting. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --

Re: [GENERAL] Invalid data read from synchronously replicated hot standby

ther you have actually solved the flaws in your process or have just been lucky so far. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] MVIEW refresh consistently faster then insert ... select

in temporary files and workspace, with just the delta applied to the table and index in permanent storage. It's hard to guess which way will be faster for the use case you describe -- it will probably depend on what percentage of rows remain unchanged on each REFRESH. -- Kevin Grittner

Re: [GENERAL] Proper relational database?

from people; id | name +-- 1 | Fred 2 | Bob (2 rows) test=# \d List of relations Schema | Name | Type | Owner ++---+----- public | people | table | kgrittn (1 row) -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Compa

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

o the point of developing a proposed patch. That and the fact that there is no guarantee that the community as a whole would feel that the feature "carried its own weight" in terms of benefit / maintenance cost, so it might not make it in anyway. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

On Wed, Apr 13, 2016 at 5:54 PM, Kevin Grittner wrote: > See this example, and imagine that > the transaction generating the list of receipts for the closed > batch is run on the standby before the transaction adding the last > receipt commits. Or test it. https://wiki.postgresql.

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

et/mediawiki/index.php/Bug_tracking_system > > Filed http://www.pgpool.net/mantisbt/view.php?id=191 As the entry stands at the moment, the suggestions for fixes will allow incorrect query results. See this example, and imagine that the transaction generating the list of receipts for the closed

Re: [GENERAL] How to quote the COALESCE function?

1, 1/0); -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL advocacy

the long term; but if they can start down that road they are likely to find the desire to eliminate different ways to do the same thing a reason to move away from RAC or similar "lock in" technologies. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] Fwd: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)

ng such behavior is not one I would consider to be mature enough for "prime time" -- although others might feel differently. Kevin Grittner -- 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] Fwd: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)

t * from foo where mynum < 100; id | mynum +--- 1 |10 2 |10 3 |10 4 |10 5 |10 (5 rows) mydb=# update foo set mynum = 20 where id < 100; UPDATE 5 mydb=# select * from foo; id | mynum ----+--- 1 |20 2 |20 3 |20 4 |20 5 |20 (

Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers

estimate the amount of random storage I/O needed to use an indexed plan. If you tell it that you only have 64MB between those two types of cache, it will assume that the index (particularly if it is deep and/or wide) will be very expensive. -- Kevin Grittner EDB: http://www.enterprisedb.com The

Re: [GENERAL] Let's Do the CoC Right

in. Problems should be resolved in a way that minimizes the chance of escalation, recognizing that there could be miscommunication.[2] -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company [1] https://en.wikipedia.org/wiki/Golden_Rule [2] http://www.khou.com/stor

Re: [GENERAL] CoC [Final]

fferences can turn into flame wars if people don't give each other some benefit of the doubt. > Who will decide how this code is enacted? Rules imply rulers, so what is the > constitution of the governing body? It has been stated several times on this thread by multiple people tha

Re: [GENERAL] CoC [Final]

me squirm a little. Could we spin that to say that those behaviors will not be tolerated, versus not tolerating the people? Maybe: * Disruption of the collaborative space or any pattern of behaviour which could be considered harassment will not be tolerated. -- Kevin Grittner

Re: [GENERAL] Data Packaging/Data Unpacking

. If a machine contains multiple clusters it is (IMO) best practice, for both security and operational reasons, to use a separate OS user for each cluster. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@p

Re: [GENERAL] Data Packaging/Data Unpacking

with the database connection. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Data Packaging/Data Unpacking

. At all times the data is present only in files owned by the OS user which runs the database server or in RAM allocated to processes run by that user. Files and RAM are freed without overwrite; we count on the OS to not gratuitously show the old values to processes making new alloca

Re: [GENERAL] WIP: CoC V5

On Tue, Jan 12, 2016 at 10:04 PM, Joshua D. Drake wrote: > On 01/12/2016 07:10 PM, Tom Lane wrote: >> Kevin Grittner writes: >>> * To maintain a safe, respectful, productive and collaborative >>> environment all participants must ensure that their language and >&

Re: [GENERAL] WIP: CoC V5

urpose of team, at the top of the community's "Contributor Profiles" page: http://www.postgresql.org/community/contributors/ To me, this reads more like the document itself. I hope I have done justice to Josh's points as well as Tom's, although I would bet there are a number

Re: [GENERAL] Code of Conduct: Is it time?

On Mon, Jan 11, 2016 at 4:10 PM, Tom Lane wrote: > Kevin Grittner writes: >> If someone wants to take the step of posting a concrete proposal, >> please start a new thread with a different subject line. > > I thought we were already at that point; see Regina Obe's pos

Re: [GENERAL] Code of Conduct: Is it time?

always easier to discuss a concrete proposal than to try to figure out > whether something is a good idea in the abstract. I'm going to give this a belated +1, and ignore any further posts on this thread. If someone wants to take the step of posting a concrete proposal, please start a new

Re: [GENERAL] Code of Conduct: Is it time?

of it is that they have a code of conduct that attempts to control the speech or actions of contributors outside of the venue of the lists or events of the project, count me as -1, regardless of how offensive I might find said speech or actions. -- Kevin Grittner EDB: http://www.enterprisedb

[GENERAL] Re: [BUGS] BUG #13847: WARNING: skipping "pg_toast_" --- cannot vacuum indexes, views, or special system tables VACUUM

ted from initdb). If you can still find a copy of 8.2.23 you might want to install that. > PostgreSQL 9.4.1 on x86_64-mv-linux-gnu, compiled by > i686-montavista-linux-gnu-gcc (MontaVista Linux G++ 4.4-1311130628) 4.4.1, > 64-bit 9.3 and 9.4 had serious bugs in early releases w

Re: [GENERAL] A unique pairs version of UNNEST() ?

rows) Nothing in that not already mentioned; just putting it all together. The OP mentioned wanting a count, but that wasn't too clear to me; using a window function to number the rows, changing the comparison from > to >= while excluding self-matches should make that pretty easy. -- Ke

Re: [GENERAL] Secret Santa List

; ('Earl'); > -- with g as (select giver, row_number() over () as rownum from secretsanta), r as (select giver, row_number() over () as rownum from (select giver from secretsanta order by random()) as x) update secretsanta set recipient = r.giver from g join r o

Re: [GENERAL] connections not getting closed on a replica

be controlled by adjusting checkpoint and background writer settings, plus the OS vm.dirty_* settings (and maybe keeping shared_buffers smaller than you otherwise might). NUMA problems are not at issue, since there is only one memory node. Without more evidence of what is causing the problem, suggest

  1   2   3   4   5   6   >