date:20140603

Re: [HACKERS] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?

2014-06-03 Thread Martijn van Oosterhout

On Mon, Jun 02, 2014 at 01:29:25PM -0400, Robert Haas wrote:
 I agree, but I think it's important to note that Alex's complaint is
 not unique - the way things work now is a real source of frustration
 for users.  In a previous job, I wrote a schema-upgrade script that
 dropped all of the views in reverse creation order, applied the schema
 updates, and then recreated all the views. This worked, but it was a
 lot of hassle that I would have preferred to avoid, and in a
 higher-volume application, simultaneously grabbing exclusive locks on
 a large number of critical views would have been a non-starter.  In
 the job before that, I did the same thing manually, which was no fun
 at all.  This was actually what posted me to write one of my first
 patches, committed by Bruce as
 ff1ea2173a92dea975d399a4ca25723f83762e55.

Would it be sufficient to automatically pass the type change through
only if nothing in the view actually references it in a function,
operator, group by, order by, etc?  That is, it only appears in the
SELECT list unadorned?  Or is that too limiting?

Have a nice day,
-- 
Martijn van Oosterhout   klep...@svana.org   http://svana.org/kleptog/
 He who writes carelessly confesses thereby at the very outset that he does
 not attach much importance to his own thoughts.
   -- Arthur Schopenhauer


signature.asc
Description: Digital signature

Re: [HACKERS] 9.4 release notes

2014-06-03 Thread Peter Geoghegan

On Sun, May 4, 2014 at 5:46 AM, Bruce Momjian br...@momjian.us wrote:
 Feedback expected and welcomed.

One item currently reads Improve valgrind error reporting.  I
suggest this be changed to Add support for Valgrind memcheck memory
error detector. It was possible to use the tool before, but the lack
of cooperation from Postgres made this far less useful.

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

[HACKERS] Memory deallocation after calling cast function

2014-06-03 Thread Soroosh Sardari

Hi

I have problem with memory deallocation. look at the following queries

1- create table test01(a) as select generate_series(1,1)::int8 ;

2- create table test02(a) as select generate_series(1,1) ;

In execution of first query, memory usage increase rapidly until the
transaction comes to end and deallocate all the memory which allocated with
palloc.
I have wondered why all the memory deallocated at the end, so the cast is
removed and query executed again. memory usage was not the same. It was
grow very slow.

I need help to find the right point to deallocate the memory,
Any idea will be appreciated.

Soroosh Sardari

Re: [HACKERS] pg_stat directory and pg_stat_statements

2014-06-03 Thread Magnus Hagander

On Mon, Jun 2, 2014 at 4:07 PM, Andres Freund and...@2ndquadrant.com
wrote:

 On 2014-06-02 22:59:55 +0900, Fujii Masao wrote:
  On Thu, May 29, 2014 at 3:02 PM, Peter Geoghegan p...@heroku.com wrote:
   On Wed, May 28, 2014 at 10:49 PM, Fujii Masao masao.fu...@gmail.com
 wrote:
   You're concerned about the scenario using pg_upgrade? I'm not sure
 the detail
   of pg_upgrade. But if it doesn't work properly, we should have gotten
   the trouble
  
   I'm not worried about pg_upgrade, because by design pg_stat_statements
   will discard stats files that originated in earlier versions. However,
   I don't see a need to change pg_stat_statements to serialize its
   statistics to disk in the pg_stat directory before we branch off 9.4.
   As you mentioned, it's harmless.
 
  Yeah, that's an idea. OTOH, there is no *strong* reason to postpone
  the fix to 9.5. So I just feel inclined to apply the fix now...

 +1 for fixing it now.


+1 for fixing it for 9.4 before the next beta, but *not* backpatching it to
9.3 - it *is* a behaviour change after all..

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] [BUGS] BUG #9652: inet types don't support min/max

2014-06-03 Thread Andres Freund


Hi,

