[GENERAL] pg_upgrade not detecting version properly

[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


 PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
(1 row)


New:
postgres=# SELECT version();
version


 PostgreSQL 9.2.1 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
(1 row)


Yet when I try to run pg_upgrade:

$ /usr/lib/postgresql/9.2/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin/
-d /postgresql/9.1/main -B /usr/lib/postgresql/9.2/bin/ -D
/postgresql/9.2/main -k -c -v
Running in verbose mode
Performing Consistency Checks
-
Checking current, bin, and data directories ok
Checking cluster versions
This utility can only upgrade from PostgreSQL version 8.3 and later.
Failure, exiting

Any idea what could be going on here?

Thank you in advance for your help.

- Chris



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

Re: [GENERAL] pgxs problem...

[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
>> you verify it works?

> hmm, i've not worked with git much.   whats the best way I can verify 
> this?   should I just run the patches against a 9.1.6 tarball or should 
> I attempt a full checkout from git?

It's enough to apply the patch to a 9.1.6 tarball.  If you go to that
page and click the "patch" button, you'll get a plain-text patch you
can feed to "patch -p1".

regards, tom lane


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

Re: [GENERAL] pgxs problem...

[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 explicitly asking for it.
>I will see about fixing the first two, but the third is on pljava to
>fix.

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
you verify it works?



hmm, i've not worked with git much.   whats the best way I can verify 
this?   should I just run the patches against a 9.1.6 tarball or should 
I attempt a full checkout from git?






(These aren't new bugs BTW --- it looks to me like this has been wrong
since the pgxs code was created, in 8.1.  I guess we don't have many
AIX users :-()


at least not AIX users using the PGXS infrastructure, we've been running 
PG on AIX for a couple years now in house.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

Re: [GENERAL] pgxs problem...

[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 will see about fixing the first two, but the third is on pljava to
> fix.

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
you verify it works?

regards, tom lane


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

Re: [GENERAL] PostgreSQL and WMS/WFS Service

[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 earlier version of postGIS.

Buena Suerte!

Greg Williamson




From: José Pedro Santos
To: Postgres Ajuda
Sent: Tuesday, October 9, 2012 8:37 AM
Subject: [GENERAL] PostgreSQL and WMS/WFS Service



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









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

Re: [GENERAL] PostgreSQL and WMS/WFS Service

[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 
>To: Postgres Ajuda  
>Sent: Tuesday, October 9, 2012 8:37 AM
>Subject: [GENERAL] PostgreSQL and WMS/WFS Service
> 
>
> 
>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
>
>
>


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

Re: [GENERAL] pgxs problem...

[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.   however, SHLIB_LINK is 
> defined to concatenate PLJAVA_LDFLAGS, so I set that to -lm and POOF, 
> its built.   scary!

Well, the reason I mentioned contrib/cube is that it's known to need
libm.  I see in its makefile

SHLIB_LINK += $(filter -lm, $(LIBS))

so apparently that's the de rigueur way to add libm when you need it.
I'd suggest pestering the pljava people to do likewise.  They might be
getting away without this on more-forgiving platforms, but that doesn't
make it good practice to omit.


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 will see about fixing the first two, but the third is on pljava to
fix.

(These aren't new bugs BTW --- it looks to me like this has been wrong
since the pgxs code was created, in 8.1.  I guess we don't have many
AIX users :-()

regards, tom lane


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

Re: [GENERAL] pgxs problem...

[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 --- do you?


I do.

   ...
   /usr/vacpp/bin/xlc -q64 -I/home/postgres/src/include -O2 -qarch=pwr5
   -qtune=balanced -qnoansialias  -o cube.so libcube.a
   -Wl,-bE:libcube.exp -L../../src/port -L/home/postgres/src/lib
   -Wl,-blibpath:'/opt/pgsql91/lib:/home/postgres/src/lib:/usr/lib:/lib' 
-Wl,-bnoentry
   -Wl,-H512 -Wl,-bM:SRE -lm -Wl,-bI:../../src/backend/postgres.imp
   ...


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.   however, SHLIB_LINK is 
defined to concatenate PLJAVA_LDFLAGS, so I set that to -lm and POOF, 
its built.   scary!







--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

Re: [GENERAL] pgxs problem...

[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 or pljava's.  On my machine, if I go into say
contrib/cube and do "make", I see -lm in the link command --- do you?

regards, tom lane


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

Re: [GENERAL] pgxs problem...

[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 tested
before).  A look in src/backend/Makefile shows that the POSTGRES_IMP
file is actually installed into $(pkglibdir), so that's probably what
you want there instead of $(bindir)/postgres.


do I need to submit bug reports on this stuff, or is this discussion 
good enough to get it into the "fix me" queue for eventual release?


btw, $pkglibdir didn't fare any better than $libdir, I'm stuck at...

$ ./makeit c_all
gmake[1]: Entering directory `/home/postgres/src/pljava-1.4.3/build/objs'
/home/postgres/src/pljava-1.4.3/src/C/pljava/Makefile:232: warning: 
overriding commands for target `pljava.so'
/opt/pgsql91/lib/pgxs/src/makefiles/../../src/Makefile.shlib:387: 
warning: ignoring old commands for target `pljava.so'
/usr/bin/ar crs libpljava.a Backend.o Exception.o ExecutionPlan.o 
Function.o HashMap.o Invocation.o Iterator.o JNICalls.o PgObject.o 
PgSavepoint.o SPI.o SQLInputFromChunk.o SQLInputFromTuple.o 
SQLOutputToChunk.o SQLOutputToTuple.o Session.o SubXactListener.o 
XactListener.o backports.o type/AclId.o type/Any.o type/Array.o 
type/BigDecimal.o type/Boolean.o type/Byte.o type/Coerce.o 
type/Composite.o type/Date.o type/Double.o type/ErrorData.o type/Float.o 
type/HeapTupleHeader.o type/Integer.o type/JavaWrapper.o 
type/LargeObject.o type/Long.o type/Oid.o type/Portal.o type/Relation.o 
type/Short.o type/String.o type/Time.o type/Timestamp.o 
type/TriggerData.o type/Tuple.o type/TupleDesc.o type/TupleTable.o 
type/Type.o type/UDT.o type/Void.o type/byte_array.o

touch libpljava.a
/opt/pgsql91/lib/pgxs/src/makefiles/../../src/backend/port/aix/mkldexport.sh 
libpljava.a >libpljava.exp
/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/src/lib 
-Wl,-blibpath:'/opt/pgsql91/lib:/opt/pgsql91/lib:/home/postgres/src/lib:/usr/lib:/lib' 
-Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE 
-Wl,-bI:/opt/pgsql91/lib/postgres.imp -L. -L"/usr/java5_64/jre/bin/j9vm" 
-ljvm

ld: 0711-317 ERROR: Undefined symbol: .floor
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more 
information.

gmake[1]: *** [libpljava.a] Error 8
gmake[1]: *** Deleting file `libpljava.a'
gmake[1]: Leaving directory `/home/postgres/src/pljava-1.4.3/build/objs'
gmake: *** [c_all] Error 2

(makeit sets up the path and other environment stuff for the pljava 
gmake... c_all tells pljava's make to just build the C side of things 
and not the java side, which has its OWN problems far out of scope of 
this list)




--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

Re: [GENERAL] pgxs problem...

[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 PGXS
   BE_DLLLIBS= -Wl,-bI:$(libdir)/$(POSTGRES_IMP)
   else
   BE_DLLLIBS= -Wl,-bI:$(top_builddir)/src/backend/$(POSTGRES_IMP)
   endif



and still no good.   but this could be tossing the ball back to the 
pljava side of things, except it does appear to be pgxs stuff still.


   gmake[1]: Entering directory
   `/home/postgres/src/pljava-1.4.3/build/objs'
   /home/postgres/src/pljava-1.4.3/src/C/pljava/Makefile:232: warning:
   overriding commands for target `pljava.so'
   /opt/pgsql91/lib/pgxs/src/makefiles/../../src/Makefile.shlib:387:
   warning: ignoring old commands for target `pljava.so'
   /usr/bin/ar crs libpljava.a Backend.o Exception.o ExecutionPlan.o
   Function.o HashMap.o Invocation.o Iterator.o JNICalls.o PgObject.o
   PgSavepoint.o SPI.o SQLInputFromChunk.o SQLInputFromTuple.o
   SQLOutputToChunk.o SQLOutputToTuple.o Session.o SubXactListener.o
   XactListener.o backports.o type/AclId.o type/Any.o type/Array.o
   type/BigDecimal.o type/Boolean.o type/Byte.o type/Coerce.o
   type/Composite.o type/Date.o type/Double.o type/ErrorData.o
   type/Float.o type/HeapTupleHeader.o type/Integer.o
   type/JavaWrapper.o type/LargeObject.o type/Long.o type/Oid.o
   type/Portal.o type/Relation.o type/Short.o type/String.o type/Time.o
   type/Timestamp.o type/TriggerData.o type/Tuple.o type/TupleDesc.o
   type/TupleTable.o type/Type.o type/UDT.o type/Void.o type/byte_array.o
   touch libpljava.a
   /opt/pgsql91/lib/pgxs/src/makefiles/../../src/backend/port/aix/mkldexport.sh
   libpljava.a >libpljava.exp
   /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/src/lib
   
-Wl,-blibpath:'/opt/pgsql91/lib:/opt/pgsql91/lib:/home/postgres/src/lib:/usr/lib:/lib'
   -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE
   -Wl,-bI:/opt/pgsql91/lib/postgres.imp -L.
   -L"/usr/java5_64/jre/bin/j9vm" -ljvm
   ld: 0711-317 ERROR: Undefined symbol: .floor
   ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more
   information.
   gmake[1]: *** [libpljava.a] Error 8
   gmake[1]: *** Deleting file `libpljava.a'
   gmake[1]: Leaving directory `/home/postgres/src/pljava-1.4.3/build/objs'
   gmake: *** [c_all] Error 2






--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

Re: [GENERAL] pgxs problem...

[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/src/lib
> 
> -Wl,-blibpath:'/opt/pgsql91/lib:/opt/pgsql91/lib:/home/postgres/src/lib:/usr/lib:/lib'
> -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE
> -Wl,-bI:/opt/pgsql91/bin/postgres/postgres.imp -L.
> -L"/usr/java5_64/jre/bin/j9vm" -ljvm
> ld: 0706-003 Cannot find or read import file:
> /opt/pgsql91/bin/postgres/postgres.imp
>  ld:accessx(): A parameter must be a directory.

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

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 tested
before).  A look in src/backend/Makefile shows that the POSTGRES_IMP
file is actually installed into $(pkglibdir), so that's probably what
you want there instead of $(bindir)/postgres.

regards, tom lane


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

Re: [GENERAL] pgxs problem...

[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 the pgxs side.

$  find /opt/pgsql91 -name Makefile\* | xargs grep 'postgres.imp'
/opt/pgsql91/lib/pgxs/src/Makefile.port:POSTGRES_IMP= postgres.imp
$  find /opt/pgsql91 -name Makefile\* | xargs grep 'POSTGRES_IMP'
/opt/pgsql91/lib/pgxs/src/Makefile.port:POSTGRES_IMP= postgres.imp
/opt/pgsql91/lib/pgxs/src/Makefile.port:BE_DLLLIBS= 
-Wl,-bI:$(bindir)/postgres/$(POSTGRES_IMP)
/opt/pgsql91/lib/pgxs/src/Makefile.port:BE_DLLLIBS= 
-Wl,-bI:$(top_builddir)/src/backend/$(POSTGRES_IMP)


$(bindir)/postgres/* can't possibly be right, since $(bindir)/postgres 
is the runtime binary for the server  ?!?






--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

Re: [GENERAL] pgxs problem...

[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/makefiles/../../src/backend/port/aix/mkldexport.sh
>libpljava.a >libpljava.exp
>/bin/sh:
>/opt/pgsql91/lib/pgxs/src/makefiles/../../src/backend/port/aix/mkldexport.sh:
>not found.

Hm.  There is a src/backend/port/aix/mkldexport.sh in the source tree,
and I see that Makefile.shlib relies on it, but I don't see any evidence
that we make any effort to install it.  For the moment you could just
copy it over to the pgxs tree ... but if we wanted to support AIX a bit
better it seems like we ought to do that automatically.



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/src/lib
   
-Wl,-blibpath:'/opt/pgsql91/lib:/opt/pgsql91/lib:/home/postgres/src/lib:/usr/lib:/lib'
   -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE
   -Wl,-bI:/opt/pgsql91/bin/postgres/postgres.imp -L.
   -L"/usr/java5_64/jre/bin/j9vm" -ljvm
   ld: 0706-003 Cannot find or read import file:
   /opt/pgsql91/bin/postgres/postgres.imp
ld:accessx(): A parameter must be a directory.

so thats /another/ missing file...  hmmm, a file of that name is in lib, 
not bin/postgres ...


   $ find . -name postgres.imp
   ./src/backend/postgres.imp
   ./src/test/regress/tmp_check/install/opt/pgsql91/lib/postgres.imp

   $ find /opt/pgsql91 -name postgres.imp
   /opt/pgsql91/lib/postgres.imp

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






--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

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

[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 kind of replication, please look into Slony,
Bucardo, or Londiste. They're all trigger-based replication systems that
have been in production use by several companies for years now. You're
much better off using them than cobbling something together yourself.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


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

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

[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 about the following, implement a trigger function that handles the 
insert
and update and does the same to the _archive database. Using dblink I am able
to connect to the _archive database. See my trigger function (implemented in
plpgsql):

DECLARE
qry TEXT;
conns TEXT[];
BEGIN
SELECT dblink_get_connections() INTO conns;
IF (COUNT(conns) = 0) THEN
SELECT dblink_connect_u('archiveconn', 'dbname=some_archive');
END IF;

IF (TG_OP = 'INSERT') THEN
qry := 'INSERT INTO ' || TG_TABLE_NAME || ' VALUES' || NEW.*;
SELECT dblink_exec('archiveconn', qry);
ELSIF (TG_OP = 'UPDATE') THEN
qry := 'UPDATE ' || TG_TABLE_NAME;
SELECT dblink_exec('archiveconn', qry);
END IF;

RETURN NULL;
END

The query generated in qry has as example the following output:
INSERT INTO test_tbl VALUES(13, somevalue)

And that generates an error of course, somevalue doesn't exist.

If someone has an idea how to solve this I would be greatful!

Maybe there is another approach, in that case let me know.

Regards,

Matthijs Möhlmann

PS: please keep me in the CC as I am not subscribed to this list.



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

[GENERAL] PostgreSQL and WMS/WFS Service

[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
  

Re: [GENERAL] something better than pgtrgm?

[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 the trigrams you were just working with.  It probably
sucks in performance, but at least you're likely to get valid
sequences this way.  

For inspiration on this (and why I have so much depressing news on the
subject of internationalization in a multi-script and multi-lingual
environment), see RFC 3743 and RFC 4290.  These are related (among
other things) to how to make "variants" of different DNS labels
somehow hang together.  The problem is not directly related to what
you're working on, but it's a similar sort of problem: people have
rough ideas of what they're entering, and they need an exact match.
You have the good fortune of being able to provide them with a hint!
I wish I were in your shoes.

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


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

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

[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);
> CREATE INDEX ON schema_a.table_a(function_x(i));
> INSERT INTO schema_a.table_a VALUES(1),(9),(2);

Mph.  Well, actually what you've got there is a function that will break
anytime somebody looks at it sideways, anyhow.  You need to
schema-qualify the reference to function_y, or if you don't want to do
that for some reason, you could attach a "SET search_path" clause to the
definition of function_x.

I don't regard this as a Postgres bug, because index functions are
required to be immutable, and function_x fails that test because its
results vary depending on search_path.

regards, tom lane


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

Re: [GENERAL] something better than pgtrgm?

[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 purpose" -- Mark Shuttleworth

Re: [GENERAL] pgxs problem...

[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/mkldexport.sh 
> libpljava.a >libpljava.exp
> /bin/sh: 
> /opt/pgsql91/lib/pgxs/src/makefiles/../../src/backend/port/aix/mkldexport.sh: 
> not found.

Hm.  There is a src/backend/port/aix/mkldexport.sh in the source tree,
and I see that Makefile.shlib relies on it, but I don't see any evidence
that we make any effort to install it.  For the moment you could just
copy it over to the pgxs tree ... but if we wanted to support AIX a bit
better it seems like we ought to do that automatically.

regards, tom lane


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

Re: [GENERAL] something better than pgtrgm?

[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 the above list does include scripts that read from right to left.

It's much worse than that.

It includes at least two variations of Arabic keyboard (depending on
which language you are using, for instance, you get a different
Unicode encoding of the character YEH, which in some languages has
something approximating the frequency of the letter a in English), and
you have endless problems with dots versus no dots on Arabic-script
spellings (not all uses of Arabic the script are Arabic the
language).  You also run smack into the problem of correct syllable
formation in Brahmi-derived scripts.

If you're going to do something with this sort of language-agnostic
"did you mean" work, you will need to be extremely rigorous about
normalizing spellings on the way in.  Is that a possibility?  If so, I
can almost imagine a way this could work.  If not, well,
"internationalization is hard."  :-/

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


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

Re: [GENERAL] something better than pgtrgm?

[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 (scientific names),
and all languages spoken in africa, europe and western asia.



>
> If not, then you're not really language unaware, but instead
> constrained by a subset of languages.  That is a more tractable
> problem (for instance, you may not have to worry about direction
> changes, which vastly simplifies the problem).
>

I'm not very knowledgeable about scripts around the world, but i am afraid
that the above list does include scripts that read from right to left.


-- 
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

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

[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));
INSERT INTO schema_a.table_a VALUES(1),(9),(2);

Run pg_upgrade:
/usr/pgsql-9.2/bin/pg_upgrade --old-datadir
"/var/lib/pgsql/9.1/data" --new-datadir
"/var/lib/pgsql/9.2/data"   --old-bindir "/usr/pgsql-9.1/bin"
--new-bindir "/usr/pgsql-9.2/bin"
...
Restoring database schema to new cluster*failure*

>From pg_upgrade_restore.log:
...
SET search_path = schema_a, pg_catalog;
...
CREATE INDEX table_a_function_x_idx ON table_a USING btree
(public.function_x(i));
psql:pg_upgrade_dump_db.sql:110: ERROR:  function function_y(integer)
does not exist
LINE 1:  SELECT function_y($1)
^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY:   SELECT function_y($1)
CONTEXT:  SQL function "function_x" during inlining

(Same error is when only restore database.)



On Tue, Oct 9, 2012 at 4:04 AM, Tom Lane  wrote:
> marian krucina  writes:
>> pg_upgrade failed on own server, because we used functions from public
>> schema in index. We install common functions (e.g. postgresql contrib)
>> to public schema. Tables and indexes are in another schema, and names
>> of functions without a schema name.
>
> Are you sure that has anything to do with schemas, or is it that you
> forgot to install the (updated versions of the) same contrib modules
> into the new installation?
>
> If not that, please provide a complete description of what you've got
> in your old database and the errors you got trying to upgrade.  Also,
> exactly what old and new PG versions are you working with?
>
> regards, tom lane


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

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

[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
results in preview items.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Postgres-will-not-start-due-to-corrupt-index-tp5726462p5727130.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] something better than pgtrgm?

[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 you want, at least from the
user's point of view.

The character codes case probably would work in a language-unaware
way.

But for the mixed languages case, surely it's not _any_ mixed
language?  Are you mixing Arabic, Farsi, Chinese, and Hindi, for
instance?

If not, then you're not really language unaware, but instead
constrained by a subset of languages.  That is a more tractable
problem (for instance, you may not have to worry about direction
changes, which vastly simplifies the problem).

Best,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


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

[GENERAL] something better than pgtrgm?

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

(i want to use it for *"did you mean ...?"* for approx 6-10 character codes
or 8-20 letter words of mixed languages)

Cheers,

WBL
-- 
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: [GENERAL] CTE materializing sets?

[Виктор Егоров]

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 (per SQL Standard).
I asked on the #postgresql channel and was pointed, that typically you
get benefits of this feature
when you have to join grouping subquery to itself.

I went and did some tests. Table "attempt" contains e-mail delivery
attempts for the postfix:

# select 
relname,relpages,reltuples::numeric,pg_size_pretty(pg_relation_size(oid))
from pg_class where relname='attempt';
 relname | relpages | reltuples | pg_size_pretty
-+--+---+
 attempt |   145117 |   4252530 | 1134 MB


My default work_mem is 1MB on this instance.

First, plain query with 2 subqueries:

# explain (analyze, buffers)
select a.eid, b.eid from
  (select recipient_email_id eid, count(*) cnt, min(tstamp) as minmsg,
max(tstamp) as maxmsg from attempt group by recipient_email_id) a,
  (select recipient_email_id eid, count(*) cnt, min(tstamp) as minmsg,
max(tstamp) as maxmsg from attempt group by recipient_email_id) b
where a.minmsg = b.maxmsg;

 QUERY PLAN
---
 Merge Join  (cost=1861911.11..1953183.16 rows=6067386 width=16)
(actual time=65758.378..66115.400 rows=59845 loops=1)
   Merge Cond: (a.minmsg = b.maxmsg)
   Buffers: shared hit=1590 read=288644, temp read=103129 written=103134
   ->  Sort  (cost=930955.56..931042.64 rows=34835 width=16) (actual
time=30242.503..30370.379 rows=212434 loops=1)
 Sort Key: a.minmsg
 Sort Method: external merge  Disk: 5400kB
 Buffers: shared hit=779 read=144338, temp read=51481 written=51481
 ->  Subquery Scan on a  (cost=873875.76..927729.06 rows=34835
width=16) (actual time=26744.434..30008.996 rows=212434 loops=1)
   Buffers: shared hit=779 read=144338, temp read=50561
written=50561
   ->  GroupAggregate  (cost=873875.76..927380.71
rows=34835 width=16) (actual time=26744.433..29951.390 rows=212434
loops=1)
 Buffers: shared hit=779 read=144338, temp
read=50561 written=50561
 ->  Sort  (cost=873875.76..884507.08 rows=4252528
width=16) (actual time=26744.273..28296.850 rows=4255749 loops=1)
   Sort Key: public.attempt.recipient_email_id
   Sort Method: external merge  Disk: 108168kB
   Buffers: shared hit=779 read=144338, temp
read=50561 written=50561
   ->  Seq Scan on attempt
(cost=0.00..187642.28 rows=4252528 width=16) (actual
time=0.010..13618.612 rows=4255749 loops=1)
 Buffers: shared hit=779 read=144338
   ->  Materialize  (cost=930955.56..931129.73 rows=34835 width=16)
(actual time=35515.860..35640.974 rows=214271 loops=1)
 Buffers: shared hit=811 read=144306, temp read=51648 written=51653
 ->  Sort  (cost=930955.56..931042.64 rows=34835 width=16)
(actual time=35515.853..35586.598 rows=210800 loops=1)
   Sort Key: b.maxmsg
   Sort Method: external merge  Disk: 5384kB
   Buffers: shared hit=811 read=144306, temp read=51648
written=51653
   ->  Subquery Scan on b  (cost=873875.76..927729.06
rows=34835 width=16) (actual time=31879.743..35251.218 rows=212434
loops=1)
 Buffers: shared hit=811 read=144306, temp
read=50561 written=50561
 ->  GroupAggregate  (cost=873875.76..927380.71
rows=34835 width=16) (actual time=31879.741..35184.965 rows=212434
loops=1)
   Buffers: shared hit=811 read=144306, temp
read=50561 written=50561
   ->  Sort  (cost=873875.76..884507.08
rows=4252528 width=16) (actual time=31879.577..33460.975 rows=4255749
loops=1)
 Sort Key: public.attempt.recipient_email_id
 Sort Method: external merge  Disk: 108168kB
 Buffers: shared hit=811 read=144306,
temp read=50561 written=50561
 ->  Seq Scan on attempt
(cost=0.00..187642.28 rows=4252528 width=16) (actual
time=0.012..17637.516 rows=4255749 loops=1)
   Buffers: shared hit=811 read=144306
 Total runtime: 67611.657 ms
(34 rows)

The source relation is scanned twice. Now, using CTE and it's
materialization feature:

# explain (analyze, buffers)
with msgs as (select recipient_email_id eid, count(*) cnt, min(tstamp)
as minmsg, max(tstamp) as maxmsg from attempt group by
recipient_email_id)
select a.eid, b.eid from msgs a, msgs b where a.minmsg=b.maxmsg;
QUE

Re: [GENERAL] CTE materializing sets?

[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 
rewrite.


--
Craig Ringer



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

Re: [GENERAL] CTE materializing sets?

[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 question, I too would be interested in this.

I'd expect it'd materialize to RAM if the result is within `work_mem`
but I'd love to know for sure.


Yes - it's using a tuplestore, and that's the default behavior. It 
stores the
tuples in RAM until it reaches work_mem and then starts spilling the 
data

to disk. It's entirely transparent behavior.

Tomas


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

Re: [GENERAL] CTE materializing sets?

[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 TRUNCATE PARTITION in SQL Server
https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table



2012/10/9 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'd expect it'd materialize to RAM if the result is within `work_mem` but
> I'd love to know for sure.
>
> --
> Craig Ringer
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>

Re: [GENERAL] CTE materializing sets?

[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'd expect it'd materialize to RAM if the result is within `work_mem` 
but I'd love to know for sure.


--
Craig Ringer



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

Re: [GENERAL] Creating and managing triggers

[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 other database users in the company were locked
> > out during this process. The table getting the triggers has about 187000
> > rows in it and is pretty central, so lots of functions join with it.
>
> CREATE TRIGGER, per se, should be nearly instantaneous.  It sounds like
> the CREATE TRIGGER command is blocked behind some other operation that
> has a (not necessarily exclusive) lock on the table; and then everything
> else is queueing up behind the CREATE TRIGGER's exclusive lock request.
>
> Look into pg_locks and pg_stat_activity to see what's holding things up.
>
> I'd bet on an old idle-in-transaction session, that may have done
> nothing more exciting than reading the table at issue, but is still
> blocking things for failure to close its transaction.  Sitting idle with
> an open transaction is something to be discouraged for a lot of reasons
> besides this one.
>
> > ... They restarted the database server when the second
> > create trigger hung, so I don't know what happened with it.
>
> Whoever "they" is needs to learn a bit more about being a Postgres DBA,
> methinks.  There are smaller hammers than a database restart.
>
> > I didn't
> > even save the name, obviously a problem on my part. But there should be
> > some equivalent of Show Trigger, shouldn't there?
>
> psql's \dt command is the usual thing, or if you like GUIs you could try
> PgAdmin.
>
Obviously, typo.
\d[S+] your_table_name instead of \dt.

-- 
// Dmitriy.