Re: [GENERAL] 9.1 - rewrite less alter table?

[hubert depesz lubaczewski]

On Tue, Mar 08, 2011 at 10:43:55PM -0500, Noah Misch wrote:
> The patch optimizing that case foundered.  We may have it in 9.2.
> The current code only kicks in when the destination has no typmod.  When the
> source/destination type pair are marked "(binary coercible)" in the output of
> \dC, the optimization applies.  Alternately, it applies when one of the types 
> is
> a constraint-free domain over the other.

ah, that explains it, thanks.

> The practical use cases are a bit thin at present.  The main interesting ones
> are varchar(N) -> text and conversions between domains and their base types.  
> We
> did these first because they required a proper subset of the code needed to
> support the more-common cases.
> The applicable definition of "binary coercible" appears in our CREATE CAST
> documentation.

thanks a lot for explanation. much clearer now.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] procedure in db

[Vibhor Kumar]

On Mar 9, 2011, at 9:56 AM, abcdef wrote:

> I use postgresql in red hat linux .
> I want to know how I can find out all the procedure stored in the database .
> Any SQL command to do it ???
pg_catalog.pg_proc can be use to find all the functions.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.ku...@enterprisedb.com
Blog:http://vibhork.blogspot.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]

[Vlad Arkhipov]

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

[GENERAL] Transaction wraparound vacuum synchronicity

[Michael Graham]

Hi all,

I have a database with a number of tables that are partitioned monthly,
after that the tables are mostly read only (on rare occasions we may
delete from a table but normally we just drop the partitions).  Recently
I've noticed that we have a lot of these tables are vacuumed around the
same time, after a little big of digging I've realised that postgres is
vacuuming them to stop xaction wrap around.  So for example in a few
million xactions (later today) postgres is going to want to vacuum 37
tables for just this reason.

I know I can fiddle autovacuum_freeze_max_age and vacuum_freeze_min_age
to change how regularly the tables have this occur, and I can do this on
a per table basis in pg_autovacuum (yes this means I'm running an old
version, version 8.2) but what I'm wondering is how other people are
breaking this synchronisation?

Should I add a random value to the freeze_max_age for all the old tables
when I start a new month?  Or do the same with the freeze_min_age?
Perhaps I should just force a vacuum on some of the tables the break it?

Cheers,
-- 
Michael Graham 



-- 
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] Using bytea field...

[Sim Zacks]

MD5 is not collision resistant (using the immortal words of wikipedia 
http://en.wikipedia.org/wiki/MD5).


This means that it is possible that multiple images will return the same 
md5 hash.


The question is, if it screws up and says that an image already exists 
and then returns a different image when querying for it, how bad would 
that be.



I've seen a lot of discussions in the past few years about how 
problematic the md5 approach is.



Sim


On 03/08/2011 09:06 PM, Andy Colson wrote:


On 3/8/2011 12:28 PM, Andre Lopes wrote:

Hi,

I'm using a bytea field to store small images in base64. I need to
know if I can compare bytea rows for equality. To know for example if
the file already exists in the database, this is possible with bytea?

Best Regads,



You dont need to use both base64 and bytea.  You can store base64 in 
text field... or just store the photo as-is into bytea.


To answer your question: it would be faster if you computed an md5 (or 
sha or whatever) and stored it in the db, then you could check to see 
if an image exists by searching for the md5, which would be way 
faster, an send a lot less data over the wire.


-Andy




--
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] Using bytea field...

[Sim Zacks]

On 03/09/2011 01:27 PM, Sim Zacks wrote:
This means that it is possible that multiple images will return the 
same md5 hash.
OTOH, if you had an indexed md5 hash and compared the image only to the 
matches, that would be a fast and accurate querying method


--
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] Using bytea field...

[rsmogura]

On Wed, 09 Mar 2011 13:27:16 +0200, Sim Zacks wrote:

MD5 is not collision resistant (using the immortal words of wikipedia
http://en.wikipedia.org/wiki/MD5).

This means that it is possible that multiple images will return the
same md5 hash.

The question is, if it screws up and says that an image already
exists and then returns a different image when querying for it, how
bad would that be.


I've seen a lot of discussions in the past few years about how
problematic the md5 approach is.


Sim


On 03/08/2011 09:06 PM, Andy Colson wrote:


On 3/8/2011 12:28 PM, Andre Lopes wrote:

Hi,

I'm using a bytea field to store small images in base64. I need to
know if I can compare bytea rows for equality. To know for example 
if
the file already exists in the database, this is possible with 
bytea?


Best Regads,



You dont need to use both base64 and bytea.  You can store base64 in 
text field... or just store the photo as-is into bytea.


To answer your question: it would be faster if you computed an md5 
(or sha or whatever) and stored it in the db, then you could check to 
see if an image exists by searching for the md5, which would be way 
faster, an send a lot less data over the wire.


-Andy



Every hash algorithm is not collision resistant, and in theory You need 
to check equality of images. Hash function says that if hashes are 
different, then images are different. It not says if hashes are same 
then images are same.


Regards,
Radek

--
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] Composite index structure

[Chetan Suttraway]

On Mon, Mar 7, 2011 at 2:49 AM, Nick Raj  wrote:

> Hi all,
>
> I want to construct an "Composite Index Structure" i.e. a combination of
> gist and btree.
> What i am thinking is that first creating a Rtree structure that is
> pointing to another Btree structure.
> For example, Suppose i want to find vehicles between 2 to 4 pm on 14/2/2011
> on X road.
>
> I am thinking of creating rtree structure for road network and then btree
> for time. For reaching X road i use Rtree, and from there btree begin i.e.
> leaf node of rtree contains the pointer to root node of btree ( in this way
> i have all time belonging to X road)
>
> My question is that how to implement this composite index structure in
> postgres?
>
> Let us suppose, if i create mygist index, then i have to write my own
> operator class?
> or
> can i use gist index as it is and btree tree as it is. I mean their
> operator class and their gist methods but how to establish linkage between
> them?
>
> Any idea ??
>
> Thanks
> Raj
>

Checkout below links:
http://www.postgresql.org/docs/8.2/static/gist-extensibility.html
http://www.postgresql.org/docs/9.0/interactive/indexam.html




-- 
Chetan Sutrave
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91.20.30589523

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are not
the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

[GENERAL] Why length(to_char(1::integer, '9')) = 2 ?

[Dmitriy Igrishin]

Hey all,

dmitigr=> select to_char(1, '9');
 to_char
-
  1

dmitigr=> select length(to_char(1, '9'));
 length

  2

Why to_char() includes preceding blank space in the result ?

-- 
// Dmitriy.

Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?

[Pavel Stehule]

2011/3/9 Dmitriy Igrishin :
> Hey all,
>
> dmitigr=> select to_char(1, '9');
>  to_char
> -
>   1
>
> dmitigr=> select length(to_char(1, '9'));
>  length
> 
>   2
>
> Why to_char() includes preceding blank space in the result ?

it is compatibility with Oracle?

Regards

Pavel

>
> --
> // Dmitriy.
>
>
>

-- 
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] Why length(to_char(1::integer, '9')) = 2 ?

[Raymond O'Donnell]

On 09/03/2011 13:15, Pavel Stehule wrote:

2011/3/9 Dmitriy Igrishin:

Hey all,

dmitigr=>  select to_char(1, '9');
  to_char
-
   1

dmitigr=>  select length(to_char(1, '9'));
  length

   2

Why to_char() includes preceding blank space in the result ?


it is compatibility with Oracle?


I've often wondered too why there is padding by default in certain uses 
of to_char() it's a bit of a PITA sometimes. :-)


Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
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] Why length(to_char(1::integer, '9')) = 2 ?

[Sim Zacks]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/2011 03:12 PM, Dmitriy Igrishin wrote:

> Hey all,
>
> dmitigr=> select to_char(1, '9');
> to_char
> -
> 1
>
> dmitigr=> select length(to_char(1, '9'));
> length
> 
> 2
>
> Why to_char() includes preceding blank space in the result ?
>
> --
> // Dmitriy.
>
>
I don't know why, but to work around it use:
select to_char(1, 'FM9');
select length(to_char(1, 'FM9'));

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJNd3/JAAoJEHr2Gm0ENObOsUMH/ApWyfc5c5A56m1pAP7raIEd
dmY0/aocCCnQbariREZIGSJPrmcWDKnNe3yNLjV2Y3+EY+eaicxy2GPTVamOrfqN
tYQ/ImH3IkrzQk1bfRX+lnUJQGEmMi8ClzAatKUIifGJwMuj7y1xUl/VBTP0lBvI
GuQQaElNkpGaPRTJZlorrtqEBgWmiyBT07gK02IST9xFsUPnrF0niNlqcaphF2Ga
kKgFfVJ8u/C3KbwowVPh5GYZHgIM1T8x6SPzpcsnFVrIGN+avnuvdEInxomCZDNN
FLuRBEPK9NFTG6rdIyrtfy5C6HVm/q7rO1alW0hjuszou1t2gBCOkmXtva9V5gY=
=pGTI
-END PGP SIGNATURE-


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

Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?

[Dmitriy Igrishin]

2011/3/9 Pavel Stehule 

> 2011/3/9 Dmitriy Igrishin :
> > Hey all,
> >
> > dmitigr=> select to_char(1, '9');
> >  to_char
> > -
> >   1
> >
> > dmitigr=> select length(to_char(1, '9'));
> >  length
> > 
> >   2
> >
> > Why to_char() includes preceding blank space in the result ?
>
> it is compatibility with Oracle?
>
Do you mean the case of MI ?
So, is this leading space reserved for a sign of number by default ?

>
> Regards
>
> Pavel
>
> >
> > --
> > // Dmitriy.
> >
> >
> >
>



-- 
// Dmitriy.

Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?

[Pavel Stehule]

2011/3/9 Dmitriy Igrishin :
>
>
> 2011/3/9 Pavel Stehule 
>>
>> 2011/3/9 Dmitriy Igrishin :
>> > Hey all,
>> >
>> > dmitigr=> select to_char(1, '9');
>> >  to_char
>> > -
>> >   1
>> >
>> > dmitigr=> select length(to_char(1, '9'));
>> >  length
>> > 
>> >   2
>> >
>> > Why to_char() includes preceding blank space in the result ?
>>
>> it is compatibility with Oracle?
>
> Do you mean the case of MI ?
> So, is this leading space reserved for a sign of number by default ?

