[GENERAL] Re: [GENERAL] Reduce the time to know trigger_fi​le's existence

[Magnus Hagander]

On Mon, Sep 3, 2012 at 8:30 AM, Harshitha S  wrote:
> Hi,
>
> We are trying to introduce a thread that monitors the creation of the
> trigger_file. As and when the file is created, the process that monitors
> postgres server needs to be notified through the inotify API.
>
> This is to reduce the 3-4 seconds delay that exists with the current
> implementation in postgres. As per the current implementation, the thread
> checks for the existence of the file every 5 seconds. If the file got
> created just 1 second after the sleep, there is a wait time of 4 seconds
> before we know whether the file is present or not. We intend to avoid this
> delay by using inotify().
>
> PostgreSQL version number you are running: postgres 9.1.5
>
> How you installed PostgreSQL: Downloaded and compiled the sources
>
>
> Does anyone have suggestions on the appropriate place to add
> inotify_add_watch to achieve our objective?
> Thanks in advance.

"pg_ctl promote" already uses a signal to remove this time delay. Is
there a reason you can't just use this?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


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

[GENERAL] SEPostgreSQL alive?

[Johannes Segitz]

Hello,

i'm currently working on a project to secure a webapplication with
SELinux and I'm interessted
in SEPostgreSQL. Is this project still alive? I couldn't find any
information besides
http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction

Is anyone actively using/developing SEPostgreSQL?

Regards,
Johannes


-- 
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] SEPostgreSQL alive?

[Devrim GÜNDÜZ]

Hi,

On Tue, 2012-09-04 at 09:49 +0200, Johannes Segitz wrote:
> 
> i'm currently working on a project to secure a webapplication with
> SELinux and I'm interessted
> in SEPostgreSQL. Is this project still alive? I couldn't find any
> information besides
> http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction
> 
> Is anyone actively using/developing SEPostgreSQL? 

It was merged to contrib/sepgsql. 

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


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

Re: [GENERAL] Would my postgresql 8.4.12 profit from doubling RAM?

[Alexander Farber]

Hello, thank you for your replies and sorry for the delay in my replying -

On Thu, Aug 30, 2012 at 4:45 PM, Scott Marlowe  wrote:
> On Thu, Aug 30, 2012 at 8:42 AM, Scott Marlowe  
> wrote:
>> users, and currently work_mem is set to 1M (the default.)  If you
>> increase that to 16M, that'd be max 1.6G of memory, which you have
>> free anyway right now.

I did look at the vmstat output, but can't deduce anything from it:

# free -m
 total   used   free sharedbuffers cached
Mem: 15862  13289   2573  0588   8407
-/+ buffers/cache:   4293  11569
Swap: 2046  0   2046

# vmstat
procs ---memory-- ---swap-- -io --system-- -cpu-
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id wa st
 5  0256 2610788 602424 860534400 0   11200 10
 0 88  1  0

I'll try changing work_mem to 2MB first - once I upgrade the RAM.

Regards
Alex


-- 
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] recovering databases

[Albe Laurenz]

Yvon Thoraval wrote:
> on my computer I had a disk probleme, then i had to reinstall the system 
> (Xubuntu 12.04).
> I've backuped some parts of the disk, namely /etc, /var and /home.
> I wonder if I'm able to recover my past databases in the 
> /var/lib/postgresql/9.1/ backup.
> If yes, how ?

If you have the complete data directory, there should be
no problem.
Install PostgreSQL 9.1 and start the server against the
data directory.

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] Would my postgresql 8.4.12 profit from doubling RAM?

[Alexander Farber]

On Tue, Sep 4, 2012 at 10:59 AM, Alexander Farber
 wrote:
> I'll try changing work_mem to 2MB first - once I upgrade the RAM.

And then I'll increase it up to 16MB every day as Scott proposed.


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

R: [GENERAL] I: Installation faillure version 8.4.12

[Segato Luca]

Dear Ashesh Vashi

Here enclose the installation log file.

The operating system is Microsoft windows vista Business service pack 2 
language Italian

 

Thank for your help, 

Best regards

 

 

 

 



Da: Ashesh Vashi [mailto:ashesh.va...@enterprisedb.com] 
Inviato: venerdì 24 agosto 2012 13.20
A: Segato Luca
Cc: pgsql-general@postgresql.org; Erba Simone
Oggetto: Re: [GENERAL] I: Installation faillure version 8.4.12

 

 

 

On Fri, Aug 24, 2012 at 4:47 PM, Segato Luca  wrote:

 

Dear All

We try to install, several times, postgres version 8.4.12, each installation 
was failed during the "post-install step "

 displaying this error:  (install-postgresql.log)

 

Executing cscript //NoLogo "F:\postgressql/installer/server/initcluster.vbs" 
"postgres" "postgres" "" "F:\postgressql" "F:\postgressql\data" 5432 
"DEFAULT"

Script exit code: 1

 

Script output:

 Errore CScript: Impossibile trovare l'interprete di script "VBScript" per lo 
script "F:\postgressql\installer\server\initcluster.vbs".

 

Script stderr:

 Program ended with an error exit code

 

Error running cscript //NoLogo 
"F:\postgressql/installer/server/initcluster.vbs" "postgres" "postgres" "" 
"F:\postgressql" "F:\postgressql\data" 5432 "DEFAULT" : Program ended with an 
error exit code

Problem running post-install step. Installation may not complete correctly

 The database cluster initialisation failed.

 

We tried with  command: regsvr32 %systemroot%\system32\vbscript.dll  but the 
problem was not solved

 

Could you suggest us , some solutions ?

Can you attach the installation logs?

And - also mention the operating system.

Can 
--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company 
 

 

http://www.linkedin.com/in/asheshvashi

 

 

Best regards

 

Luca Segato

 

Dr. Luca Segato 
R.E.A.C.H. Manager 
  
Tel. +39 02 99442252 
Fax. +30 02 99442265 

  
lseg...@prholding.it 
http://www.prholding.it 
  
P&R HOLDING S.P.A. 
HEADQUARTERS
Via Milano, 186 
20024 Garbagnate Milanese (MI) - ITALY

 

DICHIARAZIONE DI RISERVATEZZA 
Le informazioni contenute nella comunicazione che precede e negli 
eventuali allegati possono essere riservate e sono, comunque, destinate alla 
persona o all'ente indicati come destinatari. 
La diffusione, copiatura e/o distribuzione del documento trasmesso da 
parte di qualsiasi soggetto diverso dal destinatario è proibita e potrà essere 
legalmente perseguita. Se avete ricevuto questo messaggio per errore, Vi 
preghiamo di contattarci immediatamente e di distruggere la comunicazione e 
tutti gli eventuali allegati 

CONFIDENTIAL STATEMENT 
This e-mail and any attachments may contain confidential and/or 
proprietary information that are for the exclusive use of the intended 
recipient only.The disclosure, copying, distribution or any other use of this 
e-mail by any other than the intended recipient is strictly prohibited and may 
result in legal action. If you have received this e-mail in error, please 
immediately inform us then delete the e-mail and all enclosed attachments

 


DICHIARAZIONE DI RISERVATEZZA
Le informazioni contenute nella comunicazione che precede e negli eventuali 
allegati possono essere riservate e sono, comunque, destinate alla persona o 
all'ente indicati come destinatari.
La diffusione, copiatura e/o distribuzione del documento trasmesso da parte di 
qualsiasi soggetto diverso dal destinatario è proibita e potrà essere 
legalmente persegutia.Se avete ricevuto questo messaggio per errore, Vi 
preghiamo di contattarci immediatamente e di distruggere la comunicazione e 
tutti gli eventuali allegati

CONFIDENTIAL STATEMENT 
This e-mail and any attachements may contain confidential and/or proprietary 
information that are for the exclusive use of the intended recipient only.The 
disclosure, copying, distribution or any other use of this e-mail by any other 
than the intended recipient is strictly prohibited and may result in legal 
action.
If you have recived this e-mail in error, please immediately inform us then 
delete the e-mail and all enclosed attachments


bitrock_installer_1932.log
Description: bitrock_installer_1932.log

-- 
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] I: Installation faillure version 8.4.12

[Ashesh Vashi]

On Tue, Sep 4, 2012 at 2:49 PM, Segato Luca  wrote:

>  Dear Ashesh Vashi
>
> Here enclose the installation log file.
>
> The operating system is Microsoft windows vista Business service pack 2
> language Italian
>
Looks like the vbscript engine is missing or misconfigured on your system:
Please check any of the below links helps you resolved the issue:
http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#The_installer_exits_with_a_runtime_installation_error.3F
http://blogs.technet.com/b/instan/archive/2010/07/20/can-t-find-script-engine-quot-vbscript-quot-for-script-after-installing-ms10-020.aspx
http://answers.microsoft.com/en-us/windows/forum/windows_vista-windows_programs/cant-load-find-vbscript/8c657ddb-2bd1-43fd-ad73-77a3308fc475#8c657ddb-2bd1-43fd-ad73-77a3308fc475

--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company



*http://www.linkedin.com/in/asheshvashi*

> 
>
> ** **
>
> Thank for your help, 
>
> Best regards
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>  --
>
> *Da:* Ashesh Vashi [mailto:ashesh.va...@enterprisedb.com]
> *Inviato:* venerdì 24 agosto 2012 13.20
> *A:* Segato Luca
> *Cc:* pgsql-general@postgresql.org; Erba Simone
> *Oggetto:* Re: [GENERAL] I: Installation faillure version 8.4.12
>
> ** **
>
> 
>
> On Fri, Aug 24, 2012 at 4:47 PM, Segato Luca  wrote:
> 
>
>  
>
> Dear All
>
> We try to install, several times, postgres version 8.4.12, each
> installation was failed during the “post-install step ”
>
>  displaying this error:  (install-postgresql.log)
>
>  
>
> *Executing cscript //NoLogo
> "F:\postgressql/installer/server/initcluster.vbs" "postgres" "postgres"
> "" "F:\postgressql" "F:\postgressql\data" 5432 "DEFAULT"*
>
> *Script exit code: 1*
>
> * *
>
> *Script output:*
>
> * **Errore CScript: Impossibile trovare l'interprete di script "VBScript"
> per lo script "F:\postgressql\installer\server\initcluster.vbs".*
>
> * *
>
> *Script stderr:*
>
> * Program ended with an error exit code*
>
> * *
>
> *Error running cscript //NoLogo
> "F:\postgressql/installer/server/initcluster.vbs" "postgres" "postgres"
> "" "F:\postgressql" "F:\postgressql\data" 5432 "DEFAULT" : Program
> ended with an error exit code*
>
> *Problem running post-install step. Installation may not complete
> correctly*
>
> * The database cluster initialisation failed.*
>
> * *
>
> We tried with  command*: **regsvr32 %systemroot%\system32\vbscript.dll**
> **but the problem was not solved*
>
> * *
>
> *Could you suggest us , some solutions ?*
>
> Can you attach the installation logs?
>
> And - also mention the operating system.
>
> Can
> --
>
> *Thanks & Regards,**
>
> **Ashesh Vashi**
> **EnterpriseDB INDIA: *Enterprise PostgreSQL 
> Company
> 
>
>  
>
> *http://www.linkedin.com/in/asheshvashi*
>
> ** **
>
>  * *
>
> *Best regards*
>
> * *
>
> *Luca Segato*
>
>  
>
> *Dr. Luca Segato*
> *R.E.A.C.H. Manager*
>
> Tel. +39 02 99442252
> Fax. +30 02 99442265
>
>
> *lseg...@prholding.it*
> *http://www.prholding.it*
>
> *P&R HOLDING S.P.A.*
> *HEADQUARTERS*
> *Via Milano, 186*
> *20024 Garbagnate Milanese (MI) - ITALY*
>
>  
>
> *DICHIARAZIONE DI RISERVATEZZA*
> Le informazioni contenute nella comunicazione che precede e negli
> eventuali allegati possono essere riservate e sono, comunque, destinate
> alla persona o all'ente indicati come destinatari.
> La diffusione, copiatura e/o distribuzione del documento trasmesso da
> parte di qualsiasi soggetto diverso dal destinatario è proibita e potrà
> essere legalmente perseguita. Se avete ricevuto questo messaggio per
> errore, Vi preghiamo di contattarci immediatamente e di distruggere la
> comunicazione e tutti gli eventuali allegati
>
> *CONFIDENTIAL STATEMENT*
> This e-mail and any attachments may contain confidential and/or
> proprietary information that are for the exclusive use of the intended
> recipient only.The disclosure, copying, distribution or any other use of
> this e-mail by any other than the intended recipient is strictly prohibited
> and may result in legal action. If you have received this e-mail in error,
> please immediately inform us then delete the e-mail and all enclosed
> attachments
>
>  ** **
>  *DICHIARAZIONE DI RISERVATEZZA*
> Le informazioni contenute nella comunicazione che precede e negli
> eventuali allegati possono essere riservate e sono, comunque, destinate
> alla persona o all'ente indicati come destinatari.
> La diffusione, copiatura e/o distribuzione del documento trasmesso da
> parte di qualsiasi soggetto diverso dal destinatario è proibita e potrà
> essere legalmente perseguita. Se avete ricevuto questo messaggio per
> errore, Vi preghiamo di contattarci immediatamente e 

[GENERAL] I want your opinion on how to do something.

[Herouth Maoz]

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

Now, there is is a new requirement to have some of the data available in the 
reports database as soon as it is inserted in the production database. 
Specifically, the data in question is in two particular tables. However, in 
addition to just shipping the tables in, I also need to perform a bit of 
processing on the data as it comes. Basically, each transaction in production 
is represented by 1-3 rows in the two tables. One of the fields contains 
urlencoded data, which I need to decode and concatenate, so that the rows are 
represented by one row in another table.

E.g.

Table A brought from production:
key field 1 | key field 2 | num of segments | segment num | segment id |
+-+-+-+|
abcde   | 134 | 3   | 1   | 999000 |
abcde   | 567 | 3   | 3   | 999000 |
abcde   | 890 | 3   | 2   | 999000 |
fghij   | 987 | 2   | 1   | 999001 |
fghij   | 654 | 2   | 2   | 999001 |
abcde   | -11 | 1   | 1   | 999003 |

Table B from production

key field 1 | key field 2 | urlencoded data |
+-+-+
abcde   | 134 | AAA |
abcde   | 567 | CCC |
abcde   | 890 | BBB |
fghij   | 987 | fff |
fghij   | 654 | ggg |
abcde   | -11 | XXX |

Here we have basically three transactions - one with three segments, one with 
two, and one with a single segment. The data that identifies that certain rows 
belong to the same transation and what the order is is in table A. The actual 
data to decode is in table B.

The result I need to produce is a new table like this:

key field 1 | key field 2 | segment id | decoded concatenated data|
+-++--|
abcde   | 134 | 999000 | AAABBBCCC|
fghij   | 987 | 999001 | fffggg   |
abcde   | -11 | 999003 | XXX  |

Basically, a single row for each transaction, with the key fields taken from 
the original's first segment, and the data decoded and concatenated. But I need 
this to be done in (near) real-time - as the rows are added. Because of the 
decoding and concatenation requirements, this can't be a view, because there is 
no SQL function that will do the required processing, and I'm not sure a view 
would be efficient for running reports anyway. So a new table it will be.

Basically, I can replicate these two tables from production to the reports 
database using Londiste, which allows me to replicate single tables and is also 
supposed to allow me to run triggers on the tables.

What I thought of doing was create a trigger on one of the tables, such that 
when data is inserted, it will insert data into the "result" table, and write 
this trigger using PL/Perl or PL/Python which I'm guessing will allow me to do 
the string manipulation required.

The issue is that when an insert or an update is fired, I can't say whether all 
the segments of the same transaction have been written yet, and if only some of 
them were written, there is no guarantee on the order in which they are 
written. The question is whether such a trigger can be created at all. It needs 
to verify that all parts are in the database - run a query on the same table at 
the time the trigger is running. If not all data is there, it shouldn't insert 
anything. Is this feasible at all? How would you achieve it?

Thank you,
Herouth

[GENERAL] Databas has no Object Identifier Types & Functions

[Rebecca Clarke]

Hi there,

I'm running postgresql 8.4 on a debian system. I have a database that has
no object identifier types and functions in the pg_catalog,
e.g.  regclass, regclassout.


What's the best way to get these in to this database? I don't want to
upgrade postgresql.
There are other databases on the server that do have them.


Thanks

R Clarke

Re: [GENERAL] Databas has no Object Identifier Types & Functions

[Pavan Deolasee]

On Tue, Sep 4, 2012 at 3:58 PM, Rebecca Clarke  wrote:

> Hi there,
>
> I'm running postgresql 8.4 on a debian system. I have a database that has
> no object identifier types and functions in the pg_catalog,
> e.g.  regclass, regclassout.
>
>
Are you sure you don't have them ? I thought regclass is quite old, but I
may be wrong. What does the following query returns ?

SELECT * FROM pg_type WHERE typname = 'regclass';

Thanks,
Pavan

Re: [GENERAL] Databas has no Object Identifier Types & Functions

[Rebecca Clarke]

Returns 0 rows.


On Tue, Sep 4, 2012 at 11:41 AM, Pavan Deolasee wrote:

>
>
> On Tue, Sep 4, 2012 at 3:58 PM, Rebecca Clarke wrote:
>
>> Hi there,
>>
>> I'm running postgresql 8.4 on a debian system. I have a database that has
>> no object identifier types and functions in the pg_catalog,
>> e.g.  regclass, regclassout.
>>
>>
> Are you sure you don't have them ? I thought regclass is quite old, but I
> may be wrong. What does the following query returns ?
>
> SELECT * FROM pg_type WHERE typname = 'regclass';
>
> Thanks,
> Pavan
>

Re: [GENERAL] Postgres not getting logged in from command prompt

[himanshu.joshi]

Albe Laurenz wrote:

Himanshu Joshi wrote:
  

I am getting a below mentioned strange error while logging


into
  

postgres from linux command prompt. It is working fine with


GUI(pgAdmin
  

III). I am using postgres version 8.4SS.
Himanshu
# cd /opt/PostgresPlus/8.4SS/
# bin/psql -Upostgres -p
bin/psql: line 1: bzip2,: command not found
bin/psql: line 3: usage:: command not found
bin/psql: line 5: -h: command not found
bin/psql: line 6: -d: command not found
bin/psql: line 7: -z: command not found
bin/psql: line 8: syntax error near unexpected token `('
bin/psql: line 8: `   -k --keep   keep (don't delete) input


files'
  

#

I have already given the restart to postgres as well as the server


many
  

times, but it didnt worked.
Plz suggest any solution for that.



That looks like bin/psql is not an executable but a text file,
which it should not be.

How did you install PostgreSQL?  What is in that file and how
did it get there?

Maybe reinstalling the PostgreSQL software will help.

Yours,
Laurenz Albe
  

Hi Laurenz,
   You were right. bin/psql was overwritten as a text file. I have 
replaced this corrupted bin/psql file with bin/psql file from another 
server. So now the postgres it is working fine.

Thanks for your valuable suggestion.

Regards
Himanshu Joshi


--
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] Reduce the time to know trigger_fi​le's existence

[Craig Ringer]

On 09/04/2012 02:15 PM, Harshitha S wrote:

Hi,

Can Latch - that postgres already uses, be used to achieve the same?


With a quick look at latches I'm not sure what that'd have to do with 
file change notification.


Magnus's suggestion to use a signal - especially as one is already 
understood for the purpose - makes a lot more sense. He should know, 
given how much work he's done on the server.


--
Craig Ringer



--
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] Databas has no Object Identifier Types & Functions

[Chris Travers]

On Tue, Sep 4, 2012 at 3:28 AM, Rebecca Clarke  wrote:

> Hi there,
>
> I'm running postgresql 8.4 on a debian system. I have a database that has
> no object identifier types and functions in the pg_catalog,
> e.g.  regclass, regclassout.
>
>
> What's the best way to get these in to this database? I don't want to
> upgrade postgresql.
> There are other databases on the server that do have them.
>
>
 Ouch.

First thign I would do is check and make sure they are in template1.

Then I would probably rename the db (just in case), create a new db, and
use psql and pg_dump to move the old data into the freshly created one.

There may be ways to restore just these types but I would worry about what
else is missing and this seems to me to be the safest course of action
if/when you can afford the downtime.

If they are not in template1, create the database from template0.  You may
want to recreate template1 in that case also.

Best Wishes,
Chris Travers

[GENERAL] No Default Text Search Parser

[Rebecca Clarke]

Hi there

Postgresql 8.4.

I keep getting this error multiple times when I tried to access the
pg_catalog in pg_admin:

Cache lookup failed for text search parser 3722.

The logs show that the query that was being executed was: (example is one
of many)


 SELECT
  (SELECT t.alias FROM pg_catalog.ts_token_type(cfgparser) AS t
 WHERE t.tokid = maptokentype) AS tokenalias,
  dictname
FROM pg_ts_config_map
  LEFT OUTER JOIN pg_ts_config ON mapcfg=pg_ts_config.oid
  LEFT OUTER JOIN pg_ts_dict ON mapdict=pg_ts_dict.oid
WHERE mapcfg=11299::oid
ORDER BY 1, mapseqno


What I found was that my database does not have the default text
search parser with oid 3722.

Is someone able to tell me it's origin so I can back track and get it in
the database. I could easily generate it manually but it will have a
different oid which is a no go because pg_ts_parser specifies 3722 and the
records in pg_ts_config specify parsar as 3722 also.

Many thanks

R Clarke

Re: [GENERAL] Would my postgresql 8.4.12 profit from doubling RAM?

[Scott Marlowe]

On Tue, Sep 4, 2012 at 2:59 AM, Alexander Farber
 wrote:
> Hello, thank you for your replies and sorry for the delay in my replying -
>
> On Thu, Aug 30, 2012 at 4:45 PM, Scott Marlowe  
> wrote:
>> On Thu, Aug 30, 2012 at 8:42 AM, Scott Marlowe  
>> wrote:
>>> users, and currently work_mem is set to 1M (the default.)  If you
>>> increase that to 16M, that'd be max 1.6G of memory, which you have
>>> free anyway right now.
>
> I did look at the vmstat output, but can't deduce anything from it:

Looks like you're not real familiar with vmstat.  It shows you
averages of things like context switches, blocks read and written and
so on, over time.  The first line is the average since the server
booted up so isn't all that useful.

vmstat 10

and let it run for a few minutes during heavy load then cut and paste.

For instance here's some output from a production server:

vmstat 10
procs ---memory-- ---swap-- -io -system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id wa
 6  0 468388 3756476 1445228 9433935200 3   11600
15  2 83  0
 4  0 468388 3742288 1445244 943288320053  2992 18907
18320 12  2 86  0
 5  0 468388 3731272 1445264 943346400054  2826 23835
24463 17  3 80  0
 2  0 468388 3722556 1445276 943414560077  3638 23207
24297 17  3 80  0

Here's a page with a quick description of each field:

http://www.lazysystemadmin.com/2011/04/understanding-vmstat-output-explained.html

The ones to look for are b (high b means blocking IO ops) so/si which
means active swapping, in/cs interrupts / context switches per second.
 On big servers numbers into the 10s of k are fine.  100s of k are
getting a bit much.  It means how often your machine is switching
tasks.  Too much switching and it spends all its time switching and
not doing anything.

us sy id wa are all % of the cpu(s) doing each of: user space work,
system work, idle, or waiting.  Note that if you have 4 cores, and are
25% wa(it) that you have one core doing nothing but waiting.  I.e. the
% is for the total of cores, not of 1 core.

> # free -m
>  total   used   free sharedbuffers cached
> Mem: 15862  13289   2573  0588   8407
> -/+ buffers/cache:   4293  11569
> Swap: 2046  0   2046

So you're not using swap, you have 2.5G free memory and 8.4G cached.
I'm assuming there's nothing to be gained adding memory unless you
could use larger work_mem for some of your work loads.

> I'll try changing work_mem to 2MB first - once I upgrade the RAM.

Yep, small steps, measured afterwards for impact is the way to go.


-- 
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] Databas has no Object Identifier Types & Functions

[Merlin Moncure]

On Tue, Sep 4, 2012 at 6:57 AM, Rebecca Clarke  wrote:
> Returns 0 rows.

how in the world did you get yourself in that situation?

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

[punnoose]

hi all
How could i use crostab to display variable number of columns. in the output 
There could be variable number of columns
Regards
Punnoose



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/crosstab-tp5722632.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] crosstab

[dinesh kumar]

Hi ,

I believe, we need to have a Dynamic SQL for this to generate the dynamic
Crostab.. I have tried the same earlier and wrote the below Calendar Query
for the year 2011 including holidays.

Crostab Example
=
select
min(case when extract(month from d.dd)= 1 then
'Jan'
when extract(month from d.dd)= 2 then
'Feb'
when extract(month from d.dd)=3 then
'Mar'
when extract(month from d.dd)= 4 then
'Apr'
when extract(month from d.dd)= 5 then
'May'
when extract(month from d.dd)= 6 then
'Jun'
when extract(month from d.dd)= 7 then
'Jul'
when extract(month from d.dd)= 8 then
'Aug'
when extract(month from d.dd)=9 then
'Sep'
when extract(month from d.dd)= 10 then
'Oct'
when extract(month from d.dd)= 11 then
'Nov'
when extract(month from d.dd)= 12 then
'Dec'
end
) as MONTH,min(case when extract(DOW from d.dd)=0 then
(extract(day from d.dd))
end) as SUN,
min(case when extract(DOW from d.dd)=1 then
(extract(day from d.dd))
end) as MON,
min(case when extract(DOW from d.dd)=2 then
(extract(day from d.dd))
end) as TUE,
min(case when extract(DOW from d.dd)=3 then
(extract(day from d.dd))
end) as WED,
min(case when extract(DOW from d.dd)=4 then
(extract(day from d.dd))
end) as THU,
min(case when extract(DOW from d.dd)=5 then
(extract(day from d.dd))
end) as FRI,
min(case when extract(DOW from d.dd)=6 then
(extract(day from d.dd))
end) as SAT,
min(case when extract(day from d.dd)=26 and extract(month from d.dd)=1 then
'Republic Day <---> 26th January 2011 <--> Wednessday'
when extract(day from d.dd)=16 and extract(month from d.dd)=2 then
'Id-E-Milad <--> 16th February 2011 <--> Wednessday'
when extract(day from d.dd)=19 and extract(month from d.dd)=2 then
'Chhatrapati Shivaji Maharaj Jayanthi <--> 19th February 2011 <--> Saturday'
when extract(day from d.dd)=2 and extract(month from d.dd)=3 then
'Mahashivratri <--> 2nd March 2011<--> Wednessday'
when extract(day from d.dd)=4 and extract(month from d.dd)=4 then
'Gudi Padwa <--> 4th April 2011 <--> Tuesday'
when extract(day from d.dd)=12 and extract(month from d.dd)=4 then
'Ram Navmi - 12th April - Tuesday'
when extract(day from d.dd)=14 and extract(month from d.dd)=4 then
'Dr. Babasaheb Ambedkar Jayanthi - 14th April - Thursday'
when extract(day from d.dd)=16 and extract(month from d.dd)=4 then
'Mahavir Jayanthi - 16th April - Saturday'
when extract(day from d.dd)=22 and extract(month from d.dd)=4 then
'Good Friday - 22nd April - Friday'
when extract(day from d.dd)=17 and extract(month from d.dd)=5 then
'Buddha Pournima - 17th May - Tuesday'
when extract(day from d.dd)=15and extract(month from d.dd)=8 then
'Independence day - 15th August - Monday'
when extract(day from d.dd)=19 and extract(month from d.dd)=8 then
'Parsi New Year - 19th August - Friday'
when extract(day from d.dd)=31 and extract(month from d.dd)=8 then
'Ramzan Id(Id-Ul-Fitar)(Shawal-1) - 31st August - Wednessday'
when extract(day from d.dd)=1 and extract(month from d.dd)=9 then
'Ganesh Chaturthi - 1st September - Thursday'
when extract(day from d.dd)=6 and extract(month from d.dd)=10 then
'Dasara - 6th October - Thursday'
when extract(day from d.dd)=26 and extract(month from d.dd)=10 then
'Diwali Amavasya (Laxmi Pujan) - 26th October - Wednessday'
when extract(day from d.dd)=27 and extract(month from d.dd)=10 then
'Diwali (Balipratipada) - 27th October - Thursday'
when extract(day from d.dd)=7 and extract(month from d.dd)=11 then
'Bakri Id (iD-Ul-Zun) - 7th November - Monday'
when extract(day from d.dd)=10 and extract(month from d.dd)=11 then
'Guru Nanank Jayanthi - 10th November - Thursday'
when extract(day from d.dd)=6 and extract(month from d.dd)=12 then
'Moharam - 6th December - Tuesday'
when extract(day from d.dd)=20 and extract(month from d.dd)=3 then
'Holi - 20th March - Sunday'
when extract(day from d.dd)=1 and extract(month from d.dd)=5 then
'Maharastra Day - 1st May - Sunday'
when extract(day from d.dd)=2 and extract(month from d.dd)=10 then
'Mahatma Gandhi Jayanthi - 2nd October - Sunday'
when extract(day from d.dd)=25 and extract(month from d.dd)=12 then
'Christmas - 25th December - Sunday'

end) as Holidays
from
(
select
date_trunc('year',current_date)::date + a.si - 1 as dd
from
(
select generate_series(1,cast((extract('day' from
date_trunc('year',current_date)-date_trunc('year',current_date-365))) as
int)) as si) as a
) as d
group by extract(week from d.dd),extract(month from d.dd)
order by extract(month from d.dd),1,2,3,4,5,6,7;


