[GENERAL] order by and view def.

Hello, There is something strange with the views definitions, I have "order by" statement amended to the views where the view is ordered by all the columns in the view. Is this a common behavior? Ps: I am using postgresql 8.3 Regards

[GENERAL] streaming replication trigger file

Hi all, I am a bit confused regarding streaming replication. I have two server: A and B. I set up replication successfully. But when the master A goes down, B are not acting as master. My recovery.conf as follows: standby_mode = 'on' primary_conninfo = 'host=10.0.0.91 port=5432 user=postgres' t

Re: [GENERAL] Encryption For Specific Column- Where to store the key

2011/6/16 Manuel Gysin : >>From: "Pavel Stehule" >> >>Hello >> >>try to use a security definer functions >> >>http://www.postgresql.org/docs/current/static/sql-createfunction.html >> >>inside this function you can access to resourcess thats are no >>available from outer for web user >> >>Regards >

Re: [GENERAL] streaming replication trigger file

On 06/16/11 1:31 AM, AI Rumman wrote: When I manually create the C:\\pg\\stopreplication\\standby.txt' file, then it is working. That is, B is becoming the master. So, my question is, how this trigger file should be created so that B will become master automatically as soon as A goes down? you

Re: [GENERAL] Invalid byte sequence for encoding "UTF8": 0xedbebf

BRUSSER Michael wrote: > Is there a way to find the records with the text field containing Unicode bytes "0xedbebf"? > > Unfortunately this is a very old version 7.3.10 This should work on 7.3 (according to the documentation): SELECT id FROM nlsdata WHERE position('\360\235\204\236'::bytea IN val

Re: [GENERAL] streaming replication trigger file

Hi Rumman, Can you send me link to "how to create postgres replication on windows." I tried few hours as per following link, but not succeeded. Which version of postgres are you using ? I tried using 9.0. http://www.postgresql.org/docs/8.2/static/high-availability.html Any type of help would b

[GENERAL] FOREIGN TABLE with dblink

Hi, is there any way to use the new foreign table feature with dblink? That's almost clear to me: CREATE FOREIGN DATA WRAPPER pgsql90; CREATE SERVER srvlocal90 FOREIGN DATA WRAPPER pgsql90 OPTIONS (hostaddr '127.0.0.1', dbname 'lotty'); CREATE USER MAPPING FOR pgsql SERVER srvlocal90 OPTIONS (u

Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!

On 06/15/2011 04:49 PM, Josh Berkus wrote: You do not have to be a C coder to be a patch reviewer. Pretty much all you need to know is: - how to checkout PostgreSQL from Git - how to build PostgreSQL from source - how to apply a patch And you don't even really need to know these things wel

Re: [GENERAL] streaming replication trigger file

Hi, My main database server was not being replicated on standby server. In standby's logs I am getting : 2011-06-16 16:22:03 IST FATAL: database system identifier differs between the primary and standby 2011-06-16 16:22:03 IST DETAIL: The primary's identifier is 5618757375637716504, the st

Re: [GENERAL] No implicit index created when adding primary key with ALTER TABLE

Hi Tom 2011/6/15 Tom Lane : > Stefan Keller writes: >> My explanation is that the message (saying that an index was >> implicitly created) is simply wrong. > > The correct explanation is that you're misinterpreting whatever output > you're looking at. Pls. don't treat inquirers like this - but t

Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!

Josh Berkus schrieb: You do not have to be a C coder to be a patch reviewer. Pretty much all you need to know is: - how to checkout PostgreSQL from Git - how to build PostgreSQL from source - how to apply a patch If you know those three things, you can help with patch review. Of course, if yo

Re: [GENERAL] trouble building user defined agg function with plpython

On Tue, 14 Jun 2011, Tom Lane wrote: > Rick Harding writes: > > CREATE OR REPLACE FUNCTION mysum(curr integer, vals group_data) > > RETURNS integer > > AS $$ > > try: > > curr = curr + vals['weight'] > > except UnboundLocalError: > > plpy.notice("UNBOUND") > > cu

Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

-Original Message- From: Mike Christensen [mailto:m...@kitchenpc.com] Sent: Thursday, June 16, 2011 1:05 AM To: pgsql-general@postgresql.org Subject: Constraint to ensure value does NOT exist in another table? I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.

Re: [GENERAL] No implicit index created when adding primary key with ALTER TABLE

On Jun 16, 2011, at 6:52 AM, Stefan Keller wrote: > IMO this decision is actually questionable. Agreed. One should not have to look at constraints to figure out if there's an index. One should be able to check that directly from the list of indexes. I would think this is really obvious. (But t

Re: [GENERAL] No implicit index created when adding primary key with ALTER TABLE

On Thursday, June 16, 2011 5:52:43 am Stefan Keller wrote: > Hi Tom > > > Then I used pgAdminIII to look for the pkey index and there was > nothing. That was and still is actually the problem. > > When I subsequently created an index > CREATE INDEX ON mytable2(id); > ...or two (:->) > CREATE

Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

I've dealt with something similar by using a check constraint and a stored procedure. The check constraint calls a stored procedure, passing it (in your case) the key you want to make sure doesn't exist in some other table. The stored procedures queries that other table for the key and passes

Re: [GENERAL] Encryption For Specific Column- Where to store the key

On Thu, Jun 16, 2011 at 3:35 AM, Manuel Gysin wrote: > I can trust the dba. But while someone gain access, he can control everything > and could easily extend his rights to dba. > An other way with client side encryption/decryption should be possible with > deployed certificates and keys, but so

Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

On 16 June 2011 14:41, Gauthier, Dave wrote: > I've dealt with something similar by using a check constraint and a stored > procedure.  The check constraint calls a stored procedure, passing it (in > your case) the key you want to make sure doesn't exist in some other table.   > The stored proce

Re: [GENERAL] order by and view def.

salah jubeh writes: > There is something strange with the views definitions, I have "order by" > statement amended to the views where the view is ordered by all the columns > in > the view. Is this a common behavior? > Ps: I am using postgresql 8.3 If the views use SELECT DISTINCT, PG version

Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

I preferred the check constraint mainly because of the feedback "violation" messages. Other, more traditional constraints exist on other columns of the table. The names of those constraints contain information about the nature of the violation. I wanted to standardize the way I detected these

[GENERAL] PostgreSQL 9.0 or 9.1 ?

Hello again! (i got my traditional email-address back!) we have been running our infrastructure on 8.3 for quite some years now, and i am thinking it is now time to upgrade all major parts of our system (java, jboss, postgresql). I would tend to be a little "radical" and go a little optimistic and

Re: [GENERAL] PostgreSQL 9.0 or 9.1 ?

It could be worth considering 9.1. Probably by the time you get production ready version, 9.1 will be already stable (few months I guess). The usual answer to that question is - it will be ready when its ready. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] PostgreSQL 9.0 or 9.1 ?

On Thu, Jun 16, 2011 at 10:06 AM, Achilleas Mantzios wrote: > Till the end of July i must have finished all the migration to the new > versions. > > The migration will involve testing of about 5,458 sql statements and the > migration of some heavily customized in house functions, including > a ve

Re: [GENERAL] PostgreSQL 9.0 or 9.1 ?

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Grzegorz Jaskiewicz > Sent: Thursday, June 16, 2011 11:05 AM > To: Achilleas Mantzios > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] PostgreSQL 9.0 or 9.1

Re: [GENERAL] Encryption For Specific Column- Where to store the key

>From: "Pavel Stehule" > >Hello > >try to use a security definer functions > >http://www.postgresql.org/docs/current/static/sql-createfunction.html > >inside this function you can access to resourcess thats are no >available from outer for web user > >Regards > >Pavel Stehuke I understand the ide

[GENERAL] PostgreSQL 9.0 or 9.1 ?

Hello, we have been running our infrastructure on 8.3 for quite some years now, and i am thinking it is now time to upgrade all major parts of our system (java, jboss, postgresql). I would tend to be a little "radical" and go a little optimistic and greedy about it. I have been using 9.0 as a tes

[GENERAL] set returning function with variable argument - possible?

I have a function that returns a set of a defined type, something like: CREATE OR REPLACE FUNCTION f(arg) RETURNS SETOF return_type AS $$ ... $$ LANGUAGE 'plpgsql' STABLE with (isstrict); Ordinarily this function is called: SELECT * FROM f(constant); But

Re: [GENERAL] set returning function with variable argument - possible?

Hello I don't know if I understand well, but you can do select (f).* from (select f(var) from xtable where ...) x; Regards Pavel Stehule 2011/6/16 Frank Miles : > I have a function that returns a set of a defined type, something like: > >        CREATE OR REPLACE FUNCTION f(arg) RETURNS SETOF

Re: [GENERAL] PostgreSQL 9.0 or 9.1 ?

On 06/16/2011 10:06 AM, Achilleas Mantzios wrote: Till the end of July i must have finished all the migration to the new versions. So i am asking what would be better from your perspective to do? Go for 9.1? or stick to 9.0 and try to deploy it and take the most out of it? When is a stable (relea

Re: [GENERAL] PostgreSQL 9.0 or 9.1 ?

On Thu, Jun 16, 2011 at 2:47 AM, Achilleas Mantzios wrote: > Hello, > we have been running our infrastructure on 8.3 for quite some years now, > and i am thinking it is now time to upgrade all major parts of our system > (java, jboss, postgresql). > I would tend to be a little "radical" and go a l

Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!

Craig, > Well, a quick look through > https://commitfest.postgresql.org/action/commitfest_view/inprogress > suggests that these could use attention: Let me know which one of these you want to take first. I'll put your name by it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San

Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!

> I have no knowledge on the subject one way or the other, that's why > I'm asking.. If the answer is "no, there would be no benefit" then > that's the answer.. However, it seems like generating UUIDs in Win64 > has been broken, perhaps, forever which is why I question if there's a > better librar

Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!

In response to Joshua Berkus : > > > I have no knowledge on the subject one way or the other, that's why > > I'm asking.. If the answer is "no, there would be no benefit" then > > that's the answer.. However, it seems like generating UUIDs in Win64 > > has been broken, perhaps, forever which is w

[GENERAL] getting postgres server on freebsd startup?

I've installed and tested postgresql just fine on FreeBSD 8.2. I've been trying to get postgresql (the server) to start on bootup using /etc/rc.conf system. I'm using the script from the tarball (found in the contrib/start-scripts/freebsd of postreges tarball) I can't seem to get it to work on

Re: [GENERAL] Invalid byte sequence for encoding "UTF8": 0xedbebf

-Original Message- From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Sent: Thursday, June 16, 2011 5:16 AM To: BRUSSER Michael; pgsql-general@postgresql.org Subject: RE: [GENERAL] Invalid byte sequence for encoding "UTF8": 0xedbebf BRUSSER Michael wrote: > Is there a way to find the reco

[GENERAL] Postgres 8.3.10 Alter Table Waiting issue

Hi Team, I am trying to add column to the table that's hanging. alter table hr_firms add column_name biginit; This is the lock I see in the db relation | 564709 | 586888 | | || | | | | 1/8| 1871 | AccessExclusiveLock |

Re: [GENERAL] Postgres performance and the Linux scheduler

On 16 June 2011 19:10, Simon Windsor wrote: > Can the performance of Postgres be boosted, especially on busy systems, >  using the none default DEADLINE Scheduler? Probably, but it's a controversial area, and if you want to speed up a busy system, it would be almost the last thing to look at. Ch

Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

On 16 Jun 2011, at 7:04, Mike Christensen wrote: > I know I can setup a FK constraint to make sure Table1.ColA exists in > Table2.Key, however what if I want to do the reverse? > > I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do > this with any sort of CHECK constraint, trig

Re: [GENERAL] Postgres 8.3.10 Alter Table Waiting issue

On 16 Jun 2011, at 18:30, Pratheeban Jebasingh Tharmaraj wrote: > Hi Team, > > I am trying to add column to the table that's hanging. > > alter table hr_firms add column_name biginit; ^^ typo? > This is the lock

Re: [GENERAL] Postgres performance and the Linux scheduler

On 06/16/2011 02:10 PM, Simon Windsor wrote: Can the performance of Postgres be boosted, especially on busy systems, using the none default DEADLINE Scheduler? Some system reports moderate improvements in throughput, and sometimes larger ones in worst-case latency, when switching from th

[GENERAL] Domains as typedefs: Performance and automatic casting

I plan to define two domains with no contraints, sort of typedefs, to work with date-times inside my application: CREATE DOMAIN instant AS timestamp(3) with time zone; CREATE DOMAIN localdatetime AS timestamp(3) without time zone; Two questions: 1. I guess that there is no performance penalty

Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

>> I know I can setup a FK constraint to make sure Table1.ColA exists in >> Table2.Key, however what if I want to do the reverse? >> >> I want to ensure Table1.ColA does NOT exist in Table2.Key..  Can I do >> this with any sort of CHECK constraint, trigger, custom function, etc? >> Thanks! > > > Pe

[GENERAL] Installing Fedora 15 hosed my db...

Yesterday I upgraded from fedora 14 to fedora 15... The install process sorta trashed my 8.4 db...here is what I have: postgresql-server-9.0.4-1.fc15.i686 postgresql-pltcl-8.4.4-1PGDG.fc12.i386 postgresql-docs-8.4.4-1PGDG.fc12.i386 postgresql-libs-9.0.4-1.fc15.i686 postgresql-contrib-8.4.4-1PGDG.f

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

Hello, I'm still suffering with my Drupal 7.2 site and PostgreSQL 8.4.8 every evening, for example right now. I have tried different combinations for /etc/pgbouncer.ini - for example now I have: [databases] pref = host=/tmp user=pref password=XXX dbname=pref [pgbouncer] logfile = /var/log/pgboun

Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!

Radoslaw, > https://commitfest.postgresql.org/action/patch_view?id=565 > https://commitfest.postgresql.org/action/patch_view?id=579 Actually, I assigned you to 580 instead of 579, because supposedly Noah is reviewing 579. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011/6/16 Alexander Farber : > Hello, > > I'm still suffering with my Drupal 7.2 site and > PostgreSQL 8.4.8 every evening, for example > right now. I have tried different combinations > for /etc/pgbouncer.ini - for example now I have: > > [databases] > pref = host=/tmp user=pref password=XXX dbnam

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On 6/16/11, Cédric Villemain wrote: > 2011/6/16 Alexander Farber : >> I'm still suffering with my Drupal 7.2 site and >> PostgreSQL 8.4.8 every evening, for example >> right now. I have tried different combinations >> for /etc/pgbouncer.ini - for example now I have: >> >> [pgbouncer] >> logfile =

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On Thu, Jun 16, 2011 at 1:27 PM, Alexander Farber wrote: > For example pg_top shows (why is everything idle?): > > last pid:  5215;  load avg:  0.65,  1.64,  2.13;       up 0+00:46:48 > > 20:16:37 > 22 processes: 22 sleeping > CPU states: 12.4% user,  0.0% nice,  0.3% system, 87.4% idle,  0.1% iow

[GENERAL] Executing prepared statements via bind params

Hi all, I'm seeing a bit strange (some might say inconsistent) behaviour, possibly a bug. First, I prepare a statement by running the query: "PREPARE bar (int) AS (SELECT 1, $1, 3)" Then I try to use the generic libpq query function PQsendQueryParams with a query of "EXECUTE bar(2)" and I ge

Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!

Craig Ringer Thursday 16 of June 2011 06:14:13 > On 16/06/11 04:49, Josh Berkus wrote: > > We especially need folks who are able to build PostgreSQL on Windows, as > > we have several Windows-specific patches and no reviewers for them. > > Urrrggh. Does that mean I have to volunteer myself? ;

Re: [GENERAL] Installing Fedora 15 hosed my db...

Jerry LeVan writes: > Yesterday I upgraded from fedora 14 to fedora 15... > The install process sorta trashed my 8.4 db...here is > what I have: > postgresql-server-9.0.4-1.fc15.i686 > postgresql-pltcl-8.4.4-1PGDG.fc12.i386 > postgresql-docs-8.4.4-1PGDG.fc12.i386 > postgresql-libs-9.0.4-1.fc15.i68

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On 6/16/11, Scott Marlowe wrote: > What do vmstat 1 and iostat -xd 1 (or equivalent for your OS) say? So > here's a healthy not working too hard machine: > > procs ---memory-- ---swap-- -io -system-- > cpu > r b swpd free buff cache si sobibo

Re: [GENERAL] Installing Fedora 15 hosed my db...

On Thu, 2011-06-16 at 16:05 -0400, Tom Lane wrote: > Jerry LeVan writes: > > Yesterday I upgraded from fedora 14 to fedora 15... > > The install process sorta trashed my 8.4 db...here is > > what I have: > > postgresql-server-9.0.4-1.fc15.i686 > > postgresql-pltcl-8.4.4-1PGDG.fc12.i386 > > postgre

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On Thu, Jun 16, 2011 at 2:08 PM, Alexander Farber wrote: > It shows (but I have switched pgbouncer from session > to transaction mode now and it seems to have helped - > Drupal+my scripts are running again and pg_top shows > 2-3 SELECTs at the top and the rest is "idle"): > > # vmstat 1 > procs --

[GENERAL] Postgres performance and the Linux scheduler

Hi Can the performance of Postgres be boosted, especially on busy systems, using the none default DEADLINE Scheduler? Simon Simon Windsor Eml: simon.wind...@cornfield.org.uk Tel: 01454 617689 Mob: 07590 324560 "There is nothing in the

Re: [GENERAL] Executing prepared statements via bind params

Peter Bex writes: > But when I try to do the same but pas the 2 as a parameter, > (I do "EXECUTE bar($1)" with $1 bound to "2"), I get an error: Why would you do that, rather than executing the prepared statement directly with PQexecPrepared? Interposing an EXECUTE doesn't do anything but add pa

Re: [GENERAL] Executing prepared statements via bind params

On Thu, Jun 16, 2011 at 05:19:41PM -0400, Tom Lane wrote: > Peter Bex writes: > > But when I try to do the same but pas the 2 as a parameter, > > (I do "EXECUTE bar($1)" with $1 bound to "2"), I get an error: > > Why would you do that, rather than executing the prepared statement > directly with

Re: [GENERAL] "interval hour to minute" or "interval day to minute"

Noah Misch wrote: > On Sun, Apr 17, 2011 at 04:55:51PM +0100, Jack Douglas wrote: > > I discovered the 'fields' option of 'interval', but i can't figure out > > from the docs how it is supposed to work. Are "hour to minute" and "day > > to minute" really the same thing? And if not, in what circ

[GENERAL] Installing Fedora 15 hosed my db...

> > > On Jun 16, 2011, at 5:16 PM, Dagan McGregor wrote: > >> On Thu, 16 Jun 2011 15:01:30 -0400, Jerry LeVan wrote: >>> Yesterday I upgraded from fedora 14 to fedora 15... >>> >>> The install process sorta trashed my 8.4 db...here is >>> what I have: >>> postgresql-server-9.0.4-1.fc15.i686 >>>

Re: [GENERAL] set returning function with variable argument - possible?

The only way to feed another relation into a function is to place the function in the SELECT list. If you then want to expand the result of the function call you alias the function result and then ".*" against it. WITH feeder AS ( SELECT f(var) AS result FROM xtable WHERE ... ) SELECT (feeder.res

[GENERAL] ncoding "Table Name" and "Filed Name"

Hello there, I request you to give your commends on the following,  1. We have planned to cipher our table name  and filed name [the necessary data will be ciphered with predefined functions with 'key'] 2. This cipher functions will be our own code 3. Using these functions we can mange the applica

Re: Fw: [GENERAL] PostgreSQL 9.0 or 9.1 ?

Thanx, i think i'll just stick with 9.0 and try to take full advantage of it and when we are comfortable with all those features then move to 9.1 > From: Merlin Moncure > To: Achilleas Mantzios > Cc: "pgsql-general@postgresql.org" > Sent: Thursday, June 16, 2011 7:12 PM > Subject: Re: [GENERAL

Re: [GENERAL] PostgreSQL 9.0 or 9.1 ?

Thanx brad, i think 9.0 would be the most wise decision for the time being. Στις Thursday 16 June 2011 18:29:16 γράψατε: > > -Original Message- > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > > ow...@postgresql.org] On Behalf Of Grzegorz Jaskiewicz > > Sent: Thursday,

Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

On 16 Jun 2011, at 20:47, Mike Christensen wrote: >>> I know I can setup a FK constraint to make sure Table1.ColA exists in >>> Table2.Key, however what if I want to do the reverse? >>> >>> I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do >>> this with any sort of CHECK const

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On 16 Jun 2011, at 21:42, Alexander Farber wrote: > On 6/16/11, Cédric Villemain wrote: >> 2011/6/16 Alexander Farber : >>> I'm still suffering with my Drupal 7.2 site and >>> PostgreSQL 8.4.8 every evening, for example >>> right now. I have tried different combinations >>> for /etc/pgbouncer.ini