Re: [GENERAL] Possible to improve optimisation / index usage based on domain properties of a function

On Thu, Feb 20, 2014 at 3:34 PM, Alban Hertroys wrote: > > If however, I was to provide the below query, it uses a sequential scan > based plan. The planner is unable to utilise any indexes because it can't > know what the function is going to return - thus unable to constrain the > range at the

Re: [GENERAL] Possible to improve optimisation / index usage based on domain properties of a function

On 20 Feb 2014, at 7:21, Sameer Kumar wrote: > If however, I was to provide the below query, it uses a sequential scan based > plan. The planner is unable to utilise any indexes because it can’t know > what the function is going to return – thus unable to constrain the range at > the time of

[GENERAL] oid of Datum

In a postgres C function (CREATE FUNCTION LANGUAGE C), is it possible to get the OID of a parameter and therefore overload a C function? Is there any other way of overloading a C function? Thanks James -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to y

Re: [GENERAL] Possible to improve optimisation / index usage based on domain properties of a function

> > > Hi all, > > Take the following scenario > > I have a set of partitions inherited from a parent table, called *streams* > . > One of the properties of these tables is a timestamp field, nothing fancy > about it. > > I also have a qualified index on this field. > > > I've noticed that if I perf

Re: [GENERAL] Simple Web-based alternative to PgAdmin

On Wed, Feb 12, 2014 at 12:17 AM, AlexK wrote: > I would like to give my users the ability to invoke read-only functions and > select statements, so that they can easily see the data. Both me and the > users have experience mostly with SQL Server, so anyone can keep like 30 > connections without

Re: [GENERAL] Query

On 02/19/2014 08:01 PM, Daniel Cardno wrote: > Hi, > > I have recently changed from HEM2 to PT4, during the changeover i > uninstalled Postgres and HM2, i then went on to install PT4 and > postgres 8.4. > > When i know run PT4 and try and install Postgres from there it shows > me the top two below

Re: [GENERAL] Timezone information

On 02/19/2014 01:52 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan mailto:a...@crankycanuck.ca>> wrote: 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 i

Re: [GENERAL] Timezone information

Dev Kumkar writes: > On Thu, Feb 20, 2014 at 3:07 AM, Adrian Klaver > wrote: >> 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. The functionality of determining a

Re: [GENERAL] Timezone information

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

Re: [GENERAL] Timezone information

On Thu, Feb 20, 2014 at 3:40 AM, Andrew Sullivan wrote: > 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 Ti

Re: [GENERAL] Timezone information

On 02/19/2014 02:07 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 3:26 AM, Andrew Sullivan mailto:a...@crankycanuck.ca>> wrote: 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 typ

Re: [GENERAL] Timezone information

On Thu, Feb 20, 2014 at 03:37:20AM +0530, Dev Kumkar wrote: > 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? I think your client should set the TimeZone at connection time. That's

Re: [GENERAL] Timezone information

On Thu, Feb 20, 2014 at 3:26 AM, Andrew Sullivan wrote: > 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 store

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

On Thu, Feb 20, 2014 at 3:17 AM, John R Pierce 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 ins

Re: [GENERAL] Timezone information

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 dr

Re: [GENERAL] Timezone information

On Thu, Feb 20, 2014 at 3:07 AM, Adrian Klaver wrote: > > 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] Timezone information

On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan wrote: > 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,

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

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, anothe

Re: [GENERAL] Timezone information

On 02/19/2014 01:16 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 2:32 AM, Andrew Sullivan mailto:a...@crankycanuck.ca>> wrote: 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. Her

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

On 02/19/2014 01:30 PM, John R Pierce wrote: On 2/19/2014 1:21 PM, Dev Kumkar wrote: createdb -U postgres -E utf8 -l en-US -T template0 mynewdb Password: *createdb: database creation failed: ERROR: invalid locale name: "en-US"* I believe its en_US ... _ not - Unfortunately this is a Windows

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

On 20/02/14 10:28, Adrian Klaver wrote: On 02/19/2014 01:21 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 2:45 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Have you tried it? Note that the locale name is different then the one Linux. On Linux it is en_US. What

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

