[GENERAL] [OT] Slony (initial) Replication - Slow

[Ow Mun Heng]

{resend as don't see it on the list after 4 hours}

I'm just wetting my hands with slony and during the setup of the slave,
I did and dump and restore of the master DB to the Slave DB.

However during the startup of slony, I noticed that it issues a truncate
command to the (to be) replicated table. Hence, this means that there's
no such need for me to do a dump/restore in the 1st place.

can someone confirm this? It _is_ taking long time (for slony) to do the
\copy (~60GB in multiple tables being replicated, including (on the fly)
index creation)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Can't make backup

[Sebastián Baioni]

I don't have any table called pg_locks in pg_catalog.
We let running pg_dump 8.2.5 at 1:30 AM after an automatic reboot of the 
PostgreSQL service and it didn't finish. When I came to the office at 9:00 
there were a lot of locks, but all of them were from the pg_dump conection.

Thanks
 Sebastián

Albe Laurenz <[EMAIL PROTECTED]> escribió: Sebastián Baioni wrote:
> Every day we run a Windows Programmed pg_dump, it used to 
> work fine with PostgreSQL 8.0, but since we installed the new 
> version we are not able to make a backup. We tried to make a 
> whole database backup and it never ends. We tried to backup 
> table by table and it reachs a table of 50,000 rows and it 
> keeps for hours in that table, we started it again and 
> pg_dump backups that same table in seconds, but it stops in 
> another different table.
> If we watch the server message.# we don't have any error, 
> it's like some tables were locked and pg_dump would keep 
> waiting for the table to be unlocked.

If pg_dump is locked out, there should be entries in
pg_catalog.pg_locks. Check when the dump hangs!

Does the dump file keep growing or not?

Yours,
Laurenz Albe


   
-

Los referentes más importantes en compra/venta de autos se juntaron:
Demotores y Yahoo!.Ahora comprar o vender tu auto es más fácil. 
 Visitá http://ar.autos.yahoo.com/

[GENERAL] array as arguments of pg functions and php

[Ivan Sergio Borgonovo]

I've read through some solutions proposed on ml, blogs etc... but
maybe something changed or someone came up with a better idea or just
I wasn't able to find a satisfactory solution through google...

Of course PHP arrays can't be passed directly to pg functions so:

I can:
1) write a php function that prepare the array as a string with
proper escaping
2) pass a text string and "number of elements" and let the pg
function parse the text
3) create a temp table and pass it to the function (?)

Can anything be done with prepared statement and pg_execute?
Can pg functions accept variable number of arguments?

I'm going to go with 1) but well it would be nice if I discover
something nicer.

thx

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] basic questions: Postgres with yum on CentOS 5.1

[Tomasz Ostrowski]

On Tue, 01 Jan 2008, Chuck wrote:

> I'm not sure how to "make sure automatic updates are turned on" as
> Tometzky recommended. Is that a yum setting?

You need to install and configure "yum-updatesd" to perform automatic
updates for you. I don't use it so I don't know exactly how to do
this, but I believe it has a well documented configuration file in
/etc/.

> [EMAIL PROTECTED] ~]# service postgresql start
> Initializing database:   [ OK ]
> When I listed the current databases, I found out that UTF-8 is not being used.

You did not set your /etc/sysconfig/i18n and reboot before you first
started, ignoring my recommendation. I'd delete /var/lib/pgsql/data
(if there's no data yet) and try again after this setting and reboot.

> I need to store multiple languages in my database such as English, French 
>
> The end of the "21.2.2. Setting the Character Set" section says, "One way 
> to use multiple encodings safely is to set the locale to C or POSIX during 
> initdb, thus disabling any real locale awareness."

This is a very bad solution, as it would allow you to store any
garbage string in a database. It won't know letter boundaries, so any
text functions will misbehave badly. When your database encoding is
UTF-8 then you'll be forced to save consistant UTF-8 strings and
sorting, text functions, regular expressions etc... will work as
expected.

Regards
Tometzky
-- 
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
  Winnie the Pooh

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Table auditing / Pg/tcl help

[Glyn Astill]

Hi people,

Are any of you lot handy with pgtcl then? Or should I be posting on
another list, the only other list I could possible see was
pgsql-hackers?

I managed to modify the tcl script at the address I posted below to
save the field I wanted it to. However I've still not managed to get
it to save the names and values of all my primary keys. All I've
managed to do is create a script that does nothing.

I've attached it, could someone help?

Thanks
Glyn


--- Glyn Astill <[EMAIL PROTECTED]> wrote:

> Hi people,
> 
> I've been trying to set up table auditing using a tcl function and
> a
> trigger. I followed the guide here to start with:
> 
> http://www.alberton.info/postgresql_table_audit.html
> 
> The thing is we have multiple fields in our primary keys so I need
> to
> change it to handle them all. I was thinking for now to just set
> pk_name like "fieldName1,field2Name" and pk_value to
> "fieldvalue1,fieldvalue2" etc.
> 
> The script runs the query:
> 
> "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a,
> pg_index i WHERE c.relname = '$tgname' AND c.oid=i.indrelid AND
> a.attnum > 0 AND a.attrelid = i.indexrelid AND i.indisprimary='t'"
> 
> which should produce a list of the values. It then does:
> 
> #get PK value
> foreach field $TG_relatts {
>   if {[string equal -nocase [lindex [array get NEW $field] 0]
> $pk_name]} {
> set pk_value [lindex [array get NEW $field] 1]
> break;
>   }
> }
> 
> Which I presume just gets the first value, then breaks. I want to
> get
> them all and put them into a comma separated string. So (baering in
> mind I've not touched tcl before) I changed this to:
> 
> #get PK value
> foreach field $TG_relatts {
>   if {[string equal -nocase [lindex [array get NEW $field] 0]
> $pk_name]} {
> if {[string length $pk_value] > 0} {
>   append pk_value "," [lindex [array get NEW $field] 1]
>   append pk_list "," $pk_name
>   } else {
>   set pk_value [lindex [array get NEW $field] 1]
>   set pk_list $pk_name
>   }
> #break;
>   }
> }
> 
> But it didn't work, so I guess I'm missing something.
> 
> 
> 
> 
> 
> 
> 
> 
>   ___
> Support the World Aids Awareness campaign this month with Yahoo!
> For Good http://uk.promotions.yahoo.com/forgood/
> 
> 
> ---(end of
> broadcast)---
> TIP 6: explain analyze is your friend
> 



  ___
Support the World Aids Awareness campaign this month with Yahoo! For Good 
http://uk.promotions.yahoo.com/forgood/

Audit_multiple_pk.tcl
Description: 3518590780-Audit_multiple_pk.tcl

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] [OT] Slony (initial) Replication - Slow

[Bill Moran]

In response to Ow Mun Heng <[EMAIL PROTECTED]>:
> 
> I'm just wetting my hands with slony and during the setup of the slave,
> I did and dump and restore of the master DB to the Slave DB.
> 
> However during the startup of slony, I noticed that it issues a truncate
> command to the (to be) replicated table. Hence, this means that there's
> no such need for me to do a dump/restore in the 1st place.
> 
> can someone confirm this?

Confirmed.  It's how Slony is designed to work.

> It _is_ taking long time (for slony) to do the
> \copy (~60GB in multiple tables being replicated, including (on the fly)
> index creation)

1) It only needs to be done once
2) You can remove the indexes from the replica and add them back in after
   the initial sync is complete.

-- 
Bill Moran
http://www.potentialtech.com

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] C-Extenions for PostgreSQL, Call Convention Version 0

[Harald Armin Massa]

Hello,

I am researching information about writing C-language-Functions for
PostgreSQL.  I came accross the slides of an OSCON 2004 tutorial of
Joe Conway
http://www.joeconway.com/tut_oscon_2004.pdf

where he states:
Version 0 Calling Convention
• Deprecated
• Has been since the 7.1 release.
• Don't use it.

Within the PostgreSQL-Documentation for 8.2,
http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html, still I
find:

"""Using call conventions version 0, we can define c_overpaid as:"""

That sound very tutorial-like; and so $I wonder: is those version 0
depreciated? Or ist there still value in learning it?


Harald

-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Table auditing / Pg/tcl help

[Glyn Astill]

Also are there any better ways to debug tcl scripts in postgres?

