Re: [GENERAL] autovacuum worker running amok - and me too ;)

2015-03-05 Thread Kevin Grittner
wambacher wrote: > watching the memory usage of the autovaccum process: is was getting bigger > and bigger at nearly constant speed. some MB per minute, iir. What are your settings for maintenance_work_mem and autovacuum_max_workers? -- Kevin Grittner EDB: http://www.enterprisedb.c

Re: [GENERAL] #PERSONAL# Reg: date going as 01/01/0001

2015-03-06 Thread Kevin Grittner
1, to_date(nullif(:h2, ''),'yyyymmddhh24miss')); If the two arguments to nullif() are equal, it returns NULL; otherwise it returns the first argument. -- 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] VACUUM FULL doesn't reduce table size

2015-03-09 Thread Kevin Grittner
e still discussing this? Do you have some other question? -- 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] VACUUM FULL doesn't reduce table size

2015-03-09 Thread Kevin Grittner
Joshua D. Drake wrote: > On 03/09/2015 08:57 AM, Adrian Klaver wrote: >> On 03/09/2015 08:49 AM, Kevin Grittner wrote: >>> pinker wrote: >>>> DETAIL: 0 dead row versions cannot be removed yet. >>> >>> So there are no longer any dead rows be

Re: [GENERAL] How to distinguish serialization errors from others using pqxx

2015-03-24 Thread Kevin Grittner
p://pqxx.org/development/libpqxx/ticket/219 So I guess that leaves one parsing the text of the error messages and hoping you know what language is going to be there. Should pqxx be included in the table of externally maintained client interfaces?: http://www.postgresql.org/docs/current/static/ex

Re: [GENERAL] Index corruption

2015-03-24 Thread Kevin Grittner
://www.postgresql.org/docs/9.2/static/release.html -- 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] Would like to know how analyze works technically

2015-04-01 Thread Kevin Grittner
ngs WHERE source NOT IN ('default', 'override'); -- 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] How to diagnose max_locks_per_transaction is about to be exhausted?

2015-04-01 Thread Kevin Grittner
or simply by excluding the latter from a count of all rows in the view: select count(*) from pg_locks where mode <> 'SIReadLock'; -- 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] Serializable transaction restart/re-execute

2015-04-06 Thread Kevin Grittner
? Yes, but only from the "client" side of a database connection -- although that client code. That probably belongs in some language you are using for your application logic, but if you really wanted to you could use plpgsql and dblink. It's hard for me to see a case where that w

Re: [GENERAL] Setting up a database for 10000 concurrent users

2015-05-05 Thread Kevin Grittner
cer -- 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] moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

2015-05-09 Thread Kevin Grittner
-- a sysadmin can manage interleaved buffer allocation pretty easily if they need to. If you were able to find a situation where NUMA issues within PostgreSQL caused even a 1% hit, we could always revisit the issue. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Compa

Re: [GENERAL] Planner cost adjustments

2015-06-05 Thread Kevin Grittner
although I have personally never seen problems with going up to 0.05, and that sometimes fixes a few plans that 0.03 misses. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make change

Re: [GENERAL] Indexed views like SQL Server - NOT Materialized Views

2015-06-10 Thread Kevin Grittner
While nobody can say with any certainty when such features will make it into a PostgreSQL release, I think it's safe to predict that it will not be before late 2017, and most probably later than that. This capability probably will be available through materialized views, rather than using t

Re: [GENERAL] Indexed views like SQL Server - NOT Materialized Views

2015-06-10 Thread Kevin Grittner
Nicolas Paris wrote: > Would views + partial indexes (based on views predicat) do the trick ?​ I don't see anything promising that way, but feel free to work up a proof of concept patch if you do. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] serialization failure why?

2015-06-17 Thread Kevin Grittner
is the exact version you are using (as reported by the version() function)? I am at a conference this week, away from my normal development environment; but I will take a look next week. Kevin Grittner -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes t

Re: [GENERAL] Question about the isolation level and visible

2015-06-26 Thread Kevin Grittner
SELECT 1;) in session 1 before starting the transaction in session 2. -- 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] serialization failure why?

2015-06-29 Thread Kevin Grittner
Simon Riggs wrote: > On 17 June 2015 at 13:52, Kevin Grittner wrote: >> Filipe Pina wrote: >>> if drop the foreign key constraint on stuff_ext table there are >>> no failures at all… >> >> It is my recollection that we were excluding the queries u

Re: [GENERAL] Disconnected but query still running

2015-07-14 Thread Kevin Grittner
-HOWTO/overview.html http://superuser.com/questions/729034/any-way-to-keep-connection-alive-in-pgadmin-without-setting-it-on-the-server -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make c

Re: [GENERAL] Q: text query search and

2015-07-23 Thread Kevin Grittner
both for a system to search court document text, and it seemed to work well.) The facilities for custom full text search parsers seem pretty bad; I found what I needed using regular expressions and cast to the appopriate ts types. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterp

Re: [GENERAL] Splitting a string containing a numeric value in to three parts

2015-07-27 Thread Kevin Grittner
the array. > > select regexp_matches('hello+123123.453the-123re', > > '((RE)[\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)') > should return array {hello,+123123.453,the-123re}. select regexp_matches('hello+123123.453the-123re',

Re: [GENERAL] Synchronous replication and read consistency

2015-07-29 Thread Kevin Grittner
ple replicas to prevent a failure of a standby from stalling the primary indefinitely, and you don't have an easy way to know *which* replica succeeded in persisting the transaction without doing a lot of work. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Compan

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

2015-10-02 Thread Kevin Grittner
a btree ordering comparison. For example, if a citext column were changed from 'a' to 'A', it would compare as equal with its type's "=" operator, but the row would show as changed anyway, if you use "*=" or "*<>". -- Kevin Grittner E

Re: [GENERAL] Broken primary key after backup restore.

2015-10-02 Thread Kevin Grittner
create a backup that will even start on other attempts. You might find this blog post helpful: http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing l

Re: [GENERAL] md5(large_object_id)

2015-10-05 Thread Kevin Grittner
s, but as far as I'm aware nobody has yet done so. How are your C skills? -- 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.postgresq

Re: [GENERAL] Serialization errors despite KEY SHARE/NO KEY UPDATE

2015-10-05 Thread Kevin Grittner
;>> UPDATE orders >>>> SET name = 'order of foo (2)', >>>> user_id = 1 >>>> WHERE id = 1; >>>> >>>> T1 fails with: >>>> ERROR: could not serialize access due to concurrent update >>>> CONTEXT

Re: [GENERAL] [Q] Serializable

2015-10-06 Thread Kevin Grittner
but you > mention nothing about "read dependencies". For examples, see this page: https://wiki.postgresql.org/wiki/SSI > Why not just assume it can and put code in place to handle that > possibility - especially since you should probably be > frameworking database

Re: [GENERAL] Understanding "seq scans"

2015-10-12 Thread Kevin Grittner
at can generate correct results, and picks the one with the lowest estimated cost based on your costing factors. To get a plan more like what you seem to be expecting you might need to adjust cost factors or create an index that allows more direct access to the data needed by the query. -- K

Re: [GENERAL] Multiple word synonyms (maybe?)

2015-10-20 Thread Kevin Grittner
as to pick those out with regular expression searches, put them into a space-separated string, cast that to tsvector, assign a higher weight to such key elements, and concatenate that tsvector with the one generated from the standard text parser and dictionaries. -- Kevin Grittner EDB: http://w

Re: [GENERAL] Multiple word synonyms (maybe?)

2015-10-21 Thread Kevin Grittner
ector/tsquery; this results in all *matching* but the identical wording being considered a *closer match*. As with most things, I encourage you to play around with it a bit to see what gives the best results for you. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -

Re: [GENERAL] Locks acquired by "update" statement within serializable transaction.

2015-10-28 Thread Kevin Grittner
e cases where snapshot isolation fails to protect against serialization anomalies, and this is not one of the cases. -- 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 you

Re: [GENERAL] Locks acquired by "update" statement within serializable transaction.

2015-10-28 Thread Kevin Grittner
On Wednesday, October 28, 2015 1:52 PM, Kevin Grittner wrote: > But if we already have a write > lock on the tuple (through the xmax column), then an update or > delete of the row by another transaction would cause a write > conflict and one of the transactions will surely be rolle

Re: [GENERAL] Postgresql SSI: read/write dependencies

2015-10-28 Thread Kevin Grittner
DATE, duplicating data which you maintain via triggers, and other tricks; but if you do that you will generally see performance degrade below what you get from traditional RAM-based S2PL. I hope this is enough to get you comfortable with what's happening within SSI. -- Kevin

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

2015-11-05 Thread Kevin Grittner
r when run concurrently with unknown software. If you have a link to a paper on the topic, that would serve as well as a description here. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

Re: [GENERAL] plql and or clausule

2016-05-31 Thread Kevin Grittner
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] dumb question

2016-06-02 Thread Kevin Grittner
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] Sequences, txids, and serial order of transactions

2016-06-14 Thread Kevin Grittner
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] PSQL does not remove obvious useless joins

2016-07-01 Thread Kevin Grittner
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: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

2016-07-01 Thread Kevin Grittner
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: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

2016-07-02 Thread Kevin Grittner
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: [GENERAL] unique constraint with several null values

2016-07-20 Thread Kevin Grittner
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: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-27 Thread Kevin Grittner
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] Uber migrated from Postgres to MySQL