On 2014-06-03 12:43:28 +1000, Haribabu Kommi wrote:
 *** a/src/test/regress/expected/create_function_3.out
 --- b/src/test/regress/expected/create_function_3.out
 ***
 *** 153,389  RESET SESSION AUTHORIZATION;
   SELECT proname, prorettype::regtype, proargtypes::regtype[]
  FROM pg_proc JOIN pg_namespace ON pronamespace = pg_namespace.oid
  WHERE nspname = 'pg_catalog' AND proleakproof ORDER BY proname;
 ! proname | prorettype | proargtypes  

 ! 
 ++-
 !  abstimeeq  | boolean| [0:1]={abstime,abstime}
...
 !  xideq  | boolean| [0:1]={xid,xid}
 ! (228 rows)
   
   --
   -- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
 --- 153,391 
   SELECT proname, prorettype::regtype, proargtypes::regtype[]
  FROM pg_proc JOIN pg_namespace ON pronamespace = pg_namespace.oid
  WHERE nspname = 'pg_catalog' AND proleakproof ORDER BY proname;
 !  proname | prorettype | proargtypes 
 
 ! 
 -++-
 !  abstimeeq   | boolean| [0:1]={abstime,abstime}
...
 !  xideq   | boolean| [0:1]={xid,xid}
 ! (230 rows)

I didn't reall look at the patch, but it very much looks to me like that
query result could use the \a\t treatment that rules.sql and
sanity_check.sql got. It's hard to see the actual difference
before/after the patch.
I'll patch that now, to reduce the likelihood of changes there causing
conflicts for more people.

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] Proposing pg_hibernate

2014-06-03 Thread Robert Haas

On Thu, May 29, 2014 at 12:12 AM, Amit Kapila amit.kapil...@gmail.com wrote:
 IMHO, all of these caveats, would affect a very small fraction of
 use-cases and are eclipsed by the benefits this extension provides in
 normal cases.

 I agree with you that there are only few corner cases where evicting
 shared buffers by this utility would harm, but was wondering if we could
 even save those, say if it would only use available free buffers.  I think
 currently there is no such interface and inventing a new interface for this
 case doesn't seem to reasonable unless we see any other use case of
 such a interface.

It seems like it would be best to try to do this at cluster startup
time, rather than once recovery has reached consistency.  Of course,
that might mean doing it with a single process, which could have its
own share of problems.  But I'm somewhat inclined to think that if
recovery has already run for a significant period of time, the blocks
that recovery has brought into shared_buffers are more likely to be
useful than whatever pg_hibernate would load.

-- 
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] Proposing pg_hibernate

2014-06-03 Thread Gurjeet Singh

On Tue, Jun 3, 2014 at 7:57 AM, Robert Haas robertmh...@gmail.com wrote:
On Thu, May 29, 2014 at 12:12 AM, Amit Kapila amit.kapil...@gmail.com wrote:
IMHO, all of these caveats, would affect a very small fraction of
use-cases and are eclipsed by the benefits this extension provides in
normal cases.

I agree with you that there are only few corner cases where evicting
shared buffers by this utility would harm, but was wondering if we could
even save those, say if it would only use available free buffers. I think
currently there is no such interface and inventing a new interface for this
case doesn't seem to reasonable unless we see any other use case of
such a interface.

It seems like it would be best to try to do this at cluster startup
time, rather than once recovery has reached consistency. Of course,
that might mean doing it with a single process, which could have its
own share of problems. But I'm somewhat inclined to think that if
recovery has already run for a significant period of time, the blocks
that recovery has brought into shared_buffers are more likely to be
useful than whatever pg_hibernate would load.

I am not absolutely sure of the order of execution between recovery
process and the BGWorker, but ...

For sizeable shared_buffers size, the restoration of the shared
buffers can take several seconds. I have a feeling the users wouldn't
like their master database take up to a few minutes to start accepting
connections. From my tests [1], In the 'App after Hibernator' [case]
... This took 70 seconds for reading the ~4 GB database.

[1]:
http://gurjeet.singh.im/blog/2014/04/30/postgres-hibernator-reduce-planned-database-down-times/

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

EDB 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] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?

2014-06-03 Thread Robert Haas

On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 I can see two answers.  Answer #1 is
 that the column type of bar.a changes from int to bigint and the view
 definition is still SELECT a FROM foo.  In that case, showing the user
 the SQL does not help them see and approve semantic changes because
 the SQL is completely unchanged.

 Yeah, we need some way of highlighting the semantic differences, and just
 printing ruleutils.c output doesn't do that.  But if the user is going to
 put in a change to whatever choice the tool makes by default here,
 I would expect that change to consist of adding (or removing) an explicit
 cast in the SQL-text view definition.  We can't make people learn some
 random non-SQL notation for this.

 Perhaps the displayed output of the tool could look something like

 CREATE VIEW bar AS
   SELECT
 a  -- this view output column will now be of type int8 not int4
   FROM foo;

 Or something else; I don't claim to be a good UI designer.  But in the
 end, this is 90% a UI problem, and that means that raw SQL is seriously
 poorly suited to solve it directly.

I guess I don't agree that is 90% a UI problem.  There's currently no
mechanism whatsoever by means of which a user can change the data type
of a column upon which a view depends.  If we had such a mechanism,
then perhaps someone could build a UI providing the sort of user
feedback you're suggesting to help them use it more safely.  But isn't
the core server support the first thing?

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

[HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Vik Fearing

This patch implements a timeout for broken clients that idle in transaction.

This is a TODO item.

When the timeout occurs, the backend commits suicide with a FATAL
ereport.  I thought about just aborting the transaction to free the
locks but decided that the client is clearly broken so might as well
free up the connection as well.

The same behavior can be achieved by an external script that monitors
pg_stat_activity but by having it in core we get much finer tuning (it
is session settable) and also traces of it directly in the PostgreSQL
logs so it can be graphed by things like pgbadger.

Unfortunately, no notification is sent to the client because there's no
real way to do that right now.

-- 
Vik

*** a/contrib/postgres_fdw/connection.c
--- b/contrib/postgres_fdw/connection.c
***
*** 343,348  configure_remote_session(PGconn *conn)
--- 343,356 
  		do_sql_command(conn, SET extra_float_digits = 3);
  	else
  		do_sql_command(conn, SET extra_float_digits = 2);
+ 
+ 	/*
+ 	 * Ensure the remote server doesn't kill us off if we remain idle in a
+ 	 * transaction for too long.
+ 	 * XXX: The version number must be corrected prior to commit!
+ 	 */
+ 	if (remoteversion = 90400)
+ 		do_sql_command(conn, SET idle_in_transaction_timeout = 0);
  }
  
  /*
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***
*** 5545,5550  COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
--- 5545,5566 
/listitem
   /varlistentry
  
+  varlistentry id=guc-idle-in-transaction-timeout xreflabel=idle_in_transaction_timeout
+   termvarnameidle_in_transaction_timeout/varname (typeinteger/type)
+   indexterm
+primaryvarnameidle_in_transaction_timeout/ configuration parameter/primary
+   /indexterm
+   /term
+   listitem
+para
+ Terminate any session that is idle in transaction for longer than the specified
+ number of seconds.  This not only allows any locks to be released, but also allows
+ the connection slot to be reused.  However, aborted idle in transaction sessions
+ are not affected.  A value of zero (the default) turns this off.
+/para
+   /listitem
+  /varlistentry
+ 
   varlistentry id=guc-vacuum-freeze-table-age xreflabel=vacuum_freeze_table_age
termvarnamevacuum_freeze_table_age/varname (typeinteger/type)
indexterm
*** a/doc/src/sgml/mvcc.sgml
--- b/doc/src/sgml/mvcc.sgml
***
*** 676,682  ERROR:  could not serialize access due to read/write dependencies among transact
   listitem
para
 Don't leave connections dangling quoteidle in transaction/quote
!longer than necessary.
/para
   /listitem
   listitem
--- 676,684 
   listitem
para
 Don't leave connections dangling quoteidle in transaction/quote
!longer than necessary.  The configuration parameter
!xref linkend=guc-idle-in-transaction-timeout may be used to
!automatically disconnect lingering sessions.
/para
   /listitem
   listitem
*** a/src/backend/storage/lmgr/proc.c
--- b/src/backend/storage/lmgr/proc.c
***
*** 57,62 
--- 57,63 
  int			DeadlockTimeout = 1000;
  int			StatementTimeout = 0;
  int			LockTimeout = 0;
+ int			IdleInTransactionTimeout = 0;
  bool		log_lock_waits = false;
  
  /* Pointer to this process's PGPROC and PGXACT structs, if any */
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
***
*** 3937,3942  PostgresMain(int argc, char *argv[],
--- 3937,3946 
  			{
  set_ps_display(idle in transaction, false);
  pgstat_report_activity(STATE_IDLEINTRANSACTION, NULL);
+ 
+ /* Start the idle-in-transaction timer, in seconds */
+ if (IdleInTransactionTimeout  0)
+ 	enable_timeout_after(IDLE_IN_TRANSACTION_TIMEOUT, 1000*IdleInTransactionTimeout);
  			}
  			else
  			{
***
*** 3970,3976  PostgresMain(int argc, char *argv[],
  		DoingCommandRead = false;
  
  		/*
! 		 * (5) check for any other interesting events that happened while we
  		 * slept.
  		 */
  		if (got_SIGHUP)
--- 3974,3986 
  		DoingCommandRead = false;
  
  		/*
! 		 * (5) turn off the idle-in-transaction timeout
! 		 */
! 		if (IdleInTransactionTimeout  0)
! disable_timeout(IDLE_IN_TRANSACTION_TIMEOUT, false);
! 
! 		/*
! 		 * (6) check for any other interesting events that happened while we
  		 * slept.
  		 */
  		if (got_SIGHUP)
***
*** 3980,3986  PostgresMain(int argc, char *argv[],
  		}
  
  		/*
! 		 * (6) process the command.  But ignore it if we're skipping till
  		 * Sync.
  		 */
  		if (ignore_till_sync  firstchar != EOF)
--- 3990,3996 
  		}
  
  		/*
! 		 * (7) process the command.  But ignore it if we're skipping till
  		 * Sync.
  		 */
  		if (ignore_till_sync  firstchar != EOF)
*** a/src/backend/utils/errcodes.txt
--- b/src/backend/utils/errcodes.txt

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Abhijit Menon-Sen

At 2014-06-03 15:06:11 +0200, vik.fear...@dalibo.com wrote:

 This patch implements a timeout for broken clients that idle in
 transaction.

I think this is a nice feature, but I suggest that (at the very least)
the GUC should be named idle_transaction_timeout.

 +para
 + Terminate any session that is idle in transaction for longer than 
 the specified
 + number of seconds.  This not only allows any locks to be released, 
 but also allows
 + the connection slot to be reused.  However, aborted idle in 
 transaction sessions
 + are not affected.  A value of zero (the default) turns this off.
 +/para

I suggest:

Terminate any session with an open transaction that has been idle
for longer than the specified duration in seconds. This allows any
locks to be released and the connection slot to be reused.

It's not clear to me what However, aborted idle in transaction sessions
are not affected means.

The default value of 0 means that such sessions will not be
terminated.

-- 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] Proposal for CSN based snapshots

2014-06-03 Thread Robert Haas

On Fri, May 30, 2014 at 2:38 PM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
 (forgot to answer this question)

 On 05/30/2014 06:27 PM, Andres Freund wrote:

 On 2014-05-30 17:59:23 +0300, Heikki Linnakangas wrote:

 * I expanded pg_clog to 64-bits per XID, but people suggested keeping
 pg_clog as is, with two bits per commit, and adding a new SLRU for the
 commit LSNs beside it. Probably will need to do something like that to
 avoid
 bloating the clog.


 It also influences how on-disk compatibility is dealt with. So: How are
 you planning to deal with on-disk compatibility?


 Have pg_upgrade read the old clog and write it out in the new format.

That doesn't address Bruce's concern about CLOG disk consumption.

-- 
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] Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?

