Re: [GENERAL] PostgreSql support for Windows 8

[Brar Piening]

There should be no Problems runnig PostgreSQL on Windows 8 - it's still 
Windows.

(I've tested 8.3.20 and yesterdays git head)
Regards,
Brar

K, Baraneetharan wrote:


Hi there,

Could you please let me know PostgreSql supports Windows 8 /Windows 2012?

If so pls provide the version from which its starts supporting Windows8.

Thanks & Regards,

Baranee

Re: [GENERAL] See the WHERE clause of a partial index

[Paul Jungwirth]

> I think the OID
> argument will need to be the table not the index, but not 100% sure.

Yep, that's true. :-)

Paul


-- 
_
Pulchritudo splendor veritatis.


-- 
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] See the WHERE clause of a partial index

[Paul Jungwirth]

> pg_get_indexdef() should help.  If you really want just the WHERE
> clause, possibly pg_get_expr() would work, but I've not tried it on
> index clauses.

Thank you for such a quick response!

pg_get_indexdef is very helpful:

> select pg_get_indexdef(223630);

pg_get_indexdef

-
 CREATE UNIQUE INDEX
index_user_languages_on_user_id_and_language_id ON user_languages
USING btree (user_id, language_id) WHERE (deleted_at IS NULL)
(1 row)

It'd be great to get just the WHERE clause if possible, although I can
work around it if not. I couldn't find much documentation re
pg_get_expr. Does this message mean I can't use it, or am I just doing
something wrong?:

> select pg_get_expr('{NULLTEST :arg {VAR :varno 1 :varattno 6
:vartype 1114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1
:varoattno 6 :location 128} :nulltesttype 0 :argisrow false}',
223630);
ERROR:  cannot accept a value of type pg_node_tree
LINE 1: select pg_get_expr('{NULLTEST :arg {VAR :varno 1 :varattno 6...

Thank you for your help!
Paul


-- 
_
Pulchritudo splendor veritatis.


-- 
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] See the WHERE clause of a partial index

[Tom Lane]

Paul Jungwirth  writes:
> It'd be great to get just the WHERE clause if possible, although I can
> work around it if not. I couldn't find much documentation re
> pg_get_expr. Does this message mean I can't use it, or am I just doing
> something wrong?:

>> select pg_get_expr('{NULLTEST :arg {VAR :varno 1 :varattno 6
> :vartype 1114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1
> :varoattno 6 :location 128} :nulltesttype 0 :argisrow false}',
> 223630);
> ERROR:  cannot accept a value of type pg_node_tree
> LINE 1: select pg_get_expr('{NULLTEST :arg {VAR :varno 1 :varattno 6...


That's a security restriction (if you could pass random text to
pg_get_expr, you could probably crash it).  If you feed the actual
pg_index.indpred column to it, that won't happen.  I think the OID
argument will need to be the table not the index, but not 100% sure.

regards, tom lane


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

Re: [GENERAL] See the WHERE clause of a partial index

[Paul Jungwirth]

> It'd be great to get just the WHERE clause if possible, although I can
> work around it if not. I couldn't find much documentation re
> pg_get_expr.

To answer my own question, this works:

> select pg_get_expr(indpred, indrelid) from pg_index where
indexrelid = 223630;
 pg_get_expr
--
 (deleted_at IS NULL)
(1 row)

So I guess the problem was trying to get a pg_node_tree out of a string.

Thanks again!
Paul

-- 
_
Pulchritudo splendor veritatis.


-- 
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] Amazon High I/O instances

[Sébastien Lorion]

Disks are doing 150 read + 90 write ops/s when they should be able to do a
total of 1000 iops each as currently configured (this is the max that can
be set). Total bandwidth is 1000mb/s each too. So clearly, either there is
something wrong with ZFS/FreeBSD on Amazon (either because of config or
something deeper) or PostgreSQL is not fully utilizing the hardware, again
because of config or some other issue.

I will make another test instance with pg 9.2 this time.

Concerning shared_buffers and wal_buffers, I found this article interesting:

http://rhaas.blogspot.ca/2012/03/tuning-sharedbuffers-and-walbuffers.html

Sébastien

On Thu, Sep 13, 2012 at 5:28 PM, John R Pierce  wrote:

> On 09/13/12 2:08 PM, Sébastien Lorion wrote:
>
>> I started db creation over, this time with 16GB maintenance_work_mem and
>> fsync=off and it does not seem to have a great effect. After again 5 hours,
>> during index creation, disk and cpu are barely used: 95% idle and 2-3 MB/s
>> writes (150 reads/s, 90 writes/s).
>>
>
> I've never had to set maintenance_work_mem any higher than 1gb for plenty
> good enough performance.
>
> whats the %busy on the disk ?   if you have a slow disk device (such as a
> shared virtual disk), 90 write/sec may be all its good for. MB/s is fairly
> meaningless when dealing with random committed writes.
>
>
>
>
>
>
> --
> 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
>

[GENERAL] PostgreSql support for Windows 8

[K, Baraneetharan]

Hi there,

Could you please let me know PostgreSql supports Windows 8 /Windows 2012?
If so pls provide the version from which its starts supporting Windows8.

Thanks & Regards,
Baranee

[GENERAL] How to access the extension's operator installed with schema ?

[xoip]

Hello,

I'm having a problem accessing an extension's operator installed with a
schema.

PostgreSQL version: 9.1

CREATE SCHEMA inty AUTHORIZATION psql_user;
CREATE EXTENSION intarray SCHEMA inty;
SELECT inty.uniq(ARRAY[1,2,3]); --> OK, result: {1,2,3}

How can I access the operator with inty schema?

SELECT ARRAY[1,2,3] + 10; --> returns "ERROR: operator does not exist:
integer[] + integer" because the default operator (+) has other definition
SELECT ARRAY[1,2,3] fast.+ 10; --> NOT OK



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-access-the-extension-s-operator-installed-with-schema-tp5724032.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] How to access the extension's operator installed with schema ?

[Tom Lane]

xoip  writes:
> CREATE SCHEMA inty AUTHORIZATION psql_user;
> CREATE EXTENSION intarray SCHEMA inty;
> SELECT inty.uniq(ARRAY[1,2,3]); --> OK, result: {1,2,3}

> How can I access the operator with inty schema?

The easy way is to change your search_path to include inty.
If you really don't want to do that, you can write
OPERATOR(inty.+)
but that's pretty inconvenient.

regards, tom lane


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

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

[jam3]

Here is the 9.0 versionand yes I meant maintenance_work_mem

# Postgresql Memory Configuration and Sizing Script
# By: James Morton
# Last Updated 05/16/2012
#
# Note This script is meant to be used with by the postgres user with a
configured .pgpass file
# It is for Postgres version 9 running on Linux and only tested on Centos 5
#
# Reference -
http://eee.postgresql.org/docs/9.1/static/kernel-resources.html
#
# This script should be run after changing any of the following in the
postgresconf.sql
#
# maximum_connections
# block_size
# wal_block_size
# wal_buffers
# max_locks_per_transaction
# max_prepared_transactions
# shared_buffers
#
# or after changing the following OS kernel values
#
# SHMMAX
# SHMALL
# SHMMNI
# SEMMNS
# SEMMNI
# SEMMSL

#!/bin/bash

#Input Variables
DBNAME=$1
USERNAME=$2


clear
echo
echo "Postgresql Shared Memory Estimates"
echo

echo
echo "Local Postgres Configuration settings"
echo

#Postgresql Version
PSQL="psql "$DBNAME" -U "$USERNAME
PG_VERSION=$($PSQL --version)
echo "PG_VERSION:"$PG_VERSION

#Postgresql Block Size
PG_BLKSIZ=$($PSQL -t -c "show block_size;")
echo "PG_BLKSIZ:"$PG_BLKSIZ

#Maximum Connections
PG_MAXCON=$($PSQL -t -c "show max_connections;")
echo "PG_MAXCON:"$PG_MAXCON

#Maximum Locks per Tansaction
PG_MAXLPT=$($PSQL -t -c "show max_locks_per_transaction;")
echo "PG_MAXLPT:"$PG_MAXLPT

#Maximum Prepared Transactions, 2 phase commit, might not configured in
postresql.conf
let PG_MAXPRT=0
PG_MAXPRT=$($PSQL -t -c "show max_prepared_transactions;")
echo "PG_MAXPRT:"$PG_MAXPRT

