[HACKERS] Error handling in transactions

[Peter van Hardenberg]

After the previous thread, Jean-Paul, Ads, Alvarro and I were discussing
the use-case described by Joshua and trying to think about mitigating
strategies. Before getting into a discussion of a proposed solution, I'll
try and expand on the reasoning behind why I think this is a problem worth
solving.

First, discoverability of the current ON_ERROR_ROLLBACK=interactive is
poor. How would a user ever know that this was available as an option they
may want to set? Even if they could be told it was an option (in say a hint
message on a transaction abort) they would only find out about this after
the fact when the damage (a lost transaction) was done.

So let's try and imagine a solution where a user who has made a mistake in
a transaction might be able to gracefully recover but where the current
semantics are preserved.

In this case, we'd want a transaction not to abort immediately (allowing
recoverability) but not to commit if there was an error.

To make this work, an error during a transaction would not trigger an
immediate ROLLBACK but would instead set a session state say,
ERROR_TRIGGERED.

Most statements would not be allowed to execute in this state and each
statement would return an error describing the current state. A COMMIT
would then finally trigger the ROLLBACK, closing the transaction scope.

If the user were interested in recovering their transaction, they could set
ERROR_TRIGGERED back to "false", send any commands they wanted (retrying
part of the transaction, or whatever.) It might be simplest to prevent all
statements besides reading or setting ERROR_TRIGGERED but it may be
desirable to allow non-DDL/DML statements in order to aid in diagnosing
what happened.

This would also allow for programmatic error handling during transactions
without the overhead of savepoints by checking the value of ERROR_TRIGGERED
after each statement and handling it as appropriate.

Of course, the additional complexity of this feature is greater than simply
updating a default value but I'm certainly willing to accept the argument
that setting a new default to a potentially destructive setting is
problematic. Still, I do believe that the current state of affairs is
painful and problematic and this is a problem worth solving.

-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

[HACKERS] Defaulting psql to ON_ERROR_ROLLBACK=interactive

[Peter van Hardenberg]

All,

Ads and I were talking over breakfast about usability issues and he
mentioned transaction cancellation during interactive sessions as a serious
pain point.

I suggest we update the default of ON_ERROR_ROLLBACK to interactive for
10.0.

The last discussion I could find about this subject was in 2011 and while
there was concern about setting the default to "on" (as this would tamper
with the expected behaviour of scripts), I don't see any identification of
a problem that would be caused by setting it to "interactive" by default.

https://www.postgresql.org/message-id/CABwTF4V_J47GaryQcHD5Xe9gR57=shiyakgrtjbcusfqfvp...@mail.gmail.com

It's quite clear to me that the current default behaviour costs the world
serious pain and heart-ache on a daily basis. I myself have made this
mistake in the middle of a long transaction (as my former colleague Will
describes in the previous thread) and I can only imagine others have as
well.

A few preemptive rejoinders to the obvious objections:

- We shouldn't optimize for people who make mistakes.

It may well be that you don't make mistakes, but I do, and I expect I am
hardly the less competent person using Postgres, so we should design our
system accordingly.

- Anyone who wants this can set it in their .psqlrc already.

Most people won't know about .psqlrc, and even fewer will set it. Worse, by
the time you realize you don't have it set, it will be too late to do
anything about.

If folks are comfortable with this change, I'll provide a patch for the
current commitfest.

-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] SQL/JSON in PostgreSQL

[Peter van Hardenberg]

Anecdotally, we just stored dates as strings and used a convention (key
ends in "_at", I believe) to interpret them. The lack of support for dates
in JSON is well-known, universally decried... and not a problem the
PostgreSQL community can fix.

On Thu, Mar 9, 2017 at 10:24 AM, Sven R. Kunze <srku...@mail.de> wrote:

> On 09.03.2017 18:58, Robert Haas wrote:
>
>> Also, even if the superset thing were true on a theoretical plane, I'm
>> not sure it would do us much good in practice.  If we start using
>> YAML-specific constructs, we won't have valid JSON any more.  If we
>> use only things that are legal in JSON, YAML's irrelevant.
>>
>
> That's true. I just wanted to share my view of the "date guessing" part of
> pgpro's commits.
> I don't have a good solution for it either, I can only tell that where I
> work we do have same issues: either we guess by looking at the string value
> or we know that "this particular key" must be a date.
> Unsatisfied with either solution, we tend to use YAML for our APIs if
> possible.
>
>
> Regards,
> Sven
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>



-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] SQL/JSON in PostgreSQL

[Peter van Hardenberg]

Small point of order: YAML is not strictly a super-set of JSON.

Editorializing slightly, I have not seen much interest in the world for
YAML support though I'd be interested in evidence to the contrary.

On Tue, Mar 7, 2017 at 1:43 PM, Sven R. Kunze <srku...@mail.de> wrote:

