Re: [HACKERS] \set AUTOROLLBACK ON

2017-06-28 Thread Joel Jacobson
On Mon, Jun 26, 2017 at 9:35 PM, David G. Johnston wrote: > We already have SET TRANSACTION READ ONLY. But in my use-case I am OK with the query doing write operations, since sometimes you need to test something in prod (that cannot be tested easily locally) but you

Re: [HACKERS] Optional message to user when terminating/cancelling backend

2017-06-26 Thread Joel Jacobson
+1 On Tue, Jun 20, 2017 at 8:54 PM, Alvaro Herrera wrote: > Unless you have a lot of users running psql manually, I don't see how > this is actually very useful or actionable. What would the user do with > the information? Hopefully your users already trust that you'd

[HACKERS] \set AUTOROLLBACK ON

2017-06-26 Thread Joel Jacobson
Hi hackers, A colleague of mine wondered if there is a way to always run everything you type into psql in a db txn and automatically rollback it as soon as it finish. I couldn't think of any way to do so, but thought it would be a nice feature and probably quite easy to add to psql, so I thought

Re: [HACKERS] case_preservation_and_insensitivity = on

2017-02-24 Thread Joel Jacobson
On Thu, Feb 23, 2017 at 8:04 AM, Robert Haas wrote: > > It doesn't sound like a good solution to me, because there can be SQL > code inside stored procedures that clients never see. In our code base, we use CamelCase in all PL/pgSQL functions, both for columns and

