Re: [HACKERS] elegant and effective way for running jobs inside a database

2012-03-05 Thread David Johnston
Keep in mind that it's not about coding in C but mostly about figuring out what a sane design out to look like. While I can straddle the fence pretty my first reaction is that we are talking about application functionality that falls outside what belongs in core PostgreSQL. I'd

Re: [HACKERS] Parser - Query Analyser

2012-11-17 Thread David Johnston
On Nov 17, 2012, at 9:18, Michael Giannakopoulos miccagi...@gmail.com wrote: Hello guys, My name is Michail Giannakopoulos and I am a graduate student at University of Toronto. I have no previous experience in developing a system like postgreSQL before. What I am trying to explore is

Re: [HACKERS] Materialized views WIP patch

2012-11-27 Thread David Johnston
On Nov 27, 2012, at 5:25, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: So my proposal for the current feature would be: ALTER MATERIALIZED VIEW mv UPDATE [ CONCURRENTLY ]; UPDATE MATERIALIZED VIEW mv; The choice of keywords and syntax here hopefully clearly hint the user about the

Re: [HACKERS] Materialized views WIP patch

2012-11-27 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Dimitri Fontaine Sent: Tuesday, November 27, 2012 10:03 AM To: Kevin Grittner Cc: Pavel Stehule; Peter Eisentraut; Pgsql Hackers Subject: Re: [HACKERS] Materialized

Re: [HACKERS] enhanced error fields

2012-12-10 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Peter Geoghegan Sent: Monday, December 10, 2012 3:29 PM To: Pavel Stehule Cc: PostgreSQL Hackers; Alvaro Herrera; Tom Lane Subject: Re: [HACKERS] enhanced error

Re: [HACKERS] Review of Row Level Security

2012-12-19 Thread David Johnston
The more secure behavior is to allow entry of data which will not be visible by the person doing the entry. I don't think it is that simple. Allowing inserts without regard for row level restrictions makes it far easier to probe for data. E.g. by inserting rows and checking for unique

Re: [HACKERS] PL/PgSQL STRICT

2012-12-21 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Marko Tiikkaja Sent: Friday, December 21, 2012 10:53 AM To: Tom Lane Cc: PostgreSQL-development Subject: Re: [HACKERS] PL/PgSQL STRICT On 12/21/12 4:49 PM, I

Re: [HACKERS] count(*) of zero rows returns 1

2013-01-14 Thread David Johnston
Tom Lane-2 wrote Gurjeet Singh lt; singh.gurjeet@ gt; writes: Can somebody explain why a standalone count(*) returns 1? postgres=# select count(*); count --- 1 (1 row) The Oracle equivalent of that would be SELECT count(*) FROM dual. Does it make more sense to you thought

Re: [HACKERS] Covering Indexes

2012-07-17 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of David E. Wheeler Sent: Tuesday, July 17, 2012 11:55 AM To: Simon Riggs Cc: Pg Hackers Subject: Re: [HACKERS] Covering Indexes On Jul 17, 2012, at 5:32 PM, Simon

Re: [HACKERS] strange evaluation Window function and SRF functions?

2012-07-30 Thread David Johnston
On Jul 30, 2012, at 12:33, Thom Brown t...@linux.com wrote: On 30 July 2012 17:19, Pavel Stehule pavel.steh...@gmail.com wrote: Hello I seen nice trick based on window function http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql but isn't it example of

Re: [HACKERS] temporal support patch

2012-08-20 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Robert Haas Sent: Monday, August 20, 2012 5:04 PM To: Jeff Davis Cc: Vlad Arkhipov; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] temporal support patch On

Re: [HACKERS] temporal support patch

2012-08-25 Thread David Johnston
-Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Saturday, August 25, 2012 12:46 PM To: David Johnston Cc: Jeff Davis; Vlad Arkhipov; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] temporal support patch On Mon, Aug 20, 2012 at 7:17 PM, David Johnston

Re: [HACKERS] Invalid optimization of VOLATILE function in WHERE clause?

2012-09-19 Thread David Johnston
-Original Message- There really needs to be some way to specify that when an expression is evaluated for each row in a set, a function used within that expression is not optimized away for some rows. Fortunately we have a way:

Re: [HACKERS] Invalid optimization of VOLATILE function in WHERE clause?

2012-09-19 Thread David Johnston
-Original Message- | VOLATILE indicates that the function value can change even within a | single table scan, so no optimizations can be made. | Relatively few database functions are volatile in this sense; some | examples are random(), [...] What are the arguments against

Re: [HACKERS] Invalid optimization of VOLATILE function in WHERE clause?

2012-09-19 Thread David Johnston
-Original Message- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: Wednesday, September 19, 2012 5:51 PM To: k...@rice.edu; David Johnston Cc: 'Florian Schoppmann'; 'Robert Haas'; pgsql-hackers@postgresql.org; 'Tom Lane' Subject: RE: [HACKERS] Invalid optimization

Re: [HACKERS] Oid registry

2012-09-27 Thread David Johnston
I did like the alternative idea upthread of UUIDs for types which would give them a virtually unlimited space. Yeah, me too. That doesn't require a centralized authority (hence, no debates here about whether a given extension is important enough to merit an allocation of a given size),

Re: [HACKERS] Raise a WARNING if a REVOKE affects nothing?

2012-10-02 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Noah Misch Sent: Tuesday, October 02, 2012 3:02 PM To: Craig Ringer Cc: PostgreSQL Hackers Subject: Re: [HACKERS] Raise a WARNING if a REVOKE affects nothing? On

Re: [HACKERS] Deprecating RULES

2012-10-11 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Andrew Dunstan Sent: Thursday, October 11, 2012 8:52 PM To: Daniel Farina Cc: Joshua D. Drake; Josh Berkus; Simon Riggs; pgsql-hackers@postgresql.org Subject: Re:

Re: [HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread David Johnston
On Oct 12, 2012, at 22:13, Stephen Frost sfr...@snowman.net wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: [ shrug... ] You're attacking a straw man, or more precisely putting words into my mouth about what the percentage-based thresholds might be. Notice the examples I gave involved update

Re: [HACKERS] [WIP] pg_ping utility

2012-10-15 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Tom Lane Sent: Monday, October 15, 2012 7:13 PM To: Andres Freund Cc: pgsql-hackers@postgresql.org; Thom Brown; Phil Sorber Subject: Re: [HACKERS] [WIP] pg_ping

Re: [HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows

2012-10-21 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Abhijit Menon-Sen Sent: Sunday, October 21, 2012 5:45 AM To: Tom Lane Cc: P. Christeas; pgsql-hackers@postgresql.org Subject: [HACKERS] Re: [PATCH] Enforce that

Re: [HACKERS] Deprecating RULES

2012-10-22 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Merlin Moncure Sent: Monday, October 22, 2012 6:54 PM To: Robert Haas Cc: Andrew Dunstan; Josh Berkus; Daniel Farina; pgsql- hack...@postgresql.org Subject: Re:

Re: [HACKERS] Creating indexes in the background

2012-10-29 Thread David Johnston
On Oct 27, 2012, at 19:20, David Lee davidomu...@gmail.com wrote: Hey folks, It seems like right now when you want to create an index concurrently, the index creation will get canceled if you cancel the statement (i.e. you must keep your statement open). Is there a way to launch an

Re: [HACKERS] Add contrib module functions to docs' function index

2012-11-13 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Craig Ringer Sent: Tuesday, November 13, 2012 6:15 PM To: PostgreSQL Hackers Subject: [HACKERS] Add contrib module functions to docs' function index Hi all

Re: [HACKERS] Switching to Homebrew as recommended Mac install?

2012-04-01 Thread David Johnston
On Apr 1, 2012, at 21:50, Jay Levitt jay.lev...@gmail.com wrote: Tom Lane wrote: While you might not like the EDB installer, at least those folks are active in the lists and accountable for whatever problems their code has. Who in heck is responsible for the homebrew packaging, and do they

Re: [HACKERS] Switching to Homebrew as recommended Mac install?

2012-04-01 Thread David Johnston
On Apr 1, 2012, at 13:14, Jay Levitt jay.lev...@gmail.com wrote: The Mac installation docs currently recommend the EDB one-click installer as the first choice. While this does install pgadmin and some other refinements, it also is fairly confusing to troubleshoot: The items are not

Re: [HACKERS] 9.2 release notes, beta time?

2012-04-27 Thread David Johnston
On Apr 27, 2012, at 21:24, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: On Fri, Apr 27, 2012 at 09:10:54PM -0400, Tom Lane wrote: How would that help? The bottleneck is packaging, which is the same work whatever we call it. We release the alpha with no

Re: [HACKERS] precision and scale functions for numeric

2012-04-30 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Robert Haas Sent: Monday, April 30, 2012 2:20 PM To: Peter Eisentraut Cc: pgsql-hackers Subject: Re: [HACKERS] precision and scale functions for numeric I think

Re: [HACKERS] proposal: additional error fields

2012-05-01 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Peter Geoghegan Sent: Tuesday, May 01, 2012 4:37 PM To: Tom Lane Cc: Pavel Stehule; PostgreSQL Hackers Subject: Re: [HACKERS] proposal: additional error fields On

Re: [HACKERS] JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ?

2012-05-01 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Hannu Krosing Sent: Tuesday, May 01, 2012 5:29 PM The reason I am whining now is that with minor adjustments in implementation it could all be made much more

Re: [HACKERS] proposal: additional error fields

2012-05-01 Thread David Johnston
On May 1, 2012, at 20:05, Robert Haas robertmh...@gmail.com wrote: On Tue, May 1, 2012 at 4:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: I continue to maintain that the SQLSTATE is a much better basis for solving this problem. Its categories are already pretty close to what Peter needs:

Re: [HACKERS] JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ?

2012-05-01 Thread David Johnston
On May 1, 2012, at 20:41, Hannu Krosing ha...@2ndquadrant.com wrote: Most people don't work in strongly-typed environment, and thus would work around such restriction if they need a simple JSON value at the other end of the interchange. My personal take it is have it fail since any

Re: [HACKERS] JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ?

2012-05-04 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Hannu Krosing Sent: Friday, May 04, 2012 4:40 PM To: Robert Haas Cc: Tom Lane; Andrew Dunstan; PostgreSQL-development; Merlin Moncure Subject: Re: [HACKERS] JSON in

Re: [HACKERS] Possible error in psql or Postgres?

2012-06-12 Thread David Johnston
On Jun 12, 2012, at 15:21, Dusan Misic promi...@gmail.com wrote: Is this normal Postgres / psql behavior? griffindb=# \d system.user; Table system.user Column | Type | Modifiers

Re: [HACKERS] We probably need autovacuum_max_wraparound_workers

2012-06-27 Thread David Johnston
On Jun 27, 2012, at 22:00, Josh Berkus j...@agliodbs.com wrote: Folks, Yeah, I can't believe I'm calling for *yet another* configuration variable either. Suggested workaround fixes very welcome. The basic issue is that autovacuum_max_workers is set by most users based on autovac's

Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries

2011-06-17 Thread David Johnston
On Wed, Feb 9, 2011 at 4:50 AM, Thom Brown t...@linux.com wrote: On 9 February 2011 02:11, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 8, 2011 at 8:30 PM, Andrew Dunstan and...@dunslane.net wrote: Quite right, but the commitfest manager isn't meant to be a substitute for one.

Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions

2011-07-11 Thread David Johnston
Christopher Browne wrote: Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1, there's a pretty good reason NOT to support that, namely that this breaks relational handling of tables. PostgreSQL is a *relational* database system, hence it's preferable for structures to be

Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions

2011-07-11 Thread David Johnston
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug f...@phlo.org wrote: On Jul11, 2011, at 07:08 , Darren Duncan wrote: Christopher Browne wrote: Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1, there's a pretty good reason NOT to support that, namely that this breaks

Re: [HACKERS] Unsigned integer types

2013-05-28 Thread David Johnston
Maciej Gajewski wrote I'm also afraid that with the extension I'd be left on my own maintaining it forever. While if this could go into the core product, it would live forever. Clarification from the gallery: are we talking an extension or a custom PostgreSQL build/fork? If it is an extension

Re: [HACKERS] Bad error message on valuntil

2013-06-07 Thread David Johnston
Tom Lane-2 wrote Joshua D. Drake lt; jd@ gt; writes: I had a customer pulling their hair out today because they couldn't login to their system. The error was consistently: 2013-06-07 08:42:44 MST postgres 10.1.11.67 27440 FATAL: password authentication failed for user user However

Re: [HACKERS] Bad error message on valuntil

2013-06-07 Thread David Johnston
Joshua D. Drake wrote On 06/07/2013 12:31 PM, Tom Lane wrote: Joshua D. Drake lt; jd@ gt; writes: On 06/07/2013 11:57 AM, Tom Lane wrote: I think it's intentional that we don't tell the *client* that level of detail. Why? That seems rather silly. The general policy on authentication

Re: [HACKERS] Why can't I use windowing functions over ordered aggregates?

2013-06-21 Thread David Johnston
Cédric Villemain-2 wrote And also, first_value is a *window* function, not a simple aggregate function... Per the documentation any aggregate function can be used with a WINDOW declaration. The logical question is why are window aggregates special so that the reverse cannot be true? In other

Re: [HACKERS] Kudos for Reviewers -- straw poll

2013-06-25 Thread David Johnston
Brendan Jurd wrote On 26 June 2013 03:17, Josh Berkus lt; josh@ gt; wrote: How should reviewers get credited in the release notes? a) not at all b) in a single block titled Reviewers for this version at the bottom. c) on the patch they reviewed, for each patch I think some consideration

