Re: [GENERAL] Is there a way to ask PostgreSQL for the name of the computer it's running on?

[Albe Laurenz]

Rob Richardson wrote:
> My customer has 3 computers.  The PostgreSQL service could be running
on either of two of them.  There
> is currently no way in our system to determine which one it is running
on.  The third computer
> sometimes needs to know which of the other two computers is active.
It would be enough to know which
> computer is running the PostgreSQL service.  Is the name of the server
available in a PostgreSQL
> database, so that it could be retrieved using a query?

As far as I know, you'd have to write a function
in C, PL/PerlU or something similar that runs an OS
command for you.

Yours,
Laurenz Albe

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

Re: [GENERAL] How to create c language in postgresql database. Thanks.

[Albe Laurenz]

leaf_yxj wrote:
> Thanks for your answers. I really appreciate it. Although I don't
understand the whole things you guys
> mentioned to me. I think maybe I should do it by myself. I need to do
a test. If there is any good
> guide/white paper, please give me a link for me to study.

I think the documentation is quite good and comprehensive:

http://www.postgresql.org/docs/current/static/xfunc-c.html

If you look for samples, the contrib modules will serve.

Yours,
Laurenz Albe

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

Re: [GENERAL] Daisy chaining replication slaves ?

[Albe Laurenz]

Rob Cowell wrote:
> I'm just wondering if there is a way to slave from a slave server?
> 
> I have a Postgres9.1.3 master serving up data quite happily to the web
applications, and I have also
> set up a slave via streaming replication.
> 
> I've now been asked by my manager to set up a secondary slave, slaving
off the first slave

That is not possible in 9.1.
In 9.2 there will be such a feature:

http://www.postgresql.org/docs/devel/static/warm-standby.html#CASCADING-
REPLICATION

You can try it with the beta version, that will also benefit
development.

Yours,
Laurenz Albe

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

[GENERAL] Backslashitis

[hamann . w]

Hi,

I have a column declared as array of text. I can get a single backslash into 
one of the array elements by
update ... set mycol[1] = E'blah \\here'
If I try to update the whole array
update ... set mycol = E'{"blah \\here"}'
the backslash is missing. I can get two backslashes there.
Is there a good way to solve the problem, other than rewriting my update script 
to do array updates one element at a time?

Regards
Wolfgang Hamann

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

[Raghavendra]

I think you need to double the quotes. Its mentioned in the PG documention
http://www.postgresql.org/docs/9.1/static/arrays.html

Eg:-

postgres=# update array_test set name=E'{"meeting"}';
UPDATE 2
postgres=# select * from array_test ;
 name
---
 {"meet\\ing"}
 {"meet\\ing"}
(2 rows)

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/



On Thu, Jun 14, 2012 at 1:47 PM,  wrote:

> Hi,
>
> I have a column declared as array of text. I can get a single backslash
> into one of the array elements by
> update ... set mycol[1] = E'blah \\here'
> If I try to update the whole array
> update ... set mycol = E'{"blah \\here"}'
> the backslash is missing. I can get two backslashes there.
> Is there a good way to solve the problem, other than rewriting my update
> script to do array updates one element at a time?
>
> Regards
> Wolfgang Hamann
>
> --
> 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] Backslashitis

[Thomas Kellerer]

haman...@t-online.de, 14.06.2012 10:17:

Hi,

I have a column declared as array of text. I can get a single backslash into 
one of the array elements by
update ... set mycol[1] = E'blah \\here'
If I try to update the whole array
update ... set mycol = E'{"blah \\here"}'
the backslash is missing. I can get two backslashes there.
Is there a good way to solve the problem, other than rewriting my update script 
to do array updates one element at a time?



Setting
   
standard_conforming_strings = true


should do the trick.

http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS

In that case you don't need any escaping inside the string literals.

Regards
Thomas





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

[Raghavendra]

On Thu, Jun 14, 2012 at 2:19 PM, Thomas Kellerer  wrote:

> haman...@t-online.de, 14.06.2012 10:17:
>
>  Hi,
>>
>> I have a column declared as array of text. I can get a single backslash
>> into one of the array elements by
>> update ... set mycol[1] = E'blah \\here'
>> If I try to update the whole array
>> update ... set mycol = E'{"blah \\here"}'
>> the backslash is missing. I can get two backslashes there.
>> Is there a good way to solve the problem, other than rewriting my update
>> script to do array updates one element at a time?
>>
>>
> Setting
>   standard_conforming_strings = true
>
> should do the trick.
>
> http://www.postgresql.org/**docs/current/static/runtime-**
> config-compatible.html#GUC-**STANDARD-CONFORMING-STRINGS
>
> In that case you don't need any escaping inside the string literals.
>
> Regards
> Thomas
>
>
Nope..

postgres=# show standard_conforming_strings ;
 standard_conforming_strings
-
 on
(1 row)
postgres=# set standard_conforming_strings =on;
SET
postgres=# show standard_conforming_strings ;
 standard_conforming_strings
-
 on
(1 row)
postgres=# update array_test set name=E'{"meet\\ing"}';
UPDATE 2
postgres=# select * from array_test ;
   name
---
 {meeting}
 {meeting}
(2 rows)

Correct me, if anything wrong.

--Raghav

Re: [GENERAL] Reference with inheritance propagate data

[Yuriy Rusinov]

Hello, Jeff !

>
> One foreign key cannot reference two tables.
>
> Have you considered a design that does not use inheritance? For
> instance, the users table could reference q_base_table, and then
> record_rubricator could also reference q_base_table?

Unfortunately not, because this design does not developed by me and
these changes will result to inefficiency of all project.
>
> Also, I don't understand what you mean about propagating data. What data
> do you want to propagate?

We're need common numeration for primary key for all users tables, but
others columns may be different for tables.

-- 
Best regards,
Sincerely yours,
Yuriy Rusinov.

-- 
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] Reference with inheritance propagate data

[Chris Travers]

