Re: [GENERAL] JDBC prepared statements & server-side prepared statements

[Albe Laurenz]

Jack Orenstein wrote:
> I'm using postgresql 8.3.7. Under what conditions does a JDBC prepared 
> statement 
> result in a server-side prepared statement?

http://jdbc.postgresql.org/development/privateapi/org/postgresql/PGStatement.html#setPrepareThreshold(int)

The default is 5, as far as I remember.

Yours,
Laurenz Albe

-- 
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] example of aggregate function for product

[Albe Laurenz]

Whit Armstrong wrote:
> I needed to write a product aggregate function, and just happened to
> find this example in the nodes to the 8.0 manual:
> 
[...]
> 
> but that example looks pretty different than the ones found in the 8.3
> manual (avg for instance):
> 
[...]
> 
> Are there any experts out there who have defined a product or
> cumulative product function that abides best practices who would be
> willing to share?

I don't know what "best practices" are, but the following works
fine on PostgreSQL 8.3 and 8.4:

CREATE AGGREGATE mul(double precision) (SFUNC=float8mul, STYPE=double 
precision, INITCOND=1);

Yours,
Laurenz Albe

-- 
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] 8.4 RC1 on ubuntu 9.04 jaunty, problems after install

[Dave Page]

On Wed, Jun 24, 2009 at 10:07 PM, Dragan
Sahpaski wrote:

> Ok the same thing happened with the 8.3.7 installer after a few hours - not
> right away.
> I uninstalled the 8.4, and there are no libuuid files in /opt/8.3/lib/.

No, uuid-ossp isn't included in the 8.3 installer, so the offending
libraries aren't installed. I've tested 8.3 here on an otherwise clean
install of 9.04, and themes, sound and network all seem to work as
they should. There is no delayed installation of libraries and
ldconfig is run during the installation, so I can't see any reason why
things would suddenly break 'after a few hours'

> What shoud I do and how can I be notified when the installers will be
> updated?

8.4.0 is due to be released on Monday.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.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] Bug in ecpg lib ?

[Albe Laurenz]

l...@crysberg.dk wrote:
>I'm using PostgreSQL in a server project that uses many 
> forks and many threads in each forked process.
> 
>Almost everytime I do a pthread_cancel() I get a SIGSEGV. 
> I have then linked the libmudflapth into my program to catch 
> the problem sooner and now that reports either 'invalid 
> pointer' or 'double free or corruption' when a thread is 
> cancelled. Typically I have 2 database connection opened 
> before any of the threads are created. I am pretty sure that 
> I'm only using 1 connection in any 1 thread, i.e. only 2 of 
> the threads are doing database access and using each their 
> allocated connection.
> 
>After the main thread has done a pthread_cancel() I get a 
> "mudflapth dump" with the following trace back (the abort 
> comes from the mudflapth lib when detecting the bad pointer):
> 
> #0  0xe405 in __kernel_vsyscall ()
> #1  0xf7ca2335 in raise () from /lib32/libc.so.6
> #2  0xf7ca3cb1 in abort () from /lib32/libc.so.6
> #3  0xf7cdb6ec in ?? () from /lib32/libc.so.6
> #4  0xf7ce71ab in free () from /lib32/libc.so.6
> #5  0xf7dec061 in free (buf=0x87ed138) at ../../../libmudflap/mf-hooks1.c:241
> #6  0xf7ef2b5c in ecpg_sqlca_key_destructor () from /lib32/libecpg.so.6
> #7  0xf7dcebb0 in __nptl_deallocate_tsd () from /lib32/libpthread.so.0
> #8  0xf7dcf509 in start_thread () from /lib32/libpthread.so.0
> #9  0xf7d5008e in clone () from /lib32/libc.so.6
> 
>Looking in the ecpg_sqlca_key_destructor(), it seems to me 
> that the sqlca can be deallocated several times !? (I'm not 
> too much into the Postgres code including ecpg, so that is a 
> novice point of view.)
> 
>I have tried both pgsql-8.3.5 and pgsql-8.4rc1, with 
> exactly the same result and and on many different Linux 
> systems, mainly Slackware 10.2 and Ubuntu 7. I have on all 
> systems configured and compiled Postgres with this configure line:
> 
> ./configure --prefix=/usr/local/Packages/pgsql-8.3.5 
> --with-openssl --enable-thread-safety

Could you create a small sample program that reproduces the bug?

That would make it easier for me or somebody else to do something about it.

Yours,
Laurenz Albe

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

[GENERAL] Add Space symbols for TSvector

[Henk van Lingen]

Hi,

How can I add a character as space symbol for the full text search indexes?

I'm adding firewall syslog lines in a database (8.3.7), and have a GIN
index on the message field. I'd like the slash considered a space symbol,
so i can search for IPnumbers in test like

world/123.456.192.46(0) -> some-fwsm/123.456.221.121(0)

Regards,
-- 
Henk van Lingen,  (o-  -+
Netwerk & Telefonie, ICT Service Center   /\|
Universiteit Utrecht, Jenalaan 18a, kamer 1.40v_/_
http://henk.vanlingen.net/ http://www.tuxtown.net/netiquette/

-- 
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] Add Space symbols for TSvector

[Oleg Bartunov]

On Thu, 25 Jun 2009, Henk van Lingen wrote:



Hi,

How can I add a character as space symbol for the full text search indexes?

I'm adding firewall syslog lines in a database (8.3.7), and have a GIN
index on the message field. I'd like the slash considered a space symbol,
so i can search for IPnumbers in test like

world/123.456.192.46(0) -> some-fwsm/123.456.221.121(0)


preprocess text, for example, using replace(), regexp_replace() functions

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

Re: [GENERAL] Replication

[Jasen Betts]

On 2009-06-23, Mike Christensen  wrote:

> Does anyone actually have that (any node
> can go down and the others still replicate amongst themselves?)

you are describing usenet. (it was not designed for relational database 
replication but it does have that feature)


-- 
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] Add Space symbols for TSvector

[Henk van Lingen]

On Thu, Jun 25, 2009 at 01:05:41PM +0400, Oleg Bartunov wrote:
>>
>> How can I add a character as space symbol for the full text search indexes?
>>
>> I'm adding firewall syslog lines in a database (8.3.7), and have a GIN
>> index on the message field. I'd like the slash considered a space symbol,
>> so i can search for IPnumbers in test like
>>
>> world/123.456.192.46(0) -> some-fwsm/123.456.221.121(0)
>
> preprocess text, for example, using replace(), regexp_replace() functions

Oke, you mean at the time the insert in de db is done, i suppose.
However, I'm using the new syslog-ng 3 feature which does the 
insert directly. So I have to change the syslogd for that.

I understand the ts_search parser is not configurable?

Thanks,
-- 
Henk van Lingen,  (o-  -+
Netwerk & Telefonie, ICT Service Center   /\|
Universiteit Utrecht, Jenalaan 18a, kamer 1.40v_/_
http://henk.vanlingen.net/ http://www.tuxtown.net/netiquette/

-- 
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] JDBC prepared statements & server-side prepared statements

[Dave Cramer]

On Thu, Jun 25, 2009 at 2:59 AM, Albe Laurenz wrote:

> Jack Orenstein wrote:
> > I'm using postgresql 8.3.7. Under what conditions does a JDBC prepared
> statement
> > result in a server-side prepared statement?
>
>
> http://jdbc.postgresql.org/development/privateapi/org/postgresql/PGStatement.html#setPrepareThreshold(int)
>
> The default is 5, as far as I remember.
>
> Yours,
> Laurenz Albe
>
> 5 is when they become named statements. The driver always uses prepared
statements

Dave

Re: [GENERAL] [JDBC] Postgres 8.3.7 -- EOF on client connection

[Saurabh Dave]

Hi CMJ,

If you are using WebNMS framework with hibernate, can you check if you have
done this:

*ConfigReader configReader = ConfigReader.getInstance();
configReader.modifyHbmToDdl(true);
*

Thanks,
Saurabh

On Wed, Jun 24, 2009 at 6:06 PM, CM J  wrote:

> Hi ,
>
>   I installed postgres 8.3.7-1 in my windows machine. The server starts
> all right.However when i attempt a JDBC connection(Driver used:
> postgresql-8.3-604.jdbc4.jar), the following error is thrown:
>
> 2009-06-24 17:55:03.564 ISTERROR:  relation "befailover" does not exist
> 2009-06-24 17:55:03.564 ISTSTATEMENT:  SELECT HOSTADDRESS,RMIREGISTRYPORT
> FROM BEFAILOVER WHERE SERVERROLE='PRIMARY'
> 2009-06-24 17:55:03.580 ISTERROR:  relation "befailover" does not exist
> 2009-06-24 17:55:03.580 ISTSTATEMENT:  DELETE  FROM BEFAILOVER WHERE
> SERVERROLE='STANDBY'
> *2009-06-24 17:55:12.639 ISTLOG:  could not receive data from client: No
> connection could be made because the target machine
> actively refused it.
> 2009-06-24 17:55:12.639 ISTLOG:  unexpected EOF on client connection*
>
> I attempt the JDBC connection from the machine as the postgres
> server. There are no anti-virus server software or firewall involved. Can
> someone tell me what could be wrong with my setup.
>
> Thanks.
>

[GENERAL] Upgrade

[Pedro Doria Meunier]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi all,

I've got a somewhat 'hairy' problem in my hands...

One of my servers is still Fedora Core (cough) (cough) 6 :]
It's running Postgresql 8.2.4

I want it to serve as a last resort, end of the spectrum, backup/slave
for another server running Postgresql 8.2.9 via Slony-I.

