Re: [GENERAL] Problem with locales on Linux with 9.3.4

[Adrian Klaver]

On 06/06/2014 04:36 PM, hubert depesz lubaczewski wrote:

locale-gen just regenerates the locale - which I have. It's just that
PostgreSQL doesn't see it. When I run locale-gen, it just shows that all
locales (listed by locale -a) are "up to date".



On further reading another way would be to use the localedef command 
with the --no-archive option:


http://manpages.ubuntu.com/manpages/precise/man1/localedef.1.html


depesz




--
Adrian Klaver
adrian.kla...@aklaver.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 with locales on Linux with 9.3.4

[Adrian Klaver]

On 06/06/2014 04:36 PM, hubert depesz lubaczewski wrote:

locale-gen just regenerates the locale - which I have. It's just that
PostgreSQL doesn't see it. When I run locale-gen, it just shows that all
locales (listed by locale -a) are "up to date".


Did you do 'dpkg-reconfigure locales' after the locale-gen?



depesz



--
Adrian Klaver
adrian.kla...@aklaver.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 with locales on Linux with 9.3.4

[hubert depesz lubaczewski]

locale-gen just regenerates the locale - which I have. It's just that
PostgreSQL doesn't see it. When I run locale-gen, it just shows that all
locales (listed by locale -a) are "up to date".

depesz


On Fri, Jun 6, 2014 at 10:04 PM, Adrian Klaver 
wrote:

> On 06/06/2014 09:05 AM, hubert depesz lubaczewski wrote:
>
>> Hi,
>> I'm running Pg 9.3.4 on Ubuntu Linux 12.04/Precise. Pg is installed from
>> PGDG repo (http://apt.postgresql.org/pub/repos/apt/).
>>
>> It somehow got database created in locale that it can't now open:
>>
>> $ psql
>> psql: FATAL:  database locale is incompatible with operating system
>> DETAIL:  The database was initialized with LC_COLLATE "en_GB.UTF-8",
>> which is not recognized by setlocale().
>> HINT:  Recreate the database with another locale or install the missing
>> locale.
>>
>>
>
>> The thing is that system knows about en_GB:
>>
>>
>
>> Is it possible? Anyone encountered something like this before? If so -
>> what can be done? I didn't found any way to "unpack" the archive with
>> locales.
>>
>
> Seems this is a new Ubuntu thing:
>
> http://manpages.ubuntu.com/manpages/precise/man8/locale-gen.8.html
>
> and an example:
>
> http://backdrift.org/fixing-no-such-file-or-directory-locale-errors
>
>
>> depesz
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] bytea Issue - Reg

[Adrian Klaver]

On 06/04/2014 10:49 PM, sramay wrote:

Sir,

The base table is having bytea and having records around 32 lakhs shows size
of 300 mb.
bytea field has attached documents size is not shown in the base table.

The message on  Tomcat is

--begin text --

4 Jun, 2014 3:29:07 PM org.apache.catalina.core.StandardWrapperValve invoke
INFO: WARN [TP-Processor24] JDBCExceptionReporter.logExceptions(100) | SQL
Error: 0, SQLState: 42P01
ERROR [TP-Processor24] JDBCExceptionReporter.logExceptions(101) | ERROR:
relation "public.file_attachments" does not exist
   Position: 238
org.hibernate.exception.SQLGrammarException: could not execute query
 at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
 at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
 at org.hibernate.loader.Loader.doList(Loader.java:2231)
 at
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
 at org.hibernate.loader.Loader.list(Loader.java:2120)
 at
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
 at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
 at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
 at
org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:328)
 at
com.nic.edistrict.certificates.dao.impl.FileAttachmentsDaoImpl.getFileAttachments(FileAttachmentsDaoImpl.java:53)
 at
com.nic.edistrict.certificates.service.impl.FileAttachmentsManagerImpl.getFileAttachments(FileAttachmentsManagerImpl.java:28)
 at sun.reflect.GeneratedMethodAccessor1999.invoke(Unknown Source)
 at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:28

--end text



To add to Alan's comment about checking which database you are 
connecting to, I have one:


In seems there are a couple of Java programs at work here, Hibernate and 
the Spring Framework. I see something dealing with reflection and that 
raises a flag.


Are you sure that the framework/Hibernate are doing the right thing?