Re: [HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

2017-02-22 Thread Joel Jacobson
On Wed, Feb 22, 2017 at 2:18 PM, Tom Lane wrote: > I think this is really *not* a good idea. The entire permissions model > is built around granting permissions to roles, by other roles. My bad. I shouldn't have proposed the idea on how to achieve/implement the idea. I

Re: [HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

2017-02-22 Thread Joel Jacobson
On Wed, Feb 22, 2017 at 9:07 AM, Pavel Stehule wrote: > Usage of X functions can be locked in schema. I think that's also a good idea. Both are useful I think. They solve two different use-cases. If there are multiple callers of a private function within a schema, it

[HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

2017-02-21 Thread Joel Jacobson
Hi Hackers, Currently, it's only possible to grant/revoke execute on functions to roles. I think it would be useful in many situations, both for documentation purposes, but also for increased security, to in a precise way control what other function(s) are allowed to execute a specific function.

Re: [HACKERS] case_preservation_and_insensitivity = on

2017-02-20 Thread Joel Jacobson
On Mon, Feb 20, 2017 at 1:45 AM, Tom Lane wrote: > The versions of autocommit that have actually stood the test of time were > implemented on the client side (in psql and JDBC, and I think ODBC as > well), where the scope of affected code was lots smaller. I wonder > whether

Re: [HACKERS] case_preservation_and_insensitivity = on

2017-02-20 Thread Joel Jacobson
On Mon, Feb 20, 2017 at 2:40 AM, Jim Nasby wrote: > Even if the project decided that "Users" and users is stupid and that we > should deprecate it, I think the odds of also deciding to tell existing > users to re-write their apps are zero. But if the feature can't be

Re: [HACKERS] case_preservation_and_insensitivity = on

2017-02-19 Thread Joel Jacobson
On Sun, Feb 19, 2017 at 5:58 PM, Robert Haas wrote: >> When case preservation by default is on, then simply enforce >> UNIQUE(LOWER(object_name)), to prevent ambiguity. > > That (1) breaks backward compatibility, because people might have > objects with names identical

Re: [HACKERS] case_preservation_and_insensitivity = on

2017-02-16 Thread Joel Jacobson
On Thu, Feb 16, 2017 at 6:53 AM, Tom Lane wrote: > Have you read any of our innumerable previous discussions about this? No, sorry, didn't see them, thanks for sharing the links. > The short answer is that nobody can see a way to modify the identifier > case-folding rules

[HACKERS] case_preservation_and_insensitivity = on

2017-02-15 Thread Joel Jacobson
Hi hackers, "A system that is not case-preserving is necessarily case-insensitive, but it is possible and common for a system to be case-insensitive, yet case-preserving" [1] Imagine if you could turn on a GUC that would turn PostgreSQL into such a system, where the case would be preserved by

Re: [HACKERS] Idea on how to simplify comparing two sets

2017-02-07 Thread Joel Jacobson
On Tue, Feb 7, 2017 at 6:28 PM, David Fetter wrote: > This could be shortened further to the following if we ever implement > DISTINCT for window functions, which might involve implementing > DISTINCT via hashing more generally, which means hashable > types...whee! > > SELECT

Re: [HACKERS] Idea on how to simplify comparing two sets

2017-02-07 Thread Joel Jacobson
On Tue, Feb 7, 2017 at 4:58 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Joel Jacobson <j...@trustly.com> writes: >> Currently there is no simple way to check if two sets are equal. > > Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2 > and SELECT set2 EXC

Re: [HACKERS] Idea on how to simplify comparing two sets

2017-02-07 Thread Joel Jacobson
SELECT TRUE > WHERE FALSE > IS NOT DISTINCT FROM > (SELECT TRUE) > > On Tue, Feb 7, 2017 at 4:13 PM, Joel Jacobson <j...@trustly.com> wrote: >> >> Hi hackers, >> >> Currently there is no simple way to check if two sets are equal. >> >> Looks like no R

[HACKERS] Idea on how to simplify comparing two sets

2017-02-07 Thread Joel Jacobson
Hi hackers, Currently there is no simple way to check if two sets are equal. Looks like no RDBMS in the world has a simple command for it. You have to do something like: WITH T1 AS (SELECT * FROM Foo WHERE FooID BETWEEN 1 AND 1), T2 AS (SELECT * FROM Bar WHERE BarID BETWEEN 1 AND 1)

Re: [HACKERS] RustgreSQL

2017-01-09 Thread Joel Jacobson
On Mon, Jan 9, 2017 at 6:03 PM, Craig Ringer wrote: > Immutable functions can and do use functionality from all over the > server. They just don't depend on user-visible mutable _state_ > elsewhere in the server. OK, fair point, but at least the functionality *could* be

Re: [HACKERS] RustgreSQL

2017-01-09 Thread Joel Jacobson
On Mon, Jan 9, 2017 at 3:22 PM, Jim Nasby wrote: > I do wonder if there are parts of the codebase that would be much better > suited to a language other than C, and could reasonably be ported. > Especially if that could be done in such a way that the net result is still

Re: [HACKERS] RustgreSQL

2017-01-08 Thread Joel Jacobson
Thank you Craig for explaining the current C state of the project, very interesting to learn about. On Sun, Jan 8, 2017 at 4:19 AM, Craig Ringer wrote: > If a low pain seamless conversation/adaptation like that were possible I'd > have to wonder what Rust could

[HACKERS] RustgreSQL

2017-01-08 Thread Joel Jacobson
Hi all, Is anyone working on porting PostgreSQL to Rust? Corrode looks a bit limited for the task, but maybe it can be a start. It doesn't support goto or switch, but maybe the gotos patterns are not too complicated. My motivation is primarily I don't want to learn all the over-complicated

Re: [HACKERS] merging some features from plpgsql2 project

2017-01-08 Thread Joel Jacobson
On Sat, Jan 7, 2017 at 8:56 PM, Pavel Stehule wrote: > > Jim, Marko, Joel - is there a place, features where we can find a partial > agreement? If it is, then we can move our view there. I have decided I definitively want a new language, and I'm willing to pay for it.

Re: [HACKERS] pg_stat_activity.waiting_start

2017-01-07 Thread Joel Jacobson
On Sat, Jan 7, 2017 at 3:25 AM, Greg Stark wrote: > What users need to know is in aggregate how much of the time the > database is spending working on their queries is going into different > states. This is a separate feature idea, but I think it's really valuable as well. Maybe

Re: [HACKERS] merging some features from plpgsql2 project

2017-01-06 Thread Joel Jacobson
On Thu, Jan 5, 2017 at 7:03 AM, Robert Haas wrote: > > I think it would be a good idea to lock all the people who really care > about PL/pgsql in a room until they agree on what changes should be > made for the next version of the language. If they don't agree > quickly

Re: [HACKERS] pg_stat_activity.waiting_start

2017-01-05 Thread Joel Jacobson
On Thu, Jan 5, 2017 at 4:59 PM, Bruce Momjian wrote: > Agreed. No need in adding overhead for short-lived locks because the > milli-second values are going to be meaningless to users. I would be > happy if we could find some weasel value for non-heavyweight locks. To avoid a

Re: [HACKERS] pg_stat_activity.waiting_start

2016-12-25 Thread Joel Jacobson
On Sun, Dec 25, 2016 at 8:01 PM, Andres Freund <and...@anarazel.de> wrote: > On December 25, 2016 1:21:43 AM GMT+01:00, Joel Jacobson <j...@trustly.com> > wrote: > >>Is it really a typical real-life scenario that processes can be >>waiting extremely often fo

Re: [HACKERS] pg_stat_activity.waiting_start

2016-12-24 Thread Joel Jacobson
> This is not an easy problem. See our most recent discussion at > https://www.postgresql.org/message-id/flat/31856.1400021891%40sss.pgh.pa.us Thanks for the small test program. I tested it on my MacBook Pro and gettimeofday() was way faster than time(). The clock_gettime() used by the patch

Re: [HACKERS] pg_stat_activity.waiting_start

2016-12-23 Thread Joel Jacobson
Attached is a patch implementing the seconds-resolution wait_start, but presented as a timestamptz to the user, just like the other *_start fields: commit c001e5c537e36d2683a7e55c7c8bfcc154de4c9d Author: Joel Jacobson <j...@trustly.com> Date: Sat Dec 24 13:20:09 2016 +0700 A

Re: [HACKERS] pg_stat_activity.waiting_start

2016-12-23 Thread Joel Jacobson
On Sat, Dec 24, 2016 at 9:56 AM, Joel Jacobson <j...@trustly.com> wrote: >> The difficulty with that is it'd require a gettimeofday() call for >> every wait start. Even on platforms where those are relatively cheap, I just realized how this can be optimized. We only need

Re: [HACKERS] pg_stat_activity.waiting_start

2016-12-23 Thread Joel Jacobson
On Sat, Dec 24, 2016 at 9:00 AM, Tom Lane wrote: >> I would like to propose adding a fourth such column, "waiting_start", >> which would tell how long time a backend has been waiting. > > The difficulty with that is it'd require a gettimeofday() call for > every wait start.

Re: [HACKERS] pg_stat_activity.waiting_start

2016-12-23 Thread Joel Jacobson
Actually, "wait_start" is a better name to match the others ("wait_event_type" and "wait_event"). On Sat, Dec 24, 2016 at 8:20 AM, Joel Jacobson <j...@trustly.com> wrote: > Hi hackers, > > We already have xact_start, query_start and backend_star

[HACKERS] pg_stat_activity.waiting_start

2016-12-23 Thread Joel Jacobson
case would be e.g. monitoring tools when you want to know how long time queries are waiting. -- Joel Jacobson -- 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] SET NOT NULL [NOT VALID / CONCURRENTLY]?

2016-12-21 Thread Joel Jacobson
on the column 3. UPDATE pg_catalog.pg_attribute SET attnotnull = TRUE WHERE attrelid = %L::oid AND attname = %L Pragmatically, would this be a safe approach? On Wed, Dec 21, 2016 at 6:53 PM, Joel Jacobson <j...@trustly.com> wrote: > If you are fully confident you have no NULL valu

Re: [HACKERS] SET NOT NULL [NOT VALID / CONCURRENTLY]?

2016-12-21 Thread Joel Jacobson
straint > for NOT NULL. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services -- Joel Jacobson Mobile: +46703603801 Trustly.com | Newsroom | LinkedIn | Twitter -- 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] SET NOT NULL [NOT VALID / CONCURRENTLY]?

2016-12-21 Thread Joel Jacobson
great lot to us. I don't know if this is the right forum trying to find someone/some company to sign up for the task, please let me know if I should mail to some other list. Thanks. Joel Jacobson Trustly -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make chan

[HACKERS] SET NOT NULL [NOT VALID / CONCURRENTLY]?

2016-12-21 Thread Joel Jacobson
table took 28 seconds locally on my machine. Is anyone working on fixing this for PostgreSQL 10? Joel Jacobson Trustly -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Typo in doc/src/sgml/catalogs.sgml

2016-12-11 Thread Joel Jacobson
amhandler - oid + regproc pg_proc.oid OID of a handler function that is responsible for supplying information Best regards, Joel Jacobson Trustly -- 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] autonomous transactions

2016-09-02 Thread Joel Jacobson
On Thu, Sep 1, 2016 at 8:38 PM, Andres Freund wrote: > > I'm not convinced this makes much sense. All FDWs, dblink etc. already > allow you do stuff outside of a transaction. You as a DBA can prevent FDWs from being used and dblink is an extension that you don't have to

Re: [HACKERS] autonomous transactions

2016-09-01 Thread Joel Jacobson
On Wed, Aug 31, 2016 at 6:22 PM, Simon Riggs <si...@2ndquadrant.com> wrote: > On 31 August 2016 at 14:09, Joel Jacobson <j...@trustly.com> wrote: >> My idea on how to deal with this would be to mark the function to be >> "AUTONOMOUS" similar to how a funct

Re: [HACKERS] autonomous transactions

2016-09-01 Thread Joel Jacobson
On Thu, Sep 1, 2016 at 12:12 AM, Vik Fearing wrote: > Part of what people want this for is to audit what people *try* to do. > We can already audit what they've actually done. > > With your solution, we still wouldn't know when an unauthorized attempt > to do something

Re: [HACKERS] autonomous transactions

2016-08-31 Thread Joel Jacobson
On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova <jaime.casan...@2ndquadrant.com> wrote: > > On 30 August 2016 at 23:10, Joel Jacobson <j...@trustly.com> wrote: > > > > There should be a way to within the session and/or txn permanently > > block autonomous transa

Re: [HACKERS] autonomous transactions

2016-08-30 Thread Joel Jacobson
I would love to see autonomous transactions in core. I just have one major concern, but thankfully it's easily addressed. There should be a way to within the session and/or txn permanently block autonomous transactions. This is important if you as a caller function want to be sure none of the

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

2016-03-12 Thread Joel Jacobson
On Sun, Mar 13, 2016 at 12:40 AM, Tom Lane wrote: > In short: we've already been over this territory, at length, > and I am not excited by people trying to bikeshed it again > after the fact, especially when no new arguments are being > presented. Can we call the discussion

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

2016-03-11 Thread Joel Jacobson
On Sat, Mar 12, 2016 at 6:31 AM, Jim Nasby wrote: > On 3/10/16 8:36 PM, Robert Haas wrote: >> >> 1. We make it true only for heavyweight lock waits, and false for >> other kinds of waits. That's pretty strange. >> 2. We make it true for all kinds of waits that we now

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

2016-03-11 Thread Joel Jacobson
On Sat, Mar 12, 2016 at 5:09 AM, Pavel Stehule wrote: >> What we need is more input on proposed changes from other companies >> who are also heavy users of PL/pgSQL. >> >> Only then can we move forward. It's like Robert is saying, there is a >> risk for bikeshedding here,

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

2016-03-11 Thread Joel Jacobson
On Sat, Mar 12, 2016 at 4:48 AM, Joel Jacobson <j...@trustly.com> wrote: > neither you nor me have nothing to add. Correction: neither you nor me have anything to add. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscript

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

2016-03-11 Thread Joel Jacobson
On Sat, Mar 12, 2016 at 4:41 AM, Pavel Stehule wrote: > I afraid so you try to look on your use case as global/generic issue. The > PL/SQL, ADA. PL/pgSQL are verbose languages, and too shortcuts does the > languages dirty. In this point we have different opinion. > > I

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

2016-03-11 Thread Joel Jacobson
On Sat, Mar 12, 2016 at 4:08 AM, Robert Haas wrote: > > I don't think my experience in this area is as deep as you seem to > think. I can tell you that most of the requests EnterpriseDB gets for > PL/pgsql enhancements involve wanting it to be more like Oracle's > PL/SQL,

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

2016-03-11 Thread Joel Jacobson
On Fri, Mar 11, 2016 at 11:14 AM, Robert Haas wrote: > I'm not direly opposed to most of what's on that page, > but I'm not excited about most of it, either. May I ask, what improvements of PL/pgSQL would you personally be most excited about, if you or someone else would

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

2016-03-10 Thread Joel Jacobson
On Fri, Mar 11, 2016 at 9:36 AM, Robert Haas wrote: > Well, this was discussed. If we keep the Boolean "waiting" column, then > either: Oh, sorry for missing out on that discussion. > 1. We make it true only for heavyweight lock waits, and false for > other kinds of

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

2016-03-10 Thread Joel Jacobson
: Joel Jacobson <j...@trustly.com> Date: Fri Mar 11 08:19:52 2016 +0700 Make Have_Queries_Waiting() work with both <9.6 and >=9.6. Apparently pg_stat_activity.waiting was removed by this commit: commit 53be0b1add7064ca5db3cd884302dfc3268d884e Author: Rob

Re: [HACKERS] Disabling an index temporarily

2016-03-09 Thread Joel Jacobson
On Mon, Dec 14, 2015 at 10:27 AM, Tom Lane wrote: > Corey Huinker writes: >> So, I'd propose we following syntax: >> ALTER INDEX foo SET DISABLED >> -- does the SET indisvalid = false shown earlier. > > This is exactly *not* what Tatsuo-san was after,

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-03-08 Thread Joel Jacobson
On Wed, Mar 9, 2016 at 1:25 AM, Shulgin, Oleksandr wrote: > Thank you for spending your time to run these :-) n/p, it took like 30 seconds :-) > I don't want to be asking for too much here, but is there a chance you could > try the effects of the proposed patch on

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-03-08 Thread Joel Jacobson
ored statstarget > one still needs superuser to join the pg_statistic table directly). Also, > on the scale of ~30k attribute statistics records, the queries take only a > few seconds to finish. > > Cheers! > -- > Alex > -- Joel Jacobson Mobile: +46703603801 Trustly.com | Newsroom | LinkedIn | Twitter -- 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] Releasing in September

2016-01-20 Thread Joel Jacobson
On Wed, Jan 20, 2016 at 5:29 PM, Andres Freund wrote: > I think one thing we should work on, is being absolutely religious about > requiring, say, 2 reviews for every nontrivial contribution. We > currently seem to have a significantly increased submission rate, and at > the

Re: [HACKERS] Proposal: "Causal reads" mode for load balancing reads without stale data

2016-01-07 Thread Joel Jacobson
sistency. This is an important model where process A, > after it has updated a data item, always accesses the updated value and will > never see an older value. This is a special case of the causal consistency > model." > > [3] > http://www.postgresql.org/message-id/flat/cao

Re: [HACKERS] Remaining 9.5 open items

2015-12-02 Thread Joel Jacobson
On Mon, Nov 30, 2015 at 8:43 PM, Tom Lane wrote: > * Finish multixact truncation rework > > We're not seriously going to push something this large into 9.5 at this > point, are we? I don't know all the details here, so my apologies if any of this is

Re: [HACKERS] Remaining 9.5 open items

2015-12-02 Thread Joel Jacobson
On Wed, Dec 2, 2015 at 12:19 PM, Andres Freund wrote: > The significant changes are in 9.5. Will multixact truncations be WAL logged in 9.5? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] Remaining 9.5 open items

2015-12-02 Thread Joel Jacobson
On Wed, Dec 2, 2015 at 12:36 PM, Andres Freund <and...@anarazel.de> wrote: > > On 2015-12-02 12:25:37 +0100, Joel Jacobson wrote: > > On Wed, Dec 2, 2015 at 12:19 PM, Andres Freund <and...@anarazel.de> wrote: > > > The significant changes are in 9.5. > >

Re: [HACKERS] Rework the way multixact truncations work

2015-09-29 Thread Joel Jacobson
On Tue, Sep 22, 2015 at 3:20 PM, Andres Freund wrote: > What I've tested is the following: > * continous burning of multis, both triggered via members and offsets > * a standby keeping up when the primary is old > * a standby keeping up when the primary is new > * basebackups

Re: [HACKERS] Autonomous Transaction is back

2015-07-28 Thread Joel Jacobson
On Tue, Jul 28, 2015 at 12:56 AM, Josh Berkus j...@agliodbs.com wrote: Ah, ok. The goal of the project is that the writer of X() *cannot* prevent Y() from writing its data (B1) and committing it. One of the primary use cases for ATX is audit triggers. If a function writer could override

Re: [HACKERS] Autonomous Transaction is back

2015-07-27 Thread Joel Jacobson
On Fri, Jul 24, 2015 at 9:39 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Jul 23, 2015 at 1:49 PM, Josh Berkus j...@agliodbs.com wrote: Batch Jobs: large data-manipulation tasks which need to be broken up into segments, with each segment committing separately. Example: updating 1

Re: [HACKERS] Autonomous Transaction is back

2015-07-27 Thread Joel Jacobson
On Mon, Jul 27, 2015 at 11:49 PM, Josh Berkus j...@agliodbs.com wrote: Ah, you're missing how commits in ATX are expected to work. Let me illustrate: X ( Data write A1 call Y( Start ATX Data write B1 Commit ATX ) Data write A2 Exception ) In

Re: [HACKERS] pg_stat_*_columns?

2015-07-06 Thread Joel Jacobson
On Mon, Jun 29, 2015 at 11:14 PM, Jim Nasby jim.na...@bluetreble.com wrote: What might be interesting is setting things up so the collector simply inserted into history tables every X seconds and then had a separate process to prune that data. The big problem with that is I see no way for

Re: [HACKERS] pg_stat_*_columns?

2015-06-20 Thread Joel Jacobson
On Tue, Jun 16, 2015 at 4:47 AM, Jim Nasby jim.na...@bluetreble.com wrote: Magnus idea was to first optimize the collector to make it less of a problem to collect more data. Sounds like a good thing to do, but maybe more data in it wouldn't be a problem as long as you don't read too often

Re: [HACKERS] pg_stat_*_columns?

2015-06-08 Thread Joel Jacobson
it less of a problem to collect more data. Sounds like a good thing to do, but maybe more data in it wouldn't be a problem as long as you don't read too often from it? On Mon 8 Jun 2015 at 18:48 Robert Haas robertmh...@gmail.com wrote: On Fri, Jun 5, 2015 at 7:51 AM, Joel Jacobson j...@trustly.com

[HACKERS] pg_stat_*_columns?

2015-06-05 Thread Joel Jacobson
Would others find it useful to see per column statistics about accesses to specific columns? Two possible use-cases: (maybe there are more?) 1. I think it would be helpful for DBAs to better understand their own system. Finding unused *tables* is today easy thanks to pg_stat_*_tables, but

Re: [HACKERS] pg_xlog - pg_xjournal?

2015-06-02 Thread Joel Jacobson
On Tue, Jun 2, 2015 at 6:35 AM, Michael Nolan htf...@gmail.com wrote: Why not take a simpler approach and create a zero length file in directories that should not be fiddled with by non-experts using a file name something like DO.NOT.DELETE.THESE.FILES? Then the smart sysadmin will say but I

[HACKERS] [PATCH] Fix documentation bug in how to calculate the quasi-unique pg_log session_id

2015-06-02 Thread Joel Jacobson
Fix documentation bug in how to calculate the quasi-unique pg_log session_id While the code is truncating the backend_start time, the query example in the documentation is rouding. Fix by doing the same thing in the documentation as in, i.e. truncating the backend_start. elog.c:

Re: [HACKERS] pg_xlog - pg_xjournal?

2015-06-01 Thread Joel Jacobson
On Mon, Jun 1, 2015 at 10:17 PM, Josh Berkus j...@agliodbs.com wrote: If we symlink pg_xlog, then it will still trip up anyone who does rm -rf *log*/* or deletes files directly from inside the directory, both of which I've seen. Deleting the directory itself is comparatively rare in my

Re: [HACKERS] pg_xlog - pg_xjournal?

2015-05-31 Thread Joel Jacobson
On Sun, May 31, 2015 at 7:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Hm. I think the impact on third-party backup tools would be rather bad, but there's a simple modification of the idea that might fix that: just always create pg_xlog as a symlink to pg_xjournal during initdb. Anybody who

[HACKERS] pg_xlog - pg_xjournal?

2015-05-31 Thread Joel Jacobson
While anyone who is familiar with postgres would never do something as stupid as to delete pg_xlog, according to Google, there appears to be a fair amount of end-users out there having made the irrevocable mistake of deleting the precious directory, a decision made on the assumption that since it

Re: [HACKERS] PL/pgSQL, RAISE and error context

2015-04-27 Thread Joel Jacobson
Stehule pavel.steh...@gmail.com: Hi 2015-04-24 19:16 GMT+02:00 Joel Jacobson j...@trustly.com: On Fri, Apr 24, 2015 at 6:07 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Example: context_messages = -warning, -error, +notice I prefer your first proposal

Re: [HACKERS] PL/pgSQL, RAISE and error context

2015-04-27 Thread Joel Jacobson
On Mon, Apr 27, 2015 at 6:14 PM, Marko Tiikkaja ma...@joh.to wrote: That sounds weird. log_min_messages are the messages sent to the log; client_min_messages are sent to the client. context_min_messages are not sent to a context, whatever that would mean. Good point. I think it can't be

Re: [HACKERS] PL/pgSQL, RAISE and error context

2015-04-25 Thread Joel Jacobson
+1 On Sat, Apr 25, 2015 at 10:23 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hi 2015-04-24 19:16 GMT+02:00 Joel Jacobson j...@trustly.com: On Fri, Apr 24, 2015 at 6:07 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Example: context_messages = -warning, -error, +notice I

Re: [HACKERS] PL/pgSQL, RAISE and error context

2015-04-24 Thread Joel Jacobson
Entering the discussion because this is a huge pain for me in my daily work as well. This is not a reply to any specific post in this thread, but my first message in the thread. I see a great value in providing both a GUC and a new RAISE syntax. The different benefits of the two are maybe

Re: [HACKERS] PL/pgSQL, RAISE and error context

2015-04-24 Thread Joel Jacobson
On Fri, Apr 24, 2015 at 1:16 PM, Robert Haas robertmh...@gmail.com wrote: This would allow doing something crazy as: suppress_context_messages = warning,error display_context_messages = notice This is a very flexible proposal, but it's a tremendous amount of machinery for what's really a

Re: [HACKERS] PL/pgSQL, RAISE and error context

2015-04-24 Thread Joel Jacobson
On Fri, Apr 24, 2015 at 6:07 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Example: context_messages = -warning, -error, +notice I prefer your first proposal - and there is a precedent for plpgsql - plpgsql_extra_checks It is clean for anybody. +-identifiers looks like horrible httpd

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] 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 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] PL/PgSQL: EXIT USING ROLLBACK

2014-09-05 Thread Joel Jacobson
On 3 sep 2014, at 16:20, Robert Haas robertmh...@gmail.com wrote: On Mon, Sep 1, 2014 at 5:08 AM, Joel Jacobson j...@trustly.com wrote: On Sat, Jul 26, 2014 at 8:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Basically my point is that this just seems like inventing another way to do what one can

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On Wed, Sep 3, 2014 at 11:19 PM, Hannu Krosing ha...@2ndquadrant.com wrote: SELECT[1] - select exactly one row, anything else raises error SELECT[0:1] - select zero or one rows, anything else raises error SELECT[1:] - select one or more rows plain SELECT is equivalent to SELECT[0:] same

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule pavel.steh...@gmail.com wrote: we have totally different opinion what is good Can you elaborate on that? Your ASSERT CHECK ROWCOUNT = 1; is lengthly, which is why I don't like it. Imagine if having to type my $var === 'foo';

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
it simple also for UPDATE/DELETE/INSERT. On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 10:06 GMT+02:00 Joel Jacobson j...@trustly.com: On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule pavel.steh...@gmail.com wrote: we have totally different opinion what

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On Thu, Sep 4, 2014 at 11:07 AM, Pavel Stehule pavel.steh...@gmail.com wrote: it is different semantic - returns composite or set of composites --- it is not row or rows The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n. Actually BL is usually processed oriented, so PL

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On 4 sep 2014, at 11:42, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 11:22 GMT+02:00 Joel Jacobson j...@trustly.com: The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n. no RETURNS return VALUE (it is not a row) .. and in combination with SELECT - value

Re: [HACKERS] PL/pgSQL 2

2014-09-04 Thread Joel Jacobson
On 4 sep 2014, at 15:09, Shaun Thomas stho...@optionshouse.com wrote: On 09/01/2014 04:04 AM, Joel Jacobson wrote: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On 4 sep 2014, at 15:32, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-09-04 15:24 GMT+02:00 Jan Wieck j...@wi3ck.info: On 09/04/2014 01:14 AM, Pavel Stehule wrote: 2014-09-03 23:19 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com A more SQL-ish way of doing the same could probably

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On 4 sep 2014, at 16:45, Hannu Krosing ha...@2ndquadrant.com wrote: When looking from the other end of the problem, we are using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql when we really want scalars. My understanding is that one main drivers of starting this thread was

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On 4 sep 2014, at 17:18, Pavel Stehule pavel.steh...@gmail.com wrote: You just need a ISAM API for Postgres, That is all. Now you are being ironic, and I would prefer to keep the discussion on a serious level. You know that's not applicable in my case, you know what I do for work and what kind

Re: [HACKERS] pgcrypto: PGP signatures

2014-09-04 Thread Joel Jacobson
Marko, et al, This is a review of the pgcrypto PGP signatures patch: http://www.postgresql.org/message-id/53edbcf0.9070...@joh.to There hasn't been any discussion, at least that I've been able to find. Contents Purpose == This patch add functions to create, verify and extract

Re: [HACKERS] PL/pgSQL 1.2

2014-09-04 Thread Joel Jacobson
On Thu, Sep 4, 2014 at 5:51 PM, Robert Haas robertmh...@gmail.com wrote: When you suggest ISAM, that's like saying demolish your house and build a new one when all I want is to make small but important changes to what I already do as a professional on a daily basis. Go right ahead: this is an

Re: [HACKERS] pgcrypto: PGP armor headers

2014-09-04 Thread Joel Jacobson
Marko, et al, This is a review of the pgcrypto PGP Armor Headers patch: http://www.postgresql.org/message-id/53edcae8.20...@joh.to Contents Purpose == This patch add functions to create and extract OpenPGP Armor Headers. from OpenPGP messages. Included in the patch are updated

Re: [HACKERS] PL/pgSQL 2

2014-09-03 Thread Joel Jacobson
On Wed, Sep 3, 2014 at 7:54 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I am not against to improve a PL/pgSQL. And I repeat, what can be done and can be done early: a) ASSERT clause -- with some other modification to allow better static analyze of DML statements, and enforces checks in

Re: [HACKERS] PL/pgSQL 2

2014-09-03 Thread Joel Jacobson
On Wed, Sep 3, 2014 at 10:07 AM, Pavel Stehule pavel.steh...@gmail.com wrote: When you use name plpgsql2 you say, so plpgsql2 is successor plpgsql. It is very hard to accept it. So any other name is not problem for me - like plpgsql-safe-subset or something else plpgsql2 *is* the successor of

Re: [HACKERS] pgcrypto: PGP signatures

2014-09-03 Thread Joel Jacobson
On Wed, Aug 6, 2014 at 2:46 PM, Marko Tiikkaja ma...@joh.to wrote: Hi hackers, Attached is a patch to add support for PGP signatures in encrypted messages into pgcrypto. I noticed Heikki wanted to check if there is any interested for the patches in the current commitfest. Yes, our company

Re: [HACKERS] PL/pgSQL 2

2014-09-03 Thread Joel Jacobson
On Wed, Sep 3, 2014 at 3:17 PM, Joshua D. Drake j...@commandprompt.com wrote: Well, I don't think PostgreSQL needs its own PL. I mean we already have several (what other database has pl/javascript or pl/python?) PostgreSQL already *have* it's own PL, it's called PL/pgSQL. Besides, the idea of

Re: [HACKERS] PL/pgSQL 2

2014-09-02 Thread Joel Jacobson
On Tue, Sep 2, 2014 at 5:46 AM, Craig Ringer cr...@2ndquadrant.com wrote: My point is that weeks can be spent just arguing about whether you should have a variable-delimiter ($variable) or not, how syntax should look, etc. Imagine how long it'd take to get a new language syntax agreed upon? I

Re: [HACKERS] PL/pgSQL 2

2014-09-02 Thread Joel Jacobson
On Tue, Sep 2, 2014 at 8:26 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 09/02/2014 09:06 AM, Joel Jacobson wrote: For me, the most important is to not break *most* of existing plpgsql code, but it's OK to break *some*. And when breaking it, it should be trivial to rewrite

Re: [HACKERS] PL/pgSQL 2

2014-09-02 Thread Joel Jacobson
On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule pavel.steh...@gmail.com wrote: What we can do better? 1. we can implement a conditional RAISE DELETE FROM tab WHERE xx = somevar; GET DIAGNOSTICS rc = ROW_COUNT; RAISE EXCEPTION 'some' WHEN rc 0; It is relatively natural and we use similar

  1   2   3   >