The other server is running Postgresql 8.2.9 over Fedora Core 8.

First challenge: I need to upgrade to postgresql 8.3 if I'm to use
Slony-I 2.0.2 on both servers.

At a glance I feel that upgrading the FC6 server is the hairiest of
tasks to be accomplished.
Regarding the FC8 (a.k.a. master wannabe): would an upgrade from 8.2.9
to 8.3 require a dump/reload ?
The DB size on this server is not that big (~6GB), although a single
table has >8.3M rows, meaning that if a dump/reload is involved I'm
looking at several hours of out of service (sweat)...

Insight on how to tackle the upgrade for these two servers (especially
the FC6 one) would be much appreciated! ;-)

Thanks in advance.
BR.

- --
Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam
 
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFKQ2eq2FH5GXCfxAsRAvqkAJwOrlUSI37IdbiaFmnWfkBo4hakkwCeMugm
NbYacw6nox6xubq3llNR0xk=
=f862
-END PGP SIGNATURE-


-- 
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] Upgrade

[Bill Moran]

In response to Pedro Doria Meunier :

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Hi all,
> 
> I've got a somewhat 'hairy' problem in my hands...
> 
> One of my servers is still Fedora Core (cough) (cough) 6 :]
> It's running Postgresql 8.2.4
> 
> I want it to serve as a last resort, end of the spectrum, backup/slave
> for another server running Postgresql 8.2.9 via Slony-I.
> 
> The other server is running Postgresql 8.2.9 over Fedora Core 8.
> 
> First challenge: I need to upgrade to postgresql 8.3 if I'm to use
> Slony-I 2.0.2 on both servers.
> 
> At a glance I feel that upgrading the FC6 server is the hairiest of
> tasks to be accomplished.
> Regarding the FC8 (a.k.a. master wannabe): would an upgrade from 8.2.9
> to 8.3 require a dump/reload ?
> The DB size on this server is not that big (~6GB), although a single
> table has >8.3M rows, meaning that if a dump/reload is involved I'm
> looking at several hours of out of service (sweat)...
> 
> Insight on how to tackle the upgrade for these two servers (especially
> the FC6 one) would be much appreciated! ;-)

Why don't you set up Slony to replicate data between the two, then you
can take the slave offline to upgrade it to PG 8.3, then switch the
slave to be the master, then upgrade the other.

We just did that with 4 DB servers (all replicas of the same DB) a few
weeks ago and it went fine.  It was a 5G database and half of the servers
were replicating across the country.

We're looking at the upgrade to Slony 2 as a separate step.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] Upgrade

[Guy Flaherty]

On Thu, Jun 25, 2009 at 10:04 PM, Pedro Doria Meunier  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi all,
>
> I've got a somewhat 'hairy' problem in my hands...
>
> One of my servers is still Fedora Core (cough) (cough) 6 :]
> It's running Postgresql 8.2.4
>
> I want it to serve as a last resort, end of the spectrum, backup/slave
> for another server running Postgresql 8.2.9 via Slony-I.
>
> The other server is running Postgresql 8.2.9 over Fedora Core 8.
>
> First challenge: I need to upgrade to postgresql 8.3 if I'm to use
> Slony-I 2.0.2 on both servers.
>
> At a glance I feel that upgrading the FC6 server is the hairiest of
> tasks to be accomplished.
> Regarding the FC8 (a.k.a. master wannabe): would an upgrade from 8.2.9
> to 8.3 require a dump/reload ?
> The DB size on this server is not that big (~6GB), although a single
> table has >8.3M rows, meaning that if a dump/reload is involved I'm
> looking at several hours of out of service (sweat)...
>
> Insight on how to tackle the upgrade for these two servers (especially
> the FC6 one) would be much appreciated! ;-)
>  


You would need to do a dump restore when upgrading from 8.2.9 to 8.3.

Guy Flaherty

[GENERAL] create a table inside a function

[Alain Roger]

Hi,

i would like to execute the following SQL command into a function based on
some IF, END IF tests before.
how can i do that ?

here is my SQL command:

> create table sw.tmp_import

(

id serial NOT NULL,

software VARCHAR(1024),

barcode VARCHAR(10),

username VARCHAR(1024),

area VARCHAR(512),

locality VARCHAR(512)

CONSTRAINT id_pkey PRIMARY KEY (id))

WITH (OIDS=FALSE);
>

thanks a lot,

Alain
---
Windows XP x64 SP2 / Fedora 10 KDE 4.2
PostgreSQL 8.3.5 / MS SQL server 2005
Apache 2.2.10
PHP 5.2.6
C# 2005-2008

Re: [GENERAL] Upgrade

[Greg Stark]

On Thu, Jun 25, 2009 at 1:28 PM, Guy Flaherty wrote:
> One of my servers is still Fedora Core (cough) (cough) 6 :]
> It's running Postgresql 8.2.4
>
> I want it to serve as a last resort, end of the spectrum, backup/slave
> for another server running Postgresql 8.2.9 via Slony-I.

Before doing anything else you should upgrade both of these to 8.2.13.
There's no point in running software with known bugs and security
holes when there are updates available.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
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] [BUGS] Integrity check

[Greg Stark]

On Wed, Jun 24, 2009 at 11:45 PM, Scott
Mead wrote:
>
>    If you're asking "Does the database have the ability to verify that
> whatever is in a block is what was put into that block", then Oracle has
> block check-summing (I'm not sure if this got into PG 8.4 or not...)

It didn't, due to technical issues.

However you should be aware that checksumming doesn't necessarily
provide a broad guarantee either. It can only protect against damage
that occurs between the time the checksum is generated and when it's
verified. If you have bad memory, for example, it can corrupt the data
before the checksum is calculated, so the block may be stored happily
with a valid checksum for the corrupt data.



-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
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] create a table inside a function

[hubert depesz lubaczewski]

On Thu, Jun 25, 2009 at 02:53:59PM +0200, Alain Roger wrote:
> i would like to execute the following SQL command into a function based on
> some IF, END IF tests before.
> how can i do that ?

1. you can use execute in pl/pgsql.
2. if your ifs are basically testing if the table exists - you might
want to check this:
http://www.depesz.com/index.php/2008/06/18/conditional-ddl/

Best regards,

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
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] create a table inside a function

[Alain Roger]

This is what i did at the beginning and it did not work. But now i've just
discovered that my create table command was wrong...so it's ok. :-)
thx.

A.

On Thu, Jun 25, 2009 at 3:01 PM, Frank Heikens wrote:

> A very simple and incomplete example:
>
> CREATE OR REPLACE FUNCTION new_table(int) returns bool AS
> $$
> BEGIN
>IF $1 = 1 THEN
>EXECUTE 'CREATE TABLE x()';
>ELSIF $1 = 2 THEN
>EXECUTE 'CREATE TABLE y()';
>ELSE
>EXECUTE 'CREATE TABLE z()';
>END IF;
>
>RETURN TRUE;
> END;
> $$
> language plpgsql;
>
> SELECT new_table(1);
>
>
>
> Use EXECUTE and be sure you can't be the next victim of SQL injection. If
> you need some userinput in the EXECUTE-statement, use quote_literal() and/or
> quote_ident().
>
> Regards,
> Frank
>
> Op 25 jun 2009, om 14:53 heeft Alain Roger het volgende geschreven:
>
>
>  Hi,
>>
>> i would like to execute the following SQL command into a function based on
>> some IF, END IF tests before.
>> how can i do that ?
>>
>> here is my SQL command:
>> create table sw.tmp_import
>> (
>> id serial NOT NULL,
>> software VARCHAR(1024),
>> barcode VARCHAR(10),
>> username VARCHAR(1024),
>> area VARCHAR(512),
>> locality VARCHAR(512)
>> CONSTRAINT id_pkey PRIMARY KEY (id))
>> WITH (OIDS=FALSE);
>>
>> thanks a lot,
>>
>> Alain
>> ---
>> Windows XP x64 SP2 / Fedora 10 KDE 4.2
>> PostgreSQL 8.3.5 / MS SQL server 2005
>> Apache 2.2.10
>> PHP 5.2.6
>> C# 2005-2008
>>
>
>


-- 
Alain
---
Windows XP x64 SP2 / Fedora 10 KDE 4.2
PostgreSQL 8.3.5 / MS SQL server 2005
Apache 2.2.10
PHP 5.2.6
C# 2005-2008

Re: [GENERAL] create a table inside a function

[Alain Roger]

I'm confused now because if i try my SQL command alone it works without any
problem, but in the following pglSQL code, it does not :-(

IF (outResult = 1) THEN
>  return true;
> ELSE
>  EXECUTE 'create table sw.tmp_import (id serial NOT NULL CONSTRAINT id_key
> PRIMARY KEY, software VARCHAR(1024), barcode VARCHAR(10), username
> VARCHAR(1024), area VARCHAR(512), locality VARCHAR(512)) WITH
> (OIDS=FALSE);';
>  EXECUTE 'ALTER TABLE sw.tmp_import OWNER TO usr_audit';
>  return false;
> END IF;
>

why ?

thx.

A.

On Thu, Jun 25, 2009 at 3:09 PM, Alain Roger  wrote:

> This is what i did at the beginning and it did not work. But now i've just
> discovered that my create table command was wrong...so it's ok. :-)
> thx.
>
> A.
>
>
> On Thu, Jun 25, 2009 at 3:01 PM, Frank Heikens wrote:
>
>> A very simple and incomplete example:
>>
>> CREATE OR REPLACE FUNCTION new_table(int) returns bool AS
>> $$
>> BEGIN
>>IF $1 = 1 THEN
>>EXECUTE 'CREATE TABLE x()';
>>ELSIF $1 = 2 THEN
>>EXECUTE 'CREATE TABLE y()';
>>ELSE
>>EXECUTE 'CREATE TABLE z()';
>>END IF;
>>
>>RETURN TRUE;
>> END;
>> $$
>> language plpgsql;
>>
>> SELECT new_table(1);
>>
>>
>>
>> Use EXECUTE and be sure you can't be the next victim of SQL injection. If
>> you need some userinput in the EXECUTE-statement, use quote_literal() and/or
>> quote_ident().
>>
>> Regards,
>> Frank
>>
>> Op 25 jun 2009, om 14:53 heeft Alain Roger het volgende geschreven:
>>
>>
>>  Hi,
>>>
>>> i would like to execute the following SQL command into a function based
>>> on some IF, END IF tests before.
>>> how can i do that ?
>>>
>>> here is my SQL command:
>>> create table sw.tmp_import
>>> (
>>> id serial NOT NULL,
>>> software VARCHAR(1024),
>>> barcode VARCHAR(10),
>>> username VARCHAR(1024),
>>> area VARCHAR(512),
>>> locality VARCHAR(512)
>>> CONSTRAINT id_pkey PRIMARY KEY (id))
>>> WITH (OIDS=FALSE);
>>>
>>> thanks a lot,
>>>
>>> Alain
>>> ---
>>> Windows XP x64 SP2 / Fedora 10 KDE 4.2
>>> PostgreSQL 8.3.5 / MS SQL server 2005
>>> Apache 2.2.10
>>> PHP 5.2.6
>>> C# 2005-2008
>>>
>>
>>
>
>
> --
> Alain
> ---
> Windows XP x64 SP2 / Fedora 10 KDE 4.2
> PostgreSQL 8.3.5 / MS SQL server 2005
> Apache 2.2.10
> PHP 5.2.6
> C# 2005-2008
>



-- 
Alain
---
Windows XP x64 SP2 / Fedora 10 KDE 4.2
PostgreSQL 8.3.5 / MS SQL server 2005
Apache 2.2.10
PHP 5.2.6
C# 2005-2008

Re: [GENERAL] create a table inside a function

[Adrian Klaver]

On Thursday 25 June 2009 6:29:10 am Alain Roger wrote:
> I'm confused now because if i try my SQL command alone it works without any
> problem, but in the following pglSQL code, it does not :-(

What is the error?

>
> IF (outResult = 1) THEN
>
> >  return true;
> > ELSE
> >  EXECUTE 'create table sw.tmp_import (id serial NOT NULL CONSTRAINT
> > id_key PRIMARY KEY, software VARCHAR(1024), barcode VARCHAR(10), username
> > VARCHAR(1024), area VARCHAR(512), locality VARCHAR(512)) WITH
> > (OIDS=FALSE);';
^ This could be part of the problem.


> >  EXECUTE 'ALTER TABLE sw.tmp_import OWNER TO usr_audit';
> >  return false;
> > END IF;
>
> why ?
>
> thx.
>
> A.
>


-- 
Adrian Klaver
akla...@comcast.net

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

[GENERAL] Postgres online backup and restore has errors that are concerning

[Chris Barnes]

  I started an online backup of postgres, tar’d my data folder, copy to usb 
drive in production
and restored it into my RC environment. Have I missed 
something important?

Online 
Backup

psql
checkpoint;
SELECT 
pg_switch_xlog();
SELECT 
pg_start_backup('postres_full_backup_June222009');
tar -czvf 
pgprd01_June22_2009_production.dmp.tar.gz data/
SELECT 
pg_stop_backup();

Restore
tar –xzvf 
pgprd01_June22_2009_production.dmp.tar.gz

When starting the database I 
receive many errors that look like the backup was 
corrupted.

2009-06-23 08:29:15 
EDT:@:7614ERROR:  xlog flush request 10D/590D6578 is not satisfied --- flushed 
only to 10D/510C4FB8
2009-06-23 08:29:15 
EDT:@:7614CONTEXT:  writing block 2 of relation 
16403/16884/2696
2009-06-23 08:29:16 
EDT:@:7614ERROR:  xlog flush request 10D/590D6578 is not satisfied --- flushed 
only to 10D/510C4FB8
2009-06-23 08:29:16 
EDT:@:7614CONTEXT:  writing block 2 of relation 
16403/16884/2696
2009-06-23 08:29:16 
EDT:@:7614WARNING:  could not write block 2 of 
16403/16884/2696

pgdb001=> select 
criteria_rank from client001.t1020screen where criteria_rank like 
'%TR009%';
ERROR:  missing chunk number 
0 for toast value 738680

Version
[postg...@pgprd01 data]$ cat 
PG_VERSION 
8.3

Chris Barnes
_
Windows Live helps you keep up with all your friends, in one place.
http://go.microsoft.com/?linkid=9660826

Re: [GENERAL] Add Space symbols for TSvector

[Tom Lane]

Henk van Lingen  writes:
> I understand the ts_search parser is not configurable?

You can substitute your own parser, but the built-in one doesn't have
any provisions for fine-grained configuration.

regards, tom lane

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

[GENERAL] PG 8.3.7 initdb -E LATIN1 fails on Windows

[Abraham, Danny]

Hi,

Runnning:  initdb -E LATIN1 -D .

Error: encoding mismatch

Used to run fine on 8.2.4 on the same Windows platform.

OUTPUT
===
The database cluster will be initialized with locale English_United States.1252.

initdb: encoding mismatch
The encoding you selected (LATIN1) and the encoding that the selected locale 
uses (WIN1252) do not match.  This would lead to misbehavior in various 
character string processing functions.
Rerun initdb and either do not specify an encoding explicitly, or choose a 
matching combination.


Thanks

Danny Abraham


-- 
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 do I add text to a thread?

[Abraham, Danny]

I am not " .2 instance that won't come up after shutdown"
But cannot add more posts.

Thanks a lot

Danny


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

[GENERAL] Fedora 11 where is plpython.so

[Clodoaldo Pinto Neto]

Where can i find plpython.so in the Fedora 11 i586 rpm packages? The
postgresql-python does not have it:

$ rpm -q --filesbypkg postgresql-python | grep plpython
$

http://download.fedora.redhat.com/pub/fedora/linux/releases/11/Fedora/i386/os/Packages/postgresql-python-8.3.7-1.fc11.i586.rpm

There is no postgresql-pl package.

Regards, Clodoaldo

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

[GENERAL] Vacuum on the database versus individual tables.

[Hartman, Matthew]

Good morning.

 

On occasion I'll perform a full vacuum on a database but will still
receive a suggestion to vacuum an individual table immediately after.
Does the full database vacuum not handle each individual table?

 

Thanks,


Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549- x4294 

 

 

Re: [GENERAL] Postgres online backup and restore has errors that are concerning

[Alan Hodgson]

On Thursday 25 June 2009, Chris Barnes  
wrote:
>   I started an online backup of postgres, tar’d my data folder, copy to
> usb drive in production
> and restored it into my RC environment. Have I missed
> something important?
>

You need the transaction logs archived during and immediately after the 
backup, and you need to setup a recovery.conf for the initial startup that 
can access those files. See the PITR backup notes in the documentation, 
they're really quite complete.

-- 
WARNING:  Do not look into laser with remaining eye.

-- 
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] Fedora 11 where is plpython.so

[Clodoaldo Pinto Neto]

2009/6/25 Clodoaldo Pinto Neto :
> Where can i find plpython.so in the Fedora 11 i586 rpm packages? The
> postgresql-python does not have it:
>
> $ rpm -q --filesbypkg postgresql-python | grep plpython
> $
>
> http://download.fedora.redhat.com/pub/fedora/linux/releases/11/Fedora/i386/os/Packages/postgresql-python-8.3.7-1.fc11.i586.rpm
>
> There is no postgresql-pl package.

Just found it. It is in the Everything repository. Sorry for the noise

>
> Regards, Clodoaldo
>

-- 
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] horizontal sharding

[David Fetter]

On Wed, Jun 24, 2009 at 11:03:07PM -0700, mobiledream...@gmail.com wrote:
> GOals are to scale our game for 1 simultaneous connection to db
> right now db crashing at 500 or 1000 sim connections

Consider using pgbouncer to multiplex your database connections.

If that doesn't work, consider hiring one of the PostgreSQL consulting
outfits like Command Prompt, Endpoint, OmniTI, or the one I work for,
PostgreSQL Experts 

Cheers,
David.
> On Mon, Jun 15, 2009 at 8:07 AM, David Fetter 
> wrote:
> 
> > On Mon, Jun 15, 2009 at 12:32:15AM -0700, mobiledream...@gmail.com
> > wrote:
> > > Hey guys what is a good way to horizontal shard in postgresql
> >
> > "Sharding" is not really a technical term, so it's not really
> > possible to answer this question meaningfully as posed.
> >
> > What is it that you actually want to accomplish here exactly?
> > What are your goals, and what are your priorities on those goals?
> > What will you trade off to achieve them?
> >
> > > 1. pgpool 2 2. gridsql
> > >
> > > which is a better way to use sharding
> > >
> > > also is it possible to paritition without changing client code
> >
> > Almost certainly not.  This would be one of the trade-offs
> > mentioned above.

-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

[GENERAL] Support of multibyte encoding for pg_trgm

[Brian Hirt]

Teodor,

I ran across a commit message that shows multibyte encoding support in  
8.4 and my testing shows that to be the case as well.   Is there a  
back patch for 8.2?   My own quick attempt at creating one didn't work  
so well and before I start spending some major time trying I thought  
I'd check in with you.


Regards,

Brian Hirt

