I created a function that does some heavy string manipulation, so I needed to use pl/perl rather than pl/pgsql.

I'm not experienced in perl, but the function works well when used as an independent perl subroutine - it depends only on its arguments. I use the Encode package (in postgresql configuration).

But my use of the "pack" function causes a Safe error. Apparently "pack" and "unpack" are in the operator mask.

Is there any way to remove them from that mask? I don't want to use pl/perlu - it's too unsafe, I think, and its running environment is different.

We have tables which we archive and shorten every day. That is - the main table that has daily inserts and updates is kept small, and there is a parallel table with all the old data up to a year ago.

In the past we noticed that the bulk transfer from the main table to the archive table takes a very long time, so we decided to do this in three steps: (1) drop indexes on the archive table, (2) insert a week's worth of data into the archive table. (3) recreate the indexes.

Basically, I have several production databases with various data, and I have a reports database that grabs all necessary data once a day.

Now, there is is a new requirement to have some of the data available in the reports database as soon as it is inserted in the production database. Specifically, the data in question is in two particular tables. However, in addition to just shipping the tables in, I also need to perform a bit of processing on the data as it comes. Basically, each transaction in production is represented by 1-3 rows in the two tables.

I am using Postgresql 8.3.14 on our reporting system. There are scripts that collect data from many databases across the firm into this database. Recently I added tables from a particular database which has encoding UTF-8. My dump procedure says

\encoding ISO-8859-8
\copy ( SELECT ...

Hi guys,

I'm interested in a solution that will allow our customers to run reports - which may involve complicated queries - on data which is as up-to-date as possible.

One thing I don't want to do is to let the reporting system connect to the production database. I want the indexes in production to be limited to what production needs, and not add indexes that are required for reports, for instance.

We are looking at a replication solution aimed at high availability.

So we want to use PostgreSQL 9's streaming replication/hot standby. But I seem to be missing a very basic piece of information: suppose the primary is host1 and the secondary is host2. Suppose that when host1 fails host2 detects that and creates the trigger file that causes the secondary to act as primary.

How do all clients, which have connection strings aimed at host1 know to fail over and use host2?

Is there a good Internet resource for reading on this?

Thank you,
Herouth

Hi.

I was instructed to delete old records from one of the tables in our production system. The deletion took hours and I had to stop it in mid-operation and reschedule it as a night job. But then I had to do the same when I got up in the morning and it was still running.

The odd thing about it: There are 4720965 records in the table, of which I have to delete 3203485.

Hi.

I asked a question on the pgsql-sql mailing list, and the solution suggested using a type from pgfoundry.

How does PHP deal with user-defined types? Specifically, we use ZF library and it would be preferred not to have to write the queries literally but rather use the prepared statements offered by ZF. Are User-Defined types converted into any appropriate PHP constructs? Or do I have to convert everything into strings and parse returned strings into objects/arrays?

TIA
Herouth

Hi there.

During the weekend I've worked for hours on recovering table bloat. Now I was hoping that after the tables are properly trimmed, then after the next delete operation which created dead tuples, autovacuum will go into effect and do its job properly, and prevent the situation from recurring.

Indeed autovacuum started working on some of the tables. At least one of these tables was one that I have trimmed up using CLUSTER. So I was watching that autovacuum process carefully. And then suddenly it was gone, after working for 20-odd hours.

As a result of my recent encounter with table bloat and other tuning issues I've been running into, I'm looking for a good resource for improving my tuning skills.

My sysadmin ran into the following book:

PostgreSQL 9.0 High Performance, by Gregory Smith, ISBN 184951030X
<a href="http://amzn.com/184951030X" title="http://amzn.com/184951030X">http://amzn.com/184951030X</a>

Which covers versions 8.1 through 9.

Any opinions on this book? Other suggestions?

Thank you,
Herouth

Hello. We have two problems (which may actually be related...)

1. We are running at over 90% capacity of the disk at one of the servers - a report/data warehouse system. We have ran out of disk space several times. Now we need to make some file-archived data available on the database to support our legal team. This means two huge tables to be added to the database. The only solution that I see is to add more space by means of another tablespace.