Re: [HACKERS] Audit of logout

2014-09-06 Thread Amit Kapila
On Wed, Sep 3, 2014 at 8:09 PM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Aug 28, 2014 at 11:23 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Aug 27, 2014 at 5:19 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Aug 23, 2014 at 3:44 PM, Amit Kapila

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 06:59, Pavel Stehule wrote: People can prepare a simple functions like you did: ... And then use it in mass operations: BEGIN FOR company IN SELECT * FROM company_list() LOOP FOR id IN SELECT * FROM user_list(company) LOOP update_user(id); END LOOP;

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Marko Tiikkaja
(Forgot to answer to this part) On 2014-09-06 06:59, Pavel Stehule wrote: Your strategy is defensive. 100%. But then I don't understand to your resistant to verbosity. It is one basic stone of Ada design I've never programmed in Ada, but I don't necessarily see why more verbose would

Re: [HACKERS] SKIP LOCKED DATA (work in progress)

2014-09-06 Thread Thomas Munro
On 31 August 2014 01:36, Thomas Munro mu...@ip9.org wrote: On 28 August 2014 00:25, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Thomas Munro wrote: I haven't yet figured out how to get get into a situation where heap_lock_updated_tuple_rec waits. Well, as I think I said in the first post

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Joel Jacobson
On Sat, Sep 6, 2014 at 6:59 AM, Pavel Stehule pavel.steh...@gmail.com wrote: People can prepare a simple functions like you did: ... CREATE OR REPLACE FUNCTION user_list () RETURNS SETOF id AS $$ BEGIN RETURN QUERY SELECT id FROM user WHERE .. some = $1 END; $$ LANGUAGE plpgsql;

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Pavel Stehule
2014-09-06 15:12 GMT+02:00 Joel Jacobson j...@trustly.com: On Sat, Sep 6, 2014 at 6:59 AM, Pavel Stehule pavel.steh...@gmail.com wrote: People can prepare a simple functions like you did: ... CREATE OR REPLACE FUNCTION user_list () RETURNS SETOF id AS $$ BEGIN RETURN QUERY

Re: [HACKERS] pg_upgrade and epoch

2014-09-06 Thread Bruce Momjian
On Fri, Sep 5, 2014 at 07:35:42PM -0400, Bruce Momjian wrote: On Sat, Sep 6, 2014 at 12:26:55AM +0100, Greg Stark wrote: On Wed, Sep 3, 2014 at 3:59 AM, Bruce Momjian br...@momjian.us wrote: I have developed the attached patch which causes pg_upgrade to preserve the transaction epoch.

Re: [HACKERS] plpgsql defensive mode

2014-09-06 Thread Joel Jacobson
On Sat, Sep 6, 2014 at 7:51 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 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

Re: [HACKERS] plpgsql defensive mode

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 7:51 AM, Pavel Stehule wrote: In this mode .. all DML statements should to return EXACTLY ONE row with exception CURSORs and FOR LOOP cycle where more rows is expected. But in this case we can raise a exception NODATA if there is no row. In this mode late IO casting will be

Re: [HACKERS] plpgsql defensive mode

2014-09-06 Thread Joel Jacobson
On 6 sep 2014, at 16:32, Marko Tiikkaja ma...@joh.to wrote: How do you run queries which affect more than one row in this mode? Because that's crucial as well. We want something we can run 100% of our code on, but with a slightly more convenient syntax than PL/PgSQL provides right when

Re: [HACKERS] pg_dump warnings in MinGW build

2014-09-06 Thread Bruce Momjian
On Thu, May 15, 2014 at 10:52:43AM -0700, Jeff Janes wrote: Now that popen and pclose don't throw thousands of warnings when compiling mingw builds, some other warnings stand out. parallel.c: In function 'pgpipe': parallel.c:1332:2: warning: overflow in implicit constant conversion

Re: [HACKERS] plpgsql defensive mode

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 4:41 PM, Joel Jacobson wrote: On 6 sep 2014, at 16:32, Marko Tiikkaja ma...@joh.to wrote: How do you run queries which affect more than one row in this mode? Because that's crucial as well. We want something we can run 100% of our code on, but with a slightly more convenient

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

2014-09-06 Thread Stephen Frost
* Zhaomo Yang (zhy...@cs.ucsd.edu) wrote: I am surprised to hear this too. :) We haven't talked to many web developers yet and this is one of the things we need to do in the future. Certainly an excellent idea to talk to your target audience. :) The goal of this mechanism is to add another