> Hi,
>
> about the datetime issue: as far as I know, JSON does not define a
> serialization format for dates and timestamps.
>
> On the other hand, YAML (as a superset of JSON) already supports a
> language-independent date(time) serialization format (
> http://yaml.org/type/timestamp.html).
>
> I haven't had a glance into the SQL/JSON standard yet and a quick search
> didn't reveal anything. However, reading your test case here
> https://github.com/postgrespro/sqljson/blob/5a8a241/src/
> test/regress/sql/sql_json.sql#L411 it seems as if you intend to parse all
> strings in the form of "-MM-DD" as dates. This is problematic in case a
> string happens to look like this but is not intended to be a date.
>
> Just for the sake of completeness: YAML solves this issue by omitting the
> quotation marks around the date string (just as JSON integers have no
> quotations marks around them).
>
> Regards,
> Sven
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>



-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] GSoC 2017

[Peter van Hardenberg]

On Fri, Jan 27, 2017 at 2:48 PM, Jim Nasby <jim.na...@bluetreble.com> wrote:

> On 1/27/17 8:17 AM, Brad DeJong wrote:
>
>> Add the potential for regulatory requirements to change at any time -
>> sort of like timezone information. So no hard coded behavior.
>>
>
> Well, I wish we had support for storing those changing requirements as
> well. If we had that it would greatly simplify having a timestamp type that
> stores the original timezone.
>
> BTW, time itself fits in the multi-unit pattern, since months don't have a
> fixed conversion to days (and technically seconds don't have a fixed
> conversion to anything thanks to leap seconds).


I agree with Jim here.

I think we don't need to solve all the possible currency problems to have a
useful type. I'll reiterate what I think is the key point here:

A currency type should work like a wallet. If I have 20USD in my wallet and
I put 20EUR in the wallet, I have 20USD and 20EUR in the wallet, not 42USD
(or whatever the conversion rate is these days). If I want to convert those
to a single currency, I need to perform an operation.

If we had this as a basic building block, support for some of the major
currency formats, and a function that a user could call (think of the way
we justify_interval sums of intervals to account for the ambiguities in day
lengths and so on), I think we'd have a pretty useful type.

As to Tom's point, conversion rates do not vary with time, they vary with
time, space, vendor, whether you're buying or selling, and in what
quantity, and so on. We can give people the tools to more easily and
accurately execute this math without actually building a whole financial
tool suite in the first release.

I'll also note that in the absence of progress here, users continue to get
bad advice about using the existing MONEY type such as here:
http://stackoverflow.com/questions/15726535/postgresql-which-datatype-should-be-used-for-currency

-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] GSoC 2017

[Peter van Hardenberg]

On Mon, Jan 23, 2017 at 4:12 PM, Jim Nasby <jim.na...@bluetreble.com> wrote:

> On 1/23/17 3:45 PM, Peter van Hardenberg wrote:
>
>> A new currency type would be nice, and if kept small in scope, might be
>> manageable.
>>
>
> I'd be rather nervous about this. My impression of community consensus on
> this is a currency type that doesn't somehow support conversion between
> different currencies is pretty useless, and supporting conversions opens a
> 55 gallon drum of worms. I could certainly be mistaken in my impression,
> but I think there'd need to be some kind of consensus on what a currency
> type should do before putting that up for GSoC.
>

There's a relatively simple solution to the currency conversion problem
which avoids running afoul of the various mistakes some previous
implementations have made. Track currencies separately and always ask for a
conversion chart at operation time.

Let the user specify the values they want at conversion time. That looks
like this:

=> select '1 CAD'::currency + '1 USD'::currency + '1 CHF'::currency
'1.00CAD 1.00USD 1.00CHF'

=> select convert('10.00CAD'::new_currency, ('USD, '1.25', 'CHF',
'1.50')::array, 'USD')
12.50USD

The basic concept is that the value of a currency type is that it would
allow you to operate in multiple currencies without accidentally adding
them. You'd flatten them to a single type if when and how you wanted for
any given operation but could work without fear of losing information.

I have no opinion about the most pleasing notation for the currency
conversion chart, but I imagine it would be reasonable to let users provide
a default set of conversion values somewhere.

There are interesting and worthwhile conversations to have about
non-decimal currencies, but I think it would be totally reasonable not to
support them at all in a first release. As for currency precision, I would
probably consider leaning on numeric under the hood for the actual currency
values themselves but IANAA (though I have done quite a lot of work on
billing systems).

If it would be helpful, I could provide a detailed proposal on the wiki for
others to critique?

-
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] GSoC 2017

[Peter van Hardenberg]

A new currency type would be nice, and if kept small in scope, might be
manageable. Bringing Christoph Berg's PostgreSQL-units into core and
extending it could be interesting. Peter E's URL and email types might be
good candidates. What else? Informix Datablades had a media type way back
in the day... That's still a gap in community Postgres.

On Mon, Jan 16, 2017 at 6:43 PM, Jim Nasby <jim.na...@bluetreble.com> wrote:

> On 1/13/17 3:09 PM, Peter van Hardenberg wrote:
>
>> A new data type, and/or a new index type could both be nicely scoped
>> bits of work.
>>
>
> Did you have any particular data/index types in mind?
>
> Personally I'd love something that worked like a python dictionary, but
> I'm not sure how that'd work without essentially supporting a variant data
> type. I've got code for a variant type[1], and I don't think there's any
> holes in it, but the casting semantics are rather ugly. IIRC that problem
> appeared to be solvable if there was a hook in the current casting code
> right before Postgres threw in the towel and said a cast was impossible.
>
> 1: https://github.com/BlueTreble/variant/
>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
> 855-TREBLE2 (855-873-2532)
>



-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] GSoC 2017

[Peter van Hardenberg]

A new data type, and/or a new index type could both be nicely scoped bits
of work.

On Thu, Jan 12, 2017 at 12:27 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2017-01-12 21:21 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com>:
>
>> On 1/10/17 1:53 AM, Alexander Korotkov wrote:
>>
>>> 1. What project ideas we have?
>>>
>>
>> Perhaps allowing SQL-only extensions without requiring filesystem files
>> would be a good project.
>>
>
> Implementation safe evaluation untrusted PL functions - evaluation under
> different user under different process.
>
> Regards
>
> Pavel
>
>
>
>> --
>> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
>> Experts in Analytics, Data Architecture and PostgreSQL
>> Data in Trouble? Get it in Treble! http://BlueTreble.com
>> 855-TREBLE2 (855-873-2532)
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
>


-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] Patch: Implement failover on libpq connect level.

[Peter van Hardenberg]

On Wed, Oct 19, 2016 at 3:08 PM, Robert Haas  wrote:

> On Mon, Oct 26, 2015 at 4:25 PM, Peter Eisentraut  wrote:
> > On 10/14/15 6:41 AM, Victor Wagner wrote:
> All in all, I'm still feeling pretty good about trying to support the
> same syntax that our JDBC driver already does.  It's certainly not a
> perfect solution, but it is at least compatible with MySQL's JDBC
> driver and with MongoDB, and in a world where everybody has picked a
> different approach that's not too bad.  Hey, maybe if we use the same
> syntax as MongoDB they'll let us hang out with the cool kids...
>
>
They will never let us hang out with the cool kids. Don't worry though, the
cool kids are too busy figuring out why their cluster is out of consensus
to pay attention to much else.

Supporting different ports on different servers would be a much appreciated
feature (I can't remember if it was Kafka or Cassandra that didn't do this
and it was very annoying.)

Remember, as the connection string gets more complicated, psql supports the
Postgres URL format as a single command-line argument and we may want to
begin encouraging people to use that syntax instead.

Re: [HACKERS] \timing interval

[Peter van Hardenberg]

On Thu, Sep 1, 2016 at 12:14 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Corey Huinker <corey.huin...@gmail.com> writes:
> > On Thu, Sep 1, 2016 at 3:01 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> >> Well, that code's on the backend side so we're not going to just call it
> >> in any case.  And I think we don't want to be quite so verbose as to go
> up
> >> to hh:mm:ss.fff as soon as we get past 1 second.  However, comparing
> that
> >> output to what I had suggests that maybe it's better to keep a leading
> >> zero in two-digit fields, that is render times like "00:01.234",
> >> "01:23.456", or "01:23:45.678" rather than suppressing the initial zero
> as
> >> I had in my examples.  It's an extra character but I think it reinforces
> >> the meaning.
>
> > +1
> > The larger jump in widths from no MM:SS to HH:MM:SS is a good visual cue.
> > Jumping from MM:SS to H:MM:SS to HH:MM:SS would be more subtle and
> possibly
> > confusing.
>
> Attached is an updated patch that does it like that.  Sample output
> (generated by forcing specific arguments to PrintTiming):
>
> Time: 0.100 ms
> Time: 1.200 ms
> Time: 1001.200 ms (00:01.001)
> Time: 12001.200 ms (00:12.001)
> Time: 60001.200 ms (01:00.001)
> Time: 720001.200 ms (12:00.001)
> Time: 3660001.200 ms (01:01:00.001)
> Time: 43920001.200 ms (12:12:00.001)
> Time: 176460001.200 ms (2 01:01:00.001)
> Time: 216720001.200 ms (2 12:12:00.001)
> Time: 8816460001.200 ms (102 01:01:00.001)
> Time: 8856720001.200 ms (102 12:12:00.001)
>
> Barring objections I'll commit this soon.
>
> regards, tom lane
>


Some kind of units on the parenthetical format would be helpful. Glancing
at several of these values it takes me a couple of seconds to decide what
I'm reading.

-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE

[Peter van Hardenberg]

On Tue, Jul 26, 2016 at 6:15 PM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 7/26/16 6:14 PM, Vik Fearing wrote:
> > As mentioned elsewhere in the thread, you can just do WHERE true to get
> > around it, so why on Earth have it PGC_SUSET?
>
> I'm not sure whether it's supposed to guard against typos and possibly
> buggy SQL string concatenation in application code.  So it would help
> against accidental mistakes, whereas putting a WHERE TRUE in there would
> be an intentional override.
>
>
I know I'm late to the thread here, but I just wanted to add my small voice
in support
of this feature.

