Re: [HACKERS] pg_upgrade changes can it use CREATE EXTENSION?

2017-09-05 Thread Sandro Santilli
On Wed, Aug 30, 2017 at 06:01:58PM -0400, Tom Lane wrote:
> "Regina Obe"  writes:
> > I think this thread covers most of the issues.
> > https://lists.osgeo.org/pipermail/postgis-devel/2017-August/026355.html
> > My thought was is it possible for pg_upgrade to be taught to use CREATE
> > EXENSION if asked? 
> 
> We intentionally *don't* do that; pg_dump goes to a lot of trouble to
> duplicate the old extension contents exactly, instead.  There are a bunch
> of corner cases that would fail if we allowed the new installation to
> have different extension contents than the old.  Believe you me, we'd
> rather have just issued CREATE EXTENSION, but it doesn't work.

Did you mean `pg_upgrade` ("goes to a lot of trouble") ?
Because I'm pretty sure I saw a `CREATE EXTENSION` in a dump created by
pg_dump from PostgreSQL 9.6

> Looking quickly at the thread you cite, I wonder how much of this problem
> is caused by including version numbers in the library's .so filename.
> Have you considered not doing that? 

The name change is intentional, to reflect a promise we make between
patch-level releases but not between minor-level releases. The promise
to keep C function signatures referenced by SQL objects immutable and
behavior compatible.

--strk;


-- 
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] make check false success

2017-06-05 Thread Sandro Santilli
On Fri, Jun 02, 2017 at 08:20:25AM -0400, Robert Haas wrote:
> On Thu, Jun 1, 2017 at 10:18 AM, Sandro Santilli <s...@kbt.io> wrote:
> > I noticed that the `check` Makefile rule imported by PGXS is giving
> > a success exit code even when it is unsupported.
> >
> > The attached patch fixes that.
> 
> Hmm.  I'm not 100% sure that the existing behavior is wrong.

Why not ? The caller is attempting to make an unsupported target,
how's that different from calling `make unexistent` ?

--strk;


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


[HACKERS] make check false success

2017-06-01 Thread Sandro Santilli
I noticed that the `check` Makefile rule imported by PGXS is giving
a success exit code even when it is unsupported.

The attached patch fixes that.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   https://strk.kbt.io/services.html
>From 43fa28f141871a6efdd3e5d0c9ec8cc537585ff5 Mon Sep 17 00:00:00 2001
From: Sandro Santilli <s...@kbt.io>
Date: Thu, 1 Jun 2017 16:14:58 +0200
Subject: [PATCH] Make sure `make check` fails when it cannot be run

---
 src/makefiles/pgxs.mk | 1 +
 1 file changed, 1 insertion(+)

diff --git a/src/makefiles/pgxs.mk b/src/makefiles/pgxs.mk
index c27004ecfb..5274499116 100644
--- a/src/makefiles/pgxs.mk
+++ b/src/makefiles/pgxs.mk
@@ -279,6 +279,7 @@ ifdef PGXS
 check:
 	@echo '"$(MAKE) check" is not supported.'
 	@echo 'Do "$(MAKE) install", then "$(MAKE) installcheck" instead.'
+	@false
 else
 check: submake $(REGRESS_PREP)
 	$(pg_regress_check) $(REGRESS_OPTS) $(REGRESS)
-- 
2.11.0


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


[HACKERS] ALTER EXTENSION DROP FUNCTION not working ?

2016-02-09 Thread Sandro Santilli
I'm seeing an issue with ALTER EXTENSION DROP FUNCTION
not fully unregistering the dependency of the function
on the extension. Commands to reproduce described here:
https://trac.osgeo.org/postgis/ticket/3450#comment:23

Basically I'm getting:

 ERROR:  cannot drop function pgis_twkb_accum_finalfn(internal) because other 
objects depend on it
 DETAIL:  extension postgis depends on function 
pgis_twkb_accum_finalfn(internal)

Right after successfully running:

 ALTER EXTENSION postgis DROP FUNCTION pgis_twkb_accum_finalfn(internal);

Is it a bug in PostgreSQL ? (9.3.6 running on the test machine).

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html


-- 
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] ALTER EXTENSION DROP FUNCTION not working ?

2016-02-09 Thread Sandro Santilli
On Tue, Feb 09, 2016 at 10:33:42AM +0100, Sandro Santilli wrote:

> https://trac.osgeo.org/postgis/ticket/3450#comment:23
> 
> Basically I'm getting:
> 
>  ERROR:  cannot drop function pgis_twkb_accum_finalfn(internal) because other 
> objects depend on it
>  DETAIL:  extension postgis depends on function 
> pgis_twkb_accum_finalfn(internal)

Figured:  the "pgis_twkb_accum_finalfn(internal)" function is not
a _direct_ dependency of extension "postgis", but is needed for
an aggregate which is still registered.

So this is more an annoyance than a bug, being the non-clear error
message about what's the direct dependent object that prevents
the drop.

--strk;


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


[HACKERS] [patch] extensions_path GUC

2015-10-23 Thread Sandro Santilli
One problem we have with PostGIS is you cannot test an extension
unless you have access to the system extension dir.

The following patch tries to address that by allowing to specify
a per-cluster extension path via an "extensions_path" GUC.

It is more a request-for-comments rather than a ready patch, as
I hadn't considered all use cases like upgrades of already-loaded
extensions and the possibility ot have a list of directories to
seek for extensions.

Anyway, patch is attached.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
>From 9bba783d6581bc445b8a24fc8e615969fc16ab90 Mon Sep 17 00:00:00 2001
From: Sandro Santilli <s...@keybit.net>
Date: Fri, 23 Oct 2015 12:00:51 +0200
Subject: [PATCH] Add extensions_path GUC

---
 src/backend/commands/extension.c | 37 ++---
 src/backend/utils/misc/guc.c | 14 ++
 src/include/commands/extension.h |  3 +++
 3 files changed, 43 insertions(+), 11 deletions(-)

diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 67b16a7..df4e5df 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -64,6 +64,7 @@
 /* Globally visible state variables */
 bool		creating_extension = false;
 Oid			CurrentExtensionObject = InvalidOid;