Postgres Version 8.2 and 8.3
test=# select show_trgm('魔法門英雄無敵2：王位爭奪戰');
   show_trgm
---
 {"  2"," 2 "}
(1 row)

Postgres Version 8.4
test=# select show_trgm('魔法門英雄無敵2：王位爭奪戰');
 
show_trgm

--
 {0x84af82,0x8426fb,0x886567,0x8986ec, 
0x8c3de8,0x9c19f5,0xa0ef88,0xae352c,0xc7de23,0xf449ca, 
0x003dc9,0x1003c8,0x143838,0x64a38f,0x650b2a}

(1 row)


Log Message:
---
Support of multibyte encoding for pg_trgm

Modified Files:
--
pgsql/contrib/pg_trgm:
trgm.h (r1.9 -> r1.10)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_trgm/trgm.h?r1=1.9&r2=1.10 
)

trgm_gin.c (r1.5 -> r1.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_trgm/trgm_gin.c?r1=1.5&r2=1.6 
)

trgm_op.c (r1.10 -> r1.11)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_trgm/trgm_op.c?r1=1.10&r2=1.11 
)



--
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] Vacuum on the database versus individual tables.

[Grzegorz Jaśkiewicz]

On Thu, Jun 25, 2009 at 3:40 PM, Hartman,
Matthew wrote:
> On occasion I’ll perform a full vacuum on a database but will still receive
> a suggestion to vacuum an individual table immediately after. Does the full
> database vacuum not handle each individual table?

What's the exact message, what OS, what pg version, how do you call vacuum.




-- 
GJ

-- 
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] create a table inside a function

[Alvaro Herrera]

Alain Roger escribió:

> IF (outResult = 1) THEN
> >  return true;
> > ELSE
> >  EXECUTE 'create table sw.tmp_import (id serial NOT NULL CONSTRAINT id_key
> > PRIMARY KEY, software VARCHAR(1024), barcode VARCHAR(10), username
> > VARCHAR(1024), area VARCHAR(512), locality VARCHAR(512)) WITH
> > (OIDS=FALSE);';
> >  EXECUTE 'ALTER TABLE sw.tmp_import OWNER TO usr_audit';
> >  return false;
> > END IF;

Just leave out the EXECUTE and quotes.  This example should work without
them.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] Upgrade

[Pedro Doria Meunier]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

First of all, thank you all who have replied so far :)

The FC8 server has been upgraded to 8.2.13 with minor "glitches" :)
I'm one step shy of upgrading it to 8.3 :P
Btw: I'm using the http://yum.pgsqlrpms.org/ repository as the source
for the upgrade.
After dumping the DB and stopping the server, should I remove all
PGSQL rpms via yum and then install the pgdg-fedora-8.3-6.noarch

and continue on to the upgrade?

As far as the older FC6 server is concerned to my great frustration
this arch is EOL and there are no upgrade packages available... :-(
Perhaps someone is able to conjure up some sort of solution for this?

TY,

Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam
 



Greg Stark wrote:
> On Thu, Jun 25, 2009 at 1:28 PM, Guy Flaherty
> wrote:
>> One of my servers is still Fedora Core (cough) (cough) 6 :] It's
>> running Postgresql 8.2.4
>>
>> I want it to serve as a last resort, end of the spectrum,
>> backup/slave for another server running Postgresql 8.2.9 via
>> Slony-I.
>
> Before doing anything else you should upgrade both of these to
> 8.2.13. There's no point in running software with known bugs and
> security holes when there are updates available.
>
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFKQ5fa2FH5GXCfxAsRAhllAJ4rLBABOm0eX8NdYGhNgLn8rybvJQCgoV/Q
W1y8JfZlkmrGs72YOPCawZI=
=2gPK
-END PGP SIGNATURE-


-- 
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] create a table inside a function

[Frank Heikens]

A very simple and incomplete example:

CREATE OR REPLACE FUNCTION new_table(int) returns bool AS
$$
BEGIN
IF $1 = 1 THEN
EXECUTE 'CREATE TABLE x()';
ELSIF $1 = 2 THEN
EXECUTE 'CREATE TABLE y()';
ELSE
EXECUTE 'CREATE TABLE z()';
END IF;

RETURN TRUE;
END;
$$
language plpgsql;

SELECT new_table(1);



Use EXECUTE and be sure you can't be the next victim of SQL injection.  
If you need some userinput in the EXECUTE-statement, use  
quote_literal() and/or quote_ident().


Regards,
Frank

Op 25 jun 2009, om 14:53 heeft Alain Roger het volgende geschreven:


Hi,

i would like to execute the following SQL command into a function  
based on some IF, END IF tests before.

how can i do that ?

here is my SQL command:
create table sw.tmp_import
(
id serial NOT NULL,
software VARCHAR(1024),
barcode VARCHAR(10),
username VARCHAR(1024),
area VARCHAR(512),
locality VARCHAR(512)
CONSTRAINT id_pkey PRIMARY KEY (id))
WITH (OIDS=FALSE);

thanks a lot,

Alain
---
Windows XP x64 SP2 / Fedora 10 KDE 4.2
PostgreSQL 8.3.5 / MS SQL server 2005
Apache 2.2.10
PHP 5.2.6
C# 2005-2008



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

[GENERAL] Postgres online backup and restore

[Chris Barnes]

Sorry if posting twice, wasn't part of general when sent and didn't see it
received by group.

  I started an online backup of postgres, tar'd my data folder, copy to usb
drive in production
and restored it into my RC environment. Have I missed something important?

Online Backup

psql

checkpoint;

SELECT pg_switch_xlog();

SELECT pg_start_backup('postres_full_backup_June222009');

tar -czvf pgprd01_June22_2009_production.dmp.tar.gz data/

SELECT pg_stop_backup();

Restore

tar -xzvf pgprd01_June22_2009_production.dmp.tar.gz

 

When starting the database I receive many errors that look like the backup
was corrupted.

 

2009-06-23 08:29:15 EDT:@:7614ERROR:  xlog flush request 10D/590D6578 is not
satisfied --- flushed only to 10D/510C4FB8

2009-06-23 08:29:15 EDT:@:7614CONTEXT:  writing block 2 of relation
16403/16884/2696

2009-06-23 08:29:16 EDT:@:7614ERROR:  xlog flush request 10D/590D6578 is not
satisfied --- flushed only to 10D/510C4FB8

2009-06-23 08:29:16 EDT:@:7614CONTEXT:  writing block 2 of relation
16403/16884/2696

2009-06-23 08:29:16 EDT:@:7614WARNING:  could not write block 2 of
16403/16884/2696

 

 

pgdb001=> select criteria_rank from client001.t1020screen where
criteria_rank like '%TR009%';

ERROR:  missing chunk number 0 for toast value 738680

 

Version

[postg...@pgprd01 data]$ cat PG_VERSION 

8.3

 


Chris Barnes



 

[GENERAL] Problems with postgres online backup - restore

[Chris Barnes]

  I started an online backup of postgres, tar'd my data folder, copy to usb
drive in production
and restored it into my RC environment. Have I missed something important?



When starting the database I receive many errors that look like the backup
was corrupted.

 

2009-06-23 08:29:15 EDT:@:7614ERROR:  xlog flush request 10D/590D6578 is not
satisfied --- flushed only to 10D/510C4FB8

2009-06-23 08:29:15 EDT:@:7614CONTEXT:  writing block 2 of relation
16403/16884/2696

2009-06-23 08:29:16 EDT:@:7614ERROR:  xlog flush request 10D/590D6578 is not
satisfied --- flushed only to 10D/510C4FB8

2009-06-23 08:29:16 EDT:@:7614CONTEXT:  writing block 2 of relation
16403/16884/2696

2009-06-23 08:29:16 EDT:@:7614WARNING:  could not write block 2 of
16403/16884/2696

 

 

pgdb001=> select criteria_rank from client001.t1020screen where
criteria_rank like '%TR009%';

ERROR:  missing chunk number 0 for toast value 738680

 

Version

[postg...@pgprd01 data]$ cat PG_VERSION 

8.3

 

Online Backup

psql

checkpoint;

SELECT pg_switch_xlog();

SELECT pg_start_backup('postres_full_backup_June222009');

tar -czvf pgprd01_June22_2009_production.dmp.tar.gz data/

SELECT pg_stop_backup();

Restore

tar -xzvf pgprd01_June22_2009_production.dmp.tar.gz

 

 

 

 

[GENERAL] why the similar query takes so difference time in tsearch2?

[Miao Jiang]

miao=> \d items
  Table "public.items"
 Column |   Type   | Modifiers 


+--+
 id | integer  | not null default 
nextval('items_id_seq'::regclass)

 tags   | tsvector | not null
 score  | double precision | not null default random()
Indexes:
"items_pkey" PRIMARY KEY, btree (id)
"items_tags_idx" gin (tags)

-- table items have 131 rows.

miao=> explain select id from items where tags @@ to_tsquery('1') limit 151;
   QUERY PLAN 



 Limit  (cost=0.00..604.92 rows=151 width=4)
   ->  Index Scan using items_tags_idx on items  (cost=0.00..5187.83 
rows=1295 width=4)

 Index Cond: (tags @@ to_tsquery('1'::text))
(3 rows)

Time: 0.775 ms
miao=> explain select id from items where tags @@ to_tsquery('1') limit 152;
   QUERY PLAN 



 Limit  (cost=103.21..608.85 rows=152 width=4)
   ->  Bitmap Heap Scan on items  (cost=103.21..4411.17 rows=1295 width=4)
 Recheck Cond: (tags @@ to_tsquery('1'::text))
 ->  Bitmap Index Scan on items_tags_idx  (cost=0.00..102.88 
rows=1295 width=0)

   Index Cond: (tags @@ to_tsquery('1'::text))
(5 rows)

Time: 0.838 ms

miao=> select id from items where tags @@ to_tsquery('1') limit 151;
Time: 1.494 ms
miao=> select id from items where tags @@ to_tsquery('1') limit 152; -- 
Only 1 more than limit 151, but takes about 300 times time.

Time: 413.360 ms
miao=>


Why limit 152 takes so much long time than limit 151? How to improve 
that limit 152 even limit 500 could as fast as limit 151.


Thanks
Miao


--
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] horizontal sharding

[mobiledreamers]

Jim thanks

Can you please show how to setup partitining in gridsql and pgpool 2

thanks a lot

On Mon, Jun 15, 2009 at 10:28 AM, Jim Mlodgenski  wrote:

> what is a good way to horizontal shard in postgresql
>
>> 1. pgpool 2
>> 2. gridsql
>>
>> which is a better way to use sharding
>>
>
> Both are good methods of sharding, but it depends on your goals. GridSQL is
> better in reporting applications where as PG Pool2 is better in
> transactional situations.
>
>
>>
>> also is it possible to paritition without changing client code
>
> Yes, but it depends on the SQL in your client code. If you are just using
> simple SQL with no stored functions, you should be able to slip in either
> solution without changing the client code.
>
> --
> Jim Mlodgenski
> EnterpriseDB (http://www.enterprisedb.com)
>
>


-- 
Bidegg worlds best auction site
http://bidegg.com

Re: [GENERAL] horizontal sharding

[mobiledreamers]

GOals are to scale our game for 1 simultaneous connection to db
right now db crashing at 500 or 1000 sim connections

On Mon, Jun 15, 2009 at 8:07 AM, David Fetter  wrote:

> On Mon, Jun 15, 2009 at 12:32:15AM -0700, mobiledream...@gmail.com wrote:
> > Hey guys
> > what is a good way to horizontal shard in postgresql
>
> "Sharding" is not really a technical term, so it's not really possible
> to answer this question meaningfully as posed.
>
> What is it that you actually want to accomplish here exactly?  What
> are your goals, and what are your priorities on those goals?  What
> will you trade off to achieve them?
>
> > 1. pgpool 2
> > 2. gridsql
> >
> > which is a better way to use sharding
> >
> > also is it possible to paritition without changing client code
>
> Almost certainly not.  This would be one of the trade-offs mentioned
> above.
>
> Cheers,
> David.
> --
> David Fetter  http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter  XMPP: david.fet...@gmail.com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>



-- 
Bidegg worlds best auction site
http://bidegg.com

Re: [GENERAL] Vacuum on the database versus individual tables.

[Hartman, Matthew]

Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical 
"Running vacuum on this table is recommended" dialog box.


Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549- x4294 
 
-Original Message-
From: Grzegorz Jaśkiewicz [mailto:gryz...@gmail.com] 
Sent: Thursday, June 25, 2009 11:25 AM
To: Hartman, Matthew
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Vacuum on the database versus individual tables.

On Thu, Jun 25, 2009 at 3:40 PM, Hartman,
Matthew wrote:
> On occasion I'll perform a full vacuum on a database but will still receive
> a suggestion to vacuum an individual table immediately after. Does the full
> database vacuum not handle each individual table?

What's the exact message, what OS, what pg version, how do you call vacuum.




-- 
GJ


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

[GENERAL] planned recovery from a certain transaction

[Chris Spotts]

I know questions like this have been asked before, but I hadn't seen one
quite from the same perspective (although I'm sure it's out there
somewhere).

 

We have a database which has one long involved procedure early in the
morning that updates all sorts of things, moves data around, deletes some
stuff, alters some DDL - you name it, it does it.  The rest of the day, the
database is read only.  Autovacuum is not on, it was killing performance for
it to kick on in the middle of the proc.  Vacuum is run right before the
long procedure.  We typically wait until the guy onsite verifies the
procedure went smoothly and then vacuum (Why? Because we've read enough of
"well, you would have been able to restore that if the autovacuum wasn't
running".  We have a backup so this is just a checking before vacuuming is
technically unneeded.).  Don't get me wrong, I'm a big autovacuum fan, just
not for this specific case.

 

The transaction itself works flawlessly, but every once and awhile the data
the it uploads from comes in flawed and we have to find a way to reset it.
This reset involves restoring a backup that was taken right before the proc
started.   If we had the xid of the long running transaction, is there a
better way to reset it right before that transaction happened?  Restoring
the backup is a lengthy process because several of the tables that are
affected are rather large.

 

Chris Spotts

 

 

 

 

Re: [GENERAL] PG 8.3.7 initdb -E LATIN1 fails on Windows

[Alvaro Herrera]

Abraham, Danny wrote:
> Hi,
> 
> Runnning:  initdb -E LATIN1 -D .
> 
> Error: encoding mismatch

Right.  Try using Win1252 instead of Latin1:

initdb -E win1252 ...

Or just leave -E out entirely, since it will be picked up by default
from the locale setting anyway.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] Vacuum on the database versus individual tables.

[Grzegorz Jaśkiewicz]

2009/6/25 Hartman, Matthew :
> Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical 
> "Running vacuum on this table is recommended" dialog box.

Well, it really has nothing to do with postgresql it self. Either it
is a bug or property of PgAdmin, but I don't know.
Either someone who knows more about pgadmin is going to respond here,
or you have to ask on pgadmin list.


-- 
GJ

-- 
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] Vacuum on the database versus individual tables.

[Dave Page]

2009/6/25 Grzegorz Jaśkiewicz :
> 2009/6/25 Hartman, Matthew :
>> Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical 
>> "Running vacuum on this table is recommended" dialog box.
>
> Well, it really has nothing to do with postgresql it self. Either it
> is a bug or property of PgAdmin, but I don't know.
> Either someone who knows more about pgadmin is going to respond here,
> or you have to ask on pgadmin list.

pgAdmin will advise vacuuming a table if there is a significant
discrepancy between the number of rows in the table and the value in
pg_class.reltuples. i forget the exact algorithm off-hand, but it
takes the size of the table into account, and is looking for a %age
difference between the value, not a set number of rows.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.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] planned recovery from a certain transaction

[Richard Huxton]

Chris Spotts wrote:


The transaction itself works flawlessly, but every once and awhile the data
the it uploads from comes in flawed and we have to find a way to reset it.
This reset involves restoring a backup that was taken right before the proc
started.   If we had the xid of the long running transaction, is there a
better way to reset it right before that transaction happened?  Restoring
the backup is a lengthy process because several of the tables that are
affected are rather large.


No way really to "rewind" to a previous transaction (although I believe 
the original academic code PostgreSQL is based on could do this sort of 
thing).


I'd look at doing a PITR backup (full+WAL) just before the long 
transaction is started. Alternatively, if you can afford the downtime 
you could just stop the database server and take a snapshot of all the 
DB files (as for PITR).


Use of rsync or filesystems that handle snapshots would make both of 
these reasonably fast. Restores are just a matter of moving the files / 
pointing PG at the backup set and starting it - effectively instant.


--
  Richard Huxton
  Archonet Ltd

--
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] Vacuum on the database versus individual tables.

[Merlin Moncure]

On Thu, Jun 25, 2009 at 10:40 AM, Hartman,
Matthew wrote:
> Good morning.
>
>
>
> On occasion I’ll perform a full vacuum on a database but will still receive
> a suggestion to vacuum an individual table immediately after. Does the full
> database vacuum not handle each individual table?

As of 8.3, for the most part you can trust autovacuum to do its thing
if it's enabled...there are exceptions to this but I'm curious why you
are doing 'vacuum full'.

merlin

-- 
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] planned recovery from a certain transaction

[Alan Hodgson]

On Thursday 25 June 2009, "Chris Spotts"  wrote:
> The transaction itself works flawlessly, but every once and awhile the
> data the it uploads from comes in flawed and we have to find a way to
> reset it. This reset involves restoring a backup that was taken right
> before the proc started.   If we had the xid of the long running
> transaction, is there a better way to reset it right before that
> transaction happened?  Restoring the backup is a lengthy process because
> several of the tables that are affected are rather large.
>

PITR would permit you to restore the database to a point in time or a 
transaction ID. You could probably do something with filesystem snapshots  
to minimize backup/restore time for it.


-- 
WARNING:  Do not look into laser with remaining eye.

-- 
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] Vacuum on the database versus individual tables.

[Hartman, Matthew]

But it'll do so immediately after I run a full vacuum on the entire database? 
Nothing has changed. This is a development box.

You know, I bet it doesn't refresh the view of the database after having run 
the maintenance script..

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549- x4294 
 

-Original Message-
From: Dave Page [mailto:dp...@pgadmin.org] 
Sent: Thursday, June 25, 2009 12:00 PM
To: Grzegorz Jaśkiewicz
Cc: Hartman, Matthew; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Vacuum on the database versus individual tables.

2009/6/25 Grzegorz Jaśkiewicz :
> 2009/6/25 Hartman, Matthew :
>> Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical 
>> "Running vacuum on this table is recommended" dialog box.
>
> Well, it really has nothing to do with postgresql it self. Either it
> is a bug or property of PgAdmin, but I don't know.
> Either someone who knows more about pgadmin is going to respond here,
> or you have to ask on pgadmin list.

pgAdmin will advise vacuuming a table if there is a significant
discrepancy between the number of rows in the table and the value in
pg_class.reltuples. i forget the exact algorithm off-hand, but it
takes the size of the table into account, and is looking for a %age
difference between the value, not a set number of rows.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.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] Vacuum on the database versus individual tables.

