Re: [PATCHES] AllocSetReset improvement

[a_ogawa]

(BTom Lane <[EMAIL PROTECTED]> writes:
(B> >> And I'm worried about adding even a small amount of overhead to
(B> >> palloc/pfree --- on the vast majority of the profiles I look at, those
(B> >> are more expensive than AllocSetReset.
(B>
(B> > I don't worry about palloc. Because overhead increases only when malloc
(B> > is executed in AllocSetAlloc. But I'm wooried about pfree, too. However,
(B> > when palloc/pfree was executed many times, I did not see a bad
(Binfluence.
(B>
(B> In most of the tests I've looked at, palloc/pfree are executed far more
(B> often than AllocSetReset, and so adding even one instruction there to
(B> sometimes save a little work in AllocSetReset is a bad tradeoff.  You
(B> can't optimize to make just one test case look good.
(B
(BI agree. I give up adding instruction to palloc/pfree.
(B
(B> I have another idea though: in the case you are looking at, I think
(B> that the context in question never gets any allocations at all, which
(B> means its blocks list stays null.  We could move the MemSet inside the
(B> "if (blocks)" test --- if there are no blocks allocated to the context,
(B> it surely hasn't got any chunks either, so the MemSet is unnecessary.
(B> That should give us most of the speedup without any extra cost in
(B> palloc/pfree.
(B
(BIt is a reasonable idea. However, the majority part of MemSet was not
(Bable to be avoided by this idea. Because the per-tuple contexts are used
(Bat the early stage of executor.
(B
(B function that calls  numbercontextset->blocks
(B MemoryContextReset   of calls  addressis null
(B-
(B execTuplesMatch(execGrouping.c:65)   450x836dd28  false
(B agg_fill_hash_table(nodeAgg.c:924)   500x836dd28  false
(B ExecHashJoin(nodeHashjoin.c:108) 510x836dec0  false
(B ExecHashJoin(nodeHashjoin.c:217) 500x836dec0  false
(B ExecHashGetHashValue(nodeHash.c:669) 550x836dec0  false
(B ExecScanHashBucket(nodeHash.c:785)   500x836dec0  false
(B ExecScan(execScan.c:86)  570x836df48  true
(B
(BI am considering another idea: I think that we can change behavior of the
(Bcontext by switching the method table of context.
(B
(BAn simple version of AllocSetAlloc and AllocSetReset is made. These API
(Bcan be accelerated instead of using neither a freelist nor the blocks
(B(The keeper excludes it). When the context is initialized and reset,
(Bthese new API is set to the method table. And, when a freelist or a new
(Bblock is needed, the method table is switched to normal API. This can be
(Bexecuted by doing the pfree/repalloc hook. As a result, overhead of pfree
(Bbecomes only once about one context.
(B
(BI think that this idea is effective in context that executes repeatedly
(Breset after small allocations such as per-tuple context.  And I think that
(Boverhead given to the context that executes a lot of palloc/pfree is a
(Bvery few.
(B
(BAn attached patch is a draft of that implementation. Test and comment on
(Bthe source code are insufficient yet.
(B
(Bregards,
(B
(B---
(BAtsushi Ogawa

aset.c.patch
Description: Binary data

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PATCHES] AllocSetReset improvement

[Tom Lane]

a_ogawa <[EMAIL PROTECTED]> writes:
> It is a reasonable idea. However, the majority part of MemSet was not
> able to be avoided by this idea. Because the per-tuple contexts are used
> at the early stage of executor.

Drat.  Well, what about changing that?  We could introduce additional
contexts or change the startup behavior so that the ones that are
frequently reset don't have any data in them unless you are working
with pass-by-ref values inside the inner loop.

> I am considering another idea: I think that we can change behavior of the
> context by switching the method table of context.

That's a possible solution but it feels a bit klugy somehow.  I can't
quite articulate what is bothering me ... will think more.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[Fwd: Re: [HACKERS] [PATCHES] plperl and pltcl installcheck targets]

[Andrew Dunstan]

I don't know what heppened to the original of this. Maybe it caught by 
some filter because of the .tgz file. So instead of including it again I 
have uploaded it to planetpostgresql. It can be got here: 
|http://people.planetpostgresql.org/andrew/wp-content/plregress.tgz

cheers
andrew
|
---
Tom Lane wrote:
Andrew Dunstan <[EMAIL PROTECTED]> writes:
 

Is that what you had in mind?
   

Not entirely.  We should move around the test sql script and the expected
result file so that the file structure looks exactly like one of the
test-enabled contrib modules.  I realize that's a PITA to describe as a
patch --- just tell me in words what to put where (ls -lR maybe) and
it shall be so.
 

OK, here's what I have. The tgz file contains the sql and expected 
directories for the 3 PLs, plus there's a patch for the makefiles. With 
this the following would become redundant:

pl/plperl/test/*
pl/tcl/test/*
pl/plpython/*.sql
pl/plpython/{feature,error}.*
pl/plpython/test.sh
If you wanted to you could preserve history by moving and renaming the 
sql files - I'm not sure it're really worth it.

cheers
andrew

Index: plperl/GNUmakefile
===
RCS file: /home/cvsmirror/pgsql/src/pl/plperl/GNUmakefile,v
retrieving revision 1.18
diff -c -r1.18 GNUmakefile
*** plperl/GNUmakefile  19 Nov 2004 19:22:58 -  1.18
--- plperl/GNUmakefile  13 May 2005 01:03:13 -
***
*** 53,58 
--- 53,63 
 echo "*"
  endif
  
+ TESTS = plperl
+ installcheck:
+   $(top_srcdir)/src/test/regress/pg_regress --load-language=plperl 
$(TESTS)
+ 
+ 
  installdirs:
$(mkinstalldirs) $(DESTDIR)$(pkglibdir)
  
Index: plpython/Makefile
===
RCS file: /home/cvsmirror/pgsql/src/pl/plpython/Makefile,v
retrieving revision 1.18
diff -c -r1.18 Makefile
*** plpython/Makefile   19 Nov 2004 19:23:01 -  1.18
--- plpython/Makefile   12 May 2005 15:18:03 -
***
*** 85,92 
rm -f python${pytverstr}.def
  endif
  
  installcheck:
!   PATH=$(bindir):$$PATH $(SHELL) $(srcdir)/test.sh
  
  else # can't build
  
--- 85,94 
rm -f python${pytverstr}.def
  endif
  
+ TESTS = plpython_schema plpython_populate plpython_function plpython_test 
plpython_error
  installcheck:
!   $(top_srcdir)/src/test/regress/pg_regress --load-language=plpythonu 
$(TESTS)
! 
  
  else # can't build
  
Index: tcl/Makefile
===
RCS file: /home/cvsmirror/pgsql/src/pl/tcl/Makefile,v
retrieving revision 1.44
diff -c -r1.44 Makefile
*** tcl/Makefile16 Dec 2004 20:41:01 -  1.44
--- tcl/Makefile12 May 2005 16:46:58 -
***
*** 57,62 
--- 57,68 
  endif
$(MAKE) -C modules $@
  
+ TESTS = pltcl_setup pltcl_queries
+ installcheck:
+   $(top_srcdir)/src/test/regress/pg_regress --load-language=pltcl $(TESTS)
+ 
+ 
+ 
  installdirs:
$(mkinstalldirs) $(DESTDIR)$(pkglibdir)
$(MAKE) -C modules $@


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] [PATCHES] plperl and pltcl installcheck targets

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> OK, here's what I have. The tgz file contains the sql and expected 
> directories for the 3 PLs, plus there's a patch for the makefiles.

Applied with minor editorialization to make it more like the contrib
installcheck process.

I added a "make installcheck" target in the src/pl directory, so that
you can just fire that one make instead of having to check which PLs
are actually configured.

It occurs to me that maybe we should just add src/pl to the toplevel
installcheck target, which would make the whole thing pretty
automatic.  However, contrib isn't in that target, so maybe the PLs
shouldn't be either.  Thoughts?

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] [PATCHES] plperl and pltcl installcheck targets

[Peter Eisentraut]

Tom Lane wrote:
> It occurs to me that maybe we should just add src/pl to the toplevel
> installcheck target, which would make the whole thing pretty
> automatic.  However, contrib isn't in that target, so maybe the PLs
> shouldn't be either.  Thoughts?

Contrib isn't built by default, so it isn't tested by default.  But the 
PLs that are built should also be tested, I think.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] [PATCHES] plperl and pltcl installcheck targets

[Andrew Dunstan]

Peter Eisentraut wrote:
Tom Lane wrote:
 

It occurs to me that maybe we should just add src/pl to the toplevel
installcheck target, which would make the whole thing pretty
automatic.  However, contrib isn't in that target, so maybe the PLs
shouldn't be either.  Thoughts?
   

Contrib isn't built by default, so it isn't tested by default.  But the 
PLs that are built should also be tested, I think.

 

I agree. That will also mean that buildfarm members will automatically 
start doing the checks (as long as they are set up to build the PLs), so 
it would be an extra bonus for me :-)

cheers
andrew
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [HACKERS] [PATCHES] plperl and pltcl installcheck targets

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> I agree. That will also mean that buildfarm members will automatically 
> start doing the checks (as long as they are set up to build the PLs), so 
> it would be an extra bonus for me :-)

The only argument I can think of against it is that the buildfarm
status page will then not distinguish core system check failures
from PL check failures.  If that doesn't bother you, we can make
it so.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PATCHES] Exec statement logging

[Bruce Momjian]

Simon Riggs wrote:
> Following patch is a minor addition to postgres.c that allows the two
> existing statement logging techniques to work with V3 exec. This then
> allows statement logging with PostgreSQL 8.0+ for JDBC and other V3
> connection types.
> 
> The rationale of this patch is to add functionality without modifying
> existing behaviour. There is expected to be some difficulty with
> log_statement producing a log line at both parse and exec, but some may
> find that useful. Since there are two ways of producing statement
> logging with duration times, setting log_min_duration_statement=0 will
> avoid the logging of statements for both parse and exec. For many this
> method is already the preferred way of logging statement performance
> stats.

Uh, I am confused by this.  Your code test is:

+   if ((log_statement == LOGSTMT_ALL && save_log_duration) ||
+ save_log_min_duration_statement)
+   gettimeofday(&start_t, NULL);

First, log_min_duration_statement == -1 turns off logging.  Your test
would enable it for -1.  Also, you added "log_statement == LOGSTMT_ALL",
but don't have a similar test lower down where gettimeofday is used, so
I don't understand its usage here, or why it would be used in this
place.  The original test is:

+   if (save_log_duration || save_log_min_duration_statement != -1)
+   gettimeofday(&start_t, NULL);

> There is no attempt to log parameters, since these are not often
> required for performance analysis.

OK.

> The enclosed patch has been tested against cvstip.
> 
> I also see this as a backpatch onto 8.0, since it prevents statements
> from being logged as described in the manual and prevents effective
> performance tuning. It has not been tested against 8.0.2, though was
> originally written against 8.0.1 and is believed to apply cleanly.

I don't think it should be backpatched.  This is a behavior changes that
can be seen as a feature addition.

> Some code has been duplicated with this patch; refactoring and cleanup
> can be performed should anybody desire it.

Not sure it is worth it considering how many variables are involved.

> The patch was produced quickly to assist tuning efforts during
> Scalability & Performance benchmarking of PostgreSQL 8.0 carried out at
> Unisys Corporation's Mission Viejo engineering laboratory. The
> development was sponsored by Unisys Corporation and the patch has now
> been donated to the PostgreSQL community under the standard
> PostgreSQL/BSD licence. Approval for release of this code has been given
> in writing to me by the Director, Open Runtime Products, Unisys on April
> 8, 2005.

I have made a new version of the patch using your patch only as a guide.
I copied the sections you suggested.  It compiles fine but I would like
someone to test that it actually works for client-side EXECUTE.  I don't
have a test setup for that here.

One thing you did was to log debug_query_string, but I don't see how
that could be the right value.  I assume it would be empty in a
client-side execute, or be the previous query.  I instead used "EXECUTE
portal_name" because that is what we are passed from the client.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: src/backend/tcop/postgres.c
===
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.443
diff -c -c -r1.443 postgres.c
*** src/backend/tcop/postgres.c 21 Apr 2005 19:18:13 -  1.443
--- src/backend/tcop/postgres.c 14 May 2005 20:22:17 -
***
*** 1011,1017 
stop_t.tv_sec--;
stop_t.tv_usec += 100;
}
!   usecs = (long) (stop_t.tv_sec - start_t.tv_sec) * 100 + 
(long) (stop_t.tv_usec - start_t.tv_usec);
  
/* Only print duration if we previously printed the statement. 
*/
if (statement_logged && save_log_duration)
--- 1011,1018 
stop_t.tv_sec--;
stop_t.tv_usec += 100;
}
!   usecs = (long) (stop_t.tv_sec - start_t.tv_sec) * 100 +
!   (long) (stop_t.tv_usec - start_t.tv_usec);
  
/* Only print duration if we previously printed the statement. 
*/
if (statement_logged && save_log_duration)
***
*** 1579,1584 
--- 1580,1590 
boolis_trans_exit = false;
boolcompleted;
charcompletionTag[COMPLETION_TAG_BUFSIZE];
+   struct timeval start_t,
+   stop_t;
+   boolsave_log_duration = log_duration;
+   int save_log_min_duration_state

Re: [HACKERS] [PATCHES] plperl and pltcl installcheck targets

[Andrew Dunstan]

Tom Lane wrote:
Andrew Dunstan <[EMAIL PROTECTED]> writes:
 

I agree. That will also mean that buildfarm members will automatically 
start doing the checks (as long as they are set up to build the PLs), so 
it would be an extra bonus for me :-)
   

The only argument I can think of against it is that the buildfarm
status page will then not distinguish core system check failures
from PL check failures.  If that doesn't bother you, we can make
it so.
 

That's probably a good point. I was just being a little too lazy. 
Building an extra check step in is not very difficult - it will just 
take a little while for all the buildfarm emebers to catch up. But since 
we're still 6 weeks even from feature freeze that shouldn't matter too 
much. I'll get onto it.

cheers
andrew
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [PATCHES] Exec statement logging

[Tom Lane]

Bruce Momjian  writes:
> One thing you did was to log debug_query_string, but I don't see how
> that could be the right value.  I assume it would be empty in a
> client-side execute, or be the previous query.  I instead used "EXECUTE
> portal_name" because that is what we are passed from the client.

You could use portal->sourceText whenever that isn't null; it should be
the querystring that the portal was created by.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PATCHES] [GENERAL] pgsql config flag update: apple rendezvous renamed bonjour

[Bruce Momjian]

OpenMacNews wrote:
> hi all,
> 
> fyi:
> 
> "Apple's Rendezvous renamed 'Bonjour'"
> 
> 
> 
> since it's now official, might it be time to add a conditional for:
> 
> --with-rendezvous || --with-bonjour
> 
> in configure?
> 
> sorry if its 'old news' =)

OK, patch applied to rename Rendezvous to Bonjour.  This will be in 8.1.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: configure
===
RCS file: /cvsroot/pgsql/configure,v
retrieving revision 1.437
diff -c -c -r1.437 configure
*** configure   7 May 2005 05:48:50 -   1.437
--- configure   15 May 2005 00:16:13 -
***
*** 871,877 
--with-krb5 build with Kerberos 5 support
--with-krb-srvnam=NAME  name of the service principal in Kerberos [postgres]
--with-pam  build with PAM support
!   --with-rendezvous   build with Rendezvous support
--with-openssl  build with OpenSSL support
--without-readline  do not use Readline
--without-zlib  do not use Zlib
--- 871,877 
--with-krb5 build with Kerberos 5 support
--with-krb-srvnam=NAME  name of the service principal in Kerberos [postgres]
--with-pam  build with PAM support
!   --with-bonjour  build with Bonjour support
--with-openssl  build with OpenSSL support
--without-readline  do not use Readline
--without-zlib  do not use Zlib
***
*** 3431,3452 
  
  
  #
! # Rendezvous
  #
! echo "$as_me:$LINENO: checking whether to build with Rendezvous support" >&5
! echo $ECHO_N "checking whether to build with Rendezvous support... $ECHO_C" 
>&6
  
  
  
! # Check whether --with-rendezvous or --without-rendezvous was given.
! if test "${with_rendezvous+set}" = set; then
!   withval="$with_rendezvous"
  
case $withval in
  yes)
  
  cat >>confdefs.h <<\_ACEOF
! #define USE_RENDEZVOUS 1
  _ACEOF
  
;;
--- 3431,3452 
  
  
  #
! # Bonjour
  #
! echo "$as_me:$LINENO: checking whether to build with Bonjour support" >&5
! echo $ECHO_N "checking whether to build with Bonjour support... $ECHO_C" >&6
  
  
  
! # Check whether --with-bonjour or --without-bonjour was given.
! if test "${with_bonjour+set}" = set; then
!   withval="$with_bonjour"
  
case $withval in
  yes)
  
  cat >>confdefs.h <<\_ACEOF
! #define USE_BONJOUR 1
  _ACEOF
  
;;
***
*** 3454,3472 
:
;;
  *)
!   { { echo "$as_me:$LINENO: error: no argument expected for 
--with-rendezvous option" >&5
! echo "$as_me: error: no argument expected for --with-rendezvous option" >&2;}
 { (exit 1); exit 1; }; }
;;
esac
  
  else
!   with_rendezvous=no
  
  fi;
  
! echo "$as_me:$LINENO: result: $with_rendezvous" >&5
! echo "${ECHO_T}$with_rendezvous" >&6
  
  
  
--- 3454,3472 
:
;;
  *)
!   { { echo "$as_me:$LINENO: error: no argument expected for 
--with-bonjour option" >&5
! echo "$as_me: error: no argument expected for --with-bonjour option" >&2;}
 { (exit 1); exit 1; }; }
;;
esac
  
  else
!   with_bonjour=no
  
  fi;
  
! echo "$as_me:$LINENO: result: $with_bonjour" >&5
! echo "${ECHO_T}$with_bonjour" >&6
  
  
  
***
*** 9674,9680 
  
  fi
  
! if test "$with_rendezvous" = yes ; then
if test "${ac_cv_header_DNSServiceDiscovery_DNSServiceDiscovery_h+set}" = 
set; then
echo "$as_me:$LINENO: checking for 
DNSServiceDiscovery/DNSServiceDiscovery.h" >&5
  echo $ECHO_N "checking for DNSServiceDiscovery/DNSServiceDiscovery.h... 
$ECHO_C" >&6
--- 9674,9680 
  
  fi
  
! if test "$with_bonjour" = yes ; then
if test "${ac_cv_header_DNSServiceDiscovery_DNSServiceDiscovery_h+set}" = 
set; then
echo "$as_me:$LINENO: checking for 
DNSServiceDiscovery/DNSServiceDiscovery.h" >&5
  echo $ECHO_N "checking for DNSServiceDiscovery/DNSServiceDiscovery.h... 
$ECHO_C" >&6
***
*** 9779,9786 
  if test $ac_cv_header_DNSServiceDiscovery_DNSServiceDiscovery_h = yes; then
:
  else
!   { { echo "$as_me:$LINENO: error: header file 
 is required for Rendezvous" >&5
! echo "$as_me: error: header file  
is required for Rendezvous" >&2;}
 { (exit 1); exit 1; }; }
  fi
  
--- 9779,9786 
  if test $ac_cv_header_DNSServiceDiscovery_DNSServiceDiscovery_h = yes; then
:
  else
!   { { echo "$as_me:$LINENO: error: header file 
 is required for Bonjour" >&5
! echo "$as_me: error: header file  
is required for Bonjour" >&2;}
 { (exit 1); exit 1; }; }
  fi
  
***
*** 19710,19716 
  s,@with_krb5@,$with_krb5,;t t
  s,@krb_srvtab@,$krb_sr

[PATCHES] [Fwd: Re: [HACKERS] alternate regression dbs?]

[Andrew Dunstan]

Darn ... previously sent to wrong list
Tom Lane wrote:
Andrew Dunstan <[EMAIL PROTECTED]> writes:
 

Currently the pg_regress script does "dbname=regression" and then does 
everything in terms of $dbname.  Would there be any value in providing a 
--dbname=foo parameter so that different regression sets could use their 
own db? One virtue at least might be that we would not drop the core 
regression db all the time - having it around can be useful, I think.
   

I'd be in favor of using three such DBs, one for core, PLs, and contrib.
(More than that seems like it would clutter the disk a lot.)  But I do
use the standard regression DB as a handy testbed for a lot of stuff,
and it has bothered me in the past that the contrib installcheck wipes
it out.
 

Try attached ... season to taste. The bulk of it is changes for dblink 
which has the dbname hardcoded.

There is probably more to be done with the regression stuff, but this 
and the earlier change give us the low hanging fruit at least, I think.

cheers
andrew

Index: contrib/contrib-global.mk
===
RCS file: /projects/cvsroot/pgsql/contrib/contrib-global.mk,v
retrieving revision 1.8
diff -c -r1.8 contrib-global.mk
*** contrib/contrib-global.mk	30 Jul 2004 12:26:39 -	1.8
--- contrib/contrib-global.mk	15 May 2005 02:02:50 -
***
*** 1,4 
--- 1,5 
  # $PostgreSQL: pgsql/contrib/contrib-global.mk,v 1.8 2004/07/30 12:26:39 petere Exp $
  
  NO_PGXS = 1
+ TEST_DB = $(CONTRIB_TESTDB)
  include $(top_srcdir)/src/makefiles/pgxs.mk
Index: contrib/dblink/expected/dblink.out
===
RCS file: /projects/cvsroot/pgsql/contrib/dblink/expected/dblink.out,v
retrieving revision 1.13
diff -c -r1.13 dblink.out
*** contrib/dblink/expected/dblink.out	7 Mar 2004 02:27:00 -	1.13
--- contrib/dblink/expected/dblink.out	15 May 2005 02:02:51 -
***
*** 98,104 
  
  -- regular old dblink
  SELECT *
! FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
   a | b | c  
  ---+---+
--- 98,104 
  
  -- regular old dblink
  SELECT *
! FROM dblink('dbname=contrib_regression','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
   a | b | c  
  ---+---+
***
*** 112,118 
  WHERE t.a > 7;
  ERROR:  connection not available
  -- create a persistent connection
! SELECT dblink_connect('dbname=regression');
   dblink_connect 
  
   OK
--- 112,118 
  WHERE t.a > 7;
  ERROR:  connection not available
  -- create a persistent connection
! SELECT dblink_connect('dbname=contrib_regression');
   dblink_connect 
  
   OK
***
*** 260,273 
  ERROR:  connection not available
  -- put more data into our slave table, first using arbitrary connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('dbname=regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
   substr 
  
   INSERT
  (1 row)
  
  -- create a persistent connection
! SELECT dblink_connect('dbname=regression');
   dblink_connect 
  
   OK
--- 260,273 
  ERROR:  connection not available
  -- put more data into our slave table, first using arbitrary connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('dbname=contrib_regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
   substr 
  
   INSERT
  (1 row)
  
  -- create a persistent connection
! SELECT dblink_connect('dbname=contrib_regression');
   dblink_connect 
  
   OK
***
*** 383,389 
  DETAIL:  missing "=" after "myconn" in connection info string
  
  -- create a named persistent connection
! SELECT dblink_connect('myconn','dbname=regression');
   dblink_connect 
  
   OK
--- 383,389 
  DETAIL:  missing "=" after "myconn" in connection info string
  
  -- create a named persistent connection
! SELECT dblink_connect('myconn','dbname=contrib_regression');
   dblink_connect 
  
   OK
***
*** 420,429 
  
  -- create a second named persistent connection
  -- should error with "duplicate connection name"
! SELECT dblink_connect('myconn','dbname=regression');
  ERROR:  duplicate connection name
  -- create a second named persistent connection with a new name
! SELECT dblink_connect('myconn2','dbname=regression');
   dblink_connect 
  
   OK
--- 420,429 
  
  -- create a second named persistent connection
  -- should error with "duplicate connection name"
! SELECT dblink_connect('myconn','dbname=contrib_regression');
  ERROR:  duplicate connection name
  -- create a second named persistent connection with a new name
! SELECT dblink_conne

Re: [PATCHES] Exec statement logging

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > One thing you did was to log debug_query_string, but I don't see how
> > that could be the right value.  I assume it would be empty in a
> > client-side execute, or be the previous query.  I instead used "EXECUTE
> > portal_name" because that is what we are passed from the client.
> 
> You could use portal->sourceText whenever that isn't null; it should be
> the querystring that the portal was created by.

Interesting, but right now we don't display the query using EXECUTE:

test=> SET client_min_messages='log';
SET
test=> SET log_statement = 'all';
SET
test=> PREPARE xx AS SELECT 1;
LOG:  statement: PREPARE xx AS SELECT 1;
PREPARE
test=> EXECUTE xx;
LOG:  statement: EXECUTE xx;
 ?column?
--
1
(1 row)

test=>

so I assume client-side EXECUTE shouldn't either.  Do people want it
displayed?  The patch actually hard-codes the word EXECUTE after
"statement:" and prints the portal name:

LOG:  statement: EXECUTE xx

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings