[GENERAL] Question about switchover with PG9 replication
Hello, I have a few questions about streaming replication. We have a database of nearly 300 GB. We are using Postgres 8.3.1 and Slony for replication on Ubuntu 10.04. We have 4 Postgres servers PGMaster1, PGSlave1, PGMaster2 and PGSlave2 on 2 distant sites (distance between the 2 sites is nearly 1000 kms). PGMaster1 and PGSlave1 are in the site S1, and PGMaster2 and PGSlave2 are in the other site S2. Using Slony, we are replicating as following : PGSlave1 has subscribed as a slave of PGMaster1 (local replication in S1) PGMaster2 has subscribed as a slave of PGMaster1 (distant replication from S1 to S2) PGSlave2 has subscribed as a slave of PGMaster2 (local replication in S2; this is a cascaded replication) All the SQL command UPDATE, INSERT and DELETE are sent to PGMaster1 and the data modifications are then propagated to the other servers by Slony. Read are load-balanced between the 4 servers We often need to do switchovers between PGMaster1 and PGMaster2 (for maintenance operations, upgrade, ...). We would like to upgrade to Postgres 9, and use the streaming replication of PG9 Question 1 : is it possible to have such a replication configuration with the streaming replication of PG9 (cascaded replication) ? Question 2 : All the procedures I have seen describing a switchover between 2 PG servers require to copy (or rsync) the database from the new master (old slave) to the new slave (old master). Is it possible to do switchover between sites (between PGMaster1 and PGMaster2) whithout copying all the database from the new PG master to the new PG slave ? If it is not possible yet, shall it be possible in future releases ? Thanks
[GENERAL] Very slow moving between partition
Hi! I have a big table with about 26 millon registers, and I have 3 partitions, one that handles daily information, only one register per day for about 24,000 elements, that's it about 24,000 register daily, other one that handles one register per minute per day, only 8 hours a day, for about 9000 elements, that's 60*8*9000 registers daily, and other one that works like an archive of that per minute information. The archive partition don't have indexes, because it works as an archive, I don't do queries on that table frequently. Well, maybe the primary key (ID, bigserial is the only index) The other tables have only have the primary key, the name of the element (varchar16) and date (date) fields as indexes. I use a boolean field to decide if a register is archived or not, so if I update one field setting archived=true, then a trigger deletes that field from their original partition and reinserts it on the archive partition. That works good with small number of records, but if I have 300,000 records it can take a lot of time to move that records. I wish to know if there is a better way to move records from one partition to another partition, that does it fast. Thanks.
Re: [GENERAL] Question about switchover with PG9 replication
Question 1 : is it possible to have such a replication configuration with the streaming replication of PG9 (cascaded replication) ? Nope, as far as I have tested pg only has 1 master and can have a number of slaves, so having 2 masters is not possible. Question 2 : All the procedures I have seen describing a switchover between 2 PG servers require to copy (or rsync) the database from the new master (old slave) to the new slave (old master). Is it possible to do switchover between sites (between PGMaster1 and PGMaster2) whithout copying all the database from the new PG master to the new PG slave ? If it is not possible yet, shall it be possible in future releases ? Nope this is not possible, pg requires to have an updates basebackup at the slave, this is because if you loose a lot of streamed wal files it is impossible for the slave to catch up and have consistent data.
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Fri, Feb 4, 2011 at 21:32, Thom Brown t...@linux.com wrote:
The issue is that generate_series will not return if the series hits
either the upper or lower boundary during increment, or goes beyond
it. The attached patch fixes this behaviour, but should probably be
done a better way. The first 3 examples above will not return.
There are same bug in int8 and timestamp[tz] versions.
We also need fix for them.
=# SELECT x FROM generate_series(9223372036854775807::int8,
9223372036854775807::int8) AS a(x);
=# SELECT x FROM generate_series('infinity'::timestamp, 'infinity', '1
sec') AS a(x);
=# SELECT x FROM generate_series('infinity'::timestamptz, 'infinity',
'1 sec') AS a(x);
postgres=# SELECT x FROM generate_series(1, 9,-1) AS a(x);
postgres=# SELECT x FROM generate_series(1, 9,3) AS a(x);
They work as expected in 9.1dev.
--
Itagaki Takahiro
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On 7 February 2011 09:04, Itagaki Takahiro itagaki.takah...@gmail.com wrote:
On Fri, Feb 4, 2011 at 21:32, Thom Brown t...@linux.com wrote:
The issue is that generate_series will not return if the series hits
either the upper or lower boundary during increment, or goes beyond
it. The attached patch fixes this behaviour, but should probably be
done a better way. The first 3 examples above will not return.
There are same bug in int8 and timestamp[tz] versions.
We also need fix for them.
=# SELECT x FROM generate_series(9223372036854775807::int8,
9223372036854775807::int8) AS a(x);
Yes, of course, int8 functions are separate. I attach an updated
patch, although I still think there's a better way of doing this.
=# SELECT x FROM generate_series('infinity'::timestamp, 'infinity', '1
sec') AS a(x);
=# SELECT x FROM generate_series('infinity'::timestamptz, 'infinity',
'1 sec') AS a(x);
I'm not sure how this should be handled. Should there just be a check
for either kind of infinity and return an error if that's the case? I
didn't find anything wrong with using timestamp boundaries:
postgres=# SELECT x FROM generate_series('1 Jan 4713 BC
00:00:00'::timestamp, '1 Jan 4713 BC 00:00:05'::timestamp, '1 sec') AS
a(x);
x
4713-01-01 00:00:00 BC
4713-01-01 00:00:01 BC
4713-01-01 00:00:02 BC
4713-01-01 00:00:03 BC
4713-01-01 00:00:04 BC
4713-01-01 00:00:05 BC
(6 rows)
Although whether this demonstrates a true timestamp boundary, I'm not sure.
postgres=# SELECT x FROM generate_series(1, 9,-1) AS a(x);
postgres=# SELECT x FROM generate_series(1, 9,3) AS a(x);
They work as expected in 9.1dev.
Those 2 were to demonstrate that the changes don't affect existing
functionality. My previous patch proposal (v2) caused these to return
unexpected output.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
generate_series_fix.v4.patch
Description: Binary data
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] XML Encoding problem
Hi, I have test database with UTF-8 encoding. I putted there XML aЁĄ¡/a, (U+0401, U+0104, U+00A1). I changed client encoding to iso8859-2, as the result of select I got ERROR: character 0xd081 of encoding UTF8 has no equivalent in LATIN2 Stan SQL:22P05. I should got result with characters entities for unparsable characters #...;. Kind regards, Radosław Smogura -- 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] Subquery for column names of tablefunc crosstab queries
Thanks Joe
I'm very much learning as I go.
I've followed your example from top to bottom - your sample code works -
however its not clear to me how to execute the generate_crosstab_sql
function to produce the results in one single step.
I've tried this:
CREATE OR REPLACE VIEW mycrosstabresults AS select * FROM (SELECT
generate_crosstab_sql('mytable',
'rowid',
'text',
'rowdt::date',
'temperature',
'int',
'1 = 1')) as crosstabresults;
but this simply populates the view with the string below, not the actual
crosstab results.
SELECT * FROM crosstab ('SELECT rowid,rowdt::date,temperature FROM
mytable WHERE 1 = 1 ORDER BY
1,2','VALUES($v$2003-03-01$v$),($v$2003-03-02$v$),($v$2003-03-03$v$)')
AS (rowid
text,2003-03-01 int,2003-03-02 int,2003-03-03 int)
Is it possible to incorporate and execute this string, all within my CREATE
OR REPLACE VIEW statement?
I would appreciate any help you can give.
Many thanks
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Subquery-for-column-names-of-tablefunc-crosstab-queries-tp3351437p3374199.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
[GENERAL] How to create index on only some of the rows
Hello. How do you create an index for only some of the rows in a table? I read in the docs: The expression used in the WHERE clause can refer only to columns of the underlying table, but it can use all columns, not just the ones being indexed. Presently, subqueries and aggregate expressions are also forbidden in WHERE. The same restrictions apply to index fields that are expressions. So until this changes, can you just add a boolean field to tell if the column should be used in the index, and then run create index where use_in_index = true or are there other (better?) ways of doing this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Slow Inserts, two different scenarios.
Hi,
I have a problem when doing INSERT's in a table.
The table structure is:
uri (varchar 1) PK
id_language (varchar 10) PK
id_category (int4) PK
id_data (varchar 50) PK
id_ordinal (int4) PK (this field have a trigger to auto increment)
n_text (text)
When I run this function to do 90 INSERT's it runs well and in few time:
CREATE OR REPLACE FUNCTION doInserts() RETURNS integer AS
$BODY$
DECLARE
i integer;
BEGIN
i := 1;
while i = 90 loop
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'pagetitle', 'Pagina teste ' || i);
i := i + 1;
end loop;
RETURN i;
END
$BODY$
LANGUAGE 'plpgsql' ;
But when I do this with 10 INSERT's it seems to never end the
INSERT's operation, It is running at 5h now...
CREATE OR REPLACE FUNCTION doInserts() RETURNS integer AS
$BODY$
DECLARE
i integer;
BEGIN
i := 1;
while i = 10 loop
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'pagetitle', 'Pagina teste ' || i);
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'country_ad', 'italy');
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'services_available', 'service 1');
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'services_available', 'service 2');
i := i + 1;
end loop;
RETURN i;
END
$BODY$
LANGUAGE 'plpgsql' ;
What could be the problem here? Any clues?
Best Regards,
--
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] tuning postgresql writes to disk
On Thu, Feb 3, 2011 at 7:08 PM, Vinubalaji Gopal vinubal...@gmail.com wrote: already does this. I looked at the WAL parameters and the new async commit but not sure if I am looking at the right place. Say i have 10 clients connecting and each client is inserting a record. I want to You want the async commit. If you can detect and re-execute lost transactions, it gives you the best of everything: defer disk I/O and transaction boundaries are honored so you never have inconsistent data after crash recovery. -- 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] Directing Partitioned Table Searches
On Fri, Feb 4, 2011 at 7:35 PM, Bill Thoen bth...@gisnet.com wrote: And this selection will result in ALL partitions being searched. But why? SELECT cluid, farmid FROM clu JOIN farms ON ogc_fid=link WHERE state=zone The constraint exclusion code does not execute your constraints to decide whether to look at your partition; it examines the query and the constraint and does a proof to try to exclude the partition. If it cannot do that proof, it will scan that table. I'd like to be able to run some queries w/o the overhead of searching partitions unnecessarily. Can it be done? Your best bet is to know which partition you need and write your query that way dynamically, rather than trying to use a generic query and have the DB do the constraint exclusion. In your above case, if you know that 'zone' will limit you to just the MI table, then specify the MI table instead of the base clu table. -- 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] Why copy ... from stdio does not return immediately when reading invalid data?
I have analyzed the PostgreSQL protocol using Wireshark (an open source packet analyzer), and I observed that the PostgreSQL backend, while doing a COPY ... FROM STDIN, reports errors as soon as possible (especially errors related to invalid data). Therefore, the late reporting of errors while doing a COPY ... FROM STDIN is not a limitation of the underlying protocol; it is a limitation (or a design choice) of the libpq library. It looks like this is a well known issue because it is listed on the todo list: http://wiki.postgresql.org/wiki/Todo#COPY And was discussed before: http://archives.postgresql.org/pgsql-hackers/2008-04/msg01169.php Do you think it is possible to change that behavior, or work around it? While reading libpq source code, I noticed the function pqParseInput3 (file fe-protocol3.c) ignores error responses while the connection is in PGASYNC_COPY_IN state. Maybe we can make a special case for the COPY FROM subprotocol and handle errors early, in order to make them available to PQgetResult? Is is feasible in a simple way or is it a bad idea? Regards, Nicolas Grilly On Wed, Feb 2, 2011 at 20:06, John R Pierce pie...@hogranch.com wrote: On 02/02/11 10:20 AM, Nicolas Grilly wrote: Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to send gigabytes of data with just one copy ... from stdio query, and is there a way to be notified of a potential error before calling PQputCopyEnd? Or do I have to send my data in small chunks (for example batch of 1 rows), issue a PQputCopyEnd, check for errors, and continue with the next chunk? I would batch the data, maybe 1000 lines or even 100 lines at a time if these errors are at all frequent. put the errored batches in an exception list or something so you can sort them out later.
Re: [GENERAL] Question about switchover with PG9 replication
On Mon, Feb 07, 2011 at 09:20:36AM +0100, Jean-Armel Luce wrote: Is it possible to do switchover between sites (between PGMaster1 and PGMaster2) whithout copying all the database from the new PG master to the new PG slave ? You can't do database replication without copying the whole database, no. 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] How to create index on only some of the rows
On Feb 7, 2011, at 11:00 PM, A B wrote:
So until this changes, can you just add a boolean field to tell if the
column should be used in the index, and then run create index
where use_in_index = true or are there other (better?) ways of doing
this?
If you want you can do that Or You can use proper where clause as given below:
CREATE INDEX indexname on tablename(columname) where condition;
example:
create index directed_graph_idx on directed_graph(node_from) where node_from in
('A','B');
Thanks Regards,
Vibhor Kumar
vibhor.ku...@enterprisedb.com
Blog:http://vibhork.blogspot.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] How to create index on only some of the rows
On Mon, Feb 07, 2011 at 03:00:54PM +0100, A B wrote: So until this changes, can you just add a boolean field to tell if the column should be used in the index, and then run create index where use_in_index = true or are there other (better?) ways of doing this? What are the criteria for inclusion in the index? Those would be the criteria you put in your WHERE clause. 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] fulltext search and hunspell
Hey,
I want to use hunspell as a dictionary for the full text search by
* using PostgresSQL 8.4.7
* installing hunspell-de-de, hunspell-de-med
* creating a dictionary:
CREATE TEXT SEARCH DICTIONARY german_hunspell (
TEMPLATE = ispell,
DictFile = de_de,
AffFile = de_de,
StopWords = german
);
* changing the config
ALTER TEXT SEARCH CONFIGURATION german
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
word, hword, hword_part
WITH german_hunspell, german_stem;
* now testing the lexizer:
SELECT ts_lexize('german_hunspell', 'Schokaladenfarik');
ts_lexize
---
(1 Zeile)
Shouldn't it be something like this:
SELECT ts_lexize('norwegian_ispell', 'sjokoladefabrikk');
{sjokoladefabrikk,sjokolade,fabrikk}
(from the 8.4 documentation of PostgreSQL)
The dict and affix files in the tsearch_data directory were
automatically generated by pg_updatedicts.
Is this a problem of the splitting compound word functionality? Should
I use ispell instead of hunspell?
Thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests
Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. *configure: WARNING: could not locate CUnit required for liblwgeom unit tests* is there some setting I need to do to make it work? $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... ^C10.112.161.124$ $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/5bin/sed checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for fgrep... /usr/sfw/bin/ggrep -F checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for BSD- or MS-compatible name lister (nm)... /usr/ccs/bin/nm -p checking the name lister (/usr/ccs/bin/nm -p) interface... BSD nm checking whether ln -s works... yes checking the maximum length of command line arguments... 786240 checking whether the shell understands some XSI constructs... yes checking whether the shell understands +=... no checking for /usr/ccs/bin/ld option to reload object files... -r checking for objdump... no checking how to recognize dependent libraries... pass_all checking for ar... ar checking for strip... strip checking for ranlib... ranlib checking command to parse /usr/ccs/bin/nm -p output from gcc object... ok checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking for dlfcn.h... yes checking for objdir... .libs checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... -fPIC -DPIC checking if gcc PIC flag -fPIC -DPIC works... yes checking if gcc static flag -static works... no checking if gcc supports -c -o file.o... yes checking if gcc supports -c -o file.o... (cached) yes checking whether the gcc linker (/usr/ccs/bin/ld) supports shared libraries... yes checking whether -lc should be explicitly linked in... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... no checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes checking for gcc... (cached) gcc checking whether we are using the GNU C compiler... (cached) yes checking whether gcc accepts -g... (cached) yes checking for gcc option to accept ISO C89... (cached) none needed checking how to run the C preprocessor... gcc -E checking for g++... g++ checking whether we are using the GNU C++ compiler... yes checking whether g++ accepts -g... yes checking whether we are using the GNU C++ compiler... (cached) yes checking whether g++ accepts -g... (cached) yes checking how to run the C++ preprocessor... g++ -E checking for ld used by g++... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking whether the g++ linker (/usr/ccs/bin/ld) supports shared libraries... yes checking for g++ option to produce PIC... -fPIC -DPIC checking if g++ PIC flag -fPIC -DPIC works... yes checking if g++ static flag -static works... no checking if g++ supports -c -o file.o... yes checking if g++ supports -c -o file.o... (cached) yes checking whether the g++ linker (/usr/ccs/bin/ld) supports shared libraries... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking if g++ supports -Wall... yes checking if g++ supports -Wmissing-prototypes... yes checking if g++ supports -ffloat-store... yes checking for flex... flex checking lex output file root... lex.yy checking lex library... -lfl checking whether yytext is a pointer... yes checking for bison... bison -y checking ieeefp.h usability... yes checking ieeefp.h presence... yes checking for ieeefp.h... yes
Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests
It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. configure: WARNING: could not locate CUnit required for liblwgeom unit tests is there some setting I need to do to make it work? $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... ^C10.112.161.124$ $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/5bin/sed checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for fgrep... /usr/sfw/bin/ggrep -F checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for BSD- or MS-compatible name lister (nm)... /usr/ccs/bin/nm -p checking the name lister (/usr/ccs/bin/nm -p) interface... BSD nm checking whether ln -s works... yes checking the maximum length of command line arguments... 786240 checking whether the shell understands some XSI constructs... yes checking whether the shell understands +=... no checking for /usr/ccs/bin/ld option to reload object files... -r checking for objdump... no checking how to recognize dependent libraries... pass_all checking for ar... ar checking for strip... strip checking for ranlib... ranlib checking command to parse /usr/ccs/bin/nm -p output from gcc object... ok checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking for dlfcn.h... yes checking for objdir... .libs checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... -fPIC -DPIC checking if gcc PIC flag -fPIC -DPIC works... yes checking if gcc static flag -static works... no checking if gcc supports -c -o file.o... yes checking if gcc supports -c -o file.o... (cached) yes checking whether the gcc linker (/usr/ccs/bin/ld) supports shared libraries... yes checking whether -lc should be explicitly linked in... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... no checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes checking for gcc... (cached) gcc checking whether we are using the GNU C compiler... (cached) yes checking whether gcc accepts -g... (cached) yes checking for gcc option to accept ISO C89... (cached) none needed checking how to run the C preprocessor... gcc -E checking for g++... g++ checking whether we are using the GNU C++ compiler... yes checking whether g++ accepts -g... yes checking whether we are using the GNU C++ compiler... (cached) yes checking whether g++ accepts -g... (cached) yes checking how to run the C++ preprocessor... g++ -E checking for ld used by g++... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking whether the g++ linker (/usr/ccs/bin/ld) supports shared libraries... yes checking for g++ option to produce PIC... -fPIC -DPIC checking if g++ PIC flag -fPIC -DPIC works... yes checking if g++ static flag -static works... no checking if g++ supports -c -o file.o... yes checking if g++ supports -c -o file.o... (cached) yes checking whether the g++ linker (/usr/ccs/bin/ld) supports shared libraries... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking if g++ supports -Wall... yes checking if g++ supports -Wmissing-prototypes... yes checking if g++ supports -ffloat-store... yes checking for
Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests
installation was successful. But it did not install the liblwgeom.so Regards On Mon, Feb 7, 2011 at 12:29 PM, Paul Ramsey pram...@cleverelephant.cawrote: It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. *configure: WARNING: could not locate CUnit required for liblwgeom unit tests* is there some setting I need to do to make it work? $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... ^C10.112.161.124$ $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/5bin/sed checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for fgrep... /usr/sfw/bin/ggrep -F checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for BSD- or MS-compatible name lister (nm)... /usr/ccs/bin/nm -p checking the name lister (/usr/ccs/bin/nm -p) interface... BSD nm checking whether ln -s works... yes checking the maximum length of command line arguments... 786240 checking whether the shell understands some XSI constructs... yes checking whether the shell understands +=... no checking for /usr/ccs/bin/ld option to reload object files... -r checking for objdump... no checking how to recognize dependent libraries... pass_all checking for ar... ar checking for strip... strip checking for ranlib... ranlib checking command to parse /usr/ccs/bin/nm -p output from gcc object... ok checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking for dlfcn.h... yes checking for objdir... .libs checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... -fPIC -DPIC checking if gcc PIC flag -fPIC -DPIC works... yes checking if gcc static flag -static works... no checking if gcc supports -c -o file.o... yes checking if gcc supports -c -o file.o... (cached) yes checking whether the gcc linker (/usr/ccs/bin/ld) supports shared libraries... yes checking whether -lc should be explicitly linked in... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... no checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes checking for gcc... (cached) gcc checking whether we are using the GNU C compiler... (cached) yes checking whether gcc accepts -g... (cached) yes checking for gcc option to accept ISO C89... (cached) none needed checking how to run the C preprocessor... gcc -E checking for g++... g++ checking whether we are using the GNU C++ compiler... yes checking whether g++ accepts -g... yes checking whether we are using the GNU C++ compiler... (cached) yes checking whether g++ accepts -g... (cached) yes checking how to run the C++ preprocessor... g++ -E checking for ld used by g++... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking whether the g++ linker (/usr/ccs/bin/ld) supports shared libraries... yes checking for g++ option to produce PIC... -fPIC -DPIC checking if g++ PIC flag -fPIC -DPIC works... yes checking if g++ static flag -static works... no checking if g++ supports -c -o file.o... yes checking if g++ supports -c -o file.o... (cached) yes checking whether the g++ linker (/usr/ccs/bin/ld) supports shared libraries... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate
Re: [GENERAL] Additional Grants To SuperUser?
On Fri, Feb 4, 2011 at 5:08 PM, Dmitriy Igrishin dmit...@gmail.com wrote: These all (SUPERUSER, CREATEDB, SUPERUSER) are role attributes. By performing ALTER ROLE postgres NOSUPERUSER it is possible to turn role with a superuser status into a role that just can create databases and manage roles (admin, but without superuser privileges). So is it very bad to alter ANY of the default role attributes granted to the 'postgres' user? I don't know if removing role attributes from him will have negative consequences to features / functional tasks of the PostgreSQL server / client application(s). -- 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] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests
Please pardon my ignorance. The reason I am worried about it is, when I tried to use pg_upgrade to migrate from 8.3 to 9.0.2. I am getting an error Failed to load library: $libdir/liblwgeom ERROR: could not access file $libdir/liblwgeom: No such file or directory I am using pg_upgrade for upgrading Regards On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey pram...@cleverelephant.cawrote: It's not a dynlib, it's statically linked at build time, so have no fear. Stop thinking so much :) P On 2011-02-07, at 9:38 AM, akp geek wrote: installation was successful. But it did not install the liblwgeom.so Regards On Mon, Feb 7, 2011 at 12:29 PM, Paul Ramsey pram...@cleverelephant.cawrote: It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. *configure: WARNING: could not locate CUnit required for liblwgeom unit tests* is there some setting I need to do to make it work? $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... ^C10.112.161.124$ $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/5bin/sed checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for fgrep... /usr/sfw/bin/ggrep -F checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for BSD- or MS-compatible name lister (nm)... /usr/ccs/bin/nm -p checking the name lister (/usr/ccs/bin/nm -p) interface... BSD nm checking whether ln -s works... yes checking the maximum length of command line arguments... 786240 checking whether the shell understands some XSI constructs... yes checking whether the shell understands +=... no checking for /usr/ccs/bin/ld option to reload object files... -r checking for objdump... no checking how to recognize dependent libraries... pass_all checking for ar... ar checking for strip... strip checking for ranlib... ranlib checking command to parse /usr/ccs/bin/nm -p output from gcc object... ok checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking for dlfcn.h... yes checking for objdir... .libs checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... -fPIC -DPIC checking if gcc PIC flag -fPIC -DPIC works... yes checking if gcc static flag -static works... no checking if gcc supports -c -o file.o... yes checking if gcc supports -c -o file.o... (cached) yes checking whether the gcc linker (/usr/ccs/bin/ld) supports shared libraries... yes checking whether -lc should be explicitly linked in... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... no checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes checking for gcc... (cached) gcc checking whether we are using the GNU C compiler... (cached) yes checking whether gcc accepts -g... (cached) yes checking for gcc option to accept ISO C89... (cached) none needed checking how to run the C preprocessor... gcc -E checking for g++... g++ checking whether we are using the GNU C++ compiler... yes checking whether g++ accepts -g... yes checking whether we are using the GNU C++ compiler... (cached) yes checking whether g++ accepts -g... (cached) yes checking how to run the C++ preprocessor... g++ -E checking for ld used by g++... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking
Re: [GENERAL] Directing Partitioned Table Searches
Vick Khera wrote: Your best bet is to know which partition you need and write your query that way dynamically, rather than trying to use a generic query and have the DB do the constraint exclusion. In your above case, if you know that 'zone' will limit you to just the MI table, then specify the MI table instead of the base clu table. Thanks. That sounds simple enough. Since I want to automate this, I guess the next step is to learn how to create and execute a dynamic query. I think I know how to do that. -- *Bill Thoen* GISnet - www.gisnet.com http://www.gisnet.com/ 1401 Walnut St., Suite C Boulder, CO 80302 303-786-9961 tel 303-443-4856 fax bth...@gisnet.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] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests
Well, maybe you could in-place upgrade if you left your PostGIS version at the original and only upgraded the PostgreSQL part, but you aren't doing that, you're also upgrading your PostGIS version. pg_dump the database create a new database on the new server, install postgis in it pg_restore the database ignore the many errors P On 2011-02-07, at 9:49 AM, akp geek wrote: Please pardon my ignorance. The reason I am worried about it is, when I tried to use pg_upgrade to migrate from 8.3 to 9.0.2. I am getting an error Failed to load library: $libdir/liblwgeom ERROR: could not access file $libdir/liblwgeom: No such file or directory I am using pg_upgrade for upgrading Regards On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey pram...@cleverelephant.ca wrote: It's not a dynlib, it's statically linked at build time, so have no fear. Stop thinking so much :) P On 2011-02-07, at 9:38 AM, akp geek wrote: installation was successful. But it did not install the liblwgeom.so Regards On Mon, Feb 7, 2011 at 12:29 PM, Paul Ramsey pram...@cleverelephant.ca wrote: It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. configure: WARNING: could not locate CUnit required for liblwgeom unit tests is there some setting I need to do to make it work? $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... ^C10.112.161.124$ $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/5bin/sed checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for fgrep... /usr/sfw/bin/ggrep -F checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for BSD- or MS-compatible name lister (nm)... /usr/ccs/bin/nm -p checking the name lister (/usr/ccs/bin/nm -p) interface... BSD nm checking whether ln -s works... yes checking the maximum length of command line arguments... 786240 checking whether the shell understands some XSI constructs... yes checking whether the shell understands +=... no checking for /usr/ccs/bin/ld option to reload object files... -r checking for objdump... no checking how to recognize dependent libraries... pass_all checking for ar... ar checking for strip... strip checking for ranlib... ranlib checking command to parse /usr/ccs/bin/nm -p output from gcc object... ok checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking for dlfcn.h... yes checking for objdir... .libs checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... -fPIC -DPIC checking if gcc PIC flag -fPIC -DPIC works... yes checking if gcc static flag -static works... no checking if gcc supports -c -o file.o... yes checking if gcc supports -c -o file.o... (cached) yes checking whether the gcc linker (/usr/ccs/bin/ld) supports shared libraries... yes checking whether -lc should be explicitly linked in... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... no checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes checking for gcc... (cached) gcc checking whether we are using the GNU C compiler... (cached) yes checking whether gcc accepts -g... (cached) yes checking for gcc option to accept ISO C89... (cached) none needed checking how to run the C preprocessor...
Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests
I'm not sure you can in-place upgrade a postgis database... On 2011-02-07, at 9:49 AM, akp geek wrote: Please pardon my ignorance. The reason I am worried about it is, when I tried to use pg_upgrade to migrate from 8.3 to 9.0.2. I am getting an error Failed to load library: $libdir/liblwgeom ERROR: could not access file $libdir/liblwgeom: No such file or directory I am using pg_upgrade for upgrading Regards On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey pram...@cleverelephant.ca wrote: It's not a dynlib, it's statically linked at build time, so have no fear. Stop thinking so much :) P On 2011-02-07, at 9:38 AM, akp geek wrote: installation was successful. But it did not install the liblwgeom.so Regards On Mon, Feb 7, 2011 at 12:29 PM, Paul Ramsey pram...@cleverelephant.ca wrote: It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. configure: WARNING: could not locate CUnit required for liblwgeom unit tests is there some setting I need to do to make it work? $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... ^C10.112.161.124$ $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/5bin/sed checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for fgrep... /usr/sfw/bin/ggrep -F checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for BSD- or MS-compatible name lister (nm)... /usr/ccs/bin/nm -p checking the name lister (/usr/ccs/bin/nm -p) interface... BSD nm checking whether ln -s works... yes checking the maximum length of command line arguments... 786240 checking whether the shell understands some XSI constructs... yes checking whether the shell understands +=... no checking for /usr/ccs/bin/ld option to reload object files... -r checking for objdump... no checking how to recognize dependent libraries... pass_all checking for ar... ar checking for strip... strip checking for ranlib... ranlib checking command to parse /usr/ccs/bin/nm -p output from gcc object... ok checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking for dlfcn.h... yes checking for objdir... .libs checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... -fPIC -DPIC checking if gcc PIC flag -fPIC -DPIC works... yes checking if gcc static flag -static works... no checking if gcc supports -c -o file.o... yes checking if gcc supports -c -o file.o... (cached) yes checking whether the gcc linker (/usr/ccs/bin/ld) supports shared libraries... yes checking whether -lc should be explicitly linked in... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... no checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes checking for gcc... (cached) gcc checking whether we are using the GNU C compiler... (cached) yes checking whether gcc accepts -g... (cached) yes checking for gcc option to accept ISO C89... (cached) none needed checking how to run the C preprocessor... gcc -E checking for g++... g++ checking whether we are using the GNU C++ compiler... yes checking whether g++ accepts -g... yes checking whether we are using the GNU C++ compiler... (cached) yes checking whether g++ accepts -g... (cached) yes checking how to run the C++
[GENERAL] Maintenance commands on standby servers
Hi, Is it possible to run maintenance commands like ANALYZE, VACUUM, CLUSTER on a standby server ? The documentations says that those operations can not be done in recovery mode, but that some of these commands are actually allowed during read only mode transactions on the primary [1]. [1] http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-ADMIN Regards. -- Sylvain Rabot sylv...@abstraction.fr signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Directing Partitioned Table Searches
On Mon, Feb 7, 2011 at 1:17 PM, Bill Thoen bth...@gisnet.com wrote: Thanks. That sounds simple enough. Since I want to automate this, I guess the next step is to learn how to create and execute a dynamic query. I think I know how to do that. In perl, it looks something like this: $part = compute_partition($value); $sth = $dbh-prepare(select * from table_$part where foo=?); $sth-execute($value); you just interpolate the parts you need in the query string. -- 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] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests
On 07/02/11 18.55, Paul Ramsey wrote: Well, maybe you could in-place upgrade if you left your PostGIS version at the original and only upgraded the PostgreSQL part, but you aren't doing that, you're also upgrading your PostGIS version. pg_dump the database create a new database on the new server, install postgis in it pg_restore the database I am at the beginning with postigis. This is also my way to copy the data from one machine to another. I have the same server in both the machines (postgresql 8.4 + postgis 1.5), no errors during this process. ignore the many errors this sentence scares me a bit (for my future)... what kind of errors? Edoardo On 2011-02-07, at 9:49 AM, akp geek wrote: Please pardon my ignorance. The reason I am worried about it is, when I tried to use pg_upgrade to migrate from 8.3 to 9.0.2. I am getting an error Failed to load library: $libdir/liblwgeom ERROR: could not access file $libdir/liblwgeom: No such file or directory I am using pg_upgrade for upgrading Regards On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey pram...@cleverelephant.ca mailto:pram...@cleverelephant.ca wrote: It's not a dynlib, it's statically linked at build time, so have no fear. Stop thinking so much :) P On 2011-02-07, at 9:38 AM, akp geek wrote: installation was successful. But it did not install the liblwgeom.so Regards On Mon, Feb 7, 2011 at 12:29 PM, Paul Ramsey pram...@cleverelephant.ca mailto:pram...@cleverelephant.ca wrote: It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. *configure: WARNING: could not locate CUnit required for liblwgeom unit tests* is there some setting I need to do to make it work? $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... ^C10.112.161.124$ $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/5bin/sed checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for fgrep... /usr/sfw/bin/ggrep -F checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for BSD- or MS-compatible name lister (nm)... /usr/ccs/bin/nm -p checking the name lister (/usr/ccs/bin/nm -p) interface... BSD nm checking whether ln -s works... yes checking the maximum length of command line arguments... 786240 checking whether the shell understands some XSI constructs... yes checking whether the shell understands +=... no checking for /usr/ccs/bin/ld option to reload object files... -r checking for objdump... no checking how to recognize dependent libraries... pass_all checking for ar... ar checking for strip... strip checking for ranlib... ranlib checking command to parse /usr/ccs/bin/nm -p output from gcc object... ok checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking for dlfcn.h... yes checking for objdir... .libs checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... -fPIC -DPIC checking if gcc PIC flag -fPIC -DPIC works... yes checking if gcc
Re: [GENERAL] Very slow moving between partition
Le lundi 07 février 2011 à 02:42 -0600, Mario Medina a écrit : That works good with small number of records, but if I have 300,000 records it can take a lot of time to move that records. well, that's a lot of records to move. I would guess you are I/O bound with your present disks, and faster ones are needed. Some study material here : http://wiki.postgresql.org/wiki/Performance_Optimization -- Vincent Veyron http://marica.fr/ Logiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgEast: 2011, CFP closes in three days
Hello hackers, Just FYI, the CFP for PgEast in NYC closes in three days. https://www.postgresqlconference.org/talk_types Sincerely, JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] fulltext search and hunspell
Jens,
could you check affix file for
compoundwords controlled z
also, can you provide link to dictionary files, so we can check if they
supported, since we have only rudiment support of hunspell.
btw,it'd be nice to have output from ts_debug() to make sure dictionaries
actually used.
Oleg
On Mon, 7 Feb 2011, Jens Sauer wrote:
Hey,
I want to use hunspell as a dictionary for the full text search by
* using PostgresSQL 8.4.7
* installing hunspell-de-de, hunspell-de-med
* creating a dictionary:
CREATE TEXT SEARCH DICTIONARY german_hunspell (
TEMPLATE = ispell,
DictFile = de_de,
AffFile = de_de,
StopWords = german
);
* changing the config
ALTER TEXT SEARCH CONFIGURATION german
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
word, hword, hword_part
WITH german_hunspell, german_stem;
* now testing the lexizer:
SELECT ts_lexize('german_hunspell', 'Schokaladenfarik');
ts_lexize
---
(1 Zeile)
Shouldn't it be something like this:
SELECT ts_lexize('norwegian_ispell', 'sjokoladefabrikk');
{sjokoladefabrikk,sjokolade,fabrikk}
(from the 8.4 documentation of PostgreSQL)
The dict and affix files in the tsearch_data directory were
automatically generated by pg_updatedicts.
Is this a problem of the splitting compound word functionality? Should
I use ispell instead of hunspell?
Thanks
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELECT INTO array[i] with PL/pgSQL
Dear PostgreSQL community,
Please consider the following minimal example:
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO avalue FROM example WHERE row_id=i; -- This works
b[i] := a; -- perfectly!
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
The error message indicates a problem with selecting values into an array.
I have read the documentation carefully and have done extensive web
search, but a more verbose error message and some additional explanation
would help me to understand the problem.
Is there a way to select values directly into an array without the
assignment from an additional variable?
Regards,
Julia
--
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] SELECT INTO array[i] with PL/pgSQL
On 07/02/11 22.15, Julia Jacobson wrote:
Dear PostgreSQL community,
Please consider the following minimal example:
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO a value FROM example WHERE row_id=i; -- This works
b[i] := a; -- perfectly!
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
this one seems work...
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
b[i]:= value FROM example WHERE row_id=i;
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
Edoardo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to improve this query?
Hello, update TABLE_A set INT_FIELD = TABLE_B.INT_FIELD from TABLE_C, TABLE_B where TABLE_B.STR_FIELD = TABLE_C.STR_FIELD and TABLE_A.INT_FIELD2 = TABLE_B.min and TABLE_A.INT_FIELD2 = TABLE_B.max and st_intersects(TABLE_A.wkb_geometry, TABLE_C.the_geom); Tables description: TABLE A CREATE TABLE TABLE_A ( ogc_fid serial NOT NULL, wkb_geometry geometry, INT_FIELD2 integer, INT_FIELD integer NOT NULL DEFAULT 0, CONSTRAINT TABLE_A_pk PRIMARY KEY (ogc_fid), CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2), CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 23030) ) WITH ( OIDS=FALSE ); CREATE INDEX TABLE_A_geom_idx ON TABLE_A USING gist (wkb_geometry); CREATE INDEX TABLE_A_INT_FIELD2 ON TABLE_A USING btree (INT_FIELD2); +++ TABLE B CREATE TABLE TABLE_B ( STR_FIELD character(50) DEFAULT NULL::bpchar, min integer NOT NULL DEFAULT 0, max integer NOT NULL DEFAULT 0, INT_FIELD integer NOT NULL DEFAULT 0, oid integer NOT NULL DEFAULT 0, CONSTRAINT TABLE_B_pk PRIMARY KEY (oid) ) WITH ( OIDS=FALSE ); CREATE INDEX TABLE_B_idx ON TABLE_B USING btree (STR_FIELD); CREATE INDEX TABLE_B_max_idx ON TABLE_B USING btree (max); CREATE INDEX TABLE_B_min_idx ON TABLE_B USING btree (min); +++ TABLE C CREATE TABLE TABLE_C ( the_geom geometry, STR_FIELD character(50) ) WITH ( OIDS=FALSE ); CREATE INDEX TABLE_C_index ON TABLE_C USING gist (the_geom); CREATE INDEX TABLE_C_string_idx ON TABLE_C USING btree (STR_FIELD); +++ Tables data: - TABLE_A: 896888 entries. The geometries are single polygons (squares, actually), coordinates are floating point numbers - TABLE_B: 88 entries. - TABLE C: 69352 entries. Geometries are single polygons too, but much bigger than the polygons from TABLE_A. As you can see in the query, I'm interested in the polygons of TABLE_A that intersects the big polygons in TABLE_C. Query plan (explain query output): Hash Join (cost=3.98..986808.75 rows=209049 width=497) Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD) Join Filter: ((TABLE_A.INT_FIELD2 = TABLE_B.min) AND (TABLE_A.INT_FIELD2 = TABLE_B.max)) - Nested Loop (cost=0.00..955055.47 rows=470360 width=543) Join Filter: _st_intersects(TABLE_A.wkb_geometry, TABLE_C.the_geom) - Index Scan using TABLE_C_string_idx on TABLE_C (cost=0.00..8044.56 rows=69352 width=1517) - Index Scan using TABLE_A_geom_idx on TABLE_A (cost=0.00..12.61 rows=4 width=493) Index Cond: (TABLE_A.wkb_geometry TABLE_C.the_geom) - Hash (cost=2.88..2.88 rows=88 width=63) - Seq Scan on TABLE_B (cost=0.00..2.88 rows=88 width=63) With that information, how could I make the update faster? It takes more than 24hours to finish. Many thanks in advance Best regards, -- Jorge Arévalo Internet Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://es.linkedin.com/in/jorgearevalo80 http://mobility.grupodeimos.com/ http://www.twitter.com/jorgeas80 http://gis4free.wordpress.com http://geohash.org/ezjqgrgzz0g -- 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] fulltext search and hunspell
Hey,
thanks for your answer.
First I checked the links in the tsearch_data directory
de_de.affix, and de_de.dict are symlinks to the corresponding files in
/var/cache/postgresql/dicts/
Then I recreated them by using pg_updatedicts.
This is an extract of the de_de.affix file:
# this is the affix file of the de_DE Hunspell dictionary
# derived from the igerman98 dictionary
#
# Version: 20091006 (build 20100127)
#
# Copyright (C) 1998-2009 Bjoern Jacke bjo...@j3e.de
#
# License: GPLv2, GPLv3 or OASIS distribution license agreement
# There should be a copy of both of this licenses included
# with every distribution of this dictionary. Modified
# versions using the GPL may only include the GPL
SET ISO8859-1
TRY esijanrtolcdugmphbyfvkwqxzäüößáéêàâñESIJANRTOLCDUGMPHBYFVKWQXZÄÜÖÉ-.
PFX U Y 1
PFX U 0 un .
PFX V Y 1
PFX V 0 ver .
SFX F Y 35
[...]
I cannot find compoundwords controlled z there, so I manually added it.
[...]
# versions using the GPL may only include the GPL
compoundwords controlled z
SET ISO8859-1
TRY esijanrtolcdugmphbyfvkwqxzäüößáéêàâñESIJANRTOLCDUGMPHBYFVKWQXZÄÜÖÉ-.
[...]
Then I restarted PostgreSQL.
Now I get an error:
SELECT * FROM ts_debug('Schokoladenfabrik');
FEHLER: falsches Affixdateiformat für Flag
CONTEXT: Zeile 18 in Konfigurationsdatei
»/usr/share/postgresql/8.4/tsearch_data/de_de.affix«: »PFX U Y 1
«
SQL-Funktion »ts_debug« Anweisung 1
SQL-Funktion »ts_debug« Anweisung 1
Which means:
ERROR: wrong Affixfileformat for flag
CONTEXT: Line 18 in Configuration ...
If I add
COMPOUNDFLAG Z
ONLYINCOMPOUND L
instead of compoundwords controlled z
I didn't get an error:
SELECT * FROM ts_debug('Schokoladenfabrik');
alias | description | token |
dictionaries | dictionary | lexemes
---+-+---+---+-+---
asciiword | Word, all ASCII | Schokoladenfabrik |
{german_hunspell,german_stem} | german_stem | {schokoladenfabr}
(1 row)
But it seems that the hunspell dictionary is not working for compound words.
Maybe pg_updatedicts has a bug and generates affix files in the wrong format?
Jens
2011/2/7 Oleg Bartunov o...@sai.msu.su:
Jens,
could you check affix file for
compoundwords controlled z
also, can you provide link to dictionary files, so we can check if they
supported, since we have only rudiment support of hunspell.
btw,it'd be nice to have output from ts_debug() to make sure dictionaries
actually used.
Oleg
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] reindexing
Hi all - I ran query this morning, I got a wrong results. I have run the same query in an other environment with same data and I got the result set I was expecting. After that I did a re index and on the table I was getting incorrect results, the data then came out fine, Do I have to reindex periodically to make sure the data retrieval would be correct? Thanks for you help Regards
Re: [GENERAL] reindexing
On Mon, Feb 7, 2011 at 17:12, akp geek akpg...@gmail.com wrote: Hi all - I ran query this morning, I got a wrong results. I have run the same query in an other environment with same data and I got the result set I was expecting. After that I did a re index and on the table I was getting incorrect results, the data then came out fine, Do I have to reindex periodically to make sure the data retrieval would be correct? In general, no. That would be silly. However, if you are using hash indexes, per the fine manual (http://www.postgresql.org/docs/9.0/interactive/indexes-types.html): Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. They are also not replicated over streaming or file-based replication. For these reasons, hash index use is presently discouraged. REINDEX will also 'fix' a btree index if it somehow got corrupted. Depending on the type of corruption, I would expect postgres to complain (or segfault) in most cases instead of returning the wrong results. Anything interesting in your server logs? Also you failed to note what version of postgres you are using-- its hard to tell if you are hitting a known bug or not. -- 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] reindexing
thanks.. the index I was having is gist on a to_tsvector column . version we have is 8.3 On Mon, Feb 7, 2011 at 7:23 PM, Alex Hunsaker bada...@gmail.com wrote: On Mon, Feb 7, 2011 at 17:12, akp geek akpg...@gmail.com wrote: Hi all - I ran query this morning, I got a wrong results. I have run the same query in an other environment with same data and I got the result set I was expecting. After that I did a re index and on the table I was getting incorrect results, the data then came out fine, Do I have to reindex periodically to make sure the data retrieval would be correct? In general, no. That would be silly. However, if you are using hash indexes, per the fine manual (http://www.postgresql.org/docs/9.0/interactive/indexes-types.html): Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. They are also not replicated over streaming or file-based replication. For these reasons, hash index use is presently discouraged. REINDEX will also 'fix' a btree index if it somehow got corrupted. Depending on the type of corruption, I would expect postgres to complain (or segfault) in most cases instead of returning the wrong results. Anything interesting in your server logs? Also you failed to note what version of postgres you are using-- its hard to tell if you are hitting a known bug or not.
Re: [GENERAL] reindexing
On Mon, Feb 7, 2011 at 17:28, akp geek akpg...@gmail.com wrote: thanks.. the index I was having is gist on a to_tsvector column . version we have is 8.3 What minor version? I sounds like you _could_ be hitting any of the below: - (8.3.14) Fix detection of page splits in temporary GiST indexes (Heikki Linnakangas) - (8.3.10) Fix incorrect WAL data emitted during end-of-recovery cleanup of a GIST index page split - (8.3.9) Fix incorrect logic for GiST index page splits, when the split depends on a non-first column of the index (Paul Ramsey) - (8.3.6) Fix whole-index GiST scans to work correctly (Teodor) - (8.3.5) Fix GiST index corruption due to marking the wrong index entry dead after a deletion (Teodor) ... I got bored of doing your homework after this point. :-( -- 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] tuning postgresql writes to disk
Thank you. I will try to run some performance tests using the async commit option. Is there an easy way to find the lost transactions or does it have to be handled by the application? On Mon, Feb 7, 2011 at 6:23 AM, Vick Khera vi...@khera.org wrote: On Thu, Feb 3, 2011 at 7:08 PM, Vinubalaji Gopal vinubal...@gmail.com wrote: already does this. I looked at the WAL parameters and the new async commit but not sure if I am looking at the right place. Say i have 10 clients connecting and each client is inserting a record. I want to You want the async commit. If you can detect and re-execute lost transactions, it gives you the best of everything: defer disk I/O and transaction boundaries are honored so you never have inconsistent data after crash recovery. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Vinu In a world without fences who needs Gates? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] index for ilike operation
I found that in Postresql 9.0.3 documentation: *It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion. *Can anyone please tell me how to configure that?* *
Re: [GENERAL] index for ilike operation
On 02/07/11 9:07 PM, AI Rumman wrote: I found that in Postresql 9.0.3 documentation: /It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion. /Can anyone please tell me how to configure that?/ / hmm? nothing to configure. its a special case thats really not very useful. WHERE something ILIKE '432432$#@$#%' would use a index on something, since there's no characters in there subject to case shifting. ditto... WHERE something ~* '^42432$@#$#@' (where ^ anchors the string to match starting from the first char) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