On 2/19/2014 1:21 PM, Dev Kumkar wrote: createdb -U postgres -E utf8 -l en-US -T template0 mynewdb Password: *createdb: database creation failed: ERROR: invalid locale name: "en-US"* I believe its en_US ... _ not - -- john r pierce 37N 122W somewhere

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

On 02/19/2014 01:21 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 2:45 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: 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

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

On Thu, Feb 20, 2014 at 2:45 AM, Adrian Klaver wrote: > > 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: *cre

Re: [GENERAL] Timezone information

On Thu, Feb 20, 2014 at 02:46:46AM +0530, Dev Kumkar wrote: > 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. > No, select now() would return the ti

Re: [GENERAL] Timezone information

On Thu, Feb 20, 2014 at 2:32 AM, Andrew Sullivan wrote: > 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 inst

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

On 02/19/2014 01:09 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 2:24 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Alright last shot:) Taking hint from here: http://msdn.microsoft.com/en-__us/library/x99tb11d.aspx

Re: [GENERAL] Timezone information

On 02/19/2014 01:02 PM, Andrew Sullivan wrote: On Thu, Feb 20, 2014 at 02:24:53AM +0530, Dev Kumkar wrote: 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()" re

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

On Thu, Feb 20, 2014 at 2:24 AM, Adrian Klaver wrote: > 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 > > If that does not work, not sure where to go. This won't work on Windows. Note t

Re: [GENERAL] Timezone information

On Thu, Feb 20, 2014 at 02:24:53AM +0530, Dev Kumkar wrote: > 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. Try issu

Re: [GENERAL] Timezone information

On Thu, Feb 20, 2014 at 2:06 AM, John R Pierce 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 th

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

On 02/19/2014 12:43 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 2:01 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Just noticed you are not specifying the template database. Try using template0: createdb -U postgres -E utf8 --lc-ctype=american_usa --lc-collate=am

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

On Thu, Feb 20, 2014 at 2:01 AM, Adrian Klaver wrote: > 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 Same result i.e. LC_COLLATE and LC_CTYPE gets set as 'Engl

Re: [GENERAL] Timezone information

On 2/19/2014 11:59 AM, Dev Kumkar wrote: 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. postgres handles timezones on a per c

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

On 02/19/2014 12:16 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 1:41 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: What does it set LC_CTYPE to? So what happens if you do?: createdb -U postgres -E utf8 -l american_usa.65001 *createdb: database creation failed: ER

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

On Thu, Feb 20, 2014 at 1:41 AM, Adrian Klaver wrote: > What does it set LC_CTYPE to? > > So what happens if you do?: > > createdb -U postgres -E utf8 -l american_usa.65001 > *createdb: database creation failed: ERROR: invalid locale name: "american_usa.65001" * > or > > createdb -U postgres

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

On 02/19/2014 12:03 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 1:19 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: So what is the exact command you are using? createdb -U postgres -E utf8 -l american_usa Above command fails to create utf-8 LC_COLLATE. What does it set LC

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

On Thu, Feb 20, 2014 at 1:19 AM, Adrian Klaver wrote: > So what is the exact command you are using? createdb -U postgres -E utf8 -l american_usa Above command fails to create utf-8 LC_COLLATE. Regards...

Re: [GENERAL] Timezone information

On Wed, Feb 19, 2014 at 8:31 PM, Tom Lane 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

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

On 02/19/2014 11:42 AM, Dev Kumkar wrote: On Wed, Feb 19, 2014 at 10:16 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: 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/lo

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

On Wed, Feb 19, 2014 at 10:16 PM, Adrian Klaver wrote: > 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

Re: [GENERAL] configuration error

As much my knowledge. There is no pre requisite. It would work fine after your change. As said by Ray, have a closer look to the log file as mentioned. Regards Rahul Saha On Wednesday, February 19, 2014, Raymond O'Donnell wrote: > On 19/02/2014 12:21, saravanan Chow wrote: > > Hi, > > I'm work

Re: [GENERAL] pg_dump/pg_restore issues

On 02/19/2014 10:32 AM, Leonardo M. Ramé wrote: On 2014-02-19 10:23:58 -0800, Adrian Klaver wrote: Another alternative would be to add another backup: pg_dump -s -t table1 -t table2 -f view.dump This will dump the table definitions only which is all you need. And then in order restore: vie

Re: [GENERAL] pg_dump/pg_restore issues

On 2014-02-19 10:23:58 -0800, Adrian Klaver wrote: > On 02/19/2014 10:08 AM, bricklen wrote: > > > >On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé > >mailto:l.r...@griensu.com>> wrote: > > > >Hi, I'm backing up a big database using the --exclude-table option for > >two tables, say table1

Re: [GENERAL] pg_dump/pg_restore issues

On 02/19/2014 10:08 AM, bricklen wrote: On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé > wrote: Hi, I'm backing up a big database using the --exclude-table option for two tables, say table1 and table2. Then another backup of only those tables, so, the

Re: [GENERAL] pg_dump/pg_restore issues

On 2014-02-19 10:08:19 -0800, bricklen wrote: > On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé wrote: > > > Hi, I'm backing up a big database using the --exclude-table option for > > two tables, say table1 and table2. Then another backup of only those > > tables, so, the final result are three

Re: [GENERAL] pg_dump/pg_restore issues

On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé wrote: > Hi, I'm backing up a big database using the --exclude-table option for > two tables, say table1 and table2. Then another backup of only those > tables, so, the final result are three backup files. > > basic.backup > table1.backup > table2.

Re: [GENERAL] How do I track down a possible locking problem?

On Wed, Feb 19, 2014 at 2:40 AM, Herouth Maoz wrote: > > On 18/02/2014, at 19:02, Jeff Janes wrote: > > On Mon, Feb 17, 2014 at 8:45 AM, Herouth Maoz wrote: > >> I have a production system using Postgresql 9.1.2. >> >> The system basically receives messages, puts them in a queue, and then >> seve

[GENERAL] pg_dump/pg_restore issues

Hi, I'm backing up a big database using the --exclude-table option for two tables, say table1 and table2. Then another backup of only those tables, so, the final result are three backup files. basic.backup table1.backup table2.backup The problem I'm facing is at the restore moment is that basic.b

Re: [GENERAL] Issues with patitionning and triggers

Samuel Gilbert writes: > Here is a complete example demonstrating the issue I am encountering : What seems to be happening is: 1. The parent table has a NOT NULL constraint on modificationDate. 2. parent_dispatcher_trigger does RETURN NEW, so the tuple will be inserted into the parent table as

Re: [GENERAL] Support for Alert

Have a look here http://bucardo.org/wiki/Check_postgres Regards On Wednesday, February 19, 2014 5:51 PM, Alejandro Carrillo wrote: Hi, PostgreSQL have a way to put alerts about number of connections, tablespace used, etc like the DBMS_SERVER_ALERT package of Oracle? Thanks you

Re: [GENERAL] Support for Alert

On 02/19/2014 08:49 AM, Alejandro Carrillo wrote: Hi, PostgreSQL have a way to put alerts about number of connections, tablespace used, etc like the DBMS_SERVER_ALERT package of Oracle? Thanks you No but any number of monitoring systems already support PostgreSQL: Zabbix, New Relic, OpenNMS

Re: [GENERAL] Issues with patitionning and triggers

Hello everyone, Here is a complete example demonstrating the issue I am encountering : CREATE TABLE parent ( split INTEGER NOT NULL, happiness INTEGER NOT NULL, modificationDate TIMESTAMP NOT NULL ); CREATE TABLE child_split1 ( CHECK (split = 1) ) INHERITS (parent); CREATE TABLE child_s

[GENERAL] Support for Alert

Hi, PostgreSQL have a way to put alerts about number of connections, tablespace used, etc like the DBMS_SERVER_ALERT package of Oracle? Thanks you

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

On 02/19/2014 06:41 AM, Dev Kumkar wrote: 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 ut

Re: [GENERAL] configuration error

