[HACKERS] SPI API and exceptions

2012-12-28 Thread Peter Eisentraut
SPI was invented before there was proper exception handling, so it communicates errors by return values. This makes programming with SPI either prone to errors of omission, or very ugly (ultimately, the standard reasons why exceptions were invented). So I was pondering whether we could introduce

Re: [HACKERS] multiple CREATE FUNCTION AS items for PLs

2012-12-28 Thread Peter Eisentraut
On Mon, 2012-12-17 at 16:34 -0500, Peter Eisentraut wrote: Yes, this would be a good solution for some applications, but the only way I can think of to manage the compatibility issue is to invent some function attribute system like CREATE FUNCTION ... OPTIONS (call_convention 'xyz') An

Re: [HACKERS] multiple CREATE FUNCTION AS items for PLs

2012-12-28 Thread Pavel Stehule
2012/12/28 Peter Eisentraut pete...@gmx.net: On Mon, 2012-12-17 at 16:34 -0500, Peter Eisentraut wrote: Yes, this would be a good solution for some applications, but the only way I can think of to manage the compatibility issue is to invent some function attribute system like CREATE FUNCTION

Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database

2012-12-28 Thread Dimitri Fontaine
Stephen Frost sfr...@snowman.net writes: Apparently I've managed to miss the tricky case..? That shouldn't be tricky as a user, but has been a tricky subject every time we've been talking about implement Event Triggers in the past two years, so I though I would include it: create schema

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

2012-12-28 Thread Simon Riggs
On 28 December 2012 08:07, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, I saw this patch and confirmed that - Coding style looks good. - Appliable onto HEAD. - Some mis-codings are fixed. I've had a quick review of the patch to see how close we're getting. The perf

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

2012-12-28 Thread Amit Kapila
On Friday, December 28, 2012 3:52 PM Simon Riggs wrote: On 28 December 2012 08:07, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, I saw this patch and confirmed that - Coding style looks good. - Appliable onto HEAD. - Some mis-codings are fixed. I've had a

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

2012-12-28 Thread Amit Kapila
On Friday, December 28, 2012 1:38 PM Kyotaro HORIGUCHI wrote: Hello, I saw this patch and confirmed that - Coding style looks good. - Appliable onto HEAD. - Some mis-codings are fixed. And took the performance figures for 4 types of modification versus 2 benchmarks. As a whole,

[HACKERS] pg_stat_statements: calls under-estimation propagation

2012-12-28 Thread Daniel Farina
Hello, After long delay (sorry) here's a patch implementing what was hand-waved at in http://archives.postgresql.org/pgsql-hackers/2012-10/msg00176.php I am still something at a loss at how to test it besides prodding it by hand; it seems like it's going to involve infrastructure or introducing

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

2012-12-28 Thread Simon Riggs
On 28 December 2012 11:27, Amit Kapila amit.kap...@huawei.com wrote: * The internal docs are completely absent. We need at least a whole page of descriptive comment, discussing trade-offs and design decisions. This is very important because it will help locate bugs much faster if these things

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

2012-12-28 Thread Simon Riggs
On 28 December 2012 11:27, Amit Kapila amit.kap...@huawei.com wrote: * TOAST is not handled at all. No comments about it, nothing. Does that mean it hasn't been considered? Or did we decide not to care in this release? Presumably that means we are comparing toast pointers byte by byte to

Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database

2012-12-28 Thread Fabrízio de Royes Mello
Hi all, And about proposal that originated this thread... I proposed only to add a column on shared catalog pg_database with timestamp of its creation. Event triggers don't cover CREATE DATABASE statement. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI:

Re: [HACKERS] multiple CREATE FUNCTION AS items for PLs

