[HACKERS] plpgsql defensive mode

Hi There was a long discussion about future of PLpgSQL. I accept so Joel, Marko has good ideas based on probably strong experience from their domain. I can't accept their implementation and proposals as default for PLpgSQL now and in future. They try to mix wine and vodka concepts, and it has no

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-05 14:35 GMT+02:00 Jan Wieck : > On 09/05/2014 04:40 AM, Pavel Stehule wrote: > >> >> >> >> 2014-09-05 10:33 GMT+02:00 Marko Tiikkaja > >: >> >> On 9/5/14 10:09 AM, Pavel Stehule wrote: >> >> I think this could still be parsed correctly, though I'm not

Re: [HACKERS] Re: [PATCH] parser: optionally warn about missing AS for column and table aliases

2014-09-06 0:53 GMT+02:00 Marko Tiikkaja : > On 2014-09-05 11:33 PM, David G Johnston wrote: > >> To protect users on every query they write there would need to be some >> kind >> of "always explain first and only execute if no warnings are thrown" >> mode...and ideally some way to then override t

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 4:25 GMT+02:00 Marko Tiikkaja : > On 2014-09-04 2:28 PM, I wrote: > >> On 9/4/14 2:04 PM, Pavel Stehule wrote: >> >>> for example best practices for PL/SQL by Steven Feuerstein >>> >> >> I'll spend some time with that book to have a better idea on where >> you're coming from. >> > > I'v

Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)

On Wed, Sep 3, 2014 at 2:44 PM, Peter Geoghegan wrote: > I guess it should still be a configure option, then. Or maybe there > should just be a USE_ABBREV_KEYS macro within pg_config_manual.h. Attached additional patches are intended to be applied on top off most of the patches posted on Septembe

Re: [HACKERS] PL/pgSQL 2

On 2014-09-02 8:52 PM, Kevin Grittner wrote: Marko Tiikkaja wrote: Sounds like in this case you'd only use set-oriented programming at the end of the transaction, no? I guess -- more properly I would say "in the final database transaction for that financial transaction." Yes, I should have

Re: [HACKERS] PL/pgSQL 1.2

On 2014-09-04 2:28 PM, I wrote: On 9/4/14 2:04 PM, Pavel Stehule wrote: for example best practices for PL/SQL by Steven Feuerstein I'll spend some time with that book to have a better idea on where you're coming from. I've read through this book twice now. Some observations on things we do

Re: [HACKERS] A mechanism securing web applications in DBMS

Stephen, There are several things I want to explain: (1) Two philosophies important to our design - Try to not force web application developers to make too many changes to their apps if they wa. - Try to make our mechanism as simple as possible. Web application developers have all kinds of backgr

Re: [HACKERS] A mechanism securing web applications in DBMS

Zhaomo, As an FYI- we generally prefer inline responses rather than top-posting on the PostgreSQL mailing lists. Thanks. * Zhaomo Yang (zhy...@cs.ucsd.edu) wrote: > (1) Two philosophies important to our design > - Try to not force web application developers to make too many changes > to thei

[HACKERS] Adding a nullable DOMAIN column w/ CHECK

Hi, First of all, sorry about breaking the thread; I don't subscribe to -general so I can't copy the original email. This is in response to the problem here: http://www.postgresql.org/message-id/CACfv+p+8dToaR7h06_M_YMjpV5Na-CQq7kN=kgjq_k84h7u...@mail.gmail.com Attached is a very ugly proof

Re: [HACKERS] Need Multixact Freezing Docs

On Wed, Sep 3, 2014 at 05:17:17PM -0400, Robert Haas wrote: > > I had a look at this and came upon a problem --- there is no multi-xid > > SQL data type, and in fact the system catalogs that store mxid values > > use xid, e.g.: > > > > relminmxid | xid | not null > > > > With no

Re: [HACKERS] pg_upgrade and epoch

On Sat, Sep 6, 2014 at 12:26:55AM +0100, Greg Stark wrote: > On Wed, Sep 3, 2014 at 3:59 AM, Bruce Momjian wrote: > > I have developed the attached patch which causes pg_upgrade to preserve > > the transaction epoch. I plan to apply this for PG 9.5. > > I would say this is a simple bug and shou

Re: [HACKERS] pg_upgrade and epoch

On Wed, Sep 3, 2014 at 3:59 AM, Bruce Momjian wrote: > I have developed the attached patch which causes pg_upgrade to preserve > the transaction epoch. I plan to apply this for PG 9.5. I would say this is a simple bug and should be back patched to 9.4 and 9.3. We're only going to continue to get

Re: [HACKERS] Display of timestamp in pg_dump custom format

On Thu, Sep 4, 2014 at 01:19:31PM -0400, Bruce Momjian wrote: > Uh, not sure what I was thinking --- strftime() is the way to go. Here > is the new output: > > ; > ; Archive created at 2014-09-04 13:00:15 -0400 <--- > ; dbname: test > ; TOC Entries: 8 > ;

Re: [HACKERS] pg_upgrade and epoch

On Tue, Sep 2, 2014 at 10:38:55PM -0700, Sergey Konoplev wrote: > On Tue, Sep 2, 2014 at 7:59 PM, Bruce Momjian wrote: > > On Wed, Apr 23, 2014 at 12:41:41PM -0700, Sergey Konoplev wrote: > >> On Wed, Apr 23, 2014 at 5:26 AM, Bruce Momjian wrote: > >> > Sergey, are you seeing a problem only beca

Re: [HACKERS] PL/pgSQL 2

On 09/05/2014 12:37 PM, Merlin Moncure wrote: On Thu, Sep 4, 2014 at 6:40 PM, Florian Pflug wrote: Cost of hidden IO cast is negative too. If we can change it, then we can increase a sped. But the whole power of PL/pgSQL comes from the fact that it allows you to use the full set of postgres d

Re: [HACKERS] Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns

On Wed, Sep 3, 2014 at 12:07:55PM -0400, Bruce Momjian wrote: > On Mon, Sep 1, 2014 at 04:40:11PM -0400, Bruce Momjian wrote: > > On Mon, Sep 1, 2014 at 04:06:58PM -0400, Tom Lane wrote: > > > Bruce Momjian writes: > > > > NOTICE: moving and merging column "c" with inherited definition

Re: [HACKERS] Re: [PATCH] parser: optionally warn about missing AS for column and table aliases

On 2014-09-05 11:33 PM, David G Johnston wrote: To protect users on every query they write there would need to be some kind of "always explain first and only execute if no warnings are thrown" mode...and ideally some way to then override that on a per-query basis if you know you are correct and d

Re: [HACKERS] Re: [PATCH] parser: optionally warn about missing AS for column and table aliases

On Fri, Sep 5, 2014 at 6:27 PM, Marko Tiikkaja wrote: > On 2014-09-05 11:19 PM, David G Johnston wrote: > >> Marko Tiikkaja-4 wrote >> > > I probably couldn't mount a convincing defense of my opinion but at first >> blush I'd say we should pass on this. Not with prejudice - looking at the >> is

Re: [HACKERS] Re: [PATCH] parser: optionally warn about missing AS for column and table aliases

On 2014-09-05 11:19 PM, David G Johnston wrote: Marko Tiikkaja-4 wrote I think this is only problematic for column aliases. I wouldn't want to put these two to be put into the same category, as I always omit the AS keyword for tables aliases (and will continue to do so), but never omit it for c

Re: [HACKERS] Re: [PATCH] parser: optionally warn about missing AS for column and table aliases

On Fri, Sep 5, 2014 at 02:33:00PM -0700, David G Johnston wrote: > This at least avoids having to introduce 10 different GUC just to > accommodate this feature and neatly bundles them into named packages. > > A warn-and-execute mode would be possible as well. I would make it > incompatible wit

[HACKERS] Re: [PATCH] parser: optionally warn about missing AS for column and table aliases

David G Johnston wrote > > Marko Tiikkaja-4 wrote >> On 2014-09-05 22:38, Oskari Saarenmaa wrote: >>> I wrote the attached patch to optionally emit warnings when column or >>> table >>> aliases are used without the AS keyword after errors caused by typos in >>> statements turning unintended things

[HACKERS] Re: [PATCH] parser: optionally warn about missing AS for column and table aliases

Marko Tiikkaja-4 wrote > On 2014-09-05 22:38, Oskari Saarenmaa wrote: >> I wrote the attached patch to optionally emit warnings when column or >> table >> aliases are used without the AS keyword after errors caused by typos in >> statements turning unintended things into aliases came up twice this

Re: [HACKERS] [PATCH] Filter error log statements by sqlstate

17.01.2014 19:07, Tom Lane kirjoitti: Oskari Saarenmaa writes: I don't know about others, but filtering by individual SQLSTATE is exactly what I need - I don't want to suppress all plpgsql errors or constraint violations, but I may want to suppress plpgsql RAISE EXCEPTION and CHECK violations.

Re: [HACKERS] [PATCH] parser: optionally warn about missing AS for column and table aliases

