Re: [GENERAL] Need magic for identifieing double adresses

Andreas, > Relevant fields could be  name, street, zip, city, phone > Is there a way to do something like this with postgresql ? > I fear this will need still a lot of manual sorting and searching even when > potential peers get automatically identified. One of the techniques I use to increase th

Re: [GENERAL] Need magic for identifieing double adresses

Andreas wrote: I need to clean up a lot of contact data because of a merge of customer lists that used to be kept separate. I allready know that there are double entries within the lists and they do overlap, too. Relevant fields could be name, street, zip, city, phone Is there a way to do so

[GENERAL] Need magic for identifieing double adresses

Hi, I need to clean up a lot of contact data because of a merge of customer lists that used to be kept separate. I allready know that there are double entries within the lists and they do overlap, too. Relevant fields could be name, street, zip, city, phone Is there a way to do something l

[GENERAL] Re: Getting FATAL: terminating connection due to administrator command

Hi - peter.hopfgartner wrote: > >http://sourceware.org/systemtap/examples/process/sigmon.stp > As it seems I need to recompile the kernel in order to have the debug > infos, http://sourceware.org/systemtap/wiki/SystemTapOnCentOS. > Tomorrow I will start the probes. Check first on http://debugin

Re: [GENERAL] Post Install / Secure PostgreSQL

On 16/09/2010 2:10 AM, Carlos Mennens wrote: postgres=# SELECT * FROM pg_user; Doesn't that show I'm connected to the 'postgres' database and there is a table called 'pg_user' which holds all my PostgreSQL user info? That doesn't make sense to me if the database is empty unless I am missing s

Re: [GENERAL] help with error "unexpected pageaddr"

Shouldn't have, the only thing we did to the server was restart it and run our database queries. Clearing out all the wal files from pg_xlog along with a new base backup did fix it though. Thanks for the help Tom! Scot Kreienkamp skre...@la-z-boy.com -- Sent via pgsql-general mailing list (pg

Re: [GENERAL] Using libpq, floats and binary data

On Thu, Sep 16, 2010 at 12:37 AM, Merlin Moncure wrote: > On Wed, Sep 15, 2010 at 6:18 PM, Arjen Nienhuis > wrote: > > Hi, > > Inserting many of rows is almost always IO bound. Converting ints and > floats > > to text is CPU bound and really fast anyway. To speed things up first > look > > at th

Re: [GENERAL] Using libpq, floats and binary data

On Wed, Sep 15, 2010 at 6:18 PM, Arjen Nienhuis wrote: > Hi, > Inserting many of rows is almost always IO bound. Converting ints and floats > to text is CPU bound and really fast anyway. To speed things up first look > at things like indexes, how often you need to COMMIT or using COPY. Only > then

Re: [GENERAL] Using libpq, floats and binary data

Hi, Inserting many of rows is almost always IO bound. Converting ints and floats to text is CPU bound and really fast anyway. To speed things up first look at things like indexes, how often you need to COMMIT or using COPY. Only then look at prepared statements and binary transfer modes. Else it's

Re: [GENERAL] searchpath reverts to default after each server restart

alter database "MYDATABASE" SET search_path TO custom, clientdata, configs, replication, structure, archive; that seems to fix it. Thank you! -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Wednesday, September 15, 2010 4:40 PM To: Jonathan Brinkman Cc: pgsql-ge

Re: [GENERAL] searchpath reverts to default after each server restart

On Wed, Sep 15, 2010 at 4:12 PM, Jonathan Brinkman wrote: > When i run the command > SET search_path TO custom,idsystems, clientdata, configs, replication, > structure, archive; > then when I run > SHOW search_path; > it does show those schemas as the search_path. however when i restart the > post

[GENERAL] searchpath reverts to default after each server restart

When i run the command SET search_path TO custom,idsystems, clientdata, configs, replication, structure, archive; then when I run SHOW search_path; it does show those schemas as the search_path. however when i restart the postgresql service, the search_path has reverted to $User, public. when i p

Re: [GENERAL] Getting FATAL: terminating connection due to administrator command

Tom Lane wrote Subject: Re: [GENERAL] Getting FATAL: terminating connection due to administrator command Date: 15.09.2010 17:40 >Peter Hopfgartner writes: >> Tom Lane wrote >>> This is a result of something sending SIGTERM to the backend process. > >> Can I tra

Re: [GENERAL] Data Model Advice

On Wed, Sep 15, 2010 at 12:04 PM, Gary Chambers wrote: > All, > > My data modeling and SQL are oftentimes woefully inadequate, and I am > seeking advice on how to implement a particular database design.  My > database (so far) is comprised of the following types of tables: > > The parts table cont

[GENERAL] Data Model Advice

Subject: Data Model Advice All, My data modeling and SQL are oftentimes woefully inadequate, and I am seeking advice on how to implement a particular database design. My database (so far) is comprised of the following types of tables: The parts table contains parts of a particular type primary

Re: [GENERAL] help with error "unexpected pageaddr"

"Scot Kreienkamp" writes: > We have a PG 8.3.7 server that is doing WAL log shipping to 2 other > servers that are remote mirrors. This has been working well for almost > two years. Last night we did some massive data and structure changes to > one of our databases. Since then I get these erro

Re: [GENERAL] Getting FATAL: terminating connection due to administrator command

"Joshua J. Kugler" writes: > On Wednesday 15 September 2010, Peter Hopfgartner elucidated thus: >> The server is a rather bare bone server for web mapping, so basically >> PostgreSQL/PostGIS, Apache, PHP, Tomcat and little other stuff. The >> Dell software was the only which did not come from >> C

Re: [GENERAL] help with error "unexpected pageaddr"

"Scot Kreienkamp" writes: > I tried to take a new base backup about 45 minutes ago. The master has > rolled forward a number of WAL files since I last tried, but it still > fails. > LOG: restored log file "0001030100FE" from archive > LOG: restored log file "000103020

Re: [GENERAL] Getting FATAL: terminating connection due to administrator command

On Wednesday 15 September 2010, Peter Hopfgartner elucidated thus: > The server is a rather bare bone server for web mapping, so basically > PostgreSQL/PostGIS, Apache, PHP, Tomcat and little other stuff. The > Dell software was the only which did not come from > CentOS/EPEL/argeo/in-house RPM pack

Re: [GENERAL] Using libpq, floats and binary data

zz On Wed, Sep 15, 2010 at 12:02 PM, Michael Hull wrote: > Hi Guys, > I have jsut started to use the libpq interface to postgre, and was > wondering if someone with some experience could explain the 'best > practices' :) > > I am preparing a query, then submitting it with binary data. 6 values >

Re: [GENERAL] Post Install / Secure PostgreSQL

Alan Hodgson writes: > On September 15, 2010 11:10:45 am Carlos Mennens wrote: >> Doesn't that show I'm connected to the 'postgres' database and there >> is a table called 'pg_user' which holds all my PostgreSQL user info? >> That doesn't make sense to me if the database is empty unless I am >> mi

Re: [GENERAL] Post Install / Secure PostgreSQL

On 09/15/10 11:10 AM, Carlos Mennens wrote: On Wed, Sep 15, 2010 at 1:43 PM, John R Pierce wrote: the 'postgres' database on your system is empty. this is quite typical, as that database is simply a convenience for the postgres user to have something to log into while doing his administrati

Re: [GENERAL] Post Install / Secure PostgreSQL

On September 15, 2010 11:10:45 am Carlos Mennens wrote: > Doesn't that show I'm connected to the 'postgres' database and there > is a table called 'pg_user' which holds all my PostgreSQL user info? > That doesn't make sense to me if the database is empty unless I am > missing something here. The on

Re: [GENERAL] Post Install / Secure PostgreSQL

On Wed, Sep 15, 2010 at 1:43 PM, John R Pierce wrote: > the 'postgres' database on your system is empty.   this is quite typical, as > that database is simply a convenience for the postgres user to have > something to log into while doing his administrative duties. OK this makes sense and I coul

Re: [GENERAL] Post Install / Secure PostgreSQL

John R Pierce writes: > On 09/15/10 10:00 AM, Carlos Mennens wrote: >> But when if 'cmennens' wants to login to PostgreSQL but not connect to >> any specific database? I know in MySQL you can login to the MySQL >> server CLI but not be attached to any specific database if you want to >> just pee

Re: [GENERAL] Post Install / Secure PostgreSQL

On Wed, Sep 15, 2010 at 10:36 AM, Carlos Mennens wrote: > postgres=# \d > No relations found. > postgres=# \dt > No relations found. > postgres=# \c postgres > psql (8.4.4) > You are now connected to database "postgres". > postgres=# \dt > No relations found. > postgres=# \d try: select current

Re: [GENERAL] Post Install / Secure PostgreSQL

On Wed, Sep 15, 2010 at 1:36 PM, Carlos Mennens wrote: > Thanks but then I am confused why I am getting the following: > You initially connected to the postgres database because you were logging in as the postgres user... > > postgres=# \d > No relations found. > postgres=# \dt > No relations f

Re: [GENERAL] Post Install / Secure PostgreSQL

On 09/15/10 10:36 AM, Carlos Mennens wrote: On Wed, Sep 15, 2010 at 1:34 PM, John R Pierce wrote: no, there is no such state in postgres. you connect and log into a database. connecting to a different database requires closing that connection and opening a new one (which is what the \c comma

Re: [GENERAL] Post Install / Secure PostgreSQL

On Wed, Sep 15, 2010 at 1:34 PM, John R Pierce wrote: > no, there is no such state in postgres.  you connect and log into a > database.  connecting to a different database requires closing that > connection and opening a new one (which is what the \c command does in > psql). Thanks but then I am

Re: [GENERAL] Post Install / Secure PostgreSQL

On 09/15/10 10:00 AM, Carlos Mennens wrote: But when if 'cmennens' wants to login to PostgreSQL but not connect to any specific database? I know in MySQL you can login to the MySQL server CLI but not be attached to any specific database if you want to just peek around and do basic administrative

Re: [GENERAL] Post Install / Secure PostgreSQL

Thanks all for the help! I have a much better understanding now of how user accounts are managed via ident authentication. The only thing I have yet to figure out or understand is how to login to PostgreSQL as my user account but not to any specific database. I understand that when I am logged in

[GENERAL] Using libpq, floats and binary data

Hi Guys, I have jsut started to use the libpq interface to postgre, and was wondering if someone with some experience could explain the 'best practices' :) I am preparing a query, then submitting it with binary data. 6 values are ints, and one is a float. For the ints, I understand that since I n

Re: [GENERAL] help with error "unexpected pageaddr"

"Scot Kreienkamp" writes: > We have a PG 8.3.7 server that is doing WAL log shipping to 2 other > servers that are remote mirrors. This has been working well for almost > two years. Last night we did some massive data and structure changes to > one of our databases. Since then I get these error

[GENERAL] help with error "unexpected pageaddr"

Hey everyone, We have a PG 8.3.7 server that is doing WAL log shipping to 2 other servers that are remote mirrors. This has been working well for almost two years. Last night we did some massive data and structure changes to one of our databases. Since then I get these errors on the two mirr

Re: [GENERAL] value

On Wed, Sep 15, 2010 at 03:16:55PM +, Gissur Þórhallsson wrote: > Hi there, > > I have a somewhat peculiar problem. > > To begin with, here are links to my schema and rules: my_table and > associated rules and > my_table_history

Re: [GENERAL] Table Comments

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

[GENERAL] Data Model Advice

All, My data modeling and SQL are oftentimes woefully inadequate, and I am seeking advice on how to implement a particular database design. My database (so far) is comprised of the following types of tables: The parts table contains parts of a particular type primary key is an alphanumeric part

Re: [GENERAL] Table Comments

On 09/15/2010 08:53 AM, Carlos Mennens wrote: I saw in the documentation for PostgreSQL that I can add 'comments' to table entries when creating columns: http://www.postgresql.org/docs/8.1/static/tutorial-table.html CREATE TABLE weather ( cityvarchar(80), temp_lo i

Re: [GENERAL] Table Comments

On Wed, 15 Sep 2010 08:53:16 -0700, Carlos Mennens wrote: CREATE TABLE weather ( temp_lo int, -- low temperature ); I did a search and don't understand in what aspect are the 'comments' relevant / visible? I don't see the comments when I attempt to list / describe the t

Re: [GENERAL] Table Comments

In response to Carlos Mennens : > I saw in the documentation for PostgreSQL that I can add 'comments' to > table entries when creating columns: > > http://www.postgresql.org/docs/8.1/static/tutorial-table.html > > CREATE TABLE weather ( > cityvarchar(80), > temp_lo in

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

On Tue, Sep 14, 2010 at 6:55 PM, Tatsuo Ishii wrote: > Sorry for not enough description about pool_passwd. It's located under > the same directory as pgpool.conf. So the default is > /usr/local/etc/pool_passwd. > > You need to create /usr/local/etc/pool_passwd if the uid to run pgpool > server d

Re: [GENERAL] Search then Delete Performance

On Wed, Sep 15, 2010 at 5:15 AM, Dann Corbit wrote: >> -Original Message- >> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- >> ow...@postgresql.org] On Behalf Of John R Pierce >> Sent: Tuesday, September 14, 2010 8:41 PM >> To: Michael Hull >> Cc: pgsql-general@postgresql.

[GENERAL] Table Comments

I saw in the documentation for PostgreSQL that I can add 'comments' to table entries when creating columns: http://www.postgresql.org/docs/8.1/static/tutorial-table.html CREATE TABLE weather ( cityvarchar(80), temp_lo int, -- low temperature temp_hi

Re: [GENERAL] select sql slow inside function

On 09/15/2010 09:46 AM, Gary Fu wrote: On 09/15/2010 02:28 AM, Sergey Konoplev wrote: Hi, On 15 September 2010 01:56, Gary Fu wrote I have a function proc_TaskComplete that inserts a record to table TaskHistory and then calls another function proc_ExportTaskComplete, that will retrieve (select

Re: [GENERAL] Getting FATAL: terminating connection due to administrator command

Peter Hopfgartner writes: > Tom Lane wrote >> This is a result of something sending SIGTERM to the backend process. > Can I trace where the SIGTERM comes from? If this is a recent Red-Hat-based release, I think that systemtap could probably be used to determine that. There's a

Re: [GENERAL] Getting FATAL: terminating connection due to administrator command

Tom Lane wrote Subject: Re: [GENERAL] Getting FATAL: terminating connection due to administrator command Date: 15.09.2010 16:07 >Peter Hopfgartner writes: >> Since some days we are getting the above message. >> Also in the PostgreSQL logs we get: >> FATAL: terminating connecti

[GENERAL] value

Hi there, I have a somewhat peculiar problem. To begin with, here are links to my schema and rules: my_table and associated rules and my_table_history Scene: I'm implementing a pretty standard history keeping mec

[GENERAL] 8.3.8 question about backup/recovery behavior

We take nightly backups using the start backup, copying the data directory and archived logs, then stop backup method. Today I tested the recoverability of the backup by mounting this backup directory on a different server, copying the 3 hours of transactions logs from after last nights backup up

Re: [GENERAL] Getting FATAL: terminating connection due to administrator command

On 15/09/2010 10:07 PM, Tom Lane wrote: The server is from Dell, Dell's hardware monitoring, OpenManage, says that the hardware, in particular memory and disk, are ok. Never dealt with OpenManage before, but you should cast a wary eye upon any Dell-specific software on the machine. (A bit o

Re: [GENERAL] Replacing characters in a string

Sergey Konoplev writes: > Just specify 'g' as the flags parameter (the 4th one). It means 'globally'. > SELECT regexp_replace('xaxx', 'x', 'e', 'g'); > There are more of this flags described here: > http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TAB

Re: [GENERAL] select sql slow inside function

On 09/15/2010 02:28 AM, Sergey Konoplev wrote: Hi, On 15 September 2010 01:56, Gary Fu wrote I have a function proc_TaskComplete that inserts a record to table TaskHistory and then calls another function proc_ExportTaskComplete, that will retrieve (select) the record just inserted based on an

Re: [GENERAL] Getting FATAL: terminating connection due to administrator command

Peter Hopfgartner writes: > Since some days we are getting the above message. > Also in the PostgreSQL logs we get: > FATAL: terminating connection due to administrator command This is a result of something sending SIGTERM to the backend process. I have heard reports of "load management" softwa

Re: [GENERAL] Getting FATAL: terminating connection due to administrator command

On Wed, Sep 15, 2010 at 02:55:39PM +0200, Peter Hopfgartner wrote: > Where could I start to troubleshoot this problem. First with staff, then with unauthorized access, then with failover software. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 134

[GENERAL] Upload latest JDBC driver releases to Maven Central

Hi, I just subscribed to this list. Does anybody know, if the latest JDBC drivers (8.4-702, 8.3-606, 8.2-5.11) can be found on Maven Central soon? Currently I can only find 8.4-701, 8.3-603 and 8.2-507. BR Gerd Bremer

Re: [GENERAL] workaround steps for autovaccum problem

Thanks Scott for your reply ... -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Wednesday, September 15, 2010 7:36 AM To: tamanna madaan Cc: David Fetter; pgsql-general@postgresql.org Subject: Re: [GENERAL] workaround steps for autovaccum problem On Tue, Sep

Re: [GENERAL] Force ARE in regexp string

=?ISO-8859-1?Q?Johannes_=D6berg?= writes: > Hi! I'm trying to do an advanced regexp match but postgres doesn't > seem to let me. > I've set regex_flavor to ARE, and I've tried prefixing my strings, i.e. > ~* E'***:abc' but for some reason postgres treats all my regexps as BRE's. > Common new

[GENERAL] Getting FATAL: terminating connection due to administrator command

Hi Since some days we are getting the above message. The system is a current CentOS 5.5, x86_64, Postgresql 8.4 as it comes with the packages postgresql84, postgresql84-libs etc. PostGIS is enabled, as it comes from http://www.argeo.org/linux/argeo-el. The error message appears from time to ti

Re: [GENERAL] Monitoring object usage?

Well, you can see usage statistics for tables, indexes, functions and sequences ... but AFAIK it's not possible to do that for columns. See this http://www.postgresql.org/docs/8.4/static/runtime-config-statistics.html and then use pg_stat_ and pg_statio_ catalogs (http://www.postgresql.org/docs/8.

Re: [GENERAL] Monitoring object usage?

Hi, > From: "Colin 't Hart" > To: pgsql-general@postgresql.org > Date: 15/09/2010 17:37 > Subject: [GENERAL] Monitoring object usage? > Sent by: pgsql-general-ow...@postgresql.org > > Hi, > > I'm trying to get a grip on a new body of code and a Postgres > database that I'm working with. In par

Re: [GENERAL] configure: error: header file is required for XML support

Hi Peter On Wed, Sep 15, 2010 at 12:46 PM, Peter Roethlisberger wrote: > Hey Dave, > > Thanks for the quick reply. The path is correct: > >  # ls -l /usr/local/libxml2/include/libxml2/libxml/parser.h > -rw-r--r-- 1 root root 39556 Sep 15 10:07 > /usr/local/libxml2/include/libxml2/libxml/parser.h

[GENERAL] Monitoring object usage?

Hi, I'm trying to get a grip on a new body of code and a Postgres database that I'm working with. In particular I've been tasked with cleaning up the database. Is there a way in Postgres to enable monitoring on tables and columns to determine what's not actively being used? This would then form

Re: [GENERAL] configure: error: header file is required for XML support

Hey Dave, Thanks for the quick reply. The path is correct: # ls -l /usr/local/libxml2/include/libxml2/libxml/parser.h -rw-r--r-- 1 root root 39556 Sep 15 10:07 /usr/local/libxml2/include/libxml2/libxml/parser.h I first used /usr/local/libxml2/include/ which did not work. Doing a find for parser

Re: [GENERAL] configure: error: header file is required for XML support

On Wed, Sep 15, 2010 at 10:57 AM, Peter Roethlisberger wrote: > Hi there, > > I try to configure postgres: > > /configure --prefix=/usr/local/postgresql-8.4.4/ \ > --without-docdir \ > --with-openssl \ > --with-libxml \ > --with-libxslt \ > --with-libraries=/usr/local/openssl:/lib:/lib64:/usr/loca

[GENERAL] configure: error: header file is required for XML support

Hi there, I try to configure postgres: /configure --prefix=/usr/local/postgresql-8.4.4/ \ --without-docdir \ --with-openssl \ --with-libxml \ --with-libxslt \ --with-libraries=/usr/local/openssl:/lib:/lib64:/usr/local/readline/lib:/usr/local/zlib/lib:/usr/local/openssl/lib64:/usr/local/libxml2/li

[GENERAL] Force ARE in regexp string

Hi! I'm trying to do an advanced regexp match but postgres doesn't seem to let me. I've set regex_flavor to ARE, and I've tried prefixing my strings, i.e. ~* E'***:abc' but for some reason postgres treats all my regexps as BRE's. Common newbie gotchas? I'm trying it directly from psql.exe bt