Re: [GENERAL] Debugging a backend stuck consuming CPU

2016-05-19 Thread Tom Lane
many prepared statements. I'm a bit surprised that you could have gotten up to 12GB worth of prepared statements in an application that sends DISCARD ALL periodically. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To ma

Re: [GENERAL] PQcancel may hang in the recv call

2016-05-19 Thread Tom Lane
"David G. Johnston" <david.g.johns...@gmail.com> writes: > On Thu, May 19, 2016 at 3:32 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> I do not recall anyone ever reporting something similar --- and that code >> has been like that for a long time. > ​I'd t

Re: [GENERAL] PQcancel may hang in the recv call

2016-05-19 Thread Tom Lane
reate more problems than it fixes, considering the evident rarity of the problem. The race condition hazard that the recv() is trying to prevent is definitely real: we used to not have that, and we got bug reports, cf http://www.postgresql.org/message-id/flat/20030915070801.gd23...@opencloud.com

Re: [GENERAL] Debugging a backend stuck consuming CPU

2016-05-19 Thread Tom Lane
rmative: https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend regards, tom lane -- 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] first_value/last_value

2016-05-18 Thread Tom Smith
It would really save all the troubles for many people if postgresql has a built-in first/last function along with sum/avg. There is already a C extension and a wiki sample and implemented for window function. I am curious why these two functions were not added along their window implementation

Re: [GENERAL] first_value/last_value

2016-05-18 Thread Tom Smith
t will give you two rows that are the first / last in your set > based on whatever column you order on. > On May 18, 2016 8:47 PM, "Tom Smith" <tomsmith198...@gmail.com> wrote: > >> Hello: >> >> Is there a plan for 9.7 to enable using the two aggregate fu

[GENERAL] first_value/last_value

2016-05-18 Thread Tom Smith
Hello: Is there a plan for 9.7 to enable using the two aggregate function as non-window function? i.e. enabling getting the first/last row in single sql without using window features. There is actually a C-extension for first()/last(). I am wondering if 9.7 would make them built-in function

Re: [GENERAL] Ascii Elephant for text based protocols

2016-05-15 Thread Tom Lane
| or even > | v | | v | > ||/\| | regards, tom lane -- 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] EINTR causes panic (data dir on btrfs)

2016-05-13 Thread Tom Lane
I contract specified by signal(7). A bit of googling suggests that at least one such case used to exist in btrfs but has been fixed: http://linux-btrfs.vger.kernel.narkive.com/CbodH9VP/patch-btrfs-don-t-return-eintr I wonder what kernel version the OP was using. regards, tom

Re: [GENERAL] Using both ident and password in pg_hba.conf

2016-05-09 Thread Tom Lane
"D'Arcy J.M. Cain" <da...@druid.net> writes: > On Mon, 09 May 2016 17:12:22 -0400 > Tom Lane <t...@sss.pgh.pa.us> wrote: >> If the same user id + database combinations might be valid in both >> cases (from both PHP and manual connections) I think your only

Re: [GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

2016-05-09 Thread Tom Lane
Robert Anderson <ranom...@gmail.com> writes: > Only one line returned: > postgres=# select * from pg_stat_activity where pid=3990; Aaah, sorry, that was a brain fade. I meant to ask about rows in pg_locks with that pid. regards, tom lane -- Sent via p

Re: [GENERAL] Using both ident and password in pg_hba.conf

2016-05-09 Thread Tom Lane
iple challenges, which seems like a large assumption.) I don't have much of a feeling for how hard it would be to do in the server. regards, tom lane -- 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] Create index concurrently hanging with big table on pgsql 9.3.12

2016-05-09 Thread Tom Lane
quot;waiting" is false, probably there isn't anything in pg_locks. Still, it'd be better to do "select * from pg_stat_activity where pid = 3990" and be sure. regards, tom lane -- 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] Debian and Postgres

2016-05-04 Thread Tom Lane
it's an over-aggressive packet filter. regards, tom lane -- 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] Field size become unlimited in union...

2016-05-03 Thread Tom Lane
s type-specific; and we don't have one. regards, tom lane -- 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 of parent without partitions possible?

2016-05-03 Thread Tom Lane
ile other queries are scanning it. regards, tom lane -- 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] JSONB performance enhancement for 9.6