I've just noticed the script I'm using (and the one on the example
site) works for a single row delete, but not for more than 1 row.
It's hard to see from the error given in postgres what's going off.

E.g.

DELETE FROM "MYTABLE" WHERE "ID" = 1

Deletes one row, and updates my audit table, but

DELETE FROM "MYTABLE" WHERE "ID" >= 1

Deletes the rows, but gives an error on logging the audit table.

Anyone got any ideas on good ways of auditing table opperations?

--- Glyn Astill <[EMAIL PROTECTED]> wrote:

> Hi people,
> 
> Are any of you lot handy with pgtcl then? Or should I be posting on
> another list, the only other list I could possible see was
> pgsql-hackers?
> 
> I managed to modify the tcl script at the address I posted below to
> save the field I wanted it to. However I've still not managed to
> get
> it to save the names and values of all my primary keys. All I've
> managed to do is create a script that does nothing.
> 
> I've attached it, could someone help?
> 
> Thanks
> Glyn
> 
> 
> --- Glyn Astill <[EMAIL PROTECTED]> wrote:
> 
> > Hi people,
> > 
> > I've been trying to set up table auditing using a tcl function
> and
> > a
> > trigger. I followed the guide here to start with:
> > 
> > http://www.alberton.info/postgresql_table_audit.html
> > 
> > The thing is we have multiple fields in our primary keys so I
> need
> > to
> > change it to handle them all. I was thinking for now to just set
> > pk_name like "fieldName1,field2Name" and pk_value to
> > "fieldvalue1,fieldvalue2" etc.
> > 
> > The script runs the query:
> > 
> > "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a,
> > pg_index i WHERE c.relname = '$tgname' AND c.oid=i.indrelid AND
> > a.attnum > 0 AND a.attrelid = i.indexrelid AND
> i.indisprimary='t'"
> > 
> > which should produce a list of the values. It then does:
> > 
> >   #get PK value
> >   foreach field $TG_relatts {
> > if {[string equal -nocase [lindex [array get NEW $field] 0]
> > $pk_name]} {
> >   set pk_value [lindex [array get NEW $field] 1]
> >   break;
> > }
> >   }
> > 
> > Which I presume just gets the first value, then breaks. I want to
> > get
> > them all and put them into a comma separated string. So (baering
> in
> > mind I've not touched tcl before) I changed this to:
> > 
> >   #get PK value
> >   foreach field $TG_relatts {
> > if {[string equal -nocase [lindex [array get NEW $field] 0]
> > $pk_name]} {
> >   if {[string length $pk_value] > 0} {
> > append pk_value "," [lindex [array get NEW $field] 1]
> > append pk_list "," $pk_name
> >   } else {
> > set pk_value [lindex [array get NEW $field] 1]
> > set pk_list $pk_name
> >   }
> >   #break;
> > }
> >   }
> > 
> > But it didn't work, so I guess I'm missing something.
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> >   ___
> > Support the World Aids Awareness campaign this month with Yahoo!
> > For Good http://uk.promotions.yahoo.com/forgood/
> > 
> > 
> > ---(end of
> > broadcast)---
> > TIP 6: explain analyze is your friend
> > 
> 
> 
> 
>   ___
> Support the World Aids Awareness campaign this month with Yahoo!
> For Good http://uk.promotions.yahoo.com/forgood/> 
> ---(end of
> broadcast)---
> TIP 6: explain analyze is your friend
> 



  ___
Support the World Aids Awareness campaign this month with Yahoo! For Good 
http://uk.promotions.yahoo.com/forgood/


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Can't make backup

[Albe Laurenz]

Please, don't top post!

Sebastián Baioni wrote:
>>> Every day we run a Windows Programmed pg_dump, it used to 
>>> work fine with PostgreSQL 8.0, but since we installed the new 
>>> version we are not able to make a backup. We tried to make a 
>>> whole database backup and it never ends. We tried to backup 
>>> table by table and it reachs a table of 50,000 rows and it 
>>> keeps for hours in that table, we started it again and 
>>> pg_dump backups that same table in seconds, but it stops in 
>>> another different table.
>>> If we watch the server message.# we don't have any error, 
>>> it's like some tables were locked and pg_dump would keep 
>>> waiting for the table to be unlocked.
>>
>> If pg_dump is locked out, there should be entries in
>> pg_catalog.pg_locks. Check when the dump hangs!
>
> I don't have any table called pg_locks in pg_catalog.

It is a view.

> We let running pg_dump 8.2.5 at 1:30 AM after an automatic 
> reboot of the PostgreSQL service and it didn't finish. When I 
> came to the office at 9:00 there were a lot of locks, but all 
> of them were from the pg_dump conection.

Is there any row in pg_locks that has a "false" in "granted"?
That would be a wait for a lock to be released.

Does pg_dump consume any processor cycles?

You say "Windows Programmed pg_dump" - what exactly is that?
Can you use a debugger to see where your program hangs?

Yours,
Laurenz Albe

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Can't make backup

[Erik Jones]

On Jan 3, 2008, at 5:59 AM, Sebastián Baioni wrote:


I don't have any table called pg_locks in pg_catalog.
We let running pg_dump 8.2.5 at 1:30 AM after an automatic reboot  
of the PostgreSQL service and it didn't finish. When I came to the  
office at 9:00 there were a lot of locks, but all of them were from  
the pg_dump conection.


The pg_locks view most certainly exists.  Here's the list of all of  
the system catalog tables and view:  http://www.postgresql.org/docs/ 
8.2/interactive/catalogs.html.  If you haven't looked at pg_locks how  
do you know "there were a lot of locks"?


Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] C-Extenions for PostgreSQL, Call Convention Version 0