2014-09-05 23:06 GMT+02:00 Marko Tiikkaja : > On 2014-09-05 22:56, Oskari Saarenmaa wrote: > >> What'd be a good name for the GUC controlling this, >> missing_column_as_warning? >> > > I don't know about others, but I've previously had the idea of having more > warnings like this (my go-to example

Re: [HACKERS] [PATCH] parser: optionally warn about missing AS for column and table aliases

On 2014-09-05 22:56, Oskari Saarenmaa wrote: What'd be a good name for the GUC controlling this, missing_column_as_warning? I don't know about others, but I've previously had the idea of having more warnings like this (my go-to example is DELETE FROM foo WHERE bar IN (SELECT bar FROM barless

Re: [HACKERS] [PATCH] parser: optionally warn about missing AS for column and table aliases

05.09.2014 23:45, Marko Tiikkaja kirjoitti: On 2014-09-05 22:38, Oskari Saarenmaa wrote: I wrote the attached patch to optionally emit warnings when column or table aliases are used without the AS keyword after errors caused by typos in statements turning unintended things into aliases came up t

Re: [HACKERS] [PATCH] parser: optionally warn about missing AS for column and table aliases

On 2014-09-05 22:38, Oskari Saarenmaa wrote: I wrote the attached patch to optionally emit warnings when column or table aliases are used without the AS keyword after errors caused by typos in statements turning unintended things into aliases came up twice this week. First in a discussion with a

[HACKERS] [PATCH] parser: optionally warn about missing AS for column and table aliases

I wrote the attached patch to optionally emit warnings when column or table aliases are used without the AS keyword after errors caused by typos in statements turning unintended things into aliases came up twice this week. First in a discussion with a colleague who was surprised by a 1 row result

Re: [HACKERS] Allowing implicit 'text' -> xml|json|jsonb (was: PL/pgSQL 2)

On Fri, Sep 5, 2014 at 2:04 AM, Craig Ringer wrote: > On 09/05/2014 12:04 AM, Pavel Stehule wrote: >> >> But some missing casts are well - I found lot performance issues based >> on using wrong data types - integers, dates in text column. > > Of course! That's why the default implicit casts were r

[HACKERS] VIP: explain of running query

Hi I am sending a prototype with basic implementation with explain of running query specified by pid. It can show more than the execution plan. There is a examples of full query text and running completion tag. This patch is in early stage - I know, so there is one race condition. I hoped so I

Re: [HACKERS] bad estimation together with large work_mem generates terrible slow hash joins