Over the years we've seen this happen at Heroku quite a bit (accidental
manual entry
without a where clause) and the only minor gripe I'd have is that contrib
modules are
very undiscoverable and users tend not to find out about them.

On the other hand, a session setting in core would probably not be that
different.

I expect Heroku will probably wind up enabling this by default on any
interactive
psql sessions.

(And I would encourage packagers and distributors to consider doing the
same.)

-p

Re: [HACKERS] JSON[B] arrays are second-class citizens

[Peter van Hardenberg]

The idea of converting a JSONB array to a PG array is appealing and would
potentially be more general-purpose than adding a new unnest. I'm not sure
how feasible either suggestion is.

I will say that I think the current state of affairs is gratuitously
verbose and expects users to memorize a substantial number of long function
names to perform simple tasks.

-p


On Tue, May 31, 2016 at 2:06 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, May 31, 2016 at 4:34 PM, David Fetter <da...@fetter.org> wrote:
>
>> Folks,
>>
>> While querying some JSONB blobs at work in preparation for a massive
>> rework of the data infrastructure, I ran into things that really
>> puzzled me, to wit:
>>
>> SELECT * FROM unnest('["a","b","c"]'::jsonb);
>> ERROR:  function unnest(jsonb) does not exist
>>
>> SELECT * FROM jsonb_array_elements('["a","b","c"]'::jsonb);
>>  value
>> ───
>>  "a"
>>  "b"
>>  "c"
>> (3 rows)
>>
>>
> ​I'd be inclined to -1 such a proposal.  TIMTOWTDI is not a principle that
> we endeavor to emulate.
>
> Having an overloaded form: <unnest(jsonb) : setof jsonb> is unappealing.
> While likely not that common the introduction of an ambiguity makes raises
> the bar considerably.
>
> That said we do seem to be lacking any easy way to take a json array and
> attempt to convert it directly into a PostgreSQL array.  Just a conversion
> is not always going to succeed though the capability seems worthwhile if as
> yet unasked for.  The each->convert->array_agg pattern works but is likely
> inefficient for homogeneous json array cases.
>
> David J.
>



-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] Calling json_* functions with JSONB data

[Peter van Hardenberg]

I'll look into it, thanks for the explanation.

On Mon, May 23, 2016 at 1:37 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Peter van Hardenberg <p...@pvh.ca> writes:
> > Great question, Marko. If you can point me towards an example I'll take a
> > look, but I'll proceed with the current understanding and suggestions and
> > see what people have to say.
>
> I believe Marko's just complaining about the case for unknown-type
> arguments, for example:
>
> regression=# select json_array_length('[1,2,3]');
>  json_array_length
> ---
>  3
> (1 row)
>
> The parser has no trouble resolving this because there is only one
> json_array_length(); but if there were two, it would fail to make a
> determination of which one you meant.
>
> AFAICS the only way to fix that would be to introduce some preference
> between the two types.  For example, we could move both 'json' and 'jsonb'
> into their own typcategory ('J' is unused...) and then mark 'jsonb' as
> the preferred type in that category.  This would require a fair amount of
> experimentation to determine if it upsets any cases that work conveniently
> today; but right offhand I don't see any fatal problems with such an idea.
>
> regards, tom lane
>



-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] Calling json_* functions with JSONB data

[Peter van Hardenberg]

Great question, Marko. If you can point me towards an example I'll take a
look, but I'll proceed with the current understanding and suggestions and
see what people have to say.

On Mon, May 23, 2016 at 10:47 AM, Marko Tiikkaja <ma...@joh.to> wrote:

> On 2016-05-23 18:55, Peter van Hardenberg wrote:
>
>> I talked this over with Andrew who had no objections and suggested I float
>> it on the list before writing a patch. Looks pretty straightforward, just
>> a
>> few new data rows in pg_proc.h.
>>
>> Anyone have any concerns or suggestions?
>>
>
> What about cases like  json_whatever($1)  which previously worked but will
> now be ambiguous?  (Or will they somehow not be ambiguous?)
>
>
> .m
>



-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

[HACKERS] Calling json_* functions with JSONB data

[Peter van Hardenberg]

Hi there,

I noticed it was very easy to accidentally call the json_* form of JSON
manipulation functions with jsonb data as input. This is pretty
sub-optimal, since it involves rendering the jsonb then reparsing it and
calling the json_* form of the function.

Fortunately, this seems quite easy to resolve by taking advantage of our
ability to add json_*(jsonb) form of the functions.

I talked this over with Andrew who had no objections and suggested I float
it on the list before writing a patch. Looks pretty straightforward, just a
few new data rows in pg_proc.h.

Anyone have any concerns or suggestions?

-p

-- 
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut

Re: [HACKERS] Prepared statements fail after schema changes with surprising error

[Peter van Hardenberg]

On Thu, Jan 24, 2013 at 6:17 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 I wrote:
  Here's a draft patch for that.  I've not looked yet to see if there's
  any documentation that ought to be touched.

 And now with the documentation.  If I don't hear any objections, I plan
 to commit this tomorrow.