On Thu, Jun 14, 2012 at 2:08 AM, Yuriy Rusinov  wrote:
> Hello, Jeff !
>
>>
>> One foreign key cannot reference two tables.
>>
>> Have you considered a design that does not use inheritance? For
>> instance, the users table could reference q_base_table, and then
>> record_rubricator could also reference q_base_table?
>
> Unfortunately not, because this design does not developed by me and
> these changes will result to inefficiency of all project.
>>
>> Also, I don't understand what you mean about propagating data. What data
>> do you want to propagate?
>
> We're need common numeration for primary key for all users tables, but
> others columns may be different for tables.
>
When we forked LedgerSMB from SQL-Ledger they used a similar design
and it was a mess.  I don't want to go into the problems we figured
could exist or we heard about from users.

Luckily if all you are looking for is a common place for primary keys
there is a useful shim that we came up with:

1)  Create a table that stores the pkey value and what table it's in.
2)  Use triggers on other tables to maintain that data
3)  Use fkeys against either the other tables themselves (where it is
a local reference) or against the common table

It's not really ideal.  It's possible the data will get out of sync of
something goes wrong and then you will have problems but it seems to
work for the most part and without a significant performance headache.

Best Wishes,
Chris Travers

-- 
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] Problem installing extensions on Lion

[Stefan Schwarzer]

> In file included from /usr/local/pgsql-9.1/include/server/postgres.h:47,
>  from tablefunc.c:33:
> /usr/local/pgsql-9.1/include/server/c.h:67:19: error: stdio.h: No such file 
> or directory
> /usr/local/pgsql-9.1/include/server/c.h:68:20: error: stdlib.h: No such file 
> or directory
> /usr/local/pgsql-9.1/include/server/c.h:69:20: error: string.h: No such file 
> or directory


Still trying to figure it out. I did a complete fresh install of Lion. Added 
Xcode too.

After looking around, it seems that the problem is arising from Xcode 4.3+ not 
being installed anymore under "/Developer/..." but " /Applications/Xcode.app".

I found a website [1] stating that the solution would be to use something like 
this:

   "sudo xcode-select -switch 
/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/"

But I have still the same problem… Can anyone give me a hint what I should do? 
Uninstall Xcode and install just a stand-alone compiler? 

Thanks for any help. I am really stuck!

Stef

[1] http://blog.bitemyapp.com/2012/03/20/fix-fs-events-stdio.h-not-found.html
-- 
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] Backslashitis

[Dean Rasheed]

On 14 June 2012 10:03, Raghavendra  wrote:
>
> On Thu, Jun 14, 2012 at 2:19 PM, Thomas Kellerer  wrote:
>>
>> haman...@t-online.de, 14.06.2012 10:17:
>>
>>> Hi,
>>>
>>> I have a column declared as array of text. I can get a single backslash
>>> into one of the array elements by
>>> update ... set mycol[1] = E'blah \\here'
>>> If I try to update the whole array
>>> update ... set mycol = E'{"blah \\here"}'
>>> the backslash is missing. I can get two backslashes there.
>>> Is there a good way to solve the problem, other than rewriting my update
>>> script to do array updates one element at a time?
>>>
>>
>> Setting
>>       standard_conforming_strings = true
>>
>> should do the trick.
>>
>>
>> http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS
>>
>> In that case you don't need any escaping inside the string literals.
>>
>> Regards
>> Thomas
>>
>
> Nope..
>
> postgres=# show standard_conforming_strings ;
>  standard_conforming_strings
> -
>  on
> (1 row)
> postgres=# set standard_conforming_strings =on;
> SET
> postgres=# show standard_conforming_strings ;
>  standard_conforming_strings
> -
>  on
> (1 row)
> postgres=# update array_test set name=E'{"meet\\ing"}';
> UPDATE 2
> postgres=# select * from array_test ;
>    name
> ---
>  {meeting}
>  {meeting}
> (2 rows)
>
> Correct me, if anything wrong.
>
> --Raghav
>

With standard conforming strings on, you could use any of the following:

update foo set a= E'{"blah here"}';
update foo set a= '{"blah \\here"}';
update foo set a= ARRAY[E'blah \\here'];
update foo set a= ARRAY['blah \here'];

I tend to prefer the ARRAY[...] constructor syntax because it doesn't
require any additional escaping of individual elements.

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE

http://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

Regards,
Dean

-- 
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 seperate users in PostgreSQL

[Egidijus]

Hello,

How it’s possible to make that seperate users can see and control only their 
own databases when they connected to PostgreSQL server? Standard PostgreSQL 
configuration allows users to see databases of other users. 

In my PostgreSQL server all users have their own logins.  I’d like that users 
can connect only to their own databases (see only them). How it’s possible to 
make that in PostgreSQL?

Thanks.

Egidijus

Re: [GENERAL] pg_upgrade: "pg_ctl failed to start the new server"

[Evan D. Hoffman]

Woops.  The file that had to be renamed was
/etc/ld.so.conf.d/postgresql-9.0-libs.conf =>
postgresql-9.old-libs.conf .  Prior to rename, ldconfig output was:

# ldconfig -p | grep pq
 libpqwalreceiver.so (libc6) => /usr/pgsql-9.0/lib/libpqwalreceiver.so
 libpqwalreceiver.so (libc6) => /usr/pgsql-9.1/lib/libpqwalreceiver.so
 libpq.so.5 (libc6) => /usr/pgsql-9.0/lib/libpq.so.5
 libpq.so.5 (libc6) => /usr/pgsql-9.1/lib/libpq.so.5
 libpq.so (libc6) => /usr/pgsql-9.0/lib/libpq.so
 libpq.so (libc6) => /usr/pgsql-9.1/lib/libpq.so

After the rename:

# cd /etc/ld.so.conf.d
# mv postgresql-9.0-libs.conf postgresql-9.old-libs.conf
# ldconfig
# ldconfig -p | grep pq
libpqwalreceiver.so (libc6) => /usr/pgsql-9.1/lib/libpqwalreceiver.so
libpqwalreceiver.so (libc6) => /usr/pgsql-9.0/lib/libpqwalreceiver.so
libpq.so.5 (libc6) => /usr/pgsql-9.1/lib/libpq.so.5
libpq.so.5 (libc6) => /usr/pgsql-9.0/lib/libpq.so.5
libpq.so (libc6) => /usr/pgsql-9.1/lib/libpq.so
libpq.so (libc6) => /usr/pgsql-9.0/lib/libpq.so