+char		*Extension_path = NULL; /* set by guc.c */
 
 /*
  * Internal data structure to hold the results of parsing a control file
@@ -348,15 +349,29 @@ is_extension_script_filename(const char *filename)
 	return (extension != NULL) && (strcmp(extension, ".sql") == 0);
 }
 
+static void
+get_extension_path(char *ret_path)
+{
+	char		sharepath[MAXPGPATH];
+	if ( Extension_path )
+	{
+		snprintf(ret_path, MAXPGPATH, "%s", Extension_path);
+	}
+	else
+	{
+		get_share_path(my_exec_path, sharepath);
+		snprintf(ret_path, MAXPGPATH, "%s/extension", sharepath);
+	}
+}
+
 static char *
 get_extension_control_directory(void)
 {
-	char		sharepath[MAXPGPATH];
 	char	   *result;
 
-	get_share_path(my_exec_path, sharepath);
 	result = (char *) palloc(MAXPGPATH);
-	snprintf(result, MAXPGPATH, "%s/extension", sharepath);
+
+	get_extension_path(result);
 
 	return result;
 }
@@ -364,13 +379,13 @@ get_extension_control_directory(void)
 static char *
 get_extension_control_filename(const char *extname)
 {
-	char		sharepath[MAXPGPATH];
-	char	   *result;
+	char		extpath[MAXPGPATH];
+	char	  *result;
 
-	get_share_path(my_exec_path, sharepath);
+	get_extension_path(extpath);
 	result = (char *) palloc(MAXPGPATH);
-	snprintf(result, MAXPGPATH, "%s/extension/%s.control",
-			 sharepath, extname);
+	snprintf(result, MAXPGPATH, "%s/%s.control",
+			 extpath, extname);
 
 	return result;
 }
@@ -378,7 +393,7 @@ get_extension_control_filename(const char *extname)
 static char *
 get_extension_script_directory(ExtensionControlFile *control)
 {
-	char		sharepath[MAXPGPATH];
+	char		extpath[MAXPGPATH];
 	char	   *result;
 
 	/*
@@ -391,9 +406,9 @@ get_extension_script_directory(ExtensionControlFile *control)
 	if (is_absolute_path(control->directory))
 		return pstrdup(control->directory);
 
-	get_share_path(my_exec_path, sharepath);
+	get_extension_path(extpath);
 	result = (char *) palloc(MAXPGPATH);
-	snprintf(result, MAXPGPATH, "%s/%s", sharepath, control->directory);
+	snprintf(result, MAXPGPATH, "%s/%s", extpath, control->directory);
 
 	return result;
 }
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index fda0fb9..f76d07b 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -33,6 +33,7 @@
 #include "access/xact.h"
 #include "catalog/namespace.h"
 #include "commands/async.h"
+#include "commands/extension.h"
 #include "commands/prepare.h"
 #include "commands/vacuum.h"
 #include "commands/variable.h"
@@ -2943,6 +2944,19 @@ static struct config_string ConfigureNamesString[] =
 	},
 
 	{
+		{"extensions_path", PGC_SUSET, CLIENT_CONN_OTHER,
+			gettext_noop("Sets the path for extensions."),
+			gettext_noop("If an extension control file needs to be opened "
+		 "the system will search this path for "
+		 "the specified file."),
+			GUC_SUPERUSER_ONLY
+		},
+		_path,
+		NULL, /* will be handled by get_extension_control_directory */
+		NULL, NULL, NULL
+	},
+
+	{
 		{"krb_server_keyfile", PGC_SIGHUP, CONN_AUTH_SECURITY,
 			gettext_noop("Sets the location of the Kerberos server key file."),
 			NULL,
diff --git a/src/include/commands/extension.h b/src/include/commands/extension.h
index 0423350..bf6f44b 100644
--- a/src/include/commands/extension.h
+++ b/src/include/commands/extension.h
@@ -27,6 +27,9 @@
 extern PGDLLIMPORT bool creating_extension;
 extern Oid	CurrentExtensionObject;
 
+/* Path to find e

Re: [HACKERS] PGXS check target forcing an install ?

2015-06-23 Thread Sandro Santilli
On Tue, Jun 23, 2015 at 02:31:03PM +0900, Michael Paquier wrote:
 On Tue, Jun 23, 2015 at 12:11 AM, Sandro Santilli s...@keybit.net wrote:
  I've noted that upgrading from PostgreSQL 9.3 to 9.5 I'm suddenly
  unable to specify a check rule in the Makefile that includes the
  PGXS one. The error is:
 
   $ make check
   rm -rf ''/tmp_install
   make -C '/home/postgresql-9.5/lib/pgxs/src/makefiles/../..' 
  DESTDIR=''/tmp_install install
   make[1]: Entering directory `/home/postgresql-9.5/lib/pgxs'
   make[1]: *** No rule to make target `install'.  Stop.
   make[1]: Leaving directory `/home/postgresql-9.5/lib/pgxs'
   make: *** [temp-install] Error 2
 
  I tracked the dangerous -rf to come from Makefile.global and it's empty
  string being due to abs_top_builddir not being define in my own Makefile.
  But beside that, which I can probably fix, it doesn't sound correct
  that a check rule insists in finding an install rule.
 
 Oops, this is a regression, and a dangerous one indeed. This is caused
 by dcae5fac.
 
 One fix is to use NO_TEMP_INSTALL=yes in Makefile.global in the
 context of PGXS, like in the patch attached, this variable needing to
 be set before Makefile.global is loaded. We could as well use directly
 PGXS in the section Testing, but that does not sound appealing for
 Makefile.global's readability.

Thanks, setting NO_TEMP_INSTALL=yes in the including Makefile fixes
this issue.

  I'm also
  surprised that there's no warning coming out from the make invocation
  given I'm defining a check rule myself (after inclusion).
 
 Why? It looks perfectly normal to me to be able to define your own
 check rule. That's more flexible this way.

I'm surprised because I used to get warnings on overrides, and I actually
still get them for other rules. For example:

Makefile:192: warning: overriding commands for target `install'
/home/postgresql-9.3.4/lib/pgxs/src/makefiles/pgxs.mk:120: warning: ignoring 
old commands for target `install'

The same warning isn't raised for the check rule, while it is clearly
defined in some upper Makefile (as shown by the forced-install-bug).

 Thoughts?

Mixed... One one hand I'm happy to implement my own rules, but in this
specific case the lack of a warning left me with no hint about where
the offending check rule was defined.

--strk;


-- 
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 context at PG_init call ?

2015-06-23 Thread Sandro Santilli
Empirically, I seem to be getting the _PG_init call for a module while
the active memory context lifetime is that of the function call which
first needed to load the shared object.

Is this the case ? Documented anywhere ?
Initializing memory meant to be alive for the whole lifetime of a backend
in that function is a bit complex if that's confirmed.

--strk;


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


[HACKERS] PGXS check target forcing an install ?

2015-06-22 Thread Sandro Santilli
I've noted that upgrading from PostgreSQL 9.3 to 9.5 I'm suddenly
unable to specify a check rule in the Makefile that includes the
PGXS one. The error is:

 $ make check
 rm -rf ''/tmp_install
 make -C '/home/postgresql-9.5/lib/pgxs/src/makefiles/../..' 
DESTDIR=''/tmp_install install
 make[1]: Entering directory `/home/postgresql-9.5/lib/pgxs'
 make[1]: *** No rule to make target `install'.  Stop.
 make[1]: Leaving directory `/home/postgresql-9.5/lib/pgxs'
 make: *** [temp-install] Error 2

I tracked the dangerous -rf to come from Makefile.global and it's empty
string being due to abs_top_builddir not being define in my own Makefile.

But beside that, which I can probably fix, it doesn't sound correct
that a check rule insists in finding an install rule. I'm also
surprised that there's no warning coming out from the make invocation
given I'm defining a check rule myself (after inclusion).

Minimal Makefile reproducing the error:

  PGXS := /home/postgresql-9.3/lib/pgxs/src/makefiles/pgxs.mk # succeeds
  PGXS := /home/postgresql-9.5/lib/pgxs/src/makefiles/pgxs.mk # fails
  include $(PGXS)
  check:
echo Checking

To verify, just run make check

--strk; 

  ()   Free GIS  Flash consultant/developer
  /\   http://strk.keybit.net/services.html


-- 
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] Unexpected speed PLAIN vs. MAIN

2015-05-05 Thread Sandro Santilli
On Mon, May 04, 2015 at 01:50:45PM -0400, Tom Lane wrote:
 Sandro Santilli s...@keybit.net writes:
  I'm comparing speed of some queries against tables having the same data
  but different storage, and got an unexpected behavior.
 
  The tables have 2 integer fields and a PcPatch field 
  (p, custom type from pgPointCloud).
 
  There are no TOASTs involved (the toast table associated with the table
  with MAIN storage is empty, the table with PLAIN storage has no toast 
  table).
 
  Running a SELECT count(p) takes 6261.699 ms on the table with MAIN storage
  and 18488.713 ms on the table with PLAIN storage.
 
  The number of buffer reads are about the same.
  Why would reading presence/absence of a value be faster from MAIN than
  from PLAIN storage ?
 
 Hm ... MAIN allows in-line compression while PLAIN doesn't.  But for
 count(), that would only make a difference if it resulted in a smaller
 physical table size, which it evidently didn't.
 
 My best guess is that the OS had many of the pages from rtlidar_dim_main
 sitting in OS disk cache, so that those buffer reads didn't all
 translate to physical I/O.  Try flushing the OS cache immediately before
 each trial to get more-reproducible results.

Bingo, it was the OS disk cache. Thanks for the tip !

That cache (Linux) acts in mysterious ways, btw.

After a new boot, with no explicit flushing, I obtained slow times in both
tables (~18 secs) with queries in this order: PLAIN,MAIN,PLAIN,MAIN.
Then 3 queries in a row against MAIN brought down its timing to 2,
but after that no number of consecutive queries against PLAIN could
do that. It took a disk flush (echo 3/proc/sys/vm/drop_caches;
sync was not enough) to get the 18 seconds back on reading MAIN and
allowing me to force caching PLAIN via consecutive calls...

I'll play a bit with pgfincore to learn more.
(http://git.postgresql.org/gitweb/?p=pgfincore.git;a=summary)

--strk; 


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


[HACKERS] Unexpected speed PLAIN vs. MAIN

2015-05-04 Thread Sandro Santilli
I'm comparing speed of some queries against tables having the same data
but different storage, and got an unexpected behavior.

The tables have 2 integer fields and a PcPatch field 
(p, custom type from pgPointCloud).

There are no TOASTs involved (the toast table associated with the table
with MAIN storage is empty, the table with PLAIN storage has no toast table).

Running a SELECT count(p) takes 6261.699 ms on the table with MAIN storage
and 18488.713 ms on the table with PLAIN storage.

The number of buffer reads are about the same.
Why would reading presence/absence of a value be faster from MAIN than
from PLAIN storage ?

The explain output:

 =# explain (analyze, verbose, buffers) select count(pa) from rtlidar_dim_main;
 Aggregate  (cost=1202627.85..1202627.86 rows=1 width=32) (actual 
time=6261.644..6261.644 rows=1 loops=1)
   Output: count(pa)
   Buffers: shared hit=32 read=1187659
   -  Seq Scan on public.rtlidar_dim_main  (cost=0.00..1199640.48 rows=1194948 
width=32) (actual time=0.060..6105.566 rows=1194948 loops=1)
 Output: id, source, pa
 Buffers: shared hit=32 read=1187659
 Total runtime: 6261.699 ms

 =# explain (analyze, verbose, buffers) select count(pa) from rtlidar_dim_plain;
 Aggregate  (cost=1202627.85..1202627.86 rows=1 width=32) (actual 
time=18473.973..18473.973 rows=1 loops=1)
   Output: count(pa)
   Buffers: shared hit=37 read=1187654
   -  Seq Scan on public.rtlidar_dim_plain  (cost=0.00..1199640.48 
rows=1194948 width=32) (actual time=0.058..18247.974 rows=1194948 loops=1)
 Output: id, source, pa
 Buffers: shared hit=37 read=1187654
 Total runtime: 18474.028 ms


The relation sizes:

 =# select pg_total_relation_size('rtlidar_dim_plain');
 9756426240

 =# select pg_total_relation_size('rtlidar_dim_main');
 9756434432

--strk;



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


[HACKERS] contraints_exclusion fails to refute simple condition

2015-03-05 Thread Sandro Santilli
PostGIS installs standard constraints of this kind:

  CHECK (geometrytype(g) = 'POINT'::text OR g IS NULL)

The constraint is used by constraint_exclusion if using this condition:

  WHERE g IS NOT NULL AND geometrytype(g) = 'LINESTRING'

But it is _NOT_ used if the NOT NULL condition is removed:

  WHERE geometrytype(g) = 'LINESTRING'

As the geometrytype is defined as STRICT and IMMUTABLE, there's
no way for geometrytype(g) = 'LINESTRING' to hold true, so why
is the IS NOT NULL condition also needed by the planner ?

Andres Freund on IRC suggested that predicate_refuted_by_simple_clause()
looks like trying to handle such cases, but if that's the case it seems
to fail here.

--strk; 


-- 
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] postgres_fdw and connection management

2014-05-27 Thread Sandro Santilli
On Tue, May 27, 2014 at 12:32:50AM -0300, Fabrízio de Royes Mello wrote:
 On Mon, May 26, 2014 at 11:47 PM, Shigeru Hanada shigeru.han...@gmail.com
 wrote:
 
  2014-05-24 0:09 GMT+09:00 Sandro Santilli s...@keybit.net:
   Indeed I tried DISCARD ALL in hope it would have helped, so I find
   good your idea of allowing extensions to register an hook there.
  
   Still, I'd like the FDW handler itself to possibly be configured
   to disable the pool completely as a server-specific configuration.
 
  Connection management seems FDW-specific feature to me.  How about to
  add FDW option, say pool_connection=true|false, to postgres_fdw which
  allows per-server configuration?

Yes, that's what I had in mind.
I'll try something along those lines.

 Makes sense... but if we use pool_connection=true and want to close the
 opened connection. How can we do that?

Right, I still consider hooks on DISCARD a useful addition.

--strk;


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


[HACKERS] postgres_fdw and connection management

2014-05-23 Thread Sandro Santilli
I'm evaluating use of the postgres FDW to keep in sync a central database
to changes made in thousand other databases, via triggers.

But as long as postgres_fdw keeps connections open for the whole lifetime
of a session this conflicts with large use of poolers which make sessions
virtually never expire.

Is there any way to ask the postgres_fdw to close connections at the
end of a transaction ? Or would it be possible at all to do from a 
FDW handler ? Do you see any drawback in doing that ?

I'm willing to work on a patch, maybe accepting an additional OPTION,
if you agree on the idea.

--strk;

 ()  ASCII ribbon campaign  --  Keep it simple !
 /\  http://strk.keybit.net/rants/ascii_mails.txt  


-- 
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] postgres_fdw and connection management

2014-05-23 Thread Sandro Santilli
On Fri, May 23, 2014 at 11:13:50AM -0300, Fabrízio de Royes Mello wrote:
 On Fri, May 23, 2014 at 8:09 AM, Sandro Santilli s...@keybit.net wrote:
 
  I'm evaluating use of the postgres FDW to keep in sync a central database
  to changes made in thousand other databases, via triggers.
 
  But as long as postgres_fdw keeps connections open for the whole lifetime
  of a session this conflicts with large use of poolers which make sessions
  virtually never expire.
 
  Is there any way to ask the postgres_fdw to close connections at the
  end of a transaction ? Or would it be possible at all to do from a
  FDW handler ? Do you see any drawback in doing that ?
 
  I'm willing to work on a patch, maybe accepting an additional OPTION,
  if you agree on the idea.
 
 
 Maybe this is a case for the idea pointed here [1]. One way to register a
 cleanup code to extensions, then we can run a command called 'DISCARD
 EXTENSIONS' and/or 'DISCARD ALL' to do this job.
 
 Unfortunately nobody comment my suggestion yet.

 [1]
 http://www.postgresql.org/message-id/CAFcNs+orcZZ3-wPfa0RFuOCtq81sg=przuhv4-m1+j5eth6...@mail.gmail.com

Indeed I tried DISCARD ALL in hope it would have helped, so I find
good your idea of allowing extensions to register an hook there.

Still, I'd like the FDW handler itself to possibly be configured
to disable the pool completely as a server-specific configuration.

--strk;


-- 
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] Uninterruptable regexp_replace in 9.3.1 ?

2014-02-26 Thread Sandro Santilli
On Fri, Feb 21, 2014 at 08:17:36PM -0500, Tom Lane wrote:
 I wrote:
  Craig Ringer cr...@2ndquadrant.com writes:
  So I'd like to confirm that this issue doesn't affect 9.1.
 
  It doesn't.  I suspect it has something to do with 173e29aa5 or one
  of the nearby commits in backend/regex/.
 
 Indeed, git bisect fingers that commit as introducing the problem.

Is there anything I can do for this to not fade to oblivion ?
Will a mail to pgsql-bugs help ?

--strk;

 ()  ASCII ribbon campaign  --  Keep it simple !
 /\  http://strk.keybit.net/rants/ascii_mails.txt  


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


[HACKERS] Uninterruptable regexp_replace in 9.3.1 ?

2014-02-21 Thread Sandro Santilli
The following snippet reveals that 9.3.1 has a bug 
in regexp_matches, which uninterruptably keeps CPU
spinning for minutes:

-8---

\timing
SET statement_timeout = 2;
-- this is only to show statement_timeout is effective here
SELECT count(*) from generate_series(1, 10);
-- this one is uninterruptable!
SELECT regexp_matches($INPUT$








/a
$b$
$c$d
;
$INPUT$,
$REG$((?:[^'$;]+|[^]*|'(?:[^']*|'')*'|(\$[^$]*\$).*?\2)+)$REG$, 'g' );

-8---

The above has been tested to be harmless with PostgreSQL 9.1.11
in that the regexp_matches call is interrupted, but it is NOT
with PostgreSQL 9.3.1.

Is it a known bug ?

Please include my address in replies as I don't get notified
of list activity. Thanks.

--strk; 

 ()  ASCII ribbon campaign  --  Keep it simple !
 /\  http://strk.keybit.net/rants/ascii_mails.txt  


-- 
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] Uninterruptable regexp_replace in 9.3.1 ?

2014-02-21 Thread Sandro Santilli
I've just tested 9.3.3 and it is _also_ affected.
Should I report the regression somewhere else ?

--strk;

On Fri, Feb 21, 2014 at 10:17:59AM +0100, Sandro Santilli wrote:
 The following snippet reveals that 9.3.1 has a bug 
 in regexp_matches, which uninterruptably keeps CPU
 spinning for minutes:
 
 -8---
 
 \timing
 SET statement_timeout = 2;
 -- this is only to show statement_timeout is effective here
 SELECT count(*) from generate_series(1, 10);
 -- this one is uninterruptable!
 SELECT regexp_matches($INPUT$
 
 
 
 
 
 
 
 
 /a
 $b$
 $c$d
 ;
 $INPUT$,
 $REG$((?:[^'$;]+|[^]*|'(?:[^']*|'')*'|(\$[^$]*\$).*?\2)+)$REG$, 'g' );
 
 -8---
 
 The above has been tested to be harmless with PostgreSQL 9.1.11
 in that the regexp_matches call is interrupted, but it is NOT
 with PostgreSQL 9.3.1.
 
 Is it a known bug ?
 
 Please include my address in replies as I don't get notified
 of list activity. Thanks.
 
 --strk; 
 
  ()  ASCII ribbon campaign  --  Keep it simple !
  /\  http://strk.keybit.net/rants/ascii_mails.txt  


-- 
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] Interrupting long external library calls

2012-06-07 Thread Sandro Santilli
On Sat, May 26, 2012 at 01:24:26PM +0200, Florian Pflug wrote:
 On May26, 2012, at 12:40 , Simon Riggs wrote:
  On 25 May 2012 17:34, Tom Lane t...@sss.pgh.pa.us wrote:
  I assume that the geos::util::Interrupt::request() call sets a flag
  somewhere that's going to be periodically checked in long-running
  loops.  Would it be possible for the periodic checks to include a
  provision for a callback into Postgres-specific glue code, wherein
  you could test the same flags CHECK_FOR_INTERRUPTS does?  A similar
  approach might then be usable in other contexts, and it seems safer
  to me than messing with a host environment's signal handling.
  
  Can we do that as a macro, e.g.
  
  POSTGRES_LIBRARY_INTERRUPT_CHECK()
  
  Otherwise the fix to this problem may be worse - faulty interrupt
  handlers are worse than none at all.
 
 As it stands, ProcessInterrupts() sometimes returns instead of
 ereport()ing even if InterruptPending is set, interrupts aren't
 held off, and the code isn't in a critical section. That happens if
 QueryCancelPending (or however that's called) gets reset after a
 SIGINT arrived but before CHECK_FOR_INTERRUPTS() is called. Or at
 least that is how I interpret the comment at the bottom of that
 function...
 
 We could thus easily provide POSTGRES_LIBRARY_INTERRUPT_CHECK() if
 we're content with the (slim, probably) chance of false positives.
 
 Or we'd need to refactor things in a way that allows the hypothetical
 POSTGRES_LIBRARY_INTERRUPT_CHECK() to re-use the tests in
 ProcessInterrupts(), but without modifying any of the flags.

So back to this, shortly after discovering (sorry for ignorance, but I
just don't care about programming in a black box environment) that windows
doesn't support posix signals.

If I understood correctly the CHECK_FOR_INTERRUPTS postgresql function
does also take care of events dispatching within windows, so that if
nothing calls that macro there's no way that a pqsignal handler would
be invoked. Is that right ?

In that case I can understand Tom's advice about providing a callback,
and then I would only need to perform the events flushing part of
from within the callback, and only for windows.

Does it sound right ?

--strk; 

-- 
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] Interrupting long external library calls

2012-06-07 Thread Sandro Santilli
On Thu, Jun 07, 2012 at 12:00:09PM +0200, Florian Pflug wrote:
 On Jun7, 2012, at 10:20 , Sandro Santilli wrote:

  In that case I can understand Tom's advice about providing a callback,
  and then I would only need to perform the events flushing part of
  from within the callback, and only for windows.
 
 Why would you need a signal handler in the library at all, then? Just
 test the same flags that CHECK_FOR_INTERRUPTS does in the callback, and
 call your interrupt request method if they indicate abort. (Or, slightly
 cleaner maybe, allow the callback to abort processing by returning false)

I'm just afraid that invoking a callback (from a library to user code)
could be significantly slower than simply lookup a variable, especially
if the interruption checking is performed very frequently. But maybe I'm
being overparanoid.

--strk;

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Interrupting long external library calls

2012-06-06 Thread Sandro Santilli
FYI, I finally committed the code installing a signal handler in PostGIS,
using the pqsignal function:

  https://trac.osgeo.org/postgis/changeset/9850

It is currently only used to interrupt GEOS calls, but the idea is that
it could eventually also be used to interrupt other library calls having
a provision for such interruption request. GEOS itself only supports it
in the 3.4 branch.

In order to test it you'll need to define POSTGIS_ENABLE_GEOS_INTERRUPTIBILITY
at the top of postgis/postgis_module.c - the macro is off by default due
to concerns about possible consequences we may be unaware of.

Your comments will be very useful to reduce (or confirm) the concerns.
If we can get this confidently straight there may be the possibility to 
toggle the default to on before 2.0.1 ...

Thanks in advance.

PS: Tom, I still don't know what you mean by the SIGINFO case.

--strk;

On Mon, May 28, 2012 at 08:48:21AM +0200, Sandro Santilli wrote:
 On Fri, May 25, 2012 at 12:34:54PM -0400, Tom Lane wrote:
  Sandro Santilli s...@keybit.net writes:
   I ended up providing an explicit mechanism to request interruption of
   whatever the library is doing, and experimented (successfully so far)
   requesting the interruption from a SIGINT handler.
  
   Do you see any major drawback in doing so ?
  
  This seems a bit fragile.  It might work all right in Postgres, where
  we tend to set up signal handlers just once at process start, but ISTM
  other systems might assume they can change their signal handlers at
  any time.  The handler itself looks less than portable anyway ---
  what about the SIGINFO case?
 
 Indeed setting the handler from within the library itself was a temporary
 implementation to see how effective it would have been. The idea is to
 move the registration of the hanlder outside the library and into the
 user (PostGIS in this case). The library itself would only expose 
 GEOS_requestInterrupt/GEOS_cancelInterrupt API calls.
 
 I'm guessing PostGIS should use the more portable pqsignal functions ?
 
 What should I know about SIGINFO ?
 
  I assume that the geos::util::Interrupt::request() call sets a flag
  somewhere that's going to be periodically checked in long-running
  loops. 
 
 Yes, this is what will happen.
 
  Would it be possible for the periodic checks to include a
  provision for a callback into Postgres-specific glue code, wherein
  you could test the same flags CHECK_FOR_INTERRUPTS does?  A similar
  approach might then be usable in other contexts, and it seems safer
  to me than messing with a host environment's signal handling.
 
 Would it be enough for the signal handler (installed by PostGIS) 
 to check those flags and call the GEOS_requestInterrupt function
 when appropriate ?
 
 --strk; 

-- 
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] Interrupting long external library calls

2012-05-28 Thread Sandro Santilli
On Fri, May 25, 2012 at 12:34:54PM -0400, Tom Lane wrote:
 Sandro Santilli s...@keybit.net writes:
  I ended up providing an explicit mechanism to request interruption of
  whatever the library is doing, and experimented (successfully so far)
  requesting the interruption from a SIGINT handler.
 
  Do you see any major drawback in doing so ?
 
 This seems a bit fragile.  It might work all right in Postgres, where
 we tend to set up signal handlers just once at process start, but ISTM
 other systems might assume they can change their signal handlers at
 any time.  The handler itself looks less than portable anyway ---
 what about the SIGINFO case?

Indeed setting the handler from within the library itself was a temporary
implementation to see how effective it would have been. The idea is to
move the registration of the hanlder outside the library and into the
user (PostGIS in this case). The library itself would only expose 
GEOS_requestInterrupt/GEOS_cancelInterrupt API calls.

I'm guessing PostGIS should use the more portable pqsignal functions ?

What should I know about SIGINFO ?

 I assume that the geos::util::Interrupt::request() call sets a flag
 somewhere that's going to be periodically checked in long-running
 loops. 

Yes, this is what will happen.

 Would it be possible for the periodic checks to include a
 provision for a callback into Postgres-specific glue code, wherein
 you could test the same flags CHECK_FOR_INTERRUPTS does?  A similar
 approach might then be usable in other contexts, and it seems safer
 to me than messing with a host environment's signal handling.

Would it be enough for the signal handler (installed by PostGIS) 
to check those flags and call the GEOS_requestInterrupt function
when appropriate ?

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Interrupting long external library calls

2012-05-25 Thread Sandro Santilli
On Thu, May 24, 2012 at 04:37:04PM +0200, Florian Pflug wrote:
 On May24, 2012, at 15:04 , Sandro Santilli wrote:
  On Wed, May 16, 2012 at 07:30:03PM +0300, Heikki Linnakangas wrote:
  On 16.05.2012 15:42, Sandro Santilli wrote:
  But CHECK_FOR_INTERRUPTS doesn't return, right ?
  Is there another macro for just checking w/out yet acting upon it ?
  
  Hmm, no. CHECK_FOR_INTERRUPTS() checks the InterruptPending
  variable, but on Windows it also checks for
  UNBLOCKED_SIGNAL_QUEUE(). And even if InterruptPending is set, it's
  not totally certain that CHECK_FOR_INTERRUPTS() won't return. I
  think InterruptPending can be set spuriously (even if that's not
  possible today, I wouldn't rely on it), and if you're in a
  HOLD/RESUME_INTERRUPTS block, CHECK_FOR_INTERRUPTS() will do nothing
  even if InterruptPending is true.
  
  The only sane way to make 3rd party code interruptible is to add
  CHECK_FOR_INTERRUPTS() to it, in safe places.
  
  No place is safe if CHECK_FOR_INTERRUPTS doesn't return.
  How could caller code cleanup on interruption ?
 
 The postgres way is to use PG_TRY/PG_CATCH to make sure stuff gets cleaned
 up if an error or an interrupts occurs. You could use those to make the
 third-party library exception safe, but it'll probably be a quite
 invasive change :-(.
 
 Alternatively, you could replicate the check CHECK_FOR_INTERRUPTS() does,

I ended up providing an explicit mechanism to request interruption of
whatever the library is doing, and experimented (successfully so far)
requesting the interruption from a SIGINT handler.

Do you see any major drawback in doing so ?

So far I installed the SIGINT handler within the library itself, but
I guess it could be moved out instead to have ore fine-grained control
over when to request interruption.

Here's the code installing the signal handler within the library:
https://github.com/strk/libgeos/commit/e820ecd0469b777953c132661877c2967b10cee2

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Interrupting long external library calls

2012-05-24 Thread Sandro Santilli
On Wed, May 16, 2012 at 07:30:03PM +0300, Heikki Linnakangas wrote:
 On 16.05.2012 15:42, Sandro Santilli wrote:
 But CHECK_FOR_INTERRUPTS doesn't return, right ?
 Is there another macro for just checking w/out yet acting upon it ?
 
 Hmm, no. CHECK_FOR_INTERRUPTS() checks the InterruptPending
 variable, but on Windows it also checks for
 UNBLOCKED_SIGNAL_QUEUE(). And even if InterruptPending is set, it's
 not totally certain that CHECK_FOR_INTERRUPTS() won't return. I
 think InterruptPending can be set spuriously (even if that's not
 possible today, I wouldn't rely on it), and if you're in a
 HOLD/RESUME_INTERRUPTS block, CHECK_FOR_INTERRUPTS() will do nothing
 even if InterruptPending is true.
 
 The only sane way to make 3rd party code interruptible is to add
 CHECK_FOR_INTERRUPTS() to it, in safe places.

No place is safe if CHECK_FOR_INTERRUPTS doesn't return.
How could caller code cleanup on interruption ?

--strk; 

-- 
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] Interrupting long external library calls

2012-05-24 Thread Sandro Santilli
On Thu, May 24, 2012 at 04:55:34PM +0300, Heikki Linnakangas wrote:
 On 24.05.2012 16:04, Sandro Santilli wrote:
 On Wed, May 16, 2012 at 07:30:03PM +0300, Heikki Linnakangas wrote:
 On 16.05.2012 15:42, Sandro Santilli wrote:
 But CHECK_FOR_INTERRUPTS doesn't return, right ?
 Is there another macro for just checking w/out yet acting upon it ?
 
 Hmm, no. CHECK_FOR_INTERRUPTS() checks the InterruptPending
 variable, but on Windows it also checks for
 UNBLOCKED_SIGNAL_QUEUE(). And even if InterruptPending is set, it's
 not totally certain that CHECK_FOR_INTERRUPTS() won't return. I
 think InterruptPending can be set spuriously (even if that's not
 possible today, I wouldn't rely on it), and if you're in a
 HOLD/RESUME_INTERRUPTS block, CHECK_FOR_INTERRUPTS() will do nothing
 even if InterruptPending is true.
 
 The only sane way to make 3rd party code interruptible is to add
 CHECK_FOR_INTERRUPTS() to it, in safe places.
 
 No place is safe if CHECK_FOR_INTERRUPTS doesn't return.
 How could caller code cleanup on interruption ?
 
 It would only be safe to call it in places where no cleanup is
 necessary. I don't know if there are any such places in the geos
 library.

Sure, right before starting and after finishing.
That is nowhere useful for a premature interruption...

The whole point of the work I'm doing these days is letting the
users interrupt GEOS calls which often take a long time and a lot
of memory resources.

The current plan is to provide custom allocators to have automatic
garbage collection on interruption. Turned out not to be an easy task
to bend GEOS into using palloc/pfree, but there's progress in that
direction.

--strk;

-- 
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] Interrupting long external library calls

2012-05-16 Thread Sandro Santilli
On Wed, May 16, 2012 at 02:46:17PM +0300, Heikki Linnakangas wrote:
 On 16.05.2012 14:30, Mark Cave-Ayland wrote:
 On 16/05/12 11:39, Heikki Linnakangas wrote:
 
 However, if you're absolutely positively sure that the library function
 can tolerate that, you can set ImmediateInterruptOK = true before
 calling it. See e.g PGSemaphoreLock() on how that's done before starting
 to sleep on a semapgore.
 
 Hi Heikki,
 
 Yes that appears to work fine on the surface - just testing now to
 determine what state everything is left in when queries are aborted
 prematurely.
 
 You're unlikely to catch all problems just by testing. I wouldn't
 trust that it's safe unless the library authors specifically
 mentions that it is safe to longjump out of the function at any
 time. Note for example that if the library function calls back to
 palloc/pfree, then it's not safe, because interrupting those
 functions is not safe.

I'm right now getting the external library into using memory allocator
wrappers so to provide an syntetized OOM condition in an aim to have a 
more predictable answer to that.

But CHECK_FOR_INTERRUPTS doesn't return, right ?
Is there another macro for just checking w/out yet acting upon it ?

--strk;

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Gsoc2012 idea, tablesample

2012-05-11 Thread Sandro Santilli
On Thu, May 10, 2012 at 02:30:35PM -0400, Robert Haas wrote:
 On Thu, May 10, 2012 at 2:07 PM, Kevin Grittner
 kevin.gritt...@wicourts.gov wrote:
  Ants Aasma a...@cybertec.at wrote:
  It seems to me that the simplest thing to do would be to lift the
  sampling done in analyze.c (acquire_sample_rows) and use that to
  implement the SYSTEM sampling method.
 
  Definitely.  I thought we had all agreed on that ages ago.
 
 Right, and I don't think we should be considering any of this other
 stuff until that basic thing is implemented and working.

Agreed. That's what I'd love to see as well, for the GIS part.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Gsoc2012 idea, tablesample

2012-04-24 Thread Sandro Santilli
On Mon, Apr 23, 2012 at 08:34:44PM +0300, Ants Aasma wrote:
 On Mon, Apr 23, 2012 at 4:37 PM, Sandro Santilli s...@keybit.net wrote:
  I'd love to see enhanced CTID operators, to fetch all visible tuples in a 
  page
  using a tidscan.  Something like: WHERE ctid =~ '(501,*)' or a ctidrange.
 
 Among other things, this would enable user-space implementation of
 tablesample. Given the operator =~(tid, int) that matches the page
 number and planner/executor integration so that it results in a TID
 scan, you would need the following functions:
 
 random_pages(tbl regclass, samples int) returns int[]
 aggregate function:
 reservoir_sample(item anyelement, samples int) returns anyarray
 
 Implementations for both of the functions could be adapted from analyze.c.
 
 Then tablesample could be implemented with the following query:
 SELECT (SELECT reservoir_sample(some_table, 50) AS samples
FROM some_table WHERE ctid =~ ANY (rnd_pgtids))
 FROM random_pages('some_table', 50) AS rnd_pgtids;
 
 Actually, now that I think about it, it could actually be implemented
 without any modifications to core at some cost to efficiency.
 random_pages would have to return tid[] that contains for each
 generated pagenumber all possible tids on that page.

This is exactly what I'm after.
I've actually started crafting such a TableSample function and I'm in the
process to refine the signature so your suggested interface above is 
very useful, thanks !

But I don't understand the reservoir_sample call, what is it supposed to do ?
And how flexibly anyarray return would be ? Could you return arbitrary 
typed rowtypes from it ?

 By making the building blocks available users get more flexibility.
 The downside would be that we can't automatically make better sampling
 methods available.

One approach doesn't preclude the other. TABLESAMPLE will still be useful,
also for SQL compliance.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Gsoc2012 idea, tablesample

2012-04-24 Thread Sandro Santilli
On Tue, Apr 24, 2012 at 08:49:26AM +0200, Sandro Santilli wrote:
 On Mon, Apr 23, 2012 at 08:34:44PM +0300, Ants Aasma wrote:

  SELECT (SELECT reservoir_sample(some_table, 50) AS samples
 FROM some_table WHERE ctid =~ ANY (rnd_pgtids))
  FROM random_pages('some_table', 50) AS rnd_pgtids;
 
 But I don't understand the reservoir_sample call, what is it supposed to do ?

Ok got it, that was probably to avoid:

 ERROR:  more than one row returned by a subquery used as an expression

But this also works nicely:

 SELECT * FROM lots_of_points
 WHERE ctid = ANY ( ARRAY[(SELECT random_tids('lots_of_points', 10))] );

and still uses tidscan.

The advanced TID operator would be for random_tids to only return pages rather
than full tids...

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Gsoc2012 idea, tablesample

2012-04-23 Thread Sandro Santilli
On Sat, Apr 21, 2012 at 02:28:52PM +0800, Qi Huang wrote:
 
 Hi, Heikki
...
  Another idea that Robert Haas suggested was to add support doing a TID 
  scan for a query like WHERE ctid '(501,1)'. That's not enough work 
  for GSoC project on its own, but could certainly be a part of it.
 
 the first one and the last one are still not clear. 

The last one was the TID scan on filters like ctid  '(501,1)'.
TID scans are the fastest access method as they directly access
explicitly referenced addresses. Starting from this observation a sampling
function may select random pages and tuples within pages and directly
access them, optimizing accesses by grouping tuples within the same
page so to fetch them all togheter.

This is what the ANALYZE command already does when providing samples
for the type analyzers.

Unfortunately it looks like at SQL level only the equality operator triggers
a TID scan, so things like WHERE ctid  '(501,1)' won't be as fast as
fetching all visible tuples in the first 501 pages.

I think that's what Heikki was referring about.

I'd love to see enhanced CTID operators, to fetch all visible tuples in a page
using a tidscan.  Something like: WHERE ctid =~ '(501,*)' or a ctidrange.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Gsoc2012 idea, tablesample

2012-04-19 Thread Sandro Santilli
On Tue, Apr 17, 2012 at 04:29:52PM -0400, Stephen Frost wrote:
 Josh,
 
 * Josh Berkus (j...@agliodbs.com) wrote:
  FWIW, the PostGIS folks would *really* love to have a TABLESAMPLE which
  worked with geographic indexes.  This would be tremendously useful for
  constructing low-resolution zoom out tiles on maps and similar.
 
 I'm familiar with the concept of 'zoom out' tiles and PostGIS, but I
 don't actually see the connection between that and TABLESAMPLE.  Perhaps
 I'm missing something, but I've never seen a case where you create 'zoom
 out' tiles by just grabbing some portion of the data at random, as that
 would end up creating empty spots or missing pieces.

Actually a random sample would really be representative of the data
distribution. What the type analyzer gets is a sample and that sample
is what the estimator looks at to answer the question:

 How many rows fall in this rectangle ?

You can see how well it works by passing your queries using  operator
to EXPLAIN ANALYZE and compare estimated/real.

I'm looking for a way to fetch random samples these days so I confirm
the need for a quick way to fetch the same sample that analyze
command fetches but at SQL level.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Gsoc2012 idea, tablesample

2012-04-19 Thread Sandro Santilli
On Mon, 16 Apr 2012 23:17:25 -0700, Heikki Linnakangas wrote:

 1. We probably don't want the SQL syntax to be added to the
grammar. This should be written as an extension, using custom
functions as the API, instead of extra SQL syntax.

I can't find the discussion about this, have any pointer ?

I've found a patch of 2007 by Gavin Sherry implementing the SQL 2003
TABLESAMPLE syntax. May be a good starting point ?
http://www.neilconway.org/talks/hacking/

--strk;

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Gsoc2012 idea, tablesample

2012-04-19 Thread Sandro Santilli
On Thu, Apr 19, 2012 at 08:47:51AM -0400, Stephen Frost wrote:
 * Sandro Santilli (s...@keybit.net) wrote:
  Actually a random sample would really be representative of the data
  distribution. What the type analyzer gets is a sample and that sample
  is what the estimator looks at to answer the question:
 
 That might work if all you have is point data, but lines, polygons, etc,
 you're typically going to want to see, just not at the same resolution..
 At least, when you're talking about 'zoom-out' tiles, which is what this
 was about up thread.
 
  I'm looking for a way to fetch random samples these days so I confirm
  the need for a quick way to fetch the same sample that analyze
  command fetches but at SQL level.
 
 I'm all for supporting that and implementing this feature, I just don't
 think it's going to be all that useful for zoom-out tiles when complex
 geometries are involved.

Me neither. But for points it sounds very useful.
And we know it is useful for lines and polygons as well when it comes
to estimate overlaps... (since the estimator does a good job even for
lines and polygons)

I really hope Neil Conway work of 2007 could make it into PostgreSQL.

Look, the same work was a topic of an homework assignment at Berkley in
2005: http://inst.eecs.berkeley.edu/~cs186/fa05/hw/hw2/hw2.html

And the whole thing is in the SQL standard 2003 

--strk;

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-27 Thread Sandro Santilli
On Fri, Feb 24, 2012 at 08:21:05PM +0200, Peter Eisentraut wrote:
 On fre, 2012-02-24 at 17:26 +0100, Sandro Santilli wrote:
  We don't initdb with PostGIS regression testing framework
  but I've considered doing it for this specific case and it stroke me
  that even then we couldn't control SHAREDIR.
 
 I would always create a new instance using initdb for test runs.  It's
 easy and avoids all these problems.

Doesn't avoid the SHAREDIR problem, that's what I'm saying.
SHAREDIR is a compile-time setting in PostgreSQL.

--strk;

-- 
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] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-27 Thread Sandro Santilli
On Sun, Feb 26, 2012 at 09:50:04PM -0500, Robert Haas wrote:
 On Sun, Feb 26, 2012 at 10:36 AM, Peter Eisentraut pete...@gmx.net wrote:
  On lör, 2012-02-25 at 14:21 +0100, Christoph Berg wrote:
  Well, I'm trying to invoke the extension's make check target at
  extension build time. I do have a temporary installation I own
  somehwere in my $HOME, but that is still trying to find extensions in
  /usr/share/postgresql/9.1/extension/*.control, because I am using the
  system's postgresql version. The build process is not running as root,
  so I cannot do an install of the extension to its final location.
  Still it would be nice to run regression tests. All that seems to be
  missing is the ability to put
 
  extension_control_path = /home/buildd/tmp/extension
 
  into the postgresql.conf of the temporary PG installation, or some
  other way like CREATE EXTENSION foobar WITH CONTROL
  '/home/buildd/...'.
 
  Yeah, of course, the extension path is not related to the data
  directory.  So we do need some kind of path setting, just like
  dynamic_library_path.
 
 That logic seems sound to me, so +1.

+1 here as well. I may attempt to produce a patch if this gets consensus.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-24 Thread Sandro Santilli
On Fri, Feb 24, 2012 at 09:59:12AM +0100, Christoph Berg wrote:
 Re: Sandro Santilli 2012-02-22 20120222101656.GB6125@gnash
  I'm not really looking for inline extensions.
  I do want to install the extension objects somewhere, just NOT
  in the PostgreSQL builtin SHAREDIR but in an arbitrary staging
  directory to use for QA the extension before distribution.
 
 We have the same problem with testing extensions at build-time in the
 Debian packages. The server's SHAREDIR /usr/share/postgresql/... is
 only writable by root, while the build is running as buildd user, so
 there is no way to do create extension whatimbuildingrightnow to be
 able to run regression tests, even if this is a cluster I have just
 created with initdb.

Exactly. We don't initdb with PostGIS regression testing framework
but I've considered doing it for this specific case and it stroke me
that even then we couldn't control SHAREDIR.

--strk;

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-24 Thread Sandro Santilli
On Thu, Feb 23, 2012 at 06:53:05PM -0800, Daniel Farina wrote:
 On Tue, Feb 21, 2012 at 1:34 PM, Dimitri Fontaine
 dimi...@2ndquadrant.fr wrote:
  Sandro Santilli s...@keybit.net writes:
  Please see the inline extension thread where answers to your problem
  have been discussed.
 
 I'm pretty sure Sandro is hacking PostGIS, so inline extensions are of
 no help here.
 
 Can you tell us why alternative approaches that don't require adding a
 knob (such as copying/symlinking of compiled artifacts) is such a huge
 pain for you?

It's not specifically a pain for me, but it breaks the
tests can be run in a staged environment idiom.

See Christoph Berg mail, that's exactly the same issue.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
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] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-22 Thread Sandro Santilli
On Tue, Feb 21, 2012 at 10:34:42PM +0100, Dimitri Fontaine wrote:
 Sandro Santilli s...@keybit.net writes:
  On Tue, Feb 21, 2012 at 10:21:17AM -0500, Tom Lane wrote:
  Sandro Santilli s...@keybit.net writes:
   I'm trying to understand what options I have to test CREATE EXTENSION
   w/out installing the extension files in their final destination.
  
  There aren't any.  Generally speaking, if you want to be testing an
  extension, you should be doing it in a test installation anyway;
  so I don't see what's the objection to sticking the files into the
  installation's real SHAREDIR.
 
  We do a staged installation of the extension library and scripts
  in an ad-hoc dir which does not match the built-in SHAREDIR of PostgreSQL.
 
  It works fine for testing the extension directly sourcing the scripts.
  It's only CREATE EXTENSION that doesn't play nicely with that setup.
 
 Please see the inline extension thread where answers to your problem
 have been discussed.
 
   https://commitfest.postgresql.org/action/patch_view?id=746

I'm not really looking for inline extensions.
I do want to install the extension objects somewhere, just NOT
in the PostgreSQL builtin SHAREDIR but in an arbitrary staging
directory to use for QA the extension before distribution.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net
  `-o--'


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


[HACKERS] [PATCH] typo fix

2012-02-22 Thread Sandro Santilli
Typo in a comment...

--strk; 
From cfca9507df8612a48cad341653f8e9193c6b7e08 Mon Sep 17 00:00:00 2001
From: Sandro Santilli s...@keybit.net
Date: Wed, 22 Feb 2012 11:32:48 +0100
Subject: [PATCH] typo fix

---
 src/backend/commands/extension.c |2 +-
 1 files changed, 1 insertions(+), 1 deletions(-)

diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 6ecbbc7..a9963ac 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -876,7 +876,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 
 		/*
 		 * If it's not relocatable, substitute the target schema name for
-		 * occcurrences of @extschema@.
+		 * occurrences of @extschema@.
 		 *
 		 * For a relocatable extension, we needn't do this.  There cannot be
 		 * any need for @extschema@, else it wouldn't be relocatable.
-- 
1.7.0.4


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


[HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-21 Thread Sandro Santilli
I'm trying to understand what options I have to test CREATE EXTENSION
w/out installing the extension files in their final destination.

Could not find a way to set SHAREDIR from within psql, nor a way
to specify it in initdb call. Am I missing it something ?

PS: please include my address in replies as I have mail delivery
disabled from the list. Thank you.

--strk;

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net
  `-o--'


-- 
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] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-21 Thread Sandro Santilli
On Tue, Feb 21, 2012 at 10:21:17AM -0500, Tom Lane wrote:
 Sandro Santilli s...@keybit.net writes:
  I'm trying to understand what options I have to test CREATE EXTENSION
  w/out installing the extension files in their final destination.
 
 There aren't any.  Generally speaking, if you want to be testing an
 extension, you should be doing it in a test installation anyway;
 so I don't see what's the objection to sticking the files into the
 installation's real SHAREDIR.

We do a staged installation of the extension library and scripts
in an ad-hoc dir which does not match the built-in SHAREDIR of PostgreSQL.

It works fine for testing the extension directly sourcing the scripts.
It's only CREATE EXTENSION that doesn't play nicely with that setup.

--strk; 

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


[HACKERS] CASE w/out ELSE hides typmod (was: How define a view that use a case operator for geometry field)

2011-10-28 Thread Sandro Santilli
On Fri, Oct 28, 2011 at 10:33:45PM +0200, aperi2007 wrote:

 Seem that postgis try to define it an else condition assigning it a
 unknown geometry ?

It's PostgreSQL, not PostGIS.

The same happens with any type, can be reproduced with something like this:

=# CREATE VIEW test1 AS SELECT
  CASE WHEN random()  0.5 THEN 1::numeric(10, 0)
  END;

=# \d test1
   View test.test1
 Column |  Type   | Modifiers
+-+---
 case   | numeric |

=# CREATE VIEW test2 AS SELECT
  CASE WHEN random()  0.5 THEN 1::numeric(10, 0)
  ELSE 0::numeric(10, 0)
  END;

=# \d test2
 View test.test2
 Column | Type  | Modifiers
+---+---
 case   | numeric(10,0) |


Maybe someone on pgsql-hackers can tell us more about this.

--strk;

  ()   Free GIS  Flash consultant/developer
  /\   http://strk.keybit.net/services.html

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