No objections here. Thanks Tom and everyone else for setting this straight.

-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

[HACKERS] Prepared statements fail after schema changes with surprising error

[Peter van Hardenberg]

A user reported an interesting issue today. After restoring a dump created
with --clean on a running application in his development environment his
application started complaining of missing tables despite those tables very
clearly existing.

After a little thinking, we determined that this was due to the now-default
behaviour of Rails to create prepared statements for most queries. The
prepared statements error out because the old relation they point to is
missing, but this gives a misleading report thus:

PG::Error: ERROR: relation xxx does not exist

I'm not sure what the best outcome here would be. A very simple solution
might be to expand the error message or add a hint to make it descriptive
enough that a user might be able to figure out the cause on their own
without happening to have the unusual intersection of Rails and Postgres
internals knowlege I (unfortunately) possess. A better solution might be to
attempt to re-prepare the statement before throwing an error.

-pvh

-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

Re: [HACKERS] Prepared statements fail after schema changes with surprising error

[Peter van Hardenberg]

Hm - I'm still able to recreate the test the user's running using
pg_dump/pg_restore. I'm still working to see if I can minimize the
test-case, but this is against 9.2.2. Would you prefer I test against HEAD?

regression=# create table z1 (f1 int);
CREATE TABLE
regression=# prepare sz1 as select * from z1;
PREPARE
regression=# insert into z1 values (1);
INSERT 0 1
regression=# execute sz1;
 f1

  1
(1 row)

# In another terminal window
$ pg_dump -F c regression  test.dump
$ pg_restore --clean --no-acl --no-owner -d regression test.dump
ERROR:  cannot drop the currently open database
STATEMENT:  DROP DATABASE regression;
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2185; 1262 16384 DATABASE
regression pvh
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop
the currently open database
Command was: DROP DATABASE regression;

WARNING: errors ignored on restore: 1
$

# back in the same backend

regression=# execute sz1;
ERROR:  relation z1 does not exist
regression=# select * from z1;
 f1

  1
(1 row)

regression=#

On Mon, Jan 21, 2013 at 5:31 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Peter Geoghegan peter.geoghega...@gmail.com writes:
  On 22 January 2013 00:00, Tom Lane t...@sss.pgh.pa.us wrote:
  Works for me ...

  That's what I thought. But looking at RangeVarGetRelidExtended() and
  recomputeNamespacePath(), do you suppose that the problem could be
  that access privileges used by the app differed for a schema (or, more
  accurately, two physically distinct namespaces with the same nspname)
  between executions of the prepared query?

 What I'm suspicious of is that Peter is complaining about an old
 version, or that there's some other critical piece of information he
 left out.  I don't plan to speculate about causes without a concrete
 test case.

 regards, tom lane




-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

Re: [HACKERS] Prepared statements fail after schema changes with surprising error

[Peter van Hardenberg]

Okay - I've narrowed it down to an interaction with schema recreation.
Here's a minimal test-case I created by paring back the restore from the
pg_restore output until I only had the essence remaining:

-- setup
drop table z1;
create table z1 (f1 int);
insert into z1 values (1);
prepare sz1 as select * from z1;
select 'executing first prepared statement';
execute sz1;

-- remainder of minimized pg_restore SQL output
DROP TABLE public.z1;
DROP SCHEMA public;
CREATE SCHEMA public;
CREATE TABLE z1 (
f1 integer
);

-- proof of regression
select 'executing second prepared statement';
execute sz1;
select 'selecting from z1 to prove it exists';
select * from z1;


On Mon, Jan 21, 2013 at 10:45 PM, Peter van Hardenberg p...@pvh.ca wrote:

 Hm - I'm still able to recreate the test the user's running using
 pg_dump/pg_restore. I'm still working to see if I can minimize the
 test-case, but this is against 9.2.2. Would you prefer I test against HEAD?

 regression=# create table z1 (f1 int);
 CREATE TABLE
 regression=# prepare sz1 as select * from z1;
 PREPARE
 regression=# insert into z1 values (1);
 INSERT 0 1
 regression=# execute sz1;
  f1
 
   1
 (1 row)

 # In another terminal window
 $ pg_dump -F c regression  test.dump
 $ pg_restore --clean --no-acl --no-owner -d regression test.dump
 ERROR:  cannot drop the currently open database
 STATEMENT:  DROP DATABASE regression;
 pg_restore: [archiver (db)] Error while PROCESSING TOC:
 pg_restore: [archiver (db)] Error from TOC entry 2185; 1262 16384 DATABASE
 regression pvh
 pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop
 the currently open database
 Command was: DROP DATABASE regression;

 WARNING: errors ignored on restore: 1
 $

 # back in the same backend

 regression=# execute sz1;
 ERROR:  relation z1 does not exist
 regression=# select * from z1;
  f1
 
   1
 (1 row)

 regression=#

 On Mon, Jan 21, 2013 at 5:31 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Peter Geoghegan peter.geoghega...@gmail.com writes:
  On 22 January 2013 00:00, Tom Lane t...@sss.pgh.pa.us wrote:
  Works for me ...

  That's what I thought. But looking at RangeVarGetRelidExtended() and
  recomputeNamespacePath(), do you suppose that the problem could be
  that access privileges used by the app differed for a schema (or, more
  accurately, two physically distinct namespaces with the same nspname)
  between executions of the prepared query?

 What I'm suspicious of is that Peter is complaining about an old
 version, or that there's some other critical piece of information he
 left out.  I don't plan to speculate about causes without a concrete
 test case.

 regards, tom lane




 --
 Peter van Hardenberg
 San Francisco, California
 Everything was beautiful, and nothing hurt. -- Kurt Vonnegut