OUTPUT


month | sun | mon | tue | wed | thu | fri | sat | holidays
---+-+-+-+-+-+-+-+
Jan | 2 | | | | | | 1 |
Jan | 9 | 3 | 4 | 5 | 6 | 7 | 8 |
Jan | 16 | 10 | 11 | 12 | 13 | 14 | 15 |
Jan | 23 | 17 | 18 | 19 | 20 | 21 | 22 |
Jan | 30 | 24 | 25 | 26 | 27 | 28 | 29 | Republic Day <---> 26th January
2011 <--> Wednessday
Jan | | 31 | | | | | |
Feb | 6 | | 1 | 2 | 3 | 4 | 5 |
Feb | 13 | 7 | 8 | 9 | 10 | 11 | 12 |
Feb | 20 | 14 | 15 | 16 | 17 | 18

Re: [GENERAL] "Too far out of the mainstream"

[Steve Crawford]

On 08/31/2012 01:05 PM, Andrew Sullivan wrote:

..Anyone who thinks that
MySQL, with its sketchy guarantees of data integrity and persistence,
is mainstream-acceptable but Postgres isn't because they haven't read
about it in InfoWorld

And if they want to read about it in InfoWorld, they can:

http://www.infoworld.com/d/open-source-software/the-stealth-success-of-postgresql-197584

Cheers,
Steve



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

[Aram Fingal]

Has anyone tried developing a PLR procedure to do the equivalent of crosstab 
but using the Reshape library from R?  The reason I ask is that I have been 
extracting tables like this with an R script, using RPostgreSQL, then functions 
from Reshape and writing the output to a .csv file.  I wonder if a PLR 
procedure could so this with fewer lines of code and greater speed.  I'm just 
not clear on how to write the PLR code.



-- 
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] "Too far out of the mainstream"

[Mike Christensen]

On Mon, Sep 3, 2012 at 3:38 PM, Geert Mak  wrote:
> I have been looking into heroku lately, they run on PostgreSQL -
>
> https://postgres.heroku.com/postgres

Went out to lunch with a guy who worked for Redfin as well.  I guess
they're all Postgres over there too..


-- 
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] "Too far out of the mainstream"

[Scott Marlowe]

On Tue, Sep 4, 2012 at 11:28 AM, Mike Christensen  wrote:
> On Mon, Sep 3, 2012 at 3:38 PM, Geert Mak  wrote:
>> I have been looking into heroku lately, they run on PostgreSQL -
>>
>> https://postgres.heroku.com/postgres
>
> Went out to lunch with a guy who worked for Redfin as well.  I guess
> they're all Postgres over there too..

Since there's no reporting requirements for using postgresql
commercially etc, it's kind of a stealth database.  It's all over the
place and nobody knows it.  Meanwhile, finding qualified PostgreSQL
DBAs is pretty tough because of it's silent yet explosive growth in
usage.


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

[GENERAL] A next logical step after index-only scans?

[Mike Christensen]

Now that 9.2 supports index-only scans, I'm curious what possibilities
this opens up in the future.

It seems to me that the next logical step is to allow a user to put
all columns into the index, at which point you would no longer even
need the heap table.  Oracle calls this "index-organized tables", and
this offers a lot of benefits for tables with only one index.

Any thoughts on this, or is it on the road map?  Thanks!

Mike


-- 
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] "Too far out of the mainstream"

[Kevin Grittner]

Andy Yoder  wrote:
 
> I would like the community's input on a topic.  The words "too far
> out of the mainstream" are from an e-mail we received from one of
> our clients, describing the concern our client's IT group has
> about our use of PostgreSQL in our shop.  The group in question
> supports multiple different databases, including Oracle, MySQL,
> SQLServer, DB2, and even some non-relational databases (think
> Cobol and file-based storage), each type with a variety of
> applications and support needs.  We are in the running for getting
> a large contract from them and need to address their question: 
> "What makes PostgreSQL no more risky than any other database?"
 
Hi Andy,
 
You might be interested in an old post where I compared my
experiences using a commercial database with using PostgreSQL in the
Wisconsin Courts environment:
 
http://archives.postgresql.org/pgsql-advocacy/2011-11/msg00021.php
 
With only 3000 directly connected users and a few million web hits a
day backed by PostgreSQL, the Wisconsin court system is far from the
largest user, but I figure that if the larger organizations want to
broadcast their usage, that's up to them.  I also have talked to
others with much larger databases than we have -- our largest one is
3TB.  Again, it's not my place to broadcast details if they don't
choose to do so.  But I think "out of the mainstream" is a very odd
description of PostgreSQL.  It sounds like the sort of thing which a
representative of a commercial product, afraid of losing big money
to PostgreSQL but unable to come up with any *real* reason not to
use it, might throw out there to try to scare people away from it.
 
I will join the chorus advising you to ask for more particular
concerns.  What is it that they think makes those other database
products no more risky than PostgreSQL?
 
-Kevin


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

[Vincent Veyron]

Le mardi 04 septembre 2012 à 08:39 -0700, punnoose a écrit :
> hi all
> How could i use crostab to display variable number of columns. in the output 
> There could be variable number of columns

see the documentation for Additional Supplied Modules, in your case
tablefunc :

http://www.postgresql.org/docs/9.1/static/tablefunc.html


-- 
Vincent Veyron
http://marica.fr/
Gestion informatique des sinistres d'assurances et des dossiers contentieux 
pour le service juridique



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

Re: [GENERAL] crosstab

[dinesh kumar]

This is really good feature (crostab) to implement ..

Best Regards,
Dinesh
manojadinesh.blogspot.com


On Wed, Sep 5, 2012 at 12:21 AM, Vincent Veyron  wrote:

> Le mardi 04 septembre 2012 à 08:39 -0700, punnoose a écrit :
> > hi all
> > How could i use crostab to display variable number of columns. in the
> output
> > There could be variable number of columns
>
> see the documentation for Additional Supplied Modules, in your case
> tablefunc :
>
> http://www.postgresql.org/docs/9.1/static/tablefunc.html
>
>
> --
> Vincent Veyron
> http://marica.fr/
> Gestion informatique des sinistres d'assurances et des dossiers
> contentieux pour le service juridique
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] crosstab

[Aram Fingal]

On Sep 4, 2012, at 2:51 PM, Vincent Veyron wrote:

> Le mardi 04 septembre 2012 à 08:39 -0700, punnoose a écrit :
>> hi all
>> How could i use crostab to display variable number of columns. in the output 
>> There could be variable number of columns
> 
> see the documentation for Additional Supplied Modules, in your case
> tablefunc :
> 
> http://www.postgresql.org/docs/9.1/static/tablefunc.html


I evaluated tablefunc about a year and a half ago and found that it was not 
what I wanted because you have to explicitly list what you want the columns to 
be.  In some cases, there will be hundreds of columns in the pivoted table.  
The Reshape library in R can pivot tables without you even knowing ahead of 
time how many columns there are going to be.  Maybe I need to revisit the 
documentation in case I missed something or if new functionality has been added 
since I last looked.  

-Aram

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

[Joe Conway]

On 09/04/2012 12:17 PM, Aram Fingal wrote:
> On Sep 4, 2012, at 2:51 PM, Vincent Veyron wrote:
>> see the documentation for Additional Supplied Modules, in your
>> case tablefunc :
>> 
>> http://www.postgresql.org/docs/9.1/static/tablefunc.html
> 
> I evaluated tablefunc about a year and a half ago and found that it
> was not what I wanted because you have to explicitly list what you
> want the columns to be.  In some cases, there will be hundreds of
> columns in the pivoted table.  The Reshape library in R can pivot
> tables without you even knowing ahead of time how many columns there
> are going to be.