yes

pavel=# select '>' || to_char(-1,'9') || '<';
 ?column?
──
 >-1<
(1 row)

regards

Pavel

>>
>> Regards
>>
>> Pavel
>>
>> >
>> > --
>> > // Dmitriy.
>> >
>> >
>> >
>
>
>
> --
> // Dmitriy.
>
>
>

-- 
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] Why length(to_char(1::integer, '9')) = 2 ?

[Dmitriy Igrishin]

2011/3/9 Pavel Stehule 

> 2011/3/9 Dmitriy Igrishin :
> >
> >
> > 2011/3/9 Pavel Stehule 
> >>
> >> 2011/3/9 Dmitriy Igrishin :
> >> > Hey all,
> >> >
> >> > dmitigr=> select to_char(1, '9');
> >> >  to_char
> >> > -
> >> >   1
> >> >
> >> > dmitigr=> select length(to_char(1, '9'));
> >> >  length
> >> > 
> >> >   2
> >> >
> >> > Why to_char() includes preceding blank space in the result ?
> >>
> >> it is compatibility with Oracle?
> >
> > Do you mean the case of MI ?
> > So, is this leading space reserved for a sign of number by default ?
>
> yes
>
> pavel=# select '>' || to_char(-1,'9') || '<';
>  ?column?
> ──
>  >-1<
> (1 row)
>

Aha! Thanks.

>
> regards
>
> Pavel
>
> >>
> >> Regards
> >>
> >> Pavel
> >>
> >> >
> >> > --
> >> > // Dmitriy.
> >> >
> >> >
> >> >
> >
> >
> >
> > --
> > // Dmitriy.
> >
> >
> >
>



-- 
// Dmitriy.

Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?

[Dmitriy Igrishin]

2011/3/9 Sim Zacks 

>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 03/09/2011 03:12 PM, Dmitriy Igrishin wrote:
>
> > Hey all,
> >
> > dmitigr=> select to_char(1, '9');
> > to_char
> > -
> > 1
> >
> > dmitigr=> select length(to_char(1, '9'));
> > length
> > 
> > 2
> >
> > Why to_char() includes preceding blank space in the result ?
> >
> > --
> > // Dmitriy.
> >
> >
> I don't know why, but to work around it use:
> select to_char(1, 'FM9');
> select length(to_char(1, 'FM9'));
>
Thanks!

But I am missing something or there is a documentation inaccuracy:
http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIONS-FORMATTING-NUMERICMOD-TABLEsays:
fill mode (suppress padding blanks and zeroes)

Test:
dmitigr=> select to_char(12,'FM0009');
 to_char
-
 0012

dmitigr=> select length(to_char(12,'FM0009'));
 length

  4

So, FM suppresses only padding blanks not zeroes...

Any comments?



> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iQEcBAEBAgAGBQJNd3/JAAoJEHr2Gm0ENObOsUMH/ApWyfc5c5A56m1pAP7raIEd
> dmY0/aocCCnQbariREZIGSJPrmcWDKnNe3yNLjV2Y3+EY+eaicxy2GPTVamOrfqN
> tYQ/ImH3IkrzQk1bfRX+lnUJQGEmMi8ClzAatKUIifGJwMuj7y1xUl/VBTP0lBvI
> GuQQaElNkpGaPRTJZlorrtqEBgWmiyBT07gK02IST9xFsUPnrF0niNlqcaphF2Ga
> kKgFfVJ8u/C3KbwowVPh5GYZHgIM1T8x6SPzpcsnFVrIGN+avnuvdEInxomCZDNN
> FLuRBEPK9NFTG6rdIyrtfy5C6HVm/q7rO1alW0hjuszou1t2gBCOkmXtva9V5gY=
> =pGTI
> -END PGP SIGNATURE-
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Web Hosting

[Vincent Veyron]

Le lundi 07 mars 2011 à 13:09 -0500, Matt a écrit :
> Thanks, but I tried that originally and the companies that come up
> have either poor ratings, won't support postgres, won't allow me the
> freedom to run my own software

This is what I use for hosting the site that's in my sig :

http://www.online.net/serveur-dedie/offre-dedibox-sc.xhtml

I have 6 or 7 users working all day with it, so it's not a heavy traffic
site, but I have room for growth, as you can see with the top command :

top - 14:46:58 up 204 days,  7:52,  1 user,  load average: 0.00, 0.00,
0.00
Tasks:  72 total,   1 running,  71 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.3%us,  0.3%sy,  0.0%ni, 99.3%id,  0.0%wa,  0.0%hi,  0.0%si,
0.0%st
Mem:   2028176k total,  1726188k used,   301988k free,   159860k buffers
Swap:  1044216k total,  128k used,  1044088k free,  1276084k cached


That's for 15 euros/month. 

I built the machine myself with Debian, so I can't tell what their
support is like.

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des dossiers de contentieux et d'assurance 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

[GENERAL] Copying data from one table to another - how to specify fields?

[Alexander Farber]

Hello,

I'm using CentOS 5.5 with PostgreSQL 8.4.7 and
am migrating my site from phpBB 3 to Drupal 7.

I would like to copy these fields from one table:

  select user_id, username, user_email, user_regdate, user_lastvisit
from phpbb_users where user_id > 50;

into the other (already existing) table:

  select uid, name, mail, created, access from drupal_users;

I've read http://www.postgresql.org/docs/8.4/static/sql-selectinto.html
and few more docs but don't understand, how could I specify the
fields correspondence (i.e. phpbb_users.user_id -> drupal_users.uid)

Also the target table exists already, has some records already
and has more fields than specified in my select statement above.

Thank you
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] [ADMIN] FW: backup using pg_dump postgreSQL 8.3.8

[Vibhor Kumar]

On Mar 9, 2011, at 7:28 PM, Sandy Test wrote:

> 
> We have postgreSQL 8.3.8 installed on a windows 8 server (64 bit).
> 
> We want to be able to run a nightly backup using the pg_dump command.
> Unfortunately, even with the pg_hba.conf fix of adding host postgres … trust,
> It still asks for a password.
> 
> 1. Is this a 64bit issue? If the server was 32bit, would it not ask for a 
> password? 

I haven;t seen issue with PG 8.3.8
Please share pg_hba.conf file entry.


> 2. Is there a way of running a dos script that would run the pg_dump 
> providing a password when prompted? 

Include following in dos script.
set PGPASSWORD=

Or you can use pgpass.conf file option.

> 3. Is there a way of running the pg_dump with a user that doesn't have a 
> password? 

For that user, you have to mention trust with hostname in pg_hba.conf

Thanks & Regards,
Vibhor Kumar
vibhor@gmail.com
Blog:http://vibhork.blogspot.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] Copying data from one table to another - how to specify fields?

[Andrew Sullivan]

On Wed, Mar 09, 2011 at 02:47:46PM +0100, Alexander Farber wrote:
> I would like to copy these fields from one table:
> 
>   select user_id, username, user_email, user_regdate, user_lastvisit
> from phpbb_users where user_id > 50;
> 
> into the other (already existing) table:
> 
>   select uid, name, mail, created, access from drupal_users;
> 

INSERT INTO drupal users (uid, name, mail, created, access) 
   SELECT user_id, username, user_email, user_regdate, user_lastvist
 FROM phpbb_users where user_id > 50;

This is sort of like an example on
http://www.postgresql.org/docs/9.0/interactive/sql-insert.html, but
that example uses SELCT *.  Perhaps an additional example would have
helped?  (This is basic SQL, though, and I'm not sure the keyword
manual is the best place for such an example.)

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
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] Copying data from one table to another - how to specify fields?

[Michael Glaesemann]

On Mar 9, 2011, at 08:47 , Alexander Farber wrote:

> Hello,
> 
> I'm using CentOS 5.5 with PostgreSQL 8.4.7 and
> am migrating my site from phpBB 3 to Drupal 7.
> 
> I would like to copy these fields from one table:
> 
>  select user_id, username, user_email, user_regdate, user_lastvisit
> from phpbb_users where user_id > 50;
> 
> into the other (already existing) table:
> 
>  select uid, name, mail, created, access from drupal_users;

INSERT INTO drupal_users (uid, name, mail, created, access)
  SELECT user_id, username, user_email, user_regdate, user_lastvisit
FROM phpbb_users
WHERE user_id > 50;



Michael Glaesemann
michael.glaesem...@myyearbook.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] Copying data from one table to another - how to specify fields?

[Alexander Farber]

Oh it is called "INSERT INTO", thank you!

I still have a problem though:

# select uid, name, mail, created, access from drupal_users;
 uid | name |mail|  created   |   access
-+--+++
   0 |  ||  0 |  0
   1 | Alex | alexander.far...@gmail.com | 1299512207 | 1299751991
(2 rows)

# INSERT INTO drupal_users (uid, name, mail, created, access)
 SELECT user_id, username, user_email, user_regdate, user_lastvisit
   FROM phpbb_users
   WHERE user_id > 50 and length(username) > 0;
ERROR:  duplicate key value violates unique constraint "drupal_users_name_key"

I don't understand, what is wrong with "name" here and
how to find the troublemaking record in my 4700 lines table

# \d+ drupal_users_name_key;
  Index "public.drupal_users_name_key"
 Column | Type  | Storage  | Description
+---+--+-
 name   | character varying(60) | extended |
unique, btree, for table "public.drupal_users"

Using PostgreSQL 8.4.7

Thank you
Alex

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

[GENERAL] FW: backup using pg_dump postgreSQL 8.3.8

[Sandy Test]

 

We have postgreSQL 8.3.8 installed on a windows 8 server (64 bit).

 

We want to be able to run a nightly backup using the pg_dump command.

Unfortunately, even with the pg_hba.conf fix of adding host postgres ...
trust, 

It still asks for a password.

 

1. Is this a 64bit issue? If the server was 32bit, would it not ask for
a password?

 

2. Is there a way of running a dos script that would run the pg_dump
providing a password when prompted? 

 

3. Is there a way of running the pg_dump with a user that doesn't have a
password?

 

Thanks

 

Sandy Test

System Manager Makhteshim

IT

Makhteshim Agan Group

