Re: [HACKERS] sql2008 diff sql2003

2008-09-08 Thread Andrew Gierth
Alvaro == Alvaro Herrera [EMAIL PROTECTED] writes: Alvaro Wow, this is really horrid: Alvaro # F856 through F859: FETCH FIRST clause in subqueries, Alvaro views, and query expressions. The SQL:2008 syntax for Alvaro restricting the rows of a result set is FETCH FIRST, rather Alvaro

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

2008-09-08 Thread Andrew Gierth
Jeff == Jeff Davis [EMAIL PROTECTED] writes: Jeff * Mutual Recursion: This limitation isn't at all uncommon in other implementations; DB2 docs for example say: If more than one common table expression is defined in the same statement, cyclic references between the common table expressions are

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

2008-09-08 Thread Andrew Gierth
Jeff == Jeff Davis [EMAIL PROTECTED] writes: Jeff Aggregates should be blocked according to the standard. Jeff Also, causes an infinite loop. This should be fixed for 8.4. Does the standard require anywhere that non-conforming statements must be diagnosed? (seems impractical, since it

Re: [HACKERS] SQL standard question about Common Table Expressions

2008-09-08 Thread Andrew Gierth
Jeff == Jeff Davis [EMAIL PROTECTED] writes: Jeff I am looking into the SQL standard to try to determine Jeff precisely how the CTE feature should behave. Jeff Taking a simple case like: Jeff with recursive Jeff foo(i) as Jeff (values(1) Jeff union all Jeff

Re: [HACKERS] sql2008 diff sql2003

2008-09-08 Thread Andrew Gierth
Alvaro == Alvaro Herrera [EMAIL PROTECTED] writes: so it's like this: select * from foo order by bar offset 5 rows fetch first 10 rows only; Alvaro Oh, I see -- it's just a cumbersome way to have our LIMIT Alvaro clause. What's the ONLY for? It seems to be just a mandatory noise

[HACKERS] Hint bits vs. OS readahead

2008-12-05 Thread Andrew Gierth
While waiting for a large restore to complete (and investigating why parts of it were so slow), I came across this scenario. This isn't quite the same as some previous discussion of hint bits, but I thought it was something that could probably be taken into account in future. This also may be

[HACKERS] Regexps vs. locale

2008-12-08 Thread Andrew Gierth
This came up on irc: postgres=# show lc_ctype; lc_ctype - fr_FR.UTF-8 postgres=# show server_encoding; server_encoding - UTF8 (1 row) postgres=# select E'\303\201' ILIKE E'\303\241'; ?column? -- t (1 row) postgres=# select E'\303\201' ~*

Re: [HACKERS] Regexps vs. locale

2008-12-08 Thread Andrew Gierth
Tom == Tom Lane [EMAIL PROTECTED] writes: Andrew Gierth [EMAIL PROTECTED] writes: Obviously, this happens because the locale support functions in backend/regex/regc_locale.c are (presumably intentionally) crippled so as not to support non-ascii chars, despite all the code there using

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-11-14 Thread Andrew Gierth
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: (A question here: the spec allows (by my reading) the use of parameters in the window frame clause, i.e. BETWEEN $1 PRECEDING AND $2 FOLLOWING.  Wouldn't it therefore make more sense to treat the values as Exprs, albeit very limited

Re: [HACKERS] NULL input for array_agg()?

2009-11-14 Thread Andrew Gierth
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: Hitoshi Hi, During reviewing aggregates ORDER BY, I was reading spec Hitoshi and found description like: Hitoshi == snip == Hitoshi Of the rows in the aggregation, the following do not qualify: Hitoshi — If DISTINCT is specified, then

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-15 Thread Andrew Gierth
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: Hitoshi Questions here: Hitoshi - agglevelsup? Hitoshi We have aggregate capability that all arguments from upper Hitoshi level query in downer level aggregate makes aggregate call Hitoshi itself to upper level call, as a constant

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-15 Thread Andrew Gierth
Andrew == Andrew Gierth and...@tao11.riddles.org.uk writes: Andrew Performance. Andrew tuplesort_getdatum etc. seems to be substantially faster than Andrew tuplesort_gettupleslot especially for the case where you're Andrew sorting a pass-by-value datum such as an integer (since the Andrew

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-15 Thread Andrew Gierth
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: What case exactly would you consider an error? When an order by expression references a lower (more deeply nested) query level than any of the actual arguments? Hitoshi It's only that I felt not intuitive. To me, arguments are

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-15 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom I agree with Hitoshi that this seems to be a hack to deal with the [snip] So copying the way that SELECT DISTINCT works would be the way to go? i.e. keeping separate lists in the parse node for sorting and distinct? What about error handling? If

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-15 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: What about error handling? If the user specifies agg(distinct x) where x is not sortable, do we leave it to the planner to detect that (which means not reporting the error position?) Tom Well, at the moment there's only going to be a sort-based

[HACKERS] ORDER BY vs. volatile functions

2009-11-15 Thread Andrew Gierth
This query: select random() from generate_series(1,10) order by random(); produces sorted output. Should it? -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-15 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: This query: select random() from generate_series(1,10) order by random(); produces sorted output. Should it? Tom It always has; we'd doubtless break some apps if we changed that. For bonus weirdness: select distinct random(),random() from

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-16 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: For bonus weirdness: select distinct random(),random() from generate_series(1,10); set enable_hashagg=off; select distinct random(),random() from generate_series(1,10); I think _that_ one is a bug. Tom Hmm. I think the first one is a bug

Re: [HACKERS] ORDER BY vs. volatile functions

2009-11-16 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Ron Mayer rm...@cheapcomplexdevices.com writes: [1] http://archives.postgresql.org/pgsql-general/2006-11/msg01544.php Tom FWIW, the behavior has changed from the time of that discussion --- Tom we now track sort ordering using

Re: [HACKERS] Timezones (in 8.5?)

2009-11-17 Thread Andrew Gierth
hernan == hernan gonzalez hgonza...@gmail.com writes: Perhaps the OP should explain exactly what real-world problems he's trying to solve.  As noted in the discussion you linked, there's not a lot of enthusiasm around here for getting closer to the spec's datetime handling simply because

Re: [HACKERS] Timezones (in 8.5?)

2009-11-18 Thread Andrew Gierth
Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: If he meant (A), then you store the event as: (ts,tz) = (timestamp '2010-07-27 10:30:00', 'Chile/Santiago') If he meant (B), then you store the event as (tsz,tz) = (timestamp '2010-07-27 10:30:00' at time zone

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-11-19 Thread Andrew Gierth
Here's the rest of the review, as far as I've taken it given the problems with the code. The patch applied cleanly and includes regression tests but not docs. Small nitpicks: there are some comments not updated (e.g. the big one at the start of eval_windowaggregates). A couple of lines are

Re: [HACKERS] Timezones (in 8.5?)

2009-11-19 Thread Andrew Gierth
Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: For example, if you have a type T which incorporates a timestamp and a timezone, what semantics does the T = T operator have? What semantics apply if the definitions of timezones change? Kevin I'd rather sort that out once and

Re: [HACKERS] [GENERAL] Updating column on row update

2009-11-23 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Thom Brown thombr...@gmail.com writes: As for having plpgsql installed by default, are there any security implications? Tom Well, that's pretty much exactly the question --- are there? It Tom would certainly make it easier for someone to

Re: [HACKERS] [GENERAL] Updating column on row update

2009-11-23 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Loops in plain SQL are no problem: see generate_series. The last time we discussed this I demonstrated reasonably straightforward SQL examples of how to do things like password-cracking (and that was long before we had CTEs, so it would be even

Re: [HACKERS] Timezones (in 8.5?)

2009-11-29 Thread Andrew Gierth
Bruce == Bruce Momjian br...@momjian.us writes: Bruce I think there is general agreement that we should have a Bruce timezone data type which validates against Bruce pg_timezone_names().name. What happens when pg_timezone_names output changes? (which it can do, especially if the install is

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-30 Thread Andrew Gierth
Updated version of the aggregate order by patch. Includes docs + regression tests all in the same patch. Changes: - removed SortGroupClause.implicit as per review comments, replacing it with separate lists for Aggref.aggorder and Aggref.aggdistinct. - Refactored in order to move

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-12-02 Thread Andrew Gierth
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: Hitoshi As earlier mail, I added aggcontext to WindowAggState. This issue (as detailed in this post): http://archives.postgresql.org/pgsql-hackers/2009-11/msg01871.php is currently the only significant outstanding issue in my review of

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

2009-12-04 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Andrew Dunstan and...@dunslane.net writes: Before we go too far with this, I'd like to know how we will handle the problems outlined here: http://archives.postgresql.org/pgsql-hackers/2008-02/msg00916.php Tom Hm, I think that's only a

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-12-04 Thread Andrew Gierth
Functionally this patch looks excellent; correct format, applies cleanly, passes regression, and I've been unable to find any issues with the code itself. But I still have a concern over the interface change, so I'm setting this back to waiting on author for now even though it's really a matter

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-12-05 Thread Andrew Gierth
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: Hitoshi One thing for rule test, I checked existing regression test Hitoshi cases and concluded DROP VIEW is necessary, or even VIEW Hitoshi test for a specific feature is not needed. I remember your Hitoshi aggregate ORDER BY patch

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-12-06 Thread Andrew Gierth
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: Hitoshi Attached is updated version. I added AggGetMemoryContext() Hitoshi in executor/nodeAgg.h (though I'm not sure where to go...) Hitoshi and its second argument iswindowagg is output parameter to Hitoshi know whether the call

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-12-06 Thread Andrew Gierth
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: So for this and the regression test problem mentioned in the other mail, I'm setting this back to waiting on author. Hitoshi In my humble opinion, view regression test is not necessary Hitoshi in both my patch and yours. At least

Re: [HACKERS] Aggregate ORDER BY patch

2009-12-14 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Updated version of the aggregate order by patch. Tom I'm starting to look at this now. I find it rather bizarre to Tom merge both the actual arguments of an aggregate and the optional Tom ORDER BY expressions into a single targetlist. It doesn't

Re: [HACKERS] Aggregate ORDER BY patch

2009-12-15 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Andrew Gierth and...@tao11.riddles.org.uk writes: Updated version of the aggregate order by patch. Tom Applied with some editorialization. The main change I made was Tom to get rid of all the ad-hoc DISTINCT handling in parse_agg.c Tom

Re: [HACKERS] Aggregate ORDER BY patch

2009-12-15 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Query-level DISTINCT shouldn't allow columns in the order by that aren't in the select list because those columns _do not exist_ at the point that ordering logically takes place (even though in the implementation, they might). This isn't the

Re: [HACKERS] Does verbose Need to be Reserved?

2009-12-16 Thread Andrew Gierth
David == David E Wheeler da...@kineticode.com writes: David Hey All, David I was just getting a new version of pgTAP ready for release, and while testing it on HEAD, I got this error: David + psql:pgtap.sql:5789: ERROR: syntax error at end of input David + LINE 28: IF

Re: [HACKERS] Does verbose Need to be Reserved?

2009-12-16 Thread Andrew Gierth
Euler == Euler Taveira de Oliveira eu...@timbira.com writes: David E. Wheeler escreveu: A list of such words, to be included in the Changes file, would be most useful I expect. I'm guessing Keywords listed as reserved in appendix C no longer work as variable names in pl/pgsql, even though

Re: [HACKERS] Range types

2009-12-17 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Someone mentioned LATERAL? Tom Lane t...@sss.pgh.pa.us writes: Hm, how would you do it with LATERAL? The problem is not so much composition as the need for a variable number of rounds of composition. Let's have a try at it: select

Re: [HACKERS] Proposal: Pre ordered aggregates, default ORDER BY clause for aggregates - median support

2009-12-20 Thread Andrew Gierth
Pavel == Pavel Stehule pavel.steh...@gmail.com writes: 2009/12/20 Tom Lane t...@sss.pgh.pa.us: I think that we've already expanded the capabilities of aggregates a great deal for 8.5, and we should let it sit as-is for a release or two and see what the real user demand is for additional

Re: [HACKERS] revised hstore patch

2009-08-24 Thread Andrew Gierth
Ron == Ron Mayer rm...@cheapcomplexdevices.com writes: At this point it's been 12 days since this was written and no updated patch has been posted, so I think it's well past time to move this to Returned with Feedback. Accordingly I'm going to make that change. Hopefully, an updated

Re: [HACKERS] LATERAL

2009-09-08 Thread Andrew Gierth
David == David Fetter da...@fetter.org writes: I've attempted to search the archives for references to the SQL LATERAL feature, which AIUI is fairly-frequently requested. [snip] Has anyone poked at this at all? David I believe Andrew (RhodiumToad) Gierth is taking a look at David

Re: [HACKERS] LATERAL

2009-09-09 Thread Andrew Gierth
Robert == Robert Haas robertmh...@gmail.com writes: Just to pick up on some points from the discussion: 1. LATERAL has to be explicit because it changes the scope of references.  For example, in: ... (select ... FROM (select a AS b), (select b)) ... the b in the second subselect

[HACKERS] revised hstore patch

2009-09-14 Thread Andrew Gierth
Latest hstore patch with provision for inplace upgrading. -- Andrew (irc:RhodiumToad) hstore-20090914.patch.gz Description: hstore patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] [BUGS] BUG #5053: domain constraints still leak

2009-09-14 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom [ probably time to move this thread to -hackers ] Tom There is some moderately interesting reading material in section Tom 4.17.4 Domain constraints of SQL:2008. In particular, it Tom appears to me that the standard goes out of its way to NOT

Re: [HACKERS] [BUGS] BUG #5053: domain constraints still leak

2009-09-15 Thread Andrew Gierth
Sam == Sam Mason s...@samason.me.uk writes: But there's a kicker: in Subclause 6.12, cast specification, in the General Rules is: a) If the cast operand specifies NULL, then the result of CS is the null value and no further General Rules of this Subclause are applied. That no

Re: [HACKERS] Timestamp to time_t

2009-09-15 Thread Andrew Gierth
Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: Kevin TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, Given that the spec requires that 2009-01-31 + interval 1 month = 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I think we can safely ignore virtually

Re: [HACKERS] Timestamp to time_t

2009-09-15 Thread Andrew Gierth
Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: Given that the spec requires that 2009-01-31 + interval 1 month = 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I think we can safely ignore virtually everything it says about date/time handling. Kevin Codd

Re: [HACKERS] revised hstore patch

2009-09-15 Thread Andrew Gierth
Accidentally left the doc patch out of the hstore patch posted previously, so here it is as a separate patch. -- Andrew (irc:RhodiumToad) hstore-doc-20090914.patch.gz Description: hstore doc patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] updated hstore patch

2009-09-15 Thread Andrew Gierth
Gah. rerolled to fix a missing file. includes the docs too this time. -- Andrew (irc:RhodiumToad) hstore-20090915.patch.gz Description: hstore patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] updated hstore patch

2009-09-18 Thread Andrew Gierth
David == David E Wheeler da...@kineticode.com writes: David * I ran the following to update the SQL functions in my simple database: Davidpsql -d try --set hstore_xact='--' -f hstore.sql DavidThe use of `--set hstore_xact='--' was on Andrew's advice Davidvia IRC, because

Re: [HACKERS] updated hstore patch

2009-09-20 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: David E. Wheeler da...@kineticode.com writes: ... I think that this patch is ready for committer review and, perhaps, committing. The code looks clean (though mainly over my head) and the functionality is top-notch. Tom Given the number of

Re: [HACKERS] updated hstore patch

2009-09-20 Thread Andrew Gierth
David == David E Wheeler da...@kineticode.com writes: The only open question I can see is what delete(hs,$1) resolves to when $1 is an unknown-type placeholder; this is probably an incompatibility with the old version if anyone is relying on that (but I don't see why they would be).

Re: [HACKERS] updated hstore patch

2009-09-21 Thread Andrew Gierth
David == David E Wheeler da...@kineticode.com writes: But I checked, and delete(hstore,$1) still resolves to delete(hstore,text) when the type of $1 is not specified, so there's no compatibility issue there that I can see. (I'm not sure I understand _why_ it resolves to that rather than

Re: [HACKERS] updated hstore patch

2009-09-21 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: On Sep 21, 2009, at 4:57 PM, Andrew Gierth wrote: I don't think there's any way to do that from the regression tests. The output that you demonstrated a few messages back should do nicely for delete(), at least: Tom Anything involving

[HACKERS] latest hstore patch

2009-09-22 Thread Andrew Gierth
Hstore patch incorporating changes as previously discussed. In addition the requested new features of conversions to and from array formats have been added (with docs). -- Andrew (irc:RhodiumToad) hstore-20090923.patch.gz Description: hstore patch -- Sent via pgsql-hackers mailing list

Re: [HACKERS] latest hstore patch

2009-09-23 Thread Andrew Gierth
David == David E Wheeler da...@kineticode.com writes: David Just a few thoughts for discussion: David * From my previous posts: Is it time to kill off `...@` and `~`,? David Not necessarily for your patch to handle, just wondering what David others think. I'll take them out if people think

Re: [HACKERS] plpgsql function is so slow

2009-09-24 Thread Andrew Gierth
Euler == Euler Taveira de Oliveira eu...@timbira.com writes: Euler Ops... forgot to remove it from other test. It seems much Euler better but far from the ideal. :( I've never taken a look at Euler the pl/pgsql code but it could be nice if there would be two Euler path codes: access-data and

Re: [HACKERS] latest hstore patch

2009-09-29 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: David * More name stuff: Why `hstore_to_list` rather than David `hstore_to_array`? And I'm not sure about `hstore_to_matrix` David for the 2-dimensional array. I guess that's better than David `hstore_to_multidimensional_array` would be. ;-) I

Re: [HACKERS] latest hstore patch

2009-09-29 Thread Andrew Gierth
David == David E Wheeler da...@kineticode.com writes: I don't feel particularly strongly about the name (I've also intentionally held off on updating the pgfoundry version of the code until this is settled so no-one else should care either). David I'm down with hstore_to_array() and

Re: [HACKERS] latest hstore patch

2009-09-30 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: And there was much rejoicing Tom ... except in the buildfarm. Must be some platform dependency Tom we both missed ... oops -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to

Re: [HACKERS] latest hstore patch

2009-10-02 Thread Andrew Gierth
Bruce == Bruce Momjian br...@momjian.us writes: There's still the issue of how to get the improved module definition (new functions etc) into a migrated database. That's not specific to hstore in any way though, it would affect any contrib module that had added stuff in a new release.

[HACKERS] taking a stab at agg(foo ORDER BY bar)

2009-10-04 Thread Andrew Gierth
The spec defines array_agg(foo ORDER BY ...) which we don't implement yet; obviously, we have no reason not to define this for any aggregate, rather than just array_agg. This doesn't seem to present any problems as far as the syntax goes, and the actual execution is just a small matter of coding,

[HACKERS] first-draft patch for aggregate ORDER BY

2009-10-05 Thread Andrew Gierth
What it does: # select array_agg(b order by a) from (values (3,'foo'),(2,'bar'),(1,'baz')) v(a,b); array_agg --- {baz,bar,foo} (1 row) What it doesn't do: - no docs or regression tests yet - no support for agg(... ORDER BY ...) OVER window (which the spec does

Re: [HACKERS] Rules: A Modest Proposal

2009-10-05 Thread Andrew Gierth
Greg == Greg Sabino Mullane g...@turnstep.com writes: They're mostly a foot-gun. Greg Lots of things in Postgres could be considered potential foot Greg guns. Frankly, I don't think rules are even near the top of Greg such a list. Can you give examples of rule foot guns? There are so many

Re: [HACKERS] Rules: A Modest Proposal

2009-10-05 Thread Andrew Gierth
Josh == Josh Berkus j...@agliodbs.com writes: 1) any reference in an insert rule to NEW.col where col has a volatile default, or the expression in the insert statement was volatile, or the expression's value is changed by the insert, will do the wrong thing: Josh Is this different from

Re: [HACKERS] LATERAL

2009-10-19 Thread Andrew Gierth
Greg == Greg Stark gsst...@mit.edu writes: Why not?  As Andrew pointed out, what we're really trying to accomplish here is consider sub-join plans that are parameterized by a value obtained from an outer relation.  I think we shouldn't artificially limit what we consider. Greg Am I

Re: [HACKERS] Tightening binary receive functions

2009-10-24 Thread Andrew Gierth
James == James Pye li...@jwp.name writes: James Is the new date_recv() constraint actually correct? No, it's not: regression=# create table x (a date); CREATE TABLE regression=# insert into x values ('1999-01-01'); INSERT 0 1 regression=# copy x to '/tmp/tst.dmp' binary; COPY 1 regression=#

Re: [HACKERS] Tightening binary receive functions

2009-10-26 Thread Andrew Gierth
Heikki == Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Heikki Oops, you're right. The check is indeed confusing julian day Heikki numbers, with epoch at 23th of Nov 4714 BC, with Heikki postgres-reckoning day numbers, with epoch at 1th of Jan Heikki 2000. Thanks, will

Re: [HACKERS] backup_label in a crash recovery

2009-11-03 Thread Andrew Gierth
Albe == Albe Laurenz laurenz.a...@wien.gv.at writes: Albe Removing postmaster.pid can lead to a corrupt database. Albe Removing backup_label means that one of your backups will go Albe wrong, and a subsequent pg_stop_backup() will throw an error. Albe If you have a cluster failover during

Re: [HACKERS] more support for various frame types of window functions

2009-11-09 Thread Andrew Gierth
Heikki == Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Heikki Yeah, we need the reinitialization support to handle the generic case. Heikki One idea is to take a copy of the state datum after each row. Some existing aggregates, notably array_agg, rely on the assumption

Re: [HACKERS] Listen / Notify rewrite

2009-11-11 Thread Andrew Gierth
Martijn == Martijn van Oosterhout klep...@svana.org writes: Hi, Attached is a patch for a new listen/notify implementation. In a few words, the patch reimplements listen/notify as an slru-based queue which works similar to the sinval structure. Essentially it is a ring buffer on

[HACKERS] NULL-handling in aggregate(DISTINCT ...)

2009-11-11 Thread Andrew Gierth
Quoth the comments in nodeAgg.c: * We don't currently implement DISTINCT aggs for aggs having more * than one argument. This isn't required for anything in the SQL * spec, but really it ought to be implemented for * feature-completeness. FIXME someday. and: * DISTINCT always suppresses

Re: [HACKERS] NULL-handling in aggregate(DISTINCT ...)

2009-11-11 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Andrew Gierth and...@tao11.riddles.org.uk writes: Now the question: If the limit of one argument for DISTINCT aggs were removed (which I'm considering doing as part of an update to the aggregate ORDER BY patch I posted a while back), what should

Re: [HACKERS] NULL-handling in aggregate(DISTINCT ...)

2009-11-11 Thread Andrew Gierth
Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom I think you could probably just change it: make DISTINCT work as Tom per regular DISTINCT (treat null like a value, keep one copy). Tom All the spec-conforming aggregates are strict and would ignore Tom the null in the next step anyway.

[HACKERS] Aggregate ORDER BY patch

2009-11-12 Thread Andrew Gierth
Herewith a patch to implement agg(foo ORDER BY bar) with or without DISTINCT, etc. No artificial restrictions are imposed on what syntactical combinations are allowed. However, ORDER BY is not allowed with aggregates used as window functions (as per the existing restriction on DISTINCT). Included

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-13 Thread Andrew Gierth
Heikki == Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Herewith a patch to implement agg(foo ORDER BY bar) with or without DISTINCT, etc. Heikki What does that mean? Aggregate functions are supposed to be Heikki commutative, right? The SQL spec defines two

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-13 Thread Andrew Gierth
Peter == Peter Eisentraut pete...@gmx.net writes: I'm not entirely convinced that adding ORDER BY here is a good idea, partly because it goes so far beyond the spec Peter This is exactly the syntax that is in the spec AFAICT. Right. The spec defines this syntax for array_agg and xmlagg

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-13 Thread Andrew Gierth
Heikki == Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: No artificial restrictions are imposed on what syntactical combinations are allowed. However, ORDER BY is not allowed with aggregates used as window functions (as per the existing restriction on DISTINCT).

[HACKERS] Aggregate ORDER BY docs patch, first attempt

2009-11-13 Thread Andrew Gierth
First attempt at a docs patch for aggregate order by. -- Andrew. Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.491 diff -c -r1.491 func.sgml ***

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-11-14 Thread Andrew Gierth
Hi, I've started reviewing your patch. I've already found some things that need work: - missing _readWindowFrameDef function (all nodes that are output from parse analysis must have both _read and _out functions, otherwise views can't work) - the A_Const nodes should probably be

[HACKERS] Executor question

2008-07-26 Thread Andrew Gierth
While trying to construct testcases for a patch, I ran into this: execAmi.c has a function ExecMayReturnRawTuples which indicates whether a given plan might return tuples that come straight from a table rather than having been projected. InitPlan() uses this to force the addition of a junk

Re: [HACKERS] WITH RECUSIVE patches 0723

2008-07-26 Thread Andrew Gierth
At David's request I've been looking through this patch. Regarding documentation: if it would help, I can write some; I have already made a start on writing down what is going on internally in order to understand it myself. I've found three more bugs so far: 1) create view v2(id) as values

Re: [HACKERS] WITH RECUSIVE patches 0723

2008-07-28 Thread Andrew Gierth
Tatsuo == Tatsuo Ishii [EMAIL PROTECTED] writes: This behaviour is clearly intentional, since the entire mechanism of estate- es_disallow_tuplestore exists for no other reason, but it seems to me to be clearly wrong. What is the justification for it? Tatsuo Yes, this is due to prevent

Re: [HACKERS] WITH RECUSIVE patches 0723

2008-07-28 Thread Andrew Gierth
Tatsuo == Tatsuo Ishii [EMAIL PROTECTED] writes: Tatsuo Included patches from Yoshiyuki should fix 1) and 2). I also Tatsuo add your SQLs to the regression test. Thanks. I think it needs this change in addition; without it, incorrect results are returned when you reference a recursive view

Re: [HACKERS] WITH RECUSIVE patches 0723

2008-07-28 Thread Andrew Gierth
Tom == Tom Lane [EMAIL PROTECTED] writes: I think it needs this change in addition; without it, incorrect results are returned when you reference a recursive view from within the recursive query, due to the RecursionScan nodes becoming linked to the wrong tuplestores. Tom That whole

Re: [HACKERS] WITH RECUSIVE patches 0723

2008-07-28 Thread Andrew Gierth
Tom == Tom Lane [EMAIL PROTECTED] writes: [snip spec] Just out of curiosity, since I don't have a copy of the spec handy, how does the language for WITH compare to that for views? Tom I think this is a must fix because of the point about volatile Tom functions --- changing it later will

Re: [HACKERS] WITH RECUSIVE patches 0723

2008-07-28 Thread Andrew Gierth
Tom == Tom Lane [EMAIL PROTECTED] writes: Tom We are not in the business of getting spec-required semantics Tom 80% right; I guess that's why we still fold identifiers to lowercase, why our timestamp implementation differs from the standard, why we used to require AS for select-list aliases,

Re: [HACKERS] WITH RECUSIVE patches 0723

2008-07-28 Thread Andrew Gierth
Gregory == Gregory Stark [EMAIL PROTECTED] writes: Gregory I just wonder where all these examples of real-world queries Gregory were when I posted this patch and asked for such feedback Gregory originally. sigh. Gregory In any case I think we've already made this decision. If we Gregory

Re: [HACKERS] WITH RECUSIVE patches 0723

2008-07-28 Thread Andrew Gierth
Tom == Tom Lane [EMAIL PROTECTED] writes: Martijn van Oosterhout [EMAIL PROTECTED] writes: Since the problem is using the result of a WITH clause more than once, would it be sufficient to simply detect that case and bail? You don't want materialisation is most cases, there's just a few

Re: [HACKERS] WITH RECUSIVE patches 0723

2008-08-01 Thread Andrew Gierth
Tatsuo == Tatsuo Ishii [EMAIL PROTECTED] writes: At David's request I've been looking through this patch. Regarding documentation: if it would help, I can write some; I have already made a start on writing down what is going on internally in order to understand it myself. Tatsuo

[HACKERS] Re: [Pljava-dev] Should creating a new base type require superuser status?

2008-08-01 Thread Andrew Gierth
Tom == Tom Lane [EMAIL PROTECTED] writes: Tom, could you please elaborate where you see a security hole? Tom The problem that we've seen in the past shows up when the user Tom lies in the CREATE TYPE command, specifying type representation Tom properties that are different from what the

Re: [HACKERS] bug in prepared statements, alter table 8.3

2008-08-07 Thread Andrew Gierth
Merlin == Merlin Moncure [EMAIL PROTECTED] writes: Merlin postgres=# alter table abc alter a type numeric; Merlin ALTER TABLE Merlin Time: 254.847 ms Merlin postgres=# EXECUTE ins_abc; Merlin INSERT 0 1 Merlin Time: 0.452 ms Merlin postgres=# select * from abc; Merlin ERROR: invalid

Re: [HACKERS] Replay attack of query cancel

2008-08-10 Thread Andrew Gierth
Tom == Tom Lane [EMAIL PROTECTED] writes: Alvaro Herrera [EMAIL PROTECTED] writes: I wonder if we can do something diffie-hellman'ish, where we have a parameter exchanged in the initial SSL'ed handshake, which is later used to generate new cancel keys each time the previous one is

Re: [HACKERS] SeqScan costs

2008-08-12 Thread Andrew Gierth
Tom == Tom Lane [EMAIL PROTECTED] writes: Proposal: Make the first block of a seq scan cost random_page_cost, then after that every additional block costs seq_page_cost. Tom This is only going to matter for a table of 1 block (or at least Tom very few blocks), and for such a table it's

Re: [HACKERS] SeqScan costs

2008-08-13 Thread Andrew Gierth
Decibel! == Decibel! [EMAIL PROTECTED] writes: Decibel Roughly what I get on my MBP (I'm about a factor of 2 Decibel slower). This makes me think it's an issue of having to slog Decibel through an entire page one row at a time vs just using the Decibel index. To test this I tested selecting

Re: [HACKERS] SeqScan costs

2008-08-13 Thread Andrew Gierth
Decibel == Decibel! [EMAIL PROTECTED] writes: Decibel OK, ran the test again via this query: Decibel explain analyze select (select value from oneblock where id = i) Decibel from generate_series(1,1) i, generate_series(1,10) j; Decibel changing 1,1 to 200,200 as needed. I don't see

Re: [HACKERS] Replay attack of query cancel

2008-08-16 Thread Andrew Gierth
Magnus == Magnus Hagander [EMAIL PROTECTED] writes: [snip] I'm looking (at Magnus' suggestion) at implementing this. There appears to be only one significant obstacle; since the query cancel message is received _after_ forking a new backend, there has to be some mechanism for recording the

Re: [HACKERS] Replay attack of query cancel

2008-08-16 Thread Andrew Gierth
Tom == Tom Lane [EMAIL PROTECTED] writes: Andrew Gierth wrote: 2. The server accepts either the old-style or the secure cancel request from the client, but doesn't allow old-style requests once a valid secure request has been seen. Hmm, I think there should be a way to turn off

Re: [HACKERS] hstore == and deprecate =

2010-06-09 Thread Andrew Gierth
Robert == Robert Haas robertmh...@gmail.com writes: Robert I don't think so, either. The most someone might want to do Robert is make == work wherever = does now, but I wouldn't want to Robert start monkeying with that without some input from Andrew Robert Gierth; and I don't think it's a

  1   2   3   4   5   >