[Pavel Stehule]

Hello

version 0 Calling Convention is really deprecated. From 8.2 manual:

We present the "old style" calling convention first — although this
approach is now deprecated, it's easier to get a handle on initially.

Regards
Pavel Stehule


On 03/01/2008, Harald Armin Massa <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I am researching information about writing C-language-Functions for
> PostgreSQL.  I came accross the slides of an OSCON 2004 tutorial of
> Joe Conway
> http://www.joeconway.com/tut_oscon_2004.pdf
>
> where he states:
> Version 0 Calling Convention
> • Deprecated
> • Has been since the 7.1 release.
> • Don't use it.
>
> Within the PostgreSQL-Documentation for 8.2,
> http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html, still I
> find:
>
> """Using call conventions version 0, we can define c_overpaid as:"""
>
> That sound very tutorial-like; and so $I wonder: is those version 0
> depreciated? Or ist there still value in learning it?
>
>
> Harald
>
> --
> GHUM Harald Massa
> persuadere et programmare
> Harald Armin Massa
> Spielberger Straße 49
> 70435 Stuttgart
> 0173/9409607
> fx 01212-5-13695179
> -
> EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Feature request: NOTIFY enhancement

[Bruce Momjian]

> I am not sure if I am asking too much but does it make sense, and is it
> possible, to enhance NOTIFY that process "name/value" pair? Like this:
> 
> NOTIFY "MyName=MyValue";
> 
> With the capability of name/value pair, the listener will not have to
> check notification details by reading tables.

TODO already has:

* Add optional textual message to NOTIFY

  This would allow an informational message to be added to the notify
  message, perhaps indicating the row modified or other custom
  information.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Feature request: NOTIFY enhancement

[CN]

Dear developers,

With the existing single-notification-value mechanism, not only
SELECT'ing tables are necessary in some cases but DELETE'ing rows and
synchronizing the DELETE are also required in many cases where there are
multiple client connections to the same database.

As an example, supposing there are 3 client connections to a database,
c1, c2, and c3. C1 updates a row with primary key value "cust A" on
table "customer". Then c1 notifies all clients to refresh their local
data only for "cust A". The present implementation requires c2 and c3 to
read all rows from table "customer" instead of just one row that has
primary key value "cust A".

Probably one will implement an additional table, say tableX, so that c1
will first insert value "cust A" into tableX and then do the
notification. Sure c2 and c3 can first check tableX so they can
determine how to proceed. Problem is who is responsible for the delete
of row from tableX, and when and which row! This is really complicate
because c1, c2, or c3 can update the row for "cust B" any time later,
too.