Re: [HACKERS] column b is of type X but expression is of type text

2013-07-12 Thread David Johnston
Benedikt Grundmann wrote A third party application we use generates SQL queries. Here is query it generated that broke today and for which I have a hard time arguing that the postgres behavior is correct (minimally the error message is confusing): =# create temporary table foo (b double

Re: [HACKERS] column b is of type X but expression is of type text

2013-07-12 Thread David Johnston
Josh Berkus wrote On 07/12/2013 07:28 AM, Benedikt Grundmann wrote: Thanks David, I like the fact that postgres is explicit in it's types. All I'm arguing is that error message is misleading. And that I had a hard time understanding why happened what happened. The part I was missing is

Re: [HACKERS] A general Q about index

2013-07-16 Thread David Johnston
soroosh sardari wrote Hi I want to know how an index is created and used. actually if you can show to me a simple start point, it would be great. Regards, Soroosh Sardari In the documentation there is a table of contents and listed on that table is a section named Indexes. David J.

Re: [HACKERS] A general Q about index

2013-07-16 Thread David Johnston
David Johnston wrote soroosh sardari wrote Hi I want to know how an index is created and used. actually if you can show to me a simple start point, it would be great. Regards, Soroosh Sardari In the documentation there is a table of contents and listed on that table is a section

[HACKERS] Re: How to configer the pg_hba record which the database name with \n ?

2013-08-01 Thread David Johnston
huxm wrote where there is a newline(\n) in the name. I can't imagine why you would want to use non-printing characters in a name, especially a database name. Even if the hba.conf file was able to interpret it (which it probably cannot but I do not know for certain) client interfaces are

[HACKERS] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-01 Thread David Johnston
Minor request: could someone enlighten me as to why making the directory location a compile-time option is undesirable. Packagers then can setup whatever structure they desire when they compile their distributions. In which case the discussion becomes what is a reasonable default and that can be

[HACKERS] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-01 Thread David Johnston
Andres Freund-3 wrote Even trying to do this completely will guarantee that this patch will never, ever, suceed. There simply is no way to reliably detect problems that have complex interactions with the rest of the system. We can improve the detection rate of problems after some real world

[HACKERS] Re: File-per-GUC WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-05 Thread David Johnston
Josh Berkus wrote (this discussion concerns issue (D), file-per-setting vs. one-big-file) So the case of two sessions both modifying ALTER SYSTEM SET, and one succeeding for some-but-all-GUCS, and the other succeeding for some-but-not-all-GUCs, would not be user-friendly or pretty, even if

[HACKERS] Re: Doc Patch: Subquery section to say that subqueries can't modify data

2013-08-06 Thread David Johnston
Instead of simply expanding the section on sub-queries, which may still be worthwhile, it seems that we have effectively introduced a new kind of query - namely one that mixes both query DDL and update DDL into a kind of hybrid query. An entire section describing the means to implement these

Re: [HACKERS] Variadic aggregates vs. project policy

2013-08-30 Thread David Johnston
Tom Lane-2 wrote Pavel Stehule lt; pavel.stehule@ gt; writes: I was one who sent a bug report - this error is not too dangerous, but it is hidden, and difficult to find, if you don't know what can be happen. Same as bug with plpgsql and SQL identifier collisions. If you understand, then

Re: [HACKERS] Variadic aggregates vs. project policy

2013-08-30 Thread David Johnston
Andres Freund-3 wrote On 2013-08-30 06:34:47 -0700, David Johnston wrote: Tom Lane-2 wrote I was one who sent a bug report - this error is not too dangerous, but it is hidden, and difficult to find, if you don't know what can be happen. Same as bug with plpgsql and SQL identifier

Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME

2013-09-02 Thread David Johnston
Jeff Davis-8 wrote Is there any semantic difference between marking a constraint as DISABLED and simply dropping it? Or does it just make it easier to re-add it later? I cannot answer the question but if there is none then the main concern I'd have is capturing meta-information about WHY such

Re: [HACKERS] 9.3 RC1 psql encoding reporting inconsistently?

2013-09-02 Thread David Johnston
Tom Lane-2 wrote Michael Nolan lt; htfoot@ gt; writes: This is 9.3 RC1 on a Fedora 7 system. Why does \l report the encoding as SQL_ASCII and \set report it as UTF8? psql sets client_encoding based on its environment (LANG or related variables). That's been true for some time --- since

Re: [HACKERS] Question regarding Sync message and unnamed portal

2013-09-10 Thread David Johnston
Tatsuo Ishii-4 wrote From these statements, I would think #4 will fail in the following sequence of commands because #3 closes transaction and it destroys unnamed portal: 1)Parse/Bind creates unnamed portal, 2)Parse/Bind/Execute creates named portal and executes, 3)Send Sync message

Re: [HACKERS] Question regarding Sync message and unnamed portal

2013-09-10 Thread David Johnston
Continuing my novice interpretation from before... Tatsuo Ishii-4 wrote It would be nice if something like unnamed portal will be destroyed by a Sync message if you are in an explicit transaction is in our manual. I do not believe this to be true from what I've scanned. Inside an implicit

Re: [HACKERS] Pending query cancel defeats SIGQUIT

2013-09-10 Thread David Johnston
Noah Misch-2 wrote The errfinish() pertaining to that WARNING issues CHECK_FOR_INTERRUPTS(), and the query cancel pending since before the SIGQUIT arrived then takes effect. This is less bad on 9.4, because the postmaster will SIGKILL the backend after 5s. On older releases, the backend

Re: [HACKERS] Weaker shmem interlock w/o postmaster.pid

2013-09-11 Thread David Johnston
Noah Misch-2 wrote I'm thinking to preserve postmaster.pid at immediate shutdown in all released versions, but I'm less sure about back-patching a change to make PGSharedMemoryCreate() pickier. On the one hand, allowing startup to proceed with backends still active in the same data

Re: [HACKERS] Questions about checksum feature in 9.3

2013-09-16 Thread David Johnston
Ants Aasma-2 wrote So, has anyone compiled checksum vectorized on OS X? Are there any performance data that would indicate whether or not I should worry with this in the first place? Even without vectorization the worst case performance hit is about 20%. This is for a workload that is fully

Re: [HACKERS] Not In Foreign Key Constraint

2013-09-18 Thread David Johnston
Misa Simic wrote I guess that rule can be achieved with triigers on TableA and TableC - but the same is true for FK (and FK constraint is more effective then trigger - that is why I wonder would it be useful/achievable to create that kind of constraint) Thoughts, ideas? You create a

Re: [HACKERS] Not In Foreign Key Constraint

2013-09-19 Thread David Johnston
Misa Simic wrote Hi hackers, I just wonder how hard would be to implement something like Not In FK Constraint or opposite to FK... A more useful couple next sentences would be along the lines of: I have this problemI've approached it by doingbut it seems that an actual database

Re: [HACKERS] Documentation for SET var_name FROM CURRENT

2013-09-30 Thread David Johnston
Amit Kapila-2 wrote While reading documentation for SET command, I observed that FROM CURRENT syntax and its description is missing from SET command's syntax page (http://www.postgresql.org/docs/devel/static/sql-set.html). Do you think that documentation should be updated for the same or is

Re: [HACKERS] Documentation for SET var_name FROM CURRENT

2013-10-01 Thread David Johnston
Amit Kapila-2 wrote On Tue, Oct 1, 2013 at 10:25 AM, David Johnston lt; polobo@ gt; wrote: Amit Kapila-2 wrote While reading documentation for SET command, I observed that FROM CURRENT syntax and its description is missing from SET command's syntax page (http://www.postgresql.org/docs

Re: [HACKERS] Documentation for SET var_name FROM CURRENT

2013-10-01 Thread David Johnston
David Johnston wrote A paragraph cross-referencing where SET sub-commands exist has merit but since the main SET command does not accept FROM CURRENT it (FC) should not be included in its page directly. It is strange that this actually does work - at least in 9.0 - given that SET ... FROM

[HACKERS] Re: dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread David Johnston
Robert Haas wrote Unfortunately, the buildfarm isn't entirely happy with this decision. On buildfarm member anole (HP-UX B.11.31), allocation of dynamic shared memory fails with a Permission denied error, and on smew (Debian GNU/Linux 6.0), it fails with Function not implemented, which

[HACKERS] Re: Using indexes for ORDER BY and PARTITION BY clause in windowing functions

2013-10-23 Thread David Johnston
Sameer Kumar wrote edb=# explain analyze select max(score) from student_score group by course; This query returns 6 records. The window one returns 123,000. Why do you expect these to have anywhere near the same performance or plan? You can enable/disable indexes/scans to see what

Re: [HACKERS] Additional information on log_line_prefix

2013-10-25 Thread David Johnston
emanuel_calvo wrote %E = estimated rows How would you expect this to work? This information seems mostly useless without the context of a full EXPLAIN output. %T = temporal tables used I am guessing you mean temporary, not temporal - the later also being known as time oriented David J.

Re: [HACKERS] How should row-security affects ON UPDATE RESTRICT / CASCADE ?

2013-10-29 Thread David Johnston
Tom Lane-2 wrote Craig Ringer lt; craig@ gt; writes: During my testing of Kohei KaiGai's row-security patches I've been looking into how foreign keys should be and are handled. There are some interesting wrinkles around FK cascades, the rights under which FK checks execute, and about the

Re: [HACKERS] Feature request: Optimizer improvement

2013-11-01 Thread David Johnston
Jim Nasby-2 wrote Should that really matter in this case? ISTM we should always handle LIMIT before moving on to the SELECT clause…? SELECT generate_series(1,10) LIMIT 1 David J. -- View this message in context:

Re: [HACKERS] logical column order and physical column order

2013-11-03 Thread David Johnston
David Rowley wrote I'm sure in the real world there are many cases where a better choice in column ordering would save space and save processing times, but is this something that we want to leave up to our users? Right now there is little visibility, from probably 99% of people, that this is

Re: [HACKERS] TABLE not synonymous with SELECT * FROM?

2013-11-11 Thread David Johnston
Colin 't Hart wrote Methinks we should fix the documentation, something like: The command TABLE name is equivalent to SELECT * FROM name It can be used as a top-level command or as a space-saving syntax variant in parts of complex queries. Only the WITH, ORDER BY, LIMIT, and

Re: [HACKERS] pg_dump and pg_dumpall in real life

2013-11-11 Thread David Johnston
Andrew Dunstan wrote A general ability to rename things would be good. In particular, restoring schema x into schema y or table x into table y would be very useful, especially if you need to be able to compare old with new. compare old and new what? I would imagine that schema comparisons

Re: [HACKERS] pg_dump and pg_dumpall in real life

2013-11-11 Thread David Johnston
Josh Berkus wrote Well, then we just need pg_restore to handle the role already exists error message gracefully. That's all. Or a CREATE ROLE IF NOT EXISTS statement, and use that for roles. My only qualm here is if the exists check is based off of role name only. If database A and database

Re: [HACKERS] Transaction-lifespan memory leak with plpgsql DO blocks

2013-11-12 Thread David Johnston
Robert Haas wrote That's a sufficiently astonishing result that it wouldn't be surprising for this to get reported as a bug where a simple performance gap wouldn't be, and I think if we don't fix it the perception will be that we've left that bug unfixed. Now, there are lots of things we

Re: [HACKERS] First patch -- somewhat trivial feature

2013-11-13 Thread David Johnston
Stephen Frost wrote * Robert Berry ( berrydigital@ ) wrote: This is my first attempt at writing a patch, so it's pretty simple. Neat! seq_page: 1.00, rnd_page: 4.00, cpu_tup: 0.01, cpu_ind: 0.005000, cpu_op: 0.002500 amenabled: 111

Re: [HACKERS] additional json functionality

