Re: [HACKERS] Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-03 Thread Greg Smith

On 8/2/13 4:48 PM, Stephen Frost wrote:

* Josh Berkus (j...@agliodbs.com) wrote:

I really think this is the wrong approach.  If we start removing
unsafe parameters from ALTER SYSTEM SET, we basically hobble the
feature to the point of uselessness.  Out of the 15 or so parameters 80%
of our users touch, half of them are on your unsafe list.


Reflecting on this a bit more, I'm curious what your list-of-15 is.


You can get a top 15-ish list from 
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server


The list of things I see changed regularly that are on your unsafe list are:

listen_addresses, port, shared_buffers, log_directory, log_filename

Obvious missing thing from your unsafe list that is also changed 
regularly is max_connection.  I count 6 parameters that are both unsafe 
and changed regularly.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.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] comment for fast promote

2013-08-03 Thread Tomonari Katsumata
Hi,

I made a patch for REL9_3_STABLE which gets rid of
old promote processing. please check it.
This patch make PostgreSQL do fast promoting(*) always.
(*) which means skipping long checkpoint before increasing
timeline.

And after this, I'll do make another patch for unlinking files which are
created by user as a trigger_file or pg_ctl promote command.

---
Tomonari Katsumata
2013/7/30 Fujii Masao masao.fu...@gmail.com

 On Sat, Jul 27, 2013 at 6:57 PM, Tomonari Katsumata
 t.katsumata1...@gmail.com wrote:
  Hi,
 
 
  Yes, it prevents PROMOTE_SIGNAL_FILE from remaining even if
  both promote files exist.
 
  The command(unlink(PROMOTE_SIGNAL_FILE)) here is for
  unusualy case.
  Because the case is when done both procedures below.
   - user create promote file on PGDATA
   - user issue pg_ctl promote
 
  I understand the reason.
  But I think it's better to unlink(PROMOTE_SIGNAL_FILE) before
  unlink(FAST_PROMOTE_SIGNAL_FILE).
  Because FAST_PROMOTE_SIGNAL_FILE is definetly there but
  PROMOTE_SIGNAL_FILE is sometimes there or not there.
 
  I could not understand why that's better. Could you elaborate that?
 
  I'm sorry for less explanation.
 
  I've thought that errno would be set ENOENT and
  this may lead something wrong.
  I checked this and I know it's not problem.
 
  sorry for confusing you.
 
 
 
  And I have another question linking this behavior.
  I think TriggerFile should be removed too.
  This is corner-case but it will happen.
  How do you think of it ?
 
  I don't have strong opinion about that. I've never heard the complaint
  about that current behavior so far.
 
  For example, please imagine the cascading replication environment and
  using old master as a standby without copying the timeline history file
  to new standby.
 
  ---
  1. replicating 3 servers(A,B,C)
  A-B-C
  (trigger_file = /tmp/trig is set in recovery_recovery.conf on B and C.)
 
  2. stop server A and promoting server B with touch /tmp/trig;pg_ctl
  promote

 Why do you need to both create the trigger file and run pg_ctl promote?

 Anyway, if the patch is useful for fail-safe and it doesn't break the
 current
 behavior, I'd be happy to apply it. You are suggesting that we should
 remove
 the trigger file in CheckForStandbyTrigger() even if pg_ctl promote is
 executed.
 But there can be some cases where we can get out of the WAL replay loop,
 for example, reach the recovery_target_xxx. So ISTM we should try to remove
 both the trigger file and promote file at the end of recovery
 instead. Thought?

  B-C
  (/tmp/trig file remains on server B)
 
  4. stop server B and promoting server C with pg_ctl promote
  C
 
  5. making server B connect for standby of server C
  C-B
  -
 
  In step5 server B will promote as soon as it starts,
  because /tmp/trig is stil there.
 
 
 
  One question is that: we really still need to support normal promote?
  pg_ctl promote provides only way to do fast promotion. If we want to
  do normal promotion, we need to create PROMOTE_SIGNAL_FILE
  and send the SIGUSR1 signal to postmaster by hand. This seems messy.
 
  I think that we should remove normal promotion at all, or change
  pg_ctl promote so that provides also the way to do normal promotion.
 
  I think he merit of fast promote is
   - allowing quick connection by skipping checkpoint
  and its demerit is
   - taking little bit longer when crash-recovery
 
  If it is seldom to happen its crash soon after promoting
  and fast promte never breaks consistency of database cluster,
  I think we don't need normal promotion.
 
  You can execute checkpoint after fast promotion for that.
 
  OK.
  Then I think we should do below things.
  - removing normal promotion at all from source
  - adding the know-how you suggest on document

 IMO either is necessary.

 Regards,

 --
 Fujii Masao



getting_rid_of_old_promote.patch
Description: Binary data

-- 
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] inconsistent state after crash recovery

2013-08-03 Thread Satoshi Nagayasu

(2013/08/02 21:19), Robert Haas wrote:

On Fri, Aug 2, 2013 at 8:17 AM, Tom Lane t...@sss.pgh.pa.us wrote:

Robert Haas robertmh...@gmail.com writes:

On Fri, Jul 26, 2013 at 8:27 AM, Tom Lane t...@sss.pgh.pa.us wrote:

would you expect crash recovery to notice the disappearance of a file
that was touched nowhere in the replayed actions?



Eh, maybe not.  But should we try harder to detect the unexpected
disappearance of one that is?


We do, don't we?  The replay stuff should complain unless it sees a drop
or truncate covering any unaccounted-for pages.


Hmm.  Yeah.  But the OP seems to think it doesn't work.


Yes. I'm afraid that.

My attached script shows that crash recovery re-creates the lost
table file implicitly, and fills some of those blocks (maybe
lower ones) with zero without any notice. We can easily observe
it by using pg_filedump.

Thus, the table file can lose records, but DBA cannot recognize
it because no message is left in the server log.

I agree that this is not a PostgreSQL bug.

However, DBA still needs to detect this table corruption,
brought by several components which PostgreSQL relys on, to
consider restoring from database backup.

If PostgreSQL can detect and tell something about that, it
would be really helpful for DBA to make some critical decision.
I think PostgreSQL will be able to do that.

Regards,
--
Satoshi Nagayasu sn...@uptime.jp
Uptime Technologies, LLC. http://www.uptime.jp
#!/bin/sh

PGHOME=/tmp/pgsql
PGDATA=/tmp/pgsql/data
PATH=${PGHOME}/bin:${PATH}
export PGHOME PGDATA PATH

createdb testdb

psql -e testdbEOF
drop table if exists t1;

create table t1 ( c1 varchar(20) );
checkpoint;

select count(*) from t1;

select relname,relfilenode from pg_class where relname='t1';
select oid,* from pg_database where datname=current_database();

insert into t1 values (trim(to_char(generate_series(1,1000), 
'')) );

-- 1000 records
select count(*) from t1;
select pg_relation_size('t1');

-- write lower blocks in the table file.
checkpoint;

-- write backup blocks and new records in WAL
insert into t1 values (trim(to_char(generate_series(1001,2000), 
'')) );

-- 2000 records
select count(*) from t1;
select pg_relation_size('t1');
EOF

tablefile=`psql -A -t testdbEOF
select
  ( select setting from pg_settings where name='data_directory' ) || '/'  ||
  ( select pg_relation_filepath(oid) from pg_class where relname='t1' )
;
EOF
`

echo $tablefile

# force a process crash, then recovery after that.
killall -9 postgres

# remove the table file.
rm -rf $tablefile