So it appears the problem was inability to connect, although
pg_upgrade reported that it couldn't start the server (I assume
ability to connect is how it determines whether or not the server was
started).

On Wed, Jun 13, 2012 at 10:49 PM, Bruce Momjian  wrote:
> On Wed, Jun 13, 2012 at 10:41:37PM -0400, Evan D. Hoffman wrote:
>> Actually I found the solution right after I sent that email (of
>> course):
>>
>> https://wiki-bsse.ethz.ch/download/attachments/55283107/PostgreSQL_9_M
>> aintenance_Backup_and_Recovery_final.docx
>>
>> Has to do with the order in which shared libs are loaded when both 9.0
>> and 9.1 are installed.  Renaming the config file as described in that
>> doc resolved it.
>
> I didn't find out which file you renamed.  Why would the server show as
> started while pg_ctl -w returned an error?  I am guessing that you could
> not connect somehow.
>
> --
>  Bruce Momjian          http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + It's impossible for everything to be true. +

-- 
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] installation problems on OSX Lion

[Pratik Chauhan]

Thank for your post...

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/installation-problems-on-OSX-Lion-tp4627419p5712557.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] pg_upgrade: "pg_ctl failed to start the new server"

[Evan D. Hoffman]

Actually I found the solution right after I sent that email (of course):

https://wiki-bsse.ethz.ch/download/attachments/55283107/PostgreSQL_9_Maintenance_Backup_and_Recovery_final.docx

Has to do with the order in which shared libs are loaded when both 9.0 and 9.1 
are installed.  Renaming the config file as described in that doc resolved it. 



On Jun 13, 2012, at 10:26 PM, Bruce Momjian  wrote:

> On Wed, Jun 13, 2012 at 11:19:41AM -0400, Evan D. Hoffman wrote:
>> I'm trying to upgrade Postgres 9.0 to 9.1 with pg_upgrade.  Both
>> versions are installed from the PGDG Yum repo:
>> 
>> -bash-4.1$ /usr/pgsql-9.0/bin/postgres -V
>> postgres (PostgreSQL) 9.0.8
>> -bash-4.1$ /usr/pgsql-9.1/bin/postgres -V
>> postgres (PostgreSQL) 9.1.4
>> 
>> I can successfully start and connect to both 9.0 and 9.1.  When I run
>> pg_upgrade, it fails with the error "pg_ctl failed to start the new
>> server," however it apparently does start the new server (output
>> below).  Any ideas?  This is a freshly installed CentOS 6.2 machine
>> with the 9.0 DB copied over from another machine via streaming
>> replication (which I have since ended and the server is now
>> standalone).
> 
> Have you looked at the logs from pg_upgrade?  Does "pg_ctl -w start"
> work?  My guess is that the -w (wait) is failing for some reason.  We
> did improve the -w handling in PG 9.1 so I didn't think there were any
> failure cases, but you might have found a new one.
> 
> -- 
>  Bruce Momjian  http://momjian.us
>  EnterpriseDB http://enterprisedb.com
> 
>  + It's impossible for everything to be true. +

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

[Raghavendra]

>
> With standard conforming strings on, you could use any of the following:
>
> update foo set a= E'{"blah here"}';
> update foo set a= '{"blah \\here"}';
> update foo set a= ARRAY[E'blah \\here'];
> update foo set a= ARRAY['blah \here'];
>
> I tend to prefer the ARRAY[...] constructor syntax because it doesn't
> require any additional escaping of individual elements.
>
>
> http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE
>
>
> http://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
>
> Regards,
> Dean
>

Thanks

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Re: [GENERAL] about seperate users in PostgreSQL

[Andreas Kretschmer]

Egidijus  wrote:

> Hello,
>  
> How it’s possible to make that seperate users can see and control only their
> own databases when they connected to PostgreSQL server? Standard PostgreSQL
> configuration allows users to see databases of other users.
>  
> In my PostgreSQL server all users have their own logins.  I’d like that users
> can connect only to their own databases (see only them). How it’s possible to
> make that in PostgreSQL?

Please read http://wiki.postgresql.org/wiki/Shared_Database_Hosting
(the author is a colleague )

AFAIK you can't avoid that users can see other databases.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

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

[GENERAL] Error message "psql: could not connect to server: No such file or directory"

[Stefan Schwarzer]

Hi there,

I guess this is a typical user error. I searched around to find the solution, 
but in vain.

I just upgraded to Lion, and used Kyngchaos libraries for installation of 
Postgres. 

Try to get running postgres, and I get this:

  /usr/local/pgsql-9.1/bin/psql -U postgres
  psql: could not connect to server: No such file or directory
 Is the server running locally and accepting
 connections on Unix domain socket "/tmp/.s.PGSQL.5432"?


In my limited understanding, it seems that Postgres is not running. When I use:

   ps auxw | grep post

out comes only the "grep post", but no postgres process.

When I use:

   sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist

it says:

   org.postgresql.postgres: Already loaded

and displays in the "ps" list then:

   user935   0.0  0.1  2493888   7296   ??  Ss3:24PM   0:00.12 
/System/Library/PrivateFrameworks/DiskImages.framework/Resources/diskimages-helper
 -uuid 9EFB9424-7971-4A8B-9D73-B93BD7F1DB5F -post-exec 4

(which looks a bit strange to me (DiskImages ??))


I am somewhat lost (and worse is that it worked the other day, before 
re-installing Lion anew.).

Thanks for any hints,

Stef
-- 
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] Problem installing extensions on Lion

[Adrian Klaver]

On 06/14/2012 04:47 AM, Stefan Schwarzer wrote:

In file included from /usr/local/pgsql-9.1/include/server/postgres.h:47,
  from tablefunc.c:33:
/usr/local/pgsql-9.1/include/server/c.h:67:19: error: stdio.h: No such file or 
directory
/usr/local/pgsql-9.1/include/server/c.h:68:20: error: stdlib.h: No such file or 
directory
/usr/local/pgsql-9.1/include/server/c.h:69:20: error: string.h: No such file or 
directory