-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

Re: [HACKERS] Synchronous commit not... synchronous?

[Peter van Hardenberg]

On Fri, Nov 2, 2012 at 11:16 AM, Peter Eisentraut pe...@eisentraut.orgwrote:


 Did the inserted row also arrive at the standby?


No, as there was no standby.

-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

[HACKERS] Synchronous commit not... synchronous?

[Peter van Hardenberg]

This was rather surprising - my synchronous commit was... not cancelled. Is
this expected behaviour?

d5r5fdj6u5ieml= begin;
BEGIN
d5r5fdj6u5ieml= set synchronous_commit = 'on';
SET
d5r5fdj6u5ieml= insert into data values ('baz');
INSERT 0 1
d5r5fdj6u5ieml= commit;
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have
been replicated to the standby.
COMMIT
d5r5fdj6u5ieml= select * from data;
 foo
-
 bar
 baz
(2 rows)

d5r5fdj6u5ieml= rollback;
NOTICE:  there is no transaction in progress
ROLLBACK
d5r5fdj6u5ieml=


-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

Re: [HACKERS] psql \i tab completion initialization problem on HEAD

[Peter van Hardenberg]

On Fri, Feb 24, 2012 at 9:46 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Actually, what I should have asked is are you running Lion?.
 Because with libedit on Lion, tab completion is 100% broken, as per
 http://archives.postgresql.org/pgsql-hackers/2011-07/msg01642.php
 This is just the latest installment in a long and sad story of
 libedit being mostly not up to snuff on OS X.

 I can reproduce the behavior you mention on my own Mac, but the fact
 that it appears to work after the first time is probably just blind
 luck from happenstance locations of malloc results :-(

 As for GNU readline, I suspect you weren't actually testing it.
 Note that the thing called /usr/lib/libreadline.dylib is not GNU
 readline, it's only a symlink to libedit.


I am indeed running Lion. Thanks for helping me track down the cause.

-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

-- 
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] psql \i tab completion initialization problem on HEAD

[Peter van Hardenberg]

On Thu, Feb 23, 2012 at 4:15 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Here's a reliable reproduction on my OS X laptop.

 OS X?  Are you using GNU readline, or Apple's libedit?


I reproduced it with both, but if that news is surprising to you, I
can certainly re-test.

-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] psql \i tab completion initialization problem on HEAD

[Peter van Hardenberg]

While testing Noah's filename quoting patch on my local development
machine, I noticed some strange behaviour around tab completion with
\i; it doesn't appear to be present in 9.1, but it is present on 9.2
HEAD and appears to be present with and without readline.

It manifests as the client preferring statement completion over
filename completion until the first time \i is forced to check
something on disk, after which it begins to work as expected.

Here's a reliable reproduction on my OS X laptop.

- % bin/psql
psql (9.2devel, server 9.0.4)
WARNING: psql version 9.2, server version 9.0.
 Some psql features might not work.
Type help for help.

pvh=# \i TABTAB
ABORT   ALTER   ANALYZE BEGIN
CHECKPOINT  CLOSE   CLUSTER COMMENT COMMIT
 COPYCREATE  DEALLOCATE
DECLARE DELETE FROM DISCARD DO  DROP
 END EXECUTE EXPLAIN FETCH
  GRANT   INSERT  LISTEN
LOADLOCKMOVENOTIFY
PREPARE REASSIGNREINDEX RELEASE RESET
 REVOKE  ROLLBACKSAVEPOINT
SECURITY LABEL  SELECT  SET SHOWSTART
 TABLE   TRUNCATEUNLISTENUPDATE
  VACUUM  VALUES  WITH
pvh=# \i asdf
asdf: No such file or directory
pvh=# \i ./TABTAB
./bin  ./include  ./lib  ./oh hai   ./share
pvh=# \i

I don't see this regression with the 9.1 client I have here, so I
suspect it has something to do with whatever patch introduced the
relative paths by default.

-p

-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

-- 
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] Inline Extension

[Peter van Hardenberg]