Hi everyone, as Heikki mentioned in his "commitfest status" message, this patch still has no reviewers :-( Is there anyone willing to pick up that, together with the 'dense allocation' patch (as those two are closely related)? I'm looking in Robert's direction, as he's the one who came up with th

Re: [HACKERS] postgresql latency & bgwriter not doing its job

On Fri, Sep 5, 2014 at 3:09 PM, Robert Haas wrote: > On Fri, Sep 5, 2014 at 4:17 AM, didier wrote: >>> It's not the size of the array that's the problem; it's the size of >>> the detonation when the allocation fails. >>> >> You can use a file backed memory array >> Or because it's only a hint and

Re: [HACKERS] postgresql latency & bgwriter not doing its job

On Fri, Sep 5, 2014 at 4:17 AM, didier wrote: >> It's not the size of the array that's the problem; it's the size of >> the detonation when the allocation fails. >> > You can use a file backed memory array > Or because it's only a hint and > - keys are in buffers (BufferTag), right? > - transition

Re: [HACKERS] PL/pgSQL 2

On Thu, Sep 4, 2014 at 6:40 PM, Florian Pflug wrote: >> Cost of hidden IO cast is negative too. If we can change it, then we can >> increase a sped. > > But the whole power of PL/pgSQL comes from the fact that it allows you to > use the full set of postgres data types and operatores, and that it s

Re: [HACKERS] implement subject alternative names support for SSL connections

On Thu, Sep 4, 2014 at 10:23 AM, Heikki Linnakangas wrote: > > Hmm. Perhaps we should use X509_NAME_get_index_by_NID + X509_NAME_get_entry > instead of X509_NAME_get_text_by_NID. You could then pass the ASN1_STRING > object to the certificate_name_entry_validate_match() function, and have it > do

Re: [HACKERS] pgbench throttling latency limit

On 09/05/2014 10:12 AM, Fabien COELHO wrote: Note that despite pg appaling latency performance, in may stay well over the 90% limit, or even 100%: when things are going well a lot of transaction run in about ms, while when things are going bad transactions would take a long time (although possibl

Re: [HACKERS] pgbench throttling latency limit

That model might make some sense if you think e.g. of a web application, where the web server has a timeout for how long it waits to get a database connection from a pool, but once a query is started, the transaction is considered a succeess no matter how long it takes. The latency limit would b

Re: [HACKERS] pgbench throttling latency limit

On 08/27/2014 04:08 AM, Heikki Linnakangas wrote: That model might make some sense if you think e.g. of a web application, where the web server has a timeout for how long it waits to get a database connection from a pool, but once a query is started, the transaction is considered a succeess no ma

Re: [HACKERS] A mechanism securing web applications in DBMS

On Fri, Sep 5, 2014 at 12:21 AM, Laurence Rowe wrote: > > > Here my_auth_function would validate the ticket and would need to be able > to do two things not currently possible with a SECURITY DEFINER function: > > 1. Call SET SESSION AUTHORIZATION / SET ROLE to become a user the > connection user

Re: [HACKERS] proposal: plpgsql - Assert statement

On 09/05/2014 04:40 AM, Pavel Stehule wrote: 2014-09-05 10:33 GMT+02:00 Marko Tiikkaja mailto:ma...@joh.to>>: On 9/5/14 10:09 AM, Pavel Stehule wrote: I think this could still be parsed correctly, though I'm not 100% sure on that: ASSERT W

Re: [HACKERS] Scaling shared buffer eviction

On Fri, Sep 5, 2014 at 8:42 AM, Mark Kirkwood wrote: > > On 04/09/14 14:42, Amit Kapila wrote: >> >> On Thu, Sep 4, 2014 at 8:00 AM, Mark Kirkwood < mark.kirkw...@catalyst.net.nz> >> wrote: >>> >>> >>> >>> Hi Amit, >>> >>> Results look pretty good. Does it help in the read-write case too? >> >> >>

Re: [HACKERS] Scaling shared buffer eviction

On Wed, Sep 3, 2014 at 1:45 AM, Robert Haas wrote: > > On Thu, Aug 28, 2014 at 7:11 AM, Amit Kapila wrote: > > I have updated the patch to address the feedback. Main changes are: > > > > 1. For populating freelist, have a separate process (bgreclaimer) > > instead of doing it by bgwriter. > > 2.

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-05 11:17 GMT+02:00 Marko Tiikkaja : > On 9/5/14 11:08 AM, Pavel Stehule wrote: > >> 2014-09-05 10:57 GMT+02:00 Marko Tiikkaja : >> >>> Oops. I meant to type ASSERT there, instead of RAISE. Does that make >>> more sense? >>> >> >> for me a basic limit is boolean_expr >> > > I don't unders

Re: [HACKERS] Allowing implicit 'text' -> xml|json|jsonb

On 9/5/14 11:08 AM, Craig Ringer wrote: On 09/05/2014 05:04 PM, Marko Tiikkaja wrote: I really don't like the idea of relaxing casts. And I really object to the notion of casting from test to date being "obviously right". Gah. It's obviously right to *reject* implicit conversions like text->

Re: [HACKERS] Join push-down support for foreign tables

On Fri, Sep 5, 2014 at 2:20 AM, Robert Haas wrote: > On Thu, Sep 4, 2014 at 11:56 AM, Bruce Momjian wrote: > >> I am thinking eventually we will need to cache the foreign server > >> statistics on the local server. > >> > >> Wouldn't that lead to issues where the statistics get outdated

Re: [HACKERS] proposal: plpgsql - Assert statement

On 9/5/14 11:08 AM, Pavel Stehule wrote: 2014-09-05 10:57 GMT+02:00 Marko Tiikkaja : Oops. I meant to type ASSERT there, instead of RAISE. Does that make more sense? for me a basic limit is boolean_expr I don't understand what you mean by this. It is about semantic and conformity of prop

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-05 10:57 GMT+02:00 Marko Tiikkaja : > On 9/5/14 10:40 AM, Pavel Stehule wrote: > >> 2014-09-05 10:33 GMT+02:00 Marko Tiikkaja : >> >>> I don't see why. The PL/PgSQL SQL parser goes to great lengths to >>> identify unmatched parenthesis. But the parens probably aren't necessary >>> in the

Re: [HACKERS] Allowing implicit 'text' -> xml|json|jsonb

On 09/05/2014 05:04 PM, Marko Tiikkaja wrote: > > I really don't like the idea of relaxing casts. And I really object to > the notion of casting from test to date being "obviously right". Gah. It's obviously right to *reject* implicit conversions like text->date. I specifically do _not_ want to

Re: [HACKERS] Allowing implicit 'text' -> xml|json|jsonb

On 9/5/14 9:04 AM, Craig Ringer wrote: It's easy to object to this on type-purist grounds, but from a pragmatic real-users point of view what we currently do is outright painful, and unless we can go and fix every language binding, every query generator, every ORM, etc to handle things just how P

Re: [HACKERS] proposal: plpgsql - Assert statement

On 9/5/14 10:40 AM, Pavel Stehule wrote: 2014-09-05 10:33 GMT+02:00 Marko Tiikkaja : I don't see why. The PL/PgSQL SQL parser goes to great lengths to identify unmatched parenthesis. But the parens probably aren't necessary in the first place; you could just omit them and keep parsing until th

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-05 10:33 GMT+02:00 Marko Tiikkaja : > On 9/5/14 10:09 AM, Pavel Stehule wrote: > >> I think this could still be parsed correctly, though I'm not 100% sure on >>> that: >>> >>> ASSERT WARNING (EXISTS(SELECT ..)), 'data are there'; >>> >>> >> PLpgSQL uses a ';' or some plpgsql keyword as SQL

Re: [HACKERS] Escaping from blocked send() reprised.

Sorry, It tha patch contains a silly bug. Please find the attatched one. > Hello, attached is the new-and-far-simple version of this > patch. It no longer depends on win32 nonblocking patch since the > socket is in blocking mode again. > > > On 08/28/2014 03:47 PM, Kyotaro HORIGUCHI wrote: > > >

Re: [HACKERS] proposal: plpgsql - Assert statement

On 9/5/14 10:09 AM, Pavel Stehule wrote: I think this could still be parsed correctly, though I'm not 100% sure on that: ASSERT WARNING (EXISTS(SELECT ..)), 'data are there'; PLpgSQL uses a ';' or some plpgsql keyword as SQL statement delimiter. It reason why RETURN QUERY ... ';' So in this c

Re: [HACKERS] Escaping from blocked send() reprised.

Hello, attached is the new-and-far-simple version of this patch. It no longer depends on win32 nonblocking patch since the socket is in blocking mode again. > On 08/28/2014 03:47 PM, Kyotaro HORIGUCHI wrote: > > - Preventing protocol violation. > > > >To prevent protocol violation, secure_writ

Re: [HACKERS] postgresql latency & bgwriter not doing its job

hi On Thu, Sep 4, 2014 at 7:01 PM, Robert Haas wrote: > On Thu, Sep 4, 2014 at 3:09 AM, Ants Aasma wrote: >> On Thu, Sep 4, 2014 at 12:36 AM, Andres Freund >> wrote: >>> It's imo quite clearly better to keep it allocated. For one after >>> postmaster started the checkpointer successfully you d

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-05 9:52 GMT+02:00 Marko Tiikkaja : > On 2014-09-05 08:16, Pavel Stehule wrote: > >> Assert is usually implemented as custom functions and used via PERFORM >> statement now >> >> -- usual current solution >> PERFORM Assert(some expression) >> >> I would to implement Assert as plpgsql intern

Re: [HACKERS] proposal: plpgsql - Assert statement

On 2014-09-05 08:16, Pavel Stehule wrote: Assert is usually implemented as custom functions and used via PERFORM statement now -- usual current solution PERFORM Assert(some expression) I would to implement Assert as plpgsql internal statement due bigger possibilities to design syntax and intern

Re: [HACKERS] Patch for psql History Display on MacOSX

On Thu, Sep 04, 2014 at 07:51:03AM -0700, Tom Lane wrote: > Noah Misch writes: > > I tried your patches against libedit-28. Wherever a command contains a > > newline, unpatched psql writes the three bytes "\^A" to the history file, > > and > > patched psql writes the four bytes "\012". Unpatche

Re: [HACKERS] Patch for psql History Display on MacOSX

On Thu, Sep 04, 2014 at 09:54:37AM -0400, Robert Haas wrote: > One point to note is that not back-patching this doesn't really fix > anything. Will a user be annoyed when .psql_history fails to reload > properly on a new minor release, but utterly indifferent to whether it > reloads in a new major

Re: [HACKERS] Allowing implicit 'text' -> xml|json|jsonb (was: PL/pgSQL 2)

On 09/05/2014 12:04 AM, Pavel Stehule wrote: > > But some missing casts are well - I found lot performance issues based > on using wrong data types - integers, dates in text column. Of course! That's why the default implicit casts were removed, and for good reason. I'm only talking about a narrow