1) Do the above files actually exist in the above location?

2)  If they do not exist there, do they exist anywhere on the machine?

3) How was Postgres installed?




Still trying to figure it out. I did a complete fresh install of Lion. Added 
Xcode too.

After looking around, it seems that the problem is arising from Xcode 4.3+ not being installed 
anymore under "/Developer/..." but " /Applications/Xcode.app".

I found a website [1] stating that the solution would be to use something like 
this:

"sudo xcode-select -switch 
/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/"

But I have still the same problem… Can anyone give me a hint what I should do? 
Uninstall Xcode and install just a stand-alone compiler?

Thanks for any help. I am really stuck!

Stef

[1] http://blog.bitemyapp.com/2012/03/20/fix-fs-events-stdio.h-not-found.html



--
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] Error message "psql: could not connect to server: No such file or directory"

[Adrian Klaver]

On 06/14/2012 06:30 AM, Stefan Schwarzer wrote:

Hi there,

I guess this is a typical user error. I searched around to find the solution, 
but in vain.

I just upgraded to Lion, and used Kyngchaos libraries for installation of 
Postgres.

Try to get running postgres, and I get this:

   /usr/local/pgsql-9.1/bin/psql -U postgres
   psql: could not connect to server: No such file or directory
  Is the server running locally and accepting
  connections on Unix domain socket "/tmp/.s.PGSQL.5432"?


In my limited understanding, it seems that Postgres is not running. When I use:

ps auxw | grep post

out comes only the "grep post", but no postgres process.

When I use:

sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist

it says:

org.postgresql.postgres: Already loaded

and displays in the "ps" list then:

user935   0.0  0.1  2493888   7296   ??  Ss3:24PM   0:00.12 
/System/Library/PrivateFrameworks/DiskImages.framework/Resources/diskimages-helper
 -uuid 9EFB9424-7971-4A8B-9D73-B93BD7F1DB5F -post-exec 4

(which looks a bit strange to me (DiskImages ??))


I am somewhat lost (and worse is that it worked the other day, before 
re-installing Lion anew.).


http://www.kyngchaos.com/software/postgres
"
Notes

NOTE: I've had reports of the installer not initializing the data 
cluster, which results in Postgres not starting. It seems to be random, 
and I haven't figured out the cause yet. If Postgres won't start (you 
get an error "could not connect to server: No such file or directory" 
when trying to connect with psql), try manually initializing the cluster 
with:

/usr/local/pgsql-9.1/bin/initdb -U postgres \
-D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US"
Or see the Locales and Encodings readme if you need a different locale 
or encoding.

Then stop and start Postgres as specified in the readme.
"


Thanks for any hints,

Stef



--
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] Problem installing extensions on Lion

[Stefan Schwarzer]

On Jun 14, 2012, at 3:32 PM, Adrian Klaver wrote:

> On 06/14/2012 04:47 AM, Stefan Schwarzer wrote:
>>> In file included from /usr/local/pgsql-9.1/include/server/postgres.h:47,
>>>  from tablefunc.c:33:
>>> /usr/local/pgsql-9.1/include/server/c.h:67:19: error: stdio.h: No such file 
>>> or directory
>>> /usr/local/pgsql-9.1/include/server/c.h:68:20: error: stdlib.h: No such 
>>> file or directory
>>> /usr/local/pgsql-9.1/include/server/c.h:69:20: error: string.h: No such 
>>> file or directory
> 
> 1) Do the above files actually exist in the above location?

Yes, the c.h exist in the mentioned location. However, as it does not indicate 
where it looks for the stdio.h, perhaps the problem lies there. I can see that 
file in several places in

/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/...

and in 

/usr/include/c++/4.2.1/tr1/stdio.h
/usr/include/stdio.h


> 2)  If they do not exist there, do they exist anywhere on the machine?
> 
> 3) How was Postgres installed?

Postgres was installed using the Kyngchaos libraries.
-- 
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] Error message "psql: could not connect to server: No such file or directory"

[Stefan Schwarzer]

>> Hi there,
>> 
>> I guess this is a typical user error. I searched around to find the 
>> solution, but in vain.
>> 
>> I just upgraded to Lion, and used Kyngchaos libraries for installation of 
>> Postgres.
>> 
>> Try to get running postgres, and I get this:
>> 
>>   /usr/local/pgsql-9.1/bin/psql -U postgres
>>   psql: could not connect to server: No such file or directory
>>  Is the server running locally and accepting
>>  connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
>> 
>> 
>> In my limited understanding, it seems that Postgres is not running. When I 
>> use:
>> 
>>ps auxw | grep post
>> 
>> out comes only the "grep post", but no postgres process.
>> 
>> When I use:
>> 
>>sudo launchctl load 
>> /Library/LaunchDaemons/org.postgresql.postgres.plist
>> 
>> it says:
>> 
>>org.postgresql.postgres: Already loaded
>> 
>> and displays in the "ps" list then:
>> 
>>user935   0.0  0.1  2493888   7296   ??  Ss3:24PM   
>> 0:00.12 
>> /System/Library/PrivateFrameworks/DiskImages.framework/Resources/diskimages-helper
>>  -uuid 9EFB9424-7971-4A8B-9D73-B93BD7F1DB5F -post-exec 4
>> 
>> (which looks a bit strange to me (DiskImages ??))
>> 
>> 
>> I am somewhat lost (and worse is that it worked the other day, before 
>> re-installing Lion anew.).
> 
> http://www.kyngchaos.com/software/postgres
> "
> Notes
> 
> NOTE: I've had reports of the installer not initializing the data cluster, 
> which results in Postgres not starting. It seems to be random, and I haven't 
> figured out the cause yet. If Postgres won't start (you get an error "could 
> not connect to server: No such file or directory" when trying to connect with 
> psql), try manually initializing the cluster with:
> /usr/local/pgsql-9.1/bin/initdb -U postgres \
> -D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US"
> Or see the Locales and Encodings readme if you need a different locale or 
> encoding.
> Then stop and start Postgres as specified in the read me.