2016-07-27 Thread Kevin Grittner
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] question on parsing postgres sql queries

2016-07-27 Thread Kevin Grittner
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

2016-07-27 Thread Kevin Grittner
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] Uber migrated from Postgres to MySQL

2016-07-29 Thread Kevin Grittner
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] Allowing multiple versions of PG under NetBSD

2016-07-29 Thread Kevin Grittner
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] Detecting if current transaction is modifying the database

2016-08-08 Thread Kevin Grittner
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] Column order in multi column primary key

2016-08-08 Thread Kevin Grittner
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] Materialized view auto refresh

2016-08-09 Thread Kevin Grittner
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

2016-08-09 Thread Kevin Grittner
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] Serializable read and blocking

2016-08-11 Thread Kevin Grittner
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] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

2016-08-22 Thread Kevin Grittner
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] Understanding Postgres Memory Usage

2016-08-25 Thread Kevin Grittner
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] COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID

2016-09-01 Thread Kevin Grittner
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] Incrementally refreshed materialized view

2016-09-26 Thread Kevin Grittner
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] Incrementally refreshed materialized view

2016-09-26 Thread Kevin Grittner
= 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

2016-09-26 Thread Kevin Grittner
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] isnull() function in pgAdmin3

2016-09-27 Thread Kevin Grittner
27;))) then 'RPG_INV' when ((("s"."Funding_Date") is null or ("s"."Funding_Date" <> '')) and (("s"."Actual_Close_Date" = '

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

2016-09-27 Thread Kevin Grittner
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] Update two tables returning id from insert CTE Query

2016-09-27 Thread Kevin Grittner
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] isnull() function in pgAdmin3

2016-09-28 Thread Kevin Grittner
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] Time travel?

2016-09-29 Thread Kevin Grittner
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] Transactional-DDL DROP/CREATE TABLE

2016-10-06 Thread Kevin Grittner
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] SERIALIZABLE and INSERTs with multiple VALUES

2016-10-11 Thread Kevin Grittner
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] SERIALIZABLE and INSERTs with multiple VALUES

2016-10-12 Thread Kevin Grittner
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

2016-10-12 Thread Kevin Grittner
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

2016-10-12 Thread Kevin Grittner
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

2016-10-12 Thread Kevin Grittner
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

2016-10-12 Thread Kevin Grittner
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

2016-10-12 Thread Kevin Grittner
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

2016-10-13 Thread Kevin Grittner
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

2016-10-13 Thread Kevin Grittner
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

2016-10-13 Thread Kevin Grittner
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

2016-10-13 Thread Kevin Grittner
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

2016-10-13 Thread Kevin Grittner
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

2016-10-14 Thread Kevin Grittner
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] out-of-order XID insertion in KnownAssignedXids

2016-10-21 Thread Kevin Grittner
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] out-of-order XID insertion in KnownAssignedXids

2016-10-24 Thread Kevin Grittner
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] SERIALIZABLE and INSERTs with multiple VALUES

2016-10-24 Thread Kevin Grittner
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] Locking question

2016-10-26 Thread Kevin Grittner
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

2016-10-26 Thread Kevin Grittner
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

2016-10-27 Thread Kevin Grittner
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] pg_class (system) table increasing size.

2016-11-17 Thread Kevin Grittner
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] pg_class (system) table increasing size.

2016-11-21 Thread Kevin Grittner
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] max_connections limit violation not showing in pg_stat_activity

2016-11-22 Thread Kevin Grittner
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] max_connections limit violation not showing in pg_stat_activity

2016-11-23 Thread Kevin Grittner
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] Methods to quiesce PostgreSQL DB without configuring log archival

2016-11-23 Thread Kevin Grittner
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] FreeBSD 10 => 11: Dump and reload your PostgreSQL database unless you like it broken

2016-12-07 Thread Kevin Grittner
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] does postgres log the create/refresh of a materialized view anywhere?

2016-12-13 Thread Kevin Grittner
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] does postgres log the create/refresh of a materialized view anywhere?

2016-12-13 Thread Kevin Grittner
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?

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

2016-12-17 Thread Kevin Grittner
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] Row value expression much faster than equivalent OR clauses

2017-01-04 Thread Kevin Grittner
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

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

2017-01-09 Thread Kevin Grittner
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] Materialized view vs. view

2017-01-10 Thread Kevin Grittner
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] Materialized view vs. view

2017-01-10 Thread Kevin Grittner
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

2017-01-10 Thread Kevin Grittner
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] Re: Are new connection/security features in order, given connection pooling?

2017-01-16 Thread Kevin Grittner
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] raise notice question

2017-01-16 Thread Kevin Grittner
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

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

2017-01-18 Thread Kevin Grittner
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] update error with serializable

2017-01-20 Thread Kevin Grittner
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

  1   2   3   4   5   6   >