I am not sure if I am asking too much but does it make sense, and is it
possible, to enhance NOTIFY that process "name/value" pair? Like this:

NOTIFY "MyName=MyValue";

With the capability of name/value pair, the listener will not have to
check notification details by reading tables.

Thank you!

CN

-- 
http://www.fastmail.fm - The professional email service


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Can't make backup

[Sebastián Baioni]

>Sebastián Baioni wrote:
 Every day we run a Windows Programmed pg_dump, it used to 
 work fine with PostgreSQL 8.0, but since we installed the new 
 version we are not able to make a backup. We tried to make a 
 whole database backup and it never ends. We tried to backup 
 table by table and it reachs a table of 50,000 rows and it 
 keeps for hours in that table, we started it again and 
 pg_dump backups that same table in seconds, but it stops in 
 another different table.
 If we watch the server message.# we don't have any error, 
 it's like some tables were locked and pg_dump would keep 
 waiting for the table to be unlocked.
>>>
>>> If pg_dump is locked out, there should be entries in
>>> pg_catalog.pg_locks. Check when the dump hangs!
>>
>> I don't have any table called pg_locks in pg_catalog.


It is a view.

> We let running pg_dump 8.2.5 at 1:30 AM after an automatic 
> reboot of the PostgreSQL service and it didn't finish. When I 
> came to the office at 9:00 there were a lot of locks, but all 
> of them were from the pg_dump conection.
>
>Is there any row in pg_locks that has a "false" in "granted"?
>That would be a wait for a lock to be released.
>
>Does pg_dump consume any processor cycles?
>
>You say "Windows Programmed pg_dump" - what exactly is that?
>Can you use a debugger to see where your program hangs?
>
>Yours,
>Laurenz Albe

--

I don't know how it is "Tareas programadas" in english, I have Windows in 
spanish, but it's the tool where yuo set a work to execute in a determinated 
time. We call a .bat that execute pg_dump:
"C:\Archivos de programa\PostgreSQL\8.2\bin\pg_dump.exe" -i -h 192.168.0.1 -p 
5432 -U MyUser -F c -b -v -f "C:\back\pd_dump\si\si.backup" MyDB

--

>Erik Jones wrote:
>The pg_locks view most certainly exists. Here's the list of all of
>the system catalog tables and view: http://www.postgresql.org/docs/
>8.2/interactive/catalogs.html. If you haven't looked at pg_locks how
>do you know "there were a lot of locks"?

With the Server Status in the pg Admin III.

Thanks
 Sebastián


   
-

Los referentes más importantes en compra/venta de autos se juntaron:
Demotores y Yahoo!.Ahora comprar o vender tu auto es más fácil. 
 Visitá http://ar.autos.yahoo.com/

Re: [GENERAL] Feature request: NOTIFY enhancement

[Bruce Momjian]

Andreas 'ads' Scherbaum wrote:
> > > With the capability of name/value pair, the listener will not have to
> > > check notification details by reading tables.
> > 
> > TODO already has:
> > 
> > * Add optional textual message to NOTIFY
> > 
> >   This would allow an informational message to be added to the notify
> >   message, perhaps indicating the row modified or other custom
> >   information.
> 
> It is possible, that some notifies, if following in a very short time
> frame, can get lost.
> 
> In case we want to send extra text messages with NOTIFY, we should make
> sure, that no notify get lost.

Agreed.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Feature request: NOTIFY enhancement

[Andreas 'ads' Scherbaum]

Hello,

On Thu, 3 Jan 2008 11:21:37 -0500 (EST) Bruce Momjian wrote:

> > I am not sure if I am asking too much but does it make sense, and is it
> > possible, to enhance NOTIFY that process "name/value" pair? Like this:
> > 
> > NOTIFY "MyName=MyValue";
> > 
> > With the capability of name/value pair, the listener will not have to
> > check notification details by reading tables.
> 
> TODO already has:
> 
>   * Add optional textual message to NOTIFY
>   
> This would allow an informational message to be added to the notify
> message, perhaps indicating the row modified or other custom
> information.

It is possible, that some notifies, if following in a very short time
frame, can get lost.

In case we want to send extra text messages with NOTIFY, we should make
sure, that no notify get lost.


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] C-Extenions for PostgreSQL, Call Convention Version 0

