Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Andres Freund
On 2014-02-27 23:54:47 -0800, Peter Geoghegan wrote:
 In any case, as I say, if that's the patch that Andres or Oleg or
 Teodor really want to submit, then by all means let them submit it.

Just to make that clear, I am not one of the authors, I just did a
couple of light review passes.

Greetings,

Andres Freund

-- 
 Andres Freund 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] jsonb and nested hstore

2014-02-28 Thread Peter Geoghegan
On Fri, Feb 28, 2014 at 12:01 AM, Andres Freund and...@2ndquadrant.com wrote:
 On 2014-02-27 23:54:47 -0800, Peter Geoghegan wrote:
 In any case, as I say, if that's the patch that Andres or Oleg or
 Teodor really want to submit, then by all means let them submit it.

 Just to make that clear, I am not one of the authors, I just did a
 couple of light review passes.

Sorry, that was a typo. I meant Andrew.


-- 
Peter Geoghegan


-- 
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] Store Extension Options

2014-02-28 Thread Abhijit Menon-Sen
Hi Fabrízio.

Here are a few comments based on a quick look at your updated patch.

At 2014-02-13 22:44:56 -0200, fabriziome...@gmail.com wrote:

 diff --git a/doc/src/sgml/ref/alter_index.sgml 
 b/doc/src/sgml/ref/alter_index.sgml
 index d210077..5e9ee9d 100644
 --- a/doc/src/sgml/ref/alter_index.sgml
 +++ b/doc/src/sgml/ref/alter_index.sgml
 @@ -82,6 +82,14 @@ ALTER INDEX [ IF EXISTS ] replaceable 
 class=PARAMETERname/replaceable RESE
xref linkend=SQL-REINDEX
to get the desired effects.
   /para
 + note
 +   para
 + A custom name can be used as namespace to define a storage 
 parameter.
 + Storage option pattern: namespace.option=value
 + (namespace=custom name, option=option name and value=option value).
 + See example bellow.
 +   /para
 + /note
  /listitem
 /varlistentry

I was slightly confused by the wording here. I think it would be better
to say something like Custom storage parameters are of the form
namespace.option and leave it at that.

(Aside: s/bellow/below/)

 @@ -202,6 +210,17 @@ ALTER INDEX distributors SET (fillfactor = 75);
  REINDEX INDEX distributors;
  /programlisting/para
  
 +  para
 +   To set a custom storage parameter:
 +programlisting
 +ALTER INDEX distributors
 +  SET (bdr.do_replicate=true);
 +/programlisting
 +   (bdr=custom name, do_replicate=option name and 
 +   true=option value)
 +/para
 +
 +
   /refsect1

It might be best to avoid using bdr.do_replicate in the example, since
it's still a moving target. It might be better to use a generic example
like somenamespace.optionname=true, in which case the explanation isn't
needed either.

The patch applies and builds fine, the tests pass, and the code looks
OK to me. I don't have a strong opinion on validating custom reloption
values through hooks as discussed earlier in the thread, but the simple
version (i.e. your latest patch) seems at least a useful starting point.

-- Abhijit


-- 
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] What behavior is in this loop?

2014-02-28 Thread KONDO Mitsumasa

(2014/02/27 20:19), Heikki Linnakangas wrote:

On 02/27/2014 12:38 PM, KONDO Mitsumasa wrote:

I found interesting for and while loop in WaitForWALToBecomeAvailable() in
xlog.c. Can you tell me this behavior?

for (;;)
{
~
} while (StanbyMode)

I confirmed this code is no problem in gcc compiler:)


Oh wow :-). That's clearly a thinko, although harmless in this case. Looking at
the git history, I made that mistake in commit abf5c5c9a. Before that, there was
no while.

That's easier to understand with some extra formatting. That's two loops, like 
this:

/* loop 1 */
for (;;)
{
   ...
}

/* loop2 */
while(StandbyMode);

The second loop is obviously completely pointless. Thankfully, the there are no
breaks inside the first loop (the ones within the switch-statements don't
count), so the endless while-loop is never reached.

Yeah, StandbyMode flag doesn't change in this loop.


I'll go fix that... Thanks for the report!

Thanks for your kind!

By the way, I found cannot promote problem in PG9.3.3 in standby mode with 
archive revovery and crash recovery situations. I analyze this problem and fix it 
now. Please take care of then!


Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center




--
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 and nested hstore

2014-02-28 Thread Andres Freund
On 2014-02-27 15:06:33 -0500, Andrew Dunstan wrote:
 You realize that this API dates from 9.3 and has been used in numerous
 extensions, right? So the names are pretty well fixed, for good or ill.

Sure. Doesn't prevent adding a couple more comments tho. I've only
noticed this because I opened the header as a reference when reading
your patch. Anyway, do something based on that feedback or not, your
choice ;)

Greetings,

Andres Freund

-- 
 Andres Freund 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] Backup throttling

2014-02-28 Thread Antonin Houska
On 02/27/2014 11:04 PM, Alvaro Herrera wrote:
 I pushed this patch with a few further tweaks.  In your changes to
 address the above point, you made the suffix mandatory in the
 pg_basebackup -r option.  This seemed a strange restriction, so I
 removed it.  It seems more user-friendly to me to accept the value as
 being expressed in kilobytes per second without requiring the suffix to
 be there; the 'k' suffix is then also accepted and has no effect.  I
 amended the docs to say that also.
 
 If you or others feel strongly about this, we can still tweak it, of
 course.

I'm used to assume the base unit if there's no suffix, but have no
objections against considering kB as the default. I see you adjusted
documentation too.

 I also moved the min/max #defines to replication/basebackup.h, and
 included that file in pg_basebackup.c.  This avoids the duplicated
 values.  That file is okay to be included there.

I kept in mind that pg_basebackup.c is not linked to the backend, but
you're right, mere inclusion is something else.

 Thanks for your patch, and the numerous reviewers who took part.

Thanks for committing - this is my first patch :-)

// Tony




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


[HACKERS] Hot standby doesn't come up on some situation.

2014-02-28 Thread Kyotaro HORIGUCHI
Hello, we found that hot standby doesn't came up under certain
condition. This occurs for 9.3 and 9.4dev.

The recovery process stays on 'incosistent' state forever when
the server has crashed before any wal record is inserted after
the last checkpoint.

This seems to be because EndRecPtr is set to minRecoveryPoint at
the end of crash recovery in ReadRecord. EndRecPtr here points to
the beginning of the next record to the record alread read, just
after the last checkpoint and no record is there in this
case. Then successive CheckRecoveryConsistency won't consider
that the 'consistent state' comes in spite that actually it is
already consistent.

I diffidently think that lastReplayedEndRecPtr is suitable there.

The script attached first causes the situation. Run it, then
after the server complains that it can't connect to the primary,
connecting it by psql results in,

| psql: FATAL:  the database system is starting up

The attached patch fixes the problem on 9.4dev.

What do you think about this?

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
#! /bin/sh

# killall postgres
# rm -rf $PGDATA/*
initdb
pg_ctl start -w
sleep 1
pg_ctl stop -m i
cat  $PGDATA/recovery.conf EOF
standby_mode = 'on'
primary_conninfo = 'host=localhost port= user=repuser application_name=pm01 
keepalives_idle=60 keepalives_interval=5 keepalives_count=5'
#restore_command = '/bin/true'
recovery_target_timeline = 'latest'
EOF
cat  $PGDATA/postgresql.conf EOF
#log_min_messages = debug5
hot_standby = on
EOF
pg_ctl start
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 483d5c3..f1f54f1 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -4496,7 +4496,15 @@ ReadRecord(XLogReaderState *xlogreader, XLogRecPtr RecPtr, int emode,
 ControlFile-state = DB_IN_ARCHIVE_RECOVERY;
 if (ControlFile-minRecoveryPoint  EndRecPtr)
 {
-	ControlFile-minRecoveryPoint = EndRecPtr;
+	/*
+	 * Altough EndRecPtr is the right value for
+	 * minRecoveryPoint in archive recovery, it is a bit too
+	 * far when the last checkpoint record is the last wal
+	 * record here. Use lastReplayedEndRecPtr as
+	 * minRecoveryPoint point to start hot stanby just after.
+	 */
+	ControlFile-minRecoveryPoint =
+		XLogCtl-lastReplayedEndRecPtr;
 	ControlFile-minRecoveryPointTLI = ThisTimeLineID;
 }
 /* update local copy */

-- 
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] Hot standby doesn't come up on some situation.

2014-02-28 Thread Kyotaro HORIGUCHI
Ouch. this is the same issue to the mail below,

http://www.postgresql.org/message-id/53104595.6060...@lab.ntt.co.jp

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center


-- 
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] Hot standby doesn't come up on some situation.

2014-02-28 Thread Andres Freund
On 2014-02-28 17:55:21 +0900, Kyotaro HORIGUCHI wrote:
 The recovery process stays on 'incosistent' state forever when
 the server has crashed before any wal record is inserted after
 the last checkpoint.

 # killall postgres
 # rm -rf $PGDATA/*
 initdb
 pg_ctl start -w
 sleep 1
 pg_ctl stop -m i
 cat  $PGDATA/recovery.conf EOF
 standby_mode = 'on'
 primary_conninfo = 'host=localhost port= user=repuser 
 application_name=pm01 keepalives_idle=60 keepalives_interval=5 
 keepalives_count=5'
 #restore_command = '/bin/true'
 recovery_target_timeline = 'latest'
 EOF
 cat  $PGDATA/postgresql.conf EOF
 #log_min_messages = debug5
 hot_standby = on
 EOF
 pg_ctl start

Uh. So, if I understand correctly, what you did is to convert a normal
live pg, into a replica that doesn't have a upstream node, right?
Normally the primary will just do an additional write shortly
afterwards, resolving this situation?

Greetings,

Andres Freund

-- 
 Andres Freund 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] Hot standby doesn't come up on some situation.

2014-02-28 Thread Kyotaro HORIGUCHI
Hello,

2014/02/28 18:07 Andres Freund :

 On 2014-02-28 17:55:21 +0900, Kyotaro HORIGUCHI wrote:
  The recovery process stays on 'incosistent' state forever when
  the server has crashed before any wal record is inserted after
  the last checkpoint.

  # killall postgres
  # rm -rf $PGDATA/*
  initdb
  pg_ctl start -w
  sleep 1
  pg_ctl stop -m i
  cat  $PGDATA/recovery.conf EOF
  standby_mode = 'on'
  primary_conninfo = 'host=localhost port= user=repuser
application_name=pm01 keepalives_idle=60 keepalives_interval=5
keepalives_count=5'
  #restore_command = '/bin/true'
  recovery_target_timeline = 'latest'
  EOF
  cat  $PGDATA/postgresql.conf EOF
  #log_min_messages = debug5
  hot_standby = on
  EOF
  pg_ctl start

 Uh. So, if I understand correctly, what you did is to convert a normal
 live pg, into a replica that doesn't have a upstream node, right?

Yes, but the same stuation could be made by restarting crashed secondary. I
didn't tried it since my console is far behind now...

Can a restart of secondry after crashing just after the completion of
restartpoint with no further records make the same situation?

I have no idea about the scenario on whitch this behavior was regarded as
undesirable but anyway I think that the secondry should start accepting
client just after crash recovery is completed.

 Normally the primary will just do an additional write shortly
 afterwards, resolving this situation?

Maybe so. I haven't tried though.

regards,
-- 
Kyotaro Horiguchi
NTT Opensource Software Center


Re: [HACKERS] extension_control_path

2014-02-28 Thread Dimitri Fontaine
Peter Eisentraut pete...@gmx.net writes:
 I think we should get rid of the module_pathname business, and
 extensions' SQL files should just refer to the base file name and rely
 on the dynamic library path to find the files.  What would we lose if we
 did that?

Control over *which* mylib.so file gets loaded for a specific sql
script. That's the whole namespace issue Stephen is worried about.

If you're testing the new version of an extension before installing it
properly, then you will have the current and the new versions of the
.so, with the exact same name, at different places.

Note that when using base file name only, then you could also have a
clash with a dynamic library of the same name installed on the system,
even if not made to be loaded by PostgreSQL.

Some extensions are using way too generic names. Hint: prefix.so.

Regards,
-- 
Dimitri
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
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] extension_control_path

2014-02-28 Thread Dimitri Fontaine
Stephen Frost sfr...@snowman.net writes:
# hstore extension
comment = 'data type for storing sets of (key, value) pairs'
default_version = '1.3'
directory = 'local/hstore-new'
module_pathname = '$directory/hstore'
relocatable = true

 Interesting idea.  I'm a *little* concerned that re-useing '$directory'
 there might confuse people into thking that any values in the control
 file could be substituted in a similar way though.  Would there really
 be much difference between that and '$ctldir' or something?

Well, using $directory makes the feature auto-documented and very easy
to read even without the reference documentation handy. It's also a very
known way to setup things in .ini files.

Now, what other parameters would you possibly use that way, other than
$directory? I can see a use for $default_version, but that's about it.

Would you rather add support for $default_version in the patch, for all
of the parameters just in case, for a different set of control
parameters, or rename the $directory macro?
My vote goes for adding $default_version only.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


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


Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-28 Thread Pavel Stehule
2014-02-27 20:10 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:
  Hello
 
  updated patch without timetz support

 Great, thanks.

 While testing, I noticed something strange regarding numeric timezone
 specification.  Basically the way any particular value is handled is
 underspecified, or maybe just completely wacko.  Consider the attached
 function, which will try to construct a timestamptz value with all
 possible values for timezone in the -1000 to 1000 range, ignoring those
 that cause errors for whatever reason, and then subtract the obtained
 timestamptz from the base value.  The output is also attached.

 First of all you can see that there are plenty of values for which the
 constructor will simply fail.

 Second, the way signs are considered or not seems arbitrary.  Note that
 if you say either '-2' or '2', you will end up with the same timestamptz
 value.  But at -16 the value jumps to the opposite sign.

 For negative values, this continues up to -99; but at -100, apparently
 it stops considering the value a number of hours, and it considers
 hours-and-minutes with a missing colon separator.  Which works up to
 -159; at -160 and up to -167 it uses a different interpretation again
 (not sure what).  Then values -168 and below are not valid; -200 is
 valid again (2 hours)  For the rest of the interval,

 For positive values, apparently there's no funny interpretation; the
 number is taken to be a number of hours up to 167.  There's no valid
 value above that.  However, if you prepend a plus sign, the result is
 completely different and there are valid values up to +1559.  The funny
 behavior in +160 through +167 is there too.

 Not sure what to make of this; certainly it's not my interest to fix it.
 However I wonder if we should really offer the capability to pass
 numeric timezone values.  Seems it'd be saner to allow just symbolic
 names, either abbreviations or full names.


I found a small issue. Routines for parsing time zone expects so time zone
starts with '+' or '-'. When this symbol is missing, then it use '-' as
default.

That is pretty stupid - probably it expects check in preprocessing

postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10, '-1');
make_timestamptz

 2014-12-10 12:10:10+01
(1 row)

postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10, '1');
make_timestamptz

 2014-12-10 12:10:10+01
(1 row)

postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10, '+1');
make_timestamptz

 2014-12-10 10:10:10+01
(1 row)

When I fix this, then make_timestamptz produce same results as timestamptz
input function.

CREATE OR REPLACE FUNCTION public.tryt1(integer)
 RETURNS TABLE (tz int, tm01 timestamptz, tm02 timestamptz, diff interval)
 LANGUAGE plpgsql
AS $function$
declare
tz int;
begin
for tz in - $1 .. $1 loop
begin
tryt1.tz = tz;
tm01 := format('1987-02-14 12:25:00 %s%s', CASE WHEN tz  0 THEN
'+' ELSE '' END, tz)::timestamptz;
tm02 := make_timestamptz(1987, 2, 14, 12, 25, 00, CASE WHEN tz  0
THEN '+' ELSE '' END || tz::text);
diff := tm02 - tm01;
return next;
exception when others then null;
  raise notice 'error %s: %', SQLERRM, tz;
end;
end loop;
end;
$function$;

A allowed (or disallowed) numeric zones are little bit strange - but it is
different issue not related to this patch.

so still I prefer to allow numeric time zones.

What I can:

a) disallow numeric only timezone without prefix + or -

or

b) add + prefix to time zone, when number is possitive.


I prefer @a.

What do you thinking?

Regards

Pavel



 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



Re: [HACKERS] Another possible corruption bug in 9.3.2 or possibly a known MultiXact problem?

2014-02-28 Thread Greg Stark
On 28 Feb 2014 06:19, Andres Freund and...@2ndquadrant.com wrote:

 On 2014-02-27 23:41:08 +, Greg Stark wrote:
  Though I notice something I can't understand here.
 
  After activating the new clone subsequent attempts to select rows from
  the page bump the LSN, presumably due to touching hint bits (since the
  prune xid hasn't changed). But the checksum hasn't changed even after
  running CHECKPOINT.

 Are you running with full_page_writes=off?

No

 Only delete and update do a PageSetPrunable(), so prune_xid not being
 changed doesn't say much...

  How is it possible for the LSN to get updated without changing the
checksum?

 Generally the LSN is computed when writing, not when a buffer is
 modified, so that's not particularly surprising. It'd be interesting to
 see what the records are that end on those LSNs.

The checksum you mean? But that's why I ran checkpoint.

 It'd probably nice to add the capability to dump records that end in a
 particular location to pg_xlogdump...

I have this crazy idea about combining xlogdump and pg_receivexlog to
archive all xlog to a postgres database for querying.


Re: [HACKERS] Another possible corruption bug in 9.3.2 or possibly a known MultiXact problem?

2014-02-28 Thread Andres Freund
On 2014-02-28 10:44:14 +, Greg Stark wrote:
 On 28 Feb 2014 06:19, Andres Freund and...@2ndquadrant.com wrote:
  Generally the LSN is computed when writing, not when a buffer is
  modified, so that's not particularly surprising. It'd be interesting to
  see what the records are that end on those LSNs.
 
 The checksum you mean? But that's why I ran checkpoint.

The checksum is computed in a copy of the buffer, not in the
original. C.f. PageSetChecksumCopy().

Greetings,

Andres Freund

-- 
 Andres Freund 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] pgbench help message fix

2014-02-28 Thread Fabien COELHO



On 2/27/14, 12:53 PM, Fabien COELHO wrote:

A very minor fix to pgbench --help which is missing the expected
argument for the -t option.


done


Thanks!

--
Fabien.


--
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 and nested hstore

2014-02-28 Thread Thom Brown
On 28 February 2014 08:12, Andres Freund and...@2ndquadrant.com wrote:

 On 2014-02-27 15:06:33 -0500, Andrew Dunstan wrote:
  You realize that this API dates from 9.3 and has been used in numerous
  extensions, right? So the names are pretty well fixed, for good or ill.

 Sure. Doesn't prevent adding a couple more comments tho. I've only
 noticed this because I opened the header as a reference when reading
 your patch. Anyway, do something based on that feedback or not, your
 choice ;)


Can I ask why I can do this:

SELECT review % 'product'-'title' as product_title
FROM rating;

But I can't do this:

SELECT review-'product'-'title' as product_title
FROM rating;

ERROR:  operator does not exist: hstore - hstore
LINE 1: explain select review - 'product'::hstore -'title' as prod...

Yet I can do this:

SELECT review::json-'product'-'title' as product_title
FROM rating;

Yours oblivious,
-- 
Thom


Re: [HACKERS] Hot standby doesn't come up on some situation.

2014-02-28 Thread Heikki Linnakangas

On 02/28/2014 11:51 AM, Kyotaro HORIGUCHI wrote:

Hello,

2014/02/28 18:07 Andres Freund :


On 2014-02-28 17:55:21 +0900, Kyotaro HORIGUCHI wrote:

The recovery process stays on 'incosistent' state forever when
the server has crashed before any wal record is inserted after
the last checkpoint.



# killall postgres
# rm -rf $PGDATA/*
initdb
pg_ctl start -w
sleep 1
pg_ctl stop -m i
cat  $PGDATA/recovery.conf EOF
standby_mode = 'on'
primary_conninfo = 'host=localhost port= user=repuser

application_name=pm01 keepalives_idle=60 keepalives_interval=5
keepalives_count=5'

#restore_command = '/bin/true'
recovery_target_timeline = 'latest'
EOF
cat  $PGDATA/postgresql.conf EOF
#log_min_messages = debug5
hot_standby = on
EOF
pg_ctl start


Uh. So, if I understand correctly, what you did is to convert a normal
live pg, into a replica that doesn't have a upstream node, right?


Yes, but the same stuation could be made by restarting crashed secondary.


Yeah.


I have no idea about the scenario on whitch this behavior was regarded as
undesirable but anyway I think that the secondry should start accepting
client just after crash recovery is completed.


Agreed, this is a bug.

I don't think your patch is the right fix for this though. Setting 
minRecoveryPoint to EndRecPtr is the right thing to do; EndRecPtr points 
to the end of the last read and replayed record. What's wrong in this 
case is lastReplayedEndRecptr. At the beginning of recovery, it's 
initialized to the REDO point, but with a shutdown checkpoint, that's 
not quite right. When starting from a shutdown checkpoint, REDO points 
to the beginning of the shutdown record, but we've already effectively 
replayed it. The next record we replay is the one after the checkpoint.


To see that, I added some elog(LOG) calls:

~/pgsql.93stable$ bin/postmaster -D data
LOG:  database system was shut down at 2014-02-28 14:06:18 EET
LOG:  ReadCheckpointRecord: 0/16479C98
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
^CLOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  INSERT @ 0/16479D00: prev 0/16479C98; xid 0; len 72: XLOG - 
checkpoint: redo 0/16479D00; tli 1; prev tli 1; fpw true; xid 0/793393; 
oid 24988; multi 655288; offset 1356722; oldest xid 687 in DB 1; oldest 
multi 1 in DB 1; oldest running xid 0; shutdown

LOG:  xlog flush request 0/16479D68; write 0/0; flush 0/0
LOG:  database system is shut down
~/pgsql.93stable$ bin/postmaster -D data
LOG:  database system was shut down at 2014-02-28 14:06:23 EET
LOG:  ReadCheckpointRecord: 0/16479D00
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
Killed

At this point, the last record is the shutdown checkpoint, beginning at 
16479D00, and the server has been killed (immediate shutdown).


~/pgsql.93stable$ cp recovery.conf data/recovery.conf
~/pgsql.93stable$ bin/postmaster -D data
LOG:  database system was interrupted; last known up at 2014-02-28 
14:06:29 EET

LOG:  entering standby mode
LOG:  ReadCheckpointRecord: 0/16479D00
LOG:  database system was not properly shut down; automatic recovery in 
progress

LOG:  record with zero length at 0/16479D68
LOG:  reached end of WAL in pg_xlog, entering archive recovery
LOG:  EndRecPtr: 0/16479D68 lastReplayedEndRecPtr: 0/16479D00
FATAL:  could not connect to the primary server: could not connect to 
server: Connection refused

...

Recovery starts from the checkpoint record, but lastReplayedEndRecPtr is 
set to the *beginning* of the checkpoint record, even though the 
checkpoint record has already been effectively replayed, by the feat of 
starting recovery from it. EndRecPtr correctly points to the end of the 
checkpoint record. Because of the incorrect lastReplayedEndRecPtr value, 
the CheckRecoveryConsistency() call concludes that it's not consistent.


I believe the attached fix is the right way to fix this.

- Heikki
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 7399fd4..a36354f 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -5457,13 +5457,17 @@ StartupXLOG(void)
 
 		/*
 		 * Initialize shared variables for tracking progress of WAL replay,
-		 * as if we had just replayed the record before the REDO location.
+		 * as if we had just replayed the record before the REDO location
+		 * (or the checkpoint record itself, if it's a shutdown checkpoint).
 		 */
 		SpinLockAcquire(xlogctl-info_lck);
-		xlogctl-replayEndRecPtr = checkPoint.redo;
+		if (checkPoint.redo  RecPtr)
+			xlogctl-replayEndRecPtr = checkPoint.redo;
+		else
+			xlogctl-replayEndRecPtr = EndRecPtr;
 		xlogctl-replayEndTLI = ThisTimeLineID;
-		xlogctl-lastReplayedEndRecPtr = checkPoint.redo;
-		xlogctl-lastReplayedTLI = ThisTimeLineID;
+		xlogctl-lastReplayedEndRecPtr = xlogctl-replayEndRecPtr;
+		xlogctl-lastReplayedTLI = xlogctl-replayEndTLI;
 		

Re: [HACKERS] [PATCH] Store Extension Options

2014-02-28 Thread Fabrízio de Royes Mello
On Fri, Feb 28, 2014 at 5:08 AM, Abhijit Menon-Sen a...@2ndquadrant.com
wrote:

 Hi Fabrízio.

 Here are a few comments based on a quick look at your updated patch.

 At 2014-02-13 22:44:56 -0200, fabriziome...@gmail.com wrote:
 
  diff --git a/doc/src/sgml/ref/alter_index.sgml
b/doc/src/sgml/ref/alter_index.sgml
  index d210077..5e9ee9d 100644
  --- a/doc/src/sgml/ref/alter_index.sgml
  +++ b/doc/src/sgml/ref/alter_index.sgml
  @@ -82,6 +82,14 @@ ALTER INDEX [ IF EXISTS ] replaceable
class=PARAMETERname/replaceable RESE
 xref linkend=SQL-REINDEX
 to get the desired effects.
/para
  + note
  +   para
  + A custom name can be used as namespace to define a storage
parameter.
  + Storage option pattern: namespace.option=value
  + (namespace=custom name, option=option name and value=option
value).
  + See example bellow.
  +   /para
  + /note
   /listitem
  /varlistentry

 I was slightly confused by the wording here. I think it would be better
 to say something like Custom storage parameters are of the form
 namespace.option and leave it at that.

 (Aside: s/bellow/below/)


You are correct... my english isn't so good... sorry!

Fixed.


  @@ -202,6 +210,17 @@ ALTER INDEX distributors SET (fillfactor = 75);
   REINDEX INDEX distributors;
   /programlisting/para
 
  +  para
  +   To set a custom storage parameter:
  +programlisting
  +ALTER INDEX distributors
  +  SET (bdr.do_replicate=true);
  +/programlisting
  +   (bdr=custom name, do_replicate=option name and
  +   true=option value)
  +/para
  +
  +
/refsect1

 It might be best to avoid using bdr.do_replicate in the example, since
 it's still a moving target. It might be better to use a generic example
 like somenamespace.optionname=true, in which case the explanation isn't
 needed either.


Fixed.


 The patch applies and builds fine, the tests pass, and the code looks
 OK to me. I don't have a strong opinion on validating custom reloption
 values through hooks as discussed earlier in the thread, but the simple
 version (i.e. your latest patch) seems at least a useful starting point.


Thanks for your review.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog sobre TI: http://fabriziomello.blogspot.com
 Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml
index 94a7af0..f0932cb 100644
--- a/doc/src/sgml/ref/alter_index.sgml
+++ b/doc/src/sgml/ref/alter_index.sgml
@@ -82,6 +82,12 @@ ALTER INDEX [ IF EXISTS ] replaceable class=PARAMETERname/replaceable RESE
   xref linkend=SQL-REINDEX
   to get the desired effects.
  /para
+ note
+   para
+ Custom storage parameters are of the form namespace.option. See
+ example below.
+   /para
+ /note
 /listitem
/varlistentry
 
@@ -202,6 +208,12 @@ ALTER INDEX distributors SET (fillfactor = 75);
 REINDEX INDEX distributors;
 /programlisting/para
 
+  para
+   To set a custom storage parameter:
+programlisting
+ALTER INDEX distributors
+  SET (somenamespace.optionname=true);
+
  /refsect1
 
  refsect1
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 2b02e66..1c36f4b 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -213,6 +213,14 @@ ALTER TABLE [ IF EXISTS ] replaceable class=PARAMETERname/replaceable
   of statistics by the productnamePostgreSQL/productname query
   planner, refer to xref linkend=planner-stats.
  /para
+
+ note
+  para
+Custom storage parameters are of the form namespace.option. See
+example below.
+  /para
+ /note
+
 /listitem
/varlistentry
 
@@ -476,6 +484,10 @@ ALTER TABLE [ IF EXISTS ] replaceable class=PARAMETERname/replaceable
commandALTER TABLE/ does not treat literalOIDS/ as a
storage parameter.  Instead use the literalSET WITH OIDS/
and literalSET WITHOUT OIDS/ forms to change OID status.
+   A custom name can be used as namespace to define a storage parameter.
+   Storage option pattern: namespace.option=value
+   (namespace=custom name, option=option name and value=option value).
+   See example bellow.
   /para
  /note
 /listitem
@@ -1112,6 +1124,20 @@ ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
 ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
 /programlisting/para
 
+  para
+   To set a custom per-attribute option:
+programlisting
+ALTER TABLE distributors
+  ALTER COLUMN dist_id SET (somenamespace.optionname=true);
+/programlisting
+/para
+
+  para
+   To set a custom storage parameter:
+programlisting
+ALTER TABLE distributors
+  SET (somenamespace.optionname=true);
+
  /refsect1
 
  refsect1
diff --git 

Re: [HACKERS] extension_control_path

2014-02-28 Thread Stephen Frost
* Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote:
 Stephen Frost sfr...@snowman.net writes:
 # hstore extension
 comment = 'data type for storing sets of (key, value) pairs'
 default_version = '1.3'
 directory = 'local/hstore-new'
 module_pathname = '$directory/hstore'
 relocatable = true
 
  Interesting idea.  I'm a *little* concerned that re-useing '$directory'
  there might confuse people into thking that any values in the control
  file could be substituted in a similar way though.  Would there really
  be much difference between that and '$ctldir' or something?
 
 Well, using $directory makes the feature auto-documented and very easy
 to read even without the reference documentation handy. It's also a very
 known way to setup things in .ini files.
 
 Now, what other parameters would you possibly use that way, other than
 $directory? I can see a use for $default_version, but that's about it.

Yeah, default_version was the other one that looked like it might be
possible to include, but folks might decide to try and use 'comment' in
that way too.  Basically, there's a chance that they'd want to use any
string in there.

 Would you rather add support for $default_version in the patch, for all
 of the parameters just in case, for a different set of control
 parameters, or rename the $directory macro?
 My vote goes for adding $default_version only.

It certainly seems to me that people will natuarlly want to use
$default_version.  I'm trying to figure out if that's actually something
we should encourage or what.  Of course, including it and/or the
PG_MODULE_MAGIC that I mentioned in my previous email run afoul of our
current 'upgrade-all-the-things' strategy when changing major versions.
I continue to wonder if that was really the best idea.

Thanks,

Stephen


signature.asc
Description: Digital signature


[HACKERS] Equivalence Rules

2014-02-28 Thread Ali Piroozi
Hi
Which equivalence rule from those are listed in
email's attachment are implemented in postgresql?
where are them?
Thanks


equivalenceRules.pdf
Description: Adobe PDF document

-- 
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] extension_control_path

2014-02-28 Thread Stephen Frost
* Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote:
 Peter Eisentraut pete...@gmx.net writes:
  I think we should get rid of the module_pathname business, and
  extensions' SQL files should just refer to the base file name and rely
  on the dynamic library path to find the files.  What would we lose if we
  did that?
 
 Control over *which* mylib.so file gets loaded for a specific sql
 script. That's the whole namespace issue Stephen is worried about.

Indeed.

 If you're testing the new version of an extension before installing it
 properly, then you will have the current and the new versions of the
 .so, with the exact same name, at different places.

Hrm.  This makes me wonder if there was a way we could check a .so
against the definition of what it should be in the control file.  As
in, somehow include the extension name and version in the
PG_MODULE_MAGIC.  That could be good on a couple of levels..

 Note that when using base file name only, then you could also have a
 clash with a dynamic library of the same name installed on the system,
 even if not made to be loaded by PostgreSQL.

Such as addressing this- perhaps with a GUC that says only load .so's
that have a PG_MODULE_MAGIC and whose extension names/versions match
what is in the associated control file.

 Some extensions are using way too generic names. Hint: prefix.so.

Agreed.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Thom Brown
On 28 February 2014 13:01, Andrew Dunstan and...@dunslane.net wrote:


 On 02/28/2014 07:19 AM, Thom Brown wrote:

  On 28 February 2014 08:12, Andres Freund and...@2ndquadrant.com mailto:
 and...@2ndquadrant.com wrote:

 On 2014-02-27 15:06:33 -0500, Andrew Dunstan wrote:
  You realize that this API dates from 9.3 and has been used in
 numerous
  extensions, right? So the names are pretty well fixed, for good
 or ill.

 Sure. Doesn't prevent adding a couple more comments tho. I've only
 noticed this because I opened the header as a reference when reading
 your patch. Anyway, do something based on that feedback or not, your
 choice ;)


 Can I ask why I can do this:

 SELECT review % 'product'-'title' as product_title
 FROM rating;

 But I can't do this:

 SELECT review-'product'-'title' as product_title
 FROM rating;

 ERROR:  operator does not exist: hstore - hstore
 LINE 1: explain select review - 'product'::hstore -'title' as prod...

 Yet I can do this:

 SELECT review::json-'product'-'title' as product_title
 FROM rating;



 I don't think this complaint has anything to do with the text you quoted,
 so you've kinda hijacked the thread slightly.


Apologies.  I'd just given the patches my first test-drive and replied to
the last message on the thread.


 But anyway, I think we've seen enough of these to conclude that the casts
 from hstore to jsonb and back should not be implicit. I am fairly confident
 that changing that would fix your complaint and the similar one that Peter
 Geoghegan had.


Thanks.
-- 
Thom


Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Andrew Dunstan


On 02/28/2014 07:19 AM, Thom Brown wrote:
On 28 February 2014 08:12, Andres Freund and...@2ndquadrant.com 
mailto:and...@2ndquadrant.com wrote:


On 2014-02-27 15:06:33 -0500, Andrew Dunstan wrote:
 You realize that this API dates from 9.3 and has been used in
numerous
 extensions, right? So the names are pretty well fixed, for good
or ill.

Sure. Doesn't prevent adding a couple more comments tho. I've only
noticed this because I opened the header as a reference when reading
your patch. Anyway, do something based on that feedback or not, your
choice ;)


Can I ask why I can do this:

SELECT review % 'product'-'title' as product_title
FROM rating;

But I can't do this:

SELECT review-'product'-'title' as product_title
FROM rating;

ERROR:  operator does not exist: hstore - hstore
LINE 1: explain select review - 'product'::hstore -'title' as prod...

Yet I can do this:

SELECT review::json-'product'-'title' as product_title
FROM rating;




I don't think this complaint has anything to do with the text you 
quoted, so you've kinda hijacked the thread slightly.


But anyway, I think we've seen enough of these to conclude that the 
casts from hstore to jsonb and back should not be implicit. I am fairly 
confident that changing that would fix your complaint and the similar 
one that Peter Geoghegan had.


cheers

andrew


--
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] Request improve pg_stat_statements module

2014-02-28 Thread Michael Paquier
Thanks for your patch!

On Fri, Feb 28, 2014 at 4:18 PM,  pgsql...@postgresql.kr wrote:
 I patched to add one column in pg_stat_statements module.
 and sent to author but
 I need a last time of query, because I want to analyse order by recent time.
Hm... I am not sure that this brings much to pg_stat_statements, which
is interested to gather normalized information about the queries run
on the server. For example, things like calculating the average time
of a query by using total_time/calls or even the total time to guess
where is an application bottleneck is interesting on a busy system,
while the latest timestamp is not really an information that can be
used for query tuning. Getting the latest timestamp when a query has
run is particularly not interesting on OLTP-like applications where
many short transactions are running. It is more interesting to
classify query results by either calls, total_time or the combination
of both IMO.

 this patch code below, review please and
 I wish to apply at next version.
When submitting patches, there are a couple of things to know, they
are summarized here.
https://wiki.postgresql.org/wiki/Submitting_a_Patch

One of the things that would be interesting for future contributions
in your case is knowing the code convention used in Postgres code (see
comments below for more details):
http://www.postgresql.org/docs/devel/static/source.html

 -   if (tupdesc-natts == PG_STAT_STATEMENTS_COLS_V1_0)
 +   if (tupdesc-natts == PG_STAT_STATEMENTS_COLS_V1_0){
 sql_supports_v1_1_counters = false;
 +   sql_supports_v1_2_counters = false;
 +   }
This is incorrect style, please create a new like for a bracket, like that:
if (cond)
{
blabla;
blabla2;
}

 per_query_ctx = rsinfo-econtext-ecxt_per_query_memory;
 oldcontext = MemoryContextSwitchTo(per_query_ctx);
 @@ -1185,8 +1195,15 @@
 values[i++] = Float8GetDatumFast(tmp.blk_read_time);
 values[i++] = Float8GetDatumFast(tmp.blk_write_time);
 }
 +   // last_executed_timestamp
Please do not use double-slashes for comments, write them instead like that:
/* my comment is here */

Finally be sure to attach a patch to an email and avoid to simply
copy/paste the content of the patch in your email. This is more
user-friendly :) Context diffs are recommended as well.

Regards,
-- 
Michael


-- 
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] GiST support for inet datatypes

2014-02-28 Thread Greg Stark
On Thu, Feb 27, 2014 at 5:30 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Indeed.  The root of the problem here is that we've never really thought
 about changing a type's default opclass before.  I don't think that a
 two-line change in pg_dump fixes all the issues this will bring up.

I think we did actually do this once. When you replaced rtree with
gist as the default opclass for the rtree method. IIRC you did it by
making rtree a synonym for gist and since the opclass wasn't
specified the default gist opclass kicked in automatically.

-- 
greg


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


Re: Fwd: [HACKERS] patch: make_timestamp function

2014-02-28 Thread Alvaro Herrera
Pavel Stehule escribió:

 so still I prefer to allow numeric time zones.
 
 What I can:
 
 a) disallow numeric only timezone without prefix + or -
 
 or
 
 b) add + prefix to time zone, when number is possitive.
 
 I prefer @a.

I can live with (a) too.  But I wonder if we should restrict the allowed
tz even further, for example to say that there must always be either 2
digits (no colon) or 4 digits, with or without a colon.

-- 
Álvaro Herrerahttp://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] GSoC 2014 - mentors, students and admins

2014-02-28 Thread Greg Stark
On Tue, Jan 28, 2014 at 5:34 PM, Thom Brown t...@linux.com wrote:
 Who would be up for mentoring this year?  And are there any project
 ideas folk would like to suggest?

I mentored in the past and felt I didn't do a very good job because I
didn't really understand the project the student was working on.

There's precisely one project that I feel I would be competent to
mentor at this point. Making hash indexes WAL recoverable. This is
something that's easy to define the scope of and easy to determine if
the student is on track and easy to measure when finished. It's
something where as far as I can tell all the mentor work will be
purely technical advice.

Also it's something the project really really needs and is perfectly
sized for a GSOC project IMHO. Also it's a great project for a student
who might be interested in working on Postgres in the future since it
requires learning all our idiosyncratic build and source conventions
but doesn't require huge or controversial architectural changes.

I fear a number of items in the Wiki seem unrealistically large
projects for GSOC IMNSHO.

-- 
greg


-- 
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 and nested hstore

2014-02-28 Thread Stephen Frost
* Peter Geoghegan (p...@heroku.com) wrote:
 On Thu, Feb 27, 2014 at 8:07 PM, Peter Geoghegan p...@heroku.com wrote:
  I'm not advocating authoring two extensions. I am tentatively
  suggesting that we look at one extension for everything. That may well
  be the least worst thing.
 
 (Not that it's clear that you imagined I was, but I note it all the same).

Thanks for that clarification- it was useful (for me anyway).  For my
2c, while I agree that it would work, I'd still rather see this get into
core for reasons mentioned elsewhere but which I'll echo here- JSON has
become the de-facto data interexchange format on a rather massive scale-
it's become what XML was trying to be, in many ways by being simpler.

While I agree that the comparison to FTS isn't entirely fair, I also
feel that we should still be considering adding new types to core and
not try to push everything out as extensions.  To add on to that- I feel
we still have a ways to go before our extension support will be really
*good* (which I certainly hope it to be some day) and I'd rather we not
force that on to the mobs of installations out there who will want
jsonb.

Thanks again,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Robert Haas
On Thu, Feb 27, 2014 at 8:55 PM, Christophe Pettus x...@thebuild.com wrote:
 On Feb 27, 2014, at 5:31 PM, Peter Geoghegan p...@heroku.com wrote:
 Now, it's confusing that it has to go through hstore, perhaps, but
 that's hardly all that bad in and of itself.

 Yes, it is.  It strikes me as irrational to have jsonb depend on hstore.  
 Let's be honest with ourselves: if we were starting over, we wouldn't start 
 by creating our own proprietary hierarchical type and then making the 
 hierarchical type everyone else uses depend on it.  hstore exists because 
 json didn't.  But json does now, and we shouldn't create a jsonb dependency 
 on hstore.

Right.  I think this is one of the smartest things that anyone has
said on this thread.  I don't have any objection to the idea of
enhancing hstore to support hierarchical data; I completely understand
the appeal of such a change.  Nor do I have any objection to the idea
of a binary-json type in core (or out of core); there are obvious uses
for such a thing.

But what's happened here is not the sum of those two admirable
proposals.  hstore has been augmented not only to support hierarchical
data but also with a notion of typed data that matches that of JSON
(except that I think the hstore and jsonb patches may have slightly
different notions as to what constitutes a valid number).  The
internal format for jsonb has been contrived to match the
upward-compatible format designed for JSON.  And thus jsonb depends on
hstore for the functionality that it isn't able to provide for itself.

Taken individually, none of those decisions seem crazy, but taken
together it's pretty weird.  Instead of inventing a new type (jsonb)
designed from the ground up to do what we want, we're, well, we're
doing what Christophe says: creating our own proprietary hierarchical
type and then making the hierarchical type everyone else uses depend
on it.  Described in those terms, it's hard for me to believe that
anyone here thinks that's not a strange thing to do.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] GiST support for inet datatypes

2014-02-28 Thread Emre Hasegeli
2014-02-27 18:15, Florian Pflug f...@phlo.org:
 It can be possible to update the new operator class in the new cluster
 as not default, before restore. After the restore, pg_upgrade can upgrade
 the btree_gist extension and reset the operator class as the default.
 pg_upgrade suggests to re-initdb the new cluster if it fails, anyway. Do
 you think it is a better solution? I think it will be more complicated
 to do in pg_dump when in binary upgrade mode.

 Maybe I'm missing something, but isn't the gist of the problem here that
 pg_dump won't explicitly state the operator class if it's the default?

No, the problem is pg_upgrade. We can even benefit from this behavior of
pg_dump, if we would like to remove the operator classes on btree_gist.
Because of that behavior; users, who would upgrade by dump and restore,
will upgrade to the better default operator class without noticing. I am
not sure not notifying is the best think to do, though.

The problem is that pg_dump --binary-upgrade dumps objects in the extension
on the old cluster, not just the CREATE EXTENSION statement. pg_upgrade
fails to restore them, if the new operator class already exists on the new
cluster as the default. It effects all users who use the extension, even
if they are not using the inet and cidr operator classes in it.


-- 
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 and nested hstore

2014-02-28 Thread Andrew Dunstan


On 02/28/2014 09:27 AM, Robert Haas wrote:

On Thu, Feb 27, 2014 at 8:55 PM, Christophe Pettus x...@thebuild.com wrote:

On Feb 27, 2014, at 5:31 PM, Peter Geoghegan p...@heroku.com wrote:

Now, it's confusing that it has to go through hstore, perhaps, but
that's hardly all that bad in and of itself.

Yes, it is.  It strikes me as irrational to have jsonb depend on hstore.  Let's 
be honest with ourselves: if we were starting over, we wouldn't start by 
creating our own proprietary hierarchical type and then making the hierarchical 
type everyone else uses depend on it.  hstore exists because json didn't.  But 
json does now, and we shouldn't create a jsonb dependency on hstore.

Right.  I think this is one of the smartest things that anyone has
said on this thread.  I don't have any objection to the idea of
enhancing hstore to support hierarchical data; I completely understand
the appeal of such a change.  Nor do I have any objection to the idea
of a binary-json type in core (or out of core); there are obvious uses
for such a thing.

But what's happened here is not the sum of those two admirable
proposals.  hstore has been augmented not only to support hierarchical
data but also with a notion of typed data that matches that of JSON
(except that I think the hstore and jsonb patches may have slightly
different notions as to what constitutes a valid number).  The
internal format for jsonb has been contrived to match the
upward-compatible format designed for JSON.  And thus jsonb depends on
hstore for the functionality that it isn't able to provide for itself.

Taken individually, none of those decisions seem crazy, but taken
together it's pretty weird.  Instead of inventing a new type (jsonb)
designed from the ground up to do what we want, we're, well, we're
doing what Christophe says: creating our own proprietary hierarchical
type and then making the hierarchical type everyone else uses depend
on it.  Described in those terms, it's hard for me to believe that
anyone here thinks that's not a strange thing to do.




Well, the time to make these sorts of decisions would have been back in 
November. The direction was clear then, if you were paying attention. 
But right from the time this came up at pgcon the idea was to leverage 
Oleg and Teodor's work. Nobody found it strange then. I'm rather 
confused about why it's suddenly strange now.


What you're essentially arguing for is the invention of TWO binary 
treeish things, without any argument I have yet seen advanced about why 
the first one we have is good enough for hstore but not good enough for 
jsonb.


Frankly, it looks to me like you have turned Christophe's argument on 
its head, or completely misunderstood his point.


cheers

andrew



--
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 and nested hstore

2014-02-28 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote:
 Taken individually, none of those decisions seem crazy, but taken
 together it's pretty weird.  Instead of inventing a new type (jsonb)
 designed from the ground up to do what we want, we're, well, we're
 doing what Christophe says: creating our own proprietary hierarchical
 type and then making the hierarchical type everyone else uses depend
 on it.  Described in those terms, it's hard for me to believe that
 anyone here thinks that's not a strange thing to do.

I was taking a slightly different perspective on it, though the devil is
almost certainly in the details.  I'll be the first to admit that I've
not looked in detail at the patch either and so I've been trying to
avoid commenting on implementation specifics, but I was seeing this from
the perspective that we are building a single hierarchical typed data
store and then providing two interfaces to it.  The way we're getting
there is a little awkward, in hindsight, and we'd like to have backwards
compatibility for one of the interfaces (and its on-disk storage), but
I'm not entirely sure that we'd actually end up in a different place
when we reach the end of this road.

Had we implemented jsonb first and then added hstore to it, would much
be different from the result we're getting here beyond the names of the
functions and the backwards-compatibility for the older on-disk format?
Are we really paying a high cost to support that older format?

The specific issues mentioned on this thread look more like bugs to be
addressed or additional operators which need to be implemented for
jsonb (imv, that should really be done for 9.4, but we have this
deadline looming...) along with perhaps dropping the implicit cast
between json and hstore (is there really a need for it..?).

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] [pgsql-advocacy] GSoC 2014 - mentors, students and admins

2014-02-28 Thread Karol Trzcionka
W dniu 27.02.2014 22:25, Thom Brown pisze:
 On 27 February 2014 21:08, David Fetter da...@fetter.org
 mailto:da...@fetter.org wrote:

 For MADlib, no.  Are you asking for mentors in general?


 Ah yes, I should clarify.  Yes, mentors in general.
In general I can help but I'm not sure if I'm not too fresh in pgsql ;)
However after GSOC as student I can try the another side.
Regards,
Karol


Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-28 Thread Noah Misch
On Fri, Feb 28, 2014 at 02:35:50PM +0900, Kyotaro HORIGUCHI wrote:
 At Thu, 27 Feb 2014 21:53:52 -0500, Noah Misch wrote
  On Thu, Feb 27, 2014 at 05:33:47PM -0500, Tom Lane wrote:
   I wonder whether we should consider adding a pass to flatten any nested
   appendrels after we're done creating them all.
  
  We did consider that upthread.  It's a valid option, but I remain more
  inclined to teach pull_up_subqueries() to preserve flatness just like
  expand_inherited_tables() will.
 
 Yes, the old dumped version of typ2 patch did so. It flattened
 appendrel tree for the query prpoerly. Let me hear the reson you
 prefer to do so.

Having reviewed my upthread reasoning for preferring one of those two
approaches over the other, it's a weak preference.  They have similar runtime
costs.  Putting the logic with the code that creates appendrels reduces the
number of code sites one must examine to reason about possible plan
structures.  We might not flatten RTE_RELATION appendrel parents exactly the
same way we flatten RTE_SUBQUERY appendrel parents.  I would tend to leave
inh=true for the former, for reasons explained in my notes on v7, but set
inh=false for the latter to save needless work.  On the other hand, a
flattening pass is less code overall and brings an attractive
uniformity-by-default to the area.

-- 
Noah Misch
EnterpriseDB http://www.enterprisedb.com


-- 
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: show relation and tuple infos of a lock to acquire

2014-02-28 Thread Amit Kapila
On Thu, Feb 27, 2014 at 4:14 PM, Christian Kruse
christ...@2ndquadrant.com wrote:
 Hi,

 On 25/02/14 16:11, Robert Haas wrote:
 On Mon, Feb 24, 2014 at 10:13 AM, Christian Kruse
 christ...@2ndquadrant.com wrote:
  To be honest, I don't like the idea of setting up this error context
  only for log_lock_wait messages. This sounds unnecessary complex to me
  and I think that in the few cases where this message doesn't add a
  value (and thus is useless) don't justify such complexity.

 Reading this over, I'm not sure I understand why this is a CONTEXT at
 all and not just a DETAIL for the particular error message that it's
 supposed to be decorating.  Generally CONTEXT should be used for
 information that will be relevant to all errors in a given code path,
 and DETAIL for extra information specific to a particular error.

 Because there is more than one scenario where this context is useful,
 not just log_lock_wait messages.

I think using this context in more scenario's can do harm for certain cases
as mentioned upthread.

However I think the other reason for not putting in Detail is that for other
cases when this message is displayed, it already display similar info in
message as below:

LOG:  process 4656 still waiting for ExclusiveLock on tuple (0,1) of relation 57
513 of database 12045 after 1046.000 ms

Also we might not have the appropriate info available at the place where
this message is generated unless we set global variable.

