[GENERAL] Computing (disjoint) union of range

Hello Dear list, this is more a plpgsql dev. issue. I wrote a plpgsql function to compute union of time range taht works : [1,4]U[3,8]U[12,14]U[16,18] ---> [1,8]U[12,14]U[16,18] It works on multiple rows. My issue is a design issue : I want to work on set of row and return set of row. I am awa

[GENERAL] Qury plan for sql function with security definer

Hello all, I have two simple SQL function. The first is defined with the modifier "security definer", while the second is not. 1 create or replace function func_with_sec_definer(param text) returns setof text as $$ 2 3 select unnest(string_to_array(param, ',')) ; 4 5 $$ languag

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

On 12/09/2013 18:16, Karl Denninger wrote: > > On 9/12/2013 11:11 AM, Patrick Dung wrote: >> While reading some manual of PostgreSQL and MySQL (eg. >> http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html). >> >> I have found that MySQL has stated many incompatibilities and kn

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

* Ivan Voras (ivo...@freebsd.org) wrote: > If I read the documentation correctly > (http://www.postgresql.org/docs/9.3/static/pgupgrade.html), it needs > oldbindir and newbindir arguments pointing to the directories of > PostgreSQL executables for the old and new versions, making it basically > unu

[GENERAL] e: Running/cumulative count using windows

> test=*# select extract (year from signup_date)::text || '/' || extract(month from signup_date)::text, count(email_address), sum(count(email_address)) over (ROWS UNBOUNDED PRECEDING) from test group by 1 order by 1; Have searched the manual and been unable to find reference to the commands 'unbou

Re: [GENERAL] Best way to populate nested composite type from JSON`

On Thu, Sep 12, 2013 at 7:25 PM, Chris Travers wrote: > > > > On Thu, Sep 12, 2013 at 8:47 AM, Merlin Moncure wrote: >> >> >> >> Can we see a hypothetical example? json best practices for me are to >> use standard tables and than serialize/deserialize json as it goes >> through the door and not

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Stephen Frost writes: > * Ivan Voras (ivo...@freebsd.org) wrote: >> If I read the documentation correctly >> (http://www.postgresql.org/docs/9.3/static/pgupgrade.html), it needs >> oldbindir and newbindir arguments pointing to the directories of >> PostgreSQL executables for the old and new versio

Re: [GENERAL] Best way to populate nested composite type from JSON`

On Fri, Sep 13, 2013 at 6:37 AM, Merlin Moncure wrote: > > > It would certainly be better if we could straight up deserialize json > into a nested structure. For now, my advise is to try and structure > your json and the receiving types/tables to not be nested. Using your > example, I was able

Re: [GENERAL] problem with query

Il 12/09/2013 22:34, Roberto Scattini ha scritto: hi, today we discovered that this query doesn't return the expected values: SELECT DISTINCT p.id , p.apellido AS "Apellido", p.nombre AS "Nombre", pf.nombre AS "Funcion", to_char(da.f_ingreso_pg, 'dd/mm/') AS "Fecha Ingreso PG

Re: [GENERAL] problem with query

hi giuseppe, On Fri, Sep 13, 2013 at 11:49 AM, Giuseppe Broccolo < giuseppe.brocc...@2ndquadrant.it> wrote: > > The problem is the encoding: SQL_ASCII encodes only the first 128 > characters, so 'ñ' and 'Ñ' cannot be encoded in ASCII. If you insert text > 'ñ' or 'Ñ' in a table inside a database

Re: [GENERAL] Best way to populate nested composite type from JSON`

On Fri, Sep 13, 2013 at 9:30 AM, Chris Travers wrote: > > > > On Fri, Sep 13, 2013 at 6:37 AM, Merlin Moncure wrote: >> >> >> >> It would certainly be better if we could straight up deserialize json >> into a nested structure. For now, my advise is to try and structure >> your json and the recei

Re: [GENERAL] e: Running/cumulative count using windows

On Fri, Sep 13, 2013 at 8:18 AM, e-letter wrote: >> test=*# select extract (year from signup_date)::text || '/' || > extract(month from signup_date)::text, count(email_address), > sum(count(email_address)) over (ROWS UNBOUNDED PRECEDING) from test > group by 1 order by 1; > > Have searched the man

Re: [GENERAL] Best way to populate nested composite type from JSON`

On Fri, Sep 13, 2013 at 7:58 AM, Merlin Moncure wrote: > Yup. As things stand currently, it's better *not* to make > serialization-driving composite types which when learning the json > stuff I did heavily; it was a habit I learned (and had to unlearn) > from libpqtypes which solves a lot of t

Re: [GENERAL] Best way to populate nested composite type from JSON`

On Fri, Sep 13, 2013 at 10:08 AM, Chris Travers wrote: > On Fri, Sep 13, 2013 at 7:58 AM, Merlin Moncure wrote: >> Yup. As things stand currently, it's better *not* to make >> serialization-driving composite types which when learning the json >> stuff I did heavily; it was a habit I learned (and

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Patrick Dung wrote on 13.09.2013 18:17: The problem of pg_upgrade is that it needed to hold two set of databases data in the server. This is not be desirable (very slow) or possible (space limitation) for database with huge data. For example, if the old version is already using over 50% of the

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

From: Tom Lane To: Stephen Frost Cc: Ivan Voras ; pgsql-general@postgresql.org Sent: Friday, September 13, 2013 9:58 PM Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL >> * Ivan Voras (ivo...@freebsd.org) wrote: >>> If I read the documentatio

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

On Fri, Sep 13, 2013 at 11:17 AM, Patrick Dung wrote: > > > From: Tom Lane > To: Stephen Frost > Cc: Ivan Voras ; pgsql-general@postgresql.org > Sent: Friday, September 13, 2013 9:58 PM > > Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL > >>> * Ivan

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

* Patrick Dung (patrick_...@yahoo.com.hk) wrote: > The problem of pg_upgrade is that it needed to hold two set of databases data > in the server. What? That's absolutely *not* required for pg_upgrade to work. In general, I would recommend that you make a copy of the database, but it's certainly

Re: [GENERAL] Small PosgreSQL locking function request - with bounty

> ...have you used the "for update" clause in your select statements? Hi Ralf, thanks for the reply. I was unaware of the "for update" construct. Thank you! > My understanding is, that "for update" does what you need. I've read through the documentation, found a few examples using "for update" s

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Patrick, On Friday, September 13, 2013, Patrick Dung wrote: > > >What? That's absolutely *not* required for pg_upgrade to work. In > >general, I would recommend that you make a copy of the database, but > >it's certainly not required. > > I mean the old version and new version would need to take

[GENERAL] The signature of postgresql-9.3.0-1-windows.exe is corrupt or invalid

9.3 x32 RTM was downloaded from http://get.enterprisedb.com/postgresql/postgresql-9.3.0-1-windows.exe in Windows 7 x64 using IE 10 After downloading IE message The signature of postgresql-9.3.0-1-windows.exe is corrupt or invalid appears and IE does not allow to run it. How to install 32-b

[GENERAL] trigger or logging

Before running a script (invoked by .sh that will call stored procs which may invoke other triggers), I want to capture every change made in the database which includes the field value before the update and the field value after the update and also capture all inserts. With this data, I would be a

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

From: Stephen Frost To: Patrick Dung Cc: "pgsql-general@postgresql.org" ; Ivan Voras ; Tom Lane Sent: Saturday, September 14, 2013 12:43 AM Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL * Patrick Dung (patrick_...@yahoo.com.hk) wrote: >>

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

From: Thomas Kellerer To: pgsql-general@postgresql.org Sent: Saturday, September 14, 2013 12:27 AM Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL Patrick Dung wrote on 13.09.2013 18:17: >> The problem of pg_upgrade is that it needed to hold tw

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

From: Stephen Frost To: Patrick Dung Cc: "pgsql-general@postgresql.org" ; Ivan Voras ; Tom Lane ; Stephen Frost Sent: Saturday, September 14, 2013 1:13 AM Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL On Friday, September 13, 2013, Patrick Dung wrote: >What?  That's abs

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

On Fri, Sep 13, 2013 at 11:13 AM, Stephen Frost wrote: > Patrick, > > > On Friday, September 13, 2013, Patrick Dung wrote: >> >> >What? That's absolutely *not* required for pg_upgrade to work. In >> >general, I would recommend that you make a copy of the database, but >> >it's certainly not requ

[GENERAL] Postgres 9.2.4 "Double Precision" Precision

I recently upgraded from postgres 8.2.6 to 9.2.4 . For the most part I am enjoying the upgrade. I have found one behavior that I can not explain. Below is sample contents of a table. "VALUE" in column 7 is defined as double precision (table definition is the same in 8.2.6 as 9.2.4). The chan

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Patrick Dung Sent: Friday, September 13, 2013 1:55 PM To: Stephen Frost; pgsql-general@postgresql.org Cc: Ivan Voras; Tom Lane Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL 3. But th

Re: [GENERAL] Postgres 9.2.4 "Double Precision" Precision

On 09/13/2013 11:32 AM, NWRFC Portland wrote: I recently upgraded from postgres 8.2.6 to 9.2.4 . For the most part I am enjoying the upgrade. I have found one behavior that I can not explain. Below is sample contents of a table. "VALUE" in column 7 is defined as double precision (table def

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Stephen Frost > Sent: Friday, September 13, 2013 2:06 PM > To: Patrick Dung > Cc: pgsql-general@postgresql.org; Ivan Voras; Tom Lane > Subject: Re: [GENERAL] Major u

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Patrick, * Patrick Dung (patrick_...@yahoo.com.hk) wrote: > I think the documentation could put a note at the beginning for new users. Yes, probably true. Feel free to propose specific improvements. > 2. Also I think the documentation should provide more info for users that use > packages. > M

Re: [GENERAL] Postgres 9.2.4 "Double Precision" Precision

Arian, It is a single database . Postgres was upgraded from 8.2.6 to 9.2.4... the database contents was not altered in upgrade, that is a 8.2.6 dump was not created then uploaded to 9.2.4. Perhaps handling of double precision, changed from 8.2.6 to 9.2.4 ? Joanne On Fri, Sep 13, 2013 at 11:40

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

From: Stephen Frost To: Patrick Dung Cc: "pgsql-general@postgresql.org" ; Ivan Voras ; Tom Lane Sent: Saturday, September 14, 2013 2:05 AM Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL Hi Stephen, >Patrick, > >* Patrick Dung (patrick_..

Re: [GENERAL] Postgres 9.2.4 "Double Precision" Precision

On 09/13/2013 12:36 PM, Joanne Salerno - NOAA Federal wrote: Arian, It is a single database . Postgres was upgraded from 8.2.6 to 9.2.4... the database contents was not altered in upgrade, that is a 8.2.6 dump was not created then uploaded to 9.2.4. So you used pg_upgrade to move the data? If

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

From: Igor Neyman To: Stephen Frost ; Patrick Dung Cc: "pgsql-general@postgresql.org" ; Ivan Voras ; Tom Lane Sent: Saturday, September 14, 2013 2:14 AM Subject: RE: [GENERAL] Major upgrade of PostgreSQL and MySQL Hi Igor, >> -Original Message

[GENERAL] Hot standby & SR - log shipping required?

Hello all, I'm tackling host standby & streaming replication for the first time, in a small set-up with just two computers (one master, one standby) and an extremely light load (at its busiest, one transaction every couple of minutes). Both systems are running PG 9.1 on Debian Wheezy from apt.post

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

From: Patrick Dung [mailto:patrick_...@yahoo.com.hk] Sent: Friday, September 13, 2013 3:50 PM To: Igor Neyman; Stephen Frost Cc: pgsql-general@postgresql.org; Ivan Voras; Tom Lane Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL For Windows, is it using symbolic links or hard links

[GENERAL] Using LDAP for PostgreSQL permissions/authentication

Looking at using LDAP to ease the pain of maintaining user accounts across many tens of PostgreSQL servers ... As documented, LDAP solves a few of the problems we have -- since everyone will be in LDAP, we can use LDAP's password complexity rules and password expiration to handle those security r

Re: [GENERAL] Using LDAP for PostgreSQL permissions/authentication

* Bill Moran (wmo...@potentialtech.com) wrote: > As documented, LDAP solves a few of the problems we have -- since everyone > will be in LDAP, we can use LDAP's password complexity rules and password > expiration to handle those security requirements, and (of course) when > someone changes their pa

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

On 13 September 2013 21:44, Patrick Dung wrote: > Ivan Voras has replied that the link method work fine in Windows on another > thread. That would be very surprising since I don't run Windows servers :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to y

[GENERAL] How to restore some DBs to a new server?

Running on my MacBook Pro, pgAdmin3 tells me I have four servers in "Server Groups", all local, no network involved. I have dumped two of my DBs from a current server using pg_dump. Then in pgAdmin3 I created another server, let's call it "New". I would like to look at these earlier DBs while not

Re: [GENERAL] Small PosgreSQL locking function request - with bounty

Hello David, I replied to your original e-mail but it must have vanished into the ether. I sent you a brief precis about transaction processing. For "SELECT FOR UPDATE" to function, you MUST have an unique key on the table. For example:- crawlq_id SERIAL NOT NULL PRIMARY KEY USING INDEX TABLESPA

Re: [GENERAL] How to restore some DBs to a new server?

On 9/13/2013 2:03 PM, Bob Futrelle wrote: Running on my MacBook Pro, pgAdmin3 tells me I have four servers in "Server Groups", all local, no network involved. I have dumped two of my DBs from a current server using pg_dump. Then in pgAdmin3 I created another server, let's call it "New". I woul

Re: [GENERAL] Using LDAP for PostgreSQL permissions/authentication

On Fri, 13 Sep 2013 16:29:47 -0400 Stephen Frost wrote: > > > Thus, when I go to log in as wmoran, LDAP checks my password, then informs > > PostgreSQL to allow me in with specified roles, and I can do operations > > granted to those roles. > > That's a little over-simplistic, isn't it? What ab

Re: [GENERAL] Best way to populate nested composite type from JSON`

On Fri, Sep 13, 2013 at 8:51 AM, Merlin Moncure wrote: > > > What's your client side stack? > > merlin > Right now we are using something a little lighter weight in terms db discovery but it doesn't handle this situation very well. I am the author of the PGObject space on CPAN and currently we

Re: [GENERAL] Postgres 9.2.4 "Double Precision" Precision

On 09/13/2013 12:36 PM, Joanne Salerno - NOAA Federal wrote: Arian, It is a single database . Postgres was upgraded from 8.2.6 to 9.2.4... the database contents was not altered in upgrade, that is a 8.2.6 dump was not created then uploaded to 9.2.4. Perhaps handling of double precision, changed

Re: [GENERAL] Hot standby & SR - log shipping required?

On Sep 13, 2013, at 3:51 PM, Raymond O'Donnell wrote: > [1] and one of the wiki > articles [2] seem to indicate that you need to set up log-shipping as > well as SR, whereas one of the wiki articles [2] indicates that > log-shipping isn't required. I've followed [3] and it seems to work fine > in

Re: [GENERAL] Hot standby & SR - log shipping required?

T he log shipping is useful when SR slave can not catch up the master and hungry enough to cause replication stop work.For example,when you want to stop the slave for a long time or do a large copy from,the wal_keep_segments on master reached，SR slave may not catch up the master.If log shipping is