Yeah, I had read that somewhere too, although in my disorientation over the 
last couple of hours didn't find it anymore. But I did try it out, but got a 

 "initdb: could not access directory "/usr/local/pgsql-9.1/data": 
Permission denied"

I guess this is solvable with some "su" access or so. But no idea how. The data 
directory looks like this:

  drwx--  18 _postgres  _postgres   612 Jun 14 14:25 data

So, the user is not anymore "postgres", but "_postgres", right? I read that 
this changed with Lion, but not sure how this applied to use with Kyngchaos 
libraries.

Thanks a lot for your help!




-- 
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] Problem installing extensions on Lion

[Alban Hertroys]

On 12 June 2012 14:21, Stefan Schwarzer  wrote:
> But the "make" process gives me this:
>
> cd contrib/tablefunc
> tablefunc $ make
> gcc -Os -arch x86_64 -isysroot /Developer/SDKs/MacOSX10.6.sdk

I think that's where it looks for the standard C headers?
In that case that's what you need to change. Or you can create a
symlink that points gcc to the new location.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
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] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[Merlin Moncure]

On Thu, Jun 14, 2012 at 1:10 AM, utsav  wrote:
> I am doing Oracle to PostgreSQL migration activity as part of Procedure
> Migration in Oracle there are *OUT parameters which return records(using
> bulk collect) of custom type.*
>
> *like function returing type1,type2. *
>
> What will be alternative for PostgreSQL to do this.
>
> *There are OUT parameters in PostgreSQL but i am not able to set returns set
> of type1,type2 .
> *
> Appreciate your Help.

postgres=# create type foo as (a int, b text);
CREATE TYPE
postgres=# create type bar as (c int, d text);
CREATE TYPE
postgres=# create function f(foo out foo, bar out bar) returns setof
record as $$
  select (v, v::text)::foo, (v, v::text)::bar from generate_series(1,3) v;
$$ language sql;
CREATE FUNCTION
postgres=# select f();
 f
---
 ("(1,1)","(1,1)")
 ("(2,2)","(2,2)")
 ("(3,3)","(3,3)")
(3 rows)

postgres=# select * from f();
  foo  |  bar
---+---
 (1,1) | (1,1)
 (2,2) | (2,2)
 (3,3) | (3,3)
(3 rows)

postgres=# select (foo).*, (bar).* from f();
 a | b | c | d
---+---+---+---
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
 3 | 3 | 3 | 3
(3 rows)

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] Error message "psql: could not connect to server: No such file or directory"

[Adrian Klaver]

On 06/14/2012 06:48 AM, Stefan Schwarzer wrote:
> 
>>> Hi there,
>>>
>>> I guess this is a typical user error. I searched around to find the 
>>> solution, but in vain.
>>>
>>> I just upgraded to Lion, and used Kyngchaos libraries for installation of 
>>> Postgres.
>>>
>>> Try to get running postgres, and I get this:
>>>
>>>/usr/local/pgsql-9.1/bin/psql -U postgres
>>>psql: could not connect to server: No such file or directory
>>>   Is the server running locally and accepting
>>>   connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
>>>
>>>
>>> In my limited understanding, it seems that Postgres is not running. When I 
>>> use:
>>>
>>> ps auxw | grep post
>>>
>>> out comes only the "grep post", but no postgres process.
>>>
>>> When I use:
>>>
>>> sudo launchctl load 
>>> /Library/LaunchDaemons/org.postgresql.postgres.plist
>>>
>>> it says:
>>>
>>> org.postgresql.postgres: Already loaded
>>>
>>> and displays in the "ps" list then:
>>>
>>> user935   0.0  0.1  2493888   7296   ??  Ss3:24PM   
>>> 0:00.12 
>>> /System/Library/PrivateFrameworks/DiskImages.framework/Resources/diskimages-helper
>>>  -uuid 9EFB9424-7971-4A8B-9D73-B93BD7F1DB5F -post-exec 4
>>>
>>> (which looks a bit strange to me (DiskImages ??))
>>>
>>>
>>> I am somewhat lost (and worse is that it worked the other day, before 
>>> re-installing Lion anew.).
>>
>> http://www.kyngchaos.com/software/postgres
>> "
>> Notes
>>
>> NOTE: I've had reports of the installer not initializing the data cluster, 
>> which results in Postgres not starting. It seems to be random, and I haven't 
>> figured out the cause yet. If Postgres won't start (you get an error "could 
>> not connect to server: No such file or directory" when trying to connect 
>> with psql), try manually initializing the cluster with:
>> /usr/local/pgsql-9.1/bin/initdb -U postgres \
>> -D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US"
>> Or see the Locales and Encodings readme if you need a different locale or 
>> encoding.
>> Then stop and start Postgres as specified in the read me.
> 
> Yeah, I had read that somewhere too, although in my disorientation over the 
> last couple of hours didn't find it anymore. But I did try it out, but got a
> 
>   "initdb: could not access directory "/usr/local/pgsql-9.1/data": 
> Permission denied"
> 
> I guess this is solvable with some "su" access or so. But no idea how. The 
> data directory looks like this:
> 
>drwx--  18 _postgres  _postgres   612 Jun 14 14:25 data
> 
> So, the user is not anymore "postgres", but "_postgres", right? I read that 
> this changed with Lion, but not sure how this applied to use with Kyngchaos 
> libraries.

I am not an Apple user so all I can do now is point you at the below. Answer 1 
would seem to be a solution:

http://stackoverflow.com/questions/6814127/lion-update-removed-the-postgres-user-how-to-restore-it

> 
> Thanks a lot for your 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

Re: [GENERAL] Problem installing extensions on Lion

[Tom Lane]

Stefan Schwarzer  writes:
> On Jun 14, 2012, at 3:32 PM, Adrian Klaver wrote:
>> 1) Do the above files actually exist in the above location?

> Yes, the c.h exist in the mentioned location. However, as it does not 
> indicate where it looks for the stdio.h, perhaps the problem lies there.

Indeed.  Where did you get the advice to use "-isysroot
/Developer/SDKs/MacOSX10.6.sdk"?  According to the gcc manual,
that means

 Use DIR as the logical root directory for headers and libraries.
 For example, if the compiler would normally search for headers in
 `/usr/include' and libraries in `/usr/lib', it will instead search
 `DIR/usr/include' and `DIR/usr/lib'.

So basically, that breaks every attempt to use a standard header from
/usr/include, unless the SDK has provided a substitute, which evidently
it mostly doesn't.

[ pokes around on own Mac... ]  Hmm, for me the file
/Developer/SDKs/MacOSX10.6.sdk/usr/include/stdio.h
does exist with reasonably sane-looking contents.  So maybe your problem
is a broken SDK installation.

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] Is there a way to ask PostgreSQL for the name of the computer it's running on?

[Steve Crawford]

On 06/13/2012 09:33 PM, Rob Richardson wrote:


My customer has 3 computers.  The PostgreSQL service could be running 
on either of two of them.  There is currently no way in our system to 
determine which one it is running on.  The third computer sometimes 
needs to know which of the other two computers is active.  It would be 
enough to know which computer is running the PostgreSQL service.  Is 
the name of the server available in a PostgreSQL database, so that it 
could be retrieved using a query?



I am unaware of any such query. However "show listen_addresses;" might 
allow you to achieve your goal.


Cheers,
Steve

[GENERAL] Submit query using dblink that hung the host

[Alex Lai]

My host was freeze up after submitted the following query that prevented 
me to ssh to the host.
I was unable to psql and submit pg_cancel_backend.  The tables have over 
20 millions rows.

Does dblink uses too much resource from the host when join large tables.
Hope someone can give me suggestion.

CREATE OR REPLACE VIEW missing_archiveset_in_mds_ops
 (filename, esdt, archiveset) AS
select * from dblink('host=ops_host port=4001 user=omiops dbname=omiops',
'select filename, esdt, archiveset from
 filemeta_archiveset join filemeta_common using(fileid)
 join file using(fileid)') as t1(filename text,esdt text,archiveset int)
where (filename, esdt, archiveset) not in (
select filename, esdt, archiveset
 from dblink('host=ops_host port=4002 user=omiops dbname=metamine',
'select filename, esdt, archiveset from
 file_archiveset join filemeta using(fileid)
 join filename using(fileid)') as t2(filename text,esdt text,archiveset 
int));



--
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
a...@sesda2.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] Submit query using dblink that hung the host

[Merlin Moncure]

On Thu, Jun 14, 2012 at 11:15 AM, Alex Lai  wrote:
> My host was freeze up after submitted the following query that prevented me
> to ssh to the host.
> I was unable to psql and submit pg_cancel_backend.  The tables have over 20
> millions rows.
> Does dblink uses too much resource from the host when join large tables.
> Hope someone can give me suggestion.
>
> CREATE OR REPLACE VIEW missing_archiveset_in_mds_ops
>  (filename, esdt, archiveset) AS
> select * from dblink('host=ops_host port=4001 user=omiops dbname=omiops',
> 'select filename, esdt, archiveset from
>  filemeta_archiveset join filemeta_common using(fileid)
>  join file using(fileid)') as t1(filename text,esdt text,archiveset int)
> where (filename, esdt, archiveset) not in (
> select filename, esdt, archiveset
>  from dblink('host=ops_host port=4002 user=omiops dbname=metamine',
> 'select filename, esdt, archiveset from
>  file_archiveset join filemeta using(fileid)
>  join filename using(fileid)') as t2(filename text,esdt text,archiveset
> int));

It would be interesting to know what exactly was the specific trigger
that brought down the server since dblink should not be allowed to do
that.  I'm assuming out of memory since libpq (used on the dblink
client side) is not memory bounded. 9.2 will include new row
processing features that should drastically reduce dblink memory
consumption and will probably prevent this from happening again.

In the meantime, restructure both dblinks to gather the data into
separate local tables (temporary if you can wing it), then create
indexes in advance of the join.

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] Empty arrays vs. NULLs, 9.1 & 8.3

[Ken Tanzer]

Hi.  I had this piece of SQL, which ran fine on my 9.1 installation:

INSERT INTO foo
SELECT ...,
CASE WHEN NOT allow_output_screen THEN array['O_SCREEN'] ELSE
array[]::varchar[] END
 || CASE WHEN NOT allow_output_spreadsheet THEN array['O_TEMPLATE']
ELSE array[]::varchar[] END,
...;

However, this failed miserably on someone else's 8.3:

ERROR:  syntax error at or near "]"
LINE 1: ...w_output_screen THEN array['O_SCREEN'] ELSE array[]::varchar

 ^
The 9.1 documentation
(http://www.postgresql.org/docs/9.1/static/sql-expressions.html)
states you can construct an empty array with my syntax (that's how I
got it originally), but there is no mention of empty arrays in the
corresponding 8.3 page.

In 8.3, I can SELECT NULL::varchar[], which seems to behave the same
in my query.  The two don't seem to be exactly the same.  I'm a little
confused, however, as to the finer points or conceptual differences
between them, and also what the differences might be between 8.3 and
9.1.

Sticking within 9.1, I ran this:

=>CREATE TEMPORARY TABLE array_fun( f1 varchar[] );
=>INSERT INTO array_fun VALUES (array[]::varchar[]),(NULL::varchar[]);

CREATE TABLE
INSERT 0 2

=> SELECT array_dims(f1) AS dims, f1 IS NULL as is_null, f1 ||
array['Item 2']::varchar[] AS concats,f1 FROM array_fun;

 dims | is_null |  concats   | f1
--+-++
  | f   | {"Item 2"} | {}
  | t   | {"Item 2"} |
(2 rows)

If anyone can shed some light on this, and also how to construct an
empty array in 8.3, it would be great.  Thanks!

Ken

p.s.,  On a side note, unless I've overlooked them before, the "this
page in other versions..." links in the doc pages seem to be new, and
are immensely helpful.  Especially because Google searches often
return results to the older versions.  Thanks a lot to whoever did
that!


--
AGENCY Software
A data system that puts you in control
http://agency-software.org/
ken.tan...@agency-software.org
(253) 245-3801

-- 
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] Empty arrays vs. NULLs, 9.1 & 8.3

[John R Pierce]

On 06/14/12 12:47 PM, Ken Tanzer wrote:

p.s.,  On a side note, unless I've overlooked them before, the "this
page in other versions..." links in the doc pages seem to be new, and
are immensely helpful.  Especially because Google searches often
return results to the older versions.


indeed, thats exactly why it was done.



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


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

[GENERAL] parsing SQLERRM ?

[david.sahagian]

(version == 9.1)

In my PL/pgSQL stored functions,
I want to be able to distinguish which FK-constraint caused the 
[foreign_key_violation] exception.
  . . .
  BEGIN
delete from MY_COOL_TABLE where id = 123 ;
  EXCEPTION
WHEN foreign_key_violation THEN
  CASE
WHEN (SQLERRM tells me it blew up because of FK X)  THEN . . . ;
WHEN (SQLERRM tells me it blew up because of FK Y)  THEN . . . ;
WHEN (SQLERRM tells me it blew up because of FK Z)  THEN . . . ;
  END;
WHEN others THEN
  raise;
  END;
  . . .

Is a "robust enough" parsing of SQLERRM actually the best/only way to determine 
this ?

-dvs-

Re: [GENERAL] Is there a way to ask PostgreSQL for the name of the computer it's running on?

[Guillaume Lelarge]

On Thu, 2012-06-14 at 04:33 +, Rob Richardson wrote:
> My customer has 3 computers.  The PostgreSQL service could be running on 
> either of two of them.  There is currently no way in our system to determine 
> which one it is running on.  The third computer sometimes needs to know which 
> of the other two computers is active.  It would be enough to know which 
> computer is running the PostgreSQL service.  Is the name of the server 
> available in a PostgreSQL database, so that it could be retrieved using a 
> query?
> 

You can have its IP address, but not its name, unless you write a C or
PL/perlU function.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.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] Is there a way to ask PostgreSQL for the name of the computer it's running on?

[Hellmuth Vargas]

Hi

SELECT inet_server_addr();

On Thu, Jun 14, 2012 at 4:43 PM, Guillaume Lelarge
wrote:

> On Thu, 2012-06-14 at 04:33 +, Rob Richardson wrote:
> > My customer has 3 computers.  The PostgreSQL service could be running on
> either of two of them.  There is currently no way in our system to
> determine which one it is running on.  The third computer sometimes needs
> to know which of the other two computers is active.  It would be enough to
> know which computer is running the PostgreSQL service.  Is the name of the
> server available in a PostgreSQL database, so that it could be retrieved
> using a query?
> >
>
> You can have its IP address, but not its name, unless you write a C or
> PL/perlU function.
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
PostgreSQL DBA

Re: [GENERAL] Empty arrays vs. NULLs, 9.1 & 8.3

[Steve Crawford]

On 06/14/2012 12:47 PM, Ken Tanzer wrote:

Hi.  I had this piece of SQL, which ran fine on my 9.1 installation:

INSERT INTO foo
SELECT ...,
CASE WHEN NOT allow_output_screen THEN array['O_SCREEN'] ELSE
array[]::varchar[] END
  || CASE WHEN NOT allow_output_spreadsheet THEN array['O_TEMPLATE']
ELSE array[]::varchar[] END,
...;

However, this failed miserably on someone else's 8.3:

ERROR:  syntax error at or near "]"
LINE 1: ...w_output_screen THEN array['O_SCREEN'] ELSE array[]::varchar

  ^