rm -rf ${PGDATA}/*.pid
rm -rf ${PGDATA}/pg_log/*

pg_ctl -w -D ${PGDATA} start

cat ${PGDATA}/pg_log/*

psql -e testdbEOF
-- only a backup block and following wal records can be
-- recovered here. so the table is going to be inconsistent.

-- 1000+ records (inconsistent)
select count(*) from t1;
select pg_relation_size('t1');
EOF

#!/bin/sh

PGHOME=/tmp/pgsql
PGDATA=/tmp/pgsql/data
PATH=${PGHOME}/bin:${PATH}
export PGHOME PGDATA PATH

createdb testdb

psql -e testdbEOF
drop table if exists t1;

create table t1 ( c1 varchar(20) );
checkpoint;

select count(*) from t1;

select relname,relfilenode from pg_class where relname='t1';
select oid,* from pg_database where datname=current_database();

insert into t1 values (trim(to_char(generate_series(1,1000), 
'')) );

select count(*) from t1;
select pg_relation_size('t1');

-- write lower blocks in the table file.
checkpoint;

-- write backup blocks and new records in WAL
insert into t1 values (trim(to_char(generate_series(1001,2000), 
'')) );

select count(*) from t1;
select pg_relation_size('t1');
EOF

tablefile=`psql -A -t testdbEOF
select
  ( select setting from pg_settings where name='data_directory' ) || '/'  || 
  ( select pg_relation_filepath(oid) from pg_class where relname='t1' )
;
EOF
`

echo $tablefile

# force a process crash, then recovery after that.
killall -9 postgres

ls -l $tablefile
pg_filedump -y -S 8192 $tablefile  /tmp/dump_before_drop

# remove the table file.
rm -rf $tablefile

rm -rf ${PGDATA}/*.pid
rm -rf ${PGDATA}/pg_log/*

pg_ctl -w -D ${PGDATA} start -o -p 5433

cat ${PGDATA}/pg_log/*

psql -e testdbEOF
-- if execute a checkpoint here, the table would be recovered
-- with inconsistent state after crash recovery,
-- becaues only the upper blocks can be recovered.
select count(*) from t1;
select pg_relation_size('t1');
EOF

tablefile=`psql -A -t testdbEOF
select
  ( select setting from pg_settings where name='data_directory' ) || '/'  || 
  ( select pg_relation_filepath(oid) from pg_class where relname='t1' )
;
EOF
`
ls -l $tablefile
pg_filedump -y -S 8192 $tablefile  /tmp/dump_after_recovery

-- 
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] how to pass data (tuples) to worker processes?

2013-08-03 Thread Andrew Tipton
On Sat, Aug 3, 2013 at 5:43 AM, Alvaro Herrera alvhe...@2ndquadrant.comwrote:

 Tomas Vondra wrote:

  I'm learning how to use the background worker processes commited in
  9.3. The usage basics are quite nicely illustrated in the worker_spi
  extension (kudos to those who designed the feature / extension).

 Thanks!

  I'm not quite sure how to pass data between the regular backend and a
  worker. Implementing the channel (socket/pipe/...) itself is not a big
  deal, that's IPC 101, but deciding which data to copy (and how) is.
 
  [...]

 Were you able to figure it out?  If so, would you share?


I'm also in the middle of doing some experiments with bgworkers, and for me
it's the IPC part that's proving tricky.  I'd love to have a simple socket
that can be used to communicate with the bgworker.  But because the
bgworker is launched by the postmaster -- and not the backend which
registers it -- there's no chance for the bgworker to inherit one end of
the socketpair().

Tomas:  in the end, what approach did you use for IPC?

Robert:  any chance you could share a few more details on the enhancements
you're planning for bgworkers?  I seem to recall reading that communicating
with the dynamic bgworkers after they had been launched was next on your
agenda...


Regards,
Andrew Tipton


Re: [HACKERS] ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-08-03 Thread Dimitri Fontaine
Greg Stark st...@mit.edu writes:
 Writing out each guc in a separate file is a singularly bad idea. It's

I'm not buying into any of your arguments here, and have something to
add to that part:

 I'm not even clear we do want this in /etc since none of our GUC
 options are repeatable things like Apache virtual servers. It actually
 makes *more* sense for pg_hba than it does for gucs. I think we can
 assume that in the future we'll have something like it however.

Given a conf.d option in /etc it's then quite easy to add per-extension
configuration files in the packaging system, so that users don't have to
edit postgresql.conf for default values.

We still need some kind of repeatable settings that we don't have yet
for that to happen: cumulative setting of a list GUC such as
local_preload_libraries and suchlike.

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: [HACKERS] Need help to begin contribution in PostgreSQL Development - Specifically XML module

2013-08-03 Thread Kevin Grittner
Kodamasimham Pridhvi (MT2012066) pridhvi.kodamasim...@iiitb.org
wrote:

 We students of International Institute of Information Technology
 Bangalore India, are interested to contribute to  PostgreSQL
 development. We identified some modules from ToDo list to which
 we want to contribute.We want to begin with an simple module with
 less dependency like 'Add pretty-printed XML output option'. If
 our work is satisfactory we would like to further contribute for
 module 'Add XML Schema validation and xmlvalidate functions
 (SQL:2008)'. 
 
 If the ToDo items chosen are okay, will you please help us by
 elaborating more details on requirements of module 'Add
 pretty-printed XML output option', we want to begin with this
 module so as to quick overview of complete process.  

The normal process would be for you to search the community
discussion list archives for any previous discussions on the topic,
and propose both an API for how to choose pretty printing and a
format for the pretty-printed output.  Once the there is a
consensus on those questions, you can start coding, post a proposed
patch, add it to the CommitFest application, and participate in the
next CF cycle.

http://wiki.postgresql.org/wiki/Submitting_a_Patch

http://wiki.postgresql.org/wiki/CommitFest

--
Kevin Grittner
EDB: 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


[HACKERS] pg_restore: show object schema name in verbose output

2013-08-03 Thread Ian Lawrence Barwick
I just noticed that pg_restore executing in verbose mode displays the
name of the object being restored, but not its schema.

I'd like to be able to see the fully-qualified object name because
if pg_restore spits out a warning like this:

  $ pg_restore -d somedb  /path/to/dumpfile.pgd
  pg_restore: WARNING:  column session_id has type unknown
  DETAIL:  Proceeding with relation creation anyway.
  $

verbose mode is useful to identify which object is at issue, e.g.:

  $ pg_restore -v -d somedb /path/to/dumpfile.pgd
  pg_restore: connecting to database for restore
(...)
  pg_restore: creating VIEW someview
  pg_restore: WARNING:  column session_id has type unknown
  DETAIL:  Proceeding with relation creation anyway.
(...)
  $

but only shows the bare object name. In the case I recently encountered,
objects with the same name existed in multiple schemas, which meant it
took longer to track down the offending object than it could have done.

The attached patch changes the output to print the schema name too, e.g.:

  $ pg_restore -v -d somedb /path/to/dumpfile.pgd
  pg_restore: connecting to database for restore
(...)
  pg_restore: creating VIEW schema94.someview
  pg_restore: WARNING:  column session_id has type unknown
  DETAIL:  Proceeding with relation creation anyway.
(...)
  $

which is more useful, IMHO.

Regards


Ian Barwick


pg-restore-verbose-output-schema-2013-08-03.patch
Description: Binary data

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