2013-11-14 Thread David Johnston
Hannu Krosing-5 wrote On 11/14/2013 08:17 PM, Merlin Moncure wrote: On Thu, Nov 14, 2013 at 11:34 AM, David E. Wheeler lt; david@ gt; wrote: On Nov 14, 2013, at 7:07 AM, Merlin Moncure lt; mmoncure@ gt; wrote: This is exactly what needs to be done, full stop (how about: hstore). It

Re: [HACKERS] additional json functionality

2013-11-15 Thread David Johnston
Merlin Moncure-2 wrote I don't want to have two types, but I think I'd probably rather have two clean types than this. I can't imagine it being remotely acceptable to have behaviour depend in whether or not something was ever stored, which is what this looks like. Well, maybe so. My main

Re: [HACKERS] additional json functionality

2013-11-15 Thread David Johnston
Josh Berkus wrote On 11/15/2013 02:59 PM, Merlin Moncure wrote: On Fri, Nov 15, 2013 at 4:31 PM, Hannu Krosing lt; hannu@ gt; wrote: I think you may be on to something here. This might also be a way opt-in to fast(er) serialization (upthread it was noted this is unimportant; I'm

Re: [HACKERS] additional json functionality

2013-11-15 Thread David Johnston
Looking at this a different way: could we just implement BSON and leave json alone? http://bsonspec.org/ David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/additional-json-functionality-tp5777975p5778656.html Sent from the PostgreSQL - hackers mailing list

Re: [HACKERS] additional json functionality

2013-11-17 Thread David Johnston
David E. Wheeler-3 wrote I like JSONB because: 1. The B means binary 2. The B means second 3. It's short 4. See also BYTEA. json_strict : Not sure about the bytea reference off-hand... I was pondering jsons which meets the short property just fine and the trailing s would stand for

[HACKERS] Re: Suggestion: Issue warning when calling SET TRANSACTION outside transaction block

2013-11-18 Thread David Johnston
Bruce Momjian wrote Considering we are doing this outside of a transaction, and WARNING or ERROR is pretty much the same, from a behavioral perspective. Should we change this and LOCK to be a warning? From the calling application's perspective an error and a warning are definitely

[HACKERS] Re: Suggestion: Issue warning when calling SET TRANSACTION outside transaction block

2013-11-18 Thread David Johnston
Bruce Momjian wrote On Mon, Nov 18, 2013 at 05:05:45PM -0800, David Johnston wrote: Bruce Momjian wrote Considering we are doing this outside of a transaction, and WARNING or ERROR is pretty much the same, from a behavioral perspective. Should we change this and LOCK to be a warning

[HACKERS] Re: Suggestion: Issue warning when calling SET TRANSACTION outside transaction block

2013-11-18 Thread David Johnston
Bruce Momjian wrote On Mon, Nov 18, 2013 at 06:30:32PM -0800, David Johnston wrote: Personally, I am fine with changing them all to WARNING. Error makes more sense if the goal is internal consistency. That goal should be subservient to backward compatibility. Changing LOCK to warning

[HACKERS] Re: Suggestion: Issue warning when calling SET TRANSACTION outside transaction block

2013-11-19 Thread David Johnston
Robert Haas wrote On Mon, Nov 18, 2013 at 9:07 PM, Bruce Momjian lt; bruce@ gt; wrote: Well, ERROR is what LOCK returns, so if we change SET TRANSACTION to be WARNING, we should change LOCK too, so on backward-compatibility grounds, ERROR makes more sense. Personally, I am fine with

[HACKERS] Re: Suggestion: Issue warning when calling SET TRANSACTION outside transaction block

2013-11-19 Thread David Johnston
Tom Lane-2 wrote David Johnston lt; polobo@ gt; writes: Robert Haas wrote I don't think it's worth breaking backward compatibility. I'm not entirely sure what I would have decided here in a vacuum, but at this point existing precedent seems determinative. Well, at this point we have

Re: [HACKERS] additional json functionality

2013-11-19 Thread David Johnston
Andrew Dunstan wrote Given that, I'm not sure we shouldn't permit them in b) either. I think I lost that argument back in the 9.2 dev cycle. I really don't want to get to a situation where foo::json::jsonb can produce an error. So what do you propose happens when the input json has duplicate

Re: [HACKERS] additional json functionality

2013-11-19 Thread David Johnston
Gavin Flower-2 wrote More seriously, there are obviously variants in what people consider useful human readable form of JSON output, but it is probably inefficient to store white space. Enough to matter? Maybe the extra whitespace causes a marginal value to be toasted but, IIUC, for a

Re: [HACKERS] additional json functionality

2013-11-20 Thread David Johnston
Hannu Krosing-3 wrote On 11/18/2013 06:49 PM, Josh Berkus wrote: On 11/18/2013 06:13 AM, Peter Eisentraut wrote: On 11/15/13, 6:15 PM, Josh Berkus wrote: Thing is, I'm not particularly concerned about *Merlin's* specific use case, which there are ways around. What I am concerned about is that

Re: [HACKERS] WITH ORDINALITY versus column definition lists

2013-11-20 Thread David Johnston
Tom Lane-2 wrote It seems to me that we don't really want this behavior of the coldeflist not including the ordinality column. It's operating as designed, maybe, but it's unexpected and confusing. We could either 1. Reinsert HEAD's prohibition against directly combining WITH ORDINALITY

Re: [HACKERS] WITH ORDINALITY versus column definition lists

2013-11-20 Thread David Johnston
Tom Lane-2 wrote David Johnston lt; polobo@ gt; writes: Tom Lane-2 wrote It seems to me that we don't really want this behavior of the coldeflist not including the ordinality column. It's operating as designed, maybe, but it's unexpected and confusing. We could either 1. Reinsert

Re: [HACKERS] WITH ORDINALITY versus column definition lists

2013-11-20 Thread David Johnston
Tom Lane-2 wrote David Johnston lt; polobo@ gt; writes: Just to clarify we are still allowing simple aliasing: select * from generate_series(1,2) with ordinality as t(f1,f2); Right, that works (and is required by spec, I believe). It's what to do with our column-definition-list

Re: [HACKERS] UNNEST with multiple args, and TABLE with multiple funcs

2013-11-20 Thread David Johnston
Robert Haas wrote select * from table(array(select generate_series(10,20,5)), array['fred','jim']); Can we have our arrays and eat our functions too? (and is someone willing to bake such a complicated cake...) select * from table ( ARRAY | FUNCTION/SET [, ARRAY | FUNCTION/SET ]* ) The

Re: [HACKERS] UNNEST with multiple args, and TABLE with multiple funcs

2013-11-20 Thread David Johnston
Tom Lane-2 wrote Andrew Gierth lt; andrew@.org gt; writes: Tom == Tom Lane lt; tgl@.pa gt; writes: Tom and this would result in producing the array elements as a table Tom column. There is nothing in there about a function returning Tom set. In the spec, there is no such thing

Re: [HACKERS] UNNEST with multiple args, and TABLE with multiple funcs

2013-11-20 Thread David Johnston
Tom Lane-2 wrote We could conceivably say that we'll implicitly UNNEST() if the function returns array, and not otherwise --- but that seems pretty inconsistent and surprise-making to me. The use-cases for putting a scalar array returning function call into a TABLE construct, and NOT wanting

Re: [HACKERS] Why is UPDATE with column-list syntax not implemented

2013-11-21 Thread David Johnston
AK wrote 9.3 documentation says: According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select: UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM

Re: [HACKERS] why semicolon after begin is not allowed in postgresql?

2013-11-25 Thread David Johnston
AK wrote Kevin, I do see your logic now, but this thing is a common mistake - it means that this seems counter-intuitive to some people. What would happen if we applied Occam's razor and just removed this rule? All existing code would continue to work as is, and we would have one less

Re: [HACKERS] why semicolon after begin is not allowed in postgresql?

2013-11-25 Thread David Johnston
Mark Kirkwood-2 wrote Postgres supports many procedural languages (e.g plperl, plpython) and all these have different grammar rules from SQL - and from each other. We can't (and shouldn't) try altering them to be similar to SQL - it would defeat the purpose of providing a procedural

Re: [HACKERS] why semicolon after begin is not allowed in postgresql?

2013-11-25 Thread David Johnston
Andrew Dunstan wrote On 11/25/2013 06:13 PM, David Johnston wrote: A side observation: why does DECLARE not require a block-end keyword but instead BEGIN acts as effectively both start and end? BEGIN, IF, FOR, etc... all come in pairs but DECLARE does not. A complete block

  1   2   >