Re: [HACKERS] pgcrypto: PGP signatures

2014-09-06 Thread Marko Tiikkaja
On 2014-09-05 1:38 PM, I wrote: 3) I've changed the code to use ntohl() and pg_time_t as per Thomas' comments. sig-creation_time = ntohl(*((uint32_t *) creation_time)); This is probably a horrible idea due to strict aliasing rules and alignment, though. I think I'll just hide the bit

Re: [HACKERS] btree_gist macaddr valgrind woes

2014-09-06 Thread Bruce Momjian
On Sat, May 17, 2014 at 11:46:39PM +0300, Heikki Linnakangas wrote: AFAICS, what we have to do is mark the wider gbtreekeyNN types as requiring double alignment. This will break pg_upgrade'ing any index in which they're used as non-first columns, unless perhaps all the preceding columns have

Re: [HACKERS] PL/pgSQL 2

2014-09-06 Thread Jan Wieck
On 09/05/2014 10:32 PM, Marko Tiikkaja wrote: On 2014-09-02 8:52 PM, Kevin Grittner wrote: Marko Tiikkaja ma...@joh.to 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

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Jan Wieck
On 09/06/2014 04:21 AM, Marko Tiikkaja wrote: We wrap these things into (sometimes) simple-looking function so that none of the application developers ever run any SQL. We define an interface between the application and the database, and that interface is implemented using PL/PgSQL functions.

Re: [HACKERS] PL/pgSQL 2

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 6:06 PM, Jan Wieck wrote: You can dismiss what we're doing by saying that it doesn't follow the best practices or we just want an interface for a key-value store or whatever. And yes, to some extent, a simple interface for a key-value store would come in handy. But we still have

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 6:12 PM, Jan Wieck wrote: On 09/06/2014 04:21 AM, Marko Tiikkaja wrote: We wrap these things into (sometimes) simple-looking function so that none of the application developers ever run any SQL. We define an interface between the application and the database, and that interface

Re: [HACKERS] PL/pgSQL 2

2014-09-06 Thread Jan Wieck
On 09/06/2014 12:17 PM, Marko Tiikkaja wrote: OK, fine. But that's not what I suggested on the wiki page, and is also not what I'm arguing for here right now. What the message you referred to was about was the condescending attitude where we were told to think in terms of sets (paraphrased),

Re: [HACKERS] PL/pgSQL 2

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 6:31 PM, Jan Wieck wrote: On 09/06/2014 12:17 PM, Marko Tiikkaja wrote: OK, fine. But that's not what I suggested on the wiki page, and is also not what I'm arguing for here right now. What the message you referred to was about was the condescending attitude where we were told

Re: [HACKERS] PL/pgSQL 2

2014-09-06 Thread Jan Wieck
On 09/06/2014 12:33 PM, Marko Tiikkaja wrote: On 2014-09-06 6:31 PM, Jan Wieck wrote: On 09/06/2014 12:17 PM, Marko Tiikkaja wrote: OK, fine. But that's not what I suggested on the wiki page, and is also not what I'm arguing for here right now. What the message you referred to was about was

Re: [HACKERS] pg_isready --username seems an empty promise

2014-09-06 Thread Bruce Momjian
On Mon, May 19, 2014 at 02:22:12PM +0300, Heikki Linnakangas wrote: On 05/19/2014 01:37 PM, Erik Rijkers wrote: pg_isready has --username: -U, --username=USERNAME user name to connect as so is replying when given a non-existent user not a bug? pg_isready --username= -p 6544

Re: [HACKERS] PL/pgSQL 2

2014-09-06 Thread David G Johnston
On Sat, Sep 6, 2014 at 12:38 PM, Jan Wieck-3 [via PostgreSQL] ml-node+s1045698n5818047...@n5.nabble.com wrote: On 09/06/2014 12:33 PM, Marko Tiikkaja wrote: On 2014-09-06 6:31 PM, Jan Wieck wrote: On 09/06/2014 12:17 PM, Marko Tiikkaja wrote: OK, fine. But that's not what I suggested on

Re: [HACKERS] PL/pgSQL 2