On Thu, Jan 26, 2012 at 3:48 PM, David E. Wheeler da...@justatheory.com wrote:
 On Jan 26, 2012, at 9:40 AM, Dimitri Fontaine wrote:

 Not for 9.2, but I can't help thinking that if we could manage to host
 the .so module itself in the catalogs, we could solve updating it in a
 transactional way and more importantly host it per-database, rather than
 having the modules work per major version (not even per cluster) and the
 extension mechanism work per-database inside each cluster. But that's
 work for another release.

 +1 Cloud vendors will *love* this.


Confirmed.

Let me share my perspective. I'll begin by describing the current
state of runtime code dependency management for comparison.

In Ruby, any user can push an application to our platform which relies
on any/every ruby gem ever released (give or take). These gems may
require exact releases of other gems, have elaborate system
dependencies, and/or natively compiled code components. This is thanks
to the rubygems.org repository, the gem system, and recently but
crucially, the bundler system for resolving and isolating
dependencies. Releasing a new gem takes moments and I have personally
released a half dozen of no real consequence to the world which I use
from time to time.

In contrast, the idea that any person or team of people could possibly
review the literally hundreds of gems released each day is no longer
plausible. The only feasible solution for providing a robust service
is to engineer a solution which can be operated from inside the
cluster to install any library whatsoever.

Our aim is, put simply, to be able to support every extension in the
world, at once, under cascading replication, across major catalogue
upgrades. We hope this ideal is shared by the community at large,
since our problems are generally the same as other users, just writ
large.

-pvh

PS: As an aside, because of the many problems with in-cluster
multi-tenancy (to pick just one example, resource isolation between
users) I have no security concerns with giving users every ability to
execute code as the cluster owner's UNIX user. On our service we do
not restrict our users access to superuser out of spite, but to reduce
the available surface area for self-destruction.

-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

-- 
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] WIP: URI connection string support for libpq

[Peter van Hardenberg]

On Mon, Dec 12, 2011 at 5:05 PM, David E. Wheeler da...@justatheory.com wrote:
 On Dec 12, 2011, at 3:55 PM, Peter van Hardenberg wrote:
 only a nearly insurmountable mailing list thread
 prevents it.

 What happened to SexQL?


Case in point.

-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

-- 
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] JSON for PG 9.2

[Peter van Hardenberg]

On Mon, Dec 12, 2011 at 9:25 PM, Peter Eisentraut pete...@gmx.net wrote:
 On mån, 2011-12-12 at 16:51 -0800, Peter van Hardenberg wrote:
 You don't need a new PL to do that.  The existing PLs can also parse
 JSON.  So that's not nearly enough of a reason to consider adding this
 new PL.

PL/V8 is interesting because it is very fast, sandboxed, and well
embedded with little overhead.

My experience with PL/Python and PL/Perl has not been thus, and
although they are handy if you want to break out and run system work,
they're not the kind of thing I'd consider for defining performant
operators with.

I feel PL/V8 has promise in that area.

-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

-- 
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] WIP: URI connection string support for libpq

[Peter van Hardenberg]

On Mon, Dec 12, 2011 at 2:06 PM, Alexander Shulgin
a...@commandprompt.com wrote:


  psql -d postgresql://user@pw:host:port/dbname?param1=value1param2=value2...


I'd like to make the controversial proposal that the URL prefix should
be postgres: instead of postgresql:. Postgres is a widely accepted
nickname for the project, and is eminently more pronounceable. Once
the url is established it will be essentially impossible to change
later, but right now only a nearly insurmountable mailing list thread
prevents it.

Excluding references to the postgresql.org domain, there are already
5x as many references in the source code to postgres (2583 lines)
than to postgresql (539 lines). Taking into account that the name of
the binary and the usual Unix user are already postgres, having one
less place which would eventually need changing seems like a good plan
overall.

Here is, for those who have understandably blocked this argument from
their memory, a link to the existing wiki document on the pros and
cons of the two names:
http://wiki.postgresql.org/wiki/Postgres

Over at Heroku decided to side with Tom's assessment that arguably,
the 1996 decision to call it PostgreSQL instead of reverting to plain
Postgres was the single worst mistake this project ever made. (And we
at Heroku have also frustratingly split our references and
occasionally used the SQL form.)

Although I do not have the stomach to push for a full renaming blitz,
I felt I must at least make a case for not making the situation any
worse.

My apologies in advance for re-opening this can of worms.

Best regards,
-pvh

PS: It is not in any way shape or form relevant to my argument, nor do
I claim that anyone else should care, but in the spirit of full
disclosure, and depending on how you count, we currently have
somewhere between 250,000 and 500,000 URLs which begin with
postgres:// in our care.

--
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

-- 
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] JSON for PG 9.2

[Peter van Hardenberg]

We reached out to Joseph to see if we could help sponsor the project,
but never really heard back from him.

Because we haven't heard from him in a while we've been using PL/V8 to
validate a JSON datatype simulated by a DOMAIN with a simple
acceptance function. (See below.) This is not ideally performant but
thanks to V8's JIT the JSON parser is actually reasonably good.

I think releasing something simple and non-performant with reasonable
semantics would be the best next step. If it were up to me, I'd
probably even try to just land PL/V8 as PL/JavaScript for 9.2 if the
crash bugs and deal breakers can be sifted out.