On 19/02/2014 12:21, saravanan Chow wrote: > Hi, > I'm working in postgresql-9.1.1 on windows 7 ,so i want to know what is > the pre-requiest to change the shared buffer in windows 7. > In my test machine i've 4gb ram. I tried to increase the shared buffer 1gb > to 2gb and restart the services, but

[GENERAL] configuration error

Hi, I'm working in postgresql-9.1.1 on windows 7 ,so i want to know what is the pre-requiest to change the shared buffer in windows 7. In my test machine i've 4gb ram. I tried to increase the shared buffer 1gb to 2gb and restart the services, but it shown the error like " the postgresql-9.1 - post

Re: [GENERAL] Timezone information

Dev Kumkar writes: > How to set timezone in postgreSQL database to pick operating system level > timezone information. 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

[GENERAL] Possible to improve optimisation / index usage based on domain properties of a function

Hi all, Take the following scenario I have a set of partitions inherited from a parent table, called streams. One of the properties of these tables is a timestamp field, nothing fancy about it. I also have a qualified index on this field. I’ve noticed that if I perform the following query, the

[GENERAL] UTF-8 collation on Windows?

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

Re: [GENERAL] why VARIADIC functions executed on array input?

Hello 2014-02-19 15:17 GMT+01:00 Tjibbe : > CREATE FUNCTION foo (VARIADIC anyarray) RETURNS void AS $$ > DECLARE > BEGIN > END > $$ LANGUAGE plpgsql; > SELECT foo (ARRAY[1]); > > I would expect an error: Like: > ERROR: function f3oo(integer[]) does not exist > > But I get this error: > ERROR: co

Re: [GENERAL] why VARIADIC functions executed on array input?

On Wed, Feb 19, 2014 at 8:17 AM, Tjibbe wrote: > CREATE FUNCTION foo (VARIADIC anyarray) RETURNS void AS $$ > DECLARE > BEGIN > END > $$ LANGUAGE plpgsql; > SELECT foo (ARRAY[1]); > > I would expect an error: Like: > ERROR: function f3oo(integer[]) does not exist > > But I get this error: > ERROR:

[GENERAL] Timezone information

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 qu

[GENERAL] why VARIADIC functions executed on array input?

CREATE FUNCTION foo (VARIADIC anyarray) RETURNS void AS $$ DECLARE BEGIN END $$ LANGUAGE plpgsql; SELECT foo (ARRAY[1]); I would expect an error: Like: ERROR: function f3oo(integer[]) does not exist But I get this error: ERROR: could not find array type for data type integer[] Why VARIADIC funct

Re: [GENERAL] File system level backup of shut down standby does not work?

On 02/19/2014 11:43 AM, Albe Laurenz wrote: > Jürgen Fuchsberger wrote: >> One very important thing I just noted when shutting down and restarting >> my standby server: >> >> My standby server *always needs the last WAL-file* from the archive >> directory, even when the shut down was "smart". Wit

Re: [GENERAL] File system level backup of shut down standby does not work?

That is correct. When the source server is shut down, it may or may not be caught up for replication (most likely not). You'll need all of the WAL files that haven't been applied yet; if for some reason you've got a large amount of replication lag, you might need more than just the first. I'm n

Re: [GENERAL] File system level backup of shut down standby does not work?

Jürgen Fuchsberger wrote: > One very important thing I just noted when shutting down and restarting > my standby server: > > My standby server *always needs the last WAL-file* from the archive > directory, even when the shut down was "smart". Without this the > consistent recovery state will not b

Re: [GENERAL] How do I track down a possible locking problem?

On 18/02/2014, at 19:02, Jeff Janes wrote: > On Mon, Feb 17, 2014 at 8:45 AM, Herouth Maoz wrote: > I have a production system using Postgresql 9.1.2. > > The system basically receives messages, puts them in a queue, and then > several parallel modules, each in its own thread, read from that q

Re: [GENERAL] File system level backup of shut down standby does not work?

All, One very important thing I just noted when shutting down and restarting my standby server: My standby server *always needs the last WAL-file* from the archive directory, even when the shut down was "smart". Without this the consistent recovery state will not be reached. 2014-02-19 11:10:20