#Shared Buffers
PG_SHABUF=$($PSQL -t -c "show shared_buffers;")
PG_SHABUF=$(echo $PG_SHABUF | sed s/MB//)
echo "PG_SHABUF:" $PG_SHABUF
PG_SHABUF_IN_B=$(( $PG_SHABUF * 1024 * 1024 ))
echo "PG_SHABUF_IN_B:"$PG_SHABUF_IN_B
PG_SHABUF_NUMOFBUF=$(($PG_SHABUF_IN_B / $PG_BLKSIZ))
echo "PG_SHABUF_NUMOFBUF:"$PG_SHABUF_NUMOFBUF

#WAL Block Size
PG_WALBLK=$($PSQL -t -c "show wal_block_size;")
echo "PG_WALBLK:"$PG_WALBLK

#WAL Buffers
PG_WALBUF=$($PSQL -t -c "show wal_buffers;")
PG_WALBUF=$(echo $PG_WALBUF | sed s/MB//)
echo "PG_WALBUF:" $PG_WALBUF
PG_WALBUF_IN_B=$(( $PG_WALBUF * 1024 * 1024 ))
echo "PG_WALBUF_IN_B:"$PG_WALBUF_IN_B
PG_WALBUF_NUMOFBUF=$(($PG_WALBUF_IN_B / $PG_WALBLK))
echo "PG_WALBUF_NUMOFBUF:"$PG_WALBUF_NUMOFBUF

#Autovacuum workers
PG_ATVWRK=$($PSQL -t -c "show autovacuum_max_workers;")
echo "PG_ATVWRK:"$PG_ATVWRK

#maintainance_work_mem
PG_MNTWKM=$($PSQL -t -c "show maintenance_work_mem;")
PG_MNTWKM=$(echo $PG_MNTWKM | sed s/MB//)
echo "PG_MNTWKM:"$PG_MNTWKM

#effective_cache_size
PG_EFCHSZ=$($PSQL -t -c "show effective_cache_size;")
echo "PG_EFCHSZ:"$PG_EFCHSZ

echo
echo "OS Memory settings"
echo

PAGE_SIZE=$(getconf PAGE_SIZE)
echo "PAGE_SIZE:"$PAGE_SIZE

PHYS_PAGES=$(getconf _PHYS_PAGES)
echo "PHYS_PAGES:"$PHYS_PAGES

TOTAL_MEM_IN_MB=$(( ((PAGE_SIZE * PHYS_PAGES) / 1024) / 1024 ))
echo "TOTAL_MEM_IN_MB:"$TOTAL_MEM_IN_MB

echo
echo "Current Kernel Shared Memory Settings"
echo

#get os mem settings into vars
CUR_SHMMAX_IN_B=$(cat /proc/sys/kernel/shmmax)
echo "CUR_SHMMAX_IN_B:"$CUR_SHMMAX_IN_B

CUR_SHMMAX_IN_MB=$(( (CUR_SHMMAX_IN_B / 1024) / 1024 )) 
echo "CUR_SHMMAX_IN_MB:"$CUR_SHMMAX_IN_MB

CUR_SHMALL=$(cat /proc/sys/kernel/shmall)
echo "CUR_SHMALL:" $CUR_SHMALL

CUR_SHMALL_IN_MB=$(( (CUR_SHMALL / 1024) / 1024 ))
echo "CUR_SHMALL_IN_MB:"$CUR_SHMALL_IN_MB

CUR_SHMMNI=$(cat /proc/sys/kernel/shmmni)
echo "CUR_SHMMNI:" $CUR_SHMMNI

echo
echo "Current Kernel Semaphore Settings"
echo

CUR_SEMMNI=$( cat /proc/sys/kernel/sem | awk '{print $4}' )
echo "CUR_SEMMNI:"$CUR_SEMMNI

CUR_SEMMNS=$( cat /proc/sys/kernel/sem | awk '{print $2}' )
echo "CUR_SEMMNS:"$CUR_SEMMNS

CUR_SEMMSL=$( cat /proc/sys/kernel/sem | awk '{print $1}' )
echo "CUR_SEMMSL:"$CUR_SEMMSL

PG_RECSET_SEMMNI=$(printf "%.0f" $(echo "scale=2;($PG_MAXCON+$PG_ATVWRK+4) /
16" | bc))
echo "PG_RECSET_SEMMNI:"$PG_RECSET_SEMMNI

PG_RECSET_SEMMNS=$(printf "%.0f" $(echo "scale=2;(($PG_MAXCON+$PG_ATVWRK+4)
/ 16)*17" | bc))
echo "PG_RECSET_SEMMNS:"$PG_RECSET_SEMMNS

echo
echo "Estimate SHMMAX per Postgresql 9.1 Doc - Table 17-2"
echo

SHMMAX_MAXCON=$(( PG_MAXCON * (1800 + 270 * PG_MAXLPT)  ))
echo "SHMMAX_MAXCON:"$SHMMAX_MAXCON

SHMMAX_ATVWRK=$(( PG_ATVWRK * (1800 + 270 * PG_MAXLPT)  ))
echo "SHMMAX_ATVWRK:"$SHMMAX_ATVWRK

SHMMAX_MAXPRT=$(( PG_MAXPRT * (770 + 270 * PG_MAXLPT) )) 
echo "SHMMAX_MAXPRT:"$SHMMAX_MAXPRT

SHMMAX_SHABUF=$(( PG_SHABUF_NUMOFBUF * (PG_BLKSIZ + 208) ))
echo "SHMMAX_SHABUF:"$SHMMAX_SHABUF

SHMMAX_WALBUF=$(( PG_WALBUF_NUMOFBUF * (PG_WALBLK + 8) ))
echo "SHMMAX_WALBUF:"$SHMMAX_WALBUF

PG_REC_SHMMAX_TOTAL_B=$(( 788480 + SHMMAX_MAXCON + SHMMAX_ATVWRK +
SHMMAX_MAXPRT + SHMMAX_SHABUF + SHMMAX_WALBUF  ))
echo "PG_REC_SHMMAX_TOTAL_B:"$PG_REC_SHMMAX_TOTAL_B

PG_REC_SHMMAX_TOTAL_MB=$(( (PG_REC_SHMMAX_TOTAL_B / 1024) / 1024 ))
echo "PG_REC_SHMMAX_TOTAL_MB:"$PG_REC_SHMMAX_TOTAL_MB

echo
echo "-checking ipcs -m, postgres should be running" 
CUR_IPCS_PG_SHAMEMSEG=$(ipcs -m | grep postgres | awk '{print $5}')
CUR_IPCS_PG_SHAMEMSEG_MB=$(

Re: [GENERAL] See the WHERE clause of a partial index

[Tom Lane]

Paul Jungwirth  writes:
> I'm not sure how to interpret that `indpred` column. Is there any way
> to reconstruct the WHERE clause I originally passed to the CREATE
> INDEX command?

pg_get_indexdef() should help.  If you really want just the WHERE
clause, possibly pg_get_expr() would work, but I've not tried it on
index clauses.

regards, tom lane


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

[GENERAL] See the WHERE clause of a partial index

[Paul Jungwirth]

Hello,

I created some indexes with WHERE clauses, so that only part of the
table would be indexed. Now I'd like to get a list of indexes, and
include the WHERE clause if an index has one. This is what I'm trying
right now:

SELECT indc.relname, ind.indpred
FROM pg_index ind, pg_class indc
WHERE indc.oid = ind.indexrelid;

But that gives results like this:

 relname|   indpred
+
. . .
 index_pwords_on_language_id  | NULL
 index_user_languages_on_user_id_and_language_id  |
{NULLTEST :arg {VAR :varno 1 :varattno 6 :vartype 1114 :vartypmod -1
:varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 6 :location 128}
:nulltesttype 0 :argisrow false}
 index_user_lists_on_user_id  | NULL
 index_users_on_email | NULL
. . .

I'm not sure how to interpret that `indpred` column. Is there any way
to reconstruct the WHERE clause I originally passed to the CREATE
INDEX command?

Thanks,
Paul

-- 
_
Pulchritudo splendor veritatis.


-- 
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] OFFTOPIC: core dumped with strcpy,atoi,sprintf.

[Scott Marlowe]

On Fri, Sep 14, 2012 at 10:37 AM, Tom Lane  wrote:
> "Ing.Edmundo.Robles.Lopez"  writes:
>> Debugging the program, found that the core is generated  by atoi,
>> sprintf and similar functions; because that functions fails when
>> received  a null pointer (NULL). But, if  the functions received  an
>> empty string ("") the program continues and run succesfully.
>
>> Yes,  I should validate each input pointers. But, its odd that works in
>> SCO and not in Linux, and its odder that fails in functions like strcpy,
>> atoi, sprintf, etc.
>
> Not really.  Per the POSIX spec, the behavior of those functions is
> undefined for NULL input pointers.  Some platforms are forgiving about
> it, most are not.

I learned C on a platform with no memory protection wherein a null
pointer would result in a write to location 0 and an immediate machine
crash.  Wow, that was a LONG time ago (late 80s).  Kids today.  Got it
easy.


-- 
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] SQLSTATE[08006] [7] server closed the connection unexpectedly

[Yvon Thoraval]

2012/9/14 Albe Laurenz 

> Yvon Thoraval wrote:
> You may want to set log_statement='all' in postgresql.conf
>

i did this change , restart PostgreSQL and test again, with the same error,
however postgres log didn't report any error, as far as i understand the
log :

2012-09-14 18:43:14 CEST LOG:  received fast shutdown request
2012-09-14 18:43:14 CEST LOG:  aborting any active transactions
2012-09-14 18:43:14 CEST LOG:  autovacuum launcher shutting down
2012-09-14 18:43:14 CEST LOG:  shutting down
2012-09-14 18:43:14 CEST LOG:  database system is shut down
2012-09-14 18:43:17 CEST LOG:  database system was shut down at 2012-09-14
18:43:14 CEST
2012-09-14 18:43:17 CEST LOG:  incomplete startup packet
2012-09-14 18:43:17 CEST LOG:  database system is ready to accept
connections
2012-09-14 18:43:17 CEST LOG:  autovacuum launcher started
2012-09-14 18:43:18 CEST LOG:  statement: SELECT d.datname as "Name",
   pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
   pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
   d.datcollate as "Collate",
   d.datctype as "Ctype",
   pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
2012-09-14 18:43:19 CEST LOG:  statement: SELECT d.datname as "Name",
   pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
   pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
   d.datcollate as "Collate",
   d.datctype as "Ctype",
   pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
2012-09-14 18:43:19 CEST LOG:  statement: SELECT d.datname as "Name",
   pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
   pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
   d.datcollate as "Collate",
   d.datctype as "Ctype",
   pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
yt@D620 /home/yt/Sites/yt_tests $



-- 
Yvon

Re: [GENERAL] SQLSTATE[08006] [7] server closed the connection unexpectedly

[Yvon Thoraval]

2012/9/14 Albe Laurenz 

>
> Is this repeatable?
>

YES, may be i didn't mention that about the same config on another computer
running mac OS X, works very well with same php script. Also this php
script was working 2 months ago...
i suspect something had changed on Xubuntu within pdo, php and psql ?


> That message normally means that the server process crashed.
>

I don't think so because even after this error I'm still able to connect to
the db that way :
 $ psql -h localhost -U yt -d yt_tests
Password for user yt:
psql (9.1.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

yt_tests=> select * from weather;
 city  | temp_lo | temp_hi | prcp |date
---+-+-+--+
 San Francisco |  46 |  50 | 0.25 | 1994-11-27
 Hayward   |  37 |  54 |  | 1994-11-29
 San Francisco |  43 |  57 |0 | 1994-11-29
(3 rows)

yt_tests=> \q

What is in the PostgreSQL server log file?
>

Nothing mentionned about this prob.


> You may want to set log_statement='all' in postgresql.conf
>

OK, fine thanks, I'll do that ASAP and repost.


> to get more context.
>
> Yours,
> Laurenz Albe
>



-- 
Yvon

Re: [GENERAL] OFFTOPIC: core dumped with strcpy,atoi,sprintf.

[Tom Lane]

"Ing.Edmundo.Robles.Lopez"  writes:
> Debugging the program, found that the core is generated  by atoi, 
> sprintf and similar functions; because that functions fails when 
> received  a null pointer (NULL). But, if  the functions received  an 
> empty string ("") the program continues and run succesfully.

> Yes,  I should validate each input pointers. But, its odd that works in 
> SCO and not in Linux, and its odder that fails in functions like strcpy, 
> atoi, sprintf, etc.

Not really.  Per the POSIX spec, the behavior of those functions is
undefined for NULL input pointers.  Some platforms are forgiving about
it, most are not.

regards, tom lane


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

[GENERAL] OFFTOPIC: core dumped with strcpy,atoi,sprintf.

[Ing.Edmundo.Robles.Lopez]

Hi.
I hope you can help me. I have problems with some functions like 
strcpy, atoi, etc and  would like to know the correct list where I have 
to ask this:




I have compiled a program  under SCO Openserver, and  run succesfully. 
But when  i compile the same program in Ubuntu  12.04,  i got a core dump.



Debugging the program, found that the core is generated  by atoi, 
sprintf and similar functions; because that functions fails when 
received  a null pointer (NULL). But, if  the functions received  an 
empty string ("") the program continues and run succesfully.



Yes,  I should validate each input pointers. But, its odd that works in 
SCO and not in Linux, and its odder that fails in functions like strcpy, 
atoi, sprintf, etc.



I have a lot of programs that i should compile in boths operative 
systems. And if you kown something that can  help me, really, really i'd 
appreciate.  :-)



Regards.
El contenido de este correo electrónico y sus archivos adjuntos son privados y 
confidenciales y va dirigido exclusivamente a su destinatario.  No se autoriza 
la utilización, retransmisión, diseminación, o cualquier otro uso de esta 
información por un receptor o entidades distintas al destinatario.  Si recibe 
este correo sin ser el destinatario se le solicita eliminarlo y hacerlo del 
conocimiento del emisor. La empresa no se hace responsable de transmisiones o 
comunicaciones no autorizadas o emitidas por personas ajenas a sus 
colaboradores utilizando éste medio electrónico.

The content of this email and its attached files are private and confidential 
and intended exclusively for the use of the individual or entity to which they 
are addressed. The retransmission, dissemination, or any other use of this 
information other than by the intended recipient is prohibited.  If you have 
received this email in error please delete it and notify the sender.  The 
company cannot be held liable for unauthorized electronic transmissions or 
communications, nor for those emitted by non-company individuals and entities.


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

Re: [GENERAL] Custom integer-like type

[Tom Lane]

Ivan Voras  writes:
> I'm creating a new data in C, and everything is proceeding well, except
> that the data type should be parsed on input like an integer. Maybe it's
> best if I explain it with an example:

> Currently, I can do this:

> INSERT INTO t(my_data_type) VALUES ('1')

> but I cannot do this:

> INSERT INTO t(my_data_type) VALUES (1)

What you'd need for that is an assignment cast from integer to myint.
Literal 1 is an integer, period; it's not going to get fed to your
type's input routine.

regards, tom lane


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

[GENERAL] Custom integer-like type

[Ivan Voras]

Hello,

I'm creating a new data in C, and everything is proceeding well, except
that the data type should be parsed on input like an integer. Maybe it's
best if I explain it with an example:

Currently, I can do this:

INSERT INTO t(my_data_type) VALUES ('1')

but I cannot do this:

INSERT INTO t(my_data_type) VALUES (1)

My type is defined as:

CREATE TYPE myint (
   internallength = variable,
   input = encbigint_in,
   output = encbigint_out,
   alignment = int4,
   storage = external
);

(The "variable" length is correct in this case.)



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Need help in reclaiming disk space by deleting the selected records

[Steve Crawford]

On 09/14/2012 05:35 AM, Andres Freund wrote:

Hi,

On Friday, September 14, 2012 01:29:59 AM Steve Crawford wrote:

2. CLUSTER is typically way faster than VACUUM FULL and rebuilds the
indexes as well but it temporarily requires sufficient disk-space to
write out a copy of the table being clustered.

Thats not the case anymore since 9.0 btw. These days VACUUM FULL does the same
thing CLUSTER does just without sorting.


That's true - I should have pointed that out. But it also means that you 
can get into a corner if you need to vacuum full large tables when you 
have limited free disk space - something the OP should consider since 
reclaiming disk space was one of his motivations.


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] PL/pgSQL debugger and PostgreSQL 9.2

[Albe Laurenz]

Fabrízio de Royes Mello wrote:
>> Has it moved somewhere else?

> Look at the pldebugger git repository in [1].

Thanks!

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] PL/pgSQL debugger and PostgreSQL 9.2

[Fabrízio de Royes Mello]

2012/9/14 Albe Laurenz 

> Dear Dave, dear Korry,
>
> I tried to build the debugger
> (http://pgfoundry.org/projects/edb-debugger/)
> with PostgreSQL 9.2 and it failed.
>
> The code seems to have bitrotted.
> The last commit was 16 months ago.
>
> Do you still maintain the project?
> Has it moved somewhere else?
>
>
Hi,

Look at the pldebugger git repository in [1].

[1] http://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary

-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

[GENERAL] PL/pgSQL debugger and PostgreSQL 9.2

[Albe Laurenz]

Dear Dave, dear Korry,

I tried to build the debugger
(http://pgfoundry.org/projects/edb-debugger/)
with PostgreSQL 9.2 and it failed.

The code seems to have bitrotted.
The last commit was 16 months ago.

Do you still maintain the project?
Has it moved somewhere else?

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] SQLSTATE[08006] [7] server closed the connection unexpectedly

[Albe Laurenz]

Yvon Thoraval wrote:
> I do have a strange probleme when connecting to a database thru php using PDO.
> When connecting to the php script thru a browser i get the following error :
> HTTP 500 (Internal Server Error)
> 
> 
> Not really informative, then i've tried the php script thru Command Line 
> where i get :
> yt@D620 /home/yt/Sites/yt_tests $ php index.php
> PHP Fatal error:  Uncaught exception 'PDOException' with message 
> 'SQLSTATE[08006] [7] server closed
> the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.' in 
> /home/yt/Sites/yt_tests/index.php:75
> Stack trace:
> #0 /home/yt/Sites/yt_tests/index.php(75): 
> PDO->__construct('pgsql:dbname=yt...', 'yt', 'yvon5533')
> #1 {main}
>   thrown in /home/yt/Sites/yt_tests/index.php on line 75
> zsh: exit 255   php index.php
> 
> 
> Line 75 being :
> $db = new PDO("pgsql:dbname=$dbname;host=$host;", $username, $password );
> 
> I should say i do NOT have any prob when connecting directly to this database 
> using :
> $ psql -h  -U yt -d yt_tests
> 
> I'm stuck, i don't see any way to debugg that prob.

Is this repeatable?

That message normally means that the server process crashed.
What is in the PostgreSQL server log file?
You may want to set log_statement='all' in postgresql.conf
to get more context.

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] Performance of pl/pgsql functions?

[Merlin Moncure]

On Fri, Sep 14, 2012 at 1:47 AM, Pavel Stehule  wrote:
> 2012/9/14 John R Pierce :
>> On 09/13/12 10:17 PM, Wells Oliver wrote:
>>>
>>> Do these tend to perform well? I have some simple formulas in functions
>>> like so:
>>
>>
>> if you code your function in SQL instead of plpgsql, and mark it immutable,
>> it can be inlined by the planner.
>
> you don't need to mark SQL functions - it is not black box for
> optimizer and usually better is don't mark SQL functions.

on my workstation:
OP's plpgsql: 63us / call
1-line plpgsql: 43us / call
1-line sql: 38us / call   (marking didn't appear to matter in this case)
manually inlined sql: 38us/call

query was tested via:
explain analyze select stat_foo(v,v,v) from generate_series(1,10) v;

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] SQLSTATE[08006] [7] server closed the connection unexpectedly

[Yvon Thoraval]

I do have a strange probleme when connecting to a database thru php using
PDO.
When connecting to the php script thru a browser i get the following error :
HTTP 500 (Internal Server Error)

Not really informative, then i've tried the php script thru Command Line
where i get :
yt@D620 /home/yt/Sites/yt_tests $ php index.php
PHP Fatal error:  Uncaught exception 'PDOException' with message
'SQLSTATE[08006] [7] server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.' in
/home/yt/Sites/yt_tests/index.php:75
Stack trace:
#0 /home/yt/Sites/yt_tests/index.php(75):
PDO->__construct('pgsql:dbname=yt...', 'yt', 'yvon5533')
#1 {main}
  thrown in /home/yt/Sites/yt_tests/index.php on line 75
zsh: exit 255   php index.php

Line 75 being :
$db = new PDO("pgsql:dbname=$dbname;host=$host;", $username, $password );

I should say i do NOT have any prob when connecting directly to this
database using :
$ psql -h  -U yt -d yt_tests

I'm stuck, i don't see any way to debugg that prob.


-- 
Yvon

Re: [GENERAL] Seeing tables with the same name on different schemas

[raghavendra . rao]

How about using this way ?

\dt *.*

Or 

\dt schemaname.*

--Raghav
Sent from my BlackBerry® smartphone from !DEA

-Original Message-
From: Robert Sosinski 
Sender: pgsql-general-owner@postgresql.orgDate: Fri, 14 Sep 2012 08:47:54 
To: 
Subject: [GENERAL] Seeing tables with the same name on different schemas

Hey Everyone, 

Question, I have multiple schemas on a database, and these schemas sometimes 
have the tables of the same name.  However, when I do \d to see what tables I 
have, I only see the first table (based on the order of the search path), but 
no the other tables that have the same name on different schemas.

Is there a way to see all tables across all schemas?

Thanks, 

-- 
Robert Sosinski

[GENERAL] Seeing tables with the same name on different schemas

[Robert Sosinski]

Hey Everyone, 

Question, I have multiple schemas on a database, and these schemas sometimes 
have the tables of the same name.  However, when I do \d to see what tables I 
have, I only see the first table (based on the order of the search path), but 
no the other tables that have the same name on different schemas.

Is there a way to see all tables across all schemas?

Thanks, 

-- 
Robert Sosinski

Re: [GENERAL] Need help in reclaiming disk space by deleting the selected records

[Andres Freund]

Hi,

On Friday, September 14, 2012 01:29:59 AM Steve Crawford wrote:
> 2. CLUSTER is typically way faster than VACUUM FULL and rebuilds the 
> indexes as well but it temporarily requires sufficient disk-space to 
> write out a copy of the table being clustered.
Thats not the case anymore since 9.0 btw. These days VACUUM FULL does the same 
thing CLUSTER does just without sorting.

Greetings,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] application for postgres Log

[Albe Laurenz]

Arvind Singh wrote:
> I have enabled the csvlog option.
> 
> There seem to be 24 columns in the Log. I have difficulty in
understanding the following columns
> 
> - virtual_transaction_id

Such a transaction ID is given to every transaction.
Can be used to track transactions in the log.

> - transaction_id

Such a transaction ID is given to a transaction
that modifies the database.

> - hint

Part of the error message (like "detail" and "context").

> - internal_query
> - internal_query_pos

These are used a handful of times in the code, I'm not
100% sure what for.
You may never encouter one of those.

> - context

Describes where the error ocurred.

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] application for postgres Log

[Arvind Singh]

Thanx for the reply,
 
I have enabled the csvlog option.
 
There seem to be 24 columns in the Log. I have difficulty in understanding the 
following columns
 
- virtual_transaction_id
- transaction_id
- hint
- internal_query
- internal_query_pos
- context
 
I have correlated the column names from log structure mentioned in documentation
 
arvind
 

> Subject: RE: [GENERAL] application for postgres Log
> Date: Mon, 10 Sep 2012 09:31:29 +0200
> From: laurenz.a...@wien.gv.at
> To: arvin...@hotmail.com; pgsql-general@postgresql.org
> 
> Arvind Singh wrote:
> > I am in a project that uses PostGresSql v9.0. We are developing an
> application in CSharp to parse the
> > PG server activity log installed on Windows 2003/XP or higher.
> > 
> > Our application will :
> > Scan the Log for a given search text and Post rows found
> > Produce statistics related to SQL queries tracked in a time frame
> > Will allow selection of log columns, that can be displayed in a
> DataGrid
> > 
> > I need some help or even a simple link that is related to this
> subject. I am already checking Goog
> > query of Postgres Log, though it gives mostly results for the postgres
> Documentation.
> 
> Maybe pgreplay can give you some inspiration:
> http://pgreplay.projects.postgresql.org/
> 
> Check out parse.c which parses a log file.
> 
> One bit of advice I'd like to give you is a recommendation
> to use the CSV log format. The regular error log is much
> harder to parse, and moreover everything that gets printed
> to stderr by the server will end up there. This could be
> dumps when the server runs out of memory or stuff that loaded
> modules produce. All this will screw up your log file
> parsing.
> 
> Yours,
> Laurenz Albe
  

Re: [GENERAL] Best free tool for relationship extraction

[Peter Hopfgartner]

On 09/13/2012 06:51 PM, Alexander Gataric wrote:

I need to determine relationships between several tables. Is there a
free tool to extract these from catalog tables? Is there an SQL that
also does this?

Thanks
Alex

Sent from my smartphone


I'm using http://squirrel-sql.sourceforge.net for those kind of things.

Peter

--
Peter Hopfgartner
web  : www.r3-gis.com



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