[Tom Lane]

"Harald Armin Massa" <[EMAIL PROTECTED]> writes:
> Within the PostgreSQL-Documentation for 8.2,
> http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html, still I
> find:

> """Using call conventions version 0, we can define c_overpaid as:"""

> That sound very tutorial-like; and so $I wonder: is those version 0
> depreciated? Or ist there still value in learning it?

That's just for teaching purposes.  As it says further up the page:

: We present the "old style" calling convention first Ñ although this
: approach is now deprecated, it's easier to get a handle on initially.

The examples are given in both styles so that you can get the point of
what's happening before you deal with the notational overhead of version
1.  For any sort of production programming I'd definitely recommend
using version 1, though.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Feature request: NOTIFY enhancement

[Alvaro Herrera]

Andreas 'ads' Scherbaum wrote:

> It is possible, that some notifies, if following in a very short time
> frame, can get lost.
> 
> In case we want to send extra text messages with NOTIFY, we should make
> sure, that no notify get lost.

Right.  Currently, NOTIFY acts like Unix signals -- consecutive signals
can get "collapsed" into a single one, and the listening process is
responsible for ensuring that it gets the communication details from
elsewhere.

If the idea is to convert NOTIFY into a full-blown communication system,
then no collapsing can take place.  This means the additional data
(which can be of unbounded size) must be stored elsewhere, probably on
disk.

So far (AFAIK), the only detailed design proposal came from Neil Conway,
which used SLRU for storage, but AFAIK he didn't take it much further.

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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Feature request: NOTIFY enhancement

[Bruce Momjian]

Alvaro Herrera wrote:
> Andreas 'ads' Scherbaum wrote:
> 
> > It is possible, that some notifies, if following in a very short time
> > frame, can get lost.
> > 
> > In case we want to send extra text messages with NOTIFY, we should make
> > sure, that no notify get lost.
> 
> Right.  Currently, NOTIFY acts like Unix signals -- consecutive signals
> can get "collapsed" into a single one, and the listening process is
> responsible for ensuring that it gets the communication details from
> elsewhere.
> 
> If the idea is to convert NOTIFY into a full-blown communication system,
> then no collapsing can take place.  This means the additional data
> (which can be of unbounded size) must be stored elsewhere, probably on
> disk.
> 
> So far (AFAIK), the only detailed design proposal came from Neil Conway,
> which used SLRU for storage, but AFAIK he didn't take it much further.

TODO has:

* Allow LISTEN/NOTIFY to store info in memory rather than tables?

  Currently LISTEN/NOTIFY information is stored in pg_listener. Storing
  such information in memory would improve performance.

I have added this to TODO:

* Allow multiple identical NOTIFY events to always be communicated to 
the
  client, rather than sent as a single notification to the listener

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Feature request: NOTIFY enhancement

[Chris Browne]

[EMAIL PROTECTED] (Bruce Momjian) writes:
>> I am not sure if I am asking too much but does it make sense, and is it
>> possible, to enhance NOTIFY that process "name/value" pair? Like this:
>> 
>> NOTIFY "MyName=MyValue";
>> 
>> With the capability of name/value pair, the listener will not have to
>> check notification details by reading tables.
>
> TODO already has:
>
>   * Add optional textual message to NOTIFY
>   
> This would allow an informational message to be added to the notify
> message, perhaps indicating the row modified or other custom
> information.

I keep wondering if this isn't just going to grow into pgq...

  

In effect, once you start defining more semantics for this, I think it
rapidly transforms into a message queueing system, and you might as
well go straight to something full-fledged like pgq...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/lisp.html
STATED REASON DOES NOT COMPUTE WITH PROGRAMMED FACTS...

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] CentOS-5 PostgreSQL 8.1.9 server instrumentation

[James B. Byrne]

I wish to enable server instrumentation for pgadmin3 on a CentOS-5 box
running the stock PostgreSQL 8.1.9 installed via yum. Can someone tell me
where I get the necessary support files and scripts for CentOS to
accomplish this?  I installed postgres-contrib but cannot find a script
called admin81.sql which pgadmin3 indicates as necessary to enable these
features.  yum whatprovides does not provide any useful information
either.