We have only information what LockTag has and for shared lock case it
doesn't have the info about tuple and relation.

 If we're going to stick with CONTEXT, we could rephrase it like this:

 CONTEXT: while attempting to lock tuple (1,2) in relation with OID 3456

 or when the relation name is known:

 CONTEXT: while attempting to lock tuple (1,2) in relation public.foo

 Accepted. Patch attached.


 I wouldn't be inclined to dump the whole tuple under any
 circumstances.  That could be a lot more data than what you want
 dumped in your log.  The PK could already be somewhat unreasonably
 large, but the whole tuple could be a lot more unreasonably large.

 Well, as I already stated: we don't. I copied the behavior we use in
 CHECK constraints (ExecBuildSlotValueDescription()).

I think now more people are of opinion that displaying whole tuple is
not useful. I believe it is good to go ahead by displaying just primary key
for this case and move ahead.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


-- 
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] GiST support for inet datatypes

2014-02-28 Thread Florian Pflug
On Feb28, 2014, at 15:45 , Emre Hasegeli e...@hasegeli.com wrote:
 The problem is that pg_dump --binary-upgrade dumps objects in the extension
 on the old cluster, not just the CREATE EXTENSION statement. pg_upgrade
 fails to restore them, if the new operator class already exists on the new
 cluster as the default. It effects all users who use the extension, even
 if they are not using the inet and cidr operator classes in it.

Hm, what if we put the new opclass into an extension of its own, say inet_gist,
instead of into core? 

Couldn't we then remove the inet support from the latest version of btree_gist
(the one we'd ship with 9.4)? People who don't use the old inet opclass could
then simply upgrade the extension after running pg_upgrade to get rid of the
old, broken version. People who *do* use the old inet opclass would need to
drop their indices before doing that, then install the extension inet_gist,
and finally re-create their indices. 

People who do nothing would continue to use the old inet opclass.

inet_gist's SQL script could check whether btree_gist has been upgrade, and
if not fail with an error like btree_gist must be upgraded to at least version
x.y before inet_gist can be installed. That would avoid failing with a rather
cryptic error later.

best regards,
Florian Pflug



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


Re: Custom Scan APIs (Re: [HACKERS] Custom Plan node)

2014-02-28 Thread Stephen Frost
* Kouhei Kaigai (kai...@ak.jp.nec.com) wrote:
 * Stephen Frost (sfr...@snowman.net) wrote:
  I don't see how you can be when there hasn't been any discussion that I've
  seen about how parallel query execution is going to change things for us.
  
 If parallel query execution changes whole of the structure of plan nodes,
 it will also affect to the interface of custom-scan because it is a thin-
 abstraction of plan-node. However, if parallel execution feature is
 implemented as one of new plan node in addition to existing one, I cannot
 imagine a scenario that affects to the structure of another plan node.

Let's just say that I have doubts that we'll be able to implement
parallel execution *without* changing the plan node interface in some
way which will require, hopefully minor, changes to all of the nodes.
The issue is that even a minor change would break the custom-scan API
and we'd immediately be in the boat of dealing with complaints regarding
backwards compatibility.  Perhaps we can hand-wave that, and we've had
some success changing hook APIs between major releases, but such changes
may also be in ways which wouldn't break in obvious ways or even
possibly be changes which have to be introduced into back-branches.
Parallel query is going to be brand-new real soon and it's reasonable to
think we'll need to make bug-fix changes to it after it's out which
might even involve changes to the API which is developed for it.

  The issue here is that we're going to be expected to maintain an interface
  once we provide it and so that isn't something we should be doing lightly.
  Particularly when it's as involved as this kind of change is with what's
  going on in the backend where we are nearly 100% sure to be changing things
  in the next release or two.
  
 FDW APIs are also revised several times in the recent releases. If we can
 design perfect interface from the beginning, it's best but usually hard
 to design.

Sure, but FDWs also have a *much* broader set of use-cases, in my view,
which is also why I was pushing to work on join-push-down to happen
there instead of having this kind of a hook interface, which I don't
think we'd want to directly expose as part of the 'official FDW API' as
it ends up putting all the work on the FDW with little aide, making it
terribly likely to end up with a bunch of duplciated code in the FDWs
from the backend to deal with it, particularly for individuals writing
FDWs who aren't familiar with what PG already has.

 Also, custom-scan interface is almost symmetric with existing plan node
 structures, so its stability is relatively high, I think.

Perhaps it will come to pass that parallel query execution doesn't
require any changes to the plan node structure, but that's not the horse
that I'd bet on at this point.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Joshua D. Drake


On 02/27/2014 11:02 PM, Christophe Pettus wrote:



On Feb 27, 2014, at 9:59 PM, Peter Geoghegan p...@heroku.com wrote:


I don't find that very reassuring.


Obviously, we have to try it, and that will decide it.


I don't understand why an extension is seen as not befitting
of a more important feature.


contrib/ is considered a secondary set of features; I routinely get pushback 
from clients about using hstore because it's not in core, and they are thus 
suspicious of it.  The educational project required to change that far exceeds 
any technical work we are talking about here..  There's a very large 
presentational difference between having a feature in contrib/ and in core, at 
the minimum, setting aside the technical issues (such as the 
extensions-calling-extensions problem).

We have an existence proof of this already: if there was absolutely no 
difference between having things being in contrib/ and being in core, full text 
search would still be in contrib/.


This is an old and currently false argument. It is true that once upon a 
time, contrib was a banished heart, weeping for the attention of a true 
prince. Now? Not so much. She is a full on passion flower with the 
princes of all the kingdoms wanting her attention.


Joshua D. Drake


--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
   a rose in the deeps of my heart. - W.B. Yeats


--
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] GSoC proposal

2014-02-28 Thread Florian Pflug
On Feb28, 2014, at 05:29 , Tan Tran tankimt...@gmail.com wrote:
 I'm applying for GSoC 2014 with Postgresql and would appreciate your comments
 on my proposal (attached).
 pg_gsoc2014_TanTran.pdf

First, please include your proposal as plain, inline text next time.
That makes it easier to quote the relevant parts when replying, and
also allows your mail to be indexed correctly by the mailing list
archive.

Regarding your proposal, I think you need to explain what exactly it
is you want to achieve in more detail.

 In particular, text and bytea are EXTERNAL by default, so that substring
 operations can seek straight to the exact slice (which is O(1)) instead
 of de-toasting the whole datum (which is O(file size)). Specifically,
 varlena.c’s text_substring(...) and bytea_substring(...) call
 DatumGetTextPSlice(...), which r!etrieves only the slice(s) at an
 easily-computed offset.!
 
 ...
 
 1. First, I will optimize array element retrieval and UTF-8 substring
 retrieval. Both are straightforward, as they involve calculating slice
 numbers and using similar code to above.!

I'm confused by that - text_substring *already* attempts to only fetch
the relevant slice in the case of UTF-8. It can't do so precisely - it
needs to use a conservative estimate - but I fail to see how that can
be avoided. Since UTF-8 maps a character to anything from 1 to 6 bytes,
you can't compute the byte offset of a given character index precisely.

You could store a constant number of *characters* per slice, instead of
a constant number of *bytes*, but due to the rather large worst-case of
6 bytes per character, that would increase the storage and access overhead
6 fold for languages which can largely be represented with 1 byte per
character. That's not going to go down well...

I haven't looked at how we currently handle arrays, but the problems
there are similar. For arrays containing variable-length types, you can't
compute the byte offset from the index. It's even worst than for varchar,
because the range of possible element lengths is much longer - one array
element might be only a few bytes long, while another may be 1kB or more...

 2. Second, I will implement a SPLITTER clause for the CREATE TYPE
 statement. As 1 proposes, one would define a type, for example:
   CREATE TYPE my_xml
 LIKE xml
 SPLITTER my_xml_splitter;

As far as I can tell, the idea is to allow a datatype to influence how
it's split into chunks for TOASTing so that functions can fetch only
the required slices more easily. To judge whether that is worthwhile or
not, you'd have to provide a concrete example of when such a facility
would be useful.

best regards,
Florian Pflug



-- 
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 and nested hstore

2014-02-28 Thread Merlin Moncure
On Fri, Feb 28, 2014 at 8:57 AM, Stephen Frost sfr...@snowman.net wrote:
 The specific issues mentioned on this thread look more like bugs to be
 addressed or additional operators which need to be implemented for
 jsonb (imv, that should really be done for 9.4, but we have this
 deadline looming...) along with perhaps dropping the implicit cast
 between json and hstore (is there really a need for it..?).

Bugs/bad behaviors should be addressed (which AFAICT are mostly if not
all due to implicit casts).  Missing operators OTOH are should not
hold up the patch, particuarly when the you have the option of an
explicit cast to hstore if you really want them.

Notwithstanding some of the commentary above, some of jsonb features
(in particular, the operators) are quite useful and should find
regular usage (json has them also, but jsonb removes the performance
penalty).  The upshot is that with the current patch you have to do a
lot of casting to get 100% feature coverage and that future
improvements to jsonb will remove the necessity of that.  Also the
hstore type will be required to do anything approximating the nosql
pattern.

I don't think the extension issue is a deal breaker either way.  While
I have a preference for extensions generally, this is nothing personal
to jsonb.  And if we can't come to a consensus on that point the patch
should be accepted on precedent (json being in core).

merlin


-- 
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] GiST support for inet datatypes

2014-02-28 Thread Emre Hasegeli
2014-02-28 17:30, Florian Pflug f...@phlo.org:
 Hm, what if we put the new opclass into an extension of its own, say 
 inet_gist,
 instead of into core?

It will work but I do not think it is better than adding it in core as
not default.


-- 
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 and nested hstore

2014-02-28 Thread Teodor Sigaev

+v.size += VARSIZE_ANY(v.numeric) +sizeof(JEntry) /* alignment */ ;

Why does + sizeof(JEntry) change anything about alignment? If it was
aligned before, adding a statically sized value doesn't give any new
guarantees about alignment?

Teodor, please comment.


Because numeric type will be copied into jsonb value. And we need to keep 
alignment inside jsonb value. The same is true for  nested jsonb (array or object).