2016-05-01 Thread Tom Smith
in the storage, all keys starting with "a" is in first segment, etc. On Sun, May 1, 2016 at 4:14 PM, Oleg Bartunov <obartu...@gmail.com> wrote: > > > On Sun, May 1, 2016 at 6:46 AM, Tom Smith <tomsmith198...@gmail.com> > wrote: > >> Hello: >> >&g

Re: [GENERAL] JSONB performance enhancement for 9.6

2016-04-30 Thread Tom Smith
implementation, both common use cases (one is global doc indexing, the other is fast retrieval of individual values) would work out and make postgresql unbeatable. On Tue, Jan 19, 2016 at 8:51 PM, Bruce Momjian <br...@momjian.us> wrote: > On Mon, Jan 11, 2016 at 09:01:03PM -0500,

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

2016-04-30 Thread Tom Lane
to > correlate with any particular queries that are running against the master or > the standby, and the delay only subsides when the vacuum completes. What PG version might this be? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql

Re: [GENERAL] Why don't custom GUCs show in pg_settings?

2016-04-30 Thread Tom Lane
, which I don't particularly approve of because it piled another hack on top of that mess without doing a thing to make it cleaner. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.p

Re: [GENERAL] Allow disabling folding of unquoted identifiers to lowercase

2016-04-29 Thread Tom Lane
tandards compliant!" going for it. So I doubt we'd accept such a patch even if someone managed to create one. regards, tom lane -- 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] Clarify "allow_system_table_mods"

2016-04-25 Thread Tom Lane
o reason to use them on a production database. I'm not sure what else we should do, short of writing "HERE BE DRAGONS" set in blackletter type. (Having said that, we could change "This is used by initdb." to "This is meant only for use by initdb". But I don't k

Re: [GENERAL] Clarify "allow_system_table_mods"

2016-04-25 Thread Tom Lane
he lack of concern about whether you can crash the system with it. regards, tom lane -- 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] Intemittendly get "server process (PID 5884) exited with exit code 3"

2016-04-25 Thread Tom Lane
to > recover is to restart postgres service. Or maybe the exit(3) is in an extension that is preloaded into all processes via shared_preload_libraries or similar? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make cha

Re: [GENERAL] different empty array syntax requirements

2016-04-21 Thread Tom Lane
st-of-an-array hack doesn't know it should look through such a node. That's a bug. Will fix it. regards, tom lane -- 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] setting time zone in a function

2016-04-21 Thread Tom Lane
bbreviation and '.' being the DST zone abbreviation. If you wanted to restrict input to be the Olson-style zone names, you could do some kind of precheck, maybe insist on only letters/ slashes/underscores. regards, tom lane -- Sent via pgsql-general mailing list (pgsq

Re: [GENERAL] Is it possible to call Postgres directly?

2016-04-21 Thread Tom Lane
hing similar. regards, tom lane -- 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] setting time zone in a function

2016-04-21 Thread Tom Lane
ght be faster than an exception block. regards, tom lane -- 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] Is it possible to call Postgres directly?

2016-04-20 Thread Tom Lane
on your own head whether that worked and kept working across releases. regards, tom lane -- 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] RLS policy dump/restore failure due to elided type-casts

2016-04-20 Thread Tom Lane
Karl Czajkowski <kar...@isi.edu> writes: > On Apr 20, Tom Lane modulated: >> Just out of curiosity, why are you doing it like that, and not simply >> USING ('example attribute value' = ANY (current_attributes())) >> It seems like you're going out of your way to compl

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

2016-04-20 Thread Tom Lane
backup/restore/ replication situations, and then you realize that the required semantics are far from clear. In practice, audit logs (which is a class of feature that we are working on) are a far better solution. regards, tom lane -- Sent via pgsql-general mailing list

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

2016-04-20 Thread Tom Lane
<da...@andl.org> writes: > From: Tom Lane [mailto:t...@sss.pgh.pa.us] >> Define "executes". You could shove those lines in via the wire protocol, >> sure, but SPI won't take them. > Now you really have me puzzled. What I provided is an extract from the lo

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

2016-04-20 Thread Tom Lane
IC, "ate" TEXT, > "ati" TIMESTAMP, UNIQUE ( "abo", "abi", "anu", "ate", "ati" ) ); > COMMIT; > INSERT INTO "V6" ( "abo", "abi", "anu", "ate", "ati" ) VALUES ( $1,

Re: [GENERAL] error while installing auto_explain contrib module

2016-04-19 Thread Tom Lane
e link is happening against. regards, tom lane -- 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] Re: what's the exact command definition in read committed isolation level?

2016-04-18 Thread Tom Lane
ly a function that's marked STABLE or IMMUTABLE will see the same snapshot as the calling query, but a function that's VOLATILE will take a new snapshot for each query it contains. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] $foo $bar is BAD

2016-04-15 Thread Tom Lane
Cool!* > omg, grow up. FWIW, the Jargon File (a/k/a Hackers Dictionary) says that "foo" can be traced back further than "fubar", making the OP's claim rather backwards. In any case, it's an old enough term that nobody is going to give it up on such grounds. http://www.catb.

Re: [GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

2016-04-14 Thread Tom Lane
r each row. It's not; especially not in your originally posted case where the up-conversion happens on the variable not the pseudo-constant. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://

Re: [GENERAL] [HACKERS] sign function with INTERVAL?

2016-04-13 Thread Tom Lane
SQL-accessible. regards, tom lane -- 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] Why is the comparison between timestamp and date so much slower then between two dates

2016-04-13 Thread Tom Lane
is active. You could try it with "localtimestamp" (no parens) instead of "now()" to see how it performs with a non-tz timestamp. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscripti

Re: [GENERAL] pg_upgrade error regarding hstore operator

2016-04-12 Thread Tom Lane
"Feld, Michael (IMS)" <fe...@imsweb.com> writes: > Thanks for the reply Tom. template1 is definitely empty and does not contain > any hstore objects. I did a little debugging and placed the below SQL before > and after the hstore creation in the file produced by the

Re: [GENERAL] Freezing localtimestamp and other time function on some value

2016-04-12 Thread Tom Lane
ion=# select timeofday(); timeofday -- Sun Apr 17 18:01:58.293623 2016 +120 (1 row) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscr

Re: [GENERAL] Freezing localtimestamp and other time function on some value

2016-04-12 Thread Tom Lane
t behavior. As an example, you might accidentally write code that expects two successive transactions to see identical values of now(), and such a testbed wouldn't detect the problem. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgres

Re: [GENERAL] max_stack_depth problem though query is substantially smaller

2016-04-10 Thread Tom Lane
returning hstore". It's quite a different concept from the => notation inside an hstore literal. That is: 'foo'::text => 'bar'::text is not like '"foo" => "bar"'::hstore even though they have the same end result. regards,

Re: [GENERAL] max_stack_depth problem though query is substantially smaller

2016-04-09 Thread Tom Lane
and be processed much more quickly. If you insist on emitting SQL statements that have operators nested to such depths, then yes you'll need to increase max_stack_depth to whatever it takes to allow it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-

Re: [GENERAL] max_stack_depth problem though query is substantially smaller

2016-04-08 Thread Tom Lane
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend regards, tom lane -- 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] max_stack_depth problem though query is substantially smaller

2016-04-08 Thread Tom Lane
g it into iteration with an explicitly-represented state stack. regards, tom lane -- 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] Non-default postgresql.conf values to log

2016-04-07 Thread Tom Lane
ges* in configuration file settings, so I'm not buying the "historical info" angle at all.) regards, tom lane -- 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] Non-default postgresql.conf values to log

2016-04-07 Thread Tom Lane
Alex Ignatov <a.igna...@postgrespro.ru> writes: > My question is: is there any option(s) to log non-default > postgresql.conf values to log file? No, but you can easily find all the non-default settings by querying the pg_settings view. regards, tom lane

Re: [GENERAL] pg_upgrade error regarding hstore operator

2016-04-06 Thread Tom Lane
"Feld, Michael (IMS)" <fe...@imsweb.com> writes: > Thanks for the assist Tom. That worked for us. Noticing a different > issue following the pg_upgrade. If we take a pg_dump of a database on > this upgraded instance with the hstore extension and try to pg_restore > it

Re: [GENERAL] Exclude constraint using custom operator

2016-04-06 Thread Tom Lane
set of supported operator strategies is determined by the code in the opclass support functions (primarily, what the "consistent" function knows how to do). You'd have to go fiddle with that C code before you could add a new operator to the opclass. regards, tom lane

Re: [GENERAL] postgresql 9.3.10, FIPS mode and DRBG issues.

2016-04-04 Thread Tom Lane
ed inadequately secure. So my guess is that psql is trying to configure OpenSSL with some inadequately-secure settings. Not sure why it'd be different from the server though. Are you sure psql and the libpq it's using are same version as the apparently-working server?

Re: [GENERAL] Is it possible to delete a single value from an enum type?

2016-03-31 Thread Tom Lane
hen visiting that index entry. Concerns like this are exactly why there is no ALTER TYPE DELETE VALUE, and probably never will be. If you need a non-fixed set of key values, you're much better off using a foreign key instead of an enum type. regards, tom lane -- Sent

Re: [GENERAL] pg_restore casts check constraints differently

2016-03-30 Thread Tom Lane
I wonder why you don't get an array of text constants in the IN case. regards, tom lane -- 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] Missed LIMIT clause pushdown in FDW API

2016-03-30 Thread Tom Lane
fetch more rows than it actually needs from the remote --- but that's not the same as telling the remote planner to prefer a fast-start plan. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: h

Re: [GENERAL] pg_restore casts check constraints differently

2016-03-29 Thread Tom Lane
"David G. Johnston" <david.g.johns...@gmail.com> writes: > On Tue, Mar 29, 2016 at 2:45 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> It's not really different. What you're seeing is pg_dump (or actually >> ruleutils.c) choosing to dump som

Re: [GENERAL] pg_restore casts check constraints differently

2016-03-29 Thread Tom Lane
tional overparenthesization. regards, tom lane -- 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 quote the COALESCE function?

2016-03-28 Thread Tom Lane
yes, and you already found one good way: if it doesn't have a pg_proc entry then it's a special case of some sort or other. Have you considered only quoting the function name if it actually needs it, ie, contains special characters? regards, tom lane -- Sent via p

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

2016-03-21 Thread Tom Lane
"David G. Johnston" <david.g.johns...@gmail.com> writes: > On Monday, March 21, 2016, Tom Lane <t...@sss.pgh.pa.us> wrote: >> What about just discarding the old format entirely, and printing one of >> these two things: >> >> Timestamp (every

Re: [GENERAL] Slow GIN indexes after bulk insert

2016-03-21 Thread Tom Lane
e more directly useful). regards, tom lane -- 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] [HACKERS] Request - repeat value of \pset title during \watch interations

2016-03-21 Thread Tom Lane
Alvaro Herrera <alvhe...@2ndquadrant.com> writes: > (I'll also use this opportunity to complain again about not being able > to use floating point sleep time.) That's not unreasonable either, though it seems like material for a separate patch. regards, tom lan

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

2016-03-21 Thread Tom Lane
a disruption, knowing > when the last one ran and how often it is supposed to run is useful info to > have at ones fingertips. That's not unreasonable. I just want it to look less weirdly different between the two cases. regards, tom lane -- Sent via pgsql-g

Re: [GENERAL] Slow GIN indexes after bulk insert

2016-03-21 Thread Tom Lane
available in 9.3.) Worthy of note here is that the max pending list size is governed by work_mem, so a large work_mem can make this effect worse. (We got smart and made it an independent setting, but not till 9.5.) regards, tom lane -- Sent via pgsql-general mailing lis

Re: [GENERAL] postgresql timezone and OS localtime correspondence

2016-03-21 Thread Tom Lane
time value rather than setting it manually in red hat family > systems? You could make your own symlink, though I'm unsure whether it'd survive tzdata package updates. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

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

2016-03-21 Thread Tom Lane
My Title (5 s) Mon Mar 21 13:39:25 2016 repeat -- xyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzy (1 row) But I don't care enough to veto it. Anyone else have an opinion?

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

2016-03-21 Thread Tom Lane
eat the magic constant 50 in several places. Also, I think the patch makes do_watch return the wrong result code for the (typical) case where we exit because of query cancel not PSQLexecWatch failure. So on the whole, I'd do it as attached. regards, tom lane diff --git a/

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

2016-03-20 Thread Tom Lane
ed? The message I saw was post-1-March. If it was in fact submitted in time for 2016-03, then we owe it a review. regards, tom lane -- 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] Error: insufficient data in the message

2016-03-19 Thread Tom Lane
And not "pgsql_pexec" either. So this must be coming from some client-side code you're using (not libpq). It's unlikely we can help you much here; you need to chat with the author of the client-side library that's emitting that error. regards, tom lane -- Sen

Re: [GENERAL] spurious /dev/shm related errors on insert