[Tom Lane]

Dave Page  writes:
> pgAdmin will advise vacuuming a table if there is a significant
> discrepancy between the number of rows in the table and the value in
> pg_class.reltuples. i forget the exact algorithm off-hand, but it
> takes the size of the table into account, and is looking for a %age
> difference between the value, not a set number of rows.

What actually counts is the tuple density (ie, the reltuples/relpages
ratio).  I would hope that it's checking whether that is off from
reality, not whether the absolute value is correct.  Otherwise it's
going to be recommending useless vacuums.

(Actually, I'm not sure such a functionality is needed at all anymore
unless the user has disabled autovacuum...)

regards, tom lane

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

Re: [GENERAL] Vacuum on the database versus individual tables.

[Hartman, Matthew]

I do have autovacuum on (as of yesterday). This was discovered when I
ran vacuum on a whim.


Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549- x4294 
 

-Original Message-
From: Merlin Moncure [mailto:mmonc...@gmail.com] 
Sent: Thursday, June 25, 2009 12:10 PM
To: Hartman, Matthew
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Vacuum on the database versus individual tables.

On Thu, Jun 25, 2009 at 10:40 AM, Hartman,
Matthew wrote:
> Good morning.
>
>
>
> On occasion I'll perform a full vacuum on a database but will still
receive
> a suggestion to vacuum an individual table immediately after. Does the
full
> database vacuum not handle each individual table?

As of 8.3, for the most part you can trust autovacuum to do its thing
if it's enabled...there are exceptions to this but I'm curious why you
are doing 'vacuum full'.

merlin


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

[GENERAL] datestyle no effect in 8.3.7

[johnf]

I added "datestyle = 'SQL,MDY' to the postgres.conf (restarted the server).  
But it does not appear to work with any setting.  Is this a bug?   Did I do 
it wrong?

openSUSE 11.0 postgres 8.3.7
-- 
John Fabiani

-- 
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] horizontal sharding

[Scott Marlowe]

On Thu, Jun 25, 2009 at 12:03 AM,  wrote:
> GOals are to scale our game for 1 simultaneous connection to db
> right now db crashing at 500 or 1000 sim connections

Definitely look into pooling.  I've got a pretty hefty database server
(16 15k5 drives, HW RAID, 32Gig RAM and 8 cores) and I wouldn't DREAM
of trying to handle 10,000 simo connections with it.

-- 
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] Vacuum on the database versus individual tables.

[Scott Marlowe]

On Thu, Jun 25, 2009 at 8:40 AM, Hartman,
Matthew wrote:
> Good morning.
>
> On occasion I’ll perform a full vacuum on a database but will still receive
> a suggestion to vacuum an individual table immediately after. Does the full
> database vacuum not handle each individual table?

Wait, is this a regular vacuum against the whole database, or a vacuum
FULL against the whole database? Or a vacuum full on an individual
table.  Vacuum full is generally not recommended for regular
maintenance, but only for getting out of bloat problems that regular
vacuum can no longer handle.  It is generally best followed by a
reindex to clean up the indexes.

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

[GENERAL] Need suggestions

[Jack W]

I will create several databases on PostGreSQL. All the databases have the
same structure: same number of table/index.
I have two choices:
1. For each database, I create a new tablespace and create a new database in
the tablespace.
2. I only create one tablespace. Create all the databases on the same
tablespace.

What is the advantage and disadvantage of the two choices? For the first
choice, different database locates in different physical directory on the
hard drive. For the second choice, all the database locate in the same
physical directory.

Another possibility is to create a new "database cluster directory" for each
database. Then each database is managed by different database server
instance using different connection.

Which way is better? Thanks a lot.

Re: [GENERAL] Need suggestions

[Bill Moran]

In response to Jack W :

> I will create several databases on PostGreSQL. All the databases have the
> same structure: same number of table/index.
> I have two choices:
> 1. For each database, I create a new tablespace and create a new database in
> the tablespace.
> 2. I only create one tablespace. Create all the databases on the same
> tablespace.
> 
> What is the advantage and disadvantage of the two choices? For the first
> choice, different database locates in different physical directory on the
> hard drive. For the second choice, all the database locate in the same
> physical directory.
> 
> Another possibility is to create a new "database cluster directory" for each
> database. Then each database is managed by different database server
> instance using different connection.
> 
> Which way is better? Thanks a lot.

Depends on what you're trying to accomplish, which you don't state.

The typical reason for tablespaces would be to store different parts of
your database cluster on different physical storage, thus a hard drive
being saturated with writes doesn't slow down other tables that are on
a completely different hard drive.  I can't think of many other reasons
to use tablespaces.

The typical reason for running multiple instances is that the global
settings must change, i.e. the listening port or listening address
must be different, or the roles and server-wide config settings must
be different.

Without knowing what problem you're trying to solve, I can't recommend
one or the other, but hopefully the previous paragraphs will help.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] Problems with postgres online backup - restore

[Gerhard Wiesinger]

Hello,

I'd like to understand the PostgreSQL internals in "backup mode".

When I understood it correctly pg_start_backup() make a checkpoint and 
stops writing to the data/ directory except the WAL.


All new transaction go into WAL which is also logical. But how is data 
consistency done when the written/changed blocks don't fit into the buffer 
cache?


E.g.
A lot of updates which exceed buffer cache (Where is data written except 
WAL since data should be kept constant?)

SELECT FROM updated data: WHERE is the new data fetched from?

Thnx for any explainations.

Ciao,
Gerhard

--
http://www.wiesinger.com/


On Thu, 25 Jun 2009, Chris Barnes wrote:



SELECT pg_switch_xlog();

SELECT pg_start_backup('postres_full_backup_June222009');

tar -czvf pgprd01_June22_2009_production.dmp.tar.gz data/

SELECT pg_stop_backup();



--
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] Need suggestions

[Bill Moran]

In response to Jack W :

> On Thu, Jun 25, 2009 at 11:37 AM, Bill Moran wrote:
> 
> > In response to Jack W :
> >
> > > I will create several databases on PostGreSQL. All the databases have the
> > > same structure: same number of table/index.
> > > I have two choices:
> > > 1. For each database, I create a new tablespace and create a new database
> > in
> > > the tablespace.
> > > 2. I only create one tablespace. Create all the databases on the same
> > > tablespace.
> > >
> > > What is the advantage and disadvantage of the two choices? For the first
> > > choice, different database locates in different physical directory on the
> > > hard drive. For the second choice, all the database locate in the same
> > > physical directory.
> > >
> > > Another possibility is to create a new "database cluster directory" for
> > each
> > > database. Then each database is managed by different database server
> > > instance using different connection.
> > >
> > > Which way is better? Thanks a lot.
> >
> > Depends on what you're trying to accomplish, which you don't state.
> >
> > The typical reason for tablespaces would be to store different parts of
> > your database cluster on different physical storage, thus a hard drive
> > being saturated with writes doesn't slow down other tables that are on
> > a completely different hard drive.  I can't think of many other reasons
> > to use tablespaces.
> >
> > The typical reason for running multiple instances is that the global
> > settings must change, i.e. the listening port or listening address
> > must be different, or the roles and server-wide config settings must
> > be different.
> >
> > Without knowing what problem you're trying to solve, I can't recommend
> > one or the other, but hopefully the previous paragraphs will help.
> 
> Thanks for your reply.

Keep the mailing list in your replies.

> The problem I want to solve is a typical web application. I want to use
> several databases on the server side to store information for different
> departments. For example, one database for sales department; one database
> for HR department. And all the databases have the same structure/schema.
> 
> If considering performance, which way is better?

Multiple database instances will fragment memory and hurt both Postgres'
and the OS' ability to use memory efficiently.  Do not use multiple
database instances if performance is a major goal.

> If I use one database server instance to manage all the databases, all the
> databases share the same transaction log. When doing backup/recovery, I need
> to back up or recover all the databases together, right?

False.  pg_dump can back up individual databases.  If you plan on doing
PITR, then you are correct.  However, if you data is so important that
you can justify PITR, you'll want to have a separate server for restore
purposes, and once you've restored you can use other methods to transfer
the data to the live system, picking and choosing what you need.

> I can not shut down
> just one database because all the the databases will be shut down, right?

True, but why are you shutting databases down?

> If I use multiple database server instances, I can back up/recover/shut down
> each database separately.

If you expect to need to do that kind of tinkering often, then I would
recommend going one step further and getting each department it's own
physical (or virtual) server.  If your environment is that unpredictable,
you're probably going to come across other issues, like department A runs
a data import that brings the server to its knees and all the other
departments complain.  However, running multiple instances of Postgres
is one way of solving _some_ of those issues (as you describe).  However,
if you need the granularity of PITR in an environment where things are
that unpredictable, you really need to establish multiple independent
environments.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] Problems with postgres online backup - restore

[Richard Huxton]

Gerhard Wiesinger wrote:

Hello,

I'd like to understand the PostgreSQL internals in "backup mode".

When I understood it correctly pg_start_backup() make a checkpoint and 
stops writing to the data/ directory except the WAL.


All new transaction go into WAL which is also logical. But how is data 
consistency done when the written/changed blocks don't fit into the 
buffer cache?


The data isn't kept consistent. Which is why you need the WAL. Restoring 
from a PITR backup is basically the same idea as recovering from a 
crash. Any blocks that might have been updated since you called 
pg_start_backup() will be rewritten from the WAL.


--
  Richard Huxton
  Archonet Ltd

--
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] Need suggestions

[Jack W]

On Thu, Jun 25, 2009 at 12:10 PM, Bill Moran wrote:

> In response to Jack W :
>
> > On Thu, Jun 25, 2009 at 11:37 AM, Bill Moran  >wrote:
> >
> > > In response to Jack W :
> > >
> > > > I will create several databases on PostGreSQL. All the databases have
> the
> > > > same structure: same number of table/index.
> > > > I have two choices:
> > > > 1. For each database, I create a new tablespace and create a new
> database
> > > in
> > > > the tablespace.
> > > > 2. I only create one tablespace. Create all the databases on the same
> > > > tablespace.
> > > >
> > > > What is the advantage and disadvantage of the two choices? For the
> first
> > > > choice, different database locates in different physical directory on
> the
> > > > hard drive. For the second choice, all the database locate in the
> same
> > > > physical directory.
> > > >
> > > > Another possibility is to create a new "database cluster directory"
> for
> > > each
> > > > database. Then each database is managed by different database server
> > > > instance using different connection.
> > > >
> > > > Which way is better? Thanks a lot.
> > >
> > > Depends on what you're trying to accomplish, which you don't state.
> > >
> > > The typical reason for tablespaces would be to store different parts of
> > > your database cluster on different physical storage, thus a hard drive
> > > being saturated with writes doesn't slow down other tables that are on
> > > a completely different hard drive.  I can't think of many other reasons
> > > to use tablespaces.
> > >
> > > The typical reason for running multiple instances is that the global
> > > settings must change, i.e. the listening port or listening address
> > > must be different, or the roles and server-wide config settings must
> > > be different.
> > >
> > > Without knowing what problem you're trying to solve, I can't recommend
> > > one or the other, but hopefully the previous paragraphs will help.
> >
> > Thanks for your reply.
>
> Keep the mailing list in your replies.
>
> > The problem I want to solve is a typical web application. I want to use
> > several databases on the server side to store information for different
> > departments. For example, one database for sales department; one database
> > for HR department. And all the databases have the same structure/schema.
> >
> > If considering performance, which way is better?
>
> Multiple database instances will fragment memory and hurt both Postgres'
> and the OS' ability to use memory efficiently.  Do not use multiple
> database instances if performance is a major goal.
>
> > If I use one database server instance to manage all the databases, all
> the
> > databases share the same transaction log. When doing backup/recovery, I
> need
> > to back up or recover all the databases together, right?
>
> False.  pg_dump can back up individual databases.  If you plan on doing
> PITR, then you are correct.  However, if you data is so important that
> you can justify PITR, you'll want to have a separate server for restore
> purposes, and once you've restored you can use other methods to transfer
> the data to the live system, picking and choosing what you need.
>

Thanks for your reply.
Yes. I plan to use PITR.


>
> > I can not shut down
> > just one database because all the the databases will be shut down, right?
>
> True, but why are you shutting databases down?
>

Maybe for maintainence purpose or schema change. For example as you
mentioned above, if using several tablespaces located on different hard
drives. If one hard drive is damaged, the database on that hard drive will
not be available. How about other databases managed by the same database
server instance? I need to shut down all the databases to do mainatainence,
right?


>
> > If I use multiple database server instances, I can back up/recover/shut
> down
> > each database separately.
>
> If you expect to need to do that kind of tinkering often, then I would
> recommend going one step further and getting each department it's own
> physical (or virtual) server.  If your environment is that unpredictable,
> you're probably going to come across other issues, like department A runs
> a data import that brings the server to its knees and all the other
> departments complain.  However, running multiple instances of Postgres
> is one way of solving _some_ of those issues (as you describe).  However,
> if you need the granularity of PITR in an environment where things are
> that unpredictable, you really need to establish multiple independent
> environments.
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>

[GENERAL] Schedule up for pgDay San Jose

[Josh Berkus]

Folks,

For any PostgresQL users who live in San Jose, or are planning to attend 
OSCON, we have a full day of PostgreSQL talks on Sunday July 19th.  Full 
details, including free registration information are here:


http://wiki.postgresql.org/wiki/PgDaySanJose2009

--Josh Berkus

--
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] planned recovery from a certain transaction

[Alvaro Herrera]

Chris Spotts escribió:

> The transaction itself works flawlessly, but every once and awhile the data
> the it uploads from comes in flawed and we have to find a way to reset it.
> This reset involves restoring a backup that was taken right before the proc
> started.   If we had the xid of the long running transaction, is there a
> better way to reset it right before that transaction happened?  Restoring
> the backup is a lengthy process because several of the tables that are
> affected are rather large.

You could mark it aborted in pg_clog, assuming none of the tuples it
touched have been examined by anyone else after it finished.  Since you
likely want to crosscheck the data (thus examine it, which sets its hint
bits), it's going to be very hard to do.

Another idea would be to use PITR to restore to the time just before the
transaction, but that's going to be painful too because restoring from a
base backup is going to take long for your big tables.

Lastly, you could use a filesystem snapshot taken just before the long
procedure, to which to revert if you don't like how it went.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] planned recovery from a certain transaction

[Scott Marlowe]

On Thu, Jun 25, 2009 at 9:40 AM, Chris Spotts wrote:
>
>
> We have a database which has one long involved procedure early in the
> morning that updates all sorts of things, moves data around, deletes some
> stuff, alters some DDL - you name it, it does it.  The rest of the day, the
> database is read only.  Autovacuum is not on, it was killing performance for
> it to kick on in the middle of the proc.  Vacuum is run right before the
> long procedure.  We typically wait until the guy onsite verifies the
> procedure went smoothly and then vacuum (Why? Because we’ve read enough of
> “well, you would have been able to restore that if the autovacuum wasn’t
> running”.  We have a backup so this is just a checking before vacuuming is
> technically unneeded.).  Don’t get me wrong, I’m a big autovacuum fan, just
> not for this specific case.
>
>
>
> The transaction itself works flawlessly, but every once and awhile the data
> the it uploads from comes in flawed and we have to find a way to reset it.
>   This reset involves restoring a backup that was taken right before the
> proc started.   If we had the xid of the long running transaction, is there
> a better way to reset it right before that transaction happened?  Restoring
> the backup is a lengthy process because several of the tables that are
> affected are rather large.

Assuming that the data is mostly created from whole cloth each
morning, it might do to have two dbs, and rename one to replace the
other when you're ready.  Gives you 20 or so hours to discover a screw
up and still have the backup db before you toss it away to build the
next day's db.

-- 
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] planned recovery from a certain transaction

[Greg Stark]

>> The transaction itself works flawlessly, but every once and awhile the data
>> the it uploads from comes in flawed and we have to find a way to reset it.

If you can automate the tests for the flaws you can do the whole
transaction itself as one big transaction in Postgres. Even DDL can be
done in transactions in Postgres. Then run the tests, still in the
same transaction, and if you detect anything wrong roll the whole
shebang back.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
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] Add Space symbols for TSvector

[Arjen Nienhuis]

> > preprocess text, for example, using replace(), regexp_replace() functions
>
> Oke, you mean at the time the insert in de db is done, i suppose.
> However, I'm using the new syslog-ng 3 feature which does the
> insert directly. So I have to change the syslogd for that.
>
> I understand the ts_search parser is not configurable?
>
>
you can make function f(s) -> replace(replace(s, foo, bar), baz, bar)

and then search and index on f(s)

CREATE INDEX ... ON table ((f(s)));
SELECT s FROM table WHERE f(s) "matches?" '127.0.0.1';

Re: [GENERAL] planned recovery from a certain transaction

[Chris Spotts]

> 
> Assuming that the data is mostly created from whole cloth each
> morning, it might do to have two dbs, and rename one to replace the
> other when you're ready.  Gives you 20 or so hours to discover a screw
> up and still have the backup db before you toss it away to build the
> next day
For this database, we're dumping in the neighborhood of 75GB of data
each morning.  This is mostly data warehousing.
I'm not quite sure how effective its going to be to do a file system
snapshot since the whole db is so large.

The aborted transaction in the pg_clog sounds similar to what I was
dreaming of, but "very hard to do" doesn't sound real promising.  

Here's the approach we've come up with today to help combat it and let
me know you're thoughts on it.

We're mostly  warehousing of logistics data.  So we have an import table
that the files are uploaded to.  We have a set of tables for today's
data processing, a set of tables for the results of yesterday (we do
some crossday day comparisions that make it needed), and a set of
partitioned archive tables.

The data is uploaded into the import table, the previous day tables are
moved to history, and then the current day tables are moved to previous
day, leaving the current day tables ready to be populated.

The import table is s then normalized into several current day tables,
mostly split into dynamic and static data.  Some fields are normalized
to reduce size, ie we store a city_id int instead of a 60 character
field.  Reports and stats are generated off the current days records and
the comparision of the two days. 

Because we calculate certain things like durations of certain statuses,
its not as simple as just "delete everything with a certain date and
then copy the appropriate dates from history into current and previous
respectively." We effectively alter some fields that cannot be
"unaltered" short of a restore of some kind.  Due to speed concerns,
we've not found a way around this.  We'd like to store the needed fields
to calculate the durations after end of the duration, but that can be
quite lengthy.  Instead triggers update a table that tracks key events.

This all is what we currently do.
This means that we'd have to have a HUGE filesystem snapshot to do it
like that.  To do a pg_dump restore, we have to restore one of the
partitions that is 250GB (2 weeks of data partitioned) - its a lengthy
process.

We're looking at adding a set of daily queue tables that sits right
before archive.  Writing the files to these tables and then if
everything checks out, dump to history.  This way, we should only ever
have to have a dump of the current, previous, and queue tables to be
able to "undo".  Suggestions?


signature.asc
Description: This is a digitally signed message part