Sure, but you cannot return that reshaped table to postgres without
specifying the list of columns explicitly. That is because of how
postgres works internally and has nothing to do with whether you are
using crosstab from tablefunc, PL/R, or some hand-coded SQL statement to
build your crosstab.

But certainly if you can do all your work on the reshaped table within
the R environment, PL/R will be easier to use.

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 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] crosstab

[Aram Fingal]

On Sep 4, 2012, at 3:26 PM, Joe Conway wrote:

> On 09/04/2012 12:17 PM, Aram Fingal wrote:
>> On Sep 4, 2012, at 2:51 PM, Vincent Veyron wrote:
>>> see the documentation for Additional Supplied Modules, in your
>>> case tablefunc :
>>> 
>>> http://www.postgresql.org/docs/9.1/static/tablefunc.html
>> 
>> I evaluated tablefunc about a year and a half ago and found that it
>> was not what I wanted because you have to explicitly list what you
>> want the columns to be.  In some cases, there will be hundreds of
>> columns in the pivoted table.  The Reshape library in R can pivot
>> tables without you even knowing ahead of time how many columns there
>> are going to be.
> 
> Sure, but you cannot return that reshaped table to postgres without
> specifying the list of columns explicitly. That is because of how
> postgres works internally and has nothing to do with whether you are
> using crosstab from tablefunc, PL/R, or some hand-coded SQL statement to
> build your crosstab.
> 
> But certainly if you can do all your work on the reshaped table within
> the R environment, PL/R will be easier to use.


So, are you saying that if I do something like this:

copy(crosstab(source_sql, category_sql)) to '/output.csv' with csv;

Then I don't have to list what the columns are going to be?  In other words, I 
can skip the "AS (...)" clause which is shown in the examples in the tablefunc 
documentation?

-Aram

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

[Joe Conway]

On 09/04/2012 12:48 PM, Aram Fingal wrote:
> So, are you saying that if I do something like this:
> 
> copy(crosstab(source_sql, category_sql)) to '/output.csv' with csv;
> 
> Then I don't have to list what the columns are going to be?  In other
> words, I can skip the "AS (...)" clause which is shown in the
> examples in the tablefunc documentation?

No, sorry, but that is not what I'm saying :-(

Wen you run

  copy ("some query") to ...

it still requires postgres to execute "some query" and the standard
grammar rules will be applied. Postgres must be able to resolve data
types for the columns in the result, and therefore it needs you to
provide a column definition either at function creation time (via OUT
params or by explicit composite return type) or at execution time via
AS(...) clause.

Joe



-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 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] "Too far out of the mainstream"

[Gavin Flower]

On 05/09/12 05:35, Scott Marlowe wrote:

On Tue, Sep 4, 2012 at 11:28 AM, Mike Christensen  wrote:

On Mon, Sep 3, 2012 at 3:38 PM, Geert Mak  wrote:

I have been looking into heroku lately, they run on PostgreSQL -

https://postgres.heroku.com/postgres

Went out to lunch with a guy who worked for Redfin as well.  I guess
they're all Postgres over there too..

Since there's no reporting requirements for using postgresql
commercially etc, it's kind of a stealth database.  It's all over the
place and nobody knows it.  Meanwhile, finding qualified PostgreSQL
DBAs is pretty tough because of it's silent yet explosive growth in
usage.



Possibly there should be mandatory reporting of postgresql,
just like there is for other contagious diseases?  :-)

How about putting a notice in the release notes to encourage
people to report their usage?

Additionally, have bragging tables were people can register
their biggest postgresql database or table, peak
transactions per day, ... ?  Probably best to solicit
entries first, them companies can send in results to get
themselves in the top ten, or something.

I have read to emails to one of the postgresql lists,
where people in companies with 1000's of databases had
power failures and only the postgresql databases
restarted without special recovery actions required.
The other databases mentioned were Oracle, MySql, and
SQL Server.


Cheers,
Gavin

Re: [GENERAL] crosstab

[Aram Fingal]

On Sep 4, 2012, at 3:56 PM, Joe Conway wrote:

> On 09/04/2012 12:48 PM, Aram Fingal wrote:
>> So, are you saying that if I do something like this:
>> 
>> copy(crosstab(source_sql, category_sql)) to '/output.csv' with csv;
>> 
>> Then I don't have to list what the columns are going to be?  In other
>> words, I can skip the "AS (...)" clause which is shown in the
>> examples in the tablefunc documentation?
> 
> No, sorry, but that is not what I'm saying :-(
> 
> Wen you run
> 
>  copy ("some query") to ...
> 
> it still requires postgres to execute "some query" and the standard
> grammar rules will be applied. Postgres must be able to resolve data
> types for the columns in the result, and therefore it needs you to
> provide a column definition either at function creation time (via OUT
> params or by explicit composite return type) or at execution time via
> AS(...) clause.

So then, PL/R is not a solution to being able to pivot tables directly in 
PostgreSQL but I might be able to define a PL/R procedure which, for example, 
pivots tables and then uses the write.table() function of R to send the results 
to disk without returning any rows to PostgreSQL?  Such a procedure might prove 
be faster and more convenient than extracting the data from PostgreSQL into an 
R application layer and then writing to disk. 

-Aram

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

[Misa Simic]

No,

you must use AS (..) in crosstab query...

To actually, be able to create AS on "unkown" data in design time, what
should produce an "unknown" result and unknown number of columns we are
using Dynamic SQL to build AS part... (but of course function can't return
result - just for export to csv purposes...)

Inside PL/R you can take the same table as it is (unpivoted) as your
data.frame and then pivot it inside R using reshape package,,, And then
inside PL/R function do whatever you would like to do with data i.e export
to whatever... - but you cant return it pivoted as result of Postgres
function..

Kind Regards,

Misa



2012/9/4 Aram Fingal 

>
> On Sep 4, 2012, at 3:26 PM, Joe Conway wrote:
>
> > On 09/04/2012 12:17 PM, Aram Fingal wrote:
> >> On Sep 4, 2012, at 2:51 PM, Vincent Veyron wrote:
> >>> see the documentation for Additional Supplied Modules, in your
> >>> case tablefunc :
> >>>
> >>> http://www.postgresql.org/docs/9.1/static/tablefunc.html
> >>
> >> I evaluated tablefunc about a year and a half ago and found that it
> >> was not what I wanted because you have to explicitly list what you
> >> want the columns to be.  In some cases, there will be hundreds of
> >> columns in the pivoted table.  The Reshape library in R can pivot
> >> tables without you even knowing ahead of time how many columns there
> >> are going to be.
> >
> > Sure, but you cannot return that reshaped table to postgres without
> > specifying the list of columns explicitly. That is because of how
> > postgres works internally and has nothing to do with whether you are
> > using crosstab from tablefunc, PL/R, or some hand-coded SQL statement to
> > build your crosstab.
> >
> > But certainly if you can do all your work on the reshaped table within
> > the R environment, PL/R will be easier to use.
>
>
> So, are you saying that if I do something like this:
>
> copy(crosstab(source_sql, category_sql)) to '/output.csv' with csv;
>
> Then I don't have to list what the columns are going to be?  In other
> words, I can skip the "AS (...)" clause which is shown in the examples in
> the tablefunc documentation?
>
> -Aram
>
> --
> 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] crosstab

[Aram Fingal]

On Sep 4, 2012, at 4:18 PM, Misa Simic wrote:

> Inside PL/R you can take the same table as it is (unpivoted) as your 
> data.frame and then pivot it inside R using reshape package,,, And then 
> inside PL/R function do whatever you would like to do with data i.e export to 
> whatever... - but you cant return it pivoted as result of Postgres function..

 Okay, you just answered my question in reply to Joe Conway (crossed in the 
mail).  So I could just use write.table() from R to save to disk or I might 
even have the PL/R procedure write a heatmap or PCA plot to disk, again without 
returning any rows to PostgreSQL.

-Aram

Re: [GENERAL] crosstab

[A.M.]

On Sep 4, 2012, at 4:30 PM, Aram Fingal wrote:

> 
> On Sep 4, 2012, at 4:18 PM, Misa Simic wrote:
> 
>> Inside PL/R you can take the same table as it is (unpivoted) as your 
>> data.frame and then pivot it inside R using reshape package,,, And then 
>> inside PL/R function do whatever you would like to do with data i.e export 
>> to whatever... - but you cant return it pivoted as result of Postgres 
>> function..
> 
>  Okay, you just answered my question in reply to Joe Conway (crossed in the 
> mail).  So I could just use write.table() from R to save to disk or I might 
> even have the PL/R procedure write a heatmap or PCA plot to disk, again 
> without returning any rows to PostgreSQL.
> 
> -Aram

Or you could return the heatmap/plot as BYTEA data or use arrays as necessary.

Cheers,
M





-- 
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] "Too far out of the mainstream"

[Scott Marlowe]

On Tue, Sep 4, 2012 at 2:03 PM, Gavin Flower
 wrote:
> On 05/09/12 05:35, Scott Marlowe wrote:
> I have read to emails to one of the postgresql lists,
> where people in companies with 1000's of databases had
> power failures and only the postgresql databases
> restarted without special recovery actions required.
> The other databases mentioned were Oracle, MySql, and
> SQL Server.

That was likely me, tho it wasn't thousands, it was somewhere near 100
or so.  It was more a case of the other DBAs not doing their due
diligence and testing their hardware back 10 or so years ago, when
hard drives and RAID controllers often lied about fsync.


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

[Misa Simic]

That is correct,

Re you additional question re PL/R or get data from Pg in plain R - I am
not sure it will be that much faster inside PL/R...

I dont think there will be too much difference in execution time... Would
be good to test it... and would be good to share results with us :)

Thanks,

Misa



2012/9/4 Aram Fingal 

>
> On Sep 4, 2012, at 4:18 PM, Misa Simic wrote:
>
> Inside PL/R you can take the same table as it is (unpivoted) as your
> data.frame and then pivot it inside R using reshape package,,, And then
> inside PL/R function do whatever you would like to do with data i.e export
> to whatever... - but you cant return it pivoted as result of Postgres
> function..
>
>
>  Okay, you just answered my question in reply to Joe Conway (crossed in
> the mail).  So I could just use write.table() from R to save to disk or I
> might even have the PL/R procedure write a heatmap or PCA plot to disk,
> again without returning any rows to PostgreSQL.
>
> -Aram
>
>
>
>

Re: [GENERAL] crosstab

[Merlin Moncure]

On Tue, Sep 4, 2012 at 10:39 AM, punnoose
 wrote:
> hi all
> How could i use crostab to display variable number of columns. in the output
> There could be variable number of columns
> Regards
> Punnoose

No.   The workaround I use is to write a query generator in pl/pgsql
(you can also do it in the client) which queries the specific columns
that are going to be crosstabbed and generates a query textually.
That query is returned to the client and executed, essentially
bypassing the restriction.   The way functions are implemented in
postgres forces them to have a rigidly defined set of output
columns...it's annoying and everybody hates it but that's the way
things have to be for various reasons.

Looking to the future, stored procedures might not end up having this
restriction depending on how they are implemented.  Now that LATERAL
and standalone backends are or are in the process of being knocked out
I consider stored procedures to be one of the great unsolved features
of postgres.

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

[Joe Conway]

On 09/04/2012 01:23 PM, Aram Fingal wrote:
> So then, PL/R is not a solution to being able to pivot tables
> directly in PostgreSQL but I might be able to define a PL/R procedure
> which, for example, pivots tables and then uses the write.table()
> function of R to send the results to disk without returning any rows
> to PostgreSQL?  Such a procedure might prove be faster and more
> convenient than extracting the data from PostgreSQL into an R
> application layer and then writing to disk.

As mentioned somewhere else along this thread, I don't know that it will
be faster, but it may well be more convenient if your goal is to get a
CSV file in the end anyway.

Joe


-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 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] Maintaining a materialized view only on a replica

[Craig Ringer]

Subject changed to describe the problem. Reply in-line.

On 09/04/2012 07:57 PM, Herouth Maoz wrote:


The issue is that when an insert or an update is fired, I can't say
whether all the segments of the same transaction have been written yet,
and if only some of them were written, there is no guarantee on the
order in which they are written.


Does Slony-I provide stronger guarantees? If your replication doesn't 
guarantee ordering then you're going to have a very hard time doing this.



Is this
feasible at all? How would you achieve it?


I'd try to find a replication system that guaranteed ordering if at all 
possible.


--
Craig Ringer


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

[Aram Fingal]

On Sep 4, 2012, at 4:36 PM, A.M. wrote:

> Or you could return the heatmap/plot as BYTEA data or use arrays as necessary.

I was actually thinking exactly the same thing.  Part of the reason I use 
PostgreSQL for all my bioinformatics work is that there is a need to correctly 
associate analysis results with the data and experimental methods they come 
from.  I have tables for experimental runs, technicians, procedures, samples, 
drugs, etc. and I use foreign key constraints to connect them all.  The idea is 
to have all the information readily accessible to reproduce complex results in 
modern scientific fashion.  If I store the plots in the DB, I can connect them 
to all these basic information tables.

-Aram

[GENERAL] MS Sync framework sync SQL Server 2012 to PostgreSQL 9

[Claire Chang]

Has anyone done that successfully? Is there any better way to sync data between 
SQL server 2012 and Postgres SQL 9?

Thanks,
Claire

Re: [GENERAL] crosstab

[Joe Conway]

On 09/04/2012 02:59 PM, Aram Fingal wrote:
> On Sep 4, 2012, at 4:36 PM, A.M. wrote:
> 
>> Or you could return the heatmap/plot as BYTEA data or use arrays as
>> necessary.
> 
> I was actually thinking exactly the same thing.  Part of the reason I
> use PostgreSQL for all my bioinformatics work is that there is a need to
> correctly associate analysis results with the data and experimental
> methods they come from.  I have tables for experimental runs,
> technicians, procedures, samples, drugs, etc. and I use foreign key
> constraints to connect them all.  The idea is to have all the
> information readily accessible to reproduce complex results in modern
> scientific fashion.  If I store the plots in the DB, I can connect them
> to all these basic information tables.

You can return your results (or some intermediate) object in serialized
form as bytea from a PL/R function and store it in a table along with
the basic experimental info. Then later if you pass the serialized
object back into another PL/R function as a bytea argument, it gets
reconstituted as the original R object.

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 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] crosstab

[Misa Simic]

Hi Joe,

Do you maybe know, is it possible inside PL/R to call another PL/R function
- but take result as R object (whatever R function returns)?

If we take this scenario for example (Take some data from DB, pivot them,
and save it as CSV)... pseudo code would be:

-Execute SQL query
-pivot returned data
-save it to CSV...

of course everything could be in one function, but I would organize my code
in much more... because of for example, if I need tomorrow get the same
thing and save it to PDF, or Save it to graph etc... I could copy/paste the
same function and change code in step 3... but if something tomorrow should
be changed in step 1 or 2 - I would need to make the same change on 3
places... In pure R for example, I would have at least: getPivotedData
function and then SaveToCSV would call getPivotedData() and save it in CSV,
SaveToPdf would call the same getPivotedData() and save it to PDF... etc,
then tomorrow if something should be changed in first two steps - I would
change just getPivotedData function... and cover whole 3 scenarios...

Now, of course PostgreSQL function can't return  R object, but plv8 has
solved that problem as plv8 Postgres Function returns record - what is an
v8 object...

Then inside one plv8 function, we say
plv8.find_function("myv8FunctionWhatReturnsv8Object"), call it and takes
actuall result and do with that whatever need to be done inside v8
environment...

During I have written this mail - you have replied that actually my
question could be acomplished with serialise/deserialise... but I am not
sure I would take that approach - because of "methods" of an object would
be lost during serialize/deserialize... (what btw forced me to check it in
plv8 - and it seems they use the same approach internally in behind :(  -
though doesn't make sense because of find_function works just with plv8
postgres functions)


Thanks,

Misa




2012/9/4 Joe Conway 

> On 09/04/2012 01:23 PM, Aram Fingal wrote:
> > So then, PL/R is not a solution to being able to pivot tables
> > directly in PostgreSQL but I might be able to define a PL/R procedure
> > which, for example, pivots tables and then uses the write.table()
> > function of R to send the results to disk without returning any rows
> > to PostgreSQL?  Such a procedure might prove be faster and more
> > convenient than extracting the data from PostgreSQL into an R
> > application layer and then writing to disk.
>
> As mentioned somewhere else along this thread, I don't know that it will
> be faster, but it may well be more convenient if your goal is to get a
> CSV file in the end anyway.
>
> Joe
>
>
> --
> Joe Conway
> credativ LLC: http://www.credativ.us
> Linux, PostgreSQL, and general Open Source
> Training, Service, Consulting, & 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] crosstab

[Misa Simic]

>
> (what btw forced me to check it in plv8 - and it seems they use the same
> approach internally in behind :(  - though doesn't make sense because of
> find_function works just with plv8 postgres functions)


Just realised I have called wrong method - so above is not true... it
returns real v8 object, and methods of that object can be called :)

Sorry,

Misa


2012/9/5 Misa Simic 