2014-09-06 Thread Jan Wieck
On 09/06/2014 12:47 PM, David G Johnston wrote: ‚ÄčIf the language, and the system as a whole, was only used by perfectionists that do not make errors - and with perfectly clean data - this adherence to purity would be acceptable. But the real world is not that clean and so enhancing the language

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-06 Thread Petr Jelinek
On 05/09/14 14:35, Jan Wieck wrote: On 09/05/2014 04:40 AM, Pavel Stehule wrote: Adding a WHEN clause to RAISE would have the benefit of not needing any new keywords at all. RAISE EXCEPTION 'format' [, expr ...] WHEN row_count 1; +1 -- Petr Jelinek

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 7:27 AM, Pavel Stehule wrote: 2014-09-05 14:35 GMT+02:00 Jan Wieck j...@wi3ck.info: Adding a WHEN clause to RAISE would have the benefit of not needing any new keywords at all. RAISE EXCEPTION 'format' [, expr ...] WHEN row_count 1; It was one my older proposal. Can we find

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Oskari Saarenmaa
06.09.2014 19:12, Jan Wieck kirjoitti: On 09/06/2014 04:21 AM, Marko Tiikkaja wrote: We wrap these things into (sometimes) simple-looking function so that none of the application developers ever run any SQL. We define an interface between the application and the database, and that interface is

Re: [HACKERS] PL/pgSQL 1.2

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 7:34 PM, Oskari Saarenmaa wrote: Anyway, I think the discussed feature to make select, update and delete throw an error if they returned or modified 1 row would be more useful as an extension of the basic sql statements instead of a plpgsql (2) only feature to make it possible to

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-06 Thread Pavel Stehule
2014-09-06 19:26 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 2014-09-06 7:27 AM, Pavel Stehule wrote: 2014-09-05 14:35 GMT+02:00 Jan Wieck j...@wi3ck.info: Adding a WHEN clause to RAISE would have the benefit of not needing any new keywords at all. RAISE EXCEPTION 'format' [, expr ...] WHEN

Re: [HACKERS] plpgsql defensive mode

2014-09-06 Thread Pavel Stehule
2014-09-06 16:31 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 2014-09-06 7:51 AM, Pavel Stehule wrote: In this mode .. all DML statements should to return EXACTLY ONE row with exception CURSORs and FOR LOOP cycle where more rows is expected. But in this case we can raise a exception NODATA if

Re: [HACKERS] plpgsql defensive mode

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 7:50 PM, Pavel Stehule wrote: 2014-09-06 16:31 GMT+02:00 Marko Tiikkaja ma...@joh.to: How do you run queries which affect more than one row in this mode? Because that's crucial as well. We want something we can run 100% of our code on, but with a slightly more convenient syntax

Re: [HACKERS] plpgsql defensive mode

2014-09-06 Thread Pavel Stehule
2014-09-06 19:54 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 2014-09-06 7:50 PM, Pavel Stehule wrote: 2014-09-06 16:31 GMT+02:00 Marko Tiikkaja ma...@joh.to: How do you run queries which affect more than one row in this mode? Because that's crucial as well. We want something we can run 100%

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 7:49 PM, Pavel Stehule wrote: this doesn't to supply assertions, it is just shorter form The original proposal very clearly seems to be why don't we do this *instead* of assertions? And in that case all of my points apply, and I'm very much against this syntax. If this is

Re: [HACKERS] proposal: plpgsql - Assert statement

2014-09-06 Thread Pavel Stehule
2014-09-06 19:59 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 2014-09-06 7:49 PM, Pavel Stehule wrote: this doesn't to supply assertions, it is just shorter form The original proposal very clearly seems to be why don't we do this *instead* of assertions? And in that case all of my points

[HACKERS] Improving PL/PgSQL (was: Re: plpgsql defensive mode)

2014-09-06 Thread Marko Tiikkaja
On 2014-09-06 7:56 PM, Pavel Stehule wrote: 2014-09-06 19:54 GMT+02:00 Marko Tiikkaja ma...@joh.to: Then that doesn't really solve our problem. Switching between two languages on a per-function basis, when both look exactly the same but have very different semantics would be a nightmare. It

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

2014-09-06 Thread Tom Lane
Craig Ringer cr...@2ndquadrant.com writes: 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

Re: [HACKERS] Improving PL/PgSQL

2014-09-06 Thread Jan Wieck
On 09/06/2014 02:08 PM, Marko Tiikkaja wrote: On 2014-09-06 7:56 PM, Pavel Stehule wrote: 2014-09-06 19:54 GMT+02:00 Marko Tiikkaja ma...@joh.to: Then that doesn't really solve our problem. Switching between two languages on a per-function basis, when both look exactly the same but have very

Re: [HACKERS] Improving PL/PgSQL (was: Re: plpgsql defensive mode)

2014-09-06 Thread Pavel Stehule
2014-09-06 20:08 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 2014-09-06 7:56 PM, Pavel Stehule wrote: 2014-09-06 19:54 GMT+02:00 Marko Tiikkaja ma...@joh.to: Then that doesn't really solve our problem. Switching between two languages on a per-function basis, when both look exactly the same

Re: [HACKERS] Final Patch for GROUPING SETS - unrecognized node type: 347

2014-09-06 Thread Tomas Vondra
On 31.8.2014 22:52, Andrew Gierth wrote: Recut patches: gsp1.patch - phase 1 code patch (full syntax, limited functionality) gsp2.patch - phase 2 code patch (adds full functionality using the new chained aggregate mechanism) gsp-doc.patch - docs

Re: [HACKERS] Final Patch for GROUPING SETS - unrecognized node type: 347

2014-09-06 Thread Andrew Gierth
Tomas == Tomas Vondra t...@fuzzy.cz writes: Tomas I have significant doubts about the whole design, Tomas though. Especially the decision not to use HashAggregate, There is no decision not to use HashAggregate. There is simply no support for HashAggregate yet. Having it be able to work with

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

2014-09-06 Thread Tom Lane
Noah Misch n...@leadboat.com writes: On Thu, Sep 04, 2014 at 07:51:03AM -0700, Tom Lane wrote: I think you got the test cases backwards, or maybe neglected the aspect about how unpatched psql will only translate ^J to ^A in the oldest (or maybe the newest? too pressed for time to recheck right

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

2014-09-06 Thread Peter Geoghegan
On Fri, Sep 5, 2014 at 7:45 PM, Peter Geoghegan p...@heroku.com wrote: Attached additional patches are intended to be applied on top off most of the patches posted on September 2nd [1]. I attach another amendment/delta patch, intended to be applied on top of what was posted yesterday. I

Re: [HACKERS] jsonb format is pessimal for toast compression

2014-09-06 Thread David E. Wheeler
On Sep 4, 2014, at 7:26 PM, Jan Wieck j...@wi3ck.info wrote: This is only because the input data was exact copies of the same strings over and over again. PGLZ can very well compress slightly less identical strings of varying lengths too. Not as well, but well enough. But I suspect such

Re: [HACKERS] Stating the significance of Lehman Yao in the nbtree README

2014-09-06 Thread Peter Geoghegan
On Tue, Jul 22, 2014 at 10:49 PM, Peter Geoghegan p...@heroku.com wrote: Basically I think it will be better if you can explain in bit more detail that how does right-links at all levels and high-key helps to detect and recover from concurrent page splits. You might be right about that -

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

2014-09-06 Thread Tom Lane
I wrote: What I'm inclined to do based on this info is to start the loop at history_base - 1, and ignore NULL returns until we're past history_base. I poked at that for awhile and decided it was a bad approach. It emerges that libedit's history_get() is just as full of version-specific

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

2014-09-06 Thread Zhaomo Yang
Stephen, As an FYI- we generally prefer inline responses rather than top-posting on the PostgreSQL mailing lists. Thanks. Sorry for that. - Try to make our mechanism as simple as possible. Web application developers have all kinds of backgrounds. If the security mechanism is too alien

Re: [HACKERS] Improving PL/PgSQL (was: Re: plpgsql defensive mode)

2014-09-06 Thread Pavel Stehule
2014-09-06 21:47 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com: 2014-09-06 20:08 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 2014-09-06 7:56 PM, Pavel Stehule wrote: 2014-09-06 19:54 GMT+02:00 Marko Tiikkaja ma...@joh.to: Then that doesn't really solve our problem. Switching between

Re: [HACKERS] Adding a nullable DOMAIN column w/ CHECK

2014-09-06 Thread Noah Misch
On Sat, Sep 06, 2014 at 02:01:32AM +0200, Marko Tiikkaja wrote: 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:

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

2014-09-06 Thread Peter Geoghegan
On Sat, Sep 6, 2014 at 3:01 PM, Peter Geoghegan p...@heroku.com wrote: I attach another amendment/delta patch Attached is another amendment to the patch set. With the recent addition of abbreviation support on 32-bit platforms, we should just hash the Datum representation as a uint32 on

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

2014-09-06 Thread Noah Misch
On Sat, Sep 06, 2014 at 11:40:02PM -0400, Tom Lane wrote: I only tried this directly on Tiger, Snow Leopard, and Mavericks. I tested libedit-28 by compiling from source on a RHEL machine, so it's possible that there's some difference between what I tested and what Apple's really shipping. If