tel:972-8-6296823   fax:972-8-6296043

sandy.t...@ma-industries.com

 

[GENERAL] ER tool that supports domains and custom types?

[Arturo Perez]

Hi all,

Anyone have a suggestion for a graphical ER tool that can work with 
Postgresql's domains and custom types?

I was using Mogwai designer but it can't reverse engineer a DB with the 
above.

tia, 
arturo

-- 
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] Copying data from one table to another - how to specify fields?

[Raymond O'Donnell]

On 09/03/2011 14:21, Alexander Farber wrote:

Oh it is called "INSERT INTO", thank you!

I still have a problem though:

# select uid, name, mail, created, access from drupal_users;
  uid | name |mail|  created   |   access
-+--+++
0 |  ||  0 |  0
1 | Alex | alexander.far...@gmail.com | 1299512207 | 1299751991
(2 rows)

# INSERT INTO drupal_users (uid, name, mail, created, access)
  SELECT user_id, username, user_email, user_regdate, user_lastvisit
FROM phpbb_users
WHERE user_id>  50 and length(username)>  0;
ERROR:  duplicate key value violates unique constraint "drupal_users_name_key"

I don't understand, what is wrong with "name" here and
how to find the troublemaking record in my 4700 lines table


Is "drupal_users" a table you created yourself? In a vanilla 
installation of Drupal, the users table is called just "users".


Anyway, to answer your question, you're trying to insert into 
drupal_users a value that already exists there, and which is subject to 
a constraint which allows only one instance of that value. To find the 
offending value you could do something like this (not tested):


  select * from phpbb_users where exists
  (select 1 from drupal_users
where drupal_users.name = phpbb_users.name);


Hope this helps,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
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] Transaction wraparound vacuum synchronicity

[Andrew Sullivan]

On Wed, Mar 09, 2011 at 10:52:25AM +, Michael Graham wrote:
> Perhaps I should just force a vacuum on some of the tables the break it?

It seems to me that you might want to VACUUM FREEZE the table when it
becomes read-only.  (I note that FREEZE is deprecated according to the
9.0 manual.  Too bad, that, because it seems to me that this is a use
case where one might want to put a thumb on the scale, and having to
twiddle a parameter just to affect one table is kind of
user-unfriendly.)

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
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] Copying data from one table to another - how to specify fields?

[Alexander Farber]

Hello Raymond and others,

thank you for looking at my problem!

It is a core Drupal 7 table, with a prefix "drupal_"
(you can set it while installing Drupal 7 in adv. options).

I have only 2 records in that target table:

# select uid, name, mail, created, access from drupal_users;
 uid | name |mail|  created   |   access
-+--+++
   0 |  ||  0 |  0
   1 | Alex | alexander.far...@gmail.com | 1299512207 | 1299753672
(2 rows)

I've tried you suggestion and the conflicting record is 'Alex':

#  select username, user_id from phpbb_users where exists
 (select 1 from drupal_users
   where drupal_users.name = phpbb_users.username);
 username | user_id
--+-
 Alex |   2
(1 row)

But isn't that record excluded by the conditions below?

# INSERT INTO drupal_users (uid, name, mail, created, access)
 SELECT user_id, username, user_email, user_regdate, user_lastvisit
   FROM phpbb_users
   WHERE user_id > 50 and length(username) > 0 and username <> 'Alex';
ERROR:  duplicate key value violates unique constraint "drupal_users_name_key"

-- 
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] FW: backup using pg_dump postgreSQL 8.3.8

[Andrew Sullivan]

On Wed, Mar 09, 2011 at 03:58:20PM +0200, Sandy Test wrote:
> 
> We want to be able to run a nightly backup using the pg_dump command.
> 
> Unfortunately, even with the pg_hba.conf fix of adding host postgres ...
> trust, 
> 
> It still asks for a password.

pg_hba sometimes surprises people because of its matching rules.  If
there's another entry that matches before the explicit postgres entry,
that's the rule you're going to get:

The first record with a matching connection type, client address,
requested database, and user name is used to perform
authentication. There is no "fall-through" or "backup": if one
record is chosen and the authentication fails, subsequent records
are not considered. If no record matches, access is denied.

(http://www.postgresql.org/docs/9.0/interactive/auth-pg-hba-conf.html).
Are you sure that's not your problem.  (It always is for me, and I
always make this mistake at least once per installation, even after
many years.)

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
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] Using bytea field...

[Andy Colson]

On 03/08/2011 09:06 PM, Andy Colson wrote:


On 3/8/2011 12:28 PM, Andre Lopes wrote:

Hi,

I'm using a bytea field to store small images in base64. I need to
know if I can compare bytea rows for equality. To know for example if
the file already exists in the database, this is possible with bytea?

Best Regads,



You dont need to use both base64 and bytea. You can store base64 in
text field... or just store the photo as-is into bytea.

To answer your question: it would be faster if you computed an md5 (or
sha or whatever) and stored it in the db, then you could check to see
if an image exists by searching for the md5, which would be way
faster, an send a lot less data over the wire.

-Andy






On 3/9/2011 5:27 AM, Sim Zacks wrote:
> MD5 is not collision resistant (using the immortal words of wikipedia
> http://en.wikipedia.org/wiki/MD5).
>
> This means that it is possible that multiple images will return the same
> md5 hash.
>
> The question is, if it screws up and says that an image already exists
> and then returns a different image when querying for it, how bad would
> that be.
>
>
> I've seen a lot of discussions in the past few years about how
> problematic the md5 approach is.
>
>
> Sim
>
>


It'll never happen:

http://stackoverflow.com/questions/862346/how-do-i-assess-the-hash-collision-probability

Sure you CAN go out of your way to generate collisions, but I'd bet 
money you never see one from your setup.


The probability is extremely slim.  And if thats too much of a chance, 
use sha2, its mind numbingly slim.


If you were doing cryptography it would be a problem, yes, but not 
checking file equality.


-Andy

--
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] Copying data from one table to another - how to specify fields?

[Alexander Farber]

I've also tried renaming 'Alex' to a temp. value:

# update drupal_users set name='Alex_1972' where name='Alex';
UPDATE 1

# INSERT INTO drupal_users (uid, name, mail, created, access)
 SELECT user_id, username, user_email, user_regdate, user_lastvisit
   FROM phpbb_users
   WHERE user_id > 50 and length(username) > 0 and username <> 'Alex';
ERROR:  duplicate key value violates unique constraint "drupal_users_name_key"

# select username, user_id from phpbb_users where exists
 (select 1 from drupal_users
   where drupal_users.name = phpbb_users.username);
 username | user_id
--+-
(0 rows)

so there must be a duplicated username in the source table
phpbb_users, but how could I find that record?

Thank you
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] Copying data from one table to another - how to specify fields?

[Alexander Farber]

Oh ok, found it:

# select one.username, one.user_id, two.user_id from phpbb_users one,
phpbb_users two where two.username=one.username and two.user_id <>
one.user_id;
 username | user_id | user_id
--+-+-
 Вячеслав |7564 | 421
 Вячеслав | 421 |7564
(2 rows)

Sorry!

-- 
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] Copying data from one table to another - how to specify fields?

[David Johnston]

2 Possibilities (assuming there is a single record with name == 'Alex' in
the drupal_users table; not counting uid 0)

1. There is a record with username = 'Alex' in the phpbb_users table
2. Username is not UNIQUE within phpbb_users

Write a select statement to extract username from phpbb_user for BOTH these
conditions.

Decide how you want to modify those records so that can be imported into
drupal_users.

David J.

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
Sent: Wednesday, March 09, 2011 9:21 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Copying data from one table to another - how to
specify fields?

Oh it is called "INSERT INTO", thank you!

I still have a problem though:

# select uid, name, mail, created, access from drupal_users;
 uid | name |mail|  created   |   access
-+--+++
   0 |  ||  0 |  0
   1 | Alex | alexander.far...@gmail.com | 1299512207 | 1299751991
(2 rows)

# INSERT INTO drupal_users (uid, name, mail, created, access)  SELECT
user_id, username, user_email, user_regdate, user_lastvisit
   FROM phpbb_users
   WHERE user_id > 50 and length(username) > 0;
ERROR:  duplicate key value violates unique constraint
"drupal_users_name_key"

I don't understand, what is wrong with "name" here and how to find the
troublemaking record in my 4700 lines table



-- 
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] equivalent of mysql's SET type?

[Merlin Moncure]

On Tue, Mar 8, 2011 at 11:41 PM, John R Pierce  wrote:
> On 03/08/11 5:06 PM, Reece Hart wrote:
>>
>> I'm considering porting a MySQL database to PostgreSQL. That database uses
>> MySQL's SET type. Does anyone have advice about representing this type in
>> PostgreSQL?
>>
>> MySQL DDL excerpt:
>> CREATE TABLE `transcript_variation` (
>>  `transcript_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
>>  `transcript_stable_id` varchar(128) NOT NULL,
>>   ...
>>  `consequence_type`
>> set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','SPLICE_SITE')
>> ) ENGINE=MyISAM AUTO_INCREMENT=174923212 DEFAULT CHARSET=latin1;
>>
>>
>
> why not just have a set of booleans in the table for these individual on/off
> attributes?   wouldn't that be simplest?

+1

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] FW: backup using pg_dump postgreSQL 8.3.8

[Adrian Klaver]

On Wednesday, March 09, 2011 5:58:20 am Sandy Test wrote:
>
> 
> 
> 
> 2. Is there a way of running a dos script that would run the pg_dump
> providing a password when prompted?

Using .pgpass
http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html



> 
> 
> 
> Thanks
> 
> 
> 
> Sandy Test
>
-- 
Adrian Klaver
adrian.kla...@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] FW: backup using pg_dump postgreSQL 8.3.8

[Tom Lane]

Andrew Sullivan  writes:
> On Wed, Mar 09, 2011 at 03:58:20PM +0200, Sandy Test wrote:
>> We want to be able to run a nightly backup using the pg_dump command.
>> 
>> Unfortunately, even with the pg_hba.conf fix of adding host postgres ...
>> trust, 
>> 
>> It still asks for a password.

> pg_hba sometimes surprises people because of its matching rules.

If it is asking for a password, and password authentication is what's
supposed to be used, then pg_hba is not where the problem is.  What
I think is that the OP has placed his client-side pgpass file in the
wrong place, or given it the wrong name, or the wrong permissions,
or the wrong contents.

I'm not too sure what the right name or place is on Windows, though,
so I have to defer to those who use that platform for more detailed
help.

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] FW: backup using pg_dump postgreSQL 8.3.8

[Andrew Sullivan]

On Wed, Mar 09, 2011 at 10:31:56AM -0500, Tom Lane wrote:
> Andrew Sullivan  writes:
> > On Wed, Mar 09, 2011 at 03:58:20PM +0200, Sandy Test wrote:

> >> Unfortunately, even with the pg_hba.conf fix of adding host postgres ...
> >> trust, 

> If it is asking for a password, and password authentication is what's
> supposed to be used, then pg_hba is not where the problem is.

Yes, but I think the OP was saying that the setting was moved to
trust.  My point was just that adding a trust entry isn't enough if
there's an earlier password entry.

I agree that .pgpass is probably the desired answer anyway.

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
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] Why length(to_char(1::integer, '9')) = 2 ?

[Adrian Klaver]

On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote:

> 
> But I am missing something or there is a documentation inaccuracy:
> http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO
> NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding blanks and
> zeroes)
> 
> Test:
> dmitigr=> select to_char(12,'FM0009');
>  to_char
> -
>  0012
> 
> dmitigr=> select length(to_char(12,'FM0009'));
>  length
> 
>   4
> 
> So, FM suppresses only padding blanks not zeroes...
> 
> Any comments?
> 

test(5432)aklaver=>select to_char(12,'');
 to_char 
-
12

test(5432)aklaver=>select to_char(12,'FM');
 to_char 
-
 12

It is a little confusing, but you asked for the 0 in your specification so they 
are not considered padding. 

Look at the examples in the table listed below to get an idea of what I am 
talking about.
http://www.postgresql.org/docs/9.0/static/functions-formatting.html
Table 9-25


-- 
Adrian Klaver
adrian.kla...@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

[GENERAL] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[John Edens]

Hey guys, I'm trying to get a VB program to make a client connection to my
PostgreSQL server running on an 

 

Ubuntu 10.10 server.

 

Here's what I've done:

 

Client side - installed and registered the OLE DB .dll's from the PostgreSQL
OLE DB Provider project.

 

server side - 

 

the configuration described here:
https://help.ubuntu.com/10.10/serverguide/C/postgresql.html

 

---

Have added this line to postgresql.conf:

 

 

listen_addresses = '*, 144.96.80.35, localhost'

 

I've also tried this as simply:

 

listen_addresses = '144.96.80.35, localhost'

 

also, port = 5432

 

--

 

I have added the following to pg_hba.conf:

 

 

# IPv4 local connections:

hostall all 144.96.80.35  255.255.255.0 md5

local   all postgresmd5

 

 

--

 

I've got the following code going in VB:

 

Public Class Form1

 

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Const connString As String = "Provider=PostgreSQL;
Server=144.96.80.35; User Id=postgres;Password=postgres;"

 

Dim theOleDBconnection As New OleDb.OleDbConnection

 

theOleDBconnection.ConnectionString = connString

 

 

theOleDBconnection.Open()

End Sub

End Class

 

So when the project opens up, it should make the connection and display the
form.

 

What happens is that I get an error...

 

OleDbException was unhandled

 

could not connect to server: Connection refused (0x274D/10061)

   Is the server running on host "" and accepting

   TCP/IP connections on port 5432?

 

---

 

So, obviously I've got something configured incorrectly.

 

I'm troubled that the VB error seems to show a null value for the host.
Shouldn't that be the Server value in 

 

the connection string?

 

Thanks to all for any help.

 

[GENERAL] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[John Edens]

Hey guys, I'm trying to get a VB program to make a client connection to my
PostgreSQL server running on an 

Ubuntu 10.10 server.

 

Here's what I've done:

 

Client side - installed and registered the OLE DB .dll's from the PostgreSQL
OLE DB Provider project.

 

server side - 

 

the configuration described here:
https://help.ubuntu.com/10.10/serverguide/C/postgresql.html

 

---

Have added this line to postgresql.conf:

 

 

listen_addresses = '*, 144.96.80.35, localhost'

 

I've also tried this as simply:

 

listen_addresses = '144.96.80.35, localhost'

 

also, port = 5432

 

--

 

I have added the following to pg_hba.conf:

 

 

# IPv4 local connections:

hostall all 144.96.80.35  255.255.255.0 md5

local   all postgresmd5

 

 

--

 

I've got the following code going in VB:

 

Public Class Form1

 

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Const connString As String = "Provider=PostgreSQL;
Server=144.96.80.35; User Id=postgres;Password=postgres;"

 

Dim theOleDBconnection As New OleDb.OleDbConnection

 

theOleDBconnection.ConnectionString = connString

 

 

theOleDBconnection.Open()

End Sub

End Class

 

So when the project opens up, it should make the connection and display the
form.

 

What happens is that I get an error...

 

OleDbException was unhandled

 

could not connect to server: Connection refused (0x274D/10061)

   Is the server running on host "" and accepting

   TCP/IP connections on port 5432?

 

---

 

So, obviously I've got something configured incorrectly.

 

I'm troubled that the VB error seems to show a null value for the host.
Shouldn't that be the Server value in 

the connection string?

 

Thanks to all for any help.

Re: [GENERAL] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[Steve Crawford]

On 03/09/2011 07:31 AM, John Edens wrote:


Hey guys, I'm trying to get a VB program to make a client connection 
to my PostgreSQL server running on an


Ubuntu 10.10 server

listen_addresses = '*, 144.96.80.35, localhost'



Using * should be fine unless you have multiple IP addresses and want 
the *server* to *listen* on only some of those addresses - say localhost 
if you were running web and db on the same machine and didn't want to 
listen to connections from the outside. This setting only determines 
where the server listens.


I have added the following to pg_hba.conf:

# IPv4 local connections:

hostall all 144.96.80.35  255.255.255.0 md5

local   all postgresmd5


This setting determines what *clients* are allowed to connect to the 
server. Your setting here is strange. It should be a proper network 
range. You have an IP address with a class-C netmask.


If you want to only accept connections from a specific single IP 
address, use 144.96.80.35/32 or 144.96.80.35 255.255.255.255. But if you 
want to listen to the class-C then 144.96.80.0/24 or 144.96.80.0 
255.255.255.0.


And since the IP address you gave appears real since it is in the 
assigned public space for Stephen F. Austin State University, I hope 
this machine is hiding behind a firewall.


Cheers,
Steve

Re: [GENERAL] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[Adrian Klaver]

On 03/09/2011 08:30 AM, John Edens wrote:

Hey guys, I'm trying to get a VB program to make a client connection to
my PostgreSQL server running on an

Ubuntu 10.10 server.

Here's what I've done:

Client side - installed and registered the OLE DB .dll's from the
PostgreSQL OLE DB Provider project.

server side -

the configuration described here:
https://help.ubuntu.com/10.10/serverguide/C/postgresql.html

---

Have added this line to postgresql.conf:

listen_addresses = '*, 144.96.80.35, localhost'


I would stick with listen_addresses = '*', this covers all the bases and 
simplifies things for the time being.





I've also tried this as simply:

listen_addresses = '144.96.80.35, localhost'

also, port = 5432

--

I have added the following to pg_hba.conf:

# IPv4 local connections:

host all all 144.96.80.35 255.255.255.0 md5

local all postgres md5


If I am following, 144.96.80.35 is your server address not the clients.
In pg_hba.conf the IPs are those from the clients you want to let in, so 
you will need to use an IP or mask that matches that of your client. To 
reduce confusion you might want to do that in the section for remote 
connections :)




--

I've got the following code going in VB:

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Const connString As String = "Provider=PostgreSQL; Server=144.96.80.35;
User Id=postgres;Password=postgres;"

Dim theOleDBconnection As New OleDb.OleDbConnection

theOleDBconnection.ConnectionString = connString

theOleDBconnection.Open()

End Sub

End Class

So when the project opens up, it should make the connection and display
the form.

What happens is that I get an error...

OleDbException was unhandled

could not connect to server: Connection refused (0x274D/10061)


Per the explanation above your client IP does not have access to the server.



Is the server running on host "" and accepting

TCP/IP connections on port 5432?

---

So, obviously I've got something configured incorrectly.

I'm troubled that the VB error seems to show a null value for the host.
Shouldn't that be the Server value in

the connection string?

Thanks to all for any help…






--
Adrian Klaver
adrian.kla...@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

[GENERAL] about memory size reported by system.

[Edmundo Robles L.]

Hi everyone!

I have a doubt about  the memory consumed by  each connection open.

suddenly my server, running postgres 8.3.11,  began to run   too slowly.

Checking the  process with: ps -adelfo pcpu,vsz,args | more

The processes,related to the connection to the db, each one  have a consume of 
memory near to the 50Mb. by the way i have 50 connections to the db, so i have 
a reported  memory consumed of 2.5M, when i have only 1G in the server.

If  I modify the shared_buffers value,  the report of  memory consumed  is 
according  with  shared buffers value.



Should I worry by that?
There is a  best way to tweak  the memory consumed by postgres?

--
SENSA Control Digital.
Ing. Edmundo Robles Lopez.
Analista Programador.





-- 
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] equivalent of mysql's SET type?

[Reece Hart]

On Tue, Mar 8, 2011 at 9:41 PM, John R Pierce  wrote:

> why not just have a set of booleans in the table for these individual
> on/off attributes?   wouldn't that be simplest?


I like that approach, but I think it's unlikely to fly in this specific case
for a couple reasons.

First, there are actually 8 factors (I edited for clarity... sorry about
that).

The original database is actively developed (released apx quarterly). I will
need an approach that minimizes my burden when they edit the set factors.

And, I'd like to be compatible with mysql syntax and semantics for sets. If
you hold your nose for a moment, you'll be able to read the following
without becoming ill: mysql uses comma delimited strings to assign and query
set types (but stored internally as bit vectors). So, one does
validation_status = 'cluster,freq' to set those bits or validation_status
like '%freq%' to query. Much to my chagrin, emulating this interface will
make migration easier. However, implementing this string interface to
set/get boolean columns is just too offensive to whatever modest design
sensibilities I have. (For more pleasure reading, see
http://dev.mysql.com/doc/refman/5.0/en/set.html. I particularly like the
*warning* issued when one tries to add a value that's not part of the set.)

-Reece

Re: [GENERAL] equivalent of mysql's SET type?

[Merlin Moncure]

On Wed, Mar 9, 2011 at 10:59 AM, Reece Hart  wrote:
> On Tue, Mar 8, 2011 at 9:41 PM, John R Pierce  wrote:
>>
>> why not just have a set of booleans in the table for these individual
>> on/off attributes?   wouldn't that be simplest?
>
> I like that approach, but I think it's unlikely to fly in this specific case
> for a couple reasons.
> First, there are actually 8 factors (I edited for clarity... sorry about
> that).
> The original database is actively developed (released apx quarterly). I will
> need an approach that minimizes my burden when they edit the set factors.
> And, I'd like to be compatible with mysql syntax and semantics for sets. If
> you hold your nose for a moment, you'll be able to read the following
> without becoming ill: mysql uses comma delimited strings to assign and query
> set types (but stored internally as bit vectors). So, one does
> validation_status = 'cluster,freq' to set those bits or validation_status
> like '%freq%' to query. Much to my chagrin, emulating this interface will
> make migration easier. However, implementing this string interface to
> set/get boolean columns is just too offensive to whatever modest design
> sensibilities I have. (For more pleasure reading, see
> http://dev.mysql.com/doc/refman/5.0/en/set.html. I particularly like the
> *warning* issued when one tries to add a value that's not part of the set.)
> -Reece

create type validation_flags as
(
  cluster bool,
  freq bool
);

create function validation_flags_in(
  flags text, flags out validation_flags) returns validation_flags as
$$
  select row($1 ~ 'cluster', $1 ~ 'freq')::validation_flags
$$ language sql immutable;

create table foo (flags validation_flags);
insert into foo values (validation_flags_in('cluster'));

select * from foo;
select (flags).* from foo;

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] Mounting file system for WAL on Solaris 10

[runner]

 

 PostgreSQL 9.0.3 on Solaris 10 Sparc

I have moved the WAL to a separate file system and set the forcedirectio option 
per popular recommendation.  My question is this:  As standard operating 
procedure in our shop, whenever we mount a UFS file system, we use the 
"logging" option.  Is there any problem with mounting the WAL file system with 
both "logging" and "forcedirectio" options?  In all the examples I've seen (and 
I've seen a lot) these two options are never used together.

I don't know enough about this area of sysadmin and I've already spent several 
hours google searching and reading up on this.  There comes a time when you 
have to cut your losses and ask someone else.

Thank you,
Rick

[GENERAL] select count(*)

[Rajesh Kumar Mallah]

Dear List ,

if we simply do select count(*) and not specify any table then it gives 1
eg:

bric=# SELECT count(*)  from job ;
 count
---
  2380
(1 row)

bric=# SELECT count(*)  job ;
 job
-
   1
(1 row)



bric=# SELECT count(*)  ;
 count
---
 1
(1 row)



bric=# SELECT count(*)  job_non_exist ;
 job_non_exist
---
 1
(1 row)

bric=# SELECT count(*)  jo1b ;
 jo1b
--
1
(1 row)

bric=# SELECT count(*)  none ;
ERROR:  syntax error at or near "none"
LINE 1: SELECT count(*)  none ;


I fail to see any  progression ?


regds
mallah.

Re: [GENERAL] ER tool that supports domains and custom types?

[Jaiswal Dhaval Sudhirkumar]

open system architect (open source), E-R data modeling, DBWrench.

If you are planning to go for paid one than E-R data modeling is the best one. 

--
Thanks & Regards
Dhaval Jaiswal 


-Original Message-
From: pgsql-general-ow...@postgresql.org on behalf of Arturo Perez
Sent: Wed 3/9/2011 6:26 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] ER tool that supports domains and custom types?
 
Hi all,

Anyone have a suggestion for a graphical ER tool that can work with 
Postgresql's domains and custom types?

I was using Mogwai designer but it can't reverse engineer a DB with the 
above.

tia, 
arturo

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

The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. 
Any review, re-transmission, dissemination or other use of or taking of any 
action in reliance upon,this information by persons or entities other than the 
intended recipient is prohibited. 
If you received this in error, please contact the sender and delete the 
material from your computer. 
Microland takes all reasonable steps to ensure that its electronic 
communications are free from viruses. 
However, given Internet accessibility, the Company cannot accept liability for 
any virus introduced by this e-mail or any attachment and you are advised to 
use up-to-date virus checking software. 

Re: [GENERAL] select count(*)

[Bill Moran]

In response to Rajesh Kumar Mallah :

> Dear List ,
> 
> if we simply do select count(*) and not specify any table then it gives 1
> eg:
> 
> bric=# SELECT count(*)  from job ;
>  count
> ---
>   2380
> (1 row)
> 
> bric=# SELECT count(*)  job ;
>  job
> -
>1
> (1 row)
> 
> 
> 
> bric=# SELECT count(*)  ;
>  count
> ---
>  1
> (1 row)
> 
> 
> 
> bric=# SELECT count(*)  job_non_exist ;
>  job_non_exist
> ---
>  1
> (1 row)
> 
> bric=# SELECT count(*)  jo1b ;
>  jo1b
> --
> 1
> (1 row)
> 
> bric=# SELECT count(*)  none ;
> ERROR:  syntax error at or near "none"
> LINE 1: SELECT count(*)  none ;
> 
> 
> I fail to see any  progression ?

When you don't specify a FROM clause, you get 1 because it's
returning 1 row.  No matter what you alias the result to, it's not going
to change the result, unless of course you try to alias it to an SQL
reserved word, such as "none", without quoting it.  Of course, if you
include the optional AS, it probably makes more sense what's going on:

SELECT count(*) AS jolb;
SELECT count(*) AS none;
SELECT count(*) AS "none";

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

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

Re: [GENERAL] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[John Edens]

Using * should be fine unless you have multiple IP addresses and want the
*server* to *listen* on only some of those addresses - say localhost if you
were running web and db on the same machine and didn't want to listen to
connections from the outside. This setting only determines where the server
listens. 

-

 

Okay, done that - what is the difference between  listening on only some of
those addresses and allowing only clients in a certain range to connect?

 

 



And since the IP address you gave appears real since it is in the assigned
public space for Stephen F. Austin State University, I hope this machine is
hiding behind a firewall. 



 

Yes, behind a firewall and also that machine is a brand new linux box that
literally has nothing else on it besides a new install of PostgreSQL

 

But, yeah, should have anoned up the address...

 

Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?

[Dmitriy Igrishin]

2011/3/9 Adrian Klaver 

> On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote:
>
> >
> > But I am missing something or there is a documentation inaccuracy:
> >
> http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO
> > NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding blanks
> and
> > zeroes)
> >
> > Test:
> > dmitigr=> select to_char(12,'FM0009');
> >  to_char
> > -
> >  0012
> >
> > dmitigr=> select length(to_char(12,'FM0009'));
> >  length
> > 
> >   4
> >
> > So, FM suppresses only padding blanks not zeroes...
> >
> > Any comments?
> >
>
> test(5432)aklaver=>select to_char(12,'');
>  to_char
> -
>12
>
> test(5432)aklaver=>select to_char(12,'FM');
>  to_char
> -
>  12
>
> It is a little confusing, but you asked for the 0 in your specification so
> they
> are not considered padding.
>
> Look at the examples in the table listed below to get an idea of what I am
> talking about.
> http://www.postgresql.org/docs/9.0/static/functions-formatting.html
> Table 9-25
>
Yes, I see, thanks!

I just talking about phrase "fill mode (suppress padding blanks and zeroes)"
in the documentation should be rephrased to "fill mode (suppress padding
blanks)".

Or I misunderstood what is "padding zeroes" without explicitly
specification "0" pattern in the format format template...

>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>



-- 
// Dmitriy.

Re: [GENERAL] about memory size reported by system.

[Jaiswal Dhaval Sudhirkumar]

Are you using any connection pooling system, if not look at the pgpool & 
pgbouncer. 

open connections always reserves memory, which slowdown the performance of 
system. 

you can also handle application connections at application level by defining 
garbage 
value if any connection disconnect. It will release the memory reserved by 
connection.  


--
Thanks & Regards
Dhaval Jaiswal 


-Original Message-
From: pgsql-general-ow...@postgresql.org on behalf of Edmundo Robles L.
Sent: Wed 3/9/2011 10:12 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] about memory size  reported by system.
 
Hi everyone!

I have a doubt about  the memory consumed by  each connection open.

suddenly my server, running postgres 8.3.11,  began to run   too slowly.

Checking the  process with: ps -adelfo pcpu,vsz,args | more

The processes,related to the connection to the db, each one  have a consume of 
memory near to the 50Mb. by the way i have 50 connections to the db, so i have 
a reported  memory consumed of 2.5M, when i have only 1G in the server.

If  I modify the shared_buffers value,  the report of  memory consumed  is 
according  with  shared buffers value.



Should I worry by that?
There is a  best way to tweak  the memory consumed by postgres?

--
SENSA Control Digital.
Ing. Edmundo Robles Lopez.
Analista Programador.





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

The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. 
Any review, re-transmission, dissemination or other use of or taking of any 
action in reliance upon,this information by persons or entities other than the 
intended recipient is prohibited. 
If you received this in error, please contact the sender and delete the 
material from your computer. 
Microland takes all reasonable steps to ensure that its electronic 
communications are free from viruses. 
However, given Internet accessibility, the Company cannot accept liability for 
any virus introduced by this e-mail or any attachment and you are advised to 
use up-to-date virus checking software. 

Re: [GENERAL] Copying data from one table to another - how to specify fields?

[David Johnston]

SELECT username, count(username) FROM phpbb_users
GROUP BY username
HAVING count(username) > 1;

If anything shows up then (phpbb_users .username) is not a unique field but
you are trying to insert it into one that is (drupal_users.uid)



-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
Sent: Wednesday, March 09, 2011 9:48 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Copying data from one table to another - how to
specify fields?

Hello Raymond and others,

thank you for looking at my problem!

It is a core Drupal 7 table, with a prefix "drupal_"
(you can set it while installing Drupal 7 in adv. options).

I have only 2 records in that target table:

# select uid, name, mail, created, access from drupal_users;
 uid | name |mail|  created   |   access
-+--+++
   0 |  ||  0 |  0
   1 | Alex | alexander.far...@gmail.com | 1299512207 | 1299753672
(2 rows)

I've tried you suggestion and the conflicting record is 'Alex':

#  select username, user_id from phpbb_users where exists  (select 1 from
drupal_users
   where drupal_users.name = phpbb_users.username);  username | user_id
--+-
 Alex |   2
(1 row)

But isn't that record excluded by the conditions below?

# INSERT INTO drupal_users (uid, name, mail, created, access)  SELECT
user_id, username, user_email, user_regdate, user_lastvisit
   FROM phpbb_users
   WHERE user_id > 50 and length(username) > 0 and username <> 'Alex';
ERROR:  duplicate key value violates unique constraint
"drupal_users_name_key"

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


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

Re: [GENERAL] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[John Edens]

>If I am following, 144.96.80.35 is your server address not the clients.
>In pg_hba.conf the IPs are those from the clients you want to let in, so 
>you will need to use an IP or mask that matches that of your client. To 
>reduce confusion you might want to do that in the section for remote 
>connections :)

>Per the explanation above your client IP does not have access to the
server.

Okay, I have set the mask as described...

I can now use pgAdmin III to access the database on the server from my
desktop machine, which I could not previously do.

However, the VB code still gives the error previously described.

Thanks for the config file help - any ideas about the OLE DB connection
problem?


-- 
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] Why length(to_char(1::integer, '9')) = 2 ?

[Adrian Klaver]

On 03/09/2011 09:59 AM, Dmitriy Igrishin wrote:



2011/3/9 Adrian Klaver mailto:adrian.kla...@gmail.com>>

On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote:

 >
 > But I am missing something or there is a documentation inaccuracy:
 >
http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO
 > NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding
blanks and
 > zeroes)
 >
 > Test:
 > dmitigr=> select to_char(12,'FM0009');
 >  to_char
 > -
 >  0012
 >
 > dmitigr=> select length(to_char(12,'FM0009'));
 >  length
 > 
 >   4
 >
 > So, FM suppresses only padding blanks not zeroes...
 >
 > Any comments?
 >

test(5432)aklaver=>select to_char(12,'');
  to_char
-
12

test(5432)aklaver=>select to_char(12,'FM');
  to_char
-
  12

It is a little confusing, but you asked for the 0 in your
specification so they
are not considered padding.

Look at the examples in the table listed below to get an idea of
what I am
talking about.
http://www.postgresql.org/docs/9.0/static/functions-formatting.html
Table 9-25

Yes, I see, thanks!

I just talking about phrase "fill mode (suppress padding blanks and zeroes)"
in the documentation should be rephrased to "fill mode (suppress padding
blanks)".


To get technical it means suppress unspecified padding O's. See below 
for example.




Or I misunderstood what is "padding zeroes" without explicitly
specification "0" pattern in the format format template...


This combination from the example table shows that:

to_char(-0.1, 'FM9.99') '-.1'
to_char(0.1, '0.9') ' 0.1'

The 0 in 0.1 is not strictly needed, so if you use FM it will be suppressed.






--
Adrian Klaver
adrian.kla...@gmail.com 




--
// Dmitriy.





--
Adrian Klaver
adrian.kla...@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] Why length(to_char(1::integer, '9')) = 2 ?

[Dmitriy Igrishin]

2011/3/9 Adrian Klaver 

> On 03/09/2011 09:59 AM, Dmitriy Igrishin wrote:
>
>>
>>
>> 2011/3/9 Adrian Klaver > >
>>
>>
>>On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote:
>>
>> >
>> > But I am missing something or there is a documentation inaccuracy:
>> >
>>
>> http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO
>> > NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding
>>blanks and
>> > zeroes)
>> >
>> > Test:
>> > dmitigr=> select to_char(12,'FM0009');
>> >  to_char
>> > -
>> >  0012
>> >
>> > dmitigr=> select length(to_char(12,'FM0009'));
>> >  length
>> > 
>> >   4
>> >
>> > So, FM suppresses only padding blanks not zeroes...
>> >
>> > Any comments?
>> >
>>
>>test(5432)aklaver=>select to_char(12,'');
>>  to_char
>>-
>>12
>>
>>test(5432)aklaver=>select to_char(12,'FM');
>>  to_char
>>-
>>  12
>>
>>It is a little confusing, but you asked for the 0 in your
>>specification so they
>>are not considered padding.
>>
>>Look at the examples in the table listed below to get an idea of
>>what I am
>>talking about.
>>http://www.postgresql.org/docs/9.0/static/functions-formatting.html
>>Table 9-25
>>
>> Yes, I see, thanks!
>>
>> I just talking about phrase "fill mode (suppress padding blanks and
>> zeroes)"
>> in the documentation should be rephrased to "fill mode (suppress padding
>> blanks)".
>>
>
> To get technical it means suppress unspecified padding O's. See below for
> example.
>
>
>
>> Or I misunderstood what is "padding zeroes" without explicitly
>> specification "0" pattern in the format format template...
>>
>
> This combination from the example table shows that:
>
> to_char(-0.1, 'FM9.99') '-.1'
> to_char(0.1, '0.9') ' 0.1'
>
> The 0 in 0.1 is not strictly needed, so if you use FM it will be
> suppressed.
>
Ahh, I guess I understand (thanks to you examples).
Lets look at the test:

dmitigr=> SELECT '>'||to_char(-0.1, 'FM9.99')||'<' AS v;
   v
---
 >-.1<

dmitigr=> SELECT '>'||to_char(0.1, '0.9')||'<' AS v;
   v

 > 0.1<

dmitigr=> SELECT '>'||to_char(0.1, 'FM0.9')||'<' AS v;
   v
---
 >0.1<

dmitigr=> SELECT '>'||to_char(0.1, '0.9')||'<' AS v;
 v

 > 0.1<

dmitigr=> SELECT '>'||to_char(0.1, 'FM0.9')||'<' AS v;
   v
---
 >0.1<

So, padding zeroes suppressed by FM is a rest of the value.

Thank you very much!


>
>
>
>>
>>
>>--
>>Adrian Klaver
>>adrian.kla...@gmail.com 
>>
>>
>>
>>
>> --
>> // Dmitriy.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>



-- 
// Dmitriy.

Re: [GENERAL] about memory size reported by system.

[Scott Marlowe]

On Wed, Mar 9, 2011 at 9:42 AM, Edmundo Robles L.
 wrote:
> Hi everyone!
>
> I have a doubt about  the memory consumed by  each connection open.
>
> suddenly my server, running postgres 8.3.11,  began to run   too slowly.
>
> Checking the  process with: ps -adelfo pcpu,vsz,args | more
>
> The processes,related to the connection to the db, each one  have a consume 
> of memory near to the 50Mb. by the way i have 50 connections to the db, so i 
> have a reported  memory consumed of 2.5M, when i have only 1G in the server.
>
> If  I modify the shared_buffers value,  the report of  memory consumed  is 
> according  with  shared buffers value.

The most likely explanation is that you're misinterpreting the restuls
of your ps output.   Vsize is virtual size which includes all libs
linked whether loaded or not and the amount of shared memory the
process is touching.  For instance, here's the output of top on my
machine running postgres at work:

  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
14037 dgish 20   0 4259m 455m 445m R   98  0.4  31:16.24 postgres
32318 dgish 20   0 4260m 2.0g 1.9g R   98  1.6   0:08.59 postgres
32464 dgish 20   0 4263m 1.4g 1.4g R   91  1.2   0:06.96 postgres
 2150 dgish 20   0 4254m 227m 220m S   86  0.2   0:01.94 postgres
  866 dgish 20   0 4270m 1.5g 1.4g S   50  1.2   0:08.46 postgres
29930 dgish 20   0 4263m 1.6g 1.6g R   34  1.3   0:15.99 postgres
  888 dgish 20   0 4259m 239m 230m S   33  0.2   0:02.01 postgres

Note that virt is a tad over 4G.  Can you guess what my shared_buffers
are set to?  Yep, 4G.  Each process is aware of all the
shared_buffers.  Actual resident / in use memory ranges from 455M to
2G, but we subtract the shared memory it's using right now for
shared_buffers and we get a delta, which is in the 100Meg or less
range.  So each individual backend is using < !00Meg (good thing I've
got several huindred of them).

-- 
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 configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[Adrian Klaver]

On 03/09/2011 09:59 AM, John Edens wrote:

If I am following, 144.96.80.35 is your server address not the clients.
In pg_hba.conf the IPs are those from the clients you want to let in, so
you will need to use an IP or mask that matches that of your client. To
reduce confusion you might want to do that in the section for remote
connections :)



Per the explanation above your client IP does not have access to the

server.

Okay, I have set the mask as described...

I can now use pgAdmin III to access the database on the server from my
desktop machine, which I could not previously do.

However, the VB code still gives the error previously described.

Thanks for the config file help - any ideas about the OLE DB connection
problem?



What I know about OLE would fit in the navel of a flea:) That being said 
I offer:

http://www.connectionstrings.com/postgre-sql#p53

It would seem your connection string needs different information.


--
Adrian Klaver
adrian.kla...@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] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[Steve Crawford]

On 03/09/2011 09:54 AM, John Edens wrote:




Using * should be fine unless you have multiple IP addresses and want 
the *server* to *listen* on only some of those addresses - say 
localhost if you were running web and db on the same machine and 
didn't want to listen to connections from the outside. This setting 
only determines where the server listens.


-

Okay, done that - what is the difference between  listening on only 
some of those addresses and allowing only clients in a certain range 
to connect?




They are completely different.

Your server can have multiple IP addresses. In fact, it almost certainly 
has an IP assigned to your NIC and a localhost address. But you could 
have multiple NICs in the machine and/or multiple IP addresses assigned 
to each NIC. This is very common - especially for web servers hosting 
several sites on different addresses. The addresses can be on different 
networks, as well.


Suppose your server has addresses 10.1.1.2, 10.1.1.3 and 192.168.1.4. If 
listen_addresses is set to *, a client could connect to PostgreSQL by 
connecting to any of those addresses - i.e. the host in their connection 
string could be any of the addresses you listen on. If that is not what 
you want, you need to explicitly list the addresses you want PostgreSQL 
to use.


Now that you are listening on one or more addresses, the pg_hba.conf 
determines who is allowed to connect. It is completely independent of 
where you listen. It could include all IP addresses, or one or more 
individual IPs or IP ranges. And the IP addresses allowed for clients do 
not need to overlap your listen_address or be on the same IP network.


By way of example, you could have three independent instances of 
PostgreSQL running on your machine - perhaps one for dev, one for QA and 
one for deployment-staging. You could have dev listen only on .2, QA on 
.3 and deploy on .4. You could even have a 4th, say "sandbox", listening 
on all (though it would have to have a different port). And each one of 
those instances could have different pg_hba.conf settings to restrict 
client access as appropriate.


Cheers,
Steve

Re: [GENERAL] select count(*)

[Rajesh Kumar Mallah]

i "discovered" it as a result of typo :)

we usually select expressions without tables
eg select 1+2 ; etc and the results are as expected,
somehow i failed to stretch the analogy to count(*)
which is mostly used over tables or table expression.

thanks anyways.

regds
mallah.


On Wed, Mar 9, 2011 at 11:20 PM, Bill Moran wrote:

> In response to Rajesh Kumar Mallah :
>
> > Dear List ,
> >
> > if we simply do select count(*) and not specify any table then it gives 1
> > eg:
> >
> > bric=# SELECT count(*)  from job ;
> >  count
> > ---
> >   2380
> > (1 row)
> >
> > bric=# SELECT count(*)  job ;
> >  job
> > -
> >1
> > (1 row)
> >
> >
> >
> > bric=# SELECT count(*)  ;
> >  count
> > ---
> >  1
> > (1 row)
> >
> >
> >
> > bric=# SELECT count(*)  job_non_exist ;
> >  job_non_exist
> > ---
> >  1
> > (1 row)
> >
> > bric=# SELECT count(*)  jo1b ;
> >  jo1b
> > --
> > 1
> > (1 row)
> >
> > bric=# SELECT count(*)  none ;
> > ERROR:  syntax error at or near "none"
> > LINE 1: SELECT count(*)  none ;
> >
> >
> > I fail to see any  progression ?
>
> When you don't specify a FROM clause, you get 1 because it's
> returning 1 row.  No matter what you alias the result to, it's not going
> to change the result, unless of course you try to alias it to an SQL
> reserved word, such as "none", without quoting it.  Of course, if you
> include the optional AS, it probably makes more sense what's going on:
>
> SELECT count(*) AS jolb;
> SELECT count(*) AS none;
> SELECT count(*) AS "none";
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>

[GENERAL] pg_restore: [archiver] unsupported version (1.12) in file header

[akp geek]

I am getting the following error when I try to restore it from a dump. Any
suggestions?

Regards

[GENERAL] Huge spikes in number of connections doing "PARSE"

[hubert depesz lubaczewski]

hi,
i have a server with pg 8.3.11.

Every now and then, for no apparent reason (io normal, cpu usage normal,
web traffic normal) i see hundreds of connections in "PARSE" state.

to give some perspective - we're doing in the lowest traffic of the day
around 3000 transactions per second, with ~ 500-600 connections.

So. every now and then (couple of times per day at most). I see hundreds
(800-900) of connections in "PARSE" state.

I did notice one thing.

we do log output of ps axo 
user,pid,ppid,pgrp,%cpu,%mem,rss,lstart,nice,nlwp,sgi_p,cputime,tty,wchan:25,args
every 15 seconds or so.

And based on its output, I was able to get stats of "wchan" of all PARSE
pg processes when the problem was logged.
Results:

805 x semtimedop
 10 x stext

Any ideas on what could be wrong? Machine was definitely not loaded most
of the times it happened.

The problem usually goes away in ~ 10-15 seconds.

Any hints? Suggestions?

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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 configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[John Edens]

Thanks for that explanation, Steve. This is my first time doing much of
anything with a server, so that's all good to know!

John

 


Your server can have multiple IP addresses. In fact, it almost certainly has
an IP assigned to your NIC and a localhost address. But you could have
multiple NICs in the machine and/or multiple IP addresses assigned to each
NIC. This is very common - especially for web servers hosting several sites
on different addresses. The addresses can be on different networks, as well.
-

Re: [GENERAL] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

[John Edens]

That's a great site - I am now making a connection.

Thanks for all the help!
John

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com] 
Sent: Wednesday, March 09, 2011 12:28 PM
To: John Edens
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to configure for remote TCP/IP client
conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

On 03/09/2011 09:59 AM, John Edens wrote:
>> If I am following, 144.96.80.35 is your server address not the clients.
>> In pg_hba.conf the IPs are those from the clients you want to let in, 
>> so you will need to use an IP or mask that matches that of your 
>> client. To reduce confusion you might want to do that in the section 
>> for remote connections :)
>
>> Per the explanation above your client IP does not have access to the
> server.
>
> Okay, I have set the mask as described...
>
> I can now use pgAdmin III to access the database on the server from my 
> desktop machine, which I could not previously do.
>
> However, the VB code still gives the error previously described.
>
> Thanks for the config file help - any ideas about the OLE DB 
> connection problem?
>

What I know about OLE would fit in the navel of a flea:) That being said I
offer:
http://www.connectionstrings.com/postgre-sql#p53

It would seem your connection string needs different information.


--
Adrian Klaver
adrian.kla...@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

[GENERAL] Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT

[Vlad Romascanu]

Hello,

I need to perform "conversions" (transcoding) between BYTEA and TEXT
columns in a UTF-8 database.  I searched for existing solutions and
was unable to find one for 8.x or 9.x, so I cam up with something I'd
like to validate with the more enlightened members of this list...

Case 1: reinterpreting:

(Working on a 8.4.3 backend.)

In a UTF8 database I have a BYTEA value which contains a perfectly
valid UTF8 string.  I want to simply *reinterpret* it as TEXT (in the
same way pg_convert_from internally reinterprets the BYTEA return
value from pg_convert as TEXT), backend-side, no
transcoding/encoding/decoding should take place.

The solution I came up with goes something like this:

  CREATE DOMAIN my_varlena AS bytea;
  CREATE CAST (my_varlena AS text) WITHOUT FUNCTION;
  ...
  SELECT bytea_col::my_varlena::text FROM tbl; -- bypass the bytea to
varchar/text conversion which actually calls encode()
  ...
  DROP DOMAIN my_varlena CASCADE;

Is there anything blatantly wrong with this approach that I have
missed, or is there a more straightforward way, or anything to be
improved?  (Again, I need a backend-side solution, not a client-side
one -- e.g. copying huge amounts of data from a BYTEA column to a TEXT
column in some other table.)



Case 2: converting:

(Working on a 8.4.3 backend.)

In a UTF8 database I have a BYTEA value which contains a perfectly
valid e.g. LATIN1 string.  Building on top of the above (again, 100%
backend-side), is there anything blatantly wrong with:

  CREATE DOMAIN my_varlena AS bytea;
  CREATE CAST (my_varlena AS text) WITHOUT FUNCTION;
  ...
  SELECT convert(bytea_col::bytea, 'LATIN1', 'UTF8')::my_varlena::text FROM tbl;
  ...
  DROP DOMAIN my_varlena CASCADE;


Thank you!

PS: Incidentally, if bytea_col contains any of the invalid LATIN1
sequences 0x7f, 0x80, 0x81, 0x82 etc., pg_convert (v8.4.3) will
(leniently) convert them to the invalid UTF-8 BYTEA sequences 0x7f,
0xc2 0x80, 0xc2 0x81, 0xc2 0x82 etc. :)

-- 
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] Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT

[Merlin Moncure]

On Wed, Mar 9, 2011 at 4:09 PM, Vlad Romascanu  wrote:
> Hello,
>
> I need to perform "conversions" (transcoding) between BYTEA and TEXT
> columns in a UTF-8 database.  I searched for existing solutions and
> was unable to find one for 8.x or 9.x, so I cam up with something I'd
> like to validate with the more enlightened members of this list...
>
> Case 1: reinterpreting:
>
> (Working on a 8.4.3 backend.)
>
> In a UTF8 database I have a BYTEA value which contains a perfectly
> valid UTF8 string.  I want to simply *reinterpret* it as TEXT (in the
> same way pg_convert_from internally reinterprets the BYTEA return
> value from pg_convert as TEXT), backend-side, no
> transcoding/encoding/decoding should take place.
>
> The solution I came up with goes something like this:
>
>  CREATE DOMAIN my_varlena AS bytea;
>  CREATE CAST (my_varlena AS text) WITHOUT FUNCTION;
>  ...
>  SELECT bytea_col::my_varlena::text FROM tbl; -- bypass the bytea to
> varchar/text conversion which actually calls encode()
>  ...
>  DROP DOMAIN my_varlena CASCADE;
>
> Is there anything blatantly wrong with this approach that I have
> missed, or is there a more straightforward way, or anything to be
> improved?  (Again, I need a backend-side solution, not a client-side
> one -- e.g. copying huge amounts of data from a BYTEA column to a TEXT
> column in some other table.)

I think convert_from is a little more direct:
convert_from(string bytea, src_encoding name)
http://www.postgresql.org/docs/8.4/interactive/functions-string.html

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] pg_restore: [archiver] unsupported version (1.12) in file header

[Adrian Klaver]

On Wednesday, March 09, 2011 12:19:13 pm akp geek wrote:
> I am getting the following error when I try to restore it from a dump. Any
> suggestions?

At a guess you are trying to go backwards, using a newer dump file with an 
older 
pg_restore. In other words doing 9.0 pg_dump and then 8.3 pg_restore. Could you 
clarify what is the exact procedure you are using?

> 
> Regards

-- 
Adrian Klaver
adrian.kla...@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

[GENERAL] list all members in a tablespace

[Michael Andrew Babb]

Hi All,

I'm doing a little housekeeping on my tablespaces and I'm curious if there is a 
quick and easy way to list all of the objects in a tablespace. Is there a 
command to list all objects in a tablespace?

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] ER tool that supports domains and custom types?

[Arturo Perez]

In article 
,
 "Jaiswal Dhaval Sudhirkumar"  wrote:

> E-R data modeling

Couldn't find the E-R data modeling and Open System Architect doesn't 
support Mac OS.  Forgot to mention that latter in my original post.

dbwrench fails to connect to my database even though psql connects just 
fine.  go figure.

-arturo


-- 
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] list all members in a tablespace

[Raghavendra]

Hi Mike,

I tried this, which will get the list of tables belong to 'XYZ' tablespace.

select relname from pg_class where reltablespace=(select oid from
pg_tablespace where spcname='xyz');

Hope this helps

Best Regards,
Raghavendra
EnterpriseDB Corporation
The Enterprise Postgres Company

On Thu, Mar 10, 2011 at 6:05 AM, Michael Andrew Babb  wrote:

> Hi All,
>
> I'm doing a little housekeeping on my tablespaces and I'm curious if there
> is a quick and easy way to list all of the objects in a tablespace. Is there
> a command to list all objects in a tablespace?
>
> 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
>

[GENERAL] 9.1 Trigger question

[Michael Black]

The following from 9.1 documentation on triggers 



"SQL allows you to define aliases for the "old"
  and "new" rows or tables for use in the definition
  of the triggered action (e.g., CREATE TRIGGER ... ON
  tablename REFERENCING OLD ROW AS somename NEW ROW AS othername
  ...).  Since PostgreSQL
  allows trigger procedures to be written in any number of
  user-defined languages, access to the data is handled in a
  language-specific way.
 "



This seems to imply that triggers actually have to reference a function 
rather than containing the actual code for the trigger to perform.  For 
example the only valid format of a trigger is to 

CREATE TRIGGER view_insert
--- other parameters here ---
   EXECUTE PROCEDURE view_insert_row();

Instead of the normal way

CREATE TRIGGER view_insert
--- other parameters here ---
   AS
--- sql functions, conditions and statements ---
;

Is my understand in of this correct?  If so, how does the other language know 
the old record from the new?

  

Re: [GENERAL] First production install - general advice

[mark]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Ray Stell
> Sent: Monday, March 07, 2011 10:45 AM
> To: runner
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] First production install - general advice
> 
> On Mon, Mar 07, 2011 at 12:34:19PM -0500, runner wrote:
> >
> > I'd like to know if any of you have ever installed a PostgreSQL
> database for production use and then found something you wish you had
> done differently after the fact.
> 
> 
> Test and document your disaster recovery plan.  You don't want be
> trying
> to figure it out when you need it.  It is what gets left in the scurry
> very often.   I'm pretty sure mine is dusty and I regret that.
> 

DR is a critical one to have worked out and well tested in advance of any
production deployment. 


I would add->
Explicitly setting the encoding, lc_ctype, and lc_collate when running
initdb. (less of an issue now with 9 than it was in the 8.{1,2,3} days,
since different encodings, ctypes and collate settings can at least
cohabitate in the same pg instance now)

Was a non-issue for us until some version/update of redhat when suddenly,
since we didn't specify what lc_ctype and lc_collate were supposed to be, we
foot-gunned out selves on creating a new database that took me a while
to figure out that that was the cause of some "slow" queries that were not
(as) slow on other boxes.


Obviously some people want to use settings for ctype and collate other than
straight C/POSIX. But it can be kind of shock when you expected them to be C
and they are not... at least it was for me  so make sure it explicitly gets
set to "whatever" you want, not just letting some defaults go in. 

Maybe that is just me.::shrug::


I will assume since you have the (great -IMO) high performance book by Greg
Smith you have done things around setting the xlog directory to a different
set of spindles, set readahead, using a more modern file system than ext3,
setting the IO scheduler , using a raid card with a battery backed write
cache, max semaphores, ...etc tuning the config parameters in
postgresql.conf ...etc...etc...etc. Using a connection pooler from the
start. Something to monitor server trends over time like cacti, something to
page (someone) when things go bad (nagios). And benchmark/profile as much as
possibile to compare to down the road. Something like are just good sys
admin things to have ... like say a tested and working lights out management


I am rambling but yeah ... those are all things that came to mind.

\mark


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

[GENERAL] 9.0 streaming replication problem

[Sean Hsien]

Hi,

I've setup 2 nodes for streaming replication and it was working
perfectly. I the tested multiple failovers, switching master/slave,
and now the streaming replication doesn't work anymore.

I had a look at the log and it seems fine "LOG:  replication
connection authorized: user=postgres ...". From ps, the sender and
receiver processes have been started. However there is something
strange with the sender process, ps says:

postgres: wal sender process postgres xxx.xxx.xxx.xxx (48159) startup

And the streaming never starts. What are the possible problems?


Thanks and regards,
Sean

-- 
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] 9.0 streaming replication problem

[Sean Hsien]

On 10 March 2011 14:21, Sean Hsien  wrote:
> Hi,
>
> I've setup 2 nodes for streaming replication and it was working
> perfectly. I the tested multiple failovers, switching master/slave,
> and now the streaming replication doesn't work anymore.
>
> I had a look at the log and it seems fine "LOG:  replication
> connection authorized: user=postgres ...". From ps, the sender and
> receiver processes have been started. However there is something
> strange with the sender process, ps says:
>
> postgres: wal sender process postgres xxx.xxx.xxx.xxx (48159) startup
>
> And the streaming never starts. What are the possible problems?

In the end the problem was that the 2 servers were out of sync.

I noticed the problem by doing another failover and examing the logs,
it said that the timelines did not match. So did another rsync of
$PGDATA and it was fixed.


Kind Regards,
Sean

-- 
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] Detecting whether a point is in a box.

[hrsuprith]

Dear Madam,

I had the same issue tonight.

I sorted it out, perhaps it may help you :)

Convert BOX to a polygon & insert some values as per the syntax.

Than run a query 

SELECT polygon '((-3,10),(8,18),(-3,30),(-10,20))' @> point '(-8,25)';

let me know if this doesn't work for you ;)

Cheers!
Rao

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Detecting-whether-a-point-is-in-a-box-tp1887472p3422647.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

[GENERAL] output screen in psql

[abcdef]

Hi, I use postgres in red hat linux .
When I use psql and select the content of the table .
I find the format is not tidy .
I change align and unalign , the output is not filful my requirement.
Any special setting that can change the line size like oralce to fit my
requirement ???
So, the whole table can be view within the screen of psql ???

===
kwokdb=#  select * from issue;
 issue_id |   issue_name   |
issue_description  | issue_assig
nee | issue_url | issue_type | issue_status | issue_priority |
issue_resolution | duplicate_id | creator | creation_date
| modifier |   modification_date   |   issue_due_date|  creator_ip  
| issue_created_from_email | issue_modified_from
_email
--+++
+---++--++--+--+-+---
+--+---+-+---+--+
---
3 | computer cannot power up   | test data:
computer cannot power up|
  1 |   |  2 |8 | 16 |  
20 |  |   1 | 2011-03-08 12:16:2
6.4 |  |   | 2011-03-31 00:00:00 | 192.168.1.143
|  

==
kwokdb=# select * from issue;
issue_id|issue_name|issue_description|issue_assignee|issue_url|issue_type|issue_status|issue_priority|issue_resolution|duplicate_id|creator|creation_date|modifier|modification_date|issue_due_date|creator_ip|issue_created_from_email|issue_modified_from_email
3|computer cannot power up|test data: computer cannot power
up|1||2|8|16|20||1|2011-03-08 12:16:26.4|||2011-03-31
00:00:00|192.168.1.143||
4|VMware workstation cannot install in XP|test data: Vmware workstation
cannot install in XP|4||4|8|16|23||4|2011-03-08
13:54:53.4192.168.1.143||
5|User cannot connect to wireless router by WIFI|User cannot connect to
wireless router by WIFI|1||2|7|15|19||4|2011-03-08 14:03:37.1|||2011-03-29
00:00:00|192.168.1.143||


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/output-screen-in-psql-tp3422631p3422631.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] output screen in psql

[Pavel Stehule]

Hello

2011/3/10 abcdef :
> Hi, I use postgres in red hat linux .
> When I use psql and select the content of the table .
> I find the format is not tidy .
> I change align and unalign , the output is not filful my requirement.
> Any special setting that can change the line size like oralce to fit my
> requirement ???
> So, the whole table can be view within the screen of psql ???
>

try wrap mode 
http://archives.postgresql.org/pgsql-committers/2008-05/msg00126.php

you can try 
http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#psql_together_with_less


Regards

Pavel Stehule

> ===
> kwokdb=#  select * from issue;
>  issue_id |                   issue_name                   |
> issue_description                  | issue_assig
> nee | issue_url | issue_type | issue_status | issue_priority |
> issue_resolution | duplicate_id | creator |     creation_date
>    | modifier |   modification_date   |   issue_due_date    |  creator_ip
> | issue_created_from_email | issue_modified_from
> _email
> --+++
> +---++--++--+--+-+---
> +--+---+-+---+--+
> ---
>        3 | computer cannot power up                       | test data:
> computer cannot power up                |
>  1 |           |          2 |            8 |             16 |
> 20 |              |       1 | 2011-03-08 12:16:2
> 6.4 |          |                       | 2011-03-31 00:00:00 | 192.168.1.143
> |
>
> ==
> kwokdb=# select * from issue;
> issue_id|issue_name|issue_description|issue_assignee|issue_url|issue_type|issue_status|issue_priority|issue_resolution|duplicate_id|creator|creation_date|modifier|modification_date|issue_due_date|creator_ip|issue_created_from_email|issue_modified_from_email
> 3|computer cannot power up|test data: computer cannot power
> up|1||2|8|16|20||1|2011-03-08 12:16:26.4|||2011-03-31
> 00:00:00|192.168.1.143||
> 4|VMware workstation cannot install in XP|test data: Vmware workstation
> cannot install in XP|4||4|8|16|23||4|2011-03-08
> 13:54:53.4192.168.1.143||
> 5|User cannot connect to wireless router by WIFI|User cannot connect to
> wireless router by WIFI|1||2|7|15|19||4|2011-03-08 14:03:37.1|||2011-03-29
> 00:00:00|192.168.1.143||
>
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/output-screen-in-psql-tp3422631p3422631.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
>

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