2014-06-03 Thread David G Johnston

On Tuesday, June 3, 2014, Robert Haas [via PostgreSQL]
ml-node+s1045698n5805857...@n5.nabble.com wrote:

On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane [hidden email]
http://user/SendEmail.jtp?type=nodenode=5805857i=0 wrote:

I can see two answers. Answer #1 is
that the column type of bar.a changes from int to bigint and the view
definition is still SELECT a FROM foo. In that case, showing the user
the SQL does not help them see and approve semantic changes because
the SQL is completely unchanged.

Yeah, we need some way of highlighting the semantic differences, and
just
printing ruleutils.c output doesn't do that. But if the user is going
to
put in a change to whatever choice the tool makes by default here,
I would expect that change to consist of adding (or removing) an
explicit
cast in the SQL-text view definition. We can't make people learn some
random non-SQL notation for this.

Perhaps the displayed output of the tool could look something like

CREATE VIEW bar AS
SELECT
a -- this view output column will now be of type int8 not
int4
FROM foo;

Or something else; I don't claim to be a good UI designer. But in the
end, this is 90% a UI problem, and that means that raw SQL is seriously
poorly suited to solve it directly.

I guess I don't agree that is 90% a UI problem. There's currently no
mechanism whatsoever by means of which a user can change the data type
of a column upon which a view depends. If we had such a mechanism,
then perhaps someone could build a UI providing the sort of user
feedback you're suggesting to help them use it more safely. But isn't
the core server support the first thing?

The current mechanism is DROP VIEWs - ALTER TABLE - CREATE VIEWs

The UI would prompt the user for the desired ALTER TABLE
parameters, calculate the DROP/CREATE commands, then issue all three sets
as a single transaction.

Having a more surgical REWRITE RULE command to alter a view without
dropping it may provide for performance improvements but, conceptually, the
current mechanism should be sufficient to allow for this tool to be
developed.

The main thing that core could do to help is to store as text of the
original create view command - though it may be sufficient to reverse
engineer from the rule. Having both available would give any tools more
options.

David J.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-create-dependent-views-on-ALTER-TABLE-ALTER-COLUMN-TYPE-tp5804972p5805864.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Vik Fearing

On 06/03/2014 03:30 PM, Abhijit Menon-Sen wrote:
 At 2014-06-03 15:06:11 +0200, vik.fear...@dalibo.com wrote:
 This patch implements a timeout for broken clients that idle in
 transaction.
 I think this is a nice feature, but I suggest that (at the very least)
 the GUC should be named idle_transaction_timeout.

I prefer the way I have it, but not enough to put up a fight if other
people like your version better.

 +para
 + Terminate any session that is idle in transaction for longer than 
 the specified
 + number of seconds.  This not only allows any locks to be released, 
 but also allows
 + the connection slot to be reused.  However, aborted idle in 
 transaction sessions
 + are not affected.  A value of zero (the default) turns this off.
 +/para
 I suggest:

 Terminate any session with an open transaction that has been idle
 for longer than the specified duration in seconds. This allows any
 locks to be released and the connection slot to be reused.

 It's not clear to me what However, aborted idle in transaction sessions
 are not affected means.

 The default value of 0 means that such sessions will not be
 terminated.

How about this?

Terminate any session with an open transaction that has been idle
for longer than the specified duration in seconds. This allows any
locks to be released and the connection slot to be reused.

Sessions in the state idle in transaction (aborted) occupy a
connection slot but because they hold no locks, they are not
considered by this parameter.

The default value of 0 means that such sessions will not be
terminated.
-- 
Vik


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

77 matches

Mail list logo