Even though table is present and connections are opening for subsequently.
The message
appear at frequent intervals.   Is there any way to see the complete size of
a table
which has bytea filed like database
pg_size_pretty(pg_relation_size('file_attachments') shows
just 300 mb.


http://www.postgresql.org/docs/9.1/interactive/functions-admin.html

pg_table_size accepts the OID or name of a table and returns the disk 
space needed for that table, exclusive of indexes. (TOAST space, free 
space map, and visibility map are included.)




Any help is welcome.

Regards

Ramachandran s





--
Adrian Klaver
adrian.kla...@aklaver.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 with locales on Linux with 9.3.4

[Adrian Klaver]

On 06/06/2014 09:05 AM, hubert depesz lubaczewski wrote:

Hi,
I'm running Pg 9.3.4 on Ubuntu Linux 12.04/Precise. Pg is installed from
PGDG repo (http://apt.postgresql.org/pub/repos/apt/).

It somehow got database created in locale that it can't now open:

$ psql
psql: FATAL:  database locale is incompatible with operating system
DETAIL:  The database was initialized with LC_COLLATE "en_GB.UTF-8",
which is not recognized by setlocale().
HINT:  Recreate the database with another locale or install the missing
locale.





The thing is that system knows about en_GB:





Is it possible? Anyone encountered something like this before? If so -
what can be done? I didn't found any way to "unpack" the archive with
locales.


Seems this is a new Ubuntu thing:

http://manpages.ubuntu.com/manpages/precise/man8/locale-gen.8.html

and an example:

http://backdrift.org/fixing-no-such-file-or-directory-locale-errors



depesz





--
Adrian Klaver
adrian.kla...@aklaver.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] bytea Issue - Reg

[Alan Hodgson]

On Wednesday, June 04, 2014 10:49:18 PM sramay wrote:
> relation "public.file_attachments" does not exist

.. is almost certainly not a size problem. What does your PostgreSQL log say? 
I suspect your app is connecting to the wrong database.


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

[GENERAL] Trigger function permissions

[Keith Fiske]

Just want to make sure I'm understanding the permissions needed for trigger
functions as well as making sure this is what the developers intended
before I go assuming things will always work this way. Also as a sanity
check for myself that I'm not missing something obvious.

I have an extension (https://github.com/omniti-labs/mimeo) that does
logical replication. The setup functions for the trigger-based DML
replication automatically create the queue table, trigger function &
trigger on the source database. I'm working on fixing a bug where the
correct permissions weren't being given and in my testing for a fix found
that just simply giving the trigger function SECURITY DEFINER fixed all the
problems and I don't have to even bother looking up which roles currently
have write permissions on the source table to set them on the queue table
and trigger function.

I understand how SECURITY DEFINER solves the issue of the function writing
to the queue table (function and queue table owner are the same). But I
would've thought that any roles with write privileges to the table would've
needed to be given EXECUTE permissions on the trigger function. I thought
maybe the trigger function was being called as the owner of the table, but
apparently even the owner of the table doesn't need these execute
permissions. Reading through the docs on triggers, I didn't see anything
mentioned about how this is expected to work. Examples are in the gist link
below. You can see the owner has no explicit permissions to the trigger
function and inserts still work even after revoking PUBLIC.

https://gist.github.com/keithf4/83c5c6516e2726609675
--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

Re: [GENERAL] bytea Issue - Reg

[sramay]

Sir,

The base table is having bytea and having records around 32 lakhs shows size
of 300 mb.
bytea field has attached documents size is not shown in the base table.

The message on  Tomcat is

--begin text --

4 Jun, 2014 3:29:07 PM org.apache.catalina.core.StandardWrapperValve invoke
INFO: WARN [TP-Processor24] JDBCExceptionReporter.logExceptions(100) | SQL
Error: 0, SQLState: 42P01
ERROR [TP-Processor24] JDBCExceptionReporter.logExceptions(101) | ERROR:
relation "public.file_attachments" does not exist
  Position: 238
org.hibernate.exception.SQLGrammarException: could not execute query
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
at
org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:328)
at
com.nic.edistrict.certificates.dao.impl.FileAttachmentsDaoImpl.getFileAttachments(FileAttachmentsDaoImpl.java:53)
at
com.nic.edistrict.certificates.service.impl.FileAttachmentsManagerImpl.getFileAttachments(FileAttachmentsManagerImpl.java:28)
at sun.reflect.GeneratedMethodAccessor1999.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:28

--end text

Even though table is present and connections are opening for subsequently. 
The message
appear at frequent intervals.   Is there any way to see the complete size of
a table
which has bytea filed like database
pg_size_pretty(pg_relation_size('file_attachments') shows
just 300 mb.

Any help is welcome.

Regards

Ramachandran s



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


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

Re: [GENERAL] How to select rows for which column has empty array ?

[Bosco Rama]

On 06/06/14 07:30, Arup Rakshit wrote:
> 
> How would I select rows which has empty array for the field "team_ids" ?

The shortest way is to test against an empty array constant:
  select id from reporting_groups where team_ids = '{}';

Or you can test using array_length().  Or you test against an empty
array[], cast appropriately.

HTH,
Bosco.


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

[GENERAL] Problem with locales on Linux with 9.3.4

[hubert depesz lubaczewski]

Hi,
I'm running Pg 9.3.4 on Ubuntu Linux 12.04/Precise. Pg is installed from
PGDG repo (http://apt.postgresql.org/pub/repos/apt/).

It somehow got database created in locale that it can't now open:

$ psql
psql: FATAL:  database locale is incompatible with operating system
DETAIL:  The database was initialized with LC_COLLATE "en_GB.UTF-8",  which
is not recognized by setlocale().
HINT:  Recreate the database with another locale or install the missing
locale.

When I connect to another database, I can see that:


List of databases
  Name   | Owner  | Encoding |   Collate   |Ctype
|   Access privileges
-++--+-+-+---
 xxx | xx | UTF8 | pl_PL.UTF-8 | pl_PL.UTF-8 |
 postgres| postgres   | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 template0   | postgres   | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
=c/postgres  +
 ||  | | |
postgres=CTc/postgres
 template1   | postgres   | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
=c/postgres  +
 ||  | | |
postgres=CTc/postgres
(4 rows)


Locale settings in Pg:

# select name, setting from pg_settings where name  ~ '^lc_';
name |   setting
-+-
 lc_collate  | pl_PL.UTF-8
 lc_ctype| pl_PL.UTF-8
 lc_messages | en_US.UTF-8
 lc_monetary | en_US.UTF-8
 lc_numeric  | en_US.UTF-8
 lc_time | en_US.UTF-8
(6 rows)

The thing is that system knows about en_GB:

$ locale -a
C
cs_CZ.utf8
C.UTF-8
de_DE.utf8
en_GB
en_GB.iso88591
en_GB.iso885915
en_GB.utf8
en_US.utf8
pl_PL.utf8
POSIX
sk_SK.utf8

$ LC_ALL=en_GB.UTF-8 locale
LANG=en_GB.UTF-8
LANGUAGE=
LC_CTYPE="en_GB.UTF-8"
LC_NUMERIC="en_GB.UTF-8"
LC_TIME="en_GB.UTF-8"
LC_COLLATE="en_GB.UTF-8"
LC_MONETARY="en_GB.UTF-8"
LC_MESSAGES="en_GB.UTF-8"
LC_PAPER="en_GB.UTF-8"
LC_NAME="en_GB.UTF-8"
LC_ADDRESS="en_GB.UTF-8"
LC_TELEPHONE="en_GB.UTF-8"
LC_MEASUREMENT="en_GB.UTF-8"
LC_IDENTIFICATION="en_GB.UTF-8"
LC_ALL=en_GB.UTF-8

I straced Pg, and it showed:

17:18:42.386260 open("/usr/lib/locale/en_GB.UTF-8/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.14>
17:18:42.386299 open("/usr/lib/locale/en_GB.utf8/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.14>
17:18:42.386337 open("/usr/lib/locale/en_GB/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.14>
17:18:42.386374 open("/usr/lib/locale/en.UTF-8/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.16>
17:18:42.386418 open("/usr/lib/locale/en.utf8/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.14>
17:18:42.386455 open("/usr/lib/locale/en/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.15>
17:18:42.386492
open("/usr/share/locale-langpack/en_GB.UTF-8/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.15>
17:18:42.386530
open("/usr/share/locale-langpack/en_GB.utf8/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.13>
17:18:42.386562 open("/usr/share/locale-langpack/en_GB/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.11>
17:18:42.386591 open("/usr/share/locale-langpack/en.UTF-8/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.08>
17:18:42.386618 open("/usr/share/locale-langpack/en.utf8/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.09>
17:18:42.386645 open("/usr/share/locale-langpack/en/LC_COLLATE",
O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory) <0.08>
17:18:42.386723 write(2, "2014-06-06 15:18:42 GMT FATAL:  database
locale is incompatible with operating system\n2014-06-06 15:18:42 GMT
DETAIL:  The database was initialized with LC_COLLATE \"en_GB.UTF-8\",
 which is not recognized by setlocale().\n2014-06-06 15:18:42 GMT
HINT:  Recre"..., 324) = 324 <0.23>

In /usr/lib/locale, I have only:
1. Directory C.UTF-8
2. File: locale-archive

It looks like if system locale was "packed" into this archive, but Pg for
some reason cannot open it, and required locale files to be separately in
subdirectories.

Is it possible? Anyone encountered something like this before? If so - what
can be done? I didn't found any way to "unpack" the archive with locales.

depesz

Re: [GENERAL] How to select rows for which column has empty array ?

[David G Johnston]

Arup Rakshit wrote
> I have a below table :
> 
> yelloday_development=# select id,workplace_ids,team_ids  from
> reporting_groups ;                                                        
>                                                                          
>                                                   
>  id | workplace_ids | team_ids 
> +---+--
>   2 | {}            | {}
>   3 | {}            | {}
>   1 | {}            | {}
>   4 | {1}           | {1}
>   5 | {2}           | {2,3,4}
> (5 rows)
> 
> yelloday_development=# select id from reporting_groups where 2 = ANY
> (team_ids)
> yelloday_development-# ;
>  id 
> 
>   5
> (1 row)
> 
> How would I select rows which has empty array for the field "team_ids" ?

The generic way would be to checks its length:

WHERE array_length(team_ids, 1) = 0

you could also check for equality with the empty array:

WHERE teams_ids = array[]::integer[]

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-select-rows-for-which-column-has-empty-array-tp5806338p5806343.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] interpret bytea output as text / double encode()

[Stefan Froehlich]

On Fri, Jun 06, 2014 at 10:30:56AM -0400, Tom Lane wrote:
> It's possible that what you are looking for is a binary-equivalent
> cast from text to bytea, which you could create like this:

> # create cast (text as bytea) without function;

Hm. No, actually it does not help. But playing around with the cast
revealed to me another thing. If I double-encode "12345", I have:

| # select encode('\x5c783331333233343335', 'escape');
| encode 
| ---
|  \\x3132333435
|  (1 row)

Now the intermediate result contains a '\\' which I just ignored as
it is the usual quoting for a '\'. *But* of course this prevents the
string from being correctly interpreted as a bytea-sequence, so the
solution for the problem is:

| # select encode(right(encode('\x5c783331333233343335', 'escape'), 
-1)::bytea, 'escape');
|  encode 
|  
|   12345
|   (1 row)

Kind of a hack, but this works on tables as well and will save me a
lot of trouble. Thank you for the inspiration!

Stefan 


-- 
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] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb

[Tom Lane]

Bhushan Pathak  writes:
>>> Stopping postgresql service:  [  OK  ]
>>> Starting postgresql service:  [FAILED]
>>> 
>>> pgstartup log has the same line  -
>>> Segmentation fault (core dumped)
>>> 
>>> Where is this core dump file generated? How do we proceed further from
>>> here?

FWIW, I'd expect any such core to be generated either in postgres'
home directory or the $PGDATA directory, depending on what is
failing when.  If you don't see a core, it's likely because the failing
program is running under "ulimit -c 0", which is the default environment
for daemons (for security reasons).  Try adding "ulimit -c unlimited"
to the start script and/or the postgres user's ~/.bash_profile to see
if you can get a core file for debugging.

The issue seems like it must trace back to some difference in the
normal shell environment of the postgres user versus the environment
set up by "service" ... but it's not clear yet what that difference
is.

Also, it's not very clear whether you're trying to use the Red Hat/CentOS
packaging of PG, or the PGDG packaging.  As Adrian alluded to, those are
not terribly compatible --- if you've got fragments of both laying about,
that could be causing issues.

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] interpret bytea output as text / double encode()

[Tom Lane]

Stefan Froehlich  writes:
> ... Casting the result does not work either:

> # select encode(encode(column, 'escape')::bytea, 'escape')

> because the cast reverts the effect of the first encode(), so the result
> does not change.

Since there's no explicitly defined cast from text to bytea according
to pg_cast, what happens when you do this is that the textual result
of the inner encode() is fed to bytea's input function, which of course
is supposed to decode escaped data.

> If I take the result of the first encode() with copy/paste and put
> it through another encode() statement, everything is fine - but I
> have not found out a way how to automate this.

I'm a bit confused by this statement, because it sounds like what
you did manually is precisely to feed the text string to bytea's
input function.  So I don't see quite what the difference is.

It's possible that what you are looking for is a binary-equivalent
cast from text to bytea, which you could create like this:

# create cast (text as bytea) without function;

However when I experiment with

# select encode(encode(column, 'escape')::bytea, 'escape')

after doing that, it doesn't seem like the results are very useful,
so I think I'm misunderstanding.

regards, tom lane


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

[GENERAL] How to select rows for which column has empty array ?

[Arup Rakshit]

I have a below table :

yelloday_development=# select id,workplace_ids,team_ids  from reporting_groups 
;                                                                               
                                                                                
                      
 id | workplace_ids | team_ids 
+---+--
  2 | {}            | {}
  3 | {}            | {}
  1 | {}            | {}
  4 | {1}           | {1}
  5 | {2}           | {2,3,4}
(5 rows)

yelloday_development=# select id from reporting_groups where 2 = ANY (team_ids)
yelloday_development-# ;
 id 

  5
(1 row)

How would I select rows which has empty array for the field "team_ids" ?
 
Regards,
Arup Rakshit

Re: [GENERAL] Postgresql service stopped automatically after restart

[Adrian Klaver]

On 06/04/2014 10:31 PM, Kalai R wrote:

Thank You for your suggestions.

In silent installation we use the following code in vb.net
 to install postgres

xinststr =" --mode unattended  --superaccount 
  --superpassword--serviceaccount 
--servicepassword--serverport 
  --unattendedmodeui none "

*For 64 bit windows*
*
*
Shell(Application.StartupPath & "\postgresql-9.3.1-1-windows " &
xInstStr, AppWinStyle.MaximizedFocus)

*For 32 bit wondows*

Shell(Application.StartupPath & "\postgresql-9.3.0-1-windows-x64 " &
xInstStr, AppWinStyle.MaximizedFocus)


We change default super user name, service account name and port.
Windows service account created automatically. We set only folder
permissions to the windows service account.



I do not actually spend much time working with Windows so someone else 
will need to eyeball this for correctness.


Have you had a chance to look at the other questions/observations from 
the previous post?



--
Adrian Klaver
adrian.kla...@aklaver.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] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb

[Adrian Klaver]

On 06/06/2014 12:29 AM, Bhushan Pathak wrote:

My locale is set to en_US.UTF-8, that's what the service script is using.

  If I try to start the server with pg_ctl command, it does start -
./pg_ctl start -D /var/lib/pgsql/data -l /tmp/logfile
server starting

What would this mean? The service script does not work but manually
issuing pg_ctl commands work.


Meant to add to my previous post:

Does anything show up in the system log when the service script fails, 
beside the segfault?




Thanks
Bhushan Pathak




--
Adrian Klaver
adrian.kla...@aklaver.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] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb

[Adrian Klaver]

On 06/06/2014 12:29 AM, Bhushan Pathak wrote:

My locale is set to en_US.UTF-8, that's what the service script is using.

  If I try to start the server with pg_ctl command, it does start -
./pg_ctl start -D /var/lib/pgsql/data -l /tmp/logfile
server starting

What would this mean? The service script does not work but manually
issuing pg_ctl commands work.


The service script is not correct?

Could you post the contents of the script?

Do you have another instance of Postgres installed by any chance?

Have you had another instance/version installed in the past, something 
that might have left parts of itself around?




Thanks
Bhushan Pathak





--
Adrian Klaver
adrian.kla...@aklaver.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] Trigger to convert UNIX time to timestamp without time zone.

[Andrew Sullivan]

On Fri, Jun 06, 2014 at 02:19:50PM +0100, Alberto Olivares wrote:
> How can I create a trigger to transform the time from UNIX to timestamp
> without time zone every time a new record is inserted into my database?

This is in the manual, section 9.8: to_timestamp(double precision)

It's always a little confusing when you go to look it up, because it's
not with the date and time functions, because it's actually a
formatting issue.  (There's a cross reference, but if you don't know
this is just a formatting issue you won't know to follow the
reference.)  You probably don't need a trigger, just put that in your
query.

Are you sure you want this without time zone?  In my experience,
almost every time people think they want "without time zone" they
actually don't. 

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] interpret bytea output as text / double encode()

[Stefan Froehlich]

> > # select encode(encode(column, 'escape'), 'escape')
 
> Any  chance you can encode() into temp table then encode() the result. 

Don't think so, PostgreSQL will recognize the temp column either as text
or as bytea and then refuse either the insert or the encode() because of
a type mismatch.

Any workaround for this would most likely solve my original problem
without the use of a temp table.

Stefan


-- 
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] Trigger to convert UNIX time to timestamp without time zone.

[Adrian Klaver]

On 06/06/2014 06:19 AM, Alberto Olivares wrote:

Hello,

I want to insert data into a column "timestamp without time zone" data
type. The problem is I am receiving the data in UNIX time.

How can I create a trigger to transform the time from UNIX to timestamp
without time zone every time a new record is inserted into my database?


to_timesstamp will take the epoch, which I assume is what you are 
talking about, and turn it into a timestamp with time zone. Don't worry 
about the timezone.


test=> select to_timestamp(extract(epoch from now()));
 to_timestamp
---
 2014-06-06 06:27:20.484509-07
(1 row)

test=> \d timestamp_test 



  Table "public.timestamp_test" 



 Column |Type | Modifiers 



+-+--- 



 id | integer | 



 ts | timestamp without time zone | 



 ts_z   | timestamp with time zone|


Inserting a timestamp with time zone into a field that is timestamp 
without timezone will strip the timezone automatically.


test=> insert into timestamp_test values (1, to_timestamp(extract(epoch 
from now())), to_timestamp(extract(epoch from now(;

INSERT 0 1

test=> select * from timestamp_test ;
 id | ts | ts_z
++---
  1 | 2014-06-06 06:30:58.080158 | 2014-06-06 06:30:58.080158-07
(1 row)

So you just need to wrap the above in a function and call it from your 
trigger.





Thank you in advance.

Regards,
Alberto.


*Alberto Olivares Colas
*Technical Consultant
Snowflake Software




--
Adrian Klaver
adrian.kla...@aklaver.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] interpret bytea output as text / double encode()

[Rob Sargent]

Sent from my iPhone

> On Jun 6, 2014, at 7:00 AM, Stefan Froehlich  
> wrote:
> 
> In one of my databases, I have mistakenly double encoded bytea
> values (i.e. the content is literally '\x202020...' which would have
> to be decoded once more to get the actually desired content).
> 
> But how to get to the content? This:
> 
> # select encode(column, 'escape')
> 
> gives me the once-only encoded value, which I'd like to decode once more
> to get the original content. Using this:
> 
> # select encode(encode(column, 'escape'), 'escape')
> 
> does not work, because PostgreSQL is aware of the fact, that the result
> of the inner operation is text and thus may not be used as an argument
> for encod(). Casting the result does not work either:
> 
> # select encode(encode(column, 'escape')::bytea, 'escape')
> 
> because the cast reverts the effect of the first encode(), so the result
> does not change.
> 
> If I take the result of the first encode() with copy/paste and put
> it through another encode() statement, everything is fine - but I
> have not found out a way how to automate this.
> 
> Is there a chance? Can anyone help out?
> 
> Stefan
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Any  chance you can encode() into temp table then encode() the result. 

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

[GENERAL] Trigger to convert UNIX time to timestamp without time zone.

[Alberto Olivares]

Hello,

I want to insert data into a column "timestamp without time zone" data
type. The problem is I am receiving the data in UNIX time.

How can I create a trigger to transform the time from UNIX to timestamp
without time zone every time a new record is inserted into my database?

Thank you in advance.

Regards,
Alberto.



*Alberto Olivares Colas*Technical Consultant
Snowflake Software

Tel.: +44 (0)2380 386578

Email: alberto.oliva...@snowflakesoftware.com

Website: www.snowflakesoftware.com
Twitter: @sflakesoftware 
Follow us on LinkedIn 

Registered in England & Wales. Registered Number: 4294244
-


*Geospatial Technology Company of the Year*
*Read more *

-- 
*Geospatial Technology Company of the Year*
*Read more *

[GENERAL] interpret bytea output as text / double encode()

[Stefan Froehlich]

In one of my databases, I have mistakenly double encoded bytea
values (i.e. the content is literally '\x202020...' which would have
to be decoded once more to get the actually desired content).

But how to get to the content? This:

# select encode(column, 'escape')

gives me the once-only encoded value, which I'd like to decode once more
to get the original content. Using this:

# select encode(encode(column, 'escape'), 'escape')

does not work, because PostgreSQL is aware of the fact, that the result
of the inner operation is text and thus may not be used as an argument
for encod(). Casting the result does not work either:

# select encode(encode(column, 'escape')::bytea, 'escape')

because the cast reverts the effect of the first encode(), so the result
does not change.

If I take the result of the first encode() with copy/paste and put
it through another encode() statement, everything is fine - but I
have not found out a way how to automate this.

Is there a chance? Can anyone help out?

Stefan


-- 
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] Optimizer issue -- bad query plan?

[Moshe Jacobson]

On Thu, Jun 5, 2014 at 7:19 PM, Tom Lane  wrote:

 We need to see the actual view definition and calling query,
> not a simplified "equivalent" query.
>
The simple query has the same performance issues as the view. What help
would it be to see the view?
I can provide any other information you want, just let me know.

In case you still want it, here is the view definition, though I think it's
irrelevant.

 SELECT r.reset,
l.number,
pj.name AS project_name,
r.location,
r.reset_team,
r.project,
r.program,
r.wbse,
r.in_scope,
r.bay_count,
r.labor_duration,
r.execution_date,
r.creator,
r.prewalk_due_date,
r.prewalk_duration,
r.planogram_url,
r.signoff_received,
r.reset_status,
v.name AS vendor_name,
rst.label AS reset_status_label,
pg.name AS program_name,
pg.fiscal_year,
rsv.submitted,
CASE
WHEN rsv.reset_survey IS NOT NULL THEN r.prewalk_due_date
ELSE NULL::date
END AS prewalk_date,
d.number AS department_number,
CASE
WHEN pg.program_type = 14 THEN pj.rollout_date
WHEN pg.program_type = 9 THEN r.cet_wave_date
ELSE pl.execution_date
END AS reset_start_date,
pg.program_type,
pj.project_type,
pj.project_status,
rs.rollout_week,
l.region,
l.ogrp,
l.market,
l.buying_office,
l.district,
l.mregion,
l.location_type,
ol.order_location,
pp.entity AS project_participant_entity,
pp.role AS project_participant_role,
d.department,
v.vendor,
pj.archived AS project_archived,
r.archived AS reset_archived,
rt.labor_team_type,
el_mem.entity AS entity_30,
er_fcpm.entity AS entity_74,
el_fss.entity AS entity_35,
pp_ipm.entity AS entity_3
   FROM tb_reset r
   LEFT JOIN tb_project pj ON pj.project = r.project
   LEFT JOIN tb_location l ON l.location = r.location
   LEFT JOIN tb_program pg ON pg.program = r.program
   LEFT JOIN tb_reset_survey rsv ON rsv.reset = r.reset
   LEFT JOIN tb_program_location pl ON pl.program = r.program AND
pl.location = r.location
   LEFT JOIN tb_program_type pgt ON pgt.program_type = pg.program_type
   LEFT JOIN tb_project_department pd ON pd.project = pj.project
   LEFT JOIN tb_department d ON d.department = pd.department
   LEFT JOIN tb_fiscal_calendar fc ON fc.day = r.execution_date
   LEFT JOIN tb_fiscal_month fm ON fm.fiscal_month = fc.month
   LEFT JOIN tb_rollout_schedule rs ON rs.fiscal_week = fc.week AND
rs.fiscal_year = fc.year AND rs.program = r.program
   LEFT JOIN tb_reset_team rt ON rt.reset_team = r.reset_team
   LEFT JOIN tb_vendor v ON v.vendor = rt.vendor
   LEFT JOIN tb_reset_status rst ON rst.reset_status = r.reset_status
   LEFT JOIN tb_order_location ol ON ol.location = r.location
   LEFT JOIN tb_entity_reset er ON er.reset = r.reset
   LEFT JOIN tb_market m ON m.market = l.market
   LEFT JOIN tb_district dist ON dist.district = l.district
   LEFT JOIN tb_mregion mr ON mr.mregion = l.mregion
   LEFT JOIN tb_region rg ON rg.region = l.region
   LEFT JOIN tb_ogrp ogrp ON ogrp.ogrp = l.ogrp
   LEFT JOIN tb_buying_office byo ON byo.buying_office = l.buying_office
   LEFT JOIN tb_project_participant pp ON pp.project = r.project
   LEFT JOIN tb_project_status ps ON ps.project_status = pj.project_status
   LEFT JOIN tb_entity_location el_mem ON el_mem.location = r.location
AND el_mem.role = 30
   LEFT JOIN tb_entity_reset er_fcpm ON er_fcpm.reset = r.reset AND
er_fcpm.role = 74
   LEFT JOIN tb_entity_location el_fss ON el_fss.location = r.location
AND el_fss.role = 35
   LEFT JOIN tb_project_participant pp_ipm ON pp_ipm.project =
r.project AND pp_ipm.role = 3;

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. 
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

“Quality is not an act, it is a habit.” — Aristotle
​

Re: [GENERAL] Postgress Doubts

[Atri Sharma]

On Fri, Jun 6, 2014 at 4:22 PM, Ravi Kiran  wrote:

> Hello, I am Ravikiran, pursuing my third year BITS Pilani, India, I am
> doing my thesis in postgress technology,
>
> The project is about implementing new join algorithms in postgres, Since I
> am new to postgress, could you help me in which part of source code of
> postgres should be looking in postgress so that I can progress in this
> project, also are there any tools for postgress which helps me look into
> the code of postgress easily.
>
>
>
You can get help from http://doxygen.postgresql.org/

You can look in optimizer/path for looking at how current join paths are
generated.

Regards,

Atri

[GENERAL] Postgress Doubts

[Ravi Kiran]

Hello, I am Ravikiran, pursuing my third year BITS Pilani, India, I am
doing my thesis in postgress technology,

The project is about implementing new join algorithms in postgres, Since I
am new to postgress, could you help me in which part of source code of
postgres should be looking in postgress so that I can progress in this
project, also are there any tools for postgress which helps me look into
the code of postgress easily.

Thank you.

Re: [GENERAL] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb

[Bhushan Pathak]

My locale is set to en_US.UTF-8, that's what the service script is using.

 If I try to start the server with pg_ctl command, it does start -
./pg_ctl start -D /var/lib/pgsql/data -l /tmp/logfile
server starting

What would this mean? The service script does not work but manually issuing
pg_ctl commands work.

Thanks
Bhushan Pathak



On Fri, Jun 6, 2014 at 5:48 AM, Adrian Klaver 
wrote:

> On 06/05/2014 09:11 AM, Bhushan Pathak wrote:
>
>> It did create the data directory as well pg_log directory. I ran the
>> initdb again with -d option, but it still printed the same line in the
>> startup log, nothing more.
>> $SU -l postgres -c "$PGENGINE/initdb --pgdata='$PGDATA' --auth='ident'
>> $LOCALESTRING -d" >> "$PGLOG" 2>&1 < /dev/null
>>
>> I then ran the pg_ctl command as follows
>> su - postgres
>> cd /usr/pgsql-9.2/bin
>> ./pg_ctl initdb -D /var/lib/pgsql/data -o '--auth="ident"
>> --locale=en_US.UTF-8 -d'
>>
>>
> Try this with initdb directly. I personally am confused with what pg_ctl
> does with -o since -d is an option to postgres not initdb and --locale is
> an option to initdb not postgres.
>
>
>
>  creating collations ... initdb: locale name has non-ASCII characters,
>> skipped: "bokmål"
>> initdb: locale name has non-ASCII characters, skipped: "français"
>> could not determine encoding for locale "hy_AM.armscii8": codeset is
>> "ARMSCII-8"
>> could not determine encoding for locale "ka_GE": codeset is "GEORGIAN-PS"
>> could not determine encoding for locale "ka_GE.georgianps": codeset is
>> "GEORGIAN-PS"
>> could not determine encoding for locale "kk_KZ": codeset is "PT154"
>> could not determine encoding for locale "kk_KZ.pt154": codeset is "PT154"
>> could not determine encoding for locale "tg_TJ": codeset is "KOI8-T"
>> could not determine encoding for locale "tg_TJ.koi8t": codeset is "KOI8-T"
>> could not determine encoding for locale "thai": codeset is "TIS-620"
>> could not determine encoding for locale "th_TH": codeset is "TIS-620"
>> could not determine encoding for locale "th_TH.tis620": codeset is
>> "TIS-620"
>> could not determine encoding for locale "vi_VN.tcvn": codeset is
>> "TCVN5712-1"
>>
>
> Looks like Postgres is having a problem determining what the locale is for
> your setup.
>
> What is your locale?
>
>
>
>  Success. You can now start the database server using:
>>
>>  /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/data
>> or
>>  /usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
>>
>>
>> And also seemed to be successful. But when I quit the postgres shell,
>> tried to restart the service as root user, it has failed with the
>> following message on CLI -
>>
>
> So you are using the system start script?
>
> What happens if you do pg_ctl start -D /var/lib/pgsql/data as the postgres
> user?
>
>
>
>  Stopping postgresql service:   [  OK  ]
>> Starting postgresql service:  [FAILED]
>>
>> pgstartup log has the same line  -
>> Segmentation fault (core dumped)
>>
>> Where is this core dump file generated? How do we proceed further from
>> here?
>>
>
> Take a look at this Wiki page:
>
> https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_
> a_running_PostgreSQL_backend_on_Linux/BSD
>
>
>
>> Thanks
>> Bhushan Pathak
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>