Re: [HACKERS] sorting a union over inheritance vs pathkeys
On Jun 25, 2014, at 22:14, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Michael Glaesemann g...@seespotcode.net writes: -- ERROR: could not find pathkey item to sort Hm ... I can reproduce that in 9.3 but it seems fine in 9.4 and HEAD. Don't know what's going on exactly. Interesting --- it appears that commit a87c729153e372f3731689a7be007bc2b53f1410 is why it works in 9.4. I had thought that was just improving plan quality, but it seems to also prevent this problem. I guess we'd better back-patch it. Thanks, Tom! Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] sorting a union over inheritance vs pathkeys
I’ve come across an issue when creating a union over tables which includes inheritance: CREATE TABLE events (event_id INT NOT NULL); -- CREATE TABLE CREATE UNIQUE INDEX events_event_id_key ON events (event_id); -- CREATE INDEX CREATE TABLE legacy_events (event_id INT NOT NULL); -- CREATE TABLE CREATE UNIQUE INDEX legacy_events_event_id_key ON legacy_events (event_id); -- CREATE INDEX CREATE TABLE events_2 () INHERITS (events); -- CREATE TABLE -- this index isn't necessary to reproduce the error CREATE UNIQUE INDEX events_2_event_id_key ON events_2 (event_id); -- CREATE INDEX SELECT event_id FROM (SELECT event_id FROM events UNION ALL SELECT event_id FROM legacy_events) _ ORDER BY event_id; -- ERROR: could not find pathkey item to sort It’ll work if the indexes are removed. Using PRIMARY KEY in lieu of NOT NULL and UNIQUE indexes still exhibits the issue. I’ve seen this in 9.2.8 and 9.3.4. I haven’t tested this in 9.4 or earlier than 9.2. Any thoughts? Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] another error perhaps to be enhanced
On Jun 14, 2013, at 13:38, Joshua D. Drake j...@commandprompt.com wrote: ERROR: index foo_idx We should probably add the schema. I've noticed similar issues with functions. I'd like to see those schema-qualified as well. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tablespaces in the data directory
On Dec 3, 2012, at 12:33, Magnus Hagander wrote: On Dec 3, 2012 2:55 AM, Andrew Dunstan and...@dunslane.net wrote: On 12/02/2012 07:50 PM, Magnus Hagander wrote: On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Someone just reported a problem when they had created a new tablespace inside the old data directory. I'm sure there can be other issues caused by this as well, but this is mainly a confusing scenario for people now. As there isn't (as far as I know at least) any actual *point* in creating a tablespace inside the main data directory, should we perhaps disallow this in CREATE TABLESPACE? Or at least throw a WARNING if one does it? It could be pretty hard to detect that in general (think symlinks and such). I guess if we're just trying to print a helpful warning, we don't have to worry about extreme corner cases. But what exactly do you have in mind --- complain about any relative path? Complain about absolute paths that have a prefix matching the DataDir? Oh, I hadn't thought quite so far as the implementation :) Was looking to see if there were going to be some major objections before I even started thinking about that. But for the implementation, I'd say any absolute path that have a prefix matching DataDir. Tablespaces cannot be created using relative paths, so we don't have to deal with that. I have been known to symlink a tablespace on a replica back to a directory in the datadir, while on the primary it points elsewhere. What exactly is the problem? That wouldn't be affected by this though, since it would only warn at create tablespace. I'd still consider it a bad idea in general to do that, since you're basically messing with the internal structure of the data directory. Why not just link it to some place outside the data directory? One reason is that subsequent copies of the data directory then also includes the tablespace data. Saves one step when setting up a standby. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Schema version management
On Jul 5, 2012, at 9:21, Andrew Dunstan wrote: No they are not necessarily one logical unit. You could have a bunch of functions called, say, equal which have pretty much nothing to do with each other, since they refer to different types. +1 from me for putting one function definition per file. +1. It might make sense to include some sort of argument type information. The function signature is really its identifier. The function name is only part of it. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Schema version management
On Jul 5, 2012, at 11:17, Alvaro Herrera wrote: Excerpts from Tom Lane's message of jue jul 05 10:46:52 -0400 2012: Joel Jacobson j...@trustly.com writes: Maybe it could be made an option to pg_dump? Ick. Then we have to deal with all the downsides of *both* methods. pg_dump is already a bloated, nearly unmaintainable mess. The very last thing it needs is even more options. Agreed. However I am also against what seems to be the flow. Normally, you don't write overloaded plpgsql functions such as equal. I often write functions that perform fetches based on different criteria. For example, -- returns count of all orders for the given customer int function order_count(in_customer_name text) -- returns count of all orders for the given customer since the given timestamp int function order_count(in_customer_name text, in_since timestamp with time zone) -- returns count of orders for the given customer during a given interval int function order_count(in_customer_name text, in_from timestamp with time zone, in_through timestamp with time zone) Or, I'll write overloaded functions, one of which provides default values. -- returns the set of members whose birthday is today. Calls birthday_members(CURRENT_DATE) setof record function birthday_members() -- returns the set of members whose birthday is on the given date, which makes testing a lot easier setof record function birthday_members(in_date DATE) Some may disagree that this is a proper usage of function overloading. Some may even argue that function names shouldn't be overloaded at all. However, I find this usage of function name overloading useful, especially for keeping function names relatively short. If we're dumping objects (tables, views, functions, what-have-you) into separate files, each of these functions is a separate object and should be in its own file. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Schema version management
On Jul 5, 2012, at 11:52, Alvaro Herrera wrote: Isn't this a perfect example of stuff that, since it does much the same thing, should be in the same file so that you remember to fix them all together if you find a bug in one? That's what tests are for. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New Postgres committer: Kevin Grittner
On Jun 7, 2012, at 18:15, Tom Lane wrote: I am pleased to announce that Kevin Grittner has accepted the core committee's invitation to become our newest committer. As you all know, Kevin's done a good deal of work on the project over the past couple of years. We judged that he has the requisite skills, dedication to the project, and a suitable degree of caution to be a good committer. Please join me in welcoming him aboard. Congratulations, Kevin! Well-deserved! Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - urlencode, urldecode support
On Apr 25, 2012, at 13:54, Pavel Stehule wrote: what do you think about enhancing encode, decode functions for support of mentioned code? Sounds like a great idea for a PGXN module. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Document hashtext() and Friends?
On Feb 21, 2012, at 15:01, Tom Lane wrote: David E. Wheeler da...@justatheory.com writes: Is there a reason that hashtext() and friends are not documented? Yes. They are internal functions that exist for the convenience of the system, not for users. We've discussed this before, and decided that we don't want people to rely on them continuing to have exactly the current behavior. One example of a possible future change is to widen the results from 4 bytes to 8. And hashtext *has* changed across versions, which is why Peter Eisentraut published a version-independent hash function library: https://github.com/petere/pgvihash Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] logging in high performance systems.
On Dec 13, 2011, at 13:57, Greg Smith wrote: With this idea still being pretty new, and several of the people popping out opinions in this thread being local--Theo, Stephen, myself--we've decided to make our local Baltimore/Washington PUG meeting this month be an excuse to hash some of this early stuff out a bit more in person, try to speed things up . See http://www.meetup.com/Baltimore-Washington-PostgreSQL-Users-Group/events/44335672/ if any other locals would like to attend, it's a week from today. (Note that the NYC PUG is also having its meeting at the same time this month) What time? I'd potentially like to attend. Philadelphia, represent! Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] non-superuser reserved connections? connection pools?
On Jul 4, 2011, at 10:09, Magnus Hagander wrote:
On Mon, Jul 4, 2011 at 00:01, Michael Glaesemann g...@seespotcode.net wrote:
It would be nice to be able to set aside a few connections for
non-superusers, such as stats-monitoring connections. There's often no
reason to grant these users superuser privileges (they're just observers,
and security-definer functions can make anything visible that they may
need)), but at the same time you want them to be able to connect even when
the normal pool of slots may be full.
snip/
connection_pools={stats=3,superuser=10}
max_connections=100
The connections allotted to superuser would have the same meaning as the
current superuser_reserved_connections GUC.
Does this seem to be a useful feature to anyone else?
Yeah, I'd definitely find it useful. Gives you a bit more flexibility
than just using connection limiting on the individual user (though in
your specific case here, that might work, if all your stats processes
are with the same user).
Good point. It's another way to think of managing connections.
Michael Glaesemann
grzm seespotcode net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] non-superuser reserved connections? connection pools?
It would be nice to be able to set aside a few connections for non-superusers,
such as stats-monitoring connections. There's often no reason to grant these
users superuser privileges (they're just observers, and security-definer
functions can make anything visible that they may need)), but at the same time
you want them to be able to connect even when the normal pool of slots may be
full.
I googled a bit, assuming there had been discussion of something similar in the
past, but didn't find anything.
I'm not sure what configuration would look like. Perhaps there's a generalized
connection pool concept that's missing, extending the current superuser
connection pool specified by the superuser_reserved_connections GUC something
like:
CREATE CONNECTION POOL stats WITH LIMIT 10; -- 40 connections allotted for the
foo connection pool.
ALTER ROLE nagios WITH CONNECTION POOL foo; -- the nagios role is allowed to
take a connection from the foo connection pool.
Right now, of course, connection limits are set in postgresql.conf and only
alterable on restart, so perhaps there could be a connection_pools GUC,
something along the lines of:
connection_pools={stats=3,superuser=10}
max_connections=100
The connections allotted to superuser would have the same meaning as the
current superuser_reserved_connections GUC.
Does this seem to be a useful feature to anyone else?
Michael Glaesemann
grzm seespotcode net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exposing float8-as-value to PGXS/makefiles
On May 20, 2011, at 12:51, Alvaro Herrera wrote: Excerpts from Tom Lane's message of vie may 20 12:43:25 -0400 2011: Alvaro Herrera alvhe...@alvh.no-ip.org writes: I was just messing around with a datatype that's based in int64 representation. Pretty much everything (seems to) work cleanly, but one problem I have is that I cannot build the correct CREATE TYPE sentence in the .sql.in file to actually install the type, because there's no easy way to figure out whether float64 (and therefore int64) is passed by value or not. We already solved that for contrib/isn --- use the LIKE clause in CREATE TYPE. Ooh, excellent, thanks. To confirm, this works for Postgres versions = 8.4, correct? Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] PostgreSQL Core Team
On Apr 27, 2011, at 14:48, Dave Page wrote: I'm pleased to announce that effective immediately, Magnus Hagander will be joining the PostgreSQL Core Team. Congratulations, Magnus! Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OSSP gone missing? Fate of UUID?
On Mar 1, 2011, at 17:15, Hiroshi Saito wrote: Ooops, It is some trobles now. please see Ralf-san's comment. Thanks, Hiroshi! Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why our counters need to be time-based WAS: WIP: cross column correlation ...
On Feb 28, 2011, at 14:31, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Feb 28, 2011 at 1:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ultimately we need to think of a reporting mechanism that's a bit smarter than rewrite the whole file for any update ... Well, we have these things called tables. Any chance of using those? Having the stats collector write tables would violate the classical form of the heisenberg principle (thou shalt avoid having thy measurement tools affect that which is measured), not to mention assorted practical problems like not wanting the stats collector to take locks or run transactions. The ideal solution would likely be for the stats collector to expose its data structures as shared memory, but I don't think we get to do that under SysV shmem --- it doesn't like variable-size shmem much. Maybe that's another argument for looking harder into mmap or POSIX shmem, although it's not clear to me how well either of those fixes that. Spitballing here, but could sqlite be an intermediate, compromise solution? Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] OSSP gone missing? Fate of UUID?
A couple of weeks ago when installing uuid-ossp on a new server, I noticed that the ossp site is gone. I haven't found anything on the web to indicate what happened. Anyone know? Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function(contants) evaluated for every row
On Nov 24, 2010, at 15:28 , Marti Raudsepp wrote: On Wed, Nov 24, 2010 at 21:52, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: Notice the to_date()'s were not converted to constants in EXPLAIN so they are evaluated for every row. to_date() is marked STABLE. No. This is per expectation. Only IMMUTABLE functions can be folded to constants in advance of the query. This is something that has bit me in the past. I realize that STABLE functions cannot be constant-folded at planning-time. But are there good reasons why it cannot called only once at execution-time? As long as *only* STABLE or IMMUTABLE functions are used in a query, we can assume that settings like timezone won't change in the middle of the execution of a function, thus STABLE function calls can be collapsed -- right? I've seen this as well be a performance issue, in particular with partitioned tables. Out of habit I now write functions that always cache the value of the function in a variable and use the variable in the actual query to avoid this particular gotcha. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance
On Oct 4, 2010, at 13:13 , Robert Haas wrote: On Mon, Oct 4, 2010 at 10:44 AM, Hakan Kocaman hko...@googlemail.com wrote: for whom it may concern: http://pdos.csail.mit.edu/mosbench/ They tested with 8.3.9, i wonder what results 9.0 would give. Best regards and keep up the good work Hakan Here's the most relevant bit to us: snip/ The use of lock-free techniques seems quite interesting; unfortunately, I know next to nothing about the topic and this paper doesn't provide much of an introduction. Anyone have a reference to a good introductory paper on the topic? The README in the postgres section of the git repo leads me to think the code that includes the fixes it there, if someone wants to look into it (wrt to the Postgres lock manager changes). Didn't check the licensing. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch: to_string, to_array functions
On Jul 21, 2010, at 12:30 , Robert Haas wrote: array_split() and array_join(), following Perl? +1. Seems common in other languages such as Ruby, Python, and Java as well. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Additional startup logging
On Jun 30, 2010, at 22:43 , Takahiro Itagaki wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: It seems potentially useful to LOG the version() string in the log file during startup. It might also help to LOG any settings which might result in the loss of committed transactions or in database corruption during startup. (After a crash, the postgresql.conf file might not show the values which were in effect during startup, and it is too late to show the values.) I think such logs depends on purposes, so they should be customizable. You could write a module, that is registered in 'shared_preload_libraries' and logs internal information you want from _PG_init() or shmem_startup_hook. For long-running systems, you may not have the beginning of the log file. Perhaps a method of dumping the version and/or setting information on demand (or perhaps at the beginning of each log file?): Shouldn't be too hard to put together a function which prints out such information via RAISE even now using PL/pgSQL. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
On Jun 8, 2010, at 15:38 , Robert Haas wrote: On Tue, Jun 8, 2010 at 3:34 PM, Merlin Moncure mmonc...@gmail.com wrote: hm. any chance of a shorter operator, like '#'? I kinda agree that hstore_in and the operator don't have to be the same, but requiring three letter token for the two most high traffic operations w/hstore seems off to me. # is currently used for bitwise xor/geo I'm happy to do whatever the consensus is. I thought it would be easier to remember if the two operators were spelled at least somewhat similarly, but I just work here. Perhaps - Would a colon work? (That's enough bikeshedding for me.) Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
On Jun 8, 2010, at 16:17 , Robert Haas wrote: (That's enough bikeshedding for me.) Test first, then post? :-) What? :) If I was productively contributing, I wouldn't be bikeshedding, now would I? Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone know if Alvaro is OK?
On Feb 27, 2010, at 20:33 , Robert Haas wrote: On Sat, Feb 27, 2010 at 7:21 PM, Marc G. Fournier scra...@hub.org wrote: Is there a higher then normal amount of earthquakes happening recently? haiti, japan just had one for 6.9, there was apparently one in illinos a few weeks back, one on the Russia/China/N.Korean border and now Chile? Hrmmm ... Should I rocket my children to a solar system with a yellow sun? ...Robert Isn't that Rob-el? Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha4 Available Now
On Feb 26, 2010, at 0:55 , Дмитрий Фефелов wrote: http://developer.postgresql.org/pgdocs/postgres/release-9-0.html Performance section: Simplify the forms foo true and foo false to foo = false and foo = true during query optimization. Will it work correct;ly when foo is NULL? It shouldn't have any effect: NULL anything and NULL = anything is NULL SELECT arg1, arg2, (arg1 arg2) AS arg1 arg2, (arg1 = arg2) AS (arg1 = arg2) FROM (VALUES (TRUE, TRUE), (TRUE, FALSE), (FALSE, TRUE), (FALSE, FALSE), (NULL, TRUE), (NULL, FALSE)) AS bools (arg1, arg2) ORDER BY arg1, arg2; arg1 | arg2 | arg1 arg2 | (arg1 = arg2) +--+--+--- f | f| f| t f | t| t| f t | f| t| f t | t| f| t (null) | f| (null) | (null) (null) | t| (null) | (null) (6 rows) Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Correcting Error message
On Feb 26, 2010, at 3:30 , Piyush Newe wrote: Hi, Consider following testcase, CREATE TABLE footable(id int4, name varchar2(10)); CREATE FUNCTION foofunc(a footable, b integer DEFAULT 10) RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL; CREATE FUNCTION foofunc(a footable, b numeric DEFAULT 10) RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL; SELECT (footable.*).foofunc FROM footable; ERROR: column footable.foofunc does not exist LINE 1: SELECT (footable.*).foofunc FROM footable; ^ Is that calling syntax correct? I'd think it should be: SELECT foofunc(footable.*, 10) FROM footable; Note there are two arguments to foofunc (in either version) test=# SELECT version(); version -- PostgreSQL 8.4.2 on i386-apple-darwin9.8.0, compiled by GCC i686- apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5493), 32-bit (1 row) test=# CREATE TABLE footable(id int4, name varchar(10)); CREATE TABLE test=# INSERT INTO footable (id, name) VALUES (1, 'foo'), (2, 'bar'); INSERT 0 2 test=# CREATE FUNCTION foofunc(a footable, b integer DEFAULT 10) postgres-# RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL; CREATE FUNCTION test=# CREATE FUNCTION foofunc(a footable, b numeric DEFAULT 10) postgres-# RETURNS integer AS $$ SELECT 456; $$ LANGUAGE SQL; CREATE FUNCTION test=# SELECT name, foofunc(footable.*, 10) FROM footable; name | foofunc --+- foo | 123 bar | 123 (2 rows) test=# SELECT name, foofunc(footable.*, 10.0) FROM footable; name | foofunc --+- foo | 456 bar | 456 (2 rows) In any event, I couldn't get your example to work on Postgres 8.4 regardless due to the varchar2 type. Which version of Postgres are you using? test=# CREATE TABLE footable(id int4, name varchar2(10)); ERROR: type varchar2 does not exist Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Correcting Error message
On Feb 26, 2010, at 21:03 , Tom Lane wrote: Michael Glaesemann g...@seespotcode.net writes: On Feb 26, 2010, at 3:30 , Piyush Newe wrote: SELECT (footable.*).foofunc FROM footable; ERROR: column footable.foofunc does not exist Is that calling syntax correct? I'd think it should be: SELECT foofunc(footable.*, 10) FROM footable; He's relying on the f(x) === x.f syntactic equivalence, as per the comments for ParseFuncOrColumn: Note there are two arguments to foofunc (in either version) ... and the example also relies on the presence of default arguments for both functions. This makes both of them match a single-argument call, resulting in an ambiguous-function situation. The proposed change would cause it to actually throw an ambiguous function error. Ah! Learned two new things. Thanks, Tom! Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Pathological regexp match
On Jan 31, 2010, at 22:14 , Tom Lane wrote: The Tcl folk accepted that patch, so I went ahead and applied it to our code. It would still be a good idea for us to do any testing we can on it, though. I applied the patch and ran both the test query I submitted as well as original problematic query that triggered the report, and it runs much faster. Thanks for the fix! Michael Glaesemann michael.glaesem...@myyearbook.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Pathological regexp match
We came across a regexp that takes very much longer than expected.
PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-44), 64-bit
SELECT 'ooo...' ~ $r$Z(Q)[^Q]*A.*?(\1)$r$; -- omitted for email brevity
?column?
--
t
(1 row)
Time: 90525.148 ms
The full query is below.
The same match in perl takes less than 0.01 seconds on the same hardware.
#!/bin/env perl
use warnings;
use strict;
my $sample = 'ooo...'; # omitted for email brevity
if ($sample =~ /Z(Q)[^Q]*A.*?(\1)/) {
print 'matches';
}
else {
print 'does not match';
}
This is a simplified version of a match that finally finished after 18 hours.
Given the nearly 4 orders of magnitude difference between the Perl script and
the Postgres version, is there something that could be improved in the Postgres
regex engine?
Cheers,
Michael Glaesemann
michael.glaesem...@myyearbook.com
SELECT
'ooQooQoQQoooQoQoooQZQooQooQoQQoQoQoooQoooQooQZQoooAQoooQooQoooQooQoQoooAQQoQooQoQooQoooQoooQoooQooZQoQooQoQoooQooQoQoooQQoQZQoQooQoQQoQoZQooQooQoQQoooQoQooQZQooQooQoQQoQooZQoQooQoQooQoooQooQoQoooQooQQoQoZQQooQoQZQooQooQoQ
Re: [HACKERS] Pathological regexp match
On Jan 28, 2010, at 21:59 , Alvaro Herrera wrote:
Hi Michael,
Michael Glaesemann wrote:
We came across a regexp that takes very much longer than expected.
PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-44), 64-bit
SELECT 'ooo...' ~ $r$Z(Q)[^Q]*A.*?(\1)$r$; -- omitted for email
brevity
The ? after .* is pointless.
Interesting. I would expect that *? would be the non-greedy version of
*, meaning match up to the first \1 (in this case the first Q
following A), rather than as much as possible.
For example, in Perl:
$ perl -e if ('oooZQoooAoooQooQooQooo' =~ /Z(Q)[^Q]*A.*(\1)/)
{ print \$; } else { print 'NO'; } echo
ZQoooAoooQooQooQ
$ perl -e if ('oooZQoooAoooQooQooQooo' =~ /Z(Q)[^Q]*A.*?(\1)/)
{ print \$; } else { print 'NO'; } echo
ZQoooAoooQ
If I'm reading the docs right, Postgres does support non-greedy * as *?:
http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#POSIX-QUANTIFIERS-TABLE
However, as you point out, Postgres doesn't appear to take this into
account:
postgres=# select regexp_replace('oooZQoooAoooQooQooQooo', $r$(Z(Q)
[^Q]*A.*(\2))$r$, $s$X$s$);
regexp_replace
oooXooo
(1 row)
postgres=# select regexp_replace('oooZQoooAoooQooQooQooo', $r$(Z(Q)
[^Q]*A.*?(\2))$r$, $s$X$s$);
regexp_replace
oooXooo
(1 row)
Michael Glaesemann
michael.glaesem...@myyearbook.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Pathological regexp match
On Jan 28, 2010, at 23:21 , Alvaro Herrera wrote: I think the reason for this is that the first * is greedy and thus the entire expression is considered greedy. The fact that you've made the second * non-greedy does not ungreedify the RE ... Note the docs say: The above rules associate greediness attributes not only with individual quantified atoms, but with branches and entire REs that contain quantified atoms. What that means is that the matching is done in such a way that the branch, or whole RE, matches the longest or shortest possible substring as a whole. Interesting. Thanks for pointing out this section of the docs. I wasn't aware of this twist. It's late here so I'm not sure if this is what you're looking for: I'm not actually looking for a regexp that works: I was able to accomplish the task I had at hand with a different regexp. I'm just reporting the particular unexpected nastiness we ran into. :) Michael Glaesemann michael.glaesem...@myyearbook.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Dec 20, 2009, at 13:58 , Tom Lane wrote: * Append _index not _key if it's not a constraint-related index. _idx instead of _index keeps things a bit shorter (and a couple of keystrokes further from NAMEDATALEN). There's precedent for abbreviations with automatic naming in Postgres, e.g., _fkey. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New VACUUM FULL
On Dec 4, 2009, at 18:07 , Jeff Davis wrote: On Fri, 2009-12-04 at 18:36 +, Simon Riggs wrote: Let's check it works before worrying about performance. We can take tests out as well as add them once it becomes obvious its working. Itagaki-san, perhaps you should add a variety of tests, and then Simon can remove extra tests after he's convinced that it works. I tested a variety of situations during my review, and everything worked as I expected. Would there be a way for you to package the scenarios you tested into a suite? Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby status
On Nov 10, 2009, at 13:36 , Heikki Linnakangas wrote: Attached is the latest and greatest patch against CVS head, taken from the hs-riggs branch in my git repository. Awesome. Thank you, Simon and Heikki! I skimmed through the documentation to get a better handle on what this will mean. + para + These actions produce error messages + + itemizedlist +listitem + para + DML - Insert, Update, Delete, COPY FROM, Truncate. + Note that there are no actions that result in a trigger + being executed during recovery. + /para + /listitem +listitem + para + DDL - Create, Drop, Alter, Comment (even for temporary tables because + currently these cause writes to catalog tables) + /para + /listitem Other commands are in all-caps. Any reason INSERT, UPDATE, DELETE, TRUNCATE, CREATE, DROP, ALTER, and COMMENT are not? Cheers, Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: make plpgsql IN args mutable (v1) [REVIEW]
On Sep 16, 2009, at 13:40 , Josh Berkus wrote: 3. This patch eliminates a common plpgsql beginner error and saves all of us heavy plpgsql users some typing, especially when the use of a mutable variable means that we can eliminate the DECLARE section entirely, as in: This: CREATE PROCEDURE mod ( x int, y int ) RETURNS int LANGUAGE plpgsql AS $f$ DECLARE z INT := x; BEGIN z := x % y; RETURN z; END; $f$ This is also currently valid: CREATE FUNCTION mod (x int, y int) RETURNS int LANGUAGE plpgsql AS $f$ DECLARE z INT := x % y; BEGIN RETURN z; END; $f$ As is this: CREATE FUNCTION mod (x int, y int) RETURNS int LANGUAGE plpgsql AS $f$ BEGIN RETURN (x % y); END; $f$ Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: make plpgsql IN args mutable (v1) [REVIEW]
On Sep 16, 2009, at 15:17 , Josh Berkus wrote: Michael, This is also currently valid: CREATE FUNCTION mod (x int, y int) RETURNS int LANGUAGE plpgsql AS $f$ DECLARE z INT := x % y; BEGIN RETURN z; END; $f$ As is this: CREATE FUNCTION mod (x int, y int) RETURNS int LANGUAGE plpgsql AS $f$ BEGIN RETURN (x % y); END; $f$ Certainly. I was doing that to have a simple example; obviously you wouldn't write a mod funciton, and you wouldn't do it in plpgsql. There are other case where the lack of mutability in IN parameters causes you to create a throwaway variable. Have an example at hand? I'd argue that in a case of a function of more complexity from a code clarity standpoint you'd want to assign to a new variable that describes what the new value reflects. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.5 release timetable, again
On Aug 26, 2009, at 11:18 , Jean-Michel Pouré wrote: Web apps are 95% of PostgreSQL possible users. Where does this figure come from? Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generic options for explain
On May 25, 2009, at 0:47 , Joshua Tolley wrote: On Sun, May 24, 2009 at 06:53:29PM -0400, Tom Lane wrote: Greg Smith gsm...@gregsmith.com writes: On Sun, 24 May 2009, Pavel Stehule wrote: we should have a secondary function explain_query(query_string, option) that returns setof some. +1. The incremental approach here should first be adding functions that actually do the work required. Then, if there's a set of those that look to be extremely useful, maybe at that point it's worth talking about how to integrate them into the parser. Starting with the parser changes rather than the parts that actually do the work is backwards. If you do it the other way around, at all times you have a patch that actually provides immediate useful value were it to be committed. Something that returns a setof can also be easily used to implement the dump EXPLAIN to a table feature Josh Tolley brought up (which is another common request in this area). A serious problem with EXPLAIN via a function returning set, or with putting the result into a table, is that set results are logically unordered, just as table contents are. So from a strict point of view this only makes sense when the output format is designed to not depend on row ordering to convey information. We could certainly invent such a format, but I think it's a mistake to go in this direction for EXPLAIN output that is similar to the current output. The Oracle version, as it fills the table of explain results, gives each number an id and the id of its parent row, which behavior we could presumably copy. Or some other schema that allows us to preserve the tree. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore --multi-thread
On 2009-02-12, at 14:15 , Jonah H. Harris wrote: On Thu, Feb 12, 2009 at 11:37 AM, Joshua D. Drake j...@commandprompt.com wrote: --num-workers or --num-connections would both work. --num-parallel? --num-concurrent? Michael Glaesemann michael.glaesem...@myyearbook.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: new border setting in psql
On Jan 8, 2009, at 13:56 , Joshua D. Drake wrote: There is interest in ReST for anyone doing a lot more than Python or Trac. Although that area is certainly strong with it. It is quickly becoming one of the more dominant technologies in delivering web services (now whether or not that is useful here is another argument). I think there may be confusion here betwixt ReST/RST and REST. REST: http://en.wikipedia.org/wiki/Representational_State_Transfer ReST/RST: http://en.wikipedia.org/wiki/ReStructuredText Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Constraint partition index usage
(cost=0.03..0.04 rows=1 width=0) InitPlan - Limit (cost=0.00..0.03 rows=1 width=8) - Index Scan using bars_20080915_logged_at_idx on bars_20080915 (cost=0.00..167466.14 rows=5586490 width=8) Filter: (logged_at IS NOT NULL) (5 rows) production=# explain select min(logged_at) from foo.bars; QUERY PLAN -- Aggregate (cost=913072.22..913072.23 rows=1 width=8) - Append (cost=0.00..802333.27 rows=44295577 width=8) - Seq Scan on bars (cost=0.00..32.70 rows=1770 width=8) - Seq Scan on bars_20080915 bars (cost=0.00..101199.40 rows=5586490 width=8) - Seq Scan on bars_20080922 bars (cost=0.00..215666.84 rows=11907734 width=8) - Seq Scan on bars_20080908 bars (cost=0.00..32.70 rows=1770 width=8) - Seq Scan on bars_20080901 bars (cost=0.00..32.70 rows=1770 width=8) - Seq Scan on bars_20080825 bars (cost=0.00..32.70 rows=1770 width=8) - Seq Scan on bars_20080818 bars (cost=0.00..32.70 rows=1770 width=8) - Seq Scan on bars_20080811 bars (cost=0.00..32.70 rows=1770 width=8) - Seq Scan on bars_20080804 bars (cost=0.00..32.70 rows=1770 width=8) - Seq Scan on bars_20080929 bars (cost=0.00..215029.52 rows=11872652 width=8) - Seq Scan on bars_20081006 bars (cost=0.00..223559.96 rows=12343346 width=8) - Seq Scan on bars_20081013 bars (cost=0.00..46648.65 rows=2572965 width=8) (14 rows) I thought Greg Stark had looked at some performance tweaks wrt constraint partitioning, but I can't recall the details. Was that related to this at all? Michael Glaesemann [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] \df displaying volatility
On May 23, 2008, at 8:57 PM, Joshua D. Drake wrote: Alvaro Herrera wrote: Would anyone object to \df displaying a function's volatility? Maybe limit it to \df+? Ideally we would have a short header for the column so that it doesn't take too much space, and specify the setting with a single letter. The meaning of each letter we could display at the bottom of the table as a footer (something we were going to do for \z too I think?) Thoughts? I think it would be about time :) +1 I'd like to see the function comment as well in \df+. (And probably for most database objects that don't already show the comment.) Michael Glaesemann [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Jan 27, 2008, at 21:04 , Tom Lane wrote: [ redirecting thread to -hackers ] Neil Conway [EMAIL PROTECTED] writes: On Sun, 2008-01-27 at 21:54 +, Gregory Stark wrote: I liked the synchronized_sequential_scans idea myself. I think that's a bit too long. How about synchronized_scans, or synchronized_seqscans? We have enable_seqscan already, so that last choice seems to fit in. Would it make sense to match the plural as well? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Integer datatimes vs msvc
On Dec 3, 2007, at 11:27 , Tom Lane wrote: I believe we have consensus that 8.4 is the time to do that --- see thread here: http://archives.postgresql.org/pgsql-patches/2007-05/msg00046.php Is there anything in the release notes (or elsewhere) for 8.3 that mention this as the intention, similar to the formerly deprecated money type? Would we want to? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] minimal update
On Nov 8, 2007, at 10:46 , Andrew Dunstan wrote: Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: What would be the disadvantages of always doing this, i.e., just making this part of the normal update path in the backend? (1) cycles wasted to no purpose in the vast majority of cases. (2) visibly inconsistent behavior for apps that pay attention to ctid/xmin/etc. (3) visibly inconsistent behavior for apps that have AFTER triggers. There's enough other overhead in issuing an update (network, parsing/planning/etc) that a sanely coded application should try to avoid issuing no-op updates anyway. The proposed trigger is just a band-aid IMHO. I think having it as an optional trigger is a reasonable compromise. Right. I never proposed making this the default behaviour, for all these good reasons. The point about making the app try to avoid no-op updates is that this can impose some quite considerable code complexity on the app, especially where the number of updated fields is large. It's fragile and error-prone. A simple switch that can turn a trigger on or off will be nicer. Syntax support for that might be even nicer, but there appears to be some resistance to that, so I can easily settle for the trigger. This confirms what I thought. Thanks. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] minimal update
On Nov 2, 2007, at 13:44 , Andrew Dunstan wrote: Ah. Good. Thanks, that's the piece I was missing. What would be the disadvantages of always doing this, i.e., just making this part of the normal update path in the backend? I'd think it should save on unnecessarily dead tuples as well. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS]
On Oct 31, 2007, at 11:10 , [EMAIL PROTECTED] wrote: I want to unsubscrib me please, From the headers of every list message: List-Archive: http://archives.postgresql.org/pgsql-hackers List-Help: mailto:[EMAIL PROTECTED] List-ID: pgsql-hackers.postgresql.org List-Owner: mailto:[EMAIL PROTECTED] List-Post: mailto:pgsql-hackers@postgresql.org List-Subscribe: mailto:[EMAIL PROTECTED] hackers List-Unsubscribe: mailto:[EMAIL PROTECTED] 20pgsql-hackers And from the website: http://archives.postgresql.org/pgsql-hackers/ Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Latin vs non-Latin words in text search parsing
On Oct 23, 2007, at 10:42 , Tom Lane wrote: apart_hword Part of hyphenated word, all ASCII letters part_hword Part of hyphenated word, all letters numpart_hword Part of hyphenated word, mixed letters and digits Is there a rationale for using these instead of hword_apart, hword_part and hword_numpart? I find the latter to be more readable as variable names. Or was your thought to be able to identify the content from the first part of the variable name? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Latin vs non-Latin words in text search parsing
On Oct 23, 2007, at 12:09 , Alvaro Herrera wrote: Tom Lane wrote: OK, so with that and Michael's suggestion we have asciiword word numword asciihword hword numhword hword_asciipart hword_part hword_numpart Sold? Sold here. No huge preference, but I see benefit in what Gregory was saying re: asciiword, alphaword, alnumword. word itself is pretty general, while alphaword ties it much closer to its intended meaning. They've got pretty consistent lengths as well. Maybe it leans too Hungarian. I'll take your answer off the air :) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] rolcanlogin vs. the flat password file
On Oct 14, 2007, at 14:34 , Tom Lane wrote: I am not entirely convinced whether we should do anything about this: the general theory on authentication failures is that you don't say much about exactly why it failed, so as to not give a brute-force attacker any info about whether he gave a valid userid or not. So there's an argument to be made that the current behavior is what we want. But I'm pretty sure that it wasn't intentionally designed to act this way. Would there be a difference in how this is logged and how it's reported to the user? I can see where an admin (having access to logs) would want to have additional information such as whether a role login has failed due to not having login privileges or whether the failure was due to an incorrect role/password pair. I lean towards less information back to the user as to the nature of the failure. If the general consensus is to leave the current behavior, a comment should probably be included to note that the behavior is intentional. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Locales and Encodings
On Oct 12, 2007, at 10:19 , Gregory Stark wrote: It would make Postgres inconsistent and less integrated with the rest of the OS. How do you explain that Postgres doesn't follow the system's configurations and the collations don't agree with the system collations? How is this fundamentally different from PostgreSQL using a separate users/roles system than the OS? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Seems we need a post-beta1 initdb already
On Oct 12, 2007, at 17:41 , Tom Lane wrote: Also, if we do #2 it means that we have the option to resolve the contrib/txid mess by pushing txid into the core backend before beta2. Any votes pro or con on that? +1 Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release notes introductory text
On Oct 11, 2007, at 18:51 , Joshua D. Drake wrote: With respect to you Kevin, your managers should wait. You don't install .0 releases of any software into production without months of testing. At which point, normally a .1 release has come out anyway. At the same time, an open source project such as PostgreSQL provides advantages here, in that preliminary testing can be performed during the development of the release, verified, of course, after the release has been made. Michael Glaesemann grzm seespotcode net PGP.sig Description: This is a digitally signed message part
Re: [HACKERS] quote_literal with NULL
On Oct 10, 2007, at 11:24 , Greg Sabino Mullane wrote: (Aside: seems to me that SET foo = NULL; really should be SET foo TO NULL; to be consistent with WHERE foo IS NULL;) The = character has different meanings in these two cases. UPDATE foos SET foo = NULL -- assignment WHERE bar IS NULL -- comparison AND foo = 'ignore me' -- comparison Or is that what the smiley was about? :) Michael Glaesemann grzm seespotcode net PGP.sig Description: This is a digitally signed message part
Re: [HACKERS] Skytools committed without hackers discussion/review
On Oct 10, 2007, at 13:30 , Tom Lane wrote: That could perhaps be addressed by merging it into 8.4 before anyone does any snapshot fixing, but our track record on causing such things to happen in a particular sequence isn't great ... Granted, everyone's focused on the 8.3 branch right now, but with the enthusiasm of those who want txid, I can't help but think there'd be a patch ready and waiting the day 8_3_STABLE is tagged. And there's no reason not to have something submitted to -patches right now (unless it's not ready)—there are patches in the patch queue that didn't make it in before feature freeze. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Skytools committed without hackers discussion/review
On Oct 9, 2007, at 0:06 , Bruce Momjian wrote: I am surprised we are not backing out the patch and requiring that the patch go through the formal review process. I have no opinion as to the patch itself (other than the fact that it's a not bug fix), but I think this patch should be reverted because it's (a) after feature freeze, (b) had no discussion on hackers (or patches), (c) is not a bug fix. IMO rules can be bent but there should always at least be discussion before a new feature is committed after feature freeze and definitely after beta. Otherwise, the rule appears to be if you can get it in somehow, it's in. Again, I have no opinion regarding the patch itself, and these issues are regardless of who commits or submits. Personally, I regard Jan as a helpful guy and a solid coder who has contributed a lot to PostgreSQL in the past and I'm sure will contribute even more in the future. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] IDE
On Oct 1, 2007, at 10:27 , Adrian Maier wrote: On 10/1/07, Pedro Belmino [EMAIL PROTECTED] wrote: Hello, I am having problems of productivity with IDE that I am using. Exists some IDE that recommended to develop postgresql? Hello Pedro, You are probably looking for a tool like pgAdmin (http:// www.pgadmin.org) or PhpPgAdmin (http://phppgadmin.sourceforge.net). There are also some commercial applications : http://www.postgresql.org/download/ commercial However, please take notice that your question is not suited for the pgsql-hackers mailing list : this list is for discussions about developing PostgreSQL itself. That may be what he means. Unfortunately develop PostgreSQL can be taken both ways. In case he's working on internals, I believe Emacs is used by a number of PostgreSQL hackers. And as for developing PostgreSQL-backed applications, I find $EDITOR + psql to work quite well. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Hash index todo list item
On Sep 25, 2007, at 11:26 , Kenneth Marshall wrote: Although I am very excited about this patch, I do not see any real value in including it in 8.3. I don't think you have to worry about it being in 8.3. Feature freeze was months ago. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SPI access to PostgreSQL query plan
On Sep 17, 2007, at 19:46 , Florian G. Pflug wrote: Thats only holds true for functions in languages other than pl/sql (Which is *not* the same as pl/pgsql) - SQL functions can be inlined by the executor, and then are subject to the usual optimizations. (So they essentially behave like views). AIUI, the stress is on the *can*, with a meaning of may, right? Not all SQL functions can be inlined. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Hash index todo list item
On Sep 6, 2007, at 10:53 , Mark Mielke wrote: I don't like the truncating hash suggestion because it limits the ability of a hash code to uniquely identify a key. AIUI, a hash can't be used as a unique identifier: it always needs to be rechecked due to the chance of collisions. There might be other issues with truncation, but preventing hashes from being unique isn't one of them. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] [HACKERS] Contrib modules documentation online
On Aug 29, 2007, at 13:27 , Andrew Dunstan wrote: Also, let's recall what has previously been discussed for contrib, namely that we break it out into standard modules (think Perl standard modules) and other tools, and that we abandon the wholly misleading contrib name altogether. I really want to see that happen next release. +1 Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SQL feature requests
On Aug 23, 2007, at 10:47 , Ben Tilly wrote:
On 8/22/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
*This* seems like a bug:
test=# select record_id
, count(observation_id) as bar
from observation
group by record_id
, case when true
then 'foo'
end;
record_id | bar
---+-
1 | 4
2 | 4
3 | 2
(3 rows)
Why does it seem like a bug to you?
Turn it around, and tell me in what way is its behaviour surprising to
someone who knows SQL. You asked to group on something that is the
same for all rows. That group by condition did nothing. (Except
rendered the syntax valid when it might not have been.) As I would
expect.
Considering that I expect the GROUP BY clause to include only column
references (or other groupings of column references), not
expressions. Whether or not the value is the same, it surprises me
that something other than a column reference is accepted at all. I
hadn't realized this behavior was accepted in PostgreSQL, but I learn
something new every day.
My non-rigorous way of thinking about GROUP BY is that it groups this
listed columns when the values of the listed columns are the same. An
expression that evaluates to anything other than a column name
doesn't provide any information about which column to consider
grouped, and expressions don't evaluate to column names, or
identifiers in general. If I understand you correctly, a GROUP BY
item that isn't a column name would be a value that's applied to all
columns, and the actual value is irrelevant—different values don't
change the result.
So the only purpose it would serve would be to prevent a trailing
comma from raising a syntax error: you'd still need to explicitly
list the other columns (unless the implementation behavior is changed
to extend the spec there as well). What this does is allow you to use
something like this (psuedocode):
group_columns = [ 'foo', 'bar', 'baz' ]
group_column_list = ''
for col in group_columns { group_column_list += col + ',' } #
group_column_list = foo,bar,baz,
group_by_clause = GROUP BY $group_column_list CASE WHEN TRUE THEN
'quux' END
rather than
group_column_list = join group_columns, ',' # group_column_list =
foo,bar,baz
group_by_clause = GROUP BY $group_column_list
I still feel I'm missing something. If that's it, it seems like
something easy enough to handle in middleware. Sorry if it appears
I'm being dense. I've definitely learned things in this thread.
Furthermore ask yourself whether anyone who wrote that would likely
have written it by accident.
I don't see what that has to do with anything. There are plenty of
things I can write on purpose that would be nonsense. You might even
consider my posts as prime examples :)
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] SQL feature requests
On Aug 23, 2007, at 12:25 , Ben Tilly wrote: It is already extended in postgres. For pretty good reasons. Yes, this particular point is moot. Whether or not the reasons are good is another thing and a difference of opinion. I'm not surprised that the window clause section is impenetrable to you. Window clauses are part of the definition of analytic functions, which postgres does NOT yet implement. However they are on the todo list. And being worked on, from what I gather. Just not for 8.3. So hopefully you won't have to wait too much longer. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SQL feature requests
On Aug 23, 2007, at 14:25 , Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: I just don't see the ability to omit the alias in a query with only one subquery (the only circumstances under which it would be safe to do so) as any significant gain in fuctionality. Why do you think it'd be restricted to only one subquery? As long as you take care that the subquery's column names don't match any other ones in the query, you don't *need* an alias for it --- there'll be no need to qualify the column names. This extends just fine to multiple subqueries. How about something like gensym? One alias you could always use and be guaranteed it would give a unique value. Still provide the alias, but don't have to think about name collisions. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SQL feature requests
On Aug 22, 2007, at 18:45 , Ben Tilly wrote: 1. Just a minor annoyance, but why must subqueries in FROM clauses have an alias? It's required by the SQL standard, AIUI. I wonder what EnterpriseDB does? 2. Why is 'non-integer constant in GROUP BY' an error? This works for now: case when true then true end but I don't know whether some future version of postgres might break my code by banning that as well. The PostgreSQL developers generally tries hard to preserve backwards compatibility, so I doubt the case expression as you have it would go away (though I'm kind of surprised it's allowed). Am I wrong in thinking that Oracle would accept the same format PostgreSQL does? In that case, couldn't you use whatever method works in PostgreSQL in Oracle? I haven't checked the SQL standard, but it seems unlikely it'd allow something like GROUP BY , , , ; AIUI, Integers are only allowed because the SQL standard explicitly allows you to refer to columns by the order they appear in the SELECT list. Otherwise the GROUP BY items need to be column names. Both 1 and 2 seem to me to be places where Oracle is likely deviating from the standard. If you're targeting Oracle, then using Oracle- specific syntax might be warranted. If you're hoping to target more than one possible backend, I'd think it be better to use more portable syntax (e.g., SQL-standard syntax) than expecting other DBMSs to follow another's deviations. That's not to say PostgreSQL does not have non-standard syntax: in places, it does. But it does try to hew very closely to the standard. Again, I wonder what EnterpriseDB does in this case? 3. How hard would it be to have postgres ignore aliases in group by clauses? Per my comments above, I often build complex queries in code. I can't easily use the shortcut of referring to the select column by number because the position is hard to determine. So my code has to copy the select terms. But I can't copy them exactly because the select terms include lots of ...as foo clauses that are not allowed in a group by. So I have to store very similar terms to use twice. Perhaps someone else knows what you're referring to here, but I'm having a hard time without an example. Here's what I *think* you're trying to say: test=# select * from observation; observation_id | record_id | score_id +---+-- 3240 | 1 |1 3239 | 1 |1 3238 | 1 |2 3237 | 1 |1 2872 | 2 |1 2869 | 2 |2 2870 | 2 |1 2871 | 2 |1 3218 | 3 |2 3217 | 3 |1 (10 rows) test=# select record_id as foo, count(observation_id) from observation group by record_id; foo | count -+--- 3 | 2 2 | 4 1 | 4 (3 rows) test=# select record_id as foo, count(observation_id) from observation group by foo; foo | count -+--- 3 | 2 2 | 4 1 | 4 (3 rows) test=# select record_id as foo, count(observation_id) as bar from observation group by foo; foo | bar -+- 3 | 2 2 | 4 1 | 4 (3 rows) test=# select record_id as foo, count(observation_id) as bar from observation group by record_id; foo | bar -+- 3 | 2 2 | 4 1 | 4 (3 rows) test=# select version(); version -- PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5367) (1 row) I'm not getting an error in any permutation that I can think of. What am I missing? Which is to assume that a query without a group by clause, but with an aggregate function in the select, should have an implicit group by clause where you group by all non-aggregate functions in the select. For example SELECT foo, count(*) FROM bar would be processed as: SELECT foo, count(*) FROM bar GROUP BY foo It's been discussed before. I don't believe it's been rejected out of hand (though you can check the archives), just that no one's gotten around to it. (Don't know what the SQL-spec says on this point.) I'm not trying to dismiss your points, just trying to address them. I'm interested to hear what others have to say. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SQL feature requests
On Aug 22, 2007, at 20:36 , Ben Tilly wrote:
On 8/22/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
On Aug 22, 2007, at 18:45 , Ben Tilly wrote:
1. Just a minor annoyance, but why must subqueries in FROM clauses
have an alias?
It's required by the SQL standard, AIUI. I wonder what EnterpriseDB
does?
I can well believe that the standard says that you must accept
subqueries with aliases. But does it say you must reject subqueries
without aliases? I strongly doubt that.
If I'm reading my draft copy of the SQL:2003 spec right (and there's
a good chance that I'm not, as it's not the easiest document for me
to parse), aliases *are* required.
From 5WD-02-Foundation-2003-09 (the fifth working draft)
7.5 from clause
from clause ::= FROM table reference list
table reference list ::=
table reference [ { comma table reference }... ]
7.6 table reference
table reference ::=
table factor
| joined table
table factor ::= table primary [ sample clause ]
table primary ::=
table or query name [ [ AS ] correlation name
[ left paren derived column listright paren ] ]
| derived table [ AS ] correlation name
[ left paren derived column listright paren ]
| lateral derived table [ AS ] correlation name
[ left paren derived column listright paren ]
| collection derived table [ AS ] correlation name
[ left paren derived column listright paren ]
| table function derived table [ AS ] correlation name
[ left paren derived column listright paren ]
| only spec [ [ AS ] correlation name
[ left paren derived column listright paren ] ]
| left paren joined table right paren
derived table ::= table subquery
correlation name I believe is what we commonly refer to as an
alias. I think table or query name is a table or view name, and
derived table is a subquery. correlation name is optional for
table or query name but not for derived table. The fact that the
standard clearly makes it optional in some cases and required in
others is pretty clear that derived table without a correlation
name is to be rejected, don't you think?
I have no clue what EnterpriseDB does.
In case it wasn't clear, the reason I bring it up is that
EnterpriseDB, while working from a PostgreSQL base, strives for
Oracle compatibility.
that case, couldn't you use whatever method works in PostgreSQL in
Oracle? I haven't checked the SQL standard, but it seems unlikely
it'd allow something like
GROUP BY , , , ;
That's not what Oracle accepts that postgres does not. What
Oracle accepts is:
...
GROUP BY 'foo';
Thanks for clarifying. If it wasn't obvious by now, you know I'm not
familiar with Oracle :)
AIUI, Integers are only allowed because the SQL standard explicitly
allows you to refer to columns by the order they appear in the SELECT
list. Otherwise the GROUP BY items need to be column names.
Need to be?
The SQL-92 standard is clear that you must accept a list of column
names. It is also clear that a column name must be be of the form
field or table.field.
The 2003 draft (same as above) seems to agree with the SQL92 standard:
7.9 group by clause
Format
group by clause ::=
GROUPBY [ set quantifier ] grouping element list
grouping element list ::=
grouping element [ { comma grouping element }... ]
grouping element ::=
ordinary grouping set
| rollup list
| cube list
| grouping sets specification
| empty grouping set
ordinary grouping set ::=
grouping column reference
| left paren grouping column reference listright paren
grouping column reference ::=
column reference [ collate clause ]
grouping column reference list ::=
grouping column reference [ { commagrouping column
reference }... ]
rollup list ::=
ROLLUPleft paren ordinary grouping set listright paren
ordinary grouping set list ::=
ordinary grouping set [ { comma ordinary grouping set }... ]
cube list ::=
CUBEleft paren ordinary grouping set listright paren
grouping sets specification ::=
GROUPINGSETS left paren grouping set listright paren
grouping set list ::=
grouping set [ { comma grouping set }... ]
grouping set ::=
ordinary grouping set
| rollup list
| cube list
| grouping sets specification
| empty grouping set
empty grouping set ::= left parenright paren
6.7 column reference
Format
column reference ::=
basic identifier chain
| MODULEperiod qualified identifierperiod column name
There'd have to be a pretty strong reason to extend this, more than
just a convenience, I should think.
In no way, shape or form does that allow having
terms like trim(foo.bar) in a group by.
But every reasonable database that I know - including postgres -
allows that.
Can you give an example of something like this working in PostgreSQL?
I get an error when I try to use a text value in a GROUP BY clause.
(Or are you referring specifically to the CASE expression corner case?)
test=# select
Re: [HACKERS] SQL feature requests
On Aug 22, 2007, at 20:49 , Ben Tilly wrote: If your implementation accepts: group by case when true then 'foo' end What would that mean? Regardless of whether or not it's accepted, it should have *some* meaning. It's not equivalent to GROUP BY foo test=# select record_id as foo , count(observation_id) as bar from observation group by case when true then 'foo' end; ERROR: column observation.record_id must appear in the GROUP BY clause or be used in an aggregate function test=# select record_id , count(observation_id) as bar from observation group by case when true then 'record_id' end; ERROR: column observation.record_id must appear in the GROUP BY clause or be used in an aggregate function *This* seems like a bug: test=# select record_id , count(observation_id) as bar from observation group by record_id , case when true then 'foo' end; record_id | bar ---+- 1 | 4 2 | 4 3 | 2 (3 rows) And for good measure: test=# select record_id , count(observation_id) as bar from observation group by case when true then record_id end; ERROR: column observation.record_id must appear in the GROUP BY clause or be used in an aggregate function Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] change name of redirect_stderr?
On Aug 18, 2007, at 20:44 , Andrew Dunstan wrote: Logging_collector won the day. I have just committed CSVlogs with that change. Congrats! A couple last-minute correx: http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ func.sgml?r1=1.385r2=1.386 s/log collector if running/log collector is running/ Might you want to use logging collector here, just to reinforce the term? http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ config.sgml?r1=1.137r2=1.138 varnamestart_log_collector/varname must be enabled to generate varnamelogging_collector/varname must be enabled to generate Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] change name of redirect_stderr?
On Aug 14, 2007, at 12:40 , Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: The problem here is that log seems to be a verb in log_collector which is what makes it confusing. So we need another verb to make it clear that log is not one. This is not a problem with autovacuum because that one cannot be confused with a verb. start_log_collector still gets my vote. log_collector_enable or log_collector_start or even log_redirect. But something with log_* I'm voting with Alvaro on this. All of your suggestions are confusing because log looks like the verb, which it is not. Specifically, they sound like what the switch does is to cause a log message to be emitted about some action that would occur anyway. AIUI, if the-GUC-yet-to-be-named is not enabled, no logging is done at all: messages are just sent to stderr. Why something simple like enable_logging or start_logger? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [EMAIL PROTECTED]: Re: [GENERAL] array_to_set functions]
On Aug 8, 2007, at 11:41 , Decibel! wrote: On Tue, Aug 07, 2007 at 10:18:32PM -0700, Jeremy Drake wrote: select * from information_schema._pg_expandarray(ARRAY['foo', 'bar', 'baz']); x | n -+--- foo | 1 bar | 2 baz | 3 (3 rows) Not exactly well documented or well known, but it works. Worse than that, that's something that's entirely internal and could change at any release. The fact that it exists for info_schema indicates further need for these functions to exist in the backend. Personally, I think expandarray is more appropriate and its functionality probably more generally useful, as it identifies the array indices as well. Note you can also rename the columns. select * from information_schema._pg_expandarray(ARRAY['foo', 'bar', 'baz']) as b(a,i); a | i -+--- foo | 1 bar | 2 baz | 3 (3 rows) array_to_set really isn't, as AFAICS it didn't guarantee element uniqueness (but that's just a naming issue). Michael Glaesemann grzm seespotcode net PGP.sig Description: This is a digitally signed message part
Re: [HACKERS] [EMAIL PROTECTED]: Re: [GENERAL] array_to_set functions]
On Aug 8, 2007, at 12:18 , Decibel! wrote: On Wed, Aug 08, 2007 at 12:03:34PM -0500, Michael Glaesemann wrote: Personally, I think expandarray is more appropriate and its functionality probably more generally useful, as it identifies the array indices as well. Note you can also rename the columns. Sure. My point is that we should have a way to convert arrays to sets and back in the backend. Can't really argue with you there, as I find array_accum myself. (Though I'd still nit-pick that this isn't an array to set conversion, but rather array to--possibly single-column--table.) Michael Glaesemann grzm seespotcode net PGP.sig Description: This is a digitally signed message part
Re: [HACKERS] Label Security and Fine-grained auditing
On Aug 4, 2007, at 7:06 , Rohit Khare wrote: One more feature that I am not sure PostgreSQL has is, row-level, column-level security. Oracle call this Label-Security in which you define a policy for certain columns so that they are not visible to un-authorised users during SELECT queries. This is an important security enhancement. One other feature is called Fine-Grained Auditing. Ability to track user activities. I hope this is in PostgreSQL in one form or the other. Would Veil suit your needs? http://veil.projects.postgresql.org/ Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] log_autovacuum
On Aug 3, 2007, at 14:59 , Simon Riggs wrote: On Fri, 2007-08-03 at 12:38 -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Gregory Stark wrote: Could I suggest renaming log_autovacuum to log_autovacuum_min_duration? Sure, whatever makes the most sense. In fact min_duration would be more consistent. I'm not sure I believe Greg's argument about needing more autovac logging parameters, but since this one acts just like log_min_duration_statement, I concur with renaming it. log_min_duration_autovacuum makes the most sense in comparison, IMHO. True, but the log_min_duration_statement is kind of poorly named (as is log_min_error_statement). log_statement is the overall concept, min_duration and min_error further specialize the concept. log_statement_min_duration and log_statement_min_error would have been better, IMO. Question is whether it's better to move forward with consistent naming or improve naming when the chance arises. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Updated tsearch documentation
On Jul 17, 2007, at 16:24 , Bruce Momjian wrote: I assume my_filter_name is optional right? I have updated the prototype to be: tsearch([vector_column_name], [my_filter_name], text_column_name [, ... ]) Just a style point, but would [filter_name] be better than [my_filter_name]? You're not qualifying the others with my_ ... or is there something you want to tell us, Bruce? :) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [Pgbuildfarm-members] time to play ...
On Jun 29, 2007, at 10:25 , Andrew Dunstan wrote: I will be on vacation in Spain for the next two weeks, and only sporadically in electronic contact. Have a great time! Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Worries about delayed-commit semantics
On Jun 22, 2007, at 9:23 , Richard Huxton wrote: Or perhaps sync_on_commit = off? Or switch it around... sink_on_commit = on (sorry for the noise) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch in core patch
On Jun 22, 2007, at 9:28 , Tom Lane wrote: Is the point here for initdb to be able to establish a sane default initially? Seems to me it can guess the language from the first component of the locale (ru_RU - russian). How would this work for initdb with locale C? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Change sort order on UUIDs?
On Jun 14, 2007, at 19:04 , [EMAIL PROTECTED] wrote: For UUID, I would value random access before sequential performance. Why would anybody scan UUID through the index in sequential order? AIUI, to allow UUID columns to be indexed using BTREE, there needs to be some ordering defined. So regardless of what this ordering is, doesn't there need to be some order? And as a (primary?) purpose of UUIDs is to be (universally) unique, and the implementation of uniqueness constraints in PostgreSQL is based on BTREE indexes, this makes the necessity of ordering doubly so. Or have I missed something? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] question for serial types with CHECK conditions
On Apr 28, 2007, at 10:30 , Guido Barosio wrote: Now, my question is: Shouldn't postgresql avoid the creation of the table while a serial type contains a check condition? My question to you is why should it? a SERIAL is a shorthand for creating an INTEGER column a, a sequence (a_seq) with a dependency, and DEFAULT nextval(a_seq). There may be a valid reason someone wants to put additional constraints on the column, and I'm not sure why the server should second guess the DBA in this case. If the CHECK constraint isn't what you want, then don't include it: and in this case the server helpfully gave you an error which let you know that the CHECK constraint was not doing what you expected. Also, the server doesn't have the smarts to look into the CHECK constraint and decide if it makes sense in your case. For example, perhaps you want to have CHECK (a 0), which won't really do anything for a default sequence. However, if the sequence is changed, it may return negative integers, which you may not want, so in some cases, CHECK (a 0) may be a valid constraint *in your case*. The crux of the issue is that there may be valid reasons to have a CHECK constraint on a INTEGER (SERIAL) column, and the server is not (and will probably never be) smart enough to know your particular business rules without you telling it specifically. Does this help clarify the situation? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] functions to obtain query plan
On Apr 21, 2007, at 4:46 , sharath kumar wrote: For a particular query, are there any functions which can give me the start-up cost, total run-cost, number of rows and width? -hackers is a list for discussion of development of PostgreSQL itself. Your question would probably be more appropriate in -general (which I am cc'ing) or perhaps -performance. I believe EXPLAIN ANALYZE will provide what you're looking for. The PostgreSQL documentation is a good place to get started: http://www.postgresql.org/docs/8.2/interactive/sql-explain.html http://www.postgresql.org/docs/8.2/interactive/performance-tips.html Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Developer TODO List as a PostgreSQL DB
On Feb 27, 2007, at 23:40 , Andrew Dunstan wrote: Maybe we need some extra FAQs, like: . Why do you still use CVS instead of insert favorite SCM system here? I just saw a patch from Robert Treat on just this topic. Doesn't look like its been applied yet. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] period data type
On Feb 5, 2007, at 12:47 , Warren Turkal wrote: Is anyone working on a period data type as described in Dr. Richard Snodgrass' book _Developing Time-Oriented Database Applications in SQL_[1]? I did not see a relevant project listed in the TODO. I would like to contribute (possible funding and/or coding) the development of a conforming implementation of this data type. [1]http://www.cs.arizona.edu/~rts/tdbbook.pdf This is an area I'm actively working on. I've worked out an example using composite types and PL/pgsql, though it's not very performant. I'm currently looking at making a contrib module, but have been slowed a bit by my lack of C experience. Regarding conformance, as there's currently no standard, I'm leary of cleaving too closely to the Temporal SQL proposal to ward of making something that would be close but not quite conformant to a future addition to the SQL standard. Also, I hope to make a more general interval/range/period constructor that would allow you to make periods of other types (say, integers) as well. I've found Date/Darwen/Lorentzos' Temporal Data and the Relational Model[1] very helpful. Interested in possibly working together? Michael Glaesemann grzm seespotcode net [1]http://books.elsevier.com/uk/mk/uk/subindex.asp?isbn=9781558608559 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Modifying and solidifying contrib
On Jan 31, 2007, at 12:42 , David Fetter wrote: On Tue, Jan 30, 2007 at 03:49:14PM -0500, Andrew Dunstan wrote: 6. they all need proper docs. READMEs and the like are nowhere near good enough. Agreed. I'm thinking a new major section in the SGML docs is in order with a subsection for each contrib/ piece underneath. I agree re: new section. Are you thinking that all contrib docs would be built automatically, even if the individual extensions (neé contrib modules?) aren't installed? I think that would definitely raise awareness of the extensions that are available. I'd also like to see being able to add docs for non-core extensions (e.g., ip4r) to the main documentation. Not sure what that would involve: rebuilding the tocs and index, besides the new pages themselves? Or perhaps just a rebuild of the complete docs? I haven't had docs building on a local system for a couple of years, so I'm not it a position currently to play around with this, but it's something I'd love to learn how to do. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Modifying and solidifying contrib
On Jan 28, 2007, at 11:25 , Joshua D. Drake wrote: David Fetter wrote: Not so great. SQL:2003 has a special meaning for the word module. Yeah I saw mention of that in another thread, but I really didn't like the word plugins. Do you have another thought? Extensions? Extensions would tie in nicely with its common use in the docs, especially wrt pgxs: PostgreSQL can be extended by the user in many ways ... PostgreSQL also accepts escape string constants, which are an extension to the SQL standard To use the infrastructure for your extension ... Here is an example that builds an extension module ... They test standard SQL operations as well as the extended capabilities of PostgreSQL. The pgxs docs do use module as well, but as previously mentioned module already has a particular meaning in the spec. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ideas for auto-processing patches
On Jan 9, 2007, at 20:41 , Jim C. Nasby wrote: On Mon, Jan 08, 2007 at 10:40:16PM -0600, Michael Glaesemann wrote: On Jan 8, 2007, at 19:25 , Jim C. Nasby wrote: Actually, I see point in both... I'd think you'd want to know if a patch worked against the CVS checkout it was written against. Regardless, it's unlikely that the patch was tested against all of the platforms available on the build farm. If it fails on some of the build|patch farm animals, or if it fails due to bitrot, the point is it fails: whatever version the patch was generated against is pretty much moot: the patch needs to be fixed. Wouldn't there be some value to knowing whether the patch failed due to bitrot vs it just didn't work on some platforms out of the gate? I'm having a hard time figuring out what that value would be. How would that knowledge affect what's needed to fix the patch? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ideas for auto-processing patches
On Jan 11, 2007, at 10:35 , Richard Troy wrote: On Wed, 10 Jan 2007, Jim C. Nasby wrote: On Thu, Jan 11, 2007 at 08:04:41AM +0900, Michael Glaesemann wrote: Wouldn't there be some value to knowing whether the patch failed due to bitrot vs it just didn't work on some platforms out of the gate? I'm having a hard time figuring out what that value would be. How would that knowledge affect what's needed to fix the patch? I was thinking that knowing it did work at one time would be useful, but maybe that's not the case... Has it ever worked is the singularly most fundamental technical support question; yes, it has value. You'd be able to see whether or not it ever worked by when the patch first hit the patch farm. One question here - rhetorical, perhaps - is; What changed and when? This is recorded in the current build farm. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO item: update source/timezone for 64-bit tz files
On Sep 17, 2006, at 2:34 , Tom Lane wrote: Back when we converted src/timezone to use int64 for pg_time_t, we wondered what to do about extending the compiled timezone data file format for int64, so that it would work for years beyound 2038. We shelved the problem waiting to see what the upstream zic folks would do. Well, it looks like they've done something about it. So I think we ought to plan on updating our code to match theirs, so that we fix the y2038 problem while keeping it possible to use a standard zic-database installation with Postgres. This is not urgent (I surely see no need to hold up 8.2 to fix it), but it ought to go on the TODO list. regards, tom lane Did this get fixed? I don't see it in the release notes for 8.2 or on the current TODO. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO item: update source/timezone for 64-bit tz files
On Jan 11, 2007, at 12:51 , Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: Did this get fixed? I don't see it in the release notes for 8.2 or on the current TODO. No, nothing's been done. It's going to be a minor PITA, likely, since our sources have diverged from upstream --- someone will have to go through the upstream changes by hand and apply them :-( Any volunteers? I just want to make sure it gets on the TODO if it hasn't been done. Thanks for confirming. Bruce, could this get added? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ideas for auto-processing patches
On Jan 8, 2007, at 19:25 , Jim C. Nasby wrote: Actually, I see point in both... I'd think you'd want to know if a patch worked against the CVS checkout it was written against. Regardless, it's unlikely that the patch was tested against all of the platforms available on the build farm. If it fails on some of the build|patch farm animals, or if it fails due to bitrot, the point is it fails: whatever version the patch was generated against is pretty much moot: the patch needs to be fixed. (And isn't the version number included in the patch if generated as a diff anyway?) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Reverse-sort indexes and NULLS FIRST/LAST sorting
On Jan 4, 2007, at 13:33 , Tom Lane wrote:
Another possible objection is that in the proposed CREATE INDEX syntax
index-column-id [ opclass-name ] [ DESC ] [ NULLS {FIRST|LAST} ]
DESC must be a fully reserved word else it can't be distinguished from
an opclass name. But guess what, it already is.
A point in favor of using DESC over REVERSE as you had earlier
proposed is that DESC is already a reserved word, while REVERSE isnt'
even in the list of key words. As DESC is quite closely associated
with its antonym ASC wrt ordering, any thoughts of allowing ASC as an
optional noise word? Users may be surprised if ASC were to throw an
error.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Better management of mergejoinable operators
On Dec 13, 2006, at 7:56 , Tom Lane wrote: Right offhand I cannot see a reason why there should be different equality operators with the same sortops. (If anyone can come up with a plausible scenario for that, stop me here...) So what I'm thinking about is a unique index on oprlsortop/oprrsortop; that would both allow efficient search, and prevent multiple answers. I think this makes sense. Would this be affected at all by equality of text strings, taking into account locale? Or would there be equality for text in each locale (so oprlsortop and oprrsortop would always be not only the same type (text) but also of the same locale)? I'd think this is would be the case so it wouldn't end up being a problem. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Better management of mergejoinable operators
On Dec 13, 2006, at 8:45 , Tom Lane wrote: the entire operator/function structure is built on the assumption that there is, say, only one = between any two datatypes. You mean only on = between any two values of a given datatype? Or is there something else I'm missing? So what you're doing will just reinforce that. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Better management of mergejoinable operators
On Dec 13, 2006, at 12:33 , Michael Glaesemann wrote: On Dec 13, 2006, at 8:45 , Tom Lane wrote: the entire operator/function structure is built on the assumption that there is, say, only one = between any two datatypes. You mean only on = between any two values of a given datatype? Ignore that. :) if that were true, you wouldn't need to have both left and right argument types. I think I got it now. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
On Oct 6, 2006, at 1:50 , Tom Lane wrote: I'm tempted to propose that we remove the justify_hours call, and tell anyone who really wants the old results to apply justify_hours() to the subtraction result for themselves. Not sure what the fallout would be, though. I'm tempted to support such a proposal. Is this something that we'd want to do for 8.2? There are some interval range checking fixes I'm working on for 8.3. Perhaps this could be rolled into that as well? Then again, range checking and behavior are two separate things. Considering how late it is in the cycle, perhaps the change in behavior should come in 8.3. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] @ versus ~, redux
On Sep 10, 2006, at 5:21 , Tom Lane wrote: In the category of egad, I found out that the tinterval datatype uses for contains! This is simply bizarre --- whether you approve of the inet-like notation or not, surely the arrows are pointing the wrong way. Given that tinterval is deprecated and not even documented, maybe this isn't worth fixing. Any thoughts? I'd say leave tinterval as it is. If it's ever updated to use the modern time types (rather than abstime), that would be a logical time to change it, it seems to me. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] @ versus ~, redux
On Sep 4, 2006, at 12:44 , Tom Lane wrote: OK, so if everyone is leaning to #3, the name game remains to be played. Do we all agree on this: x @ y means x contains y x @ y means x is contained in y Are we all prepared to sign a solemn oath to commit hara-kiri if we invent a new datatype that gets this wrong? No? Maybe these still aren't obvious enough. When I've been working on range/interval stuff, I tried to come up with a self-consistent set of operator symbols for the Allen operators, which includes the contains and is contained in pair. Here's what I came up with. Where r1 and r2 are ranges r1 r2r1 is strictly during r2, i.e., r1 is a strict subset of r2 r1 r2r2 is strictly during r1, i.e., r2 is a strict subset of r1 and are meant to evoke the (strict) subset (⊂ or sub;) and superset (⊃ or sup;) operators. r1 = r2 r1 is a superset of r2 r1 = r2 r1 is a subset of r2 = and = are mean to evoke the subset (⊆ or sube;) and superset (⊇ or supe;) operators. Assuming the meaning of contains and is contained in is inclusive (rather than strict), then we'd have a = b : a contains b a = b : a is contained by b I've included the other Allen operators at the bottom for completeness. Michael Glaesemann grzm seespotcode net r1 = r2 r1 equals r2 r1 r2r1 does not equal r2 For the following, the or indicates the relative position of the two ranges if they were depicted on an line that increases from left to right. r1 | r2r1 strictly meets r2, i.e., begin(r2) is next(end(r1)) r1 | r2r2 strictly meets r2, i.e., begin(r1) is next(end(r2)) The | is meant to evoke the meeting point of r1 and r2. They don't overlap, they are just abutting. The or points to the direction the of the range it points to relative to the other range, i.e., r1 is to the left of r2 on an line that increases from left to right. r1 / r2r1 is before r2 r1 / r2r1 is after r2 The / is meant to evoke the fact that they are not abutting. r1 r2r1 strictly overlaps r2 r1 r2r2 strictly overlaps r1 The is meant to evoke and, in that there is something the two ranges share. r1 @ r2r1 starts r2 r1 @ r2r2 starts r1 r1 @ r2r1 finishes r2 r1 @ r2r2 finishes r1 The @ is meant to indicate the point where the two ranges share a begin or end point. E.g., for r1 @ r2, r1 and r2 start together, and end(r1) end(r2). For r1 @ r2, begin(r1) begin(r2), but they share the same end point. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Interval month, week - day
On Sep 1, 2006, at 9:32 , Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: On Sep 1, 2006, at 9:12 , Tom Lane wrote: I agree that this seems like an oversight in the original months/days/seconds patch, rather than behavior we want to keep. But is DecodeInterval the only place with the problem? I'll check on this tonight. Any idea where I might start to look? I'd look at the input routines for all the datetime types and see where they go. It's entirely possible that DecodeInterval is the only place with the problem, but I'd not assume that without looking. AFAICS, DecodeInterval is the only place that needed changing. I've looked through datetime.c, timestamp.c, date.c, and nabstime.c, and don't see anything else. It makes sense, too, as the only place where you could have weeks or non-integer months is during Interval input or interval multiplication/division. The pg_tm struct, which is used in time(stamp)?(tz)?/interval arithmetic only has integral months and no weeks component, so that shouldn't cause any problems. So, I think that's about it. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Interval month, week - day
On Sep 3, 2006, at 20:00 , Michael Glaesemann wrote:
On Sep 1, 2006, at 9:32 , Tom Lane wrote:
Michael Glaesemann [EMAIL PROTECTED] writes:
On Sep 1, 2006, at 9:12 , Tom Lane wrote:
I agree that this seems like an oversight in the original
months/days/seconds patch, rather than behavior we want to keep.
But is DecodeInterval the only place with the problem?
I'll check on this tonight. Any idea where I might start to look?
I'd look at the input routines for all the datetime types and see
where
they go. It's entirely possible that DecodeInterval is the only
place
with the problem, but I'd not assume that without looking.
AFAICS, DecodeInterval is the only place that needed changing. I've
looked through datetime.c, timestamp.c, date.c, and nabstime.c, and
don't see anything else. It makes sense, too, as the only place
where you could have weeks or non-integer months is during Interval
input or interval multiplication/division. The pg_tm struct, which
is used in time(stamp)?(tz)?/interval arithmetic only has integral
months and no weeks component, so that shouldn't cause any
problems. So, I think that's about it.
I realized there might be something in ecpg, and there was. I've
updated the ecpg DecodeInterval to match. However, I haven't been
able to get ecpg make check to work, so that part's untested.
Michael Glaesemann
grzm seespotcode net
Index: src/backend/utils/adt/datetime.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.169
diff -c -r1.169 datetime.c
*** src/backend/utils/adt/datetime.c25 Jul 2006 03:51:21 - 1.169
--- src/backend/utils/adt/datetime.c3 Sep 2006 23:55:34 -
***
*** 2920,2935
tm-tm_mday += val * 7;
if (fval != 0)
{
! int
sec;
!
! fval *= 7 *
SECS_PER_DAY;
! sec = fval;
! tm-tm_sec += sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval - sec) *
100;
#else
! *fsec += fval - sec;
#endif
}
tmask = (fmask DTK_M(DAY)) ?
0 : DTK_M(DAY);
break;
--- 2920,2942
tm-tm_mday += val * 7;
if (fval != 0)
{
! int extra_days;
! fval *= 7;
! extra_days = (int32)
fval;
! tm-tm_mday +=
extra_days;
! fval -= extra_days;
! if (fval != 0)
! {
! int
sec;
! fval *=
SECS_PER_DAY;
! sec = fval;
! tm-tm_sec +=
sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval
- sec) * 100;
#else
! *fsec += fval -
sec;
#endif
+ }
}
tmask = (fmask DTK_M(DAY)) ?
0 : DTK_M(DAY);
break;
***
*** 2938,2953
tm-tm_mon += val;
if (fval != 0)
{
! int
sec;
!
! fval *= DAYS_PER_MONTH
* SECS_PER_DAY;
! sec = fval;
! tm-tm_sec += sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval - sec) *
100;
#else
! *fsec
Re: [HACKERS] [PATCHES] Interval month, week - day
On Sep 4, 2006, at 9:41 , Tom Lane wrote: This patch fails to apply --- looks like whitespace got mangled in transit. Please resend as an attachment. Please let me know if you have any problems with this one. Michael Glaesemann grzm seespotcode net 10interval_input_0904T0855+0900.diff Description: Binary data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
