Re: [HACKERS] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c

2011-04-08 Thread Brendan Jurd
On 7 April 2011 16:56, Tom Lane t...@sss.pgh.pa.us wrote: Brendan Jurd dire...@gmail.com writes: TRAP: FailedAssertion(!((data - start) == data_size), File: heaptuple.c, Line: 255) [ scratches head ... ]  That implies that heap_fill_tuple came to a different conclusion about a tuple's data

[HACKERS] switch UNLOGGED to LOGGED

2011-04-08 Thread Leonardo Francalanci
Hi, I read the discussion at http://archives.postgresql.org/pgsql-hackers/2011-01/msg00315.php From what I can understand, going from/to unlogged to/from logged in the wal_level == minimal case is not too complicated. Suppose I try to write a patch that allows ALTER TABLE tablename SET

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Noah Misch
On Thu, Apr 07, 2011 at 10:21:06PM -0400, Bruce Momjian wrote: Noah Misch wrote: 1) The pg_class.relfrozenxid that the TOAST table should have received (true relfrozenxid) is still covered by available clog files. Fixable with some combination of pg_class.relfrozenxid twiddling and SET

Re: [HACKERS] Typed-tables patch broke pg_upgrade

2011-04-08 Thread Noah Misch
On Wed, Mar 30, 2011 at 09:32:08PM -0400, Noah Misch wrote: ... ALTER TYPE mistakenly only touches the first table-of-type: create type t as (x int, y int); create table is_a of t; create table is_a2 of t; alter type t drop attribute y cascade, add attribute z int cascade; \d is_a

[HACKERS] k-neighbourhood search in databases

2011-04-08 Thread Oleg Bartunov
Hi there, I'm interesting if other databases provides built-in effective knn search ? Google didn't help me. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Noah Misch wrote: On Thu, Apr 07, 2011 at 10:21:06PM -0400, Bruce Momjian wrote: Noah Misch wrote: 1) The pg_class.relfrozenxid that the TOAST table should have received (true relfrozenxid) is still covered by available clog files. Fixable with some combination of

Re: [HACKERS] switch UNLOGGED to LOGGED

2011-04-08 Thread Robert Haas
On Fri, Apr 8, 2011 at 6:01 AM, Leonardo Francalanci m_li...@yahoo.it wrote: I read the discussion at http://archives.postgresql.org/pgsql-hackers/2011-01/msg00315.php From what I can understand, going from/to unlogged to/from logged in the wal_level == minimal case is not too complicated.

Re: [HACKERS] k-neighbourhood search in databases

2011-04-08 Thread PostgreSQL - Hans-Jürgen Schönig
hello ... i have put some research into that some time ago and as far as i have seen there is a 99% chance that no other database can do it the way we do it. it seems nobody comes even close to it (especially not in the flexibility-arena). oracle: disgusting workaround ...

[HACKERS] workaround for expensive KNN?

2011-04-08 Thread PostgreSQL - Hans-Jürgen Schönig
hello all ... given oleg's posting before i also wanted to fire up some KNN related question. let us consider a simple example. i got some million lines and i want all rows matching a tsquery sorted by price. i did some tests: test=# explain (analyze true, buffers true, costs true) SELECT id

Re: [HACKERS] workaround for expensive KNN?

2011-04-08 Thread Oleg Bartunov
Probably, you miss two-columnt index. From my early post: http://www.sai.msu.su/~megera/wiki/knngist =# CREATE INDEX spots_idx ON spots USING knngist (coordinates, to_tsvector('french',address)); =# SELECT id, address, (coordinates - '(2.29470491409302,48.858263472125)'::point) AS dist FROM

Re: [HACKERS] k-neighbourhood search in databases

2011-04-08 Thread Oleg Bartunov
Hans, thanks a lot. I've heard about Oracle Spatial, but I don't know if it's knn is just syntactic sugar for workarounds. Oleg On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote: hello ... i have put some research into that some time ago and as far as i have seen there is a 99%