2012-12-28 Thread Hannu Krosing
On 12/28/2012 09:15 AM, Peter Eisentraut wrote: On Mon, 2012-12-17 at 16:34 -0500, Peter Eisentraut wrote: Yes, this would be a good solution for some applications, but the only way I can think of to manage the compatibility issue is to invent some function attribute system like CREATE

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Stephen Frost
Pavel, Peter, To be honest, I haven't been following this thread at all, but I'm kind of amazed that this patch seems to be progressing. I spent quite a bit of time previously trying to change the CSV log structure to add a single column and this patch is adding a whole slew of them. My prior

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Pavel Stehule
Hello 2012/12/28 Stephen Frost sfr...@snowman.net: Pavel, Peter, To be honest, I haven't been following this thread at all, but I'm kind of amazed that this patch seems to be progressing. I spent quite a bit of time previously trying to change the CSV log structure to add a single column

Re: [HACKERS] multiple CREATE FUNCTION AS items for PLs

2012-12-28 Thread Andrew Dunstan
On 12/28/2012 03:15 AM, Peter Eisentraut wrote: On Mon, 2012-12-17 at 16:34 -0500, Peter Eisentraut wrote: Yes, this would be a good solution for some applications, but the only way I can think of to manage the compatibility issue is to invent some function attribute system like CREATE

Re: [HACKERS] SPI API and exceptions

2012-12-28 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: SPI was invented before there was proper exception handling, so it communicates errors by return values. This makes programming with SPI either prone to errors of omission, or very ugly (ultimately, the standard reasons why exceptions were invented).

Re: [HACKERS] multiple CREATE FUNCTION AS items for PLs

2012-12-28 Thread Tom Lane
On 12/28/2012 09:15 AM, Peter Eisentraut wrote: An alternative that has some amount of precedent in the Python world would be to use comment pragmas, like this: ... This way we could get this done fairly easily without any new infrastructure outside the language handler. +1 for not

[HACKERS] Behaviour of bgworker with SIGHUP

2012-12-28 Thread Guillaume Lelarge
Hi, Today, I tried to make fun with the new background worker processes in 9.3, but I found something disturbing, and need help to go further. My code is available on https://github.com/gleu/stats_recorder. If you take a look, it is basically a copy of Alvarro's worker_spi contrib module with a

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: To be honest, I haven't been following this thread at all, but I'm kind of amazed that this patch seems to be progressing. I spent quite a bit of time previously trying to change the CSV log structure to add a single column and this patch is adding a

[HACKERS] proposal: ANSI SQL 2011 syntax for named parameters

2012-12-28 Thread Pavel Stehule
Hello I am not sure, but maybe is time to introduce ANSI SQL syntax for functions' named parameters It is defined in ANSI SQL 2011 CALL P (B = 1, A = 2) instead PostgreSQL syntax CALL ( B := 1, A := 2) Patch is very simple, but there are lot of questions about support previous syntax. *

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2012-12-28 Thread Peter Geoghegan
On 28 December 2012 11:43, Daniel Farina drfar...@acm.org wrote: Without further ado, the cover letter taken from the top of the patch: This tries to establish a maximum under-estimate of the number of calls for a given pg_stat_statements entry. That means the number of calls to the

Re: [HACKERS] XLByte* usage

2012-12-28 Thread Alvaro Herrera
Andres Freund escribió: On 2012-12-17 13:16:47 -0500, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2012-12-17 12:47:41 -0500, Tom Lane wrote: But, if the day ever comes when 64 bits doesn't seem like enough, I bet we'd move to 128-bit integers, which will surely be

[HACKERS] Whats the correct way to change trigdata-tg_relation

2012-12-28 Thread Charles Gomes
I'm creating a simple trigger that will be called during an insert and change the destination table. All values are going to be preserved, just the destination table will be different. From what I see I can't modify trigdata-tg_relation. All examples use: return Datum(trigdata-tg_trigtuple);

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Geoghegan
On 28 December 2012 14:00, Stephen Frost sfr...@snowman.net wrote: There are some additional concerns regarding the patch itself that I have (do we really want to modify ereport() in this way? How can we implement something which scales better than just adding more and more parameters?) but I

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Geoghegan
On 28 December 2012 15:57, Tom Lane t...@sss.pgh.pa.us wrote: I don't think that part's been agreed to at all; it seems entirely possible that it'll get dropped if/when the patch gets committed. I'm not convinced that having these fields in the log is worth the compatibility hit of changing