sincerely,

-- 
***  E-Mail is NOT a SECURE channel  ***
James B. Byrnemailto:[EMAIL PROTECTED]
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] negative duration times in query logs

[George Pavlov]

I started seeing some negative durations in my production query logs --
a "-" sign preceding the duration number, e.g.:

% grep 'duration: -' postgresql-Wed-*
postgresql-Wed-09.log:2008-01-02 08:56:33 PST [11705]: [538-1] LOG:
duration: -268280.421 ms
postgresql-Wed-15.log:2008-01-02 15:01:01 PST [30072]: [18-1] LOG:
duration: -1005.500 ms
postgresql-Wed-15.log:2008-01-02 15:01:01 PST [30066]: [14-1] LOG:
duration: -265.634 ms

These follow fairly standard queries that are issued hundreds if not
thousands times a day and ordinarily produce "normal" (positive)
durations. 

Has anyone seen this before? Any special meaning to the minus sign
(maybe it does not mean negative, but something else?)

I am running:

PostgreSQL 8.1.10 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-3)

My logging setup is: 

 log_connections | on  
 log_destination | stderr  
 log_disconnections  | on  
 log_duration| on  
 log_error_verbosity | default 
 log_executor_stats  | off 
 log_hostname| off 
 log_line_prefix | %t [%p]: [%l-1] 
 log_min_duration_statement  | -1  
 log_min_error_statement | panic   
 log_min_messages| notice  
 log_parser_stats| off 
 log_planner_stats   | off 
 log_rotation_age| 60  
 log_rotation_size   | 0   
 log_statement   | all 
 log_statement_stats | off 
 log_truncate_on_rotation| on  
 redirect_stderr | on  

George

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] [OT] Slony (initial) Replication - Slow

[Geoffrey]

Ow Mun Heng wrote:

{resend as don't see it on the list after 4 hours}

I'm just wetting my hands with slony and during the setup of the slave,
I did and dump and restore of the master DB to the Slave DB.


You don't need to do this.


However during the startup of slony, I noticed that it issues a truncate
command to the (to be) replicated table. Hence, this means that there's
no such need for me to do a dump/restore in the 1st place.

can someone confirm this? It _is_ taking long time (for slony) to do the
\copy (~60GB in multiple tables being replicated, including (on the fly)
index creation)


This is correct.  You want an empty replication database.  When you 
start replication slony will bring the master and slave into sync.


--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] SQLSTATE code for duplicate constraint

[Dan Langille]

Hi folks,

With reference to:

http://www.postgresql.org/docs/8.2/interactive/errcodes-appendix.html

What error code would be raised for a duplicate index name?

I didn't see anything appropriate. Possibly: 42710 DUPLICATE OBJECT

thanks

--
Dan Langille - http://www.langille.org/
BSDCan - The Technical BSD Conference: http://www.bsdcan.org/
PGCon  - The PostgreSQL Conference:http://www.pgcon.org/

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] negative duration times in query logs

[Tom Lane]

"George Pavlov" <[EMAIL PROTECTED]> writes:
> I started seeing some negative durations in my production query logs --
> a "-" sign preceding the duration number, e.g.:

> % grep 'duration: -' postgresql-Wed-*
> postgresql-Wed-09.log:2008-01-02 08:56:33 PST [11705]: [538-1] LOG:
> duration: -268280.421 ms

AFAICS this is only possible if gettimeofday() goes backwards, ie,
a later call returns a time less than an earlier one did.

> I am running:
> PostgreSQL 8.1.10 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
> 3.4.6 20060404 (Red Hat 3.4.6-3)

A quick search in Red Hat's bugzilla turns up several known instances of
time-going-backwards on various x86_64 hardware, eg
https://bugzilla.redhat.com/show_bug.cgi?id=241373
https://bugzilla.redhat.com/show_bug.cgi?id=244697
You might want to try to duplicate the problem using one of the test
programs mentioned there, and then add your hardware and kernel
details to the thread ... or open a new bug if it seems to be different
from these.

If your kernel isn't fully up-to-date, don't bother testing until you
have one that is ...

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] SQLSTATE code for duplicate constraint

[Tom Lane]

Dan Langille <[EMAIL PROTECTED]> writes:
> What error code would be raised for a duplicate index name?

regression=# \set VERBOSITY verbose
regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create index t1i on t1(f1);
CREATE INDEX
regression=# create index t1i on t1(f1);
ERROR:  42P07: relation "t1i" already exists
LOCATION:  index_create, index.c:567

Evidently it's ERRCODE_DUPLICATE_TABLE.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] [OT] Slony (initial) Replication - Slow

[Ow Mun Heng]

On Thu, 2008-01-03 at 19:17 -0500, Geoffrey wrote:
> Ow Mun Heng wrote:
> > However during the startup of slony, I noticed that it issues a truncate
> > command to the (to be) replicated table. Hence, this means that there's
> > no such need for me to do a dump/restore in the 1st place.
> > 
> This is correct.  You want an empty replication database.  When you 
> start replication slony will bring the master and slave into sync.
> 

Thanks to you and others who has responded for confirmation.
I would additionally like to know if there was any way for me to use the
dump/restore method and have slony pick up where it was left off?

BTW, it's working right now after some unsuccessful attempts.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?

[Fernando Hevia]

> Tom Lane [mailto:[EMAIL PROTECTED] wrote:
> 
> Since the OP has apparently already managed to get updated tzdata files 
> installed on his system, he could just copy them into 
> /usr/share/postgresql/timezone --- anything using zic should be a 
> compatible file format.
>
> The lack-of-ARST-on-input problem can be addressed by mucking with
> /usr/share/postgresql/timezonesets/Default, if you're using 8.2.
> In earlier versions the table is hardwired into datetime.c :-(
> 

Summing up:

After installing the updated tzdata files in the server I had to copy the
America/Argentina/* files to /usr/share/postgresql/timezone in order to get
postgres determine the correct local time.

With 8.2.x the ARST abbreviation was recognized after including the
following line in /usr/share/postgresql/8.2/timezonesets/Default

ARST   -14400 D  # Argentina Summer Time

postgres=# select '01:13:16.426 ARST Wed Jan 2 2008'::timestamp with time
zone;
timestamptz

 2008-01-02 01:13:16.426-02
(1 row)

I wonder if pg_timezone_names plays any role in the ARST issue. It does
contain the right data (appeared after copying tzdata into
/usr/share/postgresql/timezone and restarting server) but ARST wasn't
accepted till previous step was done.

postgres=# select * from pg_timezone_names where abbrev = 'ARST';
  name  | abbrev | utc_offset | is_dst
+++
 localtime  | ARST   | -02:00:00  | t
 America/Argentina/Rio_Gallegos | ARST   | -02:00:00  | t
 America/Argentina/Mendoza  | ARST   | -02:00:00  | t
 America/Argentina/La_Rioja | ARST   | -02:00:00  | t
 America/Argentina/Buenos_Aires | ARST   | -02:00:00  | t
 America/Argentina/Cordoba  | ARST   | -02:00:00  | t
 America/Argentina/Catamarca| ARST   | -02:00:00  | t
 America/Argentina/Ushuaia  | ARST   | -02:00:00  | t
 America/Argentina/Tucuman  | ARST   | -02:00:00  | t
 America/Argentina/Jujuy| ARST   | -02:00:00  | t
 America/Argentina/San_Juan | ARST   | -02:00:00  | t
(11 rows)


Thanks for all contributions.

Regards,
Fernando.




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?

[Tom Lane]

"Fernando Hevia" <[EMAIL PROTECTED]> writes:
> With 8.2.x the ARST abbreviation was recognized after including the
> following line in /usr/share/postgresql/8.2/timezonesets/Default

> ARST   -14400 D  # Argentina Summer Time

Um ... is that really offsetting in the correct direction?  What I put
into CVS was

ARST-7200 D  # Argentina Summer Time

If that's wrong I need to know ...

> I wonder if pg_timezone_names plays any role in the ARST issue. It does
> contain the right data (appeared after copying tzdata into
> /usr/share/postgresql/timezone and restarting server) but ARST wasn't
> accepted till previous step was done.

Too tired to experiment, but you may need a "pg_ctl reload" or even a
restart to get PG to notice changes in your timezonesets file.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend