Re: [GENERAL] How to stop script executions

[Dev Kumkar]

On Tue, Jul 26, 2016 at 7:53 PM, John McKown 
wrote:

> I agree. From my reading at:
> https://www.postgresql.org/docs/9.5/static/sql-copy.html the COPY FROM
> PROGRAM is only available to a PostgreSQL user who is database superuser.
> That, sort of, implies to me that said user is trusted not to do "evil",
> but abide by the restrictions place upon him/her/it. In some strange
> reality where this is not the case, and I was running on Linux, I would use
> SeLinux in enforcing mode to really restrict what the id under which the
> server is running could do. That is, a "don't allow unless explicitly
> allowed" type policy. Or I'd "sandbox" the PostgreSQL server code using
> something like docker, or under in a virtual machine with little access to
> other services.
>

PostgreSQL version is 9.3 and O/s is both linux and windows.

I was trying to understand from both O/S perspectives that what kind of
commands can be run using COPY FROM PROGRAM which can have an impact.
Thanks for all the information!

Regards...

Re: [GENERAL] How to stop script executions

[Dev Kumkar]

On Tue, Jul 26, 2016 at 7:49 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> ​For superusers, no.  You'd have to protect the system using external
> tools to limit what areas and commands the postgres (or whatever PostgreSQL
> runs as) user ​can access.
>

Thanks David for the information so far!

Re: [GENERAL] How to stop script executions

[Dev Kumkar]

On Tue, Jul 26, 2016 at 6:59 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> ​Typically this means that given user only having psql, or some other
> backend protocol only, connect to the database are they able to execute
> arbitrary commands as the user running the PostgreSQL process on the host
> system.​
>
> Untrusted langauges are untrusted for specifically this reason.  Without
> untrusted languages it requires privilege escalation to interact
> dynamically with the host operating system.
>
> Assuming raised privileges it is presently impossible to prevent such
> dynamic interaction.
>

Just thinking if untrusted language like plperlu is not installed then
executing arbitrary commands is not possible.
So the other possible which you did mention was COPY FROM PROGRAM command,
is this understanding correct?

Regards...

Re: [GENERAL] How to stop script executions

[Dev Kumkar]

On Tue, Jul 26, 2016 at 6:54 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> ​Superuser: yes (specifically I'm thinking the COPY FROM PROGRAM command,
> but also C language functions)
> Ordinary user: not that I can think of.
>

OK, is there a way to restrict usage of COPY FROM PROGRAM command?

Regards...

Re: [GENERAL] How to stop script executions

[Dev Kumkar]

On Tue, Jul 26, 2016 at 6:29 PM, Sameer Kumar 
wrote:

You mean you don't want any routines/functions written in Pl/perl or
> PL/pythin to get executed?
>
> If that is what you are looking for them simply drop the extension for
> these languages or deny privilege to users/public on these extensions.
>
> Infact from what I know, these extensions are not there by default, so
> don't create them at all.
>

Thanks Sameer!
Yeah these extensions are not present, are their any chances of running OS
commands from database?

Regards..

Re: [GENERAL] How to stop script executions

[Dev Kumkar]

On Tue, Jul 26, 2016 at 6:35 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> The way to go about prohibiting their usage altogether is specific to your
> installation method.  But by default those languages are not installed into
> newly created databases and only a superuser can "CREATE EXTENSION" so
> without superuser intervention uses in the database will be unable to
> create functions in those languages.  If that is an insufficient level of
> prevention you would have to somehow get your installation of PostgreSQL to
> not include those core extensions.  I'm pretty sure that during compilation
> you ​can do this.  For packaged distributions I do not know what all is
> involved (i.e., what you have to choose to not install).
>
>
>
Thanks David !
Yeah, the extensions for perl and python are not installed. Checked both
pg_available_extensions & \dx output.

Can I run OS commands without these extensions?

Regards...

[GENERAL] How to stop script executions

[Dev Kumkar]

Hello Experts,

I want to have my postgreSQL database to only execute SQLs and avoid
execution of perl and python script executions.

Can you please suggest ways to achieve this?

Regards...

[GENERAL] PostgreSQL upgrade 9.3.4 -> 9.3.10

[Dev Kumkar]

Hello,


I want to upgrade my database from version 9.3.4 to 9.3.10.

For this task, do I need to upgrade database using pg_upgrade utility?
http://www.postgresql.org/docs/9.3/static/pgupgrade.html


>From the details it looks like for minor version upgrade pg_upgrade utility
is not required.
"pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data
files to be upgraded to a later PostgreSQL major version without the data
dump/reload typically required for major version upgrades, e.g. from 8.4.7
to the current major release of PostgreSQL. It is not required for minor
version upgrades, e.g. from 9.0.1 to 9.0.4."

Can someone please provide more details here and also what steps needs to
be done to upgrade to 9.3.10 level?

Regards...

Re: [GENERAL] PostgreSQL upgrade 9.3.4 -> 9.3.10

[Dev Kumkar]

On Tue, Jan 12, 2016 at 3:25 PM, John R Pierce <pie...@hogranch.com> wrote:

> On 1/12/2016 1:28 AM, Dev Kumkar wrote:
>
>
> I want to upgrade my database from version 9.3.4 to 9.3.10.
>
> For this task, do I need to upgrade database using pg_upgrade utility?
> <http://www.postgresql.org/docs/9.3/static/pgupgrade.html>
> http://www.postgresql.org/docs/9.3/static/pgupgrade.html
>
>
> From the details it looks like for minor version upgrade pg_upgrade
> utility is not required.
> "pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL 
> data
> files to be upgraded to a later PostgreSQL major version without the data
> dump/reload typically required for major version upgrades, e.g. from 8.4.7
> to the current major release of PostgreSQL. It is not required for minor
> version upgrades, e.g. from 9.0.1 to 9.0.4."
>
> Can someone please provide more details here and also what steps needs to
> be done to upgrade to 9.3.10 level?
>
>
> you can do an inplace upgrade of the software, as others said, pg_upgrade
> is for upgrading from 9.X to 9.Y where Y>X
>
> however, its always a good idea to check the release notes of the version
> you're upgrading to, and seeing if there are any special issues, for
> instance, sometimes there's a potential bug in an obscure feature in said
> A.B.C version, and upgrading to A.B.D might require a reindex or
> something.
> http://www.postgresql.org/docs/current/static/release-9-3-10.html
>
> I don't see anything specifically for 9.3.4 to 9.3.10 but read it for
> yourself, following the links to the applicable intermediate release notes.
> .
>
> --
> john r pierce, recycling bits in santa cruz
>
> Thanks everyone for your quick comments, appreciate it !

Yes nothing specifically mentioned for 9.3.4 to 9.3.10

Regards...

Re: [GENERAL] Lock Management: Waiting on locks

[Dev Kumkar]

Thanks Bill !

On Wed, Nov 26, 2014 at 9:07 AM, Bill Moran wmo...@potentialtech.com
wrote:

 In addition to what you're getting from that query, include the xact_start
 and
 state_change columns from pg_stat_activity. My guess is that your code is
 starting a transaction, then running a query, then processing the query
 results before committing the transaction. Since query locks are held for
 the duration of the transaction, this will cause the locks to be held for a
 long time if the processing step takes a while.


Actually each of this transaction is just single INSERT or single UPDATE
query.


 If that turns out not to be the problem, then you'll probably need to
 provide
 a bit more detail before anyone will be able to provide a better answer. I
 mean, I'm even guessing that it's an app making the queries.


Can you let me know what exact details should be provided here?

Regards...

Re: [GENERAL] Transactions to create pg_multixact members and offsets

[Dev Kumkar]

On Fri, Nov 21, 2014 at 1:14 AM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:


 If there are foreign keys on the tables, the system internally runs some
 SELECT FOR KEY SHARE queries on the referenced tables (the ones
 containing the primary or unique keys).  You can get some multixacts
 that way too.

 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


Thanks !

If I recollect correct, then is the cleanup of pg_multixact/members fixed
in 9.3.5 release?

Regards...

[GENERAL] Lock Management: Waiting on locks

[Dev Kumkar]

Am debugging a race condition scenario where multiple transaction are
running in parallel and there are insert/update these transactions are
performing.

I was able to identify the blocking query and blocked query using following
SQL.

However observed the blocking query is holding the locks on the tuple in
minutes thereby making the other transaction query to wait on locks and
slowness.

Can I get more insight why the blocking query is not releasing locks?

SELECT
kl.pid as blocking_pid,
ka.usename as blocking_user,
ka.query as blocking_query,
bl.pid as blocked_pid,
a.usename as blocked_user,
a.query as blocked_query,
to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.pid
JOIN pg_catalog.pg_locks kl
ON bl.locktype = kl.locktype
and bl.database is not distinct from kl.database
and bl.relation is not distinct from kl.relation
and bl.page is not distinct from kl.page
and bl.tuple is not distinct from kl.tuple
and bl.virtualxid is not distinct from kl.virtualxid
and bl.transactionid is not distinct from kl.transactionid
and bl.classid is not distinct from kl.classid
and bl.objid is not distinct from kl.objid
and bl.objsubid is not distinct from kl.objsubid
and bl.pid  kl.pid
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.pid
WHERE kl.granted and not bl.granted
ORDER BY a.query_start;

Regards...

[GENERAL] Transactions to create pg_multixact members and offsets

[Dev Kumkar]

Hellos,

How to manually increase pg_multixact members and offsets?

Does a transaction waiting for exclusive lock or shared lock result into
entry being created in pg_multixact?

Excerpt of multixact.c:
/*-
2  *
3  * multixact.c
4  * PostgreSQL multi-transaction-log manager
5  *
6  * The pg_multixact manager is a pg_clog-like manager that stores an
array of
7  * MultiXactMember for each MultiXactId. It is a fundamental part of the
8  * shared-row-lock implementation.

Regards...

Re: [GENERAL] Transactions to create pg_multixact members and offsets

[Dev Kumkar]

On Thu, Nov 20, 2014 at 11:15 PM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:

 Search for burnmulti in the archives, which is a contrib module to
 test pg_multixact.

Thanks, got some links. Will give a try and get back.

 Merely waiting does not, but more than one lock being acquired on a
 tuple does cause a multixact to be created.  Try SELECT FOR SHARE on two
 transactions on the same tuple.

Sure.
Also what if there are only inserts/updates which transaction have and
there are no explicit select queries?

Regards...

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Fri, Sep 26, 2014 at 1:36 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 Received the database with huge pg_multixact directory of size 21G and
 there are ~82,000 files in pg_multixact/members and 202 files in
 pg_multixact/offsets directory.

 Did run vacuum full on this database and it was successful. However now
 am not sure about pg_multixact directory. truncating this directory except
  file results into database start up issues, of course this is not
 correct way of truncating.
  FATAL:  could not access status of transaction 13224692

 Stumped ! Please provide some comments on how to truncate pg_multixact
 files and if there is any impact because of these files on database
 performance.


Facing this issue on couple more machines where pg_multixact is huge and
not being cleaned up. Any suggestions / troubleshooting tips?

Regards...

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Tue, Sep 30, 2014 at 8:50 PM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:

 Did you try decreasing the autovacuum_multixact_freeze_min_age and
 autovacuum_multixact_freeze_table_age parameters?

As per the docs this set anywhere from zero to 1 billion for
vacuum_multixact_freeze_min_age

And zero to 2 billion for vacuum_multixact_freeze_table_age.

Modified this to have value 10 and 15 respectively. Not sure if that's
correct way of setting these parameters?

What exact server version are you running?

 Am using PostgreSQL 9.3.4 (linux-64-bit)

Regards...

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Fri, Sep 19, 2014 at 1:23 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 Apologies for the delay, was working/troubleshooting same issue and was
 away from my emails. :(
 Regards...


Received the database with huge pg_multixact directory of size 21G and
there are ~82,000 files in pg_multixact/members and 202 files in
pg_multixact/offsets directory.

Did run vacuum full on this database and it was successful. However now
am not sure about pg_multixact directory. truncating this directory except
 file results into database start up issues, of course this is not
correct way of truncating.
 FATAL:  could not access status of transaction 13224692

Stumped ! Please provide some comments on how to truncate pg_multixact
files and if there is any impact because of these files on database
performance.

Regards...

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Fri, Sep 19, 2014 at 8:07 AM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:

 Can you paste the pg_controldata output please?


pg_controldata output as follows:

pg_control version number:937
Catalog version number:   201306121
Database system identifier:   6023658189132429183
Database cluster state:   in production
pg_control last modified: Fri Sep 19 12:09:05 2014
Latest checkpoint location:   2D3/5DB461C0
Prior checkpoint location:2D3/5D08D0D0
Latest checkpoint's REDO location:2D3/5D68EFE0
Latest checkpoint's REDO WAL file:000102D3005D
Latest checkpoint's TimeLineID:   1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:  0/10882952
Latest checkpoint's NextOID:  3443291
Latest checkpoint's NextMultiXactId:  3622064
Latest checkpoint's NextMultiOffset:  4294172074
Latest checkpoint's oldestXID:1673
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:Fri Sep 19 12:06:35 2014
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline:   0
Backup start location:0/0
Backup end location:  0/0
End-of-backup record required:no
Current wal_level setting:minimal
Current max_connections setting:  100
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:   8
Database block size:  8192
Blocks per segment of large relation: 131072
WAL block size:   8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index:  32
Maximum size of a TOAST chunk:1996
Date/time type storage:   64-bit integers
Float4 argument passing:  by value
Float8 argument passing:  by value
Data page checksum version:   0



 Also, what files are there in pg_multixact/offsets/ ?

Currently there are about 56 files in  pg_multixact/offsets/.

  0002  0004  0006  0008  000A  000C  000E  0010  0012  0014  0016
0018  001A  001C  001E  0020  0022  0024  0026  0028  002A  002C  002E
0030  0032  0034  0036
0001  0003  0005  0007  0009  000B  000D  000F  0011  0013  0015  0017
0019  001B  001D  001F  0021  0023  0025  0027  0029  002B  002D  002F
0031  0033  0035  0037

Regards...

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Fri, Sep 19, 2014 at 1:03 PM, Andres Freund and...@2ndquadrant.com
wrote:

 Yes: Learning some patience. You'd given the previous answer two hours
 before this one. Nobody is paid to work on this list...


Apologies for the delay, was working/troubleshooting same issue and was
away from my emails. :(

Regards...

Re: [GENERAL] pg_multixact issues

[Dev Kumkar]

On Fri, Sep 19, 2014 at 7:14 PM, Emanuel Calvo 
emanuel.ca...@2ndquadrant.com wrote:


 Could it be related to some fixes on 9.3.5?:

 - Fix wraparound handling for pg_multixact/members (Álvaro Herrera)
 - Truncate pg_multixact during checkpoints, not during VACUUM (Álvaro
 Herrera)


Currently won't be possible to upgrade to 9.3.5.

Anything can be done in 9.3.4 to trucate them - vacuum full?

Regards...

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Thu, Sep 18, 2014 at 2:41 AM, Adrian Klaver adrian.kla...@aklaver.com
wrote:


 Aaah, hit enter too soon. Also see the other changes under Changes that
 apply to multixact in 9.3.5


Thanks for sharing same. Found this one interesting Truncate pg_multixact
during checkpoints, not during VACUUM (Álvaro Herrera) and also other
changes. But am not sure are you suggesting to move to 9.3.5 ?

Actually looking for some guidelines on truncating pg_multixact at this
situation.

- Do I need to run vaccum manually here and then the pg_multixact can be
truncated?
- Actually looking out for some hints wherein can know the current
pg_multixact/members which are active and which one are stale which can be
truncated? Is there any query to find this information?

pg_class.relminmxid can be referred but should I change the value of
autovacuum_multixact_freeze_max_age which defaults to 400 million
multixacts, setting this value to lower limits would help in cleaning up
pg_multixact?

Regards...

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund and...@2ndquadrant.com
wrote:

 I don't think that's relevant for you.

 Did you upgrade the database using pg_upgrade?


That's correct! No, there is no upgrade here.


 Can you show pg_controldata output and the output of 'SELECT oid,
 datname, relfrozenxid, age(relfrozenxid), relminmxid FROM pg_database;'?


Here are the details:
 oid   datname datfrozenxidage(datfrozenxid)datminmxid
16384 myDB1673 10872259 1

Additionally wanted to mention couple more points here:
When I try to run vacuum full on this machine then facing following issue:
 INFO:  vacuuming myDB.mytable
 ERROR:  MultiXactId 3622035 has not been created yet -- apparent
wraparound

No Select statements are working on this table, is the table corrupt?

Regards...

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund and...@2ndquadrant.com
 wrote:

 I don't think that's relevant for you.

 Did you upgrade the database using pg_upgrade?


 That's correct! No, there is no upgrade here.


 Can you show pg_controldata output and the output of 'SELECT oid,
 datname, relfrozenxid, age(relfrozenxid), relminmxid FROM pg_database;'?


 Here are the details:
  oid   datname datfrozenxidage(datfrozenxid)datminmxid
 16384 myDB1673 10872259 1

 Additionally wanted to mention couple more points here:
 When I try to run vacuum full on this machine then facing following
 issue:
  INFO:  vacuuming myDB.mytable
  ERROR:  MultiXactId 3622035 has not been created yet -- apparent
 wraparound

 No Select statements are working on this table, is the table corrupt?


Any inputs/hints/tips here?

Re: [GENERAL] Regarding timezone

[Dev Kumkar]

On Tue, Sep 16, 2014 at 6:16 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 On Fri, Sep 12, 2014 at 7:31 PM, Adrian Klaver adrian.kla...@aklaver.com
 wrote:


 What OS and what packaging?

 For both windows-64-bit and Linux-64-bit.
 PostgreSQL version - 9.3.4

 I believe the file pgsql/share/postgresql/timezone/Europe/Moscow will
 require changes.
 As the above changes will then be reflected in the function
 *pg_timezone_names()* which will modify the output as follows:

  Europe/Moscow;MSK;04:00:00;f
  Europe/Moscow;MSK;03:00:00;f

 Can I get a modified version of this file?

 Regards...


Any inputs?

Regards...

[GENERAL] pg_multixact issues

[Dev Kumkar]

Hello,

On one my machine the pg_multixact directory size has grown up to 5 GB and
am not sure how to clean up this directory.

From the storage-file-layout this directory contains multitransaction
status data.
pg_multixactSubdirectory containing multitransaction status data (used for
shared row locks)
It would really help if someone can provide some reading material regarding
pg_multixact? Would this also result in database slowness by any chance?

Are there any tweaking commands related to this directory settings, also
how can I cleanup/truncate this directory without impacting the overall
database.

Looking forward to get some insight here.

Regards...

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:


 http://www.postgresql.org/docs/9.3/static/routine-
 vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND

 Might also want to take a look at pg_stat_activity to see what queries
 maybe hanging up:


 http://www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW


Thanks, yes have been looking into pg_stat_activity table and somehow the
standard queries are hanging.
Not sure if this is because the database response has become very slow.

Regards...

Re: [GENERAL] pg_multixact issues

[Dev Kumkar]

On Wed, Sep 17, 2014 at 6:53 PM, Andres Freund and...@2ndquadrant.com
wrote:

 On 2014-09-17 17:46:05 +0530, Dev Kumkar wrote:
  On one my machine the pg_multixact directory size has grown up to 5 GB
 and
  am not sure how to clean up this directory.

 Which version of postgres are you using?

 Greetings,

 Andres Freund


Postgres 9.3.4 (linux-64-bit)

Regards...

Re: [GENERAL] Regarding timezone

[Dev Kumkar]

On Wed, Sep 17, 2014 at 7:24 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:

 So you will need to determine what method you binaries use. If they are
 using the system timezone data, you will need to update that.


Thanks!

Just a hack here, how about copying
pgsql/share/postgresql/timezone/Etc/GMT-3 as
pgsql/share/postgresql/timezone/Europe/Moscow
This will make the *pg_timezone_names()* function to return output as
follows:
 Europe/Moscow;GMT-3;03:00:00;f

With this now() function returns expected data for Moscow timezone.
Please let me know your comments here.

Regards..

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 Thanks, yes have been looking into pg_stat_activity table and somehow the
 standard queries are hanging.
 Not sure if this is because the database response has become very slow.


Would having a huge pg_multixact directory have an impact on databse
performance?

Regards...

Re: [GENERAL] Regarding timezone

[Dev Kumkar]

On Wed, Sep 17, 2014 at 8:52 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:


 I am not that versed in the timezone handling to make a definitive
 statement. I would say it should work until you upgrade. At that point the
 new Moscow timezone should be correct and the change will not matter. I
 would verify on the upgrade though.


Which upgraded postgres version will have the corrected Moscow timezone?

Tom, can you too provide your comments on the mentioned hack here.

Regards...

Re: [GENERAL] Regarding timezone

[Dev Kumkar]

On Thu, Sep 18, 2014 at 12:01 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 The next set of minor releases, whenever those are (and no, there's
 no schedule).


I hope the binaries archive containing pgsql/share/postgresql/
timezone/Europe/Moscow uploaded at
http://www.enterprisedb.com/products-services-training/pgbindownload will
also get corrected.

It would break timezone reporting for historical Moscow timestamps, ie
 they'd be printed as GMT-3 whether or not that was applicable at the time.
 Don't know whether you care about that.


The column storing datetime is of type timestamp and timezone is not
being stored. So should not be an issue for this particular use-case. For
timestamp with time zone that was definitely an issue.
By the way won't that be issue anyways once the Moscow timezone is
corrected in upgraded postgres version?

Regards...

Re: [GENERAL] Regarding timezone

[Dev Kumkar]

On Thu, Sep 18, 2014 at 1:22 AM, Adrian Klaver adrian.kla...@aklaver.com
wrote:

 On 09/17/2014 12:26 PM, Dev Kumkar wrote:

 I hope the binaries archive containing pgsql/share/postgresql/
 timezone/Europe/Moscow uploaded at
 http://www.enterprisedb.com/products-services-training/pgbindownload
 will also get corrected.


 They will.


Thanks, on 25 October 2014 the timezone will change permanently. Currently
binaries are at 9.3.5.1 level, will keep an watch on the update.

 No, because a timezone(versus an offset) definition includes a historical
 record of the changes in the timezone. For all the gory details see:

 http://en.wikipedia.org/wiki/Tz_database


Great info! So the historical Moscow timestamps will still reflect GMT+4
based on the datetime.
Sure, awaiting fix and will cross-check the behavior.

Re: [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver adrian.kla...@aklaver.com
 wrote:


 http://www.postgresql.org/docs/9.3/static/routine-
 vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND


Looked into these details. Can there be an example explained that will
really help to understand this in practice?

Also one additional information here, the database was restarted various
times. But still the pg_multixact directory size is increasing.
Actually there were multiple updates happening in different processes which
lead to the locking issues and landed up into this situation.

How can I recover the system at this stage and also clean up pg_multixact
gracefully?

Regards...

Re: [GENERAL] Regarding timezone

[Dev Kumkar]

On Fri, Sep 12, 2014 at 7:31 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:


 What OS and what packaging?

 For both windows-64-bit and Linux-64-bit.
PostgreSQL version - 9.3.4

I believe the file pgsql/share/postgresql/timezone/Europe/Moscow will
require changes.
As the above changes will then be reflected in the function
*pg_timezone_names()* which will modify the output as follows:

 Europe/Moscow;MSK;04:00:00;f
 Europe/Moscow;MSK;03:00:00;f

Can I get a modified version of this file?

Regards...

Re: [GENERAL] Regarding timezone

[Dev Kumkar]

On Wed, Sep 10, 2014 at 8:43 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 You'd want to get a new version of the IANA timezone database files for
 that.  Depending on what packaging you're using, this might be an
 operating-system update not a Postgres update.  If you are relying
 on the Postgres copies, you'd have to update src/timezone/data/ and
 then do a make install in src/timezone.

 regards, tom lane


Thanks Tom !
Actually am using the postgres binaries and not building from sources. Is
there any solution if using the binaries and want to update timezone
settings?

Regards...

[GENERAL] Regarding timezone

[Dev Kumkar]

Hello,

I want to change the timezone to Europe/Moscow. Moscow timezone is changing
from +4 to +3

On the box, I have changed the timezone to MSK+3.

From the db side I have modified the following files:
1)postgresql.conf
timezone = 'Europe/Moscow'
2)pgsql/share/postgresql/timezonesets/Default
MSK 10800# Moscow Time (caution: this used to mean
10800)
 # (Europe/Moscow)
# date
Wed Sep 10 17:34:14 MSK 2014

Running following queries the output is as follows:
- select now()  =   2014-09-10 18:34:19.041994+04
- select now() at time zone 'MSK'   =  2014-09-10 17:34:31.006944

What changes needs to done so that now() returns expected value which
matches with the time zone?

Thanks in advance!

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Wed, Apr 16, 2014 at 9:08 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 On Wed, Apr 16, 2014 at 7:50 PM, Stephen Frost sfr...@snowman.net wrote:

 * Dev Kumkar (devdas.kum...@gmail.com) wrote:
  I just downloaded the latest binaries from EnterpriseDB and when checked
  with libssl.so.1.0.0 can see this:
  OpenSSL 1.0.1g 7 Apr 2014
 
  OpenSSL 1.0.1g is the patched version.

 Yes, checked w/ them and they say it's all patched..

  Awaiting confirmation and also please let know if there is certain NOTE
 or
  link which talks about this fix from EnterpriseDB side.

 There's a note on the 'installers' page here:
 http://www.enterprisedb.com/products-services-training/pgdownload

 I believe they're going to add a note to the other page too.

 Thanks,

 Stephen


 Thanks for the confirmation. Yup checked the NOTE on 'installers' page and
 a note on binary page will really help.

 Regards...


Hello Guys,

For postgreSQL, is there any OpenSSL fix coming up for this issue:
http://www.zdnet.com/openssl-fixes-another-severe-vulnerability-730253/

Currently in PostgreSQL 9.4.3 the version is as follows: OpenSSL 1.0.1g 7
Apr 2014

As per the above link, fixed OpenSSL version would be 1.0.1h

Looking forward for some comments here.

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Thu, Jun 5, 2014 at 11:03 PM, Magnus Hagander mag...@hagander.net
wrote:

 Hi!

 The guys at EnterpriseDB are busy building new installers as we speak, I
 would expect them to be out tomorrow or so.

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


Thanks for the update.
That's really good to know, I hope binary fix will also be available.
http://www.enterprisedb.com/products-services-training/pgbindownload

Regards...

[GENERAL] pg_ctl start error and lock file

[Dev Kumkar]

Faced following issue when trying to start database using pg_ctl:

FATAL:  could not create lock file /tmp/.s.PGSQL.5432.lock: Permission
denied

Accidentally someone cleaned up /tmp and recreated it but without any write
permissions to non-root user, if the non-root user write permissions are
given to /tmp then it would start without any issues.

I was going thru some links and could find following ( 9+ years old
thread):
http://compgroups.net/comp.postgresql.general/postgres-start-error/2886274

Is there any way to control where the lock file can be created?

Regards...

Re: [GENERAL] pg_ctl start error and lock file

[Dev Kumkar]

On Tue, Apr 29, 2014 at 6:26 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote:

 Set unix_socket_directory (in versions before 9.3) or
 unix_socket_directories (from 9.3 on) in postgresql.conf.

 Yours,
 Laurenz Albe


Fantastic, thanks for this setting.

regards...

[GENERAL] Heartbleed Impact

[Dev Kumkar]

We are using postgresql binaries downloaded from here
http://www.enterprisedb.com/products-services-training/pgbindownload

The binaries which are currently at 9.3.3 were updated when the security
vulnerabilities were announced in Feb 2014.

We embed certain binaries and libssl.so.1.0.0 gets shipped along with
pre-build in-house database with product.

Referred this link
http://blog.hagander.net/archives/219-PostgreSQL-and-the-OpenSSL-Heartbleed-vulnerability.htmland
for our database SSL is off:
SSL connection are in OFF.

postgres=# show ssl;
 ssl
-
 off

There is a note for the graphical installers but not the same for binaries:
*NOTE:* April 10, 2014: The installers for PostgreSQL 9.3.4-3, 9.2.8-3,
9.1.13-3, 9.0.17-3 and 8.4.21-3 have recently been updated to include a
patch to address CVE-2014-0160, a TLS heartbeat read overrun issue in the
OpenSSL library that is packaged in the installer.

Can you please let us know about the impact in case binaries are being
shipped and SSL is off?

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Wed, Apr 16, 2014 at 4:57 PM, Boszormenyi Zoltan zbos...@pr.hu wrote:

 The package version and the soversion are only loosely related.
 E.g .the upstream OpenSSL 1.0.0 and 1.0.1 series both ship soversion 1.0.0.

 Best regards,
 Zoltán Böszörményi


of which OpenSSL package versions' libssl.1.0.0.so is available at
http://www.enterprisedb.com/products-services-training/pgbindownload ?

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Wed, Apr 16, 2014 at 3:18 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote:


 Unless somebody changes the setting to ssl=on, there should be no problem.

 Yours,
 Laurenz Albe


Thanks also please help to understand - does changing this postgresql.conf
setting enough to be vulnerable here?

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Wed, Apr 16, 2014 at 5:28 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 On Wed, Apr 16, 2014 at 4:57 PM, Boszormenyi Zoltan zbos...@pr.hu wrote:

 The package version and the soversion are only loosely related.
 E.g .the upstream OpenSSL 1.0.0 and 1.0.1 series both ship soversion
 1.0.0.

 Best regards,
 Zoltán Böszörményi


 of which OpenSSL package versions' libssl.1.0.0.so is available at
 http://www.enterprisedb.com/products-services-training/pgbindownload ?


Ok, looked at the STRINGS versions and the OpenSSL 1.0.1f 6 Jan 2014 is
seen.

Please let me know if the new binary is uploaded at PG binary download link.

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Wed, Apr 16, 2014 at 6:54 PM, Stephen Frost sfr...@snowman.net wrote:


 Yeah, I'm doing that already and they're looking into it right now.

 Thanks,

 Stephen


I just downloaded the latest binaries from EnterpriseDB and when checked
with libssl.so.1.0.0 can see this:
OpenSSL 1.0.1g 7 Apr 2014

OpenSSL 1.0.1g is the patched version.

Awaiting confirmation and also please let know if there is certain NOTE or
link which talks about this fix from EnterpriseDB side.

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Wed, Apr 16, 2014 at 7:50 PM, Stephen Frost sfr...@snowman.net wrote:

 * Dev Kumkar (devdas.kum...@gmail.com) wrote:
  I just downloaded the latest binaries from EnterpriseDB and when checked
  with libssl.so.1.0.0 can see this:
  OpenSSL 1.0.1g 7 Apr 2014
 
  OpenSSL 1.0.1g is the patched version.

 Yes, checked w/ them and they say it's all patched..

  Awaiting confirmation and also please let know if there is certain NOTE
 or
  link which talks about this fix from EnterpriseDB side.

 There's a note on the 'installers' page here:
 http://www.enterprisedb.com/products-services-training/pgdownload

 I believe they're going to add a note to the other page too.

 Thanks,

 Stephen


Thanks for the confirmation. Yup checked the NOTE on 'installers' page and
a note on binary page will really help.

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Wed, Apr 16, 2014 at 6:49 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote:

 Dev Kumkar wrote:
  Unless somebody changes the setting to ssl=on, there should be no
 problem.

  Thanks also please help to understand - does changing this
 postgresql.conf setting enough to be
  vulnerable here?

 Just changing the setting will only cause your database server to error
 out on restart - you also need to create certificates and put them into
 the server directory.

 So whoever does this change must know what they are doing (to some extent).

 Once SSL has been enabled, a cunning attacker may be able to steal
 the server's private key (if I understood the vulnerability correctly)
 and then launch man-in-the-middle attacks, i.e. impersonate the server,
 to eavesdrop on encrypted communication.

 The remedy would be to create a new key pair for the server.

 Yours,
 Laurenz Albe


Thanks, this really helps. Currently we are not creating certificate and
working in non SSL mode.

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

Hey,

What is the windows equivalent of libssl.so.1.0.0 ?
Please reply as this is really becoming priority for me.

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Thu, Apr 17, 2014 at 12:53 AM, John R Pierce pie...@hogranch.com wrote:

 windows native stuff uses completely different TLS libraries, SChannel and
 stuff.  AFAIK, these aren't subject to this bug, which was specific to
 OpenSSL 1.0.1x for x=a-f...openssl is only used on windows when someone
 uses it explicitly, such as in Cygwin applications, and such.

 It *is* used by postgresql under windows as enterpriseDB builds it, since
 PG was written to use openssl in the first place.

 --
 john r pierce  37N 122W
 somewhere on the middle of the left coast

So does this mean PostgreSQL binaries available on EnterpriseDB has an
impact for windows ?
Can you help me with the binary name?

Regards...

Re: [GENERAL] Heartbleed Impact

[Dev Kumkar]

On Thu, Apr 17, 2014 at 1:31 AM, John R Pierce pie...@hogranch.com wrote:

 do you enable SSL  and expose it to an insecure network ?   if not, no
 exposure to the heartbleed bug.


No, SSL is not enabled in my case but also wanted to make sure there is no
binary available which can later result into any potential issue.


 AFAIK, the binary name is postgres.exe, from what I've read they are
 static linking openssl.  the updated versions on the site linked in another
 message are fixed per the note on that page.
 http://www.enterprisedb.com/products-services-training/pgdownload


http://www.enterprisedb.com/products-services-training/pgbindownload also
has the note added sometime back.
I was able to verify for Linux binaries looking at STRINGS of so file but
was not sure about the windows side and hence was looking for confirmation.

Regards...

Re: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - April 01 2014 ==

[Dev Kumkar]

On Tue, Apr 1, 2014 at 7:10 PM, David Fetter da...@fetter.org wrote:

 == PostgreSQL Weekly News - April 01 2014 ==

 PostgreSQL 10.0 Released.
 This release includes built-in, tradeoff-free multi-master
 replication, full integration with all other data stores, and a broad
 choice of SQL query dialects including Cassandra, Hadoop, Oracle,
 MS-SQL Server, MySQL, and mSQL.
 http://db.cs.berkeley.edu/papers/ERL-M85-95.pdf

 == PostgreSQL Jobs for April ==

 http://archives.postgresql.org/pgsql-jobs/2014-04/threads.php

 == PostgreSQL Local ==

 The Open Data Summit will be held Friday April 11, 2014 in Denver,
 Colorado, USA.
 http://www.opendatasummit.com

 PGCon 2014, the world-wide developer conference for PostgreSQL, will
 be in Ottawa, Ontario, Canada May 20-24, 2014.
 http://www.pgcon.org/2014/

 The sixth PGDay Cubano be held on 13 and 14 October 2014 in Habana.
 https://postgresql.uci.cu/?p=380

 == PostgreSQL in the News ==

 Planet PostgreSQL: http://planet.postgresql.org/

 PostgreSQL Weekly News is brought to you this week by David Fetter

 Submit news and announcements by Sunday at 3:00pm Pacific time.
 Please send English language ones to da...@fetter.org, German language
 to p...@pgug.de, Italian language to p...@itpug.org.  Spanish language
 to p...@arpug.com.ar.

 == Applied Patches ==

 == Rejected Patches (for now) ==

 Everyone was disappointed this week :-(


It talks about multi-master replication. Are there more details available??

Regards...

Re: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - April 01 2014 ==

[Dev Kumkar]

On Thu, Apr 3, 2014 at 11:52 PM, Andy Colson a...@squeakycode.net wrote:


 You realize its an April fools joke, right?

 I think I can hear David's evil laugh from here :-)

 -Andy


Ya, actually did realize the same time when it got posted as was online
that time. ;)

Regards...

[GENERAL] RHEL 7 and Postgres 9.3.4

[Dev Kumkar]

Hello,

RHEL 7 will be in market by June 2014.
I just wanted to check here RHEL 7 and Postgres 9.3.4 compatibility?

Regards...

Re: [GENERAL] RHEL 7 and Postgres 9.3.4

[Dev Kumkar]

On Tue, Apr 1, 2014 at 7:58 PM, Devrim Gündüz dev...@gunduz.org wrote:

 Hi,

 It works fine. We already branched RHEL 7 RPMs. I am using them on my
 testing instance.

 Regards,


Hi Devrim,
Thanks for the quick update !

Regards...

Re: [GENERAL] Auditing Code - Fortify

[Dev Kumkar]

On Thu, Mar 27, 2014 at 1:36 AM, Dev Kumkar devdas.kum...@gmail.com wrote:

 On Thu, Mar 27, 2014 at 1:31 AM, John R Pierce pie...@hogranch.comwrote:

 why don't you ask the Fortify vendor ?


 Yup, following up with them in parallel.
 Search didn't gave me any good links, so wanted to check with community
 too here.

 If not Fortify, is there any other such tool?

 Regards...


Awaiting response..

Correct me if this is wrong alias and need to post to this different
pgsql-ALIAS

Re: [GENERAL] Auditing Code - Fortify

[Dev Kumkar]

On Thu, Mar 27, 2014 at 7:11 PM, Adrian Klaver adrian.kla...@aklaver.comwrote:

 Search on:

 fortify software database

 found:

 http://www.hpenterprisesecurity.com/vulncat/en/vulncat/index.html

 This indicates Postgres is not supported.


 Search on

 fortify open source alternatives

 found:

 https://www.owasp.org/index.php/Source_Code_Analysis_Tools


Thanks, this helps.

Regards...

[GENERAL] Auditing Code - Fortify

[Dev Kumkar]

Is Fortify supported for PostgreSQL?

Any auditing tool which you suggest to check the schema design, roles and
functions and other aspects?

I have used fortify for oracle and sybase, but just not sure about
postgreSQL?

Can anyone provide some pointers here and if not Fortify then any such tool?

Regards...

Re: [GENERAL] Auditing Code - Fortify

[Dev Kumkar]

On Thu, Mar 27, 2014 at 1:31 AM, John R Pierce pie...@hogranch.com wrote:

 why don't you ask the Fortify vendor ?


Yup, following up with them in parallel.
Search didn't gave me any good links, so wanted to check with community too
here.

If not Fortify, is there any other such tool?

Regards...

[GENERAL] Issue with default values and Rule

[Dev Kumkar]

Am facing issues with using UPSERT rule having default value columns.
Here is the code:

create table my_test (id int, col_1 timestamp null, col_2 varchar(12) null
default 'Initial');

CREATE  OR REPLACE RULE RULE_my_test AS ON INSERT TO my_test WHERE EXISTS
(SELECT 1 from my_test  WHERE id = NEW.id )
DO INSTEAD
UPDATE my_test SET col_1 = coalesce(NEW.col_1,my_test.col_1),col_2 =
coalesce(NEW.col_2,my_test.col_2),id = coalesce(NEW.id,my_test.id) WHERE id
= NEW.id;

insert into my_test(id,col_1,col_2) values(1, now() at time zone 'UTC',
'NewValue');
select * from my_test;

Results:
1, 2014-02-27 10:19:20.144141,NewValue

-- Lets not insert col_2 here
insert into my_test(id,col_1) values(1, now() at time zone 'UTC');
select * from my_test;

Results:
1,2014-02-27 10:20:06.573496,Initial


col_2 value becomes the default value i.e. 'Initial'
So rule picks up default value when column is not in the insert list. Can
the rule here modified to not pick default value of column and do the
update stuff correctly?

Regards...

Re: [GENERAL] Issue with default values and Rule

[Dev Kumkar]

On Thu, Feb 27, 2014 at 9:32 PM, Adrian Klaver adrian.kla...@aklaver.comwrote:


 Realized my previous answer:

 col_2 = coalesce(my_test.col_2, NEW.col_2)

 works for the particular situation you described, but not for the general
 case. It would not allow an update of a field where a NON NULL value exists
 and you want to change that value, as the existing field would trump the
 new one.


Yes, there you are. Changing the order in coalesce will not solve the issue
here. As update will also have some real non-null NEW values.
Actually internally when the rule gets called then default value is being
in this case.
However note that 'null' is being explicitly inserted then default value is
not picked by postgres engine internally and data is persisted correctly:

  create table my_test (id int, col_1 timestamp null, col_2 varchar(12)
null default 'Initial');

  insert into my_test(id,col_1,col_2) values(1, now() at time zone
'UTC','ShowMe');
  select * from my_test;
  Results:
  1,2014-02-27 16:34:23.464088,ShowMe

  insert into my_test(id,col_1,col_2) values(1, now() at time zone
'UTC',null);
  select * from my_test;
  Results:
  1,2014-02-27 16:35:49.206237,ShowMe

Agree trigger might give more control here. But still suggest any
breakthrough here.

Regards...

Re: [GENERAL] Issue with default values and Rule

[Dev Kumkar]

On Thu, Feb 27, 2014 at 11:17 PM, Adrian Klaver
adrian.kla...@aklaver.comwrote:

 That works because you said NULL is a valid value for the column. If you
 had specified NOT NULL then you would get an error about violating the NOT
 NULL constraint. Since you have said NULL is a valid value and you actually
 specified it in the INSERT the following applies:

 http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html

 DEFAULT default_expr
  

 The default expression will be used in any insert operation that does
 not specify a value for the column. If there is no default for a column,
 then the default is null.


Hmm, so looks like there is no generic way out here to handle this in case
of rules.

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Thu, Feb 20, 2014 at 4:34 PM, Daniel Verite dan...@manitou-mail.orgwrote:

 Despite windows-1252 being a monobyte encoding sharing most
 of LATIN1 codes and character set, it does not mean that
 English_United States.1252 is limited to this character set.
 You may use UTF-8 databases with that locale.

 Consider the 2nd paragraph of  Character Set Support
 in the doc:
 http://www.postgresql.org/docs/current/static/multibyte.html

 For C or POSIX locale, any character set is allowed, but for other
  locales there is only one character set that will work
  correctly. (On Windows, however, UTF-8 encoding can be used with
  any locale.)

 This is a key difference with Unix when choosing a locale.

 As for getting the exact same sort order than Linux, it's not possible but
 that's not a Windows-vs-Unix issue. If you used FreeBSD or MacOS X, some
 en_US.UTF-8 collation rules  would differ from Linux's libc too, resulting
 in
 a different sort order for certain strings.


There is no issue of using windows-1252 with utf8 database. The point of
discussion here is sorting order and windows code page for utf8?
The links http://msdn.microsoft.com/en-us/library/dd317756%28VS.85%29.aspxwhich
I provided earlier has those code pages but creating database with
these code pages fail.

Well overall with the discussion so far and whatever search I could over
net/community it looks like there is no code page on windows corresponding
to what is utf8 of linux. If there is then please let me know?

Conclusion: I have basically decided to have the database encoding UTF8 on
both windows and linux. And then set the collation to 'C'.
At least my customers on linux and windows sees the same behavior when
sorting. Any gotchas here?

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 3:53 AM, Adrian Klaver adrian.kla...@aklaver.comwrote:

 Each driver will have its own behavior. For an explanation of the JDBC
 behavior see here:

 http://www.postgresql.org/message-id/4b2f2ced.10...@opencloud.com


 Per Andrews posts, the least surprise behavior is to explicitly set the
 client time zone. Then you control what is being seen/used.


Actually then this goes back to the same thing that identify the timezone
setting in OS and accordingly set at the driver level.
In case of java JVM is picking up OS timezone and hence things are working
without any issues for windows/linux both.

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 3:55 AM, Terence Ferraro
terencejferr...@gmail.comwrote:

 Or, if you don't mind a little patching: http://pastebin.com/5AyaX2RF

 That restores the pre-9.1 functionality of determining the timezone on
 postmaster start. As has been pointed out, their new stuff is more useful
 if you're shipping instances to the farthest reaches of the planet (We've
 only got clients in North America at the moment). You will undoubtedly need
 to make some modifications to patch with 9.3, but I've got hundreds of 9.2
 instances in the field now that, well, just work.


Thanks for sharing!
So that means need to build the database all over again for both windows
and linux. Actually have been using binaries and utilizing them for
creating database.

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

inline:
On Thu, Feb 20, 2014 at 3:57 AM, Tom Lane t...@sss.pgh.pa.us wrote:


 The functionality of determining an IANA timezone name equivalent to the
 platform's behavior is currently embedded in initdb and isn't separately
 accessible.  So you've got several options:

Hmm, actually was looking for exact that command which could resolve my
requirement. But as you explained its not separately available.


1. Wait to do initdb until the machine is installed.


No doubt this is solution and doable but doesn't fit into my current
deployment model.


2. Modify the timezone setting in postgresql.conf after the machine is
 installed (although there might not be an easy way to determine what
 to set it to).


Yes as mentioned earlier here is the POA - a perl function to get the
timezone on both windows and linux then map them to the pg_timezone_names
table to get actual names in format Asia/Calcutta and set this as
timezone parameter in postgreSQL.conf to workaround things.



 3. Leave the server timezone setting as GMT and rely on clients to select
 the zone they want to work in.


JDBC there is no issue. However if there is any clue at ODBC driver level,
please let know.

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 4:31 AM, Adrian Klaver adrian.kla...@aklaver.comwrote:

 It depends on how you are declaring the timestamp field. If you do not use
 with time zone then the input value is open to interpretation and is not
  'anchored' to a point in time.

 Example

 My time zone is currently PST.
 test= create table timestamp_test(id int, ts timestamp, ts_z timestamp
 with time zone);
 CREATE TABLE
 test= insert into timestamp_test values (1, now(), now());
 INSERT 0 1
 .
 If you know what time zone the value was inserted under you can get it
 back.
 .
 .
 That assumes a lot, so the best thing is to use timestamp with time zone.


Thanks for trying this out on your setup. However looks like my requirement
is different here.

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Thu, Feb 20, 2014 at 3:04 AM, Gavin Flower gavinflo...@archidevsys.co.nz
 wrote:

 Upgrade servers to Linux?  :-P


Actually that's not the solution but running away from it.
There is a heavy footprint of customers and huge market on windows too and
so not that easy to migrate and convince in market.

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 9:30 PM, Adrian Klaver adrian.kla...@aklaver.comwrote:


 So what is your requirement?
 Do you have a specific application/use for the databases you are
 installing?


There are two interfaces for the database. One is from the ODBC driver and
other is from the JDBC driver.
These are the two clients which I have currently who communicate with
database.

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Fri, Feb 21, 2014 at 12:14 AM, Gavin Flower 
gavinflo...@archidevsys.co.nz wrote:

  On 21/02/14 02:04, Dev Kumkar wrote:

  On Thu, Feb 20, 2014 at 3:04 AM, Gavin Flower 
 gavinflo...@archidevsys.co.nz wrote:

 Upgrade servers to Linux?  :-P


  Actually that's not the solution but running away from it.
 There is a heavy footprint of customers and huge market on windows too and
 so not that easy to migrate and convince in market.

 Regards...

 I am aware of the heavy presence of Microsoft in the market place and the
 huge inertia of Microsoft dominated companies (even where management would
 like to change), hence I was not trying to push upgrading to Linux too
 strongly for this particular situation - more light hearted exasperation!

 None-the-less there are more and more companies making that move - as
 there are a whole raft of very good reasons to do so.

 If the sole reason for going to Linux was the collation problem, then it
 would probably be considered by most people to be a silly reason.


 Cheers,
 Gavin


 P.S.  Once a Senior Systems Analyst left the company I was working for to
 become the DP manager of an IT department.  I spoke to him shortly after
 and he said it was an 'IBM shop' - about ten years later he was at the same
 place, but he now said it was a 'Microsoft shop'.  The dominant technology
 that appears set in stone, does eventually change despite the market
 inertia - my first commercial languages were FORTRAN  COBOL on
 minicomputers  IBM style mainframes, now I use Java on Linux.


Hmm. Don't want to digress here and loose the topic context.
Here would really appreciate if there are any suggestions for UTF-8
collation on Windows?

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Fri, Feb 21, 2014 at 1:26 AM, Adrian Klaver adrian.kla...@aklaver.comwrote:

 Well I dug out a Windows machine and tried to get what you wanted, to no
 avail. As far as I know there is no UTF8 collation, it is an encoding. What
 you want if I am following, is the en_US locale (or equivalent for another
 language) on Windows. Anything I tried resolved back to a Windows code
 page. So the answer from my tests, is no you cannot match en_US on Windows.


Thanks for taking time out and looking into it !
Yes all the scenarios we tested didn't work for any of the utf8 code pages
specified on MSDN or may be I don't know the correct representation of
language_territory.code.

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Fri, Feb 21, 2014 at 12:43 AM, Andrew Sullivan a...@crankycanuck.cawrote:

 Do you control the client code?  If so, why not set the TimeZone
 locally when you connect?  That's the right way to handle this,
 really.


Agree. So find the OS timezone at programming level and set accordingly.
This can be done either in C for ODBC stuff and at java level.
Although JVM has its own timezone database and sets accordingly.

Regards...

[GENERAL] Timezone information

[Dev Kumkar]

How to set timezone in postgreSQL database to pick operating system level
timezone information.

In postgresql.conf there exists timezone parameter whose value can be set.
However this value needs to be in format 'US/Pacific', 'Asia/Calcutta' and
so on to work correctly.

Is there any database query to get operating system level timezone
information and then set accordingly.

Took a look into pg_timezone_names table and

SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

When postgres is installed on IST timezone windows operating system,
registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation
has TimeZoneKeyName set as India Standard Time. However post installation
postgresql.conf has timezone information set as 'Asia/Calcutta', is there
any binary which can be executed?

Regards...

[GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

Am really going no where with this after so many searching over net or am
missing some basic things, not sure!

What is the equivalent for en_US.UTF-8 collation in case of windows?

In Linux am creating database with following options, as follows:
-E utf8 -l en_US.UTF-8 -T template0

This creates utf8 encoding and also the collation is set as en_US.UTF-8.

in case of windows utf8 encoding parameter works but not sure abou the
equivalent for en_US.UTF-8 collation.
Default database gets created with 'English_United States.1252' collation.

As a result the sorting is not same in case of windows and linux database.

Any suggestions/ pointers here?

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Wed, Feb 19, 2014 at 10:16 PM, Adrian Klaver
adrian.kla...@aklaver.comwrote:

 I found the below that might help. I do not use Windows much any more so I
 do not have a machine handy to confirm.

 http://www.g-loaded.eu/2011/02/27/locale-windows/


Thanks for the pointer. *american_usa* works however it sets the
LC_COLLATE to 'English_United States.1252' which is basically ANSI Latin
1 and is not utf8. The third parameter as the link says is codeset -
language_territory.codeset.

Here is the list of all codesets
http://msdn.microsoft.com/en-us/library/dd317756%28VS.85%29.aspx but still
no success.

One of the link says codepage 65001 and utf-8 is same -
http://stackoverflow.com/questions/1629437/is-codepage-65001-and-utf-8-the-same-thing
.

Am not able to find windows codeset equivalent of utf-8 and set it as
LC_COLLATE while creating database.

Has anyone set the LC_COLLATE as utf-8 on windows?

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Wed, Feb 19, 2014 at 8:31 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 If you mean you would like to use Windows' timezone data, the answer is
 you can't --- and you generally shouldn't want to, because AFAIK their
 timezone data is pretty sucky: it's incomplete and not terribly accurate
 about historical details.  We use the IANA timezone database[1], which is
 where those names like Asia/Calcutta come from.

 Most modern operating systems use the IANA database for their system-level
 timezone knowledge, but Windows is still in the dark ages last I heard.

 regards, tom lane


Hmm. Is there any postgreSQL command/binary which can be used to set
timezone according to OS one.
At the time of postgreSQL install how does it pick timezone information and
sets into postgreSQL.conf accordingly.

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Thu, Feb 20, 2014 at 1:19 AM, Adrian Klaver adrian.kla...@aklaver.comwrote:

 So what is the exact command you are using?


createdb -U postgres -E utf8 -l american_usa DBNAME
Above command fails to create utf-8 LC_COLLATE.

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Thu, Feb 20, 2014 at 1:41 AM, Adrian Klaver adrian.kla...@aklaver.comwrote:

 What does it set LC_CTYPE to?

 So what happens if you do?:

 createdb -U postgres -E utf8 -l american_usa.65001 DBNAME


*createdb: database creation failed: ERROR:  invalid locale name:
american_usa.65001 *


 or

 createdb -U postgres -E utf8 --lc-ctype=american_usa
 --lc-collate=american_usa DBNAME

Succeeds but as replied earlier it creates database with LC_COLLATE =
'English_United States.1252' which corresponds to Latin1.

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Thu, Feb 20, 2014 at 2:01 AM, Adrian Klaver adrian.kla...@aklaver.comwrote:

 Just noticed you are not specifying the template database. Try using
 template0:

 createdb -U postgres -E utf8 --lc-ctype=american_usa
 --lc-collate=american_usa  -T template0 DBNAME


Same result i.e. LC_COLLATE and LC_CTYPE gets set as 'English_United
States.1252'
Had specified template option this earlier too.

Btw in case the specified collation is incompatible then createdb binary
complains and instructs to use template0 as template, which isn't the case
here.

The codeset parameter is missed here and hence it takes the collation as
the default one which is 'English_United States.1252'.

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 2:06 AM, John R Pierce pie...@hogranch.com wrote:

 postgres handles timezones on a per client connection basis.  the server
 itself doesn't really care what timezone it is running in. 'timestamp with
 time zone' data is internally stored in the equivalent of UTC, and
 converted to/from the current client timezone on the fly (unless another
 timezone is explicitly stated in the query).


Ok but am confused then, as if no timezone parameter is defined in
postgreSQL.conf file then yes UTC time is returned. Select now()
but if timezone parameter is set then Select now() returns date time as
per timezone.

Again one question which am still looking answer is, in case of install
from postgreSQL installer how the timezone parameter in postgreSQL.conf is
set correctly to match with Operating system timezone.

Also note that, I can write a perl function to get the timezone on both
windows and linux then map them to the pg_timezone_names table to get
actual names in format Asia/Calcutta and set this as timezone parameter
in postgreSQL.conf to workaround things.

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Thu, Feb 20, 2014 at 2:24 AM, Adrian Klaver adrian.kla...@aklaver.comwrote:

 Alright last shot:)

 Taking hint from here:

 http://msdn.microsoft.com/en-us/library/x99tb11d.aspx

 try:

 createdb -U postgres -E utf8 -l en-US DBNAME

 If that does not work, not sure where to go.


This won't work on Windows. Note that en-US collation name is specific to
linux and in case of Windows these names are different which is where am
facing issues to find the exact code page which corresponds to utf8.

Your msdn link has this mentioned which states that for code pages that
require more than two bytes per character which is basically UTF8 doesn't
work with setlocale command. But again its specific to the setlocale API.



*The locale argument can take a locale name, a language string, a language
string and country/region code, a code page, or a
language string, country/region code, and code page. The set of available
locale names, languages, country/region codes, andcode
pages includes all those supported by the Windows NLS API except code pages
that require more than two bytes percharacter, such as
UTF-7 and UTF-8. If you provide a code page value of UTF-7 or UTF-8,
setlocale will fail, returning NULL.*

However am sure there would be some codepage which can be used in
postgreSQL to set the collation to UTF8 equivalent of linux.

Please suggest? Am sure this not something new which am looking for.

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 2:32 AM, Andrew Sullivan a...@crankycanuck.cawrote:

 Try issuing SET TimeZone commands.  I think you'll find that the
 client can set whatever time zone it wants.


I think am not being heard incorrectly here.

Here is the scenario - Database is created using binaries and not
postgreSQL installer here. Next this database gets created on one machine
SRC-INDIA and then is shipped globally which can be in any timezone, for
example TGT-USA.
Since the database is created on SRC-INDIA the timezone parameter gets set
as Asia/Calcutta, but when shipped locally then now() would return
incorrect time as per target TGT-USA local time.

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Thu, Feb 20, 2014 at 2:45 AM, Adrian Klaver adrian.kla...@aklaver.comwrote:


 Have you tried it?

 Note that the locale name is different then the one Linux.

 On Linux it is en_US.

 What I suggested is en-US.


Yes. Here is the output:
createdb -U postgres -E utf8 -l en-US -T template0 mynewdb
Password:
*createdb: database creation failed: ERROR:  invalid locale name: en-US*

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan a...@crankycanuck.cawrote:

 No, select now() would return the time in whatever timezone is set, or
 the timezone that the server defaulted to if there's nothing set by
 the client.  So in your installation, set up the server to use UTC by
 default and, if you like, set the client's time zone according to
 locale or whatever when the client connects.


In my case the timezone parameter gets set. If I comment/remove it then UTC
is returned by default. So server here defaulted to UTC.
Executed Select now() from pgAdmin and psql, time gets returned in UTC.


 The timestamps in the server are not actually in a time zone.
 They're all stored as UTC, and the display is altered according to
 what your time zone settings are at the time of query.


Hmm. Missed one observation here, created a test table with timestamp
column of type 'default current_timestamp'.
When the query is executed from JDBC then it stores OS specific local time
into this column.

However when the same query is executed from ODBC then it behaves either as
per the timezone set in postgreSQL.conf or when not set then UTC. So looks
like am missing some setting while executing query from ODBC. Btw also the
pgAdmin and psql behave same as ODBC case. What am missing here which JDBC
is doing correctly.

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 3:07 AM, Adrian Klaver adrian.kla...@aklaver.comwrote:


 Do you know where the machine is going when you do the install?


No if that was the case then target could have set before shipping itself.
Machine TGT-Region is not known here.

Regards...

Re: [GENERAL] UTF-8 collation on Windows?

[Dev Kumkar]

On Thu, Feb 20, 2014 at 3:17 AM, John R Pierce pie...@hogranch.com wrote:

 On 2/19/2014 1:35 PM, Adrian Klaver wrote:


 Unfortunately this is a Windows install and that does not work either.


 windows encodings are a pain.   their Unicode is NOT utf8, its ucs2 aka
 utf16.   I just checked my default install of potsgres 9.2, it appears its
 using WIN1252 encoding, another bastard, this is a modified ISO-8859-1


Yes I guess that will be a different topic altogether to discuss. As
windows internally uses UTF-16 encoding. I have one use case wherein will
have to change the client_encoding in ODBC so that Unicode characters on
windows are stored correctly. Currently on linux machine same unicode data
is stored correctly but in case of windows it is not by default, so there
are tweaks there required as well. But separate topic later.

Coming back here, yes by default it is WIN1252 on windows. So is there no
way I could achieve linux alike utf-8 collation?
Linux and windows sorting order behaves differently in this case.

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 3:26 AM, Andrew Sullivan a...@crankycanuck.cawrote:

 On Thu, Feb 20, 2014 at 03:22:15AM +0530, Dev Kumkar wrote:
 
  Hmm. Missed one observation here, created a test table with timestamp
  column of type 'default current_timestamp'.
  When the query is executed from JDBC then it stores OS specific local
 time
  into this column.

 Probably the JDBC driver is setting its TimeZone.  Really, try it:

 SET TimeZone=UTC;
 SELECT now();

 SET TimeZone=EST5EDT;
 SELECT now();

 and so on.  Try selecting from your table, too, and you will discover
 that the time zone of the timestamps changes.  If you're used to
 certain other RDBMSes, this mode of functioning will be unusual, but
 that really is how it works.


Yes had tried this earlier and it works as expected.

I think I missed that observation earlier and then was looking to set
timezone in postgreSQL.conf which could ultimately resolve this.
But better is to set the TimeZone. Now haven't done anything special but
JDBC is working with setting TimeZone and ODBC not. So what should I look
from here now?

Regards...

Re: [GENERAL] Timezone information

[Dev Kumkar]

On Thu, Feb 20, 2014 at 3:40 AM, Andrew Sullivan a...@crankycanuck.cawrote:

 I think your client should set the TimeZone at connection time.
 That's going to yield the most predictable behaviour for the users, I
 suspect.


Agree! But nothing special is done at JDBC level, is the JDBC driver
setting things like TimeZone by default?
How to do same in case of ODBC?

Regards...

Re: [GENERAL] Case sensitivity

[Dev Kumkar]

On Thu, Dec 12, 2013 at 12:47 PM, Dev Kumkar devdas.kum...@gmail.comwrote:

 + hackers



 On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar devdas.kum...@gmail.comwrote:

 On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar devdas.kum...@gmail.comwrote:

 Actually for searches lower will work.
 But the other important aspect is 'inserts' which would result 2 rows if
 the values are 'A' and 'a'. Intent here to have it case insensitive.

 If CITEXT it will update the same row and works.
 CITEXT is an alternative but was wondering if there is any other
 alternate solution/setting while creating database.

 Also does CITEXT fetch via JDBC works the same way as fetch/set string
 values? Any quick comments here.

 http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html

 Regards...


 Also if the key columns are CITEXT is there any performance issues on
 indexes?

 I am ok with CITEXT but for changing the database design for the
primary/foreign key columns to be CITEXT need some suggestions/comments
regarding performance for inserts/reads.

Awaiting...

Re: [GENERAL] Case sensitivity

[Dev Kumkar]

Thanks John.

Yes CITEXT would work, the only thing its needs DDL changes across and
hence was looking for any such global database parameter setting while
creating database. I have been looking at other discussions and doesn't
look like anything of that coming up soon that makes database case
insensitive.

regards...


On Wed, Dec 11, 2013 at 12:10 PM, John R Pierce pie...@hogranch.com wrote:

 On 12/10/2013 10:31 PM, Dev Kumkar wrote:

 I know about CITEXT data type, but what am looking for is if there any
 parameter at database level which just makes the database case insensitive.


 there's nothing that will do that in postgres.

 whats wrong with using CITEXT ?



 --
 john r pierce  37N 122W
 somewhere on the middle of the 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




-- 
:o) dev

[GENERAL] Scheduled Events

[Dev Kumkar]

How to create scheduled events in postgres simillar to whats event in
Sybase.
Is there any method of doing so?

Also am looking at PgAgent which can create jobs but is it similar like
events in sybase.

Please suggest.

Regards...

Re: [GENERAL] [ADMIN] Scheduled Events

[Dev Kumkar]

Resending...

On Wed, Dec 11, 2013 at 8:29 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 Yes actually that's one alternate solution to use cron or windows
 scheduled tasks.

 The intent is to call certain stored procedures at certain time intervals.


 On Wed, Dec 11, 2013 at 7:24 PM, Payal Singh pa...@omniti.com wrote:

 You can schedule those jobs in a crontab. If it is a SQL command, you can
 use  the -c option with psql.

 On Wed, Dec 11, 2013 at 05:08:12PM +0530, Dev Kumkar wrote:
  How to create scheduled events in postgres simillar to whats event in
  Sybase.
  Is there any method of doing so?
 
  Also am looking at PgAgent which can create jobs but is it similar like
  events in sybase.
 
  Please suggest.
 
  Regards...

Re: [GENERAL] Case sensitivity

[Dev Kumkar]

Can case-insensitive collation help here?


On Wed, Dec 11, 2013 at 4:55 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 Thanks John.

 Yes CITEXT would work, the only thing its needs DDL changes across and
 hence was looking for any such global database parameter setting while
 creating database. I have been looking at other discussions and doesn't
 look like anything of that coming up soon that makes database case
 insensitive.

 regards...


 On Wed, Dec 11, 2013 at 12:10 PM, John R Pierce pie...@hogranch.comwrote:

 On 12/10/2013 10:31 PM, Dev Kumkar wrote:

 I know about CITEXT data type, but what am looking for is if there any
 parameter at database level which just makes the database case insensitive.


 there's nothing that will do that in postgres.

 whats wrong with using CITEXT ?

Re: [GENERAL] Case sensitivity

[Dev Kumkar]

Actually for searches lower will work.
But the other important aspect is 'inserts' which would result 2 rows if
the values are 'A' and 'a'. Intent here to have it case insensitive.

If CITEXT it will update the same row and works.
CITEXT is an alternative but was wondering if there is any other alternate
solution/setting while creating database.

Also does CITEXT fetch via JDBC works the same way as fetch/set string
values? Any quick comments here.
http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html

Regards...


On Wed, Dec 11, 2013 at 8:58 PM, Andrew Sullivan a...@crankycanuck.cawrote:

 On Wed, Dec 11, 2013 at 04:55:07PM +0530, Dev Kumkar wrote:
 You could build lower() indexes on any column you want to search CI
 and lower() all the input text during searches, in order to avoid any
 work on the schema.  Bit of a kludge, though.

 Best,

 A

Re: [GENERAL] Case sensitivity

[Dev Kumkar]

On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 Actually for searches lower will work.
 But the other important aspect is 'inserts' which would result 2 rows if
 the values are 'A' and 'a'. Intent here to have it case insensitive.

 If CITEXT it will update the same row and works.
 CITEXT is an alternative but was wondering if there is any other alternate
 solution/setting while creating database.

 Also does CITEXT fetch via JDBC works the same way as fetch/set string
 values? Any quick comments here.

 http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html

 Regards...


Also if the key columns are CITEXT is there any performance issues on
indexes?

Re: [GENERAL] Case sensitivity

[Dev Kumkar]

+ hackers


On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar devdas.kum...@gmail.comwrote:

 On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar devdas.kum...@gmail.comwrote:

 Actually for searches lower will work.
 But the other important aspect is 'inserts' which would result 2 rows if
 the values are 'A' and 'a'. Intent here to have it case insensitive.

 If CITEXT it will update the same row and works.
 CITEXT is an alternative but was wondering if there is any other
 alternate solution/setting while creating database.

 Also does CITEXT fetch via JDBC works the same way as fetch/set string
 values? Any quick comments here.

 http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html

 Regards...


 Also if the key columns are CITEXT is there any performance issues on
 indexes?

[GENERAL] Case sensitivity

[Dev Kumkar]

How to create case insensitive database?

I know about CITEXT data type, but what am looking for is if there any
parameter at database level which just makes the database case insensitive.

I mean both values 'ABC' and 'abc' are treated same for inserts and also
all the comparisons by default are case insensitive.

Was not able to find anything concrete forums, hope I didn't overlook
anything here.

Regards...

Re: [ODBC] [GENERAL] ODBC constructs

[Dev Kumkar]

On Tue, May 21, 2013 at 7:25 PM, Dann Corbit dcor...@connx.com wrote:

  

 I do not know if you have 64 bit or 32 bit Linux and if it is Redhat or
 Mandrake or whatever.

 To be clear:

 The PostgreSQL distribution allows you to download the PostgreSQL ODBC
 driver.  The PostgreSQL ODBC driver is not a library.  It is a shared
 object that uses a standardized API to communicate with the PostgreSQL
 database.  This allows standards based tools to access the data by linking
 to a single library (the ODBC library) and then connecting to as many
 different database systems as you like that have well written ODBC drivers.
 

 In order to use an ODBC driver, you do need an ODBC library.  Microsoft
 has one that comes installed.  For Posix systems, you can choose between
 IODBC and UNIXODBC implementations.   I would probably try them both if I
 were you and choose the one that I like best.  

 Maybe this google query can help you:


 https://www.google.com/search?q=%28iodbc+OR+unixodbc%29+postgresql+tutorialie=utf-8oe=utf-8aq=trls=org.mozilla:en-US:unofficialclient=firefox-nightly#client=firefox-nightlyhs=6pKrls=org.mozilla:en-US%3Aunofficialsclient=psy-abq=%28iodbc+OR+unixodbc%29+postgresql+tutorialoq=%28iodbc+OR+unixodbc%29+postgresql+tutorialgs_l=serp.12...0.0.0.39894.0.0.0.0.0.0.0.0..0.0...0.0...1c..14.psy-ab.E8PvcYa5HCcpbx=1bav=on.2,or.r_qf.bvm=bv.46751780,d.cGEfp=9deb35a4864e0689biw=1920bih=1027
 

 ** **

Thanks for the info. Its Red Hat Enterprise Linux Server release 5.5.
Please also take a look at my previous reply too.

Re: [ODBC] [GENERAL] ODBC constructs

[Dev Kumkar]

On Tue, May 21, 2013 at 11:28 PM, Dev Kumkar devdas.kum...@gmail.comwrote:

 On Tue, May 21, 2013 at 10:49 PM, John R Pierce pie...@hogranch.comwrote:

 its looking for the RPM installed packages it was linked against. its not
 looking to see if any same named files just happen to be on your system.


 Oh, got it. So the only way is to do bless it thru 'yum install
 unixODBC-libs' only. Hmm need this going tomorrow. Thanks so far!

 The other method which I was trying to manually install psqlODBC library
 is also stuck because of incomplete configuration.


Started first with looking more into building psqlODBC today and things
looks good. Need to write a test program though.

# cd psqlodbc-09.01.0200

-- The configuration failed with following error (same as yesterday)
# ./configure
configure: error: libpq library not found

-- ran with --with-libpq and it could find libpq this time
# ./configure --with-libpq=/usr/pgsql-9.1

-- next make failed with following error
# make
odbcapi.c:1125: error: expected declaration specifiers or '...' before
'SQLROWSE
odbcapi.c:1130: error: conflicting types for 'SQLExtendedFetch'
/usr/local/include/sqlext.h:1927: error: previous declaration of
'SQLExtendedFet
odbcapi.c: In function 'SQLExtendedFetch':
odbcapi.c:1143: error: 'pcrow' undeclared (first use in this function)
odbcapi.c:1143: error: (Each undeclared identifier is reported only once
odbcapi.c:1143: error: for each function it appears in.)
make[1]: *** [odbcapi.lo] Error 1
make[1]: Leaving directory `/installers/psqlodbc-09.01.0200'
make: *** [all] Error 2

-- Went ahead and edited odbcapi.c and changed definition of
SQLExtendedFetch
-- to match with the types defined in /usr/local/include/sqlext/sqlext.h
-- Works this time
# make

# make install
make[1]: Entering directory `/installers/psqlodbc-09.01.0200'
test -z /usr/local/lib || /bin/mkdir -p /usr/local/lib
 /bin/sh ./libtool   --mode=install /usr/bin/install -c  'psqlodbcw.la'
'/usr/local/lib/psqlodbcw.la'
libtool: install: /usr/bin/install -c .libs/psqlodbcw.so
/usr/local/lib/psqlodbcw.so
libtool: install: /usr/bin/install -c .libs/psqlodbcw.lai /usr/local/lib/
psqlodbcw.la
libtool: finish: PATH=... ldconfig -n /usr/local/lib
--
Libraries have been installed in:
   /usr/local/lib

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
 during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
 during linking
   - use the `-Wl,-rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
--
make[1]: Nothing to be done for `install-data-am'.
make[1]: Leaving directory `/installers/psqlodbc-09.01.0200'
#

Well need to write a test program though whether its really working.

Regards...

Re: [ODBC] [GENERAL] ODBC constructs

[Dev Kumkar]

On Wed, May 22, 2013 at 6:06 PM, Dev Kumkar devdas.kum...@gmail.com wrote:

 On Wed, May 22, 2013 at 4:10 PM, Devrim GÜNDÜZ dev...@gunduz.org wrote:


 Yes, or, as mentioned before, you can simply download the RPM directly
 from the repo.

 Thanks Devrim!

 Installed postgres-92 server from
 postgresql92-server-9.2.4-1PGDG.rhel5.x86_64.rpm, actually links which John
 (Thanks!) mentioned were for CentOS, actually installed unixODBC with them
 only but then for postgres DB used the one for RHEL
 http://yum.postgresql.org/9.2/redhat/rhel-5.0-x86_64/
 Had to reload unixODBC-libs-2.2.11-10.el5.x86_64.rpm again and then also
 got postgresql92-odbc rpm from RHEL link.
 Things are now installed.

 So I have one box with compiled unixodbc and psqlodbc from sources.
 And other box where unixodbc and psqlodbc are installed via rpm way.

 Can someone provide a quick C odbc code snippet?

 Regards...


Nevermind, I could write some C odbc programs for verification of compiled
sources and working with postgres db.
Also program compilation and execution is working on both the machines one
with compiled sources of unixODBC and psqlODBC and the other with rpm
repository installed ones.

Pretty long thread. Thanks everyone for their excellent support here!

Regards...

Re: [ODBC] [GENERAL] ODBC constructs

[Dev Kumkar]

On Tue, May 21, 2013 at 10:49 PM, John R Pierce pie...@hogranch.com wrote:

 its looking for the RPM installed packages it was linked against. its not
 looking to see if any same named files just happen to be on your system.


Oh, got it. So the only way is to do bless it thru 'yum install
unixODBC-libs' only. Hmm need this going tomorrow. Thanks so far!

The other method which I was trying to manually install psqlODBC library is
also stuck because of incomplete configuration.

Regards...