Re: [GENERAL] libpq: PQreset not reconnecting connection, even though server is running

2017-10-25 Thread Geoff Winkless
I wrote: > I'm guessing (from "incomplete startup packet") that the connection > created by the new thread never actually succeeded, is that the > problem? Will PQreset only work on a connection that had previously > connected successfully? And as soon as I hit "send" on this email light dawned: t

[GENERAL] libpq: PQreset not reconnecting connection, even though server is running

2017-10-25 Thread Geoff Winkless
Hi I have code in our "run this query" shim that automatically attempts to reconnect to the server if it finds that the connection is bad (usually it's just there to handle unexpected postmaster restarts). while (PQstatus(Conn) == CONNECTION_BAD) { fprintf(stderr, "postgres connection

Re: [GENERAL] multiple sql results to shell

2017-10-23 Thread Geoff Winkless
On 23 October 2017 at 15:08, Mark Lybarger wrote: > I have this bash/sql script which outputs some curl commands. the backticks > causes it to get interpreted by the shell. This works fine if there is one > result, but when there are many rows returned, it looks like one shell > command. > > an

Re: [GENERAL] pgcon2015, what happened to SMR disk technolgy ?

2017-10-17 Thread Geoff Winkless
On 17 October 2017 at 11:59, Laurent Laborde wrote: > What's the point of the seagate archive now ? > Ironwolf, for the same public price, have better performance (obviously) > and, more surprising, a better MTBF. > ​I have no real insight into whether Seagate are still pursuing the product desi

Re: [GENERAL] Python versus Other Languages using PostgreSQL

2017-05-09 Thread Geoff Winkless
On 8 May 2017 at 22:26, Paul Hughes wrote: > 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 l

Re: [GENERAL] The Contractor Conundrum

2017-04-26 Thread Geoff Winkless
On 26 April 2017 at 15:45, Melvin Davidson wrote: > In summary, I can only advise that aspiring contractors find out as much > about a system/schema/policies before commiting to a contract, no matter > how much they offer to pay. An interesting perspective, thanks. >From the other side, I'd be

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Geoff Winkless
On 3 March 2017 at 12:17, Sven R. Kunze wrote: > On 03.03.2017 11:43, Geoff Winkless wrote: > > ​One alternative would be to make to_date accept all language variants of > months simultaneously. A quick search of google suggests that there aren't > any overlaps between lan

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Geoff Winkless
On 1 March 2017 at 14:23, Sven R. Kunze wrote: > I don't consider rolling an UDF the best alternative especially after > having looked through many solution proposals on the Web which just take an > mutable expression and wrap them up in an immutable function. > ​One alternative would be to make

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-01 Thread Geoff Winkless
On 1 March 2017 at 13:36, Sven R. Kunze wrote: > On 28.02.2017 17:50, David G. Johnston wrote: > > Supposedly one could provide a version of to_date that accepts a locale in > which to interpret names in the input data - or extend the format string > with some kind of "{locale=en_US}" syntax to a

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-28 Thread Geoff Winkless
On 28 February 2017 at 15:59, Adrian Klaver wrote: > On 02/28/2017 07:30 AM, Sven R. Kunze wrote: > >> On 28.02.2017 15:40, Adrian Klaver wrote: >> >>> [explanation of why date casting and to_datetime don't work] >>> >> >> Why is to_date not immutable? >> > > Not sure, but if I where to hazard a

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-27 Thread Geoff Winkless
On 27 February 2017 at 10:52, Sven R. Kunze wrote: > > So, what can I do to parse texts to date(times) in a safe manner? > > You know best the format of your data; if you know that your date field is always in a particular style and timezone, you can write a function that can be considered safe t

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Geoff Winkless
On 26 February 2017 at 16:09, Adrian Klaver wrote: > On 02/26/2017 07:56 AM, Geoff Winkless wrote: > > On 26 February 2017 at 10:09, Sven R. Kunze > <mailto:srku...@mail.de>>wrote: > > > > >>># create index docs_birthdate_idx ON docs using

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Geoff Winkless
On 26 February 2017 at 10:09, Sven R. Kunze wrote: > >>># create index docs_birthdate_idx ON docs using btree > (((meta->>'birthdate')::date)); > ERROR: functions in index expression must be marked IMMUTABLE > > So, what is the problem here? > ​Date functions are inherently not immutable becaus

Re: [GENERAL] How to optimize SELECT query with multiple CASE statements?

2016-10-31 Thread Geoff Winkless
On 31 October 2016 at 15:46, Alexander Farber wrote: > do you mean, instead of having player1, player2 columns in the words_games > table (as in my current schema > https://gist.github.com/afarber/c40b9fc5447335db7d24 ) - I should move the > player stuff (uid, hand, score) to a separate table and

Re: [GENERAL] How to optimize SELECT query with multiple CASE statements?

2016-10-31 Thread Geoff Winkless
On 31 October 2016 at 15:21, Geoff Winkless wrote: > LEFT JOIN words_social s1 ON s1.uid = in_uid > LEFT JOIN words_social s2 ON CASE WHEN g.player1 = in_uid THEN > g.player2 ELSE g.player1 Ugh. Of course I meant LEFT JOIN words_social s1 ON s1.uid = in_uid LEFT JOIN words_social s2

Re: [GENERAL] How to optimize SELECT query with multiple CASE statements?

2016-10-31 Thread Geoff Winkless
On 31 October 2016 at 12:53, Alexander Farber wrote: > > Good afternoon, > > is it please posible to optimize the following SQL query with numerous CASE > statements (on same condition!) without switching to PL/pgSQL? You could break the game table apart into game and gameplayer. That's more "

Re: [GENERAL] SELECT DISTINCT ON removes results

2016-10-29 Thread Geoff Winkless
On 28 October 2016 at 21:39, Guyren Howe wrote: > Using 9.5, this query: > > SELECT o.id, >a.number AS awb > FROM pt.orders o > LEFT JOIN ( > SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, > ','::text))) > string_agg(air_way_bills.number::

Re: [GENERAL] WHERE ... IN condition and multiple columns in subquery

2016-10-28 Thread Geoff Winkless
On 28 October 2016 at 12:03, Alexander Farber wrote: > is it please possible to rewrite the SQL query > > SELECT DISTINCT ON (uid) > uid, > female, > given, > photo, > place > FROM words_social > WHERE uid IN (SELECT

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

2016-10-11 Thread Geoff Winkless
On 10 October 2016 at 14:49, Merlin Moncure wrote: > MVCC rules (which DDL generally fall under) try to interleave work as > much as possible which is the problem you're facing. Mmff. Yes, that exposes a fundamental misunderstanding on my part: I had thought that under MVCC things were done indep

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

2016-10-10 Thread Geoff Winkless
On 6 October 2016 at 18:33, Tom Lane wrote: > I'm a bit confused about exactly what the context is here. AFAICS, > the fragment you quoted should work as you expect, as long as the > table always exists beforehand. Then, the DROPs serialize the > transactions' access to the table and all is well.

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

2016-10-10 Thread Geoff Winkless
On 6 October 2016 at 18:25, Adrian Klaver wrote: > I do not see sarcasm, I see someone trying to work through what is a complex > scenario. When someone talks about things "magically working as you think it should" I see sarcasm. Perhaps I misread, in which case I apologise. >> _As far as the tr

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

2016-10-06 Thread Geoff Winkless
On 6 October 2016 at 16:47, Kevin Grittner wrote: > I recommend using a transactional advisory lock to serialize these. Thanks Kevin, that does seem like the best (although not particularly pleasant) solution. Geoff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

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

2016-10-06 Thread Geoff Winkless
On 6 October 2016 at 16:57, Francisco Olarte wrote: > You are contradicting yourself. First you say after the command it > must not exist. Then you say to do it at commit time. If it is done at > commit time you cannot guarantee it does not exist after the command. I'm not contradicting myself at

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

2016-10-06 Thread Geoff Winkless
On 6 October 2016 at 15:04, Francisco Olarte wrote: > And anyway, what isolation level are you working on? Because it seems > you are using a weaker one than serializable, as I think serializable > should give you more or less what you are expecting ( not on commit > time, but second drop could pr

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

2016-10-06 Thread Geoff Winkless
On 6 Oct 2016 12:06 p.m., "Francisco Olarte" wrote: > > On Thu, Oct 6, 2016 at 11:21 AM, Geoff Winkless wrote: > > DROP TABLE IF EXISTS mytable; CREATE TABLE mytable > > > > Occasionally this produces > > > > ERROR:

[GENERAL] Transactional-DDL DROP/CREATE TABLE

2016-10-06 Thread Geoff Winkless
Hi I have code that does (inside a single transaction) DROP TABLE IF EXISTS mytable; CREATE TABLE mytable Occasionally this produces ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(mytable, 2200) already exists. I can

Re: [GENERAL] joined tables with USING and GROUPBY on the USING() column

2016-09-07 Thread Geoff Winkless
Thanks for the suggestions. Turns out I was right: I _was_ missing something obvious - results had token as char(4), tokens had token as varchar(4). Because the columns aren't the same they aren't treated as identical so the query builder won't accept the unqualified name. Next task is to work out

Re: [GENERAL] joined tables with USING and GROUPBY on the USING() column

2016-09-07 Thread Geoff Winkless
On 7 Sep 2016 9:01 p.m., "Adrian Klaver" wrote: > What happens if you table qualify all the references to token? Oh it definitely fixes it; I was more confused why it works on one server and not another. I thought perhaps there was a config option to allow more lax naming in this way. If not I'll

[GENERAL] joined tables with USING and GROUPBY on the USING() column

2016-09-07 Thread Geoff Winkless
I'll start by saying that I'm sure I'm missing something obvious... I have a query that is working fine on all my servers except one. The only obvious difference is that the failing one is running 9.5.3, while most are running 9.5.4, but since the query works on a 9.5.1 box I also have I can't ima

Re: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-28 Thread Geoff Winkless
On 28 July 2016 at 16:34, Igor Neyman wrote: > Which means that you can make use of some new feature, but definitely not > all. > That makes "downgrade" feature very, very limited, if useful at all. > ​ Sufficient to allow you to run the upgrade, find that there's a catastrophic bug in the new v

Re: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-27 Thread Geoff Winkless
7;s worth").​ G On 27 July 2016 at 17:11, Andrew Sullivan wrote: > On Wed, Jul 27, 2016 at 04:51:42PM +0100, Geoff Winkless wrote: > > technical reasons. Most developers will harp on at their boss about how > > terrible their current database is and how performs > > muc

Re: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-27 Thread Geoff Winkless
On 27 July 2016 at 15:22, Scott Mead wrote: > "The bug we ran into only affected certain releases of Postgres 9.2 and > has been fixed for a long time now. However, we still find it worrisome > that this class of bug can happen at all. A new version of Postgres could > be released at any time th

Re: [GENERAL] Thoughts on "Love Your Database"

2016-05-17 Thread Geoff Winkless
On 17 May 2016 at 10:22, Achilleas Mantzios wrote: > On 17/05/2016 12:16, Geoff Winkless wrote: >> >> On 17 May 2016 at 09:34, Pierre Chevalier Géologue >> wrote: >>> >>> On this matter, I hear *very* often from such guys that the only reproach >>

Re: [GENERAL] Thoughts on "Love Your Database"

2016-05-17 Thread Geoff Winkless
On 17 May 2016 at 09:34, Pierre Chevalier Géologue wrote: > On this matter, I hear *very* often from such guys that the only reproach > they have to PostgreSQL is that it does not come with a slick GUI like > Access. PGAdmin does not suit their needs at all: they want to design their > forms, dir

Re: [GENERAL] Thoughts on "Love Your Database"

2016-05-04 Thread Geoff Winkless
On 4 May 2016 at 17:14, Will McCormick wrote: > Examples: Companies buy other companies - You are using a competitors data > store and want to replace it. Company needs to compete with competitors and > wants to reduce cost ... Or, let's say, massive multibillion-dollar DBMS competitor buys s

Re: [GENERAL] Thoughts on "Love Your Database"

2016-05-04 Thread Geoff Winkless
On 4 May 2016 at 12:36, Szymon Lipiński wrote: > From my perspective there is one more thing: when I tried, in couple of > companies, to move some part of the logic to a database, then usually the > management said "no, that's not doable, as we will have trouble with finding > good sql programmers

Re: [GENERAL] Thoughts on "Love Your Database"

2016-05-04 Thread Geoff Winkless
On 4 May 2016 at 06:46, dandl wrote: > I'm a strong believer in putting the business code next to the data, not the > wrong > side of the object-relational divide. However, for many the challenge of > writing and > debugging SQL code is just too high! Your source for this statement please? "For

Re: [GENERAL] Proper relational database?

2016-04-24 Thread Geoff Winkless
On 24 April 2016 at 12:29, Geoff Winkless wrote: > To find students with no exam today (the other point of your argument): > > SELECT student_id, name > FROM student > LEFT JOIN exam USING(student_id) > WHERE exam_date=CURRENT_DATE AND exam.student_id IS NULL; *sigh* problem w

Re: [GENERAL] Proper relational database?

2016-04-24 Thread Geoff Winkless
On 24 April 2016 at 08:36, Thomas Munro wrote: > For example, say we want all students who have one or more exam today: > > SELECT s.student_id, s.name > FROM student s >WHERE EXISTS (SELECT 1 >FROM exam e > WHERE e.student_id = s.student_id >

Re: [GENERAL] Proper relational database?

2016-04-23 Thread Geoff Winkless
On 23 April 2016 at 07:08, Manuel Gómez wrote: > but its semantics can be rather wonky. Witness: > > postgres=# select 1; > ?column? > -- > 1 > (1 row) > > postgres=# select 1 union select 1; > ?column? > -- > 1 > (1 row) Exactly what you would expect. Use UNIO

Re: [GENERAL] Proper relational database?

2016-04-22 Thread Geoff Winkless
On 22 April 2016 at 07:05, Guyren Howe wrote: > As I say, it amazes and somewhat depresses me that someone isn't doing this. > The NoSQL movement shows that the world is ready for change. Someone should > be offering folks something better than bloody MongoDB. > > Please don't get me wrong. I *ado

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

2016-04-21 Thread Geoff Winkless
On 21 April 2016 at 17:08, David G. Johnston wrote: > I have little experience (and nothing practical) with columnar store but at > a high level I don't see the point. I would hope that anyone interested in > working on a columnar store database would pick an existing one to improve > rather than

Re: [GENERAL] More correlated (?) index woes

2016-03-31 Thread Geoff Winkless
On 31 Mar 2016 03:02, "bricklen" wrote: > Perhaps a partial index like "create index pa_sc_id_pidx on pa (sc_id) with > (fillfactor=100) where field1 IS NULL;" will help? Thanks for the suggestion. It might, but the problem with that is there's (something like) field2-16 which are used in simil

Re: [GENERAL] More correlated (?) index woes

2016-03-29 Thread Geoff Winkless
On 28 March 2016 at 20:23, I wrote: > Table pa has 7522676 rows, 4834042 of which have field1 NULL, so it's > absolutely not reasonable to expect this to be an optimal strategy. > ​ > It occurred to me that even though the majority of values are NULL, there are ​ ​1691 unique values in pa.field1,

Re: [GENERAL] More correlated (?) index woes

2016-03-28 Thread Geoff Winkless
On 28 March 2016 at 22:01, rob stone wrote: > What does:- > > DELETE FROM pa > WHERE pa.field1 IS NULL > AND pa.sc_id IN (SELECT legs.sc_id FROM legs > WHERE legs.scdate BETWEEN 20160220 AND > 20160222) > > give as a cost when you run ANALYZE over it? > ​Thanks for the suggestion. It's a ​pprox

[GENERAL] More correlated (?) index woes

2016-03-28 Thread Geoff Winkless
So I accept that when using MIN(sc_id) against scdate it makes statistical sense to use the sc_id index for a reasonable percentage of the full range of scdate, unless we know in advance that scdate is closely correlated to sc_id (because using MIN means we can stop immediately we hit a value). Ho

Re: [GENERAL] index problems (again)

2016-03-13 Thread Geoff Winkless
On 12 March 2016 at 22:00, Peter J. Holzer wrote: > I don't think most people's data is perfectly distributed. But as you > say most data is probably within some deviation of being perfectly > distributed and as long as that deviation isn't too big it doesn't > matter. Is that how what I wrote ca

Re: [GENERAL] index problems (again)

2016-03-12 Thread Geoff Winkless
On 12 March 2016 at 18:43, Peter J. Holzer wrote: > On 2016-03-08 10:16:57 +0000, Geoff Winkless wrote: >> On 7 March 2016 at 20:40, Peter J. Holzer wrote: >> > As Tom wrote, the estimate of having to read only about 140 rows is only >> > valid if sc_id and sc_date a

Re: [GENERAL] index problems (again)

2016-03-08 Thread Geoff Winkless
On 7 March 2016 at 20:40, Peter J. Holzer wrote: > As Tom wrote, the estimate of having to read only about 140 rows is only > valid if sc_id and sc_date are uncorrelated. In reality your query has > to read a lot more than 140 rows, so it is much slower. But as I've said previously, even if I do

Re: [GENERAL] index problems (again)

2016-03-07 Thread Geoff Winkless
On 7 March 2016 at 20:23, Jeff Janes wrote: > PostgreSQL does not (yet) implement "loose" index scans or "skip > scans", which is what you are asking for. You can roll your own using > the techniques described here: > https://wiki.postgresql.org/wiki/Loose_indexscan, which has the > benefit over

Re: [GENERAL] index problems (again)

2016-03-07 Thread Geoff Winkless
On 7 March 2016 at 16:44, Tom Lane wrote: > Geoff Winkless writes: >> But as far as I can see, apart from the absolute extremes, the >> index-only scan is _always_ going to be quicker than the index+table >> scan. > > Well, that is a different issue: what does the

Re: [GENERAL] index problems (again)

2016-03-07 Thread Geoff Winkless
On 7 March 2016 at 16:02, Tom Lane wrote: > In English, what that plan is trying to do is scan the index > in sc_id order until it hits a row with scdate in the target range. > The first such row, by definition, has the correct min(sc_id) value. > The problem is that we're guessing at how soon we'

Re: [GENERAL] index problems (again)

2016-03-07 Thread Geoff Winkless
On 7 March 2016 at 14:51, Tom Lane wrote: > Geoff Winkless writes: >> So it seems that it should in fact be usable after all. So I'm still >> stumped as to why the (scdate,sc_id) index isn't used :( > > Because the other way is estimated to be cheaper. The es

Re: [GENERAL] index problems (again)

2016-03-07 Thread Geoff Winkless
On 7 March 2016 at 14:27, I wrote: > So it seems that it should in fact be usable after all. So I'm still > stumped as to why the (scdate,sc_id) index isn't used :( Also, while the index on sc_id will be sorted there's no guarantee that sc_id values will be in order in the table itself, so you're

Re: [GENERAL] index problems (again)

2016-03-07 Thread Geoff Winkless
On 7 March 2016 at 14:18, I wrote: > That assumes that I've not completely misunderstood, of course :) Always a dangerous assumption, I'm rapidly learning. The very next section: Constraints on columns to the right of these columns are checked in the index, so they save visits to the table

Re: [GENERAL] index problems (again)

2016-03-07 Thread Geoff Winkless
On 7 March 2016 at 13:23, Victor Yegorov wrote: > Your `sc_id` and `scdate` columns are correlated. Actually not necessarily, although in the majority case that's mostly true. > Planner has no such knowledge and assumes columns being independent. Your > `scdate` predicate is > estimate to return

Re: [GENERAL] index problems (again)

2016-03-07 Thread Geoff Winkless
On 7 March 2016 at 11:48, Victor Yegorov wrote: > 2016-03-07 13:38 GMT+02:00 Geoff Winkless : >> >> # EXPLAIN (ANALYZE,BUFFERS) SELECT MIN(sc_id) FROM legs WHERE scdate >> BETWEEN 20160219 AND 20160221; > > > Will it help if you'll add `count(*)` to your query

[GENERAL] index problems (again)

2016-03-07 Thread Geoff Winkless
Hi all Firstly, I appreciate that my index problems are fairly difficult to debug given that I can't upload the data anywhere (it's commercially sensitive); I tried creating an equivalent dataset for my last problem using a lot of random() inserts, but unfortunately, even though the sizes and inde

Re: [GENERAL] space required before negative

2016-03-04 Thread Geoff Winkless
On 3 March 2016 at 17:30, Tom Lane wrote: > That's a syntax issue, so the place to look is > http://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS Ah, thanks. Perhaps a note in the operators page might be helpful? I'll change to use <> in place of !=; quite apart

[GENERAL] space required before negative

2016-03-03 Thread Geoff Winkless
Hi I was surprised to find that whitespace is required between the != operator and a negative sign, otherwise postgres believes that I'm intending !=- as an operator (I get "operator does not exist: integer !=- integer"). This isn't the case with <>-x. Is this intentional? I couldn't find refere

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

2016-02-29 Thread Geoff Winkless
On 29 Feb 2016 22:47, "Kevin Grittner" wrote: > > On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless wrote: > > > I'm not really sure what changes I could make that would make one > > index that's ostensibly equivalent to the other not be attractive to >

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

2016-02-29 Thread Geoff Winkless
On 29 February 2016 at 18:31, Joshua D. Drake wrote: > I haven't been following this thread but did you try looking at the costs? Thanks for the response... > #seq_page_cost = 1.0# measured on an arbitrary scale > #random_page_cost = 4.0 # same scale as above

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

2016-02-29 Thread Geoff Winkless
Just as a continuation of this, I can set effective_cache_size to 64MB and it will still use the single-column index, but PG flatly refuses to use the multicolumn index without effective_cache_size being an unfeasibly large number (2x the RAM in the machine, in this case). Geoff -- Sent via pgs

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

2016-02-29 Thread Geoff Winkless
On 29 February 2016 at 14:07, Geoff Winkless wrote: > On 29 February 2016 at 14:06, Jim Mlodgenski wrote: >> No they are not the same. When you don't include a unit for >> effective_cache_size, it defaults to page size so you're saying 2146435072 * >> 8K > &g

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

2016-02-29 Thread Geoff Winkless
On 29 February 2016 at 14:06, Jim Mlodgenski wrote: > No they are not the same. When you don't include a unit for > effective_cache_size, it defaults to page size so you're saying 2146435072 * > 8K Hah. Thanks Jim, like I said I was sure I'd be missing something :) Geoff -- Sent via pgsql-ge

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

2016-02-29 Thread Geoff Winkless
I'm sure I'm missing something here. A query takes 50 seconds; it's doing a seq-scan on a joined table, even though the table is joined via a field that's the leftmost column in a multicolumn index (http://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html says "equality constraints on le

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread Geoff Winkless
On 9 February 2016 at 15:16, David G. Johnston wrote: > Same error...I tested using the table as well...also the same error for > values of id between 1 and 3. Oh my. In my memory, this was working. I try it now, and it doesn't. Apologies: I've obviously managed to lose track of what worked and

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread Geoff Winkless
On 9 February 2016 at 14:53, Tom Lane wrote: > SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid; > > There's no null visible anywhere in that. I suppose that if there's > no row with id=4, there would be a null at runtime, Well yes, that was the whole point. > but that's no

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread Geoff Winkless
On 8 February 2016 at 16:05, David G. Johnston wrote: > While explicit casting of literals can at times be annoying and seemingly > unncessary I wouldn't call it unintuitive. Well that very much depends on your definition of intuitive. If something is "seemingly unnecessary" I would say that'

[GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-08 Thread Geoff Winkless
Hi Not an important question, but a niggle. CREATE TABLE gwtest (id INT PRIMARY KEY); INSERT INTO gwtest VALUES (1),(2),(3); SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid; gives an error failed to find conversion function from unknown to text I can work around this with

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-08 Thread Geoff Winkless
On 8 February 2016 at 14:49, Tom Lane wrote: > Yup. The output column type of the sub-SELECT is determined without > reference to its context, so there's nothing causing the unknown-type > literal to get assigned a definite type. Mm. I can follow that, although it makes me unhappy that casting t

[GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-08 Thread Geoff Winkless
Hi Not an important question, but a niggle. CREATE TABLE gwtest (id INT PRIMARY KEY); INSERT INTO gwtest VALUES (1),(2),(3); SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid; gives an error failed to find conversion function from unknown to text I can work around this with

Re: [GENERAL] Is PRIMARY KEY the same as UNIQUE NOT NULL?

2016-02-08 Thread Geoff Winkless
On 7 February 2016 at 21:04, Tom Lane wrote: > Geoff Winkless writes: >> On 31 January 2016 at 19:53, David G. Johnston >> wrote: >>> A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally allows > >> I would just remove the whole paragraph. A pr

Re: [GENERAL] Is PRIMARY KEY the same as UNIQUE NOT NULL?

2016-02-01 Thread Geoff Winkless
On 31 January 2016 at 19:53, David G. Johnston wrote: > A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally allows [snip] I would just remove the whole paragraph. A primary key does what it does, a unique constraint does what it does. I'm not really sure why you need to link the

Re: [GENERAL] request for comment re "contributor-covenant.org"

2016-01-26 Thread Geoff Winkless
Wow. And I was annoyed with myself that _I'd_ wasted so much time by being drawn into this nonsense. It appears that the only way to deal with the covenant and its proponents is just to say "lalalalalala can't hear you" because they will not listen to reason or take on board that any of what they

Re: [GENERAL] CoC [Final v2]

2016-01-24 Thread Geoff Winkless
On 24 January 2016 at 17:30, Joshua D. Drake wrote: > If you are participating in this thread, be productive. If you are going to > be sarcastic and not helpful, get off the thread. And as for being not helpful, I was being helpful and my helpful and reasoned points were ignored because they simp

Re: [GENERAL] CoC [Final v2]

2016-01-24 Thread Geoff Winkless
On 24 January 2016 at 17:34, Joshua D. Drake wrote: > That won't work. The community does take positions. A good example is when > -core denounced the topless dancers at the Russian conference. That position > was taken without consideration that at a lot of this community doesn't > care, won't ca

Re: [GENERAL] CoC [Final v2]

2016-01-24 Thread Geoff Winkless
On 24 January 2016 at 17:30, Joshua D. Drake wrote: > Sarcasm is not productive. Actually I wasn't being sarcastic. OK, I was being sarcastic in the first paragraph, but not the second :p The most significant problem I see with the Contributor Covenant (other than my personal feeling that Postgr

Re: [GENERAL] CoC [Final v2]

2016-01-24 Thread Geoff Winkless
On 24 January 2016 at 14:53, FarjadFarid(ChkNet) wrote: > I do agree with Dave on the points he has made. > > Can we please add these so everyone is happy and finalise the CoC? Sure, why not? Forget that at least 50% (I'm being generous) of the contributors to the thread disagree, we'll just do w

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

2016-01-24 Thread Geoff Winkless
On 24 January 2016 at 00:06, David E. Wheeler wrote: > On Jan 23, 2016, at 3:43 PM, Joshua D. Drake wrote: > >> I have been accused of being a fat hater. My crime? I suggested that >> generally speaking, obesity is a matter of diet and exercise. Worse? The >> individual started the conversation

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

2016-01-24 Thread Geoff Winkless
On 24 January 2016 at 00:15, Steve Litt wrote: > On Sun, 24 Jan 2016 00:00:27 + > Geoff Winkless wrote: >> Did I say we all need equal protection? No. I said we're all entitled >> to the same level of protection. > > The preceding two sentences form a di

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

2016-01-23 Thread Geoff Winkless
On 23 January 2016 at 23:39, David E. Wheeler wrote: > I get that my short, snarky posts don’t help my argument, but I admit to > being a bit frustrated that the posts wherein I have tried to lay out a > position get little or no response. So let me try again. They get a response; however it's

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

2016-01-23 Thread Geoff Winkless
On 23 January 2016 at 21:59, Steve Litt wrote: > I'm reminded of a person on a computer on a no-Internet-connection LAN > saying that everyone needs equal protection from firewalls. Ummm, no. > The Internet connected firewall has many, many more attempts made > against it than the guy on the islan

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

2016-01-23 Thread Geoff Winkless
On 23 January 2016 at 18:07, David E. Wheeler wrote: > On Jan 22, 2016, at 6:14 PM, Joshua D. Drake wrote: >> A Code of Conduct should protect all, equally and without bias. > > Says someone who requires no protection at all. I must object to the repeated assertions that certain people in this c

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

2016-01-23 Thread Geoff Winkless
On 22 January 2016 at 23:31, David E. Wheeler wrote: > On Jan 22, 2016, at 3:15 PM, Kevin Grittner wrote: > >> I do wonder what it is that made you terrified of a shitstorm, and >> what it is that you're hoping for that you don't feel is already >> present. > > Regina linked to some shitstorms in

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

2016-01-22 Thread Geoff Winkless
On 22 January 2016 at 19:47, Luz Violeta wrote: > And that's the foundation on > which the CoC is being written. I saw the CoC go down, down, and down in > content and quality, not taking stances for nothing and falling into > generalizations. As I understand it the main motivation for not wantin

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

2016-01-22 Thread Geoff Winkless
On 22 January 2016 at 19:37, David E. Wheeler wrote: > On Jan 22, 2016, at 11:28 AM, Magnus Hagander wrote: > >> Regardless whether it's true or not (to which I cannot speak), surely >> statements like that would violate *both* the contributor covenant *and* the >> CoC suggested by others. > >

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

2016-01-22 Thread Geoff Winkless
I'm copying this (which I sent to you individually) back into the group because you clearly don't score enough troll points to make it worth your while answering my questions when I send it to you off-list. On 22 January 2016 at 17:21, David E. Wheeler wrote: > On Jan 22, 2016, at 9:18 AM, Adrian

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

2016-01-22 Thread Geoff Winkless
On 22 January 2016 at 17:30, David E. Wheeler wrote: > The way to involve a broader audience is to solicit feedback from outside the > immediate confines of a single mail list. Or even the community itself. > People have left the community because of issues; how do you get their help > fixing t

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

2016-01-22 Thread Geoff Winkless
On 22 January 2016 at 13:09, FarjadFarid(ChkNet) wrote: >>Geoff wrote >>> Then end users will move on, or get involved. That's also right and proper. > You rather see postgresql ,as a product, die but you want to no one have an > input. Just yours. Now I'm being reasonable and explaining my poi

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

2016-01-22 Thread Geoff Winkless
On 22 January 2016 at 12:08, FarjadFarid(ChkNet) wrote: > > But Geoff, Without knowing what problems people are facing in their > businesses no product will ever stay relevant to end users for long. Then end users will move on, or get involved. That's also right and proper. > So everyone's prob

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

2016-01-22 Thread Geoff Winkless
On 22 January 2016 at 10:47, FarjadFarid(ChkNet) wrote: > A number of contributors have asked why we should have Coc. I'm not sure that that's true. Several have said that they don't believe that we should, but that's not the same thing. Everyone is entitled to their opinion. I don't think we sho

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

2016-01-22 Thread Geoff Winkless
On 22 January 2016 at 05:25, David E. Wheeler wrote: > I can’t help that there are a whole lot of white guys working on this > document, with very little feedback from the people who it’s likely to > benefit (only exception I spotted in a quick scan was Regina; sorry if I > missed you). I suspe

Re: [GENERAL] CoC [Final]

2016-01-21 Thread Geoff Winkless
On 21 January 2016 at 12:36, Chris Travers wrote: > I still side with the Scandinavian approach of passing general laws and > trusting judges to apply them in line with moral rather than purely legal > principles. I believe that it's generally accepted that people will unconsciously apply their o

Re: [GENERAL] CoC [Final]

2016-01-21 Thread Geoff Winkless
On 21 January 2016 at 11:28, Chris Travers wrote: > Resisting the urge to talk about how justice was actually seen in the Dark > Ages Pitchforks. Baying crowds dragging those they consider to be wrongdoers from their beds and tying them to four horses and pulling them apart in the town square

Re: [GENERAL] CoC [Final]

2016-01-21 Thread Geoff Winkless
On 21 January 2016 at 10:37, Chris Travers wrote: > At the end of the day this will require human judgment rather than > formulation. Then make it explicit. * Disruption of the collaborative space, or patterns of behaviour which the majority of the core team consider to be harassment, will not b

Re: [GENERAL] CoC [Final]

2016-01-21 Thread Geoff Winkless
On 20 January 2016 at 20:04, Alvaro Herrera wrote: > "which could be considered" is too open-ended. Since this point is > the one and only that can cause enforcement to occur, it should be more > strict as to what it is that will not be tolerated. I'd propose > something like "is widely regarded

Re: [GENERAL] CoC [Final]

2016-01-20 Thread Geoff Winkless
On 20 January 2016 at 15:19, Brian Dunavant wrote: >> * Participants who disrupt the collaborative space, or participate in >> a pattern of behaviour which could be considered harassment will not >> be tolerated. > > Perhaps changing the ", or participate" to " by engaging" would make > that state

Re: [GENERAL] WIP: CoC V5

2016-01-14 Thread Geoff Winkless
On 14 January 2016 at 17:11, Joshua D. Drake wrote: > Right but here is the rub. Being anti-gay marriage isn't an extreme opinion. > It is a minority opinion for sure but it is certainly not extreme. Well it is - it's an extremity in the range of potential view points. > Another issue, consider

Re: [GENERAL] WIP: CoC V5

2016-01-14 Thread Geoff Winkless
On 14 January 2016 at 16:37, Joshua D. Drake wrote: > If someone stands up in a respectful way in a public place and argues > a position, they should not be demonized or punished for that. I completely agree with you, unfortunately there are enough people who are so militant about their particula

  1   2   >