+type = JsonbIteratorGet(it, v, false);
+if (type == WJB_VALUE)
+{
+first = false;
+putEscapedValue(out, v);
+}
+else
+{
+Assert(type == WJB_BEGIN_OBJECT || type ==
WJB_BEGIN_ARRAY);
+/*
+ * We need to rerun current switch() due to put
+ * in current place object which we just got
+ * from iterator.
+ */

due to put?



I think that's due to the author not being a native English speaker. I've tried
to improve it a bit.

Teodor, please comment if you like.


Pls, fix my English. I mean if we got first element of array/object and it isn't 
a scalar value we should do actions pointed by case 
WJB_BEGIN_OBJECT/WJB_BEGIN_ARRAY in the same switch without calling iterator.




Teodor, please examine and comment on all comments below this point.



+JsonbValue *
+findUncompressedJsonbValueByValue(char *buffer, uint32 flags,
+  uint32 *lowbound, JsonbValue *key)
+{

Functions like this *REALLY* need documentation for their
parameters. And of their actual purpose.


 What's actually the uncompressed bit here? Isn't it actually the
 contrary? This is navigating the compressed, non-tree form, no?

Functions returns value in  JsonbValue form (uncompressed, not just a pointer). 
For object it performs search by key and returns corresponding value, for array 
- returns matched value. If lowbound is not null then it will be set into 
array/object position of found value. And search will be started from *lowbound 
position in array/object. That allows some optimizations.


Buffer is a pointer to header of jsonb value. After pointer there is an array of 
JEntry and following list of values of array or key and values of object. This 
is internal representation for jsonb or hstore without varlena header.
Nested array/object have the same representation. Flags points desired search - 
in array or object. For example, If buffer contains array and flags has only 
JB_FLAG_OBJECT then function returns NULL.





+else if (flags  JB_FLAG_OBJECT  header)
+{
+JEntry   *array = (JEntry *) (buffer + sizeof(header));
+char   *data = (char *) (array + (header  JB_COUNT_MASK) * 2);
+uint32stopLow = lowbound ? *lowbound : 0,
+stopHigh = (header  JB_COUNT_MASK),
+stopMiddle;

I don't understand what the point of the lowbound logic could be here?
If a key hasn't been found, it hasn't been found? Maybe the idea is to
use it when testing containedness or somesuch? Wouldn't iterating over
the keyspace be a better idea for that case?
If we has keys (a,b,c,d,e,f,g) and need to search keys e and f, then for second 
search we could do in in subset of keys (f,g), we don't need to search in full 
set of keys. The idea was introduced in hstoreFindKey() in hstore V2.






+if (key-type != jbvString)
+return NULL;

That's not allowed, right?


Right. it should be an Assert or ERROR.




+/*
+ * Get i-th value of array or hash. if i  0 then it counts from
+ * the end of array/hash. Note: returns pointer to statically
+ * allocated JsonbValue.
+ */
+JsonbValue *
+getJsonbValue(char *buffer, uint32 flags, int32 i)
+{
+uint32header = *(uint32 *) buffer;
+static JsonbValue r;

Really? And why on earth would static allocation be a good idea? Specify
it on the caller's stack if need be. Or even return by value, today's
calling convention will just allocate that on the caller's stack without
copying.
Accessing static data isn't even faster.


Just to prevent multiple palloc(). Could be changed, I don't insist. I saw 
problems with a lot of small allocations but didn't see such problems with 
static allocations.





+if (JBE_ISSTRING(*e))
+{
+r.type = jbvString;
+r.string.val = data + JBE_OFF(*e);
+r.string.len = JBE_LEN(*e);
+r.size = sizeof(JEntry) + r.string.len;
+}
+else if (JBE_ISBOOL(*e))
+{
+r.type = jbvBool;
+r.boolean = (JBE_ISBOOL_TRUE(*e)) ? true : false;
+r.size = sizeof(JEntry);
+}
+else if (JBE_ISNUMERIC(*e))
+{
+r.type = jbvNumeric;
+r.numeric = (Numeric) (data + INTALIGN(JBE_OFF(*e)));
+
+r.size = 2 * sizeof(JEntry) + VARSIZE_ANY(r.numeric);
+}
+else if (JBE_ISNULL(*e))
+{
+r.type 

Re: [HACKERS] Equivalence Rules

2014-02-28 Thread David Johnston
Ali Piroozi wrote
 Hi
 Which equivalence rule from those are listed in
 email's attachment are implemented in postgresql?
 where are them?

What do you mean by where?

The various JOINS and UNION/INTERSECT/DIFFERENCE are all defined
capabilities.

SQL is not purely relational in nature so some of the mathematical rules may
not be exactly implemented as theory would dictate but largely they are
present.

I would suggesting providing more context as to why you are asking this as
people are more likely to provide help at this level of technical depth if
they know why; and can frame their answers more appropriately.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Equivalence-Rules-tp5794035p5794069.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


-- 
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 and nested hstore

2014-02-28 Thread Christophe Pettus

On Feb 28, 2014, at 6:27 AM, Robert Haas robertmh...@gmail.com wrote:

 Taken individually, none of those decisions seem crazy, but taken
 together it's pretty weird.  Instead of inventing a new type (jsonb)
 designed from the ground up to do what we want, we're, well, we're
 doing what Christophe says: creating our own proprietary hierarchical
 type and then making the hierarchical type everyone else uses depend
 on it.  Described in those terms, it's hard for me to believe that
 anyone here thinks that's not a strange thing to do.

A lot of it is that we're getting really tied up in knots about terminology.  
Because of the history of the project, it's being approached as jsonb depends 
on hstore2, rather than, We need a binary format, BSON won't cut it, but 
hstore2 is creating one, so let's use the same for both to avoid duplication of 
effort.

Put that last way, it's a more sensible decision.  My specific concern was 
Well, if you want binary json, install hstore is a very strange presentation 
to give to customers.  Many of the user-facing objections can be solved just by 
removing the implicit cast from jsonb to hstore, and the remaining operators 
(if they don't make it into this patch) can be added over time.

--
-- Christophe Pettus
   x...@thebuild.com



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


[HACKERS] proposal: new long psql parameter --on-error-stop

2014-02-28 Thread Pavel Stehule
Hello

for beginners is relative difficult to set psql variable from command line
and option

-v ON_ERROR_STOP=1 is little bit unclean.

and for any user it is not comfortable. So I propose a new psql option

--on-error-stop

It is clean, and it will be mentioned in psql --help.

Comments, ideas?

Regards

Pavel


Re: [HACKERS] [PATCH] Use MAP_HUGETLB where supported (v3)

2014-02-28 Thread Heikki Linnakangas

On 02/27/2014 09:34 AM, Christian Kruse wrote:

Hi,

On 26/02/14 13:13, Alvaro Herrera wrote:


There's one thing that rubs me the wrong way about all this
functionality, which is that we've named it huge TLB pages.  That is
wrong -- the TLB pages are not huge.  In fact, as far as I understand,
the TLB doesn't have pages at all.  It's the pages that are huge, but
those pages are not TLB pages, they are just memory pages.


I didn't think about this, yet, but you are totally right.


Since we haven't released any of this, should we discuss renaming it to
just huge pages?


Attached is a patch with the updated documentation (now uses
consistently huge pages) as well as a renamed GUC, consistent wording
(always use huge pages) as well as renamed variables.


Hmm, I wonder if that could now be misunderstood to have something to do 
with the PostgreSQL page size? Maybe add the word memory or operating 
system in the first sentence in the docs, like this: Enables/disables 
the use of huge memory pages.



   para
At present, this feature is supported only on Linux. The setting is
ignored on other systems when set to literaltry/literal.
productnamePostgreSQL/productname will
refuse to start when set to literalon/literal.
   /para


Is it clear enough that PostgreSQL will only refuse to start up when 
it's set to on, *if the feature's not supported on the platform*? 
Perhaps just leave that last sentence out. It's mentioned later that  
With literalon/literal, failure to use huge pages will prevent the 
server from starting up., that's probably enough.


- Heikki


--
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 and nested hstore

2014-02-28 Thread Josh Berkus
On 02/28/2014 07:39 AM, Joshua D. Drake wrote:
 
 This is an old and currently false argument. It is true that once upon a
 time, contrib was a banished heart, weeping for the attention of a true
 prince. Now? Not so much. She is a full on passion flower with the
 princes of all the kingdoms wanting her attention.

That's one of the more colorful metaphors ever posted on this list.  I
don't think we've had language like that since Hitoshi stopped being
active.  ;-)

-- 
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


Re: [HACKERS] proposal: new long psql parameter --on-error-stop

2014-02-28 Thread Tom Lane
Pavel Stehule pavel.steh...@gmail.com writes:
 for beginners is relative difficult to set psql variable from command line
 and option
 -v ON_ERROR_STOP=1 is little bit unclean.
 and for any user it is not comfortable. So I propose a new psql option
 --on-error-stop

That saves a whole three characters ... not sure it's really worth it,
especially since it will not work on older versions.

Also, it's not like this will save people from having to know about the
ON_ERROR_STOP variable, because there will still be plenty of contexts
where they need to know that (eg, inspecting the setting or changing it
mid-session).  So I don't buy the argument that this simplifies what
beginners need to learn.

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] proposal: new long psql parameter --on-error-stop

2014-02-28 Thread Pavel Stehule
2014-02-28 19:13 GMT+01:00 Tom Lane t...@sss.pgh.pa.us:

 Pavel Stehule pavel.steh...@gmail.com writes:
  for beginners is relative difficult to set psql variable from command
 line
  and option
  -v ON_ERROR_STOP=1 is little bit unclean.
  and for any user it is not comfortable. So I propose a new psql option
  --on-error-stop

 That saves a whole three characters ... not sure it's really worth it,
 especially since it will not work on older versions.

 Also, it's not like this will save people from having to know about the
 ON_ERROR_STOP variable, because there will still be plenty of contexts
 where they need to know that (eg, inspecting the setting or changing it
 mid-session).  So I don't buy the argument that this simplifies what
 beginners need to learn.


three chars is not important

important is a  placing in --help output

Regards

Pavel





 regards, tom lane



Re: [HACKERS] proposal: new long psql parameter --on-error-stop

2014-02-28 Thread Fabrízio de Royes Mello
On Fri, Feb 28, 2014 at 3:17 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:

 2014-02-28 19:13 GMT+01:00 Tom Lane t...@sss.pgh.pa.us:

 Pavel Stehule pavel.steh...@gmail.com writes:
  for beginners is relative difficult to set psql variable from command
line
  and option
  -v ON_ERROR_STOP=1 is little bit unclean.
  and for any user it is not comfortable. So I propose a new psql option
  --on-error-stop

 That saves a whole three characters ... not sure it's really worth it,
 especially since it will not work on older versions.

 Also, it's not like this will save people from having to know about the
 ON_ERROR_STOP variable, because there will still be plenty of contexts
 where they need to know that (eg, inspecting the setting or changing it
 mid-session).  So I don't buy the argument that this simplifies what
 beginners need to learn.


 three chars is not important

 important is a  placing in --help output


Well, then we just have to add more info to --help

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog sobre TI: http://fabriziomello.blogspot.com
 Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello


Re: [HACKERS] proposal: new long psql parameter --on-error-stop

2014-02-28 Thread Pavel Stehule
2014-02-28 19:25 GMT+01:00 Fabrízio de Royes Mello fabriziome...@gmail.com
:


 On Fri, Feb 28, 2014 at 3:17 PM, Pavel Stehule pavel.steh...@gmail.com
 wrote:
 
  2014-02-28 19:13 GMT+01:00 Tom Lane t...@sss.pgh.pa.us:
 
  Pavel Stehule pavel.steh...@gmail.com writes:
   for beginners is relative difficult to set psql variable from command
 line
   and option
   -v ON_ERROR_STOP=1 is little bit unclean.
   and for any user it is not comfortable. So I propose a new psql option
   --on-error-stop
 
  That saves a whole three characters ... not sure it's really worth it,
  especially since it will not work on older versions.
 
  Also, it's not like this will save people from having to know about the
  ON_ERROR_STOP variable, because there will still be plenty of contexts
  where they need to know that (eg, inspecting the setting or changing it
  mid-session).  So I don't buy the argument that this simplifies what
  beginners need to learn.
 
 
  three chars is not important
 
  important is a  placing in --help output
 

 Well, then we just have to add more info to --help


it can be solution

Pavel



 Regards,

 --
 Fabrízio de Royes Mello
 Consultoria/Coaching PostgreSQL
  Timbira: http://www.timbira.com.br
  Blog sobre TI: http://fabriziomello.blogspot.com
  Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
  Twitter: http://twitter.com/fabriziomello



Re: [HACKERS] patch: option --if-exists for pg_dump

2014-02-28 Thread Alvaro Herrera
Pavel Stehule escribió:

 It is irony, so this is death code - it is not used now. So I removed it
 from patch.
 
 Reduced, fixed patch attached + used tests

Nice, thanks.

Here's a new version in which I reworded some comments and docs, and
also inverted the sense of some if/else so that the oneliner case is
first, which makes it more readable IMHO.

However, I don't think this is behaving sanely in pg_dumpall.  AFAICT,
pg_dumpall does not pass --clean to pg_dump (in other words it only
emits DROP for the global objects, not the objects contained inside
databases), so passing --if-exists results in failures.  Therefore I
think the solution is to not pass --if-exists to pg_dump at all, i.e.
keep it internal to pg_dumpall.  But maybe I'm missing something.

I still find the code to inject IF EXISTS to the DROP commands ugly as
sin.  I would propose to stop storing the dropStmt in the archive
anymore; instead just store the object identity, which can later be used
to generate both DROP commands, with or without IF EXISTS, and the ALTER
OWNER commands.  However, that's a larger project and I don't think we
need to burden this patch with that.

Another point is that we could argue about whether specifying
--if-exists ought to imply --clean instead of erroring out.  There's no
backwards compatibility argument to be had; it's not like existing
scripts are going to suddenly start dropping objects that weren't
dropped before.

Other than the pg_dumpall issue, this patch seems ready.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 40c69f0..1f0d4de 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -145,7 +145,8 @@ PostgreSQL documentation
para
 Output commands to clean (drop)
 database objects prior to outputting the commands for creating them.
-(Restore might generate some harmless error messages, if any objects
+(Unless option--if-exists/ is also specified,
+restore might generate some harmless error messages, if any objects
 were not present in the destination database.)
/para
 
@@ -650,6 +651,17 @@ PostgreSQL documentation
  /varlistentry
 
  varlistentry
+  termoption--if-exists/option/term
+  listitem
+   para
+Use conditional commands (i.e. add an literalIF EXISTS/literal
+clause) when cleaning database objects.  This option is not valid
+unless option--clean/ is also specified.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption--disable-dollar-quoting//term
   listitem
para
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index f337939..fcf5f77 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -301,6 +301,17 @@ PostgreSQL documentation
  /varlistentry
 
  varlistentry
+  termoption--if-exists/option/term
+  listitem
+   para
+Use conditional commands (i.e. add an literalIF EXISTS/literal
+clause) to clean databases and other objects.  This option is not valid
+unless option--clean/ is also specified.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption--inserts/option/term
   listitem
para
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index cd60b25..4bc30ce 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -109,7 +109,8 @@
   listitem
para
 Clean (drop) database objects before recreating them.
-(This might generate some harmless error messages, if any objects
+(Unless option--if-exists/ is used,
+this might generate some harmless error messages, if any objects
 were not present in the destination database.)
/para
   /listitem
@@ -490,6 +491,17 @@
  /varlistentry
 
  varlistentry
+  termoption--if-exists/option/term
+  listitem
+   para
+Use conditional commands (i.e. add an literalIF EXISTS/literal
+clause) when cleaning database objects.  This option is not valid
+unless option--clean/ is also specified.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption--no-data-for-failed-tables/option/term
   listitem
para
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 6927968..83f7216 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -113,6 +113,7 @@ typedef struct _restoreOptions
 	char	   *superuser;		/* Username to use as superuser */
 	char	   *use_role;		/* Issue SET ROLE to this */
 	int			dropSchema;
+	int			if_exists;
 	const char *filename;
 	int			dataOnly;
 	int			schemaOnly;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c 

Re: [HACKERS] Equivalence Rules

2014-02-28 Thread Ali Piroozi
where means, which function or which part of source code.
I want to use that(function or part of source code), to produce the
equivalence
(based on equivalence rules in attachment) Relational Algebra
for a given SQL query(Relational Algebra).


Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Peter Geoghegan
On Fri, Feb 28, 2014 at 5:01 AM, Andrew Dunstan and...@dunslane.net wrote:
 But anyway, I think we've seen enough of these to conclude that the casts
 from hstore to jsonb and back should not be implicit. I am fairly confident
 that changing that would fix your complaint and the similar one that Peter
 Geoghegan had.

Yes, it will, but I think that that will create more problems than it
will solve (which is not to suggest that an implicit cast is the right
thing). That will require that any non-trivial usage of jsonb requires
copious casting, where nested hstore does not. The hstore module
hardly contains some nice extras that a minority of jsonb users will
be interested in. It contains among other basic things, operator
classes required to index jsonb. All of my examples will still not
work, plus a bunch of cases that currently do work reasonably well.
There'll just be a different error message.


-- 
Peter Geoghegan


-- 
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: new long psql parameter --on-error-stop

2014-02-28 Thread Alvaro Herrera
Fabrízio de Royes Mello escribió:
 On Fri, Feb 28, 2014 at 3:17 PM, Pavel Stehule pavel.steh...@gmail.com
 wrote:

  important is a  placing in --help output
 
 Well, then we just have to add more info to --help

I think psql could do with some lines for the possible options for
--pset (14) and --variable (13).  Not sure how to do that without having
it become too cumbersome while not hiding useful variables.

-- 
Álvaro Herrerahttp://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: new long psql parameter --on-error-stop

2014-02-28 Thread Fabrízio de Royes Mello
On Fri, Feb 28, 2014 at 3:44 PM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:

 Fabrízio de Royes Mello escribió:
  On Fri, Feb 28, 2014 at 3:17 PM, Pavel Stehule pavel.steh...@gmail.com
  wrote:

   important is a  placing in --help output
 
  Well, then we just have to add more info to --help

 I think psql could do with some lines for the possible options for
 --pset (14) and --variable (13).  Not sure how to do that without having
 it become too cumbersome while not hiding useful variables.


Maybe by adding something like --help-pset and --help-variables.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog sobre TI: http://fabriziomello.blogspot.com
 Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello


Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Tom Lane
Peter Geoghegan p...@heroku.com writes:
 On Fri, Feb 28, 2014 at 5:01 AM, Andrew Dunstan and...@dunslane.net wrote:
 But anyway, I think we've seen enough of these to conclude that the casts
 from hstore to jsonb and back should not be implicit. I am fairly confident
 that changing that would fix your complaint and the similar one that Peter
 Geoghegan had.

 Yes, it will, but I think that that will create more problems than it
 will solve (which is not to suggest that an implicit cast is the right
 thing). That will require that any non-trivial usage of jsonb requires
 copious casting, where nested hstore does not. The hstore module
 hardly contains some nice extras that a minority of jsonb users will
 be interested in. It contains among other basic things, operator
 classes required to index jsonb. All of my examples will still not
 work, plus a bunch of cases that currently do work reasonably well.
 There'll just be a different error message.

We should have learned by now that implicit casts are generally pretty
dangerous things.  I think putting in implicit casts as a band-aid for
missing functionality is a horrid idea that we'll regret for a long
time to come.  I gather from upthread comments that the patch currently
actually creates implicit casts in *both* directions?  That's doubly
horrid/dangerous.

The more I read in this thread, the more I think that jsonb simply
isn't ready.  We should put it off to 9.5 so that we can have a
complete implementation without so many rough edges.  I'm afraid that
if we ship it as-is, backwards compatibility considerations are going
to prevent us from filing down the rough edges in future.

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] patch: option --if-exists for pg_dump

2014-02-28 Thread Pavel Stehule
2014-02-28 19:31 GMT+01:00 Alvaro Herrera alvhe...@2ndquadrant.com:

 Pavel Stehule escribió:

  It is irony, so this is death code - it is not used now. So I removed it
  from patch.
 
  Reduced, fixed patch attached + used tests

 Nice, thanks.

 Here's a new version in which I reworded some comments and docs, and
 also inverted the sense of some if/else so that the oneliner case is
 first, which makes it more readable IMHO.


ok

thank you



 However, I don't think this is behaving sanely in pg_dumpall.  AFAICT,
 pg_dumpall does not pass --clean to pg_dump (in other words it only
 emits DROP for the global objects, not the objects contained inside
 databases), so passing --if-exists results in failures.  Therefore I
 think the solution is to not pass --if-exists to pg_dump at all, i.e.
 keep it internal to pg_dumpall.  But maybe I'm missing something.


I'll look on it tomorrow


 I still find the code to inject IF EXISTS to the DROP commands ugly as
 sin.  I would propose to stop storing the dropStmt in the archive
 anymore; instead just store the object identity, which can later be used
 to generate both DROP commands, with or without IF EXISTS, and the ALTER
 OWNER commands.  However, that's a larger project and I don't think we
 need to burden this patch with that.


there are more similar parts - and I am not sure if it is little bit heroic
task.



 Another point is that we could argue about whether specifying
 --if-exists ought to imply --clean instead of erroring out.  There's no
 backwards compatibility argument to be had; it's not like existing
 scripts are going to suddenly start dropping objects that weren't
 dropped before.


It is valid idea. I looked on any other options for and I don't known any
similar implication - so I prefer current implementation (no implication).
It is consistent with any other. I have not strong opinion about it - a
user comfort is against a clarity - but two clean option can be confusing
maybe.

Regards

Pavel



 Other than the pg_dumpall issue, this patch seems ready.

 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



Re: [HACKERS] proposal: new long psql parameter --on-error-stop

2014-02-28 Thread Pavel Stehule
2014-02-28 19:55 GMT+01:00 Fabrízio de Royes Mello fabriziome...@gmail.com
:


 On Fri, Feb 28, 2014 at 3:44 PM, Alvaro Herrera alvhe...@2ndquadrant.com
 wrote:
 
  Fabrízio de Royes Mello escribió:
   On Fri, Feb 28, 2014 at 3:17 PM, Pavel Stehule 
 pavel.steh...@gmail.com
   wrote:
 
important is a  placing in --help output
  
   Well, then we just have to add more info to --help
 
  I think psql could do with some lines for the possible options for
  --pset (14) and --variable (13).  Not sure how to do that without having
  it become too cumbersome while not hiding useful variables.
 

 Maybe by adding something like --help-pset and --help-variables.


I dislike it - I afraid so I know too much users, where --help-pset or
--help-variables is too high expert level. --help is a maximum, what they
can do - and you should not use a strange terminology like  variables.

Regards

Pavel




 Regards,

 --
 Fabrízio de Royes Mello
 Consultoria/Coaching PostgreSQL
  Timbira: http://www.timbira.com.br
  Blog sobre TI: http://fabriziomello.blogspot.com
  Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
  Twitter: http://twitter.com/fabriziomello



Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Josh Berkus
On 02/28/2014 06:27 AM, Robert Haas wrote:
 Taken individually, none of those decisions seem crazy, but taken
 together it's pretty weird.  Instead of inventing a new type (jsonb)
 designed from the ground up to do what we want, we're, well, we're
 doing what Christophe says: creating our own proprietary hierarchical
 type and then making the hierarchical type everyone else uses depend
 on it.  Described in those terms, it's hard for me to believe that
 anyone here thinks that's not a strange thing to do.

It certainly seems like a strange thing to do to *me*.  However, Oleg
and Teodor were doing the heavy lifting on the heirarchical type -- we
wouldn't even be talking about jsonb without it -- and they were very
negative to JSON.  As with many things, this reminds me of a story.

When the BART subway system was being built for the Bay Area in 1970,
the tunnel was planned to go straight under a particular hardware store
in Berkeley.  The hardware store owner was convinced that the
construction would destroy his building and his business, and hit the
state with lawsuit after lawsuit to stop the construction.  Eventually,
CALtrans caved and added a curve in that section of the BART tunnel to
go around the location of the hardware store.  Forty years later, the
hardware store owner is dead, the hardware store is gone (was gone, in
fact, by 1978), but the curve is still there.  And that curve forces
BART trains to slow down by 25mph in a spot which is fairly central to
the whole BART system, thus reducing the overall max capacity of the
entire subway system by 10-15%, and thus making thousands of people a
day late for work for the past 40 years.

I think Robert and Christophe are right: we're building a Berkeley BART
Curve.  I think there's two courses of action from here which make sense:

A) We move *all* of the important HStore libraries and operators into
core, and make the hstore extension of them just a mapping of what are
essentially jsonb operators to the hstore type (Christophe's suggestion).

B) We make hstore/jsonb a single extension with two types and all of the
requisite operators etc. (Peter's suggestion).

Reasons for (A):

* In-core jsonb would have strongly enhanced adoption value
* jsonb is liable to become one of our most-used types and it would be
strange for it not to be in core
* binary JSON would just work for web developers
* the only reason nested hstore is an extension is because hstore was an
extension and we need an upgrade path
* This is essentially the decision we collectively made in November, for
fairly well-argued reasons, and what Andrew has spent 3 months implementing.

Reasons against (A):

* Having a core type and an extension share code is strange.
* Implicit casts between a core type and an extension could cause issues.

Reasons for (B):

* Conceptually simpler.
* Makes a certain degree of bugginess/unfinishedness more acceptable.

Reasons against (B):

* Users would get tripped up by first, install the postgresql-contrib
package, then do CREATE EXTENSION hstore
* As cited, many sysadmins block the install of the -contrib package.
* Performance issues for psycopg2 and other drivers which need to look
up type information on each call.
* This requires larger changes to the existing patch, which likely means
missing the bus for 9.4 (and you've seen my blog about that)

Here's the point in particular which makes me very hesitant to endorse
(B) as a solution:

* Once created as an extension, there is no path to ever making jsonb a
core type.

... as long as we have no way to ever move types between extensions and
core, any decision we make on where a type belongs is permanent.  This
is one of the things which Andrew's proposal of a Type Registry last
year was intended to solve, but -hackers soundly rejected that proposal,
so we're currently stuck in the proverbial polluted estuary.

My cause, as everyone knows, is adoption.  Given that, I'm pretty
strongly in favor of proposal (A); I think a jsonb type which just
works will drive twice the adoption that one you have to remember to
install does.

-- 
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


Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Greg Stark
On Fri, Feb 28, 2014 at 7:12 PM, Josh Berkus j...@agliodbs.com wrote:
 * As cited, many sysadmins block the install of the -contrib package.

Of course the more you put things in core the more you make this logic
sound reasonable.


-- 
greg


-- 
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 and nested hstore

2014-02-28 Thread Andrew Dunstan


On 02/28/2014 02:00 PM, Tom Lane wrote:

Peter Geoghegan p...@heroku.com writes:

On Fri, Feb 28, 2014 at 5:01 AM, Andrew Dunstan and...@dunslane.net wrote:

But anyway, I think we've seen enough of these to conclude that the casts
from hstore to jsonb and back should not be implicit. I am fairly confident
that changing that would fix your complaint and the similar one that Peter
Geoghegan had.

Yes, it will, but I think that that will create more problems than it
will solve (which is not to suggest that an implicit cast is the right
thing). That will require that any non-trivial usage of jsonb requires
copious casting, where nested hstore does not. The hstore module
hardly contains some nice extras that a minority of jsonb users will
be interested in. It contains among other basic things, operator
classes required to index jsonb. All of my examples will still not
work, plus a bunch of cases that currently do work reasonably well.
There'll just be a different error message.

We should have learned by now that implicit casts are generally pretty
dangerous things.  I think putting in implicit casts as a band-aid for
missing functionality is a horrid idea that we'll regret for a long
time to come.  I gather from upthread comments that the patch currently
actually creates implicit casts in *both* directions?  That's doubly
horrid/dangerous.


I agree. I have removed them in my current tree.



The more I read in this thread, the more I think that jsonb simply
isn't ready.  We should put it off to 9.5 so that we can have a
complete implementation without so many rough edges.  I'm afraid that
if we ship it as-is, backwards compatibility considerations are going
to prevent us from filing down the rough edges in future.





Well, the jsonb portion of this is arguably the most ready, certainly 
it's had a lot more on-list review.


cheers

andrew


--
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 and nested hstore

2014-02-28 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes:
 ... This requires larger changes to the existing patch, which likely means
 missing the bus for 9.4 (and you've seen my blog about that)

Yeah.  I realize you're gung-ho about getting jsonb into 9.4 in some
form, and I recognize that getting better JSON support is important.
But I wonder how carefully you've thought about the damage it'll do
if what ships in 9.4 is a weird, hard-to-use mishmash.  I'd much
rather see us take the time to get it right than to ship something
that's basically a kluge.  And having a core type that depends on
an extension for critical functionality is certainly nothing but a
kluge.  As an example, you're arguing that some sysadmins won't permit
installation of contrib modules.  (Let's pass over the question of
how true or sane that is.)  If they won't allow hstore to be installed,
and jsonb is crippled in consequence, where does that put us for
adoption purposes?  I'd argue that it's worse than not shipping jsonb
yet at all.

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] jsonb and nested hstore

2014-02-28 Thread Peter Geoghegan
On Fri, Feb 28, 2014 at 11:12 AM, Josh Berkus j...@agliodbs.com wrote:
 I think Robert and Christophe are right: we're building a Berkeley BART
 Curve.  I think there's two courses of action from here which make sense:

 A) We move *all* of the important HStore libraries and operators into
 core, and make the hstore extension of them just a mapping of what are
 essentially jsonb operators to the hstore type (Christophe's suggestion).

 B) We make hstore/jsonb a single extension with two types and all of the
 requisite operators etc. (Peter's suggestion).

I agree with that dichotomy. I pointed this out a couple of times
already. I think the only reasonable way to deal with the casting
problems are to have parallel sets of operators and functions for
each, and to do that you really need one of those two things.

 Reasons against (B):

 * This requires larger changes to the existing patch, which likely means
 missing the bus for 9.4 (and you've seen my blog about that)

This seems very dubious. I highly doubt it. A big part of the reason
why I favor (B) is because I think just the opposite. Tom's remarks
just now are consistent with that.

 My cause, as everyone knows, is adoption.  Given that, I'm pretty
 strongly in favor of proposal (A); I think a jsonb type which just
 works will drive twice the adoption that one you have to remember to
 install does.

I don't think that's true. I used to work as a consultant, and I had a
number of fairly conservative clients. I don't ever recall there being
a restriction on installing a contrib package. If indeed any DBA does
operate under such a restrictive regime, then that's probably not the
kind of user that this feature is for anyway.

-- 
Peter Geoghegan


-- 
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 and nested hstore

2014-02-28 Thread Andres Freund
On 2014-02-28 14:45:29 -0500, Andrew Dunstan wrote:
 Well, the jsonb portion of this is arguably the most ready, certainly it's
 had a lot more on-list review.

Having crossread both patches I tend to agree with this. I don't think
it's unrealistic to get jsonb committable, but the hstore bits are
another story.

Greetings,

Andres Freund

-- 
 Andres Freund 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] jsonb and nested hstore

2014-02-28 Thread Andrew Dunstan


On 02/28/2014 02:46 PM, Tom Lane wrote:

Josh Berkus j...@agliodbs.com writes:

... This requires larger changes to the existing patch, which likely means
missing the bus for 9.4 (and you've seen my blog about that)

Yeah.  I realize you're gung-ho about getting jsonb into 9.4 in some
form, and I recognize that getting better JSON support is important.
But I wonder how carefully you've thought about the damage it'll do
if what ships in 9.4 is a weird, hard-to-use mishmash.  I'd much
rather see us take the time to get it right than to ship something
that's basically a kluge.  And having a core type that depends on
an extension for critical functionality is certainly nothing but a
kluge.  As an example, you're arguing that some sysadmins won't permit
installation of contrib modules.  (Let's pass over the question of
how true or sane that is.)  If they won't allow hstore to be installed,
and jsonb is crippled in consequence, where does that put us for
adoption purposes?  I'd argue that it's worse than not shipping jsonb
yet at all.





That hasn't been the way we've done things in the past. We're frequently 
incremental. New features sometimes take several releases to mature. 
Taking an example from close by, this will be the third release with 
Json, and it's got a bunch of spiffy new stuff, but there's at least one 
more round to go (what Merlin calls Manipulation functions), which I'm 
rather hopeing someone other than me will see fit to implement.


As for what Peter suggests, I just can't bring myself to do anything 
that would require people to say Oh, you want jsonb? You have to load 
hstore. It would be plain embarrassing.


cheers

andrew



--
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] Re: Patch to add support of IF NOT EXISTS to others CREATE statements

2014-02-28 Thread Alvaro Herrera
Pavel Stehule escribió:

 I agree with Tom proposal - CINE - where object holds data, COR everywhere
 else.
 
 But it means, so all functionality from this patch have to be rewritten :(

So we return this patch with feedback, right?  I don't think it's
reasonable to continue waiting this late.

I have marked as such in the CF app.

-- 
Álvaro Herrerahttp://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] jsonb and nested hstore

2014-02-28 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 That hasn't been the way we've done things in the past. We're frequently 
 incremental. New features sometimes take several releases to mature. 

That's perfectly fair.  What I don't want to see is a user-visible
dependency from jsonb to hstore.  I think that'll be a mess that will
take years to undo.  I'd rather say sorry, that functionality isn't
there yet for jsonb than have such a dependency.

Maybe we're in violent agreement.

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] extension_control_path

2014-02-28 Thread Dimitri Fontaine
Stephen Frost sfr...@snowman.net writes:
 Yeah, default_version was the other one that looked like it might be
 possible to include, but folks might decide to try and use 'comment' in
 that way too.  Basically, there's a chance that they'd want to use any
 string in there.

Actually, I think that $default_value is the only other serious enough
candidate that we should support, and I think we should support it both
from the directory and module_pathname parameters.

Also, it seems to me that while the $directory macro should still be
found only at the beginning of the module_pathname value, the
$default_value should be substituted from wherever it is found.

Please find attached a v1 version of the patch implementing that.

 doc/src/sgml/extend.sgml | 18 
 src/backend/commands/extension.c | 79 +---
 2 files changed, 91 insertions(+), 6 deletions(-)

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

*** a/doc/src/sgml/extend.sgml
--- b/doc/src/sgml/extend.sgml
***
*** 412,417 
--- 412,423 
  default behavior is equivalent to specifying
  literaldirectory = 'extension'/.
 /para
+para
+ The macro literal$default_value/literal is supported for this
+ parameter. When used literal$default_value/literal is then
+ substituted with the literaldefault_value/literal control
+ parameter value.
+/para
/listitem
   /varlistentry
  
***
*** 462,467 
--- 468,485 
  FUNCTION/ commands for C-language functions, so that the script
  files do not need to hard-wire the name of the shared library.
 /para
+para
+ The macro literal$default_value/literal is supported for this
+ parameter. When used literal$default_value/literal is then
+ substituted with the literaldefault_value/literal control
+ parameter value.
+/para
+para
+ The macro literal$directory/literal is also supported for this
+ parameter, only when found at the very start of the value for this
+ parameter. When used literal$directory/literal is then substituted
+ with the literaldirectory/literal control parameter value.
+/para
/listitem
   /varlistentry
  
*** a/src/backend/commands/extension.c
--- b/src/backend/commands/extension.c
***
*** 369,374  get_extension_control_filename(const char *extname)
--- 369,377 
  	return result;
  }
  
+ /*
+  * In the control file, the directory entry supports $default_version macro.
+  */
  static char *
  get_extension_script_directory(ExtensionControlFile *control)
  {
***
*** 383,393  get_extension_script_directory(ExtensionControlFile *control)
  		return get_extension_control_directory();
  
  	if (is_absolute_path(control-directory))
! 		return pstrdup(control-directory);
  
! 	get_share_path(my_exec_path, sharepath);
! 	result = (char *) palloc(MAXPGPATH);
! 	snprintf(result, MAXPGPATH, %s/%s, sharepath, control-directory);
  
  	return result;
  }
--- 386,406 
  		return get_extension_control_directory();
  
  	if (is_absolute_path(control-directory))
! 		result = pstrdup(control-directory);
! 	else
! 	{
! 		get_share_path(my_exec_path, sharepath);
! 		result = (char *) palloc(MAXPGPATH);
! 		snprintf(result, MAXPGPATH, %s/%s, sharepath, control-directory);
! 	}
  
! 	/* see about replacing the $default_version macro if present. */
! 	result = text_to_cstring(
! 		DatumGetTextPP(
! 			DirectFunctionCall3(replace_text,
! CStringGetTextDatum(result),
! CStringGetTextDatum($default_version),
! CStringGetTextDatum(control-default_version;
  
  	return result;
  }
***
*** 432,437  get_extension_script_filename(ExtensionControlFile *control,
--- 445,499 
  	return result;
  }
  
+ /*
+  * Substitute for any macros appearing in the given string.
+  * Result is always freshly palloc'd.
+  *
+  * Supported macros are:
+  *  - $directory
+  *  - $default_version
+  *
+  * The $directory macro must be used at the very start of the module_pathname.
+  */
+ static char *
+ substitute_module_macros(const char *module_pathname,
+ 		 const char *directory,
+ 		 const char *default_version)
+ {
+ 	Datum t_result;
+ 	const char *sep_ptr;
+ 
+ 	if (module_pathname == NULL)
+ 		return NULL;
+ 
+ 	/* Currently, we only recognize $directory at the start of the string */
+ 	if (module_pathname[0] != '$')
+ 		return pstrdup(module_pathname);
+ 
+ 	if ((sep_ptr = first_dir_separator(module_pathname)) == NULL)
+ 		sep_ptr = module_pathname + strlen(module_pathname);
+ 
+ 	/* Accept $libdir, just return module_pathname as is then */
+ 	if (strlen($libdir) == sep_ptr - module_pathname 
+ 		strncmp(module_pathname, $libdir, strlen($libdir)) == 0)
+ 		return pstrdup(module_pathname);
+ 
+ 	if 

Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Merlin Moncure
On Fri, Feb 28, 2014 at 1:45 PM, Andrew Dunstan and...@dunslane.net wrote:
 Well, the jsonb portion of this is arguably the most ready, certainly it's
 had a lot more on-list review.

That is definitely true.   Also, the jsonb type does not introduce any
new patterns that are not already covered by json -- it just does some
things better/faster (and, in a couple of cases, a bit differently) so
there's a safe harbor.  The implicit casts snuck in after the review
started -- that was a mistake obviously (but mostly with hstore).  The
side argument of 'to extension or not' is just that.  Make a decision
and commit this thing.

merlin


-- 
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 and nested hstore

2014-02-28 Thread Josh Berkus
On 02/28/2014 11:19 AM, Greg Stark wrote:
 On Fri, Feb 28, 2014 at 7:12 PM, Josh Berkus j...@agliodbs.com wrote:
 * As cited, many sysadmins block the install of the -contrib package.
 
 Of course the more you put things in core the more you make this logic
 sound reasonable.

Touche'!

However, the problems with admins not wanting to install -contrib aren't
really about what's in or not in -contrib.  They're about:

a) it's another package
b) they don't understand what's in it
c) it's called contrib which implies that these are
untested/unreviewed scripts, or somehow relates to hacking on Postgres,
both of which were true historically
d) there's some wierd/unstable dependancies for certain contrib modules
(UUID in particular)
e) some vendors don't make contrib available because of the encryption
thing (pgcrypto)

All of the above are worth fixing, but we don't have a proposal on the
table to do so.

-- 
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


Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Andrew Dunstan


On 02/28/2014 03:19 PM, Tom Lane wrote:

Andrew Dunstan and...@dunslane.net writes:

That hasn't been the way we've done things in the past. We're frequently
incremental. New features sometimes take several releases to mature.

That's perfectly fair.  What I don't want to see is a user-visible
dependency from jsonb to hstore.  I think that'll be a mess that will
take years to undo.  I'd rather say sorry, that functionality isn't
there yet for jsonb than have such a dependency.

Maybe we're in violent agreement.





Maybe we are.

There's actually no real dependency. In fact, the dependency is the 
other way. The jsonb patches I have been posting could be committed and 
pass every regression test and we'd have useful better performance for 
some operations. Every json function has an analog in jsonb except the 
generator functions (to_json and friends), and they use the same parser 
so they accept exactly the same input. The only dependency is if you 
want to be able to use some advanced indexing and other functionality, 
for which we don't currently have jsonb equivalents of the new hstore 
operators, because we ran out of time. Then you can get this 
functionality by casting the data to hstore (assuming we also have 
nested-hstore committed) and using its operators. But that's no more a 
dependency than it is for any other type for which you can leverage this 
functionality (e.g. any record type).


cheers

andrew


--
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: new long psql parameter --on-error-stop

2014-02-28 Thread Andrew Dunstan


On 02/28/2014 01:27 PM, Pavel Stehule wrote:




 three chars is not important

 important is a  placing in --help output


Well, then we just have to add more info to --help


it can be solution





+1 for at least doing that. I found it annoying just the other day not 
to find it in plsql's --help output, in a moment of brain fade when I 
forgot how to spell it. So it's not just beginners who can benefit, it's 
people like me whose memory occasionally goes awry.


cheers

andrew


--
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 and nested hstore

2014-02-28 Thread Christophe Pettus

On Feb 28, 2014, at 1:03 PM, Josh Berkus j...@agliodbs.com wrote:
 However, the problems with admins not wanting to install -contrib aren't
 really about what's in or not in -contrib.

I'll also mention that an increasingly large number of people are running 
PostgreSQL in an environment where they don't get to pick what packages are 
installed on their server (RDS, for example).  Telling them that something is 
in -contrib can very well be telling them You can't have it in those cases.

--
-- Christophe Pettus
   x...@thebuild.com



-- 
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 and nested hstore

2014-02-28 Thread Peter Geoghegan
On Fri, Feb 28, 2014 at 1:31 PM, Christophe Pettus x...@thebuild.com wrote:
 I'll also mention that an increasingly large number of people are running 
 PostgreSQL in an environment where they don't get to pick what packages are 
 installed on their server (RDS, for example).  Telling them that something is 
 in -contrib can very well be telling them You can't have it in those cases.

Amazon RDS Postgres has hstore.


-- 
Peter Geoghegan


-- 
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 and nested hstore

2014-02-28 Thread Peter Geoghegan
On Fri, Feb 28, 2014 at 1:25 PM, Andrew Dunstan and...@dunslane.net wrote:
 The only dependency is if you want to be able to use some advanced
 indexing and other functionality, for which we don't currently have jsonb
 equivalents of the new hstore operators, because we ran out of time. Then
 you can get this functionality by casting the data to hstore (assuming we
 also have nested-hstore committed) and using its operators. But that's no
 more a dependency than it is for any other type for which you can leverage
 this functionality (e.g. any record type).

I don't think hstore-style indexing is advanced; it's the main
reason for there being a jsonb, in my view. Anyway, this is where I
have a hard time understanding what you intend for jsonb for 9.4. You
ran out of time for writing jsonb operator classes, and so you can use
the hstore ones, which work fine. But, if we didn't run out of time,
how would the jsonb operator classes differ from the hstore ones? Is
there something inferior about the hstore operator class as compared
to a hypothetical jsonb operator class, other than the superficial
need to cast?


-- 
Peter Geoghegan


-- 
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 and nested hstore

2014-02-28 Thread Christophe Pettus

On Feb 28, 2014, at 1:35 PM, Peter Geoghegan p...@heroku.com wrote:
 I don't think hstore-style indexing is advanced; it's the main
 reason for there being a jsonb, in my view.

jsonb is significantly faster than json even without indexing; there are plenty 
of reasons to have jsonb even if we don't initially have indexing operations 
for it.

--
-- Christophe Pettus
   x...@thebuild.com



-- 
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: new long psql parameter --on-error-stop

2014-02-28 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 Well, then we just have to add more info to --help

 +1 for at least doing that. I found it annoying just the other day not 
 to find it in plsql's --help output, in a moment of brain fade when I 
 forgot how to spell it. So it's not just beginners who can benefit, it's 
 people like me whose memory occasionally goes awry.

No objection in principle, but what are we talking about exactly?
Adding some new backslash command that lists all the variables that have
special meanings?  I'm not sure that the main --help output is the place
for this, because that only covers psql's command line switches (and
is plenty long enough already).

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] jsonb and nested hstore

2014-02-28 Thread Christophe Pettus

On Feb 28, 2014, at 1:34 PM, Peter Geoghegan p...@heroku.com wrote:

 Amazon RDS Postgres has hstore.

Just observing that putting something in -contrib does not mean every 
installation can automatically adopt it.

--
-- Christophe Pettus
   x...@thebuild.com



-- 
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: new long psql parameter --on-error-stop

2014-02-28 Thread Pavel Stehule
2014-02-28 22:38 GMT+01:00 Tom Lane t...@sss.pgh.pa.us:

 Andrew Dunstan and...@dunslane.net writes:
  Well, then we just have to add more info to --help

  +1 for at least doing that. I found it annoying just the other day not
  to find it in plsql's --help output, in a moment of brain fade when I
  forgot how to spell it. So it's not just beginners who can benefit, it's
  people like me whose memory occasionally goes awry.

 No objection in principle, but what are we talking about exactly?
 Adding some new backslash command that lists all the variables that have
 special meanings?  I'm not sure that the main --help output is the place
 for this, because that only covers psql's command line switches (and
 is plenty long enough already).


Hard to say -

a special long option in General option can be clean.

or new section Tips

Tips:
  -v ON_ERROR_STOP=1   stops on first error





 regards, tom lane



Re: [HACKERS] proposal: new long psql parameter --on-error-stop

2014-02-28 Thread Erik Rijkers
On Fri, February 28, 2014 22:38, Tom Lane wrote:
 Andrew Dunstan and...@dunslane.net writes:
 Well, then we just have to add more info to --help

 +1 for at least doing that. I found it annoying just the other day not
 to find it in plsql's --help output, in a moment of brain fade when I
 forgot how to spell it. So it's not just beginners who can benefit, it's
 people like me whose memory occasionally goes awry.

 No objection in principle, but what are we talking about exactly?
 Adding some new backslash command that lists all the variables that have
 special meanings?  I'm not sure that the main --help output is the place
 for this, because that only covers psql's command line switches (and
 is plenty long enough already).

   regards, tom lane

Perhaps this compromise:

  -v, --set=, --variable=NAME=VALUE
   set psql variable NAME to VALUE e.g.: -v 
ON_ERROR_STOP=1

this would not lengthen and not broaden the output of psql --help





-- 
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: new long psql parameter --on-error-stop

2014-02-28 Thread Pavel Stehule
2014-02-28 22:52 GMT+01:00 Erik Rijkers e...@xs4all.nl:

 On Fri, February 28, 2014 22:38, Tom Lane wrote:
  Andrew Dunstan and...@dunslane.net writes:
  Well, then we just have to add more info to --help
 
  +1 for at least doing that. I found it annoying just the other day not
  to find it in plsql's --help output, in a moment of brain fade when I
  forgot how to spell it. So it's not just beginners who can benefit, it's
  people like me whose memory occasionally goes awry.
 
  No objection in principle, but what are we talking about exactly?
  Adding some new backslash command that lists all the variables that have
  special meanings?  I'm not sure that the main --help output is the place
  for this, because that only covers psql's command line switches (and
  is plenty long enough already).
 
regards, tom lane

 Perhaps this compromise:

   -v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE e.g.: -v
 ON_ERROR_STOP=1


can be



 this would not lengthen and not broaden the output of psql --help






Re: [HACKERS] proposal: new long psql parameter --on-error-stop

2014-02-28 Thread Andrew Dunstan


On 02/28/2014 04:38 PM, Tom Lane wrote:

Andrew Dunstan and...@dunslane.net writes:

Well, then we just have to add more info to --help

+1 for at least doing that. I found it annoying just the other day not
to find it in plsql's --help output, in a moment of brain fade when I
forgot how to spell it. So it's not just beginners who can benefit, it's
people like me whose memory occasionally goes awry.

No objection in principle, but what are we talking about exactly?
Adding some new backslash command that lists all the variables that have
special meanings?



That's a pretty good idea, especially if we give that command a command 
line option too, so something like


   psql --special-variables

would run that command and exit.

Maybe I'm over-egging the pudding a bit ;-)

cheers

andrew


--
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: new long psql parameter --on-error-stop

2014-02-28 Thread Pavel Stehule
2014-02-28 23:01 GMT+01:00 Andrew Dunstan and...@dunslane.net:


 On 02/28/2014 04:38 PM, Tom Lane wrote:

 Andrew Dunstan and...@dunslane.net writes:

 Well, then we just have to add more info to --help

 +1 for at least doing that. I found it annoying just the other day not
 to find it in plsql's --help output, in a moment of brain fade when I
 forgot how to spell it. So it's not just beginners who can benefit, it's
 people like me whose memory occasionally goes awry.

 No objection in principle, but what are we talking about exactly?
 Adding some new backslash command that lists all the variables that have
 special meanings?



 That's a pretty good idea, especially if we give that command a command
 line option too, so something like

psql --special-variables

 would run that command and exit.


it can be second one option.

Pavel



 Maybe I'm over-egging the pudding a bit ;-)

 cheers

 andrew



Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Peter Geoghegan
On Fri, Feb 28, 2014 at 1:38 PM, Christophe Pettus x...@thebuild.com wrote:
 jsonb is significantly faster than json even without indexing; there are 
 plenty of reasons to have jsonb even if we don't initially have indexing 
 operations for it.

That may be true, although I think that that's still very disappointing.

In order to make a rational decision to do the work incrementally, we
need to know what we're putting off until 9.5. AFAICT, we have these
operator classes that work fine with jsonb for the purposes of
hstore-style indexing (the hstore operator classes). Wasn't that the
point? When there is a dedicated set of jsonb operator classes, what
will be different about them, other than the fact that they won't be
hstore operator classes? A decision predicated on waiting for those to
come in 9.5 must consider what we're actually waiting for, and right
now that seems very hazy.


-- 
Peter Geoghegan


-- 
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: option --if-exists for pg_dump

2014-02-28 Thread Pavel Stehule
Hi


 However, I don't think this is behaving sanely in pg_dumpall.  AFAICT,
 pg_dumpall does not pass --clean to pg_dump (in other words it only
 emits DROP for the global objects, not the objects contained inside
 databases), so passing --if-exists results in failures.  Therefore I
 think the solution is to not pass --if-exists to pg_dump at all, i.e.
 keep it internal to pg_dumpall.  But maybe I'm missing something.


I am looking to pg_dumpall code, and I am inclined to don't pass
--if-exists to pg_dump too.

-c, --clean for pg_dumpall means drop databases


Usage:
  pg_dumpall [OPTION]...

General options:
  -f, --file=FILENAME  output file name
  -V, --versionoutput version information, then exit
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help   show this help, then exit

Options controlling the output content:
  -a, --data-only  dump only the data, not the schema
  -c, --clean  clean (drop) databases before recreating


so --if-exists should to mean

DROP DATABASE IF EXISTS dbname

do you agree?

Pavel


Re: [HACKERS] patch: option --if-exists for pg_dump

2014-02-28 Thread Pavel Stehule
2014-02-28 23:13 GMT+01:00 Pavel Stehule pavel.steh...@gmail.com:

 Hi


 However, I don't think this is behaving sanely in pg_dumpall.  AFAICT,
 pg_dumpall does not pass --clean to pg_dump (in other words it only
 emits DROP for the global objects, not the objects contained inside
 databases), so passing --if-exists results in failures.  Therefore I
 think the solution is to not pass --if-exists to pg_dump at all, i.e.
 keep it internal to pg_dumpall.  But maybe I'm missing something.


 I am looking to pg_dumpall code, and I am inclined to don't pass
 --if-exists to pg_dump too.

  -c, --clean for pg_dumpall means drop databases

 
 Usage:
   pg_dumpall [OPTION]...

 General options:
   -f, --file=FILENAME  output file name
   -V, --versionoutput version information, then exit
   --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
   -?, --help   show this help, then exit

 Options controlling the output content:
   -a, --data-only  dump only the data, not the schema
   -c, --clean  clean (drop) databases before recreating
 

 so --if-exists should to mean

 DROP DATABASE IF EXISTS dbname


+ DROP ROLE and DROP TABLESPACE



 do you agree?

 Pavel



Re: [HACKERS] patch: option --if-exists for pg_dump

2014-02-28 Thread Pavel Stehule
This patch has redesigned implementation --if-exists for pg_dumpall. Now it
is not propagated to pg_dump, but used on pg_dumpall level.

Regards

Pavel


2014-02-28 23:18 GMT+01:00 Pavel Stehule pavel.steh...@gmail.com:




 2014-02-28 23:13 GMT+01:00 Pavel Stehule pavel.steh...@gmail.com:

 Hi


 However, I don't think this is behaving sanely in pg_dumpall.  AFAICT,
 pg_dumpall does not pass --clean to pg_dump (in other words it only
 emits DROP for the global objects, not the objects contained inside
 databases), so passing --if-exists results in failures.  Therefore I
 think the solution is to not pass --if-exists to pg_dump at all, i.e.
 keep it internal to pg_dumpall.  But maybe I'm missing something.


 I am looking to pg_dumpall code, and I am inclined to don't pass
 --if-exists to pg_dump too.

  -c, --clean for pg_dumpall means drop databases

 
 Usage:
   pg_dumpall [OPTION]...

 General options:
   -f, --file=FILENAME  output file name
   -V, --versionoutput version information, then exit
   --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
   -?, --help   show this help, then exit

 Options controlling the output content:
   -a, --data-only  dump only the data, not the schema
   -c, --clean  clean (drop) databases before recreating
 

 so --if-exists should to mean

 DROP DATABASE IF EXISTS dbname


 + DROP ROLE and DROP TABLESPACE



 do you agree?

 Pavel



commit 3e2e9baa3b5e708b4014b18cbedd2e6b1fc095a5
Author: root root@nemesis.(none)
Date:   Fri Feb 28 23:31:32 2014 +0100

pg_dumpall --if-exists fix

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 40c69f0..1f0d4de 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -145,7 +145,8 @@ PostgreSQL documentation
para
 Output commands to clean (drop)
 database objects prior to outputting the commands for creating them.
-(Restore might generate some harmless error messages, if any objects
+(Unless option--if-exists/ is also specified,
+restore might generate some harmless error messages, if any objects
 were not present in the destination database.)
/para
 
@@ -650,6 +651,17 @@ PostgreSQL documentation
  /varlistentry
 
  varlistentry
+  termoption--if-exists/option/term
+  listitem
+   para
+Use conditional commands (i.e. add an literalIF EXISTS/literal
+clause) when cleaning database objects.  This option is not valid
+unless option--clean/ is also specified.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption--disable-dollar-quoting//term
   listitem
para
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index f337939..fcf5f77 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -301,6 +301,17 @@ PostgreSQL documentation
  /varlistentry
 
  varlistentry
+  termoption--if-exists/option/term
+  listitem
+   para
+Use conditional commands (i.e. add an literalIF EXISTS/literal
+clause) to clean databases and other objects.  This option is not valid
+unless option--clean/ is also specified.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption--inserts/option/term
   listitem
para
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index cd60b25..4bc30ce 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -109,7 +109,8 @@
   listitem
para
 Clean (drop) database objects before recreating them.
-(This might generate some harmless error messages, if any objects
+(Unless option--if-exists/ is used,
+this might generate some harmless error messages, if any objects
 were not present in the destination database.)
/para
   /listitem
@@ -490,6 +491,17 @@
  /varlistentry
 
  varlistentry
+  termoption--if-exists/option/term
+  listitem
+   para
+Use conditional commands (i.e. add an literalIF EXISTS/literal
+clause) when cleaning database objects.  This option is not valid
+unless option--clean/ is also specified.
+   /para
+  /listitem
+ /varlistentry
+
+ varlistentry
   termoption--no-data-for-failed-tables/option/term
   listitem
para
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 6927968..83f7216 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -113,6 +113,7 @@ typedef struct _restoreOptions
 	char	   *superuser;		/* Username to use as superuser */
 	char	   *use_role;		/* Issue SET ROLE to this */
 	int			dropSchema;
+	int			if_exists;
 	const char *filename;
 	int			dataOnly;
 	int			schemaOnly;
diff --git 

Re: [HACKERS] jsonb and nested hstore

2014-02-28 Thread Christophe Pettus

On Feb 28, 2014, at 2:12 PM, Peter Geoghegan p...@heroku.com wrote:

 AFAICT, we have these
 operator classes that work fine with jsonb for the purposes of
 hstore-style indexing (the hstore operator classes).

That assumes that it is acceptable that jsonb be packaged in the hstore 
extension.  To put it mildly, there's no consensus on that point; indeed, I 
think there's consensus that's a non-starter.

--
-- Christophe Pettus
   x...@thebuild.com



-- 
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 and nested hstore

2014-02-28 Thread Peter Geoghegan
On Fri, Feb 28, 2014 at 2:40 PM, Christophe Pettus x...@thebuild.com wrote:
 On Feb 28, 2014, at 2:12 PM, Peter Geoghegan p...@heroku.com wrote:

 AFAICT, we have these
 operator classes that work fine with jsonb for the purposes of
 hstore-style indexing (the hstore operator classes).

 That assumes that it is acceptable that jsonb be packaged in the hstore 
 extension.  To put it mildly, there's no consensus on that point; indeed, I 
 think there's consensus that's a non-starter.

No, it assumes nothing at all. It's a very simple question.


-- 
Peter Geoghegan


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


Re: Custom Scan APIs (Re: [HACKERS] Custom Plan node)

2014-02-28 Thread Kohei KaiGai
2014-03-01 0:36 GMT+09:00 Stephen Frost sfr...@snowman.net:
 * Kouhei Kaigai (kai...@ak.jp.nec.com) wrote:
 * Stephen Frost (sfr...@snowman.net) wrote:
  I don't see how you can be when there hasn't been any discussion that I've
  seen about how parallel query execution is going to change things for us.
 
 If parallel query execution changes whole of the structure of plan nodes,
 it will also affect to the interface of custom-scan because it is a thin-
 abstraction of plan-node. However, if parallel execution feature is
 implemented as one of new plan node in addition to existing one, I cannot
 imagine a scenario that affects to the structure of another plan node.

 Let's just say that I have doubts that we'll be able to implement
 parallel execution *without* changing the plan node interface in some
 way which will require, hopefully minor, changes to all of the nodes.
 The issue is that even a minor change would break the custom-scan API
 and we'd immediately be in the boat of dealing with complaints regarding
 backwards compatibility.  Perhaps we can hand-wave that, and we've had
 some success changing hook APIs between major releases, but such changes
 may also be in ways which wouldn't break in obvious ways or even
 possibly be changes which have to be introduced into back-branches.
 Parallel query is going to be brand-new real soon and it's reasonable to
 think we'll need to make bug-fix changes to it after it's out which
 might even involve changes to the API which is developed for it.

Even if we will change the plan-node interface in the future release,
it shall not be a change that makes the existing stuff impossible.
The custom-scan API is designed to provide alternative way to scan
or join relations, in addition to the existing logic like SeqScan or
NestLoop. If this change breaks plan-node interfaces and it couldn't
implement existing stuff, it is problematic for all the stuff, not only
custom-scan node. I don't think such a change that makes impossible
to implement existing logic will be merged. Likely, the new parallel
execution feature can work together existing sequential logic and
custom-scan interface.

BTW, this kind of discussion looks like a talk with a ghost because
we cannot see the new interface according to the parallel execution
right now, so we cannot have tangible investigation whether it becomes
really serious backward incompatibility, or not.
My bet is minor one. I cannot imagine plan-node interface that does
not support existing non-parallel SeqScan or NestLoop and so on.

  The issue here is that we're going to be expected to maintain an interface
  once we provide it and so that isn't something we should be doing lightly.
  Particularly when it's as involved as this kind of change is with what's
  going on in the backend where we are nearly 100% sure to be changing things
  in the next release or two.
 
 FDW APIs are also revised several times in the recent releases. If we can
 design perfect interface from the beginning, it's best but usually hard
 to design.

 Sure, but FDWs also have a *much* broader set of use-cases, in my view,
 which is also why I was pushing to work on join-push-down to happen
 there instead of having this kind of a hook interface, which I don't
 think we'd want to directly expose as part of the 'official FDW API' as
 it ends up putting all the work on the FDW with little aide, making it
 terribly likely to end up with a bunch of duplciated code in the FDWs
 from the backend to deal with it, particularly for individuals writing
 FDWs who aren't familiar with what PG already has.

It might not be a good idea to use postgres_fdw as a basis of proof-
of-concept to demonstrate that custom-scan can effectively host
an alternative way to join; that fetches the result set of remote-join
as if relation scan, even though it demonstrated it is possible.
So, I never mind the part-3 portion (remote join of postgres_fdw on
top of custom-scan) being dropped from the submission.

Thanks,
-- 
KaiGai Kohei kai...@kaigai.gr.jp


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


[HACKERS] Query cancel in regex library

2014-02-28 Thread Tom Lane
We have a couple of open bug reports in which the regex library takes
unreasonable amounts of time and/or memory to process a regexp.  While
I have hopes that both of the known issues can be improved, it seems
likely that there will always be cases where sufficiently complicated
regexps just take too long.  Right now, any such case is an effective
DOS since the regex code contains no provisions for detecting a pending
query cancel: the backend just locks up for however long it takes,
unless it runs out of memory.  I've resisted adding such provisions
because I have hopes of making that code into a standalone library someday
... but I think it's time to capitulate.  Arguably, other applications
making use of a regex library could want query-cancel support too, so as
long as there's an API for specifying a callback function to detect a
pending query cancel, this doesn't seem any worse than some of the other
things we've done to Spencer's code.

In the attached proposed patch, I've not actually invented such an API,
but I have encapsulated the cancel check in an internal callback function,
so that it wouldn't take much more work to add an API for setting a
different callback function.

I don't claim that the two places I added cancel checks necessarily
provide 100% coverage of long code paths in the library; but they're
pretty central to regex compilation and execution respectively, and
they do stop the two known problem cases quite quickly.  We can always
add more checks if found necessary.

Barring objection or better ideas, I propose to back-patch this into
all live branches.

regards, tom lane

diff --git a/src/backend/regex/regc_nfa.c b/src/backend/regex/regc_nfa.c
index ae2dbe4..f6dad01 100644
*** a/src/backend/regex/regc_nfa.c
--- b/src/backend/regex/regc_nfa.c
*** newstate(struct nfa * nfa)
*** 174,184 
--- 174,196 
  {
  	struct state *s;
  
+ 	/*
+ 	 * This is a handy place to check for operation cancel during regex
+ 	 * compilation, since no code path will go very long without making a new
+ 	 * state.
+ 	 */
+ 	if (CANCEL_REQUESTED(nfa-v-re))
+ 	{
+ 		NERR(REG_CANCEL);
+ 		return NULL;
+ 	}
+ 
  	if (TooManyStates(nfa))
  	{
  		NERR(REG_ETOOBIG);
  		return NULL;
  	}
+ 
  	if (nfa-free != NULL)
  	{
  		s = nfa-free;
diff --git a/src/backend/regex/regcomp.c b/src/backend/regex/regcomp.c
index ca1ccc5..f10f7a8 100644
*** a/src/backend/regex/regcomp.c
--- b/src/backend/regex/regcomp.c
***
*** 34,39 
--- 34,41 
  
  #include regex/regguts.h
  
+ #include miscadmin.h			/* needed by rcancelrequested() */
+ 
  /*
   * forward declarations, up here so forward datatypes etc. are defined early
   */
*** static long nfanode(struct vars *, struc
*** 67,72 
--- 69,75 
  static int	newlacon(struct vars *, struct state *, struct state *, int);
  static void freelacons(struct subre *, int);
  static void rfree(regex_t *);
+ static int	rcancelrequested(void);
  
  #ifdef REG_DEBUG
  static void dump(regex_t *, FILE *);
*** struct vars
*** 276,281 
--- 279,285 
  /* static function list */
  static const struct fns functions = {
  	rfree,		/* regfree insides */
+ 	rcancelrequested			/* check for cancel request */
  };
  
  
*** rfree(regex_t *re)
*** 1893,1898 
--- 1897,1918 
  	}
  }
  
+ /*
+  * rcancelrequested - check for external request to cancel regex operation
+  *
+  * Return nonzero to fail the operation with error code REG_CANCEL,
+  * zero to keep going
+  *
+  * The current implementation is Postgres-specific.  If we ever get around
+  * to splitting this out as a standalone library, there will need to be some
+  * API to let applications provide a callback function for this.
+  */
+ static int
+ rcancelrequested(void)
+ {
+ 	return InterruptPending  (QueryCancelPending || ProcDiePending);
+ }
+ 
  #ifdef REG_DEBUG
  
  /*
diff --git a/src/backend/regex/regexec.c b/src/backend/regex/regexec.c
index 78ebdee..0edb83c 100644
*** a/src/backend/regex/regexec.c
--- b/src/backend/regex/regexec.c
*** cdissect(struct vars * v,
*** 595,600 
--- 595,604 
  	assert(t != NULL);
  	MDEBUG((cdissect %ld-%ld %c\n, LOFF(begin), LOFF(end), t-op));
  
+ 	/* handy place to check for operation cancel */
+ 	if (CANCEL_REQUESTED(v-re))
+ 		return REG_CANCEL;
+ 
  	switch (t-op)
  	{
  		case '=':/* terminal node */
diff --git a/src/backend/utils/adt/regexp.c b/src/backend/utils/adt/regexp.c
index 588052c..7c5b0d5 100644
*** a/src/backend/utils/adt/regexp.c
--- b/src/backend/utils/adt/regexp.c
***
*** 31,36 
--- 31,37 
  
  #include catalog/pg_type.h
  #include funcapi.h
+ #include miscadmin.h
  #include regex/regex.h
  #include utils/array.h
  #include utils/builtins.h
*** RE_compile_and_cache(text *text_re, int 
*** 188,193 
--- 189,203 
  	if (regcomp_result != REG_OKAY)
  	{
  		/* re didn't compile (no need for pg_regfree, if 

[HACKERS] Securing make check (CVE-2014-0067)

2014-02-28 Thread Noah Misch
As announced with last week's releases, use of trust authentication in the
make check temporary database cluster makes it straightforward to hijack the
OS user account involved.  The prerequisite is another user account on the
same system.  The solution we discussed on secur...@postgresql.org was to
switch to md5 authentication with a generated, strong password.

This patch adds a pg_genpassword program, which pg_regress.c and the
pg_upgrade test drivers use to generate password files for initdb and psql.
It is essentially a portable head -c16 /dev/urandom | xxd -p.  I have it
installing to $(bindir) for the benefit of out-of-tree test suites that create
temporary clusters.  $(pgxsdir)/$(subdir), where we install pg_regress itself,
was another candidate.  This is the first core need for high-entropy random
sequences, so I adapted px_acquire_system_randomness() to libpgport as
secure_rand_bytes().

The implementation of secure_rand_bytes() prompted the question of what to do
when there's no adequate entropy source, such as on Unix-like systems with no
/dev/urandom.  OpenSSL and OpenSSH give up; you're expected to install EGD or
PRNGD.  I made the test drivers recognize a PGTESTPWFILE environment variable.
It should name a file suitable as input to initdb --pwfile=  When
present, the test drivers will skip attempting to generate a password and will
use the one in that file.  I don't see any platforms on the buildfarm that
will need to use PGTESTPWFILE, but I bet at least Tom's old HP-UX system will
need it.

The dblink and postgres_fdw tests rely on a postmaster environment such that
superuser sessions can make new bootstrap superuser connections without a
password.  pg_regress achieves this by starting the server with the same
PGPASSFILE setting as it uses for psql.  Secure make -C contrib installcheck
rigs will need to do something similar.

Needing some conversion from random bytes to a text password, I moved the hex
encoder from encode.c to src/port/pgencode.c.  That place will be suitable for
the base64 encoder, too, when other code needs it (pgcrypto already contains a
duplicate base64 implementation).  Though the new libpgport functions fit
better in libpgcommon, since this is slated for back-patch, I chose the
library available in all supported versions.

Peripheral to the topic at hand, I sought and did not find evidence of
contemporary systems where an unprivileged user can examine the environment
variables of another user's processes.  What's a non-ancient system for which
the warning documented for the PGPASSWORD environment variable is apropos?

Thanks,
nm

-- 
Noah Misch
EnterpriseDB http://www.enterprisedb.com
diff --git a/contrib/dblink/expected/dblink.out 
b/contrib/dblink/expected/dblink.out
index f237c43..36fdf73 100644
--- a/contrib/dblink/expected/dblink.out
+++ b/contrib/dblink/expected/dblink.out
@@ -782,18 +782,17 @@ SELECT dblink_disconnect('dtest1');
 (1 row)
 
 -- test foreign data wrapper functionality
-CREATE USER dblink_regression_test;
+CREATE ROLE dblink_regression_test;
 CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw
   OPTIONS (dbname 'contrib_regression');
 CREATE USER MAPPING FOR public SERVER fdtest
   OPTIONS (server 'localhost');  -- fail, can't specify server here
 ERROR:  invalid option server
 HINT:  Valid options in this context are: user, password
-CREATE USER MAPPING FOR public SERVER fdtest;
+CREATE USER MAPPING FOR public SERVER fdtest OPTIONS (user :'USER');
 GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test;
 GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO 
dblink_regression_test;
-\set ORIGINAL_USER :USER
-\c - dblink_regression_test
+SET SESSION AUTHORIZATION dblink_regression_test;
 -- should fail
 SELECT dblink_connect('myconn', 'fdtest');
 ERROR:  password is required
@@ -821,7 +820,7 @@ SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a 
int, b text, c text[])
  10 | k | {a10,b10,c10}
 (11 rows)
 
-\c - :ORIGINAL_USER
+\c - -
 REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test;
 REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM 
dblink_regression_test;
 DROP USER dblink_regression_test;
diff --git a/contrib/dblink/sql/dblink.sql b/contrib/dblink/sql/dblink.sql
index 2a10760..30396ed 100644
--- a/contrib/dblink/sql/dblink.sql
+++ b/contrib/dblink/sql/dblink.sql
@@ -359,25 +359,24 @@ SELECT dblink_error_message('dtest1');
 SELECT dblink_disconnect('dtest1');
 
 -- test foreign data wrapper functionality
-CREATE USER dblink_regression_test;
+CREATE ROLE dblink_regression_test;
 CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw
   OPTIONS (dbname 'contrib_regression');
 CREATE USER MAPPING FOR public SERVER fdtest
   OPTIONS (server 'localhost');  -- fail, can't specify server here
-CREATE USER MAPPING FOR public SERVER fdtest;
+CREATE USER MAPPING FOR public SERVER fdtest OPTIONS (user :'USER');
 
 GRANT USAGE ON FOREIGN SERVER fdtest TO 

Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements

2014-02-28 Thread Fabrízio de Royes Mello
On Sat, Jan 18, 2014 at 11:12 PM, Stephen Frost sfr...@snowman.net wrote:

 Fabrízio, can you clarify the use-case for things like CREATE AGGREGATE
 to have IF NOT EXISTS rather than OR REPLACE, or if there is a reason
 why both should exist?  Complicating our CREATE options is not something
 we really wish to do without good reason and we certainly don't want to
 add something now that we'll wish to remove in another version or two.


Hi Stephen,

First I'm really sorry about the long time without an answer. I'm very busy
in this start of the year.

Well I have a scenario with many servers to deploy DDL scripts, and most of
them we must run without transaction control because some tasks like CREATE
INDEX CONCURRENTLY, DROP/CREATE DATABASE, CLUSTER, etc.

When an error occurs the script stops, but the previous commands was
commited, then we must review the script to comment parts that was already
executed and then run it again. Until now is not a really trouble, but in
some cases we must deploy another DDL script that contains a new version of
some object before we finish to fix the previous version that was in
production, and if we have CINE for all CREATE objects this task will more
easy because we just run it again without care if will replace the content
and do not produce an error.

I know that is a very specific case, but in my mind I don't see any problem
to have CINE and COR to this objects. The behavior is totally different.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog sobre TI: http://fabriziomello.blogspot.com
 Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello


Re: [HACKERS] Re: Patch to add support of IF NOT EXISTS to others CREATE statements

2014-02-28 Thread Fabrízio de Royes Mello
On Fri, Feb 28, 2014 at 5:05 PM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:

 Pavel Stehule escribió:

  I agree with Tom proposal - CINE - where object holds data, COR
everywhere
  else.
 
  But it means, so all functionality from this patch have to be rewritten
:(

 So we return this patch with feedback, right?  I don't think it's
 reasonable to continue waiting this late.

 I have marked as such in the CF app.


Sorry guys... I'm very busy in this start of the year, so I have no time to
dedicate to this patch. Maybe in the next commit fest I have time and do a
rework on it.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog sobre TI: http://fabriziomello.blogspot.com
 Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello


Re: [HACKERS] [PATCH] Use MAP_HUGETLB where supported (v3)

2014-02-28 Thread Peter Geoghegan
On Fri, Feb 28, 2014 at 9:43 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
 Hmm, I wonder if that could now be misunderstood to have something to do
 with the PostgreSQL page size? Maybe add the word memory or operating
 system in the first sentence in the docs, like this: Enables/disables the
 use of huge memory pages.

Whenever I wish to emphasize that distinction, I tend to use the term
MMU pages.


-- 
Peter Geoghegan


-- 
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: new long psql parameter --on-error-stop

2014-02-28 Thread Pavel Stehule
2014-02-28 22:52 GMT+01:00 Erik Rijkers e...@xs4all.nl:

 e.g.: -v ON_ERROR_STOP=1



I checked it and it is not the most long line there, so it can be a good
solution.

Pavel