Re: [HACKERS] Audit of logout
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 amit.kapil...@gmail.com wrote: On Tue, Aug 5, 2014 at 8:04 PM, Fujii Masao masao.fu...@gmail.com wrote: Changing PGC_SU_BACKEND parameter (log_connections) is visible even with a non-super user client due to above code. Shouldn't it be only visible for super-user logins? Simple steps to reproduce the problem: a. start Server (default configuration) b. connect with superuser c. change in log_connections to on in postgresql.conf d. perform select pg_reload_conf(); e. connect with non-super-user f. show log_connections; --This step shows the value as on, --whereas I think it should have been off In this case, log_connections is changed in postgresql.conf and it's reloaded, so ISTM that it's natural that even non-superuser sees the changed value. No? Maybe I'm missing something. Yeah, you are right. With the latest patch, I am getting one regression failure on windows. Attached is the regression diff. Thanks for testing the patch! That regression failure looks strange, I'm not sure yet why that happened... Does it happen only on Windows? Yes, it was failing only on windows. Today when I further tried to look into the issue, I found that if I rebuild plpgsql, it didn't occurred. So the conclusion is that it occurred due to build mismatch, however I am not sure why a rebuild of plpgsql is required for this patch. Sorry for the noise. There are no more comments from myside, so I will mark this as Ready For Committer. Can we improve this line a bit? ! * BACKEND options are the same as SU_BACKEND ones, but they can BACKEND options can be set same as SU_BACKEND ones, .. Yep. Okay. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] PL/pgSQL 1.2
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; Or use it in application same style. Yes, someone *could* do that, people are dumb. But that's sort of *exactly* why we do it. 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. Sure, sometimes one function will just fire off a single UPDATE .. RETURNING, or a SELECT, but that doesn't matter. The trick is to be consistent everywhere. But further, even if we did follow every single one of the above points perfectly, it wouldn't change the point we're trying to make. What we're doing is following what the book dedicated an entire chapter to: Defensive Programming. Enforcing that that UPDATE affected exactly one row? Defensive Programming. Your strategy is defensive. 100%. But then I don't understand to your resistant to verbosity. It is one basic stone of Ada design The problem of defensive strategy in stored procedures is possibility to block optimizer and result can be terrible slow. On the end, it needs a complex clustering solution, complex HA24 solution and higher complexity ~ less safety. This is not problem on low load or low data applications. Banking applications are safe (and I accept, so there it is necessary), but they are not famous by speed. Right. We deal with money. In general, I'll take slow over buggy any day. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 1.2
(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 unconditionally mean more defensive. My primary reason for objecting to some of the syntax suggestions that have been thrown around previously and during the last couple of days is that once you increase verbosity enough, the specialized syntax starts to be less and less desirable compared to what you can already do today. And even that I only try to apply to the parts of the syntax I find verbose just for the sake of being verbose, i.e. without any additional functionality, disambiguity or clarity. For example, having something like a CONSTRAINT CHECK (row_count = 1); is not really significantly better than RETURNING TRUE INTO STRICT _OK. It's better because the intent is more clear, and because you don't need a special _OK variable, but it still has 90% of the pain of the syntax you can use today. That being the useless verbosity. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SKIP LOCKED DATA (work in progress)
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 I mentioned this, maybe there is no such situation. In any case, like the EvalPlanQualFetch issue, we can fix it later if we find it. I finally came up with a NOWAIT spec that reaches heap_lock_updated_rec and then blocks. I can't explain why exactly... but please see attached. The fix seems fairly straightforward. Do you think I should submit an independent patch to fix this case (well there are really two cases, since there is a separate multixact path) for the existing NOWAIT support and then tackle the SKIP LOCKED equivalent separately? Oops, that isolation wasn't right, please disregard. Unless you think this obscure case needs to be addressed before the SKIP LOCKED patch can be committed, I will investigate it separately and maybe submit something to a future commitfest. Best regards, Thomas Munro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 1.2
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; CREATE OR REPLACE FUNCTION update_user(int) RETURNS void AS $$ BEGIN UPDATE user SET .. WHERE id = $1 END; $$ LANGUAGE; 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; Or use it in application same style. It is safe .. sure, and I accept it. But It is terrible slow. The above is horrible and ugly. That's not how I write code. Only for top-level functions, i.e. API-functions, is it motivated to encapsulate even simple queries like that, but *never* in other PL-functions, as that doesn't fulfil any purpose, putting simple queries inside functions only make it less obvious what the code does where you have a function call instead of a SQL-query. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 1.2
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 SELECT id FROM user WHERE .. some = $1 END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION update_user(int) RETURNS void AS $$ BEGIN UPDATE user SET .. WHERE id = $1 END; $$ LANGUAGE; 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; Or use it in application same style. It is safe .. sure, and I accept it. But It is terrible slow. The above is horrible and ugly. That's not how I write code. Only for top-level functions, i.e. API-functions, is it motivated to encapsulate even simple queries like that, but *never* in other PL-functions, as that doesn't fulfil any purpose, putting simple queries inside functions only make it less obvious what the code does where you have a function call instead of a SQL-query. It is ugly, but I meet it. Its nothing special. Pavel
Re: [HACKERS] pg_upgrade and epoch
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. 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 complaints from people running into this. Yes, I did think about that, but it seems like a behavior change. However, it is tempting to avoid future bug reports about this. When this came up in March, Tom and I agreed that this wasn't something we wanted to slip into 9.4. Given that, it is hard to argue we should now slip this into 9.5, 9.4, and 9.3, so unless someone else votes for inclusion, I think I will leave this as 9.5-only. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plpgsql defensive mode
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 PLpgSQL now and in future. They try to mix wine and vodka concepts, and it has no good ends. I understand to their proposal as restrictive subset of PLpgSQL. restrictive subset is not good name. We can implement some features without impact on syntax as block on function level. Marko likes defensive programming, so we can use a name defensive_mode 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 disabled. We can disallow implicit casting too. We can talk what plpgsql warnings in this mode will be critical. This mode can be enabled for function by option #option defensive or for all plpgsql functions by GUC SET plpgsql.defensive = on In this moment I don't see a necessity to change or enhance syntax. I have no plan to use it as default, but anybody can do it simply by change one GUC in Postgres configuration. Defensive mode (strict defensive mode) is good strategy, but it is not for all. +1 -- this would mean my original proposal would be possible, i.e. no syntax change at all. But to solve this the proper way, and avoid a long list of options/settings, it would be really nice being able to define a new language, like pltrustly, which sets the mix of settings which are relevant for us, where this would be a setting which is apparently not desirable for everybody. I also hope all the other things listed in the wiki* are possible to fix in PL/pgSQL 2 (or even better in PL/pgSQL, if possible). Pavel, do you have any input on the other items on the wiki? Most of them are problems which really ought to raise errors. *) https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plpgsql defensive mode
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 disabled. We can disallow implicit casting too. We can talk what plpgsql warnings in this mode will be critical. This mode can be enabled for function by option #option defensive or for all plpgsql functions by GUC SET plpgsql.defensive = on In this moment I don't see a necessity to change or enhance syntax. 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 coding defensively in the cases where exactly one row should be affected. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plpgsql defensive mode
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 coding defensively in the cases where exactly one row should be affected. If we would have ORDER BY also for UPDATE/DELETE then one could just order by something arbitrary to express multiple or zero rows are OK, even if not being interested in the order. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump warnings in MinGW build
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 [-Woverflow] parallel.c:1386:3: warning: overflow in implicit constant conversion [-Woverflow] I think the solution is to use the pgsocket typedef from src/include/port.h, rather than int. Like attached. But I'm far from being a typedef lawyer, so maybe I am all wet. FYI, I am not sure if you saw that this was fixed in June as part of a larger patch: commit ac608fe758455804f26179ea7c556e7752e453e8 Author: Bruce Momjian br...@momjian.us Date: Mon Jun 16 15:24:38 2014 -0400 Use type pgsocket for Windows pipe emulation socket calls This prevents several compiler warnings on Windows. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plpgsql defensive mode
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 syntax than PL/PgSQL provides right when coding defensively in the cases where exactly one row should be affected. If we would have ORDER BY also for UPDATE/DELETE then one could just order by something arbitrary to express multiple or zero rows are OK, even if not being interested in the order. Ugh. That can't possibly end well. Overriding the meaning of something that already works as standalone SQL statement sounds like a really bad idea. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A mechanism securing web applications in DBMS
* 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 layer of protection inside DBMS so that even if the application server is compromised the users' data is under protection*. This requires DBMS to be able to authenticate application-level users (know which application-level user it is communicating with). That it, we need to move the authentication logic of application-level users into DBMS. For this purpose, using store procedures (or something similar) is a must. I think even if a security mechanism is designed to be easy to use, it will still require some expertise. I agree that good security does require expertise to get right. * this mechanism can't help if the attackers control the app server completely and the users are not aware of that and keep using the app. In that case the attackers will be able to collect all the credentials of the users who log in while they are in charge. This is really the crux of the problem you're trying to solve- what is the attack vector? Based on the discussion so far, I imagine you're considering the attacker can submit arbitrary SQL situation, where the attacker doesn't have full access to the application server. That's certainly a worthwhile thing to consider, but I'm not entirely sure the approach you've outlined will work out well.. If a temp table is being used then dynamic SQL may be required and therefore a plpgsql function will be involved to handle looking up the current user, as you won't be using PG roles. This is why I'd like to have global temp table in PG. With that we can probably get around of dynamic SQL. Have you considered just using a regular, but unlogged, table? That would also avoid any risk that the application manages to drop or shadow the temp table somehow with a fake table that changes who is currently authenticated, and avoids having to figure out how to deal with the temp table vanishing due to the connections going away. You are absolutely right. I should've explained it better. I just wanted to show how authentication works and skipped all the hashing part. Ah, ok. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pgcrypto: PGP signatures
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 shifts behind a function instead. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] btree_gist macaddr valgrind woes
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 at least double size/alignment. I guess pg_upgrade can check for that, but it'll be kind of a pain. Another issue is what the heck btree_gist's extension upgrade script ought to do about this. It can't just go and modify the type declarations. Actually, on further thought, this isn't an issue for pg_upgrade at all, just for the extension upgrade script. Maybe we just have to make it poke through the catalogs looking for at-risk indexes, and refuse to complete the extension upgrade if there are any? I think it would be best to just not allow pg_upgrade if there are any indexes using the ill-defined types. The upgrade script could then simply drop the types and re-create them. The DBA would need to drop the affected indexes before upgrade and re-create them afterwards, but I think that would be acceptable. I doubt there are many people using btree_gist on int8 or float8 columns. Another way to attack this would be to change the code to memcpy() the values before accessing them. That would be ugly, but it would be back-patchable. In HEAD, I'd rather bite the bullet and get the catalogs fixed, though. What did we decide about this issue/fix and pg_upgrade? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
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 transaction for that financial transaction. Yes, I should have said financial transaction, but I hit send a bit too early. And no, that never made me wish that plpgsql functions defaulted to throwing errors for DML statements that affected more than one row. Fine. But you should still be able to see the point we're trying to make. The number one is special, and it's present everywhere. If you want to program defensively, you have to go through a lot of pain right now. We're looking for a way to alleviate that pain. Defaulting to throwing errors would be one way to do it, but that's not what's being suggested here anymore. 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 the 5-15% (big part of it being the reporting we need to do) of the code that *doesn't* want that, *and* we want to use all of the Postgres features where applicable. The point isn't about best practices. The point is that if you want to ensure that at maximum one row is affected, then qualify it by a unique set of columns. Making PL/pgSQL behave different on UPDATE than SQL to enforce that by default was simply a misguided design idea. Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 1.2
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. Sure, sometimes one function will just fire off a single UPDATE .. RETURNING, or a SELECT, but that doesn't matter. The trick is to be consistent everywhere. There is precisely your root problem. Instead of educating your application developers on how to properly use a relational database system, you try to make it foolproof. Guess what, the second you made something foolproof, evolution will create a dumber fool. This is a race you cannot win. Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
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 the 5-15% (big part of it being the reporting we need to do) of the code that *doesn't* want that, *and* we want to use all of the Postgres features where applicable. The point isn't about best practices. It got to that point upthread. The point is that if you want to ensure that at maximum one row is affected, then qualify it by a unique set of columns. And what if you get the set of columns wrong (also consider the presence of joins)? What if someone changes that set of columns? What if your unique indexes have been violated because of a bug in postgres or hardware malfunction? Wouldn't you want the problem to be obvious? Making PL/pgSQL behave different on UPDATE than SQL to enforce that by default was simply a misguided design idea. 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), without considering whether that's even possible to do *all the time*. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 1.2
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 is implemented using PL/PgSQL functions. Sure, sometimes one function will just fire off a single UPDATE .. RETURNING, or a SELECT, but that doesn't matter. The trick is to be consistent everywhere. There is precisely your root problem. Instead of educating your application developers on how to properly use a relational database system, you try to make it foolproof. Foolproofing is just one thing that's good about this solution. The other one would be that the application *doesn't need to know* what's going on behind the scenes. The app deals with a consistent API, and we make that API happen with PL/PgSQL. Guess what, the second you made something foolproof, evolution will create a dumber fool. This is a race you cannot win. You're completely missing the point. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
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), without considering whether that's even possible to do *all the time*. SQL is, by definition, a set oriented language. The name Procedural Language / pgSQL was supposed to suggest that this language adds some procedural elements to the PostgreSQL database. I never intended to create a 100% procedural language. It was from the very beginning, 16 years ago, intended to keep the set orientation when it comes to DML statements inside of functions. That means that you will have to think in sets *all the time*. The empty set and a set with one element are still sets. No matter how hard you try to make them special, in my mind they are not. Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
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 to think in terms of sets (paraphrased), without considering whether that's even possible to do *all the time*. SQL is, by definition, a set oriented language. The name Procedural Language / pgSQL was supposed to suggest that this language adds some procedural elements to the PostgreSQL database. I never intended to create a 100% procedural language. It was from the very beginning, 16 years ago, intended to keep the set orientation when it comes to DML statements inside of functions. No matter how hard you try to make them special, in my mind they are not. Of course they are. That's why you have PRIMARY KEYs and UNIQUE constraints. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
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 the condescending attitude where we were told to think in terms of sets (paraphrased), without considering whether that's even possible to do *all the time*. SQL is, by definition, a set oriented language. The name Procedural Language / pgSQL was supposed to suggest that this language adds some procedural elements to the PostgreSQL database. I never intended to create a 100% procedural language. It was from the very beginning, 16 years ago, intended to keep the set orientation when it comes to DML statements inside of functions. No matter how hard you try to make them special, in my mind they are not. Of course they are. That's why you have PRIMARY KEYs and UNIQUE constraints. Then please use those features instead of crippling the language. Jan -- Jan Wieck Senior Software Engineer http://slony.info -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_isready --username seems an empty promise
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 /tmp:6544 - accepting connections There is no user . (PG envvars are removed) Per the manual page on pg_isready: The options --dbname and --username can be used to avoid gratuitous error messages in the logs, but are not necessary for proper functionality. The libpq entry on PQpingParams(), which pg_isready uses, has a bit more details: It is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connection attempt. BTW, I find the above PQpingParams() explanation much more clear than one in pg_isready. I think we should use the same text in both places. Done. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
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 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), without considering whether that's even possible to do *all the time*. SQL is, by definition, a set oriented language. The name Procedural Language / pgSQL was supposed to suggest that this language adds some procedural elements to the PostgreSQL database. I never intended to create a 100% procedural language. It was from the very beginning, 16 years ago, intended to keep the set orientation when it comes to DML statements inside of functions. No matter how hard you try to make them special, in my mind they are not. Of course they are. That's why you have PRIMARY KEYs and UNIQUE constraints. Then please use those features instead of crippling the language. 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 to meet the needs of the real world is not crippling the language. Begin able to state explicitly that the cardinality of the set I get back must be 1, no more and no less, doesn't remove the fact that I know I am dealing with a set and that I simply want to make an assertion as to its properties so that if a bug 3 layers deep into the application causes something other than 1 row to be affected I know immediately and can invoke the appropriate action - throw an error. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5818051.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] PL/pgSQL 2
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 to meet the needs of the real world is not crippling the language. Begin able to state explicitly that the cardinality of the set I get back must be 1, no more and no less, doesn't remove the fact that I know I am dealing with a set and that I simply want to make an assertion as to its properties so that if a bug 3 layers deep into the application causes something other than 1 row to be affected I know immediately and can invoke the appropriate action - throw an error. As I already mentioned in the other thread, those assertions or checks do not belong into the PL. If they are desired they should be added to the main SQL syntax as COMMAND CONSTRAINT like suggested by Hannu. Your statement is not limited to PL functions. It is just as valid for NORMAL applications. However, this would be a proprietary extension that is not covered by any SQL standard and for that reason alone cannot be the default. Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: plpgsql - Assert statement
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 http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: plpgsql - Assert statement
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 a agreement there? I find: 1) The syntax less readable than IF row_count 1 THEN RAISE EXCEPTION ..; END IF; 2) It needless to require the user to specify an error message for every assertion. 3) Allowing these to be disabled would be weird (though I might be the only one who wants that feature at this point). 4) It would also be weird to display the parameters passed to the WHEN clause like I suggested here: http://www.postgresql.org/message-id/54096ba4.5030...@joh.to . I think that's a crucial part of the feature. So at least the vote isn't unanimous: -1 from me. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 1.2
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 implemented using PL/PgSQL functions. Sure, sometimes one function will just fire off a single UPDATE .. RETURNING, or a SELECT, but that doesn't matter. The trick is to be consistent everywhere. There is precisely your root problem. Instead of educating your application developers on how to properly use a relational database system, you try to make it foolproof. There are also other reasons to wrap everything in functions, for example sharding using pl/proxy which by the way always throws an error if a SELECT didn't match exactly one row and the function wasn't declared returning 'SETOF' (although it currently doesn't set any sqlstate for these errors making it a bit difficult to properly catch them.) 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 use it from other languages and outside functions. / Oskari -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 1.2
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 use it from other languages and outside functions. I can't really say I object to this, but doing it in the PL allows the parameters to be printed as well, akin to the plpgsql.print_strict_params setting added in 9.4. Though I wonder if that would still be possible if PL/PgSQL peeked inside the parse tree a bit to pull out these constraints or something *waves hands*. Or perhaps there's a better way to attach a helpful DETAIL line to the error. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: plpgsql - Assert statement
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 row_count 1; It was one my older proposal. Can we find a agreement there? I find: 1) The syntax less readable than IF row_count 1 THEN RAISE EXCEPTION ..; END IF; 2) It needless to require the user to specify an error message for every assertion. 3) Allowing these to be disabled would be weird (though I might be the only one who wants that feature at this point). 4) It would also be weird to display the parameters passed to the WHEN clause like I suggested here: http://www.postgresql.org/ message-id/54096ba4.5030...@joh.to . I think that's a crucial part of the feature. So at least the vote isn't unanimous: -1 from me. this doesn't to supply assertions, it is just shorter form Pavel .marko
Re: [HACKERS] plpgsql defensive mode
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 there is no row. In this mode late IO casting will be disabled. We can disallow implicit casting too. We can talk what plpgsql warnings in this mode will be critical. This mode can be enabled for function by option #option defensive or for all plpgsql functions by GUC SET plpgsql.defensive = on In this moment I don't see a necessity to change or enhance syntax. 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 coding defensively in the cases where exactly one row should be affected. you use a normal function. I don't expect, so it can be too often in your case. Pavel .marko
Re: [HACKERS] plpgsql defensive mode
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 than PL/PgSQL provides right when coding defensively in the cases where exactly one row should be affected. you use a normal function. I don't expect, so it can be too often in your case. 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. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plpgsql defensive mode
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% of our code on, but with a slightly more convenient syntax than PL/PgSQL provides right when coding defensively in the cases where exactly one row should be affected. you use a normal function. I don't expect, so it can be too often in your case. 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 is maximum what is possible use a different language instead Pavel .marko
Re: [HACKERS] proposal: plpgsql - Assert statement
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 supposed to be in the language *in addition to* assertions, let's please be clear about that. (In that case I wouldn't object, though I probably wouldn't use this feature either.) .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: plpgsql - Assert statement
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 apply, and I'm very much against this syntax. If this is supposed to be in the language *in addition to* assertions, let's please be clear about that. (In that case I wouldn't object, though I probably wouldn't use this feature either.) It was just my reaction to Jan proposal in this thread. pavel .marko
[HACKERS] Improving PL/PgSQL (was: Re: plpgsql defensive mode)
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 is maximum what is possible use a different language instead Sigh. OK, let's try and forget the cardinality assertions we've been talking about in the other thread(s). I seem to recall there being a generally welcoming atmosphere in the discussion about adding a set of pragmas (or options/whatever) to make some of PL/PgSQL's flaws go away, in a non-backwards compatible way. From the list here: https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014) do you think at least some of those would be reasonable candidates for these pragmas? Do you see others ones that are missing from this list? Please also keep discussion about ASSERT in the thread for that, and the suggestion under Single-row operations out of this. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing implicit 'text' - xml|json|jsonb
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 text-date. I specifically do _not_ want to add such a conversion, and gave a list of types for which I think conversions from text are appropriate. The only concrete argument you gave why it would be safe to allow those was that the respective datatypes perform input validation. But so does text-date, so I am failing to see any meaningful distinction there. As a larger point, validation during runtime type conversions isn't really the problem. The risk created by having an abundance of implicit casts is that the parser may choose a surprising interpretation of an expression, or be unable to choose at all because there's no clearly preferred option among multiple ambiguous possibilities. So what you'd really need to argue to claim this is safe is that there are no existing functions or operators overloaded for both text and xml (resp. jsonb, etc). And that no such ambiguous cases are likely to be wanted in the future either. A quick look in pg_operator says this already falls down for the basic comparison operators on jsonb ... The problem here seems to be only related to mistyped parameters. Can we contain the damage to that part only somehow? Or make this optional (defaulting to off, I hope)? I'd love to make it affect only parameters, actually, for v3 protocol bind/parse/execute. That would be ideal. Well, let's talk about that. Doing something with parameter type assignment seems a lot less likely to result in unexpected side-effects than introducing a dozen new implicit casts. Right now the main workaround is to send all string-typed parameters as 'unknown'-typed, but that causes a mess with function overload resolution, and it's wrong most of the time when the parameter really is just text. If you think adding implicit casts *won't* cause a mess with function overload resolution, I wonder why. Really though it seems like the question is how much clarity there is on the client side about what data types parameters should have. I get the impression that liberal use of unknown is really about the right thing in a lot of client APIs ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Improving PL/PgSQL
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 different semantics would be a nightmare. It is maximum what is possible use a different language instead Sigh. OK, let's try and forget the cardinality assertions we've been talking about in the other thread(s). I seem to recall there being a generally welcoming atmosphere in the discussion about adding a set of pragmas (or options/whatever) to make some of PL/PgSQL's flaws go away, in a non-backwards compatible way. From the list here: https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014) do you think at least some of those would be reasonable candidates for these pragmas? Do you see others ones that are missing from this list? Please also keep discussion about ASSERT in the thread for that, and the suggestion under Single-row operations out of this. +1 for SELECT INTO throwing TOO_MANY_ROWS if there are more than one. Zero rows should be dealt with an IF NOT FOUND ... construct. +1 for the number of result columns should match the expression list of SELECT INTO. -1 on removal of implicit type casting. This needs to go into a #pragma or GUC. Too drastic of a backwards compatibility break. -1 on the single row operations. This belongs into the main SQL engine as COMMAND CONSTRAINTS. +1 on EXECUTE and FOUND, where applicable (DML statements only). I do not recall why we decided to implement GET DIAGNOSTICS instead of an automatically set global ROW_COUNT variable. But there was a reason I believe and we should check the list archives for it. +1 on the OUT alias. -1 on the ASSERT as proposed. It would be too easy for application developers to abuse them to govern business logic and a DBA later turning off assertions for performance reasons. Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Improving PL/PgSQL (was: Re: plpgsql defensive mode)
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 but have very different semantics would be a nightmare. It is maximum what is possible use a different language instead Sigh. OK, let's try and forget the cardinality assertions we've been talking about in the other thread(s). I seem to recall there being a generally welcoming atmosphere in the discussion about adding a set of pragmas (or options/whatever) to make some of PL/PgSQL's flaws go away, in a non-backwards compatible way. From the list here: https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014) do you think at least some of those would be reasonable candidates for these pragmas? Do you see others ones that are missing from this list? Please also keep discussion about ASSERT in the thread for that, and the suggestion under Single-row operations out of this. SELECT .. INTO vs. TOO_MANY_ROWS +1 .. possible every where Variable assignments +1 .. only in defensive mode Single-row operations +1 .. only in defensive mode without special syntax EXECUTE and FOUND -1 .. it is emulation of PL/SQL behave.. so introduction can do too high unhappy surprise if somebody will migrate to Oracle -- the syntax is too similar OUT parameters -1 .. to proposal .. It is in contradiction with current feature. Next it is nonsense. INTO clause should to contains only plpgsql variables - in 9.x Postgres there is not possible issue. postgres=# create table x(a int, b int); CREATE TABLE postgres=# insert into x values(10,20); INSERT 0 1 postgres=# create or replace function foo(out a int, out b int) postgres-# returns record as $$ postgres$# begin postgres$# select x.a, x.b from x into a, b; postgres$# return; postgres$# end; postgres$# $$ language plpgsql; CREATE FUNCTION postgres=# select * from foo(); a | b + 10 | 20 (1 row) you can see, there is not any collision Assertions -1 to proposed syntax - I wrote about my reasons in other thread. Regards Pavel .marko
Re: [HACKERS] Final Patch for GROUPING SETS - unrecognized node type: 347
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 gsp-contrib.patch - quote cube in contrib/cube and contrib/earthdistance, intended primarily for testing pending a decision on renaming contrib/cube or unreserving keywords gsp-u.patch- proposed method to unreserve CUBE and ROLLUP (the contrib patch is not necessary if the -u patch is used; the contrib/pg_stat_statements fixes are in the phase1 patch) Hi, I looked at the patch today. The good news is it seems to apply cleanly on HEAD (with some small offsets, but no conflicts). The code generally seems OK to me, although the patch is quite massive. I've also did a considerable amount of testing and I've been unable to cause failures. I have significant doubts about the whole design, though. Especially the decision not to use HashAggregate, and the whole chaining idea. I haven't noticed any discussion about this (at least in this thread), and the chaining idea was not mentioned until 21/8, so I'd appreciate some reasoning behind this choice. I assume the no HashAggregate decision was done because of fear of underestimates, and the related OOM issues. I don't see how this is different from the general HashAggregate, though. Or is there another reason for this? Now, the chaining only makes this worse, because it effectively forces a separate sort of the whole table for each grouping set. We're doing a lot of analytics on large tables, where large means tens of GBs and hundreds of millions of rows. What we do now at the moment is basically the usual ROLAP approach - create a cube with aggregated data, which is usually much smaller than the source table, and then compute the rollups for the interesting slices in a second step. I was hoping that maybe we could eventually replace this with the GROUP BY CUBE functionality provided by this patch, but these design decisions make it pretty much impossible. I believe most other users processing non-trivial amounts of data (pretty much everyone with just a few million rows) will be in similar position :-( What I envisioned when considering hacking on this a few months back, was extending the aggregate API with merge state function, doing the aggregation just like today and merging the groups (for each cell) at the end. Yeah, we don't have this infrastructure, but maybe it'd be a better way than the current chaining approach. And it was repeatedly mentioned as necessary for parallel aggregation (and even mentioned in the memory-bounded hashagg batching discussion). I'm ready to spend some time on this, if it makes the grouping sets useful for us. regards Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Final Patch for GROUPING SETS - unrecognized node type: 347
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 GroupAggregate is essential, because there are always cases where HashAggregate is simply not permitted (e.g. when using distinct or sorted aggs; or unhashable types; or with the current code, when the estimated memory usage exceeds work_mem). HashAggregate may be a performance improvement, but it's something that can be added afterwards rather than an essential part of the feature. Tomas Now, the chaining only makes this worse, because it Tomas effectively forces a separate sort of the whole table for each Tomas grouping set. It's not one sort per grouping set, it's the minimal number of sorts needed to express the result as a union of ROLLUP clauses. The planner code will (I believe) always find the smallest number of sorts needed. Each aggregate node can process any number of grouping sets as long as they represent a single rollup list (and therefore share a single sort order). Yes, this is slower than using one hashagg. But it solves the general problem in a way that does not interfere with future optimization. (HashAggregate can be added to the current implementation by first adding executor support for hashagg with multiple grouping sets, then in the planner, extracting as many hashable grouping sets as possible from the list before looking for rollup lists. The chained aggregate code can work just fine with a HashAggregate as the chain head. We have not actually tackled this, since I'm not going to waste any time adding optimizations before the basic idea is accepted.) Tomas What I envisioned when considering hacking on this a few Tomas months back, was extending the aggregate API with merge Tomas state function, That's not really on the cards for arbitrary non-trivial aggregate functions. Yes, it can be done for simple ones, and if you want to use that as a basis for adding optimizations that's fine. But a solution that ONLY works in simple cases isn't sufficient, IMO. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for psql History Display on MacOSX
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 now) history entry. I, too, had more-productive uses for this time, but morbid curiosity prevailed. It was the latter: I was testing a one-command history file. Under libedit-28, unpatched psql writes ^A for newlines in the oldest command and \012 for newlines in subsequent commands. Patched psql writes \012 for newlines in the oldest command and ^A for newlines in subsequent commands. (Surely a comment is in order if that's intentional. Wasn't the point to discontinue making the oldest command a special case?) Un-frickin-believable. Comparing the sources for history_get() at http://www.opensource.apple.com/source/libedit/libedit-28/src/readline.c vs http://www.opensource.apple.com/source/libedit/libedit-39/src/readline.c shows that -39 counts entries from history_base, as expected, but -28 counts them from zero (even though it exports history_base as one). So that's why the patched loop is misbehaving for you and not for me. 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. We could start the loop at zero unconditionally, but in a situation where libreadline had increased history_base much beyond one, that would be a bit wasteful. In any case, it now appears that we'd better test on more than just the oldest and newest libedits :-(. My confidence in the competence of libedit's authors has fallen another notch. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)
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 neglected to remove some abort logic that was previously only justified by the lack of opportunistic memcmp() == 0 comparisons in all instances, rather than just with abbreviated keys. -- Peter Geoghegan From c60b07dda3e81e1fc9a2e95c386faf1fccfe8f04 Mon Sep 17 00:00:00 2001 From: Peter Geoghegan p...@heroku.com Date: Sat, 6 Sep 2014 14:56:37 -0700 Subject: [PATCH 8/8] Remove obsolete abort logic Remove some abort logic that was previously only justified by the lack of opportunistic memcmp() == 0 comparisons in all instances, rather than just with abbreviated keys. That justification no longer stands. --- src/backend/utils/adt/varlena.c | 9 - 1 file changed, 9 deletions(-) diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c index 6aa3eaa..db4eae1 100644 --- a/src/backend/utils/adt/varlena.c +++ b/src/backend/utils/adt/varlena.c @@ -2295,15 +2295,6 @@ bttext_abbrev_abort(int memtupcount, double rowhint, SortSupport ssup) norm_key_card = key_distinct / (double) memtupcount; /* - * If this is a very low cardinality set generally, that is reason enough - * to favor our strategy, since many comparisons can be resolved with - * inexpensive memcmp() tie-breakers, even though abbreviated keys are of - * marginal utility. - */ - if (norm_key_card 0.05) - return false; - - /* * Abort abbreviation strategy. * * The worst case, where all abbreviated keys are identical while all -- 1.9.1 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb format is pessimal for toast compression
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 input data would make it fail again, even with lengths. We had a bit of discussion about JSONB compression at PDXPUG Day this morning. Josh polled the room, and about half though we should apply the patch for better compression, while the other half seemed to want faster access operations. (Some folks no doubt voted for both.) But in the ensuing discussion, I started to think that maybe we should leave it as it is, for two reasons: 1. There has been a fair amount of discussion about ways to better deal with this in future releases, such as hints to TOAST about how to compress, or the application of different compression algorithms (or pluggable compression). I’m assuming that leaving it as-is does not remove those possibilities. 2. The major advantage of JSONB is fast access operations. If those are not as important for a given use case as storage space, there’s still the JSON type, which *does* compress reasonably well. IOW, We already have a JSON alternative the compresses well. So why make the same (or similar) trade-offs with JSONB? Just my $0.02. I would like to see some consensus on this, soon, though, as I am eager to get 9.4 and JSONB, regardless of the outcome! Best, David signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [HACKERS] Stating the significance of Lehman Yao in the nbtree README
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 - perhaps I should go into more detail. I've gone into more detail on what a high key is, and how we arguably do not follow Lehman Yao to the letter because we still have read locks. LY's assumption of atomic page reads/writes, and cursory handling of deletion kind of make it inevitable that read locks are used, which I now imply. So nbtree isn't a substandard LY implementation - it's a realistic one, which only needs to hold a single read lock at a time when servicing index scans (or when finding a place for insertion). I guess Lehman Yao preferred to put forward the claim no read locks rather than only one read lock at a time on internal pages, even for insertion because there might be some uses of their algorithm where that is actually realistic. It is really a sympathetic way of spinning things to say that *no* read locks are used, though. -- Peter Geoghegan diff --git a/src/backend/access/nbtree/README b/src/backend/access/nbtree/README new file mode 100644 index 4820f76..8285050 *** a/src/backend/access/nbtree/README --- b/src/backend/access/nbtree/README *** use a simplified version of the deletion *** 11,16 --- 11,50 Shasha (V. Lanin and D. Shasha, A Symmetric Concurrent B-Tree Algorithm, Proceedings of 1986 Fall Joint Computer Conference, pp 380-389). + Lehman and Yao don't require read locks, but assume that in-memory + copies of tree pages are unshared. Postgres shares in-memory buffers + among backends. As a result, we do page-level read locking on btree + pages in order to guarantee that no record is modified while we are + examining it. This reduces concurrency but guarantees correct + behavior. An advantage is that when trading in a read lock for a + write lock, we need not re-read the page after getting the write lock. + Since we're also holding a pin on the shared buffer containing the + page, we know that buffer still contains the page and is up-to-date. + + Although it could be argued that Lehman and Yao isn't followed to the + letter because single pages are read locked as the tree is descended, + this is at least necessary to support deletion, a common requirement + which LY hardly acknowledge. Read locks also ensure that B-tree + pages are self-consistent (LY appear to assume atomic page reads and + writes). Even with these read locks, following LY obviates the need + of earlier schemes to hold multiple locks concurrently when descending + the tree as part of servicing index scans (pessimistic lock coupling). + The addition of right-links at all levels, as well as the addition of + a page high key allows detection and dynamic recovery from + concurrent page splits (that is, splits between unlocking an internal + page, and subsequently locking its child page during a descent). When + a page is first locked (at every level of a descent servicing an index + scan), we consider the need to move right: if the scankey value is + less than (or sometimes less than or equal to) the page's existing + highkey value, a value which serves as an upper bound for values on + the page generally, then it must be necessary to move the scan to the + right-hand page on the same level. It's even possible that the scan + needs to move right more than once. Once the other session's + concurrent page split finishes, a downlink will be inserted into the + parent, and so assuming there are no further page splits, future index + scans using the same scankey value will not need to move right. LY + Trees are sometimes referred to as B-Link trees in the literature. + The Lehman and Yao Algorithm and Insertions --- *** to be inserted has a choice whether or n *** 42,57 key could go on either page. (Currently, we try to find a page where there is room for the new key without a split.) - Lehman and Yao don't require read locks, but assume that in-memory - copies of tree pages are unshared. Postgres shares in-memory buffers - among backends. As a result, we do page-level read locking on btree - pages in order to guarantee that no record is modified while we are - examining it. This reduces concurrency but guarantees correct - behavior. An advantage is that when trading in a read lock for a - write lock, we need not re-read the page after getting the write lock. - Since we're also holding a pin on the shared buffer containing the - page, we know that buffer still contains the page and is up-to-date. - We support the notion of an ordered scan of an index as well as insertions, deletions, and simple lookups. A scan in the forward direction is no problem, we just use the right-sibling pointers that ---
Re: [HACKERS] Patch for psql History Display on MacOSX
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
misbehaviors as the next_history() approach. While we could possibly
work around all those bugs, it's true in all libedit versions that
history_get(N) is O(N) because it iterates down the history list. So
looping over the whole history list with it is O(N^2) in the number of
history entries, which could well get painful with long history lists.
What seems better is to stick with the history_set_pos/next_history
approach, but adapt it to use previous_history when required. This is
O(N) overall in both libreadline and libedit. I therefore propose the
attached patch.
Experimenting with this, it seems to work as expected in Apple's
libedit-13 and up (corresponding to OS X Snow Leopard and newer). The
fact that it works in pre-Mavericks releases is a bit accidental, because
history_set_pos() is in fact partially broken in those releases, per
comments in the patch. And it doesn't work very well in libedit-5.1 (OS X
Tiger) because history_set_pos() is seemingly *completely* broken in that
release: it never succeeds, and we end up iterating over a subset of the
history list that does not seem to have any rhyme or reason to it.
However I don't think that this patch makes things any worse than they
were before with that release.
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 anyone wants to try it on other platforms,
feel free.
[ wanders away wondering how it is that libedit has any following
whatsoever ... ]
regards, tom lane
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a66093a..39b5777 100644
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** exec_command(const char *cmd,
*** 1088,1107
char *fname = psql_scan_slash_option(scan_state,
OT_NORMAL, NULL, true);
- #if defined(WIN32) !defined(__CYGWIN__)
-
- /*
- * XXX This does not work for all terminal environments or for output
- * containing non-ASCII characters; see comments in simple_prompt().
- */
- #define DEVTTY con
- #else
- #define DEVTTY /dev/tty
- #endif
-
expand_tilde(fname);
! /* This scrolls off the screen when using /dev/tty */
! success = saveHistory(fname ? fname : DEVTTY, -1, false, false);
if (success !pset.quiet fname)
printf(_(Wrote history to file \%s\.\n), fname);
if (!fname)
--- 1088,1095
char *fname = psql_scan_slash_option(scan_state,
OT_NORMAL, NULL, true);
expand_tilde(fname);
! success = printHistory(fname, pset.popt.topt.pager);
if (success !pset.quiet fname)
printf(_(Wrote history to file \%s\.\n), fname);
if (!fname)
diff --git a/src/bin/psql/input.c b/src/bin/psql/input.c
index aa32a3f..136767f 100644
*** a/src/bin/psql/input.c
--- b/src/bin/psql/input.c
***
*** 11,16
--- 11,17
#include unistd.h
#endif
#include fcntl.h
+ #include limits.h
#include input.h
#include settings.h
*** gets_fromFile(FILE *source)
*** 222,244
#ifdef USE_READLINE
/*
* Convert newlines to NL_IN_HISTORY for safe saving in readline history file
*/
static void
encode_history(void)
{
! HIST_ENTRY *cur_hist;
! char *cur_ptr;
!
! history_set_pos(0);
! for (cur_hist = current_history(); cur_hist; cur_hist = next_history())
{
/* some platforms declare HIST_ENTRY.line as const char * */
for (cur_ptr = (char *) cur_hist-line; *cur_ptr; cur_ptr++)
if (*cur_ptr == '\n')
*cur_ptr = NL_IN_HISTORY;
}
}
/*
--- 223,285
#ifdef USE_READLINE
+
+ /*
+ * Macros to iterate over each element of the history list
+ *
+ * You would think this would be simple enough, but in its inimitable fashion
+ * libedit has managed to break it: in all but very old libedit releases it is
+ * necessary to iterate using previous_history(), whereas in libreadline the
+ * call to use is next_history(). To detect what to do, we make a trial call
+ * of previous_history(): if it fails, then either next_history() is what to
+ * use, or there's zero or one history entry so that it doesn't matter.
+ *
+ * In case that wasn't disgusting enough: the code below is not as obvious as
+ * it might appear. In some libedit releases history_set_pos(0) fails until
+ * at least one add_history() call has been done. This is not an issue for
+ * printHistory() or encode_history(), which cannot be invoked before that has
+ * happened. In decode_history(), that's not so, and what actually happens is
+ * that
Re: [HACKERS] A mechanism securing web applications in DBMS
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 to them, they wouldn't use it. I'm surprised to hear this and a suggestion to used stored procedures in the same email- SPs are generally considered 'foreign' to the web developers that I've talked to. :) That said, I'll grant that there are generally two camps: those who expect a database to only have BerkleyDB level key/value capabilities, and those who know what they're doing and what relational databases and SQL are all about. The latter (and clear minority) group will take advantage of these capabilites, certainly, regardless of how they are expressed and are likely already comfortable using stored procedures and database-level roles. 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. The goal of this mechanism is to add another layer of protection inside DBMS so that even if the application server is compromised the users' data is under protection*. This requires DBMS to be able to authenticate application-level users (know which application-level user it is communicating with). That it, we need to move the authentication logic of application-level users into DBMS. For this purpose, using store procedures (or something similar) is a must. I think even if a security mechanism is designed to be easy to use, it will still require some expertise. * this mechanism can't help if the attackers control the app server completely and the users are not aware of that and keep using the app. In that case the attackers will be able to collect all the credentials of the users who log in while they are in charge. If a temp table is being used then dynamic SQL may be required and therefore a plpgsql function will be involved to handle looking up the current user, as you won't be using PG roles. This is why I'd like to have global temp table in PG. With that we can probably get around of dynamic SQL. (3) CREATE AUTHENTICATION FUNCTION In our mechanism, we ask web application developers provide an authentication function which normally takes user id and password as inputs and returns a row containing all the identifiers (attributes) of the corresponding application-level user. Let us call the place storing the current application-level user's identifiers as identifier store. I would *strongly* advocate *against* passing the password to the database in any (non-hashed) form. You are much better off using a one-way hash as early as possible in the stack (ideally, in whatever system initially receives the password on the server side) and then comparing that one-way hash. Of course, passwords in general are not considered secure and one-time passwords, hardware tokens, or PIV / HSPD12 / CAC cards with client-side certificates. You are absolutely right. I should've explained it better. I just wanted to show how authentication works and skipped all the hashing part. Thanks, Zhaomo On Fri, Sep 5, 2014 at 5:52 PM, Stephen Frost sfr...@snowman.net wrote: 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 their apps if they wa. That's certainly fair. - Try to make our mechanism as simple as possible. Web application developers have all kinds of backgrounds. If the security mechanism is too alien to them, they wouldn't use it. I'm surprised to hear this and a suggestion to used stored procedures in the same email- SPs are generally considered 'foreign' to the web developers that I've talked to. :) That said, I'll grant that there are generally two camps: those who expect a database to only have BerkleyDB level key/value capabilities, and those who know what they're doing and what relational databases and SQL are all about. The latter (and clear minority) group will take advantage of these capabilites, certainly, regardless of how they are expressed and are likely already comfortable using stored procedures and database-level roles. (2) Why we need to cache application-level users' identifiers We want to differentiate application-level users in DBMS, but not by creating a DB user (or role in PG's terminology ) for every application-level user, otherwise there will be all sorts of problems when the number of application-level users is greater than a threshold (e.g. catalog, as you mentioned). While I agree that this can be an issue when things scale up, you *can* address it by sharding the database based on user. Even so though, I
Re: [HACKERS] Improving PL/PgSQL (was: Re: plpgsql defensive mode)
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 two languages on a per-function basis, when both look exactly the same but have very different semantics would be a nightmare. It is maximum what is possible use a different language instead Sigh. OK, let's try and forget the cardinality assertions we've been talking about in the other thread(s). I seem to recall there being a generally welcoming atmosphere in the discussion about adding a set of pragmas (or options/whatever) to make some of PL/PgSQL's flaws go away, in a non-backwards compatible way. From the list here: https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014) do you think at least some of those would be reasonable candidates for these pragmas? Do you see others ones that are missing from this list? Please also keep discussion about ASSERT in the thread for that, and the suggestion under Single-row operations out of this. SELECT .. INTO vs. TOO_MANY_ROWS +1 .. possible every where correction +1 .. in defensive mode due compatibility issues. Variable assignments +1 .. only in defensive mode Single-row operations +1 .. only in defensive mode without special syntax EXECUTE and FOUND -1 .. it is emulation of PL/SQL behave.. so introduction can do too high unhappy surprise if somebody will migrate to Oracle -- the syntax is too similar OUT parameters -1 .. to proposal .. It is in contradiction with current feature. Next it is nonsense. INTO clause should to contains only plpgsql variables - in 9.x Postgres there is not possible issue. postgres=# create table x(a int, b int); CREATE TABLE postgres=# insert into x values(10,20); INSERT 0 1 postgres=# create or replace function foo(out a int, out b int) postgres-# returns record as $$ postgres$# begin postgres$# select x.a, x.b from x into a, b; postgres$# return; postgres$# end; postgres$# $$ language plpgsql; CREATE FUNCTION postgres=# select * from foo(); a | b + 10 | 20 (1 row) you can see, there is not any collision Assertions -1 to proposed syntax - I wrote about my reasons in other thread. Regards Pavel .marko
Re: [HACKERS] Adding a nullable DOMAIN column w/ CHECK
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: http://www.postgresql.org/message-id/CACfv+p+8dToaR7h06_M_YMjpV5Na-CQq7kN=kgjq_k84h7u...@mail.gmail.com You can download the message via view raw in the web archives, open it as an mbox file, and reply there. The old thread was fuzzy concerning how the system works today. Adding a domain-type column rewrites the table unless the domain has no constraints. Simultaneously adding an all-NULL column and a CHECK constraint to a table scans the table, even if the CHECK constraint references only the new column. The patch is obviously a load of horse crap, but does anyone have any objections to the general approach of making this pattern faster? +1 in general. To do this optimization we do have to assume that CHECKs in DOMAINs are at least STABLE, but I don't see that as a problem; those should be IMMUTABLE anyway, I think. The system has such assumptions already. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)
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 SIZEOF_DATUM != 8
platforms.
--
Peter Geoghegan
From 180ad839d8f049d83a89b42a1d85c7c99a7929f0 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan p...@heroku.com
Date: Sat, 6 Sep 2014 21:39:16 -0700
Subject: [PATCH 9/9] On SIZEOF_DATUM != 4 platforms, call hash_uint32()
directly
In passing, remove some dead code within bttext_abbrev_abort().
---
src/backend/utils/adt/varlena.c | 28 ++--
1 file changed, 14 insertions(+), 14 deletions(-)
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index db4eae1..23944f2 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -2068,9 +2068,7 @@ bttext_abbrev_convert(Datum original, SortSupport ssup)
char *pres;
int len;
Sizebsize;
- uint32lohalf,
- hihalf,
- hash;
+ uint32hash;
/*
* Abbreviated key representation is a pass-by-value Datum that is treated
@@ -2143,9 +2141,18 @@ retry:
memcpy(pres, tss-buf2, Min(sizeof(Datum), bsize));
/* Hash abbreviated key */
- lohalf = (uint32) res;
- hihalf = (uint32) (res 32);
- hash = hash_uint32(lohalf ^ hihalf);
+#if SIZEOF_DATUM == 8
+ {
+ uint32lohalf,
+ hihalf;
+
+ lohalf = (uint32) res;
+ hihalf = (uint32) (res 32);
+ hash = hash_uint32(lohalf ^ hihalf);
+ }
+#else /* SIZEOF_DATUM != 8 */
+ hash = hash_uint32((uint32) res);
+#endif
addHyperLogLog(tss-abbr_card, hash);
@@ -2168,8 +2175,7 @@ bttext_abbrev_abort(int memtupcount, double rowhint, SortSupport ssup)
{
TextSortSupport *tss = (TextSortSupport *) ssup-ssup_extra;
doubleabbrev_distinct,
- key_distinct,
- norm_key_card;
+ key_distinct;
Assert(ssup-abbrev_state == ABBREVIATED_KEYS_YES);
@@ -2289,12 +2295,6 @@ bttext_abbrev_abort(int memtupcount, double rowhint, SortSupport ssup)
return false;
/*
- * Normalized cardinality is proportion of distinct original, authoritative
- * keys
- */
- norm_key_card = key_distinct / (double) memtupcount;
-
- /*
* Abort abbreviation strategy.
*
* The worst case, where all abbreviated keys are identical while all
--
1.9.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for psql History Display on MacOSX
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 anyone wants to try it on other platforms, feel free. I ran libedit-history-fixes-v3.patch through my previous libedit-28 test. Now, patched psql writes ^A for newlines in any command. Here's the new matrix of behaviors when recalling history: master using master-written history: oldest command: ok rest: ok v3-patched using master-written history: oldest command: ok rest: ok master using v3-patched-written history oldest command: ok rest: each broken if it contained a newline v3-patched using v3-patched-written history oldest command: ok rest: ok That's probably the same result you saw. How does it compare to the compatibility effects for other libedit versions you tested? [ wanders away wondering how it is that libedit has any following whatsoever ... ] Quite so. Thanks, nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