Re: [HACKERS] A stab at implementing better password hashing, with mixed results

2012-12-28 Thread Alastair Turner
On Thu, Dec 27, 2012 at 5:39 PM, Peter Bex peter@xs4all.nl wrote: On Thu, Dec 27, 2012 at 12:31:08PM -0300, Claudio Freire wrote: On Thu, Dec 27, 2012 at 11:46 AM, Peter Bex peter@xs4all.nl wrote: Implementing a more secure challenge-response based algorithm means a change in the

Re: [HACKERS] multiple CREATE FUNCTION AS items for PLs

2012-12-28 Thread Hannu Krosing
On 12/28/2012 09:15 AM, Peter Eisentraut wrote: On Mon, 2012-12-17 at 16:34 -0500, Peter Eisentraut wrote: Yes, this would be a good solution for some applications, but the only way I can think of to manage the compatibility issue is to invent some function attribute system like CREATE

Re: [HACKERS] XLByte* usage

2012-12-28 Thread Andres Freund
On 2012-12-28 14:59:50 -0300, Alvaro Herrera wrote: Andres Freund escribió: On 2012-12-17 13:16:47 -0500, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2012-12-17 12:47:41 -0500, Tom Lane wrote: But, if the day ever comes when 64 bits doesn't seem like enough, I bet

Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database

2012-12-28 Thread Josh Berkus
On 12/28/12 4:05 AM, Fabrízio de Royes Mello wrote: Hi all, And about proposal that originated this thread... I proposed only to add a column on shared catalog pg_database with timestamp of its creation. Event triggers don't cover CREATE DATABASE statement. Works for me, in that case. You'd

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Geoghegan
On 28 December 2012 18:40, Pavel Stehule pavel.steh...@gmail.com wrote: If I understand you, we have a fields that has behave that you expected - filename and funcname. And I have not used these fields for application programming. No, that's not what I mean. What I mean is that it seems

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Pavel Stehule
2012/12/28 Peter Geoghegan pe...@2ndquadrant.com: On 28 December 2012 18:40, Pavel Stehule pavel.steh...@gmail.com wrote: If I understand you, we have a fields that has behave that you expected - filename and funcname. And I have not used these fields for application programming. No, that's

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Geoghegan
On 28 December 2012 19:23, Pavel Stehule pavel.steh...@gmail.com wrote: for this subject ANSI SQL is more relevant source or manual for DB2 or Oracle. Design of Python and native PL languages are different. Python can use complex nested structures. PL - PL/pgSQL or PL/PSM is designed for work

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Pavel Stehule
2012/12/28 Peter Geoghegan pe...@2ndquadrant.com: On 28 December 2012 19:23, Pavel Stehule pavel.steh...@gmail.com wrote: for this subject ANSI SQL is more relevant source or manual for DB2 or Oracle. Design of Python and native PL languages are different. Python can use complex nested

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Eisentraut
On 12/10/12 4:23 PM, Peter Geoghegan wrote: Well, this is an area that the standard doesn't have anything to say about. The standard defines errcodes, but not what fields they make available. I suppose you could say that the patch proposes that they become a Postgres extension to the standard.

Re: [HACKERS] Submission Review: User control over psql error stream

2012-12-28 Thread Alastair Turner
Hi Karl, Sorry for the slow reply ... Excerpt from Karl O. Pinc k...@meme.com On Mon, Dec 10, 2012 at 5:00 AM: I was thinking along the same lines, that case 2) stderr to a file or pipe needs addressing. I think it's necessary to address the issue now. Otherwise we risk cluttering up the

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Eisentraut
On 12/28/12 2:03 PM, Peter Geoghegan wrote: No, that's not what I mean. What I mean is that it seems questionable to want to do anything *within* an exception handler on the basis of where an exception was raised from. Isn't that the whole point of this patch? The only purpose of this feature

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Geoghegan
On 28 December 2012 19:55, Pavel Stehule pavel.steh...@gmail.com wrote: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fdb2%2Frbafzmstgetdiag.htm I'm unconvinced by this. First of all, it only applies to the GET DIAGNOSTICS statement, and the only implementation that

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Eisentraut
On 12/28/12 2:03 PM, Peter Geoghegan wrote: Are you aware of any popular programming language that provides this kind of information? Can you tell in a well-principled way what function a Python exception originated from, for example? These are the built-in Python 2 exception classes:

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Geoghegan
On 28 December 2012 20:34, Peter Eisentraut pete...@gmx.net wrote: Isn't that the whole point of this patch? The only purpose of this feature is to make the exception information available in a machine-readable way. That functionality has been requested many times over the years. Right, and

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Pavel Stehule
2012/12/28 Peter Geoghegan pe...@2ndquadrant.com: On 28 December 2012 19:55, Pavel Stehule pavel.steh...@gmail.com wrote: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fdb2%2Frbafzmstgetdiag.htm I'm unconvinced by this. First of all, it only applies to the GET

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Geoghegan
On 28 December 2012 20:40, Pavel Stehule pavel.steh...@gmail.com wrote: It cannot to wait to GET DIAGNOSTICS request - because when GET DIAGNOSTICS is called, then all unsupported info about exception is lost, all used memory will be released. I'm not suggesting that you do. I'm only

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Pavel Stehule
2012/12/28 Peter Geoghegan pe...@2ndquadrant.com: On 28 December 2012 20:40, Pavel Stehule pavel.steh...@gmail.com wrote: It cannot to wait to GET DIAGNOSTICS request - because when GET DIAGNOSTICS is called, then all unsupported info about exception is lost, all used memory will be released.

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Peter Geoghegan
On 28 December 2012 20:40, Peter Eisentraut pete...@gmx.net wrote: Sure, OSError has a filename attribute (which I'm sure is qualified by a directory name if necessary), SyntaxError has filename, lineno, etc. OSError.filename is essentially the equivalent of what is being proposed here. I

Re: [HACKERS] multiple CREATE FUNCTION AS items for PLs

2012-12-28 Thread Peter Eisentraut
On 12/28/12 7:09 AM, Hannu Krosing wrote: Maybe just export the function with the same name as is defined in CREATE FUNCTION ? And in case we do want to override it, call it CREATE FUNCTION foo(a int,b int, c text) AS $$ # plpython: module modulename import x,y,z def foo(a,b,c):

Re: [HACKERS] proposal: ANSI SQL 2011 syntax for named parameters

2012-12-28 Thread Peter Eisentraut
On 12/28/12 11:22 AM, Pavel Stehule wrote: I am not sure, but maybe is time to introduce ANSI SQL syntax for functions' named parameters It is defined in ANSI SQL 2011 CALL P (B = 1, A = 2) instead PostgreSQL syntax CALL ( B := 1, A := 2) I agree it's probably time. * should we

Re: [HACKERS] proposal: ANSI SQL 2011 syntax for named parameters

2012-12-28 Thread Gavin Flower
On 29/12/12 10:19, Peter Eisentraut wrote: On 12/28/12 11:22 AM, Pavel Stehule wrote: I am not sure, but maybe is time to introduce ANSI SQL syntax for functions' named parameters It is defined in ANSI SQL 2011 CALL P (B = 1, A = 2) instead PostgreSQL syntax CALL ( B := 1, A := 2) I agree

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2012-12-28 Thread Peter Eisentraut
On 12/27/12 1:07 AM, Pavel Stehule wrote: Hello I rechecked performance of dynamic SQL and it is significantly slower in 9.2 than 9.1 -- 9.1 postgres=# create or replace function test() returns void as $$ begin for i in 1..100 loop execute 'select 1'; end loop; end $$ language

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2012-12-28 Thread Heikki Linnakangas
On 28.12.2012 23:53, Peter Eisentraut wrote: On 12/27/12 1:07 AM, Pavel Stehule wrote: Hello I rechecked performance of dynamic SQL and it is significantly slower in 9.2 than 9.1 -- 9.1 postgres=# create or replace function test() returns void as $$ begin for i in 1..100 loop execute

[HACKERS] ILIKE vs indices

2012-12-28 Thread James Cloos
While tuning an application, I found the posts from 2003 recomending the use of LOWER() and LIKE in place of ILIKE to take advantage of indices. For this app, given the limitations of the upper-layer protocol it must support, that change replaced about 30 minutes of repeated seq scans with about

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On 12/28/12 2:03 PM, Peter Geoghegan wrote: None of the Python built-in exception types have this kind of information available from fields or anything. Sure, OSError has a filename attribute (which I'm sure is qualified by a directory name if

Re: [HACKERS] ILIKE vs indices

2012-12-28 Thread Tom Lane
James Cloos cl...@jhcloos.com writes: Is there any contraindication to recasting: foo ILIKE 'bar' into: LOWER(foo) LIKE LOWER('bar') In some locales those are not equivalent, I believe, or at least shouldn't be. (What the current code actually does is a separate question.) Perhaps the

[HACKERS] Rewriter hook

2012-12-28 Thread Vlad Arkhipov
Hi all, Are there any plans on adding a rewriter hook? There are already exist parser, planner, executor hooks but there is no way to control rewriter from plugins. Some use cases: 1. Complex rules in C language. 2. Transforming an original query into a series of queries. For example,

Re: [HACKERS] Rewriter hook

2012-12-28 Thread Peter Geoghegan
On 29 December 2012 01:36, Vlad Arkhipov arhi...@dc.baikal.ru wrote: Are there any plans on adding a rewriter hook? I doubt it will ever happen. If you look at QueryRewrite(Query *parsetree), the primary entry point to the rewriter, it's easy enough to get a high level overview of what goes on.

Re: [HACKERS] Rewriter hook

2012-12-28 Thread Jaime Casanova
On Fri, Dec 28, 2012 at 8:36 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote: Some use cases: 1. Complex rules in C language. 2. Transforming an original query into a series of queries. For example, instead of UPDATE query on a table you may wish to execute UPDATE and INSERT into *the same*

Re: [HACKERS] Rewriter hook

2012-12-28 Thread Vlad Arkhipov
On 12/29/2012 11:05 AM, Jaime Casanova wrote: On Fri, Dec 28, 2012 at 8:36 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote: Some use cases: 1. Complex rules in C language. 2. Transforming an original query into a series of queries. For example, instead of UPDATE query on a table you may wish to

Re: [HACKERS] enhanced error fields

2012-12-28 Thread Pavel Stehule
2012/12/29 Tom Lane t...@sss.pgh.pa.us: Peter Eisentraut pete...@gmx.net writes: On 12/28/12 2:03 PM, Peter Geoghegan wrote: None of the Python built-in exception types have this kind of information available from fields or anything. Sure, OSError has a filename attribute (which I'm sure is

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2012-12-28 Thread Pavel Stehule
Hello 2012/12/28 Heikki Linnakangas hlinnakan...@vmware.com: On 28.12.2012 23:53, Peter Eisentraut wrote: On 12/27/12 1:07 AM, Pavel Stehule wrote: Hello I rechecked performance of dynamic SQL and it is significantly slower in 9.2 than 9.1 -- 9.1 postgres=# create or replace function