Re: [HACKERS] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c

2011-04-08 Thread Alvaro Herrera
Excerpts from Brendan Jurd's message of vie abr 08 06:00:22 -0300 2011: Memtest didn't report any errors. I intend to try swapping out the RAM tomorrow, but in the meantime we got a *different* assertion failure today. The fact that we are tripping over various different assertions seems to

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Andrew Dunstan
On 04/07/2011 09:58 PM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstanand...@dunslane.net wrote: That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on warts such as $varname

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Pavel Stehule
Hello Well, if we're going to consider 100% backwards compatibility a must, then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match.  This is also per spec if I interpreted

Re: [HACKERS] SSI bug?

2011-04-08 Thread YAMAMOTO Takashi
hi, YAMAMOTO Takashi y...@mwd.biglobe.ne.jp wrote: LOG: could not truncate directory pg_serial: apparent wraparound Did you get a warning with this text?: memory for serializable conflict tracking is nearly exhausted there is not such a warning near the above aparent wraparound

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Andrew Dunstan
On 04/08/2011 10:53 AM, Pavel Stehule wrote: For SQL language functions, I think you're right. The only caveat I have is that if your function name is very long, having to use it as a disambiguating qualifier can be a bit ugly. same mechanism works well in plpgsql and nobody requested a some

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Robert Haas
On Fri, Apr 8, 2011 at 10:53 AM, Pavel Stehule pavel.steh...@gmail.com wrote: same mechanism works well in plpgsql and nobody requested a some special shortcut. I did. That mechanism sucks. But I think we're committed to doing what the standard and/or Oracle do, so oh well. -- Robert Haas

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Andres Freund
On Friday, April 08, 2011 04:53:27 PM Pavel Stehule wrote: same mechanism works well in plpgsql and nobody requested a some special shortcut. Well, for one it sucks there as well. For another it has been introduced for quite some time and most people have introduced naming like p_param or

Re: [HACKERS] workaround for expensive KNN?

2011-04-08 Thread Oleg Bartunov
Hans, what if you create index (price,title) ? On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote: hello ... i got that one ... idx_product_t_product_titleprice gist (to_tsvector('german'::regconfig, title), int_price) so, i have a combined index on text + number. to me the

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread David E. Wheeler
On Apr 8, 2011, at 8:05 AM, Robert Haas wrote: same mechanism works well in plpgsql and nobody requested a some special shortcut. I did. That mechanism sucks. But I think we're committed to doing what the standard and/or Oracle do, so oh well. I think I've worked around that in PL/pgSQL

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Robert Haas
On Fri, Apr 8, 2011 at 11:51 AM, David E. Wheeler da...@kineticode.com wrote: On Apr 8, 2011, at 8:05 AM, Robert Haas wrote: same mechanism works well in plpgsql and nobody requested a some special shortcut. I did.  That mechanism sucks.  But I think we're committed to doing what the

Re: [HACKERS] workaround for expensive KNN?

2011-04-08 Thread Tom Lane
Oleg Bartunov o...@sai.msu.su writes: what if you create index (price,title) ? I think that SELECT ... WHERE ... ORDER BY ... LIMIT is basically an intractable problem. We've recognized the difficulty in connection with btree indexes for a long time, and there is no reason at all to think that

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Merlin Moncure
On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan and...@dunslane.net wrote: That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Tom Lane
Merlin Moncure mmonc...@gmail.com writes: On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, if we're going to consider 100% backwards compatibility a must, then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query

[HACKERS] getting carriage return character in vacuumo