PL/V8 is fast, it's sandboxed, and while it doesn't provide GIN or
GIST operators out of the box, maybe those could be motivated by its
inclusion.

Andrew, you've been down in the guts here, what do you think?

-pvh

(Code sample.)

create or replace function valid_json(json text)
returns bool as $$
  try { JSON.parse(json); return true }
  catch(e) { return false}
$$ LANGUAGE plv8 IMMUTABLE STRICT;

select valid_json('{key: value}'), valid_json('lol');
valid_json | t
valid_json | f
Time: 0.283 ms

create domain json
  as text check(valid_json(VALUE));
create table jsononly(data json);

insert into jsononly values 'lol';
ERROR:  syntax error at or near 'lol'
LINE 1: insert into jsononly values 'lol';

insert into jsononly
  values ('{ok: true}');
INSERT 0 1

-p

On Mon, Dec 12, 2011 at 1:34 PM, Josh Berkus j...@agliodbs.com wrote:
 Bruce,

 I thought that Joseph Adams was still working on this, sponsored by
 Heroku.  Joseph?


 --
 Josh Berkus
 PostgreSQL Experts Inc.
 http://pgexperts.com

 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers



-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

-- 
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] Policy on pulling in code from other projects?

[Peter van Hardenberg]

On Sat, Jul 23, 2011 at 3:39 AM, Andrew Dunstan and...@dunslane.net wrote:


 1. I think the proposed use is of very marginal value at best, and
 certainly not worth importing an external library for.


Now that I've seen two people who seem to think that this is not an
important feature I'll wade in and respond to this idea.

I think it's very easy to doubt the value of a definitively recognizable
string that represents a postgres database when you don't have a
heterogenous environment with more than a hundred thousand applications of
all types in it. To make matters worse, as language support on that platform
continues to widen beyond its humble beginnings, there isn't a standard
across those languages for what constitutes a postgres URL. This is the
current situation at Heroku, where we currently run ~150,000 individual
databases on our infrastructure as well as a variety of other databases such
as MySQL, Redis, Mongo, Couch, Riak, Cassandra, c.

To head off the most obvious criticism, we aren't using connection strings
in our system because there isn't any reasonable way to recognize them. A PG
conninfo string is just a set of key value pairs with no dependably present
signifier. This is why almost every database library from Ruby to Python to
Java takes some form of a URL with a protocol called postgres in it in
order to help select which driver to use.

Further, support (and syntax!) for the more esoteric connection parameters
varies from library to library as well as between languages. A good spec by
the project would go a long way in resolving this, and I can at least be
confident that we could get it adopted very quickly by all three of the
Ruby-community Postgres libraries.

In conclusion, this is a serious operational concern for me and my team and
I will be personally dealing with fires caused by this for years to come
regardless of the outcome of this thread.

Best,
-pvh

-- 
Peter van Hardenberg
Department of Data
Heroku
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut

Re: [HACKERS] Extension Packaging

[Peter van Hardenberg]

My apologies for wading in out of the blue here as a first time poster with
big demands, but allow me to briefly state my hopes without trying to be too
proscriptive about particular mechanisms.

My hope here is that the extension model should eventually enable me to
offer the ability for non-superuser databases to specify by some mechanism
the extensions that they require in a reproducible fashion, enabling my
users to recreate their local development conditions on a production
cluster.

My particular worry, and I apologize if I have misunderstood the thrust of
this thread, is that extension version might not be tied to the extension
revision, and so I will not be able to determine whether or not all
existing extensions are already at a specific version.

The precision of this process is very important to me. My intended use case
for this feature is to allow users to specify the versions of extensions
that they need in some kind of a control file or in a database migration
script such that they can then install those extensions on various new
systems in a reliable and reproducible way.

David, if you do what you propose, haven't I already lost?

---
Peter van Hardenberg
Heroku

On Wed, May 11, 2011 at 7:48 PM, David E. Wheeler da...@kineticode.comwrote:

 On May 11, 2011, at 2:47 PM, Robert Haas wrote:

  Okay, how we add a revision key to the control file and extrevision to
 the pg_extension catalog. Its type can be TEXT and is optional for use by
 extensions.
 
  This would allow extension authors to identify the base version of an
 extension but also the revision. And the core doesn't have to care how it
 works or if it's used, but it would allow users to know exactly what they
 have installed.
 
  Thoughts?
 
  How would pg_extension.extrevision be kept up to date?  AFAICS, the
  whole point is that you might swap out the shared libraries without
  doing anything at the SQL level.

 Bah! Okay, I give up. I'll not worry about it right now, as I have only one
 C extension outside of core and it won't change much in the code. And I'll
 just keep using the full version string (x.y.z) for the upgrade scripts.
 What I won't do is change that version with every release, unless there is a
 code change to demand it. The distribution version can increment
 independently.

 Best,

 David
 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers




-- 
Peter van Hardenberg
San Francisco, California
Everything was beautiful, and nothing hurt. -- Kurt Vonnegut