Re: [GENERAL] Creating and managing triggers

2012-10-09 Thread Dmitriy Igrishin
Hey, 2012/10/9 Tom Lane > Dean Myerson writes: > > I need to create some triggers and the docs seem pretty straightforward. > > When I tried to create one using CREATE TRIGGER, it took over 20 > > minutes, and the second one hadn't finished over more than an hour. And > > I later found that all

Re: [GENERAL] CTE materializing sets?

2012-10-09 Thread Craig Ringer
On 10/06/2012 08:45 AM, Liam Caffrey wrote: Hi, If I run a CTE does that materialize the resulting data in the same (or a similar) way as if I created a temp table and referred to that instead? Or does the CTE keep the set in memory? Really good question, I too would be interested in this. I'

Re: [GENERAL] CTE materializing sets?

2012-10-09 Thread Serge Fonville
This indeed is a very interesting question. At http://wiki.postgresql.org/wiki/CTEReadme it seems to suggest that a CTE is just rewritten and the resulting query is executed. Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl Convince Microsoft! They need to add TR

Re: [GENERAL] CTE materializing sets?

2012-10-09 Thread Tomas Vondra
Dne 09.10.2012 11:48, Craig Ringer napsal: On 10/06/2012 08:45 AM, Liam Caffrey wrote: Hi, If I run a CTE does that materialize the resulting data in the same (or a similar) way as if I created a temp table and referred to that instead? Or does the CTE keep the set in memory? Really good qu

Re: [GENERAL] CTE materializing sets?

2012-10-09 Thread Craig Ringer
On 10/09/2012 05:53 PM, Serge Fonville wrote: This indeed is a very interesting question. At http://wiki.postgresql.org/wiki/CTEReadme it seems to suggest that a CTE is just rewritten and the resulting query is executed. CTEs are an optimisation fence, so there's something more than a simple

Re: [GENERAL] CTE materializing sets?

2012-10-09 Thread Виктор Егоров
2012/10/9 Serge Fonville : > This indeed is a very interesting question. > > At http://wiki.postgresql.org/wiki/CTEReadme it seems to suggest that a CTE > is just rewritten and the resulting query is executed. As was mentioned a couple of times in this list, CTE do have optimization fence feature

[GENERAL] something better than pgtrgm?

2012-10-09 Thread Willy-Bas Loos
Hi, I need a *language unaware* text comparison algorithm, so i found pgtrgm. But i am not so content with it, because the similarities it finds are: - biased to favor text that is the same in the first character - much dependent on similar length of the strings Are there any other options

Re: [GENERAL] something better than pgtrgm?

2012-10-09 Thread Andrew Sullivan
On Tue, Oct 09, 2012 at 02:10:26PM +0200, Willy-Bas Loos wrote: > Hi, > > I need a *language unaware* text comparison algorithm [. . .] > (i want to use it for *"did you mean ...?"* for approx 6-10 character codes > or 8-20 letter words of mixed languages) I don't think this is going to do what

Re: [GENERAL] Postgres will not start due to corrupt index

2012-10-09 Thread davegeeit
In case corruption of SQL server, you can try any third party application to repair sql database. when you search on the internet you will get lots of option but i would suggest you "RecoveryFix for SQL Database" software because this company offer the free trial version of software to get the resu

Re: [GENERAL] Dump/restore indexes and functions in public schema

2012-10-09 Thread marian krucina
Example: in PG91: CREATE FUNCTION function_y(x INT) RETURNS INT AS $$ SELECT $1*$1 $$ LANGUAGE SQL; CREATE FUNCTION function_x(x INT) RETURNS INT AS $$ SELECT function_y($1) $$ LANGUAGE SQL; CREATE SCHEMA schema_a; CREATE TABLE schema_a.table_a(i INT); CREATE INDEX ON schema_a.table_a(function_x(i

Re: [GENERAL] something better than pgtrgm?

2012-10-09 Thread Willy-Bas Loos
Hi, Andrew thanks for replying On Tue, Oct 9, 2012 at 2:18 PM, Andrew Sullivan wrote: > But for the mixed languages case, surely it's not _any_ mixed > language? Are you mixing Arabic, Farsi, Chinese, and Hindi, for > instance? > We're mixing species names of birds in greek and latin (scientifi

Re: [GENERAL] something better than pgtrgm?

2012-10-09 Thread Andrew Sullivan
On Tue, Oct 09, 2012 at 03:10:31PM +0200, Willy-Bas Loos wrote: > > > We're mixing species names of birds in greek and latin (scientific names), > and all languages spoken in africa, europe and western asia. Yike. > I'm not very knowledgeable about scripts around the world, but i am afraid > that

Re: [GENERAL] pgxs problem...

2012-10-09 Thread Tom Lane
John R Pierce writes: > I'm trying to build something (pljava, dont laugh) for IBM AIX (don't > laugh). I have my own build of postgresql 9.1.6 on AIX, compiled with > IBM XLC, it works quite nicely, but this build is bombing.. > /opt/pgsql91/lib/pgxs/src/makefiles/../../src/backend/port/aix/m

Re: [GENERAL] something better than pgtrgm?

2012-10-09 Thread Willy-Bas Loos
On Tue, Oct 9, 2012 at 3:23 PM, Andrew Sullivan wrote: > you will need to be extremely rigorous about > normalizing spellings on the way in. Is that a possibility? Yes, it is. > If so, I > can almost imagine a way this could work > Great! How? -- "Quality comes from focus and clarity of

Re: [GENERAL] Dump/restore indexes and functions in public schema

2012-10-09 Thread Tom Lane
marian krucina writes: > Example: > in PG91: > CREATE FUNCTION function_y(x INT) RETURNS INT AS $$ SELECT $1*$1 $$ > LANGUAGE SQL; > CREATE FUNCTION function_x(x INT) RETURNS INT AS $$ SELECT > function_y($1) $$ LANGUAGE SQL; > CREATE SCHEMA schema_a; > CREATE TABLE schema_a.table_a(i INT); > CREA

Re: [GENERAL] something better than pgtrgm?

2012-10-09 Thread Andrew Sullivan
On Tue, Oct 09, 2012 at 03:54:35PM +0200, Willy-Bas Loos wrote: > > > If so, I > > can almost imagine a way this could work > > > > Great! How? Well, it involves very large tables. But basically, you work out a "variant" table for any language you like, and then query across it with subsets of

[GENERAL] PostgreSQL and WMS/WFS Service

2012-10-09 Thread José Pedro Santos
Dear all, How can I set up a service for a layer stored within postgres like WMS or WFS? I have one WEBGIS Framework that only allow data with that input. Many thanks. Best Regards, José Santos

[GENERAL] plpgsql: trigger insert new into other table (archive)

2012-10-09 Thread Matthijs Möhlmann
Hello all, First the explanation: I have to databases, some_production and some_archive, those two databases have an identical layout. Now we need to implement that all insert and update queries should be replicated to the some_archive database. Well, the question is how to do that? I thought ab

Re: [GENERAL] plpgsql: trigger insert new into other table (archive)

2012-10-09 Thread Shaun Thomas
On 10/09/2012 09:55 AM, Matthijs Möhlmann wrote: Now we need to implement that all insert and update queries should be replicated to the some_archive database. Well, the question is how to do that? Whatever you do, please don't roll your own. This is a solved problem. If you plan on doing this

Re: [GENERAL] pgxs problem...

2012-10-09 Thread John R Pierce
On 10/09/12 6:52 AM, Tom Lane wrote: John R Pierce writes: >I'm trying to build something (pljava, dont laugh) for IBM AIX (don't >laugh). I have my own build of postgresql 9.1.6 on AIX, compiled with >IBM XLC, it works quite nicely, but this build is bombing.. >/opt/pgsql91/lib/pgxs/src/makef

Re: [GENERAL] pgxs problem...

2012-10-09 Thread John R Pierce
On 10/09/12 11:49 AM, John R Pierce wrote: and, muh-oh... $PG_PREFIX/bin/postgres is the binary executable, so I *can't* put a copy of postgres.imp there, hah. and a bit of poking around the pljava makefiles, I'm not figuring out how its cooking that path name up?!? ok, its coming from

Re: [GENERAL] pgxs problem...

2012-10-09 Thread Tom Lane
John R Pierce writes: > mm, k. so I manually copied that file over, and now get... > > /usr/vacpp/bin/xlc -q64 -I/home/postgres/src/include -O2 -qarch=pwr5 > -qtune=balanced -qnoansialias -o pljava.so libpljava.a > -Wl,-bE:libpljava.exp -L/opt/pgsql91/lib -L/home/postgres/s

Re: [GENERAL] pgxs problem...

2012-10-09 Thread John R Pierce
On 10/09/12 11:49 AM, John R Pierce wrote: $ find /opt/pgsql91 -name postgres.imp /opt/pgsql91/lib/postgres.imp ah, and so I find the $PG_PREFIX/lib/pgxs/src/Makefile.port has that bad reference to $(bindir)/postgres/postgres.imp and hack it like... POSTGRES_IMP= postgres.imp ifdef

Re: [GENERAL] pgxs problem...

2012-10-09 Thread John R Pierce
On 10/09/12 12:11 PM, Tom Lane wrote: Looks like it's coming from src/makefiles/Makefile.aix: ifdef PGXS BE_DLLLIBS= -Wl,-bI:$(bindir)/postgres/$(POSTGRES_IMP) else BE_DLLLIBS= -Wl,-bI:$(top_builddir)/src/backend/$(POSTGRES_IMP) endif I think the first case is just wrong (evidently never been t

Re: [GENERAL] pgxs problem...

2012-10-09 Thread Tom Lane
John R Pierce writes: > btw, $pkglibdir didn't fare any better than $libdir, I'm stuck at... > ld: 0711-317 ERROR: Undefined symbol: .floor Well, you're making progress anyway. That one probably needs -lm added to the command. Hard to tell if the fact that it's not there already is our fault o

Re: [GENERAL] pgxs problem...

2012-10-09 Thread John R Pierce
On 10/09/12 1:45 PM, Tom Lane wrote: Well, you're making progress anyway. That one probably needs -lm added to the command. Hard to tell if the fact that it's not there already is our fault or pljava's. On my machine, if I go into say contrib/cube and do "make", I see -lm in the link command -

Re: [GENERAL] pgxs problem...

2012-10-09 Thread Tom Lane
John R Pierce writes: > Further, it appears the link command pljava is using for the AIX case is > given in its makefile as... > $(COMPILER) $(LDFLAGS_NO_L) $(LDFLAGS_SL) -o $(plugin) $< > -Wl,-bE:$(NAME)$(EXPSUFF) $(SHLIB_LINK) > I can't find anywhere LDFLAGS_NO_L is defined. howev

Re: [GENERAL] PostgreSQL and WMS/WFS Service

2012-10-09 Thread Greg Williamson
You might look at the GIS extension, PostGIS: Not sure how much yu need to do, but a company I used to work for ran a WMS service off of an earlier version of postGIS. Buena Suerte! Greg Williamson > > From: José Pedro Santos

Re: [GENERAL] PostgreSQL and WMS/WFS Service

2012-10-09 Thread David Salisbury
that, and with Geoserver.. http://en.wikipedia.org/wiki/GeoServer -ds On 10/9/12 4:58 PM, Greg Williamson wrote: You might look at the GIS extension, PostGIS: Not sure how much yu need to do, but a company I used to work for ran a WMS service off of an ear

Re: [GENERAL] pgxs problem...

2012-10-09 Thread Tom Lane
I wrote: > So if I've not lost track, the scorecard is: > 1. We need to install mkldexport.sh when on AIX, so that pgxs builds can > use it. > 2. Makefile.aix has the wrong idea about where to find postgres.imp when > in pgxs mode. > 3. pljava needs -lm and isn't explicitly asking for it. > I w

Re: [GENERAL] pgxs problem...

2012-10-09 Thread John R Pierce
On 10/09/12 6:09 PM, Tom Lane wrote: I wrote: >So if I've not lost track, the scorecard is: >1. We need to install mkldexport.sh when on AIX, so that pgxs builds can >use it. >2. Makefile.aix has the wrong idea about where to find postgres.imp when >in pgxs mode. >3. pljava needs -lm and isn't e

Re: [GENERAL] pgxs problem...

2012-10-09 Thread Tom Lane
John R Pierce writes: > On 10/09/12 6:09 PM, Tom Lane wrote: >> I've committed a patch for the first two things: >> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bd0ef304f8a306522983f3b4b06274fdc45beed8 >> ... but not having an AIX machine, I can't actually test it. Would >> y

[GENERAL] pg_upgrade not detecting version properly

2012-10-09 Thread Chris Ernst
Hi all, I'm trying to test using pg_upgrade to go from 9.1.6 to 9.2.1 on Ubuntu server 10.04. But when I run pg_upgrade, it tells me I can only run it on 8.3 or later. Old: postgres=# SELECT version(); version -