2011-04-08 Thread Muhammad Usama
Hi, While using the vacuumlo utility I encountered a redundant carriage return(\r') character in the output. It is required in any scenario? If not, please find attached a tiny patch which will get rid of that extra '\r' character. Regards, Usama carriage_return_in_vacuumlo.patch Description:

Re: [HACKERS] Headcount for PL Summit, Saturday May 21, 2011 at PgCon

2011-04-08 Thread Selena Deckelmann
Hello again! On Thu, Apr 7, 2011 at 10:22 AM, Selena Deckelmann sel...@chesnok.com wrote: We need to get a headcount for the PL Summit at PgCon on Saturday, May 21, 2011. Please sign up using this form: http://chesnok.com/u/1r A wiki page has been started here:

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Jeff Davis
On Thu, 2011-04-07 at 22:21 -0400, Bruce Momjian wrote: One concern I have is that existing heap tables are protecting clog files, but once those are frozen, the system might remove clog files not realizing it has to freeze the heap tables too. I don't understand. Can you elaborate? Regards,

Re: [HACKERS] workaround for expensive KNN?

2011-04-08 Thread PostgreSQL - Hans-Jürgen Schönig
hello ... i got that one ... idx_product_t_product_titleprice gist (to_tsvector('german'::regconfig, title), int_price) so, i have a combined index on text + number. to me the plan seems fine ... it looks like a prober KNN traversal. the difference between my plan and your plan seems to be

Re: [HACKERS] workaround for expensive KNN?

2011-04-08 Thread Oleg Bartunov
Oops, my previous example was fromm early prototype :) I just recreated test environment for 9.1: knn=# select count(*) from spots; count 908846 (1 row) knn=# explain (analyze true, buffers true) SELECT id, address, (coordinates - '(2.29470491409302,48.858263472125)'::point) AS

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Jeff Davis
On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote: Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0; True; it just performs more

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Jeff Davis wrote: On Thu, 2011-04-07 at 22:21 -0400, Bruce Momjian wrote: One concern I have is that existing heap tables are protecting clog files, but once those are frozen, the system might remove clog files not realizing it has to freeze the heap tables too. I don't understand. Can

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Jeff Davis wrote: On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote: Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0;

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Noah Misch
On Fri, Apr 08, 2011 at 10:05:01AM -0700, Jeff Davis wrote: On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote: Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE)

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: New version; I made some other small adjustments: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster -- except

Re: [HACKERS] Open issues for collations

2011-04-08 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Reading through this thread... On Sat, Mar 26, 2011 at 12:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: ** Selecting a field from a record-returning function's output. Currently, we'll use the field's declared collation; except that if the field has

[HACKERS] sync rep and smart shutdown

2011-04-08 Thread Robert Haas
There is an open item for synchronous replication and smart shutdown, with a link to here: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01391.php The issue is not straightforward, however, so I want to get some broader input before proceeding. In short, the problem is that if

Re: [HACKERS] getting carriage return character in vacuumo

2011-04-08 Thread Tom Lane
Muhammad Usama m.us...@gmail.com writes: While using the vacuumlo utility I encountered a redundant carriage return(\r') character in the output. It is required in any scenario? If not, please find attached a tiny patch which will get rid of that extra '\r' character. I think the idea there

Re: [HACKERS] Open issues for collations

2011-04-08 Thread Peter Eisentraut
On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug mode. Quick question on this: Should we at least warn if zero suitable locales were found or some other problem

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: OK, now that I have committed the fixes to git, I think it is time to consider how we are going to handle this fix for people who have already used pg_upgrade, or are using it in currently released versions. I am thinking an announce list email with this query would be

Re: [HACKERS] Open issues for collations

2011-04-08 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug mode. Quick question on this: Should we at least warn if zero

Re: [HACKERS] sync rep and smart shutdown

2011-04-08 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: There is an open item for synchronous replication and smart shutdown, with a link to here: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01391.php There are a couple of plausible ways to proceed here: 1. Do nothing. 2. When a smart

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
Bruce, * Bruce Momjian (br...@momjian.us) wrote: OK, here is a draft email announcement: Couple suggestions (also on IRC): --- A bug has been discovered in all released versions of pg_upgrade and (formerly) pg_migrator.

Re: [HACKERS] sync rep and smart shutdown

2011-04-08 Thread Robert Haas
On Fri, Apr 8, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: There is an open item for synchronous replication and smart shutdown, with a link to here: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01391.php There are a couple of

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Stephen Frost wrote: -- Start of PGP signed section. Bruce, * Bruce Momjian (br...@momjian.us) wrote: OK, here is a draft email announcement: Couple suggestions (also on IRC): Yes, I like your version better; I did adjust the wording of the last sentence to mention it is really the

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: Yes, I like your version better; I did adjust the wording of the last sentence to mention it is really the release, not the new pg_upgrade, which fixes the problem because the fixes are in pg_dump, and hence a new pg_upgrade binary will not work; you

Re: [HACKERS] Open issues for collations

2011-04-08 Thread Alvaro Herrera
Excerpts from Tom Lane's message of vie abr 08 15:27:15 -0300 2011: Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Jeff Davis
On Fri, 2011-04-08 at 13:35 -0400, Noah Misch wrote: 1. Make relfrozenxid go backward to the right value. There is currently no mechanism to do this without compiling C code into the server, because (a) VACUUM FREEZE will never move the relfrozenxid backward; and (b) there is no way to

Re: [HACKERS] Typed-tables patch broke pg_upgrade

2011-04-08 Thread Robert Haas
On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch n...@leadboat.com wrote: Incidentally, this led me to notice that you can hang a typed table off a table row type.  ALTER TABLE never propagates to such typed tables, allowing them to get out of sync: create table t (x int, y int); create table

Re: [HACKERS] psql \dt and table size

2011-04-08 Thread Robert Haas
On Thu, Apr 7, 2011 at 3:03 PM, Bernd Helmle maili...@oopsware.de wrote: --On 28. März 2011 13:38:23 +0100 Bernd Helmle maili...@oopsware.de wrote: But I think we can just call pg_table_size() regardless in 9.0+; I believe it'll return the same results as pg_relation_size() on non-tables.  

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Josh Berkus
But breaking people's code is not a better answer. We still have people on 8.2 because the pain of upgrading to 8.3 is more than they can bear, and how many releases have we spent trying to get standard_conforming_strings worked out? I admit this probably wouldn't be as bad, but we've

Re: [HACKERS] k-neighbourhood search in databases

2011-04-08 Thread Josh Berkus
On 4/8/11 5:21 AM, Oleg Bartunov wrote: Hi there, I'm interesting if other databases provides built-in effective knn search ? Google didn't help me. Nobody I've talked to, and I asked both Couch and Oracle devs. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Stephen Frost wrote: -- Start of PGP signed section. * Bruce Momjian (br...@momjian.us) wrote: Yes, I like your version better; I did adjust the wording of the last sentence to mention it is really the release, not the new pg_upgrade, which fixes the problem because the fixes are in

Re: [HACKERS] Typed-tables patch broke pg_upgrade

2011-04-08 Thread Robert Haas
On Wed, Mar 30, 2011 at 12:50 PM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-02-10 at 06:31 +0200, Peter Eisentraut wrote: ERROR:  cannot drop column from typed table which probably is because test_type2 has a dropped column. It should call ALTER TYPE test_type2 DROP ATTRIBUTE

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Robert Haas
On Fri, Apr 8, 2011 at 3:56 PM, Josh Berkus j...@agliodbs.com wrote: But breaking people's code is not a better answer.  We still have people on 8.2 because the pain of upgrading to 8.3 is more than they can bear, and how many releases have we spent trying to get standard_conforming_strings

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: I am worried if I mention pg_dump that people will think pg_dump is broken, when in fact it is only the --binary-upgrade mode of pg_dump that is broken. I adjusted the wording of the last paragraph slighly to be clearer, but hopefully not confuse. We don't actually

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Noah Misch
On Fri, Apr 08, 2011 at 12:16:50PM -0700, Jeff Davis wrote: On Fri, 2011-04-08 at 13:35 -0400, Noah Misch wrote: 1. Make relfrozenxid go backward to the right value. There is currently no mechanism to do this without compiling C code into the server, because (a) VACUUM FREEZE will never

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Josh Berkus
-- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this script during a low traffic period and watch the database load.

Re: [HACKERS] Typed-tables patch broke pg_upgrade

2011-04-08 Thread Noah Misch
On Fri, Apr 08, 2011 at 03:43:39PM -0400, Robert Haas wrote: On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch n...@leadboat.com wrote: Incidentally, this led me to notice that you can hang a typed table off a table row type. ?ALTER TABLE never propagates to such typed tables, allowing them to

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Josh Berkus
Now, when this person attempts to recreate this function on a hypothetical version of PostgreSQL that thinks id is ambiguous, it doesn't work. Hence the GUC. Where's the issue? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Robert Haas
On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus j...@agliodbs.com wrote: Now, when this person attempts to recreate this function on a hypothetical version of PostgreSQL that thinks id is ambiguous, it doesn't work. Hence the GUC.   Where's the issue? Behavior-changing GUCs for this kind of

[HACKERS] gincostestimate

2011-04-08 Thread Jeff Janes
Dear Hackers, A gin index created on an initially empty table will never get used until the table is vacuumed, which for a table with no update or delete activity could be forever unless someone manually intervenes. The problem is that numEntries in src/backend/utils/adt/selfuncs.c is zero and

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: -- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this script during a low traffic

Re: [HACKERS] getting to beta

2011-04-08 Thread Robert Haas
On Wed, Apr 6, 2011 at 12:16 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Apr 6, 2011 at 12:06 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 06.04.2011 18:02, Tom Lane wrote: I agree.  But again, that's not really what I'm focusing on - the collations stuff, the

Re: [HACKERS] getting to beta

2011-04-08 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: I think I've cleared out most of the small stuff. Thanks! The two SSI related issues still on the open items list are: * SSI: failure to clean up some SLRU-summarized locks This one is very important. Not only could it lead to unnecessary

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: -- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this

[HACKERS] pgindent

2011-04-08 Thread Robert Haas
So, we talked about running pgindent a few weeks ago, but reading over the thread, I guess we're still waiting for Andrew to update the list of typedefs? It would be really nice to get this done. Andrew, is there any chance you can knock that out? -- Robert Haas EnterpriseDB:

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: -- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance.

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Jeff Davis
On Fri, 2011-04-08 at 15:03 -0400, Bruce Momjian wrote: A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly updating all TOAST tables in the migrated databases. You might want to clarify that the fix may be

Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-08 Thread Alvaro Herrera
Excerpts from Jeff Davis's message of mié abr 06 19:39:27 -0300 2011: On Wed, 2011-04-06 at 18:33 -0300, Alvaro Herrera wrote: (Consider, for example, that you may want to enable a user to run some operation to which he is authorized, but you want to carry out some privileged operation

Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-08 Thread Alvaro Herrera
Excerpts from A.M.'s message of mié abr 06 19:08:35 -0300 2011: That's really strange considering that the new role may not normally have permission to switch to the original role. How would you handle the case where the security definer role is not the super user? As I said to Jeff, it's up

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus j...@agliodbs.com wrote: Hence the GUC.   Where's the issue? Behavior-changing GUCs for this kind of thing cause a lot of problems. If you need one GUC setting for your application to work, and the

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 4:00 PM, Jeff Davis pg...@j-davis.com wrote: On Fri, 2011-04-08 at 15:03 -0400, Bruce Momjian wrote: A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly updating all TOAST tables in

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 4:51 PM, bricklen brick...@gmail.com wrote: I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be running the suggested script shortly, but can anyone tell me in how bad

Re: [HACKERS] pgindent

2011-04-08 Thread Andrew Dunstan
On 04/08/2011 06:05 PM, Robert Haas wrote: So, we talked about running pgindent a few weeks ago, but reading over the thread, I guess we're still waiting for Andrew to update the list of typedefs? It would be really nice to get this done. Andrew, is there any chance you can knock that out?

Re: [HACKERS] pg_dump --binary-upgrade vs. ALTER TYPE ... DROP ATTRIBUTE

2011-04-08 Thread Noah Misch
On Wed, Mar 30, 2011 at 09:37:56PM -0400, Robert Haas wrote: On Wed, Mar 30, 2011 at 9:30 PM, Noah Misch n...@leadboat.com wrote: Perhaps it would be reasonable to extend ALTER TABLE .. [NO] INHERIT to accept a type name as the final argument. ?If used in this way, it converts a typed table

[HACKERS] \dO versus collations for other encodings

2011-04-08 Thread Tom Lane
I've noticed that psql's \dO command for showing collations is a bit schizophrenic about whether it shows entries for collations that are irrelevant in the current database (because they use a different encoding). For example: regression=# \dOS aa* List of collations

Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-08 Thread A.M.
On Apr 8, 2011, at 7:20 PM, Alvaro Herrera wrote: Excerpts from A.M.'s message of mié abr 06 19:08:35 -0300 2011: That's really strange considering that the new role may not normally have permission to switch to the original role. How would you handle the case where the security definer

Re: [HACKERS] using a lot of maintenance_work_mem

2011-04-08 Thread Stephen Frost
Tom, all, Having run into issues caused by small work_mem, again, I felt the need to respond to this. * Tom Lane (t...@sss.pgh.pa.us) wrote: You would break countless things. It might be okay anyway in a trusted environment, ie, one without users trying to crash the system, but there are a

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
bricklen, * bricklen (brick...@gmail.com) wrote: I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be running the suggested script shortly, but can anyone tell me in how bad of shape my db is

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
Hi Stephen, On Fri, Apr 8, 2011 at 6:57 PM, Stephen Frost sfr...@snowman.net wrote: bricklen, * bricklen (brick...@gmail.com) wrote: I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
bricklen, * bricklen (brick...@gmail.com) wrote: I looked deeper into our backup archives, and it appears that I do have the clog file reference in the error message DETAIL: Could not open file pg_clog/04BE: No such file or directory. Great! And there's no file in pg_clog which matches that

Re: [HACKERS] pgindent

2011-04-08 Thread Robert Haas
On Fri, Apr 8, 2011 at 8:05 PM, Andrew Dunstan and...@dunslane.net wrote: On 04/08/2011 06:05 PM, Robert Haas wrote: So, we talked about running pgindent a few weeks ago, but reading over the thread, I guess we're still waiting for Andrew to update the list of typedefs? It would be really

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 7:11 PM, Stephen Frost sfr...@snowman.net wrote: bricklen, * bricklen (brick...@gmail.com) wrote: I looked deeper into our backup archives, and it appears that I do have the clog file reference in the error message DETAIL:  Could not open file pg_clog/04BE: No such

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 7:20 PM, bricklen brick...@gmail.com wrote: On Fri, Apr 8, 2011 at 7:11 PM, Stephen Frost sfr...@snowman.net wrote: bricklen, * bricklen (brick...@gmail.com) wrote: I looked deeper into our backup archives, and it appears that I do have the clog file reference in the

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. One recommendation is to also increase autovacuum_freeze_max_age to 5 (500m), which will hopefully prevent autovacuum from 'butting in' and causing issues

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Stephen Frost wrote: -- Start of PGP signed section. bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. One recommendation is to also increase autovacuum_freeze_max_age to 5 (500m), which will

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 8:07 PM, Bruce Momjian br...@momjian.us wrote: Stephen Frost wrote: -- Start of PGP signed section. bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats.  One recommendation is to also

Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name

2011-04-08 Thread Pavel Stehule
2011/4/9 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus j...@agliodbs.com wrote: Hence the GUC.   Where's the issue? Behavior-changing GUCs for this kind of thing cause a lot of problems.  If you need one GUC setting for

Re: [HACKERS] pgindent

2011-04-08 Thread Andrew Dunstan
On 04/08/2011 10:12 PM, Robert Haas wrote: On Fri, Apr 8, 2011 at 8:05 PM, Andrew Dunstanand...@dunslane.net wrote: On 04/08/2011 06:05 PM, Robert Haas wrote: So, we talked about running pgindent a few weeks ago, but reading over the thread, I guess we're still waiting for Andrew to update

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
bricklen, * bricklen (brick...@gmail.com) wrote: Thanks guys, I really appreciate your help. For the vacuum freeze, you say database-wide, should I run vacuumdb -a -v -F ? Will freezing the other tables in the cluster help (not sure how that works with template0/1 databases?) Yes, using the

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
bricklen wrote: On Fri, Apr 8, 2011 at 8:07 PM, Bruce Momjian br...@momjian.us wrote: Stephen Frost wrote: -- Start of PGP signed section. bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. ?One

Re: [HACKERS] k-neighbourhood search in databases

2011-04-08 Thread Oleg Bartunov
On Fri, 8 Apr 2011, Josh Berkus wrote: On 4/8/11 5:21 AM, Oleg Bartunov wrote: Hi there, I'm interesting if other databases provides built-in effective knn search ? Google didn't help me. Nobody I've talked to, and I asked both Couch and Oracle devs. That's great to know :)

Re: [HACKERS] pgindent

2011-04-08 Thread Robert Haas
On Fri, Apr 8, 2011 at 11:21 PM, Andrew Dunstan and...@dunslane.net wrote: We've got more work to do before that works, so I have committed what we have. Some symbols have disappeared, some because of code changes and some probably because Cygwin has changed the way it does objdump. This is

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Alvaro Herrera
Why is it important to have the original pg_clog files around? Since the transactions in question are below the freeze horizon, surely the tuples that involve those transaction have all been visited by vacuum and thus removed if they were leftover from aborted transactions or deleted, no? So

Re: [HACKERS] Open issues for collations

2011-04-08 Thread Peter Eisentraut
On fre, 2011-04-08 at 16:14 -0300, Alvaro Herrera wrote: Excerpts from Tom Lane's message of vie abr 08 15:27:15 -0300 2011: Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * Remove initdb's warning about useless locales? Seems like pointless

Re: [HACKERS] k-neighbourhood search in databases

2011-04-08 Thread Jeremiah Peschka
On 4/8/11 5:21 AM, Oleg Bartunov wrote: Hi there, I'm interesting if other databases provides built-in effective knn search ? Google didn't help me. SQL Server provides some knn search functionality[1] with enhancements coming this November in SQL 11[2]. [1]:

Re: [HACKERS] Open issues for collations

2011-04-08 Thread Alvaro Herrera
Excerpts from Peter Eisentraut's message of sáb abr 09 01:32:28 -0300 2011: On fre, 2011-04-08 at 16:14 -0300, Alvaro Herrera wrote: Excerpts from Tom Lane's message of vie abr 08 15:27:15 -0300 2011: Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane

Re: [HACKERS] alpha5

2011-04-08 Thread Alvaro Herrera
Excerpts from Peter Eisentraut's message of lun mar 28 17:00:01 -0300 2011: On mån, 2011-03-28 at 09:35 -0400, Robert Haas wrote: Actually those are all my fault. Sorry, I'm still learning the ropes. I didn't realize xref couldn't be used in the release notes; it looks like Bruce used

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 9:28 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Why is it important to have the original pg_clog files around?  Since the transactions in question are below the freeze horizon, surely the tuples that involve those transaction have all been visited by vacuum

  1   2   >