[GENERAL] Re planned recovery from a certain transaction

[Chris Spotts]

On Thu, 2009-06-25 at 21:59 +0100, Greg Stark wrote:
> >> The transaction itself works flawlessly, but every once and awhile the data
> >> the it uploads from comes in flawed and we have to find a way to reset it.
> 
> If you can automate the tests for the flaws you can do the whole
> transaction itself as one big transaction in Postgres. Even DDL can be
> done in transactions in Postgres. Then run the tests, still in the
> same transaction, and if you detect anything wrong roll the whole
> shebang back.
> 
We automate dozens of tests for the data, very first thing all in one
large transaction.  We love the fact that it undoes DDL and have taken
full advantage of it. But we get "these stats don't look right" and then
go look at the import and find out that the context of the data was
incorrect.  I can't imagine how to write the tests for the stuff we find
wrong.  If anybody has an extra AI system laying around, let me know ;).


signature.asc
Description: This is a digitally signed message part

[GENERAL] Custom runtime variables

[Scott Bailey]

I want to be able to change the behavior of some functions based on 
custom runtime variables.


I added the following lines to my postgresql.conf file:

custom_variable_classes = 'foo'
foo.name = '1s'

Now if I do "show foo.name" I get '1s'
But it does not show up in show all or in pg_settings.

I didn't find much in the way of documentation. How does one access the 
value of foo.name from a plpgsql or sql function?


Also, can the variable be stored in a specific data type or am I stuck 
with text?




--
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] Custom runtime variables

[Tom Lane]

Scott Bailey  writes:
> I added the following lines to my postgresql.conf file:
> custom_variable_classes = 'foo'
> foo.name = '1s'

> Now if I do "show foo.name" I get '1s'
> But it does not show up in show all or in pg_settings.

No, it doesn't.  It should still work in set/show though.

> Also, can the variable be stored in a specific data type or am I stuck 
> with text?

There's no data type associated with it.  This is all an artifact of the
fact that this isn't a genuine designed-in feature, but a kluge that
someone shoehorned in.  You should have to declare the variable in some
fashion...

regards, tom lane

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

Re: [GENERAL] Problems with postgres online backup - restore

[Gerhard Wiesinger]

Hello,

OK, what's then the difference doing a pg_start_backup() or just 
doing the backup?


Isn't that a problem that the datablock are very inconsistent, even so 
inconsistent that they are corrupt:


E.g. A part of a datablock is written when e.g. the file is tarred. => 
Datablock on backup is corrupt => An then even the WAL can't be applied.


Why does it work correctly? Or is there some design problem?

Thnx.

Ciao,
Gerhard

--
http://www.wiesinger.com/


On Thu, 25 Jun 2009, Richard Huxton wrote:


Gerhard Wiesinger wrote:

Hello,

I'd like to understand the PostgreSQL internals in "backup mode".

When I understood it correctly pg_start_backup() make a checkpoint and 
stops writing to the data/ directory except the WAL.


All new transaction go into WAL which is also logical. But how is data 
consistency done when the written/changed blocks don't fit into the buffer 
cache?


The data isn't kept consistent. Which is why you need the WAL. Restoring from 
a PITR backup is basically the same idea as recovering from a crash. Any 
blocks that might have been updated since you called pg_start_backup() will 
be rewritten from the WAL.


--
 Richard Huxton
 Archonet Ltd

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



--
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] [JDBC] Postgres 8.3.7 -- EOF on client connection

[CM J]

Hi,

   The problem turned out to be not with postgres but my JDBC connection
code.

Thanks !

On Thu, Jun 25, 2009 at 5:25 PM, Saurabh Dave  wrote:

> Hi CMJ,
>
> If you are using WebNMS framework with hibernate, can you check if you have
> done this:
>
> *ConfigReader configReader = ConfigReader.getInstance();
> configReader.modifyHbmToDdl(true);
> *
>
> Thanks,
> Saurabh
>
>
> On Wed, Jun 24, 2009 at 6:06 PM, CM J  wrote:
>
>> Hi ,
>>
>>   I installed postgres 8.3.7-1 in my windows machine. The server
>> starts all right.However when i attempt a JDBC connection(Driver used:
>> postgresql-8.3-604.jdbc4.jar), the following error is thrown:
>>
>> 2009-06-24 17:55:03.564 ISTERROR:  relation "befailover" does not exist
>> 2009-06-24 17:55:03.564 ISTSTATEMENT:  SELECT HOSTADDRESS,RMIREGISTRYPORT
>> FROM BEFAILOVER WHERE SERVERROLE='PRIMARY'
>> 2009-06-24 17:55:03.580 ISTERROR:  relation "befailover" does not exist
>> 2009-06-24 17:55:03.580 ISTSTATEMENT:  DELETE  FROM BEFAILOVER WHERE
>> SERVERROLE='STANDBY'
>> *2009-06-24 17:55:12.639 ISTLOG:  could not receive data from client: No
>> connection could be made because the target machine
>> actively refused it.
>> 2009-06-24 17:55:12.639 ISTLOG:  unexpected EOF on client connection*
>>
>> I attempt the JDBC connection from the machine as the postgres
>> server. There are no anti-virus server software or firewall involved. Can
>> someone tell me what could be wrong with my setup.
>>
>> Thanks.
>>
>
>

Re: [GENERAL] Problems with postgres online backup - restore

[Richard Huxton]

Gerhard Wiesinger wrote:

Hello,

OK, what's then the difference doing a pg_start_backup() or just doing 
the backup?


pg_start_backup() forces a checkpoint (and logs a label for your backup 
too).


Isn't that a problem that the datablock are very inconsistent, even so 
inconsistent that they are corrupt:


E.g. A part of a datablock is written when e.g. the file is tarred. => 
Datablock on backup is corrupt => An then even the WAL can't be applied.


Why does it work correctly? Or is there some design problem?


It works because the WAL doesn't hold a list of row updates ("update row 
12345 set field 4 = true") but block updates. Any update to a disk block 
is recorded - table or index. The WAL doesn't really know anything about 
 tables, columns, primary keys etc - just disk blocks.


One small optimisation is that the first time a block is touched after a 
checkpoint the value of the whole block is written to WAL and after that 
only updates.


So - if you have a checkpointed system (all updates guaranteed written 
to disk) and a complete set of WAL files from that point on you can 
always recreate the writes to any point in time after that.


http://www.postgresql.org/docs/8.3/static/continuous-archiving.html

--
  Richard Huxton
  Archonet Ltd

--
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] Can't start postgresql 8.3.7

[Sam Wun]

I copied the sample config file to postgresql workign directory before
trying to start postgresql:
# pwd
/usr/local/share/postgresql
twp1:postgresql # cp postgresql.conf.sample ../../pgsql/data/postgresql.conf

On Fri, Jun 26, 2009 at 4:04 PM, Sam Wun wrote:
> Hi,
>
> I had been running postgresql 8.3.7 in freebsd for some time, but
> after I changed its postgresql.conf file, it can't start any more
> eventhoguh I changed the config back to its original file.
> Anything wrong?
>
> Here is the errros:
>
> Jun 26 15:58:52 twp1 postgres[1394]: [1-1] FATAL:  could not create
> semaphores: No space left on device
> Jun 26 15:58:52 twp1 postgres[1394]: [1-2] DETAIL:  Failed system call
> was semget(5432004, 17, 03600).
> Jun 26 15:58:52 twp1 postgres[1394]: [1-3] HINT:  This error does
> *not* mean that you have run out of disk space.
> Jun 26 15:58:52 twp1 postgres[1394]: [1-4]      It occurs when either
> the system limit for the maximum number of semaphore sets (SEMMNI), or
> the system wide maximum number of
> Jun 26 15:58:52 twp1 postgres[1394]: [1-5]  semaphores (SEMMNS), would
> be exceeded.  You need to raise the respective kernel parameter.
> Alternatively, reduce PostgreSQL's
> Jun 26 15:58:52 twp1 postgres[1394]: [1-6]  consumption of semaphores
> by reducing its max_connections parameter (currently 103).
> Jun 26 15:58:52 twp1 postgres[1394]: [1-7]      The PostgreSQL
> documentation contains more information about configuring your system
> for PostgreSQL.
>
> Very appreciate for any suggestion.
> 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] Can't start postgresql 8.3.7

[Sam Wun]

Hi,

I had been running postgresql 8.3.7 in freebsd for some time, but
after I changed its postgresql.conf file, it can't start any more
eventhoguh I changed the config back to its original file.
Anything wrong?

Here is the errros:

Jun 26 15:58:52 twp1 postgres[1394]: [1-1] FATAL:  could not create
semaphores: No space left on device
Jun 26 15:58:52 twp1 postgres[1394]: [1-2] DETAIL:  Failed system call
was semget(5432004, 17, 03600).
Jun 26 15:58:52 twp1 postgres[1394]: [1-3] HINT:  This error does
*not* mean that you have run out of disk space.
Jun 26 15:58:52 twp1 postgres[1394]: [1-4]  It occurs when either
the system limit for the maximum number of semaphore sets (SEMMNI), or
the system wide maximum number of
Jun 26 15:58:52 twp1 postgres[1394]: [1-5]  semaphores (SEMMNS), would
be exceeded.  You need to raise the respective kernel parameter.
Alternatively, reduce PostgreSQL's
Jun 26 15:58:52 twp1 postgres[1394]: [1-6]  consumption of semaphores
by reducing its max_connections parameter (currently 103).
Jun 26 15:58:52 twp1 postgres[1394]: [1-7]  The PostgreSQL
documentation contains more information about configuring your system
for PostgreSQL.

Very appreciate for any suggestion.
Thanks

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