2016-03-19 Thread Tom Lane
for > reading: No such file or directory Offhand I do not believe that any part of the core PG code would attempt to access such a file. Maybe you've got some extensions in there that would do so? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-

Re: [GENERAL] Confusing deadlock report

2016-03-19 Thread Tom Lane
as a side effect of some previous insert? regards, tom lane -- 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] Error: insufficient data in the message

2016-03-19 Thread Tom Lane
Michael Paquier <michael.paqu...@gmail.com> writes: > On Fri, Mar 18, 2016 at 9:00 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> Hmm ... I can't find the string "insufficient data in the message" >> anywhere in the Postgres sources. And not "pgsql_pexec

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

2016-03-18 Thread Tom Lane
David Steele <da...@pgmasters.net> writes: > On 3/17/16 7:00 PM, Tom Lane wrote: >> The message I saw was post-1-March. If it was in fact submitted in >> time for 2016-03, then we owe it a review. > I meant to add the CF record and forgot: > https://commitfest.postgres

Re: [GENERAL] retrieve grouping sets/rollup rows

2016-03-13 Thread Tom Smith
exactly what I am looking for. very nice. Thx On Sun, Mar 13, 2016 at 10:44 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> On 03/13/2016 10:07 PM, Tom Smith wrote: > >>> It would help if the resultset has some param to mark which is which > >>> with the

Re: [GENERAL] retrieve grouping sets/rollup rows

2016-03-13 Thread Tom Lane
>> On 03/13/2016 10:07 PM, Tom Smith wrote: >>> It would help if the resultset has some param to mark which is which >>> with the grouping sets index. I think you're looking for the GROUPING() function. See http://www.postgresql.org/docs/9.5/static/

Re: [GENERAL] retrieve grouping sets/rollup rows

2016-03-13 Thread Tom Smith
t to know if a row is from the (a,b) or (c,d) group? All rows > will contain (a,b,c,d) but (a,b) will be NULL for the (c,d) grouping > sets, and vice-versa. > > Jim > > On 03/13/2016 09:45 PM, Tom Smith wrote: > > Hello: > > > > With JDBC, how can I tell which row i

[GENERAL] retrieve grouping sets/rollup rows

2016-03-13 Thread Tom Smith
Hello: With JDBC, how can I tell which row is for which grouping sets or rollup using result sets Thanks

Re: [GENERAL] "brew services list" shows postgresql as "started", but can not connect to it

2016-03-13 Thread Tom Lane
ok in the log file. Hopefully there is some documentation included with the homebrew PG package that explains that. But if not, perhaps a look into the launch-agent script mentioned above would tell you. Look for something like a -l argument to pg_ctl, or a redirection of stderr into a file.

Re: [GENERAL] OS X 10.11.3, psql, bus error 10, 9.5.1

2016-03-12 Thread Tom Lane
Chris Ruprecht <ch...@cdrbill.com> writes: > no such luck (no easy fix). I turned SIP off and rebuilt PG 9.5.1, copied > libpq.5.8.dylib to /usr/lib, bent the two sym links to the new library but > I'm still getting "Bus error: 10". You owe somebody a nickel, Tom ;).

Re: [GENERAL] OS X 10.11.3, psql, bus error 10, 9.5.1

2016-03-11 Thread Tom Lane
development work. Here's what I have bookmarked about turning it off: http://www.howtogeek.com/230424/how-to-disable-system-integrity-protection-on-a-mac-and-why-you-shouldnt/ If turning off SIP doesn't fix things, we'll need to look closer. regards, tom lane -- Sent

Re: [GENERAL] pg_upgrade error regarding hstore operator

2016-03-08 Thread Tom Lane
ill properly look like it's 1.1 after pg_upgrade'ing. regards, tom lane -- 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] Plan to support predicate push-down into subqueries with aggregates?

2016-03-08 Thread Tom Lane
(It does, on the other hand, know very well that SQL equality operators don't necessarily guarantee bitwise identity.) So I'd suggest just modifying your queries to write out both constraints explicitly. regards, tom lane -- 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] Exclude pg_largeobject form pg_dump

2016-03-08 Thread Tom Lane
ry to dump system catalogs as tables, even if the switches seem to ask it to. regards, tom lane -- 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] Exclude pg_largeobject form pg_dump

2016-03-08 Thread Tom Lane
Andreas Joseph Krogh <andr...@visena.com> writes: > What I'm looking for is "inverse -b" in an otherwise complete dump. Any plans > to add that? [ shrug... ] Nobody ever asked for it before. regards, tom lane -- Sent via pgsql-general maili

Re: [GENERAL] Exclude pg_largeobject form pg_dump

2016-03-08 Thread Tom Lane
lines of pg_dump -t '*' ... although this will result in *all* non-schema-named objects being excluded, I believe, which might be a problem. regards, tom lane -- 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] INDEX ONLY scan with expression index

2016-03-07 Thread Tom Lane
IOS. As a small consolation prize, it might let you get an IOS on cases where you *do* need x as well. regards, tom lane -- 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] index problems (again)

2016-03-07 Thread Tom Lane
ed to a regular index scan. I suspect that it's pricing the IOS very high because a lot of the table is dirty and therefore will have to be visited even in a nominally index-only scan. You might check whether the plan choice changes immediately after a VACUUM of the table.

Re: [GENERAL] index problems (again)

2016-03-07 Thread Tom Lane
Geoff Winkless <pgsqlad...@geoff.dj> writes: > On 7 March 2016 at 14:51, Tom Lane <t...@sss.pgh.pa.us> wrote: >> Because the other way is estimated to be cheaper. The estimate is >> wrong, because it's based on a statistical assumption that's wrong >> (ie that

Re: [GENERAL] index problems (again)

2016-03-07 Thread Tom Lane
ve been fine. regards, tom lane -- 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] arrays returned in text format

2016-03-04 Thread Tom Lane
ity source code. If it does happen, I'd be happy to look into it, because I agree it'd be a bug. But I have other things to spend my time on than reverse-engineering test cases out of code fragments dependent on incompletely-described custom modifications of Postgres. re

Re: [GENERAL] arrays returned in text format

2016-03-04 Thread Tom Lane
a test case that fails in stock community source code, I'll be happy to take a look. regards, tom lane -- 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] arrays returned in text format

2016-03-04 Thread Tom Lane
hat is not the call signature of PQexec(), nor of any of its variants. regards, tom lane -- 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] String literal doesn't autocast to text type

2016-03-04 Thread Tom Lane
e you show with a lot less risk of side-effects elsewhere. But it's not exactly trivial because of interactions with INSERT ... SELECT. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Check constraints for varchar[] and varchar[][] columns in a table

2016-03-04 Thread Tom Lane
ttern on the left. There's no such thing in the core PG distribution, but the only hard part of making your own is figuring out what to name the operator ;-) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscri

Re: [GENERAL] C function migration from 9.2 to 9.5

2016-03-03 Thread Tom Lane
ion of heap_form_tuple somewhere along the line.) A general tip for getting C code to work is to turn on as many compiler warnings as you can, and never ignore any of them. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make ch

Re: [GENERAL] space required before negative

2016-03-03 Thread Tom Lane
only characters used in SQL operator names. It would likely have been cleaner to just disallow operator names ending in "+" or "-", but we had several long-established names that failed to conform to that, so this was the best compromise we could come up with between flexibil

Re: [GENERAL] C function migration from 9.2 to 9.5

2016-03-03 Thread Tom Lane
ynamic linker does about it. What exactly does the crash look like --- anything interesting in the postmaster log? (If your logging setup fails to capture postmaster stderr, now would be a good time to fix that.) Have you tried to get a back-trace with gdb? regards, tom lane

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

2016-03-02 Thread Tom Lane
all. That doesn't seem like an improvement either. regards, tom lane -- 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] pg_upgrade 9.5.1: pg_upgrade_support missing

2016-03-02 Thread Tom Lane
XISTS binary_upgrade CASCADE; though you'll probably have to do that in every database of the installation. regards, tom lane -- 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] Issue enabling track_counts to launch autovacuum in 9.4.5

2016-03-02 Thread Tom Lane
configuration > 2016-03-02 14:58:09 EST [14366]: [11-1] HINT: Enable the "track_counts" > option. Or both changes, or something else entirely? I'd be interested to hear how you perceived these log messages and what you think might help the next person.

Re: [GENERAL] Rules on View

2016-03-01 Thread Tom Lane
w without ON SELECT? regards, tom lane -- 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] ERROR: cannot convert relation containing dropped columns to view

2016-02-29 Thread Tom Lane
rlier, I doubt anyone wants to invest the work. So I'm just going to go improve the comment and error message and leave it at that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

<    4   5   6   7   8   9   10   11   12   13   >