The 9.1 documentation
(http://www.postgresql.org/docs/9.1/static/sql-expressions.html)
states you can construct an empty array with my syntax (that's how I
got it originally), but there is no mention of empty arrays in the
corresponding 8.3 page.

In 8.3, I can SELECT NULL::varchar[], which seems to behave the same
in my query.  The two don't seem to be exactly the same.  I'm a little
confused, however, as to the finer points or conceptual differences
between them, and also what the differences might be between 8.3 and
9.1.

Sticking within 9.1, I ran this:

=>CREATE TEMPORARY TABLE array_fun( f1 varchar[] );
=>INSERT INTO array_fun VALUES (array[]::varchar[]),(NULL::varchar[]);

CREATE TABLE
INSERT 0 2

=>  SELECT array_dims(f1) AS dims, f1 IS NULL as is_null, f1 ||
array['Item 2']::varchar[] AS concats,f1 FROM array_fun;

  dims | is_null |  concats   | f1
--+-++
   | f   | {"Item 2"} | {}
   | t   | {"Item 2"} |
(2 rows)

If anyone can shed some light on this, and also how to construct an
empty array in 8.3, it would be great.  Thanks!



Array handling in general has undergone many changes from 8.3 to 9.1 and 
more if you go back from 8.3. Check the release notes for each major 
release for the specifics and logic. Some of the changes such as how to 
handle string_to_array('')  (my fault for bringing it up originally) 
required discussions that spanned a couple major versions.


To create an empty array in 8.3 you can just use '{}' and note that an 
empty array is *not* null while NULL::varchar[] *is*.


Cheers,
Steve


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

Re: [GENERAL] Empty arrays vs. NULLs, 9.1 & 8.3

[Ken Tanzer]

Thanks Steve.  FWIW I looked at the 9.0 and 9.1 release notes, and didn't
find much on arrays in them.

I do have one follow-up curiosity question, though.  Why does
array_dims(array[]::varchar[]) return NULL instead of 0?  I would expect
NULL for a NULL array, but not an empty one.  (And the same for
array_[upper,lower,length] functions as well.

There doesn't seem to be much coverage of NULLs in the array documentation,
so in the making-work-for-other-people department, I'd suggest that either
weaving it in or including a small separate section on the topic might be
helpful.

Cheers,
Ken

On Thu, Jun 14, 2012 at 3:15 PM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> On 06/14/2012 12:47 PM, Ken Tanzer wrote:
>
>> Hi.  I had this piece of SQL, which ran fine on my 9.1 installation:
>>
>> INSERT INTO foo
>> SELECT ...,
>> CASE WHEN NOT allow_output_screen THEN array['O_SCREEN'] ELSE
>> array[]::varchar[] END
>>  || CASE WHEN NOT allow_output_spreadsheet THEN array['O_TEMPLATE']
>> ELSE array[]::varchar[] END,
>> ...;
>>
>> However, this failed miserably on someone else's 8.3:
>>
>> ERROR:  syntax error at or near "]"
>> LINE 1: ...w_output_screen THEN array['O_SCREEN'] ELSE array[]::varchar
>>
>>  ^
>> The 9.1 documentation
>> (http://www.postgresql.org/**docs/9.1/static/sql-**expressions.html
>> )
>> states you can construct an empty array with my syntax (that's how I
>> got it originally), but there is no mention of empty arrays in the
>> corresponding 8.3 page.
>>
>> In 8.3, I can SELECT NULL::varchar[], which seems to behave the same
>> in my query.  The two don't seem to be exactly the same.  I'm a little
>> confused, however, as to the finer points or conceptual differences
>> between them, and also what the differences might be between 8.3 and
>> 9.1.
>>
>> Sticking within 9.1, I ran this:
>>
>> =>CREATE TEMPORARY TABLE array_fun( f1 varchar[] );
>> =>INSERT INTO array_fun VALUES (array[]::varchar[]),(NULL::**varchar[]);
>>
>> CREATE TABLE
>> INSERT 0 2
>>
>> =>  SELECT array_dims(f1) AS dims, f1 IS NULL as is_null, f1 ||
>> array['Item 2']::varchar[] AS concats,f1 FROM array_fun;
>>
>>  dims | is_null |  concats   | f1
>> --+-++**
>>   | f   | {"Item 2"} | {}
>>   | t   | {"Item 2"} |
>> (2 rows)
>>
>> If anyone can shed some light on this, and also how to construct an
>> empty array in 8.3, it would be great.  Thanks!
>>
>>
> Array handling in general has undergone many changes from 8.3 to 9.1 and
> more if you go back from 8.3. Check the release notes for each major
> release for the specifics and logic. Some of the changes such as how to
> handle string_to_array('')  (my fault for bringing it up originally)
> required discussions that spanned a couple major versions.
>
> To create an empty array in 8.3 you can just use '{}' and note that an
> empty array is *not* null while NULL::varchar[] *is*.
>
> Cheers,
> Steve
>
>


-- 
AGENCY Software
A data system that puts you in control
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801

Re: [GENERAL] Empty arrays vs. NULLs, 9.1 & 8.3

[Steve Crawford]

On 06/14/2012 03:54 PM, Ken Tanzer wrote:
Thanks Steve.  FWIW I looked at the 9.0 and 9.1 release notes, and 
didn't find much on arrays in them.
The notes are terse (a 1-2 line comment encapsulates the results of 
hundreds of messages covering a couple year) but there are many 
references to updates in how arrays are handled.


I do have one follow-up curiosity question, though.  Why does 
array_dims(array[]::varchar[]) return NULL instead of 0?  I would 
expect NULL for a NULL array, but not an empty one.  (And the same for 
array_[upper,lower,length] functions as well.
I can't answer, specifically. But it makes a certain amount of sense. An 
empty array has no elements so where does it "begin" or "end" - 
especially since PostgreSQL array indexes don't need to start at 1 and 
the array could be multi-dimensional. Unknown, aka null, seems the best 
response.


Cheers,
Steve


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

[GENERAL] pg_ctl start close the cmd.exe then the server would stop.

[xytianer]

pg_ctl start   close the cmd.exe then the server would stop.

i  use the command pg_ctl start my dbserver in cmd.   then i could not
close the cmd.exe , if i do, the server will stop.

can someone give me some advice?

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-ctl-start-close-the-cmd-exe-then-the-server-would-stop-tp5712685.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] about seperate users in PostgreSQL

[Craig Ringer]

On 06/14/2012 08:31 PM, Egidijus wrote:

Hello,
How it’s possible to make that seperate users can see and control only 
their own databases when they connected to PostgreSQL server? Standard 
PostgreSQL configuration allows users to see databases of other users.
In my PostgreSQL server all users have their own logins.  I’d like 
that users can connect only to their own databases (see only them). 
How it’s possible to make that in PostgreSQL?


At the moment, the only way to to that is to run a cluster (ie: a 
postmaster) per-user. This is very inefficient.


A Google search and/or search of these archives for "postgresql 
multi-tenant" will provide some more info on different approaches to 
multi-tenant clusters. Right now none of the approaches really offer 
ideal isolation, Pg just isn't made for that and nobody's stepped up 
with the code, time, or funding to build full isolated multi-tenancy.


In addition to visibility of databases, users and some other objects in 
global tables, you'll find that you can't use built-in replication to 
replicate just one database or one user's databases, and you can't 
separate WAL activity, bgwriter activity, etc for different users so one 
user's load can adversely affect other users.


While most people's use of Pg doesn't suffer for these limitations, I 
suspect you'll need to know about them if you're trying to offer 
multi-tenant hosting.


--
Craig Ringer

Re: [GENERAL] pg_ctl start close the cmd.exe then the server would stop.

[gelin yan]

On Fri, Jun 15, 2012 at 8:24 AM, xytianer  wrote:

> pg_ctl start   close the cmd.exe then the server would stop.
>
> i  use the command pg_ctl start my dbserver in cmd.   then i could not
> close the cmd.exe , if i do, the server will stop.
>
> can someone give me some advice?
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/pg-ctl-start-close-the-cmd-exe-then-the-server-would-stop-tp5712685.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
>


Hi

   Why do you want to close the cmd.exe? pg_ctl is supposed not to work as
a daemon.

[GENERAL] Get RULE condition and commands

[Vlad Arkhipov]

What is the proper way of getting RULE condition and commands? The query 
below does not work.


select pg_get_expr(ev_qual, ev_class, true) as condition,
   pg_get_expr(ev_action, ev_class, true) as commands
from pg_rewrite;

ERROR:  bogus varno: 2

** Error **

ERROR: bogus varno: 2
SQL state: XX000