> Hi Joe,
>
> Do you maybe know, is it possible inside PL/R to call another PL/R
> function - but take result as R object (whatever R function returns)?
>
> If we take this scenario for example (Take some data from DB, pivot them,
> and save it as CSV)... pseudo code would be:
>
> -Execute SQL query
> -pivot returned data
> -save it to CSV...
>
> of course everything could be in one function, but I would organize my
> code in much more... because of for example, if I need tomorrow get the
> same thing and save it to PDF, or Save it to graph etc... I could
> copy/paste the same function and change code in step 3... but if something
> tomorrow should be changed in step 1 or 2 - I would need to make the same
> change on 3 places... In pure R for example, I would have at least:
> getPivotedData function and then SaveToCSV would call getPivotedData() and
> save it in CSV, SaveToPdf would call the same getPivotedData() and save it
> to PDF... etc, then tomorrow if something should be changed in first two
> steps - I would change just getPivotedData function... and cover whole 3
> scenarios...
>
> Now, of course PostgreSQL function can't return  R object, but plv8 has
> solved that problem as plv8 Postgres Function returns record - what is an
> v8 object...
>
> Then inside one plv8 function, we say
> plv8.find_function("myv8FunctionWhatReturnsv8Object"), call it and takes
> actuall result and do with that whatever need to be done inside v8
> environment...
>
> During I have written this mail - you have replied that actually my
> question could be acomplished with serialise/deserialise... but I am not
> sure I would take that approach - because of "methods" of an object would
> be lost during serialize/deserialize... (what btw forced me to check it in
> plv8 - and it seems they use the same approach internally in behind :(  -
> though doesn't make sense because of find_function works just with plv8
> postgres functions)
>
>
> Thanks,
>
> Misa
>
>
>
>
> 2012/9/4 Joe Conway 
>
>> On 09/04/2012 01:23 PM, Aram Fingal wrote:
>> > So then, PL/R is not a solution to being able to pivot tables
>> > directly in PostgreSQL but I might be able to define a PL/R procedure
>> > which, for example, pivots tables and then uses the write.table()
>> > function of R to send the results to disk without returning any rows
>> > to PostgreSQL?  Such a procedure might prove be faster and more
>> > convenient than extracting the data from PostgreSQL into an R
>> > application layer and then writing to disk.
>>
>> As mentioned somewhere else along this thread, I don't know that it will
>> be faster, but it may well be more convenient if your goal is to get a
>> CSV file in the end anyway.
>>
>> Joe
>>
>>
>> --
>> Joe Conway
>> credativ LLC: http://www.credativ.us
>> Linux, PostgreSQL, and general Open Source
>> Training, Service, Consulting, & 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] MS Sync framework sync SQL Server 2012 to PostgreSQL 9

[Craig Ringer]

On 09/05/2012 09:11 AM, Claire Chang wrote:

Has anyone done that successfully? Is there any better way to sync data
between SQL server 2012 and Postgres SQL 9?



If you're going to cross-post between Stack Overflow and the PostgreSQL 
mailing list, please consider mentioning the fact to reduce the amount 
of wasted/duplicated time and effort that might otherwise arise.


http://stackoverflow.com/questions/12272676/use-ms-sync-framework-to-sync-sql-server-2012-to-postgres-9

--
Craig Ringer


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

[Joe Conway]

On 09/04/2012 04:45 PM, Misa Simic wrote:
> Hi Joe,
> 
> Do you maybe know, is it possible inside PL/R to call another PL/R
> function - but take result as R object (whatever R function returns)?

Mmmm, what about using plr_modules table?

It creates native R functions that get installed into your R interpreter
when R is first initialized.

http://www.joeconway.com/plr/doc/plr-module-funcs.html

Joe




-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 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] crosstab

[Misa Simic]

Excellent :)

Thanks - looks as acceptable workaround...

Many thanks,

Misa

2012/9/5 Joe Conway 

> On 09/04/2012 04:45 PM, Misa Simic wrote:
> > Hi Joe,
> >
> > Do you maybe know, is it possible inside PL/R to call another PL/R
> > function - but take result as R object (whatever R function returns)?
>
> Mmmm, what about using plr_modules table?
>
> It creates native R functions that get installed into your R interpreter
> when R is first initialized.
>
> http://www.joeconway.com/plr/doc/plr-module-funcs.html
>
> Joe
>
>
>
>
> --
> Joe Conway
> credativ LLC: http://www.credativ.us
> Linux, PostgreSQL, and general Open Source
> Training, Service, Consulting, & 24x7 Support
>
>
>

Re: [GENERAL] "Too far out of the mainstream"

[Scott Marlowe]

On Tue, Sep 4, 2012 at 3:13 PM, Gavin Flower
 wrote:
> On 05/09/12 08:38, Scott Marlowe wrote:
>
> On Tue, Sep 4, 2012 at 2:03 PM, Gavin Flower
>  wrote:
>
> On 05/09/12 05:35, Scott Marlowe wrote:
> I have read to emails to one of the postgresql lists,
> where people in companies with 1000's of databases had
> power failures and only the postgresql databases
> restarted without special recovery actions required.
> The other databases mentioned were Oracle, MySql, and
> SQL Server.
>
> That was likely me, tho it wasn't thousands, it was somewhere near 100
> or so.  It was more a case of the other DBAs not doing their due
> diligence and testing their hardware back 10 or so years ago, when
> hard drives and RAID controllers often lied about fsync.
>
> I fairly certain the 2 emails were from different people, and I read them
> within the last 12 (6?) months.

I may well have written about it in the last 6 to 12 months, that
doesn't mean it happened in the last 6 to 12 months.


-- 
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] "Too far out of the mainstream"

[Geert Mak]

Since this PGCon 2011 page has no slides -

http://www.pgcon.org/2011/schedule/events/361.en.html

I Googled for "sharding" and "PostgreSQL" and I found this -

http://www.databasesoup.com/2012/04/sharding-postgres-with-instagram.html

"On Tuesday last week we had a terrific SFPUG meeting at which Mike Kreiger of 
Instagram explained how they grew and eventually sharded their 2TB of Postgres 
data to support 27 million users."

Here are the slides -

http://media.postgresql.org/sfpug/instagram_sfpug.pdf

--

Is somebody collecting and organizing such things? I could not find anything 
about Instagram on www.postgresql.org.

-- 
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 process got stuck in "notify interrupt waiting" status

[Aleksey Tsalolikhin]

Hi.

We use LISTEN/NOTIFY quite a bit but today something unusual (bad) happened.

Number of processes waiting for a lock just started to go up up up.

I finally found the object being locked was pg_listener which
RhodiumToad on IRC kindly informed happens during LISTEN/NOTIFY.  The
process that had the lock (in pg_locks it had granted = t ) was shown
by ps in status "notify interrupt waiting" and has had the lock for
over half an hour.  (Usually these notifications are very quick.)

the process would not respond to kill, so I kill -9'ed

The only reference I could find to a similar problem was at
http://archives.postgresql.org/pgsql-performance/2008-02/msg00345.php
which seemed to indicate a process should not be in this state for
very long.

We are on postgres 8.4.12.

I'd like to figure out what happened.

There is a web server that talks to this database server (amongst
other clients), and the client addr and port mapped to this web
server, but there was no process on the web server matching the port
number.  that's when I decided to kill the postgres process.

Anything I should know or read up on?  Any suggestions?

I'd like the system to be able to recover, and for the process to
terminate if the client is no longer around.

Best,
Aleksey


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

[GENERAL] regexp_matches question

[Sergio Basurto]

I am using regexp_matches in a function like this

create or replace function test (v_string   in text) returns varchar
as $$
declare
i_strings   text[];
i_stringtext[];

i_strings := regexp_matches(v_string,E'[a-zA-Z0-9:\\s\\-\\.#%
]*:[A-Za-z0-9\\s\\-\\.#%]+','g');

-- Then I use  the results
foreach i_string slice 1 in array i_strings
loop
raise notice 'row = %',i_string;
end loop;

when I run the function like this:

select test('1:Warehouse1;2:Warehouse2;');

postgresql complains:
ERROR:  query "SELECT regexp_matches(v_string,E'[a-zA-Z0-9:\\s\\-\\.#%
]*:[A-Za-z0-9\\s\\-\\.#%]+','g')" returned more than one row

Why postgres is sending the ERROR?

Off course I am expecting more than one row!, that's why is in a foreach
loop in the first place.

If I run:
select regexp_matches('1:Warehouse1;2:Warehouse2;',E'[a-zA-Z0-9:\\s\\-\
\.#%]*:[A-Za-z0-9\\s\\-\\.#%]+','g');
regexp_matches 

{1:Warehouse1}
{2:Warehouse2}
(2 rows)

I am doing something wrong?

Regards,

Re: [GENERAL] regexp_matches question

[David Johnston]

On Sep 4, 2012, at 21:39, Sergio Basurto  wrote:

> I am using regexp_matches in a function like this
> 
> create or replace function test (v_string   in text) returns varchar as $$
> declare
> i_strings   text[];
> i_stringtext[];
> 
> i_strings := 
> regexp_matches(v_string,E'[a-zA-Z0-9:\\s\\-\\.#%]*:[A-Za-z0-9\\s\\-\\.#%]+','g');

You can store a single array value into i_strings.  It does not magically 
convert a multi-row result into an array.  You can use ARRAY_AGG to do so or 
execute the query directly as part of the loop while using a "record" variable 
to store the current row's value(s). 

> 
> -- Then I use  the results
> foreach i_string slice 1 in array i_strings
> loop
> raise notice 'row = %',i_string;
> end loop;
> 
> when I run the function like this:
> 
> select test('1:Warehouse1;2:Warehouse2;');
> 
> postgresql complains:
> ERROR:  query "SELECT 
> regexp_matches(v_string,E'[a-zA-Z0-9:\\s\\-\\.#%]*:[A-Za-z0-9\\s\\-\\.#%]+','g')"
>  returned more than one row
> 
> Why postgres is sending the ERROR?
> 
> Off course I am expecting more than one row!, that's why is in a foreach loop 
> in the first place.
> 
> If I run:
> select 
> regexp_matches('1:Warehouse1;2:Warehouse2;',E'[a-zA-Z0-9:\\s\\-\\.#%]*:[A-Za-z0-9\\s\\-\\.#%]+','g');
> regexp_matches 
> 
> {1:Warehouse1}
> {2:Warehouse2}
> (2 rows)
> 
> I am doing something wrong?

Note that because you do not use grouping in your expression there is only a 
single array "cell" in each row - but there could be more than one in which 
case your for-each above would effectively loop through each sub-component of 
the match.

> 
> Regards,
>

David J.

-- 
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] postgres process got stuck in "notify interrupt waiting" status

[Aleksey Tsalolikhin]

BTW, after I signalled TERM, the process status changed from

notify interrupt waiting

to

notify interrupt waiting waiting

which I thought looked kind of odd.

Then I signalled KILL.

Aleksey

On Tue, Sep 4, 2012 at 6:21 PM, Aleksey Tsalolikhin
 wrote:
> Hi.
>
> We use LISTEN/NOTIFY quite a bit but today something unusual (bad) happened.
>
> Number of processes waiting for a lock just started to go up up up.
>
> I finally found the object being locked was pg_listener which
> RhodiumToad on IRC kindly informed happens during LISTEN/NOTIFY.  The
> process that had the lock (in pg_locks it had granted = t ) was shown
> by ps in status "notify interrupt waiting" and has had the lock for
> over half an hour.  (Usually these notifications are very quick.)
>
> the process would not respond to kill, so I kill -9'ed
>
> The only reference I could find to a similar problem was at
> http://archives.postgresql.org/pgsql-performance/2008-02/msg00345.php
> which seemed to indicate a process should not be in this state for
> very long.
>
> We are on postgres 8.4.12.
>
> I'd like to figure out what happened.
>
> There is a web server that talks to this database server (amongst
> other clients), and the client addr and port mapped to this web
> server, but there was no process on the web server matching the port
> number.  that's when I decided to kill the postgres process.
>
> Anything I should know or read up on?  Any suggestions?
>
> I'd like the system to be able to recover, and for the process to
> terminate if the client is no longer around.
>
> Best,
> Aleksey



-- 
Upcoming Trainings:
"Editing with vi" 31 Aug 2012 at LinuxCon North America in San Diego,
CA (http://lcna2012.sched.org/speaker/alekseytsalolikhin)
"Time Management for System Administrators" 28 Sep 2012 at Ohio Linux
Fest (http://ohiolinux.org/register)
"Editing with vi" 28 Sep 2012 at Ohio Linux Fest (http://ohiolinux.org/register)
"Automating System Administration with CFEngine 3" 22-25 Oct 2012 in
Palo Alto, CA (http://www.eventbrite.com/event/3388161081)


-- 
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] "Too far out of the mainstream"

[Chris Travers]

On Tue, Sep 4, 2012 at 10:35 AM, Scott Marlowe wrote:

>
>
> Since there's no reporting requirements for using postgresql
> commercially etc, it's kind of a stealth database.  It's all over the
> place and nobody knows it.  Meanwhile, finding qualified PostgreSQL
> DBAs is pretty tough because of it's silent yet explosive growth in
> usage.
>
>
> Ever since I have been doing database stuff (since 1999), PostgreSQL has
been the go-to db in the open source world for complex business
applications, first because MySQL didn't have transactions and later
because it's just better.  I don't think the stealth phenomenon is just a
matter of no reporting requirements.  After all MySQL's exposure is way
beyond its reporting requirements.

Rather here's the thing:  You have basically two kinds of uses for RDBMS's
out there.  The first is for internal information storage, centralization,
and management, and the second is as a platform for applications you are
going to sell or otherwise distribute.  PostgreSQL has always been far more
at home in the former than the latter.   This is the exact opposite of
MySQL which is today really built almost exclusively for the latter at the
expense of the former (sql_mode being subject to each application's
discretion and the like).

So people are using PostgreSQL in roles that aren't very visible anyway,
DBA's are usually coming to PostgreSQL from other RDBMS's, and few
applications are really distributed for PostgreSQL.

I do think that is changing.  Last year I went to the Malaysian Government
Open Source Software convention and was amazed at the visibility of
PostgreSQL.  We were the only booth advertising services for official
PostgreSQL versions but advertised EnterpriseDB resellers were more common
than people advertising MySQL services even when you add Oracle to the mix
(they were there too).  At the same time, my sense from talking with people
there was that despite the way that EDB's marketing had framed the official
version as the "community edition," the official version was by far the
most common open source RDBMS used in the public sector in Malaysia.  Not
only this but there was significant interest in moving more db's to
PostgreSQL, but the big limitation is that everyone who knows PostgreSQL
already has a job.

Best Wishes,
Chris Travers

Re: [GENERAL] postgres process got stuck in "notify interrupt waiting" status

[John R Pierce]

On 09/04/12 7:09 PM, Aleksey Tsalolikhin wrote:

BTW, after I signalled TERM, the process status changed from

notify interrupt waiting

to

notify interrupt waiting waiting

which I thought looked kind of odd.

Then I signalled KILL.


was this a client process or a postgres process?   kill -9 on postgres 
processes can easily trigger data corruption.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast



--
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] postgres process got stuck in "notify interrupt waiting" status

[Aleksey Tsalolikhin]

On Tue, Sep 4, 2012 at 7:21 PM, John R Pierce  wrote:
> On 09/04/12 7:09 PM, Aleksey Tsalolikhin wrote:
>>
>> BTW, after I signalled TERM, the process status changed from
>>
>> notify interrupt waiting
>>
>> to
>>
>> notify interrupt waiting waiting
>>
>> which I thought looked kind of odd.
>>
>> Then I signalled KILL.
>
>
> was this a client process or a postgres process?   kill -9 on postgres
> processes can easily trigger data corruption.

This was a postgres process.  i certainly won't signal KILL anymore to
postgres processes, thanks for that warning, John.

Aleksey


-- 
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] "Too far out of the mainstream"

[Gavin Flower]

On 05/09/12 08:38, Scott Marlowe wrote:

On Tue, Sep 4, 2012 at 2:03 PM, Gavin Flower
 wrote:

On 05/09/12 05:35, Scott Marlowe wrote:
I have read to emails to one of the postgresql lists,
where people in companies with 1000's of databases had
power failures and only the postgresql databases
restarted without special recovery actions required.
The other databases mentioned were Oracle, MySql, and
SQL Server.

That was likely me, tho it wasn't thousands, it was somewhere near 100
or so.  It was more a case of the other DBAs not doing their due
diligence and testing their hardware back 10 or so years ago, when
hard drives and RAID controllers often lied about fsync.


I fairly certain the 2 emails were from different people, and I read 
them within the last 12 (6?) months.

Re: [GENERAL] "Too far out of the mainstream"

[Ondrej Ivanič]

Hi,

On 5 September 2012 12:14, Chris Travers  wrote:

> So people are using PostgreSQL in roles that aren't very visible anyway,
> DBA's are usually coming to PostgreSQL from other RDBMS's, and few
> applications are really distributed for PostgreSQL.
> 
>  Not only
> this but there was significant interest in moving more db's to PostgreSQL,
> but the big limitation is that everyone who knows PostgreSQL already has a
> job.

Some shops are going opposite way -- from PostgreSQL to MySQL like
databases because of missing replication features. The 9.1 caught up
but there is no multi-master replication like in Percona's XtraDB
cluster: http://www.percona.com/software/percona-xtradb-cluster/

Postgres-XC can solve this missing multi-master replication issue but
"nobody" knows that this project exists. Another project is "Galera
Cluster for PostgreSQL" (Galera is used in XtraDB) but this looks like
vaporware...

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.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] Are there any options to parallelize queries?

[Michael Paquier]

On Wed, Aug 22, 2012 at 7:21 PM, Chris Travers wrote:

> Does Postgres-XC support query parallelism (at least splitting the
> query up for portions that run on different nodes)?  They just
> released 1.0.  I don't know if this sort of thing is supported there
> and it might be overkill at any rate.
>
Yes it does.
There are things implemented in Postgres-XC planner that allows to ship to
remote nodes portion of the query if necessary.
-- 
Michael Paquier
http://michael.otacoo.com

Re: [GENERAL] Are there any options to parallelize queries?

[Aleksey Tsalolikhin]

Hi, Seref.  You might want to take a look at Stado:
http://www.slideshare.net/jim_mlodgenski/scaling-postresql-with-stado

Best,
-at


-- 
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] "Too far out of the mainstream"

[Chris Travers]

On Tue, Sep 4, 2012 at 9:06 PM, Ondrej Ivanič wrote:

> Hi,
>
> On 5 September 2012 12:14, Chris Travers  wrote:
>
> > So people are using PostgreSQL in roles that aren't very visible anyway,
> > DBA's are usually coming to PostgreSQL from other RDBMS's, and few
> > applications are really distributed for PostgreSQL.
> > 
> >  Not only
> > this but there was significant interest in moving more db's to
> PostgreSQL,
> > but the big limitation is that everyone who knows PostgreSQL already has
> a
> > job.
>
> Some shops are going opposite way -- from PostgreSQL to MySQL like
> databases because of missing replication features. The 9.1 caught up
> but there is no multi-master replication like in Percona's XtraDB
> cluster: http://www.percona.com/software/percona-xtradb-cluster/
>
> Postgres-XC can solve this missing multi-master replication issue but
> "nobody" knows that this project exists. Another project is "Galera
> Cluster for PostgreSQL" (Galera is used in XtraDB) but this looks like
> vaporware...
>

To be fair I was speaking specifically of the folks I talked to at
MYGOSSCON.  The major question was "Do we really need Oracle?"

Also I don't know about others but I have been trying to highlight
Postgres-XC wherever it seems appropriate.

Best Wishes,
Chris Travers