[HACKERS] [PATCH] explain tup_fetched/returned in monitoring-stats

2012-10-12 Thread Abhijit Menon-Sen
I'm making some changes to a program that, among other things, reports tup_fetched/tup_returned as if it were a cache hit rate, analogous to blks_hit/blks_fetched. The documentation didn't help me to understand if that was appropriate, so I looked at the source and asked on IRC. It seems I'm not

Re: [HACKERS] Switching timeline over streaming replication

2012-10-12 Thread Amit Kapila
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Amit Kapila Sent: Wednesday, October 10, 2012 7:57 PM To: 'Heikki Linnakangas' Cc: 'PostgreSQL-development' Subject: Re: [HACKERS] Switching timeline over

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Simon Riggs
On 11 October 2012 23:59, Josh Berkus j...@agliodbs.com wrote: With the DDL trigger, we're able to do that faster. The idea is you can still delete it if you need compatibility, so we get the message across without an extra release and without an annoying GUC (etc). You're seeing these

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Simon Riggs
On 12 October 2012 01:52, Andrew Dunstan and...@dunslane.net wrote: I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat surprised at the willingness of some people to spring surprises on users. I've never caused nor argued in favour of hardcoded changes that catch users.

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Simon Riggs
On 12 October 2012 00:45, Peter Geoghegan pe...@2ndquadrant.com wrote: On 11 October 2012 20:28, Simon Riggs si...@2ndquadrant.com wrote: Not many RULE-lovers out there, once you've tried to use them. Allowing RULEs complicates various things and can make security more difficult. What

Re: [HACKERS] Measure Theoretic Data Types in Postgresql

2012-10-12 Thread Heikki Linnakangas
On 11.10.2012 07:37, Aaron Sheldon wrote: This would allow for a succinct syntax to do calculations such as finding the daily unique patient count given the intervals of their attendance in particular programs; a computation I encounter routinely as a statistician for a health services provider.

Re: [HACKERS] Deprecating RULES

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

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Hannu Krosing
On 10/12/2012 08:47 AM, Simon Riggs wrote: On 12 October 2012 01:52, Andrew Dunstan and...@dunslane.net wrote: I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat surprised at the willingness of some people to spring surprises on users. I've never caused nor argued in

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Daniel Farina
On Thu, Oct 11, 2012 at 11:55 PM, Simon Riggs si...@2ndquadrant.com wrote: As regards cost/benefit analysis, this is a low importance feature, but then that is why I proposed a low effort fix that is flexible to the needs of users affected. Is there any feature that is more loathed and more

[HACKERS] velog + vereport?

2012-10-12 Thread Andres Freund
Hi, When writing code which should be able to run inside and outside a normal backend environment its sometimes useful to be able add a wrapper arround elog/ereport for when executing inside the backend. Currently that requires relatively ugly macro surgery and/or recompiling the file. I

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Andres Freund
On Friday, October 12, 2012 01:45:56 AM Peter Geoghegan wrote: On 11 October 2012 20:28, Simon Riggs si...@2ndquadrant.com wrote: Not many RULE-lovers out there, once you've tried to use them. Allowing RULEs complicates various things and can make security more difficult. What exactly

Re: [HACKERS] velog + vereport?

2012-10-12 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: When writing code which should be able to run inside and outside a normal backend environment its sometimes useful to be able add a wrapper arround elog/ereport for when executing inside the backend. Currently that requires relatively ugly macro

Re: [HACKERS] velog + vereport?

2012-10-12 Thread Andres Freund
On Friday, October 12, 2012 02:48:42 PM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: When writing code which should be able to run inside and outside a normal backend environment its sometimes useful to be able add a wrapper arround elog/ereport for when executing inside the

Re: [HACKERS] velog + vereport?

2012-10-12 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On Friday, October 12, 2012 02:48:42 PM Tom Lane wrote: Um ... and that accomplishes what? You wouldn't have velog/vereport outside the backend either. If you were going to clone those in some form in the external environment, you might as well

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Bruce Momjian
On Thu, Oct 11, 2012 at 05:20:14PM -0700, Daniel Farina wrote: On Thu, Oct 11, 2012 at 5:07 PM, Joshua D. Drake j...@commandprompt.com wrote: On 10/11/2012 03:59 PM, Josh Berkus wrote: I'm also not real keen on the idea that someone could dump a 9.2 database and be unable to load it

Re: [HACKERS] line type

2012-10-12 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: What's the deal with the line type? It's installed in the catalogs and listed in the documentation, varyingly as not implemented or not fully implemented, but all the support functions throw an error. Is there any known list of things that would need

Re: [HACKERS] velog + vereport?

2012-10-12 Thread Andres Freund
On Friday, October 12, 2012 04:59:39 PM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On Friday, October 12, 2012 02:48:42 PM Tom Lane wrote: Um ... and that accomplishes what? You wouldn't have velog/vereport outside the backend either. If you were going to clone those in

Re: [HACKERS] velog + vereport?

2012-10-12 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On Friday, October 12, 2012 04:59:39 PM Tom Lane wrote: Meh. I can't get excited about that, but in any case, that looks like it would only justify a varargs version of errmsg(), not the entire ereport infrastructure. Yes, that sounds good

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Sébastien Lardière
On 10/11/2012 09:22 PM, Simon Riggs wrote: That is a lot more typing and it's not exactly intuitive. One obvious thing that would help is a function pg_table_exists(text) that would return true or false. But even with that there's a lot of syntactic sugar in there that is less than ideal:

Re: [HACKERS] velog + vereport?

2012-10-12 Thread Andres Freund
On Friday, October 12, 2012 06:02:44 PM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On Friday, October 12, 2012 04:59:39 PM Tom Lane wrote: Meh. I can't get excited about that, but in any case, that looks like it would only justify a varargs version of errmsg(), not the

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Andrew Dunstan
On 10/12/2012 12:03 PM, Sébastien Lardière wrote: If we can do something like : SELECT pg_if_table_exists('bar' , pg_if_table_exists('foo', 'TRUNCATE TABLE foo, bar, foobar')) ; or SELECT pg_if_tables_exists('TRUNCATE TABLE foo, bar, foobar', 'foo', 'bar') ; I say yes ! This strikes

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Pavel Stehule
Hello 2012/10/12 Sébastien Lardière slardi...@hi-media.com: On 10/11/2012 09:22 PM, Simon Riggs wrote: That is a lot more typing and it's not exactly intuitive. One obvious thing that would help is a function pg_table_exists(text) that would return true or false. But even with that

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Dimitri Fontaine
Andrew Dunstan and...@dunslane.net writes: This strikes me as just highly un-SQL-like. +1 I tend to agree with Noah's comment upthread: But the syntax is a bandage for raw psql input remaining a hostile environment for implementing the full range of schema changes. Switch to submitting

Re: [HACKERS] Deparsing DDL command strings

2012-10-12 Thread Dimitri Fontaine
Dimitri Fontaine dimi...@2ndquadrant.fr writes: I'll show some examples of very involved command (CREATE and ALTER TABLE are the most complex we have I think) and some very simple commands (DROP TABLE is one of the simplest), so that we can make up our minds on that angle. So please find

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Josh Berkus
I don't think you're listening, none of those things are problems and so not user hostile. Having an upgrade fail for mysterious reasons with a cryptic error message the user doesn't understand isn't user-hostile? Wow, you must have a very understanding group of users. Lemme try to make it

Re: [HACKERS] [PATCH] explain tup_fetched/returned in monitoring-stats

2012-10-12 Thread Tom Lane
Abhijit Menon-Sen a...@2ndquadrant.com writes: I'm making some changes to a program that, among other things, reports tup_fetched/tup_returned as if it were a cache hit rate, analogous to blks_hit/blks_fetched. The documentation didn't help me to understand if that was appropriate, so I

Re: [HACKERS] Measure Theoretic Data Types in Postgresql

2012-10-12 Thread Josh Berkus
On 10/12/12 12:48 AM, Heikki Linnakangas wrote: So, I think the current range types already cover that use case pretty well. I can't imagine how the proposed measure theoretic concepts would make that simpler. Can you give some more complicated problem, perhaps, that the proposed measure

Re: [HACKERS] Measure Theoretic Data Types in Postgresql

2012-10-12 Thread Aaron Sheldon
So the key algorithmic inefficient is the inner join on the generated series. Worst case scenario this compares every range to every date in the series, which for m ranges and n dates yields O(m*n) operations. The analysts in my shop currently write queries like this for billions of records

Re: [HACKERS] proposal - assign result of query to psql variable

2012-10-12 Thread Pavel Stehule
Hello here is updated version of gset patch. * merge Shigeru's doc patch * rename psql regression test from psql to psql_cmd Regards Pavel Stehule 2012/9/27 Pavel Stehule pavel.steh...@gmail.com: Hello 2012/9/21 Shigeru HANADA shigeru.han...@gmail.com: Hi Pavel, (2012/09/21 2:01),

[HACKERS] patch: assign result of query to psql variable

2012-10-12 Thread Pavel Stehule
-- Forwarded message -- From: Pavel Stehule pavel.steh...@gmail.com Date: 2012/10/12 Subject: Re: [HACKERS] proposal - assign result of query to psql variable To: Shigeru HANADA shigeru.han...@gmail.com Kopie: David Fetter da...@fetter.org, Tom Lane t...@sss.pgh.pa.us, PostgreSQL

[HACKERS] Adding comments for system table/column names

2012-10-12 Thread Bruce Momjian
There was a thread in January of 2012 where we discussed the idea of pulling system table/column name descriptions from the SGML docs and creating SQL comments for them: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00837.php Magnus didn't seem to like the idea:

Re: [HACKERS] getopt() and strdup()

2012-10-12 Thread Bruce Momjian
Applied. --- On Wed, Oct 10, 2012 at 07:54:15PM -0400, Bruce Momjian wrote: On Mon, Oct 8, 2012 at 09:03:37PM -0400, Bruce Momjian wrote: On Mon, Oct 8, 2012 at 04:33:29PM -0400, Tom Lane wrote: Bruce Momjian

Re: [HACKERS] Measure Theoretic Data Types in Postgresql

2012-10-12 Thread Nathan Boley
However, by realizing that the bounds on the ranges have a linear ordering one can speed this up to 0(m) using windowing functions on common table expressions. So what I am proposing is formalizing this optimization into a class of data types, that will hide the implementation details.

[HACKERS] problem with mailing list

2012-10-12 Thread Pavel Stehule
Hello I have a problem with sending patch to mailing list. It is working now? Regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] dumping recursive views broken in master

2012-10-12 Thread Peter Eisentraut
CREATE VIEW sums_1_100 AS WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT sum(n) FROM t; dumps as CREATE VIEW sums_1_100 AS WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT (t_1.n + 1) FROM t WHERE (t_1.n 100)) SELECT sum(t.n) AS sum FROM t;

Re: [HACKERS] [PATCH] explain tup_fetched/returned in monitoring-stats

2012-10-12 Thread Abhijit Menon-Sen
At 2012-10-12 13:05:44 -0400, t...@sss.pgh.pa.us wrote: t_tuples_returned for instance is incremented by both pgstat_count_heap_getnext() (ie, successful returns from heap_getnext()) and pgstat_count_index_tuples() (which counts heap TIDs returned from either index_getnext_tid or

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2012-10-12 Thread Stephen Frost
* Jim Nasby (j...@nasby.net) wrote: Yeah, I was just trying to remember what other situations this has come up in. My recollection is that there's been a couple other cases where that would be useful. Yes, I've run into similar issues in the past also. It'd be really neat to somehow make

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Hannu Krosing
On 10/12/2012 06:59 PM, Josh Berkus wrote: I don't think you're listening, none of those things are problems and so not user hostile. Having an upgrade fail for mysterious reasons with a cryptic error message the user doesn't understand isn't user-hostile? Wow, you must have a very

Re: [HACKERS] dumping recursive views broken in master

2012-10-12 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: CREATE VIEW sums_1_100 AS WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT sum(n) FROM t; dumps as CREATE VIEW sums_1_100 AS WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT (t_1.n + 1) FROM t

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Greg Stark
On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs si...@2ndquadrant.com wrote: AFAICS all RULEs can be re-expressed as Triggers or Views. This is a bizarre discussion. Firstly this isn't even close to true. The whole source of people's discontentment is that triggers are *not* equivalent to rules. If

[HACKERS] Fix for log_line_prefix and session display

2012-10-12 Thread Bruce Momjian
Currently, our session id, displayed by log_line_prefix and CSV output, is made up of the session start time epoch seconds and the process id. The problem is that the printf mask is currently %lx.%x, causing a process id less than 4096 to not display a full four hex digits after the decimal

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Darren Duncan
Josh Berkus wrote: I don't think you're listening, none of those things are problems and so not user hostile. Having an upgrade fail for mysterious reasons with a cryptic error message the user doesn't understand isn't user-hostile? Wow, you must have a very understanding group of users.

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Robert Haas
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote: So we just need a function called pg_if_table_exists(table, SQL) which wraps a test in a subtransaction. And you write SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo'); and we can even get rid of all that other

Re: [HACKERS] September 2012 commitfest

2012-10-12 Thread Robert Haas
On Thu, Oct 11, 2012 at 3:37 PM, Simon Riggs si...@2ndquadrant.com wrote: On 11 October 2012 20:30, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 11, 2012 at 2:42 PM, Andrew Dunstan and...@dunslane.net wrote: I have a quietish few days starting on Saturday, will be looking at this then.

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: INSERT IF EXISTS (and, hey, why not INSERT OR CREATE for good measure?). I'm not sure what the right thing to do is... but we should probably come up with some consensus position we can all live with, and then go make this uniform[1]. 'INSERT OR

Re: [HACKERS] September 2012 commitfest

2012-10-12 Thread Andrew Dunstan
On 10/12/2012 03:07 PM, Robert Haas wrote: On Thu, Oct 11, 2012 at 3:37 PM, Simon Riggs si...@2ndquadrant.com wrote: On 11 October 2012 20:30, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 11, 2012 at 2:42 PM, Andrew Dunstan and...@dunslane.net wrote: I have a quietish few days

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: Second, to my mind the point of a multi-table TRUNCATE is to ensure that all the referenced tables get reset to empty *together*. With something like this, you'd have no such guarantee. Consider a timeline like this: Don't we have the exact same issue

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Josh Berkus
On 10/12/12 11:57 AM, Darren Duncan wrote: Assuming we decide to do away with RULEs, change the *documentation* for RULEs right away in all supported maintenance branches (including 9.2), saying that RULEs will be deprecated, but don't change any code / add any warnings until 9.3. I'd say

Re: [HACKERS] No, pg_size_pretty(numeric) was not such a hot idea

2012-10-12 Thread Robert Haas
On Wed, Oct 10, 2012 at 2:49 PM, Josh Berkus j...@agliodbs.com wrote: So, here's a complaint: 9.2 is breaking our code for checking table sizes: postgres=# select pg_size_pretty(100); ERROR: function pg_size_pretty(integer) is not unique at character 8 You know, if we implemented what Tom

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Robert Haas
On Thu, Oct 11, 2012 at 8:52 PM, Andrew Dunstan and...@dunslane.net wrote: I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat surprised at the willingness of some people to spring surprises on users. I still come across uses of rules in the wild, and not just for

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Alvaro Herrera
Robert Haas escribió: On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote: So we just need a function called pg_if_table_exists(table, SQL) which wraps a test in a subtransaction. And you write SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo'); and we can

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Stephen Frost
* Josh Berkus (j...@agliodbs.com) wrote: On 10/9/12 1:35 PM, Peter Eisentraut wrote: On 10/9/12 5:09 AM, Simon Riggs wrote: Anyone want to check for any other missing IF EXISTS capability in other DDL? TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is stopping

Re: [HACKERS] enhanced error fields

2012-10-12 Thread Pavel Stehule
Hello 2012/10/11 Peter Geoghegan pe...@2ndquadrant.com: On 10 October 2012 14:56, Pavel Stehule pavel.steh...@gmail.com wrote: (eelog3.diff) This looks better. You need a better comment here: + * relerror.c + * relation error loging functions + * I'm still not satisfied with

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Hannu Krosing
On 10/12/2012 08:48 PM, Greg Stark wrote: On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs si...@2ndquadrant.com wrote: AFAICS all RULEs can be re-expressed as Triggers or Views. This is a bizarre discussion. Firstly this isn't even close to true. The whole source of people's discontentment is

Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM

2012-10-12 Thread Stephen Frost
* Marko Kreen (mark...@gmail.com) wrote: As it works only on connect time, it can actually be secure, unlike user switching with SET ROLE. I'm guessing your issue with SET ROLE is that a RESET ROLE can be issued later..? If so, I'd suggest that we look at fixing that, but realize it could

Re: [HACKERS] Deprecating RULES

2012-10-12 Thread Simon Riggs
On 12 October 2012 17:59, Josh Berkus j...@agliodbs.com wrote: I don't think you're listening, none of those things are problems and so not user hostile. Having an upgrade fail for mysterious reasons with a cryptic error message the user doesn't understand isn't user-hostile? Wow, you must

[HACKERS] [PATCH] assign result of query to psql variable

2012-10-12 Thread Pavel Stehule
Hello here is updated version of gset patch. * merge Shigeru's doc patch * rename psql regression test from psql to psql_cmd Regards Pavel Stehule 2012/9/27 Pavel Stehule pavel.steh...@gmail.com: Hello 2012/9/21 Shigeru HANADA shigeru.han...@gmail.com: Hi Pavel, (2012/09/21 2:01),

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Dimitri Fontaine
Alvaro Herrera alvhe...@2ndquadrant.com writes: Uh, we had an execute() function of sorts in the extensions patch; that seems to have been ripped out. Do we want it back? It was pretty different from what's being proposed here, as it was the server-side version of psql \i feature, that is,

Re: [HACKERS] [PATCH] Make pg_basebackup configure and start standby [Review]

2012-10-12 Thread Robert Haas
On Wed, Oct 10, 2012 at 8:02 PM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Oct 11, 2012 at 3:36 AM, Boszormenyi Zoltan z...@cybertec.at wrote: 2012-10-10 18:23 keltezéssel, Fujii Masao írta: When tar output format is specified together with -R option, recovery.conf is not included in

Re: [HACKERS] Improving the performance of psql tab completion

2012-10-12 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: On Fri, Oct 12, 2012 at 03:57:15PM -0400, Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: There was also some discussion of fixing the name-check to be indexable, which the substring hack isn't. That would take a bit of work though.

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Christopher Browne
On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote: So we just need a function called pg_if_table_exists(table, SQL) which wraps a test in a subtransaction. And you write SELECT

Re: [HACKERS] foreign key locks

2012-10-12 Thread Alvaro Herrera
Alvaro Herrera wrote: Andres Freund wrote: * heap_lock_tuple with mode == LockTupleKeyShare nowait looks like it would wait anyway in heap_lock_updated_tuple_rec Oops. I took a stab at fixing this. However, it is not easy. First you need a way to reproduce the problem, which

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Josh Berkus
On 10/12/12 2:05 PM, Christopher Browne wrote: That feels like a cleaner extension than what we have had, with the IF EXISTS/IF NOT EXISTS clauses that have been added to various CREATE/DROP/ALTER commands. +1 Josh like! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Hannu Krosing
On 10/12/2012 11:05 PM, Christopher Browne wrote: On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote: So we just need a function called pg_if_table_exists(table, SQL) which wraps a test in a

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Dimitri Fontaine
Christopher Browne cbbro...@gmail.com writes: I suggest the though of embracing statement modifiers in DDL, with some options possible: a) { DDL STATEMENT } IF CONDITION; b) { DDL STATEMENT } UNLESS CONDITION; Just saying. I hate that. Makes it harder to read, that last bit at the end of

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Josh Berkus
where CONDITION has several possible forms: i) {IF|UNLESS} ( SQL expression returning T/F ) ii) {IF|UNLESS} {EXISTS|NOT EXISTS} {TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead us that way, but I couldn't resist

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Christopher Browne
On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Christopher Browne cbbro...@gmail.com writes: I suggest the though of embracing statement modifiers in DDL, with some options possible: a) { DDL STATEMENT } IF CONDITION; b) { DDL STATEMENT } UNLESS CONDITION;

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Greg Stark
On Tue, Oct 9, 2012 at 9:04 PM, Robert Haas robertmh...@gmail.com wrote: I've been a big proponent of adding IF EXISTS support to CREATE TABLE and ALTER TABLE but I'm having a hard time getting excited about this one. I can't imagine that many people would use it The reason CREATE IF NOT

Re: [HACKERS] Truncate if exists

2012-10-12 Thread Josh Berkus
On 10/12/12 3:49 PM, Greg Stark wrote: TRUNCATE IF EXISTS foo; CREATE IF NOT EXISTS foo... Thing is, this can be written: CREATE IF NOT EXISTS foo ... TRUNCATE foo; For the exact same result. So, based on all of the objections and discussion on this feature, I personally no longer support

Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM

2012-10-12 Thread Josh Berkus
On 10/12/12 12:44 PM, Stephen Frost wrote: Don't get me wrong- I really dislike that we don't have something better today for people who insist on password based auth, but perhaps we should be pushing harder for people to use SSL instead? Problem is, the fact that setting up SSL correctly is

Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM

2012-10-12 Thread Stephen Frost
* Josh Berkus (j...@agliodbs.com) wrote: Problem is, the fact that setting up SSL correctly is hard is outside of our control. Agreed, though the packagers do make it easier.. Unless we can give people a run these three commands on each server and you're now SSL authenticating script, we can

[HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread Josh Berkus
Folks, One chronic problem users encounter is this one: 1. User creates new table 2. User inserts 45 records into new table. 3. Time passes. 4. User creates a query which joins against new table. 5. Planner uses estimate of 1000 rows for the new table. 6. User gets very bad query plan. Now, I

Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM

2012-10-12 Thread John R Pierce
On 10/12/12 4:25 PM, Stephen Frost wrote: * Josh Berkus (j...@agliodbs.com) wrote: Unless we can give people a run these three commands on each server and you're now SSL authenticating script, we can continue to expect the majority of users not to use SSL. And I don't think that level of

Re: [HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread Josh Berkus
All, 1. User creates new table 2. User inserts 45 records into new table. 3. Time passes. 4. User creates a query which joins against new table. 5. Planner uses estimate of 1000 rows for the new table. 6. User gets very bad query plan. Because Snowman asked me for an example: Before

Re: [HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: Now, I look at this, and ask myself: why didn't autoanalyze kick in at step 3? After all, this was a table which had 0 rows, we inserted 45 rows, making the table infinitely larger. It should have got on the autoanalyze list, no? Well, no. It seems

Re: [HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread Stephen Frost
* Josh Berkus (j...@agliodbs.com) wrote: Because Snowman asked me for an example: Thanks. :) That's a 25X difference in execution time. This is not the first time I've seen this issue. If we can figure out an 'easy' solution to this, I'd definitely vote for it being back-patched. Having a

Re: [HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread Josh Berkus
No, it's not that easy. The question you have to ask is when has that initial write burst stopped?. As an example, if autovacuum happened to see that table in the instant after CREATE, it might autovacuum it while it's still empty, and then this rule fails to trigger any further effort.

Re: [HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: I remember having got voted down on the percentage approach back when we first put AV into core, but I remain convinced that decision was a bad one. Yeah, I was one of the ones voting against you. The reason not to have percentage-only is for small

Re: [HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread Josh Berkus
[ shrug... ] You're attacking a straw man, or more precisely putting words into my mouth about what the percentage-based thresholds might be. Notice the examples I gave involved update percentages quite far north of 100%. It's possible and maybe likely that we need a sliding scale. Yes, or

Re: [HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: [ shrug... ] You're attacking a straw man, or more precisely putting words into my mouth about what the percentage-based thresholds might be. Notice the examples I gave involved update percentages quite far north of 100%. It's possible and maybe likely

Re: [HACKERS] Potential autovacuum optimization: new tables

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

Re: [HACKERS] Potential autovacuum optimization: new tables

2012-10-12 Thread Stephen Frost
* David Johnston (pol...@yahoo.com) wrote: Instead of global could you attach an interface function to the table and have the auto-analyzer call that function to basically ask the table whether it needs to be analyzed? Still need to deal with defaults and provide a decent supply of

Re: [HACKERS] Adding comments for system table/column names

2012-10-12 Thread Fabrízio de Royes Mello
2012/10/12 Bruce Momjian br...@momjian.us There was a thread in January of 2012 where we discussed the idea of pulling system table/column name descriptions from the SGML docs and creating SQL comments for them: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00837.php

Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM

2012-10-12 Thread Darren Duncan
Stephen Frost wrote: * Josh Berkus (j...@agliodbs.com) wrote: Problem is, the fact that setting up SSL correctly is hard is outside of our control. Agreed, though the packagers do make it easier.. Unless we can give people a run these three commands on each server and you're now SSL

Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM

2012-10-12 Thread John R Pierce
On 10/12/12 9:00 PM, Darren Duncan wrote: And now we're migrating to Red Hat for the production launch, using the http://www.postgresql.org/download/linux/redhat/ packages for Postgres 9.1, and these do *not* include the SSL. hmm? I'm using the 9.1 for CentOS 6(RHEL 6) and libpq.so

Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM

2012-10-12 Thread Darren Duncan
John R Pierce wrote: On 10/12/12 9:00 PM, Darren Duncan wrote: And now we're migrating to Red Hat for the production launch, using the http://www.postgresql.org/download/linux/redhat/ packages for Postgres 9.1, and these do *not* include the SSL. hmm? I'm using the